SQL 数据基础原理 3

数据查询学习目标掌握select查询语句基本格式掌握基本的无条件查询掌握条件查询的使用掌握使用聚合函数实现数据的统计掌握分组和排序查询的使用掌握多表查询的使用掌握子查询的使用掌握使用正则表达式进行模糊查询理解查询优化的使用章节内容7.1基本查询语句 7.2单表查询 7.3多表查询 7.4子查询 7.5使用正则表达式进行模糊查询 7.6合并结果集7.7查询优化 7.1基本查询语句

7.1基本查询语句 查询是从数据库表中筛选出符合条件的数据，查询得到的结果集也是关系模式，以表的形式组织和显示数据。查询的结果集一般不被存储，每次查询都会从数据库表中提取数据，并按照要求进行计算，分组和统计等。7.1基本查询语句 在MySQL中使用SELECT语句来实现数据查询。SELECT语句的基本语法格式如下：SELECT[ALL|DISTINCT|DISTINCTROW]select_expr[,select_expr...][FROMtable_references[WHEREwhere_condition][GROUPBY{col_name|expr|position},...[WITHROLLUP]][HAVINGwhere_condition][ORDERBY{col_name|expr|position}[ASC|DESC],...[WITHROLLUP]][LIMIT{[offset,]row_count|row_countOFFSEToffset}][INTOOUTFILE'file_name'|INTODUMPFILE'file_name'|INTOvar_name[,var_name]]7.1基本查询语句 语法说明如下：SELECT子句：表示从表中查询指定的列。FROM子句：表示查询的数据源，可以是表或视图。WHERE子句：用于指定查询筛选条件。GROUPBY子句：用于将查询结果按指定的列进行分组；其中HAVING为可选参数，用于对分组后的结果集进行筛选。ORDERBY子句：用于对查询结果集按指定的列进行排序。LIMIT子句：用于限制查询结果集的行数。参数OFFSET为偏移量，当OFFSET值为0时，表示从查询结果的第1条记录开始，如果OFFSET为1时，表示查询结果从第2条记录开始。INTO子句用于保存查询结果，其中INTOOUTFILE用于将查询结果全部保存到文件中，INTODUMPFILE只保存一行，INTOvar_name用于将查询结果保存到变量var_name。7.2单表查询

7.2.1查询所有字段数据 7.2.2查询指定字段数据 7.2.3去掉重复记录 7.2.4表达式查询 7.2.5设置别名 7.2.6查询指定记录 7.2.7带IN关键字的查询 7.2.8带BETWEENAND关键字的查询

7.2.9带LIKE的模糊查询 7.2.10带ISNULL空值查询 7.2.11带AND|OR的多条件查询 7.2.12聚合函数查询 7.2.13GROUPBY分组查询 7.2.14ORDERBY排序查询 7.2.15LIMIT限制结果集返回的行数 7.2.1查询所有字段数据 查询所有字段是指查询表中所有字段的数据，在MySQL中可以使用“*”来代表所有的列。查询所有字段的语法格式如下：SELECT*FROM表名;【例7-1】查询班级代码表的全部数据。代码如下：select*frombjdmb;也可用下面代码实现：selectbjbh,bmh,bjzwmc,bjrsfrombjdmb;注意：用“*”来表示表中所有列时，查询出来的字段顺序与表中字段顺序一致，如果要求显示的字段顺序与表中字段顺序不一致时，只能用后一种方法实现。7.2.2查询指定字段数据 查询表中指定字段，只要在SELECT后面指定要查询的列名即可，多列之间用“，”分隔。【例7-2】查询bjdmb表中的bjbh字段和bjzwmc字段。代码如下：selectbjbh,bjzwmcfrombjdmb;7.2.3去掉重复记录如果希望查询结果没有重复值，可以使用DISTINCT或DISTINCTROW关键字从结果集中除去重复的行。基本语法如下：SELECTDISTINCT|DISTINCTROW字段名FROM表名;【例7-3】查询学生选课表中学生的学号，并去掉重复记录。代码如下：selectdistinctxhfromxsxkb;7.2.4表达式查询在使用查询语句进行查询时，可以使用表达式作为查询的结果字段。基本语法如下：SELECT表达式...

FROM表名【例7-4】查询每个女生的姓名和年龄。代码如下：selectxm,year(now())-year(csrq)fromxsjbxxbwherexb='女';7.2.5设置别名 默认情况下，查询结果中显示的字段名就是查询的字段名。当希望查询结果中显示的字段名使用自己选择的字段名时，可以在字段名后使用AS子句。AS用于为其前面的字段、表达式、函数等设置别名，也可省略AS使用空格代替。基本语法如下：（1）字段设置别名SELECT字段1[AS]别名1,字段2[AS]别名2[,…]FROM表名（2）表设置别名SELECT表别名.字段[,…]FROM表名[AS]表别名【例7-5】使用别名显示bjdmb表中的班级号、部门号、班级名。代码如下：selectbjbhas班级号,bmhas部门号,bjzwmcas班级名->frombjdmb;7.2.6查询指定记录 在实际应用中，要获取需要的数据，通常会指定查询条件，以筛选出所需的数据，这种查询方式称为条件查询。在SELECT语句中，查询条件由WHERE子句指定。语法格式如下：WHEREwhere_condition其中，where_condition为筛选条件表达式，该表达式可以由列名、常量、变量、函数及子查询组成。可以使用的运算符包括比较运算符、逻辑运算符、IN、LIKE、BETWEENAND、ISNULL运算符等。7.2.6查询指定记录 表7-1比较运算符使用比较运算符限定查询条件时，基本语法格式如下：WHERE表达式1比较运算符表达式2【例7-6】查询学生基本信息表中姓名为曹宇的学生的学号，姓名，性别与出生日期。代码如下：mysql>selectxh,xm,xb,csrq->fromxsjbxxb->wherexm='曹宇';7.2.6查询指定记录 【例7-7】查询2005年以后出生的学生的学号，姓名，性别与出生日期。代码如下：mysql>selectxh,xm,xb,csrq->fromxsjbxxb->wherecsrq>='2005-1-1';7.2.7带IN关键字的查询 IN关键字可以判断某个字段的值是否在于指定的集合中。如果字段的值在集合中，则满足查询条件，该记录将被查询出来；如果不在集合中，则不满足查询条件。其语法格式如下：SELECT*FROM表名WHERE条件[NOT]IN(元素1,元素2,…,元素n);其中，NOT是可选参数，表示不在指定的集合中，多个元素间用逗号间隔，字符型数据要加单引号。【例7-8】查询选修了课程代码为00202117或00202118的学生选课信息。代码如下：mysql>selectxh,kcdm,cj->fromxsxkb->wherekcdmin('00202117','00202118');7.2.7带IN关键字的查询 注意：使用IN运算符比较，等价由OR运算符连接多个表达式。但使用IN构建搜索条件的语法更简化；不允许在值列表中出现NULL值数据。上面示例也可写成：mysql>selectxh,kcdm,cj->fromxsxkb->wherekcdm='00202117'orkcdm='00202118';7.2.8带BETWEENAND关键字的查询BETWEENAND关键字可以判断某个字段的值是否在指定的范围内。其语法如下：SELECT*FROM表名WHERE条件[NOT]BETWEEN取值1AND取值2;其中，取值1和取值2是包含在内的；NOT是可选参数，表示不在指定的范围内。【例7-9】查询选修了成绩在80到90之间的学生学号，课程代码和成绩信息。代码如下：mysql>selectxh,kcdm,cj->fromxsxkb->wherecjbetween80and90;7.2.9带LIKE的模糊查询实际中当需要查询的条件只能提供不完全确定的部分信息时，就需要使用LIKE运算符实现字符串的模糊查询。基本语法格式为：WHERE列名[NOT]LIKE‘字符串’[ESCAPE‘转义字符’]使用LIKE进行模式匹配时，常使用通配符_和%，可进行模糊查询。“%”代表0个或多个字符，“_”代表单个字符。例如，如下的通配符示例。（1）LIKE'AB%'：匹配以“AB”开始的任意字符串。（2）LIKE'%AB'：匹配以“AB”结束的任意字符串。（3）LIKE'%AB%'：匹配包含“AB”的任意字符串。（4）LIKE'_AB'：匹配以“AB”结束的三个字符的字符串。由于MySQL默认不区分大小写，要区分大小写时需要更换字符集的校对规则。7.2.9带LIKE的模糊查询【例7-10】查找学生基本信息表中姓“张”的学生学号，姓名和性别。代码如下：mysql>selectxh,xm,xb->fromxsjbxxb->wherexmlike'张%';【例7-11】查找课程代码表中课程名称中倒数第2个字符是“技”的课程信息。代码如下：mysql>select*->fromkcdmb->wherekcmclike'%技_';7.2.9带LIKE的模糊查询当查询的字符串中含通配符时，MySQL采用转义字符来实现，默认的转义字符为“\”。【例7-12】查询学生基本信息表中名字包含下画线的学生学号和姓名。代码如下：mysql>selectxh,xm->fromxsjbxxb->wherexmlike'%\_%';执行结果：Emptyset。因为本表中没有满足条件的结果，返回空记录集。注意：在MySQL默认的转义字符为“\”，如果使用其他转义字符时，需要加关键字ESCAPE。如上例使用“@”进行转义的代码如下：mysql>selectxh,xm->fromxsjbxxb->wherexmlike'%@_%'escape'@';7.2.10带ISNULL空值查询 ISNULL关键字可以用来判断字段的值是否为空值（NULL）。其语法格式如下：WHERE表达式IS[NOT]NULL当不使用NOT时，若表达式的值为空值，返回TRUE，否则返回FALSE；当使用NOT时，结果刚好相反。【例7-13】查询学生选课表中成绩为空的记录。代码如下：mysql>select*->fromxsxkb->wherecjisnull;执行结果：Emptyset。注意：一个字段值是空值或者不是空值，要表示为“isnull“或“isnotnull”。不能表示为：“=null”或“<>null”。7.2.11带AND|OR的多条件查询AND|OR关键字可以用来联合多个条件进行查询。使用AND关键字时，只有同时满足所有查询条件的记录会被查询出来。AND关键字的语法格式如下：SELECT*FROM数据表名WHERE条件1AND条件2[…AND条件表达式n];带OR的多条件查询，是指只要符合多条件中的一个，记录就会被搜索出来；如果不满足这些查询条件中的任何一个，这样的记录将被排除掉。OR关键字的语法格式如下：SELECT*FROM数据表名WHERE条件1OR条件2[…OR条件表达式n];7.2.11带AND|OR的多条件查询【例7-14】查找学生基本信息表中2005年以后出生的男生的学号，姓名，性别和出生日期。代码如下：mysql>selectxh,xm,xb,csrq->fromxsjbxxb->wherexb='男'andcsrq>='2005-1-1';【例7-15】查找学生基本信息表中女生或者2006年以后出生的学生的学号，姓名，性别和出生日期。代码如下：mysql>selectxh,xm,xb,csrq->fromxsjbxxb->wherexb='女'orcsrq>='2006-1-1';7.2.12聚合函数查询表7-2常用的聚合函数7.2.12聚合函数查询1.COUNT()函数函数用于统计组中满足条件的行数或总行数，返回SELECT语句检索到的行中非NULL值的数目，若找不到匹配的行，则返回0。语法格式为：COUNT({[ALL|DISTINCT]表达式}|*)其中，表达式的数据类型可以是除BLOB或TEXT之外的任何类型。ALL表示对所有值进行运算，DISTINCT表示去除重复值，默认为ALL。对于参数“*”，返回选择集合中所有行的数目，包含NULL值的行。对于除“*”以外的任何参数，返回所选择集合中非NULL值的行的数目。【例7-16】查询学生基本信息表的记录总数。代码如下：mysql>selectcount(*)as学生人数fromxsjbxxb;7.2.12聚合函数查询2.SUM()函数和AVG()函数SUM()函数和AVG()函数分别用于计算表达式中所有值项的总和与平均值，语法格式为：SUM/AVG([ALL|DISTINCT]表达式)【例7-17】计算学号为“202320107101”的学生的所选课程的总成绩。代码如下：mysql>selectsum(cj)as总成绩

->fromxsxkb->wherexh='202320107101';7.2.12聚合函数查询【例7-18】计算学号为“202320107101”的学生的所选课程的平均成绩。代码如下：mysql>selectavg(cj)as平均成绩

->fromxsxkb->wherexh='202320107101';7.2.12聚合函数查询3.MAX()函数和MIN()函数MAX和MIN分别用于计算表达式中所有值项的最大值与最小值，语法格式为：MAX/MIN([ALL|DISTINCT]表达式)【例7-19】计算课程号为“00202117”的课程的最高成绩。代码如下：mysql>selectmax(cj)->fromxsxkb->wherekcdm='00202117';【例7-20】查询学生基本信息表中年龄最大的学生的出生日期。代码如下：mysql>selectmin(csrq)->fromxsjbxxb;7.2.13GROUPBY分组查询如果需要按某一列数据的值进行分组，在分组的基础上再进行查询，就要使用GROUPBY子句，它的语法格式如下：

GROUPBY<组合表达式>[WITHROLLUP]][HAVINGwhere_condition]其中，组合表达式可以是字段名，表达式，查询列的次序。WITHROLLUP表示对分组的数据进行分类汇总。HAVING为可选参数，用于对分组后的结果集进行筛选，where_condition为筛选条件。通常GROUPBY和聚合函数一起使用，可以统计出某个分组中的项数、最大最小值等。【例7-21】查询学生基本信息表中男生和女生的人数。代码如下：mysql>selectxbas性别,count(*)as人数

->fromxsjbxxb->groupbyxb;7.2.13GROUPBY分组查询注意：如果MySQL中全局变量sql_mode的值包含ONLY_FULL_GROUP_BY，对于GROUPBY聚合操作，如果在SELECT中的列，没有在GROUPBY分组中出现，那么这个SQL语句是不合法的，因为列不在GROUPBY子句中。简而言之，在查询中，查询的列除了聚合函数外，其余列必须包含在分组的列中。SELECT查询的字段列表只能是GROUPBY分组的字段，或使用了聚合函数的非分组字段。本题也可以用下面代码实现：（1）mysql>selectxbas性别,count(*)as人数

->fromxsjbxxb->groupby1;其中数字1代表xb字段在SELECT查询列表中的次序。（2）mysql>selectxbas性别,count(*)as人数

->fromxsjbxxb->groupby性别;其中性别为字段xb的别名。7.2.13GROUPBY分组查询GROUPBY和WITHROLLUP一起使用，可以输出每一类分组的汇总值。【例7-22】查询学生基本信息表中男生和女生的人数以及学生总人数。代码如下：mysql>selectxbas性别,count(*)as人数

->fromxsjbxxb->groupbyxbwithrollup;【例7-23】查询每个学生的选课门数。代码如下：mysql>selectxhas学号,count(*)as选课门数

->fromxsxkb->groupbyxh;7.2.13GROUPBY分组查询对查询的数据分组时，可以利用HAVING根据条件进行数据筛选，它与前面学习过的WHERE功能相同，但是在实际运用时两者有一定的区别。（1）WHERE操作是从数据表中获取数据符合条件的数据，而HAVING是根据条件对已分组的数据进行操作。（2）HAVING位于GROUPBY子句后，而WHERE位于GROUPBY子句之前。（3）HAVING关键字后可以使用聚合函数，且只能跟GROUPBY一起使用，而WHERE则不可以。通常情况下，HAVING关键字与GROUPBY一起使用，对分组后的结果进行筛选过滤。当一个语句中同时出现WHERE子句、GROUPBY子句和HAVING子句，执行顺序如下。（1）执行WHERE子句，从数据表中选取满足条件的数据行。（2）GROUPBY子句对选取的数据行进行分组。（3）执行聚合函数。（4）执行HAVING子句，选取满足条件的分组。7.2.13GROUPBY分组查询【例7-24】查询选修了3门及以上课程的男同学的学生学号，选课门数。代码如下：mysql>selectxsxkb.xhas学号,count(*)as选课门数

->fromxsxkb,xsjbxxb->wherexsxkb.xh=xsjbxxb.xh->andxb='男'->groupbyxsxkb.xh->havingcount(*)>=3;7.2.14ORDERBY排序查询使用ORDERBY可以对查询的结果进行升序（ASC）和降序（DESC）排列，在默认情况下，ORDERBY按升序输出结果。如果要按降序排列可以使用DESC来实现。语法格式如下：ORDERBY字段名[ASC|DESC];【例7-25】查询学生基本信息表女生的信息，要求显示学号，姓名性别，出生日期，并按出生日期降序排序。代码如下：mysql>selectxh,xm,xb,csrq->fromxsjbxxb->wherexb='女'->orderbycsrqdesc;7.2.15LIMIT限制结果集返回的行数 查询数据时，可能会查询出很多的记录。LIMIT子句可以对查询结果的记录条数进行限定，控制它输出的行数。语法格式如下：LIMIT[OFFSET,]N;N：表示限定获取的最大记录数量。仅含此参数时，表示从第1条记录开始获取N条数据。OFFSET：表示偏移量，用于设置从哪条记录开始，默认第1条记录的偏移量值为0，第2条记录的偏移量值为1，依次类推。【例7-26】查询学生基本信息表中年龄最大的学生的出生日期。代码如下：mysql>selectcsrq->fromxsjbxxb->orderbycsrq->limit1;7.2.15LIMIT限制结果集返回的行数 【例7-27】查询每个学生的课程平均成绩，并显示平均成绩最高的前3名的学生学号，平均成绩。代码如下：mysql>selectxh,avg(cj)->fromxsxkb->groupbyxh->orderbyavg(cj)desc->limit3;【例7-28】显示部门代码表的第4至第7条记录。代码如下：mysql>select*frombmdmb->limit3,4;7.3多表查询

7.3.1内连接 7.3.2外连接 7.3.3交叉连接 7.3.4自连接 7.3多表查询 在实际应用中，很多情况下用户需要的数据并不全在一张表中，而是存在于多个不同的表中，这时就需要用到多表查询。多表查询是通过各个表间的相关列，从两个或多个表中检索数据。多表查询首先要在这些表中建立连接，再在连接的生成的结果集上进行筛选。多表连接查询，需要指定连接条件，即指定每个表中要用于联接的列。典型的连接条件是在一个表中指定外键，在另一个表中指定与其关联的键。基本的语法格式为：SELECT[ALL|DISTINCT]*|查询输出列表FROM表1[别名1]连接类型表2[别名2][ON表1.相关列=表2.相关列][WHERE条件表达式];7.3多表查询 其中，连接类型有以下几种：（1）INNERJOIN：内连接。结果只包含满足条件的行，INNER关键字可以省略。（2）OUTERJOIN：外连接。外连接包括：左外连接（LEFTOUTERJOIN）：结果集中除了包括满足连接条件的行外，还包括左表中不满足条件的记录行。当左表中不满足条件的记录与右表记录进行组合时，右表相应列值为NULL。右外连接（RIGHTOUTERJOIN）：结果集中除了包括满足连接条件的行外，还包括右表中不满足条件的记录行。当右表中不满足条件的记录与左表记录进行组合时，左表相应列值为NULL。左外连接和右外连接中的OUTER关键字均可省略。（3）CROSSJOIN：交叉连接。结果只包含两个表中所有行的组合，指明两表间的笛卡儿操作7.3.1内连接 内连接有以下两种语法格式：格式一：SELECT输出列表FROM表1[INNER]JOIN表2ON表1.字段名比较运算符表2.字段名；格式二：SELECT输出列表FROM表1，表2WHERE表1.字段名比较运算符表2.字段名；7.3.1内连接 内连接包括3种类型：等值连接、非等值连接和自然连接。（1）等值连接：当比较运算符为“=”时，查询结果中包含被连接表的所有字段，包括重复字段。通常使用“表1.主键=表2.外键”形式。（2）非等值连接：在连接中使用除等号外的比较运算符（>、>=、<、<=、!=）来比较连接字段的值。（3）自然连接：是一种特殊的等值连接，但是结果集中不包括重复字段。【例7-29】查询学号为“202320107101”的学生的选课信息，输出学号，姓名，课程名和成绩。代码如下：mysql>selectxs.xh,xm,kcmc,cj->fromxsjbxxbasxsjoinxsxkb->onxs.xh=xsxkb.xh->joinkcdmbonxsxkb.kcdm=kcdmb.kcdm->wherexs.xh='202320107101';7.3.1内连接 注意：如果要输出的字段是表1和表2中都有的字段，则必须要在输出的字段名前加上表名进行区分，用“表名.字段名”表示。如果要连接的表中有列名相同，并且连接的条件就是列名相等，那么ON条件也可以换成USING子句。USING(两表中相同的列名)子句用于为一系列的列进行命名。该例题也可以用下列代码来实现：mysql>selectxs.xh,xm,kcmc,cj->fromxsjbxxbasxsjoinxsxkbusing(xh)->joinkcdmbusing(kcdm)->wherexs.xh='202320107101';结果也是一样的。7.3.2外连接外连接返回的结果集除了包括符合条件的记录外，还会返回FROM子句中至少一个表中的所有行，不满足条件的数据行将显示为空值，又分为左外连接、右外连接和全外连接。左外连接（LEFTJOIN）：结果集中除了包括满足连接条件的行外，还包括左表中不满足条件的记录行。当左表中不满足条件的记录与右表记录进行组合时，右表相应列值为NULL。右外连接（RIGHTJOIN）：结果集中除了包括满足连接条件的行外，还包括右表中不满足条件的记录行。当右表中不满足条件的记录与左表记录进行组合时，左表相应列值为NULL。语法格式为：SELECT输出列表FROM表名1LEFT|RIGHTJOIN表名2ON表名1.字段名1=表名2.字段名2;7.3.2外连接【例7-30】利用左外连接查询学号为“202320901102”的学生的选课信息，输出学号，姓名，课程编码和成绩。代码如下：mysql>selectxs.xh,xm,kcdm,cj->fromxsjbxxbasxsleftjoinxsxkb->onxs.xh=xsxkb.xh->wherexs.xh='202320901102';【例7-31】利用右外连接查询女学生的选课信息，输出学号，课程编码和成绩。代码如下：mysql>selectxs.xh,kcdm,cj->fromxsxkbrightjoinxsjbxxbasxs->onxsxkb.xh=xs.xh->wherexb='女';7.3.3交叉连接 交叉连接(CrossJoin)是在没有where子句的情况下,产生的表的笛卡儿积。两个表作交叉连接时，结果集大小为二者行数之积。该种方式在实际过程中用的很少。其语法格式如下：SELECT字段名列表FROM表1CROSSJOIN表2；【例7-32】对部门代码表和教师基本情况表进行交叉连接。代码如下：mysql>select*->frombmdmbcrossjoinjsjbxxb;执行结果由152条记录（部门代码表有8条记录，教师基本情况表有19条记录）。7.3.4自连接 自连接就是一个表的两个副本之间的内连接，即同一个表名在FROM子句中出现两次，为了区别，必须对表指定不同的别名，字段名前也要加上表的别名进行区分。【例7-33】查询与魏志强同一个专业的的学生的学号和姓名。代码如下：mysql>selectxs1.xh,xs1.xm->fromxsjbxxbasxs1,xsjbxxbasxs2->wherexs1.zymc=xs2.zymcandxs2.xm='魏志强'->andxs1.xm!='魏志强';7.4子查询

7.4.1比较子查询 7.4.2带IN关键字的子查询 7.4.3批量比较子查询 7.4.4带exists子查询 7.4.5利用子查询插入、更新与删除数据 7.4子查询 子查询就是指在一个查询语句中，还包括另一个查询语句。在外一层的查询中使用里面一层查询产生的结果集。外层的查询称为父查询或外层查询，里面嵌套的查询称为子查询或内层查询。它可以嵌套在SELECT、INSERT、UPDATE、DELETE语句或其他的子查询语句中。子查询的执行过程为：首先执行子查询中的语句，并将返回的结果作为外层查询的过滤条件，然后再执行外部查询。在子查询中通常要使用比较运算符、IN、ANY及EXISTS等关键字，分为比较子查询、IN子查询、批量比较子查询和EXISTS子查询。子查询是一个SELECT命令，需要用圆括号将SELECT语句括起来。7.4.1比较子查询当子查询的结果返回为单个值时，通常可以用比较运算符为外层查询提供比较操作。语法格式如下：WHERE表达式比较运算符(子查询)【例7-34】查询选修了“微机原理与应用”的学生学号和成绩。提示：首先子查询在课程代码表中找出课程“微机原理与应用”的课程代码，然后在将找到的课程代码作为查询条件，在学生选课表中找出选修了该课程的学号和成绩。代码如下：mysql>selectxh,cj->fromxsxkb->wherekcdm=(selectkcdm->fromkcdmb->wherekcmc='微机原理与应用');7.4.2带IN关键字的子查询 当子查询的结果返回为单列的集合时，可以使用IN关键字来判断外层查询中一个给定值是否在子查询的结果集中。基本语法格式如下：WHERE表达式[NOT]IN(子查询)【例7-35】查询选修成绩在90分以上的学生学号和姓名。提示：首先在学生选课表中将成绩在90分以上的学生学号找出来，然后根据找到的学号在学生基本信息表中将姓名找出来。代码如下：mysql>selectxh,xm->fromxsjbxxb->wherexhin(selectxh->fromxsxkb->wherecj>90);7.4.3批量比较子查询ALL、SOME和ANY运算都是对比较运算进行限制的子查询。ALL指定表达式要与子查询结果集中的每个值都进行比较，当表达式与每个值都满足比较的关系时，才返回true，否则返回false。SOME和ANY是同义词，表示表达式只要与子查询结果集中的某个值满足比较的关系时，就返回true，否则返回false。语法格式如下：WHERE表达式比较运算符{ANY|SOME|ALL}(子查询)【例7-36】查询考试成绩小于60分的学生姓名。代码如下：mysql>selectxmfromxsjbxxb->wherexh=some(selectxhfromxsxkbwherecj<60);7.4.3批量比较子查询【例7-37】查询学生基本信息表中年龄最大的学生姓名。代码如下：mysql>selectxm->fromxsjbxxb->wherecsrq<=all(selectcsrqfromxsjbxxb);该实例也可以用下面代码实现：mysql>selectxm->fromxsjbxxb->wherecsrq=(selectmin(csrq)fromxsjbxxb);7.4.4带exists子查询 使用EXISTS的子查询不需要返回任何实际数据，而仅返回一个逻辑值。如果内层查询语句查询到满足条件的记录，就返回一个真值（true），否则，将返回一个假值（false）。当返回的值为true时，外层查询语句将进行查询；当返回的为false时，外层查询语句不进行查询或者查询不出任何记录。语法格式如下：WHERE[NOT]EXISTS(子查询)【例7-38】查询选修了课程的学生姓名。代码如下：mysql>selectxm->fromxsjbxxb->whereexists(select*fromxsxkbwherexsjbxxb.xh=xsxkb.xh);7.4.4带exists子查询 注意：子查询和连接查询在很多情况下可以互换。至于什么时候用子查询，什么时候用连接查询，可以参考下面原则。（1）如果查询语句要输出的字段来自多个表时，用连接查询。（2）如果查询语句要输出的字段来自一个表，但是其WHERE子句涉及另一个表时，常用子查询。（3）如果查询语句要输出的字段和WHERE子句都只涉及到一个表，但是WHERE子句的查询条件涉及聚合函数进行数值比较时，一般用子查询。7.4.5利用子查询插入、更新与删除数据 1.利用子查询插入记录INSERT语句中的SELECT子查询可用于将一个或多个其他的表或视图的数据添加到表中。使用SELECT子查询可同时插入多行数据。【例7-39】将学生基本信息表中2005年出生的学生记录添加到xs表中。分析：子查询的选择列表必须与insert语句列的列表匹配。如果insert语句没有指定列的列表，则选择列表必须与正向其插入的表或视图的列匹配且顺序一致。（1）首先创建xs表，结构与xsjbxxb表的表结构一致。mysql>createtablexslikexsjbxxb;（2）复制数据，代码和执行结果如下：mysql>insertintoxs->select*fromxsjbxxb->whereyear(csrq)=2005;7.4.5利用子查询插入、更新与删除数据 2.利用子查询更新数据UPDATE语句中的SELECT子查询可用于将一个或多个其他的表或视图的值进行更新。使用SELECT子查询可同时更新多行数据。实际上是通过将子查询的结果作为更新条件表达式中的一部分。【例7-40】将学生选课表中成绩在90分以上的学生，在xs表的简历字段填上“优秀”。代码和执行结果如下：mysql>updatexs->setjl='优秀'->wherexhin(selectxhfromxsxkbwherecj>90);在DELETE语句中利用子查询同样可以删除符合条件的行。实际上也是通过将子查询的结果作为删除条件表达式中的一部分。7.4.5利用子查询插入、更新与删除数据 1.利用子查询插入纪录。INSERT语句中的SELECT子查询可用于将一个或多个其他的表或视图的值添加到表中。使用SELECT子查询可同时插入多行。【例7-39】将学生基本信息表中2000年出生的学生记录添加到xs表中。分析：子查询的选择列表必须与insert语句列的列表匹配。如果insert语句没有指定列的列表，则选择列表必须与正向其插入的表或视图的列匹配且顺序一致。（1）首先创建xs表，结构与xsjbxxb表的表结构一致。mysql>createtablexslikexsjbxxb;（2）复制数据，代码和执行结果如下：mysql>insertintoxs->select*fromxsjbxxb->whereyear(csrq)=2000;7.5使用正则表达式进行模糊查询

7.5使用正则表达式进行模糊查询REGEXP是正则表达式的缩写，但它不是SQL标准的一部分。REGEXP运算符的一个同义词是RLIKE。其基本语法为：WHERE字段名[NOT][REGEXP|RLIKE]表达式其中，表达式表示以哪种方式来进行匹配查询。表7-3正则表达式常用字符7.5使用正则表达式进行模糊查询1.查询以特定字符或字符串开头的记录使用字符“^”可以匹配以特定字符或字符串开头的记录。【例7-42】查找学生基本信息表中姓“张”的学生学号，姓名和性别。代码如下：mysql>selectxh,xm,xb->fromxsjbxxb->wherexmregexp'^张';2.查询以特定字符或字符串结尾的记录使用字符“$”可以匹配以特定字符或字符串结尾的记录。【例7-43】查找课程代码表中课程名称以“技术”为结束字符串的课程名称。代码如下：mysql>selectkcmc->fromkcdmb->wherekcmcregexp'技术$';7.5使用正则表达式进行模糊查询3.匹配指定字符串正则表达式可以匹配字符串。当表中的记录包含这个字符串时，就可以将该记录查询出来。如果指定多个字符串时，需要用符号“|”隔开。只要匹配这些字符串中的任意一个即可。【例7-44】查找学生学号中包含“2010”或“2030”的学生信息，显示学号，姓名，专业。代码如下：mysql>selectxh,xm,zymc->fromxsjbxxb->wherexhregexp'2010|2030';7.6合并结果集

7.6合并结果集UNION操作符可以将多个SELECT语句的返回结果组合到一个结果集中。当要检索的数据在不同的结果集中，并且不能够利用一个单独的查询语句得到时，可以使用union合并多个结果集。基本语法格式如下：SELECT语句1UNION[ALL]SELECT语句2[UNION[ALL]<SELECT语句3>][...n]【例7-45】查询部门号为01和03的班级信息。代码如下：mysql>selectbjbh,bmh,bjzwmc->frombjdmb->wherebmh='01'->union->selectbjbh,bmh,bjzwmc->frombjdmb->wherebmh='03';7.7查询优化

7.7.1基础分析工具7.7.2索引优化7.7.3SQL语句优化‌7.7.1基础分析工具

在MySQL中，可以使用EXPLAIN语句和DESCRIBE（DESC）语句来分析查询语句。使用EXPLAIN分析执行计划，检查索引命中情况，然后有针对性的进行优化查询。1.EXPLAIN命令（分析执行计划）在SELECT语句前添加EXPLAIN关键字即可获取执行计划‌，其语法格式如下：EXPLAIN|EXPLAINANALYZEselect_statement;说明：select_statement是数据库查询语句。注意：EXPLAIN命令分析查询语句，仅生成执行计划，帮助用户理解查询是如何执行的，并不执行查询。而EXPLAINANALYZE命令则真实执行查询并返回结果。7.7.1基础分析工具

【例7-46】使用explain分析查询语句select*fromxsjbxxb。代码如下：explainselect*fromxsjbxxb;执行结果如图所示。

使用EXPLAINANALYZE执行该查询的效果如下：mysql>EXPLAINANALYZEselect*fromxsjbxxb\G;***************************1.row***************************EXPLAIN:->Tablescanonxsjbxxb(cost=7.35rows=71)(actualtime=0.163..0.203rows=71loops=1)其中‌actualtime‌表述实际执行时间（毫秒），0.163..0.203（0.163ms(启动时间)：获取第一行数据所花费的实际时间。0.203ms(总时间)：获取所有行数据所花费的实际总时间。）‌rows表示实际处理的行数，‌loops‌是迭代器循环次数。7.7.1基础分析工具

2.‌EXPLAIN执行查询关键字段解析要使用MySQL的EXPLAIN分析查询执行计划，需理解其输出字段的含义。EXPLAIN输出包含以下字段： ‌id‌：查询序列号，标识子查询的执行顺序（值越大越先执行）‌。 ‌select_type‌：查询类型，如SIMPLE（简单查询）、SUBQUERY（子查询）等‌。 ‌table‌：查询涉及的表名‌。 partitions‌：用于标识查询访问的表分区名称，若表未分区或MySQL版本不支持分区，该字段值为NULL。 ‌type‌：‌最重要字段‌，表示访问数据的方式，性能从优到差排序：system（只有一条记录）>const（使用主键/唯一索引）>eq_ref（多表连接时，关联字段为主键或唯一索引）>ref（使用普通索引）>fulltext（全文索引检索）>range（范围扫描）>index（全索引扫描）>ALL（全表扫描）‌。‌优化建议‌：避免ALL（全表扫描），优先优化为ref或range‌。 ‌possible_keys‌：可能使用的索引列表‌。 ‌key‌：实际使用的索引名称（若为NULL表示未用索引）‌。 ‌key_len‌：表示查询中实际使用的索引长度。 ref：表示查询使用了哪种类型的索引引用。若ref为NULL，则可能未使用索引或索引失效。 ‌rows‌：预估扫描的行数，值越小越好‌。 ‌filtered‌：表示存储引擎返回的数据经过WHERE条件过滤后，剩余行数的百分比估计值。例如，filtered=10.00表示存储引擎返回的行中，约10%满足WHERE条件。 ‌Extra‌：额外信息，如：Usingindex：使用覆盖索引（高效）‌，Usingfilesort：需额外排序（低效），‌Usingtemporary：使用临时表（需优化）。‌想要让查询尽可能地快，那么就应该注意extra字段的值为Usingfilesort和Usingtemporary的情况，这种情况下就需要对查询进行优化。7.7.1基础分析工具

3.DESCRIBE语句DESCRIBE和EXPLAIN语句是同义词。在实践中，DESCRIBE关键字更常用于获取表结构信息，而EXPLAIN则用于获取查询执行计划（即解释MySQL将如何执行一个查询）。其语法格式为：DESCRIBE|DESCselect_statement;【例7-47】使用describe分析查询语句select*fromxsjbxxb。代码如下：describeselect*fromxsjbxxb;执行结果如图所示。可以看到执行结果与上例的执行结果相同。7.7.1基础分析工具

4.基于EXPLAIN结果优化查询策略‌（1）索引优化‌若key为NULL或type=ALL，为WHERE或JOIN字段添加索引‌。确保索引匹配查询条件，但要避免函数操作索引字段。‌（2）减少扫描行数‌若rows值过高，优化查询条件或使用LIMIT限制结果集‌。（3）‌避免额外操作‌若Extra出现Usingfilesort或Usingtemporary，优化ORDERBY或GROUPBY字段，确保该字段有索引。（4）‌覆盖索引‌通过SELECT输出项仅包含索引字段，触发Usingindex提示。‌注意：可以使用EXPLAINFORMAT=JSON获取更详细的分析（如成本估算）；索引未生效时，检查字段类型是否匹配；避免OR条件导致全表扫描，改用IN或UNION‌。7.7.1基础分析工具

针对explainselect*fromxsjbxxb;结合执行计划分析和常见优化策略，可采取以下措施：（1）避免全表扫描‌EXPLAIN显示type=ALL，表示未使用索引，需全表扫描。可为高频查询条件（如WHERE、JOIN涉及的列）创建索引，或通过强制索引‌FORCEINDEX指定索引（如SELECT*FROMxsjbxxbforceindex(primary)）。（2）减少数据量‌SELECT*会返回所有列，增加I/O和内存开销，可以指定列名，仅查询必要列（如SELECTxh,xmFROMxsjbxxb）。‌使用LIMIT限制返回行数（如LIMIT10）。‌（3）分区表优化‌‌若表数据量极大（如百万级），可考虑分区，减少单次查询扫描范围。‌示例优化后查询‌，假设高频查询条件为xm。CREATEINDEXidx_xmONxsjbxxb(xm);EXPLAINSELECTxh,xmFROMxsjbxxbWHERExm='张三'LIMIT10;7.7.2索引优化

在MySQL中，可以使用索引提高查询的效率。在使用索引查询时，可以通过为高频查询字段（如WHERE子句中的高频过滤条件、JOIN操作中的连接条件、ORDERBY和GROUPBY子句中的列、高选择性的列（唯一值多的列））创建索引，遵循最左匹配原则；避免索引失效场景，如在索引列使用函数、运算或模糊查询（如LIKE'%前缀'），使用覆盖索引（查询字段仅包含索引列）减少回表操作‌，对查询进行优化。7.7.2索引优化

1.等值查询优化（单列索引优化）使用高频查询字段做条件但未建立索引，此时需要对该字段建立索引，来进行查询优化。【例7-48】使用explain分析查询语句select*fromxsjbxxbwherexh='202420915101'。代码如下：mysql>explainselect*fromxsjbxxbwherexh='202420915101';运行结果如图所示。

从结果中可以看到，type值为ALL，查询需要全表扫描。当表中条件字段xh为创建索引时，需要在整张表中进行查找，若表中记录很多，查询速度慢。7.7.2索引优化

优化方案：在xsjbxxb表的xh字段上创建主键索引（或唯一索引）。altertablexsjbxxbaddprimarykey(xh);再次执行上述查询后的结果如图所示。type从ALL（全表扫描）提升为const（主键/唯一索引），查询被优化7.7.2索引优化

2.多条件查询（最左前缀原则）多条件查询需要对涉及的多个字段建立复合索引，来进行查询优化。【例7-49】使用explain分析查询语句，查询某个学生某门课的成绩。代码如下：mysql>explainselectxh,kcdm,cjfromxsxkbwherexh='202220701103'andkcdm='00202202';运行结果如图所示。从结果中可以看到，type值为ALL，查询需要全表扫描。若表中记录很多，查询速度慢。7.7.2索引优化

优化方案：在xsxkb表的xh和kcdm字段上创建复合主键索引（或唯一索引）。altertablexsxkbaddprimarykey(xh,kcdm);再次执行上述查询后的结果如图所示。从结果中可以看到，type从ALL（全表扫描）提升为const（主键/唯一索引），查询被优化，只查询表中的一条记录，速度极快。7.7.2索引优化

最左前缀原则（最左匹配原则）指的是：当一个索引包含多个列（例如(A,B,C)）时，数据库优化器可以使用这个索引来查询数据，只要查询条件中包含了这些列的从最左边开始的一个或多个连续组合。这个复合索引同样适用于以下查询：selectxh,kcdm,cjfromxsxkbwherexh='202220701103'';（只用到了索引的第一部分）selectxh,kcdm,cjfromxsxkbwherexh='202220701103'andkcdm='00202202';（完全用到索引）但不适用于：selectxh,kcdm,cjfromxsxkbwherekcdm='00202202';（没有xh开头，用不到此索引）7.7.2索引优化

3.覆盖索引优化创建覆盖索引（包含查询所需全部列），仅需查询索引列，避免回表。【例7-50】使用explain分析查询语句，查询某个专业的学生学号和姓名。代码如下：mysql>explainselectxh,xmfromxsjbxxbwherezymc='水利水电工程';运行结果如图所示。从结果中可以看到，type值为ALL，查询需要全表扫描。7.7.2索引优化

优化方案：创建覆盖索引（包含查询所需全部列）。altertablexsjbxxbaddindexidx_cover(xh,xm,zymc);再次执行上述查询后的结果如图所示。从结果中可以看到，type从ALL（全表扫描）提升为index（全索引扫描），且Extra字段的值为Usingindex，字段的信息直接从索引树中的信息取得，而不再去扫描实际的记录。7.7.2索引优化

4.模糊查询优化使用like关键字进行模糊查询，且包含通配符“%（或_）”。【例7-51】使用explain分析查询语句，查询某个姓的学生信息。代码如下：mysql>explainselectxh,xm,xb,zymcfromxsjbxxbwherexmlike'张%';运行结果如图所示。从结果中可以看到，type值为ALL，查询需要全表扫描。7.7.2索引优化

优化方案：在xsjbxxb表的xm列创建索引。altertablexsjbxxbaddindexidx_xm(xm);再次执行上述查询后的结果如图所示。从结果中可以看到，type从ALL（全表扫描）提升为range（范围扫描），只在姓张的学生中查找，rows值为3（只有3条记录），只检查3行。7.7.2索引优化

注意：只有LIKE'prefix%'能用到索引。LIKE'%keyword%'和LIKE'%keyword'用不到索引。在本例中，索引idx_xm只有like'张%'或like'张_'能用到，而like'%张'和like'%张%'是用不到索引idx_xm的。例如：explainselectxh,xm,xb,zymcfromxsjbxxbwherexmlike'%宇';，执行结果如图所示。建议：在实际应用中不要创建过多的索引。每个索引都会增加写操作的开销和存储空间。可以利用最左前缀原则，一个(A,B,C)索引相当于同时是(A)的索引、(A,B)的索引和(A,B,C)的索引