MySQL数据库应用 课件 项目6、7 视图与索引、存储过程与存储函数

项目六视图与索引225226任务1创建视图任务2修改和删除已创建的视图任务3创建和删除索引创建视图任务12271. 了解视图的概念。2. 了解视图的作用。3. 掌握视图的创建方法。4. 能使用SQL语句创建视图。228229视图封装了SELECT查询的定义，与表不同，它在物理上不是真实存在的，而是一张虚拟表。数据库视图有助于限制特定用户对数据的访问，为数据提供了额外的安全层，也有助于简化复杂的查询。本任务要求使用Navicat创建一个名为“s_count”的视图，如图所示，该视图可统计“数媒111班”的人数。230创建视图231一、视图的概念MySQL中的视图是一张虚拟表，该表与真实的表类似，包含一系列带有名称的列（字段），但不存储实际数据。视图的行数据来自定义视图时所引用的数据表，并且在查询视图时动态生成。视图的操作与表非常相似，用户可以使用SELECT语句查询视图中的数据。在满足一定条件时，也可使用INSERT、UPDATE和DELETE语句操作视图数据。从MySQL5.0.1版本开始，MySQL支持视图功能，视图不仅可以使用户操作更加方便，还可以保障数据库系统的安全。二、视图的作用1. 简化操作视图可以使查询语句更加直观和易于理解，将常用查询逻辑封装为视图，可以简化复杂的查询操作。2. 提高数据安全性通过视图可以限制用户对底层表的访问权限，选择性地展示数据，使不同用户只能看到被授权的信息。3. 实现逻辑数据独立视图可屏蔽真实表结构变化带来的影响，保证数据与程序之间的逻辑独立性（即逻辑数据独立性）。232三、视图的创建创建视图时，从基本表中提取数据的逻辑封装。视图本身并不存储实际数据，而是保存一个SELECT查询语句，每次查询视图时动态执行该语句生成结果。创建视图使用CREATEVIEW语句，其基本的SQL语法格式如下。233修改和删除已创建的视图任务22341. 了解更新视图数据的方法。2. 了解查看视图的方法。3. 能修改视图结构。4. 能删除视图。235236修改视图是指修改数据库中存在的视图，当基本表的某些字段发生变化时，可以通过修改视图来保持视图与基本表的一致性。删除视图时，只能删除视图的定义，不会删除数据。本任务要求通过Navicat在教学管理系统数据库“schoolsys”中使用CREATEORRE-PLACEVIEW语句修改视图，如图所示，并用DROP语句删除视图。修改视图成功237一、视图数据的更新1. 在已创建的视图中插入数据对视图数据的更新本质上是对其基本表数据的更新，包括插入、修改和删除视图数据。在视图中插入数据与在表中插入数据一样，使用基本的INSERT语句插入数据，其基本的SQL语法格式如下。2. 在已创建的视图中修改数据修改视图中的数据可使用UPDATE语句，可同时修改多行数据，其基本的SQL语法格式如下。2383. 在已创建的视图中删除数据删除视图中数据的基本语法与删除表中的数据语法一致，都可使用DELETE语句，其中DELETE语句中使用允许WHERE子句指定删除条件，具体的SQL语法格式如下。239二、视图的查看1. 使用DESCRIBE 语句查看视图的基本信息使用DESCRIBE语句可以查看视图字段的定义、字段的数据类型、是否为空等信息，具体的SQL语法格式如下。2. 使用 SHOW TABLE STATUS 语句查看视图的基本信息使用SHOWTABLESTATUS语句可以查看视图的基本信息，具体的SQL语法格式如下。2403. 使用 SHOW CREATE VIEW 语句查看视图的基本信息使用SHOWCREATEVIEW语句可以查看视图的名称、创建视图的语句等信息，具体的SQL语法格式如下。4. 使用SELECT语句查看所有视图的详细信息在MySQL中，数据库“information_schema”下的数据表“views”中存储了所有视图的定义。通过

对数据表“views”的查询，可以查看数据库中所有视图的详细信息，具体的SQL语法格式如下。241三、视图结构的修改1. 使用 CREATE OR REPLACE VIEW 语句修改视图通常意味着更改视图的定义，可以使用CREATEORREPLACEVIEW语句或ALTERVIEW语句实现。使用CREATEORREPLACEVIEW语句可以修改视图结构，具体的SQL语法格式如下。2422. 使用 ALTER VIEW语句使用ALTERVIEW语句可以对视图的结构进行修改，该语句必须在视图已存在时使用（否则报错），具体的SQL语法格式如下。243四、视图的删除删除视图是指删除数据库中已存在的视图。删除一个或多个视图可使用DROPVIEW语句，具体的SQL语法格式如下。244

创建和删除索引任务32451. 了解索引的概念。2. 掌握创建、查看和删除索引的方法。3. 能创建和删除索引。246数据库索引是为了加快查询速度而附加于数据表字段的一种标识，相当于一本书的目录，有助于MySQL高效获取数据。本任务要求使用Navicat，使用CREATETABLE语句创建表，并在创建表时对字段“s_id”创建唯一索引，如图所示。247创建唯一索引一、索引的概念索引是一个独立存储在磁盘上的数据结构，基于表中特定字段建立索引键与数据行的映射关系，用于快速定位数据。所有MySQL中的数据类型都可以被索引，为相关字段设置索引是提高查询操作速度的最佳途径。如果不使用索引，MySQL需逐行扫描全表，表越大查询效率越低。如果为表中需要查询的字段设置索引，MySQL能快速定位到目标数据所在位置，避免全表扫描。248二、索引的类型1.按特性分类（1）普通索引普通索引是最基本的索引类型，无唯一性约束，允许有重复值，可以应用于大多数数据类型的列上，并且可以加快SELECT查询的速度。249（2）唯一索引唯一索引能确保索引列值唯一，不允许出现重复值，常用于保证数据完整性。（3）全文索引全文索引专为文本搜索设计，主要用于处理大文本字段中的关键词搜索，支持复杂的自然语言查询和布尔模式匹配。（4）空间索引空间索引用于地理信息系统应用，针对空间数据类型，能高效地执行空间查询。2502.按覆盖列数分类（1）单列索引单列索引是在单个列上创建的索引，适用于基于单一字段的查询。（2）组合索引组合索引是在多个列上创建的索引，当查询条件同时包含这些列时，组合索引可以显著提高查询效率。251三、索引的创建1. 在创建表的同时创建索引（1）创建普通索引普通索引是最基本的索引类型，没有唯一性等的限制，其作用是加快对数据的访问速度。创建普通索引的SQL语法格式如下。252（2）创建唯一索引创建唯一索引的主要原因是减少查询索引列操作的执行时间，尤其是在面对比较庞大的数据表时。创建唯一索引的具体SQL语法格式如下。253（3）创建单列索引单列索引是在数据表中的某个字段上创建的索引，一张表中可以创建多个单列索引。创建单列索引的具体SQL语法格式如下。254（4）创建组合索引组合索引是在多个字段创建的索引。创建组合索引的具体SQL语法格式如下。255（5）创建全文索引使用关键字FULLTEXT创建的全文索引可以用于全文搜索。创建全文索引的具体SQL语法格式如下。256（6）创建空间索引空间索引支持MyISAM和InnoDB引擎（需MySQL5.7及以上），针对空间数据类型的字段，且该字段值必须为非空。创建空间索引的具体SQL语法格式如下。2572. 在已存在的表上创建索引（1）使用ALTERTABLE使用ALTERTABLE语句可以为已存在的表创建索引，与创建表时创建索引的语法不同的是，在这里使用了ALTERTABLE语句和ADD关键字，其中ALTERTABLE语句的作用是声明修改的数据表，ADD关键字表示向表中添加索引，具体的SQL语法格式如下。258（2）使用CREATEINDEXCREATEINDEX语句也可以为已存在的表添加索引。在MySQL中，CREATEINDEX被映射到一个ALTERTABLE语句上，具体的SQL语法格式如下。259四、索引的查看使用SHOWINDEX语句查看指定表中创建的索引，具体的SQL语法格式如下。260五、索引的删除1. 使用ALTER TABLE语句删除索引使用ALTERTABLE语句可删除任意表中的索引，具体的SQL语法格式如下。2. 使用 DROP INDEX 语句删除索引DROPINDEX语句在内部被映射到一个ALTERTABLE语句中，具体的SQL语法格式如下。261项目七存储过程与存储函数262263任务1创建和调用存储过程任务2创建存储函数任务3使用变量及流程控制语句任务4修改和删除存储过程与存储函数创建和调用存储过程任务12641. 了解存储过程的概念。2. 能创建存储过程。3. 能调用存储过程。4. 能使用游标。265266存储过程是一组预定义的SQL语句与控制逻辑的集合，作为数据库服务器端程序运行，功能远超简单的批处理。对数据库进行复杂操作时，可将这些复杂操作用存储过程封装起来，与数据库提供的事务处理结合在一起使用。本任务要求通过Navicat，在教学管理系统数据库“schoolsys”中使用SQL语句，创建一个名为“everyone_grade”的存储过程，如图所示。该存储过程能实现查找所有学生的成绩，并通过调用语句执行该存储过程。267创建存储过程268一、存储过程的概念在大型数据库系统中，存储过程是一组为了完成特定功能的SQL语句集，并以特定的名字存储在数据库中。存储过程经过第一次编译后调用时不需要再次编译，用户可通过指定存储过程的名称和参数来执行它。存储过程是数据库中的一个重要对象，任何一个设计良好的数据库应用程序都会用到存储过程。二、存储过程的创建使用存储过程将简化操作，减少多余的操作步骤，同时，还可以减少操作过程中的失误，提高效率。在MySQL中，使用CREATE PROCEDURE语句创建存储过程，需配合DELIMITER修改语句结束符（避免分号冲突），基本的SQL语法格式如下。269三、存储过程的调用在MySQL中，可使用CALL语句调用已创建的存储过程，其基本SQL语法格式如下。调用存储过程时，若已创建的存储过程中没有设置参数，调用存储过程同样不需要参数。270四、游标的使用在MySQL中，查询语句常返回多行数据，在存储过程或函数中，可以使用游标来逐个读取查询结果集中的记录。通过控制游标的移动，用户可以逐行查看和操作这些数据，但不能跳过任何记录。1. 声明游标在MySQL中，使用DECLARE语句来声明游标，具体的SQL语法格式如下。2712. 打开游标可使用关键字OPEN打开已声明的游标，该语句会将游标与相应的查询结果集关联起来，以便对查询结果集中的每条记录进行处理。如果游标未被声明，则OPEN语句会报错。具体的SQL语法格式如下。3. 使用游标使用游标的具体SQL语法格式如下。2724. 关闭游标使用CLOSE语句可关闭已使用的游标，具体的SQL语法格式如下。如果游标未打开，则使用该语句会报错。需要注意的是，如果游标未被明确地关闭，程序执行到END语句时会自动关闭游标，释放占用的资源和内存。273创建存储函数任务22741. 了解存储函数的概念。2. 能创建存储函数。3. 能调用存储函数。275276存储函数是MySQL提供的一种机制，它可以用来存储并重用在SQL查询中使用的常用逻辑或计算。通过存储函数，能在内置函数的基础上，以更简洁的方式实现个性化的数据处理。本任务要求通过Navicat，在教学管理系统数据库“schoolsys”中使用SQL语句，创建一个名为“s_find”的存储函数，如图所示。通过该存储函数，可以在输入学生的学号后，在表“tb_student”中查询学生的姓名，若学生存在，则返回该学生的姓名；若学生不存在，则返回“查无此人”。277创建存储函数278一、存储函数的概念在MySQL中，存储函数是用户自定义的SQL代码块，用于扩展内置函数的功能。类似于存储过程，存储函数封装了一系列SQL语句，以执行特定的数据库操作或计算，两者的区别在于：存储函数必须使用RETURN子句返回单一值，而存储过程无须强制返回值，需使用CALL语句单独调用，不能在表达式中直接使用。二、存储函数的创建存储函数是用户自定义的函数，先定义后调用。在MySQL中，使用CREATE FUNCTION语句创建，其基本的SQL语法格式如下。279三、存储函数的调用在MySQL中，存储函数的使用方法与MySQL内置函数的使用方法是一样的，都需使用SELECT语句调用已创建的存储函数，其基本的SQL语法格式如下。280使用变量及流程控制语句任务32811. 掌握变量的使用方法。2. 了解流程控制语句的分类。3. 掌握流程控制语句的使用方法。4. 能使用流程控制语句对数据表进行复杂的查询操作。282在MySQL中，变量用于存储中间结果或临时数据，流程控制语句用于实现复杂逻辑。通过变量和流程控制语句，可增强存储过程的灵活性。283本任务要求通过Navicat，在教学管理系统数据库“schoolsys”

中使用CREATE PRO-CEDURE语句创建一个名为“s_n”的存储过程，使用关键字IN声明输入参数“n”（数据类型INT），使用关键字DECLARE声明变量“sum”和“num”，并使用关键字SET为变量赋值，使存储过程“s_n”能计算1至任意数的累加和，如图所示。284创建存储过程，求1至任意数的累加和一、变量的使用在MySQL中，除了支持标准的存储过程和存储函数外，还引入了表达式。MySQL中的表达式与其他高级语言的表达式一样，由操作数和运算符构成。变量是操作数的重要表示方式，可以用来临时存储数据。在存储过程和函数中都可以定义及使用变量。变量主要分为系统变量、用户变量和局部变量。2851. 系统变量系统变量由MySQL服务器维护，用于控制服务器的行为和配置，系统变量分为全局变量和会话变量，通常由数据库管理员设置，以优化性能或调整服务器行为，其基本SQL语法格式如下。2862. 用户变量用户变量是由用户自己定义和管理的变量，作用范围仅限于当前客户端连接（即会话），用户变量的名字以 @ 开头，允许在同一会话中的多个语句之间存储和传递值，用户变量不需要显式声明，可以直接赋值并使用，其基本SQL语法格式如下。2873. 局部变量局部变量是在存储过程或函数内部声明的变量，作用范围仅限于声明的过程或函数体内，局部变量需要显式声明，并且可以用于保存临时数据或控制流程逻辑，其基本SQL语法格式如下。288二、流程控制语句的概念流程控制语句用于控制存储过程中SQL语句的执行顺序，是完成复杂操作必不可少的一部分。MySQL中的流程控制语句可以分为三类，其中IF、CASE语句为条件判断语句，LOOP、REPEAT、WHILE语句为循环语句，LEAVE、ITERATE语句为跳转语句。每个流程控制语句中可能包含一个单独语句，也可以使用BEGIN...END语句组合的语句块。2891. IF语句IF语句用来进行条件判断，根据变量是否满足条件（可包含多个条件）来执行不同的语句，是流程控制语句中最常用的判断语句。ELSEIF语句用于对多个条件判断语句进行判断，产生不同分支，输出不同结果。2902. CASE语句CASE语句也用来进行条件判断，与IF语句不同的是，CASE语句提供了多个条件进行选择，可以实现比IF语句更复杂的条件判断。3. LOOP语句LOOP语句可以使某些特定的语句重复执行，是不含条件判断的简单循环。LOOP语句内的子句一直重复执行，直到循环被LEAVE语句退出，则跳出循环。2914. LEAVE语句LEAVE语句主要用于跳出循环控制，可以用在循环语句内，也可以用在以BEGIN...END语句包裹起来的程序体内，表示跳出循环或者跳出程序体的操作。5. ITERATE语句ITERATE为“再次循环”的意思，只能用在循环语句（LOOP、REPEAT和WHILE）内，作用是跳过当前循环的剩余语句，直接回到循环开始处重新执行。2926. REPEAT语句REPEAT语句是有条件控制的循环语句，每次语句执行完毕，会对条件表达式进行判断，如果表达式返回值为TRUE，则循环结束，否则重复执行循环中的语句。7. WHILE语句WHILE语句也是有条件控制的循环语句。WHILE语句和REPEAT语句不同的是，WHILE语句是满足条件时执行循环内的语句，否则退出循环。293三、流程控制语句的使用1. IF语句IF语句可根据表达式的结果为TRUE或FALSE执行相应的语句，其具体的SQL语法格式如下。2942. CASE语句CASE语句是条件判断语句，可同时进行多个条件的判断。CASE语句有两种格式：第一种为固定值条件判断，其具体的SQL语法格式如下。295第二种为非固定值条件判断，其具体的SQL语法格式如下。2963. LOOP语句LOOP循环语句可用来重复执行某些语句，与IF语句和CASE语句相比，LOOP语句只是创建一个循环操作的过程，并不进行条件判断。LOOP语句内的子句会一直重复执行直到循环被退出，所以LOOP语句需要通过与IF语句或CASE语句、LEAVE语句或ITERATE语句结合，构成一个完整的循环。LEAVE语句用来退出任何被标注的流程控制结构，常用于结束循环。ITERATE语句只能出现在LOOP语句、REPEAT语句和WHILE语句内。LOOP语句的具体SQL语法格式如下。2972984. WHILE语句WHILE语句用于创建一个带条件判断的循环过程，与REPEAT语句不同，WHILE语句在执行循环语句前先对指定的条件表达式进行判断，如果结果为TRUE，就执行循环内的语句，否则退出循环。其具体的SQL语法格式如下。2995. REPEAT语句REPEAT语句用于创建一个带条件判断的循环过程，每次循环语句执行完毕会对条件表达式进行判断，如果结果为TRUE，则循环结束，否则重复执行循环中的语句。其具体的SQL语法格式如下。3006. LEAVE语句使用LEAVE语句，可以在循环过程中提前退出循环，它可以用在循环语句内，也可以用在以 BEGIN...END 语句包裹起来的程序体内，表示跳出循环或者跳出程序体的操作。其具体的SQL语法格式如下。3017. ITERATE语句使用ITERATE语句，可以在循环语句中提前跳出接下来的循环语句，进行下次循环，它可以用在循环语句内，也可以用在以 BEGIN...END 语句包裹起来的程序体内，跳过当前循环的剩余语句，直接回到循环开始处重新执行。其具体的SQL语法格式如下。302修改和删除存储过程与存储函数任务43031. 了解修改存储过程与存储函数特性的方法。2. 掌握查看存储过程与存储函数的操作步骤。3. 能删除存储过程与存储函数。304修改存储过程与存储函数主要是指修改它们的特性，例如修改访问权限等。修改存储过程与修改存储函数、删除存储过程与删除存储函数，这两类操作方法高度相似，因此，本任务只介绍修改和删除存储过程的操作。305本任务通过Navicat，在教学管理系统数据库“schoolsys”中使用SQL语句，将存储过程“everyone_grade”的特性修改为“MODIFIES SQL DATA”，如图所示，随后使用关键字DROP删除该存储过程。306修改存储过程一、修改存储过程与存储函数的特性1. 存储过程与存储函数的特性在MySQL中，存储过程与存储函数的特性用于设置其访问权限和规定程序中对SQL语句的操作权限，存储过程与存储函数的特性及功能见下表。307308存储过程与存储函数的特性及功能2. 修改存储过程的特性使用ALTER语句可以修改存储过程的特性，其具体的SQL语法格式如下。3. 修改存储函数的特性和