高级数据操作-查询数据

完整的查询指令

select 选项 字段列表 
from 数据源 

-- 5子句
where 条件 
group by 分组 
having 条件 
order by 排序 
limit 限制;

1、select选项

处理查询到的结果

• all 默认值，表示保存所有记录
• distinct 去重，只保留一条（所有字段都相同认为重复）

create table my_select(
    name varchar(10)
);

insert into my_select (name) values ('A'), ('A'), ('A'), ('B');

mysql> select all * from my_select;
+------+
| name |
+------+
| A    |
| A    |
| A    |
| B    |
+------+

mysql> select distinct * from my_select;
+------+
| name |
+------+
| A    |
| B    |
+------+

2、字段列表

多张表获取数据，可能存在不同表中有同名字段，需要使用别名alias

字段名 [as] 字段别名;

select distinct name as name1, name as name2 from my_select;
+-------+-------+
| name1 | name2 |
+-------+-------+
| A     | A     |
| B     | B     |
+-------+-------+

3、from数据源

为前面的查询提供数据

数据源只要是一个符合二维表结构的数据即可

3.1、单表数据

from 表名;

select * from my_select;

3.2、多表数据

基本语法

from 表名1, 表名2...;

mysql> select * from my_select;
+------+
| name |
+------+
| A    |
| B    |
+------+
2 rows in set (0.00 sec)

mysql> select * from my_student;
+----+--------+
| id | name   |
+----+--------+
|  1 | 刘备   |
|  2 | 李四   |
|  3 | 王五   |
+----+--------+
3 rows in set (0.00 sec)

mysql> select * from my_select, my_student;
+------+----+--------+
| name | id | name   |
+------+----+--------+
| A    |  1 | 刘备   |
| B    |  1 | 刘备   |
| A    |  2 | 李四   |
| B    |  2 | 李四   |
| A    |  3 | 王五   |
| B    |  3 | 王五   |
+------+----+--------+
6 rows in set (0.00 sec)

结果是两张表记录数据相乘，字段数拼接

本质：从第一张表取出一条记录，去拼凑第二张表所有记录，保留所有结果

笛卡尔积，会给数据库造成压力，尽量避免

3.3、动态数据

from后面不是一个实体表，而是一个从表中查询出来得到的二维结果表（子查询）

基本语法

from (select 字段列表 from 表名) as 别名

mysql> select * from (select * from my_student) as t1;
+----+--------+
| id | name   |
+----+--------+
|  1 | 刘备   |
|  2 | 李四   |
|  3 | 王五   |
+----+--------+

4、Where条件

通过运算符进行结果比较，来判断符合条件的数据

5、Group by分组

根据指定的字段，将数据进行分组，分组的目的是为了统计

5.1、分组统计

group by 字段名

分组后，只保留每组的第一条数据

mysql> select * from my_student;
+----+--------+----------+
| id | name   | class_id |
+----+--------+----------+
|  1 | 刘备   |        1 |
|  2 | 李四   |        1 |
|  3 | 王五   |        2 |
+----+--------+----------+

mysql> select  class_id  from my_student group by class_id;
+----------+
| class_id |
+----------+
|        1 |
|        2 |
+----------+

5.2、聚合函数

• count() 统计数量。如果是字段，不统计null字段
• avg 平均值
• sum 求和
• max 最大值
• min 最小值
• group_concat 分组中指定字段拼接

按照班级统计每班人数，最大年龄，最小年龄，平均年龄

mysql> select * from my_student;
+----+--------+----------+------+
| id | name   | class_id | age  |
+----+--------+----------+------+
|  1 | 刘备   |        1 |   18 |
|  2 | 李四   |        1 |   19 |
|  3 | 王五   |        2 |   20 |
+----+--------+----------+------+

mysql> select class_id, count(*), max(age), min(age), avg(age) from my_student group by class_id;
+----------+----------+----------+----------+----------+
| class_id | count(*) | max(age) | min(age) | avg(age) |
+----------+----------+----------+----------+----------+
|        1 |        2 |       19 |       18 |  18.5000 |
|        2 |        1 |       20 |       20 |  20.0000 |
+----------+----------+----------+----------+----------+


mysql> select class_id, group_concat(name), count(*), max(age), min(age), avg(age) from my_student group by class_id;
+----------+--------------------+----------+----------+----------+----------+
| class_id | group_concat(name) | count(*) | max(age) | min(age) | avg(age) |
+----------+--------------------+----------+----------+----------+----------+
|        1 | 刘备,李四          |        2 |       19 |       18 |  18.5000 |
|        2 | 王五               |        1 |       20 |       20 |  20.0000 |
+----------+--------------------+----------+----------+----------+----------+

5.3、多分组

对已分组的数据进行再次分组

基本语法

-- 按照字段1进行分组，将结果再按照字段2进行分组
group by 字段1, 字段2;

mysql> select * from my_student;
+----+--------+----------+------+--------+
| id | name   | class_id | age  | gender |
+----+--------+----------+------+--------+
|  1 | 刘备   |        1 |   18 |      2 |
|  2 | 李四   |        1 |   19 |      1 |
|  3 | 王五   |        2 |   20 |      2 |
|  4 | 张飞   |        2 |   21 |      1 |
|  5 | 关羽   |        1 |   22 |      2 |
+----+--------+----------+------+--------+

mysql> select class_id, gender, count(*), group_concat(name) from my_student group by class_id, gender;
+----------+--------+----------+--------------------+
| class_id | gender | count(*) | group_concat(name) |
+----------+--------+----------+--------------------+
|        1 |      1 |        1 | 李四               |
|        1 |      2 |        2 | 刘备,关羽          |
|        2 |      1 |        1 | 张飞               |
|        2 |      2 |        1 | 王五               |
+----------+--------+----------+--------------------+

5.4、分组排序

按照分组字段排序，默认升序

-- 班级升序，性别降序 
-- mysql8.012之后，不支持group by 排序，需要使用order by排序
select class_id, gender, count(*), group_concat(name) 
from my_student 
group by class_id, gender 
order by class_id asc, gender desc;
+----------+--------+----------+--------------------+
| class_id | gender | count(*) | group_concat(name) |
+----------+--------+----------+--------------------+
|        1 |      2 |        2 | 刘备,关羽          |
|        1 |      1 |        1 | 李四               |
|        2 |      2 |        1 | 王五               |
|        2 |      1 |        1 | 张飞               |
+----------+--------+----------+--------------------+

5.5、回溯排序

统计过程中层层上报

group by 字段 with rollup;

-- 分组
mysql> select class_id, count(*) from my_student group by class_id;
+----------+----------+
| class_id | count(*) |
+----------+----------+
|        1 |        3 |
|        2 |        2 |
+----------+----------+

-- 分组回溯
mysql> select class_id, count(*) from my_student group by class_id with rollup;
+----------+----------+
| class_id | count(*) |
+----------+----------+
|        1 |        3 |
|        2 |        2 |
|     NULL |        5 |
+----------+----------+

-- 多分组
mysql> select class_id, gender, count(*) from my_student group by class_id, gender;
+----------+--------+----------+
| class_id | gender | count(*) |
+----------+--------+----------+
|        1 |      2 |        2 |
|        1 |      1 |        1 |
|        2 |      2 |        1 |
|        2 |      1 |        1 |
+----------+--------+----------+

-- 多分组回溯
mysql> select class_id, gender, count(*) from my_student group by class_id, gender with rollup;
+----------+--------+----------+
| class_id | gender | count(*) |
+----------+--------+----------+
|        1 |      1 |        1 |
|        1 |      2 |        2 |
|        1 |   NULL |        3 |
|        2 |      1 |        1 |
|        2 |      2 |        1 |
|        2 |   NULL |        2 |
|     NULL |   NULL |        5 |
+----------+--------+----------+

6、having条件

和where一样，用来进行数据条件筛选

区别：

• where是从表中取数据，where将数据从磁盘拿到内存，where之后的操作都是内存操作
• having聚合之后的数据中取数据

用在group by分组之后，可以针对分组数据进行统计筛选

mysql> select * from my_student;
+----+--------+----------+------+--------+
| id | name   | class_id | age  | gender |
+----+--------+----------+------+--------+
|  1 | 刘备   |        1 |   18 |      2 |
|  2 | 李四   |        1 |   19 |      1 |
|  3 | 王五   |        2 |   20 |      2 |
|  7 | 张飞   |        2 |   21 |      1 |
|  8 | 关羽   |        1 |   22 |      2 |
+----+--------+----------+------+--------+


-- 查询班级人数大于等于3以上的班级
mysql> select class_id, count(*) as total 
from my_student 
group by class_id 
having total >= 3;
+----------+-------+
| class_id | total |
+----------+-------+
|        1 |     3 |
+----------+-------+

7、order by排序

7.1、单字段排序

基本语法

-- 默认asc升序，desc降序
order by 字段 [asc|desc]

-- 按照年龄降序排序
mysql> select * from my_student order by age asc;
+----+--------+----------+------+--------+
| id | name   | class_id | age  | gender |
+----+--------+----------+------+--------+
|  1 | 刘备   |        1 |   18 |      2 |
|  2 | 李四   |        1 |   19 |      1 |
|  3 | 王五   |        2 |   20 |      2 |
|  7 | 张飞   |        2 |   21 |      1 |
|  8 | 关羽   |        1 |   22 |      2 |
+----+--------+----------+------+--------+

7.2、多字段排序

基本语法

order by 字段1, 字段2... [asc|desc];

-- 按照班级和年龄排序
mysql> select * from my_student order by class_id, age desc;
+----+--------+----------+------+--------+
| id | name   | class_id | age  | gender |
+----+--------+----------+------+--------+
|  8 | 关羽   |        1 |   22 |      2 |
|  2 | 李四   |        1 |   19 |      1 |
|  1 | 刘备   |        1 |   18 |      2 |
|  7 | 张飞   |        2 |   21 |      1 |
|  3 | 王五   |        2 |   20 |      2 |
+----+--------+----------+------+--------+

8、limit 限制

限制记录数数量，如果数量不够，仅返回剩余数据

8.1、记录数限制

基本语法

limit 数量;

mysql> select * from my_student;
+----+--------+----------+------+--------+
| id | name   | class_id | age  | gender |
+----+--------+----------+------+--------+
|  1 | 刘备   |        1 |   18 |      2 |
|  2 | 李四   |        1 |   19 |      1 |
|  3 | 王五   |        2 |   20 |      2 |
|  7 | 张飞   |        2 |   21 |      1 |
|  8 | 关羽   |        1 |   22 |      2 |
+----+--------+----------+------+--------+

mysql> select * from my_student limit 2;
+----+--------+----------+------+--------+
| id | name   | class_id | age  | gender |
+----+--------+----------+------+--------+
|  1 | 刘备   |        1 |   18 |      2 |
|  2 | 李四   |        1 |   19 |      1 |
+----+--------+----------+------+--------+

8.2、分页

获取指定区间的数据

基本语法

limit 偏移量, 数量;

-- 等价于
limit 数量 offset 偏移量;

MySQL下标从0开始

分页计算公式：

page: 页数
size: 每页数量

偏移量 = (page - 1) * size

mysql> select * from my_student;
+----+--------+----------+------+--------+
| id | name   | class_id | age  | gender |
+----+--------+----------+------+--------+
|  1 | 刘备   |        1 |   18 |      2 |
|  2 | 李四   |        1 |   19 |      1 |
|  3 | 王五   |        2 |   20 |      2 |
|  7 | 张飞   |        2 |   21 |      1 |
|  8 | 关羽   |        1 |   22 |      2 |
+----+--------+----------+------+--------+

-- 每页2条数据，获取第1页 (1 - 1) * 2, 2
mysql> select * from my_student limit 0, 2;
+----+--------+----------+------+--------+
| id | name   | class_id | age  | gender |
+----+--------+----------+------+--------+
|  1 | 刘备   |        1 |   18 |      2 |
|  2 | 李四   |        1 |   19 |      1 |
+----+--------+----------+------+--------+

-- 每页2条数据，获取第2页 (2 - 1) * 2, 2
mysql> select * from my_student limit 2, 2;
+----+--------+----------+------+--------+
| id | name   | class_id | age  | gender |
+----+--------+----------+------+--------+
|  3 | 王五   |        2 |   20 |      2 |
|  7 | 张飞   |        2 |   21 |      1 |
+----+--------+----------+------+--------+

-- 每页3条数据，获取第2页 (3 - 1) * 2, 2
mysql> select * from my_student limit 4, 2;
+----+--------+----------+------+--------+
| id | name   | class_id | age  | gender |
+----+--------+----------+------+--------+
|  8 | 关羽   |        1 |   22 |      2 |
+----+--------+----------+------+--------+