数据库技术 课件 龙琦 项目7-11　数据查询-综合应用

数据库技术7.1查询语句的基础7.1查询语句的基础

查询是对存储在MySQL中的数据的一种请求。数据表在接受查询请求时，会逐行判断数据是否符合查询条件，若符合则提取出来，并以一个或多个结果集的形式返回给用户。结果集是对来自SELECT语句的数据的表格排列，与MySQL表相似，结果集由行和列组成，通常也被称为记录集（RecordSet）。7.1查询语句的基础

由于结果集的结构与表的结构非常接近，都是行列组成，因此可以在结果集上再次进行查询，即在已查询的基础上进一步查询。在MySQL中，查询可以通过以下几种方式发出：

使用MySQLWorkbench或phpMyAdmin等图形用户界面（GUI）工具，用户可以从一个或多个MySQL表中选择想要查看的数据。

使用MySQL命令行客户端或MySQLShell的用户可以发出SELECT语句。

客户端或基于中间层的应用程序（例如使用Python、Java、PHP等编写的应用程序）可以将MySQL表中的数据映射到绑定控件（如数据网格）。

尽管查询可以以多种方式与用户交互，但它们的核心任务都是相同的：为用户提供SELECT语句的结果集。即使用户使用图形化工具且未直接编写SELECT语句，客户端软件也会将用户的查询转换为SELECT语句并发送到MySQL服务器。SELECT语句的基本结构7.1.17.1.1SELECT语句的基本结构虽然SELECT语句的完整语法可能较为复杂，但大多数SELECT语句都涵盖了结果集的四个核心属性：

结果集中的列的数量及其特性。对于每个结果集列，需明确以下属性：

列的数据类型。

列的大小（对于字符串类型）以及数值列的精度和小数位数。

数据值的来源列。

检索数据的表及其间的逻辑关系。

源表行需满足的条件，以符合SELECT语句要求。不符合条件的行将被排除。

结果集行的排序方式。语法格式如下：SELECT[ALL|DISTINCT]select_listFROMtable_list[WHEREsearch_conditions][GROUPBYgroup_by_list][HAVINGsearch_conditions][ORDERBYorder_list[ASC|DESC]][LIMITrow_count[OFFSEToffset]]7.1.1SELECT语句的基本结构参数说明：（1）[ALL|DISTINCT]（可选）：用于指定检索行的类型。

ALL：默认选项，表示检索表中所有符合条件的行，包括重复的行。

DISTINCT：表示仅检索唯一不同的行，即排除重复的行。

select_list：描述结果集的列。它是一个由逗号分隔的表达式列表。通常，每个选择列表表达式都是对源表或视图中的列的引用，但也可以是常量、函数等表达式。使用*可返回源表的所有列。（2）FROMtable_list：包含从中检索数据的表的列表。这些来源可以是：

MySQL数据库中的基表。

视图。

通过JOIN语句连接的多个表。（3）WHEREsearch_conditions（可选）：定义源表中的行需满足的条件，以符合SELECT语句要求。（4）GROUPBYgroup_by_list（可选）：根据group_by_list列中的值将结果集分组。（5）HAVINGsearch_conditions（可选）：对结果集进行附加筛选。通常与GROUPBY子句一起使用。（6）ORDERBYorder_list[ASC|DESC]（可选）：定义结果集中行的排序顺序。order_list指定排序的列，ASC表示升序（默认），DESC表示降序。（7）LIMITrow_count（可选）：限制返回的行数。条件表达式的构建7.1.27.1.2条件表达式的构建

在MySQL中，条件表达式用于在SELECT、INSERT、UPDATE和DELETE等语句中根据特定条件筛选或修改数据。条件表达式通常由比较运算符、逻辑运算符和函数组成。7.1.2条件表达式的构建1.比较运算符比较运算符用于比较（除TEXT和BLOB类型外)两个表达式值，并返回一个布尔结果（TRUE、FALSE或NULL）。常见的比较运算符包括如表7.1所示。7.1.2条件表达式的构建2.逻辑运算符逻辑运算符。逻辑运算符用于对某些条件进行测试，以获得其真实情况。逻辑运算符和比较运算符一样，返回TRUE或FLASE值。逻辑运算符如表7.2所示。7.1.2条件表达式的构建3.范围比较BETWEEN和IN用于范围比较。(1)当要查询的条件是某个值的范围时，可以使用BETWEEN关键字。BETWEEN关键字指出查询范围的格式为：表达式[NOT]BETWEEN表达式1AND表达式2注意：表达式1的值不能大于表达式2的值。（2）使用IN关键字可以指定一个值表，值表中列出所有可能的值，当与值表中的任一个匹配时，即返回TRUE，否则返回FALSE。使用IN关键字指定值表的格式为：表达式IN(表达式1[,…n])注意：IN关键字最主要的作用是表达子查询。4.模式匹配LIKE运算符用于指出一个字符串是否与指定的字符串相匹配，其运算对象可以是char、varchar、text、datetime等类型的数据，返回逻辑值TRUE或FALSE。使用LIKE进行模式匹配时，常使用特殊符号_和%，可进行模糊查询。“%”代表0个或多个字符，“_”代表单个字符。由于MySQL默认不区分大小写，要区分大小写时需要更换字符集的校对规则。7.1.2条件表达式的构建5.空值比较ISNULL空值比较。在MySQL中，处理空值（NULL）比较时需要特别注意，因为NULL在SQL中表示“未知”或“无值”，它不同于空字符串（''）或零值（0）。注意：

要检查一个列的值是否为NULL，应使用ISNULL条件。

要检查一个列的值是否不为NULL，应使用ISNOTNULL条件。6.函数在MySQL中，内置函数为数据处理提供了强大的工具，这些函数可以根据数据的类型分为字符串函数、数值函数、日期和时间函数等。我们将在项目9中详细介绍。谢谢数据库技术7.2单表查询7.2单表查询单表查询是数据库操作的基础，涉及从单一表中检索数据。主要步骤包括选择表、指定所需列、应用条件表达式筛选数据。

选择表中的列7.2.17.2.1选择表中的列1.输出表中的所有列输出表中的所有列，有两种方法，一种是将所有的字段名在select关键字后列出，一种是用星号“*”代替所有的字段，当使用星号“*”时，结果集中的列的顺序与CREATETABLE、ALTERTABLE或CREATEVIEW语句中所指定的顺序相同。任务1：查询全体学生的记录信息。代码如下：

SELECT*FROMstudents;运行结果如图7.1所示。7.2.1选择表中的列任务2：查询所有班级信息。代码如下：SELECTcls_id,cls_nameFROMclasses;运行结果如图7.2所示：7.2.1选择表中的列2.输出表中的特定列要选择表中的特定列，应在选择列表中明确地列出每一个特定列。任务3：查询学生的姓名和性别。代码如下：SELECTstu_name,stu_genderFROMstudents;运行结果如图7.3所示：7.2.1选择表中的列3.输出计算列选择表中的列时可以包含通过对一个或多个简单表达式应用运算符而生成的表达式。即在结果集中显示基表中不存在，但是根据基表中存储的值计算得到的值。任务4：查询成绩表中所有学生的学号和平均分。代码如下：SELECTstu_id,AVG(sc_grade)FROMscores运行结果如图7.4所示：图7.4查询成绩表中所有学生的学号和平均分7.2.1选择表中的列注意：（1）使用SUM(),AVG(),COUNT(),MAX(),MIN()等聚合函数来计算列的总和、平均值、数量、最大值和最小值。 ·（2）聚合函数对一组值执行计算并返回单一值，只能用于SELECT语句中，并且通常与GROUPBY子句一起使用，以便对数据进行分组。（3）当使用聚合函数时，SELECT列表中未包含在聚合函数中的其他列必须出现在GROUPBY子句中。（4）因为WHERE子句是在数据分组和聚合之前对数据进行过滤，而聚合函数是在数据分组后对每个分组进行计算的。所以在WHERE子句中不能出现聚合函数。（5）HAVING子句是用于对聚合查询结果进行过滤的。它允许在GROUPBY子句之后使用聚合函数来设置条件，从而筛选出满足特定聚合条件的分组。7.2.1选择表中的列4.为结果集中的列指定列名在任务4中的查询结果中我们可以看到，通过计算得到的列“平均分”，列标题显示为“AVG(sc_grade)”。在实际应用中我们可以根据需要利用AS子句来更改结果集中列的名称或为列指定别名。AS子句语法格式如下：SELECTcolumn_nameASalias_name-–列名AS列别名，给列指定别名SELECTt1.column_nameFROMtable1ASt1--表名as表别名，给表指定别名说明：

在SELECT子句中定义的别名可以在ORDERBY、GROUPBY和HAVING子句中引用，但不能在WHERE子句中引用，因为WHERE子句在SELECT子句之前执行。

可以省略AS关键字，但在编写SQL查询时，为了清晰和一致性，建议明确地使用。7.2.1选择表中的列任务5：查询显示成绩表中所有学生的学号和平均分。要求计算得到的字段用平均分字段名表示。代码如下：SELECTstu_id,AVG(sc_grade)AS平均分FROMscoresGROUPBYstu_id;运行结果如图7.5所示：选择查询7.2.27.2.2选择查询

选择查询从表中检索数据或进行计算的查询，是最基础且最常用的查询类型。于从数据库表中检索数据。可以选择特定的列、使用条件过滤行、排序结果集以及限制返回的行数。7.2.2选择查询1．查询条件WHERE和HAVING子句中的搜索条件或限定条件如表7.1所示。7.2.2选择查询2.运算符条件设置设置

在两个表达式之间可以使用运算符来进行比较，在比较字符串数据时，主要基于字符集和排序规则。首先，MySQL会根据字符集确定字符串中可以包含的字符。其次，排序规则决定了字符的比较方式，包括是否区分大小写。在比较时，MySQL会先比较字符串的长度，长度不同的字符串直接按长度排序；长度相同的字符串则按字典序（ASCII值或Unicode码点）进行比较。此外，还可以使用BINARY关键字进行二进制比较，或使用LIKE和REGEXP运算符进行模糊匹配和正则表达式匹配。任务6：查询成绩表中平均分高于80分的学生的学号和平均分。代码如下：SELECTstu_id,AVG(sc_grade)AS平均分FROMscoresGROUPBYstu_idHAVING平均分>80;--在SELECT子句中定义的别名可以在HAVING子句中引用运行结果如图7.6所示：7.2.2选择查询3.范围条件设置范围搜索中BETWEEN返回的是介于两个指定值之间的所有值，NOTBETWEEN是不返回与两个指定值匹配的任何值。任务7：查询显示学生表中2002年出生的所有学生的学号、姓名和出生日期。代码如下：SELECTstu_id,stu_name,stu_birthFROMstudentsWHEREstu_birthBETWEEN'2002-1-1'AND'2002-12-31';运行结果如图7.7所示：7.2.2选择查询4.列表条件设置IN关键字可以选择与列表中的任意值匹配的行。IN关键字之后的各项必须括在括号中并用逗号隔开。任务8：查询显示学生表中民族为白族和藏族的学生的学号、姓名和民族。代码如下：SELECTstu_id,stu_name,stu_nationFROMstudentsWHEREstu_nationIN('藏','傣');运行结果如图7.8所示：7.2.2选择查询5.字符匹配条件设置LIKE关键字可以查询与指定模式匹配的字符串、日期或时间值，与之相匹配的可以是完整的字符串，也可以包含通配符，任务9：查询显示学生表中姓“张”的学生的学号、姓名和民族。字段用学号、姓名和民族表示代码如下：SELECTstu_idAS学号,stu_nameAS姓名,stu_nationAS民族FROMstudentsWHEREstu_namelike'张%';运行结果如图7.9所示：7.2.2选择查询6.空值查询NULL值表示列的数据值未知或不可用。语法格式为：列表达式is[NOT]Null任务10：查询显示学生表中简历为空值的学生的学号、姓名和简历。代码如下：SELECTstu_id,stu_name,stu_resumeFROMstudentsWHEREstu_resumeISNULL;运行结果如图7.10所示：7.2.2选择查询任务11：查询显示学生表中简历不为空值的学生的学号、姓名和简历。代码如下：SELECTstu_id,stu_name,stu_resumeFROMstudentsWHEREstu_resumeISnotNULL;运行结果如图7.11所示：7.2.1选择表中的列注意：

在MySQL中，空值（NULL）和空串（''，即两个单引号之间没有字符）是两个完全不同的概念。

空值表示未知或缺失的数据。它不是一个有效的值，而是一个特殊的标记，用于指示该字段没有值。在数据库中，空值通常不占用存储空间（具体取决于数据库的实现）。它只是一个标记，表示该字段没有数据。空值不能与任何值（包括空串）进行比较。必须使用

ISNULL或

ISNOTNULL

来判断一个字段是否为空值。

空串是一个长度为0的字符串。它是一个有效的值，表示该字段有一个值，但这个值是一个空字符串。空串在数据库中占用存储空间（尽管很少），因为它仍然是一个字符串对象，只是没有字符而已。空串可以与任何值进行比较，包括其他空串。使用=

或

!=

运算符来比较空串和其他字符串。7.2.2选择查询7.条件组合查询使用逻辑运算符AND、OR、NOT连接可以实现条件组合查询。任务12：查询显示学生表中名字由四个字符组成，性别为“女”的学生的学号、姓名和性别。代码如下：SELECTstu_id,stu_name,stu_genderFROMstudentsWHEREstu_nameLIKE'____'ANDstu_gender='女';运行结果如图7.12所示：分类汇总与排序7.2.37.2.3分类汇总与排序

在实际应用中，用户经常需要对结果集中的数据进行统计，例如在“学生信息管理系统”数据库中我们需要对学生的人数进行统计，对学习成绩进行分析，这些统计查询可以通过聚合函数、COMPUTE子句、GROUPBY子句来实现。7.2.3分类汇总与排序1．使用聚合函数

聚合函数对一组值执行计算，并返回单个值。除了COUNT函数以外，聚合函数都会忽略空值。聚合函数经常与SELECT语句的GROUPBY子句一起使用。常用的聚合函数有:

AVG([ALL|DISTINCT]<列名>)，返回组中各值的平均值，其中忽略Null值，此列的数据类型必须是数值型。

COUNT([ALL|DISTINCT]<列名>)，COUNT_BIG([ALL|DISTINCT]<列名>)。返回组中的个数，两个函数唯一的差别是它们的返回值。COUNT始终返回int数据类型值。COUNT_BIG始终返回bigint数据类型值。

MAX([ALL|DISTINCT]<列名>),返回表达式中的最大值。

MIN([ALL|DISTINCT]<列名>)，返回表达式的最小值。

SUM([ALL|DISTINCT]<列名>)，返回表达式中所有值的和。参数说明：ALL是不取消重复项，ALL为默认值。DISTINCT是去掉指定列中的重复值。7.2.3分类汇总与排序任务13：查询课程编号为“230101”课程的最低成绩。代码如下：SELECTcrs_id,MIN(sc_grade)AS最低分FROMscoresWHEREcrs_id='230101';运行结果如图7.13所示：7.2.3分类汇总与排序2．对结果分组GROUPBY子句是将查询结果集按一列或多列进行分组，并对每一组进行统计。语法如下：[GROUPBYgroup_by_list][HAVINGsearch_conditions]参数说明：“BYgroup_by_list”按列名指定的字段进行分组。“HAVINGsearch_conditions”对生成的组筛选后再对满足条件的组进行统计。任务14：查询每门课程的选课人数。代码如下：SELECTcrs_id,COUNT(*)AS选课人数FROMscoresGROUPBYcrs_id;运行结果如图7.14所示：7.2.3分类汇总与排序3.HAVING子句

HAVING子句是SQL中用于对分组后的结果进行过滤的子句。它通常与GROUPBY

子句一起使用。HAVING子句中的条件可以包含聚合函数，如COUNT、SUM、AVG等。但是，HAVING子句中的聚合函数不能作用于非分组列。HAVING子句中不能使用WHERE

子句中的某些限制条件（如LIKE、IN、BETWEEN等），因为这些条件是在数据分组之前进行筛选的，而HAVING是在数据分组之后进行筛选的。任务15：查询选修人数少于50人的课程编号和相应的选课人数。代码如下：SELECTcrs_id,COUNT(*)AS选课人数FROMscoresGROUPBYcrs_idHAVINGCOUNT(*)<50;运行结果如图7.15所示：7.2.3分类汇总与排序3．对结果排序任务16：查询选修了课程编号为“230101”课程的学生学号、成绩，按成绩从高到低排序。代码如下：SELECTstu_id,crs_id,sc_gradeFROMscoresWHEREcrs_id='230101'ORDERBYsc_gradeDESC;运行结果如图7.16所示：7.2.3分类汇总与排序4．LIMIT子句LIMIT子句是SQL中用于限制查询结果返回行数的子句，它通常与SELECT语句一起使用。语法格式如下：LIMITrow_count[OFFSEToffset];参数说明：

row_count：指定要返回的最大行数。

OFFSET：可选参数，指定从哪一行开始返回结果（从0开始计数）。如果省略，则默认从第一行开始。7.2.3分类汇总与排序任务17：查询各门课程的平均分，显示平均分由高到低排名前6的课程的课程编号、平均分。代码如下：SELECTcrs_id,AVG(sc_grade)as平均分FROMscoresGROUPBYcrs_idORDERBY平均分DESCLIMIT6;运行结果如图7.17所示：7.2.3分类汇总与排序任务18：查询各门课程的平均分，显示平均分由高到低排名第3到第5的课程的课程编号、平均分。代码如下：SELECTcrs_id,AVG(sc_grade)as平均分FROMscoresGROUPBYcrs_idORDERBY平均分DESCLIMIT2,3;运行结果如图7.17所示：注意：如果初始值不是从头开始，需要使用参数：偏移量、行数。记录是从0开始计数的。谢谢数据库技术7.3多表查询7.3多表查询MySQL多表查询是通过SQL语句在多个表之间检索数据的过程，常用方法包括INNERJOIN（内连接）、LEFTJOIN（左连接）、RIGHTJOIN（右连接）和FULLJOIN（全连接，MySQL中不直接支持，需通过UNION模拟）。通过指定连接条件和选择字段，实现跨表数据获取。

连接(JOIN)的类型与使用7.3.17.3.1连接(JOIN)的类型与使用1.内连接（INNERJOIN）内连接查询返回两个表中满足连接条件的匹配行。它是最常用的连接类型，查询的是两张表交集的部分。内连接有两种语法形式，隐式内连接和显式内连接是，其中显式内连接使用INNERJOIN关键字，而隐式内连接则直接在WHERE子句中指定连接条件。7.3.1连接(JOIN)的类型与使用（1）隐式内连接语法格式如下：SELECT列名1,列名2,...FROM表1,表2WHERE表1.连接字段=表2.连接字段;参数说明：列名1,列名2,...：要检索的字段列表，可以来自表1、表2或两者都有。表1,表2：要连接的表名。WHERE表1.连接字段=表2.连接字段：连接条件，指定了两个表中用于匹配的字段。7.3.1连接(JOIN)的类型与使用任务19：查询选修了课程编号为“230101”课程的学生学号、姓名、课程编号、成绩。代码如下：SELECTstudents.stu_id,stu_name,crs_id,sc_gradeFROMstudents,scoresWHEREstudents.stu_id=scores.stu_idandcrs_id='230101';运行结果如图7.19所示：说明：在scores表中可以查询到学生的学号、课程编号、成绩，不能查询到同学的姓名。但如果知道学生的学号，可以到students表中查找到对应的同学姓名。这就可以用多表查询来完成。由于两个表中都有stu_id字段，所以在连接这两个表时，需要明确指定stu_id字段来自哪一张表，以避免歧义。7.3.1连接(JOIN)的类型与使用（2）显式内连接语法格式如下：SELECT列名1,列名2,...FROM表1INNERJOIN表2ON表1.连接字段=表2.连接字段;;参数说明：

列名1,列名2,...：同样表示要检索的字段列表。

表1：第一个要连接的表名。

INNERJOIN表2：使用INNERJOIN关键字连接第二个表。

ON表1.连接字段=表2.连接字段：通过ON子句指定连接条件。7.3.1连接(JOIN)的类型与使用任务20：查询选修了课程编号为“230203”课程的学生学号、姓名、课程编号、成绩。代码如下：SELECTstudents.stu_id,stu_name,crs_id,sc_gradeFROMstudentsINNERJOINscoresONstudents.stu_id=scores.stu_idWHEREcrs_id='230203';运行结果如图7.19所示：注意：

隐式内连接和显式内连接在逻辑上是等价的，但显式内连接通常更易于阅读和维护。

在处理复杂查询时，显式内连接可以帮助你更清晰地表达查询逻辑。

在某些情况下，隐式内连接可能会导致性能问题，因为数据库优化器可能无法像处理显式连接那样有效地优化查询计划。因此，建议使用显式内连接。7.3.1连接(JOIN)的类型与使用2.外连接（OUTERJOIN）在内部连接操作中只有当两个表中至少有一个行符合联接条件时，才返回行，因为内部连接消除了两个表中不匹配的行。而外部联接则会返回FROM子句中至少一个表或视图中的所有行，只要这些行符合WHERE或HAVING搜索条件的。外连接分为左外连接（LEFTJOIN或LEFTOUTERJOIN）、右外连接（RIGHTJOIN或RIGHTOUTERJOIN）和全外连接（FULLJOIN）。MySQL不直接支持FULLJOIN。左外连接就是将左表作为主表，主表中所有行分别与右表中的每一行进行连接，结果集中除了满足连接条件的行外，还有主表中不满足连接条件的行，在右表的相应列上自动填充NULL值。右外连接则相反。7.3.1连接(JOIN)的类型与使用任务21：查找所有学生的学号，姓名和成绩信息，若学生没有选修课程，也要显示其信息。代码如下：SELECTstudents.stu_id,stu_name,scores.*FROMstudentsLEFTJOINscoresONstudents.stu_id=scores.stu_id;如果不使用左外连接，查询结果中不会包含没有选修过课程的同学信息。使用了左外连接后，结果集中返回的行中有没用选修过的同学信息，相应的行的成绩信息为NULL。运行结果如图7.21所示：子查询与嵌套查询7.3.27.3.2子查询与嵌套查询

在MySQL中，子查询和嵌套查询被视为处理复杂SQL查询需求的同一概念的不同表述方式。从字面含义上解析，“子查询”一词更多地聚焦于查询之间的层级或嵌套关系，意味着一个查询作为另一个查询的组成部分存在；相对而言，“嵌套查询”则更强调查询结构的层次性，即一个查询被另一个查询所包裹。然而，在MySQL的实际应用中，这两个术语具有相同的含义，均指在一个查询语句内部嵌入另一个查询语句。为了保持术语的一致性和清晰性，本书统一采用“子查询”这一表述来指代这种查询结构。这样的定义有助于我们更准确地理解和运用MySQL中的复杂查询技术。

子查询基本语法格式如下：SELECT列名

FROM表名

WHERE列名

比较运算符(SELECT列名FROM表名WHERE条件);

括号内的部分即为子查询。子查询可以用在WHERE子句、HAVING子句、SELECT子句（作为计算列）等位置。7.3.2子查询与嵌套查询1．使用IN的子查询使用IN（或NOTIN）的子查询结果是包含零个值或多个值的列表。IN子查询用于检查某个值是否存在于另一个查询的结果集中。适用于过滤记录，根据一个字段的值是否匹配另一个查询返回的集合来决定是否包含该记录。任务22：查询19计算机应用1班，19旅游管理1班学生的信息。代码如下：SELECT*FROMstudentsWHEREcls_idIN(SELECTcls_idFROMclassesWHEREcls_name='19计算机应用1班'ORcls_name='19旅游管理1班');运行结果如图7.22所示：在运行包含子查询的SELECT语句时，系统先运行子查询，产生一个结果表，再运行查询。在本任务中，先运行子查询“SELECTcls_idFROMclassesWHEREcls_name='19计算机应用1班'ORcls_name='19旅游管理1班'”，得到一个只含有班级编号的表；再运行外查询，如果学生表中某行的班级编号列值等于子查询结果表中的任意一个值，则该行就会被选择。7.3.2子查询与嵌套查询2.使用比较运算符的的子查询常用的比较运算符有：=、>、<、>=、<=、<>、!=。直接使用比较运算符的子查询（即后面不接ANY或ALL的比较运算符）必须返回单个值而不是值列表。因为比较运算符设计用于单个值之间的比较。如果子查询返回了多于一个的值，MySQL会抛出一个错误。任务23：查询选修了“230301”号课程，且成绩高于该课程平均分的学生的学号。代码如下：查询选修了“230301”号课程，且成绩高于该课程平均分的学生的学号。SELECTstu_idFROMscoresWHEREsc_grade>(SELECTAVG(sc_grade)FROMscoresWHEREcrs_id='230301')ANDcrs_id='230301';运行结果如图7.23所示：在这个任务中，首先查询计算课程编号为“'230301”这门课程的平均分（单个值），然后再根据这个平均分找到所有选修了该课程，且成绩高于平均分的学生student_id。7.3.2子查询与嵌套查询任务23：查询选修了“230301”号课程，且成绩高于该课程平均分的学生共有多少人。代码如下：SELECTcount(stu_id)as高于平均分人数FROMstudentsWHEREstu_idIN(SELECTstu_idFROMscoresWHEREsc_grade>(SELECTAVG(sc_grade)FROMscoresWHEREcrs_id='230301')ANDcrs_id='230301');运行结果如图7.24所示：在这个任务中中，最内层的子查询首先查询计算课程编号为“'230301”这门课程的平均分，然后中层的子查询根据这个平均分找到所有选修了该课程，且成绩高于平均分的学生student_id，最后外层的查询根据这些stu_id找到对应的学生信息,并进行统计计数。7.3.2子查询与嵌套查询2.使用比较运算符的的子查询常用的比较运算符有：=、>、<、>=、<=、<>、!=。直接使用比较运算符的子查询（即后面不接ANY或ALL的比较运算符）必须返回单个值而不是值列表。因为比较运算符设计用于单个值之间的比较。如果子查询返回了多于一个的值，MySQL会抛出一个错误。任务23：查询选修了“230301”号课程，且成绩高于该课程平均分的学生的学号。代码如下：查询选修了“230301”号课程，且成绩高于该课程平均分的学生的学号。SELECTstu_idFROMscoresWHEREsc_grade>(SELECTAVG(sc_grade)FROMscoresWHEREcrs_id='230301')ANDcrs_id='230301';运行结果如图7.23所示：在这个任务中，首先查询计算课程编号为“'230301”这门课程的平均分（单个值），然后再根据这个平均分找到所有选修了该课程，且成绩高于平均分的学生student_id。7.3.2子查询与嵌套查询3．使用ANY（或SOME）、ALL的子查询ANY（或SOME）、ALL关键字，可以与比较运算符结合使用，以便在子查询返回的值集合中进行比较。ANY（或SOME）：SOME与ANY是同义词，在MySQL中它们是等价的。如果外部查询中的值与子查询返回的集合中的任何一个值满足比较条件，则返回TRUE,否则返回FALSE。ALL：如果外部查询中的值与子查询返回的集合中的所有值都满足比较条件，则返回TRUE,否则返回FALSE。7.3.2子查询与嵌套查询任务25：查询选修了“230301”号课程，学生的学号，姓名。先查找选修了“230301”号课程的同学的学号。代码如下：SELECTstu_idFROMscoresWHEREcrs_id='230301';运行结果如图7.25所示：因为有多名同学选修了“230301”号课程，所以在子查询中要用ANY（或SOME）。任务25的代码如下：SELECTstu_id,stu_nameFROMstudentsWHEREstu_id=ANY(SELECTstu_idFROMscoresWHEREcrs_id='230301');运行结果如图7.26所示：图7.25查询选修了“230301”号课程的同学的学号图7.26查询选修了“230301”号课程，学生的学号，姓名7.3.2子查询与嵌套查询本任务也可以用IN子查询，任务25的代码与下例语句是代码是等价的。SELECTstu_id,stu_nameFROMstudentsWHEREstu_idIN(SELECTstu_idFROMscoresWHEREcrs_id='230301');7.3.2子查询与嵌套查询任务26：查询比“51020171”班中所有学生年龄都小的其他班的学生的学号与姓名。要完成这个任务，需要先知道班级编号为51020171班同学的出生日期，因为“51020171”班级中不只有一名同学，所以会有多个出生日期，可以通过以下代码查询“51020171”班同学的出生日期，代码如下：SELECTstu_birthFROMstudentsWHEREcls_id='51020171';运行结果如图7.27所示：因为比“51020171”班同学年龄都小就是比子查询中每一条记录的出生日期都要大，所以在子查询的比较条件中要用ALL，任务26的代码如下：SELECTstu_id,stu_nameFROMstudentsWHEREstu_birth>ALL(SELECTstu_birthFROMstudentsWHEREcls_id='51020171');运行结果如图7.28所示：图7.27查询“51020171”班同学的出生日期图7.28查询比“51020171”班中所有学生年龄都小的其他班的学生的学号与姓名7.3.2子查询与嵌套查询5．使用EXISTS的子查询EXISTS用于检查子查询是否返回任何行。如果子查询返回至少一行，则EXISTS条件为真，否则为假。EXISTS通常用于提高查询效率，特别是在处理存在性检查时。EXISTS引出的子查询的目标列通常为*。任务27：查询选修了“230301”课程的学生的学号与姓名。代码如下：SELECTstu_id,stu_nameFROMstudentsWHEREEXISTS(SELECT*FROMscoresWHEREcrs_id='230301'ANDstu_id=students.stu_id);运行结果如图7.29所示：图7.29查询选修了“230301”课程的学生的学号与姓名

在这个任务中，虽然是单表查询，但查询条件使用了外查询的列名引用“students.stu_id”，表示这里的学号来自于表students。在这个任务中，内层查询要处理多次，因为内层查询与“students.stu_id”有关，在外层查询中students表的不同行有不同的学号。其处理过程是：首先查找外层查询中students的第一行，根据该行的“学号”列值处理内层查询，若结果不为空，则where条件为真，把该行的学号、姓名值取出作为结果集的一行；然后再找students表的第2，3，4……行，重复上述处理过珵直到students表的所有行都查找完为止。联合查询7.3.37.3.3联合查询MySQL实际上并不直接支持全连接（FULLJOIN）语法，但可以通过组合左连接（LEFTJOIN）和右连接（RIGHTJOIN）并使用联合查询（UNION查询）来模拟全连接的效果。全连接旨在返回两个表中所有匹配的记录以及不匹配的记录，对于不匹配的部分，结果集中的相应列将包含NULL值。联合查询使用UNION或UNIONALL关键字。注意：

列数和数据类型：所有SELECT语句必须返回相同数量的列，并且相应列的数据类型必须兼容。如果不兼容，MySQL会尝试进行隐式类型转换。

默认去除重复：UNION去除重复的行，返回唯一的结果集。UNIONALL包含所有的行，包括重复的行。

列名：结果集的列名来自第一个SELECT语句。如果后续的SELECT语句中有不同的列名，这些列名会被忽略，但列的数据会按照位置对应。

排序：ORDERBY子句应用于整个结果集，并且必须位于最后一个SELECT语句之后。LIMIT子句同样应用于整个结果集，用于限制返回的行数。

WHERE子句：每个SELECT语句都可以有自己的WHERE子句来过滤行。7.3.2子查询与嵌套查询任务28：将学生成绩表中学号为“23510102710101”成绩信息与课程编号为“230303”的成绩信息合并。查询成绩表中学号为“23510102710101”成绩信息，代码如下：SELECTstu_id,crs_id,sc_grade,sc_rebuildFROMscoresWHEREstu_id="23510102710101';运行结果如图7.30所示：查询课程编号为“230303”的成绩信息，代码如下：SELECTstu_id,crs_id,sc_grade,sc_rebuildFROMscoresWHEREcrs_id="230303";运行结果如图7.31所示：图7.30查询成绩表中学号为“23510102710101”成绩信息图7.31查询课程编号为“230303”的成绩信息7.3.2子查询与嵌套查询任务28：使用UNION将图7.30和图7.31的结果合并，代码如下：SELECTstu_id,crs_id,sc_grade,sc_rebuildFROMscoresWHEREstu_id="23510102710101"UNIONSELECTstu_id,crs_id,sc_grade,sc_rebuildFROMscoresWHEREcrs_id="230303";运行结果如图7.32所示：图7.32学号为“23510102710101”与课程编号为“230303”的成绩信息合并结果7.3.2子查询与嵌套查询任务28：图7.30中有9条成绩记录，图7.31中有4条成绩记录，使用UNION合并后，去掉了重复项，图7.32中共有12条记录。如果要保留所有记录，可以使用UNIONALL，代码如下：SELECTstu_id,crs_id,sc_grade,sc_rebuildFROMscoresWHEREstu_id="23510102710101"UNIONAllSELECTstu_id,crs_id,sc_grade,sc_rebuildFROMscoresWHEREcrs_id="230303";运行结果如图7.33所示：图7.33使用ALL学号为“23510102710101”与课程编号为“230303”的成绩信息合并结果谢谢小结

本章主要介绍了基本的SELECT语句，过滤数据（WHERE子句）、排序结果（ORDERBY子句）以及分组和聚合数据（GROUPBY子句和聚合函数）。此外，我们还学习了子查询和连接查询，这些高级查询技巧使我们能够处理更复杂的数据检索需求。

在进行查询操作时，信息安全问题不容忽视。我们必须严格遵守隐私保护原则，确保用户数据的安全和保密。同时，要遵守相关法律法规和行业标准，确保数据访问的合规性。在实际操作中，应合理使用权限控制、数据加密等安全措施，以防止数据泄露和滥用。小结数据库技术8.1视图的创建与使用8.1视图的创建与使用MySQL视图是一种虚拟表，基于SQL查询结果集创建，不存储实际数据，仅保存查询定义。通过视图，可以简化复杂查询、增强数据安全性及实现数据逻辑独立性。创建视图使用CREATEVIEW语句，并指定视图名称和查询语句。视图的使用与表类似，可执行SELECT、UPDATE等操作，但需视具体权限和视图的可更新性而定。视图的定义与优点8.1.18.1.1视图的定义与优点

视图是基于SQL查询结果的虚拟表，不存储数据，仅保存查询定义，用于简化查询和增强数据安全。

使用视图有以下优点：

简化复杂查询：视图可以简化复杂查询，使用户无需关心底层数据结构的复杂性，只需通过视图即可获取所需的数据。

增强数据安全性：视图可以限制用户对基础表的访问权限，确保用户只能访问其被允许查询的结果集，从而保护敏感数据不被非法访问或修改。

提高数据独立性：视图提供了一种逻辑层的数据抽象，当基础表的结构发生变化时，视图可以屏蔽这些变化对用户的影响，从而保持数据的独立性。

实现数据重用：视图可以被多个查询或应用程序共享，避免了重复编写相同的查询语句，提高了代码的可重用性和可维护性。视图的创建与修改8.1.28.1.2视图的创建与修改1.创建视图在MySQL中，可以使用CREATEVIEW语句来创建视图。创建视图时，需要指定视图的名称和用于生成视图数据的SQL查询语句。语法格式如下：CREATE[ORREPLACE]VIEWview_name[(column_list)]ASSELECT_statement[WITH[CASCADED|LOCAL]CHECKOPTION];8.1.2视图的创建与修改参数说明：

ORREPLACE（可选）：如果指定的视图已经存在，则替换它。如果不存在，则创建一个新的视图。

view_name：表示要创建的视图的名称。该名称在数据库中必须是唯一的，不能与其他表或视图同名。

column_list（可选）：表示属性清单，即视图中各个属性的名称。如果指定了此子句，则视图的列名将按照此清单中的顺序和名称来定义。默认情况下，如果不指定此子句，则视图的列名将与SELECT语句中查询的属性名称相同。

SELECT_statement：是一个完整的查询语句，用于从某个表或视图中查出某些满足条件的记录，并将这些记录导入视图中。这个查询语句可以包含SELECT子句、FROM子句、WHERE子句、GROUPBY子句、HAVING子句等，用于筛选、排序和连接数据。

WITH[CASCADED|LOCAL]CHECKOPTION（可选）：表示视图在更新时保证在视图的权限范围之内。CASCADED：表示更新视图时要满足所有相关视图和表的条件。这是默认值。LOCAL：表示更新视图时只要满足该视图本身定义的条件即可。8.1.2视图的创建与修改注意：创建视图时，用户必须具有创建视图的权限。查询语句不能包含子查询、系统或用户变量、预处理语句参数等。视图定义中引用的表或视图必须存在。但是，创建完视图后，可以删除定义引用的表或视图（可能会导致视图变得无效）。视图定义中允许使用ORDERBY语句，但是若从特定视图进行选择，而该视图使用了自己的ORDERBY语句，则视图定义中的ORDERBY将被忽略。8.1.2视图的创建与修改任务1：在“学生信息管理系统”数据库中，创建一个名称为“学生成绩”的视图，使用这个视图可以按学号的升序显示学生的学号、姓名、课程名、成绩。操作步骤如下，代码如下：CREATEVIEW学生成绩ASSELECTstudents.stu_id,stu_name,crs_name,sc_gradeFROMstudents,courses,scoresWHEREstudents.stu_id=scores.stu_idANDcourses.crs_id=scores.crs_idORDERBYstudents.stu_idASC;视图创建成功，可以通过SHOWTABLES命令查看。运行结果如图8.1所示：图8.1查看数据库中的表、视图

在任务1中，因为学号（stu_id）、姓名（stu_name）来自students表，课程名（crs_name）来自courses表，成绩（sc_grade）来自scores表，要查询这些信息，需要建立多表查询。代码如下：SELECTstudents.stu_id,stu_name,crs_name,sc_gradeFROMstudents,courses,scoresWHEREstudents.stu_id=scores.stu_idANDcourses.crs_id=scores.crs_id;建议初学者在创建视图之前，先编写并验证一个查询语句，确保其返回的结果准确无误后，再依据该查询来构建视图：8.1.2视图的创建与修改任务2：在“学生信息管理系统”数据库中，创建一个名称为“学生成绩_高等数学”的视图，使用这个视图可以按学号的升序显示学生的学号、姓名、课程名、高等数学成绩。代码如下：CREATEVIEW学生成绩_高等数学ASSELECTstudents.stu_id,stu_name,crs_name,sc_gradeFROMstudents,courses,scoresWHEREstudents.stu_id=scores.stu_idANDcourses.crs_id=scores.crs_idANDcrs_name='高等数学'ORDERBYstudents.stu_idASCWITHCHECKOPTION;为了确保该视图在更新时，在视图的权限范围之内。需要使用参数WITHCHECKOPTION。8.1.2视图的创建与修改2.修改视图

若需要修改视图，可以使用ALTERVIEW语句，或者先删除视图再重新创建。使用ALTERVIEW语句时，需要指定要修改的视图名称和新的查询语句。代码如下：ALTERVIEW<视图名>ASSELECT_statement[WITH[CASCADED|LOCAL]CHECKOPTION];ALTERVIEW语句的语法与CREATEVIEW类似，这里不再详细介绍。注意：要实现视图的修改，用户需要具有针对该视图的CREATEVIEW和DROP权限，以及由SELECT语句选择的每一列上的某些权限。8.1.2视图的创建与修改任务3：修改“学生成绩_高等数学”视图，增加课程编号（crs_id）字段。代码如下：ALTERVIEW学生成绩_高等数学ASSELECTstudents.stu_id,stu_name,courses.crs_id,crs_name,sc_gradeFROMstudents,courses,scoresWHEREstudents.stu_id=scores.stu_idANDcourses.crs_id=scores.crs_idANDcrs_name='高等数学'ORDERBYstudents.stu_idASCWITHCHECKOPTION;8.1.2视图的创建与修改2.删除视图

若要删除视图，必须拥有DROP权限。如果没有足够的权限，将无法删除视图。语法格式如下：LOCAL]CHECKOPTION];参数说明：

view_name是要删除的视图的名称。可以一次性删除多个视图，各个视图名称之间用逗号隔开。

IFEXISTS是可选的，它用于在视图不存在时避免产生错误。任务4：删除学生成绩、学生成绩_高等数学视图。代码如下：dropVIEW学生成绩,学生成绩_高等数学;注意：使用DROPVIEW语句只能删除视图的定义，而不会删除视图所依赖的数据。视图中的数据仍然存在于基本表中。

视图的使用8.1.38.1.3视图的使用1.通过视图操作数据

可以通过视图修改基表中的数据，包括UPDATE、INSERT和DELETE操作，由于视图是虚表，本身并不保存数据，所以通过视图来修改数据实质上是修改视图引用的基表中的数据，只有在满足下列条件时，才可以通过视图修改基础基表的数据：

只能引用一个基表的列：如果视图是基于两个或更多基表创建的，那么通过该视图进行的修改将不被允许。这是因为MySQL无法确定修改应该应用于哪个基表。如果确实需要修改多个基表，那么必须分别对每个基表进行修改。

直接引用表列中的基础数据：视图中的列必须直接对应于基表中的列，而不能是通过任何计算或函数派生得到的。例如，如果视图中的一列是通过CONCAT函数将两个基表列连接起来的，那么这一列就不能被修改。

不能修改计算列：如果视图中的列是通过某种计算得到的（如使用+、-、*、/等运算符），那么这一列也不能被修改。

不受GROUPBY、HAVING或DISTINCT子句的影响：如果视图包含GROUPBY、HAVING或DISTINCT子句，那么这些子句所作用的列将不能被修改。这是因为这些子句改变了数据的聚合方式或去重方式，使得修改操作无法准确地定位到基表中的具体行。

权限要求：用户需要对目标基表具有相应的UPDATE、INSERT或DELETE权限。8.1.3视图的使用任务5：在“学生信息管理系统”数据库中，创建一个名称为“学生成绩”的视图，使用这个视图可以显示学生的学号、姓名、课程编号、课程名、成绩。并通过这个视图将学号为23510102710101同学的大学英语修改为99分。首先创建视图学生成绩，代码如下：CREATEVIEW学生成绩ASSELECTstudents.stu_id,stu_name,courses.crs_id,crs_name,sc_gradeFROMstudents,courses,scoresWHEREstudents.stu_id=scores.stu_idANDcourses.crs_id=scores.crs_idWITHCHECKOPTION;图8.2视图学生成绩查询结果图8.3基本表scores查询结果接下来修改数据，代码如下：UPDATE学生成绩SETsc_grade=99WHEREstu_id='23510102710101'ANDcrs_name='大学英语'运行成功后，使用查询语句分别查询视图学生成绩，表scores,代码和运行结果如图8.2，图8.3所示。UPDATE修改数据实际上是将视图所依赖的基本表中的数据进行修改，如果一个视图依赖于多个基本表，则通过该视图修改数据，一次只能变动一个基本表的数据。8.1.3视图的使用任务6：在“学生信息管理系统”数据库中，创建一个名称为“学生信息”的视图，使用这个视图可以显示“51010271”班学生的学号、姓名、班级编号。通过该视图插入一条学生记录“23510102710121，李华,51010271”。首先创建视图学生信息，代码如下：CREATEVIEW学生信息ASSELECTstu_id,stu_name,cls_idFROMstudentsWHEREcls_id='51010271'WITHCHECKOPTION;图8.4视图学生信息查询结果图8.5基本表students查询结果接下来插入数据，代码如下：INSERTINTO学生信息VALUES('23510102710121','李华','51010271');运行成功后，使用查询语句分别查询视图学生信息，表students,代码和运行结果如图8.4，图8.5所示。从运行结果可知，记录插入成功。通过视图“学生信息”插入学生信息，只能插入班级编号为“51010271”的数据，如果插入其他班级编号的数据，系统将提示“1369-CHECKOPTIONfailed'sims.学生信息'”错误提示。因为“学生信息”视图中使用了WITHCHECKOPTION，则插入的数据必须符合视图定义中SELECT语句所设置的条件。使用INSERT语句时还需要注意，INSERT语句中必须包含FROM子句中指定表中所有不能为空的列。例如，通过“学生信息”视图插入数据时，如果“姓名”字段为空，则会出现插入错误。8.1.3视图的使用任务7：通过“学生信息”视图删除学号为“23510102710121”的学生记录。代码如下：DELETEFROM学生信息WHEREstu_id='23510102710121'如果视图来源于单个的基本表，可以使用DELETE语句通过视图来删除基本表中的数据，对于依赖多个基本表的视图，则不能使用DELETE语句。8.1.3视图的使用2.通过视图查询数据

可以像查询表一样查询视图。视图在逻辑上是一个虚拟表，它是基于一个或多个表的查询结果集。因此，你可以使用标准的SQL查询语句来查询视图，就像查询物理表一样。

在数据库的查询操作中，可以使用视图来简化查询，特别是当分析需要基于多个表或复杂计算时。可以创建一个包含所需分析数据的视图，然后在分析工具中查询该视图。8.1.3视图的使用（1）创建学生成绩信息视图。代码如下：CREATEVIEW学生成绩信息ASSELECTstudents.stu_id,stu_name,courses.crs_id,crs_name,sc_gradeFROMstudents,scores,coursesWHEREstudents.stu_id=scores.stu_idandcourses.crs_id=scores.crs_id;任务8：

查询选修了课程名为“数据库及应用”课程的学生学号、姓名、课程编号、课程名，成绩。

这个查询任务涉及students，courses，scores三个表的链接，我们可以将这些查询封装在一个视图中。这样，每次需要这些数据时，只需简单地查询视图即可，而无需重复编写复杂的查询语句。（2）查询选修了课程编号为“230101”课程的学生成绩信息。代码如下：SELECT*FROM学生成绩信息WHEREcrs_name='数据库及应用';8.1.3视图的使用任务9：查询选修了课程“网页制作技术”，且成绩高于该课程平均分的学生的学号、姓名和成绩。代码如下：SELECTstu_id,stu_name,sc_gradeFROM学生成绩信息WHEREcrs_name='网页制作技术'andsc_grade>(SELECTAVG(sc_grade)FROM学生成绩信息WHEREcrs_name='网页制作技术')；谢谢数据库技术8.2索引的创建与使用8.2索引的创建与使用

数据库中索引是一种高效获取数据的数据结构，它类似于书籍的目录，能够显著提升查询操作的效率。在MySQL数据库中，索引通常被创建在表的特定列上，这些索引充当了数据的快速检索路径，帮助MySQL迅速定位并访问所需的数据行，从而大大加快了针对这些列的查询速度，优化了数据库的整体性能。

索引的类型与结构8.2.18.2.1索引的类型与结构

主键索引：当表中的某个列被设为主键时，该列就是主键索引。主键索引具有唯一性和非空性。

唯一索引：索引列的值必须唯一，但允许为空值。唯一索引用于保证数据的唯一性。

普通索引：用表中的普通列构建的索引，没有任何限制。普通索引用于加速对该列的查询操作。

全文索引：主要用于文本数据的全文检索。01按功能分类

聚集索引：聚集索引要求表中数据存储的物理顺序与索引值的顺序一致。在InnoDB存储引擎中，主键索引默认为聚集索引。

二级索引（非聚集索引）：二级索引的叶子节点存储的是该字段值对应的主键值，而不是行数据本身。在查询时，需要先通过二级索引找到主键值，然后再通过主键值到聚集索引中找到行数据。02按存储形式分类

单列索引：一个索引只包含单个列。

组合索引：一个索引包含多个列。03按作用字段个数划分1.索引的类型8.2.1索引的类型与结构1.索引的结构

B-Tree是一种平衡树结构，其所有值都出现在叶子节点，且叶子节点形成一个单向链表。B-Tree索引具有查询效率高、支持范围查询和排序操作等优点，是MySQL中最常用的索引数据结构。哈希索引采用哈希算法，将键值换算成哈希值，并映射到对应的槽位上。哈希索引只能用于等值比较（=、IN），不支持范围查询。其查询效率通常很高，但在处理哈希冲突时可能需要扫描链表。

全文索引主要用于文本数据的全文检索，如文章的标题和内容。在MySQL中，全文索引通常用于MyISAM存储引擎，但在MySQL5.6及更高版本中，InnoDB存储引擎也支持全文索引。010203B-Tree索引

哈希索引全文索引索引的类型与结构8.2.28.2.2索引的特点1.索引的优点

加速查询：索引允许数据库系统直接跳到数据所在位置，避免了全表扫描，尤其是在大型数据表中，这种效果尤为明显。通过索引，数据库可以更快地定位到符合条件的数据，从而提高查询效率。

提高响应速度：由于查询可以快速定位数据，因此查询的响应时间通常会显著缩短。这对于需要快速响应的在线应用来说尤为重要。

保证数据记录的唯一性：通过创建唯一索引，可以确保表中的某一列或某几列的数据记录是唯一的，防止重复数据的插入。

实现表与表之间的参照性：索引可以用于外键约束，确保表与表之间的数据一致性。

减少排序和分组的时间：在使用ORDERBY或GROUPBY查询语句进行数据检索时，索引可以帮助减少排序和分组的时间，因为B-Tree结构的索引本身就是按照索引字段的值有序存储的。8.2.2索引的特点3.索引的优化

选择合适的列创建索引：对于经常用于WHERE查询条件、GROUPBY操作或ORDERBY操作的字段，可以创建索引以提高查询效率。同时，应尽量避免在经常更新的字段上创建索引。

创建联合索引：当查询条件涉及多个字段时，可以创建联合索引以进一步提高查询效率。在创建联合索引时，应将取值离散大的字段放在前面，以更有效地缩小结果集范围。

使用前缀索引：对于包含很长字符串描述的字段，如文章的内容摘要，可以只选取字符串的前几个字符来创建前缀索引，以减少索引字段所占用的存储空间并提高查询速度。但需要注意的是，前缀索引在某些情况下可能无法用于ORDERBY操作和覆盖索引。

主键索引最好是自增的：在InnoDB中，主键索引默认是聚簇索引。当使用自增主键时，每次插入新数据都会按顺序追加到当前索引节点的位置，这种追加式的插入操作效率极高。若使用非自增主键，则可能导致数据调整和页面变动，增加系统开销。8.2.2索引的特点1.索引的优点

加速查询：索引允许数据库系统直接跳到数据所在位置，避免了全表扫描，尤其是在大型数据表中，这种效果尤为明显。通过索引，数据库可以更快地定位到符合条件的数据，从而提高查询效率。

提高响应速度：由于查询可以快速定位数据，因此查询的响应时间通常会显著缩短。这对于需要快速响应的在线应用来说尤为重要。

保证数据记录的唯一性：通过创建唯一索引，可以确保表中的某一列或某几列的数据记录是唯一的，防止重复数据的插入。

实现表与表之间的参照性：索引可以用于外键约束，确保表与表之间的数据一致性。

减少排序和分组的时间：在使用ORDERBY或GROUPBY查询语句进行数据检索时，索引可以帮助减少排序和分组的时间，因为B-Tree结构的索引本身就是按照索引字