数据库技术.

Presentation on theme: "数据库技术."— Presentation transcript:

1 数据库技术

2 第5章 索引与视图 索引 视图 视图的创建与使用

3 ？ 问题提出 什么是索引？为什么需要索引？ 索引与排序有什么区别？ 什么是视图？为什么要建立视图？

4 5.1 索引 5.1.1 索引的概念 索引的分类 索引的管理

5 5.1.1 索引的概念 索引 是对数据库表中一个或多个字段的值进行排序而创建的一种分散存储结构（即通过指针来建立元素间的联系和存取路径）。
索引也是对数据表中一个或多个关键字段的值与表中的行之间的对照表。 院系代码

6 5.1.1 索引的概念 建立索引的目的： （1）可以大大加快数据检索速度； （2）可以加速排序、分组、连接等操作
（3）实现表与表之间的参照完整性（强制实施唯一性） （4）通过创建唯一索引，可以保证数据记录的唯一性 例如，查询Sales数据库employee表中编号为“E002”的员工的信息，可以执行如下SQL语句： SELECT * FROM employee WHERE employee_id='E002' 若在employee_id列上没建索引，按顺序一行一行地查询； 若在该列上建了索引，先搜索该索引，找到要求的值(E002)，再按照索引中的信息位置，确定表中的行。

7 5.1.2 索引的分类 索引类型： 聚集索引、非聚集索引、唯一索引、索引视图、全文索引、包含性列索引、空间索引、筛选索引、XML（即可扩展标记语言）索引 按存储结构分 聚集索引和非聚集索引 按数据的唯一性分 唯一索引和非唯一索引 按键列的个数分 单列索引和多列索引 XML数据查询的基本特征就是根据模式特征（正则路径表达式形式描述的结构关系）的输入提取符合该模式的数据，所以，XML索引的主要内容就是设计适用于模式匹配的技术。 空间索引： 是一种扩展索引，允许对空间列编制索引。空间列是包含空间数据类型（如geometry或geography）数据的表列。

8 5.1.2 索引的分类 按索引的存储结构分为 1. 聚集索引 将数据行的键值在表内排序并存储对应的数据记录，使得数据表物理顺序与索引顺序一致（即通过物理上的顺序进行排列而建立索引。 一个表只能建立一个聚集索引，该索引可以包含多个列（组合索引），一般为主键建立此类索引。 2.非聚集索引 非聚集索引完全独立于数据行的结构。数据存储在一个地方，索引存储在一个地方。非聚集索引中的数据排列顺序并不是表中数据的排列顺序。 最多能创建249个；创建非聚集索引前要先创建聚集索引 在计算机科学中，B树（B-tree）是一种树状数据结构，它能够存储数据、对其进行排序并允许以O(log n)的时间复杂度运行进行查找、顺序读取、插入和删除的数据结构。B树，概括来说是一个节点可以拥有多于2个子节点的二叉查找树。与自平衡二叉查找树不同，B-树为系统最优化大块数据的读和写操作。B-tree算法减少定位记录时所经历的中间过程，从而加快存取速度。普遍运用在数据库和文件系统。

9 5.1.2 索引的分类 聚集索引与非聚集索引的比较 索引类型 存取速度 索引的数量 所需空间 聚集索引 快 一表一个 少 慢 一表可以多个

10 5.1.2 索引的分类 SQL Sever中数据的访问方法： 表扫描法 使用索引
当访问未建索引的表内数据时，从表的起始处逐行查找，直到符合条件为止。 使用索引 当使用索引访问建有索引的表内数据时，系统会通过遍历索引树结构来查找行的存储位置，效率非常高。

11 5.1.2 索引的分类 唯一索引 确保在索引键上不存在相同的值。 索引也可以不是唯一的，非唯一索引的多行可以共享同一键值。
聚集索引和非聚集索引都可以是唯一的。 创建主键（PRIMARY KEY）或唯一性（UNIQUE）约束时，数据库引擎会自动为指定的列创建唯一索引。 UNIQUE ，尤妮克

12 5.1.3 索引的管理 索引的管理包括 创建索引的方法 1. 系统自动创建索引 创建索引、查看索引、修改索引、删除索引。
在创建UNIQUE约束或PRIMARY KEY约束时，系统自动为这些列创建聚集索引。

13 5.1.3 索引的管理 2.用户创建索引 （1）使用图形化界面向导创建索引

14 5.1.3 索引的管理 （2）使用“表设计器”创建索引 “索引/键”窗口

15 5.1.3 索引的管理 索引的查看 索引的修改 索引的删除 在SQL Server的对象资源管理器可以直观地查看索引信息
可通过“对象资源管理器”实现 注意： 不能通过此方法修改作为PRIMARY KEY或UNIQUE约束的结果而创建的索引，必须修改约束。 索引的删除 索引会减慢插入（INSERT）、更新（UPDATE）和删除（DELETE）语句的执行速度。如果发现索引阻碍整体性能或不再需要索引，则可将其删除。

16 5.2 视图 5.2.1 视图的概念 视图的创建 视图的查询 视图的修改 视图的删除

17 为什么要提供视图？ 什么是视图？ 5.2.1 视图的概念
索引是为了提升数据查询性能，视图一般用于给用户提供一个统一的接口而屏蔽掉无用或需要保密的信息。 视图 是一个虚拟表，并不包含任何的物理数据 视图中的数据不物理地存储在数据库内。 SELECT 语句的结果集构成了视图的内容。 为什么要提供视图？ 什么是视图？

18 视图的优点有什么？ 5.2.1 视图的概念 视图的优点 视图有系统视图和用户创建的视图 系统视图（用于返回系统相关信息的视图）
（1）为用户集中数据，简化用户的数据查询和处理。 （2）屏蔽数据库的复杂性。 （3）简化用户权限的管理。 （4）便于数据共享。 （5）可以重新组织数据以便输出到其他应用程序中。 视图有系统视图和用户创建的视图 系统视图（用于返回系统相关信息的视图） 目录视图、兼容性视图、动态管理视图、信息架构视图、复制视图 视图的优点有什么？

19 5.2.2 视图的创建 用户创建的视图 使用图形工具创建视图 在SSMS中创建视图的方法主要在视图设计器中完成。
在显示的表列表中选择需要添加的表名，单击“添加”按钮。

20 5.2.2 视图的创建 使用图形工具创建视图 新建视图窗口

21 5.2.2 视图的创建 使用CREATE VIEW语句创建视图 语法格式： 查询语句
CREATE VIEW [schema_name.]view_name AS select_statement [WITH CHECK OPTION] 查询语句 指出在视图上所进行的修改都要符合查询语句所指定的限制条件，这样可以确保数据修改后仍可通过视图看到修改的数据。 schema英[ˈski:mə]美[ˈskimə] n.概要，计划，图表;

22 5.2.2 视图的创建 查询语句 用来创建视图的SELECT语句。对SELECT语句有以下的限制：
不能使用COMPUTE或COMPUTE BY子句。 不能使用ORDER BY子句。 不能使用INTO子句。 不能在临时表或表变量上创建视图。

23 5.2.2 视图的创建 操作方法 在SSMS窗口右键单击“新建查询”命令， 在弹出的快捷菜单中选中“视图设计器”命令，打开“视图设计器” ，
然后输入相应命令进行操作。

24 5.2.2 视图的创建 创建St_view视图的语句： 查询语句 创建基于单表的视图
例5.1：在Student数据库中创建St_view视图，该视图选择学生信息表St_Info中的所有女学生。 创建St_view视图的语句： CREATE VIEW St_view AS SELECT * FROM St_Info WHERE St_Sex='女' 查询语句 创建基于单表的视图

25 5.2.2 视图的创建 例5.2：创建v_Stu1视图，包括“法学1601”班各学生的学号、姓名、选修的课程号及成绩。要保证对该视图的修改都符合“Cl_Name为法学1601”这一条件 。 创建v_Stu1视图的语句： CREATE VIEW v_Stu1 AS SELECT St_Info.St_ID, St_Name, S_C_Info. C_No, Score FROM St_Info, S_C_Info WHERE St_Info.Cl_Name = '法学1601' AND St_Info.St_ID = S_C_Info.St_ID WITH CHECK OPTION 对v_Stu1视图的更新操作： 修改操作：自动加上Cl_Name = '法学0501'的条件 删除操作：自动加上Cl_Name = '法学0501'的条件 插入操作：自动检查Cl_Name属性值是否为 '法学0501' 如果不是，则拒绝该插入操作 如果没有提供Sdept属性值，则自动定义Cl_Name为'法学0501' 创建基于多表的视图

26 5.2.2 视图的创建 创建v_stu_avg视图的语句：
例5.3：创建学生的平均成绩视图v_stu_avg，包括St_ID（在视图中列名为学号）和平均成绩。 创建v_stu_avg视图的语句： CREATE VIEW v_stu_avg AS SELECT St_ID AS 学号, AVG(Score) AS 平均成绩 FROM S_C_Info GROUP BY St_ID 创建分组视图

27 5.2.2 视图的创建 例5.4：在视图文件 v_Stu1中创建选修了课程且成绩在85分以上的学生的视图。
CREATE VIEW stu_sc_Score AS SELECT St_ID, St_Name, Score FROM v_stu1 WHERE Score>=85 基于视图创建的视图

28 5.2.2 视图的创建 例5.5：创建一个反映学生出生年份的视图; CREATE VIEW stu_year(St_id,St_name, birthdate) AS SELECT St_id, St_name, 出生年份=year(birthdate) FROM St_Info 有表达式的视图

29 5.2.2 视图的创建 【例5-5】在数据库School中，建视图V_StudentGrade，查询学生成绩情况。
2）在添加表对话框中选择与视图有关的表、视图或函数（通过Ctrl或Shift键可选择多项），然后单击【添加】按钮；或者直接双击要添加的表等，即可将其添加到视图的查询中。这里选择表：Student、Course和Grade。单击“关闭”按钮，进入视图设计窗口。

30 5.2.2 视图的创建 3）选择的表添加到窗口的上格，单击每个表字段前的复选框，可将该字段添加到视图中。也可在所示窗口的第二个子窗口选择视图字段，并可指定列的别名、排序方式和规则等。在选择过程中，第三个子窗口中的SELECT语句也会随之自动改变。也可直接在该子窗口中输入SELECT语句。这里选择StudentName、CourseName和Grade三个字段。 4）单击工具栏上的　　按钮执行，结果显示窗口的第三个子窗口。 5）单击工具栏上的　　按钮，在弹出的另存为对话框中为视图命名，本例输入“V_StudentGrade”，单击【确定】按钮保存视图，从而完成视图创建。

31 5.2.2 视图的创建 【例5-7】在数据库School中，建立视图V_StuAvgGradExcel, 选拔优培生（平均成绩大于等于90的学生），并要求按平均成绩从高到低排列。 1) 在新建视图窗口中“视图”标签中选择视图V_StudentGrade 2) 在视图设计窗口中，选择“姓名”和“成绩”字段 3) 在第二个窗口中右击“姓名”，在右键菜单中“添加分组依据”；右击“成绩”，在“分组依据”中选择“Avg”在“筛选器”列中输入“>=90”，“排序类型”选“降序” 4) 运行查看视图结果，保存该视图为V_StuAvgGradExcel 。

32 5.2.3 视图的查询 视图创建后，就可以像查询基本表那样对视图进行查询。
例5.8：在数据库School中，从视图V_StudentGrade查询张三的成绩。 SELECT * FROM V_StudentGrade WHERE 姓名='张三'

33 5.2.4 视图的修改 在视图使用过程中，可能经常会发生基表改变，视图无法正常工作的情况，则需修改视图的定义。 1.使用图形工具修改视图

34 5.2.4 视图的修改 2.使用ALTER VIEW语句修改视图 语法格式：
ALTER VIEW [schema_name.]view_name AS select_statement [WITH CHECK OPTION]

35 5.2.4 视图的修改 例5.9：修改例5.1中创建的St_view视图。将视图中选择学生信息表St_Info中的所有女学生修改为选择所有男学生。 修改St_view视图的语句： ALTER VIEW St_view AS SELECT * FROM St_Info WHERE St_Sex='男'

36 5.2.5 视图的删除 当不再需要某个存在的视图时，可以删除它。以释放存储空间。删除视图后，表和视图所基于的数据并不受影响。 语法格式：
1．使用图形工具删除视图 2. 使用DROP VIEW语句删除视图 语法格式： DROP VIEW [schema_name.]view_name [ ,...n ] 例5.10：删除St_view视图。 语句为： DROP VIEW st_view

37 本章小结 （1）索引 索引是一个单独的、物理的数据库结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。 根据索引的存储结构不同，将其分为聚集索引和非聚集索引两类。 （2)视图 视图是一个虚拟表，并不包含任何的物理数据，即视图所对应的数据不进行实际存储，数据库中只存放视图的定义，这些数据仍存放在定义视图的基本表中。