【MySQL - 4】条件查询、排序查询、分组查询、分页查询、聚合函数等DQL操作大全（R）

在开始介绍各种查询之前，先给出一个总的语法框架

select
	列名1, 列名2, ... ,列名n
from
	表名
where
	条件
group by
	分组依据（列名）
having
	分组后的条件（想看哪个分组）
limit
	分页限定;

以上的语法框架并不是每个部分都必须要有的，必须要有的部分是

select 列名1, 列名2, ... ,列名n from 表名;

其它部分则是根据需求进行选用，那么下面就来详细了解每个部分的功能吧

1. 基础查询

其实基础查询就是刚刚所说的必须要有的部分：

select 列名1, 列名2, ... ,列名n from 表名;

其中，如果我要查询所有的列名，就是查询整个表的话，则可以用*来代替列名列表，即上节课所介绍的：

select * from 表名;

为了方便演示，这里就不用上节课所创建的表啦，这里创建了一个新表

CREATE TABLE test(id INT,NAME VARCHAR(8),sex VARCHAR(8),age INT,address VARCHAR(8),math INT,english INT);
INSERT INTO test(id,NAME,sex,age,address,math,english) VALUES(1,"小明","男",23,"广东",98,65),
(2,"花花公主","女",18,"福建",85,90),(3,"小月","女",47,"福建",83,99),(4,"马冬梅","女",65,"湖南",80,70),
(5,"冬秋夏","男",18,"江西",23,NULL),(6,"江小冬","女",19,"北京",85,90),(7,"强仔","男",20,"上海",73,47),
(8,"金翎中","男",30,"广东",87,56);

查看一下这个表

SELECT * FROM test;

假如我不想看那么多信息，我只想看学号、姓名与性别怎么操作呢？

SELECT id,NAME,sex FROM test;

去重

这时候，如果我又想看看这些同学分别在什么城市

SELECT address FROM test;

你会发现这样查询出来的城市数据，会有重复的部分，那么可不可以去重呢？那肯定是可以的，这里有一个DISTINCT关键词

SELECT DISTINCT address FROM test;

这样就可以实现去重的效果啦

四则运算

假如我想看一下每个同学的数学和英语成绩的总分，但是原始数据没有，怎么办呢？别担心，有办法，我们可以使用四则运算计算一些列的值并显示，什么意思？来，看看

SELECT NAME,math,english,math+english FROM test;

其查询结果

哇，是不是很神奇？但是，冬秋夏同学好像有点问题，怎么会是NULL呢？英语没有考也应该有数学成绩呀？这是因为MySQL运算过程中只要有NULL都会得出NULL结果，怎么办呢？别急别急，我们可以利用IFNULL表达式改进一下

• IFNULL(表达式1, 表达式2)：假如表达式1的值是NULL，则替换为表达式2

下面来改写一下语句

SELECT NAME,math,english,math+IFNULL(english,0) FROM test;

起别名

冬秋夏同学正常了吧？但是你又觉得这个列名太长了，可不可以自定义呢？对，也是可以的，这里就有一个AS关键词

SELECT NAME,math,english,math+IFNULL(english,0) AS total FROM test;

好看很多了吧？其实还可以改进，怎么改进？AS省略也可以达到重命名的效果

SELECT NAME,math,english,math+IFNULL(english,0) total FROM test;

其运行结果也是同样的，这里就不再贴出来啦

2. 条件查询

条件查询顾名思义就是筛选我们想看的数据，因此需要对数据定位，因此就需要WHERE关键词，其实在前面两篇文章中已经有接触过这个关键词了，下面先来介绍一下一些常用的运算符

• <, >, <=, >=, =, <>
• BETWEEN…AND…
• IN(值1, 值2, … ,值n)
• IS NULL
• AND 或 &&
• OR 或 ||
• NOT 或 ！

上述<>运算符其实就是不等运算符，IN运算符随后直接演示会更加清楚，除此以外应该各自的意思都很清楚，就不详细解释了，下面直接来演示一下他的效果

现在我需要看数学和英语都及格的同学的成绩

SELECT * FROM test WHERE math>60 AND english>60;

查看数学成绩在70与80之间的同学的成绩

SELECT * FROM test WHERE math BETWEEN 70 AND 80;

找出那个没有考英语的同学（英语成绩为空），注意这里不能使用=NULL来判断

SELECT * FROM test WHERE english IS NULL;

现在要找出英语成绩是46，56或65的同学

SELECT * FROM test WHERE english IN(65,56,47);

经过一波操作，是不是就很清楚明白了，下面再介绍另外一个条件查询

模糊查询

• LIKE：有两个占位符，_表示单个任意字符，%表示多个任意字符

究竟什么是模糊查询呢？这里来个简单的例子，我想要找一个名字里有“冬”的同学，那么这个冬就有可能出现在任意位置，因此需要用到占位符，%可以表示0-n个任意字符，因此这里使用它作为占位符

SELECT * FROM test WHERE NAME LIKE "%冬%";

现在我主意变了，想要找一个名字第一个字是“小”的同学

SELECT * FROM test WHERE NAME LIKE "小%";

最后我再想找一下名字的第二个字是“小”的同学，因为说明了是第二个字，那么这个字前就应该需要且只需要一个表示单个字符的占位符

SELECT * FROM test WHERE NAME LIKE "_小%";

3. 排序查询

为排序查询服务的正是ORDER BY关键词，他的语法也很简单

SELECT 列名1, 列名2, ... ,列名n from 表名 ORDER BY 列名1 排序方式1, 列名2 排序方式2, ... ,列名n;

排序方式有两种：

• ASC：升序排序，若不指定排序方式则默认以该种方式进行排序
• DESC：降序排序

另外注意，如果有多个排序条件，仅当前面的条件值一样的时候才会判断第二条件

现在我们按数学成绩升序排序来查看一下

SELECT * FROM test ORDER BY math;

我们再按英语成绩为倒序排序来查看一下

SELECT * FROM test ORDER BY english DESC;

终极混合使用，现在需求是，按数学成绩升序排序，若数学成绩相同则按英语成绩升序排序，若英语成绩也相同则按学号降序排序

SELECT * FROM test ORDER BY math ASC,english ASC,id DESC;

4. 聚合函数

聚合函数：将一列数据作为一个整体进行纵向计算，它的函数关键字有如下几种：

• count：计算个数，一般选择不会有空值的列，假如非要选择有空值的列，也可以使用IFNULL解决
• max：计算最大值，有空值会自动排除空值
• min：计算最小值，有空值会自动排除空值
• sum：计算和，有空值会自动排除空值
• avg：计算平均值，有空值会自动排除空值

下面开始演示时间，首先是看总人数

SELECT COUNT(id) FROM test;

英语不及格的总人数

SELECT COUNT(id) FROM test WHERE IFNULL(english,0) < 60;

最高的数学成绩

SELECT MAX(math) FROM test;

最低的英语成绩

SELECT MIN(english) FROM test;

咦，好像有点问题，不应该是没有考试的同学最低吗？对，理论上肯定是这样，但是刚刚说了，这个函数会把NULL值自动排除，因此我们需要做些手脚

SELECT MIN(IFNULL(english,0)) FROM test;

再来看看全班的数学总分和英语总分

SELECT SUM(math),SUM(english) FROM test;

总分没啥意义？我们来看看平均分

SELECT AVG(math),AVG(english) FROM test;

慢着，英语平均分好像不太对？怎么没算没有考那个同学？这就对了，都说自动排除了，所以我们还是要做手脚

SELECT AVG(math),AVG(IFNULL(english,0)) FROM test;

这个成绩就对了嘛，可是作为班主任的你又觉得平均分太低了很难看，因此你决定看看及格的人的平均分

SELECT AVG(math),AVG(IFNULL(english,0)) FROM test WHERE math>=60 AND english>=60;

5. 分组查询

分组查询的关键词的GROUP BY，他的一般使用格式：

SELECT 分组依据（列名）,聚合函数 FROM test GROUP BY 分组依据（列名）;

是不是好像有点抽象？我们先来个直观的例子，假如我要看看女生与男生分别的数学平均分

SELECT sex,AVG(math) FROM test GROUP BY sex;

是不是很清楚啦？其实在分组关键词附近还可以用关键词进行限定，主要是两个关键词：

• WHERE：在分组之前进行限定，如果不满足条件，则不参与分组，其条件表达式不可以使用聚合函数
• HAVING：在分组之后进行限定，如果不满足条件，则不进行显示，其条件表达式可以使用聚合函数

又懵了对吧，下面继续用例子来唤醒你，现在我要看一下男生和女生数学成绩大于80的人数

SELECT sex,COUNT(id) FROM test WHERE math>80 GROUP BY sex;

这时候你又不想看见那些人数太少的，因此我们又来限定一下

SELECT sex,COUNT(id) FROM test WHERE math>80 GROUP BY sex HAVING COUNT(id)>2;

咦，那个函数写两次聚合函数有点麻烦？你也可以帮它改名的

SELECT sex,COUNT(id) 人数 FROM test WHERE math>80 GROUP BY sex HAVING 人数>2;

6. 分页查询

说起分页查询，大家一下子可能不知道是什么东西，但绝对你用得很多，你在百度搜索某样东西的时候，通常会有数以万计的搜索结果，那百度是把全部结果都扔出来给你看了吗？没有，他给你10个结果一页，看完了你自己再去点下一页对吧？是这种操作吧？因此我们这里所介绍的分页查询同样也是这个意思，它所使用的关键字是LIMIT，下面来看看他的语法

SELECT 列名1, 列名2, ... ,列名n FROM 表名 LIMIT 开始索引,每页查询的页数;

注意这个开始索引跟我们学的数组类似，是以0为开始的，下面就来看看刚刚那个学生表第一页查询的结果

SELECT * FROM test LIMIT 0,3;

第二页

SELECT * FROM test LIMIT 3,3;

第三页

SELECT * FROM test LIMIT 6,3;

最后一页结果不够显示则会有多少显示多少

好啦，DQL相关操作全部介绍完啦，激动不？本文的内容比较多，语法很容易忘记，大家好好练习