05-第5章　索引与视图

null 索引与视图 索引与视图第五章索引与视图索引与视图第1讲 索引学习要点学习要点 索引 索引概述 创建索引 查看和删除索引 视图 视图概述 创建、修改、删除、使用视图索引索引索引概述 索引是一个单独的、物理的数据库结构 该结构中包括表中的一列或若干列的值以及相应的指向表中物理标识这些值的数据页的逻辑指针的集合 索引依赖于数据库的表，作为表的一个组成部分 一旦创建后，由数据库系统自身进行维护 存放于索引页面中 个表的存储是由两部分组成的，一部分用来存放表 的数据页面，另一部分存放索引页面 分类学习要点索引概述索引概述分类 按存储结构的不同分为两类 聚集索引 （Clustered Index） 非聚集索引（Nonclustered Index） 索引概述索引分类索引分类聚集索引（Clustered Index） 聚集索引与数据混为一体 对表的物理数据页中的数据按列进行排序，然后再重新存储到磁盘上 表的所有数据完全重新排列 一个表只能有一个聚集索引 【例5-1】 分类索引分类索引分类【例5-1】 在表information中查询s_no为“20021003016”的行（列s_no上已建立聚集索引） 返回索引分类索引分类非聚集索引（Nonclustered Index） 本索引与表的数据完全分离 无需将物理数据页中的数据按列排序 ，而是存储索引行 故使用更多存储空间(与聚集索引相比) 一个表最多可以建249 个非聚集索引 【例5-2】 分类索引分类索引分类【例5-2】 在表information中查询s_no为“20021003012”的行（列s_no上已建立非聚集索引） 返回索引索引创建索引 使用SSMS创建 使用T-SQL创建 学习要点创建索引创建索引使用SSMS创建 Step1: 选择数据表(需创建索引)→“索引”→“新建索引”step2创建索引创建索引使用SSMS创建 Step2: 创建索引创建索引创建索引使用T-SQL创建 基本语句格式： 示例CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED] INDEX 索引名 ON {表 | 视图 } （列 [ ASC | DESC ] [,...n]） UNIQUECLUSTERED NONCLUSTEREDASC | DESC创建唯一索引创建聚集索引创建非聚集索引创建排序方式，默认为升序(ASC)创建索引创建索引创建索引示例 【例5-3】 在Publisher表的p_ID列上创建聚集索引 【例5-4】 在BookType表的bt_Name列上创建惟一的非聚集索引 示例CREATE CLUSTERED INDEX idx_PubName ON Publisher（p_ID）CREATE UNIQUE NONCLUSTERED INDEX idx_TypeName ON BookType (bt_Name)创建索引创建索引示例 【例5-5】 在BorrowReturn表的s_ID列和r_ID列上创建复合非聚集索引 使用T-SQL创建索引CREATE NONCLUSTERED INDEX idx_SID_RID ON BorrowReturn（s_ID，r_ID）索引索引查看和删除索引 使用SSMS 使用T-SQL 学习要点查看和删除索引查看和删除索引使用SSMS 查看和删除索引查看和删除索引查看和删除索引使用T-SQL 查看索引 删除索引 查看和删除索引使用使用查看索引 基本语句格式： 【例5-6】查看Publisher 表的索引 返回结果使用T-SQLT-SQLsp_helpindex [@objname =] 'name' sp_helpindex Publisher返回所有索引信息使用使用删除索引 基本语句格式： 【例5-7】删除 Publisher 表的 idx_PublisherName 索引 使用T-SQLT-SQLDROP INDEX '表名.索引名' [,...n]删除一个或多个当前数据库中的索引 DROP INDEX idx_PublisherName ON Publisher此命令无法删除由CREATE TABLE 或ALTER TABLE 命令创建的PRIMARY KEY 或UNIQUE 约束索引 注意：视图视图视图概述 视图是由一个或多个数据表(基本表)或视图导出的虚拟表。 通过视图看到的数据只是存放在基本表中的数据 对视图的操作与对表的操作一样 可以对其进行查询、修改（有一定的限制）和删除 对视图的数据进行操作时，系统根据视图的定义去操作与视图相关联的基本表。 视图是关系数据库系统提供给用户以多种角度观察数据库中数据的重要机制。 学习要点视图视图创建 使用SSMS 使用T-SQL 学习要点创建视图创建视图使用SSMS Step1: 选定需要创建视图的数据库→ “视图” →右击→快捷菜单中选择“新建视图” step2创建视图创建视图使用SSMS Step2: 选择需要的表或视图 →“添加” step3创建视图创建视图使用SSMS Step3: 选择需要的列并设置相关查询选项step3自动生成的T-SQL语句相关的表和连接关系选择查看的列和筛选条件创建视图创建视图使用SSMS Step4: 单击“执行”按钮 查看结果集 step3创建视图创建视图使用SSMS Step5: 单击“保存”按钮 → 输入视图的名称 →确定→保存视图定义创建创建视图创建视图使用T-SQL 基本语句： 语句说明： 列名如未在Create view后指明，则由查询语句中 列字段组成(必须指明列名的三种情况) WITH CHECK OPTION表示对视图进行UPDATE,INSERT和DELETE操作时要保证操作后的数据都符合查询语句中的限制条件。创建视图、示例CREATE VIEW 视图名 [ ( 列名 [ ,...n ] ) ] AS 查询语句 [ WITH CHECK OPTION ] 可以是任意复杂的SELECT语句 null必须明确指明组成视图的所有列名 其中某个目标列不是单纯的列名，而是列表达式或聚合函数； 多表连接时，选出了几个同名的列作为视图的字段； 需要在视图中为某个列启用新的名字 返回创建视图创建视图使用T-SQL 注意事项： 定义视图的用户必须对所参照的表或视图有查询权限，即可执行SELECT语句。 不能使用COMPUTE或COMPUTE BY子句。 除非另外还指定了 TOP ，否则，ORDER BY 子句在视图中无效。 不能使用INTO子句。 不能在临时表上创建视图。创建视图、示例创建视图创建视图示例： 【例5-8】创建一个视图，显示机械工业出版社的图 书 关于书的成语关于读书的排比句社区图书漂流公约怎么写关于读书的小报汉书pdf 信息。 nextCREATE VIEW vw_Book_jxgy AS SELECT b_ID 图书编号, b_Name 图书名称, b_Author 作者, bt_ID 类别号, b_Price 价格, b_Quantity 数量 FROM BookInfo JOIN Publisher ON BookInfo.p_ID=Publisher.p_ID WHERE p_Name = ‘机械工业出版'创建视图创建视图示例： 【例5-9】创建一个视图，显示机械工业出版社的图书信息，而且在进行视图修改、插入和删除时，仍然保证该视图中只有机械工业出版社的图书信息。 CREATE VIEW vw_Book_jxgy AS SELECT b_ID 图书编号, b_Name 图书名称, b_Author 作者, bt_ID 类别号, b_Price 价格, b_Quantity 数量 FROM BookInfo JOIN Publisher ON BookInfo.p_ID=Publisher.p_ID WHERE p_Name = ‘机械工业出版' WITH CHECK OPTION使用T-SQL视图视图修改 使用SSMS 使用T-SQL 学习要点修改视图修改视图修改视图使用SSMS 打开视图的所在数据库→ “视图”→ 选择要修改的视图→单击右键 →“修改” 修改视图修改视图修改视图定义 打开视图的所在数据库→ “视图”→ 选择要修改的视图→单击右键 →“修改” step2修改视图修改视图使用T-SQL 基本语句 示例 【例5-23】 修改视图ALTER VIEW 视图名 [ ( 列名 [ ,...n ] ) ] AS 查询语句 [ WITH CHECK OPTION ] 修改视图修改视图【例5-10】 修改【示例5-8】定义的视图vw_Book_jxgy，将显示的“类别号”修改为“类别名”。ALTER VIEW vw_Book_jxgy AS SELECT b_ID 图书编号, b_Name 图书名称, b_Author 作者, bt_ID 类别名, b_Price 价格, b_Quantity 数量 FROM BookInfo JOIN Publisher ON BookInfo.p_ID=Publisher.p_ID JOIN BookType ON BookInfo.bt_ID=BookType.bt_ID WHERE p_Name = ‘机械工业出版' 使用T-SQL视图视图删除 使用SSMS 使用T-SQL 学习要点删除视图删除视图使用SSMS step2删除视图删除视图使用T-SQL 基本语句 示例 【例5-11】删除视图vw_Book_p001 DROP VIEW <视图名> DROP VIEW vw_Book_p001删除视图视图视图使用 查询视图数据 修改视图数据 学习要点使用视图使用视图查询视图数据 视图与表具有相似的结构 当定义视图以后，用户就可以像对基本表进行查询一样对视图进行查询。 示例： 【例5-12】：查询机械工业出版社的图书价格在20元以上的图书信息。 使用视图SELECT * FROM vw_Book_jxgy WHERE 价格>20使用视图使用视图修改视图数据 实质 对视图所引用的表(也称基表)执行数据的插入和更新 相关限制 在一个语句中，一次不能修改一个以上的视图基表 对视图中所有列的修改必须遵守视图基表中所定义 的各种数据约束条件 不允许对视图中的计算列进行修改，也不允许对 视图定义中包含有统计函数或GROUP BY子句的 视图进行插入或修改操作 使用视图、示例使用视图使用视图示例 【例5-13】 【例5-14】 【例5-15】 修改视图数据使用视图使用视图【例5-13】 创建一个基于BookType表，使用汉字列名的图书类别视图 vw_BookType，然后通过该视图实现向表BookType中增加新的图书类别。 返回CREATE VIEW vw_BookType AS SELECT bt_ID 类别编号, bt_Name 类别名称, bt_Description 描述信息 FROM BookType INSERT INTO vw_BookType(类别编号,类别名称,描述信息) VALUES('99','视图测试','这是通过测试添加的信息') 若视图所依赖的基本表有多个,不能向该视图进行插入数据操作,因为这将会影响多个基本表.使用视图使用视图【例5-14】 通过视图vw_BookType修改表BookType中 类别编号为‘99’的类别名称修改为“视图” UPDATE vw_BookType SET 类别名称='视图' WHERE 类别编号='99' 返回 若视图所依赖的基本表有多个,则修改该视图一次只能变动一个基本表的数据。使用视图使用视图【例5-15】 通过视图vw_BookType删除表BookType中 类别编号为‘99’的类别 返回DELETE FROM vw_BookType WHERE 类别编号='99' 若视图所依赖的基本表有多个,不能向该视图进行删除数据操作.视图视图优点： 为用户集中数据，简化用户的数据查询和处理。 为不同用户提供不同的数据视角，便于数据共享。 简化用户权限的管理，增加了数据安全性。 在一定程度上增强了数据的逻辑独立性。