sql 存在检测、建库、 建表、约束、外键、级联删除
--打开库master
use master
go
go
--判断删除已存在的数据库 stuDB
if exists(select * from sysdatabases where name = 'stuDB')
drop database stuDB
if exists(select * from sysdatabases where name = 'stuDB')
drop database stuDB
--新建数据库 stuDB
create database stuDB
on 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
create database stuDB
on 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 stuDB
go
use stuDB
go
--判断是否已存在要创建的表
if exists(select * from sysobjects where name = 'stuinfo')
drop table stuInfo
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
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 --在创建约束时忽略已存在的坏数据
add
constraint 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 stuAddress
go
alter table stuInfo
-- with nocheck --在创建约束时忽略已存在的坏数据
add
constraint 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 stuAddress
go
exec sp_helpconstraint stuInfo --查看约束
-- nocheck -临时禁用现有约束
alter table stuInfo
nocheck
constraint pk_stuNo
go
alter table stuInfo
nocheck
constraint pk_stuNo
go
-- check -恢复现有约束
alter table stuInfo
check
constraint pk_stuNo
go
alter table stuInfo
check
constraint pk_stuNo
go
--添加数据
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,'*威武哈')
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
select * from stuinfo
--打开stuDB数据库
use stuDB
go
use stuDB
go
--检查stuMarks表是否存在
if exists(select * from sysobjects where name='stuMarks')
drop table stuMarks
go
if exists(select * from sysobjects where name='stuMarks')
drop table stuMarks
go
--新建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
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
exec sp_help stuMarks
--为stuMarks建立约束
alter table stuMarks
add
constraint pk_marksId primary key(marksId),
constraint fk_stuNo foreign key(stuNo)
references stuInfo(stuNo)
on update no action --级联更新（无操作）
on delete cascade,--级联删除
constraint ck_writtenExam check(writtenExam between 0 and 100),
constraint ck_labExam check(labExam between 0 and 100)
go
alter table stuMarks
add
constraint pk_marksId primary key(marksId),
constraint fk_stuNo foreign key(stuNo)
references stuInfo(stuNo)
on update no action --级联更新（无操作）
on delete cascade,--级联删除
constraint ck_writtenExam check(writtenExam between 0 and 100),
constraint ck_labExam check(labExam between 0 and 100)
go
--查看表约束
exec sp_helpconstraint stuMarks
exec sp_helpconstraint stuMarks
insert into stuMarks
values('s25301',80,65)
insert into stuMarks
values('s25302',87,95)
insert into stuMarks
values('s25303',83,69)
insert into stuMarks
values('s25304',70,74)
values('s25301',80,65)
insert into stuMarks
values('s25302',87,95)
insert into stuMarks
values('s25303',83,69)
insert into stuMarks
values('s25304',70,74)
--查看表中是否存在约束名为 ck%
select * from sysobjects where name like 'ck%'
--存在检测
if exists(select * from sysobjects where name = 'ck_stuAge')
alter table stuInfo
drop constraint ck_stuAge
go
--存在检测
if exists(select * from sysobjects where name = 'ck_stuAge')
alter table stuInfo
drop constraint ck_stuAge
go
--创建stuAge约束
alter table stuinfo
add
constraint ck_stuAge check (stuAge between 15 and 40)
go
alter table stuinfo
add
constraint ck_stuAge check (stuAge between 15 and 40)
go
本文转自9pc9com博客，原文链接： http://blog.51cto.com/215363/758802 如需转载请自行联系原作者