接上篇mysql单表查询的高级查询及案例

4.3 高级查询

4.3.1聚合函数

实际开发中，经常需要对某些数据进行统计，例如统计某个字段的最大值、最小值、平均值等，为此，MySQL中提供了一些函数来实现一些功能，具体如下：

图中函数用于对一组值进行统计，并返回唯一值，这些函数被称为聚合函数，下面一一详解。

1、 COUNT()函数

COUNT()函数用来统计记录的条数。语法格式如下所示：

SELECT COUNT(*) FROM 表名;

【例】查询student表中一共有多少条记录

SELECT COUNT(*) FROM student;

2、 SUM()函数

SUM()是求和函数，用于求出表中某个字段所有值的总和。语法格式如下所示：

SELECT SUM(字段名) FROM 表名;

【例】求student表中grade字段的总和

SELECT SUM(grade) FROM student;

3、 AVG()函数

AVG()函数用于求出某个字段所有值的平均值。语法格式如下所示：

SELECT AVG(字段名) FROM 表名;

【例】求student表中grade字段的平均值。

SELECT AVG(grade) FROM student;

4、 MAX()函数

MAX()函数是求最大值的函数，用于求出某个字段的最大值。语法格式如下所示：

SELECT MAX(字段名) FROM 表名;

【例】求student表中所有学生grade字段的最大值。

SELECT MAX(grade) FROM student;

5、 MIN()函数

MIN()函数是求最小值的函数，用于求出某个字段的最小值。语法格式如下所示：

SELECT MIN(字段名) FROM 表名;

【例】求student表中所有学生grade字段的最小值。

SELECT MIN(grade) FROM student;

4.3.2对查询结果排序

从表中查询出来的数据可能是无序的，或者其排列顺序不是用户期待的，为了使查询结果满足用户的要求，可以使用ORDER BY对查询结果进行排序。语法格式如下所示：

SELECT * | 字段名1,字段名2,…… 

FROM 表名 

ORDER BY 字段名1 [ASC | DESC]，字段名2 [ASC | DESC]…… ;

注：参数ASC表示按照升序进行排序，DESC表示按照降序进行排序。默认情况下，按照ASC方式进行排序。

【例】查student表中所有记录，并按照grade字段进行排序

SELECT * FROM student

ORDER BY grade;

从查询结果可以看出，返回的记录按照ORDER BY 指定的字段grade进行排序，并且默认是按照升序排序。

【例】查student表中所有记录，并按照grade字段升序进行排序。

SELECT * FROM student ORDER BY grade ASC;

在MySQL中，可以指定按照多个字段进行查询结果进行排序，例如，将查出的student表中的所有记录按照gender和grade字段进行排序。

在排序过程中，会先按照gender字段进行排序，如果遇到gender字段值相同的记录，再把这些记录按照grade字段进行排序。

【例】查student表中所有记录，并按照gender字段升序和grade字段的降序进行排列。

SELECT * FROM student

ORDER BY gender ASC,grade DESC;

从查询记录可以看到，返回的结果首先按照gender字段升序进行排序，然后gender值为“男”和“女”的记录分别再按照grade字段值的降序进行排列。

注意：在按照指定字段进行升序排序的时候，如果某条记录的字段值为NULL，则这条记录会在第一条显示，这是因为NULL值可以被认为是最小值。

4.3.3分组查询

在对表中数据进行统计时，也可能需要按照一定的类别进行统计，比如，分别统计student表中gender字段值为“男”、“女”和NULL的学生成绩（grade字段）之和。在MySQL中，可以使用GROUP BY按某个字段或者多个字段中的值进行分组，字段中值相同的为一组。语法格式如下所示：

SELECT * | 字段名1，字段名2，…… 

FROM 表名 

GROUP BY 字段名1，字段名2，…… [HAVING 条件表达式];

注：HAVING关键字指定条件表达式对分组后的内容进行过滤。需要特别注意的是，GROUP BY一般和聚合函数一起使用，如果查询的字段出现在GROUP BY后，却没有没有包含在聚合函数中，该字段显示的是分组后的第一条记录的值。

品牌	数量
得力	2
爱好	1
晨光	2

 

 

 

1、单独使用GROUP BY分组

单独使用GROUP BY关键字，查询的是每个分组中的一条记录。

例: 查询student表中的记录，按照gender字段值进行分组，SQL语句及其执行结果如下所示：

SELECT * FROM student GROUP BY gender;

从查询结果可以看到返回了三条记录，这三条记录中的gender字段的值分别为“NULL”，“男”，“女”，这说明了查询结果是按照gender字段中不同的值进行分类。然而这样的查询结果只显示每个分组的一条记录，意义不大，一般情况下GROUP BY都和聚合函数一块使用。

2、 GROUP BY和聚合函数一起使用

GROUP BY和聚合函数一起使用，可以统计出某个或者某些字段在一个分组中的最大值、最小值、平均值等等。
例: 将student表按照gender字段值进行分组查询，计算出每个分组中各有多少名学生，SQL语句及其执行结果如下所示：

SELECT COUNT(*),gender FROM student GROUP BY gender;

从查询结果可以看到，GROUP BY对student表按照gender字段中的不同值进行了分组，并通过COUNT()函数统计出gender字段值为“NULL”的学生有一个，gender字段值为“男”的学生有5个，，gender字段值为“女”的学生有2个。

3、 GROUP BY和HAVING关键字一起使用

HAVING关键字和WHERE关键字的作用相同，都用于设置条件表达式对查询结果进行过滤。HAVING关键字和WHERE关键字的区别在于，HAVING关键字后可以跟聚合函数，而WHERE关键字不能。通常情况下HAVING关键字都和GROUP BY一起使用，用于对分组后的结果进行过滤。

例:将student表按照gender字段值进行分组查询，查询出grade字段值之和小于300的分组。

SELECT sum(grade),gender 

FROM student

GROUP BY gender 

HAVING SUM(grade)<300;

从查询结果可以看出，只有gender字段为“NULL”和“女”的分组其grade字段值之和小于300。

4.3.4 使用LIMIT限制查询结果的数量

查询数据时，可能会返回很多条记录，而用户需要的记录可能只是其中的一条或者几条，比如实现分页功能，每页显示10条信息，每次查询就需要查出10条记录。为此，MySQL中提供了一个关键字LIMIT，可以指定查询结果从哪一条记录开始以及一共查询多少条信息。语法格式如下所示：

SELECT * | 字段名1,字段名2,…… 

FROM 表名 

LIMIT [OFFSET，] 记录数;

注：OFFSET偏移量是可选关键字，若没有默认从1开始查询，若有为n,则从n+1开始查询。

例:查询student表中的前4条记录，SQL语句及其执行结果如下所示：

SELECT * FROM student LIMIT 4;

从查询结果可以看，执行语句没有指定返回记录的偏移量，只指定了查询记录的条数4，因此返回结果从第一条记录开始，一共返回4条记录。

例:查询student表中的grade字段值从第5位到第8位的学生记录，SQL语句及其执行结果如下所示：

SELECT * 

FROM student 

ORDER BY grade DESC 

LIMIT 4,4;

从查询结果可以看，返回了4条记录，为了验证返回记录的grade字段值从第5位到第8位，下面对student表中所用的记录按照grade字段从高到低的顺序进行排列。

SELECT * 

FROM student 

ORDER BY grade DESC;

4.4 为表和字段取别名

MySQL在查询数据时，可以为表和字段取别名，这个别名可以代替其指定的表和字段。

4.4.1 为表取别名

查询数据时，如果表名很长，使用起来不方便，此时，就可以为表取一个别名，用这个别名来代替表的名称

SELECT * FROM 表名 [AS] 别名；

注意，为表指定别名，AS关键字可以省略不写。

例:为student表，取别名s，并查询student表中gender字段值为女的记录。

SELECT * FROM student AS s WHERE s.gender='女';

4.4.2 为字段取别名

在查询数据时，为了使显示的查询结果更加直观，可以为字段取一个别名。

SELECT 字段名 [AS] 别名 [，字段名 [AS] 别名，……] 

FROM 表名；

注意，为字段指定别名，AS关键字可以省略不写。

例:查询student表中所有记录的，name和gender字段值，并为这两个字段起别名，stu_name和stu_gender。

SELECT name AS stu_name,gender stu_gender 

FROM student;

 

 

【例1】查询student表中的所有记录，

（1）首先创建一个数据库chapter04，语句如下：

CREATE  DATABASE  chapter04;

（2）选择使用chapter04：

USE  chapter04;

（3）在chapter04中创建表student：

CREATE TABLE student(

id INT(3) PRIMARY KEY AUTO_INCREMENT,

name VARCHAR(20) NOT NULL,

grade FLOAT,

gender CHAR(2)

);

（4） 执行SQL语句创建student表，然后使用INSERT语句向student表中插入8条记录，INSERT语句如下：

INSERT INTO student(name,grade,gender)

VALUES('songjiang',40,'男'),

('wuyong',100,'男'),

('qinming',90,'男'),

('husanniang',88,'女'),

('sunerniang',66,'女'),

('wusong',86,'男'),

('linchong',92,'男'),

('yanqing',90,NULL);