--创建数据库
use master
go
if exists(select * from sysdatabases where name = 'StudentManageDB')
drop database StudentManageDB
create database StudentManageDB
on primary
(
name = 'StudentManageDB',
filename = 'D:\DB\StudentManageDB.mdf',
size=5MB,
filegrowth=5MB
),
(
name = 'StudentManageDB1',
filename = 'D:\DB\StudentManageDB.ndf',
size=5MB,
filegrowth=5MB
)
log on
(
name = 'StudentManageDBLog',
filename = 'D:\DB\StudentManageDB.ldf',
size=5MB,
filegrowth=5MB
)
--创建数据学生表
use StudentManageDB
go
if exists(select * from sysobjects where name = 'Students')
drop table Students
create table Students
(
StudentId int identity(10000,1),
StudentName nvarchar(20) not null,
Gender nvarchar(2) not null,
Age int not null,
Birthday datetime not null,
StudentIdNo numeric(18,0) not null,
ClassId int not null
)
go
--创建班级表
go
if exists (select * from sysobjects where name = 'StudentClass')
drop table StudentClass
go
create table StudentClass
(
ClassId int primary key,
ClassName nvarchar(20) not null
)
--创建成绩表
go
if exists (select * from sysobjects where name = 'Score')
drop table Score
go
create table Score
(
StudentId int primary key,
CSharp int null,
SQLServerDB int null,
UpdateTime datetime not null
)
go
--创建管理员表
use StudentManageDB
go
if exists (select * from sysobjects where name = 'Admins')
drop table Admins
go
create table Admins
(
LoginId int primary key,
Loginpwd int not null,
AdminName nvarchar(20) not null
)
go
--创建主键约束
if exists(select * from sysobjects where name = 'PK_StudentId')
alter table Students drop constraint PK_StudentId
alter table Students add constraint PK_StudentId primary key(StudentId)
go
--创建唯一约束
if exists(select * from sysobjects where name = 'UK_StudentIdNo')
alter table Students drop constraint UK_StudentIdNo
alter table Students add constraint UK_StudentIdNo unique(StudentIdNo)
--创建Check约束
if exists(select * from sysobjects where name = 'CK_Age')
alter table Students drop constraint CK_Age
alter table Students add constraint CK_Age check(Age between 18 and 25)
--创建外键约束
if exists(select * from sysobjects where name = 'FK_ClassId')
alter table Students drop constraint FK_ClassId
alter table Students add constraint FK_ClassId foreign key(ClassId) references StudentClass(ClassId)