SQL Server--索引、视图、约束、规则

八、索引

创建索引加快查询效率，时间复杂度变小（二叉树：二分查找 O(logn)）
1、使用create index 语句创建索引

在school数据库teacher表中的“编号”列上创建一个聚集索引。

use school
--判断是否存在idx_tno索引，若存在，则删除。
if exists(select name from sysindexes where name='idx_tno')
	drop index teacher.idx_tno
go
--创建idx_tno索引
create index idx_tno on teacher(编号)
go

为student表的“班号”和“姓名”列创建非聚集索引 idx_bhname，并且强制唯一性。

use school
--判断是否存在idx_tno索引，若存在，则删除
if exists(select name from sysindexes where name='idx_bhname')
	drop index score.idx_bhname
go
--创建idx_tno索引
create unique nonclustered index idx_bhname on student(班号,姓名)
go

2、使用create table语句创建索引
使用create table（或alter table）语句创建表时，如果指定primary key约束或者unique约束，则sql server自动为这些约束创建索引。

查看索引信息：
exec sp_helpindex 对象名

采用sp_helpindex存储过程查看student表上所创建的索引。

use school
go
exec sp_helpindex student
go

3、修改索引
alter index{索引名|all} on 表或视图名
rebuild [with (rebuild_index_option)]
all:指定与表或视图相关联的所有索引，而不考虑索引类型。
rebuild：重建索引。

修改创建的索引 idx_bhname，将fillfactor改为90.

use school
go
alter index idx_bhname on student
	rebuild with (pad_index=on,fillfactor=90)

4、删除索引
drop index 表名.索引名

使用drop index语句删除前面创建的索引 index_bhname。

use school
go
drop index student.idx_bhname
go

九、视图

1、创建视图
create view 视图名 as select语句
创建一个名称为st1_score的视图，其中包括所有学生的姓名、课程和成绩。

use school
go
create view st1_score	--创建视图
as
select student.姓名,course.课程名,score.分数 from student,course,score
where student.学号=score.学号 and course.课程号=score.课程号
go

INSERT INTO view1 VALUES (‘第1行’,1) --通过视图view1插入一个记录

2、修改视图
使用alter view语句可以更改一个先前创建的视图。
alter view 视图名 as select语句
3、 使用系统存储过程sp_rename重命名视图
exec sp_rename ‘原视图名’ ,‘新视图名’

将视图view1重命名为view11。

use test
go
exec sp_rename 'view1','view11'
go

4、使用sp_helptext存储过程查看视图的信息
sp_helptext ‘name’

查看school数据库中st_score视图的定义。

use school
go
exec sp_helptext st_score

5、删除视图
drop view {视图名}

检查test数据库中是否存在view3视图，若有，则删除。

use test
go
if object_id('view3','V') is not null
	drop view view3
go

十、约束

1、primary key约束
（1）在未设置主键的表中添加主键约束

alter table department	--部门表
add
constraint 部门编号 primary key(部门号)	--部门号为主键

（2）定义组合主键约束

create table department	--部门表
(	部门号 int,
	部门名 char(20)
	constraint 部门编号 primary key(部门号,部门名)
)

（3）删除主键约束

alter table table_name
drop
constraint 约束名

2、foreign key约束
在定义表时直接创建外键约束

[CONSTRAINT 外键约束名]
FOREIGN KEY REFERENCES 引用的表名 [ (引用列) ]
[ ON DELETE { NO ACTION | CASCADE } ]
[ ON UPDATE { NO ACTION } ]

NO ACTION：指定删除因错误而失败。
CASCADE：指定还将删除已删除行的外键的所有行。

USE test
GO
CREATE TABLE worker				--职工表
(	编号 int PRIMARY KEY,  		--编号为主键
	姓名 char(8), 
	性别 char(2),
	部门号 int FOREIGN KEY REFERENCES department(部门号) ON DELETE NO ACTION,	
	地址 char(30)
)
GO

3、unique约束
使用UNIQUE约束也可以指定数据的唯一性。
UNIQUE约束指定列数据值不允许重复。
constraint 约束名称 unique 列名
在test数据库中创建一个table5表，其中指定了c1列不能包含重复的值。

use test
go
create table table5
(	c1 int unique,c2 int
)
go
insert table values(1,100)
go

--如果再插入如下一行，则出现错误
insert table5 values(1,200)

4、check约束
CHECK约束又叫检查约束，用于限制输入到列的值得范围。 它指定应用于列中输入的所有值的逻辑表达式（取值为TRUE或FALSE），拒绝所有不取值为TRUE的值。可以为每列指定多个CHECK约束。
check （逻辑表达式）

在test数据库中创建一个table6表，其中使用check约束来限定f2列只能为0~100分。

use test
go
create table table6
(	f1 int,
	f2 int not null check(f2>=0 and f2<=100) --或between 0 and 100
)
go

--执行如下语句，将出现错误信息
insert table6 values(1,120)

十一、规则

规则是对存储的数据表的列或用户定义数据类型中的值的约束。
　1、创建规则
　create rule 规则名 as condition_exprion
　condition_exprion:定义的规则的条件

创建一个名为rule1的规则，限定输入的值必须在0~10之间。

use test
go
create rule rule1 as @c1 between 1 and 10
go

2、绑定规则
sp_bindrule ‘规则名’,‘表名.列名’

将rule1规则绑定到test数据库中table9表的c1列上。

use test
go
exec sp_bindrule 'rule1','table9.c1'

3、解除和删除规则
先解除绑定，再删除。
解除规则：sp_unbindrule ‘要删除规则绑定的表和列’
删除规则：drop rule 规则名

解除绑定到table9表的c1列上的规则。

use test
go
exec sp_unbindrule 'table9.c1'
go

删除test数据库中的规则rule1.

use test
go
drop rule rule1
go