MySQL笔记 07：多表连接查询

1. 分类

---

1.1 按年代分类

1. sql92标准：仅支持内连接
2. sql99标准【推荐】：支持内连接 + 交叉连接 + 左/右外连接

1.2 按功能分类

1. 内连接：等值连接，非等值连接，自连接
2. 外连接：左外连接，右外连接，全外连接
3. 交叉连接

2. sql92标准

---

2.1 等值连接

案例：查询男神名和对应的女神名

SELECT 
  `beauty`.`name`,
  `boys`.`boyName` 
FROM
  `beauty`,
  `boys` 
WHERE `beauty`.`boyfriend_id` = `boys`.`id` ;

案例：查询员工名和对应的部门名

SELECT 
  `last_name`,
  `department_name` 
FROM
  `departments`,
  `employees` 
WHERE `employees`.`department_id` = `departments`.`department_id` ;

案例：【为表取别名】查询员工名，工种号，工种名

SELECT 
  `last_name`,
  `e`.`job_id`,
  `job_title` 
FROM
  `jobs` AS j,
  `employees` AS e 
WHERE `j`.`job_id` = `e`.`job_id` ;

---

总结1

别名的使用可以：

1. 提高语句的简洁度
2. 区分多个重名的字段

但是要注意：如果取了别名，就不能再使用表名限定

---

案例：【添加筛选条件】查询有奖金的员工名和部门名

SELECT 
  `last_name`,
  `department_name`,
  `commission_pct` 
FROM
  `departments` AS d,
  `employees` AS e 
WHERE `d`.`department_id` = `e`.`department_id` 
  AND `commission_pct` IS NOT NULL ;

案例：【添加筛选条件】查询城市名第二个字符为o的部门名和城市名

SELECT 
  `department_name`,
  `city` 
FROM
  `departments` AS d,
  `locations` AS l 
WHERE `l`.`location_id` = `d`.`location_id` 
  AND `city` LIKE "_o%" ;

案例：【添加分组条件】查询每个城市的部门个数

SELECT 
  `city` AS '城市名',
  COUNT(*) AS '部门个数' 
FROM
  `departments` AS d,
  `locations` AS l 
WHERE `l`.`location_id` = `d`.`location_id` 
GROUP BY `city` ;

案例：【添加分组条件】查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资

SELECT 
  `department_name`,
  `d`.`manager_id`,
  MIN(`salary`) 
FROM
  `departments` AS d,
  `employees` AS e 
WHERE `e`.`department_id` = `d`.`department_id` 
  AND `commission_pct` IS NOT NULL 
GROUP BY `d`.`department_id` ;

案例：【添加排序条件】查询每个工种的工种名和员工的个数，按照工种名降序排列

SELECT 
  `job_title`,
  COUNT(*) 
FROM
  `jobs` AS j,
  `employees` AS e 
WHERE `j`.`job_id` = `e`.`job_id` 
GROUP BY `j`.`job_id` 
ORDER BY COUNT(*) DESC ;

案例：【三表连接】查询员工名，部门名，和所在城市名

SELECT 
  `last_name`,
  `department_name`,
  `city` 
FROM
  `employees` AS e,
  `departments` AS d,
  `locations` AS l 
WHERE `e`.`department_id` = `d`.`department_id` 
  AND `l`.`location_id` = `d`.`location_id` ;

---

总结2

1. 多表等值连接的结果为多表的交集部分
2. n表连接，至少需要n-1个连接条件
3. 多表顺序没有要求
4. 一般需要为表取别名
5. 可以搭配所有的查询子句使用

---

2.2 非等值连接

案例：查询员工的工资和工资级别

SELECT 
  `last_name`,
  `salary`,
  `grade_level` 
FROM
  `employees` AS e,
  `job_grades` AS jd 
WHERE `salary` BETWEEN `lowest_sal` 
  AND `highest_sal` ;

2.3 自连接

案例：查询员工名以及上级的名称

SELECT 
  e.`employee_id`,
  e.`last_name`,
  e.`manager_id`,
  m.`last_name` 
FROM
  `employees` AS e,
  `employees` AS m 
WHERE e.`manager_id` = m.`employee_id` ;

3. sql99标准

select 查询列表
from 表1 别名 【连接类型】
join 表2 别名
on 连接条件（内连接 inner，左外连接 left outer，右外连接 right outer，全外连接 full outer，交叉连接 cross）
【where 筛选条件】
【group by 分组条件】
【having 筛选条件】
【order by 排序列表】

3.1 内连接 - 等值连接

案例：查询员工名和对应的部门名

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%" ;

案例：查询部门个数大于3的城市名和部门个数

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 ;

案例：查询员工个数大于3的部门名和员工个数，并按照个数降序排序

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 ;

---

总结1

1. 可以添加排序，分组，筛选
2. inner可以省略
3. 筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读
4. inner join连接和sql92语法中的等值连接效果是一样的，都是查询多表的交集

---

3.2 内连接 - 非等值连接

案例：查询员工的工资和工资级别

SELECT 
  `last_name`,
  `salary`,
  `grade_level` 
FROM
  `employees` AS e 
  INNER JOIN `job_grades` AS jd 
    ON `salary` BETWEEN `lowest_sal` 
    AND `highest_sal` 

案例：查询工资级别的员工个数大于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 

3.3 内连接 - 自连接

案例：查询员工名以及上级的名称

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%" 

3.4 外连接

案例：查询男朋友不在男神表的女神名

-- 左外连接
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 

---

总结2

1. 用于查询一个表中有，另一个表中没有的数据
2. 左外连接中，左侧为主表；右外连接，右侧为主表；左外和右外交换表的顺序可以实现同样的效果
3. 外连接的查询结果为主表中的所有数据，如果从表中有与之匹配的，就显示匹配记录，否则显示null
4. 全外连接会将左外连接和右外连接的结果组合在一起

---

3.5 交叉连接

两表的笛卡尔乘积

SELECT 
  * 
FROM
  `beauty` 
  CROSS JOIN `boys`