1. 多表查詢
專案開發中，在進行資料庫表結構設計時，會根據業務需求及業務模組之間的關係，分析並設計表結構，由於業務之間相互關聯，所以各個表結構之間也存在著各種聯絡，基本上分為三種：
- 一對多(多對一)
- 多對多
- 一對一
2. 分類
-
連線查詢
-
內連線：相當於查詢A、B交集部分資料
-
外連線：
-
左外連線：查詢左表所有資料，以及兩張表交集部分資料
-
右外連線：查詢右表所有資料，以及兩張表交集部分資料
-
自連線：當前表與自身的連線查詢，自連線必須使用表別名
-
-
子查詢
3. 內連線
內連線的語法分為兩種: 隱式內連線、顯式內連線。先來學習一下具體的語法結構。
- 隱式內連線
SELECT 欄位列表 FROM 表1 , 表2 WHERE 條件 ... ;
- 顯式內連線
SELECT 欄位列表 FROM 表1 [ INNER ] JOIN 表2 ON 連線條件 ... ;
案例:
- 查詢每一個員工的姓名 , 及關聯的部門的名稱 (隱式內連線實現)
表結構: emp , dept
連線條件: emp.dept_id = dept.id
select emp.name , dept.name from emp , dept where emp.dept_id = dept.id ;
-- 為每一張表起別名,簡化SQL編寫
select e.name,d.name from emp e , dept d where e.dept_id = d.id;
- 查詢每一個員工的姓名 , 及關聯的部門的名稱 (顯式內連線實現) --- INNER JOIN ... ON ...
表結構: emp , dept
連線條件: emp.dept_id = dept.id
select e.name, d.name from emp e inner join dept d on e.dept_id = d.id;
-- 為每一張表起別名,簡化SQL編寫
select e.name, d.name from emp e join dept d on e.dept_id = d.id;
表的別名:
- tablea as 別名1 , tableb as 別名2 ;
- tablea 別名1 , tableb 別名2 ;
注意事項:
一旦為表起了別名，就不能再使用表名來指定對應的欄位了，此時只能夠使用別名來指定欄位。
4. 外連線
- 左外連線
SELECT 欄位列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 條件 ... ;
左外連線相當於查詢表1(左表)的所有資料，當然也包含表1和表2交集部分的資料。
- 右外連線
SELECT 欄位列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 條件 ... ;
右外連線相當於查詢表2(右表)的所有資料，當然也包含表1和表2交集部分的資料。
案例:
- 查詢emp表的所有資料, 和對應的部門資訊
由於需求中提到，要查詢emp的所有資料，所以是不能內連線查詢的，需要考慮使用外連線查詢。
表結構: emp, dept
連線條件: emp.dept_id = dept.id
select e.*, d.name from emp e left outer join dept d on e.dept_id = d.id;
select e.*, d.name from emp e left join dept d on e.dept_id = d.id;
- 查詢dept表的所有資料, 和對應的員工資訊(右外連線)
由於需求中提到，要查詢dept表的所有資料，所以是不能內連線查詢的，需要考慮使用外連線查詢。
表結構: emp, dept
連線條件: emp.dept_id = dept.id
select d.*, e.* from emp e right outer join dept d on e.dept_id = d.id;
select d.*, e.* from dept d left outer join emp e on e.dept_id = d.id;
注意事項：
左外連線和右外連線是可以相互替換的，只需要調整在連線查詢時SQL中，表結構的先後順序就可以了。而我們在日常開發使用時，更偏向於左外連線。
5. 自連線
5.1 自連線查詢
自連線查詢，顧名思義，就是自己連線自己，也就是把一張表連線查詢多次。我們先來學習一下自連線的查詢語法：
SELECT 欄位列表 FROM 表A 別名A JOIN 表A 別名B ON 條件 ... ;
而對於自連線查詢，可以是內連線查詢，也可以是外連線查詢。
案例:
- 查詢員工 及其 所屬領導的名字
表結構: emp
select a.name , b.name from emp a , emp b where a.managerid = b.id;
- 查詢所有員工 emp 及其領導的名字 emp , 如果員工沒有領導, 也需要查詢出來
表結構: emp a , emp b
select a.name '員工', b.name '領導' from emp a left join emp b on a.managerid = b.id;
注意事項:
在自連線查詢中，必須要為表起別名，要不然我們不清楚所指定的條件、返回的欄位，到底是哪一張表的欄位。
5.2 聯合查詢
對於 union 查詢，就是把多次查詢的結果合併起來，形成一個新的查詢結果集。
SELECT 欄位列表 FROM 表A ...
UNION [ ALL ]
SELECT 欄位列表 FROM 表B ....;
-
對於聯合查詢的多張表的列數必須保持一致，欄位型別也需要保持一致。
-
union all 會將全部的資料直接合並在一起，union 會對合並之後的資料去重。
案例:
- 將薪資低於 5000 的員工 , 和 年齡大於 50 歲的員工全部查詢出來.
當前對於這個需求，我們可以直接使用多條件查詢，使用邏輯運算子 or 連線即可。 那這裡呢，我們也可以透過 union / union all 來聯合查詢.
select * from emp where salary < 5000
union all
select * from emp where age > 50;
union all查詢出來的結果，僅僅進行簡單的合併，並未去重。
select * from emp where salary < 5000
union
select * from emp where age > 50;
union 聯合查詢，會對查詢出來的結果進行去重處理。
注意：
如果多條查詢語句查詢出來的結果，欄位數量不一致，在進行 union / union all聯合查詢時，將會報錯。如：
6. 子查詢
6.1 概述
- 概念
SQL語句中巢狀SELECT語句，稱為巢狀查詢，又稱子查詢。
SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2 );
子查詢外部的語句可以是INSERT / UPDATE / DELETE / SELECT 的任何一個。
- 分類
根據子查詢結果不同，分為：
-
標量子查詢（子查詢結果為單個值）
-
列子查詢 (子查詢結果為一列)
-
行子查詢 (子查詢結果為一行)
-
表子查詢 (子查詢結果為多行多列)
根據子查詢位置，分為：
-
WHERE之後
-
FROM之後
-
SELECT之後
6.2 標量子查詢
子查詢返回的結果是單個值（數字、字串、日期等），最簡單的形式，這種子查詢稱為標量子查詢。
常用的運算子：= <> > >= < <=
案例:
- 查詢 "銷售部" 的所有員工資訊
完成這個需求時，我們可以將需求分解為兩步：
①. 查詢 "銷售部" 部門ID
select id from dept where name = '銷售部';
②. 根據 "銷售部" 部門ID, 查詢員工資訊
select * from emp where dept_id = (select id from dept where name = '銷售部');
- 查詢在 "方東白" 入職之後的員工資訊
完成這個需求時，我們可以將需求分解為兩步：
①. 查詢 方東白 的入職日期
select entrydate from emp where name = '方東白';
②. 查詢指定入職日期之後入職的員工資訊
select * from emp where
entrydate > (select entrydate from emp where name = '方東白');
6.3 列子查詢
子查詢返回的結果是一列（可以是多行），這種子查詢稱為列子查詢。
常用的運算子：IN 、NOT IN 、 ANY 、SOME 、 ALL
案例:
- 查詢 "銷售部" 和 "市場部" 的所有員工資訊
分解為以下兩步:
①. 查詢 "銷售部" 和 "市場部" 的部門ID
select id from dept where name = '銷售部' or name = '市場部';
②. 根據部門ID, 查詢員工資訊
select * from emp where dept_id in
(select id from dept where name = '銷售部' orname = '市場部');
- 查詢比 財務部 所有人工資都高的員工資訊
分解為以下兩步:
①. 查詢所有 財務部 人員工資
select id from dept where name = '財務部';
select salary from emp where dept_id =
(select id from dept where name = '財務部');
②. 比 財務部 所有人工資都高的員工資訊
select * from emp where salary > all ( select salary from emp where dept_id =
(select id from dept where name = '財務部') );
- 查詢比研發部其中任意一人工資高的員工資訊
分解為以下兩步:
①. 查詢研發部所有人工資
select salary from emp where dept_id = (select id from dept where name = '研發部');
②. 比研發部其中任意一人工資高的員工資訊
select * from emp where salary > any ( select salary from emp where dept_id =
(select id from dept where name = '研發部') );
6.4 行子查詢
子查詢返回的結果是一行（可以是多列），這種子查詢稱為行子查詢。
常用的運算子：= 、<> 、IN 、NOT IN
案例:
- 查詢與 "張無忌" 的薪資及直屬領導相同的員工資訊 ;
這個需求同樣可以拆解為兩步進行:
①. 查詢 "張無忌" 的薪資及直屬領導
select salary, managerid from emp where name = '張無忌';
②. 查詢與 "張無忌" 的薪資及直屬領導相同的員工資訊 ;
select * from emp where (salary,managerid) = (select salary, managerid from emp
where name = '張無忌');
6.5 表子查詢
子查詢返回的結果是多行多列，這種子查詢稱為表子查詢。
常用的運算子：IN
案例:
- 查詢與 "鹿杖客" , "宋遠橋" 的職位和薪資相同的員工資訊
分解為兩步執行:
①. 查詢 "鹿杖客" , "宋遠橋" 的職位和薪資
select job, salary from emp where name = '鹿杖客' or name = '宋遠橋';
②. 查詢與 "鹿杖客" , "宋遠橋" 的職位和薪資相同的員工資訊
select * from emp where (job,salary) in ( select job, salary from emp where name =
'鹿杖客' or name = '宋遠橋' );
- 查詢入職日期是 "2006-01-01" 之後的員工資訊 , 及其部門資訊
分解為兩步執行:
①. 入職日期是 "2006-01-01" 之後的員工資訊
select * from emp where entrydate > '2006-01-01';
②. 查詢這部分員工, 對應的部門資訊;
select e.*, d.* from (select * from emp where entrydate > '2006-01-01') e left
join dept d on e.dept_id = d.id ;
7 多表查詢案例
這裡主要涉及到的表就三張： emp 員工表、 dept 部門表、 salgrade 薪資等級表。
- 查詢員工的姓名、年齡、職位、部門資訊 （隱式內連線）
表: emp , dept
連線條件: emp.dept_id = dept.id
select e.name , e.age , e.job , d.name from emp e , dept d
where e.dept_id = d.id;
- 查詢年齡小於30歲的員工的姓名、年齡、職位、部門資訊（顯式內連線）
表: emp , dept
連線條件: emp.dept_id = dept.id
select e.name , e.age , e.job , d.name from emp e
inner join dept d on e.dept_id =d.id where e.age < 30;
- 查詢擁有員工的部門ID、部門名稱
表: emp , dept
連線條件: emp.dept_id = dept.id
select distinct d.id , d.name from emp e , dept d
where e.dept_id = d.id;
- 查詢所有年齡大於40歲的員工, 及其歸屬的部門名稱; 如果員工沒有分配部門, 也需要展示出來(外連線)
表: emp , dept
連線條件: emp.dept_id = dept.id
select e.*, d.name from emp e left join dept d
on e.dept_id = d.id where e.age >40 ;
- 查詢所有員工的工資等級
表: emp , salgrade
連線條件 : emp.salary >= salgrade.losal and emp.salary <= salgrade.hisal
-- 方式一
select e.* , s.grade , s.losal, s.hisal from emp e , salgrade s
where e.salary >=s.losal and e.salary <= s.hisal;
-- 方式二
select e.* , s.grade , s.losal, s.hisal from emp e , salgrade s
where e.salarybetween s.losal and s.hisal;
- 查詢 "研發部" 所有員工的資訊及工資等級
表: emp , salgrade , dept
連線條件 : emp.salary between salgrade.losal and salgrade.hisal ,
emp.dept_id = dept.id
查詢條件 : dept.name = '研發部'
select e.* , s.grade from emp e , dept d , salgrade s
where e.dept_id = d.id
and (e.salary between s.losal and s.hisal ) and d.name = '研發部';
- 查詢 "研發部" 員工的平均工資
表: emp , dept
連線條件 : emp.dept_id = dept.id
select avg(e.salary) from emp e, dept d
where e.dept_id = d.id and d.name = '研發部';
- 查詢工資比 "滅絕" 高的員工資訊。
①. 查詢 "滅絕" 的薪資
select salary from emp where name = '滅絕';
②. 查詢比她工資高的員工資料
select * from emp
where salary > ( select salary from emp where name = '滅絕' );
- 查詢比平均薪資高的員工資訊
①. 查詢員工的平均薪資
select avg(salary) from emp;
②. 查詢比平均薪資高的員工資訊
select * from emp where salary > ( select avg(salary) from emp );
- 查詢低於本部門平均工資的員工資訊
①. 查詢指定部門平均薪資
select avg(e1.salary) from emp e1 where e1.dept_id = 1;
select avg(e1.salary) from emp e1 where e1.dept_id = 2;
②. 查詢低於本部門平均工資的員工資訊
select * from emp e2
where e2.salary < ( select avg(e1.salary) from emp e1 where e1.dept_id = e2.dept_id );
- 查詢所有的部門資訊, 並統計部門的員工人數
select d.id, d.name , ( select count(*) from emp e where e.dept_id = d.id ) '人數' from dept d;
- 查詢所有學生的選課情況, 展示出學生名稱, 學號, 課程名稱
表: student , course , student_course
連線條件: student.id = student_course.studentid , course.id =
student_course.courseid
select s.name , s.no , c.name from student s , student_course sc , course c
where s.id = sc.studentid and sc.courseid = c.id ;