MySQL数据库技术（第3版） 周德伟 习题答案.docx

单元1数据模型的规划与设计1略2 .略3 .（DB（2） A方案一张表：6105000=30.OOOO个数据B方窠三张表：学生表：35O=15OOO课程表：2-10=20成绩表：3'10"5000=lSOOoO三张表合计：1500020+150000=16,5020A与B方案数据差:30,0000/6.5020=13,4980（3）B史节省空机因为B方案满足第二范式,而A方案不满足.ER图关系模型：商店（盅殳，店名,店址，店经理）职工（工殳.姓名.性别.工资,店号.入职时间）商从（商品号,M名，单价，数量，产地）销伟（,一品号,月销传数设），关系模蟹：服务项目项目ID,项目名称,股务内容,眼务天数，报名条件）已开展服务明细（启动ID,参与人数，服务状态，项目ID.开始时间,结束时间，服务地点）志愿者（志愿者旧，姓名，电话.特长）报名（启动ID.志愿者ID.服务时段,审核状态单元2数据库管理环境的建立思考与练习一.1.root2.QUIT3.my.ini二略单元3数据库和表的创建与管理.1.C2.B3.A4,D二.1.CREATE,A1.TER,DROP2 .网络模型，关系模型3 .数据库管理系统，操作系统4 .数据库，数据库管理系统属性名称数据类型为空性备注飞船名称char(20)NOTNU1.1.主键发射时间dateNOTNU1.1.载人数IntNU1.1.驻罂时间Float(5,1)NU1.1.四.1. createdatabaseXSCJ;2. useXSO；CREATETAB1.EXS(学号char(6)NOTNU1.1.PRIMARYKEY,姓名char(8)NOTNU1.1.,专业名varchar(20)NU1.1.,性别tinyint(l)NOTNU1.1.,出生时间dateNOTNU1.1.,总学分tnlnt(l)NU1.1.,照片blobNU1.1.,备注textNU1.1.);3.USEXSO；CREATETAB1.EKC(课程号char(3)NOTNU1.1.PRIMARYKEYr课程名Char(16)NOTNU1.1.i开徵学期tinyint(l)NOTNU1.1.,学时tlnylnt(l)NOTNU1.1.,学分tinyint(l)NU1.1.);4. USExs;CREATETAB1.EXS_KC(学号car(6)NOTNU1.1.r课程号char(3)NOTNU1.成绩tinyintNU1.1.,学分tinynt(l)NU1.1.PRIMARYKEY(学号,课程号)；5. USExsa；AltertablexsADD奖学金等锻tinyintNU1.lfDROPCO1.UMN姓名;6. USExsaA1.TERTAB1.EXSRENAMETOstudent;7. CREATETAB1.Ekc.copyl1.IKEKC;8. CREATETAB1.EC1.CoPy2AS(SE1.ECTFROMXS-KC);9. DROPTAB1.EIFEXISTSkc.copl;io.usexsaSHOWTAB1.ES；11. DESCRIBEXS;12. DESCXS学号；单元4数据操纵一、掾作意CREATETAB1.E高铁运行表(名称char(29)NOTNU1.1.PRIMARYKEY；省内里程IntNOTNU1.1.,设计时速intNOTNU1.1.,开通时闾dateNU1.1.); INSERTINTOIWyS行表VA1.UES('哈大高快'，81,350,'2012-12-01');INSERTINTO育快运行表VAIUES('哈齐高快,266,300,2015-0817');INSERTINTO育铁运行表VA1.UESCttffttK,139,200,2015-12-28');INSERTINTO高铁运行表VA1.UES（'哈佳蜘咯,343,200,2018-09-30,）;INSERTINTO高铁运行赛VA1.UESC哈杜客专）300,250,2018-12-25,）；INSERTINTO高快运行表VAlUES。位隹客专:372,250,2021-12-06,）;1. InsertintoxsVAIUESrO81101,J王坤，计算机，,1J199O-O2-1（T,50,NU1.1.,NU1.1.）;2. InsertintoxsSET学号=P81101',姓名='程明'，专业=DEFAU1.T,性别=IH生日期='19910201',总学分=50;3. REP1.ACEINTOXSVA1.UES（'081101','王小林'，'通信工程1,1991-0308,48,NUl1.rNU1.1.）;4. UPDATEXSSET总学分=总学分+10;UPDATEXSSET学号='081251,备注=*专业学习WHERE姓名=,李方方*5. DE1.ETEFROMXSWHERE总学分50;单元5数据查询一、1. SE1.ECT姓名,专业名,总学分FROMXS;2. SE1.ECT学号ASnumber,姓名ASname,总学分ASmarkFROMXSWHERE专业名'计算机'；3. SE1.ECT学号，姓名，CASEWHEN总学分ISNU1.1.THEN'尚未选课'WHEN总学分V50THEN，不及格，WHEN总学分=5Oand总学分=52THEN，合格，E1.SE'优秀'ENDAS等级FROMXSWHERE专业名T计算机,；4. SE1.ECTDISTINCT专业名,总学分FROMXS;5. SE1.EeTCoUNT（总学分）AS，总学分SO分以上的人数FROMXSWHERE总学分50；6. SE1.ECTMAX（成钟J）,MIN（成铝UFROMXS_KCWHERE课程号=-101'7. SE1.ECTSUM（成绩）AS'i果程总成例FROMXSKCWHERE学号,081101,;86E1.ECrAVG（成沏AS课程101平均成绩'FROMXS_KCWHERE课程号='101'9. SE1.ECT,FROMXSWHERE需注ISNU1.1.;10. SE1.ECTFROMXS_KCWHERE（课程号='102'OR课程号='206'）AND成绩=80或：SE1.ECT*FROMXS_KCWHERE（课程号='102'and成绩=80）or（课程号='206'AND成绩=80）11. SE1.ECT学号,姓名,性别FROMXSWHERE姓名1.IKE12. SE1.ECT学号,姓名,专业名FROMXSWHERE学号1.IKE13. SE1.ECT*FROMXSWHERE出生时间NOTBETWEEN'1989-01-01'and'1989-12-31"、1. SE1.ECT姓名,成绩FROMXSJOINXS_KCONXS.学号»XSJ（U学号WHERE课程号='2O6'AND成绩=80;2. SE1.ECTxS.学号，姓名，课程名，成绩FROMXSJOINXS_KCONXS.学号=XS_KC.学号JOINKCONXS_KC.课程号KC.课程号WHERE课程名='计算机基础'AND成绩=80;3. SE1.ECTa.学号a课程号,a成缄FROMXS_KCASaJOINXS_KCASbONa.成绩由成绩ANDa.学号b.学号ANDa.课程号!=b.深程号；4. SE1.ECTDISTINCT课程名FROMKCINNERJOINXS_KCUslNG（课程号）或：SE1.ECTDISTINCT课程名FROMKCINNERJOINXS_KCONKC.课程号EXSJ（C.课程号5. SE1.ECT×S.课程号FROMXS1.EFTOUTERJOINXS_KCONXS.学号=XS_KC.学号；6. SE1.ECTXS_KC.,课程名FROMXS_KCRIGHTJOINKCONXSJ（U课程号=KC.深程号；7. SE1.ECT姓名,学号FROMXSWHERE学号IN（SE1.ECT学号FRoMXS_KCWHERE课程号='206'）;或：SE1.ECT姓名,xs.学号FRoMXSJOINXSJ（CONXS.学号=XSJCC.学号WHERE课程号='206'8. SE1.ECT姓名,学号,专业名FROMXSWHERE学号NOTIN（SE1.ECT学号FROMXS_KCWHERE课程号IN（SE1.ECT课程号FROMKCWHERE课理名='离散敢学'）；9. SE1.ECT学号,姓名,专业名,出生日期FRoMXSWHERE出生11期<A1.1.（SE1.ECT出生日期FROMXSWHERE专业名='计算机'）；10. SE1.ECT学号FROMXS_KCWHERE课程号='2O6'AND成缢>=ANY（SElECT成绩FROMX$_KCWHERE课程号='101'）;1. SE1.ECT专业名FROMXSGROUPBY专业能2. SE1.ECT专业名,COUNTC）AS”iIFROMXSGRoUPBY专业名；3. SE1.ECT专业名，性别,COUNTS）AS'人数'FROMXSGROUPBYV业名,性别WITHRO1.1.UP;4. SE1.ECT学号,AVG（成绩）AS平均成绩'FROMXS_KCGROUPBY学号HAVINGAVG（成绷>=85;5. SE1.ECT学号FROMXS_KCWHERE成绩>»80GROUPBY学号HAVINGC0UNT（4）>2;6. SE1.ECT学号,姓名,专业名,出生日期FROMXSWHERE专业名='通信工程ORDERBY出生日期;7. SE1.ECTaFRoMXSoRDERBY学号1.IMIT5；单元6数据视图略1. CREATEORREP1.ACEVIEWXSCJ.CS-KCASSE1.ECTXS.学号,课程号,成绩FROMXSCJ.×S,XSCJ.XS_KCWHEREXS.学号=XSj（C学号ANDXS.专业名='计算机'Withcheckoption;2. CREATEVIEWCSJa1.AVG（num,score_avg）ASSE1.ECT学号,AVG（成彻FROMC$_KCGRoUPBY学号；3. SE1.ECT学号，课程号FROMCS_KC4. 创建学生平均成绩视图XS_KC_AVG：CREATEVIEWXS_KC_AVG（numzscore_avg）ASSElECT学号，AVG（成绩）FROMXS_KCGROUPBY学号；再对XS_KC_AVG视图进行查HUSE1.ECTFROMXS_KC_AVGWHEREscore.avg>=80;5. 首先创建视图CS-X$：CREATEORREP1.ACEVIEWCS_XSASSE1.ECTFROMXSWHERE专业名='计。机，WITHCHECKOPTION;接卜来插入记录：Insertintocs_xsVA1.UESCo81255李牧,计算机,1,'1990-10-14',50,NU1.1.,NU1.1.);6. UPDATECS_X$SET总学分=总学分+8;7. UPDATECS_KCSET成绩=90WHERE学号0811OrAND课程号8. DE1.ETEFROMCS_XSWHERE性别=0;9. A1.TERVIEWCS_XSASSE1.ECT学号,姓名,总学分FROMXSWHERE专业名计售机，;单元7索引与数据完整性约束的创建略二.略1. CREATEINDEXXH_X$ONXS(学号A$C);2. CREATEINDEXXSKC-INONXSJ(C(学号,课程号)；3. A1.TERTAB1.EXSADDINDEXXS_XM(姓名)；4. A1.TERTAB1.EXSADDPRlMARYKEY(学号)；5. CREATETAB1.EXS_KC(学CHAR(6)NOTNU1.1.,课程CHARNOTNU1.1.成绩TINYINT(1),学分TINYINT(1),PRlMARYKEY(学号,课程号),INDEXeJ(成绩)；6. A1.TERTAB1.EXSDROPPRIMARYKEY;7. A1.TERTAB1.EXSlAODUNQUE(I);8. CREATETAB1.EXSl学号VarChar(6)NoTNUi1,姓名VarCharNoTNU1.I,成绩int(3)NU1.1.PRIMARYKEY（学号），FOREIGNKEY（学号）REFERENCESXS（学号）ONDE1.ETERESTRICTONUPDATERESTRICT）ENGlNE=INNODB;9. CREATETAB1.EXS2（学号varchar（6）NOTNU1.1.,姓名varchar（8）NOTNU1.1.,出生日期dateNU1.1.,PRlMARYKEY（学号），FOREIGNKEY（学号）REFERENCESXS（学号）ONUPDATECASCADE）ENGINE=INNODB;单元8数据库编程一.略DE1.IMITER$CREATETRIGGERsjnsAFTERINSERTON主侬信息表FOREACHROWBEGINIF（NEW,题目like内容审荏表.题目）or（NEW.内容like内容本电表.内容）THENUPDATE主题信息表SET是否显示否，WHERE主跑ID三三NEW.主题ID;ENDIF;END$DE1.IMITER;2.DE1.IMITER$CREATEEVENTevet-eONSCHEDU1.EEVERY60MINUTEstartsCurdateo+IhrrERVA1.60minuteDOBEGINUPDATE主题信息表set信息标志=Nwhere是否显示='否'；END$DE1.IMITER;1. SETVarI=1,user2=2,u$er3=3；2. SE1.ECT1.EFT（课程名，3）FROMKC;3. SE1.ECrSUBSTRING(姓名，1,1)AS姓，SUBSTRING(姓名，2,1.ENGTH(姓名)一1)AS名FROMXS;4. SE1.ECT学号,姓名，YEAR(NOW()-YEAR(出生日期)AS年龄FRoMXSWHERE性别=0;5. SE1.ECT姓名,旧性别;OJ女丁劝AS性别，专业名FROMXSWHERE姓名1.IKE6. DE1.IMITER$CREATEPROCEDUREDE1.ETEeSTUDENT(INXHCHAR(6)BEGINDE1.ETEFROMXSWHERE学9=XH;END$DE1.IMITER;7. DE1.IMITER$CREATEPROCEDUREXSCJ.DO,UPDATE(INXHCHAR(6),INKCMCHAR(16)BEGINDECIAREKCHCHAR；DEC1.AREXFTINYINT;DEC1.ARECJTINYINT;SE1.ECT课程学分INTOKCH.XFFROMKCWHERE课程Z=KCM;SE1.ECT成绩INTOOFROMXS_KCWHERE学号“HAND课程号KCH;IF<60THENUPDATE×S-KCSET学分=OWHERE学号=XHAND课程号=KCH;E1.SEUPDATEXS_KCSET学分XFWHERE学号=XHAND课程号=KCH;ENDIF;END$DE1.IMITER;8. DE1.IMITER$CREATEFUNCTIONNUM_OF_XS()RETURNSINTEGERBEGINRETURN(SE1.ECTCOUNT(*)FROMXS);END$DE1.IMITER;9. DE1.IMITER$CREATETRIGGERXS_DE1.ETEAFTERDE1.ETEONXSFOREACHROWBEGINDE1.ETEFROMXS_KCWHERE学号:OUI学号；END$DE1.IMITER;10. DeiimiterSSCREATETRIGGER×S.KC.UPDATEBEFOREUPDATEONXS_KCFOREACHROWBEGINDeciarexfint(I);SE1.ECT学分INTOXFFROMKCWHERE课程号=NEW.课程号；IFNEW.成绩<60THENSETNEW.学分=0;E1.SESETNEW.学分=XF;ENDIF;END$DE1.IMITER;11. DE1.IMITER$CREATETRIGGERXS_KC_ZXFAFTERINSERTONXS_KCFOREACHROWBEGINDEC1.AREXFINT(I);SE1.ECT学分INToXFFRoMKCWHERE课程号=NEW.邨程号；IFNEW.成绩>=60THENUPDATEXSSET总学分=总学分+XFWHERE学号=NEW.学号;ENDIF;ENDSSDE1.IMITER;12. 首先，定义存储过程：DE1.IMITER$CREATEPROCEDURECHANGES()BEGINREP1.ACEINTOSTUDENTSE1.ECT4FROMX$;ENDSS接着创建觥发渊：CREATETRIGGERSTUDENT-CHANGEAFTERINSERTONXSFOREACHROWCA1.1.CHANGES(;DE1.IMITER;单元9数据库管理一.略1. RENAMEUSER'kingr'localhosfTO'kel'g>'ocalhost,king2,0,localhosfTO,ken2,'Iocalhost,;2.USEXSCJ;GRANTSE1.ECTONXSTOkingloCaIhoSt;3. USEXSCJ；GRANTSE1.ECT.UPDATEONXSTOIiulocalhostIDENTIFIEDBY,1.f>WDZhang1。CaIhOStIDENTIFIEDBY,ZPWD,;4. USEXSCJ;GRANTA1.1.ON»TOkinglocalhost;5. USEXSCJ;GRANTA1.1.ONTOkinglocalhost;6. GRANTSE1.ECTONXSTOJimloCaIhOStWITHMAX_QUERIES_PER_HOUR1;7. REVOKESE1.ECTONXSFROMDaVid1。CalhOSt;8. 首先导出数据：usexsa；SE1.ECTFROMKCINTOOUTFI1.E'Dmyfilel.txt,FIE1.DSTERMINATeDBY1/OPTIONA1.1.YENC1.OSEDBY'"11.inesterminatedby'?1；文件备份先后可以将文件中的数据导入到COURSE表中.使用以下命令:1.OADDATAINFIlElDFI1.Emyfllel.t×tlINTOTAB1.ECOURSEFIE1.DSTERMINATEDBY,OPTIONA1.1.YENC1.OSEDBY,',1.inesterminatedby?；