MySQL数据库项目化教程（第2版）（微课版）课件 项目七 数据库索引与视图

MySQL数据库项目化教程微课版V2项目七：

数据库索引与视图项目任务知识目标学习目标任务1：索引的创建与删除任务2：视图的创建与管理（1）掌握索引、视图的含义和作用（2）了解索引的分类（3）掌握创建、删除索引的方法（4）掌握创建、修改、更新、

删除视图的方法能力目标素质目标学习目标（1）能够创建索引（2）能够删除索引（3）能够创建视图（4）能够查看、修改视图（5）能够更新、删除视图（1）形成自主好学的学习态度（2）养成务实解决问题的习惯（3）培养团队协作的精神课程思政（素养提示）学习目标（1）不断更新理念，利用先进思维方法解决问题，引导学生注重先进理论学习及思维拓展。（2）形成对实际数据的保护意识，引导学生注重维护数据安全。知识导图学习目标任务分析任务实现任务描述Part

2Part

3Part

1任务一

索引创建与删除过渡页任务分析任务实现Part

1Part

2Part

3任务描述任务一

索引创建与删除本节的任务是创建和删除索引。创建索引可以通过两种不同的方法：一是创建表的时候创建索引；二是在已存在的表中创建索引。对已存在的不必要索引进行删除，也可以利用两种方式：一是使用ALTERTABLE删除索引；二是DROPINDEX删除索引。任务描述过渡页任务分析任务实现Part

1Part

2Part

3任务描述任务一

索引创建与删除根据索引的应用范围和查询需求的不同，索引分成：（1）普通索引

（2）唯一索引

（3）主键索引（4）全文索引

（5）单列索引

（6）多列索引（7）空间索引任务分析索引是对数据库中单列或者多列的值进行排序后的一种特殊数据库结构，利用它可以快速指向数据库中的数据表的特定记录，索引是提高数据库性能的重要方式。过渡页任务分析任务实现Part

1Part

2Part

3任务描述任务一

索引创建与删除1.创建表的时候创建索引CREATETABLE表名(字段名数据类型[约束条件],字段名数据类型[约束条件]……字段名数据类型[UNIQUE|FULLTEXT|SPATIAL]INDEX|KEY[别名](字段名1[(长度)][ASC|DESC]));语法格式任务实现[UNIQUE|FULLTEXT|SPATIAL]可选参数，分别表示唯一索引、全文索引和空间索引。INDEX和KEY为同义词，两者作用相同，用来指定创建索引。别名是可选参数，用来给创建的索引取新的名字。字段名为需要创建索引的字段，该字段必须从数据表中定义的多个字段中选择。长度为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度。ASC或DESC指定升序或降序的索引值存储。(1)创建普通索引competion数据库中，创建一张student数据表，并为其字段st_id创建普通索引。对应代码任务实现(2)唯一索引在competion数据库中，创建一张department数据表，并为其字段dp_name创建唯一索引。对应代码任务实现(3)主键索引在competion数据库中，创建一张teacher数据表，并为其字段tc_id创建主键索引。对应代码任务实现(4)单列索引在competion数据库中，创建一张project数据表，并为其字段pr_name创建单列索引对应代码任务实现(5)多列索引在competion数据库中，创建一张class数据表，并为其创建字段class_id和class_name设置多列索引对应代码任务实现(6)全文索引在competition数据库中，创建一张st_project数据表，并为其字段remark创建全文索引对应代码任务实现(7)空间索引在competition数据库中，创建一张admin数据表，并为其字段ad_name创建空间索引。对应代码任务实现2.在已存在的表创建索引--使用CREATEINDEX创建索引CREATE

[UNIQUE|FULLTEXT|SPATIAL]INDEX别名ON表名(字段名1[(长度)][ASC|DESC]));语法格式任务实现[UNIQUE|FULLTEXT|SPATIAL]可选参数，分别表示唯一索引、全文索引和空间索引。INDEX和KEY为同义词，两者作用相同，用来指定创建索引。属性名为需要创建索引的字段列，该列必须从数据表中该定义的多个列中选择。长度为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度。ASC或DESC指定升序或降序的索引值存储。（1）在已存在的表创建普通索引在competion数据库中，为已经存在的student1表的字段st_no添加名为index_st_no的普通索引。对应代码任务实现步骤1：添加索引之前用SHOWCREATETABLE语句查看student1表的结构（1）在已存在的表创建普通索引对应代码任务实现步骤2：输入如下代码：CREATEINDEXindex_st_noONstudent1(st_no);步骤3：应用SHOWCREATETABLE语句再查看student1表结构（2）在已存在的表创建唯一索引对应代码任务实现步骤1：输入如下代码：CREATEUNIQUEINDEXuni_st_idONstudent1(st_id);步骤2：应用SHOWCREATETABLE语句再查看student1表结构在competition数据库中，为已经存在的student1表的字段st_id添加名为uni_st_id的唯一索引。（3）在已存在的表创建多列索引对应代码任务实现步骤1：输入如下代码：CREATEINDEXmulti_stONstudent1(st_name，st_no);步骤2：应用SHOWCREATETABLE语句再查看student1表结构在competition数据库中，为已经存在的student1表的字段st_no和st_name添加名为multi_st的多列索引。2.在已存在的表创建索引--使用ALTERTABLE创建索引ALTERTABLE表名

ADD[UNIQUE|FULLTEXT|SPATIAL]INDEX索引名(字段名[(长度)][ASC|DESC])语法格式任务实现[UNIQUE|FULLTEXT|SPATIAL]可选参数，分别表示唯一索引、全文索引和空间索引。INDEX和KEY为同义词，两者作用相同，用来指定创建索引。属性名为需要创建索引的字段列，该列必须从数据表中该定义的多个列中选择。长度为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度。ASC或DESC指定升序或降序的索引值存储。（1）使用ALTERTABLE创建普通索引在competition数据库中，为已经存在的student1表的字段st_no添加名为index_st_no的普通索引。对应代码任务实现步骤1：添加索引之前用SHOWCREATETABLE语句查看student1表的结构，发现该表未对任何字段设置索引。对应代码任务实现步骤2：输入如下代码：altertablestudent1addIndexmulti(st_no,st_name);步骤3：应用SHOWCREATETABLE语句再查看student1表结构（1）使用ALTERTABLE创建普通索引（2）使用ALTERTABLE创建唯一索引在competition数据库中，为已经存在的student1表的字段st_id添加名为uni_st_id的唯一索引。对应代码任务实现步骤1：输入如下代码：ALTERTABLEstudent1ADDUNIQUEINDEXuni_st_id(st_id);对应代码任务实现步骤1：输入如下代码：ALTERTABLEstudent1ADDINDEXmulti_st(st_name，st_no);（3）使用ALTERTABLE创建多列索引在competition数据库中，为已经存在的student1表的字段st_no和st_name添加名为multi_st的多列索引。对应代码任务实现步骤1：输入如下代码：ALTERTABLEstudent1ADDFULLTEXTINDEXfull_st_info(st_info);（3）使用ALTERTABLE创建多列索引在competition数据库中，为已经存在的student1表的字段st_info添加名为full_st_info的全文索引。对应代码任务实现（3）使用ALTERTABLE创建索引使用SHOWCREATETABLE语句再次查看student1表的结构，可发现，名为uni_st_id的唯一索引、名为multi_st的多列索引和名为full_st_info的全文索引添加成功3.删除索引DROPINDEX别名ON表名;语法格式任务实现（1）使用DROPINDEX删除索引在competition数据库中，将student1表的字段st_no设置的普通索引删除。例题代码：DROPINDEXindex_st_noONstudent1;3.删除索引ALTERTABLE表名

DROPINDEX别名;语法格式任务实现（2）使用

ALTERTABLE删除索引在competition数据库中，将student1表的字段st_name和st_no设置的多列索引删除。例题代码：ALTERTABLEstudent1DROPINDEXmulti_st;注意事项任务总结本节结合学生技能竞赛管理系统中的各个表，讲解了MySQL数据库的索引的基础知识，通过任务介绍了创建索引和删除索引的方法。读者应该重点掌握创建索引的两种方法。任务总结在实际项目中，读者应结合查询速度、磁盘空间、维护开销等因素尝试多个不同的索引从而建立最优的索引。过渡页任务分析任务实现Part

1Part

2Part

3任务描述任务二

视图的创建与管理以学生技能竞赛管理系统为例，在MySQL上对单表或多表创建视图，并对视图进行查看、修改、更新、删除等管理，从而快速掌握视图的相关知识和技能任务描述过渡页任务分析任务实现Part

1Part

2Part

3任务描述任务二

视图的创建与管理任务分析

视图和真实的表一样，包含一系列带有名称的列和行数据。在数据库中视图并不以存储的数据值集形式存在。它是一个虚拟表，视图的数据来自当前或其它数据库的一个或多个表，或者其它视图。通过视图进行查询没有任何限制当对视图的数据进行修改时，相应的基本表也会发生变化，当基本表的数据发生变化时，视图的数据也会产生变化。任务分析与直接从数据表中读取对比，视图有以下几个优点（1）视图能简化操作（2）视图能增加安全性（3）视图能增加逻辑数据独立性视图的缺点：（1）性能：从数据库视图查询数据可能会很慢，特别是如果视图是基于其他视图创建的。（2）表依赖关系：将根据数据库的基础表创建一个视图。每当更改与其相关联的表的结构时，都必须更改视图任务分析创建视图时，需要注意以下几点：（1）定义视图的用户必须对所参照的表或视图有查询(可执行SELECT语句)权限。（2）在定义中引用的表或视图必须存在。（3）创建视图时默认是在当前数据库创建的，如果需要在指定数据库创建需要指定具体数据库名称。任务分析以下几种情况是不能更新视图（1）视图中包含COUNT()、SUM()、MAX()和MIN()等函数。（2）视图中包含UNION、UNIONALL、DISTINCT、GROUPBY和HAVIG等关键字。（3）常量视图。（4）视图中的SELECT中包含子查询。（5）创建视图时，设置ALGORITHM为TEMPTABLE类型。过渡页任务分析任务实现Part

1Part

2Part

3任务描述任务二

视图的创建与管理1.创建视图CREATE[ORREPLACE][ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}]VIEW视图名[(字段名)]ASSELECT语句[WITH[CASCADED|LOCAL]CHECKOPTION];语法格式任务实现ORREPLACE是可选参数，表示该语句替换已有视图。ALGORITHM是可选参数，表示视图选择的算法属性名为视图的列定义明确的名称，多个列名由逗号隔开。AS表示指定视图要执行的操作。SELECT语句参数是一个完整的查询语句，表示从表中查出满足条件的记录，将这些记录导入视图中,可在SELECT语句中查询多个表或视图。WITHCHECKOPTION是可选参数。CASCADED是可选参数WITHCHECKOPTION是可选参数(1)在单表上创建视图为数据库competition中的student表创建视图view_st_man，包含男生的st_no，st_name，st_sex字段的信息。对应代码任务实现步骤1：在单表上创建视图前，用SELECT语句查看student表的完整信息。对应代码任务实现步骤2：输入如下代码：CREATEVIEWst_man_viewASSELECTst_no,st_name,st_sexFROMstudentWHEREst_sex='男';步骤3：视图st_man_view创建后，通过SELECT语句查看视图中的数据(1)在单表上创建视图(2)在多表上创建视图为数据库competition中的department表和class表创建一个视图dp_class_view，视图内容包括dp_name，class_name，class_grade字段。对应代码任务实现步骤1：在多表上创建视图前，用SELECT语句查看department表和class表的完整信息。对应代码任务实现步骤2：输入如下代码：CREATEVIEWdp_class_viewASSELECTdepartment.dp_name,class.class_name,class.class_gradeFROMdepartment,classWHEREdepartment.dp_id=class.dp_id;步骤3：视图dp_class_view创建后，通过SELECT语句可查看视图中的数据(2)在多表上创建视图2.查看视图DESCRIBE视图名;语法格式任务实现（1）DESCRIBE语句查看视图使用DESCRIBE语句查看上文中创建的视图dp_class_view例题代码：DESCRIBEdp_class_view;2.查看视图SHOWTABLESTATUSLIKE‘视图名’;语法格式任务实现（2）SHOWTABLESTATUS语句查看视图使用SHOWTABLESTATUS语句查看上一节中创建的视图dp_class_view的定义信息。例题代码：SHOWTABLEstatusLIKE'dp_class_view'\G2.查看视图SHOWCREATEVIEW视图名;语法格式任务实现（3）SHOWCREATEVIEW语句查看视图使用SHOWCREATEVIEW语句查看上一节中创建的视图dp_class_view的定义信息。例题代码：SHOWCREATEVIEWdp_class_view\G3.修改视图对应代码任务实现（1）CREATEORREPLACEVIEW语句修改视图使用CREATEORREPLACEVIEW语句修改视图view_st_man，由原来的st_no，st_name，st_sex等字段更换为st_no，st_name，st_sex，st_password4个字段。3.修改视图ALTER[ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}]VIEW视图名[(字段名)]ASSELECT语句[WITH[CASCADED|LOCAL]CHECKOPTION];语法格式任务实现（2）ALTERVIEW语句修改视图使用ALTERVIEW语句修改上一节中创建的视图st_man_view，把视图中的字段改为st_no,st_sex字段的信息4.更新视图对应代码任务实现（1）UPDATE语句更新视图使用UPDATE语句更新视图dp_class_view中class_name字段对应的数据值，将原来的数值“财务管理”改为“电子商务”。例题代码：UPDATEdp_class_viewSETclass_name='电子商务'WHEREclass_name='财务管理';4.更新视图对应代码任务实现（2）使用INSERT语句更新视图使用INSERT语句向class表中插入一条记录，从而使视图dp_class_view对应增加一条记录。输入SELECT*FROMclass语句查看更新后原表class中的数据4.更新视图对应代码任务实现（3）使用DELETE语句删除视图记录使用DELETE语句删除视图st_man_view中的学号为“2017060205”的一条记录，从而使student表中对应的记录也删除。例题代码：DELETEFROMst_man_viewWHEREst_no='2017060205';5.删除视图DROPVIEW[IFEXISTS]视图名列表[RESTRICT|CASCADE]语法格式任务实现IFEXISTS是可选参数，判断视图是否存在，不存在不执行，存在则执行。“视图名列表”参数表示要删除的视图的名称和列表，各个视图名称之间用逗号隔开。RESTRICT确保只有不存在相关视图和完整性约束的视图才能删除。CASCADE任何相关视图和完整