一、连接查询定义：
- 把多张表通过连接条件临时组成1张新表，在临时的新表里有连接表的所有表头和数据。
- 连接查询分类：
内连接、外连接、全连接
二、连接语法结构：
内连接-语法格式：
- SELECT 表头名列表 FROM 表1
INNER JOIN表2；- SELECT 表头名列表 FROM 表1
INNER JOIN表2ON连接条件;- SELECT 表头名列表 FROM 表1
INNER JOIN表2ON连接条件
WHERE 筛选条件|GROUP BY 分组|HAVING 分组后筛选|ORDER BY 排序列表
外连接-语法格式：
左外连接：
SELECT 表头名列表 FROM 表1
LEFT JOIN表2ON连接条件
WHERE 筛选条件|GROUP BY 分组|HAVING 分组后筛选|ORDER BY 排序列表右外连接：
SELECT 表头名列表 FROM 表1
RIGHT JOIN表2ON连接条件
WHERE 筛选条件|GROUP BY 分组|HAVING 分组后筛选|ORDER BY 排序列表
全连接-语法格式：
- (SELECT语句 )
UNION(SELECT语句);- (SELECT语句 )
UNION ALL(SELECT语句);
三、内连接：
- 等值连接：使用相等判断做连接条件
- 非等值连接：连接条件不是相等判断
- 自连接： 自己连接自己，把1张表当做2张表（需要给表定义别名）
案例：
- 等值连接案例：
# 查询每个员工姓名、部门编号、部门名称
mysql> select name,e.dept_id,dept_name from employees as e
-> inner join departments as d on e.dept_id=d.dept_id;
+-----------+---------+-----------+
| name | dept_id | dept_name |
+-----------+---------+-----------+
| 梁伟 | 1 | 人事部 |
| 郭岩 | 1 | 人事部 |
...
| 杨金凤 | 8 | 法务部 |
+-----------+---------+-----------+
133 rows in set (0.00 sec)
- 非等值连接： 使用非相等做判断做连接条件
# 查询2018年12月员工基本工资级别，员工需要显示姓名
mysql> select name as 姓名, date as 发工资日期, basic as 基本工资, grade as 工资等级
-> from employees as e inner join salary as s on e.employee_id=s.employee_id
-> inner join wage_grade as g on s.basic between g.low and g.high
-> where year(date)=2018 and month(date)=12;
+-----------+-----------------+--------------+--------------+
| 姓名 | 发工资日期 | 基本工资 | 工资等级 |
+-----------+-----------------+--------------+--------------+
| 梁伟 | 2018-12-10 | 17016 | D |
| 郭岩 | 2018-12-10 | 20662 | E |
| 李玉英 | 2018-12-10 | 9724 | B |
...
| 杨金凤 | 2018-12-10 | 6076 | A |
+-----------+-----------------+--------------+--------------+
120 rows in set (0.00 sec)
- 自连接案例：
# 自己连接自己 把1张当2张表使用
# 就是查询表的时候给表定义别名来实现。
mysql> select e.name, e.hire_date, em.birth_date
-> from employees as e inner join employees as em
-> on month(e.hire_date)=month(em.birth_date) and e.employee_id=em.employee_id;
+-----------+------------+------------+
| name | hire_date | birth_date |
+-----------+------------+------------+
| 李玉英 | 2012-01-19 | 1974-01-25 |
| 郑静 | 2018-02-03 | 1997-02-14 |
...
| 王荣 | 2019-11-14 | 1999-11-22 |
+-----------+------------+------------+
7 rows in set (0.01 sec)
四、外连接：
- 左外连接：左边表的记录全都显示出来 右边的表只显示与条件匹配记录，右边表比左边表少的记录使用NULL匹配
- 右外连接：右边表的记录全都显示出来 左边的表只显示与条件匹配记录，左表比右边表少的记录使用NULL 匹配
- 外连接的应用场景： 比较2个表里记录的不同
案例：
- 左连接查询：
# 输出没有员工的部门名
mysql> select d.dept_name,e.name from departments as d
-> left join employees as e on d.dept_id=e.dept_id
-> where e.name is null;
+-----------+-----------+
| dept_name | name |
+-----------+-----------+
| 小卖部 | NULL |
| 行政部 | NULL |
| 公关部 | NULL |
+-----------+-----------+
3 rows in set (0.00 sec)
- 右连接查询：
# 显示没有部门的员工名
mysql> select e.name ,d.dept_name from departments as d
-> right join employees as e on d.dept_id=e.dept_id
-> where d.dept_name is null;
+------+-----------+
| name | dept_name |
+------+-----------+
| bob | NULL |
| tom | NULL |
| lily | NULL |
+------+-----------+
3 rows in set (0.00 sec)
五、全连接：
- 全连接也称联合查询，用来一起输出多个select查询结果
- 要求查询时，多个select语句查看的表头个数必须一致
- UNION：默认去重，相当于python中的集合
- UNION ALL：包含重复项
案例：
- UNION
mysql> (select "xyz") union (select "xyz");
+-----+
| xyz |
+-----+
| xyz |
+-----+
1 row in set (0.00 sec)
- UNION ALL
mysql> (select "xyz") union all (select "xyz");
+-----+
| xyz |
+-----+
| xyz |
| xyz |
+-----+
2 rows in set (0.00 sec)
- 输出2018年、2019年、2020年， 01月10 号 总工资最高的员工编号和总工资
mysql> (select employee_id ,date,basic,bonus, basic+bonus as total from tarena.salary where date=20180110 order by total desc limit 1)
union
(select employee_id ,date, basic,bonus,basic+bonus as total from tarena.salary where date=20190110 order by total desc limit 1)
union
(select employee_id ,date,basic,bonus, basic+bonus as total from tarena.salary where date=20200110 order by total desc limit 1);
+-------------+------------+-------+-------+-------+
| employee_id | date | basic | bonus | total |
+-------------+------------+-------+-------+-------+
| 37 | 2018-01-10 | 23152 | 11000 | 34152 |
| 37 | 2019-01-10 | 24309 | 11000 | 35309 |
| 53 | 2020-01-10 | 26800 | 11000 | 37800 |
+-------------+------------+-------+-------+-------+
3 rows in set (0.01 sec)
