Select进阶查询·连接查询

一、连接查询定义：

• 把多张表通过连接条件临时组成1张新表，在临时的新表里有连接表的所有表头和数据。
• 连接查询分类：内连接、外连接、全连接

二、连接语法结构：

内连接-语法格式：

1. SELECT 表头名列表 FROM 表1 INNER JOIN 表2；
2. SELECT 表头名列表 FROM 表1 INNER JOIN 表2 ON 连接条件;
3. SELECT 表头名列表 FROM 表1 INNER JOIN 表2 ON 连接条件
   WHERE 筛选条件|GROUP BY 分组|HAVING 分组后筛选|ORDER BY 排序列表

外连接-语法格式：

左外连接：

SELECT 表头名列表 FROM 表1 LEFT JOIN 表2 ON 连接条件
WHERE 筛选条件|GROUP BY 分组|HAVING 分组后筛选|ORDER BY 排序列表

右外连接：

SELECT 表头名列表 FROM 表1 RIGHT JOIN 表2 ON 连接条件
WHERE 筛选条件|GROUP BY 分组|HAVING 分组后筛选|ORDER BY 排序列表

全连接-语法格式：

1. (SELECT语句 )UNION(SELECT语句);
2. (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)