验证性实验报告

深圳大学实验报告课程名称：数据库原理与应用试验项目名称：验证DDL，DML及数据完整性学院：管理学院专业：电子商务指导教师：叶斌汇报人：庄义锐学号：040879班级：08电子商务试验时间：.11.4试验汇报提交时间：.12.16教务处制试验目标与要求：熟悉使用SQL定义子语言；熟悉操纵子语言命令语句；熟悉数据完整性约束机制。步骤：一、SQL语句定义子语言试验11-1利用SQL语句创建，删除和修改数据库；试验11-2利用SQL创建”人员“表person，”月薪“表salary，”用户表“customer”及“订单”表orderdetail试验11-3创建视图试验11-4创建索引。试验11-5删除索引二、SQL语句操纵子语言试验11-6用SQL语句将表person、salary、customer和orderdetail中插入数据试验11-7用SQL语句修改表中数据试验11-8用SQL语句删除表中数据试验11-9更新视图试验11-10向视图中插入数据试验11-11删除视图试验11-12无条件查询试验11-13条件查询试验11-14通常连接查询试验11-15特殊联结查询试验11-16嵌套子查询试验11-17相关子查询试验11-18UNION查询三．数据库完成性验证实现步骤以下：（一）表本身完整性：试验11-27创建规则和删除规则试验11-28定义检验约束、查看定义约束、删除检验约束。试验11-29使用主键约束试验11-30应用唯一约束（二）表参考完整性验证步骤：试验11-31定义外键约束试验11-32测试对主表进行插入、更新、及删除操作时影响试验11-33测试对从表进行插入，更新及删除操作时影响。试验过程及内容：一、SQL语句定义子语言试验11-1利用sql语句创建数据库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创建”人员“表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)创建视图CustomerViewcreateviewCustomerViewSELECTCust_no,Cust_name,Sex,DiscountFROMcustomerWHERECity='北京'(2)创建视图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创建索引。（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中插入数据INSERTINTOpersonVALUES('000001','林峰','男','1973-04-07','-08-03','销售部','000007')类似语句试验11-7用SQL语句修改表中数据（1）updatesalarysetBase=1800,Bonus=160whereP_no='000006'（2）updatesalarySETBonus=Bonus*.75wherenotexists(SELECT*FROMorderdetailwheresalary.P_no=orderdetail.P_noandorder_date>=GETDATE()-730)试验11-8用SQL语句删除表中数据deletefrompersonwhereP_no='000010'000010不存在所以不受影响试验11-9更新视图updateCustomerViewSETDiscount=0.85whereCust_name='王云'试验11-10向视图中插入数据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(*)订单总数,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（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实发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)>00))试验11-18UNION查询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）（2）定义表时已经完成(3)向已由表salary中增加一个检验约束Bonus_check，限制Bonus列值大于50。altertablesalarywithnocheck(addconstraintbonus_checkcheck(bonus>=50)(4)查看对表salary结构定义:EXECsp_helpsalary（5）删除表salary中约束Bonus_checkALTERTAB