2025年數(shù)據(jù)庫SQL操作題庫及答案一、基礎(chǔ)SQL操作1.現(xiàn)有某在線教育平臺(tái)數(shù)據(jù)庫，需創(chuàng)建以下三張表：學(xué)生表（student）：包含字段s_id（學(xué)生ID，主鍵，INT類型，自增）、s_name（姓名，VARCHAR(50)，非空）、s_gender（性別，ENUM('男','女','其他')）、s_birth（出生日期，DATE）、enroll_time（入學(xué)時(shí)間，DATETIME，默認(rèn)當(dāng)前時(shí)間）、is_graduated（是否畢業(yè)，TINYINT(1)，默認(rèn)0）。課程表（course）：包含字段c_id（課程ID，主鍵，CHAR(6)）、c_name（課程名，VARCHAR(100)，非空）、credit（學(xué)分，TINYINT，CHECK(creditBETWEEN1AND5)）、c_type（課程類型，VARCHAR(20)，如“必修”“選修”）。成績(jī)表（score）：包含字段sc_id（記錄ID，主鍵，INT，自增）、s_id（學(xué)生ID，外鍵，REFERENCESstudent(s_id)ONDELETECASCADE）、c_id（課程ID，外鍵，REFERENCEScourse(c_id)ONUPDATECASCADE）、score（分?jǐn)?shù)，DECIMAL(5,2)，CHECK(scoreBETWEEN0AND100)）、exam_date（考試日期，DATE）、exam_type（考試類型，VARCHAR(20)，如“期中”“期末”）。請(qǐng)寫出創(chuàng)建這三張表的SQL語句（要求包含所有約束）。答案：```sqlCREATETABLEstudent(s_idINTAUTO_INCREMENTPRIMARYKEY,s_nameVARCHAR(50)NOTNULL,s_genderENUM('男','女','其他'),s_birthDATE,enroll_timeDATETIMEDEFAULTCURRENT_TIMESTAMP,is_graduatedTINYINT(1)DEFAULT0);CREATETABLEcourse(c_idCHAR(6)PRIMARYKEY,c_nameVARCHAR(100)NOTNULL,creditTINYINTCHECK(creditBETWEEN1AND5),c_typeVARCHAR(20));CREATETABLEscore(sc_idINTAUTO_INCREMENTPRIMARYKEY,s_idINT,c_idCHAR(6),scoreDECIMAL(5,2)CHECK(scoreBETWEEN0AND100),exam_dateDATE,exam_typeVARCHAR(20),FOREIGNKEY(s_id)REFERENCESstudent(s_id)ONDELETECASCADE,FOREIGNKEY(c_id)REFERENCEScourse(c_id)ONUPDATECASCADE);```2.向student表插入以下數(shù)據(jù)（要求使用一條INSERT語句）：姓名“張小雨”，性別“女”，出生日期“2003-05-12”，入學(xué)時(shí)間使用默認(rèn)值；姓名“李陽”，性別“男”，出生日期“2002-08-20”，已畢業(yè)（is_graduated=1）。答案：```sqlINSERTINTOstudent(s_name,s_gender,s_birth,is_graduated)VALUES('張小雨','女','2003-05-12',0),('李陽','男','2002-08-20',1);```3.將course表中所有學(xué)分小于3的“選修”課程的學(xué)分統(tǒng)一增加0.5，要求僅修改未結(jié)課的課程（假設(shè)課程是否結(jié)課通過c_id是否以“XK”結(jié)尾判斷，如“XK001”為已結(jié)課）。答案：```sqlUPDATEcourseSETcredit=credit+0.5WHEREc_type='選修'ANDcredit<3ANDc_idNOTLIKE'XK%';```4.刪除student表中未畢業(yè)（is_graduated=0）且最近1年內(nèi)無考試記錄的學(xué)生（考試記錄關(guān)聯(lián)score表，考試日期exam_date在最近1年內(nèi)視為有記錄）。答案：```sqlDELETEFROMstudentWHEREis_graduated=0ANDs_idNOTIN(SELECTs_idFROMscoreWHEREexam_date>=DATE_SUB(CURRENT_DATE,INTERVAL1YEAR));```5.查詢student表中年齡在18-22歲之間（含邊界）的女性學(xué)生，結(jié)果按入學(xué)時(shí)間從早到晚排序，顯示姓名、性別、年齡（計(jì)算方式：當(dāng)前年份-出生年份，忽略具體月份）。答案：```sqlSELECTs_name,s_gender,YEAR(CURRENT_DATE)YEAR(s_birth)ASageFROMstudentWHEREs_gender='女'ANDYEAR(CURRENT_DATE)YEAR(s_birth)BETWEEN18AND22ORDERBYenroll_timeASC;```二、復(fù)雜查詢與多表關(guān)聯(lián)6.查詢每門課程的平均分、及格率（及格分?jǐn)?shù)≥60）及參考人數(shù)，結(jié)果包含課程名、平均分（保留2位小數(shù)）、及格率（百分比形式，保留1位小數(shù)）、參考人數(shù)，按平均分降序排列。答案：```sqlSELECTc.c_name,ROUND(AVG(sc.score),2)ASavg_score,CONCAT(ROUND(SUM(CASEWHENsc.score>=60THEN1ELSE0END)/COUNT()100,1),'%')ASpass_rate,COUNT()AStotal_studentsFROMcoursecJOINscorescONc.c_id=sc.c_idGROUPBYc.c_id,c.c_nameORDERBYavg_scoreDESC;```7.查詢所有選修了“數(shù)據(jù)庫原理”（課程名）且成績(jī)高于該課程平均分的學(xué)生姓名、成績(jī)及排名（使用DENSE_RANK，按成績(jī)降序）。答案：```sqlWITHcourse_avgAS(SELECTc_id,AVG(score)ASavg_scFROMscoreWHEREc_id=(SELECTc_idFROMcourseWHEREc_name='數(shù)據(jù)庫原理'))SELECTs.s_name,sc.score,DENSE_RANK()OVER(ORDERBYsc.scoreDESC)ASrnkFROMstudentsJOINscorescONs.s_id=sc.s_idJOINcourse_avgcaONsc.c_id=ca.c_idWHEREsc.c_id=(SELECTc_idFROMcourseWHEREc_name='數(shù)據(jù)庫原理')ANDsc.score>ca.avg_sc;```8.查詢每個(gè)學(xué)生的總學(xué)分（僅計(jì)算已及格課程的學(xué)分），并按總學(xué)分降序排列，顯示姓名、總學(xué)分（保留1位小數(shù)）。答案：```sqlSELECTs.s_name,ROUND(SUM(c.credit),1)AStotal_creditFROMstudentsJOINscorescONs.s_id=sc.s_idJOINcoursecONsc.c_id=c.c_idWHEREsc.score>=60GROUPBYs.s_id,s.s_nameORDERBYtotal_creditDESC;```9.查詢2024年入學(xué)且未畢業(yè)的學(xué)生中，至少選修了3門必修課的學(xué)生姓名及選修必修課程數(shù)量。答案：```sqlSELECTs.s_name,COUNT(DISTINCTsc.c_id)ASrequired_course_cntFROMstudentsJOINscorescONs.s_id=sc.s_idJOINcoursecONsc.c_id=c.c_idWHEREYEAR(s.enroll_time)=2024ANDs.is_graduated=0ANDc.c_type='必修'GROUPBYs.s_id,s.s_nameHAVINGCOUNT(DISTINCTsc.c_id)>=3;```10.使用窗口函數(shù)查詢每個(gè)學(xué)生的每門課程成績(jī)與該課程平均分的差值，結(jié)果顯示姓名、課程名、成績(jī)、差值（保留2位小數(shù)）。答案：```sqlSELECTs.s_name,c.c_name,sc.score,ROUND(sc.scoreAVG(sc.score)OVER(PARTITIONBYsc.c_id),2)ASdiff_from_avgFROMstudentsJOINscorescONs.s_id=sc.s_idJOINcoursecONsc.c_id=c.c_id;```三、表結(jié)構(gòu)設(shè)計(jì)與優(yōu)化11.某電商平臺(tái)需設(shè)計(jì)“訂單表”（order），要求記錄訂單號(hào)、用戶ID、下單時(shí)間、總金額、支付狀態(tài)（待支付、已支付、已取消）、物流狀態(tài)（未發(fā)貨、已發(fā)貨、已簽收）。請(qǐng)結(jié)合第三范式，說明是否需要拆分表，并給出最終表結(jié)構(gòu)設(shè)計(jì)（包含約束）。答案：根據(jù)第三范式，支付狀態(tài)和物流狀態(tài)屬于狀態(tài)枚舉值，若直接存儲(chǔ)文本會(huì)導(dǎo)致數(shù)據(jù)冗余（如多個(gè)訂單的支付狀態(tài)均為“已支付”），因此應(yīng)拆分為字典表。最終設(shè)計(jì)如下：支付狀態(tài)表（pay_status）：ps_id（主鍵，TINYINT）、ps_name（狀態(tài)名，VARCHAR(20)，唯一）。物流狀態(tài)表（logistics_status）：ls_id（主鍵，TINYINT）、ls_name（狀態(tài)名，VARCHAR(20)，唯一）。訂單表（order）：order_id（主鍵，VARCHAR(32)）、user_id（用戶ID，INT，外鍵REFERENCESuser(user_id)）、order_time（下單時(shí)間，DATETIME）、total_amount（總金額，DECIMAL(10,2)）、ps_id（支付狀態(tài)ID，外鍵REFERENCESpay_status(ps_id)）、ls_id（物流狀態(tài)ID，外鍵REFERENCESlogistics_status(ls_id)）。表創(chuàng)建語句示例：```sqlCREATETABLEpay_status(ps_idTINYINTPRIMARYKEY,ps_nameVARCHAR(20)UNIQUENOTNULL);CREATETABLElogistics_status(ls_idTINYINTPRIMARYKEY,ls_nameVARCHAR(20)UNIQUENOTNULL);CREATETABLE`order`(order_idVARCHAR(32)PRIMARYKEY,user_idINTNOTNULL,order_timeDATETIMENOTNULL,total_amountDECIMAL(10,2)NOTNULL,ps_idTINYINTNOTNULL,ls_idTINYINTNOTNULL,FOREIGNKEY(user_id)REFERENCESuser(user_id),FOREIGNKEY(ps_id)REFERENCESpay_status(ps_id),FOREIGNKEY(ls_id)REFERENCESlogistics_status(ls_id));```四、事務(wù)與索引12.模擬用戶A向用戶B轉(zhuǎn)賬500元的事務(wù)操作，要求：用戶表（user）包含user_id（主鍵）、balance（余額，DECIMAL(10,2)）；需檢查轉(zhuǎn)出用戶余額是否足夠；若余額不足，回滾并返回錯(cuò)誤信息；若余額足夠，扣除轉(zhuǎn)出用戶余額，增加轉(zhuǎn)入用戶余額，提交事務(wù)。答案：```sqlDELIMITER$$CREATEPROCEDUREtransfer(INfrom_userINT,INto_userINT,INamountDECIMAL(10,2))BEGINDECLAREEXITHANDLERFORSQLEXCEPTIONBEGINROLLBACK;SELECT'交易失?。喊l(fā)生異常'ASresult;END;STARTTRANSACTION;-檢查轉(zhuǎn)出用戶余額IF(SELECTbalanceFROMuserWHEREuser_id=from_user)<amountTHENROLLBACK;SELECT'交易失敗：余額不足'ASresult;RETURN;ENDIF;-扣除轉(zhuǎn)出用戶余額UPDATEuserSETbalance=balanceamountWHEREuser_id=from_user;-增加轉(zhuǎn)入用戶余額UPDATEuserSETbalance=balance+amountWHEREuser_id=to_user;COMMIT;SELECT'交易成功'ASresult;END$$DELIMITER;```13.某電商訂單表（order）有1000萬條數(shù)據(jù)，常見查詢條件為：根據(jù)user_id查詢近30天的訂單（order_time>=當(dāng)前時(shí)間-30天）；根據(jù)order_id精確查詢訂單詳情。請(qǐng)為該表設(shè)計(jì)索引，并說明原因。答案：為order_id字段創(chuàng)建主鍵索引（已默認(rèn)存在），因order_id是主鍵且需精確查詢，主鍵索引可快速定位單條記錄。為(user_id,order_time)創(chuàng)建復(fù)合索引。原因：查詢常按user_id篩選近30天訂單，復(fù)合索引可先按user_id過濾，再按order_time范圍查詢，減少掃描行數(shù)；索引順序符合最左匹配原則，user_id在前可快速定位用戶，order_time在后支持范圍查詢。五、高級(jí)應(yīng)用14.創(chuàng)建一個(gè)存儲(chǔ)過程，輸入學(xué)生ID和學(xué)期（如“2024-2025-1”），輸出該學(xué)生本學(xué)期所有課程的成績(jī)及GPA（GPA計(jì)算：Σ(課程學(xué)分×成績(jī)/100×4)/Σ課程學(xué)分，保留2位小數(shù)）。答案：```sqlDELIMITER$$CREATEPROCEDUREcalc_gpa(INstu_idINT,INsemesterVARCHAR(20))BEGINDECLAREtotal_creditDECIMAL(10,2);DECLAREtotal_gpa_pointsDECIMAL(10,2);-計(jì)算總學(xué)分和GPA積分SELECTSUM(c.credit),SUM(c