搜索
第7章索引与视图索引与视图第1讲索引3学习要点索引索引概述创建索引查看和删除索引视图视图概述创建、修改、查看、删除、使用视图4索引索引概述索引是一个单独的、物理的数据库结构该结构中包括表中的一列或若干列的值以及相应的指向表中物理标识这些值的数据页的逻辑指针的集合索引依赖于数据库的表，作为表的一个组成部分一旦创建后，由数据库系统自身进行维护存放于索引页面中个表的存储是由两部分组成的，一部分用来存放表的数据页面，另一部分存放索引页面分类学习要点5索引概述分类按存储结构的不同分为两类聚集索引（ClusteredIndex）非聚集索引（NonclusteredIndex）索引概述6索引分类聚集索引（ClusteredIndex）聚集索引与数据混为一体对表的物理数据页中的数据按列进行排序，然后再重新存储到磁盘上•表的所有数据完全重新排列一个表只能有一个聚集索引分类7索引分类返回8索引分类非聚集索引（NonclusteredIndex）本索引与表的数据完全分离无需将物理数据页中的数据按列排序，而是存储索引行•故使用更多存储空间(与聚集索引相比)一个表最多可以建249个非聚集索引分类9索引分类返回10惟一性索引按照表中建立索引的那一列(或列组合)中的数据是否各不相同，分为惟一索引和非惟一索引。在表中建立惟一索引时，组成该索引的字段或字段组合在表中具有惟一值。也就是说，对于表中的任何两行记录来说，索引列的值都是各不相同的。11创建索引的理由•提高数据查询速度•加速表与表之间的连接，这一点在实现数据的参照完整性方面具有特别的意义。•在使用ORDERBY和GROUPBY子句进行数据检索时，可以显著减少查询中排序和分组的时间。•通过创建惟一性索引，可以保证数据记录的惟一性。•查询优化器依靠索引起作用12不在每一列上建索引的理由当我们在创建聚集索引时，索引所需要的可用空间应该是数据库表中数据量的120%,该空间不包括现存表已经占用的空间。在建立索引时，数据被复制以便建立聚集索引，索引建立后，再将旧的未加索引的表数据删除，而且创建索引也需要时间。所以，创建索引要花费时间和占用存储空间。而且，使用INSERT和UPDATE对数据进行操作时，需要动态维护索引，维护索引也是需要时间和空间的。13考虑创建索引的列一般来说，如下情况的列考虑创建索引：●主键。一般而言，存取表的最常用的方法是通过主键来进行。因此，我们应该在主键上建立索引。●连接中频繁使用的列(外键)。这是因为用于连接的列若按顺序存放，系统可以很快执行连接。●在某一范围内频繁搜索的列和按排序顺序频繁检索的列。14考虑创建索引的列而如下情况的列不考虑建立索引：●很少或从来不在查询中引用的列。因为系统很少或从来不根据这个列的值去查找行，所以不考虑建立索引。●只有两个或若干个值的列(如性别：男/女)，也得不到建立索引的好处。●小表(行数很少的表)一般也没有必要创建索引。15索引创建索引使用企业管理器创建使用T-SQL创建学习要点16在图形界面下创建索引17在图形界面下创建索引在弹出的“新建索引”对话框中输入索引的名称，设置索引的类型。18在图形界面下创建索引在对话框中单击“添加”按钮，将弹出“选择列”对话框。选择要添加到索引键的表列，并点击“确定”按钮关闭该对话框。19创建索引使用T-SQL创建基本语句格式：示例CREATE[UNIQUE][CLUSTERED|NONCLUSTERED]INDEX索引名ON{表|视图}（列[ASC|DESC][,...n]）UNIQUECLUSTEREDNONCLUSTEREDASC|DESC创建唯一索引创建聚集索引创建非聚集索引创建排序方式，默认为升序(ASC)创建索引20创建索引示例建立关于information表的s_no列的聚集索引建立关于course表的c_no列的惟一非聚集索引示例CREATECLUSTEREDINDEXs_no_idONinformation（s_no）CREATEUNIQUENONCLUSTEREDINDEXc_no_idONcourse（c_no）21创建索引示例建立关于score表的s_no列和c_no列的复合非聚集索引使用T-SQL创建索引CREATENONCLUSTEREDINDEXs_c_no_idONscore（s_no，c_no）22索引查看和删除索引使用企业管理器使用T-SQL学习要点23使用查看索引基本语句格式：查看information表的索引返回结果使用T-SQLT-SQLsp_helpindex[@objname=]'name'sp_helpindexinformation返回所有索引信息24使用删除索引基本语句格式：删除course表的c_no_id索引使用T-SQLT-SQLDROPINDEX表名.索引名[,...n]删除一个或多个当前数据库中的索引DROPINDEXcourse.c_no_id此命令无法删除由CREATETABLE或ALTERTABLE命令创建的PRIMARYKEY或UNIQUE约束索引注意：25视图视图概述视图不是真实存在的基础表，而是一张虚表通过视图看到的数据只是存放在基本表中的数据对视图的操作与对表的操作一样可以对其进行查询、修改（有一定的限制）和删除学习要点26视图视图(View)是一种常用的数据库对象。视图是一个虚表，即视图中的数据并不实际存储在视图中，而是存储在视图所对应的表中。数据库中只存储视图的定义，即视图是从哪个或哪些基表导出的。向视图提供数据的表称为视图的基表(BaseTable)。一旦定义了一个视图，就可以像使用表一样使用它，可以对视图进行查询操作，并且基于一定的限制条件，也可以对视图进行更新操作（增、删、改）。27视图使用视图有许多优点，如下所述：1)集中数据，创建查询操作select*from视图名2)提供自动的安全保护功能例如:”网络”专业的同学想查看学生记录，但要不想让他们看到其他专业同学的记录，这时可以基于学生表创建视图，其中只有“网络”专业同学的信息，然后将使用这个视图的权限赋予该专业的同学，而对基表的访问权限不赋予给他们。28视图创建使用企业管理器使用T-SQL学习要点29在ManagementStudio中创建视图2020/12/129右击视图节点，从弹出菜单中选择“新建视图”3031例7-1032例7-1133例7-1234创建视图使用T-SQL基本语句：语句说明：列名如未在Createview后指明，则由查询语句中列字段组成(必须指明列名的三种情况)查询语句通常不允许含有ORDERBY子句DISTINCT短语创建视图、示例CREATEVIEW视图名[(列名[,...n])]AS查询语句[WITHCHECKOPTION]可以是任意复杂的SELECT语句35必须明确指明组成视图的所有列名1.其中某个目标列不是单纯的列名，而是列表达式或聚合函数；2.多表连接时，选出了几个同名的列作为视图的字段；3.需要在视图中为某个列启用新的名字返回36创建视图示例：【例7-11】createviewscore_viewasselecta_name,t_teamname,s_gradefromathleteasainnerjoinscoreasbona.a_athleteid=b.s_athleteidinnerjointeamascona.a_teamid=c.t_teamidnext37查看视图查看使用企业管理器使用T-SQL学习要点38sp_help视图名sp_helptext视图名查看视图结构查看视图定义文本39修改视图修改使用企业管理器使用T-SQL学习要点40修改视图使用企业管理器修改视图定义重命名视图修改视图41修改视图使用T-SQL使用T-SQL修改视图使用sp_rename重命名视图修改视图42修改视图使用T-SQL修改视图基本语句使用T-SQLALTERVIEW视图名[(列名[,...n])]AS查询语句[WITHCHECKOPTION]43修改视图使用sp_rename重命名视图基本语句示例修改视图stu_info_view的名字为vwStu_Info。使用T-SQLsp_rename旧的视图名，新的视图名sp_renamestu_info_view，vwStu_Info44删除视图删除使用企业管理器使用T-SQL学习要点45删除视图使用T-SQL基本语句示例删除视图stu_info_viewDROPVIEW视图名DROPVIEWstu_info_view删除视图46视图使用查询视图数据修改视图数据学习要点47使用视图查询视图数据视图与表具有相似的结构当定义视图以后，用户就可以像对基本表进行查询一样对视图进行查询了使用视图48使用视图从视图score_view中查询达到10分以上的信息返回SELECT*FROMscore_viewWHEREs_grade=1049使用视图修改视图数据实质对视图所引用的表(也称基表)执行数据的插入和更新使用视图、示例50使用视图相关限制1.在一个语句中，一次不能修改一个以上的视图基表2.对视图中所有列的修改必须遵守视图基表中所定义的各种数据约束条件3.不允许对视图中的计算列进行修改，也不允许对视图定义中包含有统计函数或者GROUPBY子句的视图进行插入或修改操作使用视图、示例51使用视图通过视图t002_view插入一条记录返回INSERTINTOt002_viewVALUES(‘A00017’,‘王勇兵’,‘T005')52使用视图通过视图T002_VIEW修改表中姓名为‘徐显周’的记录，修改其姓名为‘李婷婷’UPDATEt002_viewSETs_name='李婷婷'WHEREs_name='徐显周'返回53使用视图更新s_no为“20031001002”的学生的姓名为“王勇”，成绩增加2分。UPDATEgrade_viewSETs_name＝'王勇兵'，grade＝grade＋2WHEREs_no＝'20031001002'×SETs_name＝'王勇兵'WHEREs_no＝'20031001002'UPDATEgrade_viewSETgrade＝grade＋22√返回54使用视图习题：数据库名：我班同学数据库同学表：学号char(6)，姓名，性别，年龄，民族，身份证号，宿舍号宿舍表：宿舍号char(6)，宿舍电话使用视图551：创建视图[同学表视图]代码；同学表视图(学号,姓名,性别,年龄,民族,身份证号,宿舍号,宿舍电话)2：从同学表视图中查询姓张的女同学的姓名、性别、宿舍电话。3：从同学表中查询女同学的最大年龄、最小年龄、平均年龄。56createview[同学表视图]asselect学号,姓名,性别,年龄,民族,身份证号,同学表.宿舍号,宿舍电话from同学表,宿舍表where同学表.宿舍号=宿舍表.宿舍号57select姓名,性别,宿舍电话from同学表视图where姓名like'张%'and性别='女'58select最大年龄=max(年龄),最小年龄=min(年龄),平均年龄=avg(年龄)from同学表where性别='女'