数据库原理及应用验证性实验

(管理学院适用) 指导教师批阅意见成绩评定：指导教师签字：注：1、报告内的项目或内容设置，可根据实际情况加以调整和补充。2、教师批改学生实验报告时间应在学生提交实验报告时间后10日内。实验11-1:利用SQL语句创建、修改和删除数据库。customer及“订单”表orderdetail。按表11-1~表11-4中的字段说明创建。实验11-3:利用SQL语句创建视图。(2)基于表person和表orderdetail包含培训部门所有员工(不含部门经理)的员工号、姓名。性别、所属部门实验11-4:创建索引。实验11-5:删除索引。实验11-6:利用SQL语句向表person、salary、customer和orderdetail中插入要求：按表11-5~表11-8中的数据插入。实验11-7:用SQL语句修改表中数据。要求：(1)将salary表中工号为000006的员工工资增加为1800,奖金增加为(2)利用SQL语句将两年内没有签订单的员工奖金下调25%。实验11-8:用SQL语句删除表中的数据。要求：删除person表中工号为000010的员工数据。实验11-9:更新视图。实验11-10:向视图插入数据。实验11-11:删除视图。实验11-12:无条件查询。实验11-13:条件查询。(1)查询person表中所有不重复的部门。(2)查询person表中部门女经理的数据。(3)查询person表中姓名··峰、谢志文和罗向东的员工数据。(4)利用SQL语句将员工号为000003~000008的员工的月收入按实发工资(5)查询工号为000002的员工基本工资增加2倍，奖金增加1.5倍后的实实验11-14:一般连续查询。要求：(1)利用SQL语句查询一月份发放奖金平均数大于200元的部门，并(2)查询居住城市在上海的顾客订单总数和订单总额。实验11-15:特殊联结查询(表的外联结和自联结)。(1)查询培训部员工签订订单的情况。(2)查询工作时间比他们的部门经理还长的员工。(3)查询至少有两份订单的顾客信息。实验11-16:嵌套子查询。(1)查询比工号为000005的员工实发工资高的所有员工信息。(2)查询订单额高于平均订单额的订单信息。(3)查询与成都的顾客签订订单的员工代码及姓名。实验11-17:相关子查询。要求：(1)查询至少有两份订单的顾客信息。(2)查询至少有一份订单额大于100000或总订单额大于200000的顾客信息。实验11-18:利用UNION查询。实验11-27:创建规则、删除规则。要求：(1)创建规则Discountrule并与表customer的Discount列绑定，指定列取值为0.50~1.00。(3)将规则Sexrule解除绑定，然后把它删除。实验11-28:定义检查约束、查看表的定义、删除检查约束。(1)创建表时将表orderdetail的Orederno列定义为检查约束，并限制其值前两位为字幕，后4位为数字。(2)创建表person时为其设置表级约束，并限制同一元组中Birthdate列取(3)向已有表salary中增加一个检查约束Bonus_check,限制Bonus列的值不小于50。(4)查看对表salary结构的定义。(5)删除表salary中的约束Bonus_check。实验11-29:主键约束的使用。列及表orderdetail的Orderno列定义为主键。实验11-30:唯一性约束的使用。要求：将person表的P_no列和P_name列联合定义为唯一性约束。实验11-31:定义外键约束。(1)创建表时将表orderdetail的Cust_no列和P_no列定义为外键，并分别参考表customer的列Cust_no和表person的列P_no。(2)将salary表中的Pno设为外键，并使其参照表person中的Pno。实验11-32:测试对主表进行插入、更新及删除操作时的影响。(1)向表person中插入一行数据(2)将表person中的员工号000003改为000016,测试是否影响从表。(3)删除表person中员工号为000001的员数据删除，测试是否影响从表。实验11-33:测试对从表进行插入、更新及删除操作时的影响。(1)向表orderdetail中插入一行数据(2)将表orderdetail中订单号为CX2222的订单所联系的员工号更新为000010,测试是否违背参照完整性。(3)删除表orderdetail中订单号为AS0058的订单数据，测试是否违背参照课程PPT例题海离将成十一再组解数据库原理及设计(第3版)(2)运用MicrosoftSQLserver2014Managementstudio完成实验报告Solution1-MicrosoftSQLServerManagementStudio连接▼一ALLEN(SQLServer12.0.4100.1-ALLEN\MX)数据库Employee表系统表FileTablesdbo.customerdbo.orderdetandbo.persondbo.salary肉系统视图dbo.CustomerViewdbo.TrainingView同义词书士十士+士+窗士实验过程及内容：实验11-1:CreateDatabaseEmployeeonprimaryFILENAME='C:\data\Empdat1.mdf',SIZE=10MB,MAXSIZE=50MB,FILEGROWTH=5MB)FILENAME='C:\data\Emplog.ldf',SIZE=5MB,MAXSIZE=25MB,FILEGROWTH=10%)ALTERDATABASEEmployeeADDFILEFILENAME='C:\data\Empdat2.ndf',SIZE=5MB,MAXSIZE=250MB,FILEGROWTH=2MB)ALTERDATABASEEmployeeMODIFYFILE MAXSIZE=80MB),计算机，Windows8_OS(C:)>data名称★Empdat1,mdfEmpdat2.ndfEmployeelog.ldf在计算机C盘的data文件夹创建了如上文件Person表.createtableEmployee.DBO.person(P_nochar(6)PRIMARYKEYP_namevarchar(10)NOTNULL,char(2)NOTNULLBirthdatedatetimeNULL,Date_hireddatetimeNULL,Deptnamevarchar(10)NOTNULLDEFAULT'培训部’,P_bosschar(6)NULL,CONSTRAINTbirth_hire_checkCHECK(Birthdate<Date_hired))Salary表：CreateTableEmployee.DB0.salary(P_nochar(6)PRIMARYKEY,CONSTRAINTperson_contREFERENCESperson(P_no)BaseDec(5)NULL,BonusDec(5)NULL,FactASBase+Bonus)CreateTableEmployee.DBO.customer(Custnamevarchar(10)DiscountDec(3,2)CONSTRAINTDiscount_checkPRIMARYKEY,NOTNULL,NOTNULL,NULL,NULL,CHECK(Discount>=0.50andDiscount<=1.00))CreateTableEmployee.DB0.orderdetail(Order_no_constraintLIKE’[A-Z][A-Z][0-9][0-9][0-9][0-9]’),Cust_nochar(6)NP_nochar(6)Order_datedatetimeNOTNULL,CONSTRAINTperson_contrFOREIGNKEY(P_no)REFERENCESperson(P_no)ONDELETECASCADEONUPDATECASCADE,CONSTRAINTcustomer_coFOREIGNKEY(Cust_no)REFERENCEScustomer(Cust_no)ONDELETECASCADEONUPDATECASCADE)数据库关系图国dbo.orderdetail在数据库在数据库Employee中创建如上表实验11-3:WHERECity=’北京’ALLEN.Employee…bo.CustomerViewXCustnoCustnameSexDiscount●NULLNULLNULLNULLANDP_bossisnotnullANDorder_date>=GETDATE()-365DeptnameAchievement●NULLNULLNULLNULLNULL困Edbo.CustomerView在数据库在数据库Employee中创建如上视图实验11-4:(1)CREATEINDEXname_sortONperson(P_name)(3)CREATEUNIQUEINDEXu_name_sortONperson(P_name)(4)CREATECLUSTEREDINDEXfact_idxONsalary(Fact,DESC)DROPINDEXsalary.fact_idx▶●INSERTINTOpersonALLEN.Employee-dbo.person×BirthdateDatehiredDeptnamepboss▶000001林峰男1973-04-070.…2003-08-030…销售部000007谢志文男1975-02-140.…2003-12-070...培训部000005李浩然男1970-08-250…2000-05-160…销售部000007廖小玲女1979-08-060…2004-05-06培训部000005梁玉琼女1970-08-250.…2001-03-130….培训部NULL罗向东男1979-05-110….2000-07-09.0…销售部000007肖家庆男销售部NULL000009赵文龙男1969-04-200.…1996-08-12O…销售部000007NULLNULLNULLNULLNULLNULLNULLSQLQuery3.sql-…e(ALLEN\MX(52))*×消息不能在具有唯一索引“u_name_sort”的对象“dbo.person”中插入重复键的行。重复键值为(李浩然)。语句已终止。VALUES'000001','2100','300')ALLEN.Employee-dbo.salaryxALLEN.Employeedbo.perFact▶●ALLEN.Employee-dbo.customer×ALLEN.Employee-dbo:salaryCustnoCustnameSexBirthdateCityDiscount▶林国平男1985-08-140.…女女女女000005刘善女000006李宇中男顾培铭男1973-07-230…上海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')▶AS0005AS0008AS0011AS0012900002011-03-250.BU1132BU1167NULLNULLNULLNULLNULLSETBonus=Bonus*.75order_date>=GETDATE()-730)ALLEN.Employeedbo.salary.xALLEN.Employee-dbo.orderdeDELETEFROMpersonWHEREP_no='00010'SQLQuery1.sql-…e(ALLEN\MX(53))*×DELETEFROMpersonWHEREP_no='00010'消息(0行受影响)无00010工号，因此0行受影响无00010工号，因此0行受影响WHERECustname='王云’FupdateCustomerViewALLEN.Employee.bo.CustomerViewXDiscount张雨洁女▶刘苦女NULLNULLNULLNULL客户王云不在北京因此不在客户王云不在北京因此不在CustomerView视图内，0行受影响实验11-10:因为SQLQuerySQLQuery1.sql-…e(ALLEN\MX(55))*×消息2627,级别14,状态1,第1行违反了PRIMARYKE!约束“PK_customer_7B8FA4908FA7920F”。不能在对象“dbocustomer”中插入重复键。重复键值为(000008)。语句已终止。因为视图CustomerView是由表Customer制成，在表Customer中没有000008的数据，因此无法插入。实验11-11:MicrosoftSQLServerManagementStudioXSQL执行错误。确定帮助帮助已经删除视图CustomerView实验11-12:SELECT*FROMperson消息消息田结果P_nameSexBirthdateDate_hiredDeptnameP_boss1林峰男1973-04-0700:00:00.0002003-08-0300:00:00.000销售部000007李浩然男1970-08-2500:00:00.00020廖小玲女1979-08-0600:00:00.05678罗向东男1979-05-1100:00:00.0002000-07-0900:00:00.000销售部0000071969-04-2000:00:00.000199实验11-13:(1)SELECTDISTINCTDeptnameFROMpersonSQLQuerySQLQuery1,sql-…e(ALLEN\MX(55))*×SELECTDISTINCTDeptnameFROMperson结果消息Deptname(2)SELECT*FROMpersonWHEREP_bossisnullANDSex='女’SQLQuery1.sqlSQLQuery1.sql-…e(ALLEN\MX(55))*×结果消息NULL NULL Deptname培训部(3)SELECT*FROMpersonWHEREP_nameIN(SQLQuery1,sql-…e(ALLEN\MX(55))*X田结果消息P_nameSexBirthdateDate_hiredDeptnameP_boss1000001林峰男1973-04-0700:00:00.0002003-08-0300:00:00.000销售部00000723000002谢志文男1975-02-1400:00:00.0002003-12-0700:00:00.000培训部000005000006罗向东男1979-05-1100:00:00.0002000-07-0900:00:00.000销售部000007(4)SELECT*FROMsalaryWHEREP_noBETWEEN'000003'AND'000008'ORDERBYFactASELECT*FROMsalaryWHEREP_noBETVEEN'000003’AND'000008'ORDERBYFactASC Fact25582688Fact2558268812345 2500结果消息实际收入实际收入实验11-14:fromsalaryAJOINpersonBonA.p_no=B.p_nogroupbyDeptname部门平均奖金1培训部206.333333ALLEN.Employee-dbo.salaryALLEN.Employee-dbo.personSQLQuery1.sql-…e(ALLEN}MX(55))*×订单总数订单总额155020001实验11-15:groupbyperson.P_no结果消息P_no订单总数订单总额1000002220000053265000(2)SELECTp.P_no,p.Date_hired,m.P_no,m.Date_hiredwherep.P_boss-m.P_noandp.Date_hired<m.Date_hired消息结果消息Date_hiredDate_hired0000091996-08-1200:00:00.0000000071998-06-0600:00:00.000员工000009赵文龙工作时间比部门经理000007肖家庆更久员工000009赵文龙工作时间比部门经理000007肖家庆更久fromcustomer,orderdetailol,orderdetailo2wherecustomer.cust_no=ol.cust_noandcustomer.cust_no=o2.cust_noandol.order_no<>o2.order_no用结果消息1000001王云男2000002林国平男3000003郑洋女1.004000006李宇中男5000007顾培铭男(4)selectpl.p_no,pl.p_name,pl.sex,pl.deptnamefrompersonpl,personp2wherepl.p_name=p2.p_nameandpl.p_no<>p2.p_no结果消息因未解决相同员工姓名··然的录入问题，因未解决相同员工姓名··然的录入问题，未能解决此题wherep.P_no=s.P_nowherePno='000005')理结果消息1员工号000003…姓名李浩然实发2000004廖小玲3000007肖家庆(2)SELECTOrderwhereo.Cust_no=c.Cust_noando.P_no=p.P_noand结果消息1AS0011Cust_name顾培铭Order_totalOrder_date赵文龙2AS0012林国平梁玉琼850002010-11-1100:00:00.000刘菁梁玉琼900002011-03-2500:00:00.0004AS0058李宇中谢志文5BU1139林国平梁玉琼900002009-10-1200:00:00.0006BU1167顾培铭李浩然7CX2225林国平李浩然900002008-05-0200:00:00.000wherep.p_no=o.p_noand结果结果消息2000005梁玉琼3000001林峰4000007肖家庆wherecust_nonotin(selectdistinctcust_nofromorderdetailwhere结果消息cust_no000001王云男2000002林国平男3000003郑洋女女4000004张雨洁000005刘菁女6000006李宇中男000007顾培铭男8000008刘美萍女实验11-17:(1)selectdistinctcustomer.Cust_no,Cust_name,Sex,Discountwherecustomer.Cust_no=ol.Cust_noandol.Cust_noin(selectCust_nofromorderdetailo2whereol.Order_no>o2.Order_no)结果Cust_nameDiscount1王云男2林国平男3郑洋女4李宇中男5顾培铭男fromcustomerwhereCust_no=customer.Cust_noandOrder_total>10000whereCust_no=customer.Cust_nohavingsum(Order_total)>200000SQLQuerySQLQuery1.sql-…e(ALLEN\MX(55))*×一selectdistinctcustomer.Cust_noCust_name,SexDiscountwhereCust_no-customer.Cusarexista(seleotsum(OwhereCust_no=customer.Cust_no结果消息女Discount23王云000007郑洋456456刘菁7顾培铭7selectdistinctCust_no顾客号，Cust_name顾客姓名whereCity='北京’unionwhereCity='上海’结果消息顾客号顾客姓名1000004张雨洁2结果消息顾客号顾客姓名1000004张雨洁2000005刘菁3000006李宇中4000007顾培铭实验11-27:(1)createrulediscount_rspbindrule'Discount_rule','cSQLQuery1,sql-…e(ALLEN\MXcreaterulediscount_rEsp_bindrule'Discount_消息已将规则绑定到表的列。(2)createruleSex_ruleas@spbindrule'Sex_rule','personSQLQuery1.sql-…e(ALLEN\MXEreateruleSexrulea曰sp_bindrule'Sex_rule消息已将规则绑定到表的列。(3)sp_unbindrule'person.SexdropruleSex_ruleSQLQuery1.sql-…e(ALLENYMXsp_unbindrule"persondropruleSex_rule消息已解除了表列与规则之间的绑定实验11-28.(1)orderdetail的Orederno约as@discountbetweeustomer.Discount'uleas@discountbetween0.50and1.00rule','customer.Discount'Sexin('男’,’女’),person.Sex'束如下图Orderano_constreint正在编辑现有CHECK约束的属性。(名称)Order_no_constraint说明强制用于INSERT和UPDAT是强制用于复制是在创建或重新启用时检查现有是(2)表person中Birthdate列取值应小于Datehired列取值约束如下图birthhirecheck正在编辑现有CHECK约束的属性。表达式(名称)说明V表设计器birth_hire_check强制用于INSERT和UPDAT是强制用于复制是在创建或重新启用时检查现有是Addconstraintbonus_checkcheck(bonus>=50)选定的CHECK约束(S):banuschee正在编辑现有CHECK约束的属性。表达式(名称)说明([bonus]>=(50))bonus_check强制用于INSERT和UPDAT是强制用于复制是在创建或重新启用时检查现有否SQLQuery1.sql-…e(ALLEN\MX(55))*×SQLQuery1.SQLQuery1.sql-…e(ALLEN\MX(55))*×SQLQuery1.sql-…e(ALLEN\MX(55))*×=EXECsp_helpsalary专结果消息TypeusertableTypeusertable2015-12-0623:16:47.037VComputedLengthPrecScaleNullableTrimTrailingBlanksFixedLenNullInSourceCollation23ho6nonoChinesePRBaseBonusdecimalno54Fact1NoidentitycolunndefinNULLNULLNULLRowGuidcolNorwguidoolcolumndefined.1Data_located_on_filegroupdeynameindex_descriptionindexkeys1PK__salary__A341361CC520938Bclustered,unique,primarykeylocatedonPRIMARYP_noconstrainttypeconstraint_namedelete_actionstatus_enabledstatus_for_replicationconstr1CHECKoncolumnBonusbonus_checkEnabledIs_For_Repli2FOREIGNKEY34FRIMARYKEY(clust..person_contCascadeCascadeEnabledIs_For_ReplicationP_noREFEREDROPCONSTRAINTBonus_checkALTERTABLEsalaryDROPCONSTRAINTBonus_check消息命令已成功完成。已删除已删除Bonuscheck约束表表salary的主键为Pno列，如下：ALLENALLEN.Employee-dbo.salary×列名cheche表表person的主键Pno列，如下：ALLENALLEN.Employee-dbo.personXALLEN.E表表customer的主键Custno列，如下：ALLENALLEN.Employee-dbo.customerX 表表orderdetail的主键Orderno列，如下：ALLEN.Employee-dbo.orderdetailX列名数提F消息已联合定义为唯一性约束已联合定义为唯一性约束(1)创建表时已将表orderdetail的Cust_no列和P_no列定义为外键，并分别参考表customer的列列Cust_no和表person的列P消息宋全礼男1980-07-1702005-03-110…培训部000005Base000004金NULLNULL表person已插入该行数据，从表salary并无受其影响(2)UPDATEpersonSETp_no='000016'WHEREp_no='000003'PnoBaseBon▶ALLEN.Employee-dbo.personXSQLQu▶谢志文廖小玲女ALLEN.Employee-dbo.salaryXALI▶VALUES('CX88',’000009',’000010','2005-7-17',’120000')SQLQuery1.sql-…e(ALLEN\MX(55))*×INSE