数据库的多表查询

之前的简单查询、限定查询、查询排序，都属于SQL的标准语句；

这里的多表查询属于复杂查询。

最主要的问题是：消除笛卡尔积的问题

（只要增加一张表，那么就一定需要一个可以消除增加表所带来的笛卡尔积的问题的条件）

什么是多表查询？

在一条查询语句中，从多张表里一起取出所需要的数据，即在FROM子句之后跟上多个表。

会出现笛卡尔积现象，就是如果表A有n行记录，表B有m条记录，那么如果需要查询A、B两个表，那么就会有n * m条记录。因此需要添加类似于如下WHERE的条件，这条件叫等值关联。（这时候积只是不显示了，实际上还是存在的）

SELECT * FROM emp,dept WHERE emp.deptno=dept.deptno

由于多张表之间可能会存在重名的字段，所以在进行重名字段访问的时候，前面需要加上表的名称，采用“表名称.字段”的方式来进行访问。

各种实例

一. 单表查询

题目一. 查询每个雇员的编号、姓名、职位、基本工资、部门名称、部门位置

分析过程：

• 确定所需的数据表：

  • emp表：查询每个雇员的编号、姓名、职位、基本工资
  • dept表：部门名称、部门位置

• 确定已知的关联字段：

  • 部门与雇员的关联：emp.deptno=dept.deptno
  • 执行步骤：FROM、WHERE、SELECT

SELECT emp.empno,emp.ename,emp.job,emp.sal,dept.dname,dept.loc
FROM emp,dept
WHERE emp.deptno=dept.deptno;

如果表名称很长，那么会很不方便，因此我们在多表查询时，会给表定义一个别名，如下。

更新：

SELECT e.empno,e.ename,e.job,e.sal,d.dname,d.loc
FROM emp e,dept d
WHERE e.deptno=d.deptno;

题目二. 查出每个雇员的编号、姓名、雇佣日期、基本工资、工资等级

分析过程：

• 确定所需要的数据表：

  • emp表：每个雇员的编号、姓名、雇佣日期、基本工资
  • salgrade表：工资等级

• 确定已知的关联字段：

  • 雇员和工资等级：emp.sal BETWEEN salgrade.losal AND salgrade.hisal

SELECT e.empno,e.ename,e.hiredate,e.sal,s.grade
FROM emp e,salgrade s
WHERE e.sal BETWEEN s.losal AND s.hisal;

更新：

SELECT e.empno,e.ename,e.hiredate,e.sal,
       DECODE(s.grade, 1, 'E等工资', 2, 'D等工资', 3, 'C等工资', 4, 'B等工资', 5, 'A等工资') grade
FROM emp e,salgrade s
WHERE e.sal BETWEEN s.losal AND s.hisal;

二. 多表查询

题目三：查询每个雇员的姓名、职位、基本工资、部门名称、工资等级

分析过程：

• 确定所需要的数据表：

  • emp表：每个雇员的姓名、职位、基本工资
  • dept表：部门名称
  • salgrade表：工资等级

• 确定已知的关联字段：

  • 雇员和部门：emp.deptno=dept.deptno
  • 雇员和工资等级：emp.sal BETWEEN salgrade.losal AND salgrade.hisal

如果现在是多个消除笛卡尔积的条件，那么往往使用AND将这些条件连接在一起。

SELECT e.ename,e.job,e.sal, d.dname, s.grade
       DECODE(s.grade, 1, 'E等工资', 2, 'D等工资', 3, 'C等工资', 4, 'B等工资', 5, 'A等工资') grade
FROM emp e, dept d, salgrade s
WHERE e.deptno=d.deptno AND e.sal BETWEEN s.losal AND s.hisal;

三. 内连接、外连接

1. 内连接：也称为等值连接，之前所有的实例都是内连接，只有满足条件的数据才会显示，只要有一点不满足就不会显示数据

2. 外连接：目的是为了显示那些不满足等值连接的数据，可分为左外丽连接、右外连接、全外连接

   • 左外连接：左关系属性=右关系属性(+)
   • 左外连接：左关系属性(+)=右关系属性

例子：

SELECT *
FROM emp e, dept d
WHERE e.deptno=d.deptno(+)

四. 自身连接

题目四：查询出每个雇员的编号、姓名及其上级领导的编号、姓名（其中上级领导的信息也在emp表中）

分析过程：

• 确定所需要的数据表：

  • emp表：雇员的编号、姓名
  • emp表：找到的领导的编号、姓名

• 确定已知的关联字段：

  • 雇员和领导：emp.mgr=emp.empno(雇员领导的编号=领导的编号)

SELECT e.empno, e.ename, m.empno, m.ename
FROM emp e, emp m
WHERE e.mgr=m.empno(+)

五. SQL:1999语法

1. 交叉连接：主要功能是产生笛卡尔积

   SELECT * FROM emp CROSS JOIN dept;

2. 自然连接：消除笛卡尔积

   SELECT * FROM emp NATURAL JOIN dept;

3. USING子句：没有关联字段，用USING子句进行笛卡尔积消除（设置连接字段)

   SELECT * FROM emp JOIN dept USING(deptno);

4. ON子句：设置连接条件

   `SELECT * FROM emp e JOIN salgrade s ON(e.sal BETWEEN s.losal AND s.hisal);`
   

5. 右外连接

   SELECT * FROM emp e RIGHT OUTER JOIN dept d ON(e.deptno=d.deptno);

6. 左外连接

   SELECT * FROM emp e LEFT OUTER JOIN dept d ON(e.deptno=d.deptno);

7. 右外连接

   SELECT * FROM emp e FULL OUTER JOIN dept d ON(e.deptno=d.deptno);

六. 数据的集合运算

数据的集合操作指的是查询结果的操作。

1. UNION(并集)：返回若干个查询结果的全部内容，但是重复元祖不显示
2. UNION ALL(并集)：返回若干个查询结果的全部内容，重复元祖也会显示
3. MINUS(差集)：返回若干个查询结果中的不同部分
4. INTERSECT(交集)：返回若干个结果中的相同部分

例子:

SELECT * FROM dept
  UNION
SELECT * FROM dept WHERE deptno=10;

注：在以后进行查询操作编写过程中，建议尽量使用UNION或UNION ALL来代替OR

范例：

（查询所有销售人员和办事人员的信息）

SELECT * FROM emp WHERE job='SALESMAN' OR job='CLERK';

或者

SELECT * FROM emp WHERE job IN('SALESMAN', 'CLEKT');

更改为：(改成这样，效率更高)

SELECT * FROM emp WHERE job='SALESMAN' 
  UNION
SELECT * FROM emp WHERE job='CLERK';