SQL Server表的创建及索引的控制
一、定义基本表 CREATE TABLE
格式： CREATE TABLE 表名
(列名类型(长度) [NOT NULL]
[DEFAULT {常量|系统变量|NULL}]
[列约束],……)
[PRIMARY KEY (列名,…)]
[FOREIGN KEY (列名,…) REFERENCES 表名(列名,…)]
[CHECK 条件];
|
create table person( Pno char(6) not null primary key, Pname varchar(10) not null, Sex char(2) not null, Birthday datetime not null, Prof varchar(10) not null, Deptno char(4) foreign key references department(Deptno) ); |
|
create table salary( Pno char(6) not null, Base decimal(5,0) null check( base >= 800), Bonus decimal(5,0) null check( Bonus >= 200), [Month] int not null, Fact as [Base] + [Bonus], primary key( Pno , Month) ); |
二、约束
1、主码约束：主码值不允许空，也不允许出现重复
主码定义形式
表级主码定义：PRIMARY KEY(Sno , Month)
Constraint pk_student primary key(sno)
列级主码定义：Sno CHAR(4) PRIMARY KEY
2、唯一值约束UNIQUE
定义形式
表级定义：UNIQUE(Sno)
Constraint UQ_student UNIQUE (sno)
列级定义：Sno CHAR(4) UNIQUE
3、外码约束
定义形式 :在SC表中，定义FOREIGN KEY (Sno) REFERENCES Student(Sno)
Sno在SC中是外码，在Student中是主码，Sno在SC中取值或为空或为Student中某个Sno值
作为主码的关系称为基本（参照）关系，作为外码的关系称为依赖关系
删除基本关系元组
RESTRICT方式 : 只有当依赖关系中没有一个外码值与要删除的基本关系的主码值相对应时，才可以删除该元组，否则系统拒绝此删除操作
CASCADE方式 : 将依赖关系中所有外码值与基本关系中要删除的主码值所对应的元组一起删除
SET NULL方式 : 删除基本关系中元组时，将依赖关系中与基本关系中被删主码值相对应的外码值置为空值
如FOREIGN KEY (Sno) REFERENCES Student(Sno) [ON DELETE [CASCADE | SET NULL] ]
修改基本关系主码
RESTRICT方式 : 只有当依赖关系中没有一个外码值与要修改的基本关系的主码值相对应时，才可以修改该元组主码，否则系统拒绝此次修改
CASCADE方式 : 将依赖关系中所有与基本关系中要修改的主码值所对应的外码值一起修改为新值
SET NULL方式 ：修改基本关系中元组主码时，将依赖关系中与基本关系中被修改主码值相对应的外码值置为空值
如FOREIGN KEY (Sno) REFERENCES Student(Sno) [ON UPDATE [CASCADE | SET NULL] ]
4、规则
规则限定了属性列的范围
定义形式 : CREATE RULE 规则名AS规则
Create rule sex_rule as @sex in ('男' ,'女')
使用规则
Sp_bindrule sex_rule,'student.ssex'
Sp_unbindrule ’student.ssex’
5、在属性值上的约束
-非空约束
SNAME CHAR(8) NOT NULL
-基于属性的检查子句
CHECK(AGE>15)
-默认值
CREATE TABLE SC(
Sno CHAR(4),
Cno CHAR(4),
GRADE SMALLINT,
PRIMARY KEY (Sno, Cno),
CHECK(Sno IN (SELECT Sno FROM Student)),
CHECK(Cno IN (SELECT Cno FROM Course))
)
三、删除基本表 DROP TABLE
格式: DROP TABLE 表名[CASCADE|RESTRICT];
CASCADE : 连同引用该表的视图、完整性约束一起自动撤消
RESTRICT : 无引用时，才可撤消
四、修改基本表 ALTER TABLE
格式： ALTER TABLE 表名
[ADD 新列名类型(长度)[NOT NULL]
[列约束],…]
[MODIFY 旧列名类型(长度)]
[DROP 列约束]
[RENAME 旧表名TO 新表名]
例:在Student表中增加个入学时间，为日期型
ALETR TABLE Studnet ADD SCOME DATE；
例:把Sage列的类型改为半字长整数
ALETR TABLE Student MODIFY Sage SMALLINT；
例:删除对Sname列的唯一约束
ALETR TABLE Student DROP UNIQUE(Sname)；
五、定义索引 CREATE INDEX
语句格式 : CREATE [UNIQUE] [CLUSTERED] INDEX <索引名> ON <表名>(<列名>[<次序>][,<列名>[<次序>] ]…);
用<表名>指定要建索引的基本表名字
索引可以建立在该表的一列或多列上，各列名之间用逗号分隔
用<次序>指定索引值的排列次序，升序：ASC，降序：DESC。缺省值：ASC
UNIQUE表明此索引的每一个索引值只对应唯一的数据记录
CLUSTERED表示要建立的索引是聚簇索引
CREATE UNIQUE INDEX Stusno ON Student(Sno ASC); /* 唯一索引，升序*/
常用的索引:唯一索引和聚簇索引
唯一值索引:对于已含重复值的属性列不能建UNIQUE索引
对某个列建立UNIQUE索引后，插入新记录时DBMS会自动检查新记录在该列上是否取了重复值。这相当于增加了一个UNIQUE约束
聚簇索引:建立聚簇索引后，基表中数据也需要按指定的聚簇属性值的升序或降序存放。也即聚簇索引的索引项顺序与表中记录的物理顺序一致
CREATE CLUSTERED INDEX Stusname ON Student(Sname)；
在Student表的Sname（姓名）列上建立一个聚簇索引，而且Student表中的记录将按照Sname值的升序存放
在一个基本表上最多只能建立一个聚簇索引，聚簇索引的用途：对于某些类型的查询，可以提高查询效率
聚簇索引的适用范围：
很少对基表进行增删操作
很少对其中的变长列进行修改操作
六、删除索引 DROP INDEX
DROP INDEX <索引名>;
删除索引时，系统会从数据字典中删去有关该索引的描述。
例: 删除Student表的Stusname索引。
DROP INDEX Stusname；(在sqlserver中加入表名,如drop index student.stusname；)
