16.4查询数据

16.4　查询数据

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 [<offset>,] <row count>]：该子句告诉MySQL每次显示查询出来的数据条数。

•SELECT的可选参数比较多。读者可能无法一下子完全理解，不要紧，接下来从最简单的开始，一步一步深入学习之后，就会对各个参数的作用有清晰的认识。

下面创建数据表fruits，该表中包含了本章中需要用到的数据。

首先定义数据表。

　　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语句，需要插入数据。请读者插入如下数据：

　　mysql> INSERT INTO fruits (f_id, s_id, f_name, f_price)
　　    -> 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),
　　    -> ('o2',103,'coconut', 9.2),
　　    -> ('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和f_name字段的数据。

该语句的执行过程是，SELECT语句决定了要查询的列值，在这里查询f_id和f_name两个字段的值，FROM子句指定了数据的来源，这里指定数据表fruits，因此返回结果为fruits表中f_id和f_name这两个字段下所有的数据。其显示顺序为添加到表中的顺序。

16.4.1　查询所有字段

1．在SELECT语句中使用星号“*”通配符查询所有字段

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

　　SELECT * FROM 表名;

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

可以看到，使用星号通配符时，将返回所有列，列按照定义表的时候的顺序显示。

2．在SELECT语句中指定所有字段

另外一种查询所有字段值的方法，根据前面SELECT语句格式，SELECT关键字后面字段名为将要查找的数据。如果忘记了字段名称，可以使用DESC命令查看表的结构。有时候，可能表中的字段比较多，不一定能记得所有字段的名称，因此该方法有时候很不方便，不建议使用。例如查询fruits表中的所有数据，SQL语句也可以书写如下：

　　SELECT f_id, s_id ,f_name, f_price FROM fruits;

查询结果与例16.14相同。

提　示

16.4.2　查询指定字段

1．查询单个字段

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

　　SELECT 列名FROM 表名;

【例16.14】查询当前表中f_name列所有水果名称，SQL语句如下：

　　SELECT f_name FROM fruits;

该语句使用SELECT声明从fruits表中获取名称为f_name字段下的所有水果名称，指定字段的名称紧跟在SELECT关键字之后，查询结果如下：

　　mysql> SELECT f_name FROM fruits;
　　+------------+
　　| f_name      |
　　+------------+
　　| apple       |
　　| apricot     |
　　| blackberry |
　　| berry       |
　　| xxxx        |
　　| orange      |
　　| melon       |
　　| cherry      |
　　| lemon       |
　　| mango       |
　　| xbabay      |
　　| xxtt        |
　　| coconut     |
　　| banana      |
　　| grape       |
　　| xbababa     |
　　+------------+

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

2．查询多个字段

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

　　SELECT 字段名1,字段名2,…,字段名n FROM 表名;

【例16.15】从fruits表中获取f_name和f_price两列，SQL语句如下：

　　SELECT f_name, f_price FROM fruits;

该语句使用SELECT声明从fruits表中获取名称为f_name和f_price两个字段下的所有水果名称和价格，两个字段之间用逗号分隔开，查询结果如下：

输出结果显示了fruits表中f_name和f_price两个字段下的所有数据。

提　示

16.4.3　查询指定记录

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

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

在WHERE子句中，MySQL提供了一系列的条件判断符，如表16-1所示。

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

　　SELECT f_name, f_price
　　FROM fruits
　　WHERE f_price = 10.2;

该语句使用SELECT声明从fruits表中获取价格等于10.2的水果的数据，从查询结果可以看到价格是10.2元的水果的名称blackberry，其他的均不满足查询条件，查询结果如下：

本例采用了简单的相等过滤，查询一个指定列f_price具有值10.20。

相等还可以用来比较字符串。

【例16.17】查找名称为apple的水果的价格，SQL语句如下：

　　SELECT f_name, f_price
　　FROM fruits
　　WHERE f_name = 'apple';

该语句使用SELECT声明从fruits表中获取名称为apple的水果的价格，从查询结果可以看到只有名称为apple行被返回，其他的均不满足查询条件。

【例16.18】查询价格小于10.00元的水果的名称，SQL语句如下：

　　SELECT f_name, f_price
　　FROM fruits
　　WHERE f_price < 10.00;

该语句使用SELECT声明从fruits表中获取价格低于10.00元的水果名称，即f_price小于10.00的水果信息被返回，查询结果如下：

可以看到查询结果中所有记录的f_price字段的值均小于10.00元。而大于10.00元的记录没有被返回。

16.4.4　带IN关键字的查询

IN操作符用来查询满足指定条件范围内的记录。使用IN操作符时，将所有检索条件用括号括起来，检索条件用逗号分隔开，只要满足条件范围内的一个值即为匹配项。

【例16.19】查询s_id为101和102的记录，SQL语句如下：

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

查询结果如下：

相反地，可以是用关键字NOT来检索不在条件范围内的记录。

【例16.20】查询所有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;

查询结果如下：

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

16.4.5　带BETWEEN AND的范围查询

BETWEEN AND用来查询某个范围内的值，该操作符需要两个参数，即范围的开始值和结束值，如果记录的字段值满足指定的范围查询条件，则这些记录被返回。

【例16.21】查询价格在2.00元到10.5元之间水果名称和价格，SQL语句如下：

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

查询结果如下：

可以看到，返回结果包含了价格从2.00元到10.20元之间的字段值，并且端点值10.20也包括在返回结果中，即BETWEEN匹配范围中所有值，包括开始值和结束值。

BETWEEN AND操作符前可以加关键字NOT，表示指定范围之外的值，如果字段值不满足指定的范围内的值，则这些记录被返回。

【例16.22】查询价格在2.00元到10.5元之外的水果名称和价格，SQL语句如下：

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

查询结果如下：

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

16.4.6　带LIKE的字符匹配查询

在前面的检索操作中，讲述了如何查询多个字段的记录，如何进行比较查询或者查询一个条件范围内的记录，如果要查找所有的包含字符“ge”的水果名称，该如何查找呢？简单的比较操作在这里已经行不通了，在这里，需要使用通配符进行匹配查找，通过创建查找模式对表中的数据进行比较。执行这个任务的关键字是LIKE。

通配符是一种在SQL的WHERE条件子句中拥有特殊意思的字符，SQL语句中支持多种通配符，可以和LIKE一起使用的通配符有‘%’和‘_’。

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

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

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

查询结果如下：

该语句查询的结果返回所有以‘b’开头的水果的id和name，‘%’告诉MySQL，返回所有f_name字段以字母‘g’开头的记录，不管‘g’后面有多少个字符。

在搜索匹配时通配符‘%’可以放在不同位置。

【例16.24】在fruits表中，查询f_name中包含字母‘g’的记录，SQL语句如下：

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

查询结果如下：

该语句查询包含字符串中包含字母‘g’的水果名称，只要名字中有字符‘g’，而前面或后面不管有多少个字符，都满足查询的条件。

【例16.25】查询以‘b’开头，并以‘y’结尾的水果的名称，SQL语句如下：

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

查询结果如下：

　　+------------+
　　| f_name      |
　　+------------+
　　| blackberry |
　　| berry       |
　　+------------+

通过以上查询结果，可以看到，‘%’用于匹配在指定的位置的任意数目的字符。

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

另一个非常有用的通配符是下划线通配符‘_’，该通配符的用法和‘%’相同，区别是‘%’匹配多个字符，而‘_’只匹配任意单个字符，如果要匹配多个字符，则需要使用相同个数的‘_’。

【例16.26】在fruits表中，查询以字母‘y’结尾，且‘y’前面只有4个字母的记录，SQL语句如下：

　　SELECT f_id, f_name FROM fruits WHERE f_name LIKE '_ _ _ _y';

查询结果如下：

从结果可以看到，以‘y’结尾且前面只有4个字母的记录只有一条。其他记录的f_name字段也有以‘y’结尾的，但其总的字符串长度不为5，因此不在返回结果中。

16.4.7　查询空值

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

【例16.27】查询customers表中c_email为空的记录的c_id、c_name和c_email字段值，SQL语句如下：

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

查询结果如下：

可以看到，显示customers表中字段c_email的值为NULL的记录，满足查询条件。

与IS NULL相反的是NOT IS NULL，该关键字查找字段不为空的记录。

【例16.28】查询customers表中c_email不为空的记录的c_id、c_name和c_email字段值，SQL语句如下：

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

查询结果如下：

可以看到，查询出来的记录的c_email字段都不为空值。

16.4.8　带AND的多条件查询

使用SELECT查询时，可以增加查询的限制条件，这样可以使查询的结果更加精确。MySQL在WHERE子句中使用AND操作符限定只有满足所有查询条件的记录才会被返回。可以使用AND连接两个甚至多个查询条件，多个条件表达式之间用AND分开。

【例16.29】在fruits表中查询s_id = 101，并且f_price大于5的记录价格和名称，SQL语句如下：

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

查询结果如下：

前面的语句检索了s_id=101的水果供应商所有价格大于等于5元的水果名称和价格。WHERE子句中的条件分为两部分，AND关键字指示MySQL返回所有同时满足两个条件的行。即使是s_id=101的水果供应商提供的水果，价格<5或者s_id不等于‘101’的水果供应商的水果不管其价格为多少，均不是要查询的结果。

提　示

【例16.30】在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';

查询结果如下：

可以看到返回符合查询条件的记录只有一条。

16.4.9　带OR的多条件查询

与AND相反，在WHERE声明中使用OR操作符，表示只需要满足其中一个条件的记录即可返回。OR也可以连接两个甚至多个查询条件，多个条件表达式之间用OR分开。

【例16.31】查询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;

查询结果如下：

结果显示查询了s_id=101和s_id=102的供应商提供的水果名称和价格，OR操作符告诉MySQL，检索的时候只需要满足其中的一个条件，不需要全部都满足，如果这里使用AND的话，将检索不到符合条件的数据。

在这里，也可以使用IN操作符实现与OR相同的功能，下面的例子可进行说明。

【例16.32】查询s_id=101或者s_id=102的水果供应商的f_name和f_price，SQL语句如下：

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

查询结果如下：

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

提　示

16.4.10　查询结果不重复

从前面的例子可以看到，SELECT查询返回所有匹配的行。例如查询fruits表中所有的s_id，其结果为：

　　+------+
　　| s_id |
　　+------+
　　|  101 |
　　|  103 |
　　|  101 |
　　|  104 |
　　|  107 |
　　|  102 |
　　|  105 |
　　|  101 |
　　|  104 |
　　|  106 |
　　|  105 |
　　|  105 |
　　|  103 |
　　|  102 |
　　|  102 |
　　|  107 |
　　+------+

可以看到查询结果返回了16条记录，其中有一些重复的s_id值，有时，出于对数据分析的要求，需要消除重复的记录值，如何使查询结果没有重复呢？在SELECT语句中可以使用DISTINCT关键字指示MySQL消除重复的记录值。语法格式为：

　　SELECT DISTINCT 字段名 FROM 表名;

【例16.33】查询fruits表中s_id字段的值，并返回s_id字段值不得重复，SQL语句如下：

　　SELECT DISTINCT s_id FROM fruits;

查询结果如下：

　　mysql> SELECT DISTINCT s_id FROM fruits;
　　+------+
　　| s_id |
　　+------+
　　|  101 |
　　|  103 |
　　|  104 |
　　|  107 |
　　|  102 |
　　|  105 |
　　|  106 |
　　+------+

可以看到这次查询结果只返回了7条记录的s_id值，而不再有重复的值，SELECT DISTINCE s_id告诉MySQL只返回不同的s_id值。

16.4.11　对查询结果排序

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

1．单列排序

例如，查询f_name字段，查询结果如下：

　　mysql> SELECT f_name FROM fruits;
　　+------------+
　　| f_name      |
　　+------------+
　　| apple       |
　　| apricot     |
　　| blackberry |
　　| berry       |
　　| xxxx         |
　　| orange      |
　　| melon       |
　　| cherry      |
　　| lemon       |
　　| mango       |
　　| xbabay      |
　　| xxtt        |
　　| coconut     |
　　| banana      |
　　| grape       |
　　| xbababa     |
　　+------------+

可以看到，查询的数据并没有以一种特定的顺序显示，如果没有对它们进行排序，将根据它们插入到数据表中的顺序来显示。

下面使用ORDER BY子句对指定的列数据进行排序。

【例16.34】查询fruits表的f_name字段值，并对其进行排序，SQL语句如下：

　　mysql> SELECT f_name FROM fruits ORDER BY f_name;
　　+------------+
　　| f_name      |
　　+------------+
　　| apple       |
　　| apricot     |
　　| banana      |
　　| berry       |
　　| blackberry |
　　| cherry      |
　　| coconut     |
　　| grape       |
　　| lemon       |
　　| mango       |
　　| melon       |
　　| orange      |
　　| xbababa     |
　　| xbabay      |
　　| xxtt         |
　　| xxxx        |
　　+------------+

该语句查询的结果和前面的语句相同，不同的是，通过指定ORDER BY子句，MySQL对查询的f_name列的数据按字母表的顺序进行了升序排序。

2．多列排序

有时，需要根据多列值进行排序。例如，如果要显示一个学生列表，可能会有多个学生的姓氏是相同的，因此还需要根据学生的名进行排序。对多列数据进行排序，只要将需要排序的列之间用逗号隔开。

【例16.35】查询fruits表中的f_name和f_price字段，先按f_name排序，再按f_price排序，SQL语句如下：

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

查询结果如下：

提　示

3．指定排序方向

默认情况下，查询数据按字母升序进行排序（从A到Z），但数据的排序并不仅限于此，还可以使用ORDER BY对查询结果进行降序排序（从Z到A），这通过关键字DESC实现，下面的例子表明了如何降序排列。

【例16.36】查询fruits表中的f_name和f_price字段，对结果按f_price降序方式排序，SQL语句如下：

　　SELECT f_name, f_price FROM fruits ORDER BY f_price DESC;

查询结果如下：

提　示

也可以对多列进行不同的顺序排序。

【例16.37】查询fruits表，先按f_price降序排序，再按f_name字段升序排序，SQL语句如下：

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

查询结果如下：

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

提　示