一、引言

上幾篇關于多表查詢的基本幾個部分全部學習完了。

多表查詢的基本類型的查詢包括以下：

1、內(nèi)連接（隱式內(nèi)連接、顯示內(nèi)連接）：...?[INNER] JOIN ... ON 條件;?）

2、外連接（左外連接、右外連接）：... LEFT?/ RIGHT [OUTER] JOIN ... ON 條件;

3、自連接：表 A?別名 A?join 表 A?別名 B?ON 條件 ...;

4、聯(lián)合查詢：UNION(去重) / UNION ALL

5、子查詢（標量子查詢、列子查詢、行子查詢、表子查詢）

下面將通過12個案例來關于多表查詢操作的鞏固。這篇博客只有5個案例，剩下的后面博客寫。

二、案例

下面這些案例涵蓋了內(nèi)連接、外連接、自連接、子查詢以及它們的整合使用。

打開 DataGrip 工具完成以下這些需求。

（0）數(shù)據(jù)準備（員工表 emp 、部門表 dept 、薪資等級表 salgrade）

員工表：emp?

部門表：dept

薪資等級表：salgrade

（下面是創(chuàng)建表結(jié)構(gòu)、插入數(shù)據(jù)）

CREATE TABLE salgrade(grade INT COMMENT '薪資等級',losal INT COMMENT '該等級最低薪資',hisal INT COMMENT '該等級最高薪資'
)COMMENT '薪資等級表';INSERT INTO salgrade VALUES (1,0,3000);
INSERT INTO salgrade VALUES (2,3001,5000);
INSERT INTO salgrade VALUES (3,5001,8000);
INSERT INTO salgrade VALUES (4,8001,10000);
INSERT INTO salgrade VALUES (5,10001,15000);
INSERT INTO salgrade VALUES (6,15001,20000);
INSERT INTO salgrade VALUES (7,20001,25000);
INSERT INTO salgrade VALUES (8,25001,30000);

涉及到的全部表的預覽

（1）查詢員工的姓名、年齡、職位、部門名稱（隱式內(nèi)連接）

用到隱式內(nèi)連接，多張表用逗號隔開。要注意笛卡爾積的問題（消除無效查詢）

SELECT e.name,e.age,e.job,d.name FROM emp AS e,dept AS d WHERE (e.dept_id = d.id);

（2）查詢年齡小于30歲的員工的姓名、年齡、職位、部門信息（顯式內(nèi)連接）

SELECT e.name,e.age,e.job,d.name FROM emp AS e INNER JOIN dept AS d ON e.dept_id = d.id WHERE e.age < 30;

（3）查詢擁有員工的部門ID、部門名稱

（注意：這里在部門表中 "人事部" 是沒有員工的）

1、先查詢擁有部門id的員工中的部門信息，再去重復數(shù)據(jù)。

SELECT dept.id,dept.name FROM emp,dept WHERE emp.dept_id=dept.id;

2、去除重復數(shù)據(jù)，就要用到一個關鍵字：distinct（DISTINCT）

SELECT DISTINCT dept.id,dept.name FROM emp,dept WHERE emp.dept_id=dept.id;

（4）查詢所有年齡大于40歲的員工，及其歸屬的部門名稱。如果員工沒有分配部門，也需要展示出來

這里注意：一看就要用到外連接（避免有些字段的 NULL 影響查詢的結(jié)果）

SELECT e.*,d.name FROM emp e LEFT OUTER JOIN  dept d ON e.dept_id = d.id WHERE age >40;

也可以用昨天學的子查詢（表子查詢）

SELECT e.*,d.name FROM (SELECT * FROM emp WHERE age>40) AS e LEFT JOIN dept AS d ON e.dept_id= d.id;

（5）查詢所有員工的工資等級

涉及到兩張表：員工表 emp，薪資等級表 salgrade

連接條件：salary 與 薪資的范圍 losal 、hisal

（emp.salary >=?salgrade.losal AND emp.salary <=?salgrade.hisal ）

SELECT e.*,s.grade,s.losal as '最低工資' , s.hisal as '最高工資' FROM emp e ,salgrade s WHERE e.salary >= s.losal AND e.salary <= s.hisal;

對于這題還有一種SQL語句的寫法。

（用 BETWEEN ... AND....）

SELECT e.*,s.grade,s.losal as '最低工資' , s.hisal as '最高工資' FROM emp e ,salgrade s WHERE e.salary BETWEEN s.losal AND s.hisal;

后面的案例下一篇博客學習。