SQL语言多表查询、分组统计、子查询、数据表的更新操作、事务处理

1、SQL 语言多表查询、分组统计、子查询、数据表的更新操作、事务处理3.1、多表查询3.1.1、基本语法但是在多表查询之前首先必须处理一个问题：例如：现在求出雇员表中的总记录数(14条记录)SELECTCOUNT(*)FROMemp;例如：现在求出部门表的总记录数(4条记录)SELECTCOUNT(*)FROMdept;所谓的多表查询就是直接在FROM语句之后加入若干张表，下面将emp和dept表进行多表查询SELECT*FROMemp,dept;以上确实完成了两张表的联合查询，但是查询出来的结果是56条记录。部门表的记录总数*雇员表的记录总数=56条记录。那么这样的结果在数据库中就称为笛卡尔积

2、。对于这样的结果明显不是最终查询者需要返回的结果，应该想办法去掉笛卡尔积。所以如果要使用多表查询，则必须按照以下的语句形式进行编写：SELECT字段FROM表1,表2WHERE将两张表的关联字段进行比较，去掉笛卡尔积以emp和dept表为例1、雇员表结构：No.字段名称字段类型字段作用EMPNONUMBER(4)表示的是雇员编号，长度为四位的整数ENAMEVARCHAR2(10)雇员的姓名，使用字符串表示，字符串的长度最大为10JOBVARCHAR2(9)工作，字符串表示，最大长度为9MGRNUMBER(4)雇员的直接上级领导编号HIREDATEDATE雇佣日期SALNUMBER(7,2)工资

3、，工资长度一共是7位，其中整数占5位，小数占2位COMMNUMBER(7,2)奖金(佣金)DEPTNONUMBER(2)部门编号2、部门表结构：No.字段名称字段类型字段作用DEPTNONUMBER(2)雇员编号DNAMEVARCHAR2(14)部门名称LOCVARCHAR2(13)部门位置两张表中都存在deptno字段，一般在数据库建表的时候都会把关联字段的名称统一。例如：使用关联字段消除掉之前多表查询的迪卡尔积。SELECT*FROMemp,deptWHEREemp.deptno=dept.deptno;但是如果一直按照以上的格式编写的话，对于表名称太长的情况时，如果在访问字段前还是使用表

4、名称的形式，会很麻烦。所以一般可以为一张表起一个别名。修改以上的范例：SELECT*FROMempe,deptdWHEREe.deptno=d.deptno;例如：要求查询出雇员的编号、雇员的姓名、工资、部门的名称及位置SELECTe.empno,e.ename,e.sal,d.dname,d.locFROMempe,deptdWHEREe.deptno=d.deptno;例如：要求查询出每个雇员的姓名、工作、雇员工资、领导姓名、领导工资?如果要找到领导信息，则肯定需要emp表?如果要找到雇员信息，则肯定需要emp表?消除笛卡尔积的条件：雇员的管理者的编号=雇员中的雇员编号SELECTe.en

5、ame雇员女4名，e.job雇员工作，e.sal雇员工资,m.ename领导姓名,m.sal领导工资FROMempe,empmWHEREe.mgr=m.empno;可以发现，本次查询是本表关联本表，那么这样的查询称为自身关联查询。例如：要求查询出每个雇员的姓名、工作、雇员工资、部门名称、领导姓名、领导工资?如果要找到领导信息，则肯定需要emp表?如果要找到雇员信息，则肯定需要emp表?如果要找到部门信息，则肯定需要dept表SELECTe.ename雇员女4名,e.job雇员工作，e.sal雇员工资,m.ename领导姓名,m.sal领导工资,d.dname部门名称FROMempe,empm,

6、deptdWHEREe.mgr=m.empnoANDe.deptno=d.deptno;注意：在以上的查询中性能如何？思考：现在要求查询出每个雇员的姓名、工资、部门名称，雇员的工资及在公司的工资等级、领导的姓名、领导的工资及工资在公司的工资等级。salgrade:工资等级表No.字段名称字段类型字段作用ered_e848cd87-bb17-4aad-ae8d-c3b2f80b4039$GRADENUMBER等级编号ered_e848cd87-bb17-4aad-ae8d-c3b2f80b4039$LOSALNUMBER此等级的最彳氐工资ered_e848cd87-bb17-4aad-ae8d-

7、c3b2f80b4039$HISALNUMBER此等级的最高工资通过sal指定losal和hisal的范围来去除笛卡尔积?部门表?领导表：empSELECTe.ename,e.sal,d.dname,e.sal,s.grade,m.ename,m.sal,sm.gradeFROMempe,deptd,empm,salgrades,salgradesmWHEREe.salBETWEENs.losalANDs.hisalANDe.deptno=d.deptnoANDm.salBETWEENsm.losalANDsm.hisalANDe.mgr=m.empno;进一步扩展：在查询完的工资等级进行显示

8、的修改，修改要求如下：“E等工资”“D等工资”“C等工资”“B等工资”“A等工资”DECODE函数SELECTe.ename,e.sal,d.dname,e.sal,DECODE（s.grade,1,E等工资,2,D等工资,3,C等工资,4,B等工资,5,A等工资），m.ename,m.sal,DECODE（sm.grade,1,E等工资,2,D等工资,3,C等工资,4,B等工资,5,A等工资）FROMempe,deptd,empm,salgrades,salgradesmWHEREe.salBETWEENs.losalANDs.hisalANDe.deptno=d.deptnoANDm.sa

9、lBETWEENsm.losalANDsm.hisalANDe.mgr=m.empno;ered_e848cd87-bb17-4aad-ae8d-c3b2f80b4039$.1.2、SQL:1999语法（了解）在SQL语句中提供了另外一套对与表关联查询的支持语法：SELECTtable1.column,table2.columnFROMtable1CROSSJOINtable2|NATURALJOINtable2|JOINtable2USING(column_name)|JOINtable2ON(table1.column_name=table2.column_name)|LEFT|RIGHT

10、|FULLOUTERJOINtable2ON(table1.column_name=table2.column_name);交叉连接(CROSSJOIN):专门产生笛卡尔积SELECT*FROMempCROSSJOINdept;自然连接(NATUALJOIN):自动进行关联字段的匹配SELECT*FROMempNATURALJOINdept;ON子句；由用户自己指定关联的条件SELECT*FROMempJOINdeptON(emp.deptno=dept.deptno);USING子句：直接指定关联的条件SELECT*FROMempJOINdeptUSING(deptno);ered_e848

11、cd87-bb17-4aad-ae8d-c3b2f80b4039$.1.3、左、右连接现在先查询出全部雇员和部门的信息SELECT*FROMempe,deptdWHEREe.deptno=d.deptno;查询结果：以上的结果中显示了三个部门，但是实际上部门有四个select*fromdept;那么为什么之前列出的所有部门中没有40部门，因为在雇员表中没有一个雇员属于40部门。因为匹配的结果都是以雇员为主。以等号左边为准，实际上以上的多表查询，可以直接通过以下的形式表现：SELECT*FROMempe,deptdWHEREe.deptno=d.deptno(+);也就是说，现在的代码以等号的左

12、边为准，所以此连接称为左连接，如果现在以等号的右边为准呢？那么就称为右连接：SELECT*FROMempe,deptdWHEREe.deptno(+)=d.deptno;从程序运行结果可以发现，40部门出现了，那么意味着现在的匹配条件以等号右边为准，所以是右连接。可以发现一个规律:?(+)在等号的左边属于右连接?(+)在等号的右边属于左连接同样SQL:1999语法也对左、右连接有所支持(了解)例如：实现左连接SELECT*FROMempLEFTOUTERJOINdeptON(emp.deptno=dept.deptno);例如：实现右连接SELECT*FROMempRIGHTOUTERJOIN

13、deptON(emp.deptno=dept.deptno);ered_e848cd87-bb17-4aad-ae8d-c3b2f80b4039$.2.2、分组在SQL语句中如果要想实现对数据的分组统计，则必须使用GROUPBY子句，此时，完整的SQL语法如下：SELECTcolumn|*FROMtablelals1,table2als2WHEREconditionsGROUPBYcolumnORDERBYcolumn例如：求出每个部门的雇员数量?应该按照部门编号进行分组SELECTdeptno,COUNT(*)FROMempGROUPBYdeptno;例如：求出每个部门的平均工资SELECT

14、deptno,AVG(sal)FROMempGROUPBYdeptno;以上已经完成了分组，但是对于分组统计中，是不能出现分组条件之外的字段。例如：有以下一段SQL语句SELECTdeptno,AVG(sal),enameFROMempGROUPBYdeptno;一旦执行之后出现以下的错误提示：SELECTdeptno,AVG(sal),ename第1行出现错误：ORA-00979:不是GROUPBY表达式对于分组函数而言，可以单独使用，如果要连同其他字段一起查询，此字段必须是分组的字段，而且此语句之中必须存在groupby子句：SELECTdeptno,COUNT(*)FROMemp;出现以

15、下错误：SELECTdeptno,COUNT(*)第1行出现错误：ORA-00937:不是单组分组函数而如果只写一个分组函数，则不会有任何的问题。例如：要求显示出平均工资大于2000的部门编号及平均工资SELECTdeptno,AVG(sal)FROMempWHEREAVG(sal)2000GROUPBYdeptno;语句执行后出现以下的错误：WHEREAVG(sal)2000第3行出现错误：在WHERE语句之中是不能出现分组函数的。只要是分组条件且此条件中要使用分组函数，就必须在HAVING子句之中编写，与WHERE子句的功能一样，只是HAVING必须写在GROUP之后，没有GROUPBY绝

16、对不能出现HAVING。SELECTdeptno,AVG(sal)FROMempGROUPBYdeptnoHAVINGAVG(sal)2000;完整的语句格式：SELECTcolumn|*FROMtable1als1,table2als2WHEREconditionsGROUPBYcolumnHAVING分组条件ORDERBYcolumn例如：显示非销售人员的工作名称以及从事同一工作雇员的月工资的总和，并且要满足从事相同工作雇员的月工资合计大于5000,输出结果按月工资的合计升序排列。1、显示全部非销售人员的信息(限定条件，需要在WHERE子句中编写)SELECT*FROMempWHEREjo

17、bSALESMAN;2、按雇员的工作分组，分组之后可以求出工资的总和SELECTjob,SUM(sal)FROMempWHEREjobSALESMANGROUPBYjob;3、对分组的条件进行过滤，求出月工资的总和大于5000SELECTjob,SUM(sal)FROMempWHEREjobSALESMANGROUPBYjobHAVINGSUM(sal)5000;4、按照工资的总和进行排序SELECTjob,SUM(sal)suFROMempWHEREjobSALESMANGROUPBYjobHAVINGSUM(sal)5000ORDERBYsu;注意：分组函数本身是允许嵌套的，但是，嵌套之后

18、是不能出现分组条件的。例如：求出平均工资最高的部门A、错误的代码SELECTdeptno,MAX(AVG(sal)FROMempGROUPBYdeptno;实际上以上的代码使用了分组函数的嵌套。B、正确的代码SELECTMAX(AVG(sal)FROMempGROUPBYdeptno;原则：当列中存在重复的内容时，才可以进行分组。3.3、子查询例如：要求查询出工资比7566雇员的工资要高的全部雇员信息确定出7566雇员的工资SELECTsalFROMempWHEREempno=7566;求出工资大于7566的雇员SELECT*FROMempWHEREsal(SELECTsalFROMempWH

19、EREempno=7566);需要注意的是，子查询可以在任意的位置上编写：SELECT、FROM、WHERE、HAVING子查询语法：SELECT字段,SELECTcolumn_listFROM表别名FROM表名称,SELECTcolumn_listFROM表别名WHERE条件,SELECTcolumn_listFROM表别名GROUPBY分组条件，SELECTcolumn_listFROM表别名HAVING条件,SELECTcolumn_listFROM表别名对于自查询而言又分为三类：?单列子查询：返回的结果是一行一列?单行子查询：一行多列，例如：一条完整的记录?多行子查询：返回多条记录例如

20、：要求查询出工资比7654高，同时又与7788从事同一个工作的雇员信息?查询出7654雇员的工资SELECTsalFROMempWHEREempno=7654;?查出7788的工作SELECTjobFROMempWHEREempno=7788;将以上的两条记录作为查询的条件SELECT*FROMempWHEREsal(SELECTsalFROMempWHEREempno=7654)ANDjob=(SELECTjobFROMempWHEREempno=7788);例如：要求查询出工资最低的雇员的姓名、工作、工资求出最低的工作SELECTMIN(sal)FROMemp;以此为条件进行查询SELEC

21、T*FROMempWHEREsal=(SELECTMIN(sal)FROMemp);例如：查询出工资高于公司平均工资的雇员信息SELECT*FROMempWHEREsal(SELECTAVG(sal)FROMemp);思考：要求查询出：部门名称，部门的雇员数，部门的平均工资，部门中最低收入的雇员的姓名。部门中最低收入、雇员数、平均工资，按部门分组SELECTdeptno,MIN(sal)fROMempGROUPBYdeptno;?多表关联及子查询SELECTd.dname,e.ename,dm.avg,dm.min,dm.countFROMdeptd,empe,(SELECTdeptno,AV

22、G(sal)avg,MIN(sal)min,COUNT(empno)countFROMempGROUPBYdeptno)dmWHEREd.deptno=e.deptnoANDd.deptno=dm.deptnoANDe.sal=dm.min;由此可见，子查询可以在任意的位置上出现，如果在FROM语句之后，实际上此查询就是表示出了一张临时表，临时表的访问要有别名。例如：查询平均工资最底的工作名称及其平均工资SELECTjob,AVG(sal)FROMempGROUPBYjobHAVINGAVG(sal)=(SELECTMIN(AVG(sal)FROMempGROUPBYjob);在子查询中还提供

23、了以下的比较运算符：?IN:指定一个范围的内容?ANY?ALLIN操作符例如：要求查询出各个部门最低工资的雇员信息?每个部门的最低工资是多个，之后根据工资查询的时候实际上就是应该指定出一个具体的范围。SELECT*FROMempWHEREsalIN(SELECTMIN(sal)FROMempGROUPBYdeptno);ANY操作符：=ANY、ANY、ANY?=ANY:功能与IN一致例如：以上的程序修改为二ANYSELECT*FROMempWHEREsal=ANY(SELECTMIN(sal)FROMempGROUPBYdeptno);?ANY:比最大的值要小SELECT*FROMempWHE

24、REsalANY:比最小的值要大SELECT*FROMempWHEREsalANY(SELECTMIN(sal)FROMempGROUPBYdeptno);实际上ANY是要与里面的每一个内容进行比较的。3、ALL操作符?ALL:比最小的值要小SELECT*FROMempWHEREsalALL:比最大的值要大SELECT*FROMempWHEREsalALL(SELECTMIN(sal)FROMempGROUPBYdeptno);以上实际都是针对于多行子查询的应用。多列子查询：一次性返回多个列，如果要想比较，则必须多个列一起比较SELECT*FROMempWHERE(sal,NVL(comm,0

25、)IN(SELECTsal,NVL(comm,0)FROMempWHEREdeptno=20);4、查询练习?求出那个部门的雇员数量SELECTdeptno,COUNT(empno)FROMempGROUPBYdeptno;?求出数量大于1的所有部门SELECTd.*,dc.cFROMdeptd,(SELECTdeptno,COUNT(empno)cFROMempGROUPBYdeptno)dcWHEREd.deptno=dc.deptno;2、列出薪金比“SMITH多的所有员工。SELECT*FROMempWHEREsal(SELECTsalFROMempWHEREename=SMITH);

26、3、列出所有员工的姓名及其直接上级的姓名。SELECTe.ename,m.enameFROMempe,empmWHEREe.mgr=m.empno;SELECTename,(SELECTenameFROMempWHEREempno=e.mgr)FROMempe;4、列出受雇日期早于其直接上级的所有员工的编号，姓名，部门名称。?列出受雇日期早于其直接上级的所有员工的编号，姓名SELECTe.empno,e.enameFROMempe,empmWHEREe.mgr=m.empnoANDe.hiredatem.hiredate;?为其加入部门SELECTe.empno,e.ename,d.dname

27、FROMempe,empm,deptdWHEREe.mgr=m.empnoANDe.hiredate1500;?雇员的人数SELECTjob,COUNT(*)FROMempWHEREjobIN(SELECTjobFROMempGROUPBYjobHAVINGMIN(sal)1500)GROUPBYjob;8、列出在部门“SALES”(销售部)工作的员工的姓名，假定不知道销售部的部门编号。SELECT*FROMempWHEREdeptno=(SELECTdeptnoFROMdeptWHEREdnameLIKE%SALES%);9、列出薪金高于公司平均薪金的所有员工，所在部门，上级领导，公司的工资

28、等级。?求出工资高于平均工资的雇员姓名，部门编号SELECTe.ename,e.deptnoFROMempeWHEREsal(SELECTAVG(sal)FROMemp);?求出部门的名称及上级领导信息SELECTe.ename,e.deptno,d.dname,m.enameFROMempe,deptd,empmWHEREe.sal(SELECTAVG(sal)FROMemp)ANDd.deptno=e.deptnoANDe.mgr=m.empno;?工资的等级SELECTe.ename,e.deptno,d.dname,m.ename,s.gradeFROMempe,deptd,empm,

29、salgradesWHEREe.sal(SELECTAVG(sal)FROMemp)ANDd.deptno=e.deptnoANDe.mgr=m.empnoANDe.salBETWEENs.losalANDs.hisal;10、列出与“SCOTT从事相同工作的所有员工及部门名称。SELECTe.ename,d.dnameFROMempe,deptdWHEREe.job=(SELECTjobFROMempWHEREename=SCOTT)ANDe.deptno=d.deptnoANDe.enameSCOTT;11、列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。?多列自查询，IN操作SELECT*FROMempWHEREsalIN(SELECTsalFROMempWHEREdeptno=30);12、列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金、部门名称。?30部门的所有雇员薪金SELECTsalFROMempWHEREdeptno=30;?列出大于：ALLSELECTe.ename,e.sal,d.dnameFROMempe,deptdWHEREe.salALL(SELECTsalFR