7、5_连接查询：操作多张表

员工表 e

mysql> select empno,ename,job,mgr,sal from emp;
+-------+--------+-----------+------+------+
| empno | ename | job | mgr | sal |
+-------+--------+-----------+------+------+
| 7369 | SMITH | CLERK | 7902 | 800 |
| 7499 | ALLEN | SALESMAN | 7698 | 1600 |
| 7521 | WARD | SALESMAN | 7698 | 1250 |
| 7566 | JONES | MANAGER | 7839 | 2975 |
| 7654 | MARTIN | SALESMAN | 7698 | 1250 |
| 7698 | BLAKE | MANAGER | 7839 | 2850 |
| 7782 | CLARK | MANAGER | 7839 | 2450 |
| 7788 | SCOTT | ANALYST | 7566 | 3000 |
| 7839 | KING | PRESIDENT | NULL | 5000 |
| 7844 | TURNER | SALESMAN | 7698 | 1500 |
| 7876 | ADAMS | CLERK | 7788 | 1100 |
| 7900 | JAMES | CLERK | 7698 | 950 |
| 7902 | FORD | ANALYST | 7566 | 3000 |
| 7934 | MILLER | CLERK | 7782 | 1300 |
+-------+--------+-----------+------+------+

部门表 d
select * form dept;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+

工资等级表 s
mysql> select *from salgrade;
+-------+-------+-------+
| grade | losal | hisal |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+

 

需求1：找出每一个员工的部门名称和工资等级

写SQL：

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

 -----------------------------------------------------------------------------------------------------------------------------------------

需求2：找出每一个员工的部门名称、工资等级、以及上级领导

用到自连接 模拟出一张领导表

领导表 f

mysql> select empno,ename from emp;

员工表的领导编号 = 领导表的员工编号
+-------+--------+
| empno | ename |
+-------+--------+
| 7369 | SMITH |
| 7499 | ALLEN |
| 7521 | WARD |
| 7566 | JONES |
| 7654 | MARTIN |
| 7698 | BLAKE |
| 7782 | CLARK |
| 7788 | SCOTT |
| 7839 | KING |
| 7844 | TURNER |
| 7876 | ADAMS |
| 7900 | JAMES |
| 7902 | FORD |
| 7934 | MILLER |
+-------+--------+ 

书写SQL语句

SELECT
e.ename as '姓名',d.dname as '部门名称',s.grade as '工资等级',f.ename as '领导'
FROM
emp e
JOIN
dept d
ON
e.deptno = d.deptno
JOIN
salgrade s
ON
e.sal BETWEEN s.losal AND s.hisal
left JOIN
emp f
ON
e.mgr = f.empno;

 

查询结果
+--------+--------------+--------------+--------+
| 姓名    | 部门名称      |    工资等级   |  领导   |
+--------+--------------+--------------+--------+
| SMITH  | RESEARCH     |            1 | FORD   |
| ALLEN  | SALES        |            3 | BLAKE  |
| WARD   | SALES        |            2 | BLAKE  |
| JONES  | RESEARCH     |            4 | KING   |
| MARTIN | SALES        |            2 | BLAKE  |
| BLAKE  | SALES        |            4 | KING   |
| CLARK  | ACCOUNTING   |            4 | KING   |
| SCOTT  | RESEARCH     |            4 | JONES  |
| KING   | ACCOUNTING   |            5 | NULL   |
| TURNER | SALES        |            3 | BLAKE  |
| ADAMS  | RESEARCH     |            1 | SCOTT  |
| JAMES  | SALES        |            1 | BLAKE  |
| FORD   | RESEARCH     |            4 | JONES  |
| MILLER | ACCOUNTING   |            2 | CLARK  |
+--------+--------------+--------------+--------+