源码-Oracle数据库管理-第九章-SQL查询-Part 4（集合运算和子查询）

本节也算是高级查询的内容了。

集合运算分为：联合运算（UNION）、全联合运算（UNION ALL）、相交运算（INTERSECT）和相交运算（MINUS），和高一代数中集合运算如出一辙，不难理解。

子查询就复杂了，分为：相关子查询、非相关子查询（单行单列子查询（标量子查询）、多行单列子查询、多列子查询）和内联视图。其中，相关子查询最不好理解了，需要继续学习！

--TBC 2016-10-09 --9.3 集合和子查询--9.3.1 理解集合运算--创建books表的一个幅本，不包含任何数据（又学了一招，哈哈）CREATE TABLE books_his AS SELECT * FROM books WHERE 1=2;--插入测试数据记录INSERT INTO books_his VALUES(orders_seq.NEXTVAL,'PHP开发建站大全',1);INSERT INTO books_his VALUES(orders_seq.NEXTVAL,'三国演义',3);INSERT INTO books_his VALUES(orders_seq.NEXTVAL,'PHOTOSHOP艺术设计',2);INSERT INTO books_his VALUES(orders_seq.NEXTVAL,'红楼梦',3);--插入2条与boos表重复的记录select * from books_his;select * from books;INSERT INTO books_his VALUES(1019,'PL/SQL从入门到精通',1);INSERT INTO books_his VALUES(1020,'云图',3);delete from books_his where book_id in(1030,1031);--9.3.2 Union联合运算与Union all全联合运算--UNION操作符使用示例(不包含重复行)SELECT book_id, book_name, cate_id FROM booksUNIONSELECT book_id, book_name, cate_id FROM books_his;--UNION ALL操作符使用示例(包含重复行)SELECT book_id, book_name, cate_id FROM booksUNION ALLSELECT book_id, book_name, cate_id FROM books_his;--使用列别名进行排序（列名以第一个select语句为准，因此，order by语句中必须使用同样的列别名）SELECT book_id   as "图书编号",       book_name as "图书名称",       cate_id   as "图书分类"  FROM booksUNIONSELECT book_id, book_name, cate_id FROM books_his ORDER BY 图书编号;--使用列名进行排序会出现异常（不能正常运行）SELECT book_id   as "图书编号",       book_name as "图书名称",       cate_id   as "图书分类"  FROM booksUNIONSELECT book_id, book_name, cate_id FROM books_his ORDER BY book_id;--9.3.3 Intersect交集运算--使用INTERSECT进行交集运算（在比较两个表中的相同数据时非常有用！）SELECT book_id, book_name, cate_id FROM booksINTERSECTSELECT book_id, book_name, cate_id FROM books_his;--使用MINUS进行差集运算，查询books表中与books_his表不重复的记录（注意：差集是有方向的）SELECT book_id, book_name, cate_id FROM booksMINUSSELECT book_id, book_name, cate_id FROM books_his;--使用MINUS进行差集运算，查询books_his表中与books表不重复的记录SELECT book_id, book_name, cate_id FROM books_hisMINUSSELECT book_id, book_name, cate_id FROM books;--集合运算与NULL值（Oracle认为Null值与数字或日期类型相匹配）SELECT 123 num, SYSDATE dates, '第1个查询' string FROM dualUNIONSELECT NULL num, NULL dates, '第2个查询' string FROM dual;--9.3.5 理解子查询--子查询：将一个select语句嵌入到另一个SQL语句中，包含该select语句的SQL语句称为容器语句或父查询。--查询比PANDENG的工资的5分之1还要高的人员的列表SELECT empno, ename, sal, hiredate  FROM emp WHERE sal > (SELECT sal/5 FROM emp WHERE ename = 'PANDENG');select * from emp;select * from dept;--9.3.6 非相关子查询--非相关子查询的一个例子SELECT empno, ename, sal  FROM emp WHERE deptno = (SELECT deptno FROM dept WHERE loc = 'NEW YORK' and rownum<=1);--9.3.7 单行单列子查询--下面是单行单列子查询的例子--查询与黄阔在相同部门，并且工资大于PANDENG的工资的10分之1的人员名单SELECT empno, ename, sal  FROM emp WHERE deptno = (SELECT deptno FROM emp WHERE ename = '黄阔' and rownum<=1)   AND sal >= (SELECT sal/10 FROM emp WHERE ename = 'PANDENG');--查询黄阔所在部门的员工中，工资小于该部门平均工资的员工列表（这个算比较复杂的示例了，现实比这个更复杂！）SELECT empno, ename, sal  FROM emp WHERE deptno = (SELECT deptno FROM emp WHERE ename = '黄阔'and rownum<=1)   AND sal <=       (SELECT avgsal          FROM (SELECT deptno, AVG(sal) avgsal FROM emp GROUP BY deptno)         WHERE deptno = (SELECT deptno FROM emp WHERE ename = '黄阔' and rownum<=1));  --9.3.8 多行单列子查询--下面是多行单列子查询的例子--查询波士顿的员工列表SELECT empno, ename, sal  FROM emp WHERE deptno IN (SELECT deptno FROM dept WHERE loc = 'DALLAS'); --实际上的执行如下所示：SELECT empno, ename, sal  FROM emp WHERE deptno IN (20,30);  --使用ANY操作符SELECT empno, ename, sal,deptno  FROM emp WHERE deptno = 30   AND sal <= ANY (SELECT sal FROM emp WHERE deptno = 20);--使用ALL操作符SELECT empno, ename, sal,deptno  FROM emp WHERE deptno = 30   AND sal <= ALL (SELECT sal FROM emp WHERE deptno = 20 and sal>10000);      --相关子查询的语法：select columnlist  from table1 t1 where column2 in       (select column3 from table2 t2 where t2.column3 = t1.column4)--相关子查询示例SELECT book_name, cate_id  FROM books WHERE EXISTS (SELECT 1 FROM borrows WHERE borrows.book_id = books.book_id); --相关子查询示例2,薪资超过每个员工所在部门的平均薪资的人员信息（挺实用，但不太好理解）--在这个查询语句中，每当从emp表中取出一行后，会将该行的deptno传给子查询进行部门平均工资的计算SELECT e1.empno  as "工号",       e1.ename  as "姓名",       e1.deptno as "部门",       e1.sal    as "工资"  FROM emp e1 WHERE e1.sal > (SELECT AVG(sal) FROM emp e2 WHERE e2.deptno = e1.deptno);   select * from emp for update;select deptno,avg(sal) from emp group by deptno;SELECT AVG(sal) FROM emp where deptno=20; --统计函数可与where子句同时使用