MySQL 数据库的连接查询和联合查询

[TOC]

## 1. 基础设置
创建三张表，分别为学生表【my_student】、班级表【my_classs】、学生班级关联表【my_studentClasssRelation】,创建语句请[点击这里查看](/blog/info-md.html/35A410F82B2B5445?type=195)

### 1.1 my_student表

| id | birthday   | name   | gender | height | age  | nation |
|----|------------|--------|--------|--------|------|--------|
|  2 | 1989-10-22 | 刘备   | 男     |   1.72 |   58 | 苗     |
|  3 | 2021-11-12 | 刘禅   | 男     |   1.82 |   16 | 汉     |
|  4 | 1990-11-25 | 关羽   | 男     |   1.85 |   52 | 苗     |
|  5 | 1992-09-08 | 张飞   | 男     |   1.82 |   50 | 汉     |
|  6 | 1992-06-12 | 马超   | 男     |   1.77 |   46 | 苗     |
|  7 | 1993-10-22 | 甄宓   | 女     |   1.62 |   38 | 汉     |
|  8 | 1991-11-12 | 小乔   | 女     |   1.56 |   26 | 苗     |
|  9 | 1990-01-25 | 大乔   | 女     |   1.65 |   27 | 汉     |
| 10 | 1988-09-08 | 蔡文姬 | 女     |   1.82 |   30 | 苗     |

### 1.2 my_classs表

| id | name | level | createTime | endTime | endReason |
|----|------|-------|------------|---------|-----------|
|  1 | 1班  |     7 | 2023-05-12 | NULL    | NULL      |
|  2 | 2班  |     7 | 2023-05-12 | NULL    | NULL      |
|  3 | 3班  |     7 | 2023-05-12 | NULL    | NULL      |
|  4 | 1班  |    10 | 2023-05-12 | NULL    | NULL      |
|  5 | 2班  |    10 | 2023-05-12 | NULL    | NULL      |
|  6 | 3班  |    10 | 2023-05-12 | NULL    | NULL      |

### 1.3 my_studentClasssRelation表

| id | student | classs | level | createTime | endTime | endReason |
|----|------|-------|------------|---------|-----------|
|  1 |       2 |      2 |     7 | 2023-05-12 | NULL    | NULL      |
|  2 |       3 |      3 |     7 | 2023-05-12 | NULL    | NULL      |
|  3 |       4 |      4 |    10 | 2023-05-12 | NULL    | NULL      |
|  4 |       5 |      5 |    10 | 2023-05-12 | NULL    | NULL      |
|  5 |       6 |      6 |    10 | 2023-05-12 | NULL    | NULL      |

***

## 2. 连接查询
将多张表进行记录的连接，按照某个指定的条件进行数据拼接，连接后的输出的记录数有可能变化，字段数则一定会增加，因为它最少会有两张表字段合并

__连接查询的意义：__用户查看数据的时候，需要显示的数据来自多张表
__关键字：__`join`，使用方式是 左表 join 右表，左表是在join关键字左表的表，右表是在join关键字右边的表

### 2.1 交叉连接
__关键字：__`cross join`
从一张表中循环取出每一条记录，每条记录都去另外一张表进行匹配，匹配记录一定保留，而连接本身字段会增加，最终形成的结果就是表与表之间的笛卡尔积
__基本语法：__左表 cross join 右表，等效于 from 左表,右表

__示例__

```SQL
select * from my_classs cross join my_studentClasssRelation;
-- my_classs cross join my_studentClasssRelation 是数据源，先连接再查询
```

__结果：__6 * 5 = 30

| id | name | level | createTime | endTime | endReason | id | student | classs | level | createTime | endTime | endReason |
|----|------|-------|------------|---------|-----------|----|---------|--------|-------|------------|---------|-----------|
|  1 | 1班  |     7 | 2023-05-12 | NULL    | NULL      |  5 |       6 |      6 |    10 | 2023-05-12 | NULL    | NULL      |
|  1 | 1班  |     7 | 2023-05-12 | NULL    | NULL      |  4 |       5 |      5 |    10 | 2023-05-12 | NULL    | NULL      |
|  1 | 1班  |     7 | 2023-05-12 | NULL    | NULL      |  3 |       4 |      4 |    10 | 2023-05-12 | NULL    | NULL      |
|  1 | 1班  |     7 | 2023-05-12 | NULL    | NULL      |  2 |       3 |      3 |     7 | 2023-05-12 | NULL    | NULL      |
|  1 | 1班  |     7 | 2023-05-12 | NULL    | NULL      |  1 |       2 |      2 |     7 | 2023-05-12 | NULL    | NULL      |
|  2 | 2班  |     7 | 2023-05-12 | NULL    | NULL      |  5 |       6 |      6 |    10 | 2023-05-12 | NULL    | NULL      |
|  2 | 2班  |     7 | 2023-05-12 | NULL    | NULL      |  4 |       5 |      5 |    10 | 2023-05-12 | NULL    | NULL      |
|  2 | 2班  |     7 | 2023-05-12 | NULL    | NULL      |  3 |       4 |      4 |    10 | 2023-05-12 | NULL    | NULL      |
|  2 | 2班  |     7 | 2023-05-12 | NULL    | NULL      |  2 |       3 |      3 |     7 | 2023-05-12 | NULL    | NULL      |
|  2 | 2班  |     7 | 2023-05-12 | NULL    | NULL      |  1 |       2 |      2 |     7 | 2023-05-12 | NULL    | NULL      |
|  3 | 3班  |     7 | 2023-05-12 | NULL    | NULL      |  5 |       6 |      6 |    10 | 2023-05-12 | NULL    | NULL      |
|  3 | 3班  |     7 | 2023-05-12 | NULL    | NULL      |  4 |       5 |      5 |    10 | 2023-05-12 | NULL    | NULL      |
|  3 | 3班  |     7 | 2023-05-12 | NULL    | NULL      |  3 |       4 |      4 |    10 | 2023-05-12 | NULL    | NULL      |
|  3 | 3班  |     7 | 2023-05-12 | NULL    | NULL      |  2 |       3 |      3 |     7 | 2023-05-12 | NULL    | NULL      |
|  3 | 3班  |     7 | 2023-05-12 | NULL    | NULL      |  1 |       2 |      2 |     7 | 2023-05-12 | NULL    | NULL      |
|  4 | 1班  |    10 | 2023-05-12 | NULL    | NULL      |  5 |       6 |      6 |    10 | 2023-05-12 | NULL    | NULL      |
|  4 | 1班  |    10 | 2023-05-12 | NULL    | NULL      |  4 |       5 |      5 |    10 | 2023-05-12 | NULL    | NULL      |
|  4 | 1班  |    10 | 2023-05-12 | NULL    | NULL      |  3 |       4 |      4 |    10 | 2023-05-12 | NULL    | NULL      |
|  4 | 1班  |    10 | 2023-05-12 | NULL    | NULL      |  2 |       3 |      3 |     7 | 2023-05-12 | NULL    | NULL      |
|  4 | 1班  |    10 | 2023-05-12 | NULL    | NULL      |  1 |       2 |      2 |     7 | 2023-05-12 | NULL    | NULL      |
|  5 | 2班  |    10 | 2023-05-12 | NULL    | NULL      |  5 |       6 |      6 |    10 | 2023-05-12 | NULL    | NULL      |
|  5 | 2班  |    10 | 2023-05-12 | NULL    | NULL      |  4 |       5 |      5 |    10 | 2023-05-12 | NULL    | NULL      |
|  5 | 2班  |    10 | 2023-05-12 | NULL    | NULL      |  3 |       4 |      4 |    10 | 2023-05-12 | NULL    | NULL      |
|  5 | 2班  |    10 | 2023-05-12 | NULL    | NULL      |  2 |       3 |      3 |     7 | 2023-05-12 | NULL    | NULL      |
|  5 | 2班  |    10 | 2023-05-12 | NULL    | NULL      |  1 |       2 |      2 |     7 | 2023-05-12 | NULL    | NULL      |
|  6 | 3班  |    10 | 2023-05-12 | NULL    | NULL      |  5 |       6 |      6 |    10 | 2023-05-12 | NULL    | NULL      |
|  6 | 3班  |    10 | 2023-05-12 | NULL    | NULL      |  4 |       5 |      5 |    10 | 2023-05-12 | NULL    | NULL      |
|  6 | 3班  |    10 | 2023-05-12 | NULL    | NULL      |  3 |       4 |      4 |    10 | 2023-05-12 | NULL    | NULL      |
|  6 | 3班  |    10 | 2023-05-12 | NULL    | NULL      |  2 |       3 |      3 |     7 | 2023-05-12 | NULL    | NULL      |
|  6 | 3班  |    10 | 2023-05-12 | NULL    | NULL      |  1 |       2 |      2 |     7 | 2023-05-12 | NULL    | NULL      |

__交叉连接存在的价值：__保证连接这种结构的完整，本身这类笛卡尔积方式的查询没有意义，应该尽量避免此类查询

***

### 2.2 内连接
__关键字：__[inner] join

从左表中取出每一条记录，去与右表中的所有记录进行匹配，匹配必须是某个条件在左表中与右表中相同，最终才会保留结果，否则不保留

__语法：__左表 [inner] join 右表 ON 左表.字段 = 右表.字段，on 表示连接条件，条件字段就是代表相同的业务含义，如上面表的学生表与学生班级关联表的`my_student.id` 和 `my_studentClasssRelation.student`

__示例__

```SQL
select * from my_student s inner join my_studentClasssRelation r on s.id = r.student;
```

__结果__

> __表连接时，表名太长可以使用别名，输出的字段也有可能出现重复的情况，也建议使用别名__

+ 内连接可以没有连接条件，没有on之后的内容，这时系统会保留所有记录，形成笛卡尔积，例如下面的SQL

```SQL
select * from my_student s inner join my_studentClasssRelation r ;
```

+ 内连接可以没有连接条件，没有on之后的内容，可以使用 where 代替 on 关键字，单不建议这样做，因为 where 没有 on 效率高，例如下面的SQL

```SQL
select * from my_student s inner join my_studentClasssRelation r where s.id = r.student;
```

+ 如果内联的连个链接值相同，例如都为id时，可以用using(id)来替换on s.id = r.id,这样可以是代码更简洁

```SQL
select * from my_student s inner join my_studentClasssRelation r using(id);
```

***

### 2.3 外连接

以某张表为主，取出里面的所有记录，然后每条与另外一张表进行连接，不管能不能匹配上条件，最终都会保留，能匹配则正确保留，不能匹配则其它表的字段都置空，为NULL

__关键字：__[outer] join

__语法：__左表 left | right join 右表 ON 左表.字段 = 右表.字段 外连接 on 连接条件是必须的

#### 2.3.1 左外连接
左连接：以左表为主表

__关键字：__left join

__示例__

```SQL
-- 左表为主表，最终记录数至少不少于左表已有的记录数
select * from my_student s left join my_studentClasssRelation r on s.id = r.student;
```

__结果__

| id | birthday   | name   | gender | height | age  | nation | id   | student | classs | level | createTime | endTime | endReason |
|----|------|-------|------------|---------|-----------|----|---------|--------|-------|------------|---------|-----------|
|  2 | 1989-10-22 | 刘备   | 男     |   1.72 |   58 | 苗     |    1 |       2 |      2 |     7 | 2023-05-12 | NULL    | NULL      |
|  3 | 2021-11-12 | 刘禅   | 男     |   1.82 |   16 | 汉     |    2 |       3 |      3 |     7 | 2023-05-12 | NULL    | NULL      |
|  4 | 1990-11-25 | 关羽   | 男     |   1.85 |   52 | 苗     |    3 |       4 |      4 |    10 | 2023-05-12 | NULL    | NULL      |
|  5 | 1992-09-08 | 张飞   | 男     |   1.82 |   50 | 汉     |    4 |       5 |      5 |    10 | 2023-05-12 | NULL    | NULL      |
|  6 | 1992-06-12 | 马超   | 男     |   1.77 |   46 | 苗     |    5 |       6 |      6 |    10 | 2023-05-12 | NULL    | NULL      |
|  7 | 1993-10-22 | 甄宓   | 女     |   1.62 |   38 | 汉     | NULL |    NULL |   NULL |  NULL | NULL       | NULL    | NULL      |
|  8 | 1991-11-12 | 小乔   | 女     |   1.56 |   26 | 苗     | NULL |    NULL |   NULL |  NULL | NULL       | NULL    | NULL      |
|  9 | 1990-01-25 | 大乔   | 女     |   1.65 |   27 | 汉     | NULL |    NULL |   NULL |  NULL | NULL       | NULL    | NULL      |
| 10 | 1988-09-08 | 蔡文姬 | 女     |   1.82 |   30 | 苗     | NULL |    NULL |   NULL |  NULL | NULL       | NULL    | NULL      |

#### 2.3.2 右外连接
右连接：以右表为主表

__关键字：__right join

__示例__

```SQL
-- 右表为主表，最终记录数至少不少于右表已有的记录数
select * from my_student s right join my_studentClasssRelation r on s.id = r.student;
```

__结果__

***

### 2.4 自然连接

自动匹配连接条件，系统以字段名字作为匹配模式，同字段就作为条件，多个同名字段都作为条件，自然连接又分为自然内连接和自然外连接

__关键字：__natural join

#### 2.4.1 自然内连接

__语法：__左表 natural join 右表 ，自然连接自动使用同名字段作为连接条件，连接之后会合并同名字段

__示例__

```SQL
-- 自然内连接，连接之后会合并同名字段id，结果只有一列id字段
select * from my_student s natural join my_studentClasssRelation;
```

__结果__

#### 2.4.2 自然外连接

__语法：__左表 natural left join 右表

__示例__

```SQL
-- 自然外连接，连接之后会合并同名字段id，结果只有一列id字段
select * from my_student s natural left join my_studentClasssRelation;
```

__结果__

| id | birthday   | name   | gender | height | age  | nation | student | classs | level | createTime | endTime | endReason |
|----|------|-------|------------|---------|-----------|----|---------|--------|-------|------------|---------|
|  2 | 1989-10-22 | 刘备   | 男     |   1.72 |   58 | 苗     |       3 |      3 |     7 | 2023-05-12 | NULL    | NULL      |
|  3 | 2021-11-12 | 刘禅   | 男     |   1.82 |   16 | 汉     |       4 |      4 |    10 | 2023-05-12 | NULL    | NULL      |
|  4 | 1990-11-25 | 关羽   | 男     |   1.85 |   52 | 苗     |       5 |      5 |    10 | 2023-05-12 | NULL    | NULL      |
|  5 | 1992-09-08 | 张飞   | 男     |   1.82 |   50 | 汉     |       6 |      6 |    10 | 2023-05-12 | NULL    | NULL      |
|  6 | 1992-06-12 | 马超   | 男     |   1.77 |   46 | 苗     |    NULL |   NULL |  NULL | NULL       | NULL    | NULL      |
|  7 | 1993-10-22 | 甄宓   | 女     |   1.62 |   38 | 汉     |    NULL |   NULL |  NULL | NULL       | NULL    | NULL      |
|  8 | 1991-11-12 | 小乔   | 女     |   1.56 |   26 | 苗     |    NULL |   NULL |  NULL | NULL       | NULL    | NULL      |
|  9 | 1990-01-25 | 大乔   | 女     |   1.65 |   27 | 汉     |    NULL |   NULL |  NULL | NULL       | NULL    | NULL      |
| 10 | 1988-09-08 | 蔡文姬 | 女     |   1.82 |   30 | 苗     |    NULL |   NULL |  NULL | NULL       | NULL    | NULL      |

## 3. 联合查询
将多个表的记录合并起来显示，由多条select语句构成，每一条select语句获取的字段数必须严格一致，字段数量不会添加，语句的字段数量一致，但对数据类型没有要求

### 3.1 删除重复合并 union
使用`union`关键字在合并表记录时，会对最终的结果进行删除重复的操作，它省略了Distinct去重关键字

__语法：__select 语句1 union select 语句2 ……

```SQL
-- 学生表数据量过大时，会对表进行拆分操作，这时如果要查询全部的学生信息，则需要使用union合并了表数据了
select * from my_student union select * from my_student_bak;
```

### 3.1 保留重复合并 union all
使用`union all`关键字在合并表记录时，会保留全部的记录，包含重复的记录
__语法：__查询表1的结果 union all 查询表2的结果

```SQL
select * from my_student union all select * from my_student_bak;
```

> __如果记录中有相同的记录union会执行删除重复的操作，这样在数据量大的情况下会影响性能，所以使用union all可以不执行删除重复的操作，显示全部信息__

__联合查询的意义：__

+ 查询同一张表，但需求不同，例如查询学生信息，男生身高升序，女生身高降序

```SQL
(select * from my_student where gender = '男' order by height asc limit 999999)
union
(select * from my_student where gender = '女' order by height desc limit 999999);
```

| id | birthday   | name   | gender | height | age  | nation |
|----|------------|--------|--------|--------|------|--------|
|  6 | 1992-06-12 | 马超   | 男     |   1.77 |   46 | 苗     |
|  3 | 2021-11-12 | 刘禅   | 男     |   1.82 |   16 | 汉     |
|  5 | 1992-09-08 | 张飞   | 男     |   1.82 |   50 | 汉     |
|  4 | 1990-11-25 | 关羽   | 男     |   1.85 |   52 | 苗     |
| 10 | 1988-09-08 | 蔡文姬 | 女     |   1.82 |   30 | 苗     |
|  9 | 1990-01-25 | 大乔   | 女     |   1.65 |   27 | 汉     |
|  7 | 1993-10-22 | 甄宓   | 女     |   1.62 |   38 | 汉     |
|  8 | 1991-11-12 | 小乔   | 女     |   1.56 |   26 | 苗     |

> __Order by 在联合查询中的使用方式__
+ 在联合查询中，order by 不能直接使用，需要对查询语句使用括号
+ 若要order by 生效，必须搭配 limit，limit使用限定的最大数即可

+ 多表查询，多张表的结构是完全一样的，保存的数据结构也是一样的