card.sql

数据库管理系统的实习内容

use master
go

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

create database Card
go
use Card
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].[student_Info]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[student_Info]
GO

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

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

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

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

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

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



CREATE TABLE [dbo].[user_Info] (
	[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

CREATE TABLE [dbo].[student_Info] (
	[student_ID] [int] NOT NULL ,
        [card_ID][int]NULL,
	[student_Name] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[student_Sex] [char] (2) COLLATE Chinese_PRC_CI_AS NULL ,
	[born_Date] [datetime] NULL ,
	[college_NO] [int] NULL ,
	[tele_Number] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[ru_Date] [datetime] NULL ,
	[address] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[comment] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[card_Info] (
	[card_ID] [int] NOT NULL ,
	[college_Des] [char] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[card_value] [int]  NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[school_Info] (
	[school_No] [int] NOT NULL ,
        [school_add] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[school_Des] [char] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[college_Info] (
	[college_No] [int] NOT NULL ,
        [college_Name] [char] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[college_Des] [char] (50) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[schcol_Info] (
	[school_No] [int] NOT NULL ,
        [college_Name] [char] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[hang_Info] (
	[card_ID] [int] NOT NULL ,
	[card_Statue] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[account_Info] (
	[card_ID] [int] NOT NULL ,
	[card_invalue] [int] NULL ,
        [in_date] [datetime] NULL,
        [in_type] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	
) ON [PRIMARY]
GO

insert user_Info(user_ID,user_PWD)
VALUES('a','a')