# 高级数据操作-查询数据 完整的查询指令 ```sql select 选项 字段列表 from 数据源 -- 5子句 where 条件 group by 分组 having 条件 order by 排序 limit 限制; ``` ## 1、select选项 处理查询到的结果 - all 默认值，表示保存所有记录 - distinct 去重，只保留一条（所有字段都相同认为重复） ```sql 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 ```sql 字段名 [as] 字段别名; ``` ```sql select distinct name as name1, name as name2 from my_select; +-------+-------+ | name1 | name2 | +-------+-------+ | A | A | | B | B | +-------+-------+ ``` ## 3、from数据源 为前面的查询提供数据 数据源只要是一个符合二维表结构的数据即可 ### 3.1、单表数据 ```sql from 表名; select * from my_select; ``` ### 3.2、多表数据 基本语法 ```sql from 表名1, 表名2...; ``` ```sql 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后面不是一个实体表，而是一个从表中查询出来得到的二维结果表（子查询） 基本语法 ```sql from (select 字段列表 from 表名) as 别名 ``` ```sql mysql> select * from (select * from my_student) as t1; +----+--------+ | id | name | +----+--------+ | 1 | 刘备 | | 2 | 李四 | | 3 | 王五 | +----+--------+ ``` ## 4、Where条件 通过运算符进行结果比较，来判断符合条件的数据 ## 5、Group by分组 根据指定的字段，将数据进行分组，分组的目的是为了统计 ### 5.1、分组统计 ```sql group by 字段名 ``` 分组后，只保留每组的第一条数据 ```sql 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 分组中指定字段拼接 按照班级统计每班人数，最大年龄，最小年龄，平均年龄 ```sql 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、多分组 对已分组的数据进行再次分组 基本语法 ```sql -- 按照字段1进行分组，将结果再按照字段2进行分组 group by 字段1, 字段2; ``` ```sql 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、分组排序 按照分组字段排序，默认升序 ```sql -- 班级升序，性别降序 -- 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、回溯排序 统计过程中层层上报 ```sql group by 字段 with rollup; ``` ```sql -- 分组 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分组之后，可以针对分组数据进行统计筛选 ```sql 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、单字段排序 基本语法 ```sql -- 默认asc升序，desc降序 order by 字段 [asc|desc] ``` ```sql -- 按照年龄降序排序 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、多字段排序 基本语法 ```sql order by 字段1, 字段2... [asc|desc]; ``` ```sql -- 按照班级和年龄排序 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、记录数限制 基本语法 ```sql limit 数量; ``` ```sql 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、分页 获取指定区间的数据 基本语法 ```sql limit 偏移量, 数量; -- 等价于 limit 数量 offset 偏移量; ``` MySQL下标从0开始 分页计算公式： ``` page: 页数 size: 每页数量 偏移量 = (page - 1) * size ``` ```sql 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 | +----+--------+----------+------+--------+ ```