MySQL数据库基础实例教程第二版（微课版）周德伟习题答案.docx

1、MySQL数据库基础实例教程第2版（微课版）习题答案第一章、B2.B3.B4.C5.D6.C7.A8.A9.A10.D«、1.root2.quitmy.ini第二章%l.B2.C3.A4.D5.D6.A7.D8.C9.C10.A(DB(2) A方案一张表：6*10*5000=30,0000个数据B方案三张表：学生表：3*5000=15000课程表：2*10=20成绩表：3*10*5000=150000三张表合计：15000+20+150000=16,5020A与B方案数据差:30,0000-16,5020=13,4980(3) B更节省空间，因为B方案满足第二范式，而A方案不满足。2

2、.(1)班级州级编$圳级名称班级州级编$圳级名称所14卅级成绩所属院系课段(2)学生(学号、姓名、性别、出生日期、籍贯、民族、班级编号)。班级(班级编号、班级名称、所属院系、年级、班级人数)。课程(课程号、课程名、学分、课时、开课学期、前置课)。选修(学号、课程号、成绩)(3)DELIMITER$SCREATETRIGGERs_upAFTERUPDATEONstudentFOREACHROWBEGINIfnew.民族!='汉'thenUPDATEscoreset成绩=成绩+1WHERE学号=NEW.学号;Endif;ENDS$DELIMITER;7(1)CREATEEVENTe

3、venl_叩ONSCHEDULEEVERYIMINUTESTARTSCURDATEO+INTERVAL1MINUTEDOUPDATEcourseset学分=学分+1where课程号='110()3'(2)SelectEVENT_SCHEDULER;SETGLOBALEVENT_SCHEDULER=1;/*(3)*/DROPEVENTevent_up;第九章、LA2.A3.C4.D5.C6.C7.C8.D二、CREATEUSERkingllocalhostIDENTIFIEDBY'kenI',king2localhostIDENTIFIEDBY'ken2&

4、#39;GRANTSELECTONschooldb.studentTOking1localhost;1. GRANTSELECT,叩dateONschooldb.classTOking2localhost;2. GRANTSELECTONschooldb.*TOkingllocalhost;3. GRANTALLONschooldb.*TOkingllocalhost;4. REVOKEDELETEONschooldb.classFROMking2localhost;5. SELECT*FROMcourseINTOOUTFILE'Di/cl.txt'FIELDSTERMINAT

5、EDBY'；OPTIONALLYENCLOSEDBY'LINESTERMINATEDBY?;TERMINATEDBYLOADDATAINFILE'D:/cl.txt'INTOTABLEbackup.cFIELDSOPTIONALLYENCLOSEDBY”LINESTERMINATEDBY'?*;第三章LA2.D3.A4.B5.D-、1. createdatabaseschooldb;2.Useschooidb;CREATETABLEclass(班级编号char(6)NOTNULLPRIMARYKEY,班级名称varchar(20)NOTNULL,院系va

6、rchar(30)NOTNULL,年级intNULL,人数intNULL)；CREATETABLEcourse(课程号char(6)NOTNULLPRIMARYKEY,课程名varchar(20)NOTNULL,学分intNOTNULL,学时intNOTNULL,学期char(2)NULL,前置课char(6)NULL)；CREATETABLEscore(学号char(10)NOTNULL,课程号char(6)NOTNULL,成绩float(5,2)NULL,PRIMARYKEY(学号，课程号)；CREATETABLEstudent(学号char(10)NOTNULLPRIMARYKEY,姓名

7、varchar(20)NOTNULL,性别char(2)NOTNULL,出生日期dateNULL,地区varchar(20)NULL,民族varchar(IO)NULLDEFAULT汉，班级编号char(6)NULL)；3.（1）ALTERTABLEstudentADDFOREIGNKEY（班级编号）REFERENCESclass（班级编号）;（2）ALTERTABLEcourseADDFOREIGNKEY（前置课）REFERENCEScourse（课程号）ONDELETESETNULLONUPDATECASCADE;（3）ALTERTABLEscoreADDFOREIGNKEY（学号）REF

8、ERENCESstudent（学号）ONDELETERESTRICTONUPDATERESTRICT;（4）ALTERTABLEscoreADDFOREIGNKEY（课程号）REFERENCEScourse（课程号）ONDELETECASCADEONUPDATECASCADE;altertablescoreaddchcck（成绩=0and成绩=100）;altertablescoreaddcheck（成绩=0and成绩=100）;第四章LA2.B3.A4.D5.D、1.-RecordsofclassINSERTINTO'class'VALUES（'AC1301'

9、,'会计13-1班'，'会计学院2013,35）;INSERTINTO'class、VALUES（'AC1302','会计13-2班'，'会计学院2013,35）;INSERTINTO'class'VALUES（CS1401，,'计算机14-1班计算机学院，,2014,35）;INSERTINTO'class'VALUESCIS130'信息系统13-1班'，'信息学院2013,NULL）;INSERTINTO'class'VALUES（TS14

10、0,'信息系统14-1班'，'信息学院NULL,30）;-RecordsofclassINSERTINTO'class'VALUES（'AC1301','会计13-1班'，'会计学院2013,35）;INSERTINTO'class'VALUES（'AC1302','会计13-2班'，'会计学院2013,35）;INSERTINTO'class'VALUES（'CS1401','计算机14-1班计算机学院2014,35）

11、;INSERTINTO'class'VALUES（TS1301,'信息系统13-1班'，'信息学院2013,NULL）;INSERTINTO'class'VALUESCIS1401*,'信息系统14-1班'，'信息学院NULL,30）;(00OC8(0056(00.68(00.S6(00.9Z.(00.08(00.89(00.S3S0.S8s().宓OON630OOC(00.98S0.6800.983。6一寸()()一3Z00N一寸。0-Vzoo一/-so-r一寸()()一3zoom.一寸。0一r«-一。

12、0一强。0目_-80-一00一/1=_00一Z-80-,)一()目103)SUU3VAoss，OHN-F-UU-ISNIXOIO-Z寸-03)S3O,2。尸oiNIJXWSNI工0一0一2寸一oesatmv>0炎ojlnijl卷sni工0一0一Z寸一oe230,?。严OHNIFNUSNIso一0一饵一oeW3<>,?0多C1LN1住SNI壬)二)一饵一03)sOJrnvA,$ss，OLN-杉usni03K20esUJmvAohni玉心SNI工08一R-OeswrnoOHNIINWSNI=ozo二s-oe230O8S，UNI五钦NI_«-a)z()二S2()e220s

13、s，OJLN-杉uaSNItozo二203)S3<>0。/OHNI五USNILrou一一兰oe23008OFNI玉gsNIKOE一一E-Oe路IYIVA,28s，OFNIJL*钦NILEO一()二兰)3)SUU3VAoss，ohn-f-uu-isnito一O【-E-Oc230OIN匚卷SNI.-一0一0一一s-oesgmv>,208OFNI一卷SNI。.一OOSJOspo£!±1(。寸一SLW淇W工X丁966一-耘L蒙罪丹-*0一01孚_oesunlvA号P3S,OHNI岳SNI30寸一SLML是X-L6OOT966一-菌。卷H-工0一0一E20Z.)SB

14、OJuopms，OHN一影SN一_(-一。寸一烙螳廿-一却§(<66一醐蚓植箫zoo)z寸一oeSHfnVAJugpms,OHNIH质SNIco寸一8度cfewK0,nkc66一-成-.bfuoo一令一oe230Jugpnls，OHNI杉WSNIX-一om_SLC-MSJ苣oo一一<A66-L瞰咪-Ho一0一饵一sS3<>号pas,0HN1邕SN一-(-CEISL第cfeM.LO%6O，S66一-3»<-UOOIEEOZ.)S3<>Juopms,OHNIE-富SNI_(zo2o<LKUw-'0,966L_*邸。-容&g

15、t;Hoa一一20C330Juopms，OFN-岳SNI-(Nos一o<鉴Ew-Ez.69966一WLrnd-tozo二EseS3<>JuopnJS,OFNIF蚩SN一_(-8m<。这-理辰-000。6。-*-踏H-so8一一S一oeu<>JUDPms,OHNII质SNI_(-om<.必-L'flm-三，Z.Z.66一-.,HR-L鼠理米-一0一0一一E8esumvAJuopnls,OFNIE-邕SN一=<Dpmsjospo。:X00N8>00&go一0_?-0e230,$8S,OJLN-杉uaSNI-(00一6L一。ONN

16、03K寸一。c230QJO。/OINIJXUSNI-(00.08-,寸。ON工0一0一EloesUJmvAo8yOE-NIEUJSNI-(00.6Z.-»一。0一z-工0一0一0一oe路IYIVA,20京OHNIJLN钦NI2.(1) .insertintostudentvaluesC50200F,'王晓林男'，'2000-02-10；'广东'，'汉'IS2020,);.insertintostudentset学号-500102',姓名='林丽'，性别二'女'；(2) updatestud

17、entset地区=concat(地区,'(省或市)');updatestudentset出生日期='1998-02-10',班级编号=，AC1302where姓名='王一敏'；(3) deletefromstudentwhere出生日期v'1997-01-01'第五章、C2.B3.B4.A5.B6.C7.C8.B9.D10.B、1.(1)select姓名asname,(year(now()-year(出生日期)asagefromstudent;(2)select*,casewhen成绩=90then'优，when成绩=75

18、and成绩90then'良when成绩=60and成绩75then'及格，else'不及格endas成绩档fromscore;(3)select课程名，学分fromcoursewhere学时=48;(4)select课程名，学期fromcoursewhere前置课isnull;(5)select*fromstudentwhere姓名like，王_2.(1)selectstudent.学号，姓名,score.课程号，成绩fromstudent,course,scorewherestudent.学号=score.学号andcourse.课程号=score.课程号；(2)se

19、lect学号，姓名，班级名称fromstudent,classwherestudent.班级编号=class.班级编号and院系='会计学院'；(3)selectscore.学号，姓名，成绩fromsludent,scorewherestudent.学号=score.学号and成绩90;(4)selectcourse.课程号，课程名，学号，成绩fromcourseleftjoinscoreoncourse.课程号=score,课程号；（5）select学号，姓名fromstudentwhere班级编号in（select班级编号fromclasswhere班级名称='计算

20、机14-1班）;3.（1）select性别,count（*）fromstudentgroupby性别;（2）select学号,count（课程号）,avg（成绩）,max（成绩）fromscoregroupby学号；（3）select学号,count（课程号）,avg（成绩）,max（成绩）fromscoregroupby学号havingavg（成绩）>=80;（4）select性别，民族,count（*）fromstudentgroupby性别，民族orderbycount（*）;第六章LC2.B3.C4.B5.C6.D*、1.createviewv_scoreasselectstud

21、ent.学号，姓名，民族，课程号，成绩fromstudent,scorewherestudent.学号=score.学号；2.select学号，姓名，课程号，成绩fromv_scorewhere民族!='汉'；3.createviewv_avg（num,scorc_avg）asselect学号,avg（成绩）fromscoregroupby学号；4.select*fromv_avgwherescore_avg>80;5.createviewv_studentasselect*fromstudentwhere民族二'汉'withcheckoption;ins

22、ertintov_studentvalues（'2020410001','李牧'，'男71998-10-21'；广东'，'汉NULL）;6.deletefromv_studen(where性别='女'；第七章LA2.A3.C4.D5.C6.B二、1. createindexI_kconcourse(课程名(3)ASC);altertableclassaddindexI_cx(院系，年级)；altertablestudentaddunique(姓名)；altertableclassaddprimarykey(课程号)

23、；altertablescoreaddprimarykey(学号，课程号),addindex(成绩)；altertablecoursedropprimarykey;2. altertablecoursepartitionbyKey(学分)partitions4;第八章、LB2.B3.A4.C5.C6.B7.B8.C9.B10.BMM*、1.Setx=12.54;Sely=-10.63456;(1) Selectfloor(x),floor(y),round(x),round(y);SELECTTRUNCATE(y,2),TRUNCATE(y,4);2.SETsl='ABCDEFG

24、9;SETs2='XYZ(l)SELECTLEFT(s1,3),right(s1,3);SELECTLTRIM(s2),RTRIM(s2),TRIM(s2);(2) selectsubstring(sl,3,4);SELECTSTRCMP(sl,s2);3. selectnow(),CURTIME(),CURDATE(),YEAR(now(),MONTHNAME(now(),DAYNAME(now(),DATE_ADD(now(),INTERVAL-10DAY);4.(1)DELIMITER$CREATEPROCEDUREshowjj(OUTseasonVARCHAR(8)BEGINC

25、ASEWHENmonth(now()in(1,2,3)THENSETseason='春季'；WHENmonth(now()in(4,5,6)THENSETseason='夏季'；WHENmonth(now()in(7.8.9)THENSETseason='秋季'；WHENmonth(now()in(10,11,12)THENSETseason='冬季'；ENDCASE;END$SDELIMITER;callshow_jj(a);selecta;(2)DELIMITER$createproceduresum_n(innint,outrsini)beginSetrs=O;WHILEn>0DOSetrs=rs+n;SETn=n-l;ENDWHILE;End$SDELIMITER;ca