數據庫原理及應用驗證性實驗

深圳大學實驗報告（管理學院適用）課程名稱：數據庫原理及應用實驗項目名稱：數據庫驗證性操作實驗指導教師：葉斌專業(yè)：電子商務報告人：學號：班級：實驗時間：實驗報告提交時間：指導教師批閱意見：成績評定：指導教師簽字：年月日注：1、報告內的項目或內容設置，可根據實際情況加以調整和補充。2、教師批改學生實驗報告時間應在學生提交實驗報告時間后10日內。實驗目的與要求：實驗11-1：利用SQL語句創(chuàng)建、修改和刪除數據庫。實驗11-2：利用SQL創(chuàng)建“人員”表person、“月薪”表salary、“顧客”表customer及“訂單”表orderdetail。按表11-1~表11-4中的字段說明創(chuàng)建。實驗11-3：利用SQL語句創(chuàng)建視圖。要求：（1）在customer上創(chuàng)建“顧客”視圖CustomerView，其中包含居住在北京的顧客的基本信息，并顯示“顧客號”、“姓名”、“性別”、和“購買折扣”等字段。（2）基于表person和表orderdetail創(chuàng)建“培訓員工”視圖TrainingView，其中包含培訓部門所有員工（不含部門經理）的員工號、姓名、性別、所屬部門和最近一年內的總銷售業(yè)績。實驗11-4：創(chuàng)建索引。要求：（1）在“人員”表的“姓名”列上創(chuàng)建一個單列索引name_sort。（2）在“人員”表的“出生日期”列和姓名列上創(chuàng)建一個組合索引birth_name。（3）在“人員”表的“姓名”列上創(chuàng)建一個唯一索引u_name_sort。（4）在“月薪”表的“實發(fā)”列上創(chuàng)建一個聚簇索引fact_idx，并使系統(tǒng)按降序索引。實驗11-5：刪除索引。要求：刪除“月薪”表上的索引fact_idx。實驗11-6：利用SQL語句向表person、salary、customer和orderdetail中插入數據。要求：按表11-5~表11-8中的數據插入。實驗11-7：用SQL語句修改表中數據。要求：（1）將salary表中工號為000006的員工工資增加為1800，獎金增加為160。（2）利用SQL語句將兩年內沒有簽訂單的員工獎金下調25%。實驗11-8：用SQL語句刪除表中的數據。要求：刪除person表中工號為000010的員工數據。實驗11-9：更新視圖。要求：將“顧客”視圖CustomerView中姓名為“王云”的顧客的購買折扣改為0.85。實驗11-10：向視圖插入數據。要求向視圖CustomerView中插入一行數據：(‘000008’,’劉美萍’,’女’,NULL,1.00)實驗11-11：刪除視圖。要求：將視圖CustomerView刪除。實驗11-12：無條件查詢。要求：查詢person表中的所有數據。實驗11-13：條件查詢。要求：（1）查詢person表中所有不重復的部門。（2）查詢person表中部門女經理的數據。（3）查詢person表中姓名為林峰、謝志文和羅向東的員工數據。（4）利用SQL語句將員工號為000003~000008的員工的月收入按實發(fā)工資升序排序。（5）查詢工號為000002的員工基本工資增加2倍，獎金增加1.5倍后的實際收入。實驗11-14：一般連續(xù)查詢。要求：（1）利用SQL語句查詢一月份發(fā)放獎金平均數大于200元的部門，并從高到低排序。（2）查詢居住城市在上海的顧客訂單總數和訂單總額。

實驗11-15：特殊聯結查詢（表的外聯結和自聯結）。要求：（1）查詢培訓部員工簽訂訂單的情況。（2）查詢工作時間比他們的部門經理還長的員工。（3）查詢至少有兩份訂單的顧客信息。（4）查詢姓名相同的員工信息。

實驗11-16：嵌套子查詢。要求：（1）查詢比工號為000005的員工實發(fā)工資高的所有員工信息。（2）查詢訂單額高于平均訂單額的訂單信息。（3）查詢與成都的顧客簽訂訂單的員工代碼及姓名。（4）查詢一年內沒有續(xù)訂單的顧客信息。

實驗11-17：相關子查詢。要求：（1）查詢至少有兩份訂單的顧客信息。（2）查詢至少有一份訂單額大于100000或總訂單額大于200000的顧客信息。實驗11-18：利用UNION查詢。要求：利用SQL語句分別查詢居住城市在上海、北京的顧客信息，合并輸出。實驗11-27：創(chuàng)建規(guī)則、刪除規(guī)則。要求：（1）創(chuàng)建規(guī)則Discount_rule并與表customer的Discount列綁定，指定列取值為0.50~1.00。（2）創(chuàng)建規(guī)則Sex_rule并與表person的Sex列綁定，指定性別列的取值為男或女。（3）將規(guī)則Sex_rule解除綁定，然后把它刪除。實驗11-28：定義檢查約束、查看表的定義、刪除檢查約束。要求：（1）創(chuàng)建表時將表orderdetail的Oreder_no列定義為檢查約束，并限制其值前兩位為字幕，后4位為數字。（2）創(chuàng)建表person時為其設置表級約束，并限制同一元組中Birthdate列取值應小于Date_hired列取值。（3）向已有表salary中增加一個檢查約束Bonus_check，限制Bonus列的值不小于50。（4）查看對表salary結構的定義。（5）刪除表salary中的約束Bonus_check。實驗11-29：主鍵約束的使用。要求：將表salary的P_no列、表person的P_no列、表customer的Cust_no列及表orderdetail的Order_no列定義為主鍵。實驗11-30：唯一性約束的使用。要求：將person表的P_no列和P_name列聯合定義為唯一性約束。實驗11-31：定義外鍵約束。要求：（1）創(chuàng)建表時將表orderdetail的Cust_no列和P_no列定義為外鍵，并分別參考表customer的列Cust_no和表person的列P_no。（2）將salary表中的P_no設為外鍵，并使其參照表person中的P_no。實驗11-32：測試對主表進行插入、更新及刪除操作時的影響。要求：向表person中插入一行數據(‘000012’,’宋全禮’,’男’,’1980-7-17’,’2005-3-11’,’培訓部’,’000005’)，測試是否影響從表。（2）將表person中的員工號000003改為000016，測試是否影響從表。（3）刪除表person中員工號為000001的員數據刪除，測試是否影響從表。實驗11-33：測試對從表進行插入、更新及刪除操作時的影響。要求：（1）向表orderdetail中插入一行數據(‘CX88’,’000009’,’000010’,’2005-7-17’,’120000’)，測試是否違背參照完整性。（2）將表orderdetail中訂單號為CX2222的訂單所聯系的員工號更新為000010，測試是否違背參照完整性。（3）刪除表orderdetail中訂單號為AS0058的訂單數據，測試是否違背參照完整性。課程PPT例題實驗方法及步驟：（1）通過課本知識，了解實驗的目的與要求運用MicrosoftSQLserver2014Managementstudio完成實驗報告實驗過程及內容：實驗11-1：CreateDatabaseEmployeeonprimary(NAME=Empdat1,FILENAME='C:\data\Empdat1.mdf',SIZE=10MB,MAXSIZE=50MB,FILEGROWTH=5MB)LOGON(NAME=Emplog,FILENAME='C:\data\Emplog.ldf',SIZE=5MB,MAXSIZE=25MB,FILEGROWTH=10%)ALTERDATABASEEmployeeADDFILE(NAME=Empdat2,FILENAME='C:\data\Empdat2.ndf',SIZE=5MB,MAXSIZE=250MB,FILEGROWTH=2MB)ALTERDATABASEEmployeeMODIFYFILE(NAME=Empdat1,MAXSIZE=80MB)在計算機C盤的data文件夾創(chuàng)建了如上文件實驗11-2：Person表：createtableEmployee.DBO.person(P_nochar(6)PRIMARYKEY,P_namevarchar(10)NOTNULL,Sexchar(2)NOTNULL,BirthdatedatetimeNULL,Date_hireddatetimeNULL,Deptnamevarchar(10)NOTNULLDEFAULT'培訓部',P_bosschar(6)NULL,CONSTRAINTbirth_hire_checkCHECK(Birthdate<Date_hired))Salary表：CreateTableEmployee.DBO.salary(P_nochar(6)PRIMARYKEY,CONSTRAINTperson_contFOREIGNKEY(P_no)REFERENCESperson(P_no)ONDELETECASCADEONUPDATECASCADE,BaseDec(5)NULL,BonusDec(5)NULL,FactASBase+Bonus)Customer表：CreateTableEmployee.DBO.customer(Cust_nochar(6)PRIMARYKEY,Cust_namevarchar(10)NOTNULL,Sexchar(2)NOTNULL,BirthdatedatetimeNULL,Cityvarchar(10)NULL,DiscountDec(3,2)NOTNULLDEFAULT'1.00',CONSTRAINTDiscount_checkCHECK(Discount>=0.50andDiscount<=1.00))Orderdetail表：CreateTableEmployee.DBO.orderdetail(Order_nochar(6)primarykeyconstraintOrder_no_constraintcheck(Order_noLIKE'[A-Z][A-Z][0-9][0-9][0-9][0-9]'),Cust_nochar(6)NOTNULL,P_nochar(6)NOTNULL,Order_totalintNOTNULL,Order_datedatetimeNOTNULL,CONSTRAINTperson_contrFOREIGNKEY(P_no)REFERENCESperson(P_no)ONDELETECASCADEONUPDATECASCADE,CONSTRAINTcustomer_contrFOREIGNKEY(Cust_no)REFERENCEScustomer(Cust_no)ONDELETECASCADEONUPDATECASCADE)在數據庫Employee中創(chuàng)建如上表實驗11-3：（1）CreateViewCustomerViewASSELECTCust_no,Cust_name,Sex,DiscountFROMcustomerWHERECity='北京'（2）CREATEVIEWTrainingViewASSELECTperson.P_no,P_name,Sex,Deptname,SUM(order_total)ASAchievementFROMperson,orderdetailWHEREperson.P_no=orderdetail.P_noANDdeptname='培訓部'ANDP_bossisnotnullANDorder_date>=GETDATE()-365GROUPBYperson.P_no,P_name,Sex,Deptname在數據庫Employee中創(chuàng)建如上視圖實驗11-4：（1）CREATEINDEXname_sortONperson(P_name)（2）CREATEINDEXbirth_nameONperson(Birthdate,P_name)（3）CREATEUNIQUEINDEXu_name_sortONperson(P_name)（4）CREATECLUSTEREDINDEXfact_idxONsalary(Fact,DESC)實驗11-5：DROPINDEXsalary.fact_idx實驗11-6：表person：INSERTINTOperson VALUES('000001','林峰','男','1973-04-07','2003-08-03','銷售部','000007') ('000002','謝志文','男','1975-02-14','2003-12-07','培訓部','000005') ('000003','李浩然','男','1970-08-25','2000-05-16','銷售部','000007') ('000004','廖小玲','女','1979-08-06','2004-05-06','培訓部','000005') ('000005','梁玉瓊','女','1970-08-25','2001-03-13','培訓部',NULL) ('000006','羅向東','男','1979-05-11','2000-07-09','銷售部','000007') ('000007','肖家慶','男','1963-07-14','1998-06-06','銷售部',NULL) ('000008','李浩然','男','1975-01-30','2002-04-12','培訓部','000005') ('000009','趙文龍','男','1969-04-20','1996-08-12','銷售部','000007')000008號員工與000003號員工重名均為李浩然，無法插入。表salary：INSERTINTOsalary VALUES('000001','2100','300') ('000002','1800','300') ('000003','2800','280') ('000004','2500','250') ('000005','2300','275') ('000006','1750','130') ('000007','2400','210') ('000008','1800','235') ('000009','2150','210')表customer：INSERTINTOcustomer VALUES('000001','王云','男','1972-01-30','成都','1.00') ('000002','林國平','男','1985-08-14','成都','0.85') ('000003','鄭洋','女','1973-04-07','成都','1.00') ('000004','張雨潔','女','1983-09-06','北京','1.00')('000005','劉菁','女','1971-08-20','北京','0.95')('000006','李宇中','男','1979-08-06','上海','1.00')('000007','顧培銘','男','1973-07-23','上海','1.00') 表orderdetail：INSERTINTOorderdetail VALUES('AS0058','000006','000002','150000','2011-04-05')('AS0043','000005','000005','90000','2011-03-25')('AS0030','000003','000001','70000','2011-02-14')('AS0012','000002','000005','85000','2010-11-11')('AS0011','000007','000009','130000','2010-08-13')('AS0008','000001','000007','43000','2010-06-06')('AS0005','000001','000007','72000','2010-05-12')('BU1167','000007','000003','110000','2010-03-08')('BU1143','000004','000008','70000','2009-12-25')('BU1139','000002','000005','90000','2009-10-12')('BU1132','000006','000002','32000','2009-08-08')('BU1121','000004','000006','66000','2009-04-01')('CX2244','000007','000009','80000','2008-12-12')('CX2232','000003','000001','35000','2008-09-18')('CX2225','000002','000003','90000','2008-05-02')('CX2222','000001','000007','66000','2007-12-04')實驗11-7：（1）UPDATEsalarySETBase=1800,Bonus=160WHEREP_no='000006'（2）updatesalarySETBonus=Bonus*.75wherenotexists(SELECT*FROMorderdetailwheresalary.P_no=orderdetail.P_noandorder_date>=GETDATE()-730)實驗11-8：DELETEFROMpersonWHEREP_no='00010'無00010工號，因此0行受影響實驗11-9：UPDATECustomerViewSETDiscount=0.85WHERECust_name='王云'客戶王云不在北京因此不在CustomerView視圖內，0行受影響實驗11-10：INSERTCustomerView(Cust_no,Cust_name,Sex)VALUES('000008','劉美萍','女')因為因為視圖CustomerView是由表Customer制成，在表Customer中沒有000008的數據，因此無法插入。實驗11-11：DROPVIEWCustomerView已經刪除視圖CustomerView實驗11-12：SELECT*FROMperson實驗11-13：（1）SELECTDISTINCTDeptnameFROMperson（2）SELECT*FROMpersonWHEREP_bossisnullANDSex='女'（3）SELECT*FROMpersonWHEREP_nameIN('林峰','謝志文','羅向東')（4）SELECT*FROMsalaryWHEREP_noBETWEEN'000003'AND'000008'ORDERBYFactASC（5）SELECTP_no工號,2*base+1.5*bonus實際收入FROMsalaryWHEREP_no='000002'實驗11-14：（1）selectDeptname部門,AVG(Bonus)平均獎金fromsalaryAJOINpersonBonA.p_no=B.p_nogroupbyDeptnamehavingavg(Bonus)>200orderbyavg(Bonus)DESC（2）SELECTcount(*)訂單總數,sum(Order_total)訂單總額FROMorderdetail,customerwhereorderdetail.Cust_no=customer.Cust_noandCity='上海'實驗11-15：（1）SELECTperson.P_no,count(*)訂單總數,sum(Order_total)訂單總額FROMorderdetail,personwhereorderdetail.P_no=person.P_noandDeptname='培訓部'groupbyperson.P_no（2）SELECTp.P_no,p.Date_hired,m.P_no,m.Date_hiredFROMpersonp,personmwherep.P_boss=m.P_noandp.Date_hired<m.Date_hired員工000009趙文龍工作時間比部門經理000007肖家慶更久（3）selectdistinctcustomer.cust_no,cust_name,sex,discountfromcustomer,orderdetailo1,orderdetailo2wherecustomer.cust_no=o1.cust_noandcustomer.cust_no=o2.cust_noando1.order_no<>o2.order_no（4）selectp1.p_no,p1.p_name,p1.sex,p1.deptnamefrompersonp1,personp2wherep1.p_name=p2.p_nameandp1.p_no<>p2.p_no因未解決相同員工姓名李浩然的錄入問題，未能解決此題實驗11-16：（1）SELECTp.P_no員工號,p.P_name姓名,Fact實發(fā)FROMpersonp,salaryswherep.P_no=s.P_noands.Fact>(selectFactfromsalarywhereP_no='000005')（2）SELECTOrder_no,Cust_name,P_name,Order_total,Order_dateFROMorderdetailo,personp,customercwhereo.Cust_no=c.Cust_noando.P_no=p.P_noandOrder_total>(selectavg(Order_total)fromorderdetail)（3）selectdistinctp.p_no,p_namefromorderdetailo,personpwherep.p_no=o.p_noand cust_noin(selectcust_nofromcustomerwherecity='成都')（4）selectcust_no,cust_name,sex,discountfromcustomerwherecust_nonotin(selectdistinctcust_nofromorderdetailwhereorder_date>=getdate()-365)實驗11-17：（1）selectdistinctcustomer.Cust_no,Cust_name,Sex,DiscountFROMcustomer,orderdetailo1wherecustomer.Cust_no=o1.Cust_noando1.Cust_noin(selectCust_nofromorderdetailo2whereo1.Order_no<>o2.Order_no)（2）selectdistinctcustomer.Cust_no,Cust_name,Sex,Discountfromcustomerwhereexists(select*fromorderdetailwhereCust_no=customer.Cust_noandOrder_total>10000orexists(selectsum(Order_total)fromorderdetailwhereCust_no=customer.Cust_nohavingsum(Order_total)>200000))實驗11-18：selectdistinctCust_no顧客號,Cust_name顧客姓名fromcustomerwhereCity='北京'unionselectdistinctCust_no顧客號,Cust_name顧客姓名fromcustomerwhereCity='上海'實驗11-27：（1）createrulediscount_ruleas@discountbetween0.50and1.00gosp_bindrule'Discount_rule','customer.Discount'（2）createruleSex_ruleas@Sexin('男','女')gosp_bindrule'Sex_rule','person.Sex'（3）sp_unbindrule'person.Sex'dropruleSex_rule實驗11-28：（1）orderdetail的Oreder_no約束如下圖（2）表person中Birthdate列取值應小于Date_hired列取值約束如下圖（3）altertablesalarywithnocheckAddconstraintbonus_checkcheck(bonus>=50)（4）EXECsp_helpsalary（5）ALTERTABLEsalaryDROPCONSTRAINTBonus_check已刪除Bonus_check約束實驗11-29：表salary的主鍵為P_no列，如下：表person的主鍵P_no列，如下：表customer的主鍵Cust_no列，如下：表orderdetail的主鍵Order_no列，如下：實驗11-30：altertablepersonaddconstraintunique_pno_pnaunique(P_no,P_name)已聯合定義為唯一性約束實驗11-31：（1）創(chuàng)建表時已將表orderdetail的Cust_no列和P_no列定義為外鍵，并分別參考表customer的列Cust_no和表person的列P_no。（2）altertablesalaryaddconstraintdeptno_FKforeignkey(P_no)referencesperson(P_no)實驗11-32：（1）insertintopersonVALUES('000012','宋全禮','男','1980-7-17','2005-3-11','培訓部','000005')表person已插入該行數據，從表salary并無受其影響（2）UPDATEpersonSETp_no='000016'WHEREp_no='000003'表person已修改員工工號，從表salary也受影響修改員工工號。（3）DELETEpersonWHEREp_no='000001'表person中已刪除員工號，從表salary也受影響刪除員工號實驗11-33:（1）INSERTINTOorderdetailVALUES('CX88','000009','000010','2005-7-17','120000')插入失敗，違背表orderdetail的參照完整性（2）UPDATEpersonsetP_no='000010'WHEREexists(select*fromorderdetailowhereOrder_no='cx2222'andperson.P_no=o.P_no)員工號已更新，不違背表orderdetail參照完整性（3）DELETEFROMorderdetailWHEREOr