1 create DATABASE stu 2 ON 3 ( 4 name=‘stu.mdf‘, 5 filename=‘F:\胡浴东\数据库\stu数据库\stu.mdf‘, 6 size=5, 7 filegrowth=10 8 ) 9 log ON 10 ( 11 name=‘stu_log.ldf‘, 12 filename=‘F:\胡浴东\数据库\stu数据库\stu_log.ldf‘, 13 size=5, 14 filegrowth=10 15 ) 16 go 17 18 USE [stu] 19 GO 20 /****** Object: Table [dbo].[classinfo] Script Date: 2015/5/11 10:14:54 ******/ 21 SET ANSI_NULLS ON 22 GO 23 SET QUOTED_IDENTIFIER ON 24 GO 25 SET ANSI_PADDING ON 26 GO 27 CREATE TABLE [dbo].[classinfo]( 28 [c_id] [int] IDENTITY(1,1) NOT NULL, 29 [c_dep_id] [int] NULL, 30 [c_name] [varchar](20) NULL, 31 PRIMARY KEY CLUSTERED 32 ( 33 [c_id] ASC 34 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] 35 ) ON [PRIMARY] 36 37 GO 38 SET ANSI_PADDING OFF 39 GO 40 /****** Object: Table [dbo].[department] Script Date: 2015/5/11 10:14:54 ******/ 41 SET ANSI_NULLS ON 42 GO 43 SET QUOTED_IDENTIFIER ON 44 GO 45 SET ANSI_PADDING ON 46 GO 47 CREATE TABLE [dbo].[department]( 48 [dep_id] [int] IDENTITY(1,1) NOT NULL, 49 [dep_name] [varchar](20) NULL, 50 PRIMARY KEY CLUSTERED 51 ( 52 [dep_id] ASC 53 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] 54 ) ON [PRIMARY] 55 56 GO 57 SET ANSI_PADDING OFF 58 GO 59 /****** Object: Table [dbo].[logins] Script Date: 2015/5/11 10:14:54 ******/ 60 SET ANSI_NULLS ON 61 GO 62 SET QUOTED_IDENTIFIER ON 63 GO 64 SET ANSI_PADDING ON 65 GO 66 CREATE TABLE [dbo].[logins]( 67 [l_no] [varchar](10) NOT NULL, 68 [l_pwd] [varchar](10) NOT NULL, 69 [l_question] [varchar](30) NOT NULL, 70 [l_answer] [varchar](30) NOT NULL, 71 PRIMARY KEY CLUSTERED 72 ( 73 [l_no] ASC 74 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] 75 ) ON [PRIMARY] 76 77 GO 78 SET ANSI_PADDING OFF 79 GO 80 /****** Object: Table [dbo].[member] Script Date: 2015/5/11 10:14:54 ******/ 81 SET ANSI_NULLS ON 82 GO 83 SET QUOTED_IDENTIFIER ON 84 GO 85 SET ANSI_PADDING ON 86 GO 87 CREATE TABLE [dbo].[member]( 88 [username] [varchar](20) NOT NULL, 89 [pwd] [varchar](6) NOT NULL, 90 [name] [varchar](20) NULL, 91 [gender] [char](2) NULL, 92 [age] [varchar](10) NULL, 93 [enjoy] [varchar](50) NULL, 94 PRIMARY KEY CLUSTERED 95 ( 96 [username] ASC 97 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] 98 ) ON [PRIMARY] 99 100 GO 101 SET ANSI_PADDING OFF 102 GO 103 /****** Object: Table [dbo].[result] Script Date: 2015/5/11 10:14:54 ******/ 104 SET ANSI_NULLS ON 105 GO 106 SET QUOTED_IDENTIFIER ON 107 GO 108 CREATE TABLE [dbo].[result]( 109 [r_id] [int] IDENTITY(1,1) NOT NULL, 110 [r_sub_id] [int] NULL, 111 [r_stu_id] [int] NULL, 112 [r_result] [int] NULL, 113 PRIMARY KEY CLUSTERED 114 ( 115 [r_id] ASC 116 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] 117 ) ON [PRIMARY] 118 119 GO 120 /****** Object: Table [dbo].[studentinfo] Script Date: 2015/5/11 10:14:54 ******/ 121 SET ANSI_NULLS ON 122 GO 123 SET QUOTED_IDENTIFIER ON 124 GO 125 SET ANSI_PADDING ON 126 GO 127 CREATE TABLE [dbo].[studentinfo]( 128 [stu_id] [int] IDENTITY(1,1) NOT NULL, 129 [stu_name] [varchar](20) NOT NULL, 130 [stu_age] [int] NULL, 131 [stu_c_id] [int] NULL, 132 [stu_gender] [char](2) NULL, 133 [stu_address] [varchar](30) NULL, 134 PRIMARY KEY CLUSTERED 135 ( 136 [stu_id] ASC 137 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] 138 ) ON [PRIMARY] 139 140 GO 141 SET ANSI_PADDING OFF 142 GO 143 /****** Object: Table [dbo].[subject] Script Date: 2015/5/11 10:14:54 ******/ 144 SET ANSI_NULLS ON 145 GO 146 SET QUOTED_IDENTIFIER ON 147 GO 148 SET ANSI_PADDING ON 149 GO 150 CREATE TABLE [dbo].[subject]( 151 [sub_id] [int] IDENTITY(1,1) NOT NULL, 152 [sub_name] [varchar](20) NULL, 153 PRIMARY KEY CLUSTERED 154 ( 155 [sub_id] ASC 156 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] 157 ) ON [PRIMARY] 158 159 GO 160 SET ANSI_PADDING OFF 161 GO 162 /****** Object: View [dbo].[v_cool] Script Date: 2015/5/11 10:14:54 ******/ 163 SET ANSI_NULLS ON 164 GO 165 SET QUOTED_IDENTIFIER ON 166 GO 167 create view [dbo].[v_cool] 168 as 169 select s.stu_id,d.dep_name,c.c_name,s.stu_name, 170 s.stu_age,s.stu_gender,s.stu_address 171 from studentinfo s,classinfo c,department d 172 where s.stu_c_id=c.c_id and c.c_dep_id=d.dep_id 173 GO 174 /****** Object: View [dbo].[v_ggg] Script Date: 2015/5/11 10:14:54 ******/ 175 SET ANSI_NULLS ON 176 GO 177 SET QUOTED_IDENTIFIER ON 178 GO 179 create view [dbo].[v_ggg] 180 as 181 select s.stu_id 学员编号,s.stu_name 学员姓名, 182 s.stu_age 年龄,c.c_name 班级,s.stu_gender 性别, 183 s.stu_address 住址 184 from studentinfo s,classinfo c 185 where s.stu_c_id=c.c_id 186 GO 187 /****** Object: View [dbo].[View_cc] Script Date: 2015/5/11 10:14:54 ******/ 188 SET ANSI_NULLS ON 189 GO 190 SET QUOTED_IDENTIFIER ON 191 GO 192 CREATE VIEW [dbo].[View_cc] 193 AS 194 SELECT dbo.classinfo.c_name, dbo.department.dep_name, dbo.studentinfo.stu_age, dbo.studentinfo.stu_name, 195 dbo.studentinfo.stu_gender, dbo.studentinfo.stu_address 196 FROM dbo.classinfo INNER JOIN 197 dbo.department ON dbo.classinfo.c_dep_id = dbo.department.dep_id INNER JOIN 198 dbo.studentinfo ON dbo.classinfo.c_id = dbo.studentinfo.stu_c_id 199 200 GO 201 SET IDENTITY_INSERT [dbo].[classinfo] ON 202 203 INSERT [dbo].[classinfo] ([c_id], [c_dep_id], [c_name]) VALUES (1, 1, N‘2014579‘) 204 INSERT [dbo].[classinfo] ([c_id], [c_dep_id], [c_name]) VALUES (2, 2, N‘2014789‘) 205 INSERT [dbo].[classinfo] ([c_id], [c_dep_id], [c_name]) VALUES (3, 3, N‘2014272‘) 206 INSERT [dbo].[classinfo] ([c_id], [c_dep_id], [c_name]) VALUES (4, 3, N‘2014274‘) 207 SET IDENTITY_INSERT [dbo].[classinfo] OFF 208 SET IDENTITY_INSERT [dbo].[department] ON 209 210 INSERT [dbo].[department] ([dep_id], [dep_name]) VALUES (1, N‘环化系‘) 211 INSERT [dbo].[department] ([dep_id], [dep_name]) VALUES (2, N‘汽车系‘) 212 INSERT [dbo].[department] ([dep_id], [dep_name]) VALUES (3, N‘信息智能系‘) 213 SET IDENTITY_INSERT [dbo].[department] OFF 214 INSERT [dbo].[logins] ([l_no], [l_pwd], [l_question], [l_answer]) VALUES (N‘cool‘, N‘123‘, N‘111‘, N‘222‘) 215 INSERT [dbo].[logins] ([l_no], [l_pwd], [l_question], [l_answer]) VALUES (N‘jack‘, N‘123‘, N‘111‘, N‘111‘) 216 INSERT [dbo].[logins] ([l_no], [l_pwd], [l_question], [l_answer]) VALUES (N‘徐凯伦‘, N‘123‘, N‘124‘, N‘456‘) 217 INSERT [dbo].[member] ([username], [pwd], [name], [gender], [age], [enjoy]) VALUES (N‘tom‘, N‘123‘, N‘ss‘, N‘男‘, N‘90后‘, N‘美女 帅哥 ‘) 218 INSERT [dbo].[member] ([username], [pwd], [name], [gender], [age], [enjoy]) VALUES (N‘tom1‘, N‘123‘, N‘ss‘, N‘男‘, N‘90后‘, N‘美女 帅哥 ‘) 219 SET IDENTITY_INSERT [dbo].[result] ON 220 221 INSERT [dbo].[result] ([r_id], [r_sub_id], [r_stu_id], [r_result]) VALUES (1, 1, 1, 88) 222 INSERT [dbo].[result] ([r_id], [r_sub_id], [r_stu_id], [r_result]) VALUES (2, 1, 2, 91) 223 INSERT [dbo].[result] ([r_id], [r_sub_id], [r_stu_id], [r_result]) VALUES (3, 1, 3, 90) 224 INSERT [dbo].[result] ([r_id], [r_sub_id], [r_stu_id], [r_result]) VALUES (4, 1, 6, 87) 225 INSERT [dbo].[result] ([r_id], [r_sub_id], [r_stu_id], [r_result]) VALUES (5, 2, 1, 90) 226 INSERT [dbo].[result] ([r_id], [r_sub_id], [r_stu_id], [r_result]) VALUES (6, 2, 2, 88) 227 INSERT [dbo].[result] ([r_id], [r_sub_id], [r_stu_id], [r_result]) VALUES (7, 2, 3, 92) 228 INSERT [dbo].[result] ([r_id], [r_sub_id], [r_stu_id], [r_result]) VALUES (8, 2, 6, 92) 229 INSERT [dbo].[result] ([r_id], [r_sub_id], [r_stu_id], [r_result]) VALUES (9, 3, 1, 89) 230 INSERT [dbo].[result] ([r_id], [r_sub_id], [r_stu_id], [r_result]) VALUES (10, 3, 2, 92) 231 INSERT [dbo].[result] ([r_id], [r_sub_id], [r_stu_id], [r_result]) VALUES (11, 3, 3, 94) 232 INSERT [dbo].[result] ([r_id], [r_sub_id], [r_stu_id], [r_result]) VALUES (12, 3, 6, 95) 233 SET IDENTITY_INSERT [dbo].[result] OFF 234 SET IDENTITY_INSERT [dbo].[studentinfo] ON 235 236 INSERT [dbo].[studentinfo] ([stu_id], [stu_name], [stu_age], [stu_c_id], [stu_gender], [stu_address]) VALUES (1, N‘浩然刘‘, 25, 3, N‘男‘, N‘湖北宜昌‘) 237 INSERT [dbo].[studentinfo] ([stu_id], [stu_name], [stu_age], [stu_c_id], [stu_gender], [stu_address]) VALUES (2, N‘柯梦‘, 20, 4, N‘女‘, N‘湖北十堰‘) 238 INSERT [dbo].[studentinfo] ([stu_id], [stu_name], [stu_age], [stu_c_id], [stu_gender], [stu_address]) VALUES (3, N‘林智健‘, 22, 3, N‘男‘, N‘湖北天门‘) 239 INSERT [dbo].[studentinfo] ([stu_id], [stu_name], [stu_age], [stu_c_id], [stu_gender], [stu_address]) VALUES (4, N‘刘畅‘, 19, 1, N‘男‘, N‘湖北十堰‘) 240 INSERT [dbo].[studentinfo] ([stu_id], [stu_name], [stu_age], [stu_c_id], [stu_gender], [stu_address]) VALUES (6, N‘齐白石‘, 20, 4, N‘男‘, N‘湖北襄樊‘) 241 INSERT [dbo].[studentinfo] ([stu_id], [stu_name], [stu_age], [stu_c_id], [stu_gender], [stu_address]) VALUES (7, N‘刘佳‘, 22, 4, N‘男‘, N‘湖北十堰‘) 242 INSERT [dbo].[studentinfo] ([stu_id], [stu_name], [stu_age], [stu_c_id], [stu_gender], [stu_address]) VALUES (9, N‘张馨文‘, 19, 3, N‘男‘, N‘甘肃‘) 243 SET IDENTITY_INSERT [dbo].[studentinfo] OFF 244 SET IDENTITY_INSERT [dbo].[subject] ON 245 246 INSERT [dbo].[subject] ([sub_id], [sub_name]) VALUES (1, N‘C#‘) 247 INSERT [dbo].[subject] ([sub_id], [sub_name]) VALUES (2, N‘html‘) 248 INSERT [dbo].[subject] ([sub_id], [sub_name]) VALUES (3, N‘sqlserver‘) 249 INSERT [dbo].[subject] ([sub_id], [sub_name]) VALUES (4, N‘环境监测‘) 250 INSERT [dbo].[subject] ([sub_id], [sub_name]) VALUES (5, N‘汽车维修‘) 251 SET IDENTITY_INSERT [dbo].[subject] OFF 252 SET ANSI_PADDING ON 253 254 GO 255 /****** Object: Index [UQ__classinf__29397C81D09B43E9] Script Date: 2015/5/11 10:14:54 ******/ 256 ALTER TABLE [dbo].[classinfo] ADD UNIQUE NONCLUSTERED 257 ( 258 [c_name] ASC 259 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] 260 GO 261 SET ANSI_PADDING ON 262 263 GO 264 /****** Object: Index [UQ__departme__7BE54950D8CB39D9] Script Date: 2015/5/11 10:14:54 ******/ 265 ALTER TABLE [dbo].[department] ADD UNIQUE NONCLUSTERED 266 ( 267 [dep_name] ASC 268 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] 269 GO 270 SET ANSI_PADDING ON 271 272 GO 273 /****** Object: Index [UQ__subject__99BD2209CE37D31B] Script Date: 2015/5/11 10:14:54 ******/ 274 ALTER TABLE [dbo].[subject] ADD UNIQUE NONCLUSTERED 275 ( 276 [sub_name] ASC 277 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] 278 GO 279 ALTER TABLE [dbo].[studentinfo] ADD DEFAULT (‘湖北十堰‘) FOR [stu_address] 280 GO 281 ALTER TABLE [dbo].[classinfo] WITH CHECK ADD FOREIGN KEY([c_dep_id]) 282 REFERENCES [dbo].[department] ([dep_id]) 283 GO 284 ALTER TABLE [dbo].[result] WITH CHECK ADD FOREIGN KEY([r_stu_id]) 285 REFERENCES [dbo].[studentinfo] ([stu_id]) 286 GO 287 ALTER TABLE [dbo].[result] WITH CHECK ADD FOREIGN KEY([r_sub_id]) 288 REFERENCES [dbo].[subject] ([sub_id]) 289 GO 290 ALTER TABLE [dbo].[studentinfo] WITH CHECK ADD FOREIGN KEY([stu_c_id]) 291 REFERENCES [dbo].[classinfo] ([c_id]) 292 GO 293 ALTER TABLE [dbo].[member] WITH CHECK ADD CHECK (([gender]=‘女‘ OR [gender]=‘男‘)) 294 GO 295 ALTER TABLE [dbo].[studentinfo] WITH CHECK ADD CHECK (([stu_age]>=(18) AND [stu_age]<=(30))) 296 GO 297 ALTER TABLE [dbo].[studentinfo] WITH CHECK ADD CHECK (([stu_gender]=‘女‘ OR [stu_gender]=‘男‘)) 298 GO 299 EXEC sys.sp_addextendedproperty @name=N‘MS_DiagramPane1‘, @value=N‘[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00] 300 Begin DesignProperties = 301 Begin PaneConfigurations = 302 Begin PaneConfiguration = 0 303 NumPanes = 4 304 Configuration = "(H (1[11] 4[56] 2[16] 3) )" 305 End 306 Begin PaneConfiguration = 1 307 NumPanes = 3 308 Configuration = "(H (1 [50] 4 [25] 3))" 309 End 310 Begin PaneConfiguration = 2 311 NumPanes = 3 312 Configuration = "(H (1 [50] 2 [25] 3))" 313 End 314 Begin PaneConfiguration = 3 315 NumPanes = 3 316 Configuration = "(H (4 [30] 2 [40] 3))" 317 End 318 Begin PaneConfiguration = 4 319 NumPanes = 2 320 Configuration = "(H (1 [56] 3))" 321 End 322 Begin PaneConfiguration = 5 323 NumPanes = 2 324 Configuration = "(H (2 [66] 3))" 325 End 326 Begin PaneConfiguration = 6 327 NumPanes = 2 328 Configuration = "(H (4 [50] 3))" 329 End 330 Begin PaneConfiguration = 7 331 NumPanes = 1 332 Configuration = "(V (3))" 333 End 334 Begin PaneConfiguration = 8 335 NumPanes = 3 336 Configuration = "(H (1[56] 4[18] 2) )" 337 End 338 Begin PaneConfiguration = 9 339 NumPanes = 2 340 Configuration = "(H (1 [75] 4))" 341 End 342 Begin PaneConfiguration = 10 343 NumPanes = 2 344 Configuration = "(H (1[66] 2) )" 345 End 346 Begin PaneConfiguration = 11 347 NumPanes = 2 348 Configuration = "(H (4 [60] 2))" 349 End 350 Begin PaneConfiguration = 12 351 NumPanes = 1 352 Configuration = "(H (1) )" 353 End 354 Begin PaneConfiguration = 13 355 NumPanes = 1 356 Configuration = "(V (4))" 357 End 358 Begin PaneConfiguration = 14 359 NumPanes = 1 360 Configuration = "(V (2))" 361 End 362 ActivePaneConfig = 0 363 End 364 Begin DiagramPane = 365 Begin Origin = 366 Top = -96 367 Left = 0 368 End 369 Begin Tables = 370 Begin Table = "classinfo" 371 Begin Extent = 372 Top = 6 373 Left = 38 374 Bottom = 126 375 Right = 180 376 End 377 DisplayFlags = 280 378 TopColumn = 0 379 End 380 Begin Table = "department" 381 Begin Extent = 382 Top = 6 383 Left = 218 384 Bottom = 107 385 Right = 367 386 End 387 DisplayFlags = 280 388 TopColumn = 0 389 End 390 Begin Table = "studentinfo" 391 Begin Extent = 392 Top = 108 393 Left = 218 394 Bottom = 247 395 Right = 376 396 End 397 DisplayFlags = 280 398 TopColumn = 2 399 End 400 End 401 End 402 Begin SQLPane = 403 End 404 Begin DataPane = 405 Begin ParameterDefaults = "" 406 End 407 End 408 Begin CriteriaPane = 409 Begin ColumnWidths = 11 410 Column = 1440 411 Alias = 900 412 Table = 1170 413 Output = 720 414 Append = 1400 415 NewValue = 1170 416 SortType = 1350 417 SortOrder = 1410 418 GroupBy = 1350 419 Filter = 1350 420 Or = 1350 421 Or = 1350 422 Or = 1350 423 End 424 End 425 End 426 ‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘VIEW‘,@level1name=N‘View_cc‘ 427 GO 428 EXEC sys.sp_addextendedproperty @name=N‘MS_DiagramPaneCount‘, @value=1 , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘VIEW‘,@level1name=N‘View_cc‘ 429 GO