SQL班级,学生,成绩表的创建

USE [HeiMaBlog8]
GO
/****** Object:  Table [dbo].[Users]    Script Date: 03/04/2012 16:19:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Users](
 [id] [int] IDENTITY(1,1) NOT NULL,
 [LoginName] [varchar](50) NOT NULL,
 [Pwd] [varchar](50) NOT NULL,
 [ErrorTimes] [smallint] NOT NULL,
 [lastErrorDateTime] [datetime] NULL,
 CONSTRAINT [PK_Users] 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]
GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'错误登录次数' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Users', @level2type=N'COLUMN',@level2name=N'ErrorTimes'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'上次登录错误事件' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Users', @level2type=N'COLUMN',@level2name=N'lastErrorDateTime'
GO
/****** Object:  Table [dbo].[user]    Script Date: 03/04/2012 16:19:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[user](
 [uId] [int] IDENTITY(1,1) NOT NULL,
 [name] [varchar](50) NULL,
 [level] [int] NULL,
PRIMARY KEY CLUSTERED
(
 [uId] 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
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[test]    Script Date: 03/04/2012 16:19:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[test](
 [number] [varchar](10) NULL,
 [amount] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Teacher3]    Script Date: 03/04/2012 16:19:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Teacher3](
 [Id] [int] NOT NULL,
 [Name] [nvarchar](10) NULL,
 [Gender] [bit] NOT NULL,
 [Age] [float] NOT NULL,
 [Salary] [money] NOT NULL,
 [Birthday] [datetime] NOT NULL,
 [CnGender] [nchar](1) NOT NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Teacher]    Script Date: 03/04/2012 16:19:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Teacher](
 [Id] [int] IDENTITY(1,1) NOT NULL,
 [Name] [nvarchar](10) NULL,
 [Gender] [bit] NOT NULL,
 [Age] [float] NOT NULL,
 [Salary] [money] NOT NULL,
 [Birthday] [datetime] NOT NULL,
 [CnGender] [nchar](1) NOT NULL,
 CONSTRAINT [PK_Teacher] 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]
GO
/****** Object:  Table [dbo].[Table_1]    Script Date: 03/04/2012 16:19:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Table_1](
 [id] [nchar](10) NULL,
 [name] [nchar](10) NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Student]    Script Date: 03/04/2012 16:19:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Student](
 [id] [int] IDENTITY(1,1) NOT NULL,
 [cid] [int] NOT NULL,
 [name] [varchar](50) NULL,
 [age] [int] NOT NULL,
 [test] [varchar](80) NULL,
 [AddDate] [datetime] NOT NULL,
 CONSTRAINT [PK_Student] 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]
GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'学员所在的班级ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Student', @level2type=N'COLUMN',@level2name=N'cid'
GO
/****** Object:  Table [dbo].[Score]    Script Date: 03/04/2012 16:19:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Score](
 [ScoreId] [int] IDENTITY(1,1) NOT NULL,
 [SId] [int] NOT NULL,
 [English] [int] NOT NULL,
 [Math] [int] NOT NULL,
 CONSTRAINT [PK_Score] PRIMARY KEY CLUSTERED
(
 [ScoreId] 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].[PhoneType]    Script Date: 03/04/2012 16:19:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PhoneType](
 [ptId] [int] IDENTITY(1,1) NOT NULL,
 [ptName] [nvarchar](50) NULL,
PRIMARY KEY CLUSTERED
(
 [ptId] 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].[PhoneNum]    Script Date: 03/04/2012 16:19:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[PhoneNum](
 [pId] [int] IDENTITY(1,1) NOT NULL,
 [pTypeId] [int] NOT NULL,
 [pName] [nvarchar](50) NULL,
 [pCellPhone] [varchar](50) NULL,
 [pHomePhone] [varchar](50) NULL,
PRIMARY KEY CLUSTERED
(
 [pId] 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
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[ContentInfo]    Script Date: 03/04/2012 16:19:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ContentInfo](
 [dId] [int] IDENTITY(1,1) NOT NULL,
 [dTId] [int] NOT NULL,
 [dName] [nvarchar](100) NOT NULL,
 [dContent] [varchar](max) NULL,
 [dInTime] [datetime] NULL,
 [dEditTime] [datetime] NULL,
 [dIsDeleted] [bit] NULL,
PRIMARY KEY CLUSTERED
(
 [dId] 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
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Class]    Script Date: 03/04/2012 16:19:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Class](
 [Id] [int] NOT NULL,
 [Name] [nvarchar](10) NOT NULL,
 [Descr] [nvarchar](150) NOT NULL,
 [number] [int] NOT NULL,
 CONSTRAINT [PK_Class] 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]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'班级人数' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Class', @level2type=N'COLUMN',@level2name=N'number'
GO
/****** Object:  Table [dbo].[Category]    Script Date: 03/04/2012 16:19:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Category](
 [tId] [int] IDENTITY(1,1) NOT NULL,
 [tName] [nvarchar](100) NOT NULL,
 [tParentId] [int] NOT NULL,
 [tNote] [nvarchar](1000) NULL,
 CONSTRAINT [PK_Category] PRIMARY KEY CLUSTERED
(
 [tId] 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].[CallRecords]    Script Date: 03/04/2012 16:19:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[CallRecords](
 [Id] [int] IDENTITY(1,1) NOT NULL,
 [CallerNumber] [nvarchar](50) NULL,
 [TelNum] [varchar](50) NULL,
 [StartDateTime] [datetime] NULL,
 [EndDateTime] [datetime] NULL,
 CONSTRAINT [PK_CallRecords] 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]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Area]    Script Date: 03/04/2012 16:19:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Area](
 [ar_id] [int] NOT NULL,
 [ar_name] [varchar](50) NOT NULL,
 [ar_uid] [int] NOT NULL,
 [ar_sort] [int] NOT NULL,
 [ar_uniCount] [int] NOT NULL,
 [ar_addtime] [datetime] NOT NULL,
 [ar_delflag] [bit] NOT NULL,
 CONSTRAINT [PK_Area] PRIMARY KEY CLUSTERED
(
 [ar_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]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  View [dbo].[view_Phone]    Script Date: 03/04/2012 16:19:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create view [dbo].[view_Phone]
as
 select pId, pTypeId, pName, pCellPhone, pHomePhone,ptName from dbo.PhoneNum
 inner join dbo.PhoneType on pTypeId = ptId
GO
/****** Object:  Default [DF_CallRecords]    Script Date: 03/04/2012 16:19:56 ******/
ALTER TABLE [dbo].[CallRecords] ADD  CONSTRAINT [DF_CallRecords]  DEFAULT (getdate()) FOR [EndDateTime]
GO
/****** Object:  Default [DF_data_dIntime]    Script Date: 03/04/2012 16:19:56 ******/
ALTER TABLE [dbo].[ContentInfo] ADD  CONSTRAINT [DF_data_dIntime]  DEFAULT (getdate()) FOR [dInTime]
GO
/****** Object:  Default [DF_data_dEditTime]    Script Date: 03/04/2012 16:19:56 ******/
ALTER TABLE [dbo].[ContentInfo] ADD  CONSTRAINT [DF_data_dEditTime]  DEFAULT (getdate()) FOR [dEditTime]
GO
/****** Object:  Default [DF_data_dIsDeleted]    Script Date: 03/04/2012 16:19:56 ******/
ALTER TABLE [dbo].[ContentInfo] ADD  CONSTRAINT [DF_data_dIsDeleted]  DEFAULT ((0)) FOR [dIsDeleted]
GO
/****** Object:  Default [DF_Student_cid]    Script Date: 03/04/2012 16:19:56 ******/
ALTER TABLE [dbo].[Student] ADD  CONSTRAINT [DF_Student_cid]  DEFAULT ((1)) FOR [cid]
GO
/****** Object:  Default [DF_Student_AddDate]    Script Date: 03/04/2012 16:19:56 ******/
ALTER TABLE [dbo].[Student] ADD  CONSTRAINT [DF_Student_AddDate]  DEFAULT (getdate()) FOR [AddDate]
GO
/****** Object:  Default [DF_Teacher_Gender]    Script Date: 03/04/2012 16:19:56 ******/
ALTER TABLE [dbo].[Teacher] ADD  CONSTRAINT [DF_Teacher_Gender]  DEFAULT ((0)) FOR [Gender]
GO
/****** Object:  Default [DF_Teacher_Birthday]    Script Date: 03/04/2012 16:19:56 ******/
ALTER TABLE [dbo].[Teacher] ADD  CONSTRAINT [DF_Teacher_Birthday]  DEFAULT (getdate()) FOR [Birthday]
GO
/****** Object:  Default [DF_Teacher_CnGender]    Script Date: 03/04/2012 16:19:56 ******/
ALTER TABLE [dbo].[Teacher] ADD  CONSTRAINT [DF_Teacher_CnGender]  DEFAULT (N'女') FOR [CnGender]
GO
/****** Object:  Default [DF_Users_ErrorTimes]    Script Date: 03/04/2012 16:19:56 ******/
ALTER TABLE [dbo].[Users] ADD  CONSTRAINT [DF_Users_ErrorTimes]  DEFAULT ((0)) FOR [ErrorTimes]
GO
/****** Object:  Check [CK_CallRecords]    Script Date: 03/04/2012 16:19:56 ******/
ALTER TABLE [dbo].[CallRecords]  WITH CHECK ADD  CONSTRAINT [CK_CallRecords] CHECK  (([CallerNumber] like '[0-9][0-9][0-9]'))
GO
ALTER TABLE [dbo].[CallRecords] CHECK CONSTRAINT [CK_CallRecords]
GO
/****** Object:  Check [CK_CallRecords_EndDateTime]    Script Date: 03/04/2012 16:19:56 ******/
ALTER TABLE [dbo].[CallRecords]  WITH CHECK ADD  CONSTRAINT [CK_CallRecords_EndDateTime] CHECK  (([EndDateTime]>[StartDateTime]))
GO
ALTER TABLE [dbo].[CallRecords] CHECK CONSTRAINT [CK_CallRecords_EndDateTime]
GO