習題1答案1.選擇題C（2）C（3）B（4）D（5）D（6）B簡答題答案略習題2答案1.選擇題C（2）C（3）B（4）D（5）D（6）A（7）A（8）C2.簡答題答案略上機操作題圖書表結構設計如下字段名數(shù)據(jù)類型約束說明bookidintauto_increment主鍵圖書idbooknamevarchar(100)notnull圖書名authorvarchar(100)作者publishervarchar(30)出版社pricedecimal(5,2)價格建表語句：CREATETABLEbook(bookidINTauto_incrementPRIMARYKEY,booknameVARCHAR(100)notnull,authorVARCHAR(100),publisherVARCHAR(30),priceDECIMAL(5,2));錄入測試數(shù)據(jù)：INSERTINTObookVALUES(1,'三國演義','羅貫中','人民文學出版社',68.9);INSERTINTObookVALUES(null,'紅樓夢','曹雪芹','人民文學出版社',78.9);（4）SELECT*FROMbook;習題3答案選擇題C（2）C（3）C（4）A（5）B（6）C（7）A（8）A（9）D（10）C解答題答案略上機操作題SELECTDISTINCTsnoFROMscore;SELECTsno,gradeFROMscoreWHEREcno='c101'ORDERBYgradeDESC,snoASC;SELECTcno,AVG(grade),MAX(grade)fromscoreGROUPBYcno;SELECTcount(*)FROMcourse;SELECTsnoFROMscoreGROUPBYsnoHAVINGCOUNT(cno)>=5;SELECTcount(*)FROMscoreWHEREcno='c101';SELECTsno,count(*)FROMscorewheregrade<60GROUPBYsno;SELECTsno,snameFROMstudentWHEREsnoIN(SELECTsnoFROMscoreWHEREgradeISNULL);SELECTo,cnameFROMcourse,scorewhereo=ogroupbyoHAVINGcount(*)>=30;SELECTstudent.sno,sname,ame,gradefromstudent,score,coursewherestudent.sno=score.snoando=o;SELECTsno,cnofromscoreORDERBYgradedesclimit1;SELECTcno,snofromscorexwheregrade<(selectavg(grade)FROMscoreywhereo=o);SELECTstudent.sno,count(*)count_cnofromscore,studentwherescore.sno=student.snoandspec='軟件技術'groupbystudent.snoORDERBYcount_cnodescLIMIT1;SELECTstudent.sno,snamefromstudent,scorewherestudent.sno=score.snoandgrade<60GROUPBYstudent.snoHAVINGcount(*)>=2;習題4答案選擇題D（2）C（3）D（4）B（5）B（6）D（7）C（8）D簡答題答案略上機操作題CREATEINDEXidx_cnameONcourse(cname);CREATEINDEXidx_snameONstudent(sname);CREATEVIEWview_studentasSELECT*FROMstudentWHEREspec='軟件技術';CREATEORREPLACEVIEWview_studentasSELECT*FROMstudentWHEREspec='軟件技術'WITHCHECKOPTION;insertintoview_studentVALUES('20231012108','史新朋','男','2005-02-16','軟件技術');insertintoview_studentVALUES('20231012109','劉晶','女','2004-12-19','通信技術');當插入第二條數(shù)據(jù)時，報告出錯。原因是建立視圖時添加了WITHCHECKOPTION選項，添加數(shù)據(jù)時會檢查是否是軟件技術專業(yè)，如果不是，則禁止添加。UPDATEview_studentsetsno='20231012118'WHEREsname='史新朋';習題5答案選擇題C（2）B（3）D（4）B（5）C（6）C簡答題答案略習題6答案選擇題D（2）C（3）A（4）A（5）B（6）D（7）A簡答題答案略習題7答案選擇題D（2）D（3）C（4）B（5）D（6）C（7）C簡答題答案略習題8答案選擇題A（2）A（3）C（4）D（5）D（6）C思考題答案略上機操作題創(chuàng)建用戶：CREATEUSER'xiaoming'@'localhost'IDENTIFIEDBY'abc456';授予權限：GRANTSELECTONteaching.*TO'xiaoming'@'local