查询1.sql

酒店管理系统后台数据库存储过程

use rummery

--删除

--delete from  roomstatus--
--delete from register--
--delete from account--
--delete from  dailyEarning--
--update Roomstatus set onuse = '0'--

--

--查询--

--更新--
--


select * from employee
select * from admin
select * from onduty


select * from dailyearning

select * from account
select * from register
select * from roomstatus

insert into register values ('4001','sds','2007-07-28','2007-08-29',20,30,330,221,'bvbv','bvb','12121','vbv','',,,,,,


select * from employee where id = 'sh1001'

select ID,name,sex,nationality,age,IDCard,native,address,phone,title,remark from employee




select roomNo from roomStatus where class = '标准间' and onuse = '0'

select name,AccountNo,rebatePri,deposit,reckoning,operator from Account where roomno = 'C2003'

select accountNo,name,roomNo,Class,reckoning,isreturnrm,operator from Account

select collectNO ,ondate,totalBeforeTax,tax,totalAfterTax,operator,remark from dailyearning

select sum(reckoning) from account where accountNo in( select accountNo  from register where checkInDate = '2007-08-28')

select sum(reckoning) from account where accountNo in( select accountNo  from register where checkInDate = '2008-08-28')

select checkInDate,checkOutdate,total from register where roomNo = 'c2003'


select ID,name,sex,nationality,age,IDCard,native,address,phone,title,remark from employee  where id = 'sh1001'
select ID,title,name,sex,nationality,age,IDCard,native,address,phone,remark from employee



insert into onduty values ('2007-08-21','部长1','否','经理','1','检查工作','13030002656','')

insert into onduty values ('2007-8-22','部长2','否','经理','2','检查工作','13030002656','')

insert into onduty values ('2007-8-23','部长3','否','经理','3','检查工作','13030002656','')

insert into onduty values ('2007-8-24','部长4','否','经理','4','检查工作','13030002656','')

insert into onduty values ('2007-8-25','部长5','否','经理','1','检查工作','13030002656','')

insert into onduty values ('2007-8-26','部长6','否','经理','2','检查工作','13030002656','')

insert into onduty values ('2007-8-27','部长7','否','经理','3','检查工作','13030002656','')

insert into onduty values ('2007-8-28','部长8','否','经理','4','检查工作','13030002656','')

insert into onduty values ('2007-8-29','部长9','否','经理','1','检查工作','13030002656','')

select * from onduty

--插入测试数据
--插入普通房间数据
insert into roomstatus (id,roomNo ,class ,standardpri,rebatepri,onuse,place,roomIntro,remark)
values                 (10001,'C2001','普通',100,80,0,'二楼','向阳','可以看海')

insert into roomstatus (id,roomNo ,class ,standardpri,rebatepri,onuse,place,roomIntro,remark)
select                  10002,'C2002','普通',100,80,0,'二楼','背阳',''  union
select                  10003,'C2003','普通',100,80,0,'二楼','向阳','可以看海'   union
select                  10004,'C2004','普通',100,80,0,'二楼','背阳',''   union
select                  10005,'C2005','普通',100,80,0,'二楼','向阳','可以看海'  union
select                  10006,'C2006','普通',100,80,0,'二楼','背阳',''   union
select                  10007,'C2007','普通',100,80,0,'二楼','向阳','可以看海'  union
select                  10008,'C2008','普通',100,80,0,'二楼','背阳',''   union
select                  10009,'C2009','普通',100,80,0,'二楼','向阳','可以看海'   union
select                  10010,'C2010','普通',100,80,0,'二楼','背阳',''   union
select                  10011,'C2011','普通',100,80,0,'二楼','向阳','可以看海'   union
select                  10012,'C2012','普通',100,80,0,'二楼','背阳',''   union
select                  10013,'C2013','普通',100,80,0,'二楼','向阳','可以看海' union
select                  10014,'C2014','普通',100,80,0,'二楼','背阳',''   union
select                  10015,'C2015','普通',100,80,0,'二楼','向阳','可以看海'   union
select                  10016,'C2016','普通',100,80,0,'二楼','背阳',''   union
select                  10017,'C2017','普通',100,80,0,'二楼','向阳','可以看海'   union
select                  10018,'C2018','普通',100,80,0,'二楼','背阳',''   union
select                  10019,'C2019','普通',100,80,0,'二楼','向阳','可以看海'   union
select                  10020,'C2020','普通',100,80,0,'二楼','背阳',''  




--插入标准房间数据
insert into roomstatus (id,roomNo ,class ,standardpri,rebatepri,onuse,place,roomIntro,remark)
select                  20001,'B3001','标准',200,180,0,'三楼','向阳','可以看海'   union
select                  20002,'B3002','标准',200,180,0,'三楼','背阳',''   union
select                  20003,'B3003','标准',200,180,0,'三楼','向阳','可以看海'   union
select                  20004,'B3004','标准',200,180,0,'三楼','背阳',''   union
select                  20005,'B3005','标准',200,180,0,'三楼','向阳','可以看海'   union
select                  20006,'B3006','标准',200,180,0,'三楼','背阳',''   union
select                  20007,'B3007','标准',200,180,0,'三楼','向阳','可以看海'   union
select                  20008,'B3008','标准',200,180,0,'三楼','背阳',''   union
select                  20009,'B3009','标准',200,180,0,'三楼','向阳','可以看海'   union
select                  20010,'B3010','标准',200,180,0,'三楼','背阳',''   union