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,nSELECT * 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;
