数据基础及应用- 8

第2章MySQL数据库系统基础第1章数据库系统概述第3章MySQL数据库操作第5章使用SQL语句查询数据第4章MySQL数据表第6章视图和索引的使用第8章数据库的安全性管理第7章存储过程和触发器第9章数据库的备份与恢复目录目标导航重点和难点1．索引的概念和作用；2．索引的分类；3．索引的创建与管理；4．视图的概念和作用；5．视图的创建与管理。知识目标1.理解索引的概念和作用；2.掌握索引的创建和管理；3.理解视图的概念和作用；4.掌握视图的创建和管理。技能目标1.具备索引的创建和管理的能力；2.具备视图的创建和管理的能力。视图与索引是两个完全不同的数据库对象,但它们都与表密切相关。通过对数据的预定义(视图)和预排序(索引),可以显著地提高表的工作性能。本章主要介绍视图的概念、作用及操作;索引的分类、作用及索引的创建。第6章视图和索引的使用6.1.1视图概述视图是从一个或几个基本表或视图中导出的虚拟表,是从现有基本表中抽取若干子集组成用户的“专用表”,这种构造方式使用SQL语言中的SELECT语句来实现。视图与基本表不同,视图不对数据进行实际存储,数据库中只存储视图的定义。用户对视图的数据进行数据库系统及应用操作时,系统会根据视图的定义操作相关联的基本表。对视图的操作与对基本表的操作相似,可以查询数据、录入数据、更新数据和删除数据。当对视图进行录入、更新和修改后,基本表的数据也会随之发生变化;同样,基本表的数据发生变化,与之相关联的视图也随之变化。第6章视图和索引的使用6.1视图的使用6.1.1视图概述1.使用视图的优点第6章视图和索引的使用6.1视图的使用（1）简化用户的操作，使操作变得更简单（2）简化用户权限的管理（3）提高数据的逻辑独立性6.1.1视图概述2.创建视图的原则第6章视图和索引的使用6.1视图的使用（1）只能在当前数据库中创建视图（2）视图名称须遵循标识符规则（3）可基于其它视图创建视图（4）不能基于临时表创建视图6.1.2创建视图（5）WITHCHECKOPTION：可选项，在视图基础上使用的插入、修改、删除语句，必须满足创建视图时SELECT查询中的WHERE子句的条件，这样可以确保数据修改后，仍可通过视图看到修改的数据。WITHCHECKOPTION给出CASCADED和LOCAL两个可选参数，它们决定了检查测试的范围。LOCAL表示只对定义的视图进行检查，CASCADED表示对所有视图进行检查，默认为CASCADED。（6）视图名后的列可以省略，若省略了视图名后的列，则视图结果集中的列为表中的列；若指定视图中的列，则视图中列的数目必须与SELECT查询中列的数目相等。第6章视图和索引的使用6.1视图的使用6.1.2创建视图例1使用NavicatforMySQL平台创建“20160101”班学生基本信息视图,视图名为“v_student20160101”。(1)启动NavicatforMySQL平台,右键单击连接名“MySQL90”,在弹出的快捷菜单中选择“打开连接”命令,右键单击“gradem”数据库,在弹出的快捷菜单中选择“打开数据库”命令,展开数据库。(2)单击视图,在右侧区域选择“新建视图”,打开新建视图窗口,在新建视图窗口中单击“视图创建工具”,打开“视图创建工具”窗口。(3)双击左侧的“student”表,将“student”表添加进去(或者在语句窗口中单击FROM后面的“在这里添加表”,在弹出的下拉列表中选择表“student”)。(4)在“字段关系”中选择“*”,也可逐一勾选各字段前的复选框或在下方语句处单击SELECT后的“+”来添加字段。第6章视图和索引的使用6.1视图的使用6.1.2创建视图视图在数据库中是作为一个对象来存储的。用户在创建视图前要保证自己具有使用CREATEVIEW语句的权限，并且有权操作视图所涉及的表或其他视图，其语法格式如下：CREATE[ORREPLACE]VIEW<视图名>[(<列名>[,<列名>]…)]AS<SELECT查询>[WITH[CASCADED|LOCAL]CHECKOPTION]参数说明：（1）VIEW：创建视图的关键字。（2）ORREPLACE：可选项，表示可以替换已有的同名视图。（3）AS：引导创建视图的SQL查询语句。（4）SELECT查询：用来创建视图的SELECT语句。第6章视图和索引的使用6.1视图的使用6.1.2创建视图(5)选择WHERE后的选项卡,单击下面的“+”添加条件。(6)单击图6-4下面语句窗口中的第一个<值>,选择字段“classno”;单击第二个<值>,打开输入值的窗口。(7)在输入值窗口输入“20160101”,设置完成后的视图编辑窗口。(8)单击“保存”按钮,弹出“另存为”对话框。(9)输入视图的名字“v_student20160101”,单击“保存”按钮保存视图。创建完成之后展开数据库“gradem”下的视图,就会看见创建的视图。第6章视图和索引的使用6.1视图的使用6.1.3修改视图修改视图通常是指修改视图的定义。MySQL通过CREATEORREPLACEVIEW语句和ALTERVIEW语句修改视图。(1)使用CREATEORREPLACEVIEW修改视图语法格式如下:CREATEORREPLACEVIEW视图名[字段名,…]ASSELECT语句[WITH[CASCADED|LOCAL]CHECKOPTION](2)使用ALTERVIEW语句修改视图语法格式如下:ALTERVIEW<视图名>[(<列名>[,<列名>]…)]AS<SELECT查询>[WITHCHECK[CASCADED|LOCAL]OPTION]项目6视图和索引的使用6.1视图的使用6.1.2创建视图例2使用CREATEVIEW语句创建“20160102”班女生的基本信息视图,视图名为“v_student20160102”。CREATEVIEWv_student20160102ASSELECT*FROMstudentWHEREclassno='20160102'ANDssex='女'WITHLOCALCHECKOPTION;第6章视图和索引的使用6.1视图的使用6.1.3修改视图

例3使用NavicatforMySQL平台修改视图“v_student20160101”,使视图中包含学号和姓名字段。(1)在视图列表下右键单击“v_student20160101”,在弹出的快捷菜单中选择“设计视图”命令,打开“视图设计”窗口。该窗口与创建视图时的界面一致,在此窗口中单击“视图创建工具”以打开对应的创建工具窗口,在此窗口可以与创建时进行同样操作。(2)修改完成后在“视图创建工具”窗口单击“确定”按钮。回到“视图设计”窗口,单击“保存”按钮,即可完成视图的修改。例4使用CREATEORREPLACEVIEW修改视图“v_student20160101”,使视图中只包含学号和姓名字段。CREATEORREPLACEVIEWv_student20160101ASSELECTsno,snameFROMstudentWHEREclassno='20160101';第6章视图和索引的使用6.1视图的使用6.1.3修改视图例5使用ALTERVIEW语句修改视图“v_student20160102”,使视图中只包含学号和姓名字段。ALTER

VIEW

v_student20160102ASSELECTsno,snameFROMstudentWHEREclassno='20160102';第6章视图和索引的使用6.1视图的使用6.1.4使用视图1.查看视图的定义查看视图是指查看数据库中已存在的视图的基本信息。进行此操作的用户必须拥有SHOWVIEW权限。(1)使用DESCRIBE语句查看视图的基本信息DESCRIBE视图名(2)使用SHOWTABLESTATUS语句查看视图的基本信息SHOWTABLESTATUSLIKE视图名(3)使用SHOWCREATEVIEW语句查看视图的基本信息SHOWCREATEVIEW'视图名'(4)在VIEWS表中查看视图的详细信息在MySQL中,information_schema数据库下的VIEWS表存储了所有视图的定义。通过对VIEWS表进行查询,可以查看数据库内所有视图的详细信息。语句格式如下:SELECT*FROMinformation_schema.VIEWS;第6章视图和索引的使用6.1视图的使用6.1.4使用视图1.查看视图的定义例6使用DESCRIBE语句查看视图“v_student20160101”中字段的信息。DESCRIBEv_student20160101;例7使用SHOWTABLESTATUS语句查看视图“v_student20160101”的基本信息。SHOWTABLESTATUSLIKE'v_student20160101';例8使用SHOWCREATEVIEW语句查看视图“v_student20160101”的基本信息。SHOWCREATEVIEWv_student20160101;例9在VIEWS表中查看所有视图的详细信息。SELECT*FROMinformation_schema.VIEWS;第6章视图和索引的使用6.1视图的使用6.1.4使用视图2.查询视图中的数据视图创建后,可以像查询基本表一样对其进行查询操作。SELECT

列名列表

FROM

视图名例10使用SQL语句查询视图“v_student20160101”中的数据。SELECT*FROMv_student20160101;第6章视图和索引的使用6.1视图的使用6.1.5更新视图由于视图是一个虚拟表,因此更新视图数据实质上就是更新基本表中的数据。需要注意的是,并非所有的视图都允许数据更新,操作时需谨慎,否则可能导致不可预料的后果。(1)不可更新视图如果视图中包含以下任何一种结构,则视图不可更新。①使用了聚合函数。

②含有DISTINCT关键字。③含有GROUPBY、ORDERBY或HAVING子句。④含有UNION运算符。

⑤选择列表中包含子查询。⑥FROM语句中含有多个表。⑦SELECT语句中引用了不可更新的视图。⑧WHERE子句中的子查询引用了FROM子句中的表。⑨ALGORITHM选项指定为TEMPTABLE。第6章视图和索引的使用6.1视图的使用6.1.5更新视图(2)更新视图中的数据①插入数据INSERT视图名(列名)VALUES(值列表)②更新数据UPDATE视图名SET字段名=值或表达式③删除数据DELETEFROM视图名WHERE条件第6章视图和索引的使用6.1视图的使用6.1.5更新视图(2)更新视图中的数据例12使用NavicatforMySQL修改视图v_student20160101中学号为“2019010101”的学生的姓名为“刘明”。(1)选中视图“v_student20160101”,右键单击,在弹出的快捷菜单中选择“打开视图”命令,打开视图的数据编辑窗口。(2)选中要修改的记录,在其sname列中将姓名修改为“刘明”。(3)单击下面的“√”按钮,完成数据的修改。第6章优化学生信息管理数据库6.1视图的使用6.1.5更新视图(2)更新视图中的数据例11使用NavicatforMySQL向视图v_student20160101中添加数据“刘敏2019010101”。(1)选中视图“v_student20160101”,右键单击,在弹出的快捷菜单中选择“打开视图”命令。(2)在打开的视图数据窗口,如图6-13所示,单击下面的“+”按钮,添加一行空白的数据行,输入数据“2019010101刘敏”,然后单击下面的“√”按钮,完成数据的修改。第6章视图和索引的使用6.1视图的使用6.1.5更新视图(2)更新视图中的数据例15使用UPDATE语句,通过视图v_student20160101修改学号为'2016010101'的学生姓名为'白沧溟'。UPDATEv_student20160101SETsname='白沧溟'WHEREsno='2016010101';例16使用DELETE语句通过视图v_student20160101删除学号'2016010101'的学生信息。DELETEFROMv_student20160101WHEREsno='2016010101';第6章视图和索引的使用6.1视图的使用6.1.6删除视图当视图不再需要时,可以将其删除,删除视图可以使用DROPVIEW语句。DROPVIEW[IFEXISTS]视图名[,视图名…][RESTRICT|CASCADE]例17使用NavicatforMySQL删除视图“v_student20160101”。(1)选中视图“v_student20160101”,右键单击,在弹出的快捷菜单中选择“删除视图”命令(也可以选中视图,单击“删除视图”按钮),打开“确认删除”对话框。(2)勾选“我了解此操作是永久性的且无法撤消”,单击“删除”按钮,删除视图。例18使用DROPVIEW语句删除视图“v_student20160102”。DROPVIEWv_student20160102;第6章视图和索引的使用6.1视图的使用6.1.5更新视图(2)更新视图中的数据例13使用NavicatforMySQL删除视图v_student20160101中学号为“2019010101”的学生信息。(1)选中视图“v_student20160101”,右键单击,在弹出的快捷菜单中选择“打开视图”命令,打开视图的数据编辑窗口。(2)选中要删除的记录,单击“-”按钮,在弹出的“确认删除”对话框中单击“删除一条记录”按钮,完成记录的删除。例14使用INSERT语句通过视图v_student20160101向基本表中插入数据'2016030102','赵明'。INSERTv_student20160101VALUES('2016030102','赵明');第6章视图和索引的使用6.1视图的使用6.2.1索引概述数据库中的索引类似于书的目录,表中的数据则类似于书的内容。学生可以通过目录快速查找到特定内容。同样,索引是一种能够加快检索速度的数据库结构。在数据库中,使用索引可以快速检索到表中的数据,而无须扫描整个表。索引是根据表中一列或若干列的值,按照一定顺序建立的列值与记录行之间的对应关系表。在列上创建索引后,查询数据时即可直接通过该列的索引定位到对应的数据行,从而快速获取所需数据。例如在学生表student中创建了学号sno列上的索引，MySQL将在索引中排序学号列，对于索引中的每一项，MySQL在内部为它保存一个数据文件中实际记录所在位置的指针。因此，如果要查找学号“2016010101”的学生信息，MySQL将在索引中找到“2016010101”的值，然后直接转到数据文件中相应的行，准确地返回该行的数据。第6章视图和索引的使用6.2索引的使用6.2.1索引概述1.索引的分类根据索引列的内容，MySQL的索引分为以下4类：第6章视图和索引的使用6.2索引的使用（1）普通索引（2）唯一索引和主键索引（3）全文索引（4）空间索引6.2.1索引概述1.索引的分类根据索引列的数目，MySQL的索引又可分为单列索引和复合索引。第6章视图和索引的使用6.2索引的使用单列索引索引中只包含一个列，一个表可以包含多个单列索引。复合索引索引创建在多个列的组合上6.2.1索引概述3.索引的设计原则第6章视图和索引的使用6.2索引的使用（1）经常频繁访问的列应创建索引（2）避免对经常更新的表创建过多的索引（3）数据量不大的表应尽量避免创建索引。（4）应在频繁进行排序或分组的列上建立索引6.2.2创建索引1.在使用CREATETABLE语句创建表时创建索引使用CREATETABLE语句创建表时，除了可以定义列的数据类型，还可以定义主键约束、外键约束或者唯一约束，而不论创建哪种约束，在定义约束的同时相当于在指定的列上创建了一个指定约束。语法格式如下：CREATETABLE表名（字段名数据类型[完整性约束条件],…..PRIMARYKEY(字段名,...),/*主键索引*/INDEX|KEY[索引名](字段名,…[ASC|DESC])/*普通索引*/|[UNIQUE|FULLTEXT|SPATIAL][INDEX|KEY][索引名](字段名,...[ASC|DESC])/*唯一性索引/全文索引/空间索引*/）参数说明：（1）UNIQUE、FULLTEXT、SPATIAL：可选参数，分别表示唯一索引、全文索引、空间索引。（2）INDEX|KEY：INDEX和KEY是同义词，作用相同，用来指定创建索引。（3）ASC|DESC：表示索引字段的排序规则。第6章视图和索引的使用6.2索引的使用6.2.2创建索引2.用CREATEINDEX语句创建索引使用CREATEINDEX语句在已存在的表上添加索引。一个表可以创建多个索引。语法格式如下：CREATE[UNIQUE|FULLTEXT|SPATIAL]INDEX索引名[索引类型]ON表名(索引列名…)3.使用ALTERTABLE语句创建索引ALTERTABLE语句用于修改表，在修改表的时候可以向表中添加索引，语法格式如下：ALTERTABLE表名ADDPRIMARYKEY(字段名,…[ASC|DESC])|ADDINDEX|KEY[索引名](字段名,…[ASC|DESC])|ADD[UNIQUE|FULLTEXT|SPATIAL][INDEX|KEY][索引名](字段名,...[ASC|DESC])第6章视图和索引的使用6.2索引的使用6.2.2创建索引例19创建student1表,其表结构与student表相同,并在sno列上建立主键索引,在sname列上创建唯一索引,在classno列上创建普通单列索引。CREATETABLEstudent1(snoCHAR(10),snameCHAR(8)NOTNULL,ssexCHAR(2),sbirthdayDATE,saddressVARCHAR(40),spostcodeCHAR(6),sphoneCHAR(18),classnoCHAR(8),PRIMARYKEY(sno),UNIQUEINDEXindex_sname(sname),INDEXindex_classno(classnodesc));第6章视图和索引的使用6.2索引的使用6.2.2创建索引例20在已有表student的sname列上创建唯一索引。ALTERTABLEstudentADDUNIQUEindex_sname(snamedesc);例21使用CREATEINDEX语句在class