SQL基本操作（四）：索引与数据完整性

--为kc表的课程名列创建索引
--使用简单索引
use xscj
if EXISTS(select name from sysindexes where name='kc_name_ind')
drop index kc.kc_name_ind
go
--创建索引
create index kc_name_ind on kc(课程名)
go
--根据kc表的课程号列创建唯一的聚集索引，因为指定了clustered子句,所以该索引对磁盘上的数据进行物理排序
--使用唯一聚集索引
use xscj
if EXISTS(select name from sysindexes where name='kc_id_ind')
drop index kc.kc_id_ind
go
create unique clustered index kc_id_ind on kc(课程号)
go
--删除索引
drop index kc.PK__kc__79A81403
--根据xs_kc表的学号和课程号列创建符合索引
use xscj
if EXISTS(select name 
from sysindexes where name='xs_kc_ind')
drop index xs_kc.xs_kc_ind
go
create index xs_kc_ind on xs_kc(学号,课程号)
go
--根据xs表中的总学分列创建索引，列中使用了FILLFACTOR子句
use xscj
if EXISTS(select name from sysindexes where name='score_ind')
drop index score_ind
go
create nonclustered index score_ind on xs(总学分)
with fillfactor=60
go
--根据xs表中学号列创建唯一聚集索引,如果输入重复的键，将忽略该INSERT或UPDATE语句
--使用 IGNORE_DUP_KEY
use xscj
if EXISTS(select name from sysindexes where name='xs_ind')
drop index xs.xs_ind
create unique clustered index xs_ind on xs(学号)
--通过SQL语句定义和绑定DEFAULT默认值对象
--将创建的默认值对象绑定到xs表的总学分字段
--定义默认规则
create default zjc_default as 0
go
--绑定规则
use xscj
EXEC sp_bindefault 'zjc_default','xs.总学分'
go
--首先在xscj数据库中定义表book及名为today的默认值,将其绑定到book表的hire date 列
--定义表
create table book
(
 book_id char(6),
 name varchar(20) not null,
 hire_date datetime not null
)
go
--创建默认值对象
create default today as getdate()
go
--绑定默认值对象
EXEC sp_bindefault 'today','book.[hire_date]'
--定义名为birthday_date 的数据类型,然后定义默认值对象day并将其绑定到用户定义的数据类型birthday_date中
use xscj
EXEC sp_addtype birthday_date,'datetime','NULL'
go
--定义day默认值对象
create default day as '1960,00,00'
go
--将默认值对象day绑定到birthday_date数据类型
use xscj
go
EXEC sp_bindefault 'day','birthday_date'

--解除默认值对象day与xscj库中的用户定义类型birthday_date的绑定关系，然后解除名为day的默认值对象
use xscj
go
if EXISTS(select name from sysobjects where name='day' and type='D')
begin 
EXEC sp_unbindefault 'birthday_date'
drop default day
end
go
--在修改表时修改创建CHECK约束
--xs_kc表中，增加成绩字段的CHECK约束
use xscj
alter table xs_kc
add constraint cj_constraint check(成绩>0 and 成绩<=100)
--删除xs_kc表中成绩字段的check约束
use xscj
alter table xs_kc
drop constraint cj_constraint
go
--创建一个规则，并绑定到表KC的课程号列,用于限制课程号的输入范围
use xscj
go
create rule kc_rule
as @range like '[1-5][0-9][0-9]'
go
use xscj
EXEC sp_bindrule 'kc_rule','kc.课程号'
go
--创建一个规则,用以限制输入到该规则所绑定的列中的值只能是该规则中的列出的值
use xscj
go
--创建规则
create rule list_rule
as @list IN ('C语言','离散数学','微机原理')
go
use xscj
--将规则绑定到对应列
EXEC sp_bindrule 'list_rule','KC.课程名'
go
--测试数据
--不可以插入，违反规则
insert into kc values('401','计算机基础',1,80,5)
--可以插入
insert into kc values('402','C语言',1,80,5)
--定义哟个用户数据类型course_num，然后将前面定义的规则'kc_rule' 
--绑定到用户数据类型course_num上，然后定义表kc，其课程号的数据类型为course_num
use xscj
go
--用户自定义数据类型
EXEC sp_addtype 'course_num','char(3)','not null'
--将前面的用户自定义规则kc_rule绑定到用户自定义数据类型course_num上
EXEC sp_bindrule 'kc_rule','course_num'
go
--定义表KC1
create table KC1
(
课程号 course_num,
课程名 char(16) not null,
开课学期 tinyint,
学时 tinyint,
学分 tinyint
)
go
--解除课程号列与kc_rule之间的绑定关系，并删除规则对象kc_rule
use xscj
go  
if EXISTS(select name from sysobjects where name='kc_rule' and type='r')
begin
EXEC sp_unbindrule 'kc.课程号'           
drop rule kc_rule
end
go
--解除自定义类型course_num与kc_rule 之间的绑定关系,并删除规则对象kc_rule
use xscj
go
if EXISTS(select name from sysobjects where name='kc_rule' and type='r')
begin 
EXEC sp_unbindrule 'course_num'
drop rule kc_rule
end
go
--在本地磁盘上创建一个命名备份设备
use master
EXEC sp_addumpdevice 'disk','mybackupfile','c:\mybackupfile'
--删除命名备份文件
use master
EXEC sp_dropdevice 'mybackupfile',delfile
--使用backup语句进行完全数据库备份列子
--使用逻辑名创建一个命名的备份设备，并将数据库XSCJ完全备份到该设备
use master
EXEC sp_addumpdevice 'disk','test1','c:\db_temp\test1.bak'
go
BACKUP database xscj to test1
--先利用事务变量命名一个事务,提交事务后，将为所有的计算机系的学生增加10个学分
use xscj
go
declare @tran_name varchar(20)
set @tran_name='MyTran1'
begin transaction @tran_name
go
use xscj
update xs 
set 总学分=总学分+10
where 专业名='计算机'
go
--提交事务
commit transaction @tran_name
go
--向XS表中插入1行数据,然后删除该行,但执行后，新插入的数据行并没有被删除
--因为事务中使用rollback语句将操作回滚到保存点My_sav即删除前的状态
begin transaction
use xscj
insert into xs 
values('001115','朱一虹','计算机',1,'3/20/1980',38,NULL)
save transaction My_sav

delete from xs
where 姓名='朱一虹'
rollback transaction My_sav
commit transaction 
go
select *from xs