驗證性實驗報告

深圳大學實驗報告課程名稱：數(shù)據(jù)庫原理與應(yīng)用試驗項目名稱：驗證DDL，DML及數(shù)據(jù)完整性學院：管理學院專業(yè)：電子商務(wù)指導教師：葉斌匯報人：莊義銳學號：040879班級：08電子商務(wù)試驗時間：.11.4試驗匯報提交時間：.12.16教務(wù)處制試驗?zāi)繕伺c要求：熟悉使用SQL定義子語言；熟悉操縱子語言命令語句；熟悉數(shù)據(jù)完整性約束機制。步驟：一、SQL語句定義子語言試驗11-1利用SQL語句創(chuàng)建，刪除和修改數(shù)據(jù)庫；試驗11-2利用SQL創(chuàng)建”人員“表person，”月薪“表salary，”用戶表“customer”及“訂單”表orderdetail試驗11-3創(chuàng)建視圖試驗11-4創(chuàng)建索引。試驗11-5刪除索引二、SQL語句操縱子語言試驗11-6用SQL語句將表person、salary、customer和orderdetail中插入數(shù)據(jù)試驗11-7用SQL語句修改表中數(shù)據(jù)試驗11-8用SQL語句刪除表中數(shù)據(jù)試驗11-9更新視圖試驗11-10向視圖中插入數(shù)據(jù)試驗11-11刪除視圖試驗11-12無條件查詢試驗11-13條件查詢試驗11-14通常連接查詢試驗11-15特殊聯(lián)結(jié)查詢試驗11-16嵌套子查詢試驗11-17相關(guān)子查詢試驗11-18UNION查詢?nèi)當?shù)據(jù)庫完成性驗證實現(xiàn)步驟以下：（一）表本身完整性：試驗11-27創(chuàng)建規(guī)則和刪除規(guī)則試驗11-28定義檢驗約束、查看定義約束、刪除檢驗約束。試驗11-29使用主鍵約束試驗11-30應(yīng)用唯一約束（二）表參考完整性驗證步驟：試驗11-31定義外鍵約束試驗11-32測試對主表進行插入、更新、及刪除操作時影響試驗11-33測試對從表進行插入，更新及刪除操作時影響。試驗過程及內(nèi)容：一、SQL語句定義子語言試驗11-1利用sql語句創(chuàng)建數(shù)據(jù)庫Employee,并進行修改和刪除按照要求操作SQL代碼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)試驗11-2利用SQL創(chuàng)建”人員“表person，”月薪“表salary，”用戶表“customer”及“訂單”表orderdetailcreatetableEmployee.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))createtableEmployee.DBO.salary(P_nochar(6)PRIMARYKEY,CONSTRAINTperson_contFOREIGNKEY(P_no)REFERENCESperson(P_no)ONDELETECASCADEONUPDATECASCADE,BaseDec(5)NULL,BonusDec(5)NULL,FactASBase+Bonus)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))createtableEmployee.DBO.orderdetail(Order_nochar(6)primarykeyconstraintOrder_no_constraintcheck(Order_noLIKE'[A-Z][A-Z][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)試驗11-3(1)創(chuàng)建視圖CustomerViewcreateviewCustomerViewSELECTCust_no,Cust_name,Sex,DiscountFROMcustomerWHERECity='北京'(2)創(chuàng)建視圖TrainingViewcreateviewTrainingViewSELECTperson.P_no,P_name,Sex,Deptname,SUM(order_total)ASAchievementFROMperson,orderdetailWHEREperson.P_no=orderdetail.P_noANDdeptname='培訓部'ANDP_bpssisnotnullANDorder_date>=GETDATE()-365GROUPBYperson.P_no,P_name,Sex,Deptname試驗11-4創(chuàng)建索引。（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二、SQL語句操縱子語言試驗11-6用SQL語句將表person、salary、customer和orderdetail中插入數(shù)據(jù)INSERTINTOpersonVALUES('000001','林峰','男','1973-04-07','-08-03','銷售部','000007')類似語句試驗11-7用SQL語句修改表中數(shù)據(jù)（1）updatesalarysetBase=1800,Bonus=160whereP_no='000006'（2）updatesalarySETBonus=Bonus*.75wherenotexists(SELECT*FROMorderdetailwheresalary.P_no=orderdetail.P_noandorder_date>=GETDATE()-730)試驗11-8用SQL語句刪除表中數(shù)據(jù)deletefrompersonwhereP_no='000010'000010不存在所以不受影響試驗11-9更新視圖updateCustomerViewSETDiscount=0.85whereCust_name='王云'試驗11-10向視圖中插入數(shù)據(jù)insertCustomerView(Cust_no,Cust_name,Sex)values('000008','劉美萍','女')試驗11-11刪除視圖select*fromCustomerViewgo試驗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(*)訂單總數(shù),sum(Order_total)訂單總額FROMorderdetail,customerwhereorderdetail.Cust_no=customer.Cust_noandCity='上海'試驗11-15特殊聯(lián)結(jié)查詢（1）SELECTperson.P_no,count(*)訂單總數(shù),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（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相關(guān)子查詢（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)>00))試驗11-18UNION查詢selectdistinctCust_no用戶號,Cust_name用戶姓名fromcustomerwhereCity='北京'unionselectdistinctCust_no用戶號,Cust_name用戶姓名fromcustomerwhereCity='上海'三、數(shù)據(jù)庫完成性驗證（一）表本身完整性：試驗11-27創(chuàng)建規(guī)則和刪除規(guī)則(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）（2）定義表時已經(jīng)完成(3)向已由表salary中增加一個檢驗約束Bonus_check，限制Bonus列值大于50。altertablesalarywithnocheck(addconstraintbonus_checkcheck(bonus>=50)(4)查看對表salary結(jié)構(gòu)定義:EXECsp_helpsalary（5）刪除表salary中約束Bonus_checkALTERTAB