SQLServer课件第5章

索引的代价
1. 索引需要占用数据表以外的存储空间。 索引需要占用数据表以外的存储空间。 2. 数据更新时，需要对索引进行额外的维护，降低了数据的 数据更新时，需要对索引进行额外的维护， 维护速度。 维护速度。
信息技术系
5.2. 1 索引的概念
建立索引的原则
1.主键列上一定要建立索引（系统自动建立）。 主键列上一定要建立索引（系统自动建立） 主键列上一定要建立索引 2.在经常查询的字段上最好建索引，如外键。 在经常查询的字段上最好建索引， 在经常查询的字段上最好建索引 如外键。 3.按排序顺序访问的列上最好建索引。 按排序顺序访问的列上最好建索引。 按排序顺序访问的列上最好建索引 4.对于那些查询中很少使用的字段、重复值比较多的字段 对于那些查询中很少使用的字段、 对于那些查询中很少使用的字段 不要建索引。 不要建索引。 5.数据类型为 、Text、Image的字段不要建索引。 数据类型为Bit、 、 的字段不要建索引。 数据类型为 的字段不要建索引
信息技术系
5.2. 1 索引的概念
索引的分类
1．惟一索引：当惟一性是数据本身的特点时，可创建惟一 ． 惟一索引： 当惟一性是数据本身的特点时， 索引，即索引列的值不允许重复，如学生表中的学号。 索引，即索引列的值不允许重复，如学生表中的学号。 非惟一索引： 非惟一索引：列值可重复 2．聚集索引：指表中记录的物理顺序与索引顺序完全相同。 ． 聚集索引：指表中记录的物理顺序与索引顺序完全相同。 当为一个表的某列创建聚集索引时， 当为一个表的某列创建聚集索引时，表中的数据会按该列进 行重新排序，然后再存储到磁盘上。 行重新排序，然后再存储到磁盘上。由于一个表中的数据只 能按照一种顺序存储， 能按照一种顺序存储，所以在一个表中只能建立一个聚集索 引。 非聚集索引：不改变表中记录的物理顺序。 非聚集索引：不改变表中记录的物理顺序。 在检索记录时， 在检索记录时，聚集索引比非聚集索引有更快的数据访问速 在添加或更新记录时， 度。在添加或更新记录时，由于使用聚集索引时要先对记录 排序，然后再存储到表中， 排序，然后再存储到表中，所以使用聚集索引要比非聚集索 引慢。 引慢。 信息技术系
信息技术系
5.1.2
例③重新取列名
创建视图
create view view3(课程号 学生人数 课程号,学生人数 课程号 学生人数) as select 课程号,count(*) from 选修课成绩 课程号 group by 课程号
信息技术系
5.1.2
创建视图
“视图设计器” 视图设计器”
2、使用企业管理器创建视图 、 3、使用向导创建视图 、 4、视图的限制 P93 、
信息技术系
5.1.2
创建视图
1、使用SQL语句创建视图 、使用 语句创建视图 格式： 格式： CREATE VIEW 视图名 视图列名，…)] 视图名[(视图列名 视图列名， [WITH ENCRYPTION] –对包含 对包含create view语句的文本加密 语句的文本加密 AS SELECT 语句 [WITH CHECK OPTION] –对视图操作遵守定义时的 对视图操作遵守定义时的where条件 条件 语句不能包括order by（若有 注：定义视图的select语句不能包括 定义视图的 语句不能包括 （若有top n/percent则可）、 则可）、 则可）、computer by 、into等子句 等子句
第5章 视图和索引 章
5.1 视图 视图的概念 创建视图 修改、查看、 修改、查看、删除视图 使用视图对表数据操作
5.1.1 5.1.2 5.1.3 5.1.4
信息技术系
5.1.1 视图的概念
视图（ 视图（view）的概念 ）
视图是一种数据库对象， 视图是一种数据库对象，是从一个或者多个表或视 数据库对象 图中导出的虚拟表 其内容是对表进行查询的结果。 虚拟表， 图中导出的虚拟表，其内容是对表进行查询的结果。 视图被定义后便存储在数据库中，通过视图看到的 视图被定义后便存储在数据库中， 数据只是存放在基表中的数据。 数据只是存放在基表中的数据。 对视图的操作与对表的操作一样， 对视图的操作与对表的操作一样，可以对其进行查 修改（有一定的限制）， ），实际操作的都是基表中的 询、修改（有一定的限制），实际操作的都是基表中的 数据。 数据。
信息技术系
5.1.1 视图的概念
例：查询教师的姓名和所授选修课程信息 select 姓名 课程 from 教师 教师任课 课程 姓名,课程 课程.* 教师,教师任课 教师任课,课程 where 教师 工号 教师任课 工号 教师.工号 教师任课.工号 工号=教师任课 and 教师任课 课程号 课程 课程号 教师任课.课程号 课程.课程号 课程号=课程 可以建立一个视图： 可以建立一个视图： create view 教师课程 as select 姓名 课程 from 教师 教师任课 课程 姓名,课程 课程.* 教师,教师任课 教师任课,课程 where 教师 工号 教师任课 工号 教师.工号 教师任课.工号 工号=教师任课 and 教师任课 课程号 课程 课程号 教师任课.课程号 课程.课程号 课程号=课程 以后查询只要： 以后查询只要： select * from 教师课程 对视图的查询和一个普通的表相同。 对视图的查询和一个普通的表相同。如where、order by等 查询和一个普通的表相同 、 等
信息技术系
5.1.4
使用视图对表数据操作
修改记录update 修改记录
例1：create view view1 as select * from 课程 ： 正确！ 正确！ update view1 set 学分 学分 学分=学分 学分+1 例2： ： create view 教师课程 as select 姓名,课程号 from 教师 教师任课 教师,教师任课 姓名 课程号 where 教师.工号 教师任课.工号 教师 工号=教师任课 工号 工号 教师任课 update 教师课程 set 姓名 赵林 where 姓名 赵明 姓名='赵林 赵林' 姓名='赵明 赵明' update 教师课程 set 姓名 赵林 课程号 姓名='赵林 课程号='114' 赵林',课程号 where 课程号 课程号='004' 出错提示：视图 教师课程 不可更新， 教师课程' 出错提示：视图'教师课程 不可更新，因为修改会影响多个基表
信息技术系
5.1.2
创建视图
例①create view view1 as select * from 课程 where 学分 学分>2 with check option 治安管理',2) 失 此后 insert view1 values('108', '治安管理 治安管理 败! 例②create view view2 with encryption as select * from 课程 where 学分 学分>2 此后不能看到视图view2的定义 此后不能看到视图 的定义
信息技术系
5.2
索引
5.2. 1 索引的概念 5.2. 2 创建索引 5.2. 3 查看、修改和删除索引 查看、
信息技术系
5.2. 1 索引的概念
数据库中的索引是一个列表，在这个列表中包含了某个 数据库中的索引是一个列表， 表中一列或者若干列值的集合， 表中一列或者若干列值的集合，以及该列所对应的记录 在数据表中的地址。 在数据表中的地址。 编号索引表 职工表
信息技术系
5.1.1 视图的概念
视图的优点
1、集中数据，简化查询 、集中数据， 2、使用视图可以屏蔽数据的复杂性，用户不必了解 、使用视图可以屏蔽数据的复杂性， 数据库的结构， 数据库的结构，就可以方便地使用和管理数据 3、控制不同用户看到不同的数据，定制不同用户对 、控制不同用户看到不同的数据， 数据的访问权限， 数据的访问权限，保证数据安全 4、便于组织数据导出 、
信息技术系
5.1.4
使用视图对表数据操作
1、视图定义后，可以对视图进行查询 、视图定义后， 例： select * from view3 2、在一定条件下还可以通过视图修改表中数据 、 一定条件下还可以通过视图修改表中数据 增加 insert 如：insert view1 values('112','VB',3) 修改 update 学分=4 where 课程名 课程名='VB' 如：update view1 set 学分 删除 delete 如：delete view1 where 课程名='VB' 课程名 注意：由于视图本身是不能存储数据的，所以修改视图中的数 注意：由于视图本身是不能存储数据的， 据实际上是在修改视图引用的基表中的数据。 据实际上是在修改视图引用的基表中的数据。
信息技术系
5.1.4
使用视图对表数据操作
使用视图修改数据时，需要注意以下几点： 使用视图修改数据时，需要注意以下几点： 1.修改视图中的数据时，不能同时修改两个或者 修改视图中的数据时， 修改视图中的数据时 多个基表。 多个基表。 2.不能修改那些通过计算得到的字段。 不能修改那些通过计算得到的字段。 不能修改那些通过计算得到的字段 3. 如 果 在 创 建 视 图 时 指 定 了 WITH CHECK OPTION选项 ， 那么视图修改的数据必须符合 选项， 选项 定义视图的SELECT语句中所设定的条件。 语句中所设定的条件。 定义视图的 语句中所设定的条件 4.视图修改的数据必须符合基表对列的约束条件， 视图修改的数据必须符合基表对列的约束条件， 视图修改的数据必须符合基表对列的约束条件 约束等。 如CHECK约束等。 约03 姓名 李明 孙志 王伟华 张继业 黄华 李新 王华 刘明明 警衔 二级警督 二级警督 二级警司 三级警督 三级警督 三级警司 一级警督 一级警监 职工号 001 002 003 004 005 006 007 008 指针地址 1 6 8 4 2 3 7 5
信息技术系
5.1.4
使用视图对表数据操作
增加记录insert 增加记录
例1：create view view1 as ： select * from 课程 where 学分>2 学分 with check option 正确！ insert view1 values(‘108’,‘治安管理’,3) 正确！ 治安管理’ 治安管理 insert view1 values('200','VFP',2) 错！ 例2：create view view2 as ： select 工号 姓名 年工资 工资 工号,姓名 年工资=工资 姓名,年工资 工资*12 from 教师 insert view2 values('10000','章笑 章笑',12000) 错！ 章笑 的更新或插入失败， 对视图 'view2' 的更新或插入失败，因其包含有派生域 不能修改那些通过计算得到的字段
向导” “工具” “向导” 工具”
信息技术系
5.1.3
修改、查看、 修改、查看、删除视图
1、使用企业管理器 、 右击视图， 设计视图” 右击视图，“设计视图” 右击视图， 属性” 右击视图，“属性” 右击视图， 所有任务” 右击视图，“所有任务”→“显示相关性” 显示相关性” 右击视图，“删除” 右击视图， 删除” 2、使用SQL语句 、使用 语句 alter view 视图名 as select 语句 注：视图定义加密的也可以用此语句改，但不能用企 视图定义加密的也可以用此语句改， 业管理器改 drop view 视图名[，…] 视图名 ，
信息技术系
5.2. 1 索引的概念
索引的优点
1. 可以大大加快数据检索速度。 可以大大加快数据检索速度。 2. 通过创建唯一索引，可以保证数据记录的唯一性。 通过创建唯一索引，可以保证数据记录的唯一性。 3. 在使用 在使用ORDER BY和GROUP BY子句进行检索数据 和 子句进行检索数据 可以显著减少查询中排序和分组的时间。 时，可以显著减少查询中排序和分组的时间。 4. 可以加速表与表之间的连接，这一点在实现数据的参照 可以加速表与表之间的连接， 完整性方面有特别的意义。 完整性方面有特别的意义。
信息技术系 在增、 多个基表！ 在增、改、删视图时，都不能同时修改多个基表！ 删视图时，
5.1.4
使用视图对表数据操作
删除记录 delete
例1：create view view1 as select * from 课程 ： delete view1 where 课程号 课程号=‘004’ 正确！ 正确！ 例2： ： create view 教师课程 as select 姓名 课程号 from 教师 教师任课 姓名,课程号 教师,教师任课 where 教师 工号 教师任课 工号 教师.工号 教师任课.工号 工号=教师任课 delete 教师课程 where 课程号 课程号='004' 错！ 出错提示：视图'教师课程 不可更新，因为修改会影响多个基表 出错提示：视图 教师课程' 不可更新， 教师课程