本文主要是介绍SQLServe_使用T-SQL语句创建数据库、创建表以及表的约束，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
if exists(select * from sysdatabases where name='school')
begindrop database school
end
gocreate database school
on primary
(name='school_data', --主数据文件的逻辑名称filename='F:\project\school_data.mdf', --主数据文件的物理名称及地址size=5mb, --主数据文件的初始大小maxsize=100mb, --主数据文件的最大值filegrowth=15% --主数据文件的增长率
)
log on
(name='school_log.ldf', --数据日志文件的逻辑名称filename='F:\project\school_log.mdf', --数据日志文件的物理名称及地址size=2mb, --数据日志文件的初始大小filegrowth=1mb --数据日志文件的增长速度
)
go--建表
use school
go
--1.年级表
if exists(select * from sysobjects where name='Grade')
begindrop table Grade
end
gocreate table Grade
(GradeId int Identity(1,1) not null,GradeName nvarchar(50) not null
)
go--2.学生表
--如果要创建的表已存在，那么就删除
if exists(select * from sysobjects where name='Student')
begindrop table Student
end
go
create table Student
(StudentNo nchar(8) not null,StudentName nvarchar(20) not null,LoginPwd nvarchar(20) not null,Sex nchar(1) not null,GradeId int not null,Phone nvarchar(20) not null,Address nvarchar(50) not null,BornDate nvarchar(50) not null,EMail nvarchar(50) not null
)--3.科目表
if exists(select * from sysobjects where name='Subject')
begindrop table Subject
end
gocreate table Subject
(SubjectId int identity(1,1) not null,SubjectName nvarchar(20) not null,ClassHour int not null,GradeId int not null
)--4.成绩表
if exists(select * from sysobjects where name='Result')
begindrop table Result
end
gocreate table Result
(Id int identity(1,1) not null,StudentNo nchar(8) not null,SubjectId int not null,StudentResult int not null, --0-100之间ExamDate datetime not null --默认为当前日期
)--约束
--1.年级表约束
alter table Grade
add constraint PK_GradeId primary key(GradeId) --主键约束
go
alter table Grade
add constraint UQ_GradeName unique (GradeName) --唯一约束
go
--2.学生表约束
alter table Student
add constraint PK_StudentNo primary key(StudentNo)
go
alter table Student
add constraint CK_StudentNocheck check(StudentNo like 'S20118[0-9][0-9]')--检查约束
go
alter table Student
add constraint CK_LoginPwd check (len(LoginPwd)>=6 and len(LoginPwd)<=12)
go
alter table Student
add constraint FK_Student_GradeId foreign key(GradeId) references Grade(GradeId)
go
alter table Student
add constraint CK_Sex check(sex in ('男','女'))
go
alter table Student
add constraint CK_Phone check(Phone like '1[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')
go
alter table Student
add constraint DF_Address default('学生宿舍') for Address
go
alter table Student
add constraint CK_BornDate check(BornDate>1990-1-1)
go
alter table Student
add constraint CK_Email check(Email like '%@%')
go--科目表约束
alter table Subject
add constraint PK_SubjectId primary key(SubjectId)
go
alter table Subject
add constraint CK_ClassHour check(ClassHour>0)
go
alter table Subject
add constraint FK_Subject_GradeId foreign key(GradeId) references Grade(GradeId)
go--成绩表约束
alter table Result
add constraint PK_Id primary key(Id)
go
alter table Result
add constraint FK_Result_StudentNo foreign key(StudentNo) references Student(StudentNo)
go
alter table Result
add constraint FK_Result_SujectId foreign key(SubjectId) references Subject(SubjectId)
go
这篇关于SQLServe_使用T-SQL语句创建数据库、创建表以及表的约束的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
