1、第七章 索引与视图,7.1 索引概述 7.1.1 索引的基本概念 7.1.2 索引分类 7.2 索引的操作 7.2.1 创建索引 7.2.2 查看索引 7.2.3 编辑索引 7.2.4 删除索引 7.3 视图概述 7.3.1 视图的概念 7.3.2 视图的作用 7.3.3 视图的限制,7.4 视图的操作 7.4.1 创建视图 7.4.2 修改视图 7.4.3 删除视图 7.4.4 查看和修改视图属性 7.5 视图的应用 7.5.1 通过视图检索表数据 7.5.2 通过视图添加表数据 7.5.3 通过视图修改表数据 7.5.4 通过视图删除表数据,视图的概念,视图是一种数据库对象，是从一个或者多
2、个数据表或视图中导出的虚表，视图的结构和数据是对数据表进行查询的结果。 视图的定义：SQLSelect语句 视图的使用：等同于基表，可以使用Select、Insert、Update等操作,视图的概念,视图可以是一个数据表的一部分，也可以是多个基表的联合;视图也可以由一个或多个其他视图产生。 基表中的数据发生变化，从视图中查询出的数据也随之改变；视图中的数据发生变化，基表中的数据也可能发生变化,什么是视图？,基表（base table)：独立存在的表 视图虚拟表。在SQL中只存储视图的定义，不存放视图所对应的记录,视图引例,例7-6-1 男职员的工号、姓名、出生日期、地址、电话保存为视图View
3、_nan CREATE VIEW view_nan(employee_id,employee_name,birth_date,address,telephone) AS SELECT employee_id,employee_name,birth_date,address,telephone FROM employee WHERE sex=男 例7-6-1-1查询男职员的工号、姓名、出生日期、地址、电话 SELECT FROM view_nan,视图作用,优点： 简化用户查询（如连接） 为用户定制数据（不同的用户以不同的方式看到不同或者相同的数据集） 简化用户权限的管理（授予在视图上的操作权限
4、，而没有授予用户在表上的操作权限） 导出数据（定义多表视图，利用批复制功能实现数据导出） 缺点 降低性能（视图消解） 修改限制（有些复杂视图是不能修改的）,视图限制,(1)在一个批事务中，CREATE VIEW语句不能与其他SQL语句结合使用。 (2)不能在视图上建立触发器和索引。 (3)一个视图最多只能有250列。 (4)不能基于临时表建立视图，由SELECT INTO语句可建立临时表，在CREATE VIEW语句中不能使用INTO关键字。 (5)在CREATE VIEW语句中不能使用联合操作符UNION。,视图限制,(6)在插入或修改数据时，每个INSERT语句和UPDATE语句不能影响一
5、个以上的视图基表。 (7)对视图中的计算列不允许使用INSERT语句插入数据。 (8)UPDATE语句不能够修改视图的计算列数据，也不允许它修改包含集合的函数和内置函数的视图列。 (9)在视图的Text和Image数据类型的列上不允许使用READTEXT和WRITETEXT语句。,创建视图,SQL Server管理平台 T-SQL CREATE VIEW . view_name ( column_name ,.n ) WITH ENCRYPTIONAS select_statement WITH CHECK OPTION WITH CHECK OPTION：迫使通过视图执行的所有数据修改语句必
6、须符合视图定义中设置的条件。 WITH ENCRYPTION：对视图的定义进行加密,注意,组成视图的属性列名 全部省略或全部指定 省略: 由子查询中SELECT目标列中的诸字段组成 明确指定视图的所有列名时: (1) 某个目标列是集函数或列表达式 (2) 目标列为 * (3) 多表连接时选出了几个同名列作为视图的字段 (4) 需要在视图中为某个列启用新的更合适的名字,视图存储,DBMS执行CREATE VIEW语句时只是把视图的定义存入数据字典，并不执行其中的SELECT语句。 在对视图查询时，按视图的定义从基本表中将数据查出。,创建视图示例,例7-6 在Sales数据库中创建sell_vie
7、w视图，该视图选择3个基表(employee,goods,sell_order)中的数据来显示员工销售货物情况的虚拟表。 CREATE VIEW sell_view AS SELECT employee.employee_name, employee.employee_id, sell_order.order_num, sell_order.discount, goods.goods_name, goods.unit_price , sell_order.order_date FROM employee INNER JOINsell_order ON employee.employee_id
8、= sell_order.employee_id INNER JOIN goods ON sell_order.goods_id = goods.goods_id,With check option,7-6-2 建立男职员信息的视图，并要求透过该视图进行的更新操作只涉及男职员。 CREATE VIEW view_nan(employee_id,employee_name，birth_date,address,telephone) AS SELECT employee_id,employee_name，birth_date,address,telephone FROM employee WHER
9、E sex=男 WITH CHECK OPTION,视图的视图,例7-6-3 建立1975年以后出生的男职员信息视图(包括姓名、出生日期及电话)。CREATE VIEW nan_75ASSELECT employee_name,birth_date,telephoneFROM View_nanWHERE birth_date=1975-01-01；,修改视图,1.使用SQL Server管理平台修改视图 (略) 2使用Transact-SQL语句修改视图 ALTER VIEW view_name （column,.n） WITH ENCRYPTION AS select_statement W
10、ITH CHECK OPTION view_name：被修改的视图的名字。 column_name ：在视图中包含的列名。 WITH CHECK OPTION：迫使通过视图进行数据修改的所有语句必须符合视图定义中设置的条件。,修改视图（续）,table_name：视图基表的名字。 WITH ENCRYPTION：对包含创建视图的SQL脚本进行加密。 【例7-6-4】修改视图view_nan的定义，使其从employee表中查询出性别为D001部门男员工的姓名,出生日期,电话等资料。 USE sales GO ALTER VIEW view_nan AS SELECT * FROM employ
11、ee WHERE sex = 男 and dapartment_id=D001,删除视图,删除一个视图，就是删除视图的定义及其赋予的全部权限,而原先通过视图获得的数据并没有被删除。删除视图参考表的时候，视图不会被删除，视图必须被单独删除。 方法： 使用SQL Server管理平台删除 用DROP VIEW语句删除。 1. 用DROP VIEW语句删除视图DROP VIEW view_namel,view_name2, 使用DROP VIEW语句可以一次删除多个视图。,删除视图,例7-7 删除view1视图。 USE Sales IF EXISTS (SELECT TABLE_NAME FROM
12、 INFORMATION_SCHEMA.VIEWSWHERE TABLE_NAME = view1)DROP VIEW view1 GO,查看和修改视图属性,查看视图定义信息 SQL Server管理平台 系统存储过程 查看视图的定义脚本sp_helptext view_nan使用系统存储过程重命名视图 语法格式： sp_rename objname = object_name , newname = new_name , objtype = object_type Sp_rename view_nan,view_nan_d1,通过视图查询数据,例7-8 查询例7-6所创建的视图sell_vi
13、ew中的姓名为钱达理的员工所销售的商品名称。SELECT goods_name FROM sell_view WHERE employee_name=钱达理 ORDER BY order_date,通过视图插入数据,语法格式： INSERT INTO 视图名 VALUES(列值1，列值2，列值3，列值n) 例7-9-1 在goods表中建立一个视图，利用视图插入一行数据。 CREATE VIEW goods_view AS SELECT goods_id, goods_name, classification_id, unit_price, stock_quantity,supplier_id
14、 FROM goods WHERE (classification_id = P001) GO INSERT INTO goods_view VALUES (G0008,SONY DVD,P002, 400,10,S001),插入数据注意问题,插入视图中的列值个数、数据类型应该和视图定义中的列数、基表对应列的数据类型保持一致 如果视图的定义值选取了基表的部分列，则基础表的其余列都允许为空，或有列不允许为空，但设置了默认值，可以成功的插入数据 例7-9 如果在视图定义中使用了WITH CHECK OPTION子句，则在视图上执行的数据插入语句必须符合定义视图的SELECT语句中所设定的条件。例7
15、-9-2,通过视图删除数据,尽管视图不一定包含基础表的所有列，但可以通过视图删除基础表的数据行。 语法格式： DELETE FROM 视图名 WHERE 逻辑表达式 例7-10: delete from goods_view Where goods_name=IBM R51 若通过视图要删除的数据行不包含在视图定义中，该数据行不能成功删除。例7-10-1 若删除语句的条件中指定的列是视图中未包含的列，则无法通过视图删除基表数据行例7-10-2,通过视图修改数据,例7-11 将视图goods_view中商品号为G00001的商品名改为“IBM R61”。UPDATE goods_view SET
16、 goods_name= IBM R61 WHERE goods_id = G00001； 更新视图需注意的问题 若视图定义包含了计算列,该视图是不可更新的.例7-11-2,例7-11-3 若视图定义包含了多个基表，且要更改的列属于同一个基表，则可以通过视图更新源表数据。例7-11-4 若视图定义包含了多个基表，且要更改的列分属于多个基表，则不能通过视图更新源表数据。例7-11-5,什么是视图？,基表（base table)：独立存在的表 视图虚拟表。在SQL中只存储视图的定义，不存放视图所对应的记录,Student表,SC表,COURSE表,视图,1 建立信息系学生的视图IS_student
17、 CREATE VIEW IS_StudentAS SELECT Sno, Sname, SageFROM StudentWHERE Sdept= ISwith check option,视图,建立一个反映学生出生年份的视图BT_S CREATE VIEW BT_S(Sno, Sname, Sbirth)AS SELECT Sno, Sname, YEAR(GETDATE()-SageFROM Student,视图,.将学生的学号及他的平均成绩定义为一个视图S_G. CREATE VIEW S_G (Sno, Gavg)AS SELECT Sno, AVG (Grade)FROM SCGROU
18、P BY Sno,视图,.将课程的课号及选修人数定义为一个视图C_XIU. CREATE VIEW C_XIU(Cno, Scount)AS SELECT Cno, COUNT (sno)FROM SCGROUP BY cno,第七章 索引与视图,7.1 索引概述 7.1.1 索引的基本概念 7.1.2 索引分类 7.2 索引的操作 7.2.1 创建索引 7.2.2 查看索引 7.2.3 编辑索引 7.2.4 删除索引 7.3 视图概述 7.3.1 视图的概念 7.3.2 视图的作用 7.3.3 视图的限制,7.4 视图的操作 7.4.1 创建视图 7.4.2 修改视图 7.4.3 删除视图
19、7.4.4 查看和修改视图属性 7.5 视图的应用 7.5.1 通过视图检索表数据 7.5.2 通过视图添加表数据 7.5.3 通过视图修改表数据 7.5.4 通过视图删除表数据,索引简介,索引概念 索引、目录 索引、表 索引的优点 索引的缺点 考虑建立索引的列 不考虑建立索引的列,索引的含义,章节标题页码第1章 1.1. 1 1.2 5 1.3 15第2章19 2.119 2.225 2.336 2.441 ,索引（身高）关键字物理地址 1.802016 1.772017 1.772020 1.752014 1.752019 1.712011 1.712018 ,目录,索引（姓名）关键字物理
20、地址 陈雨菲2018 李刚2017 刘军生2016 刘子扬201 吴大维201 孙霖2013 孙鹏2019 ,物理顺序和逻辑顺序 索引是一个列表，在这个列表中包含了某个表中一列或者若干列值的集合，以及这些值的记录在数据表中的存储位置的物理地址。,表与索引,“学生”表,“学生” 索引,索引的优点,加速数据检索 在使用ORDER BY和GROUP BY子句进行检索数据时，可以显著减少查询中分组和排序的时间。 查询优化器依赖于索引起作用，提高系统性能。 加速表间的连接、ORDER BY和GROUP BY 通过创建唯一索引，强制实施数据记录的唯一性。,索引缺点,创建索引要花费时间和占用存储空间。 创建
21、索引需要占用存储空间， 如创建聚簇索引需要占用的存储空间是数据库表占用空间的1.2倍。在建立索引时，数据被复制以便建立聚簇索引，索引建立后，再将旧的未加索引的表数据删除。 创建索引也需要花费时间。 建立索引加快了数据检索速度，却减慢了数据修改速度。 维护索引： 因为每当执行一次数据的插入、删除和更新操作，就要维护索引。修改的数据越多，涉及维护索引的开销也就越大。 对建立了索引的表执行修改操作要比未建立索引的表执行修改操作所花的时间要长。,哪些列需要建索引,主键 通常，检索、存取表是通过主键来进行的。因此，应该考虑在主键上建立索引。连接中频繁使用的列 用于连接的列若按顺序存放，系统可以很快地执行
22、连接。如外键，除用于实现参照完整性外，还经常用于进行表的连接。 在某一范围内频繁搜索的列和按排序顺序频繁检索的列,不考虑建立索引的列,很少或从来不在查询中引用的列 因为系统很少或从来不根据这个列的值去查找数据行。 只有两个或很少几个值的列（如性别，只有两个值“男”或“女”），以这样的列创建索引并不能得到建立索引的好处。以bit、text、image数据类型定义的列。 数据行数很少的小表一般也没有必要创建索引。 标准：当数据更新的性能比查询的性能更重要时，不应创建索引 技巧：根据需要创建或删除索引以提高性能。如：对表进行大批量更新时，可先删索引。待操作完成再重建索引,索引分类,索引类型 聚簇索引
23、 非聚簇索引 惟一索引,聚簇索引,聚簇索引的结构 聚集索引：数据表的物理顺序和索引表的顺序相同，它根据表中的一列或多列值的组合排列记录。,“学生”表,“学生” 索引,注意事项,建聚簇索引的注意事项,每张表只能有一个聚簇索引 由于聚簇索引改变表的物理顺序，所以应先建聚簇索引，后创建非聚簇索引 创建索引所需的空间来自用户数据库，而不是TEMPDB数据库 主键是聚簇索引的良好候选者（也称为主键索引）,非聚簇索引,非聚簇索引的结构 非聚簇索引：表的物理顺序与索引顺序不同，即表的数据并不是按照索引列排序的。索引是有序的，而表中的数据是无序的。,注意事项,“学生”表,“学生” 索引,建非聚簇索引的注意事项
24、,创建非聚簇索引实际上是创建了一个表的逻辑顺序的对象 索引包含指向数据页上的行的指针 一张表可创建多达249个非聚簇索引 创建索引时，缺省为非聚簇索引,惟一索引,创建惟一索引 索引按照结构可分为聚簇索引和非聚簇索引两种不同的类型。按照实现的功能分，有一类索引被称作“惟一索引”。它既可以采用聚簇索引的结构，又可以采用非聚簇索引的结构。 惟一索引的特征： 不允许两行具有相同的索引值。 可用于实施实体完整性。 在创建主键约束和唯一约束时自动创建惟一索引。 在已有数据的表上创建惟一索引时，如果在该列数据存在重复值，那么系统将返回错误信息。 在实际的编程应用中会经常使用到惟一索引。因为在一个表中，可能会
25、有很多列的列值需要保证其唯一性，如：有身份证号、工号、学号等，可在这些列上创建唯一索引。,创建索引,注意事项 当在一个表上创建PRIMARY KEY约束或UNIQUE约束时，SQL Server自动创建惟一性索引。 不能在已经创建PRIMARY KEY约束或UNIQUE约束的列上创建索引。定义PRIMARY KEY 约束或UNIQUE约束与创建标准索引相比应是首选的方法。 在一个列上创建索引之前，确定该列是否已经存在索引。 使用SQL Server管理平台创建索引 T-SQL建立索引,T-SQL 创建索引,CREATE INDEX语句的语法形式如下： CREATE UNIQUE CLUSTER
26、ED | NONCLUSTERED INDEX index_name ON table_name（column_name ASC | DESC ,.n ） UNIQUE：指定创建的索引是唯一索引。如果不使用这个关键字，创建的索引就不是唯一索引。CLUSTERED|NONCLUSTERED：指定被创建索引的类型。使用CLUSTERED创建的是聚簇索引；使用NONCLUSTERED创建的是非聚簇索引。这两个关键字中只能选其中的一个。,创建索引示例,例7-1 按employee表的employee_name列建立非聚集索引name_idx。 CREATE NONCLUSTERED INDEX nam
27、e_idx ON employee(employee_name) 例7-1-1 在sales数据库的employee表上创建一个名为e_id_index的唯一性聚簇索引，索引关键字为employee_id，降序 USE sales GO CREATE UNIQUE CLUSTERED INDEX e_id_index ON employee ( employee_id DESC ),查看索引,索引信息查看 1.使用SQL Server管理平台查看索引 2.使用系统存储过程查看索引 语法格式： sp_helpindex objname=name 其中objname=name子句为指定当前数据库中
28、的表的名称。【例7-2】在查询分析器中查看表employee的索引。 USE Sales GO EXEC sp_helpindex employee(或objname=employee) GO,编辑索引,1使用SQL Server管理平台编辑索引 2使用系统存储过程更改索引 语法格式： sp_rename OldName,NewName,object_type Oldename:需要更改对象原来的名称，包括路径 例7-3 更改employee表中索引employee_index_1名称为employee_index_id。 USE Sales GO EXEC sp_rename employe
29、e. employee_index_1 , employee_index_id,index,删除索引,使用SQL Server管理平台删除索引 使用T-SQL语句删除索引 在创建索引之后，如果该索引不再需要，可以用DROP语句将其删除。 DROP语句的语法如下： DROP INDEX table.index,.n 【例7-4】用DROP语句将表employee的索引“employee_index_2 ”删除。 USE sales IF EXISTS (SELECT name FROM sysindexesWHERE name = employee_index_2)DROP INDEX empl
30、oyee.employee_index_2,删除索引,注意： 被删除的索引要以“表名.索引名”的形式给出。 删除索引时要注意，如果索引是在CREATE TABLE语句中创建的，只能用ALTER TABLE语句删除索引。如果索引是用CREATE INDEX创建的，可用DROP INDEX删除。,本章小结,(1)索引是对数据库表中一个或多个字段的值进行排序而创建的一种分散存储结构。建立索引的主要目的是加速数据检索和连接、优化查询、强制实行惟一性等操作。主要有3种类型的索引，即惟一性索引、非聚集索引和聚集索引。 (2)在SQL Server 2005中对索引的基本操作包括创建索引、查看索引、更改索引
31、、删除索引和全文索引，可以在SQL Server管理平台或通过Transact-SQL语句实现索引操作。 (3)视图是一种数据库对象，是从一个或多个表或视图中导出的虚拟表。,本章小结,(4)视图的操作主要包括视图的创建、修改、删除和重命名等，其操作可以通过SQL Server管理平台和Transact-SQL语句来实现。 (5)通过视图可以完成某些和基础表相同的一些数据操作，如数据的检索、添加、修改和删除。,选择题,建立索引的作用之一是（ ）。A 节省存储空间 B 便于管理C 提高查询速度 D 提高查询和更新的速度 索引是对数据库表中（ ）字段的值进行排序。A 一个 B 多个 C 一个或多个
32、D 零个 Transact-SQL语言中，删除一个视图的命令是（ ）。A DELETE B DROPC CLEAR D REMOVE,选择题,下面几项中，关于视图叙述正确的是（ ）。A 视图是一张虚表，所有的视图中不含有数据B 用户不允许使用视图修改表数据C 数据库中的视图只能使用所属数据库的表，不能访问其他数据库的表D 视图既可以通过表得到，也可以通过其他视图得到,填空题,如果索引是在CREATE TABLE中创建，只能用（ ）进行删除。如果用CREATE INDEX创建，可以用（ ）删除。 （ ）是关系数据库中提供给用户以多种角度观察数据库中数据的重要机制。 数据库中只存放视图的（ ），而不存放视图对应的数据，这些数据仍存放在导出视图的基础表中。 UPDATE语句不能够修改视图的（ ）数据，也不允许它修改包含集合的函数和内置函数的视图列。 通过视图可以对基础表中的数据进行检索、添加、（ ）和（ ）。,上机实验,第13周上机时间： 周五第四大节（15:40-17:20）上机地点： 经管楼 MIS(2)（D304）,Thank you very much!,谢谢您 的光临，再见！,
