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

实验1客房预订数据库客房预订数据库的E-R图:room根据E-R图转化出来的数据表结构:共有三张表customer*custanendcustan^rrvafTieMtsexcardnanecardid.■^.ddiraEEid.rv:>te实验2创建客房预订数据库和表1.创建数据库orderroom的SQL命令；CREATEDATABASEorderroomONPRIMARY(NAME="orderroom_data”,FILENAME='C:\ProgramFiles\MicrosoftSQLServer\MSSQL\Data\orderroom_data.mdf,SIZE=2MB,MAXSIZE=5MB,FILEGROWTH=1MB)LOGON(NAME="orderroom_log”,FILENAME='F:\ProgramFiles\MicrosoftSQLServer\MSSQL\Data\orderroom_log.ldf,SIZE=1MB,MAXSIZE=5MB,FILEGROWTH=1MB)创建三张表的SQL命令；⑴.创建customer表的SQL代码：useorderroomcreatetablecustomer(customeridintnotnullprimarykey,customernamevarchar(20)notnull,ageint,sexchar(2),cardnamechar(8)notnull,cardidchar(20)notnull,addressvarchar(30),telvarchar(20),notevarchar(100))⑵・创建room表的SQL代码：useorderroomCreatetableroom(roomidintnotnull,gradechar(4),priceint,statechar(4),primarykey(roomid)).创建orderdetail的SQL代码：useorderroomcreatetableorderdetail(roomidintnotnull,customeridintnotnull,orderdatedatetimenotnull,dayssmallintnotnull,notevarchar(100),primarykey(roomid,customerid))三张表的表结构(屏幕截图)：customer表：列名数据类型长度允许空acustomeridint4cuetoniernair&Ch==Ll_20ageint4Wsexchar2c：±t_iiiL：diriscihara>2==iriii-1char20adi-essCh==il-30telCh==Lt~20Wnotewarchar100room表:列名数据类型长度先许至roorrii■!int4graiech；±r4priceint4s+ath4orderdetail表:列名数据类型长度允许空roumidint4custumyridint4order-late■late11me8daysmillint2noteV!di-chid!-100V三张表中增加的10条记录(屏幕截图)(1)customer表：

custamerLicuKicmeriianeage►u[TiiiL«ULL>g醐custamerLicuKicmeriianeage►u[TiiiL«ULL>g醐®L>您dnmi

liiT-EW10H121314151617Je-nlnm忙.泸匝正如sdsbn-_I.J1inbH..L门£?门..」IP」9LII...」rig—Lllr—Lllr—Lllr—Lllr—Lllr—Lllr—LllrlllrlllrdITizlzlzlzlzl9侦TiTH5学学享字字字字字学cardidaddresstelnote工皿:皿，虻：〈NIEL；<KJLL)Jll，虹•〈NIEU<KJLL)Hll038明聂祝/序52m<1TJLL)2131<1TJLL)浏I<NILL)<1TJLL)l澎<NILL；<1TJLL)Jill<NILL；<KJLL)12345<NILL；<1TJLL)(2)orderdetail表:roomidCTistonieridorderdatelaysnote172012-11-112<mjLL>282012-11-102null392012-10-103<mjLL>4102012-10-105CffULL>5112012-11-198■ve6122012-10-295<ffULL>7132012-11-164<lnJLL>8142012-11-183<mjLL>9152012-11-111<mjLL>10162012-11-106ClfULL>►(3)room表：roomid.grad,eprlcestate111000<WULL>22500<1TULL>311000411000<NULL>52500wu611000wu711000you811000YTU25QQ<1TULL>IQ325QJr'；'U实验3客房预订数据的简单和复杂查询6.查询数据的SQL代码：(1)查询customer表中的所有客户的信息以及仅查询客户姓名。命令：select*fromcustomerselectcustomernamefromcustomer执行结果：

custonerjiarieLJlih23nin4jun5ver6yuh.7ff:8llff9ghdfLDM |rustorwridrurtonername sex cardid.addtess1tlgt■己10Timj^ty□inJUtLV±Trustorwridrurtonername sex cardid.addtess1tlgt■己10Timj^ty□inJUtLV±TTOLLNULLHULL2023NULLNULL军官证xuemheTig学生证学生证学生证J0003100?]]]]2411233565TOLLNULLHULLHULLMULLNULL TOLLHULL nidiHULL HULL02889986352HULLHULL vt 32]314]516yiih也hffghdfsd22NULLKULLMULL学生证NULL孝生证NULL学生证女孝生证24-tl2]'31231151235611111HULL NULLNULL HULLKULL NULLNULL HULLKULL NULLMULLNULLKULLNULLKULL(2)查询出customer表中的前十个或前面10%的客户信息。selecttop10*fromcustomerselecttop10percent*fromcustomerGurtajierid|cuFtomemajieasesexcardriane|cardid address!tel riote7ninjattyrdJiJLLDvetWU1LNULLWULLGurtajierid|cuFtomemajieasesexcardriane|cardid address!tel riote7ninjattyrdJiJLLDvetWU1LNULLWULL2023-EilKueshans学生证学生证学生证10QD81007UH2411231565HULLHULLNULLMULLPULLHULL WILLHULL nullNULL NULL029699^6352MULLHULL6*21W馈'IiF那1HULLHULLNULL厂LJiff]9wi.i.¥'liF2]31NULLNULLNULL8:ihf£22Ml.l.r±lL23144MULLMULLMULL9ehdfWILLeilt±ilL2356HULLHULLMULLsdNULL心trL111LHULLHULLNULL■zui'tai-ierid[:u.5~tDnE-rnuiEe-sex.curdriHJi.'SunxHlcLB[ld.EE32■fcelnateL7 ninK二LKJLL*户iF 10：：3HULLHULLBULL查询预定天数为2的所有客户的姓名，年龄，证件名称，证件号码。SelectCustomername,age,cardname,cardidfromcustomerjoinorderdetailoncustomer.customerid=orderdetail.customeridwheredays=2SelectCustomername,age,cardname,cardidfromcustomerwherecustomeridin(selectcustomeridfromorderdetailwheredays=2)

Cust□血已rnameag：ecardnaniecardid1ITlITlNULL军官证100032NULLxuesheng1007（4）将room表中客房价格按90%价格显示，并且将价格列名改为‘调价后价格’。select调价后价格=price*0.9fromroom调价后价格1soo.a2450.a3boo.a4900.05450.a6soo.a79oo.a8boo.a9450.010225.a查询customer表中的客户信息，并以姓名、电话、地址作为列名。SelectCustomernameas姓名,telas电话,addressas地址fromcustomerSelectCustomername姓名,tel电话,address地址fromcustomerSelect姓名=Customername,电话=tel,地址=addressfromcustomer姓名丑甘 址MTO-nW「.Ljft+iyW「.T.mm?HLL姓名电话地址2jettyNULLNULL3niiTLNULLNULL4juii02889966352NULL5NULLNULL6yuhNULLNULL7ffgNULLNULL8hffNULLNULL9ghdfNULLNULLIDsdNULLNULL姓名电话地址1minNULLNULL2jettyNULLNULL3nimNULLNULL402839966352NULL5werNULLNULL6j.hjJ-lNULLNULL7ffgNULLNULL8hffNULLNULL9ghdfNULLNULL10siNULLNULL查询room表，显示客房价格在150到200之间的客房信息。Select*fromroomWherepricebetween150and200roomidgradepricesrtate从客房表中找出客房价格大于200的客房信息，并按照升序和降序显示。Select*fromroomWhereprice>200orderbypricedescSelect*fromroomWhereprice>200orderbypriceasc

roonidgradepricestatd1I]innnNULL311000you3411LILILINULL45 1!□□□wu\711LILILIyou6511000wu7525DDuni822500NULL992500HULLroonidsiadepricestate1「i〕:250you.292500NULL3;RnnTr.-.452500wu5611LILILIwuJ7]innnyou11LILILIwu8I11000NULL93]!□□□yau.11111000（8）找出姓王的所有客户。Select*fromcustomerWherecustomernamelike'%王％'agecardnajnecardidaddress|telnoi&custQjneridcustojnername（9）查询预订天数大于2天的所有客户的姓名，年龄，证件名称，证件号码和预订的房间号和房间等级。Selectcustomername,age,cardname,grade,room.roomid,customer.cardidFromorderdetailjoinroomonorderdetail.roomid=room.roomidJoincustomeroncustomer.customerid=orderdetail.customeridWheredays>2selectcustomername,age,cardname,grade,b.roomid,cardidfromorderdetailajoinroombona.roomid=b.roomidjoincustomerconc.customerid=a.customeridwheredays>2

custoiRDmanieagecardi-iam.egraderoomidcardid1riiiLNULL学生证1311112JUTl20学生证142411wear23与主正252315654yuh21学生证16汹15±t>iy学生证17miGhff221s231447sdHILL兰生正3IIImucustomerriainecardriairisgrad