java.sql

学生成绩管理系统

/****** 对象: 数据库 JAVA    脚本日期: 2008-6-10 20:47:50 ******/
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'JAVA')
	DROP DATABASE [JAVA]
GO

CREATE DATABASE [JAVA]  ON (NAME = N'JAVA_Data', FILENAME = N'F:\download\QQ传送\学生成绩管理系统\学生成绩管理系统\data\JAVA_Data.MDF' , SIZE = 1, FILEGROWTH = 10%) LOG ON (NAME = N'JAVA_Log', FILENAME = N'F:\download\QQ传送\学生成绩管理系统\学生成绩管理系统\data\JAVA_Log.LDF' , SIZE = 1, FILEGROWTH = 10%)
GO

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

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

exec sp_dboption N'JAVA', N'trunc. log', N'true'
GO

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

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

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

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

exec sp_dboption N'JAVA', N'autoshrink', N'true'
GO

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

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

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

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

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

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

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

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

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

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

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

use [JAVA]
GO

/****** 对象: 触发器 dbo.insert_snake_bank    脚本日期: 2008-6-10 20:47:52 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[insert_snake_bank]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[insert_snake_bank]
GO

/****** 对象: 触发器 dbo.insert_tank_rank    脚本日期: 2008-6-10 20:47:52 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[insert_tank_rank]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[insert_tank_rank]
GO

/****** 对象: 表 [dbo].[IP]    脚本日期: 2008-6-10 20:47:52 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[IP]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[IP]
GO

/****** 对象: 表 [dbo].[member]    脚本日期: 2008-6-10 20:47:52 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[member]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[member]
GO

/****** 对象: 表 [dbo].[s_information]    脚本日期: 2008-6-10 20:47:52 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[s_information]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[s_information]
GO

/****** 对象: 表 [dbo].[s_score]    脚本日期: 2008-6-10 20:47:52 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[s_score]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[s_score]
GO

/****** 对象: 表 [dbo].[snake_bank]    脚本日期: 2008-6-10 20:47:52 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[snake_bank]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[snake_bank]
GO

/****** 对象: 表 [dbo].[tank_rank]    脚本日期: 2008-6-10 20:47:52 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tank_rank]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tank_rank]
GO

/****** 对象: 用户 dbo    脚本日期: 2008-6-10 20:47:50 ******/
/****** 对象: 用户 guest    脚本日期: 2008-6-10 20:47:50 ******/
if not exists (select * from dbo.sysusers where name = N'guest' and hasdbaccess = 1)
	EXEC sp_grantdbaccess N'guest'
GO

/****** 对象: 表 [dbo].[IP]    脚本日期: 2008-6-10 20:47:53 ******/
CREATE TABLE [dbo].[IP] (
	[id] [int] NOT NULL ,
	[client_ip] [nvarchar] (50) NULL ,
	[client_name] [nvarchar] (50) NULL ,
	[time] [int] NULL 
) ON [PRIMARY]
GO

/****** 对象: 表 [dbo].[member]    脚本日期: 2008-6-10 20:47:54 ******/
CREATE TABLE [dbo].[member] (
	[username] [char] (10) NOT NULL ,
	[password] [char] (10) NULL 
) ON [PRIMARY]
GO

/****** 对象: 表 [dbo].[s_information]    脚本日期: 2008-6-10 20:47:54 ******/
CREATE TABLE [dbo].[s_information] (
	[number] [char] (10) NOT NULL ,
	[name] [nvarchar] (10) NULL ,
	[sex] [nvarchar] (5) NULL ,
	[age] [int] NULL ,
	[class] [nvarchar] (50) NULL 
) ON [PRIMARY]
GO

/****** 对象: 表 [dbo].[s_score]    脚本日期: 2008-6-10 20:47:54 ******/
CREATE TABLE [dbo].[s_score] (
	[number] [char] (10) NOT NULL ,
	[math] [int] NULL ,
	[chinese] [int] NULL ,
	[english] [int] NULL 
) ON [PRIMARY]
GO

/****** 对象: 表 [dbo].[snake_bank]    脚本日期: 2008-6-10 20:47:54 ******/
CREATE TABLE [dbo].[snake_bank] (
	[id] [int] IDENTITY (1, 1) NOT NULL ,
	[name] [char] (50) NULL ,
	[rank] [int] NULL ,
	[score] [int] NULL 
) ON [PRIMARY]
GO

/****** 对象: 表 [dbo].[tank_rank]    脚本日期: 2008-6-10 20:47:54 ******/
CREATE TABLE [dbo].[tank_rank] (
	[id] [int] IDENTITY (1, 1) NOT NULL ,
	[rank] [int] NULL ,
	[name] [char] (10) NULL ,
	[score] [int] NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[IP] WITH NOCHECK ADD 
	CONSTRAINT [PK_IP] PRIMARY KEY  CLUSTERED 
	(
		[id]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[member] WITH NOCHECK ADD 
	CONSTRAINT [PK_member] PRIMARY KEY  CLUSTERED 
	(
		[username]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[s_information] WITH NOCHECK ADD 
	CONSTRAINT [PK_s_information] PRIMARY KEY  CLUSTERED 
	(
		[number]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[s_score] WITH NOCHECK ADD 
	CONSTRAINT [PK_s_score] PRIMARY KEY  CLUSTERED 
	(
		[number]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[snake_bank] WITH NOCHECK ADD 
	CONSTRAINT [PK_snake_bank] PRIMARY KEY  CLUSTERED 
	(
		[id]
	)  ON [PRIMARY] 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

/****** 对象: 触发器 dbo.insert_snake_bank    脚本日期: 2008-6-10 20:47:54 ******/
create trigger insert_snake_bank
on dbo.snake_bank for insert
as
declare @name char(10)
declare @score int
declare @id int
declare @count_all int
declare @count int
select @id=id,@name=name,@score=score from inserted
delete from snake_bank where id=@id
select @count_all=count(*) from snake_bank
select @count=count(*) from tank_rank where score>@score
update snake_bank set rank=rank+1 where score>@score
insert into snake_bank values(@count_all-@count+1,@name,@score)

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

/****** 对象: 触发器 dbo.insert_tank_rank    脚本日期: 2008-6-10 20:47:54 ******/
create trigger insert_tank_rank
on tank_rank for insert
as
declare @name char(10)
declare @score int
declare @id int
declare @count_all int
declare @count int
select @id=id,@name=name,@score=score from inserted
delete from tank_rank where id=@id
select @count_all=count(*) from tank_rank
select @count=count(*) from tank_rank where score>@score
update tank_rank set rank=rank+1 where score>@score
insert into tank_rank values(@count_all-@count+1,@name,@score)

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO