點選關注，我們共同每天進步一點點！
1 準備資料
create table department (
id int unsigned auto_increment primary key,
name char(12) not null unique # 部門名稱唯一
);
create table teacher(
id int unsigned auto_increment primary key,
name char(12) not null,
gender enum("male","famale") not null default "male",
age tinyint unsigned not null,
coaching_age tinyint unsigned not null, # 教齡
salary int unsigned not null,
dep_id int unsigned not null
);
insert into department(id,name) values
(100,"管理部"),
(200,"教學部"),
(300,"财務部"),
(500,"教務部");
insert into teacher(name,gender,age,coaching_age,salary,dep_id) values
("TeacherZhang","male",32,8,9000,200),
("TeacherLi","male",34,10,12000,200),
("TeacherYun","male",26,4,21000,100),
("TeacherZhou","famale",24,2,4000,300),
("TeacherZhao","famale",32,12,23000,100),
("TeacherYang","male",28,6,3000,300),
("TeacherWang","famale",22,1,3200,400);
資料說明：老師表中有個部門編号為400的TeacherWang老師，沒有對應的部門。部門表中有個編号為500的教務部，其中沒有包含老師。
2 查詢文法
SELECT DISTINCT(字段名1,字段名2) FROM 左表名 連接配接類型 JOIN 右表名
ON 連表條件
WHERE 篩選條件
GROUP BY 分組字段
HAVING 過濾條件
ORDER BY 排序字段 asc/desc
LIMIT 限制條數;
3 執行順序
在單表查詢的基礎上，多表查詢多了一些查詢的步驟，是以執行順序也與單表查詢有所不同。
1.通過from找到将要查詢的表(左表以及右表)，生成一張虛拟的笛卡爾積表
2.使用on來過濾出笛卡爾積虛拟表中需要保留的字段
3.根據連接配接類型 join來對虛拟表的記錄進行外部行的添加
4.where規定查詢條件，在虛拟表記錄中逐行進行查詢并篩選出符合規則的記錄
5.将查到的記錄進行字段分組group by，如果沒有進行分組，則預設為一組
6.将分組得到的結果進行having篩選，可使用聚合函數(where時不可使用聚合函數)
7.執行select準備列印
8.執行distinct對列印結果進行去重
9.執行ordery by對結果進行排序
10.執行limit對列印結果的條數進行限制
4 笛卡爾積
将兩張表同時進行查詢時，會産生一張笛卡爾積表。
該表是連表查詢的基礎，但是有很多無用的資料。
左表的每一行記錄都會與右表中的每一行記錄做一次連接配接，如下左表teacher有7條記錄，右表department有4條記錄，那麼總共就有4*7條記錄。
每次查詢出的表都是一張虛拟表，存放于記憶體之中
select * from teacher,department;
+----+--------------+--------+-----+--------------+--------+--------+-----+-----------+
| id | name | gender | age | coaching_age | salary | dep_id | id | name |
+----+--------------+--------+-----+--------------+--------+--------+-----+-----------+
| 1 | TeacherZhang | male | 32 | 8 | 9000 | 200 | 500 | 教務部 |
| 1 | TeacherZhang | male | 32 | 8 | 9000 | 200 | 200 | 教學部 |
| 1 | TeacherZhang | male | 32 | 8 | 9000 | 200 | 100 | 管理部 |
| 1 | TeacherZhang | male | 32 | 8 | 9000 | 200 | 300 | 财務部 |
| 2 | TeacherLi | male | 34 | 10 | 12000 | 200 | 500 | 教務部 |
| 2 | TeacherLi | male | 34 | 10 | 12000 | 200 | 200 | 教學部 |
| 2 | TeacherLi | male | 34 | 10 | 12000 | 200 | 100 | 管理部 |
| 2 | TeacherLi | male | 34 | 10 | 12000 | 200 | 300 | 财務部 |
| 3 | TeacherYun | male | 26 | 4 | 21000 | 100 | 500 | 教務部 |
| 3 | TeacherYun | male | 26 | 4 | 21000 | 100 | 200 | 教學部 |
| 3 | TeacherYun | male | 26 | 4 | 21000 | 100 | 100 | 管理部 |
| 3 | TeacherYun | male | 26 | 4 | 21000 | 100 | 300 | 财務部 |
| 4 | TeacherZhou | famale | 24 | 2 | 4000 | 300 | 500 | 教務部 |
| 4 | TeacherZhou | famale | 24 | 2 | 4000 | 300 | 200 | 教學部 |
| 4 | TeacherZhou | famale | 24 | 2 | 4000 | 300 | 100 | 管理部 |
| 4 | TeacherZhou | famale | 24 | 2 | 4000 | 300 | 300 | 财務部 |
| 5 | TeacherZhao | famale | 32 | 12 | 23000 | 100 | 500 | 教務部 |
| 5 | TeacherZhao | famale | 32 | 12 | 23000 | 100 | 200 | 教學部 |
| 5 | TeacherZhao | famale | 32 | 12 | 23000 | 100 | 100 | 管理部 |
| 5 | TeacherZhao | famale | 32 | 12 | 23000 | 100 | 300 | 财務部 |
| 6 | TeacherYang | male | 28 | 6 | 3000 | 300 | 500 | 教務部 |
| 6 | TeacherYang | male | 28 | 6 | 3000 | 300 | 200 | 教學部 |
| 6 | TeacherYang | male | 28 | 6 | 3000 | 300 | 100 | 管理部 |
| 6 | TeacherYang | male | 28 | 6 | 3000 | 300 | 300 | 财務部 |
| 7 | TeacherWang | famale | 22 | 1 | 3200 | 400 | 500 | 教務部 |
| 7 | TeacherWang | famale | 22 | 1 | 3200 | 400 | 200 | 教學部 |
| 7 | TeacherWang | famale | 22 | 1 | 3200 | 400 | 100 | 管理部 |
| 7 | TeacherWang | famale | 22 | 1 | 3200 | 400 | 300 | 财務部 |
+----+--------------+--------+-----+--------------+--------+--------+-----+-----------+
5 where連表
笛卡爾積表的資料非常全面，我們可以針對笛卡爾積表做一些條件限制使其能夠拿到我們想要的資料。
如下所示，經過where條件過濾後，拿到了很精确的一張表。
select * from teacher,department where teacher.dep_id = department.id;
+----+--------------+--------+-----+--------------+--------+--------+-----+-----------+
| id | name | gender | age | coaching_age | salary | dep_id | id | name |
+----+--------------+--------+-----+--------------+--------+--------+-----+-----------+
| 1 | TeacherZhang | male | 32 | 8 | 9000 | 200 | 200 | 教學部 |
| 2 | TeacherLi | male | 34 | 10 | 12000 | 200 | 200 | 教學部 |
| 3 | TeacherYun | male | 26 | 4 | 21000 | 100 | 100 | 管理部 |
| 4 | TeacherZhou | famale | 24 | 2 | 4000 | 300 | 300 | 财務部 |
| 5 | TeacherZhao | famale | 32 | 12 | 23000 | 100 | 100 | 管理部 |
| 6 | TeacherYang | male | 28 | 6 | 3000 | 300 | 300 | 财務部 |
+----+--------------+--------+-----+--------------+--------+--------+-----+-----------+
雖然使用where确實可以做到連表條件過濾剔除無用資料，但是強烈不建議用這種做法。
MySQL中提供了專門用于連表操作的連表條件過濾文法on，我們不應該使用where來做連表的條件過濾。
并且where連表還有一個缺點，左表teacher中有一個TeacherWang拿不出來，這是因為TeacherWang的部門編号400不在右表中，右表department中有一個部門編号為500的部門拿不出來，這是因為該部門下沒有任何老師。
是以，忘記where連表吧。
6 連接配接查詢
連接配接查詢是MySQL中提供的連表操作文法。
在連接配接查詢中，連表過濾應該使用on，而不應該使用where
6.1 inner join
内連接配接的特點是拿到左表和右表中共有的部分，這與上面的where連接配接很相似。
select * from teacher inner join department on (teacher.dep_id = department.id);
+----+--------------+--------+-----+--------------+--------+--------+-----+-----------+
| id | name | gender | age | coaching_age | salary | dep_id | id | name |
+----+--------------+--------+-----+--------------+--------+--------+-----+-----------+
| 1 | TeacherZhang | male | 32 | 8 | 9000 | 200 | 200 | 教學部 |
| 2 | TeacherLi | male | 34 | 10 | 12000 | 200 | 200 | 教學部 |
| 3 | TeacherYun | male | 26 | 4 | 21000 | 100 | 100 | 管理部 |
| 4 | TeacherZhou | famale | 24 | 2 | 4000 | 300 | 300 | 财務部 |
| 5 | TeacherZhao | famale | 32 | 12 | 23000 | 100 | 100 | 管理部 |
| 6 | TeacherYang | male | 28 | 6 | 3000 | 300 | 300 | 财務部 |
+----+--------------+--------+-----+--------------+--------+--------+-----+-----------+
6.2 left join
左連接配接的特點是可以拿到左表和右表共有的部分并且還可以拿到左表獨有的部分。
這樣就可以拿出TeachWang了。
select * from teacher left join department on (teacher.dep_id = department.id);
+----+--------------+--------+-----+--------------+--------+--------+------+-----------+
| id | name | gender | age | coaching_age | salary | dep_id | id | name |
+----+--------------+--------+-----+--------------+--------+--------+------+-----------+
| 1 | TeacherZhang | male | 32 | 8 | 9000 | 200 | 200 | 教學部 |
| 2 | TeacherLi | male | 34 | 10 | 12000 | 200 | 200 | 教學部 |
| 3 | TeacherYun | male | 26 | 4 | 21000 | 100 | 100 | 管理部 |
| 4 | TeacherZhou | famale | 24 | 2 | 4000 | 300 | 300 | 财務部 |
| 5 | TeacherZhao | famale | 32 | 12 | 23000 | 100 | 100 | 管理部 |
| 6 | TeacherYang | male | 28 | 6 | 3000 | 300 | 300 | 财務部 |
| 7 | TeacherWang | famale | 22 | 1 | 3200 | 400 | NULL | NULL |
+----+--------------+--------+-----+--------------+--------+--------+------+-----------+
6.3 right join
右連接配接的特點是可以拿到左表和右表共有的部分并且還可以拿到右表獨有的部分。
這樣就可以拿出部門編号為500的教務部了。
select * from teacher right join department on (teacher.dep_id = department.id);
+------+--------------+--------+------+--------------+--------+--------+-----+-----------+
| id | name | gender | age | coaching_age | salary | dep_id | id | name |
+------+--------------+--------+------+--------------+--------+--------+-----+-----------+
| 1 | TeacherZhang | male | 32 | 8 | 9000 | 200 | 200 | 教學部 |
| 2 | TeacherLi | male | 34 | 10 | 12000 | 200 | 200 | 教學部 |
| 3 | TeacherYun | male | 26 | 4 | 21000 | 100 | 100 | 管理部 |
| 4 | TeacherZhou | famale | 24 | 2 | 4000 | 300 | 300 | 财務部 |
| 5 | TeacherZhao | famale | 32 | 12 | 23000 | 100 | 100 | 管理部 |
| 6 | TeacherYang | male | 28 | 6 | 3000 | 300 | 300 | 财務部 |
| NULL | NULL | NULL | NULL | NULL | NULL | NULL | 500 | 教務部 |
+------+--------------+--------+------+--------------+--------+--------+-----+-----------+
6.4 full outer join
全外連接配接的特點是拿到左右兩表中共有的部分，并且還可以拿到各自獨有的部分。
遺憾的是MySQL中并不支援這種用法。
select * from teacher full outer join department on (teacher.dep_id = department.id);
6.5 union
MySQL中盡管不支援full outer join，但是我們可以使用left join與right join結合出full outer join的功能。
使用union可将多個查詢結果進行連接配接，但是要保證每個查詢傳回的列的數量與順序要一樣。
union會過濾重複的結果
union all不過濾重複結果
清單字段由是第一個查詢的字段
select * from teacher left join department on (teacher.dep_id = department.id)
union
select * from teacher right join department on (teacher.dep_id = department.id);
+------+--------------+--------+------+--------------+--------+--------+------+-----------+
| id | name | gender | age | coaching_age | salary | dep_id | id | name |
+------+--------------+--------+------+--------------+--------+--------+------+-----------+
| 1 | TeacherZhang | male | 32 | 8 | 9000 | 200 | 200 | 教學部 |
| 2 | TeacherLi | male | 34 | 10 | 12000 | 200 | 200 | 教學部 |
| 3 | TeacherYun | male | 26 | 4 | 21000 | 100 | 100 | 管理部 |
| 4 | TeacherZhou | famale | 24 | 2 | 4000 | 300 | 300 | 财務部 |
| 5 | TeacherZhao | famale | 32 | 12 | 23000 | 100 | 100 | 管理部 |
| 6 | TeacherYang | male | 28 | 6 | 3000 | 300 | 300 | 财務部 |
| 7 | TeacherWang | famale | 22 | 1 | 3200 | 400 | NULL | NULL |
| NULL | NULL | NULL | NULL | NULL | NULL | NULL | 500 | 教務部 |
+------+--------------+--------+------+--------------+--------+--------+------+-----------+
6.6新手專區
如果你還是搞不懂内連接配接，左連接配接，外連接配接的差別，那麼推薦你可以看一下runoob.com提供的這張圖。
非常詳細的舉例了各種連接配接的差别
https://www.runoob.com/w3cnote/sql-join-image-explain.html
7 子查詢
子查詢是将一個查詢語句嵌套在另一個查詢語句中
因為每一次的查詢結果都可以當作一個在記憶體中的臨時表來進行看待，是以我們可以在這張臨時表的基礎上再次進行查詢
子查詢中可以包含：IN、NOT IN、ANY、ALL、EXISTS和 NOT EXISTS等關鍵字
還可以包含比較運算符：= 、!=、> 、
使用子查詢先寫子查詢的内容
7.1基本使用
查詢管理部門的老師資訊
select * from teacher
where dep_id in
(select id from department where name = "管理部"); # 先寫下面，拿到管理部門的id号。實際上就等于 in(100)
+----+-------------+--------+-----+--------------+--------+--------+
| id | name | gender | age | coaching_age | salary | dep_id |
+----+-------------+--------+-----+--------------+--------+--------+
| 3 | TeacherYun | male | 26 | 4 | 21000 | 100 |
| 5 | TeacherZhao | famale | 32 | 12 | 23000 | 100 |
+----+-------------+--------+-----+--------------+--------+--------+
查詢薪資最高的部門，拿到部門名稱
select * from department
where id =
(select dep_id from teacher group by dep_id having max(salary) limit 1); # 先拿到薪資最高部門的id
+-----+-----------+
| id | name |
+-----+-----------+
| 100 | 管理部 |
+-----+-----------+
查詢沒人的部門的部門名稱
select * from department
where id not in
(select dep_id from teacher);
+-----+-----------+
| id | name |
+-----+-----------+
| 500 | 教務部 |
+-----+-----------+
查詢部門被撤銷的老師(部門表中沒這個部門)
select * from teacher
where dep_id not in
(select id from department);
+----+-------------+--------+-----+--------------+--------+--------+
| id | name | gender | age | coaching_age | salary | dep_id |
+----+-------------+--------+-----+--------------+--------+--------+
| 7 | TeacherWang | famale | 22 | 1 | 3200 | 400 |
+----+-------------+--------+-----+--------------+--------+--------+
寫子查詢就先寫下面，再寫上面。
7.2 exists
這玩意兒是跟着where後面使用的，代表子查詢結果是否為真
如果為真的話外邊的查詢才執行，否則将不執行
上司視察工作，如果教師平均薪資大于一萬，則看一眼教師工資，如果不大于一萬就不看。
select avg(salary) from teacher; # 教師平均工資
+-------------+
| avg(salary) |
+-------------+
| 10742.8571 |
+-------------+
select name,salary from teacher
where exists
(select name from teacher having avg(salary) > 10000);
+--------------+--------+
| name | salary |
+--------------+--------+
| TeacherZhang | 9000 |
| TeacherLi | 12000 |
| TeacherYun | 21000 |
| TeacherZhou | 4000 |
| TeacherZhao | 23000 |
| TeacherYang | 3000 |
| TeacherWang | 3200 |
+--------------+--------+
8 自連接配接
自連接配接self join是建立在子查詢以及連接配接查詢基礎之上，即在上一次查詢自己的記錄中再連接配接并查詢一次自己。
因為每次的查詢都會建立一張虛拟表，是以我們可以用as為這張虛拟表取一個别名。
如下示例将展示查詢每個部門中工資最少的教師資訊。
select * from teacher as t1 # 不僅僅可以給字段取别名，也可以為表取别名
inner join
(select min(salary) as min_salary from teacher group by (dep_id)) as t2 # 注意，虛拟表必須使用括号才能as取别名
on t1.salary = t2.min_salary;
+----+--------------+--------+-----+--------------+--------+--------+------------+
| id | name | gender | age | coaching_age | salary | dep_id | min_salary |
+----+--------------+--------+-----+--------------+--------+--------+------------+
| 1 | TeacherZhang | male | 32 | 8 | 9000 | 200 | 9000 |
| 3 | TeacherYun | male | 26 | 4 | 21000 | 100 | 21000 |
| 6 | TeacherYang | male | 28 | 6 | 3000 | 300 | 3000 |
| 7 | TeacherWang | famale | 22 | 1 | 3200 | 400 | 3200 |
+----+--------------+--------+-----+--------------+--------+--------+------------+ 第一步：寫子查詢，拿到每組中最少的薪資，并且為這張虛拟表取名為t2
select min(salary) as min_salary from teacher group by (dep_id);
+------------+
| min_salary |
+------------+
| 21000 |
| 9000 |
| 3000 |
| 3200 |
+------------+
第二步：使用inner join進行連接配接查詢，将實體表t1與虛拟表t2相連，拿到共有的部分，通過薪資來找到教師。
select * from teacher as t1
inner join
(select min(salary) as min_salary from teacher group by (dep_id)) as t2
on t1.salary = t2.min_salary;
+----+--------------+--------+-----+--------------+--------+--------+------------+
| id | name | gender | age | coaching_age | salary | dep_id | min_salary |
+----+--------------+--------+-----+--------------+--------+--------+------------+
| 1 | TeacherZhang | male | 32 | 8 | 9000 | 200 | 9000 |
| 3 | TeacherYun | male | 26 | 4 | 21000 | 100 | 21000 |
| 6 | TeacherYang | male | 28 | 6 | 3000 | 300 | 3000 |
| 7 | TeacherWang | famale | 22 | 1 | 3200 | 400 | 3200 |
+----+--------------+--------+-----+--------------+--------+--------+------------+ 9 三表查詢
三表查詢即多對多表關系查詢，總體來說也不是很難。
9.1準備資料
總有一些行業精英可以同時隸屬于多個部門，而多個部門下也可能有多個人。
在此基礎上建立多對多關系表格。
create table employee (
id int auto_increment primary key,
name char(12) not null,
gender enum("male","famale") not null default "male",
age tinyint unsigned not null,
salary int unsigned not null
); -- 員工表
create table department (
id int unsigned primary key,
name char(12) not null
); -- 部門表
create table emp_dep(
id int auto_increment primary key,
emp_id int unsigned not null,
dep_id int unsigned not null,
unique(emp_id,dep_id) # 應當設定聯合唯一
); -- 關系表
insert into employee(name,gender,age,salary) values
("Yunya","male",22,16000),
("Jack","male",25,18000),
("Bella","famale",24,12000),
("Maria","famale",22,8000),
("Tom","male",23,6000),
("Jason","male",28,32000),
("James","male",31,35000),
("Lisa","famale",36,28000);
insert into department(id,name) values
(1001,"研發部"),
(1002,"開發部"),
(1003,"财務部"),
(1004,"人事部");
insert into emp_dep(emp_id,dep_id) values
(1,1002),
(2,1002),
(3,1003),
(4,1004),
(5,1004),
(6,1001),
(6,1002),
(7,1002),
(7,1001),
(7,1003),
(8,1003),
(8,1004);
9.2思路解析
三表查詢的思路很簡單，先用左表與中間表進行查找，這時候就會得到一張虛拟的表。
select * from employee
inner join emp_dep
on employee.id = emp_dep.emp_id;
+----+-------+--------+-----+--------+----+--------+--------+
| id | name | gender | age | salary | id | emp_id | dep_id |
+----+-------+--------+-----+--------+----+--------+--------+
| 1 | Yunya | male | 22 | 16000 | 1 | 1 | 1002 |
| 2 | Jack | male | 25 | 18000 | 2 | 2 | 1002 |
| 3 | Bella | famale | 24 | 12000 | 3 | 3 | 1003 |
| 4 | Maria | famale | 22 | 8000 | 4 | 4 | 1004 |
| 5 | Tom | male | 23 | 6000 | 5 | 5 | 1004 |
| 6 | Jason | male | 28 | 32000 | 6 | 6 | 1001 |
| 6 | Jason | male | 28 | 32000 | 7 | 6 | 1002 |
| 7 | James | male | 31 | 35000 | 9 | 7 | 1001 |
| 7 | James | male | 31 | 35000 | 8 | 7 | 1002 |
| 7 | James | male | 31 | 35000 | 10 | 7 | 1003 |
| 8 | Lisa | famale | 36 | 28000 | 11 | 8 | 1003 |
| 8 | Lisa | famale | 36 | 28000 | 12 | 8 | 1004 |
+----+-------+--------+-----+--------+----+--------+--------+ 繼續按照上面的思路，再将這将中間表與右表相連，就會得到完整的三表。
select * from employee
inner join emp_dep
on employee.id = emp_dep.emp_id
inner join department
on department.id = emp_dep.dep_id;
+----+-------+--------+-----+--------+----+--------+--------+------+-----------+
| id | name | gender | age | salary | id | emp_id | dep_id | id | name |
+----+-------+--------+-----+--------+----+--------+--------+------+-----------+
| 1 | Yunya | male | 22 | 16000 | 1 | 1 | 1002 | 1002 | 開發部 |
| 2 | Jack | male | 25 | 18000 | 2 | 2 | 1002 | 1002 | 開發部 |
| 3 | Bella | famale | 24 | 12000 | 3 | 3 | 1003 | 1003 | 财務部 |
| 4 | Maria | famale | 22 | 8000 | 4 | 4 | 1004 | 1004 | 人事部 |
| 5 | Tom | male | 23 | 6000 | 5 | 5 | 1004 | 1004 | 人事部 |
| 6 | Jason | male | 28 | 32000 | 6 | 6 | 1001 | 1001 | 研發部 |
| 6 | Jason | male | 28 | 32000 | 7 | 6 | 1002 | 1002 | 開發部 |
| 7 | James | male | 31 | 35000 | 9 | 7 | 1001 | 1001 | 研發部 |
| 7 | James | male | 31 | 35000 | 8 | 7 | 1002 | 1002 | 開發部 |
| 7 | James | male | 31 | 35000 | 10 | 7 | 1003 | 1003 | 财務部 |
| 8 | Lisa | famale | 36 | 28000 | 11 | 8 | 1003 | 1003 | 财務部 |
| 8 | Lisa | famale | 36 | 28000 | 12 | 8 | 1004 | 1004 | 人事部 |
+----+-------+--------+-----+--------+----+--------+--------+------+-----------+ 9.3執行個體練習
拿到James所在的部門，列印其部門名稱。
select name
from department
where id in (
select dep_id
from emp_dep
inner join employee on emp_dep.emp_id = employee.id
where employee.name = "James"
);
+-----------+
| name |
+-----------+
| 研發部 |
| 開發部 |
| 财務部 |
+-----------+
查詢開發部的所有人員工資情況
select name,salary
from employee
where id in (
select emp_id
from emp_dep
inner join department on emp_dep.dep_id = department.id
where department.id = 1002
);
+-------+--------+
| name | salary |
+-------+--------+
| Yunya | 16000 |
| Jack | 18000 |
| Jason | 32000 |
| James | 35000 |
+-------+--------+
查詢平均工資大于三萬的部門名稱
第一步，先用中間表和員工表拿出部門id再說
select dep_id from emp_dep
inner join employee
on employee.id = emp_dep.emp_Id
group by dep_id
having avg(employee.salary) > 30000;
+--------+
| dep_id |
+--------+
| 1001 |
+--------+
第二步，讓這張虛拟表和部門表進行關聯，查詢一下其名稱即可。
select name
from department
where id in (
select dep_id
from emp_dep
inner join employee on employee.id = emp_dep.emp_Id
group by dep_id
having avg(employee.salary) > 30000
);
+-----------+
| name |
+-----------+
| 研發部 |
+-----------+
原文位址： https://www.cnblogs.com/Yunya-Cnblogs/p/13599623.html
喜歡請關注，有用請轉發~
升職、加薪、無漏測-點“在看”