歷韻攵逛大孝2012-2013學(xué)年第二學(xué)期《數(shù)據(jù)庫(kù)原理與設(shè)計(jì)》

課程實(shí)驗(yàn)報(bào)告學(xué)號(hào)： 學(xué)生姓名： 班級(jí)：軟件班教師：陶宏才輔導(dǎo)老師：劉寶菊、張建華2013年12月第#頁(yè)共13頁(yè)實(shí)驗(yàn)一表的創(chuàng)建一、實(shí)驗(yàn)?zāi)康?、?nèi)容、原理表orderdetail利用SQL創(chuàng)建“人員”表person、“月薪”表salary、“顧客”表customer表orderdetail二、實(shí)驗(yàn)代碼、結(jié)果與問(wèn)題1.實(shí)驗(yàn)代碼CREATETABLEcustomer20112736(cust_nochar(6)NOTNULLPRIMARYKEY,cust_namevarchar(10)NOTNULL,sexchar(2)NOTNULL,birthdatedatetimeNULL,cityvarchar(10)NULL,discountdec(3,2)NOTNULLDEFAULT1.00,CONSTRAINTdiscount20112736CHECK(discount>=0.50ANDdiscount<=1.00))CREATETABLEperson20112736(p_nochar(6)NOTNULLPRIMARYKEY,p_namevarchar(10)NOTNULL,sexchar(2)NOTNULL,birthdatedatetimeNULL,date_hireddatetimeNOTNULL,deptnamevarchar(10)NOTNULLDEFAULT'培訓(xùn)部',p_bosschar(6)NULL,CONSTRAINTdate_limitCHECK(birthdate<date_hired))CREATETABLEorderdetail20112736(order_nochar(6)NOTNULLPRIMARYKEYCONSTRAINTorder_no20112736CHECK(order_noLIKE'[A-Z][A-Z][0-9][0-9]'),cust_nochar(6)NOTNULLCONSTRAINTcust_no20112736REFERENCEScustomer20112736(cust_no)ONDELETECASCADEONUPDATECASCADE,p_nochar(6)NOTNULLCONSTRAINTp_no20112736

REFERENCESperson20112736(p_no),order_totalintNOTNULL,order_datedatetimeNOTNULL)CREATETABLEsalary20112736(p_nochar(6)NOTNULLPRIMARYKEYCONSTRAINTwaijian20112736REFERENCESperson20112736(p_no)ONDELETECASCADEONUPDATECASCADE,basedec(5)NULL,bonusdec(5)NULL,factASbase+bonus,J結(jié)果LB消息)2.實(shí)驗(yàn)結(jié)果-3結(jié)果消息p_nap_namesexbirthdatedatehireddeptnamep_bossJ結(jié)果.J］消息order_nocust_nop_noonderjotalonder_dateJ結(jié)果消息dtydiscountcustnocostnamesexbirthdatedtydiscount3.遇到的問(wèn)題及解決方法在最后一個(gè)’(‘的前邊使用了逗號(hào)，刪除后解決問(wèn)題實(shí)驗(yàn)二 操縱子語(yǔ)言一、實(shí)驗(yàn)?zāi)康摹?nèi)容、原理利用SQL向表person、salary、customer及orderdetail中插入數(shù)據(jù)。二、實(shí)驗(yàn)代碼、結(jié)果與問(wèn)題實(shí)驗(yàn)代碼INSERTINTOcustomer20112736VALUES('000001','王云','男','1972-01-30','成都',1.00)INSERTINTOcustomer20112736VALUES('000002','林國(guó)平','男','1985-08-14','成都',0.85)INSERTINTOcustomer20112736VALUES('000003','鄭洋','女','1973-04-07','成都',1.00)INSERTINTOcustomer20112736VALUES('000004','張雨潔','女','1983-09-06','北京',1.00)INSERTINTOcustomer20112736VALUES('000005','鄭箐'，'女','1971-08-20','北京',0.95)INSERTINTOcustomer20112736VALUES('000006','李宇中','男','1979-08-06','上海',1.00)INSERTINTOcustomer20112736VALUES('000007','顧培銘','男','1973-07-23','上海',1.00)INSERTINTOorderdetail20112736VALUES('AS58','000006','000002',150000,'2006-04-05')INSERTINTOorderdetail20112736VALUES('AS43','000005','000005',90000,'2006-03-25')INSERTINTOorderdetail20112736VALUES('AS30','000003','000001',70000,'2006-02-14')INSERTINTOorderdetail20112736VALUES('AS12','000002','000005',85000,'2005-11-11')INSERTINTOorderdetail20112736VALUES('AS11','000007','000009',130000,'2005-08-13')INSERTINTOorderdetail20112736VALUES('AS08','000001','000007',43000,'2005-06-06')INSERTINTOorderdetail20112736VALUES('AS05','000001','000007',72000,'2005-05-12')INSERTINTOorderdetail20112736VALUES('BU67','000007','000003',110000,'2005-03-08')INSERTINTOorderdetail20112736VALUES('BU43','000004','000008',70000,'2004-12-25')INSERTINTOorderdetail20112736VALUES('BU39','000002','000005',90000,'2004-10-12')INSERTINTOorderdetail20112736VALUES('BU32','000006','000002',32000,'2004-08-08')INSERTINTOorderdetail20112736VALUES('BU21','000004','000006',66000,'2004-04-01')INSERTINTOorderdetail20112736VALUES('CX44','000007','000009',80000,'2003-12-12')INSERTINTOorderdetail20112736VALUES('CX32','000003','000001',35000,'2003-09-18')INSERTINTOorderdetail20112736VALUES('CX25','000002','000003',90000,'2003-05-02')INSERTINTOorderdetail20112736VALUES('CX22','000001','000007',66000,'2002-12-04')INSERTINTOperson20112736VALUES('000001','林峰'，'男','1973-04-07','2003-08-03','銷售部','000007')INSERTINTOperson20112736VALUES('000002','謝志文','男','1975-02-14','2003-12-07','培訓(xùn)部','000005')INSERTINTOperson20112736VALUES('000003','李浩然','男','1970-08-25','2000-05-16','銷售部','000007')INSERTINTOperson20112736VALUES('000004','廖小玲','女','1979-08-06','2004-05-06','培訓(xùn)部','000005')INSERTINTOperson20112736VALUES('000005','梁玉瓊','女','1970-08-25','2001-03-13','培訓(xùn)部',NULL)INSERTINTOperson20112736VALUES('000006','羅向東','男','1979-05-21','2000-07-09','銷售部','000007')INSERTINTOperson20112736VALUES('000007','肖家慶'，'男','1963-07-14','1998-06-06','銷售部',NULL)INSERTINTOperson20112736VALUES('000008','李浩然','男','1975-01-30','2002-04-12','培訓(xùn)部','000007')INSERTINTOperson20112736VALUES('000009','趙文龍','男','1969-04-20','1996-08-12','銷售部','000007')INSERTINTOsalary20112736VALUES('000001',2100,300)INSERTINTOsalary20112736VALUES('000002',1800,300)INSERTINTOsalary20112736VALUES('000003',2800,280)INSERTINTOsalary20112736VALUES('000004',2500,250)

INSERTINTOsalary20112736VALUES('000005',2300,275)INSERTINTOsalary20112736VALUES('000006',1750,130)INSERTINTOsalary20112736VALUES('000007',2400,210)INSERTINTOsalary20112736VALUES('000008',1800,235)INSERTINTOsalary20112736VALUES('000009',2150,210)實(shí)驗(yàn)結(jié)果onder_nocust_noonder_nocust_noP_na□nderjatalonder_date1AS05OTDD01二靠00772CDD200&05-1200:00:00(M?2ASDSOTDOT1DDDD0743CDD20054?4?￡。:靠:瓶比口3AS11案2007案20091出口叩2ffi｝中晤13叫靠:血比口4AS12(MMM02OTO0O5喇叩2ffi｝^11-11叫靠:血比口5AS加0MM03omoi押叩20耐”14叫靠:血比口6AS430MM050WD059口口叩20聆叫25叫靠:血比口7AS5S靠二噸600000215CDDD20064144)5CC:C0:0D.DCDBU21000004靠二D06金口口2DD44J44J1CC:C0:01DCD9BU32靠二0000023卻叩呼叫譏:靠加口1DBU33DMM02OTO0O59TO002004-1(M200OTD0QW11BU434MMMO4000008700DD2004-12-25OT：niniD[]D12BU67二靠m7口靠口阻110000靠:6口口口13CX22OTDD01DDDD07聊叩2DD2-12-fl40C:CO：[]lDCD14CX25000002北。D039DDDD20034154)2CC:C0:01DCD15CX32000003OTDD0135CDD那叫眸18DC:靠:亞比口16CX44OTOT07000009日口口叩21?3-12-1200001)0(?0J結(jié)果消息P_nobasebonusfact1HDDDD1210022523252仆口口m21&DD225202530000032&DD210301D4&DDDD423M1SS268s500000523加20C250660000061&DD12019207DDD00724DD15B255sS口靠叩9215015B23flB結(jié)果消息CList_nocust_namesexbirthdatedtydiscount1DMHM1王云里191724)1-30OTOTMOOT成都1.DD2口口口WE林國(guó)平里19B5硼14靠:靠:6靠口成都D.B53DDDDD3鄭洋女1叼3心卬7[]。:靠:血靠口成都1.DD4DDDDD4張雨潔女1933moe匿:靠:6靠口北京1.DD5WM05鄭箸女1971-0^20OTODOOWW北京0.956more李宇中里19794JS4KM:靠:6靠口上海1.DD1moo7顧培銘里19734J723M:靠:6靠口上海1.DD□結(jié)果.J）消息p_no p_namesexbirthdatedate_hineddeptnamep_boss1000001林嶂里1973Ho4417皿靠:6靠口芭口畤吟阻常加:皿靠口稍售部二靠叩72DDDDD2謝志文里197、｝2-14皿靠:6靠口2003-12<I7OT:MiW?培訓(xùn)部0000053DDDD03李浩然里197M&2500:00:00DDO20OM&16ODOTOTWO稍售部DDDD074000004廖小玲女1979N靠:6靠口2DDO54KC0:0D:CD.C0D培訓(xùn)部0000055DDDD05梁玉瓊女197IM&250000:00DDO2001-03-1300:00^00?培訓(xùn)部NULL6DDDMK羅向東里19794*21叫靠:6靠口加M4ms霹:趾CD.靠口稍售部案20077DDDD07肖家慶里19cs書(shū)7-14叫靠:6靠口19954K4K常:趾CD.靠口稍售部NULLBDDDDOB李浩然里19而孫那叫靠:6靠口加直加12常:趾CD.靠口培訓(xùn)部0000059DDDD09趙文龍男1皎心49叫靠:6靠口1996朋12常:趾CD.靠口稍售部DDDD07遇到的問(wèn)題及解決方法插入order_no時(shí)因?yàn)楦袷讲粚?duì)而不能插入，修改其格式，使其滿足題目要求。

實(shí)驗(yàn)三用語(yǔ)句修改表中的數(shù)據(jù)一、實(shí)驗(yàn)?zāi)康?、?nèi)容、原理(1)將salary表中工號(hào)為000006的員工工資增加為1800,獎(jiǎng)金增加為160(2)利用SQL語(yǔ)句將兩年內(nèi)沒(méi)有簽訂單的員工工資下調(diào)25%二、實(shí)驗(yàn)代碼、結(jié)果與問(wèn)題實(shí)驗(yàn)代碼UPDATEsalary20112736SETbase=1800,bonus=160WHEREp_no=’000006’UPDATEsalary20112736SETbonus=bonus*0.75WHERENOTEXISTS(SELECT*FROMorderdetail20112736WHEREsalary20112736.p_no=orderdetail20112736.p_noANDorder_date>=GETDATE()-730)實(shí)驗(yàn)結(jié)果%消息行受影響14消息位行受葡響)遇到的問(wèn)題及解決方法未遇到任何問(wèn)題,實(shí)驗(yàn)較為順利實(shí)驗(yàn)四用SQL語(yǔ)句刪除表中數(shù)據(jù)一、實(shí)驗(yàn)?zāi)康摹?nèi)容、原理刪除person表中工號(hào)為000010的員工數(shù)據(jù)二、實(shí)驗(yàn)代碼、結(jié)果與問(wèn)題實(shí)驗(yàn)代碼DELETEFROMperson20112736WHEREp_no=’000010’實(shí)驗(yàn)結(jié)果DDDD07肖家慶里19634)7-14叫:北:也靠口199BgD6霹:蛇?.靠口稍售部NULLDDDDDS李浩然里19754)1-加叫:北:也靠口2002^12ODDOdOO?培訓(xùn)部000005DDDM?趙文龍里1963心^加皿北:血靠口1993晤12霹:趾皿靠口稍售部案2007000010張武里1963心^村皿北:血靠口1蟠陽(yáng)12霹:趾皿靠口稍售部二靠叩7DDDMK瑞向東里19al621回:皿皿。加2ffiHM}74}9u'D:OT：u3,Cu3稍售部uuuDD7DDDD07肖家慶里19634}7-14D0：uu：ulCuD199MM6uu：M：ulCu3稍售部NULLDDDDOB李浩然里197Sfl1-3O0O:ODO0DO02D024J4-120OWOOOOO培訓(xùn)部OW005DDDWK趙文龍里1969-D4-20D0：uu：ulCuD19964)3-12uu：M：ulCuD稍售部uuuDD76遇到的問(wèn)題及解決方法未遇到任何問(wèn)題，實(shí)驗(yàn)比較順利實(shí)驗(yàn)五條件查詢一、實(shí)驗(yàn)?zāi)康?、?nèi)容、原理(1)查詢person表中所有不重復(fù)的部門(mén)(2)查詢person表中部門(mén)女經(jīng)理的數(shù)據(jù)(3)查詢person表中姓名為林峰、謝志文和羅向東的員工數(shù)據(jù)(4)利用SQL語(yǔ)句將員工號(hào)位000003?000008的員工的月收入按實(shí)發(fā)工資升序排序(5)查詢員工號(hào)位000002的員工基本工資增加2倍，獎(jiǎng)金增加1.5倍后的實(shí)際收入。二、實(shí)驗(yàn)代碼、結(jié)果與問(wèn)題實(shí)驗(yàn)代碼selectdistinctdeptnamefromperson