use master
go
--判断当前数据库是否存在ifexists(select*from sysdatabases where name ='StudentManageDB')dropdatabase StudentManageDB
go
--创建数据库createdatabase StudentManageDB
onprimary(
name ='StudentManageDB_data',--数据库逻辑文件名
filename ='D:\DB\StudentManageDB_data.mdf',--数据库物理文件名
size =10 MB,--数据库文件初试大小
filegrowth =50%--数据文件增长量),(
name ='StudentManageDB_data1',
filename ='D:\DB\StudentManageDB_data1.ndf',
size =10 MB,--数据库文件初试大小
filegrowth =50%--数据文件增长量)--创建日志文件
log on(
name ='StudentManageDB_log',--数据库逻辑文件名
filename ='D:\DB\StudentManageDB_log.Idf',--数据库物理文件名
size =10 MB,--数据库文件初试大小
filegrowth =2MB --数据文件增长量),(
name ='StudentManageDB_log1',--数据库逻辑文件名
filename ='D:\DB\StudentManageDB_log1.Idf',--数据库物理文件名
size =10 MB,--数据库文件初试大小
filegrowth =2MB --数据文件增长量)
go
use StudentManageDB
go --指向数据库ifexists(select*from sysobjects where name='Students')droptable Students
go
createtable Students
(
StudentId intidentity(10000,1),--学号,从10000开始,每次递增1
StudentName varchar(20)notnull,--姓名
Gender char(20)notnull,--性别
Birthday datetimenotnull,--出生日期
StudentIdNo numeric(18,0)notnull,--18位
Age intnotnull,--年龄
PhoneNumber varchar(50),--50个字符
StudentAdress varchar(500),
ClassId intnotnull--班级外键)
go
--select * from Students--创建班级表ifexists(select*from sysobjects where name ='StudentClass')droptable StudentClass
go
createtable StudentClass
(
ClassId intprimarykey,--建立主键
ClassName varchar(20)notnull)
go
--创建成绩表ifexists(select*from sysobjects where name ='ScoreList')droptable ScoreList
go
createtable ScoreList
(
Id intidentity(1,1)primarykey,--建立主键 identity自增
StudentId intnotnull,--学号外键
Csharp intnull,
SQLServer intnull,
UpdateTime datetimenotnull)
go
--创建管理员表ifexists(select*from sysobjects where name ='Admins')droptable Admins
go
createtable Admins
(
LoginId intidentity(1000,1)primarykey,
LoginPwd varchar(20)notnull,
AdminName varchar(20)notnull)
go
--select * from Students