use master 
go

if exists (select * from dbo.sysdatabases where name = 'qinshi_guanli') 
drop database qinshi_guanli
GO

create database qinshi_guanli
go
use qinshi_guanli
go

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


CREATE TABLE [dbo].[user_if] (
	[user_ID] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[user_PWD] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[user_Des] [char] (10) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]

GO


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


CREATE TABLE [dbo].[student_if] (
	[student_ID] [int] NOT NULL ,
	[student_name] [varchar] (10) NOT NULL,
	[student_sex] [char] (4) NOT NULL ,
        [nj_ID] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL, 
        [student_xibie] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL,
        [class_ID] [varchar](10) COLLATE Chinese_PRC_CI_AS NULL,   
	[tele_number] [char] (15) COLLATE Chinese_PRC_CI_AS NULL ,
	[student_zzmm][varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[juzhu_ID][char] (10) COLLATE Chinese_PRC_CI_AS NULL , 
        [qinshi_ID][char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
        [chuangwei_ID][char](10) COLLATE Chinese_PRC_CI_AS NULL,
        [comment] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL
) 



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


CREATE TABLE [dbo].[juzhu_if] (
	[juzhu_ID][char](6) NOT NULL ,
        [juzhu_sex] [char](4) NULL, 
        [qinshi_ID][char](6) NULL,
       
        [comment1] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL
) 

GO


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


CREATE TABLE [dbo].[qinshi_if] (
        [juzhu_ID][char](6) NOT NULL, 
	[qinshi_ID] [char](6) NOT NULL ,
        [juzhuxingbie][char](4)NULL,  
        [qinshi_dianhua][char](11) NULL, 
        [qinshi_jiage][char](10)NULL, 
        [qinshi_yzrs][int] NULL,
        [qinshi_szrs][int]NULL,
        [qinshi_ztai][varchar](4)NULL,
        [comment1] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL
) 

GO


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


CREATE TABLE [dbo].[xin_zc] (
        [student_ID][int] NOT NULL,
        [student_name][char](10) NULL,
        [student_sex][char](4) NULL,
        [juzhu_ID][char] (8)NOT NULL,
        [qinshi_ID][char](8) NOT NULL, 
        [jidu][char](10) NULL,
        [jiaofei][char](10) NULL,
        [jiaofeiqk][char](4) NULL, 
        [zcrq][char](10) NULL,  
        [comment4][varchar](200) COLLATE Chinese_PRC_CI_AS NULL   

) 
GO


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


CREATE TABLE [dbo].[student_qinshi] (
	[student_ID][int] NOT NULL,
        [student_name][char](10) NULL,
        [juzhu_ID][char](6) NOT NULL,
        [qinshi_ID][char] (6)NOT NULL,
        [chuangwei][int] NULL, 
        [comment1] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL
) 
GO


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


CREATE TABLE [dbo].[qinshi_pingfen] (
        [qinshi_ID][char](6) NOT  NULL,
        [pingfen][char](10) NULL,
        [zhoushu][char](6) NULL,
        [nianfen][char](4) NULL,
        [comment3][varchar](200) COLLATE Chinese_PRC_CI_AS NULL   

) 
GO

insert user_if(user_ID,user_PWD)
VALUES('ming','ming')