成都信息工程学院数据库实验报告

1、实验1客房预订数据库(1)客房预订数据库的E-R图:性别年龄证件名称证件号码客户名称地址联系电话customer备注信息属于时间房间等级room房间价格房间状态房间编号客户编号(2) 根据E-R图转化出来的数据表结构:共有三张表customer +ciastsnieri AseKeardnaneuurdi daddr EX.Ktilnoteorderdclailroom实验2创建客房预订数据库和表1.创建数据库orderroom的SQL命令；CREATE DATABASE orderroomON PRIMARY(NAME=orderroom_data,FILENAME=C:Program Fi

2、lesMicrosoft SQL ServerMSSQLDataorderroom_data.mdf, SIZE=2MB,MAXSIZE=5MB, FILEGROWTH=1MB)LOG ON(NAME=orderroom_log,FILENAME=F:Program FilesMicrosoft SQL ServerMSSQLDataorderroom_log.ldf, SIZE=1MB,MAXSIZE=5MB, FILEGROWTH=1MB)2. 创建三张表的 SQL 命令；(1) .创建 customer 表的 SQL 代码 : useorderroom create table cust

3、omer( customeridint not null primary key, customernamevarchar(20) not null, ageint, sex char(2), cardname char(8) not null, cardid char(20) not null, addressvarchar(30), telvarchar(20), notevarchar(100)(2) .创建room表的SQL代码：useorderroom Create table room (roomidint not null, grade char(4), priceint, st

4、ate char(4), primary key(roomid)(3) . 创建 orderdetail 的 SQL 代码 ：useorderroomcreate table orderdetail (roomidint not null,customeridi nt not n ull, orderdatedatetime not nu II, dayssmall int not n ull, notevarchar(IOO), primary key(roomid,customerid) )3.三张表的表结构(屏幕截图)：customer 表:列名1数菇类型1低度允许空1customeri

5、dint4cxistimernanevar chir20ag-e：int47charZcardnafnechar8car di dchar20addressvar char30telvar char20notevar char100room表:列名数据类型长度允许空dint4gr&deuhar4priceint4stat echar4orderdetail 表:列名數据类型长度允许空|?roomi dint4customeri dint4rderdatedal line3daysEm all int2noterar ch ar1004.三张表中增加的10条记录(屏幕截图)(1) custome

6、r 表:CKisUnerid910111213同1516customernanecar didtddrtss证军ng価证证证证证证证证证5 兰 一一 4 一 ,一 二二二二 | M3 - 4- H3- 4- 42 42 43 U3 XU学学学学学学学学学443121389966352WDOIULL)ML)onmGnjlL.WDmnD1WML)(3) room 表:(2) orderdetail 表:rocnidl| euEtomridorderd&te112012-11-1122e2012-11-102liull392012-10-1034102012-10-1055li2012-11-19e

7、we6122012-10-2957132012-11-164S142012-11-1839152012-11-11110162012-11-106卜实验3客房预订数据的简单和复杂查询6.查询数据的SQL代码：(1)查询customer表中的所有客户的信息以及仅查询客户姓名。 命令：select * from customerselectcustomer name from customer执行结果：cuhtojiGFid cuEii： oil农rnuit 蓉匕sex cardnajuc car didaddress telRole17itinHULLWLL军官iiE10008HULLNULLP

8、TULL2BNULLNULL xu&shtnE1007HULLBULLnull39ninNULLHWLL学性证1111NULLNULLm_rLL410jun20男 学生证2411HULL02389966362WULL511wer23玄学宦证231565NULLNULLWB612yuh21男学尘证2441NULLNULLNULL71319HULL牡证2131HULLHULLNULL814hff22NULL骨土证23144HULLHUU.NULL915ghdfNULLNULL孚注证12356HULLWULLDTULlDO10sdNULL女学生证11119HULLHULLNULLcuslaiiema

9、j 日 nin jetty nin junffg hff shdf(2)查询出customer表中的前十个或前面10%的客户信息。select top 10 * from customer7891011min卯切12KULL肌ILL军官证HULL KULL Huesher勇女男UliNULLMULLirx:.24UNULLD2S8996S352HULL23t565HULLMULLTC2141HULLHULLHULL1000810D7SULL MULLMULLHULL HULLnull3 4 6 6- 1A .11 1X.hf2131NULLNULLHULL23NULLNULLNULL12366

10、MILLMULLSULL11111NULLHULLHULL|custoiieridlcustomemameageSBXcardrisuiecardid7minNULLNULL军官证10 DOBaddress tsl noteNULL NULL HJLLselect top 10 perce nt * from customercustomemameageSBS：cazdriQJtecardiidaddresstelTLOl B.cumtanerid(3) 查询预定天数为2的所有客户的姓名，年龄，证件名称，证件号码。Select Customer name,age,card name,cardi

11、dfrom customer join orderdetail on customer.customerid=orderdetail.customerid where days=2Select Customer name,age,card name,cardidfrom customerwherecustomerid in(selectcustomerid from orderdetailwhere days=2)1Cust onernameagecardnamecardidjuinNULL军官证100032jettyNULLKuesherig1007(4) 将room表中客房价格按 90%价

12、格显示，并且将价格列名改为调价后价格select 调价后价格=price*0.9 from room1调价后价格900. 02450. 03900- 04900* 05450. 06900* 07900. 0&900* 0M450- 010226. 0(5) 查询customer表中的客户信息，并以姓名、电话、地址作为列名。Select Customer name as 姓名,tel as 电话 address as 地址 from customerSelect Customer name 姓名,tel 电话,address 地址 from customerSelect 姓名=Customer

13、name,电话=tel, 地址=address from customer1姓名电话地址mirLNULLNULL2jettyNULLNULL3nimNULLNULL4jun02889966352NULL5werNULLNULL6yuhNULLNULL7ffgNULLNULL8hffNULLNULL9ghdfNULLNULL10SdNULLNULL2姓名电话地址jettyNULLNULL3nimNULLNULL4jun !02889965352NULL5werNULLNULL6yuhNULLNULL7fffiNULLNULL8hffNULLNULLgghdfNULLNULL10sdNULLNUL

14、L姓名电话地址1inHULLNULL2jetty HULLNULL3nimNULLNULL4jun02889955352NULL5werNULLNULL6yuhNULLNULL7ffeFULLNULLShffNULLNULL910EhdfHULLNULLsdPTULLNULL：(6)查询room表，显示客房价格在150到200之间的客房信息。Select * from roomWhere price between 150 and 200roDjnidgradepricestate(7)从客房表中找出客房价格大于200的客房信息，并按照升序和降序显示。Select * from roomWhe

15、re price200 order by price descSelect * from roomWhere price200 order by price ascSelect * from customerWhere customer name like % 王 %|cust OBeridcustoms mameagesescardnajnecardidaddresstelnot e(9)查询预订天数大于2天的所有客户的姓名，年龄，证件名称，证件号码和预订的房间号和房间等级。Select customer name,age,card name,grade,room.roomid,custom

16、er.cardidFrom orderdetail join room on orderdetail.roomid=room.roomidJoin customer on customer.customerid=orderdetail.customeridWhere days2selectcustomer name,age,card name,grade,b.roomid,cardidfrom orderdetail a join room b on a.roomid=b.roomidjoin customer c on c.customerid=a.customeridwhere days2

17、1cast osemameafecardnanegraderoomidcardidninNULL学生证13;11l2jun2D学生证1q24113war23学主证 252316664yuh21学生证162441e19学主还1T21316hff22学生证18231447sdNULL学生证310mu1CUSt sEtiailBagacardnaiiBgraderoomideardidninNULL学生证13111!2jiun20学生证1424113ver23学生证2523L5654yuh21学生证162441sffg19学空证1?21316hff22学生证 港证13823144TsdNULL1011111(10)查询客户地址在成都的客户所预定的房间号，房间价格和房间等级Select room.roomid,price,gradeFrom orderdetail join room on orderdetail.roomid=room.roomidJoin customer on customer.customerid=orderdetail.customeridWhere address like % 成都 %rocunidpricegrade .Select b.roomid,price,grade from orderdetail a join roo