MySQL进阶6：连接查询（sql99语法）

# 二、sql99语法


/*
语法：
	select 查询列表
	from 表1 as 别名 【连接类型】
	join 表2 as 别名
	on 连接条件
	【where 筛选条件】
	【group by 分组】
	【having 筛选条件】
	【order by 排序列表】

分类：		连接类型
内连接		inner
外连接
	左外	left 【outer】
	右外	right 【outer】
	全外	full 【outer】
交叉连接	cross
*/

# 一、内连接
/*
语法：

select 查询列表
from 表1 as 别名 【连接类型】
join 表2 as 别名
on 连接条件;

分类：
等值
非等值
自连接

特点：
①添加排序、分组、筛选
②inner可以缺省
③筛选条件在where后面，连接条件在on后面，提高可读性
④inner join连接和sql92语法中的等值连接效果是一样的，都是查询多表的交集
⑤表的顺序可以调换
⑥N个表至少需要N-1个条件
⑦结果为多个表的交集
*/


# 1.等值连接
# 案例1：查询员工名、部门名

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

# 案例2： 查询名字中包含e的员工名和工种名（筛选）

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

# 案例3： 查询部门个数>3的城市名和部门个数（分组+筛选）

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

# 案例4： 查询哪个部门的部门员工个数>3的部门名和员工个数，并按个数降序（排序）

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

# 案例5： 查询员工名、部门名、工种名，并按部门名降序

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

# 2.非等值连接

# 查询员工的工资级别
SELECT salary,grade_level
FROM employees AS e
JOIN job_grades AS g
ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`;

# 查询每个工资级别的个数>20的，并且按工资级别降序
SELECT COUNT(*),grade_level
FROM employees AS e
JOIN job_grades AS g
ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`;
GROUP BY grade_level
HAVING COUNT(*)>20
ORDER BY grade_level DESC;

# 3.自连接

# 查询员工的名字、上级的名字
SELECT e.last_name,m.last_name
FROM employees AS e
JOIN employees AS m
ON e.`manager_id`=m.`employee_id`;

# 查询姓名中包含字母k的员工的名字、上级的名字
SELECT e.last_name,m.last_name
FROM employees AS e
JOIN employees AS m
ON e.`manager_id`=m.`employee_id`
WHERE e.last_name LIKE '%k%';

# 二、外连接
/*
应用场景：用于查询一个表中有，另一个表没有的记录

特点：
1、外连接的查询结果为主表中的所有记录
	如果从表中有和它匹配的。则显示匹配的值
	如果从表中没有和它匹配的，则显示null
	外连接查询结果=内连接结果+主表中有而从表中没有的记录
2、左外连接：left join左边的是主表
   右外连接：right join右边的是主表
3、左外和右外交换两个表的顺序，可以达到同样的效果
4、全外连接，就是两个表的并集
5、交叉连接：就是笛卡尔乘积
①查询结果为主表的所有行，如果从表和它匹配则显示匹配行，如果从表没有显示，则显示null
②一般用于查询除了交集部分的剩余不匹配的行
*/


# 查询男朋友，不在男生表的女生名
USE girls

# 左外连接
SELECT b.name,bo.*
FROM beauty AS b
LEFT OUTER JOIN boys AS bo
ON b.boyfriend_id=bo.id
WHERE bo.id IS NULL;

# 右外连接
SELECT b.name,bo.*
FROM boys AS bo
RIGHT OUTER JOIN beauty AS b
ON b.boyfriend_id=bo.id
WHERE bo.id IS NULL;

# 案例1：查询哪个部门没有员工
# 左外
USE myemployees

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

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

# 全外

# 交叉连接


/*
sql92 PK sql99

功能：sql99支持的较多
可读性：sql99实现连续条件和筛选条件的分离，可读性较高

类比：
	内连接  就是  交集
	左外    就是  主表（主表与从表的交集+剩余的主表）
	右外    与左外一致
	全外 	就是  并集
	
	如果想要  剩余的主表  就添加条件，从表的key is null
	如果不想要全外的交集  就添加 主表key is null OR 从表的key is null
*/