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

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