三张表如何连接?
案例:找出每一个员工的部门名称以及工资等级
员工表:e表
mysql> select empno,ename,deptno,sal from emp;
+-------+--------+--------+---------+
| empno | ename | deptno | sal |
+-------+--------+--------+---------+
| 7369 | SMITH | 20 | 800.00 |
| 7499 | ALLEN | 30 | 1600.00 |
| 7521 | WARD | 30 | 1250.00 |
| 7566 | JONES | 20 | 2975.00 |
| 7654 | MARTIN | 30 | 1250.00 |
| 7698 | BLAKE | 30 | 2850.00 |
| 7782 | CLARK | 10 | 2450.00 |
| 7788 | SCOTT | 20 | 3000.00 |
| 7839 | KING | 10 | 5000.00 |
| 7844 | TURNER | 30 | 1500.00 |
| 7876 | ADAMS | 20 | 1100.00 |
| 7900 | JAMES | 30 | 950.00 |
| 7902 | FORD | 20 | 3000.00 |
| 7934 | MILLER | 10 | 1300.00 |
+-------+--------+--------+---------+
14 rows in set (0.02 sec)
部门表:d表
mysql> select * from dept;
+--------+------------+----------+
| deptno | dname | loc |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+--------+------------+----------+
4 rows in set (0.00 sec)
工资等级:s表
mysql> select * from salgrade;
+-------+-------+-------+
| grade | losal | hisal |
+-------+-------+-------+
| 1 | 700 | 1200 |
| 2 | 1201 | 1400 |
| 3 | 1401 | 2000 |
| 4 | 2001 | 3000 |
| 5 | 3001 | 9999 |
+-------+-------+-------+
5 rows in set (0.00 sec)
注意,解释一下
...
A
join
B
join
C
on
...
表示:A表和B表先进行表连接,连接之后A表继续和C表进行连接。
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;
执行sql
mysql> 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;
+--------+------------+-------+
| ename | dname | grade |
+--------+------------+-------+
| SMITH | RESEARCH | 1 |
| ADAMS | RESEARCH | 1 |
| JAMES | SALES | 1 |
| MILLER | ACCOUNTING | 2 |
| WARD | SALES | 2 |
| MARTIN | SALES | 2 |
| ALLEN | SALES | 3 |
| TURNER | SALES | 3 |
| CLARK | ACCOUNTING | 4 |
| JONES | RESEARCH | 4 |
| SCOTT | RESEARCH | 4 |
| FORD | RESEARCH | 4 |
| BLAKE | SALES | 4 |
| KING | ACCOUNTING | 5 |
+--------+------------+-------+
14 rows in set (0.00 sec)
案例:找出每一个员工的部门名称,工资等级、以及上级领导?
外连接的方式
select
e.ename '员工',d.dname,e1.ename '领导'
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 e1
on
e.mgr = e1.empno;
sql 执行
mysql> select
-> e.ename '员工',d.dname,e1.ename '领导'
-> 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 e1
-> on
-> e.mgr = e1.empno;
+--------+------------+-------+
| 员工 | dname | 领导 |
+--------+------------+-------+
| SMITH | RESEARCH | FORD |
| ADAMS | RESEARCH | SCOTT |
| JAMES | SALES | BLAKE |
| MILLER | ACCOUNTING | CLARK |
| WARD | SALES | BLAKE |
| MARTIN | SALES | BLAKE |
| ALLEN | SALES | BLAKE |
| TURNER | SALES | BLAKE |
| CLARK | ACCOUNTING | KING |
| JONES | RESEARCH | KING |
| SCOTT | RESEARCH | JONES |
| FORD | RESEARCH | JONES |
| BLAKE | SALES | KING |
| KING | ACCOUNTING | NULL |
+--------+------------+-------+
14 rows in set (0.02 sec)