高级数据操作-查询数据
完整的查询指令
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 |
+----+--------+----------+------+--------+
