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

本文链接：https://blog.csdn.net/hpdlzu80100/article/details/52765181

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

集合运算分为：联合运算（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 books
UNION
SELECT book_id, book_name, cate_id FROM books_his;

--UNION ALL操作符使用示例(包含重复行)
SELECT book_id, book_name, cate_id FROM books
UNION ALL
SELECT book_id, book_name, cate_id FROM books_his;

--使用列别名进行排序（列名以第一个select语句为准，因此，order by语句中必须使用同样的列别名）
SELECT book_id   as "图书编号",
       book_name as "图书名称",
       cate_id   as "图书分类"
  FROM books
UNION
SELECT book_id, book_name, cate_id FROM books_his ORDER BY 图书编号;

--使用列名进行排序会出现异常（不能正常运行）
SELECT book_id   as "图书编号",
       book_name as "图书名称",
       cate_id   as "图书分类"
  FROM books
UNION
SELECT 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 books
INTERSECT
SELECT book_id, book_name, cate_id FROM books_his;


--使用MINUS进行差集运算，查询books表中与books_his表不重复的记录（注意：差集是有方向的）
SELECT book_id, book_name, cate_id FROM books
MINUS
SELECT book_id, book_name, cate_id FROM books_his;


--使用MINUS进行差集运算，查询books_his表中与books表不重复的记录
SELECT book_id, book_name, cate_id FROM books_his
MINUS
SELECT book_id, book_name, cate_id FROM books;


--集合运算与NULL值（Oracle认为Null值与数字或日期类型相匹配）
SELECT 123 num, SYSDATE dates, '第1个查询' string FROM dual
UNION
SELECT 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子句同时使用