第四天MySQL 連接查詢（內連接、外連接、交叉連接）（sql99版本下） 子查詢： select 後面（標量子查詢） from 後面（表子查詢） where 或having後面（標量子查詢、列子查詢、行子查詢） exists後面（相關子查詢）（表子查詢） ...
2020 5/7
（sql99）
語法：
SELECT 查詢列表
FROM 表1 別名 [連接類型]
JION 表2 別名
ON 連接條件
[WHERE 篩選條件]
[GROUP BY 分組]
[HAVING 篩選條件]
[ORDER BY 排序列表]
分類：
內連接：inner
外連接：
左外：left [outer]
右外：right[outer]
全外：full[outer]
交叉連接：cross
#############（一）、內連接########################################
語法：
SELECT 查詢列表
FROM 表1 別名
INEER JOIN 表2 別名
ON 連接條件;(後可外加其他如where、group by等子句)
特點：
① 可添加排序、分組、篩選
② inner可以省略
③ 篩選條件放在where 後面，連接條件放在on後面，提高分離性，便於閱讀（對比與sql92,它的篩選條件和連接條件都是放在where後面）
分類：
###等值連接
#案例1：查詢員工名、部門名
SELECT
last_name,
department_name
FROM
employees e
INNER JOIN departments d ON e.department_id = d.department_id;
#案例2：查詢名字中包含e的員工名和工種名【篩選】
SELECT
last_name,
job_title
FROM
employees e
INNER JOIN jobs j ON e.job_id = j.job_id
WHERE
e.last_name LIKE '%e%';
#案例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;
#案例4：查詢部門個數大於3的城市名和部門個數【分組+篩選】
SELECT
city,
COUNT(*)
FROM
locations l
INNER JOIN departments d ON l.location_id = d.location_id
GROUP BY
city
HAVING
COUNT(*) > 3;
#案例5：查詢員工名、部門名、工種名、並按部門名降序
SELECT
last_name,
department_name,
job_title
FROM
employees e
INNER JOIN departments d ON e.department_id = d.department_id
INNER JOIN jobs j ON j.job_id = e.job_id
ORDER BY
department_name DESC;
###非等值連接
#查詢員工的工資級別
SELECT
*
FROM
job_grades;
SELECT
grade_level,
last_name,
salary
FROM
employees e
JOIN job_grades j ON e.salary BETWEEN lowest_sal
AND highest_sal;
#查詢工資級別的個數大於20，並且按工資級別降序【排序+分組+篩選】
SELECT
COUNT(*) 個數,
grade_level
FROM
employees e
JOIN job_grades j ON e.salary BETWEEN lowest_sal
AND highest_sal
GROUP BY
grade_level
HAVING
COUNT(*) > 20
ORDER BY
grade_level DESC;
###自連接
#案例：查詢員工的名字中包含'k'的員工名、上級的名字
SELECT
e.last_name 員工名,
m.last_name 上級領導名
FROM
employees e
JOIN employees m ON e.manager_id = m.employee_id
WHERE
e.last_name LIKE '%k%';
###（二）、外連接#########################################
特點：
- 外連接的查詢結果為主表中的所有記錄
a) 如果從表中有和它匹配的，則顯示為匹配的值
b) 如果從表中沒有和它匹配的，則線束null
c) 外連接查詢結果=內連接查詢結果+主表中有而從表中沒有的記錄
- 左外連接，left join左邊的是主表（右外連接，right join右邊的為主表）
- 左外和右外交換兩個表的順序，可以實現同樣的效果
- 全外連接=內連接查詢結果+1表中有而表2中沒有的記錄+表2有而表1沒有的記錄
引入：
#查詢沒有男朋友的女神名
USE girls;
SELECT
*
FROM
beauty;
SELECT
NAME,
bo.id
FROM
beauty bea
LEFT JOIN boys bo ON bea.boyfriend_id = bo.id
WHERE
bo.id IS NULL;
###左（右）外連接
#查詢哪個部門沒有員工
#左外
SELECT
department_name,
employee_id
FROM
departments d
LEFT JOIN employees e ON d.department_id = e.department_id
WHERE
employee_id IS NULL;
-----------------------------------------------------------
#右外
SELECT
department_name,
employee_id,
FROM
employees e
RIGHT JOIN departments d ON d.department_id = e.department_id
WHERE
employee_id IS NULL;
###全外連接
#查詢女神表和男朋友表的全外連接
SELECT b.*,bo.*
FROM beauty b
FULL OUTER JOIN boys bo
ON b.boyfriend_id = bo.id;
###交叉連接（兩個表進行笛卡爾乘積）
SELECT b.*,bo.*
FROM beauty b
CROSS JOIN boys bo;
總結（sql92 PK sql99）
功能：sql99支持的較多
可讀性：sql99實現連接查詢的條件和篩選選條件的分離，可讀性較高
十八、子查詢
含義：出現在其他語句中的select語句，稱為子查詢或內查詢
外部的查詢語句 ，稱為主查詢或外查詢
分類：
按子查詢出現的位置：
select 後面（僅僅支持標量子查詢）
from 後面（支持表子查詢）
where 或having後面（標量子查詢、列子查詢、行子查詢）-》重要
exists後面（相關子查詢）（表子查詢）
按結果集的行列數不同：
標量子查詢（結果集只有一行一列）
列子查詢（結果集只有一列多行）
行子查詢（結果集有一行多列）
表子查詢（結果一般為多行多列）
###where或having 後面
1. 標量子查詢（單行子查詢）
2. 列子查詢（多行子查詢）
3. 行子查詢（多行多列）
特點:
① 子查詢放在小括弧內
② 子查詢一般放在條件的右側
③ 標量子查詢，一般搭配著單行操作符使用（> < <> <= >= = ）
④ 列子查詢，一般搭配著多行操作符使用（in 、 any|some 、 all）
⑤ 子查詢的執行優先於主查詢的執行，主查詢的結果用到了子查詢的結果
###標量子查詢
#案例1：誰的工資比Abel高？
SELECT *
FROM employees
WHERE salary > (
SELECT salary FROM employees WHERE last_name = 'Abel' #先查出Abel的工資
);
#案例2：返回job_id與141號相同，salary比143號員工多的員工、姓名、job_id和工資
SELECT last_name,job_id,salary
FROM employees
WHERE job_id = (
SELECT job_id FROM employees WHERE employee_id = 141
) AND salary>(
SELECT salary FROM employees WHERE employee_id = 143
);
#案例3:返回工資最少的員工的last_name job_id salary
SELECT last_name,job_id,salary
FROM employees
WHERE salary =(
SELECT MIN(salary) FROM employees
);
#案例4：查詢最低工資大於50號部門最低工資的部門id和其最低工資
SELECT department_id,MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(
SELECT MIN(salary) FROM employees WHERE department_id = 50
);
###列子查詢
多行操作符：
#案例1：返回location_id是1400或1700的部門中的所有員工姓名
SELECT
last_name,
department_id
FROM
employees e
WHERE
department_id IN (
SELECT
department_id
FROM
departments
WHERE
location_id IN (1400, 1700)
);
#案例2：返回其它部門中比job_id為‘IT_PROG’部門任一工資低的員工的員 工號、姓名、job_id 以及salary
SELECT
employee_id,
last_name,
job_id,
salary
FROM
employees e
WHERE
salary < ANY (
SELECT DISTINCT
salary
FROM
employees
WHERE
job_id = 'IT_PROG'
)
AND job_id <> 'IT_PROG';
-------------------兩種方式等價------------------------
SELECT
employee_id,
last_name,
job_id,
salary
FROM
employees e
WHERE
salary < (
SELECT
MAX(DISTINCT salary)
FROM
employees
WHERE
job_id = 'IT_PROG'
)
AND job_id <> 'IT_PROG';
#案例3：返回其它部門中比job_id為‘IT_PROG’部門所有工資都低的員工 的員工號、姓名、job_id 以及salary
SELECT
employee_id,
last_name,
job_id,
salary
FROM
employees e
WHERE
salary < ALL (
SELECT DISTINCT
salary
FROM
employees
WHERE
job_id = 'IT_PROG'
)
AND job_id <> 'IT_PROG';
-------------------兩種方式等價------------------------
SELECT
employee_id,
last_name,
job_id,
salary
FROM
employees e
WHERE
salary < (
SELECT
MIN(DISTINCT salary)
FROM
employees
WHERE
job_id = 'IT_PROG'
)
AND job_id <> 'IT_PROG';
註意：因為列子查詢中的ALL和ANY|SOME通能能被MAX或MIN分組查詢後的標量子查詢替換，因此用的較少
#行子查詢（結果集一行多列或多行多列）
#案例：查詢員工編號最小並且工資最高的員工信息
SELECT
*
FROM
employees
WHERE
employee_id = (
SELECT
MIN(employee_id)
FROM
employees
)
AND salary = (
SELECT
MAX(salary)
FROM
employees
);#用標量子查詢寫的方式
-------------------兩種方式等價------------------------
SELECT
*
FROM
employees
WHERE
(employee_id, salary) = (
SELECT
MIN(employee_id),
MAX(salary)
FROM
employees
);#用行子查詢寫得方式
###select 後面
註意：僅僅支持標量子查詢
#案例：查詢每個部門的員工個數
SELECT
d.*, (
SELECT
COUNT(*)
FROM
employees e
WHERE
e.department_id = d.department_id
)
FROM
departments d;
#案例2：查詢員工號=102的部門名
SELECT
(
SELECT
department_name
FROM
departments d
INNER JOIN employees e ON e.department_id = d.department_id
WHERE
e.employee_id = 102
);
###from後面
註意：將子查詢結果充當一個表，要求必須取別名
#案例：查詢每個部門的平均工資的工資等級
SELECT
ag_dep.*, g.grade_level
FROM
(
SELECT
AVG(salary) ag,
department_id
FROM
employees
GROUP BY
department_id
) ag_dep
INNER JOIN job_grades g ON ag_dep.ag BETWEEN lowest_sal
AND highest_sal;
###exists後面（相關子查詢）
語法:exists(完整的查詢語句)
結果：1或0
SELECT EXISTS(SELECT employee_id FROM employees);->1
SELECT EXISTS(SELECT employee_id FROM employees WHERE salary = 30000);->0
存在結果就返回1，不存在就返回0
#案例：查詢有員工的部門名
SELECT department_name
FROM departments d
WHERE EXISTS(
SELECT *
FROM employees e
WHERE d.department_id = e.department_id
);
