成都信息工程學(xué)院 數(shù)據(jù)庫實驗報告

實驗1客房預(yù)訂數(shù)據(jù)庫客房預(yù)訂數(shù)據(jù)庫的E-R圖:room根據(jù)E-R圖轉(zhuǎn)化出來的數(shù)據(jù)表結(jié)構(gòu):共有三張表customer*custanendcustan^rrvafTieMtsexcardnanecardid.■^.ddiraEEid.rv:>te實驗2創(chuàng)建客房預(yù)訂數(shù)據(jù)庫和表1.創(chuàng)建數(shù)據(jù)庫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)創(chuàng)建三張表的SQL命令；⑴.創(chuàng)建customer表的SQL代碼：useorderroomcreatetablecustomer(customeridintnotnullprimarykey,customernamevarchar(20)notnull,ageint,sexchar(2),cardnamechar(8)notnull,cardidchar(20)notnull,addressvarchar(30),telvarchar(20),notevarchar(100))⑵?創(chuàng)建room表的SQL代碼：useorderroomCreatetableroom(roomidintnotnull,gradechar(4),priceint,statechar(4),primarykey(roomid)).創(chuàng)建orderdetail的SQL代碼：useorderroomcreatetableorderdetail(roomidintnotnull,customeridintnotnull,orderdatedatetimenotnull,dayssmallintnotnull,notevarchar(100),primarykey(roomid,customerid))三張表的表結(jié)構(gòu)(屏幕截圖)：customer表：列名數(shù)據(jù)類型長度允許空acustomeridint4cuetoniernair&Ch==Ll_20ageint4Wsexchar2c：±t_iiiL：diriscihara>2==iriii-1char20adi-essCh==il-30telCh==Lt~20Wnotewarchar100room表:列名數(shù)據(jù)類型長度先許至roorrii■!int4graiech；±r4priceint4s+ath4orderdetail表:列名數(shù)據(jù)類型長度允許空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學(xué)學(xué)享字字字字字學(xué)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客房預(yù)訂數(shù)據(jù)的簡單和復(fù)雜查詢6.查詢數(shù)據(jù)的SQL代碼：(1)查詢customer表中的所有客戶的信息以及僅查詢客戶姓名。命令：select*fromcustomerselectcustomernamefromcustomer執(zhí)行結(jié)果：

custonerjiarieLJlih23nin4jun5ver6yuh.7ff:8llff9ghdfLDM |rustorwridrurtonername sex cardid.addtess1tlgt■己10Timj^ty□inJUtLV±Trustorwridrurtonername sex cardid.addtess1tlgt■己10Timj^ty□inJUtLV±TTOLLNULLHULL2023NULLNULL軍官證xuemheTig學(xué)生證學(xué)生證學(xué)生證J0003100?]]]]2411233565TOLLNULLHULLHULLMULLNULL TOLLHULL nidiHULL HULL02889986352HULLHULL vt 32]314]516yiih也hffghdfsd22NULLKULLMULL學(xué)生證NULL孝生證NULL學(xué)生證女孝生證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學(xué)生證學(xué)生證學(xué)生證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查詢預(yù)定天數(shù)為2的所有客戶的姓名，年齡，證件名稱，證件號碼。SelectCustomername,age,cardname,cardidfromcustomerjoinorderdetailoncustomer.customerid=orderdetail.customeridwheredays=2SelectCustomername,age,cardname,cardidfromcustomerwherecustomeridin(selectcustomeridfromorderdetailwheredays=2)

Cust□血已rnameag：ecardnaniecardid1ITlITlNULL軍官證100032NULLxuesheng1007（4）將room表中客房價格按90%價格顯示，并且將價格列名改為‘調(diào)價后價格’。select調(diào)價后價格=price*0.9fromroom調(diào)價后價格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）查詢預(yù)訂天數(shù)大于2天的所有客戶的姓名，年齡，證件名稱，證件號碼和預(yù)訂的房間號和房間等級。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學(xué)生證1311112JUTl20學(xué)生證142411wear23與主正252315654yuh21學(xué)生證16洶15±t>iy學(xué)生證17miGhff221s231447sdHILL蘭生正3IIImucustomerriainecardriairisgrad