优化MySchool 数据库设计 第二章 上机
来源:互联网 发布:licecap for mac 编辑:程序博客网 时间:2024/06/05 04:57
--创建数据库MySchoolCREATE DATABASE MySchool ON ( NAME='bankDB_data', FILENAME='d:\MySchool_data.mdf', SIZE=3mb, FILEGROWTH=15% ) LOG ON ( NAME= 'MySchool_log', FILENAME='d:\MySchool_log.ldf', SIZE=3mb, FILEGROWTH=15% )GOUSE [MySchool]GO/****** Object: Table [dbo].[Grade] Script Date: 08/06/2014 15:03:17 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[Grade]([GradeId] [int] IDENTITY(1,1) NOT NULL,[GradeName] [nvarchar](50) NOT NULL, CONSTRAINT [PK_Grade_1] PRIMARY KEY CLUSTERED ([GradeId] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY], CONSTRAINT [IX_Grade_GradeName] UNIQUE NONCLUSTERED ([GradeId] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOSET IDENTITY_INSERT [dbo].[Grade] ONINSERT [dbo].[Grade] ([GradeId], [GradeName]) VALUES (1, N'S1')INSERT [dbo].[Grade] ([GradeId], [GradeName]) VALUES (2, N'S2')INSERT [dbo].[Grade] ([GradeId], [GradeName]) VALUES (3, N'Y2')INSERT [dbo].[Grade] ([GradeId], [GradeName]) VALUES (4, N'精英班')INSERT [dbo].[Grade] ([GradeId], [GradeName]) VALUES (11, N'zhanan')INSERT [dbo].[Grade] ([GradeId], [GradeName]) VALUES (12, N'S1288无敌班')INSERT [dbo].[Grade] ([GradeId], [GradeName]) VALUES (13, N'高一二班')INSERT [dbo].[Grade] ([GradeId], [GradeName]) VALUES (14, N'高一三班')INSERT [dbo].[Grade] ([GradeId], [GradeName]) VALUES (15, N'高一一班')INSERT [dbo].[Grade] ([GradeId], [GradeName]) VALUES (16, N'高一1班')INSERT [dbo].[Grade] ([GradeId], [GradeName]) VALUES (17, N'高一2班')INSERT [dbo].[Grade] ([GradeId], [GradeName]) VALUES (18, N'高一3班')INSERT [dbo].[Grade] ([GradeId], [GradeName]) VALUES (19, N'aa')INSERT [dbo].[Grade] ([GradeId], [GradeName]) VALUES (20, N'mine')INSERT [dbo].[Grade] ([GradeId], [GradeName]) VALUES (21, N'mine')INSERT [dbo].[Grade] ([GradeId], [GradeName]) VALUES (22, N'mine')INSERT [dbo].[Grade] ([GradeId], [GradeName]) VALUES (23, N'mine')INSERT [dbo].[Grade] ([GradeId], [GradeName]) VALUES (26, N'无敌S2214')INSERT [dbo].[Grade] ([GradeId], [GradeName]) VALUES (27, N'无敌S2214')INSERT [dbo].[Grade] ([GradeId], [GradeName]) VALUES (28, N'好人2214')INSERT [dbo].[Grade] ([GradeId], [GradeName]) VALUES (30, N'aa')INSERT [dbo].[Grade] ([GradeId], [GradeName]) VALUES (31, N'bb')INSERT [dbo].[Grade] ([GradeId], [GradeName]) VALUES (32, N'cc')INSERT [dbo].[Grade] ([GradeId], [GradeName]) VALUES (33, N'dd')INSERT [dbo].[Grade] ([GradeId], [GradeName]) VALUES (34, N'S2216')INSERT [dbo].[Grade] ([GradeId], [GradeName]) VALUES (48, N'S2216第二个班')INSERT [dbo].[Grade] ([GradeId], [GradeName]) VALUES (49, N'S2216终结版')INSERT [dbo].[Grade] ([GradeId], [GradeName]) VALUES (51, N'JDBC测试啊啊啊啊')INSERT [dbo].[Grade] ([GradeId], [GradeName]) VALUES (52, N'JDBC测试啊啊啊啊2')INSERT [dbo].[Grade] ([GradeId], [GradeName]) VALUES (53, N'JDBC测试啊啊啊啊2')INSERT [dbo].[Grade] ([GradeId], [GradeName]) VALUES (54, N'DBC啊速度速度')INSERT [dbo].[Grade] ([GradeId], [GradeName]) VALUES (55, N'DBC阿萨德完全')INSERT [dbo].[Grade] ([GradeId], [GradeName]) VALUES (56, N'DBC阿萨德完全')INSERT [dbo].[Grade] ([GradeId], [GradeName]) VALUES (57, N'DBC阿萨德完全')INSERT [dbo].[Grade] ([GradeId], [GradeName]) VALUES (58, N'aa')INSERT [dbo].[Grade] ([GradeId], [GradeName]) VALUES (59, N'bb')INSERT [dbo].[Grade] ([GradeId], [GradeName]) VALUES (60, N'aaa''')INSERT [dbo].[Grade] ([GradeId], [GradeName]) VALUES (61, N'ccc')INSERT [dbo].[Grade] ([GradeId], [GradeName]) VALUES (62, N'1')INSERT [dbo].[Grade] ([GradeId], [GradeName]) VALUES (63, N's1')SET IDENTITY_INSERT [dbo].[Grade] OFF/****** Object: Table [dbo].[Subject] Script Date: 08/06/2014 15:03:17 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[Subject]([SubjectId] [int] IDENTITY(1,1) NOT NULL,[SubjectName] [nchar](10) NOT NULL,[ClassHour] [int] NULL,[GradeId] [int] NOT NULL, CONSTRAINT [PK_Subject] PRIMARY KEY CLUSTERED ([SubjectId] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOSET IDENTITY_INSERT [dbo].[Subject] ONINSERT [dbo].[Subject] ([SubjectId], [SubjectName], [ClassHour], [GradeId]) VALUES (1, N'oop ', 78, 1)INSERT [dbo].[Subject] ([SubjectId], [SubjectName], [ClassHour], [GradeId]) VALUES (2, N'java ', 67, 2)INSERT [dbo].[Subject] ([SubjectId], [SubjectName], [ClassHour], [GradeId]) VALUES (3, N'C# ', 78, 3)INSERT [dbo].[Subject] ([SubjectId], [SubjectName], [ClassHour], [GradeId]) VALUES (4, N'分层开发 ', 24, 2)SET IDENTITY_INSERT [dbo].[Subject] OFF/****** Object: Table [dbo].[Student] Script Date: 08/06/2014 15:03:17 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[Student]([StudentNo] [int] IDENTITY(1000,1) NOT NULL,[LoginPwd] [nvarchar](50) NOT NULL,[StudentName] [nvarchar](50) NOT NULL,[Gender] [char](1) NULL,[GradeId] [int] NOT NULL,[Phone] [nvarchar](255) NULL,[Address] [nvarchar](255) NULL,[Birthday] [datetime] NULL,[Email] [nvarchar](50) NULL,[MyTT] [int] NULL, CONSTRAINT [PK_Student] PRIMARY KEY CLUSTERED ([StudentNo] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOSET ANSI_PADDING OFFGOSET IDENTITY_INSERT [dbo].[Student] ONINSERT [dbo].[Student] ([StudentNo], [LoginPwd], [StudentName], [Gender], [GradeId], [Phone], [Address], [Birthday], [Email], [MyTT]) VALUES (23, N'000000', N'灰太狼2222', N'1', 2, N'23', N'中国平安啊啊', CAST(0x0000759800000000 AS DateTime), N'好@3', 1)INSERT [dbo].[Student] ([StudentNo], [LoginPwd], [StudentName], [Gender], [GradeId], [Phone], [Address], [Birthday], [Email], [MyTT]) VALUES (23213, N'000000', N'李金香好人', N'0', 3, N'13835231533/13503542995', N'北京海淀区北宫门', CAST(0x0000759800000000 AS DateTime), N'1@3', NULL)INSERT [dbo].[Student] ([StudentNo], [LoginPwd], [StudentName], [Gender], [GradeId], [Phone], [Address], [Birthday], [Email], [MyTT]) VALUES (23219, N'000000', N'美洋洋222', N'0', 2, N'6666666', N'北京市海淀区', CAST(0x000081D500000000 AS DateTime), N'1@3', NULL)INSERT [dbo].[Student] ([StudentNo], [LoginPwd], [StudentName], [Gender], [GradeId], [Phone], [Address], [Birthday], [Email], [MyTT]) VALUES (23225, N'000000', N'张靓颖', N'0', 2, N'13503001234', N'北京市海淀区', CAST(0x0000A1E5009B6025 AS DateTime), N'1@3', NULL)INSERT [dbo].[Student] ([StudentNo], [LoginPwd], [StudentName], [Gender], [GradeId], [Phone], [Address], [Birthday], [Email], [MyTT]) VALUES (23268, N'000001', N'张玲', N'0', 2, N'11', N'北京', CAST(0x0000A0BA011CBBCC AS DateTime), N'1@3', NULL)INSERT [dbo].[Student] ([StudentNo], [LoginPwd], [StudentName], [Gender], [GradeId], [Phone], [Address], [Birthday], [Email], [MyTT]) VALUES (23270, N'000000', N'李小龙', N'1', 2, NULL, N'武汉', CAST(0x0000A227011C8BC0 AS DateTime), N'1@3', NULL)INSERT [dbo].[Student] ([StudentNo], [LoginPwd], [StudentName], [Gender], [GradeId], [Phone], [Address], [Birthday], [Email], [MyTT]) VALUES (23311, N'111111', N'微冷的雨', N'1', 1, N'11', N'111', CAST(0x0000A2D000C38D54 AS DateTime), N'yymqqc@126.com', NULL)INSERT [dbo].[Student] ([StudentNo], [LoginPwd], [StudentName], [Gender], [GradeId], [Phone], [Address], [Birthday], [Email], [MyTT]) VALUES (23316, N'123456', N'习近平', N'1', 4, N'010-1234567', N'北京人民大会堂', CAST(0x00008D8900FBB008 AS DateTime), N'xinjinping@126.com', NULL)INSERT [dbo].[Student] ([StudentNo], [LoginPwd], [StudentName], [Gender], [GradeId], [Phone], [Address], [Birthday], [Email], [MyTT]) VALUES (23317, N'111111', N'111', N'0', 1, N'11', N'11', CAST(0x0000A2F000FCE310 AS DateTime), N'@', NULL)INSERT [dbo].[Student] ([StudentNo], [LoginPwd], [StudentName], [Gender], [GradeId], [Phone], [Address], [Birthday], [Email], [MyTT]) VALUES (23318, N'111111', N'习近平222', N'1', 4, N'11111', N'北京任命', CAST(0x0000A2F000FD4DB4 AS DateTime), N'yymqqc@126.com', NULL)INSERT [dbo].[Student] ([StudentNo], [LoginPwd], [StudentName], [Gender], [GradeId], [Phone], [Address], [Birthday], [Email], [MyTT]) VALUES (23322, N'111111', N'张靓颖', N'0', 1, N'010-123', N'中国', CAST(0x0000A2FE00A0C0BC AS DateTime), N'zly@', 11)INSERT [dbo].[Student] ([StudentNo], [LoginPwd], [StudentName], [Gender], [GradeId], [Phone], [Address], [Birthday], [Email], [MyTT]) VALUES (23323, N'111111', N'张靓颖', N'0', 1, N'010-123', N'中国', CAST(0x0000A2FE00A0C0BC AS DateTime), N'zly@', 11)SET IDENTITY_INSERT [dbo].[Student] OFF/****** Object: Table [dbo].[Result] Script Date: 08/06/2014 15:03:17 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[Result]([StudentNo] [int] NOT NULL,[SubjectId] [int] NOT NULL,[StudentResult] [int] NOT NULL,[ExamDate] [datetime] NOT NULL,[Id] [int] IDENTITY(1,1) NOT NULL, CONSTRAINT [PK_Result] PRIMARY KEY CLUSTERED ([Id] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOSET IDENTITY_INSERT [dbo].[Result] ONINSERT [dbo].[Result] ([StudentNo], [SubjectId], [StudentResult], [ExamDate], [Id]) VALUES (23219, 2, 92, CAST(0x0000A215010D13FC AS DateTime), 1)INSERT [dbo].[Result] ([StudentNo], [SubjectId], [StudentResult], [ExamDate], [Id]) VALUES (23213, 1, 58, CAST(0x00009C8200000000 AS DateTime), 2)INSERT [dbo].[Result] ([StudentNo], [SubjectId], [StudentResult], [ExamDate], [Id]) VALUES (23213, 1, 92, CAST(0x0000A215010D13FC AS DateTime), 3)INSERT [dbo].[Result] ([StudentNo], [SubjectId], [StudentResult], [ExamDate], [Id]) VALUES (23219, 2, 82, CAST(0x00009C9D00000000 AS DateTime), 4)INSERT [dbo].[Result] ([StudentNo], [SubjectId], [StudentResult], [ExamDate], [Id]) VALUES (23231, 1, 93, CAST(0x0000A1ED009052E0 AS DateTime), 5)INSERT [dbo].[Result] ([StudentNo], [SubjectId], [StudentResult], [ExamDate], [Id]) VALUES (23231, 2, 97, CAST(0x0000A1ED00905C40 AS DateTime), 6)INSERT [dbo].[Result] ([StudentNo], [SubjectId], [StudentResult], [ExamDate], [Id]) VALUES (23213, 1, 96, CAST(0x00009C7F00000000 AS DateTime), 9)INSERT [dbo].[Result] ([StudentNo], [SubjectId], [StudentResult], [ExamDate], [Id]) VALUES (23213, 2, 93, CAST(0x00009C7F00000000 AS DateTime), 10)INSERT [dbo].[Result] ([StudentNo], [SubjectId], [StudentResult], [ExamDate], [Id]) VALUES (23225, 1, 62, CAST(0x0000A215010D13FC AS DateTime), 11)INSERT [dbo].[Result] ([StudentNo], [SubjectId], [StudentResult], [ExamDate], [Id]) VALUES (23225, 2, 48, CAST(0x0000A215010D27E8 AS DateTime), 12)INSERT [dbo].[Result] ([StudentNo], [SubjectId], [StudentResult], [ExamDate], [Id]) VALUES (23225, 3, 98, CAST(0x0000A2DA00C441CC AS DateTime), 16)INSERT [dbo].[Result] ([StudentNo], [SubjectId], [StudentResult], [ExamDate], [Id]) VALUES (23213, 3, 98, CAST(0x0000A2DB00CAB4F8 AS DateTime), 17)SET IDENTITY_INSERT [dbo].[Result] OFF/****** Object: Default [DF_Student_GradeId] Script Date: 08/06/2014 15:03:17 ******/ALTER TABLE [dbo].[Student] ADD CONSTRAINT [DF_Student_GradeId] DEFAULT ((1)) FOR [GradeId]GO/****** Object: Check [CK_Student_Birthday] Script Date: 08/06/2014 15:03:17 ******/ALTER TABLE [dbo].[Student] WITH CHECK ADD CONSTRAINT [CK_Student_Birthday] CHECK (([BirthDay]>'1970-01-01'))GOALTER TABLE [dbo].[Student] CHECK CONSTRAINT [CK_Student_Birthday]GO/****** Object: Check [CK_Student_Email] Script Date: 08/06/2014 15:03:17 ******/ALTER TABLE [dbo].[Student] WITH CHECK ADD CONSTRAINT [CK_Student_Email] CHECK (([Email] like '%@%'))GOALTER TABLE [dbo].[Student] CHECK CONSTRAINT [CK_Student_Email]GO/****** Object: Check [CK_Student_Gender] Script Date: 08/06/2014 15:03:17 ******/ALTER TABLE [dbo].[Student] WITH CHECK ADD CONSTRAINT [CK_Student_Gender] CHECK (([Gender]=(0) OR [Gender]='1'))GOALTER TABLE [dbo].[Student] CHECK CONSTRAINT [CK_Student_Gender]GO/****** Object: Check [CK_Student_LoginPwd] Script Date: 08/06/2014 15:03:17 ******/ALTER TABLE [dbo].[Student] WITH CHECK ADD CONSTRAINT [CK_Student_LoginPwd] CHECK ((len([LoginPwd])>=(6)))GOALTER TABLE [dbo].[Student] CHECK CONSTRAINT [CK_Student_LoginPwd]GO/****** Object: Check [CK_studentname] Script Date: 08/06/2014 15:03:17 ******/ALTER TABLE [dbo].[Student] WITH CHECK ADD CONSTRAINT [CK_studentname] CHECK (([studentname] IS NOT NULL))GOALTER TABLE [dbo].[Student] CHECK CONSTRAINT [CK_studentname]GO/****** Object: Check [CK_Result_StudentResult] Script Date: 08/06/2014 15:03:17 ******/ALTER TABLE [dbo].[Result] WITH CHECK ADD CONSTRAINT [CK_Result_StudentResult] CHECK (([StudentResult]>=(0) AND [Studentresult]<=(100)))GOALTER TABLE [dbo].[Result] CHECK CONSTRAINT [CK_Result_StudentResult]GO/****** Object: ForeignKey [FK_Student_GradeId] Script Date: 08/06/2014 15:03:17 ******/ALTER TABLE [dbo].[Student] WITH CHECK ADD CONSTRAINT [FK_Student_GradeId] FOREIGN KEY([GradeId])REFERENCES [dbo].[Grade] ([GradeId])GOALTER TABLE [dbo].[Student] CHECK CONSTRAINT [FK_Student_GradeId]GO/****** Object: ForeignKey [FK_Subject_Grade] Script Date: 08/06/2014 15:03:17 ******/ALTER TABLE [dbo].[Subject] WITH CHECK ADD CONSTRAINT [FK_Subject_Grade] FOREIGN KEY([GradeId])REFERENCES [dbo].[Grade] ([GradeId])GOALTER TABLE [dbo].[Subject] CHECK CONSTRAINT [FK_Subject_Grade]GO/****** Object: ForeignKey [FK_Result_Subject] Script Date: 08/06/2014 15:03:17 ******/ALTER TABLE [dbo].[Result] WITH CHECK ADD CONSTRAINT [FK_Result_Subject] FOREIGN KEY([SubjectId])REFERENCES [dbo].[Subject] ([SubjectId])GOALTER TABLE [dbo].[Result] CHECK CONSTRAINT [FK_Result_Subject]GO
0 0
- 优化MySchool 数据库设计 第二章上机
- 优化MySchool 数据库设计 第二章 上机
- ACCP7.0S2优化myschool数据库设计第二章上机练习1-7
- S2 优化MySchool 数据库设计 第七章 上机
- 优化MySchool数据库 第三章上机 简答
- 优化MySchool 第二章:数据库的实现
- 优化MySchool数据库设计
- 优化MySchool 第二章
- ACCP7.0S2优化MySchool数据库设计第三章上机1-3
- ACCP7.0S2优化myschool数据库设计第四章上机1-4
- 优化MYSchool数据库设计第三章
- S2 优化MySchool 数据库设计 第八章
- S2 优化MySchool 数据库设计 第四章
- S2 优化MySchool 数据库设计 第九章
- 优化MySchool 第六章 上机
- 优化MySchool 第六章上机
- 优化MySchool 第七章上机
- 优化MySchool 第七章 上机
- 行为类模式(读书笔记)
- WSClient客户端调用---codehaus.xfire
- C语言中全局变量、局部变量、静态全局变量、静态局部变量的区别
- ORCLE 知识总结
- LeetCode-35. Search Insert Position
- 优化MySchool 数据库设计 第二章 上机
- 死后的灵魂,如何看待生前的人生
- 如何快速转载CSDN中的博客
- js 导出excel,设置下载的标题
- Linux Socket套接字
- mysql忘记密码修改方法
- 如何搭建局域网共享文件
- C++:STL标准入门汇总
- 整理图书