server2005学习脚本1

数据完整性
1、主键
1.1 创建表的时候设置主键
CREATE TABLE kc
(
课程号 char(4) not null constraint pk_kc primary key,
课程名 char(16) not null ,
学分 smallint ,
学时 smallint 
)


1.2 删除表的现有主键
ALTER TABLE kc DROP CONSTRAINT pk_kc


1.3 对已存在表设置主键
ALTER TABLE kc ADD CONSTRAINT pr_kc PRIMARY KEY(课程号,课程名)


2、唯一性
2.1 创建表的同时增加唯一性约束
CREATE TABLE kc_new
(
课程号 char(6) not null constraint pr_kc primary key,
课程名 char(16) not null constraint un_kc unique ,
学分 smallint ,
学时 smallint 
)


2.2 删除唯一性约束
ALTER TABLE kc_new DROP CONSTRAINT un_kc


2.3 多列组合唯一性
CREATE TABLE kc_new
(
课程号 char(6) not null constraint pr_kc primary key,
课程名 char(16) not null ,
学分 smallint ,
学时 smallint 
CONSTRAINT un_kc UNIQUE (课程号,课程名)
)


2.4 对已存在表增加唯一性约束
ALTER TABLE kc_new ADD CONSTRAINT  un_kc UNIQUE(课程号,课程名)


3、检查
3.1 增加检查约束
CREATE TABLE student
(
stu_id char(6) NOT NULL PRIMARY KEY,
stu_name char(20) NOT NULL ,
stu_tel char(8) CONSTRAINT ck_stu CHECK (stu_tel LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')
)


3.2 删除检查约束
ALTER TABLE student DROP CONSTRAINT ck_stu 


3.3 增加检查约束
CREATE TABLE student
(
stu_id char(6) NOT NULL PRIMARY KEY,
stu_name char(20) NOT NULL ,
stu_tel char(8) CHECK (stu_tel  LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')
)


3.4 对已存在表增加检查约束
ALTER TABLE student ADD CONSTRAINT ck_stu CHECK (stu_tel LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')


3.5 使检查约束失效/有效
ALTER TABLE student CHECK CONSTRAINT ck_stu
ALTER TABLE student NOCHECK CONSTRAINT ck_stu


3.6 对现有数据不进行检查的检查约束
ALTER TABLE student WITH NOCHECK ADD CONSTRAINT ck_stu 
CHECK (stu_tel LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')


4、默认值1
4.1 创建表的同时增加默认值约束
CREATE TABLE student 
(
stu_no char(6) NOT NULL PRIMARY KEY,
stu_name char(20) NOT NULL,
stu_tel char(8) CHECK (stu_tel LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),
stu_sex char(2) DEFAULT '男'
)


4.2 创建表的同时增加默认值约束
CREATE TABLE student 
(
stu_no char(6) NOT NULL PRIMARY KEY,
stu_name char(20) NOT NULL,
stu_tel char(8) CHECK (stu_tel LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),
stu_sex char(2) CONSTRAINT df_stu DEFAULT '男'
)


4.3 删除默认值约束
ALTER TABLE student DROP  CONSTRAINT df_stu


4.4 对已存在表增加默认值约束
ALTER TABLE student ADD CONSTRAINT df_stu DEFAULT '女' for stu_sex


5 默认值2
5.1 创建默认值
CREATE DEFAULT df_xf AS 6


5.2 绑定默认值
EXEC sp_bindefault 'df_xf' , 'tb_kc.xf'


5.3 解除默认值绑定
EXEC sp_unbindefault 'tb_kc.xf'


5.4 删除默认值
DROP DEFAULT df_xf


6 外键
6.1 创建表的同时增加外键约束
--先定义参照表
CREATE TABLE class 
(
cls_no char(4) PRIMARY KEY,
cls_name char(20) NOT null
)
--新建student表时定义外键约束
CREATE TABLE student 
(
stu_no char(6) NOT NULL PRIMARY KEY,
stu_name char(20) NOT NULL,
stu_tel char(8) CHECK (stu_tel LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),
stu_sex char(2) CONSTRAINT df_stu DEFAULT '男',
stu_cls char(4) constraint  fk_stu  foreign KEY references class(cls_no)
)


6.2 删除外键约束
ALTER TABLE student DROP CONSTRAINT fk_stu


6.3 对已存在表增加外键约束
ALTER TABLE student ADD CONSTRAINT fk_stu FOREIGN KEY(stu_cls) REFERENCES class(cls_no)


6.4 级联引用完整性约束
ALTER TABLE student ADD CONSTRAINT fk_stu FOREIGN KEY(stu_cls) REFERENCES class(cls_no) ON DELETE CASCADE ON UPDATE cascade


/*
级联引用完整性约束(--on update cascade)
当用户试图删除或更新外键所指向的键时，级联引用完整性约束使您得以定义 Microsoft® SQL Server? 2000 所采取的操作。


CREATE TABLE 和 ALTER TABLE 语句的 REFERENCES 子句支持 ON DELETE 和 ON UPDATE 子句：
[ ON DELETE { CASCADE | NO ACTION } ]
[ ON UPDATE { CASCADE | NO ACTION } ]


如果没有指定 ON DELETE 或 ON UPDATE，则默认为 NO ACTION。NO ACTION 指定的行为与 SQL Server 的早期版本中发生的行为相同。


ON DELETE NO ACTION
指定如果试图删除某行，而该行含有由其它表的现有行中的外键所引用的键，则产生错误并回滚 DELETE。


ON UPDATE NO ACTION
指定如果试图更新某行中的键值，而该行含有由其它表的现有行中的外键所引用的键，则产生错误并回滚 UPDATE。


CASCADE 允许在表间级联键值的删除或更新操作，这些表的外键关系可追溯到执行修改的表。不能为任何具有 timestamp 列的外键和主键指定 CASCADE。
ON DELETE CASCADE
指定如果试图删除某行，而该行含有由其它表的现有行中的外键所引用的键，则也将删除所有包含那些外键的行。如果在目标表上也定义了级联引用操作，则对从那些表中删除的行同样采取指定的级联操作。


ON UPDATE CASCADE
指定如果试图更新某行中的键值，而该行的键值由其它表的现有行中的外键所引用，则所有外键值也将更新成为该键指定的新值。如果在目标表上也定义了级联引用操作，则对在那些表中更新的键值同样采取指定的级联操作。
*/


7 规则
7.1 创建规则
CREATE RULE rl_xf AS @score>=0 AND @score<=100
CREATE RULE rl_tel AS @dh LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' 


7.2 绑定规则
EXEC sp_bindrule 'rl_xf','tb_kc.xf'
EXEC sp_bindrule 'rl_tel','student.stu_tel'


7.3 解除规则绑定
EXEC sp_unbindrule 'tb_kc.xf'
EXEC sp_unbindrule 'student.stu_tel'


7.4 删除规则
DROP RULE rl_xf


7.5 查看所有自定义规则
SELECT * FROM sys.all_objects WHERE type='R'
SELECT * FROM sys.objects WHERE type='R'


EXEC sp_helptext 'rl_tel'


8、标识列
8.1 创建表规定标识列
CREATE TABLE student 
(
stu_id int IDENTITY(1,2) NOT NULL,
stu_no char(6) NOT NULL PRIMARY KEY,
stu_name char(20) NOT NULL,
stu_tel char(8) CHECK (stu_tel LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),
stu_sex char(2) CONSTRAINT df_stu DEFAULT '男',
stu_cls char(4) constraint  fk_stu  foreign KEY references class(cls_no)
)


8.2 对已存在表增加标识列
ALTER TABLE student ADD ID int IDENTITY(1,1) NOT null


9、自定义数据类型
9.1 创建自定义数据类型
EXEC sp_addtype tp_tel,'varchar(8)','NULL'


9.2 创建表时使用自定义数据类型
ALTER TABLE  tb_kc ADD  kc_name tp_tel


9.3 删除自定义类型
EXEC sp_droptype tp_tel