USE [master]
GO
/****** Object: Database [COLLEGE] Script Date: 2019/11/25 星期一 上午 08:57:11 /
CREATE DATABASE [COLLEGE] ON PRIMARY
( NAME = N’COLLEGE_m’, FILENAME = N’F:\COLLEGE\COLLEGE.mdf’ , SIZE = 51200KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10240KB ),
FILEGROUP [NGroup]
( NAME = N’COLLEGE_n1’, FILENAME = N’F:\COLLEGE\COLLEGE_n1.ndf’ , SIZE = 20480KB , MAXSIZE = UNLIMITED, FILEGROWTH = 20%),
( NAME = N’COLLEGE_n2’, FILENAME = N’F:\COLLEGE\COLLEGE_n2.ndf’ , SIZE = 20480KB , MAXSIZE = UNLIMITED, FILEGROWTH = 20%)
LOG ON
( NAME = N’COLLEGE_log’, FILENAME = N’F:\COLLEGE\COLLEGE.ldf’ , SIZE = 10240KB , MAXSIZE = 2048GB , FILEGROWTH = 5120KB )
GO
ALTER DATABASE [COLLEGE] SET COMPATIBILITY_LEVEL = 100
GO
IF (1 = FULLTEXTSERVICEPROPERTY(‘IsFullTextInstalled’))
begin
EXEC [COLLEGE].[dbo].[sp_fulltext_database] @action = ‘enable’
end
GO
ALTER DATABASE [COLLEGE] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [COLLEGE] SET ANSI_NULLS OFF
GO
ALTER DATABASE [COLLEGE] SET ANSI_PADDING OFF
GO
ALTER DATABASE [COLLEGE] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [COLLEGE] SET ARITHABORT OFF
GO
ALTER DATABASE [COLLEGE] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [COLLEGE] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [COLLEGE] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [COLLEGE] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [COLLEGE] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [COLLEGE] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [COLLEGE] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [COLLEGE] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [COLLEGE] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [COLLEGE] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [COLLEGE] SET DISABLE_BROKER
GO
ALTER DATABASE [COLLEGE] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [COLLEGE] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [COLLEGE] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [COLLEGE] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [COLLEGE] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [COLLEGE] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [COLLEGE] SET HONOR_BROKER_PRIORITY OFF
GO
ALTER DATABASE [COLLEGE] SET RECOVERY FULL
GO
ALTER DATABASE [COLLEGE] SET MULTI_USER
GO
ALTER DATABASE [COLLEGE] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [COLLEGE] SET DB_CHAINING OFF
GO
EXEC sys.sp_db_vardecimal_storage_format N’COLLEGE’, N’ON’
GO
USE [COLLEGE]
GO
/ Object: Table [dbo].[Course] Script Date: 2019/11/25 星期一 上午 08:57:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Course](
[CourseID] nchar NOT NULL,
[Courname] nchar NOT NULL,
[Credit] [int] NULL,
CONSTRAINT [PK_Course] PRIMARY KEY CLUSTERED
(
[CourseID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Mark] Script Date: 2019/11/25 星期一 上午 08:57:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Mark](
[CourseID] nchar NOT NULL,
[StudentID] nchar NOT NULL,
[Score] [int] NULL,
CONSTRAINT [PK_Mark] PRIMARY KEY CLUSTERED
(
[CourseID] ASC,
[StudentID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[School] Script Date: 2019/11/25 星期一 上午 08:57:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[School](
[SchoolID] nchar NOT NULL,
[SchoolName] nchar NULL,
CONSTRAINT [PK_School] PRIMARY KEY CLUSTERED
(
[SchoolID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Student] Script Date: 2019/11/25 星期一 上午 08:57:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Student](
[StudentID] nchar NOT NULL,
[Name] nchar NOT NULL,
[Sex] nchar NULL,
[Birthday] [smalldatetime] NULL,
[School] nchar NULL,
CONSTRAINT [PK_Student] PRIMARY KEY CLUSTERED
(
[StudentID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[Course] ([CourseID], [Courname], [Credit]) VALUES (N’001’, N’C语言 ‘, 4)
INSERT [dbo].[Course] ([CourseID], [Courname], [Credit]) VALUES (N’002’, N’数据库 ‘, 3)
INSERT [dbo].[Course] ([CourseID], [Courname], [Credit]) VALUES (N’003’, N’操作系统 ‘, 4)
INSERT [dbo].[Course] ([CourseID], [Courname], [Credit]) VALUES (N’005’, N’软件工程 ‘, 2)
INSERT [dbo].[Course] ([CourseID], [Courname], [Credit]) VALUES (N’006’, N’数据结构 ‘, 4)
INSERT [dbo].[Course] ([CourseID], [Courname], [Credit]) VALUES (N’007’, N’网络原理 ‘, 3)
INSERT [dbo].[Course] ([CourseID], [Courname], [Credit]) VALUES (N’008’, N’微机原理 ‘, NULL)
INSERT [dbo].[Course] ([CourseID], [Courname], [Credit]) VALUES (N’100’, N’物联网导论 ‘, 2)
INSERT [dbo].[Course] ([CourseID], [Courname], [Credit]) VALUES (N’101’, N’单片机 ‘, 3)
INSERT [dbo].[Course] ([CourseID], [Courname], [Credit]) VALUES (N’102’, N’RFID ‘, 2)
INSERT [dbo].[Course] ([CourseID], [Courname], [Credit]) VALUES (N’103’, N’短距离无线 ‘, 3)
INSERT [dbo].[Course] ([CourseID], [Courname], [Credit]) VALUES (N’104’, N’嵌入式 ‘, 3)
INSERT [dbo].[Course] ([CourseID], [Courname], [Credit]) VALUES (N’200’, N’刑法 ‘, 4)
INSERT [dbo].[Course] ([CourseID], [Courname], [Credit]) VALUES (N’201’, N’行政管理 ‘, 3)
INSERT [dbo].[Course] ([CourseID], [Courname], [Credit]) VALUES (N’202’, N’经济法 ‘, 4)
INSERT [dbo].[Course] ([CourseID], [Courname], [Credit]) VALUES (N’203’, N’未成年法 ‘, 2)
INSERT [dbo].[Course] ([CourseID], [Courname], [Credit]) VALUES (N’204’, N’宪法 ‘, 4)
INSERT [dbo].[Course] ([CourseID], [Courname], [Credit]) VALUES (N’300’, N’大学英语精读 ‘, 4)
INSERT [dbo].[Course] ([CourseID], [Courname], [Credit]) VALUES (N’301’, N’大学英语泛读 ‘, 2)
INSERT [dbo].[Course] ([CourseID], [Courname], [Credit]) VALUES (N’302’, N’英语语法 ‘, 3)
INSERT [dbo].[Course] ([CourseID], [Courname], [Credit]) VALUES (N’303’, N’法语 ‘, 3)
INSERT [dbo].[Course] ([CourseID], [Courname], [Credit]) VALUES (N’304’, N’西班牙语 ‘, 4)
INSERT [dbo].[Course] ([CourseID], [Courname], [Credit]) VALUES (N’401’, N’哲学 ‘, 4)
INSERT [dbo].[Course] ([CourseID], [Courname], [Credit]) VALUES (N’402’, N’逻辑 ‘, 3)
INSERT [dbo].[Course] ([CourseID], [Courname], [Credit]) VALUES (N’403’, N’思想品德 ‘, 3)
INSERT [dbo].[Course] ([CourseID], [Courname], [Credit]) VALUES (N’500’, N’大学体育 ‘, 2)
INSERT [dbo].[Course] ([CourseID], [Courname], [Credit]) VALUES (N’501’, N’篮球 ‘, 3)
INSERT [dbo].[Mark] ([CourseID], [StudentID], [Score]) VALUES (N’001’, N’2013019283’, 76)
INSERT [dbo].[Mark] ([CourseID], [StudentID], [Score]) VALUES (N’001’, N’2013052419’, 90)
INSERT [dbo].[Mark] ([CourseID], [StudentID], [Score]) VALUES (N’001’, N’2014021224’, 90)
INSERT [dbo].[Mark] ([CourseID], [StudentID], [Score]) VALUES (N’002’, N’2013019283’, 69)
INSERT [dbo].[Mark] ([CourseID], [StudentID], [Score]) VALUES (N’002’, N’2013052419’, 86)
INSERT [dbo].[Mark] ([CourseID], [StudentID], [Score]) VALUES (N’003’, N’2013019283’, 86)
INSERT [dbo].[Mark] ([CourseID], [StudentID], [Score]) VALUES (N’003’, N’2014021224’, 87)
INSERT [dbo].[Mark] ([CourseID], [StudentID], [Score]) VALUES (N’004’, N’2013052419’, 93)
INSERT [dbo].[Mark] ([CourseID], [StudentID], [Score]) VALUES (N’100’, N’2014110205’, 67)
INSERT [dbo].[Mark] ([CourseID], [StudentID], [Score]) VALUES (N’100’, N’2014161336’, 89)
INSERT [dbo].[Mark] ([CourseID], [StudentID], [Score]) VALUES (N’101’, N’2014110205’, 65)
INSERT [dbo].[Mark] ([CourseID], [StudentID], [Score]) VALUES (N’101’, N’2014161336’, 78)
INSERT [dbo].[Mark] ([CourseID], [StudentID], [Score]) VALUES (N’200’, N’2014001203’, 90)
INSERT [dbo].[Mark] ([CourseID], [StudentID], [Score]) VALUES (N’201’, N’2013010110’, 95)
INSERT [dbo].[Mark] ([CourseID], [StudentID], [Score]) VALUES (N’201’, N’2014001203’, 85)
INSERT [dbo].[Mark] ([CourseID], [StudentID], [Score]) VALUES (N’203’, N’2013010110’, 87)
INSERT [dbo].[Mark] ([CourseID], [StudentID], [Score]) VALUES (N’203’, N’2014002406’, 78)
INSERT [dbo].[Mark] ([CourseID], [StudentID], [Score]) VALUES (N’204’, N’2013010110’, 79)
INSERT [dbo].[Mark] ([CourseID], [StudentID], [Score]) VALUES (N’204’, N’2014001203’, 78)
INSERT [dbo].[Mark] ([CourseID], [StudentID], [Score]) VALUES (N’300’, N’2013019283’, 78)
INSERT [dbo].[Mark] ([CourseID], [StudentID], [Score]) VALUES (N’300’, N’2013052419’, 89)
INSERT [dbo].[Mark] ([CourseID], [StudentID], [Score]) VALUES (N’300’, N’201322013 ‘, 88)
INSERT [dbo].[Mark] ([CourseID], [StudentID], [Score]) VALUES (N’300’, N’2014001203’, 80)
INSERT [dbo].[Mark] ([CourseID], [StudentID], [Score]) VALUES (N’300’, N’2014002406’, 83)
INSERT [dbo].[Mark] ([CourseID], [StudentID], [Score]) VALUES (N’300’, N’2014021224’, 74)
INSERT [dbo].[Mark] ([CourseID], [StudentID], [Score]) VALUES (N’300’, N’2014021268’, 88)
INSERT [dbo].[Mark] ([CourseID], [StudentID], [Score]) VALUES (N’300’, N’2014110205’, 53)
INSERT [dbo].[Mark] ([CourseID], [StudentID], [Score]) VALUES (N’300’, N’2014161336’, 89)
INSERT [dbo].[Mark] ([CourseID], [StudentID], [Score]) VALUES (N’401’, N’2014002406’, 77)
INSERT [dbo].[Mark] ([CourseID], [StudentID], [Score]) VALUES (N’402’, N’2014002406’, 85)
INSERT [dbo].[Mark] ([CourseID], [StudentID], [Score]) VALUES (N’403’, N’2012060836’, 78)
INSERT [dbo].[Mark] ([CourseID], [StudentID], [Score]) VALUES (N’403’, N’2013010110’, 90)
INSERT [dbo].[Mark] ([CourseID], [StudentID], [Score]) VALUES (N’403’, N’201322013 ‘, 79)
INSERT [dbo].[Mark] ([CourseID], [StudentID], [Score]) VALUES (N’403’, N’2014021268’, 78)
INSERT [dbo].[Mark] ([CourseID], [StudentID], [Score]) VALUES (N’500’, N’2013019283’, 90)
INSERT [dbo].[Mark] ([CourseID], [StudentID], [Score]) VALUES (N’500’, N’2013052419’, 68)
INSERT [dbo].[Mark] ([CourseID], [StudentID], [Score]) VALUES (N’500’, N’201322013 ‘, 90)
INSERT [dbo].[Mark] ([CourseID], [StudentID], [Score]) VALUES (N’500’, N’2014021224’, 56)
INSERT [dbo].[Mark] ([CourseID], [StudentID], [Score]) VALUES (N’500’, N’2014021268’, 66)
INSERT [dbo].[Mark] ([CourseID], [StudentID], [Score]) VALUES (N’500’, N’2014161336’, 60)
INSERT [dbo].[Mark] ([CourseID], [StudentID], [Score]) VALUES (N’501’, N’2012060836’, 89)
INSERT [dbo].[Mark] ([CourseID], [StudentID], [Score]) VALUES (N’501’, N’201322013 ', 86)
INSERT [dbo].[School] ([SchoolID], [SchoolName]) VALUES (N’1 ', N’体育学院 ‘)
INSERT [dbo].[School] ([SchoolID], [SchoolName]) VALUES (N’10’, N’音乐学院 ‘)
INSERT [dbo].[School] ([SchoolID], [SchoolName]) VALUES (N’11’, N’外语学院 ‘)
INSERT [dbo].[School] ([SchoolID], [SchoolName]) VALUES (N’12’, N’马列学院 ')
INSERT [dbo].[School] ([SchoolID], [SchoolName]) VALUES (N’2 ', N’信息工程学院 ')
INSERT [dbo].[School] ([SchoolID], [SchoolName]) VALUES (N’3 ', N’文学院 ')
INSERT [dbo].[School] ([SchoolID], [SchoolName]) VALUES (N’4 ', N’政法学院 ')
INSERT [dbo].[School] ([SchoolID], [SchoolName]) VALUES (N’5 ', N’计算机学院 ')
INSERT [dbo].[School] ([SchoolID], [SchoolName]) VALUES (N’6 ', N’电气学院 ')
INSERT [dbo].[School] ([SchoolID], [SchoolName]) VALUES (N’7 ', N’化工学院 ')
INSERT [dbo].[School] ([SchoolID], [SchoolName]) VALUES (N’8 ', N’医学院 ')
INSERT [dbo].[School] ([SchoolID], [SchoolName]) VALUES (N’9 ', N’陶瓷学院 ‘)
INSERT [dbo].[Student] ([StudentID], [Name], [Sex], [Birthday], [School]) VALUES (N’2012060836’, N’赵静雅 ', N’女 ', NULL, N’1 ‘)
INSERT [dbo].[Student] ([StudentID], [Name], [Sex], [Birthday], [School]) VALUES (N’2013010110’, N’刘新丽 ', N’女 ‘, NULL, NULL)
INSERT [dbo].[Student] ([StudentID], [Name], [Sex], [Birthday], [School]) VALUES (N’2013019283’, N’田晶晶 ', N’女 ', CAST(0x88AE0000 AS SmallDateTime), N’5 ‘)
INSERT [dbo].[Student] ([StudentID], [Name], [Sex], [Birthday], [School]) VALUES (N’2013052419’, N’陈建岭 ', N’男 ', CAST(0x86890000 AS SmallDateTime), N’5 ‘)
INSERT [dbo].[Student] ([StudentID], [Name], [Sex], [Birthday], [School]) VALUES (N’2013160836’, N’赵楠楠 ', N’女 ', CAST(0x87330000 AS SmallDateTime), N’2 ')
INSERT [dbo].[Student] ([StudentID], [Name], [Sex], [Birthday], [School]) VALUES (N’201322013 ', N’刘叶青 ', N’男 ‘, NULL, NULL)
INSERT [dbo].[Student] ([StudentID], [Name], [Sex], [Birthday], [School]) VALUES (N’2014001203’, N’赵广田 ', N’男 ', CAST(0x890D0000 AS SmallDateTime), N’4 ‘)
INSERT [dbo].[Student] ([StudentID], [Name], [Sex], [Birthday], [School]) VALUES (N’2014002406’, N’刘雨航 ‘, N’男 ‘, CAST(0x8A290000 AS SmallDateTime), N’12’)
INSERT [dbo].[Student] ([StudentID], [Name], [Sex], [Birthday], [School]) VALUES (N’2014021224’, N’张兰婷 ', N’女 ', CAST(0x8A140000 AS SmallDateTime), N’5 ‘)
INSERT [dbo].[Student] ([StudentID], [Name], [Sex], [Birthday], [School]) VALUES (N’2014021268’, N’秦燕菲 ', N’女 ', CAST(0x894F0000 AS SmallDateTime), N’9 ‘)
INSERT [dbo].[Student] ([StudentID], [Name], [Sex], [Birthday], [School]) VALUES (N’2014110205’, N’张雄伟 ', N’男 ', CAST(0x87930000 AS SmallDateTime), N’2 ‘)
INSERT [dbo].[Student] ([StudentID], [Name], [Sex], [Birthday], [School]) VALUES (N’2014161336’, N’王峰宇 ', N’男 ', CAST(0x88880000 AS SmallDateTime), N’2 ')
USE [master]
GO
ALTER DATABASE [COLLEGE] SET READ_WRITE
GO