MySQL 数据库的数据查询之分组、排序、分页、子查询

[TOC]

## 1. 预置条件
### 1.1. 基础设置
准备一张学生表【my_student】,一张学生班级关联表【my_studentClasssRelation】,创建语句请[点击这里查看](/blog/info-md.html/35A410F82B2B5445?type=195)
### 1.2. 表数据

学生表

| 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 | 苗     |

学生班级关联表

| 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. 分组子句 Group By

+ group by 子句，根据某个字段进行分组，相同的放在一组，不同的分到其它的组
+ 分组的意义就是，为了统计数据，按分组字段进行数据统计
+ 分组会自动排序，根据分组字段默认升序
 - group by 字段 [asc|desc]，对分组的结果合并之后的整个结果进行排序

__语法：__select 字段1,字段2,count(0),…… from 表 group by 字段1,字段2,……，select 输出的字段必须与 `group by` 后面分组的字段一致，或是统计类函数

### 2.1. 分组常用统计函数

|函数|说明|备注|
|--|--|--|
|COUNT()|分组后，每组的记录总数|count()函数中可以是\*符号，也可以是0，还可以是字段名，为字段名时，字段中有NULL的数据不参与统计，\*符号和0统计的效果一样|
|MAX()|按统计字段获取每组中最大的值||
|MIN()|按统计字段获取每组中最小的值||
|AVG()|按统计字段获取每组中平均值||
|SUM()|按统计字段获取每组中的和|&nbsp;|

__示例__

```SQL
-- 按性别gender字段分组，统计年龄信息
select gender ,sum(age),avg(age),max(age),min(age),count(*) from my_student group by gender;
```

__结果__

| gender | sum(age) | avg(age) | max(age) | min(age) | count(\*) |
|--------|----------|----------|----------|----------|----------|
| 男     |      222 |  44.4000 |       58 |       16 |        5 |
| 女     |      121 |  30.2500 |       38 |       26 |        4 |

### 2.2. 多字段分组
先根据一个字段进行分组，然后对分组的结果再次按照其它字段进行分组，直到最后一个分组字段分组结束

__示例__

```SQL
-- 按性别gender和民族nation字段分组，统计信息
select gender ,nation ,count(*) from my_student group by gender,nation;
```

__结果__

| gender | nation | count(\*) |
|--------|--------|----------|
| 男     | 苗     |        3 |
| 男     | 汉     |        2 |
| 女     | 汉     |        2 |
| 女     | 苗     |        2 |

### 2.3. 分组字符串连接函数
group_concat() 函数可以对分组的结果中的某个字段进行字符串拼接，用,逗号分隔

__示例__

```SQL
-- 按性别gender和民族nation字段分组，统计信息
select gender ,nation ,count(*),group_concat(name) from my_student group by gender,nation;
```

__结果__

| gender | nation | count(\*) | group_concat(name) |
|--------|--------|----------|----------------------|
| 女     | 汉     |        2 | 甄宓,大乔          |
| 女     | 苗     |        2 | 小乔,蔡文姬        |
| 男     | 汉     |        2 | 刘禅,张飞          |
| 男     | 苗     |        3 | 刘备,关羽,马超     |

### 2.4. 回溯统计
使用 with rollup 进行回溯统计，回溯统计是指，根据当前分组的字段，分组后向上级分组进行汇报统计，回溯统计的时候会将分组字段置空

__示例一__

```SQL
-- 按性别gender字段分组，统计信息
select gender  ,count(*),group_concat(name) from my_student group by gender with rollup;
```

__结果__

| gender | count(\*) | group_concat(name) |说明|
|--------|----------|----------------------|--|
| 女     |        4 | 甄宓,小乔,大乔,蔡文姬||
| 男     |        5 | 刘备,刘禅,关羽,张飞,马超||
| NULL   |        9 | 甄宓,小乔,大乔,蔡文姬,刘备,刘禅,关羽,张飞,马超 |回溯统计，将分组后的数据再汇总统计|

__示例二__

```SQL
-- 按性别gender和名族nation字段分组，统计信息
select gender ,nation ,count(*),group_concat(name) from my_student group by gender,nation with rollup;
```

__结果__

| gender | nation | count(\*) | group_concat(name) |说明|
|--------|--------|----------|----------------------|--|
| 女     | 汉     |        2 | 甄宓,大乔||
| 女     | 苗     |        2 | 小乔,蔡文姬||
| 女     | NULL   |        4 | 甄宓,大乔,小乔,蔡文姬|回溯统计，按性别女，将民族分组后的数据再汇总统计|
| 男     | 汉     |        2 | 刘禅,张飞||
| 男     | 苗     |        3 | 刘备,关羽,马超||
| 男     | NULL   |        5 | 刘禅,张飞,刘备,关羽,马超|回溯统计，按性别男，将民族分组后的数据再汇总统计|
| NULL   | NULL   |        9 | 甄宓,大乔,小乔,蔡文姬,刘禅,张飞,刘备,关羽,马超 |回溯统计，按性别，民族将分组后的数据再汇总统计|

多字段回溯，第一层分组会有一次回溯，第二次分组要看第一次分组的组数，组数是多少，回溯就是多少，然后加上第一层回溯

## 3. 分组条件判断子句 Having
__说明：__与 Where 子句一样，是条件判断子句

__Q：__为什么有 Where 子句，还需要Having子句
__A：__Where 子句是针对磁盘进行判断，进入内存之后，如果有对数据进行分组，分组结果需要判断筛选，这时 Where 就不起作用了，因为 Group by 分组子句在 Where 子句之后，所以一种新的判断 Having 子句诞生了，分组结果筛选需要使用 Having 子句

Having 能做 Where 能做的几乎所有的事情，但 Where 却不能做 Having 能做的很多事情，例如下面的示例

### 3.1. Having使用
分组统计的结果或者说统计函数都只有 Having 能够使用

__示例__

```SQL
-- 得到民族人数大于4的记录
select nation ,count(*),group_concat(name) from my_student group by nation having count(*) > 4;
```

__结果__

| nation | count(\*) | group_concat(name)|
|--|--|--|
| 苗 | 5 | 刘备,关羽,马超,小乔,蔡文姬|

### 3.2. Having别名使用
Having 可以使用字段别名，Where 不能，从磁盘取数据时，名字只能是字段名，别名是在字段进入到内存后才会产生

__示例__

```SQL
-- 得到民族人数大于4的记录
select nation ,count(*) AS total,group_concat(name) from my_student group by nation having total > 4;
-- 输出：下面的结果记录
select nation ,count(*) AS total,group_concat(name) from my_student where total > 4 group by nation;
-- 输出：错误提示 Unknown column 'total' in 'where clause'
```

__结果__

| nation | total | group_concat(name)|
|--|--|--|
| 苗 | 5 | 刘备,关羽,马超,小乔,蔡文姬|

## 4. 排序子句 Order by
Order by 排序子句，根据某些字段进行升序或降序排序，依赖校对集，

### 4.1. 单字段排序

__语法：__Order by 字段名 [asc|desc]，默认是升序ASC

__示例一__

```SQL
-- 按性别进行排序
select * from my_student order by gender asc;
```

__结果__

| id | birthday   | name   | gender | height | age  | nation |
|----|------------|--------|--------|--------|------|--------|
|  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 | 苗     |
|  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 | 苗     |

### 4.2. 多字段排序
排序可以进行多字段排序，先根据某个字段进行排序，然后排序完后的内容，再按照第二字段进行再次排序

__示例二__

```SQL
-- 先按性别，再按身高进行排序
select * from my_student order by gender asc, height desc;
```

__结果__

| id | birthday   | name   | gender | height | age  | nation |
|----|------------|--------|--------|--------|------|--------|
| 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 | 苗     |
|  4 | 1990-11-25 | 关羽   | 男     |   1.85 |   52 | 苗     |
|  3 | 2021-11-12 | 刘禅   | 男     |   1.82 |   16 | 汉     |
|  5 | 1992-09-08 | 张飞   | 男     |   1.82 |   50 | 汉     |
|  6 | 1992-06-12 | 马超   | 男     |   1.77 |   46 | 苗     |
|  2 | 1989-10-22 | 刘备   | 男     |   1.72 |   58 | 苗     |

### 4.3. 排序后前几行数据

**语法：**select * from 表 order by age limit 数量

**示例：**按年龄排序显示前2行数据

```SQL
select * from my_student order by age limit 2;
```

**示例结果：**

| id | birthday   | name | gender | height | age  | nation |
|----|------------|------|--------|--------|------|--------|
|  8 | 1991-11-12 | 小乔 | 女     |   1.56 |   26 | 苗     |
|  9 | 1990-01-25 | 大乔 | 女     |   1.65 |   27 | 汉     |

### 4.4. 排序后指定范围内前几行数据

`offset` 关键字是指向后偏移量，offset后面的数值+1才是显示的开始范围

**示例：**按年龄排序显示从第3行开始显示1条记录

```SQL
select * from my_student order by age limit 1 offset 2;
```

**示例结果：**

| id | birthday   | name | gender | height | age  | nation |
|----|------------|------|--------|--------|------|--------|
| 10 | 1988-09-08 | 蔡文姬 | 女     |   1.82 |   30 | 苗     |

## 5. 限制结果子句 Limit
Limit 子句是一种限制结果的语句，限制查询数量

### 5.1. limit 限制查询数量

只用来限制数据量，限制查询记录的数量

__示例__

```SQL
-- 查询前两条记录
select * from my_student limit 2;
```

__结果__

### 5.2. limit 分页

limit 限制起始位置，再限制数量

__语法：__limit 起始位置(offset),长度(length); 起始位置是从0开始的，分页查询时需注意

__分页：__这种方式一般用来进行数据分页查询，length：每页显示的数据量，offset：分页的起始位置，offset=(页码-1) * length

__示例__

```SQL
-- 从指定的位置开始查询2条记录
select * from my_student limit 0,2; -- 从第1条记录开始查询2条记录（起始位置是从0开始计算）
select * from my_student limit 2,2; -- 从第3条记录开始查询2条记录
select * from my_student limit 4,2; -- 从第5条记录开始查询2条记录
```

__结果__

第一条语句

第二条语句

| id | birthday   | name | gender | height | age  | nation |
|----|------------|--------|--------|--------|------|--------|
|  4 | 1990-11-25 | 关羽 | 男     |   1.85 |   52 | 苗     |
|  5 | 1992-09-08 | 张飞 | 男     |   1.82 |   50 | 汉     |

第三条语句

| id | birthday   | name | gender | height | age  | nation |
|----|------------|--------|--------|--------|------|--------|
|  6 | 1992-06-12 | 马超 | 男     |   1.77 |   46 | 苗     |
|  7 | 1993-10-22 | 甄宓 | 女     |   1.62 |   38 | 汉     |

## 6. 子查询
子查询是sub query，查询是在某个查询结果之上进行的，一条select语句内部包含了另外一条select语句，子查询有两种分类，按位置分类和按结果分类

+ 按位置分类：子查询（select 语句）在外部查询（select 语句）中出现的位置
 - from子查询：子查询跟在from关键字之后
 - where子查询：子查询出现where条件中
 - exists子查询：子查询出现在exists里面
+ 按结果分类：根据子查询得到的数据进行分类，理论上讲任何一个查询得到的结果都可以理解为二维表
 - 标量子查询：子查询得到的结果是一行一列，出现的位置是在where之后
 - 列子查询：子查询得到的结果是一列多行，出现的位置是在where之后
 - 行子查询：子查询得到的结果是多列一行，多列多行也可以，出现的位置是在where之后
 - 表子查询：子查询得到的结果是多行多列，出现的位置是在form之后

### 6.1. 标量子查询
__语法：__select * from 表1 where 字段 = (select 字段 from 表2 where 字段2 = 值)，括号里面查询的结果必须是`一行一列`的数据

```SQL
-- 查询班级id为3的所有学生信息
select * from my_student where id = (select student from my_studentclasssrelation where classs = 3);
```

| id | birthday   | name | gender | height | age  | nation |
|----|----|----|----|----|----|----|
|  3 | 2021-11-12 | 刘禅 | 男     |   1.82 |   16 | 汉     |

### 6.2. 列子查询
__语法：__select * from 表1 where 字段 in (select 字段 from 表2 where 字段2 = 值)，括号里面查询的结果必须是`一列多行`的数据

#### 6.2.1 in查询

```SQL
-- 查询班级id为3和4的所有学生信息
select * from my_student where id in (select student from my_studentclasssrelation where classs in(3,4));
```

| id | birthday   | name | gender | height | age  | nation |
|----|----|----|----|----|----|----|
|  3 | 2021-11-12 | 刘禅 | 男     |   1.82 |   16 | 汉     |
|  4 | 1990-11-25 | 关羽 | 男     |   1.85 |   52 | 苗     |

#### 6.2.2 not in查询

```SQL
-- 查询班级id除了为3和4的所有学生信息
select * from my_student where id not in (select student from my_studentclasssrelation where classs in(3,4));
```

| id | birthday   | name | gender | height | age  | nation |
|----|----|----|----|----|----|----|
|  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 | 苗     |

#### 6.2.3 = any查询

```SQL
-- 查询班级id为3和4的所有学生信息
select * from my_student where id = any (select student from my_studentclasssrelation where classs in(3,4));
```

> = any查询等价于in查询

#### 6.2.4 != any查询

```SQL
select * from my_student where id != any (select student from my_studentclasssrelation where classs in(3,4));
```

| id | birthday   | name | gender | height | age  | nation |
|----|----|----|----|----|----|----|
|  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 | 苗     |

> != any查询需要同时在子查询中不出现，示例中每条记录的id都只有一个值，不可能满足同时不在子查询结果中，但id为null除外，null不参与比较

#### 6.2.5 = some查询

```SQL
-- 查询班级id为3和4的所有学生信息
select * from my_student where id = some (select student from my_studentclasssrelation where classs in(3,4));
```

> = any查询等价于in查询等价于= some查询

#### 6.2.6 ！= some查询

```SQL
select * from my_student where id != some (select student from my_studentclasssrelation where classs in(3,4));
```

> != some查询需要同时在子查询中不出现，示例中每条记录的id都只有一个值，不可能满足同时不在子查询结果中，但id为null除外，null不参与比较，!= some查询等价于!= any查询

#### 6.2.7 = all查询

```SQL
-- 查询班级id为3和4的所有学生信息
select * from my_student where id = all (select student from my_studentclasssrelation where classs in(3,4));
```

查询结果不存在`Empty set`

> = all查询需要同时满足子查询的所有数据

#### 6.2.8 != all查询

```SQL
select * from my_student where id != all (select student from my_studentclasssrelation where classs in(3,4));
```

> != all查询只要结果不在子查询的结果中出现即可，和not in查询是等价的，但id为null除外，null不参与比较

### 6.3. 行子查询
行子查询，必须要先构造行元素，多个字段使用逗号分隔，然后使用括号包裹即是一个行元素

__语法：__select * from 表1 where (字段,字段2) = (select 字段,字段2 from 表2 where 字段2 = 值)，括号里面查询的结果可以是`多行多列`，也可以是`一行多列`的数据

```SQL
-- 查询班级里面年龄最大，身高最高的学生
select * from my_student where (age,height) = (select max(age),max(height) from my_student);
```

| id | birthday   | name | gender | height | age  | nation |
|----|----|----|----|----|----|----|
|  4 | 1990-11-25 | 关羽 | 男     |   1.85 |   52 | 苗     |

### 6.4. 表子查询
子查询返回的结果是当做二维表来使用，这个也可以称之为from子查询

__语法：__select * from (select * from 表2 where 字段2 = 值) as 表别名

```SQL
-- 查询班级里面每个民族身高最高的学生
select s.* from my_student s,(select nation,max(height) height from my_student group by nation) as t where s.nation=t.nation and s.height = t.height
```

### 6.5. Exists子查询
Exists子查询指的是是否存在的意思，用来判断某些条件是否满足，exists返回的结果是0或1

__语法：__select * from 表1 where exists (select 表2.字段 from 表2 where 表2.字段2 = 表1.字段)

```SQL
-- 查询已经有班级的学生信息
select * from my_student s where exists (select * from my_studentclasssrelation r where r.student =s.id);
```