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