第7章查询数据

红太狼 2022-06-14 05:11 237阅读 0赞

学习目标  
数据库管理系统的一个最重要的功能就是查询数据，数据查询不应只是简单返回数据库中存储的数据，还应该根据需要对数据进行筛选，以及确定数据以什么样的格式显示。MySQL提供了功能强大、灵活的语句来实现这些操作，本章将介绍如何使用SELECT语句查询数据表中的一列或多列数据、使用集合函数显示查询结果、连接查询、子查询以及使用正则表达式进行查询等。

## 7.1 基本查询语句 ##

MySQL从数据表中查询数据的基本语句为SELECT语句。SELECT语句的基本格式是：

SELECT
        {*|<字段列表>}
        [
            FROM <表1>,<表2>...
            [WHERE <表达式>
            [GROUP BY <group by definition>]
            [HAVING <expression> [{<operator> <expression>}...]]
            [ORDER BY <order by definition>]
            [LIMIT [<offset>,] <row count>]
        ]
    SELECT [字段1,字段2,...,字段n]
    FROM [表或试图]
    WHERE [查询条件];

其中各条子句的含义如下：

*  \{\*|<字段列表>\}包含星号通配符选字段列表，表示查询的字段，其中字段列表列至少包含一个字段名称，如果要查询多个字段，多个字段之间用逗号隔开，最后一个字段后不要加逗号。
 *  FROM <表1>,<表2>…，表1和表2表示查询数据的来源，可以是单个或者多个。
 *  WHERE子句是可选项，如果选择该项，将限定查询行必须满足的查询条件。
 *  GROUP BY <字段>，该子句告诉MySQL如何显示查询出来的数据，并按照指定的字段分组。
 *  \[ORDER BY <字段>\]，该子句告诉MySQL按什么样的顺序显示查询出来的数据，可以进行的排序有：升序（ASC）、降序（DESC）。
 *  \[LIMIT \[，\] \]，该子句告诉MySQL每次显示查询出来的数据条数。

SELECT的可选参数比较多，读者可能无法一下完全理解，不要紧，接下来将从最简单的开始，一步一步深入学习之后，读者会对各个参数的作用有清晰的认识。  
下面以一个例子说明如何使用SELECT从单个表中获取数据。  
首先定义数据表，输入语句如下：

CREATE TABLE fruits ( f_id CHAR (10) NOT NULL, s_id INT NOT NULL, f_name CHAR (255) NOT NULL, f_price DECIMAL (8, 2) NOT NULL, PRIMARY KEY (f_id) );

为了演示如何使用SELECT语句，需要插入如下数据：

INSERT INTO fruits VALUES ('a1', 101, 'apple', 5.2), ('b1', 101, 'blackberry', 10.2), ('bs1', 102, 'orange', 11.2), ('bs2', 105, 'melon', 8.2), ('t1', 102, 'banana', 10.3), ('t2', 102, 'grape', 5.3), ('c0', 101, 'cherry', 3.2), ('a2', 103, 'apricot', 2.2), ('l2', 104, 'lemon', 6.4), ('b2', 104, 'berry', 7.6), ('m1', 106, 'mango', 15.6), ('m2', 105, 'xbabay', 2.6), ('t4', 107, 'xbababa', 3.6), ('m3', 105, 'xxtt', 11.6), ('b5', 107, 'xxxx', 3.6);

使用SELECT语句查询f\_id字段的数据。

SELECT f_id, f_name FROM fruits;

![这里写图片描述][SouthEast]  
该语句的执行过程是，SELECT语句决定了要查询的列值，在这里查询f\_id和f\_name两个字段的值，FROM子句指定了数据的来源，这里指定数据表fruits，因此返回结果为fruits表中f\_id和f\_name两个字段下所有数据。其显示顺序为添加到表中的顺序。

## 7.2表单查询 ##

表单查询是指从一张表数据中查询所需的数据。本节将介绍单表查询中的各种基本的查询方式，主要有：查询所有字段、查询指定字段、查询指定记录、查询空值、多条件的查询、对查询结果进行排序等。

### 7.2.1 查询所有字段 ###

1.在SELECT语句中使用星号“\*”通配符查询所有字段  
SELECT查询记录最简单的形式是从一个表中检索所有记录，实现的方法是使用星号（\*）通配符指定查找所有列的名称。语法格式如下：

SELECT * FROM 表名

【例7.1】从fruits表中检索所有的数据，SQL语句如下：

SELECT * from fruits;

![这里写图片描述][SouthEast 1]  
可以看到，使用星号（\*）通配符时，将返回所有列，列按照定义表时候的顺序显示。  
2.在SELECT语句中指定所有字段  
下面介绍另外一种查询所有字段值的方法。根据前面SELECT语句的格式，SELECT关键字后面的字段名为将要查找的数据，因此可以将表中所有字段的名称跟在SELECT子句后面，如果忘记了字段名称，可以使用DESC命令查看表的结构。有时候，由于表中的字段可能比较多，不一定能记得所有字段的名称，因此该方法会很不方便，不建议使用。例如查询fruits表中的所有数据，SQL语句也可以书写如下，

SELECT f_id,s_id,f_name,f_price FROM fruits;

查询结果与【例7.1】相同。

> 提示：  
> 一般情况下，除非使用表中所有的字段数据，最好不要使用通配符“\*”。使用通配符虽然可以节省输入查询语句的时间，但是获取不需要的列数据通常会降低查询和所使用的应用程序的效率。通配符的优势是，当不知道所需要的列的名称时，可以通过它获取他们。

### 7.2.2 查询指定字段 ###

1.查询单个字段  
查询表中的某一个字段，语法格式为：

SELECT 列名 FROM 表名;

【例7.2】查询fruits表中f\_name列所有水果名称，SQL语句如下：

SELECT f_name FROM fruits;

该语句使用SELECT声明从fruits表中获取名称为f\_name字段下的所有水果名称，指定字段的名称紧跟在SELECT关键字之后，查询结果如下：  
![这里写图片描述][SouthEast 2]

输出结果显示了fruits表中f\_name字段下的所有数据。

2.查询多个字段

使用SELECT声明，可以获取多个字段下的数据，只需要在关键字SELECT后面指定要查找的字段的名称，不同字段名称之间用逗号（，）分隔开，最后一个字段后面不需要加逗号，语法格式如下：

> SELECT 字段名1，字段名2，…，字段名n FROM 表名;

【例7.3】例如，从fruits表中获取f\_name和f\_price两列，SQL语句如下：

SELECT f_name,f_price FROM fruits;

该语句使用SELECT声明从fruits表中获取名称为f\_name和f\_price两个字段下的所有水果名称和价格，两个字段之间用逗号分隔开，查询结果如下：  
![这里写图片描述][SouthEast 3]

输出结果显示了fruits表中f\_name和f\_price两个字段下的所有数据。

> 提示  
> MySQL中的SQL语句是不区分大小写的，因此SELECT和select作用是相同的，但是，开发人员习惯将关键字使用大写，而数据列和表名使用小写，读者也应该养成一个良好的变成习惯，这样写出来的代码更容易阅读和维护。

### 7.2.3 查询指定记录 ###

数据库中包含大量的数据，根据特殊要求，可能只需要查询表中的指定数据，即对数据进行过滤。在SELECT语句中，通过WHERE子句可以对数据进行过滤，语法格式为：

> SELECT 字段名1，字段名2，…，字段名n  
> FROM 表名  
> WHERE 查询条件

在WHERE子句中，MySQL提供了一系列的条件判断符，查询结果如表7.1所示。

表7.1 WHERE条件判断符

<table> 
 <thead> 
  <tr> 
   <th>操作符</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>=</td> 
   <td>相等</td> 
  </tr> 
  <tr> 
   <td>&lt;&gt;，!=</td> 
   <td>不相等</td> 
  </tr> 
  <tr> 
   <td>&lt;</td> 
   <td>小于</td> 
  </tr> 
  <tr> 
   <td>&lt;=</td> 
   <td>小于或者等于</td> 
  </tr> 
  <tr> 
   <td>&gt;</td> 
   <td>大于</td> 
  </tr> 
  <tr> 
   <td>&gt;=</td> 
   <td>大于或者等于</td> 
  </tr> 
  <tr> 
   <td>BETWEEN</td> 
   <td>位于两值之间</td> 
  </tr> 
 </tbody> 
</table>

【例7.4】查询价格为10.2元的水果的名称，SQL语句如下：

SELECT f_name,f_price FROM fruits WHERE f_price=10.2;

该语句使用SELECT声明从fruits表中获取价格等于10.2的水果的数据，从查询结果可以看到，价格是10.2的水果的名称是blackberry，其他的均不满足查询条件，查询结果如下：  
　　![这里写图片描述][SouthEast 4]  
　　本例采用了简单的相等过滤，查询一个指定列f\_price具有值10.20。  
　　相等还可以用来比较字符串，如下：  
　　【例7.5】查找名称为“apple”的水果的价格，SQL语句如下：

> SELECT f\_name,f\_price FROM fruits WHERE f\_name=’apple’;

该语句使用SELECT声明从fruits表中获取名为“apple”的水果的价格，从查询结果可以看到只有名为“apple”行被返回，其他的均不满足查询条件。  
　　![这里写图片描述][SouthEast 5]

【例７.６】查询价格小于10的水果的名称，SQL如下：

SELECT f_name,f_price FROM fruits WHERE f_price<10;

该语句使用SELECT声明从fruits表中获取价格低于10的水果名称，即f\_price小于10的水果信息被返回，查询结果如下：  
　　![这里写图片描述][SouthEast 6]  
　　可以看到查询结果中，所有记录的f\_price字段的值均小于10.00元。而大于或等于10.00元的记录没有被返回。

### 7.2.4 带IN关键字的查询 ###

IN操作符用来查询满足指定范围内的条件的记录，使用IN操作符，将所有检索条件用括号括起来，检索条件之间用逗号隔开。只要满足条件范围内的一个值即为匹配项。  
　　【例7.7】s\_id为101和102的记录，SQL语句如下：

SELECT s_id,f_name,f_price FROM fruits WHERE s_id IN(101,102) ORDER BY f_name;

查询结果如下：  
![这里写图片描述][SouthEast 7]

相反的，可以使用关键字NOT来检索不在条件范围内的记录。  
【例7.8】查询所有s\_id不等于101也不等于102的记录，SQL语句如下：

SELECT s_id,f_name,f_price FROM fruits WHERE s_id NOT IN (101,102) ORDER BY f_name;

查询结果如下：  
![这里写图片描述][SouthEast 8]

可以看到，该语句在IN关键字前面加上了NOT关键字，这使得查询的结果与前面一个的结果正好相反，前面检索了s\_id等于101和102的记录，而这里所要求的查询的记录中的s\_id字段值不等于这两个值中的任何一个。

### 7.2.5 带BETWEEN AND 的范围查询 ###

BETWEEN AND 用来查询某个范围内的值，该操作符需要两个参数，即范围的开始值和结束值，如果字段值满足指定的范围查询条件，则这些记录被返回。  
　　【例 7.9】查询价格在2.00元到10.20元之间的水果名称和价格，SQL语句如下：

SELECT f_name,f_price FROM fruits WHERE f_price BETWEEN 2.00 AND 10.20;

查询结果如下：  
![这里写图片描述][SouthEast 9]

可以看到，返回结果包含了价格从2.00元到10.20元之间的字段值，并且端点值10.20也包括在返回结果中，即BETWEEN匹配范围中所有值，包括开始值和结束值。  
　　BETWEEN AND 操作符前可以加关键字NOT，表示指定范围之外的值，如果字段值不满足指定范围内的值，则这些记录被返回。  
　　【例 7.10】查询价格在2.00元到10.20元之外的水果名称和价格，SQL语句如下：

SELECT f_name,f_price FROM fruits WHERE f_price NOT BETWEEN 2.00 AND 10.20;

查询结果如下：  
![这里写图片描述][SouthEast 10]

由结果可以看到，返回的记录只有f\_price字段大于10.20的，其实，f\_price字段小于2.00的记录也满足查询条件。因此，如果表中有f\_price字段小于2.00的记录，也应当作为查询结果。

### 7.2.6 带LIKE的字符匹配查询 ###

在前面的检索操作中，讲述了如何查询多个字段的记录，如何进行比较查询或者是查询一个条件范围内的记录，如果要查找所有的包含字符“ge”的水果名称，该如何查找呢？简单的比较操作在这里已经行不通了，在这里，需要使用通配符进行匹配查找，通过创建查找模式对表中的数据进行比较。执行这个任务的关键字是LIKE。  
　　通配符是一种在SQL的WHERE条件子句中拥有特殊意思的字符，SQL语句中支持多种通配符，可以和LIKE一起使用的通配符有‘%’和‘\_’。

#### 1. 百分号通配符‘%’，匹配任意长度的字符，甚至包括零字符 ####

【例 7.11】查找所有以‘b’字母开头的水果，SQL语句如下：

SELECT f_id,f_name FROM fruits WHERE f_name LIKE 'b%';

查询结果如下：  
　　![这里写图片描述][SouthEast 11]  
　　该语句查询的结果返回所有以‘b’开头的水果的id和name，‘%’告诉MySQL，返回所有以‘b’开头的记录，不管‘b’后面有多少个字符。  
　　在搜索匹配时通配符‘%’可以放在不同位置。如【例 7.12】。  
　　【例 7.12】在fruits表中，查询f\_name中包含字母‘g’的记录，SQL语句如下：

SELECT f_id,f_name FROM fruits WHERE f_name LIKE '%g%';

查询结果如下：  
![这里写图片描述][SouthEast 12]  
　　该语句查询字符串中包含字母‘g’的水果名称，只要名字中雨字符‘g’，而前面或后面不管有多少个字符，都满足查询的条件。  
　　【例 7.13】查询以‘b’开头，并以‘y’结尾的水果的名称，SQL语句如下：

SELECT f_name FROM fruits WHERE f_name LIKE 'b%y';

查询结果如下：  
![这里写图片描述][SouthEast 13]  
　　通过以上查询结果，可以看到，‘%’用于匹配在指定的位置的任意数目的字符。

#### 2. 下划线通配符‘\_’，一次只能匹配任意一个字符 ####

另一个非常有用的通配符是下划线‘*’，该通配符的用法和‘%’相同，区别是‘%’可以匹配多个字符，而‘*’只能匹配任意单个字符，如果要匹配多个字符，则需要使用相同个数的‘\_’。  
　　【例 7.14】在fruits表中，查询以字母‘y’结尾，且‘y’前面只有4个字母的记录，SQL语句如下：

SELECT f_id,f_name FROM fruits WHERE f_name LIKE '____y';

查询结果如下：  
　　![这里写图片描述][SouthEast 14]  
　　从结果可以看到，以‘y’结尾且前面只有4个字母的记录只有一条。其他记录的f\_name字段也有以‘y’结尾的，但其总的字符串长度不为5，因此不在返回结果中。

### 7.2.7 查询空值 ###

数据表创建的时候，设计者可以指定某列中是否可以包含空值（NULL）。空值不同于0，也不同于空字符串。空值一般表示数据未知、不适用或将在以后添加数据。在SELECT语句中使用IS NULL子句，可以查询某字段内容为空记录。  
　　【例 7.15】查询customers表中c\_email为空的记录的c\_id、c\_name和c\_email字段值，SQL语句如下：

SELECT c_id,c_name,c_mail FROM customers WHERE c_mail IS NULL;

查询结果如下：  
　　![这里写图片描述][SouthEast 15]  
　　可以看到，显示customers表中字段c\_email的值为NULL的记录，满足查询条件。  
　　与IS NULL相反的是NOT IS NULL，该关键字查找字段不为空的记录。  
　　【例 7.16】查询customers表中c\_email不为空的记录的c\_id、c\_name和c\_email字段值，SQL语句如下：

SELECT c_id,c_name,c_mail FROM customers WHERE c_mail IS NOT NULL;

查询结果如下：  
　![这里写图片描述][SouthEast 16]  
　　可以看到，查询出来的记录的c\_email字段都不为空值。

### 7.2.8 带AND的多条件查询 ###

使用SELECT查询时，可以增加查询的限定条件，这样可以使查询的结果更加精确。MySQL在WHERE子句中使用AND操作符限定只有满足所有查询条件的记录才会被返回。可以使用AND连接两个甚至多个查询条件，多个条件表达式之间用AND分开。  
　　【例 7.17】在fruits表中查询s\_id = 101，并且f\_price大于等于5的水果价格和名称，SQL语句如下：

SELECT f_id,f_price,f_name FROM fruits WHERE s_id = 101 AND f_price >= 5;

查询结果如下：  
　　![这里写图片描述][SouthEast 17]  
　　前面的语句检索了s\_id=101的水果供应商所有价格大于等于5的水果名称和价格。WHERE子句中的条件分为两部分，AND 关键字指示MySQL返回所有同时满足两个条件的行。即使是id=101的水果供应商提供的水果，如果价格<5，或者是id不等101的水果供应商里的水果不管其价格为多少，均不是要查询的结果。

> 提示：上述例子的WHERE子句中只包含了一个AND语句，把两个过滤条件组合在一起，实际上可以添加多个AND过滤条件，增加条件的同时增加一个AND关键字。

【例 7.18】在fruits表中查询s\_id = 101或者102，且f\_price大于5，并且f\_name=’apple’的水果价格和名称，SQL语句如下：

SELECT f_id,f_price,f_name FROM fruits WHERE s_id IN (101,102) AND f_price >= 5 AND f_name='apple';

查询结果如下：  
　　![这里写图片描述][SouthEast 18]  
　　可以看到，符合查询条件的返回记录只有一条。

### 7.2.9 带OR的多条件查询 ###

与AND相反，在WHERE声明中使用OR操作符，表示只需要满足其中一个条件的记录即可返回。OR也可以连接两个甚至多个查询条件，多个条件表达式之间用OR分开。  
　　【例 7.19】查询s\_id = 101或者s\_id = 102 的水果供应商的f\_price和f\_name，SQL语句如下：

SELECT s_id,f_name,f_price FROM fruits WHERE s_id=101 OR s_id=102;

查询结果如下：  
　　![这里写图片描述][SouthEast 19]  
　　结果显示了s\_id=101和s\_id=102的商店里的水果名称和价格，OR操作符告诉MySQL，检索的时候只需要满足其中一个条件，不需要全部都满足。如果这里使用AND的话，将检索不到符合条件的数据。  
　　在这里，也可以使用IN操作符实现与OR相同的功能，下面的例子可进行说明。  
　　【例 7.20】查询s\_id=101或者s\_id=102的水果供应商的f\_price和f\_name，SQL语句如下：

SELECT s_id,f_name,f_price FROM fruits WHERE s_id IN (101,102);

查询结果如下：  
　　![这里写图片描述][SouthEast 20]  
　　在这里可以看到，OR操作符合IN操作符使用后的结果是一样的，它们可以实现相同的功能。但是使用IN操作符使得检索语句更加的简洁明了，并且IN执行的速度要快于OR。更重要的是，使用IN操作符，可以执行更加复杂的嵌套查询（后面章节将会讲述）

> 提示：OR可以和AND一起使用，但是在使用时要注意两者的优先级，由于AND的优先级高于OR，因此先对AND两边的操作数进行操作，再与OR中的操作数结合。

### 7.2.10 查询结果不重复 ###

从前面的例子可以看到，SELECT查询返回所有匹配的行。例如，查询fruits表中所有的s\_id，其结果为：  
　　![这里写图片描述][SouthEast 21]  
　　可以看到结果返回了16条记录，其中有一些重复的s\_id值，有时，出于对数据分析的要求，需要消除重复的记录 ，如何使查询结果没有重复呢？在SELECT语句中，可以使用DISTINCT关键字指示MySQL消除重复的记录值。语法格式为：

SELECT DISTINCT 字段名 FROM 表名;

【例 7.21】查询fruits表中s\_id字段的值，返回s\_id字段值且不得重复，SQL语句如下：

SELECT DISTINCT s_id FROM fruits;

查询结果如下：  
　　![这里写图片描述][SouthEast 22]  
　　可以看到，这次查询结果只返回了7条记录的s\_id值，且不再有重复的值，SELECT DISTINCT s\_id 告诉MySQL只返回不同的s\_id行。

### 7.2.11 对查询结果排序 ###

从前面的查询结果，读者会发现有些字段的值是没有任何顺序的，MySQL可以通过在SELECT语句中使用ORDER BY子句，对查询的结果进行排序。

#### 1. 单列排序 ####

例如查询f\_name字段，查询结果如下：  
　　![这里写图片描述][SouthEast 23]  
　　可以看到，查询的结果并没有以一种特定的顺序显示，如果没有对它们进行排序，它们将根据插入到数据表中的顺序来显示。  
　　下面使用ORDER BY 子句对指定的列数据进行排序。  
　　【例 7.22】查询fruits表的f\_name字段值，并对其进行排序，SQL语句如下：

SELECT f_name FROM fruits ORDER BY f_name;

结果如下所示：  
　　![这里写图片描述][SouthEast 24]  
　　该查询语句的结果和前面的语句相同，不同的是，通过指定ORDER BY子句，MySQL对查询的name列的数据，按字母表的顺序进行了升序排序。

#### 2. 多列排序 ####

有时，需要根据多列值进行排序。比如，如果要显示一个学生列表，可能会有多个学生的姓氏是相同的，因此还需要根据学生的名进行排序。对多列数据进行排序，须将需要排序的列之间用逗号隔开。  
　　【例 7.23】查询fruits表中的f\_name和f\_price字段，先按f\_name排序，再按f\_price排序，SQL语句如下：

SELECT f_name,f_price FROM fruits ORDER BY f_name,f_price;

查询结果如下：  
![这里写图片描述][SouthEast 25]

> 提示：在对多列进行排序的时候，首先排序的第一列必须有相同的列值，才会对第二列进行排序。如果第一列数据中所有值都是唯一的，将不再对第二列进行排序。

#### 3.指定排序方向 ####

默认情况下，查询数据按字母升序排序（从A~Z），但数据的排序并不仅限于此，还可以使用ORDER BY 对查询结果进行降序排序（从Z~A），这可以通过关键字DESC实现，下面的例子表明了如何进行降序排列。  
　　【例 7.24】查询fruits表中的f\_name和f\_price字段，对结果按f\_price降序方式排序，SQL语句如下：

SELECT f_name,f_price FROM fruits ORDER BY f_price DESC;

查询结果如下：  
![这里写图片描述][SouthEast 26]

> 提示：与DESC相反的是ASC（升序排列），将字段列中的数据，按字母顺序升序排序。实际上，在排序的时候ASC是作为默认的排序方式，所以加不加都可以。

也可以对多列进行不同的顺序排序，如【例 7.25】所示。  
　　【例 7.250】查询fruits表，先按f\_price降序排序，再按f\_name字段升序排序，SQL语句如下：

SELECT f_name,f_price FROM fruits ORDER BY f_price DESC,f_name;

查询结果如下：  
![这里写图片描述][SouthEast 27]

DESC排序方式只应用到直接位于其前面的字段上，由结果可以看出。

> 提示：DESC关键字只对其前面的列进行降序排序，在这里只对f\_price排序，而并没有对f\_name进行排序，因此，f\_price按降序排序，而f\_name列扔按升序排序，如果要对多列都进行降序排序，必须要在每一列的列名后面加DESC关键字。

### 7.2.12 分组查询 ###

分组查询是对数据按照某个或多个字段进行分组，MySQL中使用GROUP BY 关键字对数据进行分组，基本语法形式为：

[GROUP BY 字段][HAVING <条件表达式>]

字段值为进行分组时所依据的列的名称；“HAVING <条件表达式>”指定满足表达式限定条件的结果将被显示。

#### 1. 创建分组 ####

GROUP BY 关键字通常和集合函数一起使用，例如：MAX()、MIN()、COUNT()、SUM()、AVG()。例如，要返回每个水果供应商提供的水果种类，这时就要在分组过程中用到COUNT()函数，把数据分为多个逻辑组，并对每个组进行集合计算。  
　　【例 7.26】根据s\_id对fruits表中的数据进行分组，SQL语句如下：

SELECT s_id,COUNT(*) AS Total FROM fruits GROUP BY s_id;

查询结果如下：  
![这里写图片描述][SouthEast 28]

查询结果显示，s\_id表示供应商的ID，Total字段使用COUNT()函数计算得出，子句按照s\_id排序并对数据分组，可以看到ID为101、102、105的供应商分别提供3中水果，ID为104、107的供应商分别提供2中水果，ID为103、106的供应商只提供1种水果。  
　　如果要查每个供应商提供的水果的种类的名称，该怎么办呢？MySQL中可以在GROUP BY字节中使用GROUP\_CONCAT()函数，将每个分组中各个字段的值显示出来。  
　　【例 7.27】根据s\_id对fruits表中的数据进行分组，将每个供应商的水果名称显示出来，SQL语句如下：

SELECT s_id,GROUP_CONCAT(f_name) AS Names FROM fruits GROUP BY s_id;

查询结果如下：  
　　![这里写图片描述][SouthEast 29]  
　　由结果可以看到，GROUP\_CONCAT()函数将每个分组中的名称显示出来了，其名称的个数与COUNT()函数计算出来的相同。

#### 2. 使用HAVING过滤分组 ####

GROUP BY可以和HAVING一起限定显示记录所需满足的条件，只有满足条件的分组才会被显示。  
　　【例 7.28】根据s\_id对fruits表中的数据进行分组，并显示水果种类大于1的分组信息，SQL语句如下：

SELECT s_id,GROUP_CONCAT(f_name) AS Names FROM fruits GROUP BY s_id HAVING COUNT(f_name)>1;

查询结果如下：  
![这里写图片描述][SouthEast 30]  
　　由结果可以看到，ID为101、102、103、104、105、107的供应商提供的水果种类大于1，满足HAVING子句条件，因此出现在返回结果中；而ID为106的供应商的水果种类等于1，不满足限定条件，因此不在返回结果中。

> 提示：HAVING关键字与WHERE关键字都是用来过滤数据，两者有什么区别呢？其中重要的一点是，HAVING在数据分组之后进行过滤来选择分组，而WHERE在分组之前用来选择记录。另外WHERE排除的记录不再包括在分组中。

#### 3. 在GROUP BY 子句中使用WITH ROLLUP ####

使用WITH ROLLUP关键字之后，在所有查询出的分组记录之后增加一条记录，该记录计算查询出的所有记录的总和，即统计记录数量。  
　　【例 7.29】根据s\_id对fruits表中的数据进行分组，并显示记录数量，SQL语句如下：

SELECT s_id,COUNT(*) AS Total FROM fruits GROUP BY s_id WITH ROLLUP;

查询结果如下：  
![这里写图片描述][SouthEast 31]  
　　由结果可以看到，通过GROUP BY分组之后，在显示结果的最后面新添加了一行，该行Total列的值正好是上面所有数值之和。

#### 4. 多字段分组 ####

使用GROUP BY可以对多个字段进行分组，GROUP BY关键字后面跟需要分组的字段，MySQL根据多字段的值来进行层次分组，分组层次从左到右，即先按第1个字段分组，然后在第2个字段值相同的记录中，再根据第2个字段的值进行分组…依此类推。  
　　【例 7.30】根据s\_id和f\_name字段对fruits表中的数据进行分组，SQL语句如下：

SELECT s_id,f_name FROM fruits GROUP BY s_id,f_name;

查询结果如下：  
![这里写图片描述][SouthEast 32]

由结果可以看到，查询记录先按照s\_id进行分组，在对f\_name字段按不同的取值进行分组。

#### 5. GROUP BY 和 ORDER BY 一起使用 ####

某些情况下需要对分组进行排序，在前面的介绍中，ORDER BY 用来对查询的记录排序，如果和GROUP BY 一起使用可以完成对分组的排序。先看下面的【例 7.31】。  
　　【例 7.31】查询订单价格大于100的订单号和总订单价格，SQL语句如下：

SELECT o_num, SUM(quantity * item_price) AS orderTotal FROM orderitems GROUP BY o_num HAVING SUM(quantity * item_price) >= 100;

查询结果如下：  
![这里写图片描述][SouthEast 33]  
　　可以看到，返回的结果中orderTotal列的总订单价格并没有按照一定顺序显示，接下来，使用ORDER BY关键字按总订单价格排序显示结果，SQL语句如下：

SELECT o_num, SUM(quantity * item_price) AS orderTotal FROM orderitems GROUP BY o_num HAVING SUM(quantity * item_price) >= 100 ORDER BY orderTotal;

查询结果如下：  
![这里写图片描述][SouthEast 34]  
　　由结果可以看到，GROUP BY子句按订单号对数据进行分组，SUM()函数便可以返回总的订单价格，HAVING子句对分组进行过滤，使得只返回总价格大于100的订单，最后使用ORDER BY子句排序输出。

> 提示：当使用ROLLUP时，不能同时使用ORDER BY子句进行排序，即ROLLUP和ORDER BY是相互排斥的。

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

SELECT返回所有匹配的行，有可能是表中所有的行，如仅仅需要返回第一行或前几行，使用LIMIT关键字，基本语法格式如下：

LIMIT [位置偏移量,]行数

第一个“位置偏移量”参数指示MySQL从哪一行开始显示，是一个可选参数，如果不指定“位置偏移量”，将会从表中的第一条记录开始（第一条记录的位置偏移量是0，第二条记录的位置偏移量是1…依此类推）；第二个参数“行数”指示返回的记录条数。  
　　【例 7.32】显示fruits表查询结果的前4行，SQL语句如下：

SELECT * FROM fruits LIMIT 4;

查询结果如下：  
![这里写图片描述][SouthEast 35]  
　　由结果可以看到，该语句没有指定返回记录的“位置偏移量”参数，显示结果从第一行开始，“行数”参数为4，因此返回的结果为表中的前4行记录。  
　　如果指定返回记录的开始位置，则返回结果为从“位置偏移量”参数开始的指定行数，“行数”参数指定返回的记录条数。  
　　【例 7.33】在fruits表中，使用LIMIT子句，返回从第5个记录开始的，行数长度为3的记录，SQL语句如下：

SELECT * FROM fruits LIMIT 4,3;

查询结果如下：  
![这里写图片描述][SouthEast 36]  
　　由结果可以看到，该语句指示MySQL返回从第5条记录行开始之后的3条记录。第一个数字‘4’表示从第5行开始（位置偏移量从0开始，第5行的位置偏移量为4），第二个数字3表示返回的行数。  
　　所以，带一个参数的LIMIT指定从查询结果的首行开始，唯一的参数表示返回的行数，即“LIMIT n”与“LIMIT 0,n”等价。带两个参数的LIMIT可以返回从任何一个位置开始的指定的行数。  
　　返回第一行时，位置偏移量是0。因此，“LIMIT 1,1”将返回第二行，而不是第一行。

> 提示：MySQL 5.5中可以使用“LIMIT 4 OFFSET 3”,意思是获取从第5条记录开始后面的3条记录，和“LIMIT 4,3” 返回的结果相同。

## 7.3 使用集合函数查询 ##

有时候并不需要返回实际表中的数据，而只是对数据进行总结。MySQL提供一些查询功能，可以对获取的数据进行分析和报告。这些函数的功能有：计算数据表中记录行数的总数；计算某个字段列下数据的综合；以及计算表中某个字段下的最大值、最小值或者平均值。本节将介绍这些函数以及如何使用它们。这些聚合函数的名称和作用如表7.2所示。  
　　表7.2 MySQL聚合函数

<table> 
 <thead> 
  <tr> 
   <th>函数</th> 
   <th>作用</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>AVG()</td> 
   <td>返回某列的平均值</td> 
  </tr> 
  <tr> 
   <td>COUNT()</td> 
   <td>返回某列的行数</td> 
  </tr> 
  <tr> 
   <td>MAX()</td> 
   <td>返回某列的最大值</td> 
  </tr> 
  <tr> 
   <td>MIN()</td> 
   <td>返回某列的最小值</td> 
  </tr> 
  <tr> 
   <td>SUM()</td> 
   <td>返回某列的和</td> 
  </tr> 
 </tbody> 
</table>

接下来，将详细介绍各个函数的使用方法。

### 7.3.1 COUNT()函数 ###

COUNT()函数统计数据表包含的记录行的总数，或者根据查询结果返回列中包含的数据行数。其使用方法有两种：

*  COUNT(\*)计算表中总的行数，不管某列有数值或者为空值。
 *  COUNT(字段名)计算指定列下总的行数，计算时将忽略空值的行。
    
    　　【例 7.34】查询customers表中总的行数，计算时将忽略空值的行。

SELECT COUNT(*) AS cust_num FROM customers;

![这里写图片描述][SouthEast 37]

由查询结果可以看到，COUNT(\*)返回customers表中记录的总行数，不管其值是什么。返回的总数的名称为cust\_num。  
　　【例 7.35】查询customers表中有电子邮箱的顾客的总数，SQL语句如下：

SELECT COUNT(c_mail) AS email_num FROM customers;

![这里写图片描述][SouthEast 38]

由查询结果可以看到，表中５个customer只有3个有email，customer的email为空值NULL的记录没有被COUNT()函数计算。

> 提示：两个例子中不同的数值，说明了两种方式在计算总数的时候对待NULL值的方式不同。即指定列的值为空的行被COUNT()函数忽略，但是如果不指定列，而在COUNT()函数中使用星号“\*”，则所有记录都不忽略。

前面介绍分组查询的时候，介绍了COUNT()函数与GROUP BY关键字一起使用，用来计算不同分组中的记录总数。  
　　【例 7.36】在orderitems表中，使用COUNT()函数统计不同订单号中订购的水果种类，SQL语句如下：

[SouthEast]: /images/20220614/d6dcd841901b48c68bfb893f8797e0e0.png
[SouthEast 1]: /images/20220614/8cfa709e0de648118a70cf5a08e7de9d.png
[SouthEast 2]: /images/20220614/ed5e705507d54958a88cae8162aad122.png
[SouthEast 3]: /images/20220614/8439ed3e05e94b4caadb55ed5ee3139d.png
[SouthEast 4]: /images/20220614/be224ccc4763486389e270c638c11680.png
[SouthEast 5]: /images/20220614/9f61efcb8828495cad07667f0091bec1.png
[SouthEast 6]: /images/20220614/6ca671d98b86414aa55da82fe3b583d4.png
[SouthEast 7]: /images/20220614/3b385f9a7ff94f8e95423997eb893265.png
[SouthEast 8]: /images/20220614/e09710c438a84370a1d9ceb73e3942ac.png
[SouthEast 9]: /images/20220614/9d3b5e3d100641bb842a3a743d87abe9.png
[SouthEast 10]: /images/20220614/f2a181d175c74370a8acadccc228e610.png
[SouthEast 11]: /images/20220614/f9b75faba24c4d1c9ffb3fcb6e8e73ce.png
[SouthEast 12]: /images/20220614/bdfbe35af2644fe8bc90b34116838a3e.png
[SouthEast 13]: /images/20220614/2a1b1fab043a4e83836c6e129ac0c398.png
[SouthEast 14]: /images/20220614/6ea35fa40b4140a6b9a71b8eb337828c.png
[SouthEast 15]: /images/20220614/b38137e13d47423bbd78d513d160dd61.png
[SouthEast 16]: /images/20220614/276e5d7bd43c4d0896ef4ab2b0621287.png
[SouthEast 17]: /images/20220614/e374fcb9cbae4e85a800b1c81ef2b58d.png
[SouthEast 18]: /images/20220614/d71d18432b874b38898ba38dc2ee2622.png
[SouthEast 19]: /images/20220614/cddd94e4b2da4422bdd94dc0e48de3f3.png
[SouthEast 20]: /images/20220614/bafe3676a0384a4b9597cd81e5fca14b.png
[SouthEast 21]: /images/20220614/2ed20b8c10154632ade75329dafffdb3.png
[SouthEast 22]: /images/20220614/a20f6595268b40f1b6a4ba61ed53cd31.png
[SouthEast 23]: /images/20220614/6e075aba360042e49fb8931c16cca8d6.png
[SouthEast 24]: /images/20220614/8b54a7633c144e39ab0e31cadb26df52.png
[SouthEast 25]: /images/20220614/de9621eec3c948e9a6198580c5a0d4c0.png
[SouthEast 26]: /images/20220614/4d75f2a146454278aeb048886d670f7c.png
[SouthEast 27]: /images/20220614/781cb1d49b454f83b47691f4693d41c1.png
[SouthEast 28]: /images/20220614/19e2769088a04a47893d985e8e2f1c73.png
[SouthEast 29]: /images/20220614/6e5c5ae04d344e90b4622f81f0a66036.png
[SouthEast 30]: /images/20220614/736234a4b4ef44aaa7d659f59303ba78.png
[SouthEast 31]: /images/20220614/90ae82e4afd54cf09e5c739fa112c0bc.png
[SouthEast 32]: /images/20220614/08d172b2235c42a18563df619af3910a.png
[SouthEast 33]: /images/20220614/71551a00379749e1b45b691b684445b7.png
[SouthEast 34]: /images/20220614/3307e04f43424d11aee510564ced4301.png
[SouthEast 35]: /images/20220614/1efa0a5583ff4e53ad8562d2147bbf1d.png
[SouthEast 36]: /images/20220614/2ac51e5411424ba9b9f87ca2bf494256.png
[SouthEast 37]: /images/20220614/8ce8e8ab288e47bd9779581b6ce0acdf.png
[SouthEast 38]: /images/20220614/5bd7387dc1104155a12a5cddd967dd7c.png