ORACLE第三章

第三章多表查询及SQL函数课程目标熟练掌握SQL语言进行多表查询熟练掌握SQL语言进行子查询会使用基本的数据库函数SQL语言进行多表查询多表的查询操作涉及到多个表的连接，如果不加相应的连接条件会出现“笛卡尔连接”例如：selectemp.empno,emp.ename,emp.deptno,dept.dname,dept.locfromscott.emp,scott.dept;SQL语言进行多表查询产生笛卡尔连接的原因是没有指明相应的连接条件，应该尽量避免笛卡尔连接的产生，会产生效率上的问题。带连接条件的等值多表查询例子1：请你选出雇员的名字，和雇员在部门的名字？错误写法：selectename,deptnofromemp;//这里选的是部门的编号，问题里是让选部门的名字selectdname,deptnofromdeptwheredeptno=20;//选出了编号是20的这个人所在部门的名字正确写法：selectename,dnamefromemp,deptwhereemp.deptno=dept.deptno;带连接条件等值的多表查询必须明确的指出重复字段是哪个表的例如：selectename,dname,dept.deptnofromemp,deptwhereemp.deptno=dept.deptno;注意：指定哪张表的deptno实际上对它有一个效率上的影响带连接条件非等值的多表查询例子2：求每个人的薪水值包括他的名字selectename,salfromemp;例子3：求每个人的薪水值，名字和他的薪水处于第几个级别(非等值连接)selectename,sal,gradefromemp,salgradewheresalbetweenlosalandhisaselectename,sal,gradefromemp,salgradewheresal=losalandsal=hisal;带连接条件非等值的多表查询例子4：求出他的名字，求出他所在部门的名称，求出他的薪水等级首先分析这几个数据在3张表里Selectename,dname,gradefromempe,deptd,salgradesWheree.deptno=d.deptnoande.salbetweens.losalands.hisal;带连接条件非等值的多表查询例子5：求出职位除’PRESIDENT‘以外的所有雇员的名字，部门名称，薪水等级selectename,dname,gradefromempe,deptd,salgradeswheree.deptno=d.deptnoande.sal=s.losalande.sal=s.hisalandjob'PRESIDENT';子查询子查询内嵌在Select语句中的语句，子查询可以嵌套多层，子查询操作的数据表可以是不是真实的表，由其它SQL语句得到的临时表，子查询中不能包含ORDERBY分组语句。子查询例子1：求谁挣的钱最多？错误的写法：selectename,max(sal)fromemp;正确的写法：selectename,salfromempwheresal=(selectmax(sal)fromemp);子查询例子2：求出来有哪些工资位于所有人平均工资之上selectename,salfromempwheresal(selectavg(sal)fromemp);子查询例子3：按照部门进行分组之后挣钱最多的那个人的名字，薪水,部门编号？selectename,sal,t.deptnofromempjoin(selectmax(sal)max_sal,deptnofromempgroupbydeptno)ton(emp.sal=t.max_salandemp.deptno=t.deptno);****理解子查询的关键--把它当成一张表子查询求部门中哪些人的薪水最高selectename,salfromempjoin(selectmax(sal)max_sal,deptnofromempgroupbydeptno)ton(emp.sal=t.max_salandemp.deptno=t.deptno);自连接自连接：为同一张表起不同的别名，然后当成两张表来用例子1：求这个人的名字和他经理人的名字(自连接)selecte1.ename,e2.enamefromempe1,empe2wheree1.mgr=e2.empno;常用SQL函数1.Lower()函数将字符串全部转换成小写例子1：将雇员中名字含有’A’或’a’的人员全部显示出来Selectenamefromempwhereenamelike‘%A%’orenamelike‘%a%’;Selectenamefromempwherelower(ename)like‘%a%’;常用SQL函数2.Upper()函数将字符串全部转换为大写例子2：将雇员中名字含有’A’或’a’的人员全部显示出来Selectenamefromempwhereupper(ename)like‘%A%’;常用SQL函数round()函数对参数值进行四舍五入的操作例子1：对23.652进行四舍五入操作Selectround(23.652)fromdual;24可以指定四舍五入到小数点后几位例子2：对23.652四舍五入到小数点后2位Selectround(23.652,2)fromdual;23.65常用SQL函数8.to_date()函数将特定的字符串转换成日期格式，这个函数有两个参数第一参数：自定义的日期字符串第二参数：指定这个字符串的格式例子1：将1981年3月2日中午以后入职的雇员信息取出：Select*fromempwherehiredateto_date(‘1981-03-0212:00:00’,‘YYYY-MM-DDHH12:MI:SS’);常用SQL函数To_char()有两个参数：第一个参数：需要进行转换的日期或数字第二个参数：特定的转换格式，对于数字有一下几个格式可以指定：9代表数字，如果该位没有数字则不进行显示，但对于小数点后面的部分仍会强制显示0代表一位数字，如果该位没有数字则强制显示0$显示美元符号L显示本地货币符号.显示小数点,显示千分位符号常用SQL函数例子1：selectename,to_char(sal,'$99,999.9999')salaryfromemp例子2：selectto_char(sal,‘$00,000.0000’)salaryfromempwhereename=‘ALLEN’;$01,600.0000常用SQL函数对于日期：to_char()可以指定为下面的常用格式：格式控制符含义YYYY、YY----代表4位，2位数字的年份MM-----用数字表示的月份MON----月份的缩写对中文月份来说就是全称DD-----数字表示的日常用SQL函数DY-----星期的缩写，对中文的星期来说就是全称HH24、HH12-----12小时或者24小时进制下的时间MI-----分钟数SS----秒数常用SQL函数有了这些格式，就可以把日期自定义为任何格式例子1：selectto_char(sysdate,‘YYYY-MM-DD-HH24:MI:SS’)fromdual;2008-04-04常用SQL函数nvl()函数用来处理空值，这个函数有两个参数：第一参数：字段名或表达式，如果这个参数值为null，就返回第二参数值，否则返回第一参数值例子：求每个员工每年的年收入(12个月的薪水+津贴)因为comm的值为null，想要得到正确的结果，必须讲null值转换为0Selectename,sal*12+nvl(comm,0)fromemp;常用SQL函数substr()函数例1从第一个字符开始，截取到字符结束selectsubstr(‘hello’,1,3)fromdual;例2。第三个参数可以不写，表示到字符结束。selectsustr(ename,2)fromemp;常用SQL函数mod(m,n)函数用来求M除N后的余数例1Selectmgr,mod(mgr,100)fromscott.emp;常用SQL函数.组函数包括5个函数：(1).avg():求平均值(2).max()：求最大值(3).min()：求最小值(4).sum()：求总和(5).count()：求记录的数量常用SQL函数例子1：求薪水的总和、平均值、最大值和最小值Selectsum(sal),avg(sal),max(sal),min(sal)fromemp;例子2：求emp表中记录的数量Selectcount(*)fromemp;Count()可以对单独字段使用，得到的是所有非空记录的数量常用SQL函数例子3：求comm字段中所有非空记录的数量Selectcount(comm)fromemp;Count()可以和distinct一起使用，得到所有唯一值记录的数量常用SQL函数例子4：求emp表中deptno唯一的数量Selectcount(distinctdeptno)fromemp;注意：函数名不是在所有数据库中通用总结SQL语言进行多表查询SQL语言进行子查询基本的数据库函数作业平均薪水最高的部门编号与名称？作业1.先求每个部门的平均薪水selectavg(sal),deptnofromempgroupbydeptno;2.拿出最高的值selectmax(avg_sal)from(selectavg(sal)avg_sal,deptnofromempgroupbydeptno);结果：selectdeptno,avg_salfrom(selectavg(sal)avg_sal,deptnofromempgroupbydeptno)whereavg_sal=(selectmax(avg_sal)from(selectavg(sal)avg_sal,deptnofromempgroupbydeptno))