《索引与视图》PPT课件.ppt

第7章,索引与视图,索引与视图,第1讲 索引,学习要点,索引索引概述创建索引查看和删除索引视图视图概述创建、修改、查看、删除、使用视图,索引,索引概述索引是一个单独的、物理的数据库结构该结构中包括表中的一列或若干列的值以及相应的指向表中物理标识这些值的数据页的逻辑指针的集合 索引依赖于数据库的表，作为表的一个组成部分一旦创建后，由数据库系统自身进行维护 存放于索引页面中个表的存储是由两部分组成的，一部分用来存放表的数据页面，另一部分存放索引页面 分类,学习要点,索引概述,分类按存储结构的不同分为两类聚集索引 （Clustered Index）非聚集索引（Nonclustered Index）,索引概述,索引分类,聚集索引（Clustered Index）聚集索引与数据混为一体 对表的物理数据页中的数据按列进行排序，然后再重新存储到磁盘上表的所有数据完全重新排列 一个表只能有一个聚集索引,分类,索引分类,返回,索引分类,非聚集索引（Nonclustered Index） 本索引与表的数据完全分离 无需将物理数据页中的数据按列排序 ，而是存储索引行故使用更多存储空间(与聚集索引相比)一个表最多可以建249 个非聚集索引,分类,索引分类,返回,惟一性索引,按照表中建立索引的那一列(或列组合)中的数据是否各不相同，分为惟一索引和非惟一索引。 在表中建立惟一索引时，组成该索引的字段或字段组合在表中具有惟一值。也就是说，对于表中的任何两行记录来说，索引列的值都是各不相同的。,创建索引的理由,提高数据查询速度加速表与表之间的连接，这一点在实现数据的参照完整性方面具有特别的意义。在使用ORDER BY 和GROUP BY子句进行数据检索时，可以显著减少查询中排序和分组的时间。通过创建惟一性索引，可以保证数据记录的惟一性。查询优化器依靠索引起作用,不在每一列上建索引的理由,当我们在创建聚集索引时，索引所需要的可用空间应该是数据库表中数据量的120 % ,该空间不包括现存表已经占用的空间。在建立索引时，数据被复制以便建立聚集索引，索引建立后，再将旧的未加索引的表数据删除，而且创建索引也需要时间。所以，创建索引要花费时间和占用存储空间。而且，使用INSERT和UPDATE对数据进行操作时，需要动态维护索引，维护索引也是需要时间和空间的。,考虑创建索引的列,一般来说，如下情况的列考虑创建索引： 主键。一般而言，存取表的最常用的方法是通过主键来进行。因此，我们应该在主键上建立索引。 连接中频繁使用的列(外键)。这是因为用于连接的列若按顺序存放，系统可以很快执行连接。 在某一范围内频繁搜索的列和按排序顺序频繁检索的列。,考虑创建索引的列,而如下情况的列不考虑建立索引： 很少或从来不在查询中引用的列。因为系统很少或从来不根据这个列的值去查找行，所以不考虑建立索引。 只有两个或若干个值的列(如性别：男/女)，也得不到建立索引的好处。 小表(行数很少的表)一般也没有必要创建索引。,索引,创建索引使用企业管理器创建使用T-SQL创建,学习要点,在图形界面下创建索引,在图形界面下创建索引,在弹出的“新建索引”对话框中输入索引的名称，设置索引的类型。,在图形界面下创建索引,在对话框中单击“添加”按钮，将弹出“选择列”对话框。选择要添加到索引键的表列，并点击“确定”按钮关闭该对话框。,创建索引,使用T-SQL创建基本语句格式：示例,CREATE UNIQUE CLUSTERED | NONCLUSTEREDINDEX 索引名 ON 表 | 视图 （列 ASC | DESC ,.n）,UNIQUE,CLUSTERED,NONCLUSTERED,ASC | DESC,创建唯一索引,创建聚集索引,创建非聚集索引,创建排序方式，默认为升序(ASC),创建索引,创建索引,示例建立关于information表的s_no列的聚集索引 建立关于course表的c_no列的惟一非聚集索引,示例,CREATE CLUSTERED INDEX s_no_id ON information（s_no）,CREATE UNIQUE NONCLUSTERED INDEX c_no_id ON course（c_no）,创建索引,示例建立关于score表的s_no列和c_no列的复合非聚集索引,使用T-SQL创建索引,CREATE NONCLUSTERED INDEX s_c_no_id ON score（s_no，c_no）,索引,查看和删除索引使用企业管理器使用T-SQL,学习要点,使用,查看索引基本语句格式：查看information 表的索引 返回结果,使用T-SQL,T-SQL,sp_helpindex objname = name,sp_helpindex information,返回所有索引信息,使用,删除索引基本语句格式：删除 course 表的 c_no_id 索引,使用T-SQL,T-SQL,DROP INDEX 表名.索引名 ,.n,删除一个或多个当前数据库中的索引,DROP INDEX course.c_no_id,此命令无法删除由CREATE TABLE 或ALTER TABLE 命令创建的PRIMARY KEY 或UNIQUE 约束索引,注意：,视图,视图概述视图不是真实存在的基础表，而是一张虚表 通过视图看到的数据只是存放在基本表中的数据 对视图的操作与对表的操作一样可以对其进行查询、修改（有一定的限制）和删除,学习要点,视图,视图(View)是一种常用的数据库对象。视图是一个虚表，即视图中的数据并不实际存储在视图中，而是存储在视图所对应的表中。数据库中只存储视图的定义，即视图是从哪个或哪些基表导出的。向视图提供数据的表称为视图的基表(Base Table)。一旦定义了一个视图，就可以像使用表一样使用它，可以对视图进行查询操作，并且基于一定的限制条件，也可以对视图进行更新操作（增、删、改）。,视图,使用视图有许多优点，如下所述：1) 集中数据，创建查询操作select * from 视图名2) 提供自动的安全保护功能例如:”网络”专业的同学想查看学生记录，但要不想让他们看到其他专业同学的记录，这时可以基于学生表创建视图，其中只有“网络”专业同学的信息，然后将使用这个视图的权限赋予该专业的同学，而对基表的访问权限不赋予给他们。,视图,创建使用企业管理器使用T-SQL,学习要点,在Management Studio中创建视图,2022/12/12,29,右击视图节点，从弹出菜单中选择“新建视图”,例7-10,例7-11,例7-12,创建视图,使用T-SQL基本语句：语句说明：列名如未在Create view后指明，则由查询语句中列字段组成(必须指明列名的三种情况)查询语句通常不允许含有ORDER BY子句DISTINCT短语,创建视图、示例,CREATE VIEW 视图名 ( 列名 ,.n ) AS 查询语句 WITH CHECK OPTION ,可以是任意复杂的SELECT语句,必须明确指明组成视图的所有列名 其中某个目标列不是单纯的列名，而是列表达式或聚合函数；多表连接时，选出了几个同名的列作为视图的字段；需要在视图中为某个列启用新的名字,返回,创建视图,示例：【例7-11】create view score_view asselect a_name ,t_teamname,s_gradefrom athlete as a inner join score as bon a.a_athleteid=b.s_athleteid inner join team as con a.a_teamid=c.t_teamid,next,查看视图,查看使用企业管理器使用T-SQL,学习要点,sp_help 视图名sp_helptext 视图名,查看视图结构,查看视图定义文本,修改视图,修改使用企业管理器使用T-SQL,学习要点,修改视图,使用企业管理器修改视图定义重命名视图,修改视图,修改视图,使用T-SQL使用T-SQL修改视图 使用sp_rename重命名视图,修改视图,修改视图,使用T-SQL修改视图 基本语句,使用T-SQL,ALTER VIEW 视图名 ( 列名 ,.n ) AS查询语句 WITH CHECK OPTION ,修改视图,使用sp_rename重命名视图 基本语句示例修改视图stu_info_view的名字为vwStu_Info。,使用T-SQL,sp_rename ，,sp_rename stu_info_view，vwStu_Info,删除视图,删除使用企业管理器使用T-SQL,学习要点,删除视图,使用T-SQL基本语句示例删除视图stu_info_view,DROP VIEW ,DROP VIEW stu_info_view,删除视图,视图,使用查询视图数据修改视图数据,学习要点,使用视图,查询视图数据视图与表具有相似的结构当定义视图以后，用户就可以像对基本表进行查询一样对视图进行查询了,使用视图,使用视图,从视图score_view中查询达到10分以上的信息,返回,SELECT * FROM score_viewWHERE s_grade=10,使用视图,修改视图数据实质对视图所引用的表(也称基表)执行数据的插入和更新,使用视图、示例,使用视图,相关限制在一个语句中，一次不能修改一个以上的视图基表 对视图中所有列的修改必须遵守视图基表中所定义的各种数据约束条件 不允许对视图中的计算列进行修改，也不允许对视图定义中包含有统计函数或者GROUP BY子句的视图进行插入或修改操作,使用视图、示例,使用视图,通过视图t002_view插入一条记录,返回,INSERT INTO t002_viewVALUES(A00017,王勇兵,T005),使用视图,通过视图T002_VIEW修改表中姓名为徐显周的记录，修改其姓名为李婷婷,UPDATE t002_viewSET s_name=李婷婷WHERE s_name=徐显周,返回,使用视图,更新s_no为“20031001002”的学生的姓名为“王勇”，成绩增加2分。,UPDATE grade_viewSET s_name王勇兵，gradegrade2WHERE s_no20031001002,SET s_name王勇兵WHERE s_no20031001002UPDATE grade_viewSET gradegrade22,返回,使用视图,习题：数据库名：我班同学数据库同学表：学号 char(6)，姓名， 性别，年龄，民族，身份证号，宿舍号宿舍表： 宿舍号 char(6)，宿舍电话,使用视图,1：创建视图同学表视图代码； 同学表视图(学号, 姓名, 性别, 年龄, 民族, 身份证号, 宿舍号, 宿舍电话)2：从同学表视图中查询姓张的女同学的姓名、性别、宿舍电话。3：从同学表中查询女同学的最大年龄、最小年龄、平均年龄。,create view 同学表视图 asselect 学号, 姓名, 性别, 年龄, 民族, 身份证号, 同学表.宿舍号, 宿舍电话 from 同学表,宿舍表where 同学表.宿舍号=宿舍表.宿舍号,select 姓名, 性别, 宿舍电话 from 同学表视图 where 姓名 like 张% and 性别=女,select 最大年龄 = max(年龄), 最小年龄 = min(年龄), 平均年龄 = avg(年龄) from 同学表 where 性别=女,