📄 t18_04.sql
字号:
use master
go
if exists(select * from sysdatabases where name='T18_04')
drop database T18_04
go
create database T18_04
go
use T18_04
if exists(select * from sysobjects where name='BG_GLY')
drop table BG_GLY
go
create table BG_GLY --管理员表
(
gid int IDENTITY(1,1) not null, --管理员id
gname char(20), --管理员姓名
gpass char(20), --管理员密码
gqx char(20), --权限
)
go
alter table BG_GLY add constraint pkBG_GLY primary key(gid)
go
insert BG_GLY values('002','002','前台')
insert BG_GLY values('003','003','餐厅')
go
select * from BG_GLY
if exists(select * from sysobjects where name='BG_HYB')
drop table BG_HYB
go
create table BG_HYB --会员表
(
hid char(20)not null, --会员卡号
hjb char(10), --会员级别
hname char(20), --会员姓名
hxb char(10), --会员性别
hnian int, --会员年龄
hdian char(20), --会员电话
hsfz char(20), --会员身份证号码
hzk float, --会员折扣
hblrq datetime, --办卡日期
hzs varchar(50), --注释
)
go
alter table BG_HYB add constraint pkhid primary key(hid)
go
go
select * from BG_HYB
go
if exists(select * from sysobjects where name='BG_SBB')
drop table BG_SBB
go
create table BG_SBB --设备表
(
sid numeric(18,0) IDENTITY(1,1) not null, --设备类型id
slx char(10), --设备类型
sname char(20),--设备名
srq datetime, --购买日期
sgmr char(10),-- 采购人员
sjg money, --设备价格
sbz varchar(250),--设备备注
)
go
select distinct slx from BG_SBB
alter table BG_SBB add constraint pkBG_SBB primary key(sid)
go
select * from BG_SBB
go
if exists(select * from sysobjects where name='BG_FJLX')
drop table BG_FJLX
go
create table BG_FJLX --房间类型表
(
xid numeric(18,0) IDENTITY(1,1) not null, --房间类型id
xname char(10), --房间类型
xjg money, --房间价格
xzdj money, --钟点价
xyj money, --房间押金
xzs varchar(50), --注释
)
go
alter table BG_FJLX add constraint pkBG_FJLX primary key(xid)
go
select * from BG_FJLX
if exists(select * from sysobjects where name='BG_FJSB')
drop table BG_FJSB
go
create table BG_FJSB --房间设备表
(
fsid numeric(18,0) IDENTITY(1,1) not null, --类型id
fslx char(20), --房间类型
fssb char(20), --房间设备
)
go
alter table BG_FJSB add constraint pkBG_FJSB primary key(fsid)
go
select * from BG_FJSB
if exists(select * from sysobjects where name='BG_ydb')
drop table BG_YDB
go
create table BG_YDB --预定表
(
yid numeric(18,0) IDENTITY(1,1), --预定id
yname char(8), --预定人姓名
yfj char(10), --预定房间
yydsj datetime, --预定时间
ydh char(20), --预定人电话
yddsj datetime,--到达时间
yhyh char(15), --会员号
yzs varchar(50), --注释
)
go
alter table BG_YDB add constraint pkBG_YDB primary key(yid)
go
select * from BG_ydb
if exists(select * from sysobjects where name='BG_KF')
drop table BG_KF
go
create table BG_KF --客房表
(
kid char(10) not null, --房间id
klc char(4), --客房楼层
kzt char(8), --房间状态
klx char(16), --客房类型
kname char(8), --客人姓名
kxin char(4), --客人性别
ksf char(20), --客人身份证
krs char(10), --居住人数
krzsj datetime, --入住时间
kvipkh char(20), --vip卡号
kdh char(20), --客房电话
kzd char(20), --是否钟点
kyj money, --客房押金
kzk char(10), --折扣
kczy char(10), --操作员
kzs varchar(50), --注释
)
go
alter table BG_KF add constraint pkBG_KF primary key(kid)
go
select * from BG_KF
if exists(select * from sysobjects where name='BG_ZDB')
drop table BG_ZDB
go
create table BG_ZDB --帐单表表
(
zid numeric(18,0) IDENTITY(200601011001,1)not null,--帐单id
fid char(10), --房间id
flx char(10), --房间类型
kname char(8), --客人姓名
ksf char(20), --客人身份证
krs int, --居住人数
krzrq datetime, --入住日期
klksj datetime, --离开日期
kvipkh char(20), --vip卡号
kfjjg money, --房间价格
ksjsf money, --实际收费
kczy char(10), --操作员
kzs varchar(50), --注释
)
go
alter table BG_ZDB add constraint pkBG_ZDB primary key(zid)
go
select * from BG_ZDB
-------------------------------------------餐厅----------------------------------------------
if exists(select * from sysobjects where name='BG_Menu')
drop table BG_Menu
go
create table BG_Menu --菜单
(
mid int identity(1,1) not null, --菜单编号
mtype varchar(50), --菜型
mname varchar(30), --菜名
mcpuid varchar(10), --菜名编号
mbit bit, --是否点菜
mprice money --单价
)
go
alter table BG_Menu add constraint df_mbit default(0) for mbit
select * from BG_Menu
select distinct mtype from BG_Menu
if exists(select * from sysobjects where name='BG_CTtype')
drop table BG_CTtype
go
create table BG_CTtype --餐厅类型
(
CTid int identity(1,1) not null, --编号
CTqy varchar(20), --区域风格
CTzs varchar(50) --注释
)
select * from BG_CTtype
if exists(select * from sysobjects where name='BG_YD')
drop table BG_YD
create table BG_YD --客人预定
(
yid int identity(1,1) not null, --预定编号
yname varchar(20), --预定客人姓名
yphone varchar(20), --预定客人电话
yctype varchar(20), --预定餐厅类型
czid varchar(10), --预定坐位号
yktype varchar(20), --客人类型
ytime datetime , --预定时间
dtime datetime not null, --到达时间
)
go
select * from BG_YD
if exists(select * from sysobjects where name='BG_CTset')
drop table BG_CTset
go
create table BG_CTset --餐厅座位
(
czid varchar(10) primary key , --座位号
carea varchar(20), --所属区域
cempty varchar(10), --是否空,预定
crs varchar(10), --客人人数
cname varchar(10), --客人姓名
kh varchar(20), --卡号
czk varchar(10), --折扣
ctime datetime, --点单时间
cperson varchar(10), --收银员
cremark varchar(50), --备注
)
go
--update BG_CTset set cempty='预定',crs='3',kh='222222',ctime='06-10-10',cperson='张山',cremark='bnmcb' where czid='1'
--delete from BG_CTset where carea='A区中餐厅'
select * from BG_CTset
if exists(select * from sysobjects where name='BG_Ddan')
drop table BG_Ddan --点菜表
create table BG_Ddan
(
czid varchar(10), --座位号
cdid varchar(10), --菜单id
xmname varchar(20), --菜单名
dj money --单价
)
go
select * from BG_Ddan
go
if exists(select * from sysobjects where name='BG_ZD')
drop table BG_ZD
create table BG_ZD --餐厅帐单
(
zdid numeric(18,0) identity(2006101501,1) not null, --帐单编号
czid varchar(20), --座位号
carea varchar(30), --所属区域
crs varchar(30), --客人人数
cname varchar(30), --客人姓名
kh varchar(30), --卡号
dctime datetime, --点菜时间
jztime datetime, --结帐时间
cperson varchar(30), --收银员
Ys money --应收价格
)
go
select * from BG_ZD
go
if exists(select * from sysobjects where name='BG_ZDCD')
drop table BG_ZDCD
create table BG_ZDCD --帐单菜单
(
zdid numeric(18,0) not null, --帐单编号
czid varchar(10), --座位号
cdid varchar(10), --菜单编号
xmname varchar(20), --菜单名
dj money --单价
)
go
select * from BG_ZDCD
-------------------------------------存储过程-------------------------------------------------
---------------------------------------触发器-------------------------------------
if exists(select name from sysobjects where name='trig_ins_BG_YDB')
drop trigger trig_ins_BG_ydb
go
create trigger trig_ins_BG_YDB--预约表添加时候改变房间状态
on BG_YDB
for insert
as
declare @fjh char(20)
select @fjh=yfj from inserted
update BG_KF set kzt='1' where kid=@fjh
go
if exists(select name from sysobjects where name='trig_del_BG_YDB')
drop trigger trig_del_BG_YDB
go
create trigger trig_del_BG_YDB--预约表删除时候改变房间状态
on BG_YDB
for delete
as
declare @fjh char(20),@zt char(20)
select @fjh=yfj from deleted
select @zt=kzt from BG_KF where kid=@fjh
if(@zt!="2"){
update BG_KF set kzt='' where kid=@fjh
}
go
if exists(select name from sysobjects where name='trig_up_BG_YDB')
drop trigger trig_up_BG_YDB
go
create trigger trig_up_BG_YDB
on BG_YDB
for update
as
declare @fjh char(20),@fjhi char(20)
select @fjh=yfj from deleted
select @fjhi=yfj from inserted
update BG_KF set kzt='' where kid=@fjh
update BG_KF set kzt='1' where kid=@fjhi
go
if exists(select name from sysobjects where name='trig_ins_BG_ZDB')
drop trigger trig_ins_BG_ZDB
go
create trigger trig_ins_BG_ZDB--帐单表添加时候改变房间状态
on BG_ZDB
for insert
as
declare @fjh char(20)
select @fjh=fid from inserted
update BG_KF set kname='',kxin='',ksf='',krs='',kvipkh='',kzd='',kyj=0,krzsj='',kzt='',kczy='',kzk='' where kid=@fjh
go
---------------------餐厅触发器 ---------------------------------------------------------------
if exists(select name from sysobjects where name='trig_insert_BG_YD')
drop trigger trig_insert_BG_YD
go
-------创建insert触发器：在预约表上创建插入触发器------------------------------------------------
create trigger trig_insert_BG_YD
on BG_YD
for insert
as
------------定义变量-----------------
declare @id varchar(10)
select @id=czid from inserted
update BG_CTset set cempty='预定'where czid=@id
go
------------------创建delete触发器-------------------------------------------------------------
if exists(select name from sysobjects where name='trig_delete_BG_YD')
drop trigger trig_delete_BG_YD
go
create trigger trig_delete_BG_YD
on BG_YD
for delete
as
declare @id varchar(10)
select @id=czid from deleted
update BG_CTset set cempty=''where czid=@id
go
--------------------创建update触发器--------------------------------------------------------------
if exists(select name from sysobjects where name='trig_update_BG_YD')
drop trigger trig_update_BG_YD
go
create trigger trig_update_BG_YD
on BG_YD
for update
as
declare @id varchar(10),@id1 varchar(10)
select @id=czid from deleted
select @id1=czid from inserted
update BG_CTset set cempty=''where czid=@id
update BG_CTset set cempty='预定'where czid=@id1
go
----------------创建点菜触发器------------------------------------------------------------------
if exists(select name from sysobjects where name='trig_insert_BG_ZDCD')
drop trigger trig_insert_BG_ZDCD
go
create trigger trig_insert_BG_ZDCD
on BG_ZDCD
for insert
as
declare @id varchar(10)
select @id=czid from inserted
delete from BG_Ddan where czid=@id
/*insert into BG_ZDCD values(2006101501,'1','3','sf',20)
select * from BG_ZDCD
select * from BG_Ddan*/
go
------------------创建餐桌表和帐单表的触发器--------------------------------------------------------
if exists(select name from sysobjects where name='trig_insert_BG_ZD')
drop trigger trig_insert_BG_ZD
go
create trigger trig_insert_BG_ZD
on BG_ZD
for insert
as
declare @czid varchar(20)
select @czid=czid from inserted
update BG_CTset set cempty='',crs='',cname='',kh='',ctime='',cperson='',cremark='',czk=''
where czid=@czid
/*insert into BG_ZD values('1','A区中餐厅','2','张三','1523654555','06-10-15','06-10-15','fdf',50)
select * from BG_ZD
select * from BG_CTset*/
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -