1. 什么是連接查詢
    1. 查詢的時(shí)候只從一張表中查詢叫做單表查詢
    2. 在實(shí)際開發(fā)中數(shù)據(jù)并不是存儲(chǔ)在一張表中,是同時(shí)存儲(chǔ)到多張表中的,
    3.這些表和表之間存在關(guān)系,我們查詢查詢的時(shí)候通常需要將多個(gè)表聯(lián)合起來(lái)查詢，并取得數(shù)據(jù)，這種查詢叫做連接查詢或者跨表查詢搪泳！
2. 連接查詢的時(shí)候,通常會(huì)給表取別名途茫，這樣效率更高
3. 案例 ：查詢所有員工和對(duì)應(yīng)的部門
// emp 員工表  dept 部門表
// SQL 92 語(yǔ)法 內(nèi)連接 等值連接
mysql> select e.ename,d.dname from emp e,dept d where e.deptno = d.deptno;
// SQL 99 語(yǔ)法 內(nèi)連接 等值連接  表連接獨(dú)立斩个，結(jié)構(gòu)更清晰
mysql> select e.ename,d.dname from emp e join dept d on e.deptno = d.deptno;
+---------+-------------+
| ename   | dname       |
+---------+-------------+
| SIMITH  | RESEARCHING |
| ALLEN   | SALES       |
| WARD    | SALES       |
| JONES   | RESEARCHING |
| MARTIN  | SALES       |
| BLAKE   | SALES       |
| CLARK   | ACCOUNTING  |
| SCOTT   | RESEARCHING |
| KING    | ACCOUNTING  |
| TURNER  | SALES       |
| ADAMS   | RESEARCHING |
| JAMES   | SALES       |
| FORD    | RESEARCHING |
| MILLER  | ACCOUNTING  |
+---------+-------------+
14 rows in set (0.00 sec)
4.案例 ：找出每個(gè)一個(gè)員工對(duì)應(yīng)的工資等級(jí) 要求顯示員工名字 員工薪水 員工等級(jí)
// emp 員工表  salgrade 等級(jí)表
mysql> select e.ename,e.sal ,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;
+---------+------+-------+
| ename   | sal  | grade |
+---------+------+-------+
| SIMITH  |  800 |     1 |
| ALLEN   | 1600 |     3 |
| WARD    | 1250 |     2 |
| JONES   | 2975 |     4 |
| MARTIN  | 1250 |     2 |
| BLAKE   | 2850 |     4 |
| CLARK   | 2450 |     4 |
| SCOTT   | 3000 |     4 |
| KING    | 5000 |     5 |
| TURNER  | 1500 |     3 |
| ADAMS   | 1100 |     1 |
| JAMES   |  950 |     1 |
| FORD    | 3000 |     4 |
| MILLER  | 1300 |     2 |
+---------+------+-------+
14 rows in set (0.00 sec)
5. 找出每個(gè)員工的上級(jí)領(lǐng)導(dǎo)羽德，要求顯示員工名字以及對(duì)應(yīng)的領(lǐng)導(dǎo)名字
// SQL 99 語(yǔ)法 內(nèi)連接中的自連接   把一個(gè)表看成兩個(gè)表查詢
mysql> select a.ename empname,b.ename leadername from emp a join emp b on a.mgr = b.empno;
+---------+------------+
| empname | leadername |
+---------+------------+
| SIMITH  | FORD       |
| ALLEN   | BLAKE      |
| WARD    | BLAKE      |
| JONES   | KING       |
| MARTIN  | BLAKE      |
| BLAKE   | KING       |
| CLARK   | KING       |
| SCOTT   | JONES      |
| TURNER  | BLAKE      |
| ADAMS   | SCOTT      |
| JAMES   | BLAKE      |
| FORD    | JONES      |
| MILLER  | CLARK      |
+---------+------------+
13 rows in set (0.00 sec)
6.案例 ：找出每個(gè)員工對(duì)應(yīng)的部門，要求部門名字全部顯示
// 外連接把一張表的記錄全部查詢出來(lái),對(duì)方表沒(méi)有匹配的用null表示
// 把join 左邊全部查詢出來(lái)使用 left 左連接 腾仅，如果是右邊 使用right 有連接
// 右外連接 查詢     （同時(shí)還有左外連接）
mysql> select e.ename,d.dname from emp e right join dept d on e.deptno = d.deptno;
+---------+-------------+
| ename   | dname       |
+---------+-------------+
| SIMITH  | RESEARCHING |
| ALLEN   | SALES       |
| WARD    | SALES       |
| JONES   | RESEARCHING |
| MARTIN  | SALES       |
| BLAKE   | SALES       |
| CLARK   | ACCOUNTING  |
| SCOTT   | RESEARCHING |
| KING    | ACCOUNTING  |
| TURNER  | SALES       |
| ADAMS   | RESEARCHING |
| JAMES   | SALES       |
| FORD    | RESEARCHING |
| MILLER  | ACCOUNTING  |
| NULL    | OPERATIONS  |
+---------+-------------+
15 rows in set (0.01 sec)
7. 案例 ：找出每個(gè)員工對(duì)應(yīng)的部門名稱临谱，以及員工對(duì)應(yīng)的薪水等級(jí)璃俗，要求顯示員工名字 部門名字 薪水等級(jí)
// 三張表查詢(emp) (dept) (salgrade)
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;
// emp 先和 dept 比較，然后 emp 再和 salgrade 比較
+---------+-------------+-------+
| ename   | dname       | grade |
+---------+-------------+-------+
| SIMITH  | RESEARCHING |     1 |
| ALLEN   | SALES       |     3 |
| WARD    | SALES       |     2 |
| JONES   | RESEARCHING |     4 |
| MARTIN  | SALES       |     2 |
| BLAKE   | SALES       |     4 |
| CLARK   | ACCOUNTING  |     4 |
| SCOTT   | RESEARCHING |     4 |
| KING    | ACCOUNTING  |     5 |
| TURNER  | SALES       |     3 |
| ADAMS   | RESEARCHING |     1 |
| JAMES   | SALES       |     1 |
| FORD    | RESEARCHING |     4 |
| MILLER  | ACCOUNTING  |     2 |
+---------+-------------+-------+
14 rows in set (0.00 sec)