sql 存在检测、建库、 建表、约束、外键、级联删除.docVIP

sql?存在检测、建库、?建表、约束、外键、级联删除 use mastergo --判断删除已存在的数据库 stuDBif exists(select * from sysdatabases where name = stuDB)drop database stuDB --新建数据库 stuDBcreate database stuDBon primary(name=stuDBdata,filename=d:\sql\stuDBdata.mdf,size=5mb,maxsize=unlimited,filegrowth=10%)log on(name=stuDBlog,filename=d:\sql\stuDBdata.ldf,size=1mb,maxsize=unlimited,filegrowth=1mb)go --打开use stuDBgo --判断是否已存在要创建的表if exists(select * from sysobjects where name = stuinfo)drop table stuInfo --新建表create table stuInfo(stuName varchar(20) not null,stuNo varchar(20) not null,stuSex varchar(20) not null,stuAge int not null,stuSeat int identity(1,1) not null,stuAddress text not null)go exec sp_help stuInfo --查看表属性 --为表添加约束alter table stuInfo-- with nocheck? --在创建约束时忽略已存在的坏数据addconstraint pk_stuNo primary key (stuNo),constraint ck_stuNo check(stuNo like s253__),// constraint?ck_stuNo?check(stuNo?like?s253[0-9][0-9]),这样会全面些。constraint ck_stuSex check(stuSex in (男,女)),constraint ck_stuAge check(stuAge between 15 and 40),constraint ck_stuSeat check(stuSeat between 1 and 30),constraint df_stuAddress default 地址不祥 for stuAddressgo exec sp_helpconstraint stuInfo? --查看约束 -- nocheck -临时禁用现有约束alter table stuInfonocheckconstraint pk_stuNogo -- check -恢复现有约束alter table stuInfocheckconstraint pk_stuNogo --添加数据insert into stuInfo?values(小强,s25301,男,18,北京海淀)insert into stuInfo?values(旺财,s25303,女,22,河南洛阳)insert into stuInfo?values(梅超风,s25302,男,31,default)insert into stuInfo?values(欧阳俊雄,s25304,男,28,新疆威武哈) --查看表中数据select * from stuinfo --打开stuDB数据库use stuDBgo --检查stuMarks表是否存在if exists(select * from sysobjects where name=stuMarks)drop table stuMarksgo --新建stuMarks表create table stuMarks(marksId int identity(101,1) not null,stuNo varchar(20) not null,writtenExam float not null,labExam float not null)go --查看表属性exec sp_help stuMarks --为stuMarks建立约束alter table stuMarksaddconstraint pk_marksId primary key(marksId),constraint fk_stuNo foreign key