10、多表查询基本语法

4.6.1、多表查询

       之前在查询的时候查询的都是一张表的数据，如果现在使用一张以上的表，则就称为多表查询，多表查询语法如下：

SELECT {DISTINCT}*｜ 查询列1 别名1，查询列2 别名2，… FROM 表名称1别名1，表名称2 别名2，… {WHERE 条件(s)} {ORDER BY 排序的字段 ASC|DESC，排序的字段 ASC|DESC，…}

范例：下面使用了多表查询，同时查询emp和dept表

SELECT * FROM emp,dept;

       以上的查询使用了两张表进行同时查询。

       但是，从查询结果上可以发现，返回的数据是56条，但是emp表一共才14条，dept表一共才4条。

范例：查询emp表中的记录数

SELECT COUNT(*) FROM emp;

范例：查询dept表中的记录数

SELECT COUNT(*) FROM dept;

       56条记录 = emp表的14条记录 * dept表的4条记录。

       在使用多表查询的时候会产生笛卡尔积。如果表的数据越多，那么笛卡尔积就会越大。如果现在假如设有5张表，每张表100000条记录，10000⁵。所以多表查询在开发中基本上是不建议过多的使用。

       要想去掉笛卡尔积则必须使用字段进行关联的操作。

       在emp表中存在一个deptno的字段，在dept表中也存在deptno的字段，而且可以发现emp表中的deptno的取值范围都在dept表的deptno规定的范围之中。

       emp中的deptno=dept中的deptno，属于关联字段。

       在多表查询中加入WHERE语句，就可以消除掉笛卡尔积。

范例：修改之前的查询操作

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

       此时，记录只有14行了，消除掉了笛卡尔积。

       但是此时又存在一个新的问题，如果现在表名称过长的话？所有在使用的时候一般会为表起别名。

SELECT * FROM emp e,dept d WHERE e.deptno=d.deptno;

       如果要使用到多表查询的时候，最好指定别名。

范例：要求查询出雇员的编号、雇员的姓名、部门的编号、部门名称及部门位置

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

范例：要求查询出每个雇员的姓名、工作、雇员的直接上级领导的姓名

       ·在emp表中的mgr字段始终没有使用过，其表示一个雇员的上级领导的编号。那么如果现在要想查询一个雇员的上级领导，则肯定要将emp表与emp表自己进行关联。

SELECT e.ename,e.job,m.ename FROM emp e,emp m WHERE e.mgr=m.empno;

范例：要求进一步扩展之前的程序，将雇员所在的部门名称同时列出

       ·部门名称在dept表中存在定义。

SELECT e.ename,e.job,m.ename,d.dname FROM emp e,emp m,dept d WHERE e.mgr=m.empno AND e.deptno=d.deptno;

思考：现在要求查询出每个雇员的姓名、工资、部门名称，工资在公司的等级（salgrade），及其领导的姓名及工资所在公司的等级。

       ·先确定工资等级表的内容（查询salgrade表）

SELECT * FROM salgrade;

       ·分解：查询出每个雇员的姓名、工资、部门名称，工资在公司的等级（salgrade）

SELECT e.ename,e.sal,d.dname,s.grade FROM emp e,dept d,salgrade s WHERE e.deptno=d.deptno AND e.sal BETWEEN s.losal AND s.hisal;

       此时，雇员的工资等级已经求出来了。

       ·分解：其领导的姓名及工资所在公司的等级

SELECT e.ename,e.sal,d.dname,s.grade,m.ename,ms.grade FROM emp e,dept d,salgrade s,emp m,salgrade ms WHERE e.deptno=d.deptno AND        e.sal BETWEEN s.losal AND s.hisal AND e.mgr=m.empno        AND m.sal BETWEEN ms.losal AND ms.hisal;

进一步思考：现在要求按照以下的样式显示工资等级

       ·1：第五等工资

       ·2：第四等工资

·3：第三等工资

·4：第二等工资 

·5：第一等工资

此时，肯定只能使用DECODE()函数

SELECT e.ename,e.sal,d.dname,        DECODE(s.grade,1,'第五等工资',2,'第四等工资',3,'第三等工资',                      4,'第二等工资',     5,'第一等工资'),m.ename,        DECODE(ms.grade,1,'第五等工资',    2,'第四等工资',     3,'第三等工资',                      4,'第二等工资',     5,'第一等工资') FROM emp e,dept d,salgrade s,emp m,salgrade ms WHERE e.deptno=d.deptno AND        e.sal BETWEEN s.losal AND s.hisal AND e.mgr=m.empno        AND m.sal BETWEEN ms.losal AND ms.hisal;

4.6.2、左、右连接（重点）

现在在dept表中存在四条数据：

SELECT * FROM dept;

现在将emp和dept表关联查询，查询一下指定的字段

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

       部门表中一共存在四个部门的信息，但是此时只列出了3个。因为在雇员表中并没有指定40部门的雇员。

SELECT e.empno,e.ename,d.deptno,d.dname,d.loc FROM emp e,dept d WHERE e.deptno(+)=d.deptno;

       可以发现，此时40部门已经出现了。所以，此时就使用到了右连接，证明以下的规律：

              ·(+)在=左边表示右连接

              ·(+)在=右边表示左连接

SELECT e.empno,e.ename,d.deptno,d.dname,d.loc FROM emp e,dept d WHERE e.deptno=d.deptno(+);

       以上的（+）写与不写都是一样的，肯定是无法显示40部门信息的。

       左、右连接在一般的开发中使用较多，实际上之前的查找雇员姓名及每一位雇员领导的时候就应该是用左、右连接了。

范例：查找雇员的编号、姓名及其领导的编号、姓名

SELECT e.empno,e.ename,m.empno,m.ename FROM emp e,emp m WHERE e.mgr=m.empno;

       发现查询出来的结果缺少了以下的一行记录：

7839 KING     PRESIDENT      17-11月-81           5000                    10

       因为其是最高领导，所以mgr为空。

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

       加入左连接之后可以发现KING出现了。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/21817401/viewspace-621580/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/21817401/viewspace-621580/