第三课 排序检索数据

SELECT语句的ORDER BY子句

#ORDER BY子句应是SELECT语句的最后一条子句

【1】SELECT prod_name FROM products;
#将数据以底层表出现的顺序显示

【2】SELECT prod_name FROM products ORDER BY prod_name;
#对prod_name列以字母升序顺序排序数据

【3】SELECT prod_id,prod_price,prod_name FROM products ORDER BY prod_price,prod_name;
#按多个列排序，先按价格prod_price排序，再按名称prod_name排序

【4】SELECT prod_id,prod_price,prod_name FROM products ORDER BY 2,3;
#按列位置排序，ORDER BY 2,3表示先按SELECT清单中的第二列prod_price排序，再按SELECT清单中的第三列prod_name排序

【5】SELECT prod_id,prod_price,prod_name FROM products ORDER BY prod_price DESC;
#指定排序方向，进行降序排序时需指定关键字DESC

【6】SELECT prod_id,prod_price,prod_name FROM products ORDER BY prod_price DESC,prod_name;
#在多个列上降序排序必须对每一列指定DESC关键字
#DESC关键字只应用到直接位于其前面的列名
#DESC是DESCENDING的缩写，俩个关键字都可以使用
#DESC相对的是ASC（ASCENDING）升序，升序是默认的。

【7】SELECT prod_id,prod_price,prod_name FROM products ORDER BY prod_price DESC,prod_name DESC;

需要完整代码或书籍PDF版的小伙伴可关注微信公众号：菜田里守望者

打开微信扫一扫关注吧，你们的支持就是我的动力

参考文献：
【1】《SQL必知必会》第四版 人民邮电出版社 [美] Ben Forta 著 钟鸣 刘晓霞 译

MySQL—Select的子句

知识大纲

• where 条件查询
• group by 分组查询
• having 筛选
• order by 排序
• limit 分页

1.where条件查询

示例

CREATE TABLE students
(
		s_id VARCHAR(12),
		s_name VARCHAR(20),
		s_sex CHAR(2),
		s_age INT,
		s_address VARCHAR(30),
		PRIMARY KEY(s_id)
);

INSERT INTO students VALUES
	('A100101','张大','男',28,'湖南省长沙市雨花区'),
	('A100102','张三','男',18,'湖南省长沙市天心区'),
	('A100103','李四','女',22,'湖南省长沙市岳麓区'),
	('A100104','王五','女',48,'湖南省长沙市开福区'),
	('A100105','赵六','男',62,'湖南省长沙市望城区');

SELECT * FROM students;

#1.WHERE子句 [从原表中的记录中进行筛选]
#查询所有性别为"男"的学生信息
SELECT * FROM students WHERE s_sex='男';

#查询年龄在20岁以上的学生信息
SELECT * FROM students WHERE s_age>=20;

#查询年龄在20岁以上 60岁以下 的学生信息
SELECT * FROM students WHERE s_age>=20 and s_age<=60;
SELECT * FROM students WHERE s_age BETWEEN 20 and 60;

#查询年龄在20岁以下 60岁以上 的学生信息
SELECT * FROM students WHERE s_age NOT BETWEEN 20 and 60;

#查询年龄在20岁以上 性别为"男" 的学生信息
SELECT * FROM students WHERE s_age>=20 AND s_sex='男';

#查询家住雨花区的学生信息
SELECT * FROM students WHERE s_address LIKE '%雨花区';

#查询年龄在家住长沙市 姓"张"的学生信息
SELECT * FROM students WHERE s_name LIKE '张%' AND s_address LIKE '%长沙市%';

#查询年龄在家住雨花区 姓"张"的学生信息
SELECT * FROM students WHERE s_name LIKE '张%' AND s_address LIKE '%雨花区';

#查询 年龄20以上 或 在家住"雨花区" 的学生信息
SELECT * FROM students WHERE s_age>=20 OR s_address LIKE '%雨花区';

2.group by 分组查询

很多情况下，用户都需要进行一些汇总操作，比如统计整个公司的人数或者统计每一 个部门的人数等。

① 聚合函数

• AVG(【DISTINCT】 expr) 返回 expr 的平均值
• COUNT（【DISTINCT】 expr）返回 expr 的非 NULL 值的数目
• MIN(【DISTINCT】 expr)返回 expr 的最小值
• MAX(【DISTINCT】 expr)返回 expr 的最大值
• SUM（【DISTINCT】 expr）返回 expr 的总和

示例

#AVG()
SELECT * FROM students;
#查询学生的平均年龄
SELECT AVG(s_age) as '平均年龄' FROM students;

#COUNT()
#查询学生数目
SELECT COUNT(*) '学生数目' FROM students;
SELECT COUNT(s_name) '学生数目' FROM students;

#查询男同学人数
SELECT COUNT(*) FROM students WHERE s_sex='男';
#查询女同学人数
SELECT COUNT(*) FROM students WHERE s_sex!='男';
SELECT COUNT(*) FROM students WHERE s_sex<>'男';
#查询年龄在30岁以上的男同学人数
SELECT COUNT(*) FROM students WHERE s_sex='男' AND s_age>=30;
SELECT COUNT(s_id) FROM students WHERE s_sex='男' AND s_age>=30;

#MAX()
#查询同学中的年龄最大值
SELECT MAX(s_age) FROM students;

#MIN()
#查询同学中的年龄最小值
SELECT MIN(s_age) FROM students;

#查询年龄最大的同学信息
SELECT * FROM students WHERE s_age=
(
	SELECT MAX(s_age) FROM students
);
#查询年龄最小的同学信息
SELECT * FROM students WHERE s_age=
(
	SELECT MIN(s_age) FROM students
);

#SUM()
SELECT * FROM students;
#获取所有同学的年龄总和
SELECT SUM(s_age) FROM students;

#获取所有"女"同学的年龄总和
SELECT SUM(s_age) FROM students WHERE s_sex='女';

注意：

用 count(*),count(1),谁更好?

其实,对于 myisam 引擎的表,没有区别的.这种引擎内部有一计数器在维护着行数.Innodb 的表,用 count(*)直接读行数,效率很低,因为 innodb 真的要去数一遍.

#查询所有男生数目
SELECT COUNT(*) FROM students WHERE s_sex='男';
#查询所有女生数目
SELECT COUNT(1) FROM students WHERE s_sex='女';

关于 mysql 的 group by 的特殊：

注意：在 SELECT 列表中所有未包含在组函数中的列都应该是包含在 GROUP BY 子句中的，换句话说，SELECT 列表中最好不要出现 GROUP BY 子句中没有的列。

#2.2分组查询 [GROUP BY]
#统计男女生人数
#SELECT s_sex '性别',COUNT(*) '人数' FROM students GROUP BY s_sex;
#[不推荐] SELECT s_name, COUNT(*) FROM students GROUP BY s_sex;
SELECT s_sex, COUNT(*) FROM students GROUP BY s_sex;
SELECT s_name, s_sex, COUNT(*) FROM students GROUP BY s_sex,s_name;
SELECT s_name, s_sex, COUNT(*) FROM students GROUP BY s_name,s_sex;

SELECT * FROM students;
#统计年龄最大者
SELECT * FROM students WHERE s_age=
(
	SELECT MAX(s_age) FROM students
);
#SELECT MAX(s_age) FROM students GROUP BY s_age;#error

#统计男女生中年龄最大者[s_sex]
SELECT s_sex as '性别',MAX(s_age) '最大年龄' FROM students GROUP BY s_sex;

3.having 筛选

having 与 where 类似,可筛选数据

having 与 where 不同点

• where 针对表中的列发挥作用,查询数据；having 针对查询结果中的列发挥作用, 筛选数据
• where 后面不能写分组函数，而 having 后面可以使用分组函数
• having 只用于 group by 分组统计语句

#WHERE子句之后不可以编写聚合[分组]函数
#查询年龄最大者信息
#SELECT * FROM students WHERE s_age=MAX(s_age);#error
#但可以将其[聚合函数]置于子查询中
SELECT * FROM students WHERE s_age=
(
	SELECT MAX(s_age) FROM students
);

#SELECT [SELECT列表] FROM 表名 WHERE [针对原表数据进行筛选]

#3.HAVING子句
#GROUP BY [分组依据] HAVING [基于分组后结果的进一步筛选]

#统计男女人数 [增加筛选条件：人数达到3人以上的]
SELECT s_sex, COUNT(1) num FROM students 
	GROUP BY s_sex HAVING num>=3;

SELECT s_sex, COUNT(1) FROM students 
	GROUP BY s_sex HAVING COUNT(1)>=3;

4.order by 排序

• 按一个或多个字段对查询结果进行排序

用法：order by col1,col2,col3…

说明：

先按 col1 排序如果 col1 相同就按照 col2 排序，依次类推

col1，col2，col3 可以是 select 后面的字段也可以不是

• 默认是升序，也可以在字段后面加 asc 显示说明是升序，desc 为降序
• order by 后面除了跟 1 个或多个字段，还可以写表达式，函数，别名等

#4.ORDER BY [排序]
SELECT * FROM students;
#按照年龄从小到小的顺序显示所有学生信息
#默认排序规则为[ASC升序] [DESC降序]
SELECT * FROM students ORDER BY s_age;
SELECT * FROM students ORDER BY s_age ASC;

#按照年龄从大到小的顺序显示所有学生信息
SELECT * FROM students ORDER BY s_age DESC;

#查询显示所有学生信息 [排序：性别(降序)]
SELECT * FROM students ORDER BY s_sex DESC;
#查询显示所有学生信息 [排序：性别(降序)、年龄(升序)]
SELECT * FROM students ORDER BY s_sex DESC, s_age ASC;

5.limit 分页

limit m,n

m 表示从下标为 m 的记录开始查询，第一条记录下标为 0，n 表示取出 n 条出来，如 果从 m 开始不够 n 条了，就有几条取几条。m=(page-1)*n，（page 页码，n 表示每页显示的条数）

如果第一页 limit 0,n

如果第二页 limit n,n

依次类推，得出公式 limit (page-1)*n , n

#5.LIMIT m,n
SELECT * FROM students LIMIT 0,5;
SELECT * FROM students LIMIT 0,10;

#查询获取原表前三天记录
SELECT * FROM students LIMIT 0,3;

#查询年龄最小的两位同学的信息
SELECT * FROM students ORDER BY s_age LIMIT 0,2;

#查询年龄最大的三位同学的信息
SELECT * FROM students ORDER BY s_age DESC LIMIT 0,3;

#查询年龄最大者信息[方案-1：子查询]
SELECT * FROM students WHERE s_age=
(
	SELECT MAX(s_age) FROM students
);
#查询年龄最大者信息[方案-2：LIMIT]
SELECT * FROM students ORDER BY s_age DESC LIMIT 0,1;

#补充测试数据
INSERT INTO students VALUES
	('A100106','张大1','男',38,'湖南省长沙市雨花区'),
	('A100107','张三1','男',23,'湖南省长沙市天心区'),
	('A100108','李四1','女',24,'湖南省长沙市岳麓区'),
	('A100109','王五1','女',47,'湖南省长沙市开福区'),
	('A100110','赵六1','男',63,'湖南省长沙市望城区'),
	('A100111','张大2','男',31,'湖南省长沙市雨花区'),
	('A100112','张三2','男',83,'湖南省长沙市天心区'),
	('A100113','李四2','女',34,'湖南省长沙市岳麓区'),
	('A100114','王五2','女',49,'湖南省长沙市开福区'),
	('A100115','赵六2','男',62,'湖南省长沙市望城区');

#查看所有信息
SELECT * FROM students;

#将students表中的数据[15条] 分三页显示
#第一页 [1-5]
SELECT * FROM students LIMIT 0,5;
#第一页 [6-10]
SELECT * FROM students LIMIT 5,5;
#第一页 [11-15]
SELECT * FROM students LIMIT 10,5;


#公式limit (page-1)*n , n [page表示页码 n表示每页显示的条数]
SELECT * FROM students LIMIT 0,5;
#SELECT * FROM students LIMIT (1-1)*5,5;

SELECT * FROM students LIMIT 5,5;
#SELECT * FROM students LIMIT (2-1)*5,5;

SELECT * FROM students LIMIT 10,5;
#SELECT * FROM students LIMIT (3-1)*5,5;

为聚合结果指定条件

HAVING子句

HAVING子句可以让我们筛选分组之后的各种数据，其语法结构如下：

SELECT <列名1>,<列名2>,<列名3>
FROM <表名>
GROUP BY <列名1>,<列名2>,<列名3>
HAVING <分组结果对应的条件>

例：

使用HAVING子句前：

使用HAVING子句后：

例：

HAVING子句的构成要素

HAVING子句中能够使用的3种要素，如下：

• 常数
• 聚合函数
• GROUP BY子句中指定的列名(即聚合键)

例：HAVING子句的错误范例

product_name列并不包含在GROUP BY子句中，因此不允许写在HAVING子句中，可以理解为GROUP BY子句汇总后得到的表中并不存在product_name这一列。

例：正确示范

对查询结果进行排序

ORDER BY子句

ORDER BY子句可以用来指定排列的顺序，其语法结构如下：

SELECT <列名1>,<列名2>,<列名3>
FROM <表名>
ORDER BY <排序基准点1>,<排序基准点2>,<排序基准点3>,...;

ORDER BY子句中书写的列名称为排序键。
例：


注意：如果有多个排序键的话，优先按照第一个排序键排序，如果该列存在相同值的话，再接着参考右侧的键，如下图所示：


子句书写顺序：
1.SELECT子句→2.FROM子句→3.WHERE子句→4.GROUP BY子句→5.HAVING子句→6.ORDER BY子句
ORDER BY子句子句通常写在SELECT语句的末尾。

指定升序或降序

想要降序排列时，我们可以在列名后面加上DESC关键字。

例：

注：其实使用升序排序时，正式的书写方式应该是使用ASC关键字，但是省略该关键字时会默认使用升序排序。

由于ASC和DESC这两个关键字是以列为单位指定的，因此可以同时指定一个列为升序，指定其他列为降序，例：

NULL的顺序

默认情况下，MySQL将null算作最小值。

例：

在排序键中使用别名

在讲别名的使用之前我们先梳理一下MySQL的子句语法顺序和执行顺序

MySQL中子句的语法顺序：

SELECT[DISTINCT] →FROM→JOIN→ON→WHERE→GROUP BY→HAVING→UNION→ORDER BY→LIMIT

MySQL中子句的执行顺序，即在执行时SQL按照下面的顺序进行执行：

FROM→JOIN→ON→WHERE→GROUP BY(开始使用SELECT中的别名，后面的语句中都可以使用别名)→AVG，SUM，…→HAVING→SELECT→DISTINCT→ORDER BY

由此我们可以在OEDER BY子句中使用别名。

ORDER BY子句中可以使用的列

ORDER BY子句中也可以使用存在于表中、但并不包含在SELECT子句中的列，如下图：

除此以外，还可以使用聚合函数，如下图：

不要使用列编号

列编号是指SELECT子句中的列按照从左到右的顺序进行排列时所对应的编号(1,2,3…)。
例：


以上两个例子的执行结果是一样的，也就是说我们可以使用列编号来执行ORDER BY语句，但是使用列编号可能会造成一些问题。
不要使用列编号理由如下：

1. 代码阅读起来比较困难；
2. 该功能将来会被删除(SQL-92中明确指出了该排序功能未来会被删除)。