5.表连接:两元运算,对两个或多个表查询,结果通常是含有参加连接运算的表的指定列,N个表,关联条件一定有N-1个
连接谓词:在SELECT语句的WHERE子句中使用比较运算符给出连接条件对表进行查询的形式。当比较符为"="时,是等值连接,若在目标列去除相同的字段名,则为自然连接
类型:
①等值连接(EQUIJOIN):两个表某些字段值相等
非等值连接(NON-EQUIJOIN):表中的一个字段与令一个表中的多个字段比较。理解员工表(emp)和员工等级表(salgrade)
SELECT empno,ename,sal,grade,lowsal,highsal FROM emp,salgrade WHERE sal>=lowsal and sal<=highsal
②内连接(INNER JOIN):只返回满足连接条件的数据
使用别名查询:
SELECT dname,ename,a.deptno FROM emp a,dept b WHERE a.deptno = b.deptno AND a.deptno = 8;
自连接:
SELECT a.ename AS 员工姓名,b.ename AS 经理名字 FROM emp a,emp b WHERE a.mgr = b.empno(+);
③外连接(OUTER JOIN):返回满足条件的记录及另一方/两方所有不满足条件记录
左外连接(LEFT OUTER JOIN)、右外连接(RIGHT OUTER JOIIN)、全外连接(FULL OUTER JOIN),(+)写法只有在ORACLE中有效
SELECT dname,ename,b.deptno FROM emp a,dept b WHERE a.deptno(+) = b.deptno;
同
SELECT dname,ename,b.deptno FROM emp a RIGHT OUTER JOIN dept b ON a.deptno = b.deptno;
④交叉连接(CROSS JOIN):产生一个笛卡尔积,效果等同于两个表连接时未使用WHERE子句限定
SELECT empno,ename,sal,emp.deptno,dname FROM emp CROSS JOIN dept;
⑤自然连接(NATURAL JOIN):基于两个表中的全部同名列建立连接,即从两个表中选出同名列的值对应相等的所有行,若数据类型不同,会出错,不允许在参照列上用前缀
SELECT empno,ename,sal,deptno,dname FROM emp NATURAL JOIN dept;
⑥使用Using子句:不希望参照被连接表的所后同名类进行等值连接,自然连接将无法满足,可以在连接时使用USING子句来设置用于等值连接的列名
SELECT empno,ename,sal,deptno,dname FROM emp JOIN dept USING(dept); --假设emp和dept表都有dept和temp子段,但我们只想通过dept连接
⑦使用On子句:参照非同名的列进行等值连接,或设置任意的连接条件
SELECT dname,ename,a.deptno FROM emp a INNER JOIN dept b ON a.deptno = b.deptno WHERE a.deptno = 8;
⑧多表连接:(SQL99语法)两个以上的表进行连接时应依次/分别指定相临的连个表之间的连接条件,第二个表作为第三个表的主语
SELECT employee_id,first_name,salary,department_id,department_name,location_id,city
FROM employees JOIN departments USING(department_id) --两个共同字段department_id和manager_id
NATURAL JOIN locations; --departments表和locations表只有一个共同字段location_id
6.子查询:使用另一个查询的结果作为条件的一部分
①单行子查询:只返回一行记录,可使用= | > | >= | < | <= | <>。如果子查询未返回任何行,则主查询也不返回任何结果
②多行子查询:返回多行记录,可使用IN | ANY | ALL
SELECT * FROM emp WHERE sal>ANY(SELECT AVG(sal) FROM emp GROUP BY deptno);
SELECT * FROM emp WHERE sal>ALL(SELECT AVG(sal) FROM emp GROUP BY deptno);
IN语法:expression [NOT] IN (subquery)
当表达式expression与子查询subquery的结果表中某个值相等时,IN谓词返回TRUE,否则返回FALSE。查找未选修数据结构的学生:
SELECT xh,xm,zym,zxf FROM xs WHERE 学号 NOT IN
(SELECT 学号 FROM XS_KC WHERE 课程号 IN
(SELECT 课程号 FROM KC WHERE 课程名='数据结构'));
EXISTS语法:[NOT] EXISTS (subquery)。
用于测试子查询的结果是否为空,若子查询的结果集不为空,则返回TRUE,否则返回FALSE
③相关子查询:子查询的条件依赖外层查询中的某些值,先查找外层表的第一行,根据该行某字段处理内层查询,若结果不为空,则WHERE条件为真,把该行取出作为结果集的一行,然后进入外层表的下一行重复操作,例:查询选修了全部课程的同学姓名
SELECT xm FROM xs WHERE NOT EXISTS
(SELECT * FROM kc WHERE NOT EXISTS
(SELECT * FROM xs_kc WHERE xh=xs.kh AND kch=kc.kch));
7.TOP-N分析
Oracle中伪列rownum
SELECT * FROM emp WHERE rownum <= 2; --前两行
SELECT * FROM(SELECT * FROM emp ORDER BY sal DESC) WHERE rownum <= 2; --最高工资2人
--分页查询
SELECT * FROM(SELECT rownum AS num,emp.* FROM emp) WHERE num >= 10 AND num <= 20;
8.其他
合并结果集:UNION
返回交集结果集:INTERSECT
复制一张表
CREATE TABLE mydept AS SELECT * FROM dept;
复制一个表结构
CREATE TABLE mydept AS SELECT * FROM dept WHERE 1=2;
复制部分数据
INSERT INTO mydept(deptno,loc) SELECT deptno,loc FROM dept;