2022年SQL语言多表查询 .pdf

《2022年SQL语言多表查询 .pdf》由会员分享，可在线阅读，更多相关《2022年SQL语言多表查询 .pdf（13页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、1 SQL语言多表查询、分组统计、子查询、数据表的更新操作、事务处理2010-11-26 09:04 SQL语言多表查询、分组统计、子查询、数据表的更新操作、事务处理3.1 、多表查询3.1.1 、基本语法但是在多表查询之前首先必须处理一个问题：例如：现在求出雇员表中的总记录数（14条记录）SELECT COUNT(*) FROM emp ; 例如：现在求出部门表的总记录数（4 条记录）SELECT COUNT(*) FROM dept ; 所谓的多表查询就是直接在FROM 语句之后加入若干张表，下面将emp和 dept表进行多表查询SELECT * FROM emp,dept ; 以上确实完

2、成了两张表的联合查询，但是查询出来的结果是56 条记录。部门表的记录总数 * 雇员表的记录总数 = 56 条记录。那么这样的结果在数据库中就称为笛卡尔积。对于这样的结果明显不是最终查询者需要返回的结果，应该想办法去掉笛卡尔积。所以如果要使用多表查询，则必须按照以下的语句形式进行编写：SELECT 字段FROM 表 1, 表 2 WHERE 将两张表的关联字段进行比较，去掉笛卡尔积以 emp和 dept 表为例1、 雇员表结构：No. 字段名称字段类型字段作用1 EMPNO NUMBER(4) 表示的是雇员编号，长度为四位的整数2 ENAME VARCHAR2(10) 雇员的姓名，使用字符串表示

3、，字符串的长度最大为10 3 JOB VARCHAR2(9) 工作，字符串表示，最大长度为9 4 MGR NUMBER(4) 雇员的直接上级领导编号5 HIREDATE DATE 雇佣日期6 SAL NUMBER(7,2) 工资，工资长度一共是7 位，其中整数占 5 位，小数占 2位7 COMM NUMBER(7,2) 奖金（佣金）8 DEPTNO NUMBER(2) 部门编号2、 部门表结构：No. 字段名称字段类型字段作用1 DEPTNO NUMBER(2) 雇员编号2 DNAME VARCHAR2(14) 部门名称3 LOC VARCHAR2(13) 部门位置两张表中都存在 deptno

4、 字段，一般在数据库建表的时候都会把关联字段的名称统一。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 1 页，共 13 页 - - - - - - - - - 2 例如：使用关联字段消除掉之前多表查询的迪卡尔积。SELECT * FROM emp,dept WHERE emp.deptno=dept.deptno ; 但是如果一直按照以上的格式编写的话，对于表名称太长的情况时， 如果在访问字段前还是使用表名称的形式，会很麻烦。所以一般可以为一张表起一个别名。修改以上的范例：SELE

5、CT * FROM emp e,dept d WHERE e.deptno=d.deptno ; 例如：要求查询出雇员的编号、雇员的姓名、工资、部门的名称及位置SELECT e.empno,e.ename,e.sal,d.dname,d.loc FROM emp e,dept d WHERE e.deptno=d.deptno ; 例如：要求查询出每个雇员的姓名、工作、雇员工资、领导姓名、领导工资? 如果要找到领导信息，则肯定需要emp表? 如果要找到雇员信息，则肯定需要emp表? 消除笛卡尔积的条件：雇员的管理者的编号 = 雇员中的雇员编号SELECT e.ename 雇员姓名 ,e.job

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

7、OM emp e,emp m,dept d WHERE e.mgr=m.empno AND e.deptno=d.deptno; 注意：在以上的查询中性能如何？思考：现在要求查询出每个雇员的姓名、工资、部门名称， 雇员的工资及在公司的工资等级、领导的姓名、领导的工资及工资在公司的工资等级。salgrade ：工资等级表名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 2 页，共 13 页 - - - - - - - - - 3 No. 字段名称字段类型字段作用1 GRADE NUMBE

8、R 等级编号2 LOSAL NUMBER 此等级的最低工资3 HISAL NUMBER 此等级的最高工资通过 sal 指定 losal和 hisal的范围来去除笛卡尔积? 部门表? 领导表： emp SELECT e.ename,e.sal,d.dname,e.sal,s.grade,m.ename,m.sal,sm.grade FROM emp e,dept d,emp m,salgrade s,salgrade sm WHERE e.sal BETWEEN s.losal AND s.hisal AND e.deptno=d.deptno AND m.sal BETWEEN sm.losa

9、l AND sm.hisal AND e.mgr=m.empno ; 进一步扩展：在查询完的工资等级进行显示的修改，修改要求如下：? 1 ：“E 等工资”? 2 ：“D 等工资”? 3 ：“C 等工资”? 4 ：“B 等工资”? 5 ：“A 等工资”需要使用 DECODE 函数SELECT e.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等工资

10、 ) FROM emp e,dept d,emp m,salgrade s,salgrade sm WHERE e.sal BETWEEN s.losal AND s.hisal AND e.deptno=d.deptno AND m.sal BETWEEN sm.losal AND sm.hisal AND e.mgr=m.empno ; 3.1.2 、SQL:1999语法（了解）名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 3 页，共 13 页 - - - - - - - -

11、- 4 在 SQL语句中提供了另外一套对与表关联查询的支持语法：SELECT table1.column,table2.column FROM table1 CROSS JOIN table2| NATURAL JOIN table2| JOIN table2 USING(column_name)| JOIN table2 ON(table1.column_name=table2.column_name)| LEFT|RIGHT|FULL OUTER JOIN table2 ON(table1.column_name=table2.column_name); 交叉连接（ CROSS JOIN

12、）：专门产生笛卡尔积SELECT * FROM emp CROSS JOIN dept ; 自然连接（ NATUAL JOIN ）：自动进行关联字段的匹配SELECT * FROM emp NATURAL JOIN dept ; ON子句；由用户自己指定关联的条件SELECT * FROM emp JOIN dept ON(emp.deptno=dept.deptno) ; USING 子句：直接指定关联的条件SELECT * FROM emp JOIN dept USING(deptno) ; 3.1.3 、左、右连接现在先查询出全部雇员和部门的信息SELECT * FROM emp e,d

13、ept d WHERE e.deptno = d.deptno ; 查询结果：以上的结果中显示了三个部门，但是实际上部门有四个select * from dept ; 那么为什么之前列出的所有部门中没有40 部门，因为在雇员表中没有一个雇员属于 40 部门。因为匹配的结果都是以雇员为主。 以等号左边为准， 实际上以上的多表查询， 可以直接通过以下的形式表现：SELECT * FROM emp e,dept d WHERE e.deptno=d.deptno(+) ; 也就是说， 现在的代码以等号的左边为准，所以此连接称为左连接， 如果现在以等号的右边为准呢？那么就称为右连接：名师资料总结 -

14、- -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 4 页，共 13 页 - - - - - - - - - 5 SELECT * FROM emp e,dept d WHERE e.deptno(+)=d.deptno ; 从程序运行结果可以发现，40 部门出现了，那么意味着现在的匹配条件以等号右边为准，所以是右连接。可以发现一个规律：? (+) 在等号的左边属于右连接? (+) 在等号的右边属于左连接同样 SQL:1999语法也对左、右连接有所支持（了解）例如：实现左连接SELECT * FROM e

15、mp LEFT OUTER JOIN dept ON (emp.deptno=dept.deptno) ; 例如：实现右连接SELECT * FROM emp RIGHT OUTER JOIN dept ON (emp.deptno=dept.deptno) ; 3.2.2 、分组在 SQL语句中如果要想实现对数据的分组统计，则必须使用GROUP BY 子句，此时，完整的 SQL语法如下：SELECT column | * FROM table1 als1,table2 als2 WHERE conditions GROUP BY column ORDER BY column 例如：求出每个部

16、门的雇员数量? 应该按照部门编号进行分组SELECT deptno,COUNT(*) FROM emp GROUP BY deptno ; 例如：求出每个部门的平均工资SELECT deptno,AVG(sal) FROM emp GROUP BY deptno ; 以上已经完成了分组，但是对于分组统计中，是不能出现分组条件之外的字段。例如：有以下一段SQL语句SELECT deptno,AVG(sal),ename FROM emp GROUP BY deptno ; 一旦执行之后出现以下的错误提示：名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - -

17、 - - - - - 名师精心整理 - - - - - - - 第 5 页，共 13 页 - - - - - - - - - 6 SELECT deptno,AVG(sal),ename 第 1 行出现错误 : ORA-00979: 不是 GROUP BY 表达式对于分组函数而言， 可以单独使用， 如果要连同其他字段一起查询，此字段必须是分组的字段，而且此语句之中必须存在 group by 子句：SELECT deptno,COUNT(*) FROM emp ; 出现以下错误：SELECT deptno,COUNT(*) 第 1 行出现错误 : ORA-00937: 不是单组分组函数而如果只写

18、一个分组函数，则不会有任何的问题。例如：要求显示出平均工资大于2000 的部门编号及平均工资SELECT deptno,AVG(sal) FROM emp WHERE AVG(sal)2000 GROUP BY deptno ; 语句执行后出现以下的错误：WHERE AVG(sal)2000 第 3 行出现错误 : 在 WHERE 语句之中是不能出现分组函数的。 只要是分组条件且此条件中要使用分组函数，就必须在HAVING 子句之中编写，与WHERE 子句的功能一样，只是HAVING 必须写在 GROUP 之后，没有 GROUP BY 绝对不能出现HAVING 。SELECT deptno,A

19、VG(sal) FROM emp GROUP BY deptno HAVING AVG(sal)2000 ; 完整的语句格式：SELECT column | * FROM table1 als1,table2 als2 WHERE conditions GROUP BY column HAVING 分组条件 ORDER BY column 例如：显示非销售人员的工作名称以及从事同一工作雇员的月工资的总和，并且名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 6 页，共 13 页 - -

20、 - - - - - - - 7 要满足从事相同工作雇员的月工资合计大于5000，输出结果按月工资的合计升序排列。1、 显示全部非销售人员的信息（限定条件，需要在WHERE 子句中编写）SELECT * FROM emp WHERE jobSALESMAN ; 2、 按雇员的工作分组，分组之后可以求出工资的总和SELECT job,SUM(sal) FROM emp WHERE jobSALESMAN GROUP BY job; 3、 对分组的条件进行过滤，求出月工资的总和大于5000 SELECT job,SUM(sal) FROM emp WHERE jobSALESMAN GROUP B

21、Y job HAVING SUM(sal)5000; 4、 按照工资的总和进行排序SELECT job,SUM(sal) suF ROM emp WHERE jobSALESMAN GROUP BY job HAVING SUM(sal)5000 ORDER BY su; 注意：分组函数本身是允许嵌套的，但是，嵌套之后是不能出现分组条件的。例如：求出平均工资最高的部门A、 错误的代码SELECT deptno,MAX(AVG(sal) FROM emp GROUP BY deptno ; 实际上以上的代码使用了分组函数的嵌套。B、 正确的代码SELECT MAX(AVG(sal) FROM e

22、mp GROUP BY deptno ; 原则：当列中存在重复的内容时，才可以进行分组。3.3 、子查询例如：要求查询出工资比7566 雇员的工资要高的全部雇员信息1、 确定出 7566 雇员的工资SELECT sal FROM emp WHERE empno=7566 ; 2、 求出工资大于 7566 的雇员SELECT * FROM emp WHERE sal(SELECT sal FROM emp WHERE empno=7566) ; 需要注意的是， 子查询可以在任意的位置上编写：SELECT 、FROM、WHERE、HAVING 子查询语法：SELECT 字段,SELECT colu

23、mn_list FROM 表 别名名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 7 页，共 13 页 - - - - - - - - - 8 FROM 表名称 ,SELECT column_list FROM 表 别名WHERE 条件,SELECT column_list FROM 表 别名GROUP BY 分组条件 ,SELECT column_list FROM 表 别名HAVING 条件,SELECT column_list FROM 表 别名对于自查询而言又分为三类：? 单

24、列子查询：返回的结果是一行一列? 单行子查询：一行多列，例如：一条完整的记录? 多行子查询：返回多条记录例如：要求查询出工资比7654 高，同时又与 7788 从事同一个工作的雇员信息? 查询出 7654 雇员的工资SELECT sal FROM emp WHERE empno=7654 ; ? 查出 7788 的工作SELECT job FROM emp WHERE empno=7788 ; 将以上的两条记录作为查询的条件SELECT * FROM emp WHERE sal(SELECT sal FROM emp WHERE empno=7654) AND job=(SELECT job

25、FROM emp WHERE empno=7788) ; 例如：要求查询出工资最低的雇员的姓名、工作、工资求出最低的工作SELECT MIN(sal) FROM emp ; 以此为条件进行查询SELECT * FROM emp WHERE sal=(SELECT MIN(sal) FROM emp) ; 例如：查询出工资高于公司平均工资的雇员信息SELECT * FROM emp WHERE sal(SELECT AVG(sal) FROM emp) ; 思考：要求查询出：部门名称，部门的雇员数，部门的平均工资，部门中最低收入的雇员的姓名。部门中最低收入、雇员数、平均工资，按部门分组SELEC

26、T deptno,MIN(sal) fROM emp GROUP BY deptno ; ? 多表关联及子查询SELECT d.dname,e.ename,dm.avg,dm.min,dm.count FROM dept d,emp e,(SELECT deptno,AVG(sal) avg,MIN(sal) min,COUNT(empno) count FROM emp GROUP BY deptno) dm WHERE d.deptno=e.deptno AND d.deptno=dm.deptno AND e.sal=dm.min ; 由此可见，子查询可以在任意的位置上出现，如果在FRO

27、M 语句之后，实际上此查询就是表示出了一张临时表，临时表的访问要有别名。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 8 页，共 13 页 - - - - - - - - - 9 例如：查询平均工资最底的工作名称及其平均工资SELECT job,AVG(sal) FROM emp GROUP BY job HAVING AVG(sal)=(SELECT MIN(AVG(sal) FROM emp GROUP BY job) ; 在子查询中还提供了以下的比较运算符：? IN ：指定一

28、个范围的内容? ANY ? ALL 1、 IN 操作符例如：要求查询出各个部门最低工资的雇员信息? 每个部门的最低工资是多个， 之后根据工资查询的时候实际上就是应该指定出一个具体的范围。SELECT * FROM emp WHERE sal IN(SELECT MIN(sal) FROM emp GROUP BY deptno) ; 2、 ANY操作符： =ANY 、ANY 、ANY ? =ANY ：功能与 IN 一致例如：以上的程序修改为=ANY SELECT * FROM emp WHERE sal=ANY(SELECT MIN(sal) FROM emp GROUP BY deptno)

29、 ; ? ANY ：比最大的值要小SELECT * FROM emp WHERE salANY ：比最小的值要大SELECT * FROM emp WHERE salANY(SELECT MIN(sal) FROM emp GROUP BY deptno) ; 实际上 ANY是要与里面的每一个内容进行比较的。3、 ALL 操作符? ALL：比最小的值要小SELECT * FROM emp WHERE salALL：比最大的值要大SELECT * FROM emp WHERE salALL(SELECT MIN(sal) FROM emp GROUP BY deptno) ; 以上实际都是针对于

30、多行子查询的应用。多列子查询：一次性返回多个列，如果要想比较，则必须多个列一起比较SELECT * FROM emp WHERE (sal,NVL(comm,0) IN (SELECT sal,NVL(comm,0) FROM emp WHERE deptno=20) ; 4、查询练习? 求出那个部门的雇员数量名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 9 页，共 13 页 - - - - - - - - - 10 SELECT deptno,COUNT(empno) FROM

31、emp GROUP BY deptno ; ? 求出数量大于 1 的所有部门SELECT d.*,dc.c FROM dept d,(SELECT deptno,COUNT(empno) c FROM emp GROUP BY deptno) dc WHERE d.deptno=dc.deptno; 2、 列出薪金比“ SMITH ”多的所有员工。SELECT * FROM emp WHERE sal(SELECT sal FROM emp WHERE ename=SMITH) ; 3、 列出所有员工的姓名及其直接上级的姓名。SELECT e.ename,m.ename FROM emp e,

32、emp m WHERE e.mgr=m.empno ; SELECT ename, (SELECT ename FROM emp WHERE empno=e.mgr) FROM emp e; 4、 列出受雇日期早于其直接上级的所有员工的编号，姓名，部门名称。? 列出受雇日期早于其直接上级的所有员工的编号，姓名SELECT e.empno,e.ename FROM emp e,emp m WHERE e.mgr=m.empno AND e.hiredatem.hiredate ; ? 为其加入部门SELECT e.empno,e.ename,d.dname FROM emp e,emp m,de

33、pt d WHERE e.mgr=m.empno AND e.hiredate1500 ; ? 雇员的人数SELECT job,COUNT(*) FROM emp WHERE job IN (SELECT job FROM emp GROUP BY job HAVING MIN(sal)1500) GROUP BY job ; 8、 列出在部门“ SALES ”（销售部）工作的员工的姓名，假定不知道销售部的部门编号。SELECT * FROM emp WHERE deptno=(SELECT deptno FROM dept WHERE dname LIKE %SALES%) ; 9、 列出薪

34、金高于公司平均薪金的所有员工，所在部门，上级领导，公司的工资等级。? 求出工资高于平均工资的雇员姓名，部门编号SELECT e.ename,e.deptno FROM emp e WHERE sal(SELECT AVG(sal) FROM emp) ; ? 求出部门的名称及上级领导信息SELECT e.ename,e.deptno,d.dname,m.ename FROM emp e,dept d,emp m WHERE e.sal(SELECT AVG(sal) FROM emp) AND d.deptno=e.deptno AND e.mgr=m.empno ; ? 工资的等级SELEC

35、T e.ename,e.deptno,d.dname,m.ename,s.grade FROM emp e,dept d,emp m,salgrade s WHERE e.sal(SELECT AVG(sal) FROM emp) AND d.deptno=e.deptno AND e.mgr=m.empno AND e.sal BETWEEN s.losal AND s.hisal ; 10、列出与“ SCOTT ”从事相同工作的所有员工及部门名称。SELECT e.ename,d.dname FROM emp e,dept d WHERE e.job=(SELECT job FROM em

36、p WHERE ename=SCOTT) AND e.deptno=d.deptno AND e.enameSCOTT ; 11、列出薪金等于部门30 中员工的薪金的所有员工的姓名和薪金。? 多列自查询， IN 操作名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 11 页，共 13 页 - - - - - - - - - 12 SELECT * FROM emp WHERE sal IN (SELECT sal FROM emp WHERE deptno=30) ; 12、列出薪金高

37、于在部门30 工作的所有员工的薪金的员工姓名和薪金、部门名称。? 30 部门的所有雇员薪金SELECT sal FROM emp WHERE deptno=30 ; ? 列出大于： ALL SELECT e.ename,e.sal,d.dname FROM emp e,dept d WHERE e.salALL(SELECT sal FROM emp WHERE deptno=30) AND e.deptno=d.deptno ; 13、列出在每个部门工作的员工数量、平均工资和平均服务期限。SELECT COUNT(empno),AVG(sal),ROUND(AVG(MONTHS_BETWEE

38、N(sysdate,hiredate)/12) FROM emp GROUP BY deptno ; 14、列出所有员工的姓名、部门名称和工资。SELECT e.ename,d.dname,e.sal FROM emp e.dept d WHERE e.deptno=d.deptno 15、列出所有部门的详细信息和部门人数。SELECT d.deptno,d.dname,d.loc,dc.c FROM dept d,(SELECT deptno,COUNT(*) c FROM emp GROUP BY deptno) dc WHERE d.deptno=dc.deptno ; 16、列出各种工

39、作的最低工资及从事此工作的雇员姓名。SELECT e.ename,es.job,es.sal FROM emp e,(SELECT job,MIN(sal) sal FROM emp GROUP BY job) es WHERE e.job=es.job AND e.sal=es.sal ; 17、列出各个部门的 MANAGER（经理）的最低薪金。SELECT MIN(sal) FROM emp WHERE job=MANAGER GROUP BY job ; 18、列出所有员工的年工资，按年薪从低到高排序。SELECT (sal+NVL(comm.,0)*12 income FROM emp

40、 ORDER BY income DESC 19、查出某个员工的上级主管，并要求出这些主管中的薪水超过3000 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 12 页，共 13 页 - - - - - - - - - 13 SELECT * FROM emp e,emp m WHERE e.mgr=m.empno AND m.sal3000 ; 20、求出部门名称中，带 S字符的部门员工的、工资合计、部门人数SELECT d.deptno,NVL(SUM(sal),0),COUNT(empno) FROM emp e,dept d WHERE e.deptno(+)=d.deptno AND d.dname LIKE %S% GROUP BY d.deptno ; 21、给任职日期超过 10 年的人加薪 10% UPDATE emp sal=sal*1.1 WHERE MONTHS_BETWEEN(sysdate,hiredate)/1210 ; 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 13 页，共 13 页 - - - - - - - - -