mysql數(shù)據(jù)庫考試試題及答案2025年一、單項選擇題（每題2分，共20分）1.以下關(guān)于MySQL數(shù)據(jù)類型的描述中，錯誤的是（）。A.TINYINT類型的無符號范圍是0-255B.VARCHAR(255)最多存儲255個字符（假設(shè)字符集為UTF-8）C.DATETIME類型可以存儲的時間范圍是1000-01-0100:00:00到9999-12-3123:59:59D.DECIMAL(5,2)表示數(shù)值總長度5位，其中小數(shù)部分2位，整數(shù)部分最多3位2.某電商數(shù)據(jù)庫中存在訂單表（order），包含字段：order_id（主鍵，自增）、user_id（用戶ID）、create_time（下單時間）、total_amount（訂單金額）。若需統(tǒng)計2024年每個用戶的訂單總金額，且只顯示總金額超過1000元的用戶，正確的SQL語句是（）。A.SELECTuser_id,SUM(total_amount)FROMorderWHEREcreate_timeBETWEEN'2024-01-01'AND'2024-12-31'GROUPBYuser_idHAVINGSUM(total_amount)>1000;B.SELECTuser_id,SUM(total_amount)FROMorderWHEREcreate_timeLIKE'2024%'GROUPBYuser_idHAVINGSUM(total_amount)>1000;C.SELECTuser_id,SUM(total_amount)FROMorderWHEREYEAR(create_time)=2024GROUPBYuser_idWHERESUM(total_amount)>1000;D.SELECTuser_id,SUM(total_amount)FROMorderWHEREcreate_time>='2024-01-01'GROUPBYuser_idHAVINGSUM(total_amount)>1000;3.關(guān)于MySQL索引，以下說法正確的是（）。A.主鍵索引一定是聚集索引（ClusteredIndex）B.唯一索引可以包含NULL值C.覆蓋索引（CoveringIndex）不需要回表查詢D.全文索引（FULLTEXT）適用于所有存儲引擎4.若事務(wù)T1對數(shù)據(jù)A加了共享鎖（S鎖），則事務(wù)T2（）。A.可以加共享鎖，但不能加排他鎖（X鎖）B.可以加排他鎖，但不能加共享鎖C.既不能加共享鎖，也不能加排他鎖D.可以同時加共享鎖和排他鎖5.某MySQL實例的f配置中，設(shè)置`innodb_flush_log_at_trx_commit=2`，其含義是（）。A.每次事務(wù)提交時，日志立即寫入磁盤B.每秒將日志寫入磁盤一次，事務(wù)提交時僅寫入緩存C.事務(wù)提交時，日志寫入緩存，由操作系統(tǒng)決定何時刷盤D.事務(wù)提交時，日志寫入緩存，每秒刷盤一次6.以下關(guān)于MySQL存儲引擎的描述，錯誤的是（）。A.InnoDB支持行級鎖和外鍵約束B.MyISAM不支持事務(wù)，但查詢速度通常較快C.Memory引擎的數(shù)據(jù)存儲在內(nèi)存中，重啟后數(shù)據(jù)丟失D.Archive引擎適用于需要頻繁更新的大數(shù)據(jù)量表7.若需限制用戶只能查詢數(shù)據(jù)庫“mall”中的“user”表，且只能通過IP地址00連接，正確的授權(quán)命令是（）。A.GRANTSELECTONmall.userTO'user1'@'00';B.GRANTSELECTONmall.TO'user1'@'00';C.GRANTSELECT(user)ONmallTO'user1'@'00';D.GRANTALLONmall.userTO'user1'@'%';8.以下哪種情況不會導(dǎo)致索引失效？（）A.對索引列使用函數(shù)（如WHEREYEAR(create_time)=2024）B.索引列參與計算（如WHEREprice+100>200）C.聯(lián)合索引中，查詢條件跳過了中間列（如聯(lián)合索引(a,b,c)，查詢條件為WHEREa=1ANDc=3）D.使用LIKE查詢時，模式以通配符開頭（如WHEREnameLIKE'%張%'）9.關(guān)于MySQL主從復(fù)制（Replication），以下說法錯誤的是（）。A.主庫需要開啟二進(jìn)制日志（binlog）B.從庫通過IO線程讀取主庫的binlog并寫入中繼日志（relaylog）C.主從復(fù)制支持實時同步，無延遲D.復(fù)制過程分為異步復(fù)制、半同步復(fù)制和全同步復(fù)制10.若需備份數(shù)據(jù)庫“erp”的結(jié)構(gòu)（不包含數(shù)據(jù)），正確的mysqldump命令是（）。A.mysqldump-uroot-p--no-dataerp>erp_struct.sqlB.mysqldump-uroot-p--data-onlyerp>erp_struct.sqlC.mysqldump-uroot-perp>erp_struct.sqlD.mysqldump-uroot-p--skip-dataerp>erp_struct.sql二、填空題（每題2分，共20分）1.MySQL中，用于查看當(dāng)前會話事務(wù)隔離級別的命令是__________。2.InnoDB存儲引擎的默認(rèn)事務(wù)隔離級別是__________。3.若要將表“product”的字符集修改為utf8mb4并設(shè)置校對規(guī)則為utf8mb4_general_ci，ALTER語句為：ALTERTABLEproduct__________;4.事務(wù)的四大特性（ACID）是原子性、一致性、__________和持久性。5.聯(lián)合索引的最左匹配原則指的是查詢條件需包含索引的__________部分才能有效利用索引。6.MySQL中，慢查詢?nèi)罩镜哪J(rèn)文件名為__________，用于記錄執(zhí)行時間超過__________秒的查詢。7.存儲過程中，使用__________語句可以定義變量，使用__________語句可以接收查詢結(jié)果到變量。8.InnoDB的行鎖分為共享鎖（S鎖）和__________，其中__________鎖會阻塞其他事務(wù)的寫操作。9.主從復(fù)制中，從庫的SQL線程負(fù)責(zé)讀取__________并執(zhí)行其中的SQL語句。10.若要對表“sales”按月份分區(qū)（2024年1-12月），分區(qū)類型應(yīng)選擇__________，分區(qū)鍵為__________。三、簡答題（每題8分，共40分）1.簡述索引的優(yōu)缺點及設(shè)計原則。2.說明事務(wù)隔離級別“讀未提交（ReadUncommitted）”“讀已提交（ReadCommitted）”“可重復(fù)讀（RepeatableRead）”“串行化（Serializable）”的區(qū)別，并舉例說明“可重復(fù)讀”解決了哪些問題。3.對比InnoDB和MyISAM存儲引擎在鎖機制、事務(wù)支持、崩潰恢復(fù)、索引類型上的差異。4.當(dāng)MySQL出現(xiàn)慢查詢時，應(yīng)如何定位和優(yōu)化？請列出至少5個步驟。5.簡述MySQL主從復(fù)制的原理，并說明如何配置半同步復(fù)制（Semi-SyncReplication）。四、應(yīng)用題（每題10分，共30分）1.設(shè)計一個電商數(shù)據(jù)庫的“訂單表”（order）和“訂單明細(xì)表”（order_detail），要求：-order表包含字段：訂單ID（主鍵，自增）、用戶ID（非空）、下單時間（默認(rèn)當(dāng)前時間）、訂單狀態(tài)（枚舉值：待支付、已支付、已發(fā)貨、已完成）、總金額（保留2位小數(shù)）。-order_detail表包含字段：明細(xì)ID（主鍵，自增）、訂單ID（外鍵，關(guān)聯(lián)order表的訂單ID，級聯(lián)刪除）、商品ID（非空）、購買數(shù)量（正整數(shù)）、單價（保留2位小數(shù)）。請寫出創(chuàng)建這兩張表的SQL語句（需包含必要的約束）。2.基于第1題的表結(jié)構(gòu)，編寫SQL查詢：（1）查詢2024年10月所有“已支付”狀態(tài)的訂單，要求結(jié)果包含訂單ID、用戶ID、下單時間、總金額。（2）查詢每個訂單的明細(xì)總數(shù)量和明細(xì)總金額（明細(xì)總金額=購買數(shù)量×單價），結(jié)果包含訂單ID、明細(xì)總數(shù)量、明細(xì)總金額。3.某用戶表（user）包含字段：user_id（主鍵）、username（用戶名）、register_time（注冊時間）、last_login_time（最后登錄時間）。請編寫SQL語句，查詢2024年注冊且2024年最后一次登錄時間在12月的用戶，要求結(jié)果按注冊時間升序排列，且只顯示前100條記錄。五、綜合題（每題15分，共30分）1.某社交平臺數(shù)據(jù)庫的“動態(tài)表”（post）包含字段：post_id（主鍵）、user_id（用戶ID）、content（內(nèi)容）、create_time（發(fā)布時間）、like_count（點贊數(shù)）、comment_count（評論數(shù)）。當(dāng)前查詢“近7天發(fā)布的、點贊數(shù)超過1000的動態(tài)，按點贊數(shù)降序排列，取前50條”的SQL語句為：`SELECTpost_id,user_id,content,create_timeFROMpostWHEREcreate_time>=NOW()-INTERVAL7DAYANDlike_count>1000ORDERBYlike_countDESCLIMIT50;`但執(zhí)行效率較低。請分析可能的原因，并提出優(yōu)化方案（包括索引優(yōu)化、查詢語句調(diào)整等）。2.某企業(yè)數(shù)據(jù)庫出現(xiàn)數(shù)據(jù)誤刪除事故（誤刪了“employee”表中2024年1月1日之后入職的員工記錄），需要恢復(fù)數(shù)據(jù)。已知該數(shù)據(jù)庫開啟了二進(jìn)制日志（binlog），且最后一次全量備份是2024年12月1日0點。請詳細(xì)描述恢復(fù)數(shù)據(jù)的步驟，包括備份文件準(zhǔn)備、binlog分析及數(shù)據(jù)恢復(fù)操作（需寫出關(guān)鍵命令）。---答案一、單項選擇題1.B（VARCHAR(255)存儲的字符數(shù)與字符集無關(guān)，255是字符長度限制；UTF-8下每個字符占3字節(jié)，但VARCHAR的最大字節(jié)數(shù)受限于行長度，如InnoDB行最大65535字節(jié)）2.A（B選項LIKE'2024%'無法正確匹配日期范圍；C選項HAVING前不能用WHERE；D選項缺少結(jié)束時間）3.C（A錯誤，InnoDB主鍵是聚集索引，但MyISAM主鍵是輔助索引；B錯誤，唯一索引不允許重復(fù)值，但允許一個NULL；D錯誤，F(xiàn)ULLTEXT僅InnoDB和MyISAM支持）4.A（共享鎖兼容共享鎖，不兼容排他鎖）5.D（innodb_flush_log_at_trx_commit=1時每次提交刷盤；=2時提交寫緩存，每秒刷盤；=0時每秒寫緩存和刷盤）6.D（Archive引擎僅支持INSERT和SELECT，適用于歸檔歷史數(shù)據(jù)，不支持更新）7.A（授權(quán)格式：GRANT權(quán)限ON數(shù)據(jù)庫.表TO用戶@主機；B是授權(quán)庫下所有表，C語法錯誤，D主機為任意IP）8.C（聯(lián)合索引跳過中間列會導(dǎo)致部分索引失效，但前導(dǎo)列有效時仍可能使用索引的前半部分；其他選項均明確導(dǎo)致索引失效）9.C（主從復(fù)制存在延遲，半同步復(fù)制可減少延遲但無法完全消除）10.A（--no-data或--skip-data表示只備份結(jié)構(gòu)；--data-only只備份數(shù)據(jù)）二、填空題1.SELECT@@transaction_isolation;（或SELECT@@tx_isolation;但8.0后推薦前者）2.可重復(fù)讀（RepeatableRead）3.CONVERTTOCHARACTERSETutf8mb4COLLATEutf8mb4_general_ci4.隔離性（Isolation）5.前導(dǎo)（或最左）6.主機名-slow.log；long_query_time（默認(rèn)10秒）7.DECLARE；SELECT...INTO8.排他鎖（X鎖）；X（排他）9.中繼日志（relaylog）10.RANGE；MONTH(create_time)（或具體日期字段，如order_time）三、簡答題1.索引的優(yōu)點：加速查詢，減少全表掃描；缺點：增加寫操作（INSERT/UPDATE/DELETE）的開銷，占用額外存儲空間。設(shè)計原則：①為高頻查詢的列（如WHERE、JOIN、ORDERBY中的列）創(chuàng)建索引；②避免為低基數(shù)列（如性別）創(chuàng)建索引（區(qū)分度低）；③聯(lián)合索引按查詢頻率和區(qū)分度排序（高頻+高區(qū)分度在前）；④避免冗余索引（如已有(a,b)索引，無需單獨創(chuàng)建(a)索引）；⑤限制索引數(shù)量（單表索引建議不超過5個）。2.隔離級別區(qū)別：-讀未提交（RU）：允許事務(wù)讀取其他事務(wù)未提交的修改（臟讀）；-讀已提交（RC）：只讀取已提交的數(shù)據(jù)（解決臟讀，但可能不可重復(fù)讀）；-可重復(fù)讀（RR）：同一事務(wù)內(nèi)多次讀取結(jié)果一致（解決不可重復(fù)讀，InnoDB通過MVCC實現(xiàn)）；-串行化（S）：事務(wù)串行執(zhí)行（解決幻讀，但并發(fā)度低）。“可重復(fù)讀”解決的問題：例如用戶A在事務(wù)中第一次查詢賬戶余額為1000元，此時用戶B轉(zhuǎn)賬500元并提交，用戶A再次查詢時，在RR隔離級別下仍會看到1000元（保證事務(wù)內(nèi)數(shù)據(jù)一致性）。3.差異對比：-鎖機制：InnoDB支持行級鎖和表級鎖；MyISAM僅支持表級鎖。-事務(wù)支持：InnoDB支持事務(wù)；MyISAM不支持。-崩潰恢復(fù)：InnoDB通過redo/undo日志自動恢復(fù)；MyISAM崩潰后可能損壞，需手動修復(fù)。-索引類型：InnoDB主鍵是聚集索引，輔助索引存儲主鍵值；MyISAM索引是輔助索引，存儲行物理地址。4.定位與優(yōu)化步驟：①開啟慢查詢?nèi)罩荆ㄔO(shè)置slow_query_log=ON，long_query_time=1），記錄慢查詢；②使用EXPLAIN分析慢查詢的執(zhí)行計劃，查看type（是否全表掃描）、key（是否使用索引）、rows（掃描行數(shù)）等；③檢查是否缺少有效索引（如WHERE條件列未索引、聯(lián)合索引順序不當(dāng)）；④優(yōu)化查詢語句（避免SELECT、減少子查詢、使用JOIN代替多次查詢）；⑤調(diào)整數(shù)據(jù)庫配置（如增加innodb_buffer_pool_size緩存池大小，減少磁盤IO）；⑥考慮分表或分區(qū)（如按時間范圍分區(qū)，減少單表數(shù)據(jù)量）。5.主從復(fù)制原理：主庫將寫操作記錄到binlog（二進(jìn)制日志），從庫通過IO線程讀取binlog并寫入中繼日志（relaylog），SQL線程讀取中繼日志并執(zhí)行其中的SQL，實現(xiàn)數(shù)據(jù)同步。半同步復(fù)制配置步驟：①主庫安裝semisync_master插件：INSTALLPLUGINrpl_semi_sync_masterSONAME'semisync_master.so';②主庫設(shè)置參數(shù)：rpl_semi_sync_master_enabled=1，rpl_semi_sync_master_timeout=1000（超時時間，毫秒）；③從庫安裝semisync_slave插件：INSTALLPLUGINrpl_semi_sync_slaveSONAME'semisync_slave.so';④從庫設(shè)置參數(shù)：rpl_semi_sync_slave_enabled=1；⑤重啟主從服務(wù)，驗證狀態(tài)（主庫SHOWSTATUSLIKE'Rpl_semi_sync_master_status'應(yīng)為ON）。四、應(yīng)用題1.創(chuàng)建表SQL：```sql--訂單表CREATETABLEorder(order_idINTUNSIGNEDAUTO_INCREMENTPRIMARYKEY,user_idINTUNSIGNEDNOTNULL,create_timeDATETIMEDEFAULTCURRENT_TIMESTAMP,order_statusENUM('待支付','已支付','已發(fā)貨','已完成')NOTNULL,total_amountDECIMAL(10,2)NOTNULL)ENGINE=InnoDBDEFAULTCHARSET=utf8mb4;--訂單明細(xì)表CREATETABLEorder_detail(detail_idINTUNSIGNEDAUTO_INCREMENTPRIMARYKEY,order_idINTUNSIGNEDNOTNULL,product_idINTUNSIGNEDNOTNULL,quantityINTUNSIGNEDNOTNULLCHECK(quantity>0),unit_priceDECIMAL(10,2)NOTNULL,FOREIGNKEY(order_id)REFERENCESorder(order_id)ONDELETECASCADE)ENGINE=InnoDBDEFAULTCHARSET=utf8mb4;```2.（1）查詢2024年10月已支付訂單：```sqlSELECTorder_id,user_id,create_time,total_amountFROMorderWHEREorder_status='已支付'ANDcreate_timeBETWEEN'2024-10-0100:00:00'AND'2024-10-3123:59:59';```（2）查詢訂單明細(xì)總數(shù)量和總金額：```sqlSELECTorder_id,SUM(quantity)AStotal_quantity,SUM(quantityunit_price)AStotal_detail_amountFROMorder_detailGROUPBYorder_id;```3.查詢2024年注冊且12月最后登錄的用戶：```sqlSELECTuser_id,username,register_time,last_login_timeFROMuserWHEREYEAR(register_time)=2024ANDYEAR(last_login_time)=2024ANDMONTH(last_login_time)=12ORDERBYregister_timeASCLIMIT100;```五、綜合題1.原因分析：-未對create_time和like_count創(chuàng)建索引，導(dǎo)致全表掃描；-WHERE條件包含兩個列（create_time和like_count），若沒有聯(lián)合索引，無法有效利用索引；-