




已阅读5页,还剩38页未读, 继续免费阅读
版权说明:本文档由用户提供并上传,收益归属内容提供方,若内容存在侵权,请进行举报或认领
文档简介
第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_kc group 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语句(外查询)的一部分。 相关子查询:内查询的执行依赖外查询。 嵌套子查询:(不依赖),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,引:,请思考:查询结果集能否被用户修改? 在查询窗口得到的结果是不能被修改的。 查询结果集可以用into子句来保存。例: select xsqk.学号, 姓名, 专业名,课程名, 成绩 into cj from xsqk, xs_kc, kc where xsqk.学号=xs_kc.学号 and xs_kc.课程号=kc.课程号 再思考: cj表中的数据与基表数据是否并存于数据库中? 用户对查询到的信息(cj表)进行修改对基表是否有影响? 如果用户直接对基表进行操作,会有什么后果?,是。但会造成:数据重复存储。,无影响。,后果是:增加了数据的不安全性。,视图的使用 教学内容,基础知识: 视图概述 创建视图 修改视图 删除视图 使用视图管理数据表中的数据, 视图的使用 学习目标,认知目标: 了解视图的概念、使用的目的和好处 理解视图与基表的关系 能力目标: 掌握创建、修改、删除视图的方法(重点、难点) 掌握查询视图的方法(重点),一、视图概述,视图的含义和作用 是基于某个查询结果的虚表。是用户查看和修改数据表中数据的一种方式。 每个视图都有几个被定义的列和多个数据行。 视图与基表的关系 视图中的数据列和行来源于其所引用的基表。 视图所对应的数据并不实际存储在数据库中,而是仍存储在视图所引用的基表中。 数据库中只存储视图的定义。,一、视图概述,使用视图的目的与好处 聚焦特定数据:使用户只能看到和操作与他们有关的数据,提高了数据的安全性。 简化数据操作:使用户不必写复杂的查询语句就可对数据进行操作。 定制用户数据:使不同水平的用户能以不同的方式看到不同的数据。 合并分离数据:视图可以从水平和垂直方向上分割数据,但原数据库的结构保持不变。,二、创建视图,最基本的语句格式: create view 视图 (列名表) with encryption as select查询语句 with check option 通过对象资源管理器创建视图,加密视图的定义,使用户只能查看,不能修改。,强制所有通过视图修改的数据满足select语句中指定的条件。,二、创建视图,实例1:创建一个 名为“v1”的视图,用于查询计算机网络专业男生的学号、姓名、出生日期,并将视图的列名分别改为:学生学号、男生姓名、生日。,create view v1 (学生学号,男生姓名, 生日) as select 学号, 姓名, 出生日期 from xsqk where 专业名=计算机网络 and 性别=1 go select * 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:创建一个名为“v平均成绩”的视图,用于分组汇总查询每个学生的平均成绩,将视图的列名分别改为:学生学号,个人平均分,并加密视图的定义。,create view v平均成绩(学生学号,个人平均分) with encryption as select 学号, avg (成绩) from xs_kc group by 学号,二、创建视图,实例3:创建一个名为“v单科最高分”的视图,用于查询每门课程的最高分的学生学号、姓名、课程号、成绩信息。,create view v单科最高分 as select xsqk.学号,姓名,课程号,成绩 from xsqk, xs_kc where xsqk.学号= xs_kc.学号 and 成绩= any (select max(成绩) from xs_kc group by 课程号),二、创建视图,课堂练习2:创建一个名为“v全校最高分”的视图,用于查询所有成绩中的最高分的学生学号、姓名、专业名、课程号、成绩。,create view v全校最高分 as select xsqk.学号,姓名,专业名,课程号,成绩 from xsqk, xs_kc where xsqk.学号= xs_kc.学号 and 成绩= (select max(成绩) from xs_kc),成绩= all (select 成绩 from xs_kc),三、修改视图,语法格式: alter view 视图 (列名表) with encryption 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 xsqk. 学号 = xs_kc.学号 and xs_kc.课程号= kc.课程号 with check option,四、删除视图,语句格式 drop view 视图名 ,n ,五、使用视图管理数据表中的数据,说明: 视图可以代替表完成从表中查询、插入、更新和删除数据的操作。,五、使用视图管理数据表中的数据 -1.插入数据,实例6:向“v1”视图中添加两条记录。 insert into v1 values(020106,李好,1981-04-22,计算机网络,计算机) insert into v1 values(020107,周权,1981-07-08,信息安全,计算机) select * from xsqk select * from v1,请思考:v1视图中为什么不能看到周权的记录?,因为周权的记录不满足视图的定义,即视图中只显示“计算机网络”专业的学生信息,所以周权的信息只能在基表中看到,而不能在视图中看到。,五、使用视图管理数据表中的数据 -1.插入数据,插入数据的注意事项 可通过视图向基表中插入数据,但插入的数据实际上存放在基表中,而不是存放在视图中。 如果视图引用了多个表,使用insert语句插入的列必须属于同一个表。 若创建视图时定义了“with check option”选项,则使用视图向基表中插入数据时,必须保证插入后的数据满足定义视图的限制条件。 说明:如果不想让不满足视图定义的数据插入到基表中,可以在定义视图时,加上“with check option”。,五、使用视图管理数据表中的数据 -1.插入数据,实例7:假设“v1”视图已添加了“with check option”属性,通过视图向基表中添加两条记录。 insert into v1 values(020108,赵伟,1980-05-07,计算机网络,计算机) insert into v1 values(020109,刘齐,1981-12-02,信息安全,计算机) 请思考:这两条记录能否插入到基表中?能否在视图中能否显示?,“赵伟”的信息既能插入到基表中,也能在视图中看到。但“刘齐”的信息都不能,且系统会提示错误。,五、使用视图管理数据表中的数据 -2.删除数据,语句格式: delete from 视图名 where 条件 实例8:删除“v1”视图中学号为020108的记录。 delete from v1 where 学号=020108 注意事项: 要删除的数据必须包含在视图的结果集中。 如果视图引用了多个表时,无法用delete命令删除数据。,引:,请思考:在一个数据表中检索数据时是如何进行的? 如果采取逐行扫描的方式进行检索,可行吗? 有什么方法可提高检索效率?,对于小表来说,是可行的。但对于有成千上万行的表来说,其搜索的效率非常低。,建立索引。,索引 学习目标,认知目标: 掌握索引的概念 理解创建索引的必要性 掌握几种索引类型的特点和它们适合的场合 能力目标: 掌握用命令方式创建索引的方法(重点、难点) 掌握查看、删除索引的方法(重点),一、索引的基本概念 1. 索引的含义,索引:是对表或视图中的一列或多个列的值进行排序的一种物理结构,它由该表或视图中的一列或若干列值,以及这些列值的记录在数据表中存储位置的物理地址所组成。 索引如同书的目录 索引信息存放在索引页中,表中的数据存放在数据页中。,一、索引的基本概念 2.建立索引的优缺点,建立索引的优点 建立了索引的列作为查询条件时,数据的检索速度能大大地提高。 增加索引的不利方面 创建索引也要花费时间和占用物理空间。 虽然索引加快了检索速度,但减慢了数据修改的速度(因为每执行一次数据修改,就需要对索引进行维护)。,一、索引的基本概念 3. 使用索引的准则,何时创建索引? 在主键上 在用于连接的列(外键)上 在经常用作查询条件的列上 在经常要排序的列上,一、索引的基本概念 3. 使用索引的准则,何时可不考虑建立索引? 很少或从来不作为查询条件的列 在小表中的任何列 属于text、image、长度较大的char、varchar、binary等数据类型的列 当修改的性能需求远大于查询的性能需求时,不要创建索引,一、索引的基本概念 4. 索引的类型,按数据的物理存放顺序: 聚集索引:会改变表记录的物理存储顺序,使之与索引列的顺序完全相同(适用于范围搜索)。 非聚集索引:不改变表记录的存放顺序(适用于直接匹配单个条件的查询)。 按索引列取值的唯一性: 唯一索引:要求被索引列不能有重复值,也不能有两个null。 非唯一索引:不存在这一限制。 按索引列的个数:单索引、复合索引,二、创建索引 1. 创建索引的方法,系统自动建立索引 人工创建索引 t-sql命令方式(掌握) 对象资源管理器方式,二、创建索引 2. 系统自动建立索引,添加主键约束时 系统会自动在表中生成一个聚集惟一索引。 在xsqk表的“学号”列上已创建了聚集惟一索引(名为pk_xsqk) 在xs_kc表的“学号和课程号”两列上已创建了聚集惟一索引 在kc表中的“课程号”列上已创建了聚集惟一索引 添加惟一性约束时 系统会自动在表中生成一个非聚集惟一索引。,二、创建索引 2. 系统自动建立索引,实例1:创建一个名为test的表,其结构定义如下。 use xscj create 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个聚集索引。 由于系统已自动在主键上创建了聚集索引,所以用户不能再创建,除非先删除已有的。 一个表中可以创建若干个非聚集索引。,默认值,二、创建索引 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”,请重新创建这两列的组合索引,使学号降序排列,使索引页填满60%后就换新页进行填充。,create unique clustered index pk_xs_kc on xs_kc(学号 desc ,课程号) with pad_index, fillfactor=60, drop_existing,中间级索引页也按“填充因子”指定的填充度填充,叶级索引页按60%进行填充,二、创建索引 3. 手工建立索引(1)表索引,课堂练习:已知xsqk表中的“学号”列已设置为主键,请在“姓名”和“所在系”两列
温馨提示
- 1. 本站所有资源如无特殊说明,都需要本地电脑安装OFFICE2007和PDF阅读器。图纸软件为CAD,CAXA,PROE,UG,SolidWorks等.压缩文件请下载最新的WinRAR软件解压。
- 2. 本站的文档不包含任何第三方提供的附件图纸等,如果需要附件,请联系上传者。文件的所有权益归上传用户所有。
- 3. 本站RAR压缩包中若带图纸,网页内容里面会有图纸预览,若没有图纸预览就没有图纸。
- 4. 未经权益所有人同意不得将文件中的内容挪作商业或盈利用途。
- 5. 人人文库网仅提供信息存储空间,仅对用户上传内容的表现方式做保护处理,对用户上传分享的文档内容本身不做任何修改或编辑,并不能对任何下载内容负责。
- 6. 下载文件中如有侵权或不适当内容,请与我们联系,我们立即纠正。
- 7. 本站不保证下载资源的准确性、安全性和完整性, 同时也不承担用户因使用这些下载资源对自己和他人造成任何形式的伤害或损失。
最新文档
- YC/T 593-2023打叶复烤加工服务能力评价办法
- LY/T 3389-2024植物新品种特异性、一致性、稳定性测试指南蚊母树属
- 2025年中考语文二模试卷-1
- 福建省厦门市一中2020届高三最后一模考试试题(语文解析版)
- (广东高考卷 自主命题6科)2022年广东省普通高中学业水平选择性考试高考物化生+政史地真题试卷及答案
- 白酒的生产工艺以大曲酒为例77课件
- 《风景园林招投标与概预算》试题A附参考答案详解(预热题)
- 2025年黑龙江省五常市辅警招聘考试试题题库含答案详解(研优卷)
- 2024-2025学年度下学期七年级历史期末复习+期末考试押题课件(一)-2024-2025学年度下学期七年级历史期末复习
- ARDS患者的液体管理策略2025
- 2025年贵安发展集团有限公司招聘笔试参考题库含答案解析
- 二年级班主任经验交流精彩演讲稿
- 2025年度招商引资产业园区运营管理合作协议范文3篇
- 《犬猫洁牙手术流程规范》
- 2024版肺栓塞幻灯课件
- 2025中考数学复习专题:八类最值问题汇-总(瓜豆隐圆胡不归阿氏圆将军饮马逆等线费马点构造二次函数求最值)(原卷版)
- 农村煤改电工程施工设计方案
- 2025年1月八省联考新高考语文试卷试题真题(精校打印)
- 2025年郑州铁路局招聘笔试参考题库含答案解析
- 公司驾驶员安全培训内容
- 安全生产应急培训课程
评论
0/150
提交评论