MySQL数据库基础与实例教程第5章

1、肖红肖红MySQLMySQL数据库基础与实例教程数据库基础与实例教程 之之表记录的检索表记录的检索1 12 23 3 本章详细讲解本章详细讲解select语句检索表语句检索表记录的方法，并结记录的方法，并结合合“选课系统选课系统”，讨论该系统部分问讨论该系统部分问题域的实现方法。题域的实现方法。4 4使用使用where子句过滤结果集子句过滤结果集select语句概述语句概述使用使用order by子句对结果集排序子句对结果集排序使用聚合函数汇总结果集使用聚合函数汇总结果集5 5使用使用group by子句对记录分组统计子句对记录分组统计内容一览内容一览 6 67 78 8 本章详细讲解本章详细

2、讲解select语句检索表语句检索表记录的方法，并结记录的方法，并结合合“选课系统选课系统”，讨论该系统部分问讨论该系统部分问题域的实现方法。题域的实现方法。9 9子查询子查询合并结果集合并结果集选课系统综合查询选课系统综合查询使用正则表达式模糊查询使用正则表达式模糊查询1010全文检索全文检索内容一览内容一览 5.1 select5.1 select语句概述语句概述select语句的语法格式如下。语句的语法格式如下。select 字段列表字段列表from 数据源数据源 where条件表达式条件表达式 group by 分组字段分组字段 having条件表达式条件表达式 order by 排序

3、字段排序字段 asc | desc 1 12 23 3使用谓词限制记录的行数使用谓词限制记录的行数使用使用select子句指定字段列表子句指定字段列表使用使用from子句指定数据源子句指定数据源4多表连接多表连接5.1 select5.1 select语句概述语句概述使用以下几种方式指定字段列表：使用以下几种方式指定字段列表：5.1.1 5.1.1 使用使用selectselect子句指定字段列表子句指定字段列表 可以为字段列表中的字段名或表达式指定可以为字段列表中的字段名或表达式指定别名，中间使用别名，中间使用as关键字分隔即可（关键字分隔即可（as关键关键字可以省略）。字可以省略）。 多表

4、查询时，同名字段前必须添加表名前多表查询时，同名字段前必须添加表名前缀，中间使用缀，中间使用“.”分隔。分隔。5.1.1 5.1.1 使用使用selectselect子句指定字段列表子句指定字段列表MySQL中的两个谓词中的两个谓词distinct和和limit可以限制记可以限制记录的行数。录的行数。（1）使用谓词）使用谓词distinct过滤结果集中的重复记录过滤结果集中的重复记录数据库表中不允许出现重复的记录，但这不意味数据库表中不允许出现重复的记录，但这不意味着着select的查询结果集中不会出现记录重复的现象。的查询结果集中不会出现记录重复的现象。如果需要过滤结果集中重复的记录，可以使

5、用谓如果需要过滤结果集中重复的记录，可以使用谓词关键字词关键字distinct，语法格式如下。，语法格式如下。distinct 字段名字段名5.1.2 5.1.2 使用谓词限制记录的行数使用谓词限制记录的行数（2）使用谓词）使用谓词limit查询某几行记录查询某几行记录查询前几条或者中间某几条记录，可以使用谓词查询前几条或者中间某几条记录，可以使用谓词关键字关键字limit实现。语法格式如下。实现。语法格式如下。select字段列表字段列表from数据源数据源limit start,length;start表示从第几行记录开始检索，表示从第几行记录开始检索，length表示检表示检索多少行记录

6、。表中第一行记录的索多少行记录。表中第一行记录的start值为值为0。5.1.2 5.1.2 使用谓词限制记录的行数使用谓词限制记录的行数例如：例如：select * from student limit 0,3; 该该SQL语句等效于：语句等效于：select * from student limit 3;例如检索例如检索choose表中从第表中从第2条记录开始的条记录开始的3条记录条记录信息，可以使用下面的信息，可以使用下面的SQL语句。语句。select * from choose limit 1,3;5.1.2 5.1.2 使用谓词限制记录的行数使用谓词限制记录的行数 多张数据库表（或

7、者视图）多张数据库表（或者视图）“缝补缝补”成一个结成一个结果集时，需要指定果集时，需要指定“缝补缝补”条件，该条件，该“缝补缝补”条条件称为连接条件。件称为连接条件。 指定连接条件的方法有两种：第一种方法是在指定连接条件的方法有两种：第一种方法是在where子句中指定连接条件（稍后讲解）。第二子句中指定连接条件（稍后讲解）。第二种方法是在种方法是在from子句中使用连接（子句中使用连接（join）运算将）运算将多个数据源按照某种连接条件多个数据源按照某种连接条件“缝补缝补”在一起。在一起。 5.1.3 5.1.3 使用使用fromfrom子句指定数据源子句指定数据源 第二种方法第二种方法fr

8、om子句的语法格式如下。子句的语法格式如下。from 表名表名1 连接类型连接类型 join 表名表名2 on 表表1和和表表2之间的连接条件之间的连接条件说明：说明：SQL标准中的连接类型主要分为标准中的连接类型主要分为inner连连接（内连接）和接（内连接）和outer连接（外连接），而外连连接（外连接），而外连接又分为接又分为left（左外连接，简称为左连接）、（左外连接，简称为左连接）、right（右外连接，简称为右连接）以及（右外连接，简称为右连接）以及full（完（完全外连接，简称完全连接）。全外连接，简称完全连接）。5.1.3 5.1.3 使用使用fromfrom子句指定数据源子

9、句指定数据源1内连接（内连接（inner join） 内连接将两个表中满足指定连接条件的记录连内连接将两个表中满足指定连接条件的记录连接成新的结果集，舍弃所有不满足连接条件的记接成新的结果集，舍弃所有不满足连接条件的记录。内连接是最常用的连接类型，也是默认的连录。内连接是最常用的连接类型，也是默认的连接类型，可以在接类型，可以在from子句中使用子句中使用inner join（inner关键字可以省略）实现内连接，语法格关键字可以省略）实现内连接，语法格式如下。式如下。from 表表1 inner join 表表2 on 表表1和表和表2之间的之间的连接条件连接条件5.1.3 5.1.3 使用

10、使用fromfrom子句指定数据源子句指定数据源任务布置任务布置1：完成本书场景描述：完成本书场景描述1的任务要求。的任务要求。说明：使用内连接连接两个数据库表时，连接条说明：使用内连接连接两个数据库表时，连接条件会同时过滤表件会同时过滤表1与表与表2的记录信息。的记录信息。5.1.3 5.1.3 使用使用fromfrom子句指定数据源子句指定数据源2外连接（外连接（outer join） 外连接又分为左连接（外连接又分为左连接（left join）、右连接）、右连接（right join）和完全连接（）和完全连接（full）。与内连接不）。与内连接不同，外连接（左连接或右连接）的连接条件只过

11、同，外连接（左连接或右连接）的连接条件只过滤一个表，对另一个表不进行过滤（该表的所有滤一个表，对另一个表不进行过滤（该表的所有记录出现在结果集中）；记录出现在结果集中）； 注意：注意：MySQL暂不支持完全连接暂不支持完全连接 。5.1.3 5.1.3 使用使用fromfrom子句指定数据源子句指定数据源（1）左连接的语法格式）左连接的语法格式from 表表1 left join 表表2 on 表表1和表和表2之间的连之间的连接条件接条件说明：语法格式中表说明：语法格式中表1左连接表左连接表2，意味着查询结，意味着查询结果集中须包含表果集中须包含表1的全部记录，然后表的全部记录，然后表1按指定

12、的按指定的连接条件与表连接条件与表2进行连接，若表进行连接，若表2中没有满足连接中没有满足连接条件的记录，则结果集中表条件的记录，则结果集中表2相应的字段填入相应的字段填入NULL。5.1.3 5.1.3 使用使用fromfrom子句指定数据源子句指定数据源任务布置任务布置2：完成本书场景描述：完成本书场景描述2的任务要求。的任务要求。5.1.3 5.1.3 使用使用fromfrom子句指定数据源子句指定数据源（2）右连接的语法格式）右连接的语法格式from 表表1 right join 表表2 on 表表1和表和表2之间的之间的连接条件连接条件说明：语法格式中表说明：语法格式中表1右连接表右

13、连接表2，意味着查询结，意味着查询结果集中须包含表果集中须包含表2的全部记录，然后表的全部记录，然后表2按指定的按指定的连接条件与表连接条件与表1进行连接，若表进行连接，若表1中没有满足连接中没有满足连接条件的记录，则结果集中表条件的记录，则结果集中表1相应的字段填入相应的字段填入NULL。 5.1.3 5.1.3 使用使用fromfrom子句指定数据源子句指定数据源任务布置任务布置3：完成本书场景描述：完成本书场景描述3的任务要求。的任务要求。5.1.3 5.1.3 使用使用fromfrom子句指定数据源子句指定数据源 以以3个表为例，语法格式如下。个表为例，语法格式如下。from 表表1

14、连接类型连接类型 join 表表2 on 表表1和表和表2之之间的连接条件间的连接条件连接类型连接类型 join 表表3 on 表表2和表和表3之间的连接之间的连接条件条件5.1.4 5.1.4 多表连接多表连接 数据库中存储着海量数据库中存储着海量数据，数据库用户往往数据，数据库用户往往需要的是满足特定条件需要的是满足特定条件的记录，的记录，where子句可子句可以实现结果集的过滤筛以实现结果集的过滤筛选。选。 where子句的语法格式：子句的语法格式：where 条件表达式条件表达式1 12 23 3is NULL运算符运算符使用单一的条件过滤结果集使用单一的条件过滤结果集select语句

15、与字符集语句与字符集4使用逻辑运算符使用逻辑运算符5使用使用like进行模糊查询进行模糊查询5.2 5.2 使用使用wherewhere子句过滤结果集子句过滤结果集 单一的过滤条件可以使用下面的布尔表达式表单一的过滤条件可以使用下面的布尔表达式表示。示。表达式表达式1 比较运算符比较运算符 表达式表达式2说明：说明：“表达式表达式1”和和“表达式表达式2”可以是一个字段可以是一个字段名、常量、变量、函数甚至是子查询。名、常量、变量、函数甚至是子查询。比较运算符用于比较两个表达式的值，比较的结比较运算符用于比较两个表达式的值，比较的结果是一个布尔值（果是一个布尔值（true或者或者false）。

16、）。5.2.1 5.2.1 使用单一的条件过滤结果集使用单一的条件过滤结果集 常用的比较运算符有常用的比较运算符有=（等于）、（等于）、（大于）、（大于）、=（大于等于）、（大于等于）、（小于）、（小于）、=（小于等于）、（小于等于）、（不等于）、（不等于）、!=（不等于）、（不等于）、!（不大于）。（不大于）。 如果表达式的结果是数值，则按照数值的大小如果表达式的结果是数值，则按照数值的大小进行比较；如果表达式的结果是字符串，则需要进行比较；如果表达式的结果是字符串，则需要参考字符序参考字符序collation的设置进行比较。的设置进行比较。5.2.1 5.2.1 使用单一的条件过滤结果集使

17、用单一的条件过滤结果集 is NULL用于判断表达式的值是否为空值用于判断表达式的值是否为空值NULL（is not 恰恰相反），恰恰相反），is NULL的语法格式如下。的语法格式如下。表达式表达式 is not NULL说明：不能将说明：不能将“score is NULL”写成写成“score = NULL;”，原因是，原因是NULL是一个不确定的数，不能是一个不确定的数，不能使用使用“=”、“！=”等比较运算符与等比较运算符与NULL进行比较。进行比较。 5.2.2 is NULL5.2.2 is NULL运算符运算符 任务布置任务布置4：完成本书场景描述：完成本书场景描述4：MySQL

18、客户客户机显示结果集时出现乱码问题的任务要求。机显示结果集时出现乱码问题的任务要求。 任务布置任务布置5：完成本书场景描述：完成本书场景描述5：查询结果失：查询结果失败问题的任务要求。败问题的任务要求。 任务布置任务布置6：完成本书场景描述：完成本书场景描述6：SQL语句解语句解析错误问题的任务要求。析错误问题的任务要求。5.2.3 select5.2.3 select语句与字符集语句与字符集 where子句中可以包含多个查询条件，使用逻子句中可以包含多个查询条件，使用逻辑运算符可以将多个查询条件组合起来，完成更辑运算符可以将多个查询条件组合起来，完成更为复杂的过滤筛选。常用的逻辑运算符包括逻

19、辑为复杂的过滤筛选。常用的逻辑运算符包括逻辑与（与（and）、逻辑或（）、逻辑或（or）以及逻辑非（！），其）以及逻辑非（！），其中逻辑非（！）为单目运算符。中逻辑非（！）为单目运算符。 5.2.4 5.2.4 使用逻辑运算符使用逻辑运算符1逻辑非（！）逻辑非（！） 逻辑非（！）为单目运算符，逻辑非（！）的逻辑非（！）为单目运算符，逻辑非（！）的使用方法较为简单，如下所示。使用逻辑非（！）使用方法较为简单，如下所示。使用逻辑非（！）操作布尔表达式时，布尔表达式的值为操作布尔表达式时，布尔表达式的值为true时，时，整个逻辑表达式的结果为整个逻辑表达式的结果为false，反之亦然。，反之亦然。!

20、布尔表达式布尔表达式5.2.4 5.2.4 使用逻辑运算符使用逻辑运算符2and逻辑运算符逻辑运算符 使用使用and逻辑运算符连接两个布尔表达式时，逻辑运算符连接两个布尔表达式时，只有两个布尔表达式的值都为只有两个布尔表达式的值都为true时，整个逻辑时，整个逻辑表达式的结果才为表达式的结果才为true。语法格式如下。语法格式如下。布尔表达式布尔表达式1 and 布尔表达式布尔表达式2 5.2.4 5.2.4 使用逻辑运算符使用逻辑运算符 另外另外MySQL还支持还支持betweenand运算符，运算符，betweenand运算符用于判断一个表达式的值运算符用于判断一个表达式的值是否位于指定的

21、取值范围内，是否位于指定的取值范围内，betweenand的的语法格式如下。语法格式如下。表达式表达式 not between 起始值起始值 and 终止值终止值5.2.4 5.2.4 使用逻辑运算符使用逻辑运算符3使用使用or逻辑运算符逻辑运算符 使用使用or逻辑运算符连接两个布尔表达式时，只逻辑运算符连接两个布尔表达式时，只有两个表达式的值都为有两个表达式的值都为false时，整个逻辑表达式时，整个逻辑表达式的结果才为的结果才为false。语法格式如下。语法格式如下。布尔表达式布尔表达式1 or 布尔表达式布尔表达式2 5.2.4 5.2.4 使用逻辑运算符使用逻辑运算符 另外另外MySQ

22、L还支持还支持in运算符，运算符，in运算符用于判运算符用于判定一个表达式的值是否位于一个离散的数学集合定一个表达式的值是否位于一个离散的数学集合内，内，in的语法格式如下。的语法格式如下。表达式表达式 not in （数学集合）（数学集合）5.2.4 5.2.4 使用逻辑运算符使用逻辑运算符 like运算符用于判断一个字符串是否与给定的模运算符用于判断一个字符串是否与给定的模式相匹配。式相匹配。 模式是一种特殊的字符串，特殊之处在于不仅模式是一种特殊的字符串，特殊之处在于不仅包含普通字符，还包含有通配符。在实际应用中，包含普通字符，还包含有通配符。在实际应用中，如果不能对字符串进行精确查询，

23、此时可以使用如果不能对字符串进行精确查询，此时可以使用like运算符与通配符实现模糊查询，运算符与通配符实现模糊查询，like运算符的运算符的语法格式如下。语法格式如下。字符串表达式字符串表达式 not like 模式模式5.2.5 5.2.5 使用使用likelike进行模糊查询进行模糊查询 模式是一个字符串，其中包含普通字符和通配模式是一个字符串，其中包含普通字符和通配符。在符。在MySQL中常用的通配符如表所示。中常用的通配符如表所示。5.2.5 5.2.5 使用使用likelike进行模糊查询进行模糊查询 模糊查询模糊查询“%”或者或者“_”字符时，需要将字符时，需要将“%”或者或者“

24、_”字符转义，例如检索学生姓名中所有带字符转义，例如检索学生姓名中所有带“_”的学生信息，可以使用下面的的学生信息，可以使用下面的SQL语句，其语句，其中中new_student表在表记录的更新操作章节中创表在表记录的更新操作章节中创建。执行结果如图建。执行结果如图5-23所示。所示。select * from new_student where student_name like %_%;5.2.5 5.2.5 使用使用likelike进行模糊查询进行模糊查询 如果不想使用如果不想使用“”作为转义字符，可以使用作为转义字符，可以使用escape关键字自定义一个转义字符，例如下面的关键字自定义

25、一个转义字符，例如下面的SQL语句使用字符语句使用字符“!”作为转义字符。作为转义字符。select * from new_student where student_name like %!_% escape !;5.2.5 5.2.5 使用使用likelike进行模糊查询进行模糊查询 select语句的查询结果集的排序由数据库系统语句的查询结果集的排序由数据库系统动态确定，往往是无序的，动态确定，往往是无序的，order by子句用于对子句用于对结果集排序。在结果集排序。在select语句中添加语句中添加order by子句，子句，就可以使结果集中的记录按照一个或多个字段的就可以使结果集中

26、的记录按照一个或多个字段的值进行排序，排序的方向可以是升序（值进行排序，排序的方向可以是升序（asc）或降）或降序（序（desc）。）。order by子句的语法格式如下。子句的语法格式如下。order by 字段名字段名1 asc|desc ,字段名字段名n asc|desc 5.3 5.3 使用使用order byorder by子句对结果集排序子句对结果集排序 聚合函数用于对一组值进行计算并返回一个汇聚合函数用于对一组值进行计算并返回一个汇总值，常用的聚合函数有累加求和总值，常用的聚合函数有累加求和sum()函数、平函数、平均值均值avg()函数、统计记录的行数函数、统计记录的行数cou

27、nt()函数、最函数、最大值大值max()函数和最小值函数和最小值min()函数等。函数等。5.4 5.4 使用聚合函数汇总结果集使用聚合函数汇总结果集 使用使用count()对对NULL值统计时，值统计时，count()函数将函数将忽略忽略NULL值。值。sum()函数、函数、avg()函数、函数、max()以以及及min()函数等统计函数，统计数据时也将忽略函数等统计函数，统计数据时也将忽略NULL值。值。5.4 5.4 使用聚合函数汇总结果集使用聚合函数汇总结果集 group by子句将子句将查询结果按照某个查询结果按照某个字段（或多个字段）字段（或多个字段）进行分组（字段值进行分组（字

28、段值相同的记录作为一相同的记录作为一个分组。个分组。1 12 23 3group by子句与子句与having子句子句group by子句与聚合函数子句与聚合函数group by子句与子句与group_concat()函数函数4group by子句与子句与with rollup选项选项5.5 5.5 使用使用group bygroup by子句对记录分组统计子句对记录分组统计 group by子句通常与聚合函数一起使用。子句通常与聚合函数一起使用。 group by子句的语法格式如下。子句的语法格式如下。group by 字段列表字段列表 having条件表达式条件表达式 with rollu

29、p 任务布置任务布置7：完成本书场景描述：完成本书场景描述7的任务要求。的任务要求。5.5 5.5 使用使用group bygroup by子句对记录分组统计子句对记录分组统计 例如统计每一个班的学生人数。例如统计每一个班的学生人数。 例如统计每个学生已经选修多少门课程，该生例如统计每个学生已经选修多少门课程，该生的最高分、最低分、总分及平均成绩。的最高分、最低分、总分及平均成绩。5.5.1 group by5.5.1 group by子句与聚合函数子句与聚合函数 having子句用于设置分组或聚合函数的过滤筛子句用于设置分组或聚合函数的过滤筛选条件，选条件，having子句通常与子句通常与g

30、roup by子句一起使子句一起使用。用。having子句语法格式与子句语法格式与where子句语法格式子句语法格式类似，类似，having子句语法格式如下。子句语法格式如下。having条件表达式条件表达式其中条件表达式是一个逻辑表达式，用于指定分其中条件表达式是一个逻辑表达式，用于指定分组后的筛选条件。组后的筛选条件。5.5.2 group by5.5.2 group by子句与子句与havinghaving子句子句 例如检索平均成绩高于例如检索平均成绩高于70分的学生信息及平均分的学生信息及平均成绩。成绩。5.5.2 group by5.5.2 group by子句与子句与havingh

31、aving子句子句 下面下面select语句的语法格式中，语句的语法格式中，select语句的执语句的执行过程为：首先使用行过程为：首先使用where子句对结果集进行过子句对结果集进行过滤筛选，接着滤筛选，接着group by子句分组子句分组where子句的输子句的输出，最后出，最后having子句从分组的结果中再进行筛选。子句从分组的结果中再进行筛选。select 字段列表字段列表from 数据源数据源where条件表达式条件表达式 group by 分组字段分组字段 having条件表达式条件表达式 5.5.2 group by5.5.2 group by子句与子句与havinghavin

32、g子句子句 group_concat()函数的功能是将集合中的字符函数的功能是将集合中的字符串连接起来，此时串连接起来，此时group_concat()函数的功能与函数的功能与字符串连接函数字符串连接函数concat()的功能相似。的功能相似。5.5.3 group by5.5.3 group by子句与子句与group_concat()group_concat()函数函数 group_concat()函数的功能是将集合中的字符函数的功能是将集合中的字符串连接起来，此时串连接起来，此时group_concat()函数的功能与函数的功能与字符串连接函数字符串连接函数concat()的功能相似。的

33、功能相似。例如下面例如下面SQL语句中的语句中的group_concat()函数以及函数以及concat()函数负责将集合中函数负责将集合中(java, 程序程序, 设计设计)三个字符串连接起来。三个字符串连接起来。select group_concat(java,程序程序,设计设计),concat(java,程序程序,设计设计);5.5.3 group by5.5.3 group by子句与子句与group_concat()group_concat()函数函数 group_concat()函数还可以按照分组字段，函数还可以按照分组字段，将另一个字段的值（将另一个字段的值（NULL值除外）使用

34、逗号连值除外）使用逗号连接起来。接起来。 concat()却函数没有提供这样的功能。却函数没有提供这样的功能。 5.5.3 group by5.5.3 group by子句与子句与group_concat()group_concat()函数函数 group by子句将结果集分为若干个组，使用聚子句将结果集分为若干个组，使用聚合函数可以对每个组内的数据进行信息统计，有合函数可以对每个组内的数据进行信息统计，有时需要对各个组进行汇总运算，则需要在每个分时需要对各个组进行汇总运算，则需要在每个分组后加上一条汇总记录，这个任务可以通过组后加上一条汇总记录，这个任务可以通过with rollup选项实现

35、。选项实现。5.5.4 group by5.5.4 group by子句与子句与with rollupwith rollup选项选项5.6 5.6 合并结果集合并结果集 使用使用unionunion可以将多个可以将多个select select 语句的查询结语句的查询结果集组合成一个结果集。果集组合成一个结果集。select select 字段列表字段列表1 from table1 1 from table1 union allunion allselect select 字段列表字段列表2 from table2.2 from table2.说明：字段列表说明：字段列表1 1与字段列表与字段

36、列表2 2的字段个数必须相的字段个数必须相同，且具有相同的数据类型。合并产生的新结果同，且具有相同的数据类型。合并产生的新结果集的字段名与字段列表集的字段名与字段列表1 1中的字段名对应。中的字段名对应。 union 与与 union all 的区别：的区别： 当使用当使用union时，时，MySQL 会筛选掉会筛选掉select结果结果集中重复的记录（在结果集合并后会对新产生的集中重复的记录（在结果集合并后会对新产生的结果集进行排序运算，效率稍低）。而使用结果集进行排序运算，效率稍低）。而使用union all时，时，MySQL会直接合并两个结果集，效率高于会直接合并两个结果集，效率高于un

37、ion。如果可以确定合并前的两个结果集中不包。如果可以确定合并前的两个结果集中不包含重复的记录，建议使用含重复的记录，建议使用union all。5.6 5.6 合并结果集合并结果集 如果一个如果一个select语句能够返回单个值或者一列语句能够返回单个值或者一列值，且该值，且该select语句嵌套在另一个语句嵌套在另一个SQL语句中语句中（例如（例如select语句、语句、insert语句、语句、update语句或者语句或者delete语句）中，那么该语句）中，那么该select语句称为子查询语句称为子查询（也叫内层查询），包含子查询的（也叫内层查询），包含子查询的SQL语句称为语句称为主查

38、询（也叫外层查询）。为了标记子查询与主主查询（也叫外层查询）。为了标记子查询与主查询之间的关系，通常将子查询写在小括号内。查询之间的关系，通常将子查询写在小括号内。 子查询分为相关子查询与非相关子查询。子查询分为相关子查询与非相关子查询。 5.7 5.7 子查询子查询 子查询一般用在子查询一般用在主查询的主查询的where子句子句或或having子句中，子句中，与比较运算符或者逻与比较运算符或者逻辑运算符一起构成辑运算符一起构成where筛选条件或筛选条件或having筛选条件。筛选条件。 1 12 23 3子查询与子查询与in运算符运算符子查询与比较运算符子查询与比较运算符子查询与子查询与e

39、xists逻辑运算符逻辑运算符4子查询与子查询与any运算符运算符5子查询与子查询与all运算符运算符5.7 5.7 子查询子查询 如果子查询返回单个值，则可以将一个表达式如果子查询返回单个值，则可以将一个表达式的值与子查询的结果集进行比较。的值与子查询的结果集进行比较。 子查询可以仅仅使用自己定义的数据源，也可子查询可以仅仅使用自己定义的数据源，也可以以“直接引用直接引用”主查询中的数据源，但两者意义主查询中的数据源，但两者意义完全不同。完全不同。5.7.1 5.7.1 子查询与比较运算符子查询与比较运算符 如果子查询中仅仅使用了自己定义的数据源，如果子查询中仅仅使用了自己定义的数据源，这种

40、查询是非相关子查询，非相关子查询是独立这种查询是非相关子查询，非相关子查询是独立于外部查询的子查询，子查询总共执行一次，执于外部查询的子查询，子查询总共执行一次，执行完毕后将值传递给主查询。行完毕后将值传递给主查询。 如果子查询中使用了主查询的数据源，这种如果子查询中使用了主查询的数据源，这种查询是相关子查询，此时主查询的执行与相关子查询是相关子查询，此时主查询的执行与相关子查询的执行相互依赖。查询的执行相互依赖。5.7.1 5.7.1 子查询与比较运算符子查询与比较运算符 子查询经常与子查询经常与in运算符一起使用，用于将一个运算符一起使用，用于将一个表达式的值与子查询返回的一列值进行比较，

41、如表达式的值与子查询返回的一列值进行比较，如果表达式的值是此列中的任何一个值，则条件表果表达式的值是此列中的任何一个值，则条件表达式的结果为达式的结果为true；否则为；否则为false。5.7.2 5.7.2 子查询与子查询与inin运算符运算符 exists逻辑运算符用于检测子查询的结果集是逻辑运算符用于检测子查询的结果集是否包含有记录，如果结果集中至少包含一条记录，否包含有记录，如果结果集中至少包含一条记录，则则exists的结果为的结果为true；否则为；否则为false。在。在exists前面加上前面加上not时，与上述结果恰恰相反。时，与上述结果恰恰相反。5.7.3 5.7.3 子

42、查询与子查询与existsexists逻辑运算符逻辑运算符 any运算符通常与比较运算符一起使用。使用运算符通常与比较运算符一起使用。使用any运算符时，通过比较运算符将一个表达式的运算符时，通过比较运算符将一个表达式的值与子查询返回的一列值逐一进行比较，若某次值与子查询返回的一列值逐一进行比较，若某次的比较结果为的比较结果为true，则整个表达式的值为，则整个表达式的值为true；否则为否则为false。any逻辑运算符的语法格式如下。逻辑运算符的语法格式如下。表达式表达式 比较运算符比较运算符 any(子查询子查询)5.7.4 5.7.4 子查询与子查询与anyany运算符运算符 举例来说

43、：当比较运算符为大于号（举例来说：当比较运算符为大于号（）时，）时，“表达式表达式 any(子查询子查询)”表示至少大于子查询结表示至少大于子查询结果集中的某一个值（或者说大于结果集中的最小果集中的某一个值（或者说大于结果集中的最小值），那么整个表达式的结果为值），那么整个表达式的结果为true。5.7.4 5.7.4 子查询与子查询与anyany运算符运算符 all运算符通常与比较运算符一起使用。使用运算符通常与比较运算符一起使用。使用all运算符时，通过比较运算符将一个表达式的值运算符时，通过比较运算符将一个表达式的值与子查询返回的一列值逐一进行比较，若每次的与子查询返回的一列值逐一进行比

44、较，若每次的比较结果都为比较结果都为true，则整个表达式的值为，则整个表达式的值为true；否则为否则为false。any逻辑运算符的语法格式如下。逻辑运算符的语法格式如下。表达式表达式 比较运算符比较运算符 all(子查询子查询)5.7.5 5.7.5 子查询与子查询与allall运算符运算符 举例来说：当比较运算符为大于号（举例来说：当比较运算符为大于号（）时，）时，“表达式表达式 all(子查询子查询)”表示大于子查询结果集表示大于子查询结果集中的任何一个值（或者说大于结果集中的最大中的任何一个值（或者说大于结果集中的最大值），那么整个表达式的结果为值），那么整个表达式的结果为true

45、。5.7.5 5.7.5 子查询与子查询与allall运算符运算符任务布置任务布置8：书写本章节的：书写本章节的select代码。代码。5.8 5.8 选课系统综合查询选课系统综合查询5.9 5.9 使用正则表达式模糊查询使用正则表达式模糊查询 与与likelike运算符相似，正则表达式主要用于判运算符相似，正则表达式主要用于判断一个字符串是否与给定的模式匹配，但正则表断一个字符串是否与给定的模式匹配，但正则表达式的模式匹配功能比达式的模式匹配功能比likelike运算符的模式匹配功运算符的模式匹配功能更为强大，且更加灵活。使用正则表达式进行能更为强大，且更加灵活。使用正则表达式进行模糊查询时

46、，需要使用模糊查询时，需要使用regexpregexp关键字，语法格式关键字，语法格式如下。如下。字段名字段名 not regexp binary not regexp binary 正则表达式正则表达式 5.9 5.9 使用正则表达式模糊查询使用正则表达式模糊查询 正则表达式由一些普通字符和一些元字符构正则表达式由一些普通字符和一些元字符构成，普通字符包括大写字母、小写字母和数字，成，普通字符包括大写字母、小写字母和数字，而元字符具有特殊的含义。在最简单的情况下，而元字符具有特殊的含义。在最简单的情况下，一个正则表达式是一个不包含元字符的字符串。一个正则表达式是一个不包含元字符的字符串。例如

47、正则表达式例如正则表达式testingtesting中没有包含任何元字中没有包含任何元字符，它可以匹配符，它可以匹配testingtesting、123testing123testing等字等字符串。符串。 5.9 5.9 使用正则表达式模糊查询使用正则表达式模糊查询常常用用的的元元字字符符 例如检索含有例如检索含有“java”的课程信息，可以使用的课程信息，可以使用下面的下面的SQL语句。语句。select * from course where course_name regexp java;5.9 5.9 使用正则表达式模糊查询使用正则表达式模糊查询 例如检索以例如检索以“程序设计程序设

48、计”结尾的课程信息，可结尾的课程信息，可以使用下面的以使用下面的SQL语句。语句。select * from course where course_name regexp 程序设计程序设计$;5.9 5.9 使用正则表达式模糊查询使用正则表达式模糊查询 例如检索以例如检索以“j”开头，以开头，以“程序设计程序设计”结尾的结尾的课程信息，可以使用下面的课程信息，可以使用下面的SQL语句。语句。select * from course where course_name regexp j.*程序设计程序设计$;5.9 5.9 使用正则表达式模糊查询使用正则表达式模糊查询 例如检索学生联系方式中以

49、例如检索学生联系方式中以15开头或者开头或者18开开头，且后面跟着头，且后面跟着9位数字的学生信息，可以使用位数字的学生信息，可以使用下面的下面的SQL语句。语句。select * from student where student_contact regexp 1580-99;5.9 5.9 使用正则表达式模糊查询使用正则表达式模糊查询 大多数时候，使用大多数时候，使用like关键字或者正则表达式关键字或者正则表达式对字符串进行模糊查询，需要对表进行全表扫描，对字符串进行模糊查询，需要对表进行全表扫描，检索效率较低。检索效率较低。 通过全文检索，可以有效提升字符串检索效率。通过全文检索，可

50、以有效提升字符串检索效率。5.10 5.10 全文检索全文检索 简单地说，简单地说，MySQL全文检索使用特定的分词全文检索使用特定的分词技术、利用查询关键字和查询字段内容之间的相技术、利用查询关键字和查询字段内容之间的相关度进行检索，通过全文索引提高文本匹配的速关度进行检索，通过全文索引提高文本匹配的速度。全文检索的语法格式如下。度。全文检索的语法格式如下。select字段列表字段列表from 表名表名where match (全文索引字段全文索引字段1,全文索引字段全文索引字段2,.) against (搜索关键字搜索关键字 全文检索方式全文检索方式 )5.10 5.10 全文检索全文检索

51、 在在2011年发布的年发布的5.6版本中版本中InnoDB存储引存储引擎表实现了全文检索擎表实现了全文检索的支持，这将大幅提的支持，这将大幅提升升InnoDB存储引擎的存储引擎的字符串检索效率，实字符串检索效率，实现更快速、更高质量现更快速、更高质量的模糊查询。的模糊查询。 1 12 23 3全文检索方式全文检索方式全文检索的简单应用全文检索的简单应用布尔检索模式的复杂应用布尔检索模式的复杂应用4MySQL全文检索的注意事项全文检索的注意事项5InnoDB表的全文检索表的全文检索5.10 5.10 全文检索全文检索create table book(isbn char(20) primary

52、 key,name char(100) not null,brief_introduction text not null,price decimal(6,2),publish_time date not null,unique index isbn_unique (isbn),index name_index (name (20),fulltext index brief_fulltext (name,brief_introduction),index complex_index (price,publish_time) engine=MyISAM default charset=gbk;

53、创建书籍book表（注意该表为注意该表为MyISAM存储引擎），在存储引擎），在book表中的字段组合（name, brief_introduction）创建一个全文索引 。5.10.1 5.10.1 全文检索的简单应用全文检索的简单应用insert into book(isbn,name,brief_introduction,price,publish_time) values(978-7-115-25626-3,PHP Fundamentals & Practices,Web Database Applications MySQL offers web developers a m

54、ixture of theoretical and practical information on creating web database applications. ,42.0,2012-7-1), (978-7-115-25626-4,MySQL COOKBOOK,The MySQL database management system has become quite popular in recent years.,128.0,2008-1-1), (978-7-115-25626-5,Beginning MySQL, MySQL is especially heavily us

55、ed in combination with a web server for constructing database-backed web sites that involve dynamic content generation.,98.0,2008-1-1); 向向book表插入测试数据。表插入测试数据。5.10.1 5.10.1 全文检索的简单应用全文检索的简单应用 例如检索书名或者简介中涉及到例如检索书名或者简介中涉及到practices单词单词的所有图书信息，可以使用下面的的所有图书信息，可以使用下面的SQL语句。语句。select * from book where matc

56、h (name,brief_introduction) against (practices)G5.10.1 5.10.1 全文检索的简单应用全文检索的简单应用 例如检索书名或者简介中涉及到例如检索书名或者简介中涉及到practices或者或者cookbook单词的所有图书信息，可以使用下面单词的所有图书信息，可以使用下面的的SQL语句。语句。select * from book where match (name,brief_introduction) against (practices cookbook)G5.10.1 5.10.1 全文检索的简单应用全文检索的简单应用 检索书名或者简介

57、中涉及到检索书名或者简介中涉及到mysql单词的所有单词的所有图书信息，将检索失败。图书信息，将检索失败。 MySQL在进行全文检索时，默认情况下将忽在进行全文检索时，默认情况下将忽略权重超过略权重超过50%的记录，的记录，50%称为阈值（注意读称为阈值（注意读作：作：y zh），）， 因此检索失败。因此检索失败。5.10.1 5.10.1 全文检索的简单应用全文检索的简单应用 如果希望忽略阈值的因素，例如检索书名或者如果希望忽略阈值的因素，例如检索书名或者简介中涉及到简介中涉及到“mysql”单词的所有图书信息，可单词的所有图书信息，可以使用下面的以使用下面的SQL语句，执行结果如图语句，执

58、行结果如图5-66所示，所示，该该SQL语句使用了布尔检索模式（稍后讲解）。语句使用了布尔检索模式（稍后讲解）。select * from book where match (name,brief_introduction) against (mysql in boolean mode)G5.10.1 5.10.1 全文检索的简单应用全文检索的简单应用 检索书名或者简介中涉及到检索书名或者简介中涉及到php单词的所有图单词的所有图书信息，将检索失败。书信息，将检索失败。 这是由于这是由于MySQL全文检索对搜索关键字的最全文检索对搜索关键字的最小（默认值为小（默认值为4）以及最大长度（默认值为）以及最大长度（默认值为84）进行了