oracle基础知识(2)

Oracle基础一,oracle的安装与卸载二,一些基本操作:1,登录命令:sqlplus,sqlplusw.以sqlplusw登录后,首先进行环境设置.清理显示页面:clearscr;设置每行显示的长度,setlinesize200;(每行显示200个字符)设置每页显示数据行数;setpagesize40;(每页显示40行数据)2,Sql语句编辑:1)命令:ed+文件名(如:aa);可以在文件中编写要执行的sql语句;2)命令:@aa执行文件aa中编写的sql语句;命令@d:/bb.txt或者@d:bb.txt可以执行d盘bb.txt中编写的sql语句;命令@d:/bb或者@d:bb可以执行d盘bb.sql中编写的sql语句;3,变换用户连接数据库:一般用户(scott)和普通管理员(system):conn用户名/密码;超级管理员(sys):conn用户名/密码assysdba;在sys用户下查询scott用户下的emp表中的信息:select*fromscott.emp;,显示当前连接数据库的用户:showuser;查询当前用户的所有表:select*fromtab;查看表结构:desc表名;再次执行前一次运行的SQL语句:/;三,scott用户下的表1,员工表emp2,部门表dept:3,工资等级表salgrade:4,奖金表bonus:四,简单的sql语句1,简单查询语句SELECT列名别名FROMemp;问题1:查询所有的员工的,姓名,工作问题2:查询所有工作?2,去掉重复内容DISTINCT关键字SELECTDISTINCT*FROMemp;问题3:查询所有员工的编号和工作,是否还能去掉重复?问题4:查询所有员工的编号和工作,查询结果显示为:谁的工作是什么.3,字符串连接操作符||.SELECTename||'的工作是'||jobFROMemp;问题5:查询所有员工的姓名,和工资,并算出年薪4,四则运算SELECTename,sal,sal*12FROMemp;问题6:查询工资大于2000的员工的所有信息.5,限定查询WHERE语句SELECT*FROMempWHEREsal2000;问题7:查询每个月有奖金的员工的所有信息.6,ISNOTNULL修饰SELECT*FROMempWHEREcommISNOTNULL;问题8:查询每个月没有奖金的员工的所有信息.7,ISNULL修饰SELECT*FROMempWHEREcommISNULL;问题9:查询工资大于1000的,并且每月有奖金的员工的所有信息.8,多条件查询AND操作符SELECT*FROMempWHEREsal1000ANDcommISNOTNULL;问题10:查询工资大于1000的,或者每月有奖金的员工的所有信息.9,多条件查询OR操作符SELECT*FROMempWHEREsal1000ORcommISNOTNULL;问题10:查询工资不大于1000的,并且每月没有奖金的员工的所有信息.10,NOT修饰SELECT*FROMempWHERENOT(sal1000ANDcommISNOTNULL);问题11:查询工资大于等于1500的,并且小于等于3000的员工的所有信息.问题12:查询81年入职的员工的所有信息.11,BETWEEN...AND...语句SELECT*FROMempWHEREsalBETWEEN1500AND3000;SELECT*FROMempWHEREhiredateBETWEEN'1-1月-81'AND'31-12月-81';问题13:查询编号是7844,7698,7900的员工的所有信息.12,IN修饰SELECT*FROMempWHEREempnoIN(7844,7698,7900);问题14:查询姓名不是SMITH,KING的员工的所有信息.13,NOTIN修饰SELECT*FROMempWHEREenameNOTIN('SMITH','KING');问题15:查询姓名中带I字母的员工的所有信息.问题16:查询姓名中第二个字母是I的员工的所有信息.14,LIKE修饰SELECT*FROMempWHEREenameLIKE'%I%'SELECT*FROMempWHEREenameLIKE'_I%'问题17:查询编号不是7844的员工的所有信息.15,不等于符号!=,SELECT*FROMempWHEREempno!=7844;问题18:查询所有员工信息,按照工资由低到高排序.问题19:查询所有员工信息,按照工资由高到低排序.如果工资相等,按照入职时间由早到晚排序.16,ORDERBY语句SELECT*FROMempORDERBYsalASC;SELECT*FROMempORDERBYsalDESC,hiredateASC;四,单行函数1,字符函数UPPER(字符串)把字符串表示为大写.问题1:查询员工表中姓名是SMITH的员工信息.要求不考虑参数的大小写.SELECT*FROMempWHEREename=UPPER('smith');LOWER(字符串)把字符串表示为小写.INITCAP(参数)把参数中的单词的首字母表示为大写,其他字母小写.SELECTINITCAP(ename)FROMemp;CONCAT(字符串1,字符串2,...)把字符串连接成一个字符串SELECT*FROMempWHEREename=CONCAT('SMI','TH');SUBSTR(字符串,下标)把字符串从下标开始截取SUBSTR(字符串,下标,长度)把字符串从下标开始截取成一定长度的字符串注意:这个函数的下标是从0开始呢,还是从1开始?问题2:查询员工的姓名,不显示姓名的前2个字母.SELECTename,SUBSTR(ename,3)FROMemp;问题3:查询员工的姓名,和姓名的前3个字母.SELECTename,SUBSTR(ename,1,3)FROMemp;LENGTH(字符串)求字符串的长度.问题4:查询员工的姓名,和姓名的长度.SELECTename,LENGTH(ename)FROMemp;问题5:查询员工的姓名,和姓名的后3个字母.SELECTename,SUBSTR(ename,LENGTH(ename)-2)FROMemp;SELECTename,SUBSTR(ename,-3,3)FROMemp;REPLACE(字符串,原字符,新字符)问题6:查询员工的姓名,把姓名中的M字母换成X字母.SELECTename,REPLACE(ename,'M','X')FROMempWHEREename='SMITH';2,数值函数假设数据库里面有一条数据的值是8765.5678,这里可用dual表模拟.ROUND(数据)默认个位数四舍五入.SELECTROUND(8765.5678)FROMdual;ROUND(数据,保留小数位数)保留小数位数四舍五入.SELECTROUND(8765.5678,2)FROMdual;问题1:把8765.5678这个数按照十位数四舍五入.SELECTROUND(8765.5678,-2)FROMdual;TRUNC(数据)去掉小数位,不进行四舍五入SELECTTRUNC(8765.5678)FROMdual;SELECTTRUNC(8765.5678,2)FROMdual;SELECTTRUNC(8765.5678,-2)FROMdual;MOD(被除数,除数)取余(取模)SELECTMOD(5,2)FROMdual;3,日期函数Oracle中的日期是可以进行加减等一些运算的.计算规律:日期-日期=天数(数字)日期-数字=日期日期+数字=日期SYSDATE表示当前日期SELECTSYSDATEFROMdual;问题1:求出SMITH入职时到现在的星期数.SELECT(SYSDATE-hiredate)/7FROMempWHEREename='SMITH';问题2:求出SMITH入职时到现在的月数.SELECT(SYSDATE-hiredate)/?FROMempWHEREename='SMITH';MONTHS_BETWEEN(日期,日期)求出两个日期范围的月数.SELECTMONTHS_BETWEEN(SYSDATE,hiredate)FROMempWHEREename='SMITH';问题3:求两个月后的日期是多少.ADD_MONTHS(日期,月数)求出日期+月数后的日期.SELECTADD_MONTHS(SYSDATE,2)FROMdual;问题4:下一个星期二的日期是多少.NEXT_DAY(日期,'星期几')求日期后的星期几的日期SELECTNEXT_DAY(SYSDATE,'星期二')FROMdual;问题5,求出当前月的最后一天的日期.LAST_DAY(日期)求出日期所在月份的最后一天的日期SELECTLAST_DAY(SYSDATE)FROMdual;4,转换函数日期转换时的一些格式:年:y月:m日:d数字转换时的格式:'9':表示一位数字(可以是任意数)数字金额区域格式:$:美元L:local的缩写,以本地语言显示金额.TO_CHAR(参数,格式)把参数转换为指定格式的字符串问题1:查询员工的入职日期,把年月日分开显示.SELECTTO_CHAR(hiredate,'yyyy')年,TO_CHAR(hiredate,'mm')月,TO_CHAR(hiredate,'dd')日FROMemp;问题2:查询员工的入职日期,以如2010-10-01格式显示.SELECTTO_CHAR(hiredate,'yyyy-mm-dd')FROMemp;问题3:查询员工的入职日期,以如2010-10-1格式显示.SELECTTO_CHAR(hiredate,'fmyyyy-mm-dd')FROMemp;问题4:查询员工的工资,以如1,500格式显示;SELECTTO_CHAR(sal,'9,999')FROMemp;问题5:查询员工的工资,以如$1,500格式显示;SELECTTO_CHAR(sal,'$9,999')FROMemp;问题6:查询员工的工资,以如￥1,500格式显示;SELECTTO_CHAR(sal,'L9,999')FROMemp;TO_NUMBER(数字字符串)将数字字符串转换为数字类型SELECTTO_NUMBER('111')+TO_NUMBER('222')FROMdual;TO_DATE(字符串日期,格式)将字符串日期按照格式转换成日期类型SELECTTO_DATE('2010-10-10','yyyy-mm-dd')FROMdual;5,通用函数问题1:查询员工的姓名,年薪(sal+comm)*12SELECTename,(sal+comm)*12年薪FROMemp;NVL(参数1,参数2)如果参数1的值为null,则把参数2的值赋给参数1.SELECTename,(sal+NVL(comm,0))*12年薪FROMemp;DECODE(参数0,参数a1,参数a2,参数b1,参数b2,参数c1参数,c2...)如果参数0的值=参数a1的值那么返回参数a2的值如果参数0的值=参数b1的值那么返回参数b2的值如果参数0的值=参数c1的值那么返回参数c2的值以此类推.例:SELECTDECODE(1,1,1,2,2,3,3)FROMdual;问题2:查询员工信息,要求显示结果如下:SELECTempno编号,ename姓名,hiredate入职日期,DECODE(job,'CLERK','接待员','ANALYST','分析员','SALESMAN','销售员','PRESIDENT','总经理','MANAGER','经理')职位FROMemp;五,多表查询例1:同时查询emp表和dept表中的所有数据.SELECT*FROMemp,dept;SELECTCOUNT(*)FROMemp;SELECTCOUNT(*)FROMdept;会发现查询结