MySQL 实验 8:数据查询(1)—— select 语句的基本用法

MySQL 使用 SELECT 语句查询所需的数据。查询是指根据使用需求,使用不同的查询方式从数据库中获取所需的数据。

一、数据准备

本实验及后续的查询实验所使用的数据表如下:

-- 院系表
create table dept(
    dept_id char(3) primary key,
    dept_name char(50),
    phone char(20),
    address varchar(20)
);

-- 添加数据
insert into dept values('D01','经济学院','0373-3025111','10号教学楼1101');
insert into dept values('D02','管理学院','0373-3025122','12号教学楼602');
insert into dept values('D03','生命科技学院','0373-3025133','8号教学楼204');
insert into dept values('D04','农学院','0373-3025144','7号教学楼612');

-- 学生表
create table stu(
    s_id char(11) primary key,
    s_name char(20),
    gender char(2),
    birth date,
    phone char(20),
    dept_id char(3),
    foreign key(dept_id) references dept(dept_id)
);

-- 添加数据
insert into stu values('20220124001','薛智玲','男','2000-1-15','13637358097','D01');
insert into stu values('20220124002','杨铭华','男','2001-3-6','13637381407','D01');
insert into stu values('20220124003','张从超','女','2003-12-9','13637389129','D01');
insert into stu values('20220124004','孙金航','女','2003-3-7','13637387244','D01');
insert into stu values('20220214001','谭宝名','男','2002-8-5','13637332439','D02');
insert into stu values('20220214002','赵文瀚','女','2001-3-25','13637381710','D02');
insert into stu values('20220214003','裴天馨','女','2003-4-15','13637381374','D02');
insert into stu values('20220325101','董雯林','男','2003-12-27','13637357085','D03');
insert into stu values('20220325102','周宜可','男','2002-7-4','13637333898','D03');
insert into stu values('20220325103','张春晶','男','2001-6-21','13637340698','D03');
insert into stu values('20220410101','夏飞研','女','2002-9-2','13637326693','D04');
insert into stu values('20220410102','聂小龙','男','2002-12-4','13637331432','D04');
insert into stu values('20220410103','赵晨祥','女','2002-5-22','13637388564','D04');
insert into stu values('20220410104','江沐洪','男','2002-2-12','13637345052',NULL);

-- 课程表
create table course(
    c_id char(6) primary key,
    c_name char(50),
    credit decimal(4,2)
);

-- 添加数据
insert into course values('C01001','微观经济学',4);
insert into course values('C01002','宏观经济学',3);
insert into course values('C01003','财务管理',3);
insert into course values('C02101','企业管理概论',3);
insert into course values('C02102','管理学原理',3);
insert into course values('C02103','管理信息系统',4);
insert into course values('C03201','遗传学',3);
insert into course values('C03202','药物学',3);
insert into course values('C03203','药理学',3);
insert into course values('C04111','农业环境学',3);
insert into course values('C04112','土壤肥料学',4);
insert into course values('C04113','作物学',4);

-- 选课表
create table xk(
    s_id char(11),
    c_id char(6),
    score decimal(5,2),
    primary key(s_id,c_id),
    foreign key(s_id) references stu(s_id),
    foreign key(c_id) references course(c_id)
);

-- 添加数据
insert into xk values('20220124001','C01001',61);
insert into xk values('20220124002','C01001',88);
insert into xk values('20220124003','C01001',95);
insert into xk values('20220124004','C01001',81);
insert into xk values('20220124001','C01002',68);
insert into xk values('20220124002','C01002',69);
insert into xk values('20220124001','C01003',92);
insert into xk values('20220124002','C01003',69);
insert into xk values('20220124003','C01003',94);
insert into xk values('20220124004','C01003',96);
insert into xk values('20220214001','C02101',80);
insert into xk values('20220214002','C02101',63);
insert into xk values('20220214003','C02101',72);
insert into xk values('20220214001','C02102',80);
insert into xk values('20220214002','C02102',67);
insert into xk values('20220214003','C02102',96);
insert into xk values('20220214001','C02103',97);
insert into xk values('20220214002','C02103',74);
insert into xk values('20220214003','C02103',94);
insert into xk values('20220325101','C03201',83);
insert into xk values('20220325102','C03201',90);
insert into xk values('20220325103','C03201',65);
insert into xk values('20220325101','C03202',83);
insert into xk values('20220325102','C03202',84);
insert into xk values('20220325103','C03202',77);
insert into xk values('20220325101','C03203',74);
insert into xk values('20220325102','C03203',67);
insert into xk values('20220325103','C03203',66);
insert into xk values('20220410101','C04111',68);
insert into xk values('20220410102','C04111',62);
insert into xk values('20220410103','C04111',64);
insert into xk values('20220410104','C04111',64);
insert into xk values('20220410101','C04112',97);
insert into xk values('20220410102','C04112',78);
insert into xk values('20220410103','C04112',82);
insert into xk values('20220410104','C04112',75);
insert into xk values('20220410101','C04113',83);
insert into xk values('20220410102','C04113',72);
insert into xk values('20220410103','C04113',62);


-- 查看数据
-- 院系表
mysql> select * from dept;
+---------+--------------+--------------+----------------+
| dept_id | dept_name    | phone        | address        |
+---------+--------------+--------------+----------------+
| D01     | 经济学院     | 0373-3025111 | 10号教学楼1101 |
| D02     | 管理学院     | 0373-3025122 | 12号教学楼602  |
| D03     | 生命科技学院 | 0373-3025133 | 8号教学楼204   |
| D04     | 农学院       | 0373-3025144 | 7号教学楼612   |
+---------+--------------+--------------+----------------+
4 rows in set (0.00 sec)

-- 学生表
mysql> select * from stu;
+-------------+--------+--------+------------+-------------+---------+
| s_id        | s_name | gender | birth      | phone       | dept_id |
+-------------+--------+--------+------------+-------------+---------+
| 20220124001 | 薛智玲 || 2000-01-15 | 13637358097 | D01     |
| 20220124002 | 杨铭华 || 2001-03-06 | 13637381407 | D01     |
| 20220124003 | 张从超 || 2003-12-09 | 13637389129 | D01     |
| 20220124004 | 孙金航 || 2003-03-07 | 13637387244 | D01     |
| 20220214001 | 谭宝名 || 2002-08-05 | 13637332439 | D02     |
| 20220214002 | 赵文瀚 || 2001-03-25 | 13637381710 | D02     |
| 20220214003 | 裴天馨 || 2003-04-15 | 13637381374 | D02     |
| 20220325101 | 董雯林 || 2003-12-27 | 13637357085 | D03     |
| 20220325102 | 周宜可 || 2002-07-04 | 13637333898 | D03     |
| 20220325103 | 张春晶 || 2001-06-21 | 13637340698 | D03     |
| 20220410101 | 夏飞研 || 2002-09-02 | 13637326693 | D04     |
| 20220410102 | 聂小龙 || 2002-12-04 | 13637331432 | D04     |
| 20220410103 | 赵晨祥 || 2002-05-22 | 13637388564 | D04     |
| 20220410104 | 江沐洪 || 2002-02-12 | 13637345052 | NULL    |
+-------------+--------+--------+------------+-------------+---------+
14 rows in set (0.00 sec)

-- 课程表
mysql> select * from course;
+--------+--------------+--------+
| c_id   | c_name       | credit |
+--------+--------------+--------+
| C01001 | 微观经济学   |   4.00 |
| C01002 | 宏观经济学   |   3.00 |
| C01003 | 财务管理     |   3.00 |
| C02101 | 企业管理概论 |   3.00 |
| C02102 | 管理学原理   |   3.00 |
| C02103 | 管理信息系统 |   4.00 |
| C03201 | 遗传学       |   3.00 |
| C03202 | 药物学       |   3.00 |
| C03203 | 药理学       |   3.00 |
| C04111 | 农业环境学   |   3.00 |
| C04112 | 土壤肥料学   |   4.00 |
| C04113 | 作物学       |   4.00 |
+--------+--------------+--------+
12 rows in set (0.00 sec)

-- 选课表
mysql> select * from xk;
+-------------+--------+-------+
| s_id        | c_id   | score |
+-------------+--------+-------+
| 20220124001 | C01001 | 61.00 |
| 20220124001 | C01002 | 68.00 |
| 20220124001 | C01003 | 92.00 |
| 20220124002 | C01001 | 88.00 |
| 20220124002 | C01002 | 69.00 |
| 20220124002 | C01003 | 69.00 |
| 20220124003 | C01001 | 95.00 |
| 20220124003 | C01003 | 94.00 |
| 20220124004 | C01001 | 81.00 |
| 20220124004 | C01003 | 96.00 |
| 20220214001 | C02101 | 80.00 |
| 20220214001 | C02102 | 80.00 |
| 20220214001 | C02103 | 97.00 |
| 20220214002 | C02101 | 63.00 |
| 20220214002 | C02102 | 67.00 |
| 20220214002 | C02103 | 74.00 |
| 20220214003 | C02101 | 72.00 |
| 20220214003 | C02102 | 96.00 |
| 20220214003 | C02103 | 94.00 |
| 20220325101 | C03201 | 83.00 |
| 20220325101 | C03202 | 83.00 |
| 20220325101 | C03203 | 74.00 |
| 20220325102 | C03201 | 90.00 |
| 20220325102 | C03202 | 84.00 |
| 20220325102 | C03203 | 67.00 |
| 20220325103 | C03201 | 65.00 |
| 20220325103 | C03202 | 77.00 |
| 20220325103 | C03203 | 66.00 |
| 20220410101 | C04111 | 68.00 |
| 20220410101 | C04112 | 97.00 |
| 20220410101 | C04113 | 83.00 |
| 20220410102 | C04111 | 62.00 |
| 20220410102 | C04112 | 78.00 |
| 20220410102 | C04113 | 72.00 |
| 20220410103 | C04111 | 64.00 |
| 20220410103 | C04112 | 82.00 |
| 20220410103 | C04113 | 62.00 |
| 20220410104 | C04111 | 64.00 |
| 20220410104 | C04112 | 75.00 |
+-------------+--------+-------+
39 rows in set (0.00 sec)

二、SELECT 语句的语法

SELECT 语句的语法格式如下:

SELECT DISTINCT <列名或表达式 [[AS] 别名], ...>
FROM <表名 [[AS] 别名]> [INNER| LEFT| RIGHT] JOIN <表名 [[AS] 别名]> 
ON <连接条件>
WHERE  <筛选条件>
GROUP BY  <分组字段, ...>
HAVING  <分组筛选条件>
ORDER BY <排序字段> [ASC| DESC]
LIMIT <[m,]n>;

--参数说明:1DISTINCT:消除重复行。
(2<字段或表达式>:表示所要查询字段的名称,可以使用(*)表示所有字段,也可以使用列名构造表达式。
(3<连接条件>:一般使用两个表中的相同列构造连接条件。进行连接运算的表通常具有一对多联系,连接条件通常使用子表的外键和父表的主键建立。
(4<筛选条件>:限定查询数据必须满足该查询条件。
(5<分组字段>:按照指定的字段分组。
(6<分组筛选条件>:对分组进行筛选。
(7<排序字段>:对查询结果进行排序,可以进行升序(ASC)和降序(DESC)排列,默认是升序。
(8LIMIT <m,n>:分页显示数据。

SELECT 语句的执行顺序与书写顺序不同。SELECT 语句的执行顺序如下:

--(1)指定查询所使用的表
FROM <table_name> [INNER|LEFT|RIGHT] JOIN <table_name>
ON <连接条件>
--(2)指定筛选条件(注意:WHERE 后面不能使用 SELECT 所指定的列的别名,因为此时 select 子句还没有执行)
WHERE <筛选条件>
--(3)对数据分组
GROUP BY <分组字段或表达式>
HAVING <分组筛选条件>
--(4)生成需要显示的列和表达式
SELECT <字段或表达式列表>
--(5)消除重复的数据行
DISTINCT
--(6)对查询结果排序
ORDER BY <排序字段或表达式>
LIMIT [m,]n

三、指定查询结果要显示的列名或表达式列表

把查询结果需要显示的所有的列名写到 SELECT 后面,使用逗号(,)隔开。如果查询时要显示表中的所有列,可以使用(*)表示。

1、指定要查询的列名

把需要查询的列名依次写到 SELECT 后面,列名之间用逗号(,)隔开。例如:

-- 显示 dept 表中的所有列
mysql> select * from dept;
+---------+--------------+--------------+----------------+
| dept_id | dept_name    | phone        | address        |
+---------+--------------+--------------+----------------+
| D01     | 经济学院     | 0373-3025111 | 10号教学楼1101 |
| D02     | 管理学院     | 0373-3025122 | 12号教学楼602  |
| D03     | 生命科技学院 | 0373-3025133 | 8号教学楼204   |
| D04     | 农学院       | 0373-3025144 | 7号教学楼612   |
+---------+--------------+--------------+----------------+
4 rows in set (0.00 sec)

-- 显示 stu 表中的列:s_id, s_name, birth, phone
mysql> select s_id, s_name, birth, phone from stu;
+-------------+--------+------------+-------------+
| s_id        | s_name | birth      | phone       |
+-------------+--------+------------+-------------+
| 20220124001 | 薛智玲 | 2000-01-15 | 13637358097 |
| 20220124002 | 杨铭华 | 2001-03-06 | 13637381407 |
| 20220124003 | 张从超 | 2003-12-09 | 13637389129 |
| 20220124004 | 孙金航 | 2003-03-07 | 13637387244 |
| 20220214001 | 谭宝名 | 2002-08-05 | 13637332439 |
| 20220214002 | 赵文瀚 | 2001-03-25 | 13637381710 |
| 20220214003 | 裴天馨 | 2003-04-15 | 13637381374 |
| 20220325101 | 董雯林 | 2003-12-27 | 13637357085 |
| 20220325102 | 周宜可 | 2002-07-04 | 13637333898 |
| 20220325103 | 张春晶 | 2001-06-21 | 13637340698 |
| 20220410101 | 夏飞研 | 2002-09-02 | 13637326693 |
| 20220410102 | 聂小龙 | 2002-12-04 | 13637331432 |
| 20220410103 | 赵晨祥 | 2002-05-22 | 13637388564 |
| 20220410104 | 江沐洪 | 2002-02-12 | 13637345052 |
+-------------+--------+------------+-------------+
14 rows in set (0.00 sec)
2、指定要查询的表达式

查询结果可以使用表达式对列名进行计算。例如:

mysql> select s_id, s_name, year(now())-year(birth) as age from stu;
+-------------+--------+------+
| s_id        | s_name | age  |
+-------------+--------+------+
| 20220124001 | 薛智玲 |   24 |
| 20220124002 | 杨铭华 |   23 |
| 20220124003 | 张从超 |   21 |
| 20220124004 | 孙金航 |   21 |
| 20220214001 | 谭宝名 |   22 |
| 20220214002 | 赵文瀚 |   23 |
| 20220214003 | 裴天馨 |   21 |
| 20220325101 | 董雯林 |   21 |
| 20220325102 | 周宜可 |   22 |
| 20220325103 | 张春晶 |   23 |
| 20220410101 | 夏飞研 |   22 |
| 20220410102 | 聂小龙 |   22 |
| 20220410103 | 赵晨祥 |   22 |
| 20220410104 | 江沐洪 |   22 |
+-------------+--------+------+
14 rows in set (0.00 sec)

四、为查询结果中的表达式指定别名

可以为查询结果中的一个列或者表达式指定别名,语法格式如下:

--格式一
列名|表达式 as 别名

--格式二
列名|表达式  别名

例如:

-- 格式一
mysql> select s_id, s_name, year(now())-year(birth) as age from stu;
+-------------+--------+------+
| s_id        | s_name | age  |
+-------------+--------+------+
| 20220124001 | 薛智玲 |   24 |
| 20220124002 | 杨铭华 |   23 |
| 20220124003 | 张从超 |   21 |
| 20220124004 | 孙金航 |   21 |
| 20220214001 | 谭宝名 |   22 |
| 20220214002 | 赵文瀚 |   23 |
| 20220214003 | 裴天馨 |   21 |
| 20220325101 | 董雯林 |   21 |
| 20220325102 | 周宜可 |   22 |
| 20220325103 | 张春晶 |   23 |
| 20220410101 | 夏飞研 |   22 |
| 20220410102 | 聂小龙 |   22 |
| 20220410103 | 赵晨祥 |   22 |
| 20220410104 | 江沐洪 |   22 |
+-------------+--------+------+
14 rows in set (0.00 sec)

-- 格式二
mysql> select s_id, s_name, year(now())-year(birth) age from stu;
+-------------+--------+------+
| s_id        | s_name | age  |
+-------------+--------+------+
| 20220124001 | 薛智玲 |   24 |
| 20220124002 | 杨铭华 |   23 |
| 20220124003 | 张从超 |   21 |
| 20220124004 | 孙金航 |   21 |
| 20220214001 | 谭宝名 |   22 |
| 20220214002 | 赵文瀚 |   23 |
| 20220214003 | 裴天馨 |   21 |
| 20220325101 | 董雯林 |   21 |
| 20220325102 | 周宜可 |   22 |
| 20220325103 | 张春晶 |   23 |
| 20220410101 | 夏飞研 |   22 |
| 20220410102 | 聂小龙 |   22 |
| 20220410103 | 赵晨祥 |   22 |
| 20220410104 | 江沐洪 |   22 |
+-------------+--------+------+
14 rows in set (0.00 sec)

五、使用 distinct 参数消除查询结果中的重复行

使用 DISTINCT 关键词可以消除查询结果中重复的行。DISTINCT 关键词必须放到 SELECT 后面,字段列表之前。例如:

mysql> select dept_id from stu;
+---------+
| dept_id |
+---------+
| D01     |
| D01     |
| D01     |
| D01     |
| D02     |
| D02     |
| D02     |
| D03     |
| D03     |
| D03     |
| D04     |
| D04     |
| D04     |
| D04     |
+---------+
14 rows in set (0.00 sec)

-- 使用 distinct 参数消除查询结果中的重复行
mysql> select distinct dept_id from stu;
+---------+
| dept_id |
+---------+
| D01     |
| D02     |
| D03     |
| D04     |
+---------+
4 rows in set (0.00 sec)

六、使用 order by 参数对查询结果排序

在查询中使用 ORDER BY 子句可以对查询结果进行排序。默认按照升序对记录进行排序,如果希望按照降序对记录进行排序,可以使用 DESC 关键字。

语法格式如下:

ORDER BY 列名或表达式 [DESC] [,...]
1、使用列名进行排序
-- 按照出生日期排序(升序排列)
mysql> select * from stu order by birth;
+-------------+--------+--------+------------+-------------+---------+
| s_id        | s_name | gender | birth      | phone       | dept_id |
+-------------+--------+--------+------------+-------------+---------+
| 20220124001 | 薛智玲 || 2000-01-15 | 13637358097 | D01     |
| 20220124002 | 杨铭华 || 2001-03-06 | 13637381407 | D01     |
| 20220214002 | 赵文瀚 || 2001-03-25 | 13637381710 | D02     |
| 20220325103 | 张春晶 || 2001-06-21 | 13637340698 | D03     |
| 20220410104 | 江沐洪 || 2002-02-12 | 13637345052 | NULL    |
| 20220410103 | 赵晨祥 || 2002-05-22 | 13637388564 | D04     |
| 20220325102 | 周宜可 || 2002-07-04 | 13637333898 | D03     |
| 20220214001 | 谭宝名 || 2002-08-05 | 13637332439 | D02     |
| 20220410101 | 夏飞研 || 2002-09-02 | 13637326693 | D04     |
| 20220410102 | 聂小龙 || 2002-12-04 | 13637331432 | D04     |
| 20220124004 | 孙金航 || 2003-03-07 | 13637387244 | D01     |
| 20220214003 | 裴天馨 || 2003-04-15 | 13637381374 | D02     |
| 20220124003 | 张从超 || 2003-12-09 | 13637389129 | D01     |
| 20220325101 | 董雯林 || 2003-12-27 | 13637357085 | D03     |
+-------------+--------+--------+------------+-------------+---------+
14 rows in set (0.00 sec)

-- 先按照 dept_id 升序排列,如果 dept_id 相同,再按照 birth 降序排列
mysql> select * from stu order by dept_id, birth desc;
+-------------+--------+--------+------------+-------------+---------+
| s_id        | s_name | gender | birth      | phone       | dept_id |
+-------------+--------+--------+------------+-------------+---------+
| 20220124003 | 张从超 || 2003-12-09 | 13637389129 | D01     |
| 20220124004 | 孙金航 || 2003-03-07 | 13637387244 | D01     |
| 20220124002 | 杨铭华 || 2001-03-06 | 13637381407 | D01     |
| 20220124001 | 薛智玲 || 2000-01-15 | 13637358097 | D01     |
| 20220214003 | 裴天馨 || 2003-04-15 | 13637381374 | D02     |
| 20220214001 | 谭宝名 || 2002-08-05 | 13637332439 | D02     |
| 20220214002 | 赵文瀚 || 2001-03-25 | 13637381710 | D02     |
| 20220325101 | 董雯林 || 2003-12-27 | 13637357085 | D03     |
| 20220325102 | 周宜可 || 2002-07-04 | 13637333898 | D03     |
| 20220325103 | 张春晶 || 2001-06-21 | 13637340698 | D03     |
| 20220410102 | 聂小龙 || 2002-12-04 | 13637331432 | D04     |
| 20220410101 | 夏飞研 || 2002-09-02 | 13637326693 | D04     |
| 20220410103 | 赵晨祥 || 2002-05-22 | 13637388564 | D04     |
| 20220410104 | 江沐洪 || 2002-02-12 | 13637345052 | NULL    |
+-------------+--------+--------+------------+-------------+---------+
14 rows in set (0.00 sec)
2、使用表达式进行排序
-- 按照 phone 的后 4 位排序
mysql> select * from stu order by right(phone,4);
+-------------+--------+--------+------------+-------------+---------+
| s_id        | s_name | gender | birth      | phone       | dept_id |
+-------------+--------+--------+------------+-------------+---------+
| 20220325103 | 张春晶 || 2001-06-21 | 13637340698 | D03     |
| 20220214003 | 裴天馨 || 2003-04-15 | 13637381374 | D02     |
| 20220124002 | 杨铭华 || 2001-03-06 | 13637381407 | D01     |
| 20220410102 | 聂小龙 || 2002-12-04 | 13637331432 | D04     |
| 20220214002 | 赵文瀚 || 2001-03-25 | 13637381710 | D02     |
| 20220214001 | 谭宝名 || 2002-08-05 | 13637332439 | D02     |
| 20220325102 | 周宜可 || 2002-07-04 | 13637333898 | D03     |
| 20220410104 | 江沐洪 || 2002-02-12 | 13637345052 | NULL    |
| 20220410101 | 夏飞研 || 2002-09-02 | 13637326693 | D04     |
| 20220325101 | 董雯林 || 2003-12-27 | 13637357085 | D03     |
| 20220124004 | 孙金航 || 2003-03-07 | 13637387244 | D01     |
| 20220124001 | 薛智玲 || 2000-01-15 | 13637358097 | D01     |
| 20220410103 | 赵晨祥 || 2002-05-22 | 13637388564 | D04     |
| 20220124003 | 张从超 || 2003-12-09 | 13637389129 | D01     |
+-------------+--------+--------+------------+-------------+---------+
14 rows in set (0.00 sec)

-- 按照【年龄】降序排列
mysql> select * from stu order by year(now())-year(birth) desc;
+-------------+--------+--------+------------+-------------+---------+
| s_id        | s_name | gender | birth      | phone       | dept_id |
+-------------+--------+--------+------------+-------------+---------+
| 20220124001 | 薛智玲 || 2000-01-15 | 13637358097 | D01     |
| 20220124002 | 杨铭华 || 2001-03-06 | 13637381407 | D01     |
| 20220214002 | 赵文瀚 || 2001-03-25 | 13637381710 | D02     |
| 20220325103 | 张春晶 || 2001-06-21 | 13637340698 | D03     |
| 20220410103 | 赵晨祥 || 2002-05-22 | 13637388564 | D04     |
| 20220410102 | 聂小龙 || 2002-12-04 | 13637331432 | D04     |
| 20220410101 | 夏飞研 || 2002-09-02 | 13637326693 | D04     |
| 20220325102 | 周宜可 || 2002-07-04 | 13637333898 | D03     |
| 20220410104 | 江沐洪 || 2002-02-12 | 13637345052 | NULL    |
| 20220214001 | 谭宝名 || 2002-08-05 | 13637332439 | D02     |
| 20220214003 | 裴天馨 || 2003-04-15 | 13637381374 | D02     |
| 20220124004 | 孙金航 || 2003-03-07 | 13637387244 | D01     |
| 20220124003 | 张从超 || 2003-12-09 | 13637389129 | D01     |
| 20220325101 | 董雯林 || 2003-12-27 | 13637357085 | D03     |
+-------------+--------+--------+------------+-------------+---------+
14 rows in set (0.00 sec)
3、使用 select 后面的表达式的序号排序

在 ORDER BY 子句中可以使用数字代替列名或表达式,数字 n 的含义是指列名或表达式在 SELECT 子句中的序号。例如:

mysql> select s_id, s_name, birth, year(now())-year(birth) age from stu order by 4;
+-------------+--------+------------+------+
| s_id        | s_name | birth      | age  |
+-------------+--------+------------+------+
| 20220325101 | 董雯林 | 2003-12-27 |   21 |
| 20220124003 | 张从超 | 2003-12-09 |   21 |
| 20220124004 | 孙金航 | 2003-03-07 |   21 |
| 20220214003 | 裴天馨 | 2003-04-15 |   21 |
| 20220410103 | 赵晨祥 | 2002-05-22 |   22 |
| 20220410102 | 聂小龙 | 2002-12-04 |   22 |
| 20220410101 | 夏飞研 | 2002-09-02 |   22 |
| 20220325102 | 周宜可 | 2002-07-04 |   22 |
| 20220410104 | 江沐洪 | 2002-02-12 |   22 |
| 20220214001 | 谭宝名 | 2002-08-05 |   22 |
| 20220325103 | 张春晶 | 2001-06-21 |   23 |
| 20220214002 | 赵文瀚 | 2001-03-25 |   23 |
| 20220124002 | 杨铭华 | 2001-03-06 |   23 |
| 20220124001 | 薛智玲 | 2000-01-15 |   24 |
+-------------+--------+------------+------+
14 rows in set (0.00 sec)

七、使用 limit 参数限定显示的记录数

在查询中使用 LIMIT 选项可以返回前几行或者中间某几行数据。LIMIT 选项的语法格式如下:

LIMIT [offsets,] rows

--说明:1)该选项经常和 order by 一起使用;
(2LIMIT 需要指定一个或两个整型参数;
(3)如果指定两个参数,第一个参数指定从第几条记录(offset+1)开始计算,第二个参数指定要返回记录的最大行数;
(3)如果只指定一个参数(则第一个参数默认为 0),该参数为从第一条记录开始计算,需要返回的最大行数。

例如:

-- 指定一个参数 n,显示前 n 条记录
mysql> select s_id, s_name, year(now())-year(birth) age from stu order by age limit 5;
+-------------+--------+------+
| s_id        | s_name | age  |
+-------------+--------+------+
| 20220325101 | 董雯林 |   21 |
| 20220124003 | 张从超 |   21 |
| 20220124004 | 孙金航 |   21 |
| 20220214003 | 裴天馨 |   21 |
| 20220410103 | 赵晨祥 |   22 |
+-------------+--------+------+
5 rows in set (0.00 sec)

-- 指定两个参数[m,n],从第 m+1 条开始,显示 n 条记录
mysql> select s_id, s_name, year(now())-year(birth) age from stu order by age limit 3,5;
+-------------+--------+------+
| s_id        | s_name | age  |
+-------------+--------+------+
| 20220214003 | 裴天馨 |   21 |
| 20220410103 | 赵晨祥 |   22 |
| 20220410102 | 聂小龙 |   22 |
| 20220410101 | 夏飞研 |   22 |
| 20220325102 | 周宜可 |   22 |
+-------------+--------+------+
5 rows in set (0.00 sec)
10-08 12:12