sq1.txt

1:首先要导入hotel数据

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

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

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

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

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

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

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

CREATE TABLE [dbo].[administrator] (
	[admin] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[ad_type] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[ad_pwd] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[consumer] (
	[con_id] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[con_name] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[con_sex] [varchar] (5) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[con_cardtype] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[con_cardid] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[con_tel] [int] NULL ,
	[con_roomid] [int] NULL ,
	[con_address] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[con_people] [int] NULL ,
	[con_indate] [datetime] NOT NULL ,
	[con_intime] [datetime] NOT NULL ,
	[con_state] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[con_remark] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[con_operator] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[employee] (
	[emp_id] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[emp_workdate] [datetime] NOT NULL ,
	[emp_dept] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[emp_name] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[emp_sex] [varchar] (4) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[emp_age] [int] NOT NULL ,
	[emp_nation] [varchar] (4) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[emp_cardId] [varchar] (18) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[emp_address] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[emp_Graduate] [datetime] NULL ,
	[emp_school] [varchar] (30) COLLATE Chinese_PRC_CI_AS NULL ,
	[emp_specialty] [varchar] (15) COLLATE Chinese_PRC_CI_AS NULL ,
	[emp_work1] [varchar] (30) COLLATE Chinese_PRC_CI_AS NULL ,
	[emp_work2] [varchar] (30) COLLATE Chinese_PRC_CI_AS NULL ,
	[emp_remark] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[meal] (
	[meal_conid] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[meal_room] [int] NOT NULL ,
	[meal_name] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[meal_price] [money] NOT NULL ,
	[meal_date] [datetime] NOT NULL ,
	[meal_remark] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[preconcert] (
	[pre_id] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[pre_name] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[pre_sex] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[pre_prdate] [datetime] NOT NULL ,
	[pre_prtime] [datetime] NOT NULL ,
	[pre_tel] [int] NULL ,
	[pre_comedate] [datetime] NULL ,
	[pre_cometime] [datetime] NULL ,
	[pre_roomid] [int] NOT NULL ,
	[pre_roomtype] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[pre_consumer] [int] NULL ,
	[pre_unitprice] [money] NOT NULL ,
	[pre_remark] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[pre_state] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[pre_operator] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[rooms] (
	[room_floor] [varchar] (6) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[room_id] [varchar] (8) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[room_type] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[room_price] [money] NOT NULL ,
	[room_bed] [int] NOT NULL ,
	[room_consumer] [int] NOT NULL ,
	[room_state] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[room_tel] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[roomuse] (
	[use_id] [varchar] (30) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[use_name] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[use_indate] [datetime] NOT NULL ,
	[use_intime] [datetime] NOT NULL ,
	[use_roomid] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[use_consumer] [int] NOT NULL ,
	[use_unitprice] [money] NOT NULL ,
	[use_rebate] [int] NOT NULL ,
	[use_zehoujia] [money] NULL ,
	[use_deposit] [money] NOT NULL ,
	[use_paymenttype] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[use_outdate] [datetime] NULL ,
	[use_inday] [int] NULL ,
	[use_meal] [money] NULL ,
	[use_tel] [money] NULL ,
	[use_allprice] [money] NULL ,
	[use_remark] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[use_inoperator] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[use_operator] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[use_state] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL 
) ON [PRIMARY]
GO