第五章 SQL Server数据库对象管理

视图

视图是保存在数据库中的查询语句，其内容由查询定义。因此，视图是一张虚表。

定义和删除视图

1. 定义视图

create view <视图名>[(<视图列表>)][with Encryption]
as <子查询>
#<视图列表>为可选项，省略时，视图的列名由子查询的结果觉得。with Encryption是指对系统表syscomments的create view语句加密

以下两种情况下，视图列名不可省略：
1）视图由多个表连接得到，在不同的表中存在同名列，则需指定列名
2）当视图的列名为表达式或库函数的计算结果，而不是单纯的属性名称时，则需指明列名
在子查询中不许使用order by子句和distinct短语，如果需要排序，则可在视图定义后，对视图查询时再进行排序。

create view view_s
as select sno,sname,sage
from student where dept='计算机'

create view s_sc_c (sno,sname,cname,grade)
as select s.sno,sname,cname,grade
from student s, course c,sc
where s.sno = sc.sno and
sc.cno = c.cno
#由于student和sc中均存在sno列，故需要指定视图列名

create view s_avg(sno,avg)
as select sno,avg(grade)
from sc group by sno
#avg为库函数，故需要指定视图列名

2. 删除视图

drop view <视图名>

drop view view_s

查询视图

select sname
from view_s
where sname like '张%'

更新视图

视图是一张虚表，所以对视图的更新，最终实际上是转换成对基本表的更新。

1. 插入（insert）

insert into view_s
values ('08006','王凡','18')

等价于

insert into student(sno,sname,sage,sdept)
values ('08006','王凡','18','计算机')

2. 修改（update）

update view_s
set sage = 18
where sname = '王凡'

等价于

update student
set sage = 18
where sname = '王凡' and sdept = '计算机'

3. 删除（delete）

delete
from view_s
where sname = '王凡'

由于视图没有相应的存储空间，对视图的一切操作最终都要转换为对基本表的操作。
视图的优点：利于数据保密；简化查询操作；保证数据的逻辑独立性。

索引

数据库的索引和书籍中的目录非常相似。
索引按其是否聚集可分为聚集索引和非聚集索引。
聚集索引中，表中各行的物理顺序与索引键值的逻辑顺序相同。表只能包含一个聚集索引。
非聚集索引具有完全独立于数据行的结构。

创建索引

create [unique]
		[clustered|nonclustered]#默认非聚集索引
index 索引名
on {表名|视图名}(列名[asc|desc][...n])#指定索引是升序还是降序，默认asc

说明：unique指定要创建唯一索引。在创建位移索引时，如果数据已存在，SQL会检查是否有重复值，并在每次使用insert和update语句添加数据时进行这种检查。如果存在重复的键值，将取消create index语句，并返回错误信息，给出第一个重复值。

create unique index ix_name
on student (sname)

create unique clustered index ix_name on student (sname)

create index ix_age on student (sage desc)

查看、修改和删除索引

drop index 表名.索引名|视图名.索引名[,...n]

drop index student.ix_age

T-SQL语言基础

该语言是一种非过程化语言，功能强大。

符号	含义
大写	关键字
斜体或中文	参数，使用时需要替换成具体内容
|	分割括号或大括号内的语法项目，只能选一项
[]	可选的语法项
{}	必选的语法项
[,…n]	前面的项可重复n次，各项之间用逗号分隔
[…n]	前面的项可重复n次，各项之间用空格分隔
<标签>	语法块的名称，用于对过长语法或语法单元部分进行标记
<标签>::=	对语法中<标签>指定位置进行进一步的定义

变量

1. 局部变量
   局部变量的作用范围仅限制在程序的内部。常用来保存临时数据。例如，可以使用局部变量保存表达式的计算结果，作为计算器保存循环执行的次数，或者用来保存由存储过程分会的数据值。
   1）局部变量的定义

declare @mycounter int

2）局部变量的赋值

declare @myvar char(20)
set @myvar = 'this is a test'##用set赋值
print @myvar ##用print语句显示

2. 函数
   1）内置函数
   2）用户定义函数：使用create function语句
3. 流程控制语句
   begin…end和if…else

use 学生信息
declare @Today int
set @Today = day(getdate())
if (@Today = 1 )
	begin
		select 学号,姓名 as 本月寿星,出生日期
		from 学生基本信息
		where month(出生日期) = month(getdate())
	end

4. while循环

declare @i smallint,@sum smallint
set @i = 1
set @sum = 100
while @i <=100
	begin 
		set @sum = @sum +@i
		set @i = @i +2
	end
print '1到100之间的奇数和为'+str(@sum)

5. waitfor语句

begin
	waitfor delay '00:01'
	print 'hello'
end
#一分钟之后打印hello

6. return
   用于无条件地终止一个查询、存储过程或者批处理
7. 注释
   1）——：这种注释字符可与要执行的代码处在同一行，也可以另起一行。从双连字符开始到行尾均表示注释。对于多行注释，必须在每个注释行的一开始就使用双连字符。
   2）/…/：可与代码处在同一行，也可以另起一行，甚至用在可执行代码内。之间的全部内容均为注释部分。对于多行注释，必须使用/开始注释，/结束注释。注释行上不应出现其他注释字符。

存储过程

存储过程是一组事先编译好的T-SQL代码。存储过程作为一个独立的数据库对象，可以作为一个单元被用户的应用程序调用。由于存储过程是已经编译好的代码，所以执行的时候不必再次进行编译，从而提高了程序的运行效率。

使用T-SQL语句创建存储过程

create proc [edure] 存储过程名
[with recompile | encryption |recompile,encryption]
as
SQL 语句
#recompile：在执行完存储过程以后不再高速缓存里保存存储过程的备份，所以每次执行存储过程都需要对存储过程进行重新编译和优化。
#encryption：存储过程作为数据库对象将在syscomments表中留下完整的代码等信息。使用了encryption参数以后，将对访问这些数据的人口进行加密。

use lizi
go
create procedure lizi_Information
as
select sname,grade
from student,sc
where student.sno = sc.sno
go

####使用T-SQL语句查看存储过程

1. 可以使用sp_helpText命令查看创建存储过程的文本信息

use lizi
go
sp_helptext lizi_Information
go

2. 可以使用sp_help查看存储过程的一般信息

use lizi
go
sp_help lizi_Information
go
#如果在创建存储过程时，使用了with encryption参数，则使用sp_helptext将无法看到有关存储过程的信息。

3. 可以使用sp_depends查看被存储过程的引用情况

sp_depends lizi_Information
#sp_depends 存储过程名

在不改变存储过程使用许可和名字的情况下，对存储过程进行修改的语法：

alter proc [edure] 存储过程名
	[with recompile | encryption |recompile,encryption]
as
	SQL语句

使用T-SQL语句删除存储过程

drop procedure 存储过程名[,...n]

use lizi
go
drop procedure lizi_Information
go

触发器

使用T-SQL语句创建触发器

触发器是特殊的存储过程。当使用DML语句时，SQL-Server自动执行这一类存储过程。

create trigger 触发器名 on 表名 [with encryption]
for {[delete][,][insert][,][update]} [not for replication]
as
	SQL 语句
	[reture 整数表达式]#不知道是不是return

触发器作为一种数据库对象，在syscomments表中存储有完整的文本定义信息。可以使用with encryption对访问syscomments表的入口进行加密。
not for replication：定义在复制过程中，不执行触发器操作。

create trigger lianxi_del
on jsinfo
for delete
as print'借书信息表删除了数据'

在create trigger语句中不能使用select语句返回对表格查询的数据，因为触发器不接受用户应用程序传递的参数，从而也无法向用户应用程序返回查询表格数据所得到的结果。
在创建触发器的语句中禁止使用以下T-SQL语句：

T-SQL语句	T-SQL语句
alter database	alter procedure
alter table	alter trigger
alter view	create database
create default	create index
create procedure	create rule
create table	create trigger
drop database	drop default
drop index	drop procedure
drop rule	drop table
drop trigger	drop view
grant restore database	restore logrevoke
truncate table

由于系统表所存储数据的特殊性和重要性，所以建议用户不要自己在系统表删建立触发器，在创建触发器时，不允许return 返回体现运行状态的数据。

使用T-SQL语句删除触发器

drop trigger 触发器名[,...n]

use lizi
go
drop lianxi_del

用户删除某个表格时，所有建立在该表上的触发器都将被删除。