2025數(shù)據(jù)庫系統(tǒng)工程師考試數(shù)據(jù)庫系統(tǒng)性能優(yōu)化與瓶頸分析實(shí)戰(zhàn)試題考試時間：______分鐘總分：______分姓名：______一、關(guān)系數(shù)據(jù)庫設(shè)計(jì)要求：請根據(jù)以下關(guān)系模式，完成以下設(shè)計(jì)任務(wù)。關(guān)系模式：1.學(xué)生（學(xué)號，姓名，性別，年齡，班級號）2.課程（課程號，課程名，學(xué)分，教師號）3.成績（學(xué)號，課程號，成績）4.教師（教師號，姓名，職稱，性別，年齡）1.1請為上述關(guān)系模式定義主鍵和外鍵。1.2請根據(jù)需求，設(shè)計(jì)一個查詢語句，查詢所有年齡大于20歲的女學(xué)生的姓名和班級號。1.3請根據(jù)需求，設(shè)計(jì)一個查詢語句，查詢所有課程名為“數(shù)據(jù)庫系統(tǒng)”的課程的學(xué)分和教師姓名。1.4請根據(jù)需求，設(shè)計(jì)一個查詢語句，查詢所有學(xué)生的姓名和成績，成績高于90分的學(xué)生姓名后加“（優(yōu)秀）”，低于60分的學(xué)生姓名后加“（不及格）”。1.5請根據(jù)需求，設(shè)計(jì)一個查詢語句，查詢所有未開設(shè)課程的教師姓名。1.6請根據(jù)需求，設(shè)計(jì)一個查詢語句，查詢所有男教師的姓名和年齡。1.7請根據(jù)需求，設(shè)計(jì)一個查詢語句，查詢所有學(xué)生的姓名和所在班級的平均成績。1.8請根據(jù)需求，設(shè)計(jì)一個查詢語句，查詢所有未修滿20學(xué)分的學(xué)生姓名。1.9請根據(jù)需求，設(shè)計(jì)一個查詢語句，查詢所有教師的姓名和職稱。1.10請根據(jù)需求，設(shè)計(jì)一個查詢語句，查詢所有學(xué)生的姓名和所在班級的班級名。二、SQL語句編寫要求：請根據(jù)以下需求，編寫相應(yīng)的SQL語句。2.1請刪除所有年齡小于18歲的學(xué)生記錄。2.2請將所有學(xué)生的年齡增加1歲。2.3請將所有教師的職稱修改為“副教授”。2.4請將所有課程的學(xué)分修改為原來的2倍。2.5請將所有學(xué)生的成績修改為原來的1.5倍。2.6請將所有學(xué)生的班級號修改為所在班級的學(xué)生數(shù)量。2.7請將所有教師的年齡修改為原來的0.9倍。2.8請將所有課程的教師號修改為1。2.9請將所有學(xué)生的性別修改為“女”。2.10請將所有課程的學(xué)分修改為原來的0.5倍。三、數(shù)據(jù)庫查詢優(yōu)化要求：請根據(jù)以下需求，對查詢語句進(jìn)行優(yōu)化。3.1優(yōu)化以下查詢語句，使其執(zhí)行效率更高：SELECT*FROM學(xué)生WHERE年齡>20AND性別='女';3.2優(yōu)化以下查詢語句，使其執(zhí)行效率更高：SELECT學(xué)號,姓名FROM學(xué)生WHERE班級號=(SELECT班級號FROM學(xué)生GROUPBY班級號HAVINGCOUNT(*)=1);3.3優(yōu)化以下查詢語句，使其執(zhí)行效率更高：SELECT課程號,課程名FROM課程WHERE學(xué)分>(SELECTAVG(學(xué)分)FROM課程);3.4優(yōu)化以下查詢語句，使其執(zhí)行效率更高：SELECT學(xué)生.姓名,班級.班級名FROM學(xué)生,班級WHERE學(xué)生.班級號=班級.班級號;3.5優(yōu)化以下查詢語句，使其執(zhí)行效率更高：SELECT教師號,姓名FROM教師WHERE職稱='副教授'AND年齡>(SELECTAVG(年齡)FROM教師WHERE職稱='副教授');3.6優(yōu)化以下查詢語句，使其執(zhí)行效率更高：SELECT學(xué)號,姓名FROM學(xué)生WHERE年齡>(SELECTAVG(年齡)FROM學(xué)生WHERE性別='男');3.7優(yōu)化以下查詢語句，使其執(zhí)行效率更高：SELECT課程號,課程名FROM課程WHERE學(xué)分>(SELECTMAX(學(xué)分)FROM課程);3.8優(yōu)化以下查詢語句，使其執(zhí)行效率更高：SELECT學(xué)生.姓名,班級.班級名FROM學(xué)生,班級WHERE學(xué)生.班級號=班級.班級號AND班級.班級名='計(jì)算機(jī)科學(xué)與技術(shù)';3.9優(yōu)化以下查詢語句，使其執(zhí)行效率更高：SELECT教師號,姓名FROM教師WHERE職稱='副教授'AND年齡>(SELECTMIN(年齡)FROM教師WHERE職稱='副教授');3.10優(yōu)化以下查詢語句，使其執(zhí)行效率更高：SELECT學(xué)生.姓名,班級.班級名FROM學(xué)生,班級WHERE學(xué)生.班級號=班級.班級號AND班級.班級名LIKE'%計(jì)算機(jī)%';四、數(shù)據(jù)庫索引設(shè)計(jì)要求：請根據(jù)以下需求，設(shè)計(jì)相應(yīng)的數(shù)據(jù)庫索引。4.1為“學(xué)生”關(guān)系中的“學(xué)號”字段創(chuàng)建唯一索引。4.2為“課程”關(guān)系中的“課程號”字段創(chuàng)建唯一索引。4.3為“成績”關(guān)系中的“學(xué)號”和“課程號”字段創(chuàng)建復(fù)合索引。4.4為“教師”關(guān)系中的“教師號”字段創(chuàng)建唯一索引。4.5為“班級”關(guān)系中的“班級號”字段創(chuàng)建唯一索引。4.6為“成績”關(guān)系中的“成績”字段創(chuàng)建非唯一索引，以便快速查詢特定成績的學(xué)生。4.7為“學(xué)生”關(guān)系中的“班級號”字段創(chuàng)建非唯一索引，以便快速查詢特定班級的學(xué)生。4.8為“教師”關(guān)系中的“職稱”字段創(chuàng)建非唯一索引，以便快速查詢特定職稱的教師。4.9為“課程”關(guān)系中的“學(xué)分”字段創(chuàng)建非唯一索引，以便快速查詢特定學(xué)分的課程。4.10為“班級”關(guān)系中的“班級名”字段創(chuàng)建非唯一索引，以便快速查詢特定名稱的班級。五、數(shù)據(jù)庫視圖設(shè)計(jì)要求：請根據(jù)以下需求，設(shè)計(jì)相應(yīng)的數(shù)據(jù)庫視圖。5.1設(shè)計(jì)一個視圖，展示所有學(xué)生的姓名、班級號和所在班級的班級名。5.2設(shè)計(jì)一個視圖，展示所有教師的姓名、職稱和所在學(xué)院的學(xué)院名。5.3設(shè)計(jì)一個視圖，展示所有課程的信息，包括課程號、課程名、學(xué)分和授課教師姓名。5.4設(shè)計(jì)一個視圖，展示所有學(xué)生的姓名和成績，包括課程名和成績等級。5.5設(shè)計(jì)一個視圖，展示所有教師的姓名和授課課程，包括課程名和學(xué)分。5.6設(shè)計(jì)一個視圖，展示所有班級的班級號、班級名和班級人數(shù)。5.7設(shè)計(jì)一個視圖，展示所有學(xué)生的姓名和所在班級的平均成績。5.8設(shè)計(jì)一個視圖，展示所有課程的學(xué)分和授課教師姓名，包括教師的職稱。5.9設(shè)計(jì)一個視圖，展示所有學(xué)生的姓名和所在班級的班級名，包括班級的成立時間。5.10設(shè)計(jì)一個視圖，展示所有教師的姓名和授課班級，包括班級的成立時間。六、數(shù)據(jù)庫觸發(fā)器設(shè)計(jì)要求：請根據(jù)以下需求，設(shè)計(jì)相應(yīng)的數(shù)據(jù)庫觸發(fā)器。6.1設(shè)計(jì)一個觸發(fā)器，在向“學(xué)生”關(guān)系插入新記錄時，自動檢查年齡是否大于18歲，如果不滿足條件，則不允許插入。6.2設(shè)計(jì)一個觸發(fā)器，在向“課程”關(guān)系插入新記錄時，自動檢查學(xué)分是否大于0，如果不滿足條件，則不允許插入。6.3設(shè)計(jì)一個觸發(fā)器，在向“成績”關(guān)系插入新記錄時，自動檢查成績是否在0到100之間，如果不滿足條件，則不允許插入。6.4設(shè)計(jì)一個觸發(fā)器，在刪除“教師”關(guān)系中的記錄時，自動檢查是否有學(xué)生記錄依賴于該教師，如果有，則不允許刪除。6.5設(shè)計(jì)一個觸發(fā)器，在更新“班級”關(guān)系中的記錄時，自動檢查班級號是否已存在，如果存在，則不允許更新。6.6設(shè)計(jì)一個觸發(fā)器，在刪除“課程”關(guān)系中的記錄時，自動檢查是否有成績記錄依賴于該課程，如果有，則不允許刪除。6.7設(shè)計(jì)一個觸發(fā)器，在向“學(xué)生”關(guān)系插入新記錄時，自動將班級號設(shè)置為默認(rèn)值。6.8設(shè)計(jì)一個觸發(fā)器，在更新“教師”關(guān)系中的記錄時，自動檢查年齡是否小于60歲，如果不滿足條件，則不允許更新。6.9設(shè)計(jì)一個觸發(fā)器，在刪除“班級”關(guān)系中的記錄時，自動刪除所有依賴于該班級的學(xué)生記錄。6.10設(shè)計(jì)一個觸發(fā)器，在向“成績”關(guān)系插入新記錄時，自動計(jì)算成績等級（優(yōu)秀、良好、及格、不及格）。本次試卷答案如下：一、關(guān)系數(shù)據(jù)庫設(shè)計(jì)1.1主鍵：學(xué)生（學(xué)號），外鍵：班級號（學(xué)生）參照班級（班級號）。1.2查詢語句：SELECT姓名,班級號FROM學(xué)生WHERE年齡>20AND性別='女';1.3查詢語句：SELECT學(xué)分,姓名FROM課程WHERE課程名='數(shù)據(jù)庫系統(tǒng)';1.4查詢語句：SELECT學(xué)號,姓名,CASE成績WHEN成績>90THENCONCAT(姓名,'（優(yōu)秀）')WHEN成績<60THENCONCAT(姓名,'（不及格）')ELSE姓名ENDAS成績等級FROM成績;1.5查詢語句：SELECT姓名FROM教師WHERE教師號NOTIN(SELECT教師號FROM課程);1.6查詢語句：SELECT姓名,年齡FROM教師WHERE性別='男';1.7查詢語句：SELECT學(xué)生.姓名,班級.班級名,AVG(成績)AS平均成績FROM學(xué)生,成績,班級WHERE學(xué)生.班級號=班級.班級號AND學(xué)生.學(xué)號=成績.學(xué)號GROUPBY學(xué)生.姓名,班級.班級名;1.8查詢語句：SELECT姓名FROM學(xué)生WHERE(SELECTSUM(學(xué)分)FROM課程WHERE課程號IN(SELECT課程號FROM成績WHERE學(xué)號=學(xué)生.學(xué)號))<20;1.9查詢語句：SELECT姓名,職稱FROM教師;1.10查詢語句：SELECT學(xué)生.姓名,班級.班級名FROM學(xué)生,班級WHERE學(xué)生.班級號=班級.班級號;二、SQL語句編寫2.1DELETEFROM學(xué)生WHERE年齡<18;2.2UPDATE學(xué)生SET年齡=年齡+1;2.3UPDATE教師SET職稱='副教授';2.4UPDATE課程SET學(xué)分=學(xué)分*2;2.5UPDATE成績SET成績=成績*1.5;2.6UPDATE學(xué)生SET班級號=(SELECT班級號FROM學(xué)生WHERE學(xué)號=學(xué)生.學(xué)號GROUPBY班級號HAVINGCOUNT(*)=1);2.7UPDATE教師SET年齡=年齡*0.9;2.8UPDATE課程SET教師號=1;2.9UPDATE學(xué)生SET性別='女';2.10UPDATE課程SET學(xué)分=學(xué)分*0.5;三、數(shù)據(jù)庫查詢優(yōu)化3.1優(yōu)化后的查詢語句：SELECT*FROM學(xué)生WHERE年齡>20AND性別='女'INDEX(idx_student_age_gender);3.2優(yōu)化后的查詢語句：SELECT學(xué)號,姓名FROM學(xué)生WHERE班級號=(SELECT班級號FROM學(xué)生GROUPBY班級號HAVINGCOUNT(*)=1)INDEX(idx_student_class);3.3優(yōu)化后的查詢語句：SELECT課程號,課程名FROM課程WHERE學(xué)分>(SELECTAVG(學(xué)分)FROM課程)INDEX(idx_course_credit);3.4優(yōu)化后的查詢語句：SELECT學(xué)生.姓名,班級.班級名FROM學(xué)生,班級WHERE學(xué)生.班級號=班級.班級號INDEX(idx_student_class,idx_class_name);3.5優(yōu)化后的查詢語句：SELECT教師號,姓名FROM教師WHERE職稱='副教授'AND年齡>(SELECTAVG(年齡)FROM教師WHERE職稱='副教授')INDEX(idx_teacher_title_age);3.6優(yōu)化后的查詢語句：SELECT學(xué)號,姓名FROM學(xué)生WHERE年齡>(SELECTAVG(年齡)FROM學(xué)生WHERE性別='男')INDEX(idx_student_age);3.7優(yōu)化后的查詢語句：SELECT課程號,課程名FROM課程WHERE學(xué)分>(SELECTMAX(學(xué)分)FROM課程)INDEX(idx_course_max_credit);3.8優(yōu)化后的查詢語句：SELECT學(xué)生.姓名,班級.班級名FROM學(xué)生,班級WHERE學(xué)生.班級號=班級.班級號AND班級.班級名='計(jì)算機(jī)科學(xué)與技術(shù)'INDEX(idx_student_class,idx_class_name);3.9優(yōu)化后的查詢語句：SELECT教師號,姓名FROM教師WHERE職稱='副教授'AND年齡>(SELECTMIN(年齡)FROM教師WHERE職稱='副教授')INDEX(idx_teacher_title_age);3.10優(yōu)化后的查詢語句：SELECT學(xué)生.姓名,班級.班級名FROM學(xué)生,班級WHERE學(xué)生.班級號=班級.班級號AND班級.班級名LIKE'%計(jì)算機(jī)%'INDEX(idx_student_class,idx_class_name);四、數(shù)據(jù)庫索引設(shè)計(jì)4.1創(chuàng)建唯一索引：CREATEUNIQUEINDEXidx_student_idON學(xué)生(學(xué)號);4.2創(chuàng)建唯一索引：CREATEUNIQUEINDEXidx_course_idON課程(課程號);4.3創(chuàng)建復(fù)合索引：CREATEINDEXidx_score_student_courseON成績(學(xué)號,課程號);4.4創(chuàng)建唯一索引：CREATEUNIQUEINDEXidx_teacher_idON教師(教師號);4.5創(chuàng)建唯一索引：CREATEUNIQUEINDEXidx_class_idON班級(班級號);4.6創(chuàng)建非唯一索引：CREATEINDEXidx_score_gradeON成績(成績);4.7創(chuàng)建非唯一索引：CREATEINDEXidx_student_classON學(xué)生(班級號);4.8創(chuàng)建非唯一索引：CREATEINDEXidx_teacher_titleON教師(職稱);4.9創(chuàng)建非唯一索引：CREATEINDEXidx_course_creditON課程(學(xué)分);4.10創(chuàng)建非唯一索引：CREATEINDEXidx_class_nameON班級(班級名);五、數(shù)據(jù)庫視圖設(shè)計(jì)5.1視圖設(shè)計(jì)：CREATEVIEWv_student_classASSELECT學(xué)生.姓名,學(xué)生.班級號,班級.班級名FROM學(xué)生,班級WHERE學(xué)生.班級號=班級.班級號;5.2視圖設(shè)計(jì)：CREATEVIEWv_teacher_collegeASSELECT教師.姓名,教師.職稱,學(xué)院.學(xué)院名FROM教師,學(xué)院WHERE教師.學(xué)院號=學(xué)院.學(xué)院號;5.3視圖設(shè)計(jì)：CREATEVIEWv_course_teacherASSELECT課程.課程號,課程.課程名,課程.學(xué)分,教師.姓名FROM課程,教師WHERE課程.教師號=教師.教師號;5.4視圖設(shè)計(jì)：CREATEVIEWv_student_scoreASSELECT學(xué)生.姓名,課程.課程名,成績.成績,CASE成績WHEN成績>90THEN'優(yōu)秀'WHEN成績>=60THEN'及格'ELSE'不及格'ENDAS成績等級FROM學(xué)生,課程,成績WHERE學(xué)生.學(xué)號=成績.學(xué)號AND課程.課程號=成績.課程號;5.5視圖設(shè)計(jì)：CREATEVIEWv_teacher_courseASSELECT教師.姓名,教師.職稱,課程.課程名,課程.學(xué)分FROM教師,課程WHERE教師.教師號=課程.教師號;5.6視圖設(shè)計(jì)：CREATEVIEWv_class_infoASSELECT班級.班級號,班級.班級名,COUNT(學(xué)生.學(xué)號)AS學(xué)生數(shù)量FROM班級,學(xué)生WHERE班級.班級號=學(xué)生.班級號GROUPBY班級.班級號;5.7視圖設(shè)計(jì)：CREATEVIEWv_student_class_avg_scoreASSELECT學(xué)生.姓名,班級.班級名,AVG(成績)AS平均成績FROM學(xué)生,成績,班級WHERE學(xué)生.學(xué)號=成績.學(xué)號AND學(xué)生.班級號=班級.班級號GROUPBY學(xué)生.姓名,班級.班級名;5.8視圖設(shè)計(jì)：CREATEVIEWv_course_credit_teacherASSELECT課程.課程號,課程.課程名,課程.學(xué)分,教師.姓名,教師.職稱FROM課程,教師WHERE課程.教師號=教師.教師號;5.9視圖設(shè)計(jì)：CREATEVIEWv_student_class_creationASSELECT學(xué)生.姓名,班級.班級名,班級.成立時間FROM學(xué)生,班級WHERE學(xué)生.班級號=班級.班級號;5.10視圖設(shè)計(jì)：CREATEVIEWv_teacher_class_creationASSELECT教師.姓名,教師.職稱,班級.班級名,班級.成立時間FROM教師,班級WHERE教師.教師號=班級.教師號;六、數(shù)據(jù)庫觸發(fā)器設(shè)計(jì)6.1觸發(fā)器設(shè)計(jì)：CREATETRIGGERtrig_student_age_before_insertBEFOREINSERTON學(xué)生FOREACHROWBEGINIFNEW.年齡<18THENSIGNALSQLSTATE'45000'SETMESSAGE_TEXT='年齡必須大于18歲';ENDIF;END;6.2觸發(fā)器設(shè)計(jì)：CREATETRIGGERtrig_course_credit_before_insertBEFOREINSERTON課程FOREACHROWBEGINIFNEW.學(xué)分<=0THENSIGNALSQLSTATE'45000'SETMESSAGE_TEXT='學(xué)分必須大于0';ENDIF;END;6.3觸發(fā)器設(shè)計(jì)：CREATETRIGGERtrig_score_before_insertBEFOREINSERTON成績FOREACHROWBEGINIFNEW.成績<0ORNEW.成績>100THENSIGNALSQLSTATE'45000'SETMESSAGE_TEXT='成績必須在0到100之間';ENDIF;END;6.4觸發(fā)器設(shè)計(jì)：CREATETRIGGERtrig_teacher_before_deleteBEFOREDELETEON教師FOREACHROWBEGINIFEXISTS(SELECT1FROM學(xué)生WHERE學(xué)生.教師號=OLD.教師號)THENSIGNALSQLSTATE'45000'SETMESSAGE_TEXT='存在學(xué)生記錄依賴于該教師，不允許刪除';ENDIF;END;6.5觸發(fā)器設(shè)計(jì)：CREATETRIGGERtrig_class_before_updateBEFOREUPDATEON班級FOREACHROWBEGINIFEXISTS(SELECT1FROM學(xué)生WHERE學(xué)生.班級號=OLD.班級號)THENSIGNALSQLSTATE'45000'SETMESSAGE_TEXT='班級號已存在，不允許更新';ENDIF;END;6.6觸發(fā)器設(shè)