《MySQL数据库项目式教程》项目六 索引与视图

MySQL项目六索引与视图2019年7月掌握索引与视图的创建与使用方法学习使用索引与视图的优点学习目标MySQL了解索引与视图的概念具有使用Navicat工具操作索引与视图的能力重点、难点MySQL索引一一、索引MySQL1.索引的分类普通索引唯一索引普通索引是最基本的索引类型，允许在定义索引的字段中插入重复值或空值唯一索引指索引字段的值必须唯一，但允许有空值全文索引多列索引多列索引指在表中多个字段上创建的索引全文索引指在定义索引的字段上支持值的全文查找一、索引MySQL2.索引的设计原则04在不同值较少的字段上不要建立索引02避免对经常更新的表建立太多索引05为经常需要进行排序、分组和连接查询的字段建立索引03数据量小的表最好不要建立索引01索引并非越多越好一、索引MySQL3.创建索引createtable表名(字段名数据类型[约束条件]，字段名数据类型[约束条件]，……[unique][fulltext]index|key[别名](字段名[长度][asc|desc]));语法格式：一、索引MySQL3.创建索引如果不加可选项参数unique或fulltext则默认表示创建普通索引。unique：表示创建唯一索引，在索引字段中不能有相同的值存在。fulltext：表示创建全文索引。[别名](字段名[长度])：指需要创建索引的字段。asc|desc：表示创建索引时的排序方式。其中asc表示升序排列，desc表示降序排列。默认为升序排列。参数说明：一、索引MySQL4.创建表时创建普通索引createtabletb_student(stuNochar(10)primarykey,namevarchar(50)notnull,sexchar(2)notnullcheck(sexin('男','女')),birthdaydate,specvarchar(30),phonevarchar(11),addressvarchar(255)default'地址不详',indexname(name));在学生成绩管理数据库mystudent中，创建表tb_student（该表的结构与student表一致），同时在表的name字段上建立普通索引：一、索引MySQL4.创建表时建立唯一索引droptabletb_student;createtabletb_student(stuNochar(10)primarykey,namevarchar(50)notnull,sexchar(2)notnullcheck(sexin('男','女')),birthdaydate,specvarchar(30),phonevarchar(11),addressvarchar(255)default'地址不详',uniqueindexuq_name(namedesc));在学生成绩管理数据库mystudent中，创建表tb_student（该表的结构与student表一致），同时在表的name字段上建立唯一索引，并且按姓名降序排序：一、索引MySQL5.删除索引dropindex索引名on表名;语法格式：dropindexuq_nameontb_student;在学生成绩管理数据库mystudent中，将表tb_student中名为“uq_name”的索引删除：一、索引MySQL6.为已经存在的表添加索引create[unique][fulltext]index索引名on表名(字段名[长度][asc|desc]);使用createindex语句创建索引语法格式：createfulltextindexstu_nameontb_student(name);在学生成绩管理数据库mystudent中，在表tb_student的name字段上创建名为stu_name的全文索引：一、索引MySQL6.为已经存在的表添加索引altertable表名add[unique][fulltext]index索引名(字段名[长度][asc|desc]);使用altertable语句创建索引语法格式：altertabletb_studentaddindexstu_stuNo_name(stuNo,name);在学生成绩管理数据库mystudent中，在表tb_student的stuNo和name字段上创建名为stu_stuNo_name的多列索引：MySQL视图二二、视图MySQL1.视图的优点04便于数据共享02屏蔽数据库的复杂性05可以重新组织数据，以便输出到其他应用程序中03简化用户权限的管理01为用户集中数据，简化用户的数据查询和处理二、视图MySQL2.创建视图create[orreplace]view视图名[(字段名列表)]asselect语句[with[cascaded|local]checkoption];语法格式：参数说明：orreplace：当具有同名视图时，将使用新创建的视图覆盖原视图。字段名列表：指定视图查询结果的字段名。select语句：是指用来创建视图的select语句，可在select语句查询多个表或视图。withcheckoption：指出在可更新视图上所进行的修改都要符合select语句所指定的限制条件。二、视图MySQL2.创建视图createviewstudent_viewasselectstuNo,name,sex,phonefromstudent;在学生成绩管理数据库mystudent中，创建一个基于student表的student_view的视图，要求查询并输出所有学生的stuNo（学号）、name（姓名）、sex（性别）、phone（联系电话）字段：二、视图MySQL3.查看视图desc视图名;使用desc语句查看视图语法格式：二、视图MySQL3.查看视图showtablestatuslike'视图名';使用showtablestatus语句查看视图语法格式：参数说明：“like”：表示后面是匹配字符串。“视图名”：表示要查看的视图名称，可以是一个具体的视图名，也可以是包含通配符，代表要查看的多个视图二、视图MySQL3.查看视图showcreateview视图名;使用showcreateview语句查看视图语法格式：二、视图MySQL4.修改视图alterview视图名[(字段名列表)]asselect语句[with[cascaded|local]checkoption];语法格式：参数说明：上述语句中的参数含义和创建视图语句中的参数含义一样二、视图MySQL4.修改视图alterviewstudent_viewasselectstuNo,name,sex,addressfromstudentwheresex='男';在学生成绩管理数据库mystudent中，修改已经创建好的名为student_view视图，查询输出所有男生的stuNo、name、sex、address字段信息：二、视图MySQL5.使用Insert更新视图insertintostudent_viewvalues('190010','李志杰','男','天津市和平区');在学生成绩管理数据库mystudent中，向视图student_view中插入一条记录，stuNo字段值为“190010”，name字段值为“李志杰”，sex字段值为“男”，address字段值为“

天津市和平区”：二、视图MySQL5.使用update更新视图updatestudent_viewsetname='李志豪'wherestuNo='190010';学生成绩管理数据库mystudent中，将视图student_view中学号为“190010”，姓名为“李志杰”的学生的名字修改为“李志豪”：二、视图MySQL5.使用delete删除视图中的数据insertintostudent_viewvalues('190011','张宇','男','山东省济南市');deletefromstudent_viewwherestuNo='190011';在学生成绩管理数据库mystudent中，将在视图student_view中临时插入一条记录，stuNo字段值为“190011”，name字段值为“张宇”，sex字段值为“男”，address字段值为“

山东省济南市”。再使用删除命令删除视图中学号为“190011”的记录：MySQL使用Navicat工具操作索引与视图三三、使用Navicat工具操作索引与视图MySQL在左侧“连接树”工具栏中右击student表，在弹出的菜单中选择“设计表”；图1

选择设计表菜单1.使用Navicat新建索引三、使用Navicat工具操作索引与视图MySQL在工作区中选择“索引”选项卡，分别输入或选择相应的“索引名”、“栏位”、“索引类型”，再点击“保存”按钮图2

通过Navicat创建索引1.使用Navicat新建索引三、使用Navicat工具操作索引与视图MySQL在左侧“连接树”工具栏中选择并展开mystudent数据库，在工作区主界面上点击“新建视图”按钮：图3

通过Navicat创建视图2.使用Navicat新建视图三、使用Navicat工具操作索引与视图MySQL在“视图创建工具”选项卡中将需要创建视图的表拖动至工作区中，在表的结构窗口中选择要创建的视图的字段等信息：图4

通过Navicat创建视图2.使用Navicat新建视图三、使用Navicat工具操作索引与视图MySQL在“SQL预览”选项卡中可以看到视图对应的SQL语句代码，点击“保存”按钮，即可保存新创建的视图：图5

在Navicat工具中保存视图2.使用Navicat新建视图MySQL任务实施四四、任务实施MySQL1.在score表的stuNo字段和couNo字段上添加一个多列索引，索引名为idx_stuNo_couNo，SQL语句如下：altertablescoreaddindexidx_stuNo_couNo(stuNo,couNo);四、任务实施MySQL2.创建一个基于student表、course表、score表的视图，视图名为nopass_view，要求输出学号、姓名、课程名和成绩，SQL语句如下：createviewnopass_viewasselectstu.stuNo,,c.couName,sc