creat.sql

1、该系统只是测试一下本人设计的类库是否正确。 2、只包含一些简单的查询(主键

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_SCORETAB_REFERENCE_COURSETA]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[ScoreTable] DROP CONSTRAINT FK_SCORETAB_REFERENCE_COURSETA
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_STUDENTI_REFERENCE_GRADETAB]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[StudentInfo] DROP CONSTRAINT FK_STUDENTI_REFERENCE_GRADETAB
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_ROLEMODU_REFERENCE_MODULEIN]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[RoleModule] DROP CONSTRAINT FK_ROLEMODU_REFERENCE_MODULEIN
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_STUDENTI_REFERENCE_NATIONCO]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[StudentInfo] DROP CONSTRAINT FK_STUDENTI_REFERENCE_NATIONCO
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_ORGANIZA_REFERENCE_ORGATYPE]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Organization] DROP CONSTRAINT FK_ORGANIZA_REFERENCE_ORGATYPE
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_ORGANIZA_REFERENCE_ORGANIZA]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Organization] DROP CONSTRAINT FK_ORGANIZA_REFERENCE_ORGANIZA
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_STUDENTI_REFERENCE_ORGANIZA]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[StudentInfo] DROP CONSTRAINT FK_STUDENTI_REFERENCE_ORGANIZA
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_ROLEMODU_REFERENCE_ROLE]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[RoleModule] DROP CONSTRAINT FK_ROLEMODU_REFERENCE_ROLE
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_STUDENTI_REFERENCE_ROLE]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[StudentInfo] DROP CONSTRAINT FK_STUDENTI_REFERENCE_ROLE
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_SCORETAB_REFERENCE_STUDENTI]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[ScoreTable] DROP CONSTRAINT FK_SCORETAB_REFERENCE_STUDENTI
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CourseTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[CourseTable]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GradeTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[GradeTable]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ModuleInfo]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ModuleInfo]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[NationCode]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[NationCode]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[OrgaType]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[OrgaType]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Organization]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Organization]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Role]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Role]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[RoleModule]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[RoleModule]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ScoreTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ScoreTable]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[StudentInfo]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[StudentInfo]
GO

CREATE TABLE [dbo].[CourseTable] (
	[CourseID] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[CourseName] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[Remark] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[GradeTable] (
	[GradeID] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[GradeName] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[Remark] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[ModuleInfo] (
	[ModuleInfoID] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[ModuleInfoName] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[PagePath] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[OrderNumber] [int] NOT NULL ,
	[Remark] [nvarchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
	[SpareDec1] [decimal](18, 0) NULL ,
	[SpareDec2] [decimal](18, 0) NULL ,
	[SpareNvar1] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[SpareNvar2] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[SpareNvar3] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[NationCode] (
	[NationCodeID] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[NationCodeName] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[Remark] [nvarchar] (200) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[OrgaType] (
	[OrganizationTypeID] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[OrganizationTypeName] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[Remark] [nvarchar] (200) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Organization] (
	[OrganizationID] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[OrganizationTypeID] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[SuperiorID] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[OrganizationName] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[Remark] [nvarchar] (200) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Role] (
	[RoleID] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[RoleName] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[Remark] [nvarchar] (200) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[RoleModule] (
	[RoleModuleID] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[ModuleInfoID] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[RoleID] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[Remark] [nvarchar] (200) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[ScoreTable] (
	[ScoreID] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[StudentID] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[CourseID] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[Score] [decimal](18, 0) NULL ,
	[Remark] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[StudentInfo] (
	[StudentID] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[OrganizationID] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[GradeID] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[RoleID] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[NationCodeID] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[StudentName] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[Sex] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[BirthDay] [datetime] NOT NULL ,
	[Password] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO