#进阶六 sql99
/*
语法：
select 查询列表
from 表1 别名 【连接类型】
join 表2 别名
on 连接条件
【where 筛选条件】
【group by 分组】
【having 筛选条件】
【order by 排序列表】
分类：
内连接：inner
外连接：左外 left 【outer】
右外 right 【outer】
全外 full 【outer】
交叉连接：cross
*/
#一.内连接
/*
select 查询列表
from 表1 别名
inner join 别名
on 连接条件
...
[GROUP BY]
[ORDER BY]
分类：
等值连接
非等值连接
自连接
特点：
①添加排序、分组、筛选
②inner可以省略
③筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读
④inner join连接和sql92语法中的等值连接效果一样，都是查询多表的交集
*/
#1、等值连接
#案例1 查询员工名，部门名
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：查询部门个数>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 `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：查询员工名、部门名、工种名，并按部门名降序（三表连接）
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;
#二）非等值连接
#查询员工的工资级别
SELECT salary,`grade_level`
FROM `employees` e
INNER JOIN `job_grades` g
ON e.salary BETWEEN g.`lowest_sal` AND g.`highest_sal`;
#查询工资的级别个数>20的个数，并且按工资级别降序
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’的员工的名字，上级的名字
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%';
#二.外连接
/*
应用场景：用于查找一个表中有，另一个表中没有的记录
特点：
1、外连接的查询结果为主表中所有的记录 n'n'n
如果从表中有和它匹配的，则显示匹配的值
如果从表中没有和它匹配的，则显示null
外连接查询结果=内连接结果+主表中有而从表中没有的记录
2、左外连接：left jon左边的是主表
右外连接：right join右边的是主表
3、左外和右外交换两个表的顺序，可以实现同样的效果
4、全外连接=内连接结果+表1有但表2没有+表2有但表1没有
*/
#引入：查询男朋友不在男神表的女神名
#左外连接
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：查询那个部门没有员工
#左外
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;
