SQL基础数据教程 3

任务7优化学生选课系统查询操作和性能——创建索引与视图目录Contents学习目标7.1任务描述7.2知识准备7.2.1索引7.2.2视图7.3任务实现7.4任务小结如果使用前面讲的SELECT语句在100万条记录的学生基本信息表student中查询学号为11101001的学生的信息，大概用时30s，响应时间较长，为了快速访问数据表中的特定信息，提高系统的响应速度，可以建立索引加快数据查询效率。另外，使用数据库时，不仅需要提高对数据的查询效率，还需要考虑数据的安全问题，此时在MySQL中可以创建一种叫做视图的虚拟表，让使用的用户只能访问被允许访问的结果集，从而提高数据的安全性。通过创建索引和视图，了解索引和视图的概念，理解索引的分类，掌握索引和视图的查看、删除等操作，掌握视图数据的操作。优化学生选课系统查询操作和性能学习目标掌握连接查询操作，能够根据不同场景使用交叉连接查询、内连接查询和外连接查询进行多表查询；了解索引的概念，能够说出索引的作用；理解索引的分类，能够说出主要有哪几种索引；掌握索引的创建，能够在创建和修改数据表的同时创建索引、在已有的数据表上创建索引；掌握索引的查看，能够通过SHOW语句查看数据表中索引的信息；掌握索引的删除，能够使用ALTERTABLE语句和DROPINDEX语句删除索引；了解视图的概念，能够说出视图的优点；掌握视图的管理，能够创建、查看、修改和删除视图；掌握视图数据操作，能够通过视图添加、修改和删除基本表中的数据。优化学生选课系统查询操作和性能7.1任务描述首先在新建的数据库stu_course中创建一个没有任何索引的学生表stu，该表中包含sno、sname、sgender、sbirth、sclass和sresume六个字段，然后通过SQL语句分别对stu表的字段sname创建普通索引、sno创建唯一索引、sresume创建全文索引、sname和sclass创建多列索引，最后通过索引查看语句SHOWINDEX语句查看索引的创建情况，结果如图7-1所示。优化学生选课系统查询操作和性能图7-1使用SHOWINDEX语句查看stu表的结构7.1任务描述在数据库stu_course中再创建一个教师表teacher_1，该表中包含tno、tname、tsex、tage、tedu和tpro六个字段，接着基于该表创建视图teacher_view并输出所有教师的姓名tname、性别tsex和职称tpro信息，最后使用DESCRIBE语句查看视图teacher_view的基本信息，结果如图7-2所示。优化学生选课系统查询操作和性能图7-2使用DESCRIBE查看视图teacher_view7.2知识准备数据库技术经历了多年的发展，其功能已经不仅仅是存储和管理数据，还可以通过很多易用的特性来提高用户体验和帮助企业节约资源。在关系数据库中，视图和索引主要起到输助查询和组织数据的功能，可以大大提高查询数据的效率。通过对视图的操作，不但可以实现查询的简化，而且还会提高安全性。通过索引，可以快速查询数据表中某一特定的记录，本章将从索引和视图的概念出发，主要介绍索引的创建、查看和删除方法，视图的创建、查看、修改和删除方法，以及通过视图添加、修改和删除基本表中的数据。优化学生选课系统查询操作和性能7.2.1索引在数据库操作中，经常需要查找特定的数据，而索引则用来快速地寻找那些具有特定值的记录。例如，当执行“SELECT*FROMstudentWHEREsno=’11101001’”语句时，如果没有索引，MySQL数据库必须从第一个记录开始扫描表，直至找到sno是“11101001”的记录。表里面的记录数量越多，这个操作的代价就越高。如果在作为搜索条件的列上创建了索引，MySQL在查找特定值的记录时，则无须扫描任何记录即可迅速得到目标记录所在的位置，这可以大大提高查找的效率。优化学生选课系统查询操作和性能7.2.1索引1.索引的概念索引是一个单独的、物理的、存储在磁盘上的数据库表结构，其属性由数据表中的一列或多列字段组合而成。在索引表中，首先按照索引属性进行排序，然后属性值与记录行之间按照一定的顺序建立关系，即用指针指向记录的位置，因此，索引的属性实际包含两个方面，一是从数据表生成的键，即数据表中的一列或多列字段的组合，二是映射到指定记录行的存储位置指针，如图7-3所示。若此时对课程表course的课程编号cno字段创建索引后，索引表的属性由数据表course的cno字段（该字段在索引表中按照大小已排好序）和映射到指定记录行的存储位置指针构成，当查询课程编号为10500132的记录时，首先在索引表中找到10500132，然后通过指针直接找到课程表course中对应的记录（10500132，Web前端开发，64），而不用从第1条记录扫描，大大提高了查询速度。优化学生选课系统查询操作和性能7.2.1索引1.索引的概念优化学生选课系统查询操作和性能图7-3索引示例7.2.1索引1.索引的概念通过图7-3可知，当通过索引查询表中的数据时，只需要查询索引项，不需要遍历所有数据库中的所有数据，就能快速查询表中的特定记录，这样会提高查询效率，可以把索引比作书中的目录，读者可以通过目录快速找到想要查看章节的页码，然后翻到对应的页码查看具体的内容，此外，还可以把索引比作新华字典的音序表，例如，要查“库”字，若不使用音序，即不创建索引，就需要从字典的400页中逐页来找，如果提取拼音出来，构成音序表，即创建索引，就只需要从10多页的音序表中直接查找，大大节省了时间。图7-3的数据较少，建立索引后，查询速度提高不明显，当数据表中的数据非常多时，例如新华字典，创建索引后（音序表）查询速度会显著提高。优化学生选课系统查询操作和性能7.2.1索引1.索引的概念虽然使用索引可以提高数据库的性能，但是索引表需要占用磁盘的存储空间，需要注意避免在一个表上创建大量的索引，因为索引会降低更新表中数据的速度，当更新表中的数据时，系统会自动更新索引页面的数据，可能需要重新组织索引，这些都需要消耗时间。因此，索引设计不合理可能会影响系统的性能，高效、合理的索引对数据库系统获得良好的性能非常重要，设计索引时，需要遵循以下几个原则。（1）只为需要频繁查询的字段创建索引选择经常被查询的字段来建立索引可以提高查询效率。（2）数据量较小的表最好不要创建索引由于数据较少，查询花费的时间可能比遍历索引的时间还要短，因此索引可能不会产生优化效果。优化学生选课系统查询操作和性能7.2.1索引1.索引的概念（3）在不同值少的字段上不要建立索引尽量对不同值较多的字段创建索引，如“姓名”字段，因为大部分人的姓名都不同；不要对不同值很少的字段创建索引，如“性别”字段，因为性别只有“男”与“女”两个不同值，无须建立索引，如果建立索引，则不但不会提高查询效率，而且会严重降低更新速度。（4）一张表中的索引不是越多越好，需要限制索引的数量一个表中有大量的索引，不仅占用磁盘空间，还会影响INSERT、DELETE、UPDATE等语句的性能。因为在更改表中数据的同时，索引也会进行调整和更新。（5）避免对经常更新的表建立过多的索引对于需要频繁进行插入、删除、修改操作的数据表，创建的索引越多，更新表所耗费的时间越长。优化学生选课系统查询操作和性能7.2.1索引2.索引的类型分类标准不同，索引的种类也不同，主要有两种分类标准，分别是按用途分类和按索引列的个数分类。根据索引的用途不同，MySQL中常见的索引大致分为5种，具体介绍如下。（1）普通索引（INDEX）普通索引是MySQL中的最基本的索引类型，由关键字KEY或INDEX定义，但INDEX是最常用的关键字，没有唯一性的限制，允许在定义索引的列中插入重复值和空值，该类型索引可以创建在任何数据类型中。（2）唯一索引（UNIQUE）唯一索引与普通索引的区别仅在于索引列的值必须是唯一的，不能重复，但允许有空值。创建唯一索引的关键字是UNIQUE。优化学生选课系统查询操作和性能7.2.1索引2.索引的类型（3）主键索引（PRIMARYKEY）主键索引是一种特殊的唯一索引，一个表只能有一个主键索引，与唯一索引的不同在于其索引列的值不能为空。在创建表时定义主键后自动创建主键索引，也可通过修改表的方法增加主键。创建主键索引的关键字是PRIMARYKEY，即主键。（4）全文索引（FULLTEXT）全文索引是指在定义索引的列上支持值的全文查找，允许在这些素引列中插入重复值和空值，但只能在CHAR、VARCHAR或TEXT类型的字段上创建全文索引。创建全文索引的关键字是FULLTEXT。（5）空间索引（SPATIAL）空间索引是指只能在空间数据类型的列上建立的索引。MySQL中空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING和POLYGON。需要注意的是，创建空间索引的字段优化学生选课系统查询操作和性能7.2.1索引2.索引的类型必须声明为NOTNULL。创建空间索引的关键字是SPATIAL。根据索引列的个数不同，MySQL中的索引大致分为2种，具体介绍如下。（1）单列索引单列索引是指在表的单列上创建的索引，即单列索引只包含表中的一个列。一个表上可以建立多个单列索引。在表中的单个列上创建索引，单列索引只根据该列索引。单列索引可以是普通索引、唯一索引或全文索引，只要保证该索引只对应一个列即可。（2）多列索引多列索引是指在表的多个列上创建的索引。多列索引也称组合索引、复合索引，该索引指向创建时对应的多个列，可以通过这几个列进行查询。如果唯一索引是组合索引，则多个列的组合必须是唯一的。优化学生选课系统查询操作和性能7.2.1索引3.创建索引（1）使用SQL语句创建索引要想使用索引提高数据表的访问速度，首先必须创建索引，创建索引对MySQL数据库的高效运行来说是很重要的。创建索引是指在某个表的一列或多列上建立一个索引，MySQL提供了3种创建索引的方法，具体介绍如下。优化学生选课系统查询操作和性能7.2.1索引3.创建索引①

创建表的同时创建索引使用CREATETABLE语句在创建数据表的同时创建索引，其语法格式如下。CREATETABLE表名(

字段名1数据类型和长度1[完整性约束条件],…字段名n数据类型和长度n,[UNIQUE|FULLTEXT|SPATIAL]INDEX|KEY[索引名](字段名[(长度)])[ASC|DESC])；优化学生选课系统查询操作和性能7.2.1索引3.创建索引参数说明如下。UNIQUE：表示创建唯一索引，在索引列中不能有相同的值存在。FULLTEXT：表示创建全文索引。SPATIAL：表示创建空间索引。[UNIQUE|FULLTEXT|SPATIAL]：方括号中的内容可以省略，表示创建普通索引；若不省略的话，UNIQUE|FULLTEXT|SPATIAL表示三者选一即可。INDEX|KEY：INDEX和KEY为同义词，表示索引，二者选一即可。别名：表示创建索引的名称。方括号中的内容可以省略，若不加此参数，则默认用创建索引的字段名作为该索引名称。优化学生选课系统查询操作和性能7.2.1索引3.创建索引参数说明如下。长度：表示字段中用于创建索引的长度。方括号中的内容可以省略，不加此参数，则默认用整个字段内容创建索引。ASC]DESC：表示创建索引时的排序方式，其中，ASC为升序排列，DESC为降序排列，默认为ASC。为便于说明，首先创建数据库lib_db，指定其字符集为utf8，排序规则为utf8_general_ci，随后在该数据库中创建对应的表。优化学生选课系统查询操作和性能7.2.1索引3.创建索引【例7.1】创建图书表books的同时创建普通索引。SQL语句如下。CREATETABLEbooks(bookidVARCHAR(20)PRIMARYKEY,titleVARCHAR(100)NOTNULL,authorVARCHAR(50)NOTNULL,publisherVARCHAR(50),quantityINTNOTNULL,INDEXidx_title(title)--普通索引：在title字段创建索引，加速按书名查询图书的速度)ENGINE=InnoDBCOMMENT='图书表';优化学生选课系统查询操作和性能7.2.1索引3.创建索引为便于说明，将例7.1中创建的book表删除。【例7.2】创建图书表books的同时创建唯一索引。SQL语句如下。CREATETABLEbooks(bookidVARCHAR(20)PRIMARYKEY,titleVARCHAR(100)NOTNULL,authorVARCHAR(50)NOTNULL,isbnVARCHAR(20)NOTNULL,--假设添加ISBN字段publisherVARCHAR(50),quantityINTNOTNULL,UNIQUEINDEXidx_unique_isbn(isbn),--唯一索引：确保ISBN号不重复)ENGINE=InnoDBCOMMENT='图书表';优化学生选课系统查询操作和性能7.2.1索引3.创建索引为便于说明，将例7.2中创建的book表删除。【例7.3】创建图书表books的同时创建全文索引。SQL语句如下。CREATETABLEbooks(bookidVARCHAR(20)PRIMARYKEY,titleVARCHAR(100)NOTNULL,authorVARCHAR(50)NOTNULL,descriptionTEXT,--图书简介publisherVARCHAR(50),quantityINTNOTNULL,--全文索引：支持对书名、作者、简介进行全文检索FULLTEXTINDEXidx_fulltext_search(title,author,description))ENGINE=InnoDBCOMMENT='图书表';优化学生选课系统查询操作和性能7.2.1索引3.创建索引【例7.4】创建借阅记录表borrow_records的同时创建多列索引。SQL语句如下。CREATETABLEborrow_records(recordidVARCHAR(20)PRIMARYKEY,bookidVARCHAR(20)NOTNULL,reader_idVARCHAR(20)NOTNULL,borrow_dateDATENOTNULL,return_dateDATE,--多列索引（组合索引）：加速按"书号+读者"的联合查询INDEXidx_composite_book_reader(bookid,reader_id),INDEXidx_borrow_date(borrow_date)--普通索引：加速按借阅日期查询)ENGINE=InnoDBCOMMENT='借阅记录表';优化学生选课系统查询操作和性能7.2.1索引3.创建索引②在已存在的表上创建索引使用CREATEINDEX语句在已经存在的表上创建索引，其语法格式如下。CREATE[UNIQUE|FULLTEXT|SPATIAL]INDEX索引名ON表名(字段名[(长度)])[ASC|DESC])；优化学生选课系统查询操作和性能7.2.1索引3.创建索引为便于说明，将例7.3中创建的book表删除。【例7.5】图书表books表已存在，为加速按作者查询图书，在author字段上创建普通索引。SQL语句如下。图书表books已经用下面语句创建好。CREATETABLEbooks(bookidVARCHAR(20)PRIMARYKEY,titleVARCHAR(100)NOTNULL,authorVARCHAR(50)NOTNULL,publisherVARCHAR(50),quantityINTNOTNULL,INDEXidx_title(title))ENGINE=InnoDBCOMMENT='图书表';优化学生选课系统查询操作和性能7.2.1索引3.创建索引【例7.5】图书表books表已存在，为加速按作者查询图书，在author字段上创建普通索引。SQL语句如下。然后，在author字段上创建普通索引。CREATEINDEXidx_authorONbooks(author);优化学生选课系统查询操作和性能7.2.1索引3.创建索引【例7.6】读者表readers表已存在，为确保每个读者的联系电话不重复，在phone字段上创建唯一索引。SQL语句如下。读者表readers已经用下面语句创建好。CREATETABLEreaders(reader_idVARCHAR(20)PRIMARYKEYCOMMENT'读者编号',reader_nameVARCHAR(50)NOTNULLCOMMENT'读者姓名',phoneVARCHAR(20)COMMENT'联系电话（可选）')COMMENT='读者表';然后，在phone字段上创建唯一索引。CREATEUNIQUEINDEXidx_unique_phoneONreaders(phone);优化学生选课系统查询操作和性能7.2.1索引3.创建索引为便于说明，将例7.4中创建的borrow_records表删除。【例7.7】borrow_records表已存在，为优化“按书号和读者联合查询”的性能，创建组合索引。SQL语句如下。借阅记录表borrow_records已经用下面语句创建好。CREATETABLEborrow_records(recordidVARCHAR(20)PRIMARYKEY,bookidVARCHAR(20)NOTNULL,reader_idVARCHAR(20)NOTNULL,borrow_dateDATENOTNULL,return_dateDATE)ENGINE=InnoDBCOMMENT='借阅记录表';优化学生选课系统查询操作和性能7.2.1索引3.创建索引【例7.7】borrow_records表已存在，为优化“按书号和读者联合查询”的性能，创建组合索引。SQL语句如下。然后，在按书号和读者编号上创建组合索引。CREATEINDEXidx_book_readerONborrow_records(bookid,reader_id);优化学生选课系统查询操作和性能7.2.1索引3.创建索引③修改数据表的同时创建索引使用ALTERTABLE语句对已经存在的表进行修改的同时创建索引，其语法格式如下。ALTERTABLE表名ADD[UNIQUE|FULLTEXT|SPATIAL]INDEX索引名(字段名[(长度)][ASC|DESC])；优化学生选课系统查询操作和性能7.2.1索引3.创建索引【例7.8】为加速按出版社查询图书，在图书表publisher字段上创建普通索引。SQL语句如下。ALTERTABLEbooksADDINDEXidx_publisher(publisher);说明：创建后，执行SELECT*FROMbooksWHEREpublisher='人民文学出版社';将利用该索引提升查询速度。优化学生选课系统查询操作和性能7.2.1索引3.创建索引（2）使用Navicat创建索引【例7.9】使用Navicat为分别图书分类表category的分类名称字段、书号创建唯一索引、普通索引。图书分类表结构如表7-4所示。优化学生选课系统查询操作和性能7.2.1索引3.创建索引（2）使用Navicat创建索引使用Navicat为图书分类表创建索引的步骤如下。步骤1：在Navicat左侧窗格中，打开对应连接“test”和数据库“library”，在library列表中可以看到“表”选项。步骤2：右击“表”选项，在弹出的快捷菜单中选择“新建表”选项，在“字段”选项卡下将图书分类表的各个属性添加进去，如图7-3所示。优化学生选课系统查询操作和性能图7-3添加字段7.2.1索引3.创建索引（2）使用Navicat创建索引步骤3：点击“外键”选项卡，为字段bookid添加外键约束，如图7-4所示。优化学生选课系统查询操作和性能图7-4设置外键7.2.1索引3.创建索引（2）使用Navicat创建索引步骤4：点击“索引”选项卡，为分类名称字段创建唯一索引，字段编辑框中输入“category_name”，索引类型下拉列表框中选择“UNIQUE”，如图7-5所示。优化学生选课系统查询操作和性能图7-5设置索引7.2.1索引3.创建索引（2）使用Navicat创建索引步骤4：点击“保存”按钮，输入表名category，效果如图7-6所示。优化学生选课系统查询操作和性能图7-6输入表名category7.2.1索引4.查看索引（1）使用SQL语句查看索引如果需要查看数据表中已经创建的索引的信息，可以通过SQL语句进行查看，主要采用两种方法，具体介绍如下。①

使用SHOWINDEX语句查看使用SHOWINDEX语句可以查看是否已经创建好索引，其语法格式如下。SHOW{INDEXES|INDEX|KEYS}FROM表名;【例7.10】查看books表上创建的所有索引。SQL语句如下。SHOWINDEXFROMbooks;执行结果如图7-7所示。优化学生选课系统查询操作和性能7.2.1索引4.查看索引（1）使用SQL语句查看索引①

使用SHOWINDEX语句查看优化学生选课系统查询操作和性能图7-7查看books表创建的索引Non_unique=0表示创建了唯一索引，Non_unique=1表示创建了普通索引，从图7-7可以看出，为字段bookid创建了唯一索引，为其他字段创建了普通索引。7.2.1索引4.查看索引【例7.11】查看readers表上创建的所有索引。SQL语句如下。SHOWINDEXFROMreaders;执行结果如图7-8所示。优化学生选课系统查询操作和性能图7-8查看readers表创建的索引从图7-8可以看出，为两个字段都创建了唯一索引。7.2.1索引4.查看索引（1）使用SQL语句查看索引②

使用SHOWCREATETABLE语句查看在数据表的创建语句中使用SHOWCREATETABLE语句可以查看是否已经创建好索引，其语法格式为：SHOWCREATETABLE表名；【例7.12】查看borrow_records表上创建的所有索引。SQL语句如下。SHOWCREATETABLEborrow_records;执行结果如图7-9所示。优化学生选课系统查询操作和性能图7-9查看borrow_records表创建的索引7.2.1索引4.查看索引（2）使用Navicat查看索引在Navicat左侧窗格中，打开对应连接“test”和数据库“library”，右击library列表中的“表”选项，展开数据表列表，右击数据表category，在弹出的快捷菜单中选择“设计表”选项，打开相应数据表设计界面，选择“索引”选项卡，可查看当前数据表category的索引，如图7-10所示。优化学生选课系统查询操作和性能图7-10查看表category的索引7.2.1索引5.删除索引由于索引会占用一定的磁盘空间，因此为避免影响数据库性能，应该及时删除不再使用的索引。（1）使用SQL语句删除索引在MySQL中，删除索引有两种方式，一是使用ALTERTABLE语句，二是使用DROPINDEX语句。下面分别讲解这两种索引删除方式。①

使用ALTERTABLE语句删除索引ALTERTABLE语句具有多种功能，不仅能用于创建索引，还可以用于删除索引。使用ALTERTABLE删除索引的基本语法格式如下。ALTERTABLE表名{DROP{INDEX|KEY}索引名|DROPPRIMARYKEY};优化学生选课系统查询操作和性能7.2.1索引5.删除索引（1）使用SQL语句删除索引①

使用ALTERTABLE语句删除索引参数说明如下。依据上述语法格式，既可以删除普通索引（DROP{INDEX|KEY），又可以删除主键索引（DROPPRIMARYKEY），二选一。删除普通索引时，INDEX或KEY都可以。删除普通索引时，需要指定索引名称；删除主键索引时，不需要指定索引名称，因为每一张表中只能有一个主键索引。在删除索引时，语法格式中的{}不用写。优化学生选课系统查询操作和性能7.2.1索引5.删除索引（1）使用SQL语句删除索引①

使用ALTERTABLE语句删除索引【例7.13】删除books表上按作者查询的普通索引。SQL语句如下。ALTERTABLEbooksDROPINDEXidx_author;【例7.14】删除readers表上确保手机号唯一的索引。SQL语句如下。ALTERTABLEreadersDROPINDEXidx_unique_phone;优化学生选课系统查询操作和性能7.2.1索引5.删除索引（1）使用SQL语句删除索引②

使用DROPINDEX语句删除索引基本语法格式如下:DROPINDEX{索引名|`PRIMARY`}ON表名;参数说明如下。依据上述语法格式，既可以删除普通索引，又可以删除主键索引，二选一。该语句的作用是删除建立在某个数据表上的某个索引，一定要指出索引的名称（或PRIMARY）和数据表的名称。删除普通索引时，需要指定索引名称；删除主键索引时，索引名称固定为PRIMARY，因为PRIMARY是保留字，所以必须将其指定为带引号的标识符。优化学生选课系统查询操作和性能7.2.1索引5.删除索引（1）使用SQL语句删除索引②

使用DROPINDEX语句删除索引【例7.15】删除borrow_records表上的多列索引。SQL语句如下。DROPINDEXidx_book_readerONborrow_records;优化学生选课系统查询操作和性能7.2.1索引5.删除索引（2）使用Navicat删除索引步骤1：在Navicat窗口的左侧窗格中打开“test”连接和“library”数据库，然后展开数据表列表，右击category数据表选项，在弹出的快捷菜单中选择“设计表”选项，打开相应数据表设计界面，选择“索引”选项卡，选择要删除的索引选项，单击“删除索引”按钮，如图7-11所示。优化学生选课系统查询操作和性能7-11打开索引选项卡7.2.1索引5.删除索引（2）使用Navicat删除索引步骤2：打开:“确认删除”对话框，单击“删除”按钮，如图7-12所示，最后单击“保存”按钮。优化学生选课系统查询操作和性能7-12删除索引对话框7.2.2视图在实际开发中，有时候为了保障数据的安全性和提高查询效率，希望创建一个只包含指定字段数据的虚拟表给用户使用，此时可以使用视图。视图是数据库中主要的对象之一，在数据库中的作用类似窗户，用户通过这个窗口只能看到指定的数据。接下来对视图进行详细讲解。优化学生选课系统查询操作和性能7.2.2视图1.视图概述视图是从一张或多张基本表中导出的虚拟表，并不在数据库中实际存在。视图与基本表不同，视图中的内容由SELECT语句定义，数据库中只存储视图的定义，不存储视图对应的数据，这些数据存储在基本表中，直到用户使用视图时才查找出对应的数据。这样不会占用空间，由于是即时引用，视图的内容总是与真实表的内容一致，当基本表中的数据发生变化时，与之关联的视图也会发生变化，视图的数据依赖真实存在的教据表，可以在一个表的基础上创建多个视图。以学生购买教材为例，学校的教材中心将教材放置在对应的专业书架（相当于基本表）中，大一的学生需要购买高等数学、大学物理、马克思主义基本原理、通用英语等必修课的教材，管理员需要从不同的书架中取出教材，工作效率不高。为了快速找齐需要的教材，管理员可优化学生选课系统查询操作和性能7.2.2视图1.视图概述以按照必修课教材书目，将全部的教材提前取出放置在一个独立的书架（相当于视图）中，学生购买教材时，管理员可以直接从该书架取出教材，这样可以提高工作效率。购买教材的学生无须知道教材来自不同的原始书架。通过视图不仅可以看到其依赖数据表中的数据，还可以像操作数据表一样，对数据表中的数据进行添加、修改和删除。与直接操作数据表相比，视图具有以下优点。（1）简化对数据的操作使用视图可以简化对数据的操作。例如，日常开发需要经常使用一个比较复杂的语句进行查询，此时就可以将该查询语句定义为视图，这样在每次执行相同的查询时，不必重写这些复杂的语句，只要一条简单的查询视图语句即可，从而避免了大量重复且复杂的操作，使用户看到的数据的结构简单、清晰。优化学生选课系统查询操作和性能7.2.2视图1.视图概述（2）隐藏了数据的复杂性用户不必了解数据库中详细的表结构和复杂的表间联系，用户可以像使用基本表一样使用视图。（3）自定义数据视图使用户能从多种角度看待同一数据。视图的机制能使不同的用户以不同的方式待同一数据，当许多不同类型的用户使用同一个数据库时，这种灵活性是非常重要的。（4）对数据提供安全保护通过视图，用户只能查看和修改他们能看到的数据，其它数据库或表既不可见，也不可访问。优化学生选课系统查询操作和性能7.2.2视图1.视图概述（5）便于数据共享各个用户不必重复定义和存储自己所需的数据，可以共享数据库中的数据，因此，同样的数据只需存储一次。（6）逻辑数据独立性视图可以帮助用户屏蔽数据表结构变化带来的影响，例如，数据表增加字段不会影响基于该数据表查询出数据的视图。优化学生选课系统查询操作和性能7.2.2视图2.创建视图（1）使用SQL语句创建视图在MySQL中，使用CREATEVIEW语句实现对视图的创建。创建视图的基本语法格式如下。CREATE[ORREPLACE]VIEW视图名view_name[(字段名列表)]ASSELECT语句[WITH[CASCADED|LOCAL]CHECKOPTION];参数说明如下。①ORREPLACE：可选项，表示新创建的视图如果与原视图view_name重名，新创建的视图就会覆盖掉原视图view_name，否则会创建新视图。优化学生选课系统查询操作和性能7.2.2视图2.创建视图（1）使用SQL语句创建视图②view_name：所创建的视图的名称，建议命名规则采用“view_表名_功能”的形式。该名称不能与数据库表或其他视图重名，在数据库中必须是唯一的。③字段名列表：该项为可选项，若有此选项，表示创建的视图中含有指定的数据表中的字段名，否则，创建的视图中的字段名和select语句中的字段名一致。④AS：说明视图要完成的操作。⑤select语句：指定创建视图的查询语句，查询语句参数是一个完整的SELECT语句，表示从某个表中查出某些满足条件的记录，将这些记录导入视图中。select语句可以是任何复杂的查询语句，但不允许包含子查询。优化学生选课系统查询操作和性能7.2.2视图2.创建视图（1）使用SQL语句创建视图⑥WITH[CASCADED|LOCAL]CHECKOPTION：该项为可选项，在更新视图上的记录时，该子句检查新记录是否符合select语句中指定的WHERE子句的条件。如果插入的新记录不符合WHERE子句的条件，则插入操作无法成功。【例7.16】在library数据库中的图书表books的基础上，创建一个显示书号、书名、作者、出版社、库存数量的视图（名称为view_book_basic）。SQL语句如下。优化学生选课系统查询操作和性能7.2.2视图2.创建视图（1）使用SQL语句创建视图⑥WITH[CASCADED|LOCAL]CHECKOPTION：该项可选项，在更新视图上的记录时，该子句检查新记录是否符合select语句中指定的WHERE子句的条件。如果插入的新记录不符合WHERE子句的条件，则插入操作无法成功。【例7.16】在图书表books的基础上，创建一个显示书号、书名、作者、出版社、库存数量的视图（名称为view_book_basic）。SQL语句如下。优化学生选课系统查询操作和性能7.2.2视图2.创建视图（1）使用SQL语句创建视图CREATEVIEWview_book_basicASSELECTbookidAS'书号',titleAS'书名',authorAS'作者',publisherAS'出版社',quantityAS'库存数量'FROMbooks;优化学生选课系统查询操作和性能注意：若在基本表中，所有字段的值均设置了非空，为了后面操作视图方便，创建视图时，也必须包含所有字段，反之，若有些字段的值可以为空，则创建视图时，可以不包含该字段。7.2.2视图2.创建视图（1）使用SQL语句创建视图在图书表中，由于“出版社”的值可以为空，创建视图时，可以不包含该字段。SQL语句如下。CREATEVIEWview_bookASSELECTbookidAS'书号',titleAS'书名',authorAS'作者',quantityAS'库存数量'FROMbooks;由于可以在一个表的基础上创建多个视图，例7.15在图书表books的基础上创建了两个视图view_book_basic和view_book。优化学生选课系统查询操作和性能7.2.2视图2.创建视图（2）使用Navicat创建视图【例7.17】使用Navicat在数据库library中创建读者表视图view_readers，其中包括reader_id（读书编号）、reader_name（读者姓名）信息。步骤1：在Navicat左侧窗格中，打开对应连接“test”和数据库“library”，在library列表中可以看到“视图”选项。在Navicat左侧窗格中，打开对应连接“test”和数据库“library”，步骤2：右击“视图”选项，在弹出的快捷菜单中选择“新建视图”选项，打开“无标题@library(test)-视图”界面，找到“视图创建工具”按钮，如图7-13所示。优化学生选课系统查询操作和性能7.2.2视图2.创建视图（2）使用Navicat创建视图优化学生选课系统查询操作和性能图7-13“无标题@library(test)-视图”界面7.2.2视图2.创建视图（2）使用Navicat创建视图步骤3：首先点击界面中的“视图创建工具”按钮，启动“无标题-视图创建工具”窗口；窗口打开后，左侧窗格默认加载当前数据库，然后在该窗格中双击readers表，将该表添加至窗口中间的操作区域；最后在操作区域内，依次勾选“reader_id”、“reader_name”复选框，完成对应字段的添加，如图7-14所示。优化学生选课系统查询操作和性能图7-14添加数据表和字段7.2.2视图2.创建视图（2）使用Navicat创建视图步骤4：在窗口下方“SELECT”选项卡中，依次为两个字段设置别名：点击每个字段右侧标注为“<别名>”的文字链接，在弹出的编辑输入框中分别输入“读书编号”、“读者姓名”作为两个字段的别名，每次输入完成后按下回车键确认，如图7-15所示。优化学生选课系统查询操作和性能图7-15为字段设置别名7.2.2视图2.创建视图（2）使用Navicat创建视图步骤5：完成字段配置后，点击窗口底部的“构建”按钮返回视图界面，再点击“保存”，在弹出的“另存为”对话框中将视图命名为“view_readers”，确认后即可完成创建，如题7-16、7-17所示。优化学生选课系统查询操作和性能图7-16构建按钮7.2.2视图2.创建视图（2）使用Navicat创建视图优化学生选课系统查询操作和性能图7-17创建视图view_readers7.2.2视图3.查看视图（1）使用SQL语句查看视图查看视图是指查看数据库中已经存在的视图的定义，查看视图必须要有查看的权限，查看视图的方法有3种，它们分别从不同的角度显示了视图的相关信息，具体介绍如下。①使用DESCRIBE语句查看视图在MySQL中，使用DESCRIBE语句可以查看视图的字段信息，包括字段名、字段类型等。DESCRIBE语句的语法格式如下：DESCRIBE视图名;或简写为DESC视图名;优化学生选课系统查询操作和性能7.2.2视图3.查看视图（1）使用SQL语句查看视图①使用DESCRIBE语句查看视图【例7.18】使用DESCRIBE语句查看视图view_book_basic。SQL语句如下。DESCRIBEview_book_basic;或简写为DESCview_book_basic;执行结果如图7-18所示。优化学生选课系统查询操作和性能图7-18查看视图view_book_basic7.2.2视图3.查看视图（1）使用SQL语句查看视图②使用SHOWTABLESTATUS语句查看视图在MySQL中，使用SHOWTABLESTATUS语句可以查看视图的定义信息，语法格式如下：SHOWTABLESTATUSLIKE’视图名’;参数说明如下。“LIKE”表示后面是匹配字符串。“视图名”是要查看的视图名称，可以是一个具体的视图名，也可以包含通配符，代表要查看的多个视图。视图名称要用单引号标注。优化学生选课系统查询操作和性能7.2.2视图3.查看视图（1）使用SQL语句查看视图②使用SHOWTABLESTATUS语句查看视图【例7.19】使用SHOWTABLESTATUS语句查看视图view_book_basic。SQL语句如下。SHOWTABLESTATUSLIKE‘view_book_basic’;执行结果如图7-19所示。优化学生选课系统查询操作和性能图7-19查看视图view_book_basic7.2.2视图3.查看视图（1）使用SQL语句查看视图③使用SHOWCREATEVIEW语句查看视图在MySQL中，使用SHOWCREATEVIEW语句不仅可以查看视图的定义语句，还可以查看视图的字符编码，具体语法格式介绍如下：SHOWCREATEVIEW视图名;【例7.20】使用SHOWCREATEVIEW语句查看视图view_book_basic。SQL语句如下。SHOWCREATEVIEWview_book_basic;执行结果如图7-20所示。优化学生选课系统查询操作和性能图7-20查看视图view_book_basic7.2.2视图3.查看视图（2）使用Navicat查看视图在Navicat左侧窗格中，打开对应连接“test”和数据库“library”，在library列表中可以看到“视图”选项。展开“视图”选项按钮，可以看到创建的视图。优化学生选课系统查询操作和性能图7-21查看视图7.2.2视图4.操作视图视图创建好后，可以对视图进行更新、修改和删除操作，首先，可以通过视图来插入、删除和修改基本表中的数据，从而实现视图的更新操作，然后，可以通过命令来修改视图的定义，最后，当不需要视图时，可以使用命令将视图删除。由于要通过视图来插入、删除和修改基本表中的数据，因此，为了操作视图方便，我们向基本表中录入了一些数据。优化学生选课系统查询操作和性能7.2.2视图4.操作视图通过下面的SQL语句向books表插入数据。INSERTINTObooks(bookid,title,author,publisher,quantity)VALUES('B001','西游记','吴承恩','人民文学出版社',50),('B002','红楼梦','曹雪芹','人民邮电出版社',30),('B003','三国演义','罗贯中','中华书局',25),('B004','水浒传','施耐庵','三联书店',40);优化学生选课系统查询操作和性能7.2.2视图4.操作视图（1）更新视图由于视图是不存储数据的虚拟表，所以对视图的更新就是对基本表的更新，更新视图可以实现对基本表插入、删除和修改数据等操作。①使用INSERT向视图中插入数据向视图中插入数据与向基本表中插入数据一样，都可以通过INSERT语句来实现。插入数据的操作是针对视图中的字段的插入操作，而不是针对基本表中所有的字段的插入操作，对于由多个基表连接查询而生成的视图来说，一次插入操作只能作用于一个基表，其语法格式如下：INSERT[INTO]视图名[(字段名列表)]VALUES(值列表);注意：如果视图中没有包括基本表中所有属性为NOTNULL的字段，那么插入操作会由于那些字段的NULL值而失败。优化学生选课系统查询操作和性能7.2.2视图4.操作视图【例7.21】使用INSERT语句向视图view_book_basic中插入一条数据：书号：'B005'，书名：'活着'，作者：'余华'，出版社：'人民文学出版社'，库存数量：20。SQL语句如下。INSERTINTOview_book_basic(`书号`,`书名`,`作者`,`出版社`,`库存数量`)VALUES('B005','活着','余华','人民文学出版社',20);成功执行上述语句后，使用SELECT语句查看视图view_book_basic（SELECT*FROMview_book_basic;）和表books（SELECT*FROMview_books;）中的所有数据，验证插入是否成功，执行结果分别如图7-22、7-23所示。优化学生选课系统查询操作和性能7.2.2视图4.操作视图优化学生选课系统查询操作和性能图7-22查看视图view_book_basic中插入后的数据图7-23查看表books中插入后的数据通过以上结果可以看到，该数据已成功插入到视图中。7.2.2视图4.操作视图【例7.22】使用INSERT语句向视图view_book中插入一条数据：书号：'B006'，书名：'朝花夕拾'，作者：'鲁迅'，库存数量：15。SQL语句如下。INSERTINTOview_book(`书号`,`书名`,`作者`,`库存数量`)VALUES('B006','朝花夕拾','鲁迅',15);成功执行上述语句后，使用SELECT语句查看视图view_book和表books中的所有数据，验证插入是否成功，执行结果分别如图7-24、7-25所示。优化学生选课系统查询操作和性能7.2.2视图4.操作视图优化学生选课系统查询操作和性能图7-24查看视图view_book中插入后的数据图7-25查看表books中插入后的数据7.2.2视图4.操作视图通过以上结果可以看到，该数据已成功插入到视图view_book和表books中。同学们想一下，为什么视图view_book_basic中也有该条数据？优化学生选课系统查询操作和性能图7-26查看视图view_book_basic中插入后的数据7.2.2视图4.操作视图②

使用UPDATE修改（更新）视图中的数据在视图中更新数据与在基本表中更新数据一样，都可使用UPDATE语句，但是当视图的数据来自多个基表中时，与插入操作一样，每次更新操作只能更新一个基表中的数据；如果通过视图修改存在于多个基表中的数据，则对不同的基表要分别使用UPDATE语句，其语法格式如下：UPDATE视图名SET字段名1=value[,…][where条件表达式];优化学生选课系统查询操作和性能7.2.2视图4.操作视图②

使用UPDATE修改（更新）视图中的数据【例7.23】使用UPDATE语句通过视图view_book将《西游记》的库存数量修改为80本。SQL语句如下。UPDATEview_bookSETquantity=80WHERE书名='西游记';成功执行上述语句后，使用SELECT语句查看视图view_book和表books中的所有数据，验证修改是否成功，执行结果分别如图7-27、7-28所示。优化学生选课系统查询操作和性能7.2.2视图4.操作视图②

使用UPDATE修改（更新）视图中的数据优化学生选课系统查询操作和性能图7-27查看视图view_book中修改后的数据图7-28查看表books中修改后的数据7.2.2视图4.操作视图③使用DELETE删除视图中的数据通过视图删除数据与在基本表中删除数据一样，都可使用DELETE语句。在视图中删除的数据，同时也会从基本表中被删除。当一个视图使用两个或两个以上的基表进行连接查询时，对视图中数据的删除操作则是不允许的。使用DELETE语句删除视图中的数据的语法格式如下。DELETE[FROM]视图名[where条件表达式];优化学生选课系统查询操作和性能7.2.2视图4.操作视图（2）修改视图在MySQL中，使用ALTERVIEW语句修改视图，即修改数据库中已经存在的视图的定义，其语法格式如下：ALTERVIEW视图名[(名称列表)]ASSELECT语句[WITH[CASCADED|LOCAL]CHECKOPTION];上述语法格式中的参数含义和CREATEVIEW语句中的参数含义一样。修改视图的常见操作主要包含两类，一类是调整视图所展示的字段，包括对字段的添加、删除等，另一类是变更视图的数据筛选条件。接下来结合具体实例，帮助大家能够更深入地理解。优化学生选课系统查询操作和性能7.2.2视图4.操作视图（2）修改视图【例7.24】在视图view_book中增加“出版社”字段。SQL语句如下。ALTERVIEWview_bookASSELECT`bookid`,--对照视图中的“书号”`title`,--对照视图中的“书名”`author`,--对照视图中的“作者”`quantity`,--对照视图中的“库存数量”`publisher`--添加的字段FROMbooks;优化学生选课系统查询操作和性能7.2.2视图4.操作视图注意，SELECT关键词后面的用的是基本表中的字段，而不是视图中的字段。成功执行上述语句后，使用SELECT语句查看视图view_book中的所有数据，验证添加字段是否成功，执行结果分别如图7-29所示，可参考图7-27与图7-28两个表的字段名。优化学生选课系统查询操作和性能图7-29添加publisher字段后view_book中的数据7.2.2视图4.操作视图【例7.25】修改视图view_book，只显示库存量大于20本的图书信息。SQL语句如下。ALTERVIEWview_bookASSELECT`bookid`,--对照视图中的“书号”`title`,--对照视图中的“书名”`author`,--对照视图中的“作者”`quantity`,--对照视图中的“库存数量”`publisher`--添加的字段FROMbooks;WHERE`quantity`>20;优化学生选课系统查询操作和性能7.2.2视图4.操作视图成功执行上述语句后，使用SELECT语句查看视图view_book中的所有数据，验证筛选数据是否成功，执行结果分别如图7-30所示。优化学生选课系统查询操作和性能图7-30显示库存量大于20本的图书信息7.2.2视图4.操作视图（3）删除视图当视图不再需要时，使用SQL语句和Navicat可以将视图删除。①使用SQL语句删除视图在MySQL中，使用DROPVIEW语句对视图进行删除，只是将视图的定义删除，并不会影响基表中的数据。删除视图的语法格式为：DROPVIEW[IFEXISTS]视图名1[,视图名2...];在上述语法格式中，视图名可以有多个，可同时删除一个或多个视图，视图名之间用逗号分隔。删除视图必须有DROPVIEW权限，IFEXISTS为可选项，表示删除视图时如果存在指定视图，则将视图删除，如果指定的视图不存在，删除操作不会出现错误。优化学生选课系统查询操作和性能7.2.2视图4.操作视图（3）删除视图【例7.26】删除视图view_book。SQL语句如下。DROPVIEWview_book;成功执行上述语句后，使用SELECT语句查看视图view_book中的所有数据，则会显示视图不存在，执行结果分别如图7-31所示。优化学生选课系统查询操作和性能图7-31删除视图view_book7.2.2视图4.操作视图②使用Navicat删除视图【例7.27】删除视图view_book_basic。在Navicat窗口的左侧窗格中打开相应连接“test”和数据库“library”，展开视图列表，右击要删除的视图view_book_basic，在弹出的快捷菜单中选择“删除视图”选项，打开“确认删除”对话框，勾选“我了解此操作是永久性的且无法撤销”复选框，单击“删除”按钮，如图7-32所示。优化学生选课系统查询操作和性能图7-32删除视图view_book_basic7.3任务实现在本章中，主要完成的任务有两个，分别是索引和视图的操作，下面分别以stu_course数据库和“学生管理系统”数据库scoredb举例进行详细介绍。7.3.1创建索引1.创建表的同时创建索引【例7.28】首先创建数据库stu_course，指定其字符集为utf8、排序规则为utf8_general_ci，然后在该数据库中创建teacher_1表，表结构包含以下字段：tno（CHAR(4)）、tname（VARCHAR(10)）、tsex（CHAR(1)）、tage（INT）、tedu（VARCHAR(10)）、tpro（VARCHAR(8)）。要求在创建表的同时，为tname列建立普通索引，并设置该索引按升序排序。SQL语句如下。优化学生选课系统查询操作和性能7.3任务实现7.3.1创建索引CREATETABLEteacher_1(tnoCHAR(4)NOTNULLPRIMARYKEY,tnameVARCHAR(10)NOTNULL,tsexCHAR(1),tageINT,teduVARCHAR(10),tproVARCHAR(8),INDEX(tnameASC));优化学生选课系统查询操作和性能7.3任务实现7.3.1创建索引【例7.29】在数据库stu_course中创建course_1表，表结构包含以下字段：cno(CHAR(4))、cname(VARCHAR(40))、cperiod(INT)、credit(DECIMAL(3,1))、brief(VARCHAR(255))。要求在创建表的同时为cname列建立唯一索引UK_cname，并设置该索引按降序排序。SQL语句如下。优化学生选课系统查询操作和性能CREATETABLEcourse_1(cnoCHAR(4)NOTNULLPRIMARYKEY,cnameVARCHAR(40),cperiodINT,creditDECIMAL(3,1),briefVARCHAR(255),UNIQUEINDEXUK_cname(cnameDESC));7.3任务实现7.3.1创建索引【例7.30】在数据库stu_course中创建course_2表，表结构包含以下字段：cno(CHAR(4))、cname(VARCHAR(40))、cperiod(INT)、credit(DECIMAL(3,1))、brief(VARCHAR(255))，要求在创建表的同时为brief列建立全文索引FT_brief，并设置该索引按升序排序。SQL语句如下。优化学生选课系统查询操作和性能CREATETABLEcourse_2(cnoCHAR(4)NOTNULLPRIMARYKEY,cnameVARCHAR(40),cperiodINT,creditDECIMAL(3,1),briefVARCHAR(255),FULLTEXTINDEXFT_brief(brief));7.3任务实现7.3.1创建索引【例7.31】在数据库stu_course中创建teacher_2表，表结构包含以下字段：tno(INT(4))、tname(VARCHAR(10))、tsex(CHAR(1))、tedu(VARCHAR(10))。要求在创建表的同时为tname列和tage列建立多列索引name_age_index，并且tname列按升序排序，tage列按降序排序。SQL语句如下。优化学生选课系统查询操作和性能CREATETABLEteacher_2(tnoCHAR(4)NOTNULLPRIMARYKEY,tnameVARCHAR(10)NOTNULL,tsexCHAR(1),tageINT,teduVARCHAR(10),INDEXname_age_index(tnameASC,tageDESC));7.3任务实现7.3.1创建索引【例7.32】在数据库stu_course中创建e_0表，表结构包含的字段为space(GEOMETRY)。创建表的同时为space列建立空间索引sp，并按升序排序。SQL语句如下。CREATETABLEe_0(spaceGEOMETRYNOTNULL,SPATIALINDEXsp(space))ENGINE=MyISAM;优化学生选课系统查询操作和性能7.3任务实现7.3.1创建索引2.在已存在的表上创建索引为了便于学习CREATEINDEX语句，在数据库stu_course中首先创建一个没有任何索引的学生表stu，该表中包含sno、sname、sgender、sbirth、sclass、sresume字段。创建stu表的SQL语句如下。优化学生选课系统查询操作和性能CREATETABLEstu(snoCHAR(8)NOTNULL,snameVARCHAR(10)NOTNULL,sgenderCHAR(1),sbirthDATE,sclassVARCHAR(20),sresumeVARCHAR(255));7.3任务实现7.3.1创建索引2.在已存在的表上创建索引【例7.33】对stu表的sname字段创建名为stu_name的普通索引，并按升序排序。SOL语句如下。CREATEINDEXstu_nameONstu(snameASC);【例7.34】对stu表的sno字段创建名为stu_sno的唯一索引，并按升序排序。SQL语句如下。CREATEUNIQUEINDEXstu_snoONstu(sno);优化学生选课系统查询操作和性能7.3任务实现7.3.1创建索引2.在已存在的表上创建索引【例7.35】对stu表的sresume字段创建名为stu_sresume的全文索引，并按降序排序。SQL语句如下。CREATEFULLTEXTINDEXstu_sresumeONstu(sresumeDESC);【例7.36】对stu表的sname和sclass字段创建名为stu_sname_sclass的多列索引。SOL语句如下。CREATEINDEXstu_sname_sclassONstu(sname,sclass);优化学生选课系统查询操作和性能7.3任务实现7.3.1创建索引2.在已存在的表上创建索引【例7.37】在数据库stu_course中创建e_1表，表结构包含的字段为space(GEOMETRY)。在e_1表的space字段上建立空间索引sp_space，并按升序排序。①

创建e_1表。SQL语句如下。CREATETABLEe_1(spaceGEOMETRYNOTNULL)ENGINE=MyISAM;优化学生选课系统查询操作和性能7.3任务实现7.3.1创建索引2.在已存在的表上创建索引②