SQL语句之排序、聚合、分组、分页查询

DQL高级查询

在查询之前，我们也先准备好一些数据

-- 创建db2数据库
CREATE DATABASE db2;
-- 使用db2数据库
USE db2;
-- 数据student数据表
CREATE TABLE student (
 id INT, -- 编号
 NAME VARCHAR(20), -- 姓名
 age INT, -- 年龄
 sex VARCHAR(5), -- 性别
 address VARCHAR(100), -- 地址
 math INT, -- 数学
 english INT -- 英语
);

-- 添加表记录
INSERT INTO student(id,NAME,age,sex,address,math,english) VALUES (1,'马云',55,'男','
杭州',66,78),(2,'马化腾',45,'女','深圳',98,87),(3,'马景涛',55,'男','香港',56,77),(4,'柳岩
',20,'女','湖南',76,65),(5,'柳青',20,'男','湖南',86,NULL),(6,'刘德华',57,'男','香港
',99,99),(7,'马德',22,'女','香港',99,99),(8,'德玛西亚',18,'男','南京',56,65);

DQL 排序查询

– 使用order by指定字段进行排序,desc 降序 asc 升序，默认不写为升序

-- 排序查询
SELECT 字段列表 FROM 表名 ORDER BY 排序列

-- 按照english英语成绩进行排序，默认为升序排列
SELECT * FROM student ORDER BY english;
-- 按照english英语成绩进行排序，desc表示降序
SELECT * FROM student ORDER BY english DESC;
-- 按照english英语成绩进行排列，asc表示升序
SELECT * FROM student ORDER BY english ASC;

-- 先按math升序排列，如果math成绩相同，再按照english升序排列
SELECT * FROM student ORDER BY math ASC,english ASC;

下图为最后一条sql语句排序的结果集

DQL 聚合函数

聚合函数指在查询数据时可以将一列数据进行纵向的计算。掌握下面几个聚合函数

函数名	意义	语法格式
count	求指定列的个数	count(列名)
min	求指定列的最小值	min(列名)
max	求指定列的最大值	max(列名)
sum	求指定列的和	sum(列名)
avg	指定列的平均值	avg(列名)

-- 聚合函数查询的语法
SELECT 聚合函数(列名) FROM 表名

COUNT函数

-- 注意：如果类中有NULL值，不算个数

-- 求student表中math列有多少条记录，结果为8
SELECT COUNT(math) FROM student;
-- 求student表中english列有多少条记录，结果为7，因为有一个NULL值
SELECT COUNT(english) FROM student;

-- 查询所有列个数【比较常用】
SELECT COUNT(*) FROM student;

MIN函数

- 查询math的最小值
SELECT MIN(math) FROM student;

MAX函数

-- 查询math的最大值
SELECT MAX(math) FROM student;

SUM函数

-- 查询math的和
SELECT SUM(math) FROM student;

AVG函数

-- 查询math的平均值
SELECT AVG(math) FROM student;

DQL 分组查询

按照特定条件把数据进行分组，把每一组当做一个整体，分别对某一组数据进行计算。

-- 分组查询语法，字段列表只能是分组列、或者聚合函数
SELECT 字段列表 FROM 表名 where 分组前条件 GROUP BY 分组列名 HAVING 分组后条件

对所有数据分组查询

-- 按姓别进行分别，查询男、女的math的平均值
SELECT sex,AVG(math) FROM student GROUP BY sex;
-- 按性别进行分组，查询男、女同学的math平均分，和人数
SELECT sex, AVG(math), COUNT(id) FROM student GROUP BY sex;

分组前筛选

-- 分组前进行筛选使用where子句
-- 对>70分的人按照性别进行分组，查询男、女同学的math平均分和人数
SELECT sex, AVG(math),COUNT(id) FROM student WHERE math>70 GROUP BY sex;

分组后筛选

-- 分组后再进行筛选，使用having子句
-- 对>70分的人按照性别进行分组，查询男、女同学的math平均分和人数，再筛选人数>2的数据
SELECT sex, AVG(math),COUNT(id) FROM student WHERE math>70 GROUP BY sex HAVING COUNT(id)>2;

-- 可以使用AS语句给聚合函数取别名，改进上面的sql语句
SELECT sex, AVG(math) AS 平均分,COUNT(id) AS 个数 FROM student WHERE math>70 GROUP BY sex HAVING 个数>2;

DQL 分页查询

分页查询指当要查询的数据量比较多的时候，采用一次查询固定条记录的方式。

-- 开始索引=(当前页码-1)*个数
select 字段列表	from 表名 limit 开始索引,个数

-- 查询0索引开始的3条记录
SELECT * FROM student LIMIT 0,3; -- 第1页

-- 查询3索引开始的3条记录
SELECT * FROM student LIMIT 3,3; -- 第2页

-- 查询6索引开始的3条记录
SELECT * FROM student LIMIT 6,3; -- 第3页