第3章 数据查询3.1.1 SELECT语句的基本组成SELECT 输出项[, 输出项 ...][FROM 源表[WHERE 搜索条件]
[GROUP BY {列名 | 表达式|列位置}, ...][HAVING 搜索条件][ORDER BY {列名|表达式 | 列位
置}[ASC | DESC], ...][LIMIT {[偏移,]行数|行数 OFFSET 偏移}][INTO OUTFILE ''
文件名'' | INTO DUMPFILE ''文件名'' | INTO 变量名[, 变量名]]]3.1 SELECT语句概述3.1.
2 常用查询工具1. mysql命令行工具使用mysql命令行工具可以通过交互方式输入SQL语句或从文件以批处理模式执行SQL语
句，从而完成在MySQL服务器上创建数据库、查询和操作数据的任务。2. MySQL WorkbenchMySQL Workbenc
h是一种可视化的数据库设计和管理工具，可以用于管理MySQL服务器，也可以用于创建和管理数据库和各种数据库对象。MySQLWork
bench提供了一个内置的SQL编辑器，可以用来在数据库连接上执行各种SQL语句，还可以将编写的SQL语句或查询结果保存到文件中。
3. Navicat for MySQLNavicat for MySQL是由PremiumSoft公司出品的用于管理MySQL数
据库的桌面应用程序，它为MySQL数据库管理、开发和维护提供了一款直观而强大的图形界面。3.1 SELECT语句概述3.2.1
选择所有列如果要查看表中所有列的信息，可在SELECT输出项列表中使用星号（）来选择源表或视图中的所有列。如果使用表或视图名称
进行限定，则星号将被解析为对指定表或视图中的所有列的引用。当使用星号选择所有列时，查询结果中的列顺序与创建表或视图时所指定的列顺序
相同。由于SELECT 将查找表中当前存在的所有列，因此每次执行SELECT 语句时，表结构的更改（通过添加、删除或重命名
列）都会自动反映出来。3.2.2 选择部分列如果要从表中选择一部分列作为SELECT查询的输出项，则应当在输出项列表中明确地列出
每个列名，列名之间用逗号分隔。假如创建表时在表名或列名中使用了空格（不符合标识符命名规则），则编写SELECT语句时需要使用反引号
“`”将表名或列名括起来，例如SELECT `student id`, `student name` FROM `student
table`，否则会出现错误信息。如果在FROM子句中指定了多个表，而这些表中又有同名的列，则在使用这些列时需要在列名前面冠以表名
，以指明该列属于哪个表。例如，在student和score表都有一个名称为stuid的列。若要引用student表中的stuid列
，应在输出项列表中写上student.stuid；若要引用score表中的stuid列，则应在输出项列表中写上score.stui
d。3.2 选择查询输出项3.2.3 设置列别名在SELECT语句中，可以使用AS子句来更改结果集中的列名或为派生列分配名称：
列名 [AS] 别名3.2.4 增加派生列在输出项列表中，有些列不是指定为对列的简单引用，而是通过表中的其他列值计算而生成，例如
表中有单价和数量列，可以在输出项列表中增加一个用于计算总额的表达式，其值等于单价乘以数量，此即派生列。派生列本无名称，通常应使用A
S子句为其指定别名。在派生列中，可以对数值列或常量使用算术运算符或函数进行计算，也可以进行数据类型转换。MySQL支持下列算术运算
符：+（加）、－（减）、（乘）、/（除）、%（模，即取余数），可以使用算术运算符可以对数值数据进行加、减、乘、除运算。此外，也可
以使用日期函数或常规加或减算术运算符对日期时间类型的列进行算术运算。3.2 选择查询输出项3.2.5 消除重复行在输出项列表中
可以使用ALL和DISTINCT修饰符，用于指定是否应返回重复的行。ALL是默认值，指定应返回所有匹配的行，包括重复行在内。DIS
TINCT指定从结果集中删除重复的行。DISTINCTROW是DISTINCT的同义词。3.2.6 没有表列的SELECT语句在
SELECT语句中，只要SELECT子句是必选的，其他子句一律都是可选项。如果SELECT选择列表中仅包含常量、变量、函数和其他表
达式，而不包含从任何表或视图中选择的列，则没有必要使用FROM子句。3.2 选择查询输出项3.3.1 单表查询当从单个数据库表
中查询数据时，需要在FROM子句中指定要引用的该表的名称，这种查询称为单表查询。在单表查询中，可以使用下列两种方式引用来源表。首先
使用USE语句将一个数据库设置为当前数据库，然后在FROM子句中指定要引用的表名，此时这个表就是属于当前数据库。不设置当前数据库，
在FROM子句中指定要引用的表名时在其前面冠以所属数据库的名称。如果要从多个表中查询数据时，则必须在FROM子句中指定要引用的多个
表名，这种查询称为多表查询。在多表查询中，需要使用某种方式对不同的表进行连接，因此多表查询也称为连接查询。连接方式分为全连接和JO
IN连接，JOIN连接又分为内连接、外连接和交叉连接。3.3 选择查询数据源3.3.2 全连接查询全连接是指在FROM子句中使
用逗号来分隔各个表，语法格式如下：FROM 表名 [[AS] 别名][, 表名 [AS] 别名], ...其中表名指定要引用的表，
AS关键字和别名都是可选的。在全连接查询中，查询结果中的列来自各个表，结果集是由各个表中的行进行交叉所得到的各种可能的组合，也称为
笛卡儿乘积，结果集包含的行数是各个来源表行数的乘积。例如，A表包含200行，B表300行，则以全连接方式查询A表和B表时，返回的结
果集将包含200300=60000行数据。因此，使用全连接方式时有可能生成行数非常大的结果集。在实际应用中，使用全连接查询时通常
可以使用WHERE子句来设置一个条件表达式，以控制结果集的大小。3.3 选择查询数据源3.3.3 内连接查询内连接是指使用关键
词INNER JOIN来连接要查询的各个表，语法格式如下：FROM 表名 [[AS] 别名] INNER JOIN 表名 [[AS
] 别名] ON 条件表达式其中表名用于指定要查询的表，AS关键字和别名是可选的。由于内连接是默认的连接方式，关键字INNER也可
以省略不写。ON子句主要用于指定表之间的连接条件，其他与表连接无关的条件应放在WHERE子句中。使用内连接查询时，将根据ON子句指
定的连接条件合并两个表并返回满足条件的行。3.3 选择查询数据源3.3.4 外连接查询外连接是指在FROM子句中使用OUTER
关键字来连接要查询的两个表，具体分为以下几种形式。左外连接（LEFT OUTER JOIN）：结果集中除了匹配行之外，还包括左表中
存在但右表中不匹配的行，对于这样的行，从右表中选择的列设置为NULL。右外连接（RIGHT OUTER JOIN）：结果集中除了匹
配行之外，还包括右表中存在但左表中不匹配的行，对于这样的行，从左表中选择的列设置为NULL。自然连接（NATURAL JOIN）：
其语义定义与设置ON条件的INNER JOIN相同，可以进一步分为自然左连接（NATURAL LEFT OUTER JOIN）和自
然右连接（NATURAL RIGHT OUTER JOIN）。外连接只能用于连接两个表。当使用各种形式的外连接时，可以省略OUTE
R关键字。3.3.5 交叉连接查询交叉连接是指在FROM子句中使用CROSS JOIN来连接各个表。如果不使用连接条件，则交叉连
接就是对两个表进行笛卡儿乘积运算，结果集是由第一个表的行与第二个表的行拼接后而形成，结果集的行数就等于两个表的行数的乘积。在MyS
QL中，从语法上讲CROSS JOIN与INNER JOIN等同，两者可以互换。3.3 选择查询数据源3.4.1 WHERE子
句语法格式在SELECT语句中WHERE子句是一个可选项，使用时应当将其放在FROM子句的后面，语法格式如下：WHERE 搜索条件
其中搜索条件定义要查询的行应满足的条件，这个条件是用运算符连接列名、常量、变量、函数等而得到的表达式，其取值为TRUE（1）、FA
LSE（0）或NULL。通过WHERE子句可以为查询设置一系列搜索条件，只有那些满足搜索条件的行才用于生成结果集，那些不满足搜索条
件的行则不会包含在结果集内。3.4.2 比较搜索在MySQL中，可以使用比较运算符来比较两个表达式的大小，语法格式如下：表达式1
比较运算符 表达式2其中表达式1和表达式2是要进行比较的表达式，其数据类型是除TEXT和BLOB之外的数据类型。MySQL支持的
比较运算符如下。? =：等于 ? <=：小于等于 ? >：大于? >=：大于等于? <=>：相等或都是N
ULL <>：不等于? !=：不等于如果两个表达式都不是NULL，则比较运算符（<=>除外）返回布尔值TRUE或FA
LSE；如果两个表达式中有一个为NULL，或两个都是NULL，则返回UNKNOWN。3.4 设置查询条件3.4.3 判定空值前
面曾经介绍了如何使用比较运算符“<=>”来判定一个表达式是否为NULL值。实际上，MySQL还专门提供了一个用于判定空值的比较运算
符IS NULL，其语法格式如下。表达式 IS [NOT] NULL如果表达式的值为NULL，则返回TRUE，否则返回FALSE。
NOT为可选项，当使用NOT时，将对运算结果取反。3.4.4 范围搜索在WHERE子句中，可以使用BETWEEN运算符来指定要搜
索的范围，也可以使用NOT BETWEEN来查找指定范围之外的所有行，语法格式如下。表达式 [NOT] BETWEEN 起始值 A
ND 终止值其中表达式给出要测试的表达式，其值可能位于指定的范围内。这个范围的大小由起始值和终止值指定，起始值和终止值都是任何有效
的表达式。表达式、起始值和终止值必须具有相同的数据类型。NOT指定对运算结果取反。AND用作一个占位符，指示要测试的表达式的值应该
处于由起始值与终止值所指定的范围内。BETWEEN运算符返回结果为布尔类型。如果测试表达式的值大于或等于起始值，并且小于或等于终止
值，则BETWEEN返回TRUE，NOT BETWEEN返回FALSE。如果表达式的值小于起始值或大于终止值，则BETWEEN返回
FALSE，NOT BETWEEN返回TRUE。如果要指定一个排他性范围，则应使用大于运算符（>）和小于运算符（<）。3.4 设
置查询条件3.4.5 列表搜索在WHERE子句中使用IN运算符可以选择与列表中的任意值匹配的行：表达式 [NOT] IN (值,
...)其中表达式给出要进行的测试的表达式。如果表达式等于IN列表中的任何值，则返回TRUE，否则返回FALSE。如果使用NOT
关键字，则对结果取反。3.4.6 模式匹配在WHERE子句中可以使用LIKE运算符来判定一个字符串是否与指定的模式相匹配：表达式
[NOT] LIKE 模式 [ESCAPE ''转义字符'']其中表达式指定要进行测试的表达式，可以是CHAR、VARCHAR、TE
XT或DATETIME等数据类型；模式给出要在匹配表达式中搜索并且可以包含有效通配符的字符串。常用的通配符： 百分号“%”——包含
零个或多个字符的任意字符串； 下画线“_”——表示任何单个字符。使用LIKE运算符时，如果测试表达式与指定模式匹配，则返回TRUE
，否则返回FALSE。如果使用NOT关键字，则对运算结果取反。ESCAPE子句用于指定转义字符，转义字符必须是单个字符。如果要通过
查询来搜索通配符（例如“%”和“_”）本身，则可以通过使用ESCAPE子句来定义转义字符。3.4 设置查询条件3.4.7 组合
搜索条件在SQL中所有逻辑运算符的计算结果为TRUE、FALSE或NULL（UNKNOWN），它们在MySQL中分别实现为1（TR
UE）、0（FALSE）和NULL。MySQL支持以下逻辑运算符。逻辑与运算符（AND，&&）：如果所有操作数都不为零且不为NUL
L，则结果为1，如果一个或多个操作数为0，则结果为0；如果任一操作数为NULL，则返回NULL。逻辑或运算符（OR，||）：当两个
操作数都不是NULL时，如果任何操作数非零，则结果为1，否则为0。使用NULL操作数时，如果另一个操作数非零，则结果为1，否则为N
ULL。如果两个操作数均为NULL，结果为NULL。逻辑非运算符（NOT，!）：这是一个单目运算符。如果操作数为0，则结果为1；如
果操作数不为0，则结果为0，NOT NULL返回NULL。逻辑异或（XOR）：如果任一操作数为NULL，则返回NULL。对于非NU
LL操作数，如果奇数个操作数非零，则结果为1，否则返回0。3.4 设置查询条件3.4.8 正则表达式搜索在WHERE子句中，可
以使用REGEXP运算符来判定字符串是否匹配指定模式。使用REGEXP运算符时，应遵循以下语法格式。表达式 [NOT] [REGE
XP|RLIKE] 模式3.4 设置查询条件3.5.1 GROUP BY子句语法格式使用SELECT语句从数据库中查询数据时，
可以使用GROUP BY子句对查询结果进行分组：GROUP BY {列名 | 表达式 | 列位置}, ... [WITH ROLL
UP]GROUP BY后面的分组依据可以是列名、表达式和列位置，为输出选择的列都可以在这里引用。列位置为整数，从1开始编号。在GR
OUP BY子句中允许使用WITH ROLLUP修饰符，此时在查询返回的结果集内不仅包含由GROUP BY提供的行，而且还包含附加
的汇总行。使用GROUP BY子句时，SELECT语句针对每个组返回一行，每个组中的行在指定的列中具有相同的值。3.5.2 在分
组查询中应用搜索条件使用HAVING子句对GROUP BY子句设置搜索条件：HAVING 条件HAVING语法与WHERE语法类似
，两者的区别在于：WHERE搜索条件在进行分组之前应用，而HAVING搜索条件在进行分组之后应用，而且HAVING可以包含聚合函数
，也可以引用输出项列表中的任意项。3.5 查询结果分组3.5.3 使用WITH ROLLUP汇总数据当使用GROUP BY子句
对结果集进行分组处理时，每个组在结果集中都会有一行，结果集被分成几个组就会返回几行。如果要对所有组进行汇总计算，则可以使用WITH
ROLLUP子句来生成一些附加的汇总行。使用WITH ROLLUP子句指定在结果集内不仅包含由GROUP BY提供的行，还包含汇
总行。按层次结构顺序，从组内的最低级别到最高级别汇总组。组的层次结构取决于列分组时指定使用的顺序。更改列分组的顺序会影响在结果集内
生成的行数。GROUP BY ... WITH ROLLUP为列表达式的每个组合创建一个组，而且它将查询结果总结为小计和总计。3.
5 查询结果分组3.5.4 使用聚合函数汇总数据在MySQL中有很多聚合函数。聚合函数对一组值执行计算并返回单个值。除了COU
NT函数以外，其他聚合函数都会忽略空值。所有聚合函数均为确定性函数。也就是说，只要使用一组特定输入值调用聚合函数，它总是返回相同的
值。聚合函数可以用在SELECT子句、ORDER BY子句以及HAVING子句中。3.5 查询结果分组3.6.1 ORDER
BY子句语法格式在SELECT语句中，可以通过添加ORDER BY子句来指定返回的查询结果所使用的排序顺序：ORDER BY {列
名 | 表达式 | 列位置} [ASC | DESC], ...在ORDER BY后面指定的作为排序依据可以是列名、表达式或列位置
，列位置从1开始编号。作为排序依据的列也可以不包含在由SELECT子句指定的输出项列表中，计算列也可以作为排序列。ASC指定按升序
排序，即从最低值到最高值对指定列中的值进行排序，这是默认排序顺序，因此ASC关键字可以省略。DESC指定按降序排序，即从最高值到最
低值对指定列中的值进行排序。在排序操作中，空值被视为最低的可能值，因此升序排列时空值出现在最上方，降序排列时空值出现在最下方。3.
6.2 按单列排序在ORDER BY子句中可以指定单个列、表达式或列位置作为排序依据，此时也可以将聚合函数用到排序列中。3.6.
3 按多列排序在ORDER BY子句中也可以指定多个排序列，不同排序列之间以逗号分隔。排序列的顺序决定结果集的排序方式，即首先按
照前面的列值进行排序，如果在两个行中该列的值相同，则按照后面的列值进行排序，以此类推。3.6 查询结果排序3.7.1 LIMI
T子句语法格式LIMIT子句可以用于限制SELECT语句返回的行数，语法格式如下。LIMIT {[偏移,] 行数|行数 OFFSE
T 偏移}其中偏移和行数都是非负整数常数；偏移指定要返回的起始行的偏移量，其默认值为0，表示第一行的偏移量；行数指定要返回的最大行
数。例如，LIMIT 10表示SELECT语句返回结果集的前面10行；LIMIT 5, 20表示从第6行开始返回20行。在实际应用
中，经常需要按某项标准来制作排行榜，此时将LIMIT子句与ORDER BY子句一起使用即可。3.7.2 检索到结果集末尾如果要从
特定的偏移量检索所有行直到结果集的末尾，则可以使用一些大数字作为第二个参数。例如，下面的语句检索从第96行到最后一行的所有行：SE
LECT FROM tbl LIMIT 95, 18446744073709551615;3.7 限制查询结果行数3.8.1
标量子查询子查询的最简单形式是返回单一值的标量子查询。标量子查询是一个单一操作数。只要单一列值或文字是合法的，并且希望子查询具
有所有操作数都具有的特性，则可以使用标量子查询。操作数具有的特性包括：一个数据类型、一个长度、一个指示是否可以为NULL值的标志等
。3.8.2 比较子查询比较子查询是指将表达式的值与子查询返回的结果进行比较，语法格式如下。表达式 比较运算符 {ALL | S
OME | ANY} (SELECT语句)其中表达式指定要进行比较的表达式，圆括号内的SELECT语句表示一个子查询，比较运算符包
括：>、<、>=、<=、<>、!=、<=>。3.8 子查询3.8.3 IN子查询子查询可以通过关键字IN引入，语法格式如下。表
达式 [NOT] IN (子查询)其中子查询为列子查询，它返回的结果集是单个列，其中包含0个或多个值。使用IN引入的子查询可以用于
集成员测试，也就是将一个表达式的值与子查询返回的一列值进行比较，如果该表达式的值与此列中的任何一个值相等，则集成员测试返回TRUE
，否则返回FALSE。关键字NOT用于对测试结果取反。在集成员测试中，由子查询返回的结果集是单个列值的一个列表，该列的数据类型必须
与测试表达式的数据类型相同。当子查询返回结果之后，外层查询将使用这些结果。使用子查询时需要注意限定列名的问题。一般规则是，语句中的
列名通过同级FROM子句中引用的表来隐性限定。如果子查询的FROM子句引用的表中不存在子查询中引用的列，而外部查询的FROM子句引
用的表中存在该列，则这个子查询可以正确执行。3.8 子查询3.8.4 ANY子查询子查询可以使用关键字ANY引入：表达式 比较
运算符 {ANY | SOME} (子查询)ANY关键词前面必须使用一个比较运算符。子查询为列子查询，它返回的结果集是单个列，其中
包含0个或多个值。使用ANY关键词引入子查询时，对表达式与子查询返回的列中的任何一个值进行比较，如果比较结果为TRUE，则返回TR
UE。3.8.5 ALL子查询子查询可以使用关键字ALL引入：表达式 比较运算符 ALL (子查询)关键字ALL必须用在一个比较
运算符的后面。子查询是一个列子查询，它返回的结果集是单个列，其中包含0个或多个值。使用ALL引入子查询时，将使用比较运算符对表达式
与子查询返回的列中的所有值进行比较，如果比较结果为TRUE，则返回TRUE。3.8 子查询3.8.6 行子查询行子查询是一个能
返回一个单一行的子查询，它返回的结果集包含一个以上的列值（1行N列）。下面给出两个例子。SELECT FROM t1 WHER
E (1, 2) = (SELECT col1, col2 FROM t2);SELECT FROM t1 WHERE ROW
(1, 2) = (SELECT col1, col2 FROM t2);其中表达式(1, 2)和ROW(1, 2)称为行构造器，
两者是等效的。如果在表t2的一个行中，col1=1且col2=2，则WHERE表达式的值为TRUE。下面两个语句在语义上是等效的。
SELECT FROM t1 WHERE (col1,col2) = (1,1);SELECT FROM t1 WHERE
col1 = 1 AND col2 = 1;3.8 子查询3.8.7 EXISTS子查询子查询可以使用关键字EXISTS引入
，语法格式如下。[NOT] EXISTS (子查询)当使用EXISTS关键字入子查询时，将进行存在性测试。外部查询的WHERE子句
测试子查询返回的行是否存在。如果子查询返回任何的行，则EXISTS条件为TRUE，否则为FALSE。子查询实际上不产生任何数据，它
只返回TRUE或FALSE值。NOT用于对测试结果取反。3.8.8 派生表子查询除了用在SELECT语句的输出项列表和WHERE
子句中，也可以用在FROM子句中，此时还允许为子查询设置一个别名。当处理具有别名的子查询时，会将返回的结果存储到一个中间表，外部查
询可以从该中间表中查询数据，这种中间表称为派生表。注意：派生表只是由SELECT语句返回的虚拟表，它并不作为对象存储，仅在查询执行
期间存在。与子查询不同的是，派生表必须具有别名，以便能够在外部查询中引用其名称。如果派生表没有别名，将会发生错误。3.8 子查询
3.8.9 公用表表达式公用表表达式（CTE）是MySQL 8.0的新特性之一。具体来说，公用表表达式就是一个命名的临时结果集，
它存在于单个语句的范围内，并且可以在该语句中多次引用。CTE的结构包括名称、可选的列名列表和定义CTE的子查询。定义CTE后，可以
在SELECT语句中引用它。CTE的基本语法格式如下：WITH CTE名称 [(列名, 列名, ...)] AS ( 子查询
)SELECT FROM CTE名称;子查询中的列数必须与列名列表中的列数相同。如果省略列名列表，则CTE将使用定义CTE的子
查询的列名列表。与派生表类似，公用表表达式不作为对象存储，仅在查询执行期间存在。与派生表不同的是，公用表表达式可以递归引用，也可以
在同一个查询中多次引用。公用表表达式提供了更好的可读性和性能，使用公用表表达式可以通过更可读的方式来构建复杂查询。3.8 子查询
3.9.1 UNION语句UNION语句可以将多个SELECT语句的结果集组合成一个新的结果集，语法格式如下。SELECT ...UNION [ALL | DISTINCT]SELECT ...[UNION [ALL | DISTINCT]SELECT ...]在每个SELECT语句的对应位置上选择的列应具有相同的数据类型。例如，在第一个语句选择的第一列应与其他语句选择的第一列具有相同的数据类型。在第一个SELECT语句中所使用的列名称将作为于结果集的列名称。各个SELECT语句都是常规的选择语句，但是受到一些限制，例如只有最后一个SELECT语句可以使用INTO OUTFILE。3.9 组合查询结果3.9.2 应用示例在学生成绩管理数据库中查询软件专业和网络专业学号排在前3位的学生:USE sams;(SELECT major AS 专业, classname AS 班级, stuid AS 学号, stuname AS 姓名, gender AS 性别, birthdate AS 出生日期FROM studentWHERE major=''软件'' ORDER BY stuid LIMIT 3)UNION(SELECT major 专业, classname AS 班级, stuid AS 学号, stuname AS 姓名, gender AS 性别, birthdate AS 出生日期FROM studentWHERE major=''网络'' ORDER BY stuid LIMIT 3);3.9 组合查询结果 |
|
