SQL的基本操作
记录SQL的基本操作
创建表:
字段的默认值约束
字段是否可为空约束
字段唯一性约束
表的主键约束
表的外键约束
列中的值的范围约束
字段的类型
表的索引
CREATE TABLE Department ( Id int identity(1,2) primary key, Fid int not null, Code varchar(20), name nvarchar(20), statusCode char(1) , CHECKCODE INT, DelFlag bit default(0) not null, CreateDate datetime, --check(statusCode!='A')--添加check约束 CONSTRAINT chk_Person CHECK (CHECKCODE>1 AND Code='Sandnes'), CONSTRAINT chk_Person1 CHECK (name='C' or name='B'), -- Foreign key (Fid) references Department1(Id)--不加外键名称,自动生成 --constraint F_k foreign key (Fid) references Department1(Id)--自定义外键名称 ) CREATE TABLE Department1 ( Id int identity(1,2) primary key, Code varchar(20), name nvarchar(20), statusCode char(1), DelFlag bit not null, CHECKCODE INT, CreateDate datetime ) --添加删除约束-- --unique alter table Department1 add constraint U_k unique (id) alter table Department1 drop constraint U_k --Primarykey alter table Department1 add constraint P_K primary key (id) alter table Department1 drop constraint P_K --Foreign Key alter table Department1 add constraint F_kss foreign key (Id) references Department(Id) alter table Department1 drop constraint F_kss --CHECK alter table Department1 add constraint C_K CHECK(CHECKCODE>5) alter table Department1 drop constraint C_K -- 可空约束/其实就是修改列属性 alter table Department1 alter column Code varchar(20) not null --默认约束 alter table Department1 add constraint d_k default(0) for DelFlag --------------------------------------------- --修改和增加列 alter table Department1 alter column Code varchar(20) not null alter table Department1 add Code1 varchar(20) not null --创建/删除索引 CREATE INDEX INDEX_A ON Department1 (CODE,NAME) DROP INDEX Department1.INDEX_A
--重命名列名 EXEC sp_rename '表名.旧列名', '新列名', 'column' --例: EXEC sp_rename 'CustomerProduct.CustomerProductName', 'CustomerProductCode', 'column'
随便用