1 --指向当前要使用的数据库
2 use master
3 go
4 --判断当前数据库是否存在
5 if exists (select * from sysdatabases where name='StudentManager')
6 drop database StudentManager--删除数据库
7 go
8 --创建数据库
9 create database StudentManager
10 on primary
11 (
12 --数据库文件的逻辑名
13 name='StudentManager_data',
14 --数据库物理文件名（绝对路径）
15 filename='D:\DB\StudentManager_data.mdf',
16 --数据库文件初始大小
17 size=10MB,
18 --数据文件增长量
19 filegrowth=1MB
20 )
21 ,
22 (
23 name='StudentManager_data1',
24 --创建次要数据文件
25 filename='D:\DB\StudentManager_data1.ndf',
26 size=2MB,
27 filegrowth=1MB
28 )
29 --创建日志文件
30 log on
31 (
32 name='StudentManager_log',
33 filename='D:\DB\StudentManager_log.ldf',
34 size=2MB,
35 filegrowth=1MB
36 )
37 ,
38 (
39 name='StudentManager_log1',
40 filename='D:\DB\StudentManager_log1.ldf',
41 size=2MB,
42 filegrowth=1MB
43 )
44 go
45
46 --创建学员信息数据表
47 use StudentManager
48 go
49 if exists (select * from sysobjects where name='Students')
50 drop table Students
51 go
52 create table Students
53 (
54 StudentId int identity(100000,1) ,
55 StudentName varchar(20) not null,
56 Gender char(2) not null,
57 Birthday smalldatetime not null,
58 StudentIdNo numeric(18,0) not null,--身份证号
59 PhoneNumber varchar(50),
60 StudentAddress varchar(500),
61 ClassId int not null --班级外键
62 )
63 go
64 --创建班级表
65 if exists(select * from sysobjects where name='StudentClass')
66 drop table StudentClass
67 go
68 create table StudentClass
69 (
70 ClassId int primary key,
71 ClassName varchar(20) not null
72 )
73 go
74 --创建成绩表
75 if exists(select * from sysobjects where name='ScoreList')
76 drop table ScoreList
77 go
78 create table ScoreList
79 (
80 id int identity(1,1) primary key,
81 StudentId int not null,
82 CSharp int null,
83 SQLServerDB int null,
84 UpdateTime smalldatetime not null
85 )
86 go
87 --创建一卡通账户表
88 if exists(select * from sysobjects where name='CardAccount')
89 drop table CardAccount
90 go
91 create table CardAccount
92 (
93 StudentId int not null,
94 CurrentMoney money check(CurrentMoney>1) --当前余额(必须大于1元)
95 )
96 go
97 --创建管理员用户表
98 if exists(select * from sysobjects where name='Admins')
99 drop table Admins
100 create table Admins
101 (
102 LoginId int identity(1000,1) primary key,
103 LoginPwd varchar(200) not null,
104 AdminName varchar(20) not null
105 )
106 go
107 --创建数据表的各种约束
108 use StudentManager
109 go
110 --创建“主键”约束primary key
111 if exists(select * from sysobjects where name='pk_StudentId')
112 alter table Students drop constraint pk_StudentId
113
114 alter table Students
115 add constraint pk_StudentId primary key (StudentId)
116
117
118 --创建唯一约束unique
119 if exists(select * from sysobjects where name='uq_StudentIdNo')
120 alter table Students drop constraint uq_StudentIdNo
121 alter table Students
122 add constraint uq_StudentIdNo unique (StudentIdNo)
123
124 --创建身份证的长度检查约束
125 if exists(select * from sysobjects where name='ck_StudentIdNo')
126 alter table Students drop constraint ck_StudentIdNo
127 alter table Students
128 add constraint ck_StudentIdNo check (len(StudentIdNo)=18)
129
130 --创建默认约束
131 if exists(select * from sysobjects where name='df_StudentAddress')
132 alter table Students drop constraint df_StudentAddress
133 alter table Students
134 add constraint df_StudentAddress default ('地址不详' ) for StudentAddress
135
136 if exists(select * from sysobjects where name='df_UpdateTime')
137 alter table ScoreList drop constraint df_UpdateTime
138 alter table ScoreList
139 add constraint df_UpdateTime default (getdate() ) for UpdateTime
140
141 --创建外键约束
142 if exists(select * from sysobjects where name='fk_classId')
143 alter table Students drop constraint fk_classId
144 alter table Students
145 add constraint fk_classId foreign key (ClassId) references StudentClass(ClassId)
146
147 if exists(select * from sysobjects where name='fk_StudentId')
148 alter table ScoreList drop constraint fk_StudentId
149 alter table ScoreList
150 add constraint fk_StudentId foreign key(StudentId) references Students(StudentId)
151
152 if exists(select * from sysobjects where name='fk_CardStudentId')
153 alter table CardAccount drop constraint fk_CardStudentId
154 alter table CardAccount
155 add constraint fk_CardStudentId foreign key(StudentId) references Students(StudentId)
156
157 -------------------------------------------插入数据--------------------------------------
158 use StudentManager
159 go
160
161 --插入班级数据
162 insert into StudentClass(ClassId,ClassName) values(1,'软件1班')
163 insert into StudentClass(ClassId,ClassName) values(2,'软件2班')
164 insert into StudentClass(ClassId,ClassName) values(3,'计算机3班')
165 insert into StudentClass(ClassId,ClassName) values(4,'计算机4班')
166
167 --插入学员信息
168 insert into Students (StudentName,Gender,Birthday,StudentIdNo,PhoneNumber,StudentAddress,ClassId)
169 values('王小虎','男','1989-08-07',120223198908071111,'022-22222222','天津市南开区红磡公寓5-5-102',1)
170 insert into Students (StudentName,Gender,Birthday,StudentIdNo,PhoneNumber,StudentAddress,ClassId)
171 values('贺小张','女','1989-05-06',120223198905062426,'022-33333333','天津市河北区王串场58号',2)
172 insert into Students (StudentName,Gender,Birthday,StudentIdNo,PhoneNumber,StudentAddress,ClassId)
173 values('马小李','男','1990-02-07',120223199002078915,'022-44444444','天津市红桥区丁字沽曙光路79号',4)
174 insert into Students (StudentName,Gender,Birthday,StudentIdNo,PhoneNumber,StudentAddress,ClassId)
175 values('冯小强','女','1987-05-12',130223198705125167,'022-55555555',default,2)
176 insert into Students (StudentName,Gender,Birthday,StudentIdNo,PhoneNumber,StudentAddress,ClassId)
177 values('杜小丽','女','1986-05-08',130223198605081528,'022-66666666','河北衡水路北道69号',1)
178 insert into Students (StudentName,Gender,Birthday,StudentIdNo,PhoneNumber,StudentAddress,ClassId)
179 values('王俊桥','男','1987-07-18',130223198707182235,'022-77777777',default,2)
180 insert into Students (StudentName,Gender,Birthday,StudentIdNo,PhoneNumber,StudentAddress,ClassId)
181 values('张永利','男','1988-09-28',130223198909282235,'022-88888888','河北保定市风华道12号',3)
182 insert into Students (StudentName,Gender,Birthday,StudentIdNo,PhoneNumber,StudentAddress,ClassId)
183 values('李铭','男','1987-01-18',130223198701182257,'022-99999999','河北邢台市幸福路5号',1)
184 insert into Students (StudentName,Gender,Birthday,StudentIdNo,PhoneNumber,StudentAddress,ClassId)
185 values('宁俊燕','女','1987-06-15',130223198706152211,'022-11111111',default,3)
186 insert into Students (StudentName,Gender,Birthday,StudentIdNo,PhoneNumber,StudentAddress,ClassId)
187 values('刘玲玲','女','1989-08-19',130223198908192235,'022-11111222',default,4)
188 insert into Students (StudentName,Gender,Birthday,StudentIdNo,PhoneNumber,StudentAddress,ClassId)
189 values('赵雪','女','1986-06-16',130223198606162215,'022-11111111',default,3)
190 insert into Students (StudentName,Gender,Birthday,StudentIdNo,PhoneNumber,StudentAddress,ClassId)
191 values('刘阳','男','1989-09-19',130223198909192235,'022-11111222',default,4)
192 insert into Students (StudentName,Gender,Birthday,StudentIdNo,PhoneNumber,StudentAddress,ClassId)
193 values('秦双玲','女','1990-08-19',130223199008192235,'022-11111222',default,1)
194 insert into Students (StudentName,Gender,Birthday,StudentIdNo,PhoneNumber,StudentAddress,ClassId)
195 values('杜超超','男','1988-05-16',130223198805162217,'022-11111111',default,4)
196 insert into Students (StudentName,Gender,Birthday,StudentIdNo,PhoneNumber,StudentAddress,ClassId)
197 values('刘大国','男','1989-02-12',130223198902122237,'022-11111222',default,2)
198 insert into Students (StudentName,Gender,Birthday,StudentIdNo,PhoneNumber,StudentAddress,ClassId)
199 values('刘军','男','1988-03-12',130223198803122237,'022-11111222',default,3)
200
201
202
203 --插入成绩信息
204 insert into ScoreList (StudentId,CSharp,SQLServerDB)values(100000,60,78)
205 insert into ScoreList (StudentId,CSharp,SQLServerDB)values(100001,55,88)
206 insert into ScoreList (StudentId,CSharp,SQLServerDB)values(100002,90,58)
207 insert into ScoreList (StudentId,CSharp,SQLServerDB)values(100003,88,75)
208
209 insert into ScoreList (StudentId,CSharp,SQLServerDB)values(100004,62,88)
210 insert into ScoreList (StudentId,CSharp,SQLServerDB)values(100006,52,80)
211 insert into ScoreList (StudentId,CSharp,SQLServerDB)values(100007,91,66)
212 insert into ScoreList (StudentId,CSharp,SQLServerDB)values(100009,78,35)
213
214 insert into ScoreList (StudentId,CSharp,SQLServerDB)values(100011,72,80)
215 insert into ScoreList (StudentId,CSharp,SQLServerDB)values(100012,95,92)
216 insert into ScoreList (StudentId,CSharp,SQLServerDB)values(100013,76,39)
217 insert into ScoreList (StudentId,CSharp,SQLServerDB)values(100014,68,65)
218 insert into ScoreList (StudentId,CSharp,SQLServerDB)values(100015,50,85)
219
220 --插入学员一卡通账户数据
221 insert into CardAccount(StudentId,CurrentMoney)values(100001,1000)
222 insert into CardAccount(StudentId,CurrentMoney)values(100002,1500)
223 insert into CardAccount(StudentId,CurrentMoney)values(100003,1600)
224 insert into CardAccount(StudentId,CurrentMoney)values(100004,800)
225 insert into CardAccount(StudentId,CurrentMoney)values(100005,1200)
226
227
228 --插入管理员信息(初始密码：123456)
229 insert into Admins (LoginPwd,AdminName) values('123456','王晓宏')
230 insert into Admins (LoginPwd,AdminName) values('123456','李小君')
231 insert into Admins (LoginPwd,AdminName) values('123456','赵二宝')
232 insert into Admins (LoginPwd,AdminName) values('123456','范晓彤')
233
234
235 --显示学员信息和班级信息
236 select * from Students
237 select * from StudentClass
238 select * from ScoreList
239 select * from CardAccount
240 select * from Admins
