源码-Oracle数据库管理-第九章-SQL查询-Part 3（多表连接查询）

如果说多表连接查询是一顿丰盛的午餐，那么前面所学的简单查询只能算作开胃菜。

如何理解庞大的生产数据库中成百上千甚至上万的数据表之间的关系，发现数据间的爱恨情仇关系，最终找到自己所需的数据？本章的多表连接查询无疑是个很好的开始。

--TBC 2016-10-08--简单CASE语句的使用SELECT empno,       ename,       sal,       CASE job         WHEN '职员' THEN          1.10 * sal         WHEN '分析人员' THEN          1.15 * sal         WHEN '经理' THEN          1.20 * sal         ELSE          sal       END "调整后工资"  FROM emp WHERE deptno = 20;  --搜索CASE语句的使用SELECT empno 工号,       ename 姓名,       sal 工资,       CASE         WHEN EXTRACT(YEAR FROM hiredate) > 1982 THEN          1.10 * sal         WHEN EXTRACT(YEAR FROM hiredate) <= 1982 THEN          1.15 * sal         ELSE          sal       END 调薪后的工资  FROM emp WHERE deptno = 20;--9.2 多表连接查询 --多表连接查询示例SELECT emp.empno, emp.ename, dept.dname, dept.loc  FROM emp, dept WHERE emp.deptno = dept.deptno;--产生笛卡尔乘积SELECT COUNT(*) FROM (SELECT 1 FROM emp,dept);--如果连接条件无效或者完全被省略，将出现笛卡尔乘积（多张表所有行的排列）select * from emp, dept;--使用ANSI SQL标准的连接语法SELECT emp.empno, emp.ename, dept.dname, dept.loc  FROM emp  JOIN dept ON emp.deptno = dept.deptno; --9.2.2 内连接--内连接：又称等值连接或简单连接，仅返回符合连接条件的记录（如，一表中的某一外键为另一表的主键）--清除并重新构建books表的内容，select * from books;DROP TABLE books;CREATE TABLE books(       book_id INT CONSTRAINT pk_book_id PRIMARY KEY,               --指定图书表的主键            book_name VARCHAR2(50),                                        --指定图书名称       cate_id INT,           CONSTRAINT fk_cate_id FOREIGN KEY (cate_id) REFERENCES bookCategory (cate_id) --使用表级别的语法创建books表,定义外键约束并指定级联删除       ON DELETE CASCADE                                                   );     --重新向表中插入内容INSERT INTO books VALUES(orders_seq.NEXTVAL,'PL/SQL从入门到精通',1);INSERT INTO books VALUES(orders_seq.NEXTVAL,'云图',3);INSERT INTO books VALUES(orders_seq.NEXTVAL,'零基础看懂财务报表',2);INSERT INTO books VALUES(orders_seq.NEXTVAL,'Oracle从入门到精通',1);select * from bookcategory;insert into bookcategory values(1,'计算机图书','书山有路勤为径，学海无涯苦作舟！');--使用内连接查询，使用表别名SELECT a.book_name, b.cate_name, b.cate_description  FROM books a, bookCategory b WHERE a.cate_id = b.cate_id;    --使用内连接查询，并追加查询条件SELECT a.book_name, b.cate_name, b.cate_description  FROM books a, bookCategory b WHERE a.cate_id = b.cate_id   AND b.cate_id = 1;  --创建一个图书借阅表CREATE TABLE borrows(  borrow_id INT CONSTRAINT pk_borrow_id PRIMARY KEY,  --借阅id  book_id INT,                                        --图书id  borrow_date DATE DEFAULT SYSDATE,                   --借出日期  return_date DATE,                                   --归还日期  --与books表创建主外键关系  CONSTRAINT fk_borrow_id FOREIGN KEY(book_id) REFERENCES books(book_id)  ON DELETE CASCADE);--插入借书记录INSERT INTO borrows VALUES(orders_seq.NEXTVAL,1019,SYSDATE-2,NULL);INSERT INTO borrows VALUES(orders_seq.NEXTVAL,1020,NULL,SYSDATE);select * from borrows; --有了borrows表之后，就可以开始创建一个3表连接的查询（这个示例不错，通俗易懂！）SELECT a.book_name   as "书名",       a.book_id as "书本编号",       b.cate_id as "分类编号",       b.cate_name   as "分类",       b.cate_description as "分类描述",       c.borrow_date as "借出日期",       c.return_date as "归还日期"  FROM books a, bookCategory b, borrows c WHERE a.cate_id = b.cate_id   AND a.book_id = c.book_id   AND b.cate_id = 1;  --创建非等值连接SELECT a.ename as "员工姓名",       a.sal   as "员工工资",       b.dname as "部门名称",       c.grade as "薪资级别"  FROM emp a, dept b, salgrade c WHERE a.deptno = b.deptno   AND a.sal BETWEEN c.losal AND c.hisal   AND b.deptno = 20;select * from salgrade;   --使用ANSI SQL创建非等值连接SELECT a.ename as "员工姓名",       a.sal   as "员工工资",       b.dname as "部门名称",       c.grade as "薪资级别"  FROM emp a INNER JOIN dept b ON a.deptno = b.deptno INNER JOIN salgrade c ON a.sal BETWEEN c.losal AND c.hisal WHERE b.deptno = 20;--9.2.3 外连接--外连接：是内连接的扩展，即使连接条件不满足，也能返回数据--创建一个左外连接的示例SELECT a.book_name   as "图书名称",       b.borrow_date as "借出日期",       b.return_date as "归还日期"  FROM books a, borrows b WHERE a.book_id = b.book_id(+);  --创建一个右外连接的示例--1,向books表中插入2条数据INSERT INTO books VALUES(orders_seq.NEXTVAL,'C#典型模块开发实战大全',NULL);INSERT INTO books VALUES(orders_seq.NEXTVAL,'平凡的世界',NULL);--创建一个右外连接的例子（right outer join）SELECT a.cate_name, a.cate_description, b.book_name  FROM bookCategory a, books b WHERE a.cate_id(+) = b.cate_id;--创建一个左外连接的例子(left outer join)SELECT a.cate_name, a.cate_description, b.cate_id, b.book_name  FROM bookCategory a, books b WHERE a.cate_id = b.cate_id(+);insert into bookcategory values(4,'古典名著','《三国演义》、《西游记》...'); --创建一个内连接的例子(inner join)SELECT a.cate_name, a.cate_description, b.book_name  FROM bookCategory a, books b WHERE a.cate_id = b.cate_id; --使用ANSI SQL的左外连接SELECT a.book_name   as "图书名称",       b.borrow_date as "借出日期",       b.return_date as "归还日期"  FROM books a  LEFT JOIN borrows b ON a.book_id = b.book_id;  --使用ANSI SQL的右外连接SELECT a.cate_name        as "图书分类",       a.cate_description as "图书描述",       b.book_name        as "图书名称"  FROM bookCategory a RIGHT JOIN books b ON a.cate_id = b.cate_id;  --使用ANSI SQL的全连接（full join）--使用ANSI SQL语法，可读性更好，便于将Oracle开发的代码向其他数据库SELECT a.cate_name        as "图书分类",       a.cate_description as "图书描述",       b.book_name        as "图书名称"  FROM bookCategory a  FULL JOIN books b ON a.cate_id = b.cate_id;--使用UNION来模拟全连接的效果 (复杂查询可由简单查询构造) SELECT a.cate_name        as "图书分类",        a.cate_description as "图书描述",        b.book_name        as "图书名称"   FROM bookCategory a, books b  WHERE a.cate_id(+) = b.cate_id UNION SELECT a.cate_name, a.cate_description, b.book_name   FROM bookCategory a, books b  WHERE a.cate_id = b.cate_id(+);  --9.2.4 自引用连接 --自连接：表中一个字段连接到该表的另一个字段（如，emp表中的mgr字段的值指向empno字段）--From子句中使用了相同的表（emp），但指定了不同的别名，从而在where子句让第一张emp表中的empno和第二章表中的mgr进行等值连接--自引用查询示例SELECT em.ename 员工名称,       em.empno 员工工号,       em.sal   员工工资,       mg.ename 经理名,       mg.empno 经理工号,       mg.sal   经理工资  FROM emp em, emp mg WHERE em.mgr = mg.empno   AND em.deptno = 20;      --自引用外连接查询SELECT em.ename 员工名称,       em.empno 员工工号,       em.sal   员工工资,       mg.ename 经理名,       mg.empno 经理工号,       mg.sal   经理工资  FROM emp em, emp mg WHERE em.mgr = mg.empno(+)   AND em.deptno = 10;      --9.2.5 交叉连接--交叉连接：cross join，会返回两张表上的所有行的笛卡尔乘积   --交叉连接示例,使用CROSS JOIN, 使用子查询实现提数功能SELECT COUNT(*)  FROM (SELECT a.book_name, b.cate_name          FROM books a         CROSS JOIN bookCategory b);    --交叉连接示例，不指定连接条件SELECT COUNT(*)  FROM (SELECT a.book_name, b.cate_name FROM books a, bookCategory b);  --查询图书和分类的总数（dual还可以这样玩啊？哈哈哈！）  SELECT (SELECT COUNT(1) bookCount FROM books) as "图书总数",       (SELECT COUNT(1) FROM bookCategory) as "分类总数"  FROM DUAL;  --9.2.6 自然连接--Oracle建议通过表之间的关系列来连接不同的表，以便返回有意义的数据。一般这种主外键关联的列会具有相同的列名。--自然连接：Oracle可以基于字段的数据类型和字段名自动匹配，建立表之间的连接--使用NATURAL JOIN实现自然连接SELECT a.empno as "员工编号",       a.ename as "员工姓名",       a.sal   as "员工工资",       b.dname as "部门名称"  FROM emp a NATURAL  JOIN dept b WHERE deptno = 20;  --使用USING子句指定自然连接条件SELECT a.empno as "员工编号",       a.ename as "员工姓名",       a.sal   as "员工工资",       b.dname as "部门名称"  FROM emp a INNER JOIN dept b USING (deptno) WHERE deptno = 20;