MySQL——連接配接查詢（sql92和sql99）

參考連結

1. https://www.bilibili.com/video/BV12b411K7Zu?p=69

連接配接查詢

含義

連接配接查詢，又稱多表查詢，當查詢的字段來自于多個表時，就會用到連接配接查詢

笛卡爾乘積現象

如果表1有m行，表2有n行，結果=m*n行。發生原因是沒有有效的連接配接條件。

分類

按年代分類

1. sql92标準：僅僅支援内連接配接
2. sql99标準【推薦】：支援内連接配接+外連接配接（左外和右外）+交叉連接配接

按功能分類

内連接配接

1. 等值連接配接
2. 非等值連接配接
3. 自連接配接

外連接配接

1. 左外連接配接
2. 右外連接配接
3. 全外連接配接

交叉連接配接

sql92标準

等值連接配接

1. 多表等值連接配接的結果為多表的交集部分
2. n表連接配接，至少需要n-1個連接配接條件
3. 多表的順序沒有要求
4. 一般需要為表起别名

簡單查詢

比如，“查詢員工名和對應的部門名”

SELECT
	last_name,
	department_name
FROM
	employees,
	departments
WHERE
	employees.department_id = departments.department_id;
           

為表起别名

為表起别名，提高簡潔度。如果為表起了别名，則查詢的字段就不能使用原來的表名

比如，“查詢員工名、工種号、工種名”

SELECT
	last_name,
	e.job_id,
	job_title
FROM
	employees e,
	jobs j
WHERE
	e.job_id=j.job_id;
           

添加篩選條件

比如，“查詢有獎金的員工名、部門名”

SELECT
	last_name,
	department_name
FROM
	employees e,
	departments d
WHERE
	e.department_id=d.department_id
AND
	e.commission_pct IS NOT NULL;
           

又如，“查詢城市名中第二個字元為o的部門名和城市名”

SELECT
	department_name,
	city
FROM
	departments d,
	locations l
WHERE
	d.location_id=l.location_id
AND
	city LIKE '_o%';
           

添加分組

比如，“查詢每個城市的部門個數”

SELECT
	COUNT(*) 個數,
	city
FROM
	departments d,
	locations l
WHERE
	d.location_id=l.location_id
GROUP BY
	city;
           

又如，“查詢有獎金的每個部門的部門名和部門的上司編号和該部門的最低工資”

SELECT
	department_name,
	d.manager_id,
	MIN(salary)
FROM
	departments d,
	employees e
WHERE
	d.department_id=e.department_id
AND
	commission_pct IS NOT NULL
GROUP BY
	department_name, d.manager_id;
           

添加排序

比如，“查詢每個工種的工種名和員工的個數，并且按員工個數降序”

SELECT
	job_title,
	COUNT(*)
FROM
	employees e,
	jobs j
WHERE
	e.job_id=j.job_id
GROUP BY
	job_title
ORDER BY
	COUNT(*) DESC;
           

三表連接配接

比如，“查詢員工名、部門名和所在的城市”

SELECT
	last_name,
	department_name,
	city
FROM
	employees e,
	departments d,
	locations l
WHERE
	e.department_id=d.department_id
AND
	d.location_id=l.location_id;
           

非等值連接配接

比如，“查詢員工的工資和工資級别”

SELECT
	salary,
	grade_level
FROM
	employees e,
	job_grades g
WHERE
	salary BETWEEN g.lowest_sal AND g.highest_sal
           

自連接配接

比如，“查詢員工名和上級的名稱”

SELECT
	e.employee_id,
	e.last_name,
	m.employee_id,
	m.last_name
FROM
	employees e,
	employees m
WHERE
	e.manager_id=m.employee_id;
           

sql99标準

文法

SELECT
	查詢清單
FROM
	表1 别名【連接配接類型】
JOIN
	表2 别名
ON
	連接配接條件
WHERE
	篩選條件
GROUP BY
	分組
HAVING
	篩選條件
ORDER BY
	排序清單
           

分類

1. 内連接配接：inner
2. 外連接配接：a)左外：left【outer】；2)右外：right【outer】；全外：full【outer】
3. 交叉連接配接

内連接配接

文法

SELECT
	查詢清單
FROM
	表1 别名
INNER JOIN
	表2 别名
ON
	連接配接條件;
           

分類

1. 等值
2. 非等值
3. 自連接配接

特點

1. INNER可以省略
2. 篩選條件放在WHERE後面，連接配接條件放在ON後面，提高分離性，便于閱讀

等值連接配接

簡單使用

比如，“查詢員工名、部門名”

SELECT
	last_name,
	department_name
FROM
	employees e
INNER JOIN
	departments d
ON
	e.department_id=d.department_id;
           

添加篩選

比如，“查詢名字中包含e的員工名和工種名”

SELECT
	last_name,
	job_title,
FROM
	employees e
INNER JOIN
	jobs j
ON
	e.job_id=j.job_id
WHERE
	last_name LIKE "%e%";
           

添加分組+篩選

比如，“查詢部門個數>3的城市名和部門個數”

SELECT
	city,
	COUNT(*)
FROM
	locations l
INNER JOIN
	departments d
ON
	d.location_id=l.location_id
GROUP BY
	city
HAVING
	COUNT(*)>3;
           

添加排序

比如，“查詢哪個部門的員工個數>3的部門名和員工個數，并按個數降序”

SELECT
	department_name,
	COUNT(*)
FROM
	employees e
INNER JOIN
	departments d
ON
	e.department_id=d.department_id
GROUP BY
	department_name
HAVING
	COUNT(*)>3
ORDER BY
	COUNT(*) DESC;
           

三表連接配接

比如，“查詢員工名、部門名、工種名，并按部門名降序”

SELECT
	last_name,
	department_name,
	job_title
FROM
	employees e
INNER JOIN
	department d ON e.department_id=d.department_id
INNER JOIN
	jobs j ON e.job_id=j.job_id
ORDER BY
	department_name DESC;
           

非等值連接配接

比如，“查詢員工的工資級别”

SELECT
	salary,
	grade_level
FROM
	employees e
JOIN
	job_grades g
ON
	e.salary BETWEEN g.lowest_sal AND g.highest_sal;
           

又如，“查詢工資級别的個數>20的個數，并且按工資級别降序”

SELECT
	COUNT(*),
	grade_level
FROM
	employees e
JOIN
	job_grades g
ON
	e.salary BETWEEN g.lowest_sal AND g.highest_sal;
GROUP BY
	grade_level
HAVING
	COUNT(*)>2
ORDER BY
	grade_level DESC;
           

自連接配接

比如，“查詢員工的名字、上級的名字”

SELECT
	e.last_name,
	m.last_name
FROM
	employees e
JOIN
	employees m
ON
	e.manger_id=m.employee_id;
           

外連接配接

應用場景

用于查詢一個表中有，另一個表沒有的記錄

特點

1. 外連接配接的查詢結果為主表中的所有記錄，如果從表中有和它比對的，則顯示比對的值；如果從表中沒有和它比對的，則顯示null；外連接配接查詢結果=内連接配接結果+主表中有而從表中沒有的記錄
2. 左外連接配接，left join左邊的是主表；右外連接配接，right join右邊的是主表
3. 左外和右外交換兩個表的順序，可以實作同樣的效果
4. 全外連接配接=内連接配接的結果+表1中有但表2沒有的+表2中有但表1沒有的

簡單查詢

比如，“查詢男朋友不在男神表的女神名”

SELECT
	b.name
FROM
	beauty b
LEFT OUTER JOIN
	boys bo
ON
	b.boyfriend_id=bo.id
WHERE
	bo.id IS NULL;
           

又如，“查詢哪個部門沒有員工”

左外：

SELECT
	d.*,
	e.employee_id
FROM
	departments d
LEFT OUTER JOIN
	employees e
ON
	d.department_id=e.department_id
WHERE
	e.employee_id IS NULL;
           

右外：

SELECT
	d.*,
	e.employee_id
FROM
	employees e
RIGHT OUTER JOIN
	departments d
ON
	d.department_id=e.department_id
WHERE
	e.employee_id IS NULL;
           

全外連接配接（MySQL不支援）

USE
	girls;
SELECT
	b.*,
	bo.*
FULL OUTER JOIN
	boys bo
ON
	b.boyfriend_id=bo.id;
           

交叉連接配接（就是笛卡爾乘積）

SELECT
	b.*,
	bo.*
FROM
	beauty b
CROSS JOIN
	boys bo;
           

sql92 vs sql99

1. 功能：sql99支援的較多
2. 可讀性：sql99實作連接配接條件和篩選條件的分離，可讀性較高