ld.sql

这是一个非常 好的一个程序进行体会一下学生管理系统

IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'sample')
	DROP DATABASE [sample]
GO

CREATE DATABASE [sample]  ON (NAME = N'sample_Data', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL$TWINFO\data\sample_Data.MDF' , SIZE = 2, FILEGROWTH = 10%) LOG ON (NAME = N'sample_Log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL$TWINFO\data\sample_Log.LDF' , SIZE = 1, FILEGROWTH = 10%)
 COLLATE Chinese_PRC_CI_AS
GO

exec sp_dboption N'sample', N'autoclose', N'false'
GO

exec sp_dboption N'sample', N'bulkcopy', N'false'
GO

exec sp_dboption N'sample', N'trunc. log', N'false'
GO

exec sp_dboption N'sample', N'torn page detection', N'true'
GO

exec sp_dboption N'sample', N'read only', N'false'
GO

exec sp_dboption N'sample', N'dbo use', N'false'
GO

exec sp_dboption N'sample', N'single', N'false'
GO

exec sp_dboption N'sample', N'autoshrink', N'false'
GO

exec sp_dboption N'sample', N'ANSI null default', N'false'
GO

exec sp_dboption N'sample', N'recursive triggers', N'false'
GO

exec sp_dboption N'sample', N'ANSI nulls', N'false'
GO

exec sp_dboption N'sample', N'concat null yields null', N'false'
GO

exec sp_dboption N'sample', N'cursor close on commit', N'false'
GO

exec sp_dboption N'sample', N'default to local cursor', N'false'
GO

exec sp_dboption N'sample', N'quoted identifier', N'false'
GO

exec sp_dboption N'sample', N'ANSI warnings', N'false'
GO

exec sp_dboption N'sample', N'auto create statistics', N'true'
GO

exec sp_dboption N'sample', N'auto update statistics', N'true'
GO

if( ( (@@microsoftversion / power(2, 24) = 8) and (@@microsoftversion & 0xffff >= 724) ) or ( (@@microsoftversion / power(2, 24) = 7) and (@@microsoftversion & 0xffff >= 1082) ) )
	exec sp_dboption N'sample', N'db chaining', N'false'
GO

use [sample]
GO

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

if not exists (select * from master.dbo.syslogins where loginname = N'TWINFO\Administrator')
	exec sp_grantlogin N'TWINFO\Administrator'
	exec sp_defaultdb N'TWINFO\Administrator', N'master'
	exec sp_defaultlanguage N'TWINFO\Administrator', N'简体中文'
GO

exec sp_addsrvrolemember N'TWINFO\Administrator', sysadmin
GO

CREATE TABLE [dbo].[Cou_info] (
	[Cou_no] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[Cou_credit] [real] NULL ,
	[Cou_power] [real] NULL ,
	[Cou_hour] [smallint] NULL ,
	[Cou_phour] [smallint] NULL ,
	[Cou_jc] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[Cou_name] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[Cou_ms] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[Cou_type] [smallint] NULL ,
	[Cou_kshi] [bit] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Les_info] (
	[Stu_no] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[Cou_no] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[Les_tna] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[Les_cj] [real] NULL ,
	[Les_jd] [real] NULL ,
	[Les_term] [char] (6) COLLATE Chinese_PRC_CI_AS NULL ,
	[Les_xj] [real] NULL ,
	[Les_cx] [bit] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Mm_info] (
	[Mm_name] [nvarchar] (15) COLLATE Chinese_PRC_CI_AS NULL ,
	[Mm_value] [smallint] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Province_info] (
	[Province_name] [nvarchar] (15) COLLATE Chinese_PRC_CI_AS NULL ,
	[Province_value] [smallint] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Ration_info] (
	[Ration_name] [nvarchar] (15) COLLATE Chinese_PRC_CI_AS NULL ,
	[Ration_value] [smallint] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Reles_info] (
	[Stu_no] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[Cou_no] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[Reles_cj] [real] NULL ,
	[Reles_sum] [smallint] NULL ,
	[Reles_bz] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Stu_info] (
	[Stu_no] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[Stu_name] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[Stu_sex] [bit] NOT NULL ,
	[Stu_class] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[Stu_xl] [smallint] NULL ,
	[Stu_xz] [smallint] NULL ,
	[Stu_sy] [smallint] NULL ,
	[Stu_jg] [smallint] NULL ,
	[Stu_sr] [smalldatetime] NULL ,
	[Stu_zy] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[Stu_mm] [smallint] NULL ,
	[Stu_zw] [smallint] NULL ,
	[Stu_mz] [smallint] NULL ,
	[Stu_bz] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[Stu_zhcp1] [real] NULL ,
	[Stu_zhcp2] [real] NULL ,
	[Stu_zhcp3] [real] NULL ,
	[Stu_zhcp4] [real] NULL ,
	[Stu_zhcp5] [real] NULL ,
	[Stu_zhcp6] [real] NULL ,
	[Stu_zhcp7] [real] NULL ,
	[Stu_zhcp8] [real] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Szw_info] (
	[Szw_name] [nvarchar] (15) COLLATE Chinese_PRC_CI_AS NULL ,
	[Szw_value] [smallint] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[User_info] (
	[User_id] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[password] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Xl_info] (
	[Xl_name] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[Xl_value] [smallint] NOT NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Cou_info] WITH NOCHECK ADD 
	CONSTRAINT [PK_course] PRIMARY KEY  CLUSTERED 
	(
		[Cou_no]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Les_info] WITH NOCHECK ADD 
	CONSTRAINT [PK_Les_info] PRIMARY KEY  CLUSTERED 
	(
		[Stu_no],
		[Cou_no]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Mm_info] WITH NOCHECK ADD 
	CONSTRAINT [PK_Mm_info] PRIMARY KEY  CLUSTERED 
	(
		[Mm_value]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Province_info] WITH NOCHECK ADD 
	CONSTRAINT [PK_Province_info] PRIMARY KEY  CLUSTERED 
	(
		[Province_value]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Ration_info] WITH NOCHECK ADD 
	CONSTRAINT [PK_Ration_info] PRIMARY KEY  CLUSTERED 
	(
		[Ration_value]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Reles_info] WITH NOCHECK ADD 
	CONSTRAINT [PK_Reles_info] PRIMARY KEY  CLUSTERED 
	(
		[Stu_no],
		[Cou_no]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Stu_info] WITH NOCHECK ADD 
	CONSTRAINT [PK_Stu_info] PRIMARY KEY  CLUSTERED 
	(
		[Stu_no]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Szw_info] WITH NOCHECK ADD 
	CONSTRAINT [PK_Szw_info] PRIMARY KEY  CLUSTERED 
	(
		[Szw_value]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[User_info] WITH NOCHECK ADD 
	CONSTRAINT [PK_User_info] PRIMARY KEY  CLUSTERED 
	(
		[User_id]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Xl_info] WITH NOCHECK ADD 
	CONSTRAINT [PK_Xl_info] PRIMARY KEY  CLUSTERED 
	(
		[Xl_value]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Les_info] ADD 
	CONSTRAINT [FK_Les_info_Cou_info] FOREIGN KEY 
	(
		[Cou_no]
	) REFERENCES [dbo].[Cou_info] (
		[Cou_no]
	),
	CONSTRAINT [FK_Les_info_Stu_info] FOREIGN KEY 
	(
		[Stu_no]
	) REFERENCES [dbo].[Stu_info] (
		[Stu_no]
	)
GO

ALTER TABLE [dbo].[Reles_info] ADD 
	CONSTRAINT [FK_Reles_info_Cou_info] FOREIGN KEY 
	(
		[Cou_no]
	) REFERENCES [dbo].[Cou_info] (
		[Cou_no]
	),
	CONSTRAINT [FK_Reles_info_Stu_info] FOREIGN KEY 
	(
		[Stu_no]
	) REFERENCES [dbo].[Stu_info] (
		[Stu_no]
	)
GO

ALTER TABLE [dbo].[Stu_info] ADD 
	CONSTRAINT [FK_Stu_info_Mm_info] FOREIGN KEY 
	(
		[Stu_mm]
	) REFERENCES [dbo].[Mm_info] (
		[Mm_value]
	),
	CONSTRAINT [FK_Stu_info_Province_info] FOREIGN KEY 
	(
		[Stu_jg]
	) REFERENCES [dbo].[Province_info] (
		[Province_value]
	),
	CONSTRAINT [FK_Stu_info_Ration_info] FOREIGN KEY 
	(
		[Stu_mz]
	) REFERENCES [dbo].[Ration_info] (
		[Ration_value]
	),
	CONSTRAINT [FK_Stu_info_Szw_info] FOREIGN KEY 
	(
		[Stu_zw]
	) REFERENCES [dbo].[Szw_info] (
		[Szw_value]
	),
	CONSTRAINT [FK_Stu_info_Xl_info] FOREIGN KEY 
	(
		[Stu_xl]
	) REFERENCES [dbo].[Xl_info] (
		[Xl_value]
	)
GO