#進(jìn)階六 sql99
/*
語(yǔ)法：
select 查詢列表
from 表1 別名 【連接類(lèi)型】
join 表2 別名
on 連接條件
【where 篩選條件】
【group by 分組】
【having 篩選條件】
【order by 排序列表】
分類(lèi)：
內(nèi)連接：inner
外連接：左外 left? 【outer】
右外 right 【outer】
全外 full? 【outer】
交叉連接：cross
*/
#一.內(nèi)連接
/*
select 查詢列表
from 表1 別名
inner join 別名
on 連接條件
...
[GROUP BY]
[ORDER BY]
分類(lèi)：
等值連接
非等值連接
自連接
特點(diǎn)：
①添加排序、分組、篩選
②inner可以省略
③篩選條件放在where后面，連接條件放在on后面，提高分離性，便于閱讀
④inner join連接和sql92語(yǔ)法中的等值連接效果一樣，都是查詢多表的交集
*/
#1、等值連接
#案例1 查詢員工名，部門(mén)名
SELECT last_name,department_name
FROM `departments` d
INNER JOIN`employees` e
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 `last_name` LIKE '%e%';
#案例3：查詢部門(mén)個(gè)數(shù)>3的城市名和部門(mén)個(gè)數(shù)（添加分組+篩選）
#①篩選每個(gè)城市的部門(mén)個(gè)數(shù)
#②在①篩結(jié)果上篩選滿足條件的
SELECT city,COUNT(*) 部門(mén)個(gè)數(shù)
FROM `locations` l
INNER JOIN `departments` d
ON d.`location_id`=l.`location_id`
GROUP BY city
HAVING COUNT(*)>3;
#案例四.查詢哪個(gè)部門(mén)的部門(mén)員工個(gè)數(shù)>3的部門(mén)名，和員工個(gè)數(shù)，并按個(gè)數(shù)進(jìn)行降序
SELECT `department_name`,COUNT(*)
FROM? `departments` d
INNER JOIN `employees` e
ON d.`department_id`=e.`department_id`
GROUP BY `department_name`
HAVING COUNT(*)>3
ORDER BY COUNT(*) DESC;
#案例5：查詢員工名、部門(mén)名、工種名，并按部門(mén)名降序（三表連接）
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 e.`job_id`=j.`job_id`
ORDER BY department_name DESC;
#二）非等值連接
#查詢員工的工資級(jí)別
SELECT salary,`grade_level`
FROM `employees` e
INNER JOIN `job_grades` g
ON e.salary BETWEEN g.`lowest_sal`? AND g.`highest_sal`;
#查詢工資的級(jí)別個(gè)數(shù)>20的個(gè)數(shù)，并且按工資級(jí)別降序
SELECT salary,COUNT(*),`grade_level`
FROM `employees` e
INNER JOIN `job_grades` g
ON e.salary BETWEEN g.`lowest_sal`? AND g.`highest_sal`
GROUP BY grade_level
HAVING COUNT(*)>20
ORDER BY grade_level DESC;
#三）自連接?
#查詢姓名中包含字符‘k’的員工的名字，上級(jí)的名字
SELECT e.`last_name`,m.`last_name`
FROM `employees` e
INNER JOIN employees m
ON e.`manager_id`=m.`employee_id`
WHERE e.last_name LIKE '%k%';
#二.外連接
/*
應(yīng)用場(chǎng)景：用于查找一個(gè)表中有，另一個(gè)表中沒(méi)有的記錄
特點(diǎn)：
1、外連接的查詢結(jié)果為主表中所有的記錄? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? n'n'n
? 如果從表中有和它匹配的，則顯示匹配的值
? 如果從表中沒(méi)有和它匹配的，則顯示null
? 外連接查詢結(jié)果=內(nèi)連接結(jié)果+主表中有而從表中沒(méi)有的記錄
2、左外連接：left jon左邊的是主表
? 右外連接：right join右邊的是主表
3、左外和右外交換兩個(gè)表的順序，可以實(shí)現(xiàn)同樣的效果
4、全外連接=內(nèi)連接結(jié)果+表1有但表2沒(méi)有+表2有但表1沒(méi)有
*/
#引入：查詢男朋友不在男神表的女神名
#左外連接
SELECT * FROM beauty;
SELECT * FROM boys;
SELECT b.name,bo.*
FROM beauty b
LEFT OUTER JOIN boys bo
ON b.`boyfriend_id`=bo.`id`
WHERE bo.`id` IS NULL;
SELECT b.name,bo.*
FROM boys bo
RIGHT OUTER JOIN beauty b
ON b.`boyfriend_id`=bo.`id`
WHERE bo.`id` IS NULL;
#案例1：查詢那個(gè)部門(mén)沒(méi)有員工
#左外
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
#全外
USE girls;
SELECT b.*,bo.*
FROM beayty b
FULL OUTER JOIN boys bo
ON b.boyfriend_id =bo.id;
#交叉連接
SELECT b.*,bo.*
FROM beauty b
CROSS JOIN boys bo;
