第7章 使用视图和索引优化查询.ppt

《第7章 使用视图和索引优化查询.ppt》由会员分享，可在线阅读，更多相关《第7章 使用视图和索引优化查询.ppt（43页珍藏版）》请在课桌文档上搜索。

1、第7章 使用视图和索引优化查询,复习:,数据库的基本关系运算投影(查询指定的列)连接(查询多个表)选择(查询符合条件的行)基本查询对一张表的查询对表中的原始数据的查询,分别用什么子句实现?,select 列表名,from 表名,n,where 条件,例：select*from xsqk where 出生日期1981-01-01 and 性别=0,复习:,汇总查询(1)使用聚合函数：每个聚合函数只返回一个单值。select avg(成绩)from xs_kc(2)分类汇总：每个组返回一条汇总记录。select 学号,avg(成绩)from xs_kcgroup by 学号(3)(分类)明细汇总：

2、select*from xs_kc compute avg(成绩),having avg(成绩)75,by 学号,order by 学号,复习:,连接查询：同时查询多个表中的信息。内连接：将多表中满足条件的行连接起来。select xsqk.学号,姓名,课程号,成绩 from xsqk,xs_kc where xsqk.学号=xs_kc.学号 and 成绩60外连接：将满足条件的行连接，不匹配的行也保留。自连接：将同一个表中满足条件的行连接起来。,复习:,子查询：一个select语句(内查询)作为另一条select语句(外查询)的一部分。相关子查询：内查询的执行依赖外查询。嵌套子查询：（不依赖

3、）,select 学号,姓名,联系电话 from xsqkwhere(select avg(成绩)from xs_kc where xsqk.学号=xs_kc.学号)60,select 课程名称,授课教师 from kcwhere 课程号=ANY(select 课程号 from xs_kc where 成绩60),说明:子查询执行后,若返回1个单值,则可用比较运算符来设置条件:若返回一个列表值,则可用:(not)in、any、all,引：,请思考：查询结果集能否被用户修改？在查询窗口得到的结果是不能被修改的。查询结果集可以用into子句来保存。例：select xsqk.学号,姓名,专业名,课

4、程名,成绩 into cj from xsqk,xs_kc,kc where xsqk.学号=xs_kc.学号 and xs_kc.课程号=kc.课程号再思考：cj表中的数据与基表数据是否并存于数据库中？用户对查询到的信息(cj表)进行修改对基表是否有影响？如果用户直接对基表进行操作，会有什么后果？,是。但会造成：数据重复存储。,无影响。,后果是：增加了数据的不安全性。,视图的使用教学内容,基础知识：视图概述创建视图修改视图删除视图使用视图管理数据表中的数据,视图的使用学习目标,认知目标：了解视图的概念、使用的目的和好处理解视图与基表的关系 能力目标:掌握创建、修改、删除视图的方法(重点、难点

5、)掌握查询视图的方法(重点),一、视图概述,视图的含义和作用是基于某个查询结果的虚表。是用户查看和修改数据表中数据的一种方式。每个视图都有几个被定义的列和多个数据行。视图与基表的关系视图中的数据列和行来源于其所引用的基表。视图所对应的数据并不实际存储在数据库中，而是仍存储在视图所引用的基表中。数据库中只存储视图的定义。,一、视图概述,使用视图的目的与好处聚焦特定数据：使用户只能看到和操作与他们有关的数据，提高了数据的安全性。简化数据操作：使用户不必写复杂的查询语句就可对数据进行操作。定制用户数据：使不同水平的用户能以不同的方式看到不同的数据。合并分离数据：视图可以从水平和垂直方向上分割数据，但

6、原数据库的结构保持不变。,二、创建视图,最基本的语句格式：create view 视图(列名表)with encryption as select查询语句 with check option 通过对象资源管理器创建视图,加密视图的定义，使用户只能查看，不能修改。,强制所有通过视图修改的数据满足select语句中指定的条件。,二、创建视图,实例1：创建一个 名为“v1”的视图，用于查询计算机网络专业男生的学号、姓名、出生日期，并将视图的列名分别改为：学生学号、男生姓名、生日。,create view v1(学生学号,男生姓名,生日)as select 学号,姓名,出生日期 from xsqk w

7、here 专业名=计算机网络 and 性别=1goselect*from v1,通过视图查询基表中的数据。,二、创建视图,实例2：创建一个 名为“v综合信息”的视图，用于查询学生的学号、姓名、专业名、课程名、成绩等信息,create view V综合信息 as select xsqk.学号,姓名,专业名,课程名,成绩 from xsqk,xs_kc,kc where xsqk.学号=xs_kc.学号 and xs_kc.课程号=kc.课程号 exec sp_helptext v综合信息,with encryption,对视图的定义进行加密,查看视图的定义,二、创建视图,课堂练习1：创建一个名为

8、“V平均成绩”的视图，用于分组汇总查询每个学生的平均成绩，将视图的列名分别改为：学生学号，个人平均分，并加密视图的定义。,create view V平均成绩(学生学号,个人平均分)with encryptionas select 学号,avg(成绩)from xs_kc group by 学号,二、创建视图,实例3：创建一个名为“V单科最高分”的视图，用于查询每门课程的最高分的学生学号、姓名、课程号、成绩信息。,create view V单科最高分as select xsqk.学号,姓名,课程号,成绩 from xsqk,xs_kc where xsqk.学号=xs_kc.学号 and 成绩=

9、any(select max(成绩)from xs_kc group by 课程号),二、创建视图,课堂练习2：创建一个名为“V全校最高分”的视图，用于查询所有成绩中的最高分的学生学号、姓名、专业名、课程号、成绩。,create view V全校最高分asselect xsqk.学号,姓名,专业名,课程号,成绩 from xsqk,xs_kcwhere xsqk.学号=xs_kc.学号 and 成绩=(select max(成绩)from xs_kc),成绩=all(select 成绩 from xs_kc),三、修改视图,语法格式：alter view 视图(列名表)with encrypt

10、ion as select查询语句 with check option 通过对象资源管理器修改视图,三、修改视图,实例4：在“v1”的视图中增加两列：专业名和所在系。alter view v1(学生学号,男生姓名,生日,专业,系)as select 学号,姓名,出生日期,专业名,所在系 from xsqk where 专业名=计算机网络 and 性别=1,三、修改视图,实例5：修改名为“v综合信息”的视图，取消其加密属性，增加选项检查属性。,alter view V综合信息 as select xsqk.学号,姓名,专业名,课程名,成绩 from xsqk,xs_kc,kc where xsq

11、k.学号=xs_kc.学号 and xs_kc.课程号=kc.课程号 with check option,四、删除视图,语句格式drop view 视图名,n,五、使用视图管理数据表中的数据,说明：视图可以代替表完成从表中查询、插入、更新和删除数据的操作。,五、使用视图管理数据表中的数据-1.插入数据,实例6：向“V1”视图中添加两条记录。insert into v1values(020106,李好,1981-04-22,计算机网络,计算机)insert into v1values(020107,周权,1981-07-08,信息安全,计算机)select*from xsqkselect*fro

12、m v1,请思考:V1视图中为什么不能看到周权的记录?,因为周权的记录不满足视图的定义,即视图中只显示“计算机网络”专业的学生信息，所以周权的信息只能在基表中看到,而不能在视图中看到。,五、使用视图管理数据表中的数据-1.插入数据,插入数据的注意事项可通过视图向基表中插入数据，但插入的数据实际上存放在基表中，而不是存放在视图中。如果视图引用了多个表，使用insert语句插入的列必须属于同一个表。若创建视图时定义了“with check option”选项，则使用视图向基表中插入数据时，必须保证插入后的数据满足定义视图的限制条件。说明：如果不想让不满足视图定义的数据插入到基表中，可以在定义视图时

13、，加上“with check option”。,五、使用视图管理数据表中的数据-1.插入数据,实例7：假设“V1”视图已添加了“with check option”属性，通过视图向基表中添加两条记录。insert into v1values(020108,赵伟,1980-05-07,计算机网络,计算机)insert into v1values(020109,刘齐,1981-12-02,信息安全,计算机)请思考：这两条记录能否插入到基表中？能否在视图中能否显示？,“赵伟”的信息既能插入到基表中，也能在视图中看到。但“刘齐”的信息都不能，且系统会提示错误。,五、使用视图管理数据表中的数据-2.删除

14、数据,语句格式：delete from 视图名 where 条件实例8：删除“V1”视图中学号为020108的记录。delete from V1 where 学号=020108注意事项：要删除的数据必须包含在视图的结果集中。如果视图引用了多个表时，无法用delete命令删除数据。,引:,请思考:在一个数据表中检索数据时是如何进行的?如果采取逐行扫描的方式进行检索,可行吗?有什么方法可提高检索效率？,对于小表来说，是可行的。但对于有成千上万行的表来说，其搜索的效率非常低。,建立索引。,索引学习目标,认知目标：掌握索引的概念理解创建索引的必要性掌握几种索引类型的特点和它们适合的场合能力目标:掌握用

15、命令方式创建索引的方法(重点、难点)掌握查看、删除索引的方法(重点),一、索引的基本概念1.索引的含义,索引：是对表或视图中的一列或多个列的值进行排序的一种物理结构，它由该表或视图中的一列或若干列值，以及这些列值的记录在数据表中存储位置的物理地址所组成。索引如同书的目录索引信息存放在索引页中，表中的数据存放在数据页中。,一、索引的基本概念2.建立索引的优缺点,建立索引的优点建立了索引的列作为查询条件时，数据的检索速度能大大地提高。增加索引的不利方面 创建索引也要花费时间和占用物理空间。虽然索引加快了检索速度，但减慢了数据修改的速度(因为每执行一次数据修改，就需要对索引进行维护)。,一、索引的基

16、本概念3.使用索引的准则,何时创建索引？在主键上在用于连接的列(外键)上在经常用作查询条件的列上在经常要排序的列上,一、索引的基本概念 3.使用索引的准则,何时可不考虑建立索引？很少或从来不作为查询条件的列在小表中的任何列属于text、image、长度较大的char、varchar、binary等数据类型的列 当修改的性能需求远大于查询的性能需求时，不要创建索引,一、索引的基本概念4.索引的类型,按数据的物理存放顺序：聚集索引:会改变表记录的物理存储顺序，使之与索引列的顺序完全相同（适用于范围搜索）。非聚集索引:不改变表记录的存放顺序（适用于直接匹配单个条件的查询）。按索引列取值的唯一性：唯一

17、索引:要求被索引列不能有重复值，也不能有两个NULL。非唯一索引:不存在这一限制。按索引列的个数：单索引、复合索引,二、创建索引1.创建索引的方法,系统自动建立索引人工创建索引T-SQL命令方式（掌握）对象资源管理器方式,二、创建索引2.系统自动建立索引,添加主键约束时系统会自动在表中生成一个聚集惟一索引。在xsqk表的“学号”列上已创建了聚集惟一索引(名为PK_xsqk)在xs_kc表的“学号和课程号”两列上已创建了聚集惟一索引在kc表中的“课程号”列上已创建了聚集惟一索引添加惟一性约束时系统会自动在表中生成一个非聚集惟一索引。,二、创建索引2.系统自动建立索引,实例1：创建一个名为test

18、的表，其结构定义如下。use xscjcreate table test(课程号 smallint constraint pk_test primary key,课程名 char(14)constraint uq_test unique,授课教师 char(10),二、创建索引3.手工建立索引(1)表索引,命令格式create unique clustered|nonclustered index 索引名on 表名|视图名(列名 asc|desc,.n)说明：一个表中只能创建1个聚集索引。由于系统已自动在主键上创建了聚集索引，所以用户不能再创建，除非先删除已有的。一个表中可以创建若干个非聚集索

19、引。,默认值,二、创建索引 3.手工建立索引(1)表索引,实例2：在kc表的“课程名”列上创建一个非聚集索引，索引名为“ix_kcm”，并降序排列。实例3：在kc表中，重新创建名为“ix_kcm”的索引，使其成为惟一性的非聚集索引。,create unique index ix_kcm on kc(课程名 desc)with drop_existing,create index ix_kcm on kc(课程名 desc),删除同名的原索引，重建新索引。,二、创建索引 3.手工建立索引(1)表索引,实例4：已知xs_kc表中的“学号”和“课程号”两列已设置为主键，主键约束名为“PK_xs_kc

20、”，请重新创建这两列的组合索引，使学号降序排列，使索引页填满60%后就换新页进行填充。,create unique clustered index pk_xs_kcon xs_kc(学号 desc,课程号)with pad_index,fillfactor=60,drop_existing,中间级索引页也按“填充因子”指定的填充度填充,叶级索引页按60%进行填充,二、创建索引 3.手工建立索引(1)表索引,课堂练习：已知xsqk表中的“学号”列已设置为主键，请在“姓名”和“所在系”两列上创建名为“ix_name_xi”的索引，按姓名降序排列，并使索引页填满60%后就换新页进行填充。请考虑：是否

21、要创建惟一性索引？能否创建聚集索引？,create index ix_name_xi on xsqk(姓名 desc,所在系)with pad_index,fillfactor=60,三、操作索引1.查看索引,命令格式：exec sp_helpindex 表名|视图名 用对象资源管理器方式,三、操作索引2.删除索引,命令格式：drop index 表名.|视图名.索引名,n 实例6：删除表中的索引。drop index kc.ix_kcm,xsqk.ix_name_xi注意：SQL Server自动建立的索引不能用drop index删除，只能用alter table语句中的drop constraint子句来解除加在该字段上的主键约束或惟一性约束，这些约束一解除，相关的索引也就被删除了。,本课小结,使用视图的目的创建视图：create view 修改视图：alter view 查看视图：select*from 视图名通过视图管理数据表中的数据插入数据删除数据,索引的作用提高检索数据的速度使用索引的准则索引的类型聚集和非聚集索引惟一和非惟一索引单和复合索引,创建索引的方法系统自动创建索引添加主键约束时添加惟一性约束时人工创建索引命令方式对象资源管理器删除索引的方法drop index 索引名alter table 表名 drop constraint 约束名,