MySQL—Select的子句（where条件查询、group by 分组查询、having 筛选、order by 排序、limit 分页）

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;