二级MySQL数据库程序设计（五）_mysql code二级表设计

本博客为《全国计算机等级考试二级MySQL数据库程序设计教程》读书笔记，请勿转载用于其他商业用途。

课程目录
第1章 数据库的基本概念与设计方法
第2章 MySQL简介
第3章 数据库和表
第4章 表数据的基本操作
第5章 数据库的查询
第6章 索引
第7章 视图
第8章 数据完整性约束与表维护语句
第9章 触发器
第10章 事件
第11章 存储过程与存储函数
第12章 访问控制与安全管理
第13章 备份与恢复
第14章 PHP和MySQL数据库编程
第15章 开发实例

本章学习流程图

本章学习大纲

在MySQL中，查询操作时使用SELECT语句实现的。SELECT语句的数学理论基础是关系模型中对表对象的一组关系运算，即选择、投影和连接。

5.1 SELECT语句

使用SELECT语句可以从MySQL数据中方便快捷地检索、统计或者输出数据。该语句的执行过程是从数据库中选取匹配的特定行和列，并将这些数据组织成一个结果集，然后以一张临时表的形式返回。

语法格式：

SELECT <列名1,列名2...> FROM <表名> [WHERE子句] [GROUP子句] [HAVING子句] [ORDER BY子句] [LIMIT子句]
1

语法说明：

• <表名>：要进行查询的表的名称。
• <列名1，列名2…>：用于指定需要查询的字段名。
• FROM子句、WHERE子句、HAVING子句、ORDER BY子句、LIMIT子句：均为可选项。这些子句必须按照SELECT语句的语法格式罗列的顺序来使用。例如，WHERE子句必须位于GROUP子句之前，GROUP子句必须位于HAVING子句之前。

5.2 列的选择和指定

由SELECT语句语法可知，最简单的SELECT语句形式是“SELECT select_expr”。使用这种SELECT语句可以进行MYSQL所支持的任何运算。例如，执行语句“SELECT 1+4-2”，系统会返回3。

【例5-1】在MySQL数据库中执行“SELECT 1+4-2”运算。

mysql> select 1+4-2;
+-------+
| 1+4-2 |
+-------+
|     3 |
+-------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

❶ 选择指定的列
数据查询时，可以选择一个或多个表的某个或某些列作为SELECT语句的查询列。如果有多个列，则各个列用逗号分隔开，且返回结果集时，各列的次序是按照SELECT语句中指定的次序给出。若要查询一个表中的所有列，则直接用（*）通配符即可，不必列出所有的列名。另外，列名的指定可以采用直接给出列名的方式，也可以使用完全限定的列名方式，即“tbl_name.col_name”的列名格式。

注意：使用（*）可以返回所有列的数值，但是如果不需要返回所有列值的时候，为了提高效率，一般采用SELECT字段名列表的形式。

【例5-2】查询数据库 my_test 中表 students 中学生的姓名、年龄和专业信息。

mysql> select student_name,student_age,student_major
    -> from my_test.students;
+--------------+-------------+---------------+
| student_name | student_age | student_major |
+--------------+-------------+---------------+
| 李明         |          22 | 数学专业      |
| 张三         |          24 | 化学专业      |
| 王五         |          23 | 数学专业      |
| 赵柳         |          23 | 物理专业      |
+--------------+-------------+---------------+
4 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11

语句执行后，会返回一个原始的，无格式的数据集。

【例5-3】查询数据库 my_test 中表 students 学生所有信息。

mysql> select * from students;
+------------+--------------+-------------+-------------+---------------+-----------------+
| student_id | student_name | student_sex | student_age | student_major | student_contact |
+------------+--------------+-------------+-------------+---------------+-----------------+
|       1321 | 李明         | 1           |          22 | 数学专业      | NULL            |
|       1322 | 张三         | 1           |          24 | 化学专业      | 139xxxxxxxx     |
|       1323 | 王五         | 1           |          23 | 数学专业      | NULL            |
|       1324 | 赵柳         | 1           |          23 | 物理专业      | NULL            |
+------------+--------------+-------------+-------------+---------------+-----------------+
4 rows in set (0.34 sec)
1
2
3
4
5
6
7
8
9
10

语句执行后，返回表students的所有记录。

❷ 定义并使用列的别名

在系统输出查询结果集中某些列或所有列名称时，如果希望这些列的名称显示为自定义列名，而非原表的列名，则可以在SELECT语句添加AS子句，用于修改查询结果集中的别名。

别名：数据库对象正式的或规范的名称以外的名称。

语法格式：

<列名>[AS]<列名>
1

【例5-4】查询数据库 my_test 中表 students 的 student_name 和 student_contact 字段，并且要求 student_contact 列用“联系方式”来显示。

mysql> select student_name,student_contact as '联系方式' from my_test.students;
+--------------+-------------+
| student_name | 联系方式    |
+--------------+-------------+
| 李明         | NULL        |
| 张三         | 139xxxxxxxx |
| 王五         | NULL        |
| 赵柳         | NULL        |
+--------------+-------------+
4 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11

如果要将 student_name 替换成 “姓名”，代码如下：

mysql> select student_name as '姓名',student_contact as '联系方式' from my_test.students;
+------+-------------+
| 姓名 | 联系方式    |
+------+-------------+
| 李明 | NULL        |
| 张三 | 139xxxxxxxx |
| 王五 | NULL        |
| 赵柳 | NULL        |
+------+-------------+
4 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10

注意：列别名不允许出现在WHERE子句中。

❸ 计算列值
使用SELECT语句对列进行查询时，可以对列进行计算，在结果集中输出计算结果。

【例5-5】查询数据库 my_test 中表 students，输出每个学生的 student_name 和 student_id 列，同时要求输出 student_id 加上数字100后构成新列的值。

mysql> select student_name,student_id,student_id+100 from my_test.students;
+--------------+------------+----------------+
| student_name | student_id | student_id+100 |
+--------------+------------+----------------+
| 李明         |       1321 |           1421 |
| 张三         |       1322 |           1422 |
| 王五         |       1323 |           1423 |
| 赵柳         |       1324 |           1424 |
+--------------+------------+----------------+
4 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10

新列 student_id+100 是列 student_id 的数值加100后的结果。

❹ 替换查询结果集中的数据
在对表进行查询时，如果希望得到某些列的分析结果，而不仅仅是查询到的原始值，则可以在SELECT语句中替换这些列。这里可以使用CASE表达式。

语法格式：

CASE
    WHEN 条件1 THEN 表达式1
    WHEN 条件2 THEN 表达式2
    ...
    ELSE 表达式 
END[AS<列表名>]
1
2
3
4
5
6

【例5-6】查询数据库 my_test 中表 students，输出 student_name 和 student_sex 列，判断 student_sex，如果为0，则显示“男”，否则显示“女”，并在结果集中用“性别”来标注该列。

mysql> select student_name,
    -> case
    -> when student_sex='0'then'男'
    -> else'女'
    -> end as'性别'
    -> from my_test.students;
+--------------+------+
| student_name | 性别 |
+--------------+------+
| 李明         | 女   |
| 张三         | 女   |
| 王五         | 女   |
| 赵柳         | 女   |
+--------------+------+
4 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

❺ 聚合函数
SELECT语句可以指定为聚合函数。聚合函数是MySQL系统内置函数。常用语对一组值进行计算，然后返回单个值。

聚合函数通常与GROUP BY子句一起使用，如果SELECT语句中有一个GROUP BY子句，则聚合函数对所有列起作用；如果没有，则SELECT语句只产生一行作为结果。注意，除COUNT( )函数外，聚合函数都会忽略空值。表5-1列出了MySQL中常用的聚合函数。

聚合函数：MySQL的内置函数，常常用于对一组值进行计算或者统计，然后返回计算或者统计结果。

表5-1 MySQL中常用的聚合函数

例如，查询表students，输出年龄最大值。
这是，在MySQL命令行客户端输入：select max (student_age) from students 即可。聚合函数通常与group by子句结合使用。

5.3 FROM子句与连接表

前面讲的内容都是SELECT语句查询列的选择与指定方法，下面讨论SELECT语句中查询对象（数据源）的构成形式。

❶ FROM子句
SELECT语句查询对象是由FROM子句指定的。

语法格式：

FROM <表名>
1

语法说明：
<表名>：指定要查询的表名。与列别名一样，可以使用AS关键字为表格指定别名。表别名主要用于相关子查询或者链接查询。如果在FROM子句中指定了表别名，那么它所在的SELECT语句中其他句子都必须使用表别名代替原始表名。其中，<表名>可以使用单表的名称，也可以使用联合表。当同一个表在SELECT语句中被多次引用时，必须使用表别名加以区分。

❷ 连接表
在关系数据库设计中，为了减少数据的冗余，并增强数据库的稳定性和灵活性，通常会基于关系规范化原则将物理世界中的数据信息分解成多个表，实现一类数据一个表，在表与表之间通过设置“键”（如主键、外键）的方式来保持多表之间的关联关系。
相反，在关系数据库存的应用中，可通过对表对象的连接（join）运算，实现数据库中数据信息的组合。这种分解与组合数据的方法，是的数据库系统能够更有效地存储数据，更方便地处理数据，获得更大的可伸缩性。
注意：连接查询的类型和具体使用方法，是考试重点内容。

（1）交叉连接
交叉连接又被称为笛卡尔积，返回连接表中所有数据行的笛卡尔积，其结果集合中的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。在FROM子句中使用关键字CROSS JOIN连接两张表，用于实现一张表的每一行与另一张表的每一行的笛卡尔积，返回两张表的每一行相乘的所有可能的搭配结果。

【例5-7】假设数据库中有2张表，分别是a和b，要求查询这2张表的交叉连接后的结果集。
首先查看a表中的数据：

mysql> select * from a;
+------+------+
| Id   | name |
+------+------+
|    1 | a1   |
|    2 | a2   |
+------+------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8

接着查看b表中的数据：

mysql> select * from b;
+------+------+
| Id   | name |
+------+------+
|    1 | b1   |
|    2 | b2   |
|    3 | b3   |
+------+------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9

计算a表和b表交叉连接的结果：

mysql> select * from a cross join b;
+------+------+------+------+
| Id   | name | Id   | name |
+------+------+------+------+
|    1 | a1   |    1 | b1   |
|    2 | a2   |    1 | b1   |
|    1 | a1   |    2 | b2   |
|    2 | a2   |    2 | b2   |
|    1 | a1   |    3 | b3   |
|    2 | a2   |    3 | b3   |
+------+------+------+------+
6 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12

从上面的结果可以看出，交叉连接返回的结果集的记录行数是两张表的记录行数相乘。若a表记录行数为100条，b表记录行数为200条，则a表和b表交叉连接返回的是100×200=20000条。因此倘若关联的两张表的记录数很多，交叉连接的结果集会非常庞大，所以**对于存在大量数据的表，应该避免使用交叉连接。**也可以通过添加WHERE子句设置判断条件，来过滤返回的结果集。

（2）内连接
内连接是通过在查询中设置条件的方式，来移除查询结果集中某些数据行后的交叉连接。简单来说，就是利用条件表达式来消除交叉连结的某些数据行，在FROM字句中使用关键字INNER JOIN连接两张表，并使用ON子句来设置连接条件。如果没有任何条件的话，INNER JOIN和CROSS JOIN在语法上是等同的，两者可以互换。

语法格式：

SELECT <列名1，列名2...> FROM <表名1> INNER JOIN <表名2> [on子句]
1

语法说明：

• <列名1，列名2…>：需要检索的列名。
• <表名1><表名2>：进行内连接的两张表表名。

内连接是系统默认的表连接，所以在FROM子句后可以省略INNER关键字，只用关键字JOIN。使用内连接后，FROM子句中的ON子句可用来设置连接表的条件。在FROM子句中可以在多个表之间连续使用INNER JOIN或JOIN，如此可以同时实现多个表的内连接。

【例5-8】在例5-7的表中，利用内连接查询出 a 表 Id 大于 b 表 Id 的结果集。

mysql> select * from a inner join b on a.Id>b.Id;
+------+------+------+------+
| Id   | name | Id   | name |
+------+------+------+------+
|    2 | a2   |    1 | b1   |
+------+------+------+------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

在内连接语句中使用ON关键字设置条件。

（3）相等连接
相等连接是内连接的一种，用于关联具有一对一关系的两张表，即进行相等性测试的内连接。它实质上是内连接的一种特殊情况，在FROM子句中用INNER JOIN或者JOIN关键字来进行连接，在ON子句的连接条件中使用运算符“=”，通常这样的条件会包含一个主键和一个外键。

【例5-9】在例5-7的表中，利用内连接查询出 a 表 Id 等于 b 表 Id 的结果集。

mysql> select * from a inner join b on a.Id=b.Id;
+------+------+------+------+
| Id   | name | Id   | name |
+------+------+------+------+
|    1 | a1   |    1 | b1   |
|    2 | a2   |    2 | b2   |
+------+------+------+------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8

（4）不等连接
不等连接也是内连接的一种，与相等连接相对应，即进行不相等性测试的内连接。也是用INNER JOIN或者JOIN关键字来连接两张表，只是在ON子句中的连接条件使用除“=”的其他运算符。

（5）自连接
自连接是将一个表和它自身进行连接。它也是内连接的一种，同样是使用INNER JOIN或者JOIN关键字来进行连接。如果需要在一个表中查找具有相同列值的行，则可以考虑用自连接。注意，在使用自连接的时候，需要为表指定两个不同的列名，且对所有查询列的引用必须使用表别名限定，否则SELECT操作会失败。

（6）自然连接
自然连接只有在连接的列在两张表中的名称都相同时才会有用，否则返回的是笛卡尔积。自然连接在FROM子句中使用关键字NATURAL JOIN。

（7）外连接
内连接是在交叉连接的记过集上返回满足条件的记录；而外连接首先将连接的表分为基表和参考表，然后再以基表为依据返回满足和不满足条件的记录。
外连接更加注重两张表之间的关系。按照连接表的顺序，可以分为左外连接和右外连接。

①左外连接
左外连接又称为左连接。它在FROM子句中使用关键字LEFT OUTER JOIN或者LEFT JOIN，用于接收该关键字左表（基表）的所有行，并用这些行与该关键字右表（参考表）中的行进行匹配，即匹配左表中的每一行及右表中符合条件的行。在左外连接的结果集中除了匹配的行之外，还包括左表中有但在右表中不匹配的行，对于这样的行，从右表中被选择的列的值被设置为NULL，即左边连接的结果集中在NULL值表示右表中没有找到与左表相符的记录。

②右外连接
右外连接也称为右连接。它在FROM子句中使用RIGHT OUTER ON或者RIGHT JOIN。与左外连接相反，右外连接是以右表为基表，连接方法和左外连接相同。在右外连接结果集中除了匹配的行，还包括由表中有但是左表中不匹配的行，这样的行，从左表被选择的值被设置为NULL。

5.4 WHERE子句

在SELECT语句中，可以使用WHERE子句来指定查询条件，从FROM子句的中间结果中选取适当的数据行，达到数据过滤的效果。

语法格式：

WHERE<查询条件>{<判定运算>1，<判定运算>2...}
1

语法说明：

<判定运算>：判定运算，其结果取值为TRUE、FALSE和UNKNOWN。<判定结果>语法分类如下：

<表达式1>：{= | < | <= | > | >= | <=> | <>|!= } <表达式2>
<表达式1> [NOT] LIKE <表达式2>
<表达式1> [NOt] [REGEXP | RELIKE] <表达式2>
<表达式1> [NOT] BETWEEN <表达式2> AND <表达式3>
<表达式> IS [NOT] TRUE

下面对条件<判定运算>进行解释：

❶ 比较运算
语法格式：

<表达式1> {= | < | <= | > | >= | <=> | !=} <表达式2>
1

MySQL支持的比较运算符见表5-2

表5-2 比较运算符

比较运算用于比较两个表达式的值。当两个表达式中有一个值为空值或者都为空值时，将返回UNKNOWN。对于运算符“<=>”，当两个表达式彼此相等或都等于空值时，比较的结果值为TRUE；若其中一个是空值或者都是非空值但却不相等时，则为FALSE，不会出现UNKNOWN的情况。

【例5-10】查找数据库 my_test 的表 students，输出所有女生的信息。

mysql> select * from my_test.students where student_sex = '1';
+------------+--------------+-------------+-------------+---------------+-----------------+
| student_id | student_name | student_sex | student_age | student_major | student_contact |
+------------+--------------+-------------+-------------+---------------+-----------------+
|       1321 | 李明         | 1           |          22 | 数学专业      | NULL            |
|       1322 | 张三         | 1           |          24 | 化学专业      | 139xxxxxxxx     |
|       1323 | 王五         | 1           |          23 | 数学专业      | NULL            |
|       1324 | 赵柳         | 1           |          23 | 物理专业      | NULL            |
+------------+--------------+-------------+-------------+---------------+-----------------+
4 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10

❷ 字符串匹配

语法格式：

<表达式1> [NOT] LIKE <表达式2>
1

字符串匹配是一种模式匹配，使用运算符LIKE设置过滤条件，过滤条件使用通用匹配符进行匹配运算，而不是判断是否相等进行比较。相互间进行匹配运算的对象可以是CHAR，VARCHAR，TEXT，DATETIME等数据类型。运算返回的结果是TRUE或者FALSE。

利用通配符可以在不完全确定比较值的情形下，创建一个比较特定数据的搜索模式，并置于关键字LIKE之后。可以在搜索模式中任意位置使用通配符，并且可以使用多个通配符。MySQL支持的通配符有以下两种：

（1）百分号
可以表示任何字符串，并且可以出现任意次数。

【例5-11】查找数据库 my_test 的表 students，输出所有姓“李”的学生id和姓名。
在做这道题之前，我就往库里加了几个学生的信息，然后我们输入代码：

mysql> select * from students;
+------------+--------------+-------------+-------------+---------------+-----------------+
| student_id | student_name | student_sex | student_age | student_major | student_contact |
+------------+--------------+-------------+-------------+---------------+-----------------+
|       1321 | 李明         | 1           |          22 | 数学专业      | NULL            |
|       1322 | 张三         | 1           |          24 | 化学专业      | 139xxxxxxxx     |
|       1323 | 王五         | 1           |          23 | 数学专业      | NULL            |
|       1324 | 赵柳         | 1           |          23 | 物理专业      | NULL            |
|       1325 | 李晓丽       | 1           |          22 | 英语专业      | 13799876543     |
|       1326 | 李玩         | 0           |          26 | 体育专业      | 13466543889     |
|       1327 | 马丁令       | 0           |          24 | 化学专业      | 13876544678     |
|       1328 | 王杠铃       | 0           |          22 | 体育专业      | 15877890990     |
+------------+--------------+-------------+-------------+---------------+-----------------+
8 rows in set (0.00 sec)

mysql> select student_id,student_name from students
    -> where student_name like '李%';
+------------+--------------+
| student_id | student_name |
+------------+--------------+
|       1321 | 李明         |
|       1325 | 李晓丽       |
|       1326 | 李玩         |
+------------+--------------+
3 rows in set (0.34 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

注意事项：

• MySQL默认是不区分大小写的，如果要区分大小写，则需要更换字符集的校对规则。
• 百分号不匹配空值NULL
• 百分号可以代表搜索模式中给定位置的0个、1个或多个字符。
• 尾空格可能会干扰通配符的匹配，一般可以在搜索模式的最后附加一个百分号。

（2）下划线
下划线通配符和百分号通配符用途一样，下划线只匹配单个字符，而不是多个字符，也不是0个字符。

【例5-12】查找数据库 my_test 的表 students，输出所有姓“李”并且姓名只有两个中文字符的学生id和姓名。

mysql> select student_id,student_name from students
    -> where student_name like '李_';
+------------+--------------+
| student_id | student_name |
+------------+--------------+
|       1321 | 李明         |
|       1326 | 李玩         |
+------------+--------------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9

注意：不要过度使用通配符，对通配符检索的处理一般会比其他检索方式花费更长时间。

❸ 文本匹配
文本匹配也是一种模式匹配，使用正则表达式进行文本串的匹配，满足一些复杂过滤条件的要求。正则表达式是用来匹配文本的特殊串或字符集合，是文本匹配运算中的一种搜索模式。正则表达式需要使用正则表达式语句来建立。MySQL允许使用REGEXP指定正则表达式过滤SELECT语句查询的数据。

WHERE子句中使用正则表达式进行文本匹配的语法格式：

<表达式1>[NOT][REGEXP|RLIKE]<表达式2>
1

其中，RLIKE是REGEXP的同义词。在不使用数据库表的情况下，将用于文本匹配的语法置于SELECT关键字之后，进行简单的正则表达式测试。如果返回1，则表示匹配成功，如果返回0，则表示没有匹配。

（1）基本字符匹配
使用正则表达式可以匹配任意一个字符，进行基本字符的匹配索引。

【例5-13】查找数据库 my_test 的表 students，分别使用字符串匹配和文本匹配的方式，输出专业含有。
我们先查看一下数据库中 students 表的信息：

mysql> select * from students;
+------------+--------------+-------------+-------------+---------------+-----------------+
| student_id | student_name | student_sex | student_age | student_major | student_contact |
+------------+--------------+-------------+-------------+---------------+-----------------+
|       1321 | 李明         | 1           |          22 | 数学专业      | NULL            |
|       1322 | 张三         | 1           |          24 | 化学专业      | 139xxxxxxxx     |
|       1323 | 王五         | 1           |          23 | 数学专业      | NULL            |
|       1324 | 赵柳         | 1           |          23 | 物理专业      | NULL            |
|       1325 | 李晓丽       | 1           |          22 | 英语专业      | 13799876543     |
|       1326 | 李玩         | 0           |          26 | 体育专业      | 13466543889     |
|       1327 | 马丁令       | 0           |          24 | 化学专业      | 13876544678     |
|       1328 | 王杠铃       | 0           |          22 | 体育专业      | 15877890990     |
|       1329 | 周明明       | 0           |          23 | 中文专业      | 13477890009     |
|       1330 | 王小胖       | 0           |          25 | 数学专业      | NULL            |
|       1331 | 张玲玲       | 1           |          25 | 英语专业      | 13455909877     |
|       1332 | 冯晓华       | 1           |          24 | 物理专业      | 15899900009     |
+------------+--------------+-------------+-------------+---------------+-----------------+
12 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

①直接匹配

mysql> select student_name,student_age,student_major from students where student_major like'物';
Empty set (0.00 sec)
1
2

没有查到匹配的行。

②采用百分号匹配

mysql> select student_name,student_age,student_major from students where student_major like'%物%';
+--------------+-------------+---------------+
| student_name | student_age | student_major |
+--------------+-------------+---------------+
| 赵六         |          23 | 物理专业      |
| 马丁令       |          24 | 生物专业      |
| 冯晓华       |          24 | 物理专业      |
+--------------+-------------+---------------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9

③采用正则表达式REGEXP匹配

mysql> select student_name,student_age,student_major from students where student_major regexp '物';
+--------------+-------------+---------------+
| student_name | student_age | student_major |
+--------------+-------------+---------------+
| 赵六         |          23 | 物理专业      |
| 马丁令       |          24 | 生物专业      |
| 冯晓华       |          24 | 物理专业      |
+--------------+-------------+---------------+
3 rows in set (0.40 sec)

mysql> select student_name,student_age,student_major from students where student_major rlike '物';
+--------------+-------------+---------------+
| student_name | student_age | student_major |
+--------------+-------------+---------------+
| 赵六         |          23 | 物理专业      |
| 马丁令       |          24 | 生物专业      |
| 冯晓华       |          24 | 物理专业      |
+--------------+-------------+---------------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

关键字LIKE和REGEXP存在一个重要区别。LIKE用于匹配整个列，如果不适用通配符，LIKE会严格按照单引号之间的内容查询，例如LIKE‘物’，那么仅仅会返回列值为‘物’的记录行，而‘物理’这样的值不会被返回。而REGEXP在列值内进行匹配，被匹配的文本在列值中出现，REGEXP将会找到它，比如只要列值中含有‘物’的相应行就会被返回。

（2）选择匹配
正则表达式还可以达到待搜索对象的选择性匹配，即使用“|”分隔符提供选择匹配的字符串，该分隔符类似于在SELECT语句中使用OR子句。多个OR条件可以并入单个正则表达式。

【例5-14】查找数据库 my_test 的表 students，输出含有“自动化专业”或者“数学专业”的学生姓名。

mysql> select student_name,student_major from students
    -> where student_major regexp '自动化专业|数学专业';
+--------------+---------------+
| student_name | student_major |
+--------------+---------------+
| 李明         | 数学专业      |
| 王五         | 数学专业      |
| 王小胖       | 数学专业      |
| 王青青       | 自动化专业    |
+--------------+---------------+
4 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11

（3）范围匹配
正则表达式还可以在某一个范围里对数据进行过滤。使用“[ ]”包含字符或数字集合，用于在这个集合范围内查找某个匹配字符或数字。例如“[1-9]”表示列值在1到9之间的记录。

【例5-15】查找数据库 my_test 的表 students，输出年龄中有4或5的学生姓名和年龄。

mysql> select student_name,student_age from students
    -> where student_age regexp '[4-5]';
+--------------+-------------+
| student_name | student_age |
+--------------+-------------+
| 张三         |          24 |
| 马丁令       |          24 |
| 王小胖       |          25 |
| 张玲玲       |          25 |
| 冯晓华       |          24 |
+--------------+-------------+
5 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12

（4）特殊字符匹配
正则表达式语言是由一些具有特殊含义的字符组成的，如“|”、“[ ]”以及“_”等。如果要在列值中查找具有这些特殊含义的字符的时候，需要用到转义符“\\”。具体方法是在这些特殊字符前使用“\\”作为前导，例如，正则表达式中如果出现“\\_”，则表示查找字符“_”，而为了查找匹配反斜杠“\”字符本身，则需要在正则表达式中使用“\\”。
另外，“\\”也可以用来引用空白元字符（即含有特殊含义的字符）。例如“\\f”表示换页，“\\n”表示换行，“\\r”表示回车，“\\t”表示制表，“\\v”表示纵向制表。

（5）重复匹配
正则表达式支持重复元素匹配。即用某些字符来表示要匹配的元素重复的次数。表5-3列出了这些重复元字符。

表5-3 重复元字符

【例5-16】查找数据库 my_test 的表 students，输出学生名字含有两个“明”的学生的姓名、年龄和专业。

mysql> select student_name,student_age,student_major from students
    -> where student_name regexp '[明]{2}';
+--------------+-------------+---------------+
| student_name | student_age | student_major |
+--------------+-------------+---------------+
| 周明明       |          23 | 中文专业      |
+--------------+-------------+---------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8

（6）字符类匹配
为更方便地查找，可以将经常使用的数字、字符等定义成一个预定义的字符集，然后在正则表达式中直接使用。例如，字符类“[:upper:]”表示任意大写字母，如同在正则表达式中使用“[A-Z]”。

（7）使用定位符匹配
为了匹配处于特定位置的文本，可以在正则表达式中使用定位符，这些定位符见表5-4。

表5-4 定位符

【例5-17】查找数据库 my_test 的表 students，输出“生”字开头的专业名称的学生姓名和专业。

mysql> select student_name,student_major from students
    -> where student_major regexp '生';
+--------------+---------------+
| student_name | student_major |
+--------------+---------------+
| 马丁令       | 生物专业      |
+--------------+---------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8

注意：MySQL的正则表达式，符号“^”有两种用法：在“[ ]”用来表示否定该集合；而作为定位符时，表示该字符串的开始。

❹ 判定范围
在WHERE子句中，用于表示范围的关键字有BETWEEN和IN两个。

（1）BETWEEN…AND
当查询的过滤条件被限定在某个范围时，可以使用BETWEEN关键字。

语法格式：

<表达式1> [NOT] BETWEEN <表达式2> AND <表达式3>
1

当查询的过滤条件被限定在某个范围时，可以使用BETWEEN关键字。
其中<表达式2>的值不能大于<表达式3>的值。如果不使用NOT关键字，当<表达式1>的值在<表达式2>和<表达式3>值之间的时候，返回TRUE，否则返回FALSE。使用NOT关键字则返回值相反。

【例5-18】查找数据库 my_test 的表 students，输出年龄在20到23岁之间的学生姓名、专业。

mysql> select student_name,student_major from students
    -> where student_age between 20 and 23;
+--------------+---------------+
| student_name | student_major |
+--------------+---------------+
| 李明         | 数学专业      |
| 王五         | 数学专业      |
| 赵六         | 物理专业      |
| 李晓丽       | 英语专业      |
| 王杠铃       | 体育专业      |
| 周明明       | 中文专业      |
| 张大庆       | 地质专业      |
| 王青青       | 自动化专业    |
+--------------+---------------+
8 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

（2）IN
该关键字可以指定一个值的枚举表，该表会列出所有可能的值。

语法格式：

<表达式1> IN ( <表达式2> [,...n] )
1

要判定的值与该表中的任意一个值匹配，则返回TRUE，否则返回FALSE。尽管关键字IN可用于范围判定，但其主要作用是表达式子查询。

【例5-19】查找数据库 my_test 的表 students，输出年龄在23到24岁之间的学生姓名和年龄。

mysql> select student_name,student_age from students
    -> where student_age in (23,24);
+--------------+-------------+
| student_name | student_age |
+--------------+-------------+
| 张三         |          24 |
| 王五         |          23 |
| 赵六         |          23 |
| 马丁令       |          24 |
| 周明明       |          23 |
| 冯晓华       |          24 |
| 张大庆       |          23 |
| 王青青       |          23 |
+--------------+-------------+
8 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

❺ 判定空值
关键字 IS NULL 可以用来盘点一个表达式的值是否为空值。

语法格式：

<表达式> IS <NOT> NULL
1

其中，不适用关键字NOT时，<表达式>的值为空值，则返回TRUE，否则返回FALSE；使用关键字NOT时，则相反。

【例5-20】查找数据库 my_test 的表 students，输出联系方式为空学生姓名。

mysql> select student_name,student_contact from students
    -> where student_contact is null;
+--------------+-----------------+
| student_name | student_contact |
+--------------+-----------------+
| 李明         | NULL            |
| 王五         | NULL            |
| 赵六         | NULL            |
| 王小胖       | NULL            |
+--------------+-----------------+
4 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11

❻ 子查询
在MySQL中，允许使用SELECT语句创建子查询，即嵌套在其他SELECT查询中的查询。子查询有以下4类：

• 表子查询：返回结果集是一个表。
• 行子查询：返回结果集时带有一个或多个值的一行数据。
• 列子查询：返回结果集时一列数据，该列可以有一行或多行，但每行只有一个值。
• 标量子查询：返回结果集仅仅是一个值。

（1）IN 子查询
结合关键字IN所使用的子查询主要用于判断一个给定值是否存在于查询的结果集中。

语法格式：

<表达式> [NOT] IN <子查询>
1

语法说明：

• <表达式>：用于指定表达式。当<表达式>与子查询返回的结果集中的某个值相等时，返回TRUE，否则返回FALSE；若使用关键字NOT，则返回的值正好相反。
• <子查询>：用于指定子查询。这里的子查询只能返回一列数据。对于比较复杂的查询要求，可以使用SELECT语句实现子查询的多层嵌套。

（2）比较运算符子查询
主要用于将表达式的值和子查询返回的值进行比较运算。

语法格式：

<表达式> { = | < | <= | > | >= | <=> | != } {ALL | SOME | ANY} <子查询>
1

语法说明：

• <子查询>：用于指定子查询。
• <表达式>：用于指定要进行比较的表达式。
• ALL、SOME 和 ANY：可选项。用于指定对比较运算的限制。其中关键字ALL用于指定表达式需要与子查询结果集中的每个值都进行比较，当表达式与每个值都满足比较关系时，会返回TRUE，否则返回FALSE；关键字SOME和ANY是同义词，表示表达式只要与子查询结果集中的某个值满足关系时，就返回TRUE，否则返回FALSE。

（3）EXIST子查询
关键字EXIST所使用的子查询主要用于判断子查询的结果集是否为空。

语法格式：

EXIST <子查询>
1

如果子查询的结果集不能为空，则返回TRUE，否则返回FALSE。

5.5 GROUP BY子句与分组数据

在SELECT语句中，允许使用GROUP BY子句，将结果集中的数据行根据选择列的值进行逻辑分组，一遍能汇总表内容的子集，实现对每个组的聚集计算，这时对每个组而不是对整个结果集进行聚合。

语法格式：

GROUP BY {<列名> | <表达式> | <位置>}[ASC | DESC]
1

语法说明：

• <列名>：指定用于分组的列。可以指定多个列，彼此间用逗号分隔。注意：GROUP BY子句中的各选项列必须也是SELECT语句的选择列清单中的一项。
• <表达式>：指定用于分组的表达式。通常与聚合函数一起使用，例如，可将表达式COUNT (*) AS'人数'作为SELECT选择列表清单中的一项。
• <位置>：指定用于分组的选择列在SELECT语句结果集中的位置，通常是一个正整数。例如GROUP BY 2表示根据SELECT语句列清单上的第2列的值进行逻辑分组。
• ASC | DESC：关键字ASC表示按升序分组，关键字DESC表示按降序分组，其中ASC为默认值，注意这两个关键字必须位于对应的列名、表达式、列的位置之后。

【例5-21】查找数据库 my_test 的表 students 中获取一个数据结果集，要求该结果集以性别分组，并且统计男生和女生的人数。

mysql> select student_sex,count(*) as '人数'
    -> from students
    -> group by student_sex;
+-------------+------+
| student_sex | 人数 |
+-------------+------+
| 0           |    8 |
| 1           |    6 |
+-------------+------+
2 rows in set (0.38 sec)
1
2
3
4
5
6
7
8
9
10

从例子中可以看出，GROUP BY对性别进行分组，使用聚合函数count统计学生的人数。GROUP BY也可以结合使用其他聚合函数。

【例5-22】查找数据库 my_test 的表 students，要求输出每个专业学生的平均年龄和专业名称。

mysql> select student_major,avg(student_age) as '平均年龄' from students group by student_major;
+---------------+----------+
| student_major | 平均年龄 |
+---------------+----------+
| 数学专业      |  23.3333 |
| 化学专业      |  24.0000 |
| 物理专业      |  23.5000 |
| 英语专业      |  23.5000 |
| 体育专业      |  24.0000 |
| 生物专业      |  24.0000 |
| 中文专业      |  23.0000 |
| 地质专业      |  23.0000 |
| 自动化专业    |  23.0000 |
+---------------+----------+
9 rows in set (0.12 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

使用GROUP BY需要注意以下几点：

• GROUP BY子句可以包含任意数目的列，是的其可以对分组进行嵌套，为数据分组提供更加细致的控制。
• GROUP BY子句中列出的每个列都必须是检索列或有效的表达式，但不能是聚合函数。如果在SELECT语句中使用表达式，则必须在GROUP BY子句中指定相同的表达式。注意，不能使用别名。
• 除聚合函数之外，SELECT语句中的每个列都必须在GROUP BY子句中给出。
• 如果用于分组的列中包含有NULL值，则NULL将作为一个单独的分组返回；如果该列中存在多个NULL值，则将这些NULL值所在的行分为一组。

5.6 HAVING子句

在SELECT语句中，除了能使用GROUP BY子句分组数据还可以使用HAVING子句过滤，在结果集中规定包含哪些分组和排除哪些分组。

语法格式：

HAVING <条件>
1

语法说明：
<条件>：指定过滤条件。
HAVING子句和WHERE子句非常相似，HAVING子句支持WHERE子句中所有的操作符合语法，但是两者存在几点差异。

• WHRER子句主要用于过滤数据行，而HAVING子句主要用于过滤分组，即HAVING子句基于分组的聚合值而不是特定行的值来过滤数据，主要用来过滤分组。
• WHERE子句不可以包含聚合函数，HAVING子句中的条件可以包含聚合函数。
• HAVING子句是在数据分组后进行过滤，WHERE子句会在数据分组前进行过滤。WHERE子句排除的行不包含在分组中，可能会影响HAVING子句基于这些值过滤掉的分组。

【例5-23】查找数据库 my_test 的表 students，查找专业学生数量大于2的学生总数和专业名称。

mysql> select student_major,count(*) as '总人数'
    -> from students
    -> group by student_major
    -> having count(*) > 2;
+---------------+--------+
| student_major | 总人数 |
+---------------+--------+
| 数学专业      |      3 |
+---------------+--------+
1 row in set (0.14 sec)
1
2
3
4
5
6
7
8
9
10

例子中，使用GROUP BY对学生专业进行分组，统计每组学生人数，再使用HAVING子句过滤掉学生小于等于2的数据行。

5.7 ORDER BY子句

SELECT语句中，ORDER BY子句主要用来将结果集中的数据按照一定的顺序进行排序。

语法格式：

ORDER BY {<列名> | <表达式> | <位置>}[ASC|DESC]
1

语法说明：

• <列名>：指定用于排序的列。可以指定多个列，列名之间用逗号分隔。
• <表达式>：指定用于排序的表达式。
• <位置>：指定用于排序的列在SELECT语句结果集中的位置，通常是一个正整数。
• ASC|DESC：关键字ASC表示按升序分组，DESC表示按降序分组，其中ASC为默认值。这两个关键字必须位于对应的列名、表达式、列的位置之后。

【例5-24】查找数据库 my_test 的表 students，按照学生年龄的降序方式，输出学生姓名和年龄。

mysql> select student_name,student_age from students
    -> order by student_age desc;
+--------------+-------------+
| student_name | student_age |
+--------------+-------------+
| 李玩         |          26 |
| 王小胖       |          25 |
| 张玲玲       |          25 |
| 张三         |          24 |
| 马丁令       |          24 |
| 冯晓华       |          24 |
| 王五         |          23 |
| 赵六         |          23 |
| 周明明       |          23 |
| 张大庆       |          23 |
| 王青青       |          23 |
| 李明         |          22 |
| 李晓丽       |          22 |
| 王杠铃       |          22 |
+--------------+-------------+
14 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

使用ORDER BY子句该注意以下几个方面：

• ORDER BY子句中可以包含子查询。
• 当排序的值中存在空值时，ORDER BY子句会将该空值作为最小值来对待。
• 当ORDER BY子句中指定多个列进行排序时，则MySQL会按照列的顺序从左到右依次进行排序。

5.8 LIMIT子句

当SELECT语句返回的结果集行数很多时，便于用户对结果集的浏览和操作，可以使用LIMIT子句限制SELECT语句返回的行数。这通常用于分页程序中。

语法格式：

LIMIT {[标志位置] <行数> | <行数> OFFSET <标志位置> }
1

语法说明：

• <标志位置>：可选项。默认为数字0，用于指定返回数据第一行在SELECT语句结果集中的偏移量，必须为非负整数。注意，SELECT语句结果集第一行（初始行）的偏移量为数字0，而不是1。
• <行数>：用于指定返回数据的行数，必须为非负整数常量。若这个数大于结果集总数，则返回实际的记录行。
• <行数>OFFSET<位置标志>：从第<标志位置>+1行开始取<行数>行数据。

【例5-25】查找数据库 my_test 的表 students，查找从第5个学生开始的3个学生的 id 和姓名。

mysql> select student_id,student_name from students
    -> limit 4,3;
+------------+--------------+
| student_id | student_name |
+------------+--------------+
|       1325 | 李晓丽       |
|       1326 | 李玩         |
|       1327 | 马丁令       |
+------------+--------------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10

注意，上述语句中的“LIMIT 4,3”表示从第5行开始取3行数据。因为第一行下标识是从0开始。
上述例子也可以写成如下的SQL语句：

mysql> select student_id,student_name from students
    -> order by student_id
    -> limit 3 offset 4;
+------------+--------------+
| student_id | student_name |
+------------+--------------+
|       1325 | 李晓丽       |
|       1326 | 李玩         |
|       1327 | 马丁令       |
+------------+--------------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11

5.9 UNOIN语句与联合查询

联合查询：把来自多个SELECT语句查询的结果组合到一个结果集中，并且作为单个查询结果集返回，这种查询方式成为并或联合查询。

以下两种基本情况，特别适合使用联合查询来实现：

• 在单个查询中，需要从不同的表中返回相似结构的数据。
• 对单个表执行多余查询时，需要以单条查询的信息集返回。

语法格式：

SELECT...UNION [ALL | DISTINCT] SELECT...
1

语法说明：

• SELECT…：表示SELECT语句返回的结果集，和通常使用的SELECT语句相同。
• UNION：用于表示合并前后两个SELECT语句返回的结果集。
• ALL | DISTINCT：关键字ALL表示当两个结果集有重复的记录的时候，允许重复记录存在；关键字DISTINCT表示去除两个结果集中重复出现的记录，默认是DISTINCT。

【例5-26】查找数据库 my_test 的表 students，使用UNION关键字合并专业是“物理专业”和性别是“1”的学生姓名、性别和专业信息。

mysql> select student_name,student_major,student_sex from students
    -> where student_major='物理专业'
    -> union
    -> select student_name,student_major,student_sex from students
    -> where student_sex='1';
+--------------+---------------+-------------+
| student_name | student_major | student_sex |
+--------------+---------------+-------------+
| 赵六         | 物理专业      | 1           |
| 冯晓华       | 物理专业      | 1           |
| 王五         | 数学专业      | 1           |
| 李晓丽       | 英语专业      | 1           |
| 张玲玲       | 英语专业      | 1           |
| 王青青       | 自动化专业    | 1           |
+--------------+---------------+-------------+
6 rows in set (0.50 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

UNION语句的使用非常简单，只需先给出每条SELECT语句，然后在各条SELECT语句之间加上关键字UNION即可。若希望联合查询的结果集中没有重复的数据行，可在关键字UNION之后添加关键字DISTINCT，这也是UNION的默认行为；若希望联合查询的结果集中允许存在重复的数据行，则可以在关键字UNION之后直接添加关键字ALL。

使用UNION子句的时候，需要注意以下几点：

• UNION语句至少由两条或者两条以上的SELECT语句组成，之间用UNION分隔。
• UNION语句的每个SELECT查询必须包含相同的列、表达式或者聚集函数。
• UNION语句的每个SELECT对应的列数据类型必须兼容。即类型相同或者可以隐含转换。
• UNION语句中的第一个SELECT语句所用的列名会被当成整个结果集的列名。
• UNION语句只使用一条ORDER BY子句或者LIMIT子句，且他们必须置于最后一条，SELECT语句之后。