第10章视图与索引

null数据库原理与应用教程 ―SQL Server数据库原理与应用教程 ―SQL Server第10章 视图和索引第10章 视图和索引第10章 视图和索引数据库的基本表是按照数据库 设计 领导形象设计圆作业设计ao工艺污水处理厂设计附属工程施工组织设计清扫机器人结构设计 人员的观点设计的，并不一定符合用户的需求。SQL Server 2005可以根据用户需求重新定义表的数据结构，这种数据结构就是视图。 视图是关系数据库系统提供给用户以多种角度观察数据库中数据的重要机制，其结构和数据是建立在对表的查询基础上的。 视图不是真实存在的基本表而是一张虚表，视图所对应的数据并不实际地存储在数据库中，而是存储在视图所引用的表中。第10章 视图和索引第10章 视图和索引索引是以表列为基础的数据库对象，它保存着表中排序的索引列，并且记录了索引列在数据表中的物理存储位置，实现了表中数据的逻辑排序。 在数据库中，索引可以使数据库程序在最短的时间内找到所需要的数据，而不必查找整个数据库，这样可以节省时间，提高查找效率。第10章 视图和索引第10章 视图和索引在数据库的三级模式结构当中，索引对应的是内模式部分，基本表对应的是模式部分，而视图对应的是外模式部分。 本章主要介绍视图的基本概念、视图的创建、修改和删除以及如何利用视图实现对基本表中数据的操作，索引的基本概念、索引的分类以及创建、修改和删除索引等操作。第10章 视图和索引第10章 视图和索引10.1视图 视图(View) 是一个虚拟表，并不表示任何物理数据，只是用来查看数据的窗口而已，用户透过视图可以看到数据库中自己感兴趣的 内容 财务内部控制制度的内容财务内部控制制度的内容人员招聘与配置的内容项目成本控制的内容消防安全演练内容 。 视图是从一个或几个表导出来的表，它实际上是一个查询结果，视图所对应的数据不实际存储，数据库中只存储视图的定义。当基本表中的数据发生变化时，从视图中查询出来的数据也随之改变。第10章 视图和索引第10章 视图和索引10.1.1 视图概述 视图中的数据行和列都来自于基本表，是在视图被引用时动态生成的。用户可以通过视图来访问数据，而不必直接去访问该视图的基本表。 使用视图有很多优点，主要表现在： (1) 为用户集中数据，简化用户的数据查询和处理。 使得分散在多个表中的数据，通过视图定义在一起，屏蔽了数据库的复杂性，用户不必输入复杂的查询语句，只需针对此视图做简单的查询即可。第10章 视图和索引第10章 视图和索引视图的优点： (2) 保证数据的逻辑独立性。 对于视图的操作，例如，查询只依赖于视图的定义，当构成视图的基本表需要修改时，只需要修改视图定义中的子查询部分，而基于视图的查询不用改变。简化查询操作，屏蔽了数据库的复杂性。 (3) 重新定制数据，使得数据便于共享；合并分割数据，有利于数据输出到应用程序中。 (4) 数据保密。对不同的用户定义不同的视图，使用户只能看到与自己有关的数据。同时简化了用户权限的管理，增加了安全性。 第10章 视图和索引第10章 视图和索引10.1.2 创建视图 1．在SQL Server Management Studio中创建视图 2．使用T-SQL语句创建视图 SQL Server 2005提供了CREATE VIEW语句创建视图，语法 格式 pdf格式笔记格式下载页码格式下载公文格式下载简报格式下载 如下： CREATE VIEW <视图名>[(<列名>[，<列名>]···)] AS <子查询> [WITH CHECK OPTION]；透过视图进行增删改操作时，不得破坏视图定义中的谓词条件第10章 视图和索引第10章 视图和索引DBMS执行CREATE VIEW语句时只是把视图的定义存入数据字典，并不执行其中的SELECT语句。在对视图查询时，按视图的定义从基本表中将数据查出。 组成视图的属性列名或者全部省略或者全部指定 省略: 则视图由子查询中SELECT目标列中的诸字段组成 明确指定视图的所有列名: (1) 某个目标列是集函数或列表达式 (2) 目标列为 * (3) 多表连接时选出了几个同名列作为视图的字段 (4) 需要在视图中为某个列启用新的更合适的名字第10章视图和索引第10章视图和索引10.1.2 创建视图 2．使用T-SQL语句创建视图 [例10-1]创建学生_课程_成绩视图，包括计算机专业的学生的学号、姓名，和他们选修的课程号、课程名和成绩。 USE 教学库 GO CREATE VIEW 学生_课程_成绩 AS SELECT 学生.学生号,姓名,课程.课程号,课程名,成绩 FROM 学生,选课,课程 WHERE 学生.学生号=选课.学生号 AND 课程.课程号=选课.课程号 AND 专业='计算机' GO第10章视图和索引第10章视图和索引10.1.2 创建视图 2．使用T-SQL语句创建视图 例：建立信息系学生的视图，并要求进行修改和插入操作时仍需保证该视图只有信息系的学生 CREATE VIEW IS-Student AS SELECT Sno，Sname，Sage FROM Student WHERE Sdept=‘IS’ WITH CHECK OPTION； 第10章 视图和索引第10章 视图和索引10.1.2 创建视图 视图不仅可以建立在一个基本表或多个基本表上还可以建立在一个或多个已定义好的视图上, 或建立在基本表与视图上，还可以用带有聚合函数和GROUP BY子句的查询来定义视图 例：将学生的学号及他的平均成绩定义为一个视图 CREATE VIEW S-G（Sno，Gavg） AS SELECT Sno，AVG（Grade） FROM SC GROUP BY Sno； 第10章视图和索引第10章视图和索引10.1.3 修改视图 1．在SQL Server Management Studio中修改视图 2．使用T-SQL语句修改视图 T-SQL提供了ALTER VIEW语句修改视图，语法格式如下： ALTER VIEW <视图名>[(<列名>[，<列名>]···)] AS <子查询> [WITH CHECK OPTION]；第10章视图和索引第10章视图和索引10.1.4 使用视图 视图创建完毕，就可以如同查询基本表一样通过视图查询所需要的数据，而且有些查询需求的数据直接从视图中获取比从基表中获取数据要简单，也可以通过视图修改基表中的数据。 1．使用视图进行数据查询 可以在SQL Server Management Studio中选中要查询的视图并打开，浏览该视图查询的所有数据；也可以在查询窗口中执行T-SQL语句查询视图。第10章视图和索引第10章视图和索引10.1.4 使用视图 [例10-4]在查询窗口中查询学生_课程_成绩视图，统计“C++语言”课程的总分和平均分。 USE 教学库 GO SELECT 总分=SUM(成绩),平均分=AVG(成绩) FROM 学生_课程_成绩 WHERE 课程名='C++语言' GO第10章视图和索引第10章视图和索引10.1.4 使用视图 2．使用视图修改基本表中数据 修改视图的数据，其实就是对基本表进行修改，真正插入数据的地方是基本表，而不是视图，同样使用INSERT、UPDATE、DELETE语句来完成。但是在对视图进行修改的时候也要注意一些事项，并不是所有的视图都可以更新，只有对满足可更新条件（P182）的视图才能进行更新。第10章视图和索引第10章视图和索引10.1.4 使用视图 2．使用视图修改基本表中数据 [例10-6]通过“男生视图”向“学生”表中插入一个“男”生。 INSERT INTO 男生视图 VALUES ('0501001', '张三', '男', '计算机') 如果通过“男生视图”向“学生”表中插入一个女生，也可以完成插入，如果不允许通过“男生视图”插入“女”生，在创建“男生视图”应该使用WITH CHECK OPTION选项。第10章视图和索引第10章视图和索引10.1.5 删除视图 在不需要该视图的时候或想清除视图定义及与之相关联的权限时，可以删除该视图。视图的删除不会影响所依附的基表的数据。 1．在SQL Server Management Studio删除视图 2．在查询窗口中执行T-SQL语句删除视图 T-SQL提供了视图删除语句DROP VIEW。 其语法格式如下： DROP VIEW view_name第10章视图和索引第10章视图和索引10.2索引 索引(Index)是对数据库表中一个或多个列的值进行排序的结构，其主要目的是提高SQL Server系统的性能，加快数据的查询速度和减少系统的响应时间。所以，索引就是加快检索表中数据的方法。 10.2.1 索引简介 数据库的索引通过记录表中的关键值指向表中的记录，这样数据库引擎就不用扫描整个表而定位到相关的记录。相反，如果没有索引，则会导致SQL Server搜索表中的所有记录，以获取匹配结果。第10章视图和索引第10章视图和索引10.2.1 索引简介 索引的优点： （1）大大加快数据的检索速度，这是创建索引的最主要的原因。 （2）创建唯一性索引，保证表中每一行数据的唯一性。 （3）加速表和表之间的连接。 （4）在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。 （5）查询优化器可以提高系统的性能，但它是依靠索引起作用的。 第10章视图和索引第10章视图和索引10.2.2 索引类型 SQL Server 2005支持在表中任何列（包括计算列）上定义索引。 索引可以是唯一的，即索引列不会有两行记录相同，这样的索引称为唯一索引。索引也可以是不唯一的，即索引列上可以有多行记录相同。 如果索引是根据单列创建的，这样的索引称为单列索引，根据多列组合创建的索引则称为复合索引。 索引的组织方式的不同，可以将索引分为聚集索引和非聚集索引。第10章视图和索引第10章视图和索引10.2.2 索引类型 1. 聚集索引 聚集索引会对表和视图进行物理排序，所以这种索引对查询非常有效，在表和视图中只能有一个聚集索引。当建立主键约束时，如果表中没有聚集索，SQL Server会用主键列作为聚集索引键。 可以在表的任何列或列的组合上建立索引，实际应用中一般为定义成主键约束的列建立聚集索引。第10章视图和索引第10章视图和索引10.2.2 索引类型 1. 聚集索引 按照索引的字段排列记录，并且依照排好的顺序将记录存储在表中。 2. 非聚集索引 非聚集索引不会对表和视图进行物理排序。如果表中不存在聚集索引，则表是未排序的。在表或视图中，最多可以建立250个非聚集索引，或者249个非聚集索引和1个聚集索引。第10章视图和索引第10章视图和索引10.2.2 索引类型 聚集索引和非聚集索引都可以是唯一索引。 唯一索引的概念 唯一索引表示表中每个索引值只对应唯一的数据记录 当表中有被设置为UNIQUE的字段时，SQL SERVER会自动建立一个非聚集的唯一性索引。 而当表中有PRIMARY KEY的字段时，SQL SERVER会在PRIMARY KEY字段建立一个聚集唯一索引。 复合索引的概念 复合索引是将两个字段或多个字段组合起来建立的索引，而单独的字段允许有重复的值。第10章视图和索引第10章视图和索引10.2.3 创建索引 1、通过SQL Server Management Studio创建索引 2、利用T-SQL语句创建索引 使用T-SQL语句创建索引的语法格式如下： CREATE [ UNIQUE ][ CLUSTERED | NONCLUSTERED ] INDEX index_name ON { table_name | view_name } ( column [ ASC | DESC ] [ ,...n ] ) 第10章视图和索引第10章视图和索引10.2.3 创建索引 [例10-8] 根据教学库中学生表的姓名列的升序创建一个名为index_学生姓名的普通索引。 USE 教学库 GO CREATE INDEX index_学生姓名 ON 学生(姓名) GO第10章视图和索引第10章视图和索引10.2.3 创建索引 3．利用T-SQL语句创建索引 例：为学生-课程数据库中的学生、课程和选课三个表建立索引。其中，学生表按学号升序建立唯一索引；课程表按课程号升序建立唯一索引，选课表按学号升序和课程号降序建立唯一索引 CREATE UNIQUE INDEX xh ON 学生(学号)； CREATE UNIQUE INDEX kch ON 课程(课程号)； CREATE UNIQUE INDEX xkh ON 选课(学号 ASC, 课程号 DESC)；第10章视图和索引第10章视图和索引10.2.3 创建索引 4．间接创建索引 在定义表结构或修改表结构时，如果定义了主键约束（PRAMARY KEY）或者唯一性约束（UNIQUE），可以间接创建索引。 [例10-10] 创建一个学生1表，并定义主键约束。 USE 教学库 GO CREATE TABLE 学生1 (sno char(6) PRAMARY KEY, sname char(8) ) 此例中，就按sno升序创建了一个聚集索引。第10章视图和索引第10章视图和索引10.2.3 创建索引 4．间接创建索引 [例10-11] 创建一个教师表，并定义了主键约束和唯一性约束。 USE 教学库 GO CREATE TABLE 教师 ( tno char(6) PRAMARY KEY, tname char(8) UNIQUE ) 此例中，创建了两个索引，按tno升序创建了一个聚集索引，按tname升序创建了一个非聚集唯一索引。第10章视图和索引第10章视图和索引10.2.4 查看索引信息 在实际使用索引的过程中，有时需要对表的索引信息进行查询，了解在表中曾经建立的索引。可以使用SQL Server Management Studio进行查询；也可以在查询分析器中使用T-SQL语言进行查询。 1.在SQL Server Management Studio中查看索引信息 在SQL Server Management Studio中，选择要查看的表，然后使用鼠标右键单击相应的表，从菜单中选择“修改”，进入“表设计器”窗口，右键单击任意位置，选择“索引/键”即可查看此表上所有的索引信息。例如，查看“学生表”上的索引信息。第10章视图和索引第10章视图和索引10.2.4 查看索引信息 2.使用T_SQL语句查看索引信息 可以使用系统存储过程sp_helpindex或sp_help来查看索引信息，比如查看“学生表”上的索引信息。 (1)使用系统存储过程sp_helpindex查看索引信息 EXEC sp_helpindex 学生 (2)使用系统存储过程sp_help查看索引信息 EXEC sp_help 学生 执行sp_help系统存储过程查询的结果要比执行sp_helpindex显示的结果更加详细，除了索引信息，还包括当前表的基本信息、与此表相关的各种约束等。第10章 视图和索引第10章 视图和索引10.2.5 删除索引 当一个索引不再需要时，可以将其从数据库中删除，以释放当前占用的存储空间，这些释放的空间可以由数据库中的任何对象使用。删除某个表时，会自动删除在此表上创建的索引。 1．在SQL Server Management Studio中删除索引 2．使用T-SQL语句删除索引 删除索引的T-SQL语句的语法格式为： DROP INDEX table_name.index_name [例10-13 ]删除学生表中的“Index_姓名”索引。 USE 教学库 GO DROP INDEX 学生.Index_姓名 GO