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)