前言
簡單的數據我們可以直接從一個表中獲取，但在真實的項目中查詢符合條件的數據通常需要牽扯到多張表，這就不得不使用多表查詢。多表查詢分為多表連接查詢、符合條件鏈接查詢、子查詢。多表連接查詢包括內連接、外連接、全連接。符合條件連接查詢本質上是多表連接查詢+過濾條件。子查詢是將一個查詢語句嵌套在另一個查詢語句中，內層查詢語句的查詢結果作為外層查詢語句的數據源。
準備
# 建表
create table department(
id int,
name varchar(20)
);
create table employee(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') not null default 'male',
age int,
dep_id int
);
# 插入數據
insert into department values
(1,'技術'),
(2,'財務'),
(3,'法律’);
# 此處省略員工表數據...
多表連接查詢
語法
SELECT 字段列表
FROM 表1 INNER|LEFT|RIGHT JOIN 表2
ON 表1.字段 = 表2.字段;
交叉連接
在介紹多表查詢的時候，有必要先介紹下交叉連接，如下select * from employee, department;查詢語句就是交叉連接查詢，可以看出，同一個數據在在employee表和department表交叉連接之后產生了重復記錄，其重復個數取決于department表的記錄個數。所以最后交叉連接之后的記錄個數是：count(employee) * count(department)，即笛卡爾積。通常情況下，笛卡爾積的結果在工作中無實際意義，我們需要在笛卡爾積的基礎上進行篩選，找到employee.dep_id = department.id的那條記錄。
mysql> select * from employee, department;
+----+-------+------+--------+----------+-----------+--------+------+--------+
| id | name | age | sex | position | salary | dep_id | id | name |
+----+-------+------+--------+----------+-----------+--------+------+--------+
| 1 | jack | 20 | male | lawyer | 888889 | 3 | 1 | 技術 |
| 1 | jack | 20 | male | lawyer | 888889 | 3 | 2 | 財務 |
| 1 | jack | 20 | male | lawyer | 888889 | 3 | 3 | 法律 |
| 2 | mark | 22 | male | lawyer | 888889 | 3 | 1 | 技術 |
| 2 | mark | 22 | male | lawyer | 888889 | 3 | 2 | 財務 |
| 2 | mark | 22 | male | lawyer | 888889 | 3 | 3 | 法律 |
| 3 | hank | 25 | male | lawyer | 7777.8 | 3 | 1 | 技術 |
| 3 | hank | 25 | male | lawyer | 7777.8 | 3 | 2 | 財務 |
| 3 | hank | 25 | male | lawyer | 7777.8 | 3 | 3 | 法律 |
| 4 | nick | 39 | male | lawyer | 4438890 | 3 | 1 | 技術 |
| 4 | nick | 39 | male | lawyer | 4438890 | 3 | 2 | 財務 |
| 4 | nick | 39 | male | lawyer | 4438890 | 3 | 3 | 法律 |
| 5 | jenny | 26 | female | lawyer | 10000.8 | 3 | 1 | 技術 |
| 5 | jenny | 26 | female | lawyer | 10000.8 | 3 | 2 | 財務 |
| 5 | jenny | 26 | female | lawyer | 10000.8 | 3 | 3 | 法律 |
| 6 | tony | 35 | male | RD | 100000000 | 1 | 1 | 技術 |
| 6 | tony | 35 | male | RD | 100000000 | 1 | 2 | 財務 |
| 6 | tony | 35 | male | RD | 100000000 | 1 | 3 | 法律 |
| 7 | emmy | 27 | female | RD | 9999 | 1 | 1 | 技術 |
| 7 | emmy | 27 | female | RD | 9999 | 1 | 2 | 財務 |
| 7 | emmy | 27 | female | RD | 9999 | 1 | 3 | 法律 |
| 8 | emmy | 23 | female | finance | 5000 | 2 | 1 | 技術 |
| 8 | emmy | 23 | female | finance | 5000 | 2 | 2 | 財務 |
| 8 | emmy | 23 | female | finance | 5000 | 2 | 3 | 法律 |
| 9 | lucy | 45 | female | finance | 10000 | 2 | 1 | 技術 |
| 9 | lucy | 45 | female | finance | 10000 | 2 | 2 | 財務 |
| 9 | lucy | 45 | female | finance | 10000 | 2 | 3 | 法律 |
| 10 | emmi | 20 | female | finance | 20000 | 2 | 1 | 技術 |
| 10 | emmi | 20 | female | finance | 20000 | 2 | 2 | 財務 |
| 10 | emmi | 20 | female | finance | 20000 | 2 | 3 | 法律 |
| 11 | james | 18 | male | NULL | 3000 | NULL | 1 | 技術 |
| 11 | james | 18 | male | NULL | 3000 | NULL | 2 | 財務 |
| 11 | james | 18 | male | NULL | 3000 | NULL | 3 | 法律 |
+----+-------+------+--------+----------+-----------+--------+------+--------+
33 rows in set (0.00 sec)
內連接
內連接只會連接兩張表匹配的行，即取交集。找兩張表公共部分，相當于利用條件從笛卡爾積結果中篩選出了正確的結果
mysql> select employee.id,employee.name,employee.age,employee.sex,department.name from employee inner join department on employee.dep_id=department.id;
+----+-------+------+--------+--------+
| id | name | age | sex | name |
+----+-------+------+--------+--------+
| 1 | jack | 20 | male | 法律 |
| 2 | mark | 22 | male | 法律 |
| 3 | hank | 25 | male | 法律 |
| 4 | nick | 39 | male | 法律 |
| 5 | jenny | 26 | female | 法律 |
| 6 | tony | 35 | male | 技術 |
| 7 | emmy | 27 | female | 技術 |
| 8 | emmy | 23 | female | 財務 |
| 9 | lucy | 45 | female | 財務 |
| 10 | emmi | 20 | female | 財務 |
+----+-------+------+--------+--------+
10 rows in set (0.00 sec)
上述內連接查詢語句等同于：
mysql> select employee.id,employee.name,employee.age,employee.sex,department.name
from employee,department
where employee.dep_id=department.id;
外連接
外連接分為左連接、右連接、全外連接
左連接
左連接在內連接的基礎上優先顯示左表全部記錄。即左連接=內連接+左表未符合條件的記錄
#以左表為準，即找出所有員工信息，當然包括沒有部門的員工
#本質就是：在內連接的基礎上增加左邊有右邊沒有的結果
mysql> select employee.id,employee.name,department.name as depart_name from employee left join department on employee.dep_id=department.id;
+----+-------+-------------+
| id | name | depart_name |
+----+-------+-------------+
| 6 | tony | 技術 |
| 7 | emmy | 技術 |
| 8 | emmy | 財務 |
| 9 | lucy | 財務 |
| 10 | emmi | 財務 |
| 1 | jack | 法律 |
| 2 | mark | 法律 |
| 3 | hank | 法律 |
| 4 | nick | 法律 |
| 5 | jenny | 法律 |
| 11 | james | NULL |
+----+-------+-------------+
11 rows in set (0.00 sec)
右連接
又連接在內連接的基礎上優先顯示右表的內容。即右連接==內連接+右表未符合條件的記錄
#以右表為準，即找出所有部門信息，包括沒有員工的部門
#本質就是：在內連接的基礎上增加右邊有左邊沒有的結果
mysql> select employee.id,employee.name,department.name as depart_name from employee right join department on employee.dep_id=department.id;
+------+-------+-------------+
| id | name | depart_name |
+------+-------+-------------+
| 1 | jack | 法律 |
| 2 | mark | 法律 |
| 3 | hank | 法律 |
| 4 | nick | 法律 |
| 5 | jenny | 法律 |
| 6 | tony | 技術 |
| 7 | emmy | 技術 |
| 8 | emmy | 財務 |
| 9 | lucy | 財務 |
| 10 | emmi | 財務 |
+------+-------+-------------+
10 rows in set (0.00 sec)
可以發現，左表(employee表)的第11條記錄沒有被查詢出來
全外連接
全外連接會在內連接查詢的基礎上顯示左表和右表的全部記錄
mysql> select * from employee left join department on employee.dep_id = department.id
-> union
-> select * from employee right join department on employee.dep_id = department.id
-> ;
+------+-------+------+--------+----------+-----------+--------+------+--------+
| id | name | age | sex | position | salary | dep_id | id | name |
+------+-------+------+--------+----------+-----------+--------+------+--------+
| 6 | tony | 35 | male | RD | 100000000 | 1 | 1 | 技術 |
| 7 | emmy | 27 | female | RD | 9999 | 1 | 1 | 技術 |
| 8 | emmy | 23 | female | finance | 5000 | 2 | 2 | 財務 |
| 9 | lucy | 45 | female | finance | 10000 | 2 | 2 | 財務 |
| 10 | emmi | 20 | female | finance | 20000 | 2 | 2 | 財務 |
| 1 | jack | 20 | male | lawyer | 888889 | 3 | 3 | 法律 |
| 2 | mark | 22 | male | lawyer | 888889 | 3 | 3 | 法律 |
| 3 | hank | 25 | male | lawyer | 7777.8 | 3 | 3 | 法律 |
| 4 | nick | 39 | male | lawyer | 4438890 | 3 | 3 | 法律 |
| 5 | jenny | 26 | female | lawyer | 10000.8 | 3 | 3 | 法律 |
| 11 | james | 18 | male | NULL | 3000 | NULL | NULL | NULL |
| NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 人力 |
+------+-------+------+--------+----------+-----------+--------+------+--------+
12 rows in set (0.00 sec)
符合條件鏈接查詢
mysql> select employee.name, employee.age, department.name from employee inner join department
-> on employee.dep_id=department.id
-> where age > 30
-> order by age asc;
+------+------+--------+
| name | age | name |
+------+------+--------+
| tony | 35 | 技術 |
| nick | 39 | 法律 |
| lucy | 45 | 財務 |
+------+------+--------+
3 rows in set (0.00 sec)
子查詢
子查詢是將一個查詢語句的嵌套在另一個查詢語句中
內層查詢語句的查詢結果作為外層查詢語句的數據源
子查詢中可以包含 IN、NOT IN、ANY、ALL、EXISTS和NOT EXISTS等關鍵字
注意被嵌套的查詢語句需要用( )包裹
查詢員工部門平均年齡大于30的部門信息
mysql> select * from department
-> where id in
-> (select dep_id from employee group by dep_id having avg(age) > 30);
+------+--------+
| id | name |
+------+--------+
| 1 | 技術 |
+------+--------+
1 row in set (0.00 sec)
查詢技術部員工姓名
mysql> select name from employee
-> where dep_id in
-> (select id from department where name="技術");
+------+
| name |
+------+
| tony |
| emmy |
+------+
2 rows in set (0.00 sec)
查詢無員工的部門名(子查詢得到的是所有人的部門id，需要disctinct去除)
mysql> select name from department
-> where id not in
-> (select distinct dep_id from employee);
帶比較運算符的子查詢
查詢大于所有人平均年齡的員工名和年齡
mysql> select * from employee where age > (select avg(age) from employee);
+----+------+------+--------+----------+-----------+--------+
| id | name | age | sex | position | salary | dep_id |
+----+------+------+--------+----------+-----------+--------+
| 4 | nick | 39 | male | lawyer | 4438890 | 3 |
| 6 | tony | 35 | male | RD | 100000000 | 1 |
| 9 | lucy | 45 | female | finance | 10000 | 2 |
+----+------+------+--------+----------+-----------+--------+
3 rows in set (0.00 sec)
不能這樣：在前面沒有group by的時后面不能使用分組函數
mysql> select * from employee where age > avg(age);
ERROR 1111 (HY000): Invalid use of group function
帶EXISTS關鍵字的子查詢
EXISTS關字鍵字表示存在。在使用EXISTS關鍵字時，內層查詢語句不返回查詢的記錄。
而是返回一個真假值。True或False
當返回True時，外層查詢語句將進行查詢；當返回值為False時，外層查詢語句不進行查詢
mysql> select * from employee
-> where exists
-> (select id from department where id=200);
MYSQL中可以把一個查詢語句用括號括起來使用as起一個別名當做一個表使用
查詢每個職位最新入職的員工
SELECT
*
FROM
emp AS t1
INNER JOIN (
SELECT
post,
max(hire_date) max_date
FROM
emp
GROUP BY
post
) AS t2 ON t1.post = t2.post
WHERE
t1.hire_date = t2.max_date;
查詢語句關鍵字執行順序
一個完整的mysql的查詢語句如下：
SELECT DISTINCT
FROM
JOIN
ON
WHERE
GROUP BY
HAVING
ORDER BY
LIMIT
其關鍵字執行順序如下：
(7) SELECT
(8) DISTINCT
(1) FROM
(3) JOIN
(2) ON
(4) WHERE
(5) GROUP BY
(6) HAVING
(9) ORDER BY
(10) LIMIT
)
)
