【文章內(nèi)容簡介】 ypeNo) on update cascade, ) Create TABLE ClientInfo ( ClientID char(8) PRIMARY KEY, ClientName varchar(30) not null, ClientSex char(4), ClientCertificateID varchar(18) not null, ClientBirthday datetime, ClientPhone varchar(12) not null, ClientBookOrNot int, ClientAdress varchar(50), ClientConsume money, PaymentTime datetime, ClientRemarks varchar(40), ) CREATE TABLE LoginInfo (EmployeeId char(8) PRIMARY KEY, PassWord char(6) not null, Restricte int not null, ) create table ReInfo (ClientID char(8) NOT NULL, RoomNo int NOT NULL, LiveDate datetime, OutDate datetime, DayNo AS OutDateLiveDate, primary key (RoomNo,ClientID), foreign key (RoomNo) references RoomInfo(RoomNo), FOREIGN KEY (ClientID) REFERENCES ClientInfo(ClientID) ) 創(chuàng)建索引 create index sroominfo on ReInfo (ClientID ASC, RoomNo) use MC酒店前臺(tái)管理系統(tǒng) GO EXEC sp_helpindex ReInfo GO create index sclientinfo on ClientInfo (ClientID ASC, ClientName) use MC酒店前臺(tái)管理系統(tǒng) GO EXEC sp_helpindex ClientInfo GO 六 . 利用 TSQL 命令增加、刪除、修改數(shù)據(jù) 。 插入數(shù)據(jù) ：登記客戶基本信息，入住信息，管理客房信息 USE MC酒店前臺(tái)管理系統(tǒng) INSERT INTO ClientInfo values (39。C000000439。,39。馬冰潔 39。,39。女39。,39。34222219930123002X39。,39。1993012339。,39。592719526039。,2,39。中國安徽省39。,1000,39。20200710 09:06:0739。,39。需要早餐 39。) INSERT INTO ClientInfo values (39。C000000139。,39。林靜 39。,39。男 39。,39。34222219821123002X39。,39。1982112339。,39。1878787555539。,0,39。中國北京市朝陽路 39。,3100,39。20200710 09:06:0739。,39。每日：分前不要打掃其房間 39。) INSERT INTO ClientInfo values (39。C000000239。,39。葉華 39。,39。男 39。,39。34222219880123002239。,39。1988012339。,39。1878787444439。,0,39。中國廈門市鼓浪嶼 39。,3000,39。20200719 19:06:0739。,39。每日換枕頭 39。) INSERT INTO ClientInfo values (39。C000000339。,39。素素 39。,39。女 39。,39。34222219821123002X39。,39。1982112339。,39。1878787555539。,1,39。中國北京市朝陽路 39。,3100,39。20200710 09:06:0739。,39。對(duì)海鮮過敏 39。) INSERT INTO RoomInfo values (8204,2,4,39。39。) INSERT INTO RoomInfo values (5203,2,4,39。39。) INSERT INTO RoomInfo values (2222,2,4,39。39。) INSERT INTO RoomInfo values (8888,1,5,39。39。) INSERT INTO RoomInfo values (3402,0,1,39。39。) INSERT INTO RoomInfo values (2202,2,2,39。39。) INSERT INTO RoomInfo values (2201,1,2,39。39。) INSERT INTO RoomStatus values (2,39。不空閑 39。,178) INSERT INTO RoomStatus values (0,39。已預(yù)訂 39。,20) INSERT INTO RoomStatus values (1,39?？臻e 39。,99) INSERT INTO RoomType values (4,39。商務(wù)套房 39。,80,400) INSERT INTO RoomType values (1,39。單人房 39。,40,120) INSERT INTO RoomType values (2,39。標(biāo)準(zhǔn)間 39。,100,150) INSERT INTO RoomType values (5,39?？偨y(tǒng)套房 39。,2,1888) INSERT INTO RoomType values (3,39。豪華間 39。,60,270) INSERT INTO LoginInfo values (39。A000000739。,39。WWWNDX39。,0) INSERT INTO LoginInfo values (39。B000000139。,39。12345639。,1) INSERT INTO ReInfo values (39。C000000139。,8204,39。20200705 09:06:0739。,39。20200710 09:06:0739。) INSERT INTO ReInfo values (39。C000000239。,5203,39。20200704 19:06:0739。,39。20200710 09:06:0739。) INSERT INTO ReInfo values (39。C000000339。,2222,39。20200709 09:06:0739。,39。20200713 19:06:0739。) INSERT INTO ReInfo values (39。C000000439。,2202,39。20200709 09:06:0739。,39。20200713 19:06:0739。) 修改數(shù)據(jù) USE MC酒店前臺(tái)管理系統(tǒng) GO 客戶調(diào)房間 update ReInfo set RoomNo=2201 where ClientID=39。C000000439。 客戶入住修改房間狀態(tài) update RoomInfo set StatusNo=2 where RoomNo=2202 將客戶的消費(fèi)減去 200元，但因?yàn)橹笤O(shè)置了一個(gè)保護(hù)安全性的觸發(fā)器所以這個(gè)應(yīng)該執(zhí)行不了 update ClientInfo set ClientConsume=ClientConsume200 where ClientID=39。C000000439。 刪除數(shù)據(jù) 客戶退房 delete from ReInfo where ClientID=39。C000000439。 刪除客戶信息 delete from ClientInfo where ClientName=39。馬冰潔 39。 七 . 利用 TSQL 命令進(jìn)行數(shù)據(jù)的檢索和統(tǒng)計(jì) 根據(jù)自己設(shè)計(jì)的數(shù)據(jù)庫和輸入的數(shù)據(jù)，寫出至少 10 個(gè)查詢要求及對(duì)應(yīng)的 SQL 查詢命令，應(yīng)該包含如下功能： 投影和選擇； 多表連接； 分組統(tǒng)計(jì)與 HAVING； 子查詢； 查詢結(jié)果保存。 USE MC酒店前臺(tái)管理系統(tǒng) Go 詢所有顧客姓名 SELECT ClientName from ClientInfo SELECT RoomNo from RoomInfo where StatusNo=1 SELECT ,RoomNo,ClientName,ClientSex,ClientRemarks from ClientInfo,ReInfo where = select ClientSex,Count(ClientSex) as 人數(shù) from ClientInfo group by ClientSex 均收入大于 1000的客房類型 SELECT TypeName,AVG(ClientConsume) AS 平均收入 from RoomType,ClientInfo,ReInfo,RoomInfo where = and = and = Group by TypeName Having AVG(ClientConsume)1000 RoomInfo表查詢共有幾種類型的客房 SELECT TypeNo from RoomInfo GROUP BY TypeNo HAVING COUNT(*)2 客房類型的 客房 號(hào) SELECT RoomNo from RoomInfo WHERE TypeNo IN(SELECT TypeNo from RoomInfo GROUP BY TypeNo HAVING COUNT(*)2) SELECT * from RoomType where Price(SELECT AVG(Price) from RoomType) XXX的客戶姓名 SELECT ClientName from ClientInfo where ClientSex=39。男 39。 AND ClientAdress LIKE 39。中國北京市 %39。 select RoomNo,StatusNo,RoomRemarks from RoomInfo where TypeNo=(select TypeNo from RoomType where TypeName=39。商務(wù)套房 39。) 八 . 利用 SQL 命令創(chuàng)建視圖對(duì)象 根據(jù)開發(fā)的系統(tǒng)的需要，至少設(shè)計(jì)三個(gè)視圖：基于單表的、包含多表連接的、包含統(tǒng)計(jì)運(yùn)算的。 use MC酒店前臺(tái)管 理系統(tǒng) go VIP顧客基本信息視圖 create view clientVIPinfo as select ClientID,ClientName,ClientSex,ClientCertificateID,ClientBirthday,ClientPhone,ClientConsume from ClientInfo where ClientConsume2020 select * from clientVIPinfo create view clientmaninfo as select ClientID,ClientName,ClientSex,ClientCertificateID,ClientBirthday,ClientPhone from ClientInfo where ClientSex=39。男 39。 from ClientInfo select * from clientmaninfo create view roomreinfo as select ,ClientName,ClientSex,ClientCertificateID,ClientBirthday,ClientPhone,RoomNo,LiveDate,OutDate from ClientInfo,ReInfo where RoomNo=2202 and = 空閑房間基本信息 視圖 create view freeroom as select ,RoomRemarks from RoomStatus,RoomInfo,RoomType where RoomStatus=39?？臻e 39。 and = and = select * from freeroom 每種房間的總收入視圖 create view zxf as select ,SUM(ClientConsume) as SumConsume from ClientInfo,ReInfo,RoomInfo,RoomType wh