7多数据表连接和集合运算

1、 了解表连接的基本原理，分清连接的类型； 掌握各种类型连接的使用方法； 掌握利用SQL:1999的语法创建连接的方法； 掌握各种类型连接之间的区别与联系； 使用UNION,UNION ALL,INTERSECT和MINUS等集合运算； EMPNO DEPTNO LOC- - - 7839 10 NEW YORK 7698 30 CHICAGO 7782 10 NEW YORK 7566 20 DALLAS 7654 30 CHICAGO 7499 30 CHICAGO.14 rows selected. EMPNO ENAME. DEPTNO- -. - 7839 KING. 10 7698

2、BLAKE. 30 . 7934 MILLER . 10DEPTNO DNAME LOC - - - 10 ACCOUNTING NEW YORK 20 RESEARCHDALLAS 30 SALESCHICAGO 40 OPERATIONS BOSTONl使用连接从多个表中查询数据。l在 WHERE子句中书写连接条件,通常都是使用主键和外键进行连接。l如果在多个表中出现相同的列名，则需要使用表名作为来自该表的列名的前缀。lN个表相连时，至少需要N-1个连接条件。SELECTtable1.column, table2.columnFROMtable1, table2WHEREtable1.co

3、lumn1 = table2.column2;等值连接等值连接非等值连接非等值连接外部连接外部连接内部连接内部连接l笛卡尔积在下列情况产生：l连接条件被省略l连接条件是无效的l第一个表中的所有行和第二个表中的所有行都发生连接。l为了避免笛卡尔积，通常需要在WHERE子句中包含一个有效的连接条件。ENAME DNAME- -KINGACCOUNTINGBLAKE ACCOUNTING .KINGRESEARCHBLAKE RESEARCH.已选择已选择64行。行。 EMPNO ENAME. DEPTNO- -. - 7839 KING. 10 7698 BLAKE. 30 . 7934 MILL

4、ER . 10DEPTNO DNAME LOC - - - 10 ACCOUNTING NEW YORK 20 RESEARCHDALLAS 30 SALESCHICAGO 40 OPERATIONS BOSTON EMPNO ENAME DEPTNO- - - 7839 KING 10 7698 BLAKE 30 7782 CLARK 10 7566 JONES 20 7654 MARTIN 30 7499 ALLEN 30 7844 TURNER 30 7900 JAMES 30 7521 WARD 30 7902 FORD 20 7369 SMITH 20.14 rows selecte

5、d. DEPTNO DNAME LOC - - - 10 ACCOUNTING NEW YORK 30 SALES CHICAGO 10 ACCOUNTING NEW YORK 20 RESEARCHDALLAS 30 SALES CHICAGO 30 SALES CHICAGO 30 SALES CHICAGO 30 SALES CHICAGO 30 SALES CHICAGO 20 RESEARCHDALLAS 20 RESEARCHDALLAS.14 rows selected.注注: 等值连接通常被称为简单连接或内部连接。等值连接通常被称为简单连接或内部连接。 SQL SELECT e

6、mp.empno, emp.ename, emp.deptno, 2dept.deptno, dept.loc 3 FROM emp, dept 4 WHERE emp.deptno=dept.deptno;EMPNO ENAME DEPTNO DEPTNO LOC- - - - - 7839 KING 10 10 NEW YORK 7698 BLAKE 30 30 CHICAGO 7782 CLARK 10 10 NEW YORK 7566 JONES 20 20 DALLAS.已选择已选择16行。行。 EMPNO ENAME DEPTNO- - - 7839 KING 10 7698 BL

7、AKE 30 7782 CLARK 10 7566 JONES 20 7654 MARTIN 30 7499 ALLEN 30 7844 TURNER 30 7900 JAMES 30 7521 WARD 30 7902 FORD 20 7369 SMITH 20.14 rows selected.DEPTNO DNAME LOC - - 10 ACCOUNTING NEW YORK 30 SALES CHICAGO 10 ACCOUNTING NEW YORK 20 RESEARCHDALLAS 30 SALES CHICAGO 30 SALES CHICAGO 30 SALES CHICA

8、GO 30 SALES CHICAGO 30 SALES CHICAGO 20 RESEARCHDALLAS 20 RESEARCHDALLAS.14 rows selected.l在用到多个表时使用表名作前缀来限定列l通过使用表前缀可以提高性能l通过使用列的别名可以区分来自不同表但是名字相同的列l通过使用表的别名来简化查询语句SQL SELECT emp.empno, emp.ename, emp.deptno, 2 dept.deptno, dept.loc 3 FROM emp, dept 4 WHERE emp.deptno=dept.deptno;SQL SELECT e.empno

9、, e.ename, e.deptno, 2 d.deptno, d.loc 3 FROM emp e, dept d 4 WHERE e.deptno= d.deptno;NAMECUSTID-JOCKSPORTS 100TKB SPORT SHOP 101VOLLYRITE 102JUST TENNIS 103K+T SPORTS 105SHAPE UP 106WOMENS SPORTS 107.9 rows selected. CUSTID ORDID- - 101 610 102 611 104 612 106 601 102 602 106 604 106 605. 21 rows

10、selected. ORDID ITEMID- - 610 3 611 1 612 1 601 1 602 1.64 rows selected. EMPNO ENAME SAL- - - 7839 KING 5000 7698 BLAKE 2850 7782 CLARK 2450 7566 JONES 2975 7654 MARTIN 1250 7499 ALLEN 1600 7844 TURNER 1500 7900 JAMES 950.14 rows selected.GRADE LOSAL HISAL- - -1 70012002 120114003 14012000420013000

11、5 30019999ENAME SAL GRADE- - -JAMES 950 1SMITH 800 1ADAMS 1100 1.14 rows selected.SQL SELECT e.ename, e.sal, s.grade 2FROMemp e, salgrade s 3WHERE e.sal 4BETWEEN s.losal AND s.hisal;ENAMEDEPTNO-KING10BLAKE30CLARK10JONES20.DEPTNO DNAME- -10 ACCOUNTING30 SALES10 ACCOUNTING20RESEARCH.40OPERATIONSl您可以使用

12、外部连接来查看哪些行没有符合连接条件l外部连接的符号是 (+).SELECT table1.column, table2.columnFROMtable1, table2WHEREtable1.column(+) = table2.column;SELECT table1.column, table2.columnFROMtable1, table2WHEREtable1.column = table2.column(+);ENAME DEPTNO DNAME- - -KING 10 ACCOUNTINGCLARK 10 ACCOUNTING. 40 OPERATIONS已选择已选择17行。行

13、。SQL SELECTe.ename, d.deptno, d.dname 2 FROMemp e, dept d 3 WHEREe.deptno(+) = d.deptno 4 ORDER BY e.deptno;外部连接条件不能使用外部连接条件不能使用IN运算符，也不能用运算符，也不能用or运运算符与其它条件相连。算符与其它条件相连。EMPNO ENAME MGR- - - 7839 KING 7698 BLAKE7839 7782 CLARK7839 7566 JONES7839 7654 MARTIN 7698 7499 ALLEN7698EMPNO ENAME- - 7839 KIN

14、G 7839 KING 7839 KING 7698 BLAKE 7698 BLAKEWORKER.ENAME|WORKSFOR|MANAG-BLAKE works for KINGCLARK works for KINGJONES works for KINGMARTIN works for BLAKE.已选择已选择15行。行。SQL SELECT worker.ename| works for |manager.ename 2 FROM emp worker, emp manager 3 WHERE worker.mgr = manager.empno;自身连接：一个表与其自己进行连接需要

15、给表起别名以示区别由于所有属性名都是同名属性，因此必须使用别名前缀例 查询每一门课的间接先修课（即先修课的先修课） FIRST表（表（Course表）表） Ccredit 5 3 4 4 5 3 2Cno CnameCpno 1数据库数据库 5 2数学数学 3信息系统信息系统 1 4操作系统操作系统 6 5数据结构数据结构 7 6数据处理数据处理 7PASCAL语言语言 6 SECOND表（表（Course表）表） SELECT FIRST.Cno, SECOND.Cpno FROM Course FIRST, Course SECOND WHERE FIRST.Cpno = SECOND.C

16、no；Ccredit 5 3 4 4 5 3 2Cno CnameCpno 1数据库数据库 5 2数学数学 3信息系统信息系统 1 4操作系统操作系统 6 5数据结构数据结构 7 6数据处理数据处理 7PASCAL语言语言 6查询结果：查询结果：CnoPcno17354567Use a join to query data from more than one table.SELECT table1.column, table2.columnFROMtable1CROSS JOIN table2 |NATURAL JOIN table2 |JOIN table2 USING (column_n

17、ame) |JOIN table2 ON(table1.column_name = table2.column_name) |LEFT|RIGHT|FULL OUTER JOIN table2 ON (table1.column_name = table2.column_name);lCROSS JOIN 子句会产生两个表的交叉乘积，和两个表之间的笛卡尔积是一样的。 SELECT emp.empno,emp.ename,emp.sal,emp.deptno,dept.loc FROM emp CROSS JOIN dept; EMPNO ENAME SAL DEPTNO LOC- - - -

18、- 7369 SMITH 800 20 NEW YORK 7499 ALLEN 1600 30 NEW YORK 7521 WARD 1250 30 NEW YORK 7566 JONES 2975 20 NEW YORK 7934 MILLER 1300 10 BOSTON已选择64行。 l使用自然连接时应注意以下事项：l自然连接子句是基于两个表存在相同名字的列；l返回两个表相匹配列中具有相同值的记录；l如果相同名称的列的数据类型不同，则会产生错误。 SELECT empno,ename,sal,deptno,loc FROM emp NATURAL JOIN dept; EMPNO ENA

19、ME SAL DEPTNO LOC- - - - - 7369 SMITH 800 20 DALLAS 7499 ALLEN 1600 30 CHICAGO 7521 WARD 1250 30 CHICAGO 7566 JONES 2975 20 DALLAS 7934 MILLER 1300 10 NEW YORK已选择已选择16行。行。l自然连接使用所有名称和数据类型相匹配的列，USING子句可以用于指定产生连接的列。ENAME ENAME SAL DEPTNO LOC- - - - -SMITH SMITH 800 20 DALLASJONES JONES 2975 20 DALLASS

20、COTT SCOTT 3000 20 DALLASADAMS ADAMS 1100 20 DALLASFORD FORD 3000 20 DALLAS已选择已选择5行。行。 SELECT e.ename,e.ename,e.sal,deptno,d.loc FROM emp e JOIN dept d USING (deptno) WHERE deptno = 20 ;l自然连接条件基本上是具有相同列名的表之间的等值连接，要指定任意连接条件或特殊的列可以使用ON子句，用ON将连接条件和其它检索条件分隔开可以提高代码的可读性。 EMPNO ENAME DEPTNO DEPTNO LOC- - -

21、 - - 7369 SMITH 20 20 DALLAS 7499 ALLEN 30 30 CHICAGO 7934 MILLER 10 10 NEW YORK 已选择已选择15行。行。 SELECT e.empno, e.ename, e.deptno, d.deptno, d.loc FROM emp e JOIN dept d ON (e.deptno = d.deptno); SELECT e.empno, e.ename, d.loc,m.ename FROM emp e JOIN dept d ON e.deptno = d.deptno JOIN manager m ON e.m

22、gr = m.empno; EMPNO ENAME LOC ENAME- - - - 7788 SCOTT DALLAS JONES 7902 FORD DALLAS JONES 7499 ALLEN CHICAGO BLAKE 7521 WARD CHICAGO BLAKE 7934 MILLER NEW YORK CLARK 已选择已选择8行。行。 SELECT e.ename,e.deptno,d.loc FROM emp e LEFT OUTER JOIN dept d ON (e.deptno = d.deptno); ENAME DEPTNO LOC- - -MILLER 10 N

23、EW YORKKING 10 NEW YORKCLARK 10 NEW YORKFORD 20 DALLASALLEN已选择已选择16行。行。 SELECT e.ename,e.deptno,d.loc FROM emp e RIGHT OUTER JOIN dept d ON (e.deptno = d.deptno); ENAME DEPTNO LOC- - -SMITH 20 DALLASALLEN 30 CHICAGOJONES 20 DALLASMARTIN 30 CHICAGO BOSTON已选择已选择17行。行。 SELECT e.ename,e.deptno,d.loc FRO

24、M emp e FULL OUTER JOIN dept d ON (e.deptno = d.deptno); ENAME DEPTNO LOC- - -MILLER 10 NEW YORKKING 10 NEW YORKCLARK 10 NEW YORKALLEN BOSTON已选择17行。l 集合运算符lUNION 返回两个查询的所有行，但剔除重复行lUNION ALL 返回两个查询的所有行，保留所有重复行lINTERSECT 返回同时出现在两个查询结果中的所有行lMINUS 从第一个查询返回的行中减去第二个查询返回的行，剩余的行即为返回结果多个查询语句的结果可以做集合运算，结果集的字段

25、类型、数量和顺序应该一样。l返回两个查询的结果，重复行只显示一次l例：SELECT * FROM EMPlUNIONlSELECT * FROM MANAGERl与UNION运算相似，返回两个查询的结果，显示重复行l例：SELECT * FROM EMPlUNION ALLlSELECT * FROM MANAGERl返回两个查询中都出现的结果，即 交运算l例：SELECT * FROM EMPlINTERSECTlSELECT * FROM MANAGERl返回那些在第一个查询结果中但不在第二个查询结果中的行l例：SELECT * FROM MANAGERlMINUSlSELECT * FROM EMP一、从一、从student表中找出在同一个系中的不同学表中找出在同一个系中的不同学生名生名二、以下语句的作用是：二、以下语句的作用是：SELECT ename,sal FROM empWHERE sal(SELECT min(sal) FROM emp) ;A. 显示工资低于显示工资低于1000元的雇员信息元的雇员信息B. 将雇员工资小于将雇员工资小于1000元的工资增加元的工资增加1000后后显示显示C. 显示超过最低工资显示超过最低工资1000元的雇员信息元的雇员信息D. 显示不超过最低工资显示不超过最低工资1000元