3. sql99標(biāo)準(zhǔn)
語法
select 查詢列表
from 表1 別名 【連接類型】
join 表2 別名
on 連接條件（內(nèi)連接 inner鹿鳖，左外連接 left outer，右外連接 right outer，全外連接 full outer晒衩，交叉連接 cross）
【where 篩選條件】
【group by 分組條件】
【having 篩選條件】
【order by 排序列表】
內(nèi)連接 - 等值連接
- 案例：查詢員工名和對應(yīng)的部門名
SELECT
`last_name`,
`department_name`
FROM
`departments` AS d
INNER JOIN `employees` AS e
ON e.`department_id` = d.`department_id` ;
- 案例：查詢名字中包含e的員工名和工種名
SELECT
`last_name`,
`job_title`
FROM
`jobs` AS j
INNER JOIN `employees` AS e
ON e.`job_id` = j.`job_id`
WHERE `last_name` LIKE "%e%" ;
- 案例：查詢部門個數(shù)大于3的城市名和部門個數(shù)
SELECT
`city`,
COUNT(*)
FROM
`departments` AS d
INNER JOIN `locations` AS l
ON d.`location_id` = l.`location_id`
GROUP BY `city`
HAVING COUNT(*) > 3 ;
- 案例：查詢員工個數(shù)大于3的部門名和員工個數(shù)涌攻，并按照個數(shù)降序排序
SELECT
`department_name`,
COUNT(*)
FROM
`departments` AS d
INNER JOIN `employees` AS e
ON d.`department_id` = e.`department_id`
GROUP BY `department_name`
HAVING COUNT(*) > 3
ORDER BY COUNT(*) DESC
- 案例：查詢員工名，部門名，工種名倔韭，并按部門名降序
SELECT
`last_name`,
`department_name`,
`job_title`
FROM
`employees` AS e
INNER JOIN `departments` AS d
ON d.`department_id` = e.`department_id`
INNER JOIN `jobs` AS j
ON e.`job_id` = j.`job_id`
ORDER BY `department_name` DESC ;
總結(jié)：
- 可以添加排序术浪，分組，篩選
- inner可以省略
- 篩選條件放在where后面寿酌，連接條件放在on后面胰苏，提高分離性，便于閱讀
- inner join連接和sql92語法中的等值連接效果是一樣的醇疼，都是查詢多表的交集
內(nèi)連接 - 非等值連接
- 案例：查詢員工的工資和工資級別
SELECT
`last_name`,
`salary`,
`grade_level`
FROM
`employees` AS e
INNER JOIN `job_grades` AS jd
ON `salary` BETWEEN `lowest_sal`
AND `highest_sal`
- 案例：查詢工資級別的員工個數(shù)大于20的硕并，按照工資級別降序系列
SELECT
`grade_level`,
COUNT(*)
FROM
`employees` AS e
INNER JOIN `job_grades` AS jd
ON `salary` BETWEEN `lowest_sal`
AND `highest_sal`
GROUP BY `grade_level`
HAVING COUNT(*) > 20
ORDER BY `grade_level` DESC
內(nèi)連接 - 自連接
- 案例：查詢員工名以及上級的名稱
SELECT
e.`employee_id`,
e.`last_name`,
e.`manager_id`,
m.`last_name`
FROM
`employees` AS e
INNER JOIN `employees` AS m
ON e.`manager_id` = m.`employee_id`
WHERE e.`last_name` LIKE "%k%"
外連接
- 案例：查詢男朋友不在男神表的女神名
-- 左外連接
SELECT
g.`name`
FROM
`beauty` AS g
LEFT OUTER JOIN `boys` AS b
ON g.`boyfriend_id` = b.`id`
WHERE b.`id` IS NULL
-- 右外連接
SELECT
g.`name`
FROM
`boys` AS b
RIGHT OUTER JOIN `beauty` AS g
ON g.`boyfriend_id` = b.`id`
WHERE b.`id` IS NULL
- 案例：查詢沒有員工的部門
SELECT DISTINCT
d.`department_id`
FROM
`departments` AS d
LEFT OUTER JOIN `employees` AS e
ON d.`department_id` = e.`department_id`
WHERE e.`employee_id` IS NULL
總結(jié)：
- 用于查詢一個表中有，另一個表中沒有的數(shù)據(jù)
- 左外連接中秧荆，左側(cè)為主表倔毙；右外連接，右側(cè)為主表辰如；左外和右外交換表的順序可以實現(xiàn)同樣的效果
- 外連接的查詢結(jié)果為主表中的所有數(shù)據(jù)普监，如果從表中有與之匹配的，就顯示匹配記錄琉兜，否則顯示null
- 全外連接會將左外連接和右外連接的結(jié)果組合在一起
交叉連接
- 含義即為兩表的笛卡爾乘積
SELECT
*
FROM
`beauty`
CROSS JOIN `boys`
