【MySQL】6、分页、模糊、分组、子查询-CSDN博客

本文链接：https://blog.csdn.net/qq_56040271/article/details/141001734

11. 分页查询与排序

11.1 分页查询

limit关键字代表了没次查询的起始记录及条数，具体如下：
参数1：代表起始记录，记录索引号从0开始。
参数2：代表显示的记录条数。

mysql> select * from student limit 0,2;
+-----+-------+------+------+------+------+
| sid | sname | sex  | age  | addr | cid  |
+-----+-------+------+------+------+------+
|   1 | 张三  | 男   |   22 | 上海 |    1 |
|   2 | 李四  | 男   |   21 | 深圳 |    2 |
+-----+-------+------+------+------+------+
2 rows in set (0.01 sec)

mysql> select * from student limit 2,2;
+-----+-------+------+------+------+------+
| sid | sname | sex  | age  | addr | cid  |
+-----+-------+------+------+------+------+
|   3 | 王五  | 女   |   23 | 杭州 |    3 |
+-----+-------+------+------+------+------+
1 row in set (0.00 sec)

11.2 排序

order by关键字说明：
① 默认按照asc升序排序，如果要指定为降序则在排序字段后加desc。
② 可以对多个字段进行排序，需要用逗号分隔。

mysql> select * from student order by age desc;
+-----+-------+------+------+------+------+
| sid | sname | sex  | age  | addr | cid  |
+-----+-------+------+------+------+------+
|   3 | 王五  | 女   |   23 | 杭州 |    3 |
|   1 | 张三  | 男   |   22 | 上海 |    1 |
|   2 | 李四  | 男   |   21 | 深圳 |    2 |
+-----+-------+------+------+------+------+
3 rows in set (0.01 sec)

mysql> select * from student order by sex desc,age asc;
+-----+-------+------+------+------+------+
| sid | sname | sex  | age  | addr | cid  |
+-----+-------+------+------+------+------+
|   2 | 李四  | 男   |   21 | 深圳 |    2 |
|   1 | 张三  | 男   |   22 | 上海 |    1 |
|   3 | 王五  | 女   |   23 | 杭州 |    3 |
+-----+-------+------+------+------+------+
3 rows in set (0.00 sec)

12. 模糊查询与分组查询

12.1 模糊查询

12.1.1 使用 like 关键字进行模糊查询

在 like 模糊查询中，%代表任意多个字符，_代表任意一个字符。

mysql> select * from student where addr like '%州';
+-----+-------+------+------+------+------+
| sid | sname | sex  | age  | addr | cid  |
+-----+-------+------+------+------+------+
|   3 | 王五  | 女   |   23 | 杭州 |    3 |
+-----+-------+------+------+------+------+
1 row in set (0.00 sec)

# 可以一次使用多个条件查询
mysql> select * from student where addr like '%州' and sname like '王%';
+-----+-------+------+------+------+------+
| sid | sname | sex  | age  | addr | cid  |
+-----+-------+------+------+------+------+
|   3 | 王五  | 女   |   23 | 杭州 |    3 |
+-----+-------+------+------+------+------+
1 row in set (0.00 sec)

mysql> select * from student where addr like '_州';
+-----+-------+------+------+------+------+
| sid | sname | sex  | age  | addr | cid  |
+-----+-------+------+------+------+------+
|   3 | 王五  | 女   |   23 | 杭州 |    3 |
+-----+-------+------+------+------+------+
1 row in set (0.00 sec)

12.1.2 使用 regexp 关键字进行正则表达式查询

mysql> select * from student where addr regexp '[a-zA-Z]{2}';
+-----+-------+------+------+--------+------+
| sid | sname | sex  | age  | addr   | cid  |
+-----+-------+------+------+--------+------+
|   3 | 王五  | 女   |   23 | 杭Ab州 |    3 |
+-----+-------+------+------+--------+------+
1 row in set (0.00 sec)

12.1.3 使用 in/not in 关键字进行模糊查询

12.1.3.1 in 关键字代表在一个指定范围内查询

mysql> select * from student where addr in('上海','深圳');
+-----+-------+------+------+------+------+
| sid | sname | sex  | age  | addr | cid  |
+-----+-------+------+------+------+------+
|   1 | 张三  | 男   |   22 | 上海 |    1 |
|   2 | 李四  | 男   |   21 | 深圳 |    2 |
+-----+-------+------+------+------+------+
2 rows in set (0.00 sec)

12.1.3.2 not in 关键字代表不在一个指定范围内查询

mysql> select * from student where addr not in('上海','深圳');
+-----+-------+------+------+--------+------+
| sid | sname | sex  | age  | addr   | cid  |
+-----+-------+------+------+--------+------+
|   3 | 王五  | 女   |   23 | 杭Ab州 |    3 |
+-----+-------+------+------+--------+------+
1 row in set (0.00 sec)

12.1.4 使用 between…and 关键字进行区间查询

between…and 两边边界的数据值都包含在内

mysql> select * from student where age between 20 and 22;
+-----+-------+------+------+------+------+
| sid | sname | sex  | age  | addr | cid  |
+-----+-------+------+------+------+------+
|   1 | 张三  | 男   |   22 | 上海 |    1 |
|   2 | 李四  | 男   |   21 | 深圳 |    2 |
+-----+-------+------+------+------+------+
2 rows in set (0.00 sec)

12.2 分组查询

12.2.1 一次对一个字段进行分组

select 后的字段只能从 group by 后面的字段中选择，聚合函数除外。

mysql> select sex,count(*) 性别人数,min(age),max(age),avg(age) from student group by sex;
+------+----------+----------+----------+----------+
| sex  | 性别人数 | min(age) | max(age) | avg(age) |
+------+----------+----------+----------+----------+
| 女   |       14 |       19 |       39 |  29.2857 |
| 男   |       29 |       18 |       40 |  30.3793 |
+------+----------+----------+----------+----------+
2 rows in set (0.00 sec)

12.2.2 一次对多个字段进行分组

可以对多个字段进行分组，中间使用逗号分开，此例代表在对学生按性别分组后，再按照每个性别所对应的班级分组，最终求出每种性别在每个班级中对应的人数。

mysql> select sex,cid,count(*) from student group by sex,cid;
+------+------+----------+
| sex  | cid  | count(*) |
+------+------+----------+
| 女   |    1 |        1 |
| 女   |    2 |        2 |
| 女   |    3 |        5 |
| 女   |    4 |        3 |
| 女   |    5 |        3 |
| 男   |    1 |        3 |
| 男   |    2 |        8 |
| 男   |    3 |        6 |
| 男   |    4 |        8 |
| 男   |    5 |        4 |
+------+------+----------+
10 rows in set (0.00 sec)

12.2.3 对分组后的数据进行筛选

having 代表对分组后的数据进行筛选

mysql> select sex,count(*) from student
    -> group by sex
    -> having count(*)>3;
+------+----------+
| sex  | count(*) |
+------+----------+
| 女   |       14 |
| 男   |       29 |
+------+----------+
2 rows in set (0.00 sec)

13. 子查询

案例：找出 java 最高分的学生所在的班级信息
分析：班级信息 → 班级编号 → Java 最高分的学生编号 → Java 最高分→ Java 课程编号

# 第一步：找出 java 课程所对应的课程编号
mysql> select cid from course where cname='java';
+-----+
| cid |
+-----+
|   2 |
+-----+
1 row in set (0.00 sec)

# 第二步：找出 java 的最高分
mysql> select max(score) from score
    -> where cid in(
    ->  select cid from course where cname='java'
    -> );
+------------+
| max(score) |
+------------+
|         88 |
+------------+
1 row in set (0.00 sec)

# 第三步：根据 java 的最高分找出对应的学生编号 sid
mysql> select sid from score
    -> where score in(
    ->     select max(score) from score
    ->     where cid in(
    ->         select cid from course where cname='java'
    ->     )
    -> );
+------+
| sid  |
+------+
|    2 |
+------+
1 row in set (0.00 sec)

# 第四步：根据学生编号找到班级编号 cid
mysql> select cid from student
    -> where sid in(
    ->     select sid from score
    ->     where score in(
    ->         select max(score) from score
    ->         where cid in(
    ->             select cid from course where cname='java'
    ->         )
    ->     )
    -> );
+------+
| cid  |
+------+
|    2 |
+------+
1 row in set (0.00 sec)

# 第五步：根据班级编号找到班级信息
mysql> select * from classes
    -> where cid in(
    ->     select cid from student
    ->     where sid in(
    ->         select sid from score
    ->         where score in(
    ->             select max(score) from score
    ->             where cid in(
    ->                 select cid from course where cname='java'
    ->             )
    ->         )
    ->     )
    -> );
+-----+--------+
| cid | cname  |
+-----+--------+
|   2 | bj2402 |
+-----+--------+
1 row in set (0.00 sec)