《数据库原理及应用教程-MySQL8.0》课件 第7章 视图和索引

数据库原理与应用教程-MySQL8.0第7章

视图和索引7.1视图数据库的基本表是由数据库设计人员根据所有用户的需求、按照规范化设计方法设计的，并不一定符合用户的应用需求。MySQL可以根据各个用户的应用需求重新定义表的数据结构，这种数据结构就是视图。7.1.1视图概述视图可以视为另一种形式的表，是从一个或多个表中使用SELECT语句导出的虚表，那些用来导出视图的表称为基本表。数据库中只存储视图的定义，而不存储视图对应的数据，这些数据仍存储在导出视图的基本表中，视图实际上是一个查询结果。当基本表中的数据发生变化时，视图中的数据也随之改变。7.1.1视图概述视图的优点如下：(1)为用户集中数据，简化用户的数据查询和处理。(2)保证数据的逻辑独立性。(3)重新定制数据，使得数据便于共享。(4)提高了数据的安全性。7.1.2创建视图必须遵循的原则：(1)只能在当前数据库中创建视图。

(2)视图名称必须遵循标识符的规则，且对每个用户必须唯一。(3)用户可以在其它视图之上建立视图。(4)如果视图中的某一列是一个算术表达式、内置函数或常量派生而来，或者视图中两个或者更多的不同列拥有一个相同的名字，此时，需要为视图的这些列指定特定的名称。7.1.2创建视图创建视图的SQL语句语法：CREATEVIEW[database_name.]view_name

[(column_name[,...n])]AS{select_statement}[WITHCHECKOPTION]7.1.2创建视图【例7-1】创建名为v_male的视图，包括所有男生的基本信息。USEteaching;CREATEVIEWv_maleASSELECTsno,sname,ssex,sbirthday,major,gradeFROMstudentWHEREssex='男';7.1.2创建视图【例7-2】创建名为v_computer的视图，包括计算机科学与技术专业的学生的学号、姓名，和他们选修的课程号、课程名和成绩。USEteaching;CREATEVIEWv_computerASSELECTstudent.sno,sname,o,cname,scoreFROMstudent,sc,courseWHEREstudent.sno=sc.snoANDo=oANDmajor='计算机科学与技术';7.1.2创建视图【例7-3】在bankcard数据库创建名为acc_count的账户统计视图，求每个储户的账户个数，要求包括身份证号和姓名。USEbankcard;CREATEVIEWacc_countASSELECTdepositor.IDNO,Dname,COUNT(*)ASNumberFROMdepositor,accountWHEREdepositor.IDNO=account.IDNOGROUPBYdepositor.IDNO,Dname;7.1.3修改视图修改视图，语法格式如下：ALTERVIEW[database_name.]view_name[(column_name[,...n])]AS{select_statement}[WITHCHECKOPTION]注：语句中的参数与CREATEVIEW语句中的参数相同。7.1.3修改视图【例7-4】修改acc_count视图，求每个储户的账户个数和总存款余额，要求包括身份证号和姓名。USEbankcard;ALTERVIEWacc_countASSELECTdepositor.IDNO,Dname,COUNT(*)ASNumber,SUM(Balance)SumBalanceFROMdepositor,accountWHEREdepositor.IDNO=account.IDNOGROUPBYdepositor.IDNO,Dname;7.1.3修改视图【例7-5】在视图上创建视图：创建Few_Balance余额统计视图，求总存款余额少于5000的储户信息，包括身份证号和姓名和手机号。USEbankcard;CREATEVIEWFew_BalanceASSELECTdepositor.IDNO,depositor.Dname,TelephoneFROMacc_count,depositorWHEREacc_count.IDNO=depositor.IDNOandSumBalance<5000;7.1.4使用视图可以如同查询基本表一样通过视图查询所需要的数据，也可以通过视图更新基本表中的数据。1．使用视图进行数据查询查询各储户的统计信息：SELECT*FROMacc_count;

【例7-6】查询v_computer视图，统计计算机应用基础课程的总分和平均分。SELECTSUM(score)sumscore,AVG(score)avgscoreFROMv_computerWHEREcname='计算机应用基础';

7.1.4使用视图2．使用视图更新基本表中数据更新视图的数据，其实就是对基本表中数据进行更新，因为真正存储数据的地方是基本表，同样使用INSERT、UPDATE、DELETE语句来完成。但并不是所有的视图都可以进行数据更新，只有满足以下可更新条件的视图才能进行数据更新。(1)任何通过视图的数据更新都只能引用一个基本表的列。①如果视图中数据为一个表的行、列子集，则此视图可更新；但如果视图中没有包含表中某个不允许取空值又没有默认值约束的列，则不能插入数据。7.1.4使用视图②如果视图所依赖的基本表有多个时，完全不能向该视图添加（INSERT）数据。③如果视图所依赖的基本表有多个时，那么一次修改只能修改（UPDATE）一个基本表中的数据。④如果视图所依赖的基本表有多个时，那么不能通过视图删除（DELETE）数据。(2)视图中被修改的列必须直接引用表中基础数据，不能是通过任何其他方式派生而来的，比如通过聚合函数、计算（如表达式计算）、集合运算等。(3)被修改的列不应是在创建视图时受GROUPBY、HAVING、DISTINCT子句影响的。7.1.4使用视图根据以上可更新条件分析例7-1~例7-3中各视图是否为可更新视图，即能否通过此视图更新基本表中数据？很明显，例7-1的视图完全可更新例7-2的视图只可以修改数据，不能插入和删除例7-3的视图完全不可更新7.1.4使用视图通常有可能插入并不满足视图查询的WHERE子句条件中的一行。为了限制此操作，可以在创建视图时使用WITHCHECKOPTION选项。【例7-8】通过v_male视图向student表中插入一个男生。INSERTINTOv_maleVALUES('2021010006','张三','男','2003-8-1','电子信息','2021级');7.1.4使用视图通过v_male视图向student表中插入一个女生，也可以完成。如果不希望用户通过v_male视图插入女生，在创建v_male视图时应该使用WITHCHECKOPTION选项。CREATEVIEWv_maleASSELECTsno,sname,ssex,sbirthday,major,gradeFROMstudentWHEREssex='男'

WITHCHECKOPTION;7.1.5删除视图在不需要该视图或想清除视图定义及与之相关联的权限时，可以删除该视图。视图的删除不会影响所依附的基本表的数据。1．在Navicat中选菜单界面删除视图2．使用SQL语句删除视图语法：DROPVIEWview_name【例7-9】删除例7-2创建的v_computer视图。USEteaching;DROPVIEWv_computer;7.2索引索引(Index)是对数据库表中一个或多个列的值进行排序的结构，其主要目的是提高MySQL系统的性能，加快数据的查询速度和减少系统的响应时间。7.2.1索引简介数据库的索引就类似于书籍的目录，如果想快速查找而不是逐页查找指定的内容，可以通过目录中章节的页号快速找到其对应的内容。在表中建立索引，然后在索引中找到符合查询条件的索引值，最后通过保存在索引中的ROWID(行号，相当于页码)快速找到表中对应的记录。7.2.1索引简介索引的优点：（1）大大加快数据的检索速度，这是创建索引的最主要的原因。（2）创建唯一性索引，可以保证表中每一行数据的唯一性。（3）可以加速表和表之间的连接。

（4）在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。

（5）查询优化器可以提高系统的性能，但它是依靠索引起作用的。

7.2.1索引简介缺点：创建索引和维护索引都会消耗时间，当对表中的数据进行增加、删除和修改操作时，索引就要进行维护，否则索引的作用就会下降。另外，每个索引都会占用一定的物理空间，如果占用的物理空间过多，就会影响到整个MySQL系统的性能。7.2.2索引分类1.两大索引方法MySQL主要有HASH和B+树索引两大索引方法。（1）HASHHASH索引可以一次定位，不需要像树形索引那样逐层查找，具有极高的效率。但是，这种高效是有条件的，即只在“=”和“in”条件下高效，对于范围查询、排序及组合索引仍然效率不高。（2）B+树B+树索引就是一个中序遍历有序的树形结构。这是MySQL里默认和最常用的索引类型，实例中只按默认创建B+树索引。7.2.2索引分类2.索引类别MySQL的索引类别包括普通索引、主键索引、唯一索引、全文索引和空间索引。另外，还有一种前缀索引。如果索引是根据单列创建的，这样的索引称为单列索引，根据多列组合创建的索引则称为组合索引。7.2.3创建索引哪些列适合创建索引，哪些列不适合创建索引，需要进行详细的考察。1．创建索引时应考虑的问题（1）对一个表中建大量的索引，应进行权衡。对于SELECT查询，大量索引可以提高性能，可以从中选择最快的查询方法；但是，会影响INSERT、UPDATE和DELETE语句的性能。因为对表中的数据进行修改时，索引也要动态的维护，维护索引耗费的时间会随着数据量的增加而增加，所以应避免对经常更新的表建立过多的索引，而对更新少而且数据量大的表创建多个索引，可以大大提高查询性能。7.2.3创建索引（2）对于小型表（行数较少）进行索引可能不会产生优化效果。（3）对于主键和外键列应考虑建索引，因为经常通过主键查询数据，而外键用于表间的连接。（4）很少在查询中使用的列以及值很少的列（比如“性别”列）不应考虑建索引。（5）如果char或varchar类型的列字符数很多，可视情况选择前n个字符值进行索引，即创建前缀索引。7.2.3创建索引2．在Navicat中通过菜单界面创建索引3．利用SQL语句创建索引（1）CREATETABLE语句创建索引语法格式：CREATETABLE

表名(属性名数据类型[完整性约束条件],

……[UNIQUE|FULLTEXT|SPATIAL]

INDEX|KEY

[索引名]

(属性名1[(长度)]

[ASC|DESC][,…n])

);7.2.3创建索引【例7-10】在teaching1库中创建student1表，包含sno、sname、ssex和idno列，含义分别为学号、姓名、性别、身份证号，并按姓名列创建一个名为index_sname的普通索引。USEteaching1;CREATETABLEstudent1(snochar(10)PRIMARYKEY,snamevarchar(10)NOTNULL,ssexchar(1),idnochar(18),INDEXindex_sname(sname));7.2.3创建索引（2）ALTERTABLE语句创建索引ALTER

TABLE

表名

ADD

[

UNIQUE

|

FULLTEXT

|

SPATIAL

]

INDEX|KEY

[索引名](属性名1

[

(长度)

]

[

ASC

|

DESC][,…n]);

【例7-11】修改teaching库中student表，按姓名列创建一个名为index_sname的普通索引。USEteaching;ALTER

TABLE

studentADDINDEXindex_sname(sname);

7.2.3创建索引（3）CREATEINDEX语句创建索引语法格式如下：CREATE

[

UNIQUE

|

FULLTEXT

|

SPATIAL

]

INDEX|KEY

索引名ON表名(属性名1

[

(长度)

]

[

ASC

|

DESC][,…n]);

【例7-12】在teaching1库中student1表上，按身份证号列创建一个名为index_idno的唯一性索引。USEteaching1;CREATEUNIQUEINDEXindex_idnoONstudent1(idno);

7.2.3创建索引【例7-13】根据teaching库中student表的专业、年级创建一个名为major_grade的组合索引，其中专业称为升序，年级为降序。USEteaching;CREATEINDEXmajor_gradeONstudent(major,gradeDESC);7.2.3创建索引4．间接创建索引在定义表结构或修改表结构时，如果定义了主键约束（PRAMARYKEY）或者唯一性约束（UNIQUE），可以间接创建索引。【例7-15】在teaching1库中创建一个teacher教师表，并定义主键约束和唯一性约束。USEteaching1;CREATETABLEteacher(tnochar(6)PRAMARYKEY,tidchar(18)UNIQUE,tnamevarchar(10));7.2.3创建索引4．间接创建索引在定义表结构或修改表结构时，如果定义了主键约束（PRAMARYKEY）或者唯一性约束（UNIQUE），可以间接创建索引。【例7-15】在teaching1库中创建一个teacher教师表，并定义主键约束和唯一性约束。USEteaching1;CREATETABLEteacher(tnochar(6)PRAMARYKEY,tidchar(1