mysql-210712-02-CSDN博客

本文通过案例展示了如何使用SQL进行三张以上表格的连接查询，包括左连接和内连接，以获取员工的部门名称、工资等级及上级领导信息。通过查询emp, dept, salgrades和emp（自身）表格，揭示了数据库查询的复杂性和实用性。

摘要由CSDN通过智能技术生成

mysql-210712-02

3张以上的表查询

3张以上的表查询

....
	表A
join
	表B
on
	...
join
	表C
on
	...
	
表示：A表和B表先进性表连接，连接之后A表继续和C表进行连接

案例1

// 找出每一个员工的部门名称以及工资等级
mysql> select
    -> e.empno,e.ename,d.dname,e.sal,s.grade
    -> from
    -> emp e
    -> join
    -> dept d
    -> on
    -> e.deptno = d.deptno
    -> left join
    -> salgrade s
    -> on
    -> e.sal between s.losal and s.hisal;
+-------+--------+------------+---------+-------+
| empno | ename  | dname      | sal     | grade |
+-------+--------+------------+---------+-------+
|  7369 | SMITH  | RESEARCH   |  800.00 |     1 |
|  7499 | ALLEN  | SALES      | 1600.00 |     3 |
|  7521 | WARD   | SALES      | 1250.00 |     2 |
|  7566 | JONES  | RESEARCH   | 2975.00 |     4 |
|  7654 | MARTIN | SALES      | 1250.00 |     2 |
|  7698 | BLAKE  | SALES      | 2850.00 |     4 |
|  7782 | CLARK  | ACCOUNTING | 2450.00 |     4 |
|  7788 | SCOTT  | RESEARCH   | 3000.00 |     4 |
|  7839 | KING   | ACCOUNTING | 5000.00 |     5 |
|  7844 | TURNER | SALES      | 1500.00 |     3 |
|  7876 | ADAMS  | RESEARCH   | 1100.00 |     1 |
|  7900 | JAMES  | SALES      |  950.00 |     1 |
|  7902 | FORD   | RESEARCH   | 3000.00 |     4 |
|  7934 | MILLER | ACCOUNTING | 1300.00 |     2 |
+-------+--------+------------+---------+-------+
14 rows in set (0.00 sec)

案例2

// 找出每一个员工的部门名称、工资等级，以及上级领导
select 
	yuangong.empno '员工编号',yuangong.ename '员工姓名',
	d.dname '部门名称',
	yuangong.mgr '上级领导编号',lingdao.ename '上级领导姓名',
	yuangong.sal '员工薪资',s.grade '员工薪资等级'
from 
	emp yuangong
join
	dept d
on 
	yuangong.deptno = d.deptno
join 
	salgrade s
on
	yuangong.sal between s.losal and s.hisal
left join
	emp lingdao
on
	yuangong.mgr = lingdao.empno;

分析
// 1.
mysql> select
    -> empno,ename,deptno,mgr,sal
    -> from
    -> emp;
 +-------+--------+--------+------+---------+
| empno | ename  | deptno | mgr  | sal     |
+-------+--------+--------+------+---------+
|  7369 | SMITH  |     20 | 7902 |  800.00 |
|  7499 | ALLEN  |     30 | 7698 | 1600.00 |
|  7521 | WARD   |     30 | 7698 | 1250.00 |
|  7566 | JONES  |     20 | 7839 | 2975.00 |
|  7654 | MARTIN |     30 | 7698 | 1250.00 |
|  7698 | BLAKE  |     30 | 7839 | 2850.00 |
|  7782 | CLARK  |     10 | 7839 | 2450.00 |
|  7788 | SCOTT  |     20 | 7566 | 3000.00 |
|  7839 | KING   |     10 | NULL | 5000.00 |
|  7844 | TURNER |     30 | 7698 | 1500.00 |
|  7876 | ADAMS  |     20 | 7788 | 1100.00 |
|  7900 | JAMES  |     30 | 7698 |  950.00 |
|  7902 | FORD   |     20 | 7566 | 3000.00 |
|  7934 | MILLER |     10 | 7782 | 1300.00 |
+-------+--------+--------+------+---------+
14 rows in set (0.00 sec)
    
// 2.
mysql> select
    -> yuangong.empno,yuangong.ename,d.dname,yuangong.mgr,yuangong.sal
    -> from
    -> emp yuangong
    -> join
    -> dept d
    -> on
    -> yuangong.deptno = d.deptno;
+-------+--------+------------+------+---------+
| empno | ename  | dname      | mgr  | sal     |
+-------+--------+------------+------+---------+
|  7369 | SMITH  | RESEARCH   | 7902 |  800.00 |
|  7499 | ALLEN  | SALES      | 7698 | 1600.00 |
|  7521 | WARD   | SALES      | 7698 | 1250.00 |
|  7566 | JONES  | RESEARCH   | 7839 | 2975.00 |
|  7654 | MARTIN | SALES      | 7698 | 1250.00 |
|  7698 | BLAKE  | SALES      | 7839 | 2850.00 |
|  7782 | CLARK  | ACCOUNTING | 7839 | 2450.00 |
|  7788 | SCOTT  | RESEARCH   | 7566 | 3000.00 |
|  7839 | KING   | ACCOUNTING | NULL | 5000.00 |
|  7844 | TURNER | SALES      | 7698 | 1500.00 |
|  7876 | ADAMS  | RESEARCH   | 7788 | 1100.00 |
|  7900 | JAMES  | SALES      | 7698 |  950.00 |
|  7902 | FORD   | RESEARCH   | 7566 | 3000.00 |
|  7934 | MILLER | ACCOUNTING | 7782 | 1300.00 |
+-------+--------+------------+------+---------+
14 rows in set (0.00 sec)

// 3.
mysql> select
    -> yuangong.empno,yuangong.ename,d.dname,yuangong.mgr,yuangong.sal,s.grade
    -> from
    -> emp yuangong
    -> join
    -> dept d
    -> on
    -> yuangong.deptno = d.deptno
    -> join
    -> salgrade s
    -> on
    -> yuangong.sal between s.losal and s.hisal
    -> left join
    -> emp lingdao
    -> on
    -> yuangong.mgr = lingdao.empno;
+-------+--------+------------+------+---------+-------+
| empno | ename  | dname      | mgr  | sal     | grade |
+-------+--------+------------+------+---------+-------+
|  7369 | SMITH  | RESEARCH   | 7902 |  800.00 |     1 |
|  7499 | ALLEN  | SALES      | 7698 | 1600.00 |     3 |
|  7521 | WARD   | SALES      | 7698 | 1250.00 |     2 |
|  7566 | JONES  | RESEARCH   | 7839 | 2975.00 |     4 |
|  7654 | MARTIN | SALES      | 7698 | 1250.00 |     2 |
|  7698 | BLAKE  | SALES      | 7839 | 2850.00 |     4 |
|  7782 | CLARK  | ACCOUNTING | 7839 | 2450.00 |     4 |
|  7788 | SCOTT  | RESEARCH   | 7566 | 3000.00 |     4 |
|  7839 | KING   | ACCOUNTING | NULL | 5000.00 |     5 |
|  7844 | TURNER | SALES      | 7698 | 1500.00 |     3 |
|  7876 | ADAMS  | RESEARCH   | 7788 | 1100.00 |     1 |
|  7900 | JAMES  | SALES      | 7698 |  950.00 |     1 |
|  7902 | FORD   | RESEARCH   | 7566 | 3000.00 |     4 |
|  7934 | MILLER | ACCOUNTING | 7782 | 1300.00 |     2 |
+-------+--------+------------+------+---------+-------+


// 4.
mysql> select
    -> yuangong.empno '员工编号',yuangong.ename '员工姓名',
    -> d.dname '部门名称',
    -> yuangong.mgr '上级领导编号',lingdao.ename '上级领导姓名',
    -> yuangong.sal '员工薪资',s.grade '员工薪资等级'
    -> from
    -> emp yuangong
    -> join
    -> dept d
    -> on
    -> yuangong.deptno = d.deptno
    -> join
    -> salgrade s
    -> on
    -> yuangong.sal between s.losal and s.hisal
    -> left join
    -> emp lingdao
    -> on
    -> yuangong.mgr = lingdao.empno;

结果如下：
+--------------+--------------+--------------+-----------------+------------+-----------+----------------+
| 员工编号     | 员工姓名     | 部门名称       | 上级领导编号     | 上级领导姓名| 员工薪资      | 员工薪资等级 |
+--------------+--------------+--------------+--------------------+--------------------+--------------+--+
|         7369 | SMITH        | RESEARCH     |            7902 | FORD       |       800.00 |           1 |
|         7499 | ALLEN        | SALES        |            7698 | BLAKE      |      1600.00 |           3 |
|         7521 | WARD         | SALES        |            7698 | BLAKE      |      1250.00 |           2 |
|         7566 | JONES        | RESEARCH     |            7839 | KING       |      2975.00 |           4 |
|         7654 | MARTIN       | SALES        |            7698 | BLAKE      |      1250.00 |           2 |
|         7698 | BLAKE        | SALES        |            7839 | KING       |      2850.00 |           4 |
|         7782 | CLARK        | ACCOUNTING   |            7839 | KING       |      2450.00 |           4 |
|         7788 | SCOTT        | RESEARCH     |            7566 | JONES      |      3000.00 |           4 |
|         7839 | KING         | ACCOUNTING   |            NULL | NULL       |      5000.00 |           5 |
|         7844 | TURNER       | SALES        |            7698 | BLAKE      |      1500.00 |           3 |
|         7876 | ADAMS        | RESEARCH     |            7788 | SCOTT      |      1100.00 |           1 |
|         7900 | JAMES        | SALES        |            7698 | BLAKE      |       950.00 |           1 |
|         7902 | FORD         | RESEARCH     |            7566 | JONES      |      3000.00 |           4 |
|         7934 | MILLER       | ACCOUNTING   |            7782 | CLARK      |      1300.00 |           2 |
+--------------+--------------+--------------+-----------------+------------+--------------+-------------+