MySQL教程（新體系-綜合應(yīng)用實例視頻）（第4版） 習題-第08章-答案

參考答案習題-第08章一、選擇題 1.C 2.A 3.B 4.D 5.C 6.D 7.D 8.A 9.D 10.B二、說明題 1.好處：MySQL利用索引加速了WHERE子句中與條件相匹配的行的搜索，或者說在執(zhí)行連接時加快了與其他表中的行匹配的行的搜索。弊端：首先，索引是以文件的形式存儲的，索引文件要占用磁盤空間。如果有大量的索引，索引文件可能會比數(shù)據(jù)文件更快地達到最大的文件尺寸。其次，在更新表中索引列上的數(shù)據(jù)時，對索引也需要更新，這可能需要重新組織一個索引，如果表中的索引很多，這是很浪費時間的。也就是說，這樣就降低了添加、刪除、修改和其他寫入操作的效率。表中的索引越多，則更新表的時間就越長。 2.索引有普通索引（INDEX）、唯一性索引（UNIQUE）、主鍵（PRIMARYKEY）、前綴索引、全文索引（FULLTEXT）、哈希索引（HASH）等。 前綴索引：對文本的前幾個字符（具體是幾個在建立索引時指定）建立索引，這樣建立起來的索引文件更小、檢索速度更快。但是不能在ORDERBY或GROUPBY中使用前綴索引。 全文索引：在定義這種索引的列上支持值的全文檢索，允許在這些索引列中插入重復值和空值。全文索引只能在char、varchar或text類型的列上創(chuàng)建，并且只能在MyISAM表中創(chuàng)建。 3.三者的區(qū)別： （1）主鍵唯一標識一條記錄，不能有重復的，不允許為空；外鍵是另一表的主鍵，可以有重復的，也可以是空值；索引字段沒有重復值，但可以有一個空值。 （2）主鍵用來保證數(shù)據(jù)完整性；外鍵用來和其他表建立關(guān)聯(lián)；索引則是為了提高查詢排序的速度。 （3）主鍵只能有一個；外鍵可以有多個；一個表可以有多個惟一索引。 PRIMARYKEY是主鍵索引，UNIQUE是唯一性索引，主鍵其實是唯一性索引的一種，與之區(qū)別在于：每個表只能有一個主鍵，但可以有多個唯一性索引。 4.有以下3種創(chuàng)建方法 （1）CREATEINDEX語句：可以在一個已有表上創(chuàng)建索引，一個表可以創(chuàng)建多個索引。 （2）ALTERTABLE語句：在修改表的時候向其中添加索引。 （3）CREATETABLE語句：在創(chuàng)建表的語句中包含索引的定義。三、編程題 1.查詢成績（cj）表中指定課程號（@mykch）的學號、成績和等級。USExscj;SET@mykch='101';SELECT學號,成績, (CASEWHEN成績>=90THEN'優(yōu)秀' ELSE(CASEWHEN成績>=80THEN'良好' ELSE(CASEWHEN成績>=70THEN'中等' ELSE(CASEWHEN成績>=60THEN'及格' ELSE'不及格' END) END) END) END)AS等級 FROMcj WHERE課程號=@mykch; 2.查詢成績（cj）表指定年級（@mynj）的學號、成績和滿分150分成績。 3.統(tǒng)計學生表（xs）總學分在50分以上的人數(shù)。SELECTCOUNT(學號)AS'總學分50以上的人數(shù)' FROMxs WHERE總學分>50; 4.查詢成績（cj）表指定課程號（@mykch）的學生的最高分、最低分和平均分。SET@mykch='101';SELECTMAX(成績)AS'最高分',MIN(成績)AS'最低分',AVG(成績)AS'平均分' FROMcj WHERE課程號=@mykch; 5.查詢所有學生選過的課程名和課程號。SELECT課程號,課程名 FROMkc WHERE課程號IN ( SELECTDISTINCT(課程號) FROMcj ); 6.查詢選修了指定課程號（@mykch）且成績在80分以上的學生姓名及成績。SET@mykch='206';SELECT姓名,成績 FROMxs,cj WHERExs.學號=cj.學號 AND課程號=@mykch AND成績>80; 7.查詢選修了指定課程名（@mykcm）課程且成績在80分以上的學生學號、姓名、課程名及成績。SET@mykcm='離散數(shù)學';SELECTxs.學號,姓名,課程名,成績 FROMxs,kc,cj WHERExs.學號=cj.學號 ANDkc.課程號=cj.課程號 AND課程名=@mykcm AND成績>80; 8.查詢所有學生選過的課程名。SELECT課程名 FROMkc WHERE課程號IN ( SELECTDISTINCT(課程號) FROMcj ); 9.查詢所有姓“王”的學號倒數(shù)或者學號第二個數(shù)字為0的學生的學號、姓名及專業(yè)名。SELECT學號,姓名,專業(yè)名 FROMxs WHERE姓名LIKE'王%' AND( SUBSTRING(學號,1,1)='0' ORSUBSTRING(學號,-2,1)='0' ); 10.查詢學號里包含4、5、6或者學號以08結(jié)尾的學生學號、姓名和專業(yè)名。SELECT學號,姓名,專業(yè)名 FROMxs WHERELOCATE('4',學號)>0 ORLOCATE('5',學號)>0 ORLOCATE('6',學號)>0 OR學號LIKE'%08'; 11.查詢在1993年1月～6月出生的學生情況。SELECT*FROMxs WHERE出生日期BETWEEN'1993-01-01'AND'1993-06-30'; 12.查詢“計算機”或“通信工程”的學生的情況。 13.查詢未選修指定課程名（@mykcm）的學生的姓名、學號、專業(yè)名。SET@mykcm='計算機基礎(chǔ)';SELECT姓名,學號,專業(yè)名 FROMxs WHERE學號NOTIN ( SELECTxs.學號 FROMxs,kc,cj WHERExs.學號=cj.學號 ANDkc.課程號=cj.課程號 AND課程名=@mykcm ); 14.查詢比所有計算機專業(yè)的學生年齡都大的學生學號、姓名、專業(yè)名、出生日期。SELECT學號,姓名,專業(yè)名,出生日期 FROMxs WHERE出生日期< ( SELECTMIN(出生日期) FROMxs WHERE專業(yè)名='計算機' ); 15.查詢所有女學生的姓名、學號以及與指定學號（@myxh）學生的年齡差距。SET@myxh='081202';SELECT姓名,學號, FLOOR( DATEDIFF ( 出生日期,(SELECT出生日期FROMxsWHERE學號=@myxh) )/365)AS年齡差距 FROMxs WHERE性別=0; 16.統(tǒng)計各專業(yè)的學生數(shù)。SELECT專業(yè)名,COUNT(*)AS學生數(shù) FROMxs GROUPBY專業(yè)名; 17.統(tǒng)計選修的各門課程的平均成績和選修該課程的人數(shù)。SELECT課程號,AVG(成績)AS平均成績,COUNT(學號)AS選修人數(shù) FROMcj GROUPBY課程號; 18.產(chǎn)生一個結(jié)果集，包括每個專業(yè)的男生人數(shù)、女生人數(shù)、總?cè)藬?shù)，以及學生總?cè)藬?shù)。SELECT專業(yè)名,性別,COUNT(*)AS人數(shù) FROMxs GROUPBY專業(yè)名,性別 WITHROLLUP; 19.產(chǎn)生一個結(jié)果集，包括每門課程各專業(yè)的平均成績、每門課程的總平均成績和所有課程的總平均成績。SELECT課程名,專業(yè)名,AVG(成績)AS平均成績 FROMcj,kc,xs WHEREcj.課程號=kc.課程號ANDcj.學號=xs.學號 GROUPBY課程名,專業(yè)名 WITHROLLUP; 20.計算平均成績在85分以上的學生的學號和平均成績。SELECT學號,AVG(成績)AS平均成績 FROMcj GROUPBY學號 HAVINGAVG(成績)>=85; 21.查詢選修課程超過2門且成績都在80分以上的學生的學號。SELECT學號 FROMcj WHERE成績>=80 GROUPBY學號 HAVINGCOUNT(*)>2; 22.查詢通信工程專業(yè)平均成績在85分以上的學生的學號和平均成績。SELECT學號,AVG(成績)AS平均成績 FROMcj WHERE學號IN ( SELECT學號 FROMxs WHERE專業(yè)名='通信工程' ) GROUPBY學號 HAVINGAVG(成績)>=85; 23.將計算機專業(yè)學生的“計算機基礎(chǔ)”課程成績按降序排列。SELECT姓名,課程名,成績 FROMxs,kc,cj WHERExs.學號=cj.學號 ANDcj.課程號=kc.課程號 AND課程名='計算機基礎(chǔ)' AND專業(yè)名='計算機' ORDERBY成績DESC; 24.將計算機專業(yè)學生按其平均成績排列。SELECT學號,姓名,專業(yè)名 FROMxs WHERE專業(yè)名='計算機' ORDERBY( SELECTAVG(成績) FROMcj GROUPBYcj.學號 HAVINGxs.學號=cj.學號 ); 25.先用WITH查詢2個學生的所有考試成績，然后把它們聯(lián)合起來。WITHcte_stu1_cjAS( SELECT姓名,課程名,成績 FROMxs,kc,cj WHERExs.學號=cj.學號 ANDkc.課程號=cj.課程號 ANDxs.學號='081113'),cte_stu2_cjAS( SELECT姓名,課程名,成績 FROMxs,kc,cj WHERExs.學號=cj.學號 ANDkc.課程號=cj.課程號 ANDxs.學號='081202')SELECT*FROMcte_stu1_cj UNIONSELECT*FROMcte_stu2_cj; 26.向準備計算指定學生的平均成績，然后計算2個學生的平均成績。 27.創(chuàng)建計算機專業(yè)學生的平均成績視圖view_avg，包括學號和平均成績：CREATEVIEWview_avg AS SELECTxs.學號,AVG(成績)AS平均成績 FROMcj,xs WHERE專業(yè)名='計算機' ANDxs.學號=cj.學號 GROUPBYcj.學號; 基于view_avg查詢平均成績大于80的學生情況：SELECTxs.學號,姓名,專業(yè)名,性別,出生日期,年齡,總學分,平均成績 FROMxs,view_avg WHERExs.學號=view_avg.學號 AND平均成績>80; 28.創(chuàng)建計算機專業(yè)學生視圖view_jsjCREATEVIEWview_jsj AS SELECT*FROMxs WHERE專業(yè)名='計算機'