--指向当前要使用的数据库use mastergo--判断当前数据库是否存在if exists (select * from sysdatabases where name='SMDB')drop database SMDB --删除数据库go--创建数据库create database SMDBon primary(--数据库文件的逻辑名 name='SMDB_data', --数据库物理文件名（绝对路径） filename='D:\DB\SMDB_data.mdf', --数据库文件初始大小 size=10MB, --数据文件增长量 filegrowth=1MB)--创建日志文件log on( name='SMDB_log', filename='D:\DB\SMDB_log.ldf', size=2MB, filegrowth=1MB)go--创建学员信息数据表use SMDBgoif exists (select * from sysobjects where name='Students')drop table Studentsgocreate table Students( StudentId int identity(100000,1) , StudentName varchar(20) not null, Gender char(2) not null, Birthday smalldatetime not null, StudentIdNo numeric(18,0) not null,--身份证号 StuImage text null,--学员照片 Age int not null, PhoneNumber varchar(50), StudentAddress varchar(500), ClassId int not null --班级外键)go--创建班级表if exists(select * from sysobjects where name='StudentClass')drop table StudentClassgocreate table StudentClass(ClassId int primary key, ClassName varchar(20) not null)go--创建成绩表if exists(select * from sysobjects where name='ScoreList')drop table ScoreListgocreate table ScoreList( Id int identity(1,1) primary key, StudentId int not null, CSharp int null, SQLServerDB int null, UpdateTime smalldatetime not null)go--创建管理员用户表if exists(select * from sysobjects where name='Admins')drop table Adminscreate table Admins(LoginId int identity(1000,1) primary key, LoginPwd varchar(20) not null, AdminName varchar(20) not null)go--创建数据表的各种约束use SMDBgo--创建“主键”约束primary keyif exists(select * from sysobjects where name='pk_StudentId')alter table Students drop constraint pk_StudentId
alter table Studentsadd constraint pk_StudentId primary key (StudentId)
--创建检查约束checkif exists(select * from sysobjects where name='ck_Age')
