A04_多表关联查询副本

1、复杂查询课程代码：课程代码：AMPC0103V20课程介绍课程介绍v目的：掌握复杂SQL查询操作技术v内容：多表连接查询、子查询、TOP-N分析、分页查询、查询结果集合操作、层次查询、查询优化v重点：连接查询v难点：子查询、层次查询复杂查询复杂查询v连接查询vSQL92连接语法v子查询v查询结果集合操作v层次查询vOracle查询优化一一. . 连接查询连接查询 连接查询概述 等值连接 非等值连接 外连接 自连接 连接运算：可把多个表的数据连接在一起，进而获取所需的数据 怎样查询员工所在部门名称、工作地点连接查询概述连接查询概述将来自两个或两个以上表中的数据按照某种规则（数据间的关联关系）连接

2、到一起进行查询，从而获取所需信息。连接查询语法连接查询语法 笛卡尔集或称交叉连接 举例：SELECT * FROM emp, dept;-笛卡尔集或称交叉连接笛卡尔集笛卡尔集或称交叉连接.EMP表（14行）DEP表（4行）.笛卡儿集（14x4= 56行）交叉连接的特点：连接得到一个大表，列数是两个表列数之和，行数是两个表行数的乘积，很多记录没有实际意义，无用SELECT * FROM emp, dept;-笛卡尔集或称交叉连接等值连接等值连接 通过相等的字段值为条件建立表间记录的连接。 语法格式： 举例：SELECT | , .FROM table1, table2, .WHERE table

3、1.column1 = table2.column2;SELECT empno, ename, job, sal, emp.deptno, dname FROM emp, deptWHERE emp.deptno = dept.deptno;.等值连接：可看做是在交叉连接得到的表的基础上，筛选出相等的字段值的记录语句的特点：From 后为多个表名Where后有连接条件1. Select后的列名有可能加了表名前缀SELECT empno, ename, job, sal, emp.deptno, dname FROM emp, deptWHERE emp.deptno = dept.deptno

4、;等值连接（等值连接（2） 多表连接中可使用 AND 操作符增加查询条件SELECT empno, ename, job, sal, dept.deptno, dname FROM emp, deptWHERE emp.deptno = dept.deptno AND emp.deptno=20;SELECT empno, ename, job, sal, a.deptno, dname FROM emp a, dept bWHERE a.deptno = b.deptno AND b.deptno=20;多表连接中where子句包括两部分：连接条件、筛选条件使用表别名可以简化查询 练习：自己

5、构造数据熟悉表连接的过程 先交叉连接 然后连接条件筛选 接着普通筛选条件1.最后列选择SELECT empno, ename, job, sal, dept.deptno, dname FROM emp, deptWHERE emp.deptno = dept.deptno AND emp.deptno=20; 例题：列出所有工作是“CLERK”（办事员）的员工的姓名及其部门名称。 分析： 员工姓名和部门名称分别在emp表和dept表中有，所以需要表连接（必然有连接条件） 是否需要筛选：是1.是否需要列选择：是Select ename,dname from emp, dept where jo

6、b=CLERK AND EMP.DEPTNO=DEPT.DEPTNO 查询每个学生的学号、姓名、课程名和成绩 根据student、course、sc表做练习 查询性别为男、课程成绩及格的学生信息及课程号、成绩 查询计算机系（cs）选修了不少于两门课程的学生的学号、姓名 查询选修了课程名为“数据库系统”的学生学号、姓名和所在系 查询学生及其课程、成绩等情况（不管是否选课，均需列出学生信息）交叉连接交叉连接 使用CROSS JOIN显式标明，产生笛卡尔集，等同于在两个表连接时未使用WHERE子句限定连接条件。 举例：SELECT empno, ename, sal, emp.deptno, dna

7、me FROM emp CROSS JOIN dept;-等价于SELECT empno, ename, sal, emp.deptno, dname FROM emp, dept;内连接内连接JOIN和ON关键字的使用 等值连接在FROM子句中使用JOIN关键字标识，并使用ON子句指定连接条件及查询限定条件。 举例： 等价于：SELECT empno, ename, job, sal, dept.deptno, dname FROM emp JOIN deptON emp.deptno = dept.deptno AND emp.deptno=20;SELECT empno, ename,

8、job, sal, dept.deptno, dname FROM emp, deptWHERE emp.deptno = dept.deptno AND emp.deptno=20;内连接内连接等值连接还可以加inner关键字:SELECT empno, ename, job, sal, dept.deptno, dname FROM emp INNER JOIN deptON emp.deptno = dept.deptno AND emp.deptno=20;SELECT empno, ename, job, sal, dept.deptno, dname FROM emp INNER

9、JOIN deptON emp.deptno = dept.deptno WHERE emp.deptno=20;也可以写成下列形式:外连接外连接 左外联接两个表在连接过程中除返回满足连接条件的行以外，还返回左表中不满足条件的行 ，这种连接称为左外联接。 右外联接两个表在连接过程中除返回满足连接条件的行以外，还返回右表中不满足条件的行 ，这种连接称为右外联接。 全外联接Oracle9i开始新增功能，两个表在连接过程中除返回满足连接条件的行以外，还返回两个表中不满足条件的所有行 ，这种连接称为满外联接。外连接外连接 使用内连接查询时，返回结果中只包含符合连接条件的行 外连接：可返回不符合连接条件

10、的行 内连接：SELECT empno, ename, job, sal, dept.deptno, dname FROM emp INNER JOIN deptON emp.deptno = dept.deptno; 部门编号为40的部门没有员工，它的信息没有出现在查询结果中外连接外连接 外连接：可返回不符合连接条件的行 右外连接：SELECT empno, ename, job, sal, dept.deptno, dname FROM emp RIGHT JOIN deptON emp.deptno = dept.deptno; 部门编号为40的部门没有员工，但也出现在查询结果中外连接外

11、连接 左外连接：SELECT empno, ename, job, sal, dept.deptno, dname FROM emp LEFT JOIN deptON emp.deptno = dept.deptno; 9527员工没有部门信息，但也出现在查询结果中INSERT INTO EMP (empno,ename,job) values(9527,EAST,SALESMAN);外连接外连接 使用内连接查询时，返回结果中只包含符合连接条件的行 外连接：可返回不符合连接条件的行 外连接有三种： 左外连接：关键字LEFT OUTER JOIN或LEFT JOIN 右外连接：关键字RIGHT

12、OUTER JOIN或RIGHT JOIN 完全外连接：关键字FULL OUTER JOIN或FULL JOIN完全外连接完全外连接 完全外连接：会包含左表和右表中不满足连接条件的行SELECT empno, ename, job, sal, dept.deptno, dname FROM emp FULL JOIN deptON emp.deptno = dept.deptno; 连接运算中表的记录，根据连接条件被分为两类：满足连接条件的记录和不满足连接条件的记录左表右表满足连接条件的记录不满足连接条件的记录满足连接条件的记录不满足连接条件的记录等值连接左边满足连接条件的记录右边满足连接条件

13、的记录左外连接左边满足连接条件的记录右边满足连接条件的记录左边不满足连接条件的记录NullSELECT empno, ename, job, sal, dept.deptno, dname FROM emp LEFT JOIN deptON emp.deptno = dept.deptno;右外连接左边满足连接条件的记录右边满足连接条件的记录Null右边不满足连接条件的记录SELECT empno, ename, job, sal, dept.deptno, dname FROM emp RIGHT JOIN deptON emp.deptno = dept.deptno;自然连接自然连接 基

14、于两个表中的全部同名字段建立等值连接。如果两个表中同名字段的数据类型不一致，则连接出错。不允许在参照字段上使用表名或者别名作为前缀。 举例：SELECT empno, ename, job, sal, dept.deptno, dname FROM emp JOIN deptOn emp.deptno=dept.deptno;SELECT empno, ename, job, sal, dept.deptno, dname FROM emp ,deptWhere emp.deptno=dept.deptno;SELECT empno, ename, job, sal, deptno, dnam

15、e FROM emp NATURAL JOIN dept; -等价于在emp表中，smith的信息怎样通过mgr值查看smith的上司信息？ 根据mgr的值查询emp表 或通过emp表自连接也可实现，如下：自连接自连接SELECT e.empno 员工编号, e.ename 姓名, e.sal 工资, e.mgr 上司编号, m.ename 上司姓名FROM emp e, emp mWHERE e.mgr = m.empno and =smith;自连接自连接 问题：如何查得每个员工及其上司的工号和姓名？员工表(EMP)管理人员表(EMP)SELECT e.empno 员工编号,

16、e.ename 姓名, e.sal 工资, e.mgr 上司编号, m.ename 上司姓名FROM emp e, emp mWHERE e.mgr = m.empno;自连接特点：用于连接的两个表是同一个表From中表必须起别名1. 用到的字段名必须加表名前缀SELECT e.empno 员工编号, e.ename 姓名, e.sal 工资, e.mgr 上司编号, m.ename 上司姓名FROM emp e, emp mWHERE e.mgr = m.empno; 查询课程中的先修课的名称 Student 学生表 SC成绩表 COURSE成绩表SELECT sname,s.sno,cna

17、me,gradeFROM student s,sc ,course cWHERE s.sno=sc.sno and o=o 查看学生的姓名、对应的课程名、成绩，需要连接三个表多表连接多表连接 语法格式： 举例： 等价于SELECT FROM table1 table2 table3 .SELECT sname,s.sno,cname,gradeFROM student s,sc ,course cWHERE s.sno=sc.sno and o=oSELECT sname,s.sno,cname,gradeFROM student s join sc on s.sno=sc.sno join

18、course c on o=o 查询学生的学号、姓名及平均成绩 查询各门课程的平均成绩，显示课程名、课程编号 查询各科的平均成绩大于80分的学生的学号、姓名 查询各科的平均成绩大于80分的学生的学号、姓名、平均成绩三三. . 子查询子查询 子查询概述 单行子查询 多行子查询 多列子查询 相关子查询 Top-N分析 分页查询 问题引入：如何查询工资高于“CLARK的员工的信息？ 查询工资高于某个值的员工信息代码可以是Select empno,ename,sal from empSelect empno,ename,sal from empWhere sal ( ?)Where sal ( ?)

19、查询“CLARK”的工资可以是Select sal from emp where ename=CLARKSelect sal from emp where ename=CLARK 把两部分代码组合到一块子查询子查询SELECT empno, ename, job, sal, comm FROM emp WHERE sal (SELECT sal FROM emp WHERE ename=CLARK);子查询子查询 子查询被嵌入到其它SQL指令（主查询）中查询。 基本语法格式： 举例：SELECT FROM tableWHERE （);SELECT empno, ename, job, sal,

20、 comm FROM emp WHERE sal (SELECT sal FROM emp WHERE ename=CLARK);子查询子查询 子查询：也称内查询 外查询：也称主查询，包含子查询的外层查询，SELECT empno, ename, job, sal, comm FROM emp WHERE sal (SELECT sal FROM emp WHERE ename=CLARK); 代码执行过程 1.先计算出子查询，即某员工的工资 2.根据where筛选条件筛选数据 3.得到主查询结果SELECT empno, ename, job, sal, comm FROM emp WHER

21、E sal (SELECT sal FROM emp WHERE ename=CLARK); 例：列出与“SCOTT”从事相同工作的所有员工的姓名、员工号 分析： 查询结果：“员工的姓名、员工号” select ename ，empno from emp where job=（？） 查询“SCOTT”从事相同工作Select job from emp where ename=upper(scott); 把二者组合到一块儿SELECT empno, enameWHERE job= (Select job from emp where ename=upper(scott); 部门名称是“SALES

22、”（销售）的雇员的姓名，假定不知道销售部的部门编号 列出薪金高于公司平均水平的所有雇员 列出所在部门名称是“SALES”（销售）的雇员的姓名，假定不知道销售部的部门编号 思路：使用子查询尝试解决1. 主查询，查询emp表得到雇员姓名，筛选部门编号是某个值的员工 select ename from emp where deptno=某个值2.子查询，要筛选的员工的部门编号通过部门表查，查询部门名是“SALES”的部门的编号 Select deptno from dept where dname= “SALES”3.把子查询放到主查询中的where条件中select ename from emp

23、where deptno=（Select deptno from dept where dname= “SALES” ）数据源表列名、列名、表达式、表达式、分组函数分组函数查询结果数据筛选数据筛选Where筛选列运算子查询子查询构造筛构造筛选条件选条件列出从事“SALES”（销售）工作的雇员的姓名，假定不知道销售部的部门编号1.构造主查询结构（构造主查询结构（where中有些数据未定）中有些数据未定）2.构造子查询结构构造子查询结构3.把子查询放到主查询的把子查询放到主查询的where中中子查询分类子查询分类 单行子查询 多行子查询单行子查询单行子查询 单行子查询只返回一行记录。 对单行子查询

24、可使用单行记录比较运算符。 举例：运算符含 义 =等于 大于 =大于等于 小于 =小于等于 不等于SELECT empno,ename,sal FROM empWHERE sal (SELECT min(sal) FROM emp ) and sal (SELECT sal FROM emp WHERE job=CLERK);SELECT * FROM empWHERE sal (SELECT sal FROM emp WHERE empno = 8888);多行子查询多行子查询 返回多行记录，对其只能使用多行记录比较运算符。运算符含 义 IN等于列表中的任何一个 ANY和子查询返回的任意一个

25、值比较 ALL和子查询返回的所有值比较 EXISTS判断子查询是否有返回结果SELECT empno,ename,job FROM empWHERE deptno IN(SELECT deptno FROM dept WHERE dnameSALES );SELECT deptno,ename,sal FROM empWHERE sal ANY(SELECT sal FROM emp where deptno=10) and deptno10;例：在emp表中，查询不是销售部门（SALES）的员工的信息例：在emp表中，查询工资大于部门编号为10的任意一个员工工资即可的其他部门的员工信息 例：在emp表中，查询工资大于部门编号为10的所有员工工资的员工信息SEL