SQL server查询三张表

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

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

--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;