数据查询是数据操作中用的最多的操作,需要对元组按照查询条件进行逐条筛选,当涉及多表查询时十分耗时,而视图和索引可以提高查询数据的效率.
游标可以看作是一个表中的记录指针.
11.1 视图
数据库中的视图是一个虚拟表.同真实的表一样,视图包含一系列带有名称的行和列的数据.
11.1.1视图的概念
视图是图个虚拟表,是从数据库中一个或多个表中到处来的表.当用户对通过视图看到的数据进行修改时,相应的基本表的数据也要发生变化,同时若基本表的数据发生变化,则变化也可以自动反映在视图上,
11.1.2 视图的作用
1. 视图能简化用户的操作
2.视图时用户从多种角度看待同一个数据
3.提供了一定程度的逻辑独立性
4.视图能够对机密数据提供安全保护
5.改进性能
11.1.3使用t-sql语句创建视图
(1)在一个表中创建视图
例: 在图书信息表上创建一个名为View_图书的视图
create view View_图书
as
select 图书名称,作者,出版社
from 图书信息表 where 类别编号 = '1'注意: dbms执行create view View_图书只是保存视图的定义,并不执行其中的select语句,只有对视图执行查询时,才按视图的定义从相应的基本表中查询数据
(2)多个表中创建视图
这样定义的视图一般只能用于查询不能用于修改
例:在图书信息表和图书类别信息表上创建名为View_图书类别的视图,获得图书编号,图书名称和类别名称
create view View_图书类别
as
select 图书编号,图书名称,类别名称
from 图书信息表 join 图书类别信息表
on 图书信息表.类别编号 = 图书类别信息表.类别编号(3)在已有视图上定义新视图
例:利用上例的视图,建立图书类型名为'计算机类图书'的视图,列出图书编号和图书名称.
create view View_计算机类图书 (图书编号,图书名称)
as
select 图书编号,图书名称
from View_图书类别
where 类别名称 = '计算机类图书'(4)定义带表达式的视图
例:定义一个查询读者的姓名,出生年份的视图.
create view 读者_出生年份(读者姓名,出生年份)
select 读者姓名,2019-年龄
from 读者信息表(5)含分组统计信息的视图
例:定义一个视图,统计每种类型的图书数量
create view View_数量 (类别编号 , 数量)
as
select 类别编号,count(图书编号)
from 图书信息表
group by 类别编号
11.1.4修改视图
使用sp_rename修改视图的名称
--将View_班级信息的视图修改为View_班级信息2
exec sp_rename 'View_班级信息','View_班级信息2'修改视图列名称
--将View_班级信息2的人数改为总人数
exec sp_rename 'View_班级信息.人数','总人数'使用alter语句修改视图的数据或结构
--将View_图书中的三列改为两列 图书名称 作者
alter view View_图书
as
select 图书名称,作者
from 图书信息表 where 类别编号 = '1'11.1.5 管理视图
向View_学生信息中插入一行数据('7' , '13' ,'小琳' , '女','beijing')
insert into View_学生信息
values('7','13','小琳','女','beijing')11.1.6 更新视图中View_班级信息中的人数80的班级编号改为28
update View_班级信息2
set 班级编号=28
where 人数 = 80
alter与update的区别:
alter既能修改结构也能修改数据
update只能修改数据
视图中的数据做了更改,原表的数据也相应随之改变
11.1.7通过视图删除基本表中的数据
delete from 视图_图书
where 图书名称 = 'c语言程序设计'11.1.8 删除视图
drop view View_图书信息11.1.7使用系统存储过程查看视图定义信息
-- 产假按视图定义的信息
exec sp_help view_name
--查看视图的定义规则
exec sp_helptext view_name
11.2 索引
索引是一个单独的,存储在磁盘上的数据库结构,它们包含这对数据表里所有的记录的引用指针
11.2.1索引的优点
(1)通过创建唯一索引,可以保证数据库表中每一行数据的唯一性
(2)可以大大加快数据的查询熟读,这也是最主要的员应
(3)实现数据的参照完整性,可以加速表与表的连接
(4)在使用分组和排序子句进行数据查询时,也可以显著减少查询中分组和排序的时间
11.2.2增加索引不利
(1)创建索引需要耗费时间
(2)索引需要占领一定的磁盘空间
(3)索引需要动态维护
11.2.3选择创建索引的数据列
1.定义有主键和外键的列
2.在指定范围中快速或频繁查询的列
3.过程中频繁使用的列
4.需要按排序顺序快速或频繁检索的列
11.2.4 索引的分类
1.聚合索引
索引的顺序决定了表中行的存储顺序,因此每一个表中只能有一个聚合索引
2.非聚合索引
非聚合索引并不在物理上排列数据,即索引中的逻辑顺序并不等于表中行的物理顺序
3.其他索引
11.2.5 索引的特性
若创建了主键约束或唯一约束时,系统将自动的为有这些约束的列创建聚合索引,
当删除主键约束或唯一约束时,这些列上的聚合索引也会被自动删除
若不指定索引类型,sqlserve将使用非聚合索引作为默认索引类型
聚合索引与非聚合索引的特性
1. 当在同一个表中建立聚合索引和非聚合索引时,应先建立聚合索引
2.若建立非聚合索引,在建立聚合索引时,系统会自动将非聚合索引删除,然后再重新建立非聚合索引
3.当现在的聚合索引被删除时,系统将自动重建现有的非聚合索引
11.2.6 用t-sql语句创建索引
--为student表的sname列建立非聚合索引
create index IX_stu_sname on student(sname)
--为student表的sno的列创建唯一的聚合索引
create unique clustered index IX_stu_sno on student(sno)11.2.7 显示索引信息 ,索引统计信息
exec sp_helpindex 'IX_stu_sno'
dbcc show_statistics ('图书馆.dbo.学生信息表',IX_stu_sno)11.2.8重命名索引
用sp_rename更改
例:将图书信息表中索引名IX_读者姓名_性别改为复合_索引
use 图书馆
go
exec sp_rename '读者信息表.IX_读者姓名_性别','复合_索引','index'
11.2.9删除索引
例:删除表读者信息表中的复合_索引
drop index 读者信息表.复合_索引
11.3 游标
11.3.1 游标的概念
游标是一种处理数据的方法,主要用于存储过程,触发器和T-SQL脚本中,使结果集的内容可用于其他的T-SQL语句
sql server中的数据操作结果都是面向集合的,并没有一种描述表中单一记录的表达形式,除非使用where语句限定查询结果,而游标具备针对单一记录的功能,并且游标比where更加灵活高效.
11.3.2 游标的类型
1.T-SQL游标
基于declare cursor 语法的,主要在T-SQL脚本,存储过程和触发器中使用
2.api(应用程序编程接口)服务器游标
api服务器游标支持在odbc中使用游标函数
3.客户端游标
客户端游标主要在客户机上缓存结果集才使用
11.3.3 游标的基本操作
1.声明游标
use 图书馆
go
declare 游标_图书 cursor for
select 图书名称,图书价格 from 图书信息表2.打开游标
open 游标_图书3.读取游标中的数据
fetch next from 游标_图书
while @@fetch_status = 0
begin
fetch next from 游标_图书
end4.关闭游标
close 游标名
5.释放游标
deallocate 游标名
11.3.4游标的运用
声明名称为'@当前_变量'的游标变量
use 图书馆
go
declare @当前_变量 cursor --声明游标变量
declare 游标_图书 cursor for --声明游标
select 图书名称,图书价格 from 图书信息
open 游标_图书 --打开游标
set @当前_变量 = 游标_图书 -- 为游标变量赋值
fetch next from @当前_变量 --从游标变量中读取值
while @@fetch_status = 0 --判断fetch语句是否执行成功
begin
fetch next from @当前_变量
end
close @当前_变量 --关闭游标变量
deallocate @当前_变量
