《第19讲视图和索引剖析》由会员分享，可在线阅读，更多相关《第19讲视图和索引剖析（56页珍藏版）》请在金锄头文库上搜索。
1、第4章 视图,第19讲,复习:,数据库的基本关系运算 投影(查询指定的列) 连接(查询多个表) 选择(查询符合条件的行) 基本查询 对一张表的查询 对表中的原始数据的查询,分别用什么子句实现?,select 列表名,from 表名 , n ,where 条件,例：select * from xsqk where 出生日期1997-01-01 and 性别=0,复习:,汇总查询 (1) 使用聚合函数：每个聚合函数只返回一个单值。 select avg ( 成绩 ) from xs_kc (2) 分类汇总：每个组返回一条汇总记录。 select 学号, avg (成绩) from xs_kc gr
2、oup by 学号 (3) (分类)明细汇总： 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 xsqk where ( select avg(成绩) from xs_kc where xsqk.学号=xs_kc.学号 ) 60,select 课程名称 , 授课教师 from kc where 课程号 = ANY (select 课程号 from xs_kc where 成绩60),说明:子查询执行后, 若返回1个单值,则可用比较运算符来设置条件: 若返回一个列表值,则可用:(not) in、any、all,引入：,提问： 如果经常需要从多个表中获取特定列的
4、数据，并需要将这些数据组织在一起使用，有没有什么好办法？,第4章 视图 教学内容,基础知识： 视图概述 创建、修改、删除视图 使用视图管理数据表中的数据,第4章 视图 学习目标,认知目标： 了解视图的概念、使用的目的和好处 能力目标: 掌握创建、修改、删除视图的方法(重点、难点),4.1 视图概述,视图的含义和作用 是基于某个查询结果的虚表。是用户查看和修改数据表中数据的一种方式。 每个视图都有若干被定义的列和行。 视图与基表的关系 视图中的数据列和行来源于是其引用的基表。 视图所对应的数据并不实际存储在数据库中，而是仍存储在视图所引用的基表中。 数据库中只存储视图的定义。,4.1 视图概述,
5、使用视图的目的与好处 聚焦特定数据：使用户只能看到和操作与他们有关的数据，提高了数据的安全性。 简化数据操作：使用户不必写复杂的查询语句就可对数据进行操作。 定制用户数据：使不同水平的用户能以不同的方式看到不同的数据。 合并分离数据：视图可以从水平和垂直方向上分割数据，但原数据库的结构保持不变。,4.2 创建视图,最基本的语句格式： create view 视图 as select查询语句 with check option 通过SSMS创建视图,WITH CHECK OPTION：表示对视图进行UPDATE、INSERT、DELETE操作时要保证更新、插入、删除的行满足视图定义中的谓词条件（
6、即子查询中的条件表达式）。,4.2 创建视图,实例1：创建一个 名为“v1”的视图，用于查询成绩在6080之间的学生学号、姓名、专业、课程号、成绩信息。,create view v1 as select XSQK.学号,姓名,专业,课程号,成绩 from XSQK, XS_KC where XSQK.学号=XS_KC.学号 and 成绩=60 and 成绩80 go select * from v1,通过视图查询基表中的数据。,4.3 使用视图,说明： 视图可以代替表完成从表中查询、插入、更新和删除数据的操作。,4.3 使用视图 -1.插入数据,实例2：创建了一个“V2”的视图，用于查询财务管
7、理专业的学生信息，包括学号、姓名、性别、出生日期、专业、籍贯。,create view v2 as select 学号,姓名,性别,出生日期,专业,籍贯 from XSQK where 专业=财务管理,4.3 使用视图 -1.插入数据,向“V2”视图中添加两条记录。 insert into v2 values(2013020216,李好,1,1996-04-22,财务管理,四川成都) insert into v2 values(2013010111,周权,1,1986-07-08,市场营销,湖南长沙) select * from XSQK select * from v2,请思考:V2视图中能
8、不能看到周权的记录，为什么?,因为周权的记录不满足视图的定义,即视图中只显示“财务管理”专业的学生信息，所以周权的信息只能在基表中看到,而不能在视图中看到。,4.3 使用视图 -1.插入数据,插入数据的注意事项 可通过视图向基表中插入数据，但插入的数据实际上存放在基表中，而不是存放在视图中。 如果视图引用了多个表，使用insert语句插入的列必须属于同一个表。 若创建视图时定义了“with check option”选项，则使用视图向基表中插入数据时，必须保证插入后的数据满足定义视图的限制条件。 说明：如果不想让不满足视图定义的数据插入到基表中，可以在定义视图时，加上“with check o
9、ption”。,4.3 使用视图 -1.插入数据,实例3：创建V3视图,添加了“with check option”属性，通过视图向基表中添加两条记录。 create view v3 As select 学号,姓名,性别,出生日期,专业,籍贯 from XSQK where 专业=财务管理 with check option,4.3 使用视图 -1.插入数据,实例3：通过视图V3向基表中添加两条记录。 insert into v3 values(2013020227, 赵伟,1, 1996-05-07, 财务管理, 湖北武汉) insert into v3 values(2013010126,
10、 刘齐,1, 1996-12-02, 市场营销, 河南洛阳) 请思考：这两条记录能否插入到基表中？能否在视图中能否显示？,“赵伟”的信息既能插入到基表中，也能在视图中看到。但“刘齐”的信息都不能，且系统会提示错误。,4.3 使用视图 -2.删除数据,语句格式： delete from 视图名 where 条件 实例4：删除“V2”视图中学号为2013020216的记录。 delete from v2 where 学号=2013020216 注意事项： 要删除的数据必须包含在视图的结果集中。 如果视图引用了多个表时，无法用delete命令删除数据。,4.4 管理视图 -1.修改数据,语法格式：
11、alter view 视图 as select 查询语句 with check option 通过SSMS修改视图,4.4 管理视图 -1.修改数据,实例5：在“v1”的视图中增加一列：出生日期。 alter view v1 as select XSQK.学号,姓名,专业,课程号,成绩,出生日期 from XSQK, XS_KC where XSQK.学号=xs_kc.学号 and 成绩=60 and 成绩80,4.4 管理视图 -1.修改数据（使用视图更新数据）,实例6：将“V3”视图中的“刘玉红”的籍贯改为“河南洛阳”。 UPDATE v3 SET 籍贯=河南洛阳 WHERE 姓名=刘玉红
12、,4.4 管理视图 -2.删除数据,语句格式 drop view 视图名 , n 实例7：删除视图“v1” drop view v1,课堂练习,练习1：创建一个名为“V_不及格学生信息”的视图，该视图包含所有有不及格课程的学生信息（学号,姓名,专业）。,CREATE VIEW V_不及格学生信息 AS SELECT DISTINCT XSQK.学号,姓名,专业名 FROM XSQK, XS_KC WHERE XSQK.学号= XS_KC.学号 AND XS_KC.成绩60 go select * from V_不及格学生信息,课堂练习,练习2：创建一个名为“V_第1学期课程”的视图，该视图包含
13、所有第1学期的课程信息（课程号,课程名,授课教师,开课学期,学分）。,CREATE VIEW V_第1学期课程 AS SELECT 课程号,课程名,授课教师,开课学期,学分 FROM KC WHERE开课学期= 1 go select * from V_第1学期课程,课堂练习,练习3：向“V_第1学期课程”视图中添加两条记录。 (010,大学语文, 李青玉,1,4) (011,经济数学, 李梦婷,2,3),select * from V_第1学期课程 insert into V_第1学期课程 values (010,大学语文, 李青玉,1,4) select * from V_第1学期课程 i
14、nsert into V_第1学期课程 values (011,经济数学, 李梦婷,2,3),思考：如果不允许插入第2学期课程信息(011,经济数学, 李梦婷,2,3)该如何操作？,课程小结,本次课重点内容小结 视图的创建、修改、删除、查询,引入:,请思考:在一个数据表中检索数据时是如何进行的? 如果采取逐行扫描的方式进行检索,可行吗? 有什么方法可提高检索效率？,对于小表来说，是可行的。但对于有成千上万行的表来说，其搜索的效率非常低。,建立索引。,第5章 索 引 教学内容,基础知识： 索引的基本概念 索引类型 索引的创建及使用,第6章 视图与索引 学习目标,认知目标： 理解索引的概念、创建索
15、引的必要性、索引的类型 （重点） 能力目标: 掌握创建、删除索引的方法（重点、难点）,5.1 索引的基本概念 1. 索引的含义,索引：是对表的一列或多个列的值进行排序的一种物理结构，它由该表的一列或多个列的值，以及指向这些列值对应记录存储位置的指针所组成。 索引如同书的目录 索引信息存放在索引页中，表中的数据存放在数据页中。,5.1 索引的基本概念 2.建立索引的优缺点,建立索引的优点 可以大大加快数据检索速度。 通过创建唯一索引，可以保证数据记录的唯一性。 在使用ORDER BY和GROUP BY子句进行检索数据时，可以显著减少查询中分组和排序的时间。 使用索引可以在检索数据的过程中使用优化隐藏器，提高系统性能。 可以加速表与表之间的连接，这一点在实现数据的参照完整性方面有特别的意义。,5.1 索引的基本概念 2.建立索引的优缺点,增加索引的不利方面 （1）创建索引和维护索引要耗费时间； （2）索引需要占物理空间； （3）当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。,5.1 索引的基本概念 3. 使用索引的准则,何时创建索引？ （1）在作为主键的列上； （2）在频繁按范围
