MYSQL期末復(fù)習(xí)題(答案)一、單項選擇題1.以下關(guān)于MySQL數(shù)據(jù)類型的描述，錯誤的是（）。A.VARCHAR(255)最多存儲255個字符（UTF-8編碼下）B.INT類型默認占用4字節(jié)，范圍為-2147483648到2147483647C.DATETIME類型可以存儲日期和時間，范圍為1000-01-0100:00:00到9999-12-3123:59:59D.TEXT類型用于存儲大文本數(shù)據(jù)，最多可存儲65535字節(jié)答案：D（TEXT類型最大存儲65535字節(jié)是TINYTEXT，TEXT最大為65535×4字節(jié)，即約64KB）2.若要限制表中“age”字段的值在18到60之間，應(yīng)使用的約束是（）。A.PRIMARYKEYB.UNIQUEC.CHECKD.FOREIGNKEY答案：C（CHECK約束用于自定義字段的取值范圍）3.關(guān)于索引的描述，正確的是（）。A.主鍵會自動創(chuàng)建唯一索引，但不會創(chuàng)建聚簇索引B.索引越多，查詢速度一定越快C.復(fù)合索引的順序不影響查詢效率D.全文索引適用于文本內(nèi)容的模糊搜索（如文章關(guān)鍵詞）答案：D（主鍵在InnoDB中自動創(chuàng)建聚簇索引；索引過多會增加寫操作開銷；復(fù)合索引的字段順序會影響查詢時的匹配效果）4.事務(wù)的隔離級別中，可能導(dǎo)致“臟讀”的是（）。A.可重復(fù)讀（REPEATABLEREAD）B.讀未提交（READUNCOMMITTED）C.讀已提交（READCOMMITTED）D.串行化（SERIALIZABLE）答案：B（讀未提交允許事務(wù)讀取其他事務(wù)未提交的數(shù)據(jù)，導(dǎo)致臟讀）5.以下SQL語句中，用于修改表結(jié)構(gòu)的是（）。A.INSERTINTOtableSETcol=valueB.UPDATEtableSETcol=valueWHEREid=1C.ALTERTABLEtableADDCOLUMNnew_colINTD.CREATETABLEnew_tableLIKEold_table答案：C（ALTERTABLE用于修改表結(jié)構(gòu)，ADDCOLUMN是添加字段的操作）6.關(guān)于存儲引擎的描述，錯誤的是（）。A.InnoDB支持事務(wù)和行級鎖B.MyISAM不支持外鍵，但查詢速度較快C.Memory引擎將數(shù)據(jù)存儲在內(nèi)存中，適合臨時數(shù)據(jù)D.InnoDB和MyISAM都支持全文索引答案：D（MyISAM支持全文索引，InnoDB在MySQL5.6及以上版本才開始支持全文索引）7.執(zhí)行“SELECTCOUNT()FROMtable”時，MyISAM引擎會直接返回預(yù)存的行數(shù)，而InnoDB需要遍歷數(shù)據(jù)，原因是（）。A.MyISAM的COUNT()優(yōu)化更好B.InnoDB支持事務(wù)，行數(shù)可能因未提交事務(wù)而變化C.MyISAM的表結(jié)構(gòu)更簡單D.InnoDB不存儲行數(shù)統(tǒng)計信息答案：B（InnoDB支持事務(wù)，未提交的事務(wù)可能影響當(dāng)前會話的行數(shù)統(tǒng)計，因此無法直接返回預(yù)存值）8.若要查詢“學(xué)生表”中姓“張”且名字為兩個字的學(xué)生，正確的WHERE條件是（）。A.nameLIKE'張%'B.nameLIKE'張_'C.nameLIKE'張??'（假設(shè)數(shù)據(jù)庫字符集為GBK）D.nameREGEXP'^張.$'答案：B（下劃線_匹配單個字符，因此'張_'匹配姓張且名字為兩個字的情況；正則表達式'^張.$'也可實現(xiàn)，但B更直接）9.以下關(guān)于視圖的描述，錯誤的是（）。A.視圖是虛擬表，不存儲實際數(shù)據(jù)B.對視圖的DML操作可能受到底層表約束的限制C.視圖可以簡化復(fù)雜查詢D.視圖的索引會自動同步到底層表答案：D（視圖本身不存儲數(shù)據(jù)，因此無法創(chuàng)建索引，索引只能創(chuàng)建在底層表上）10.備份MySQL數(shù)據(jù)庫時，若需要熱備份（不鎖表），應(yīng)選擇的工具是（）。A.mysqldump（默認鎖表）B.mysqlhotcopy（僅支持MyISAM）C.PerconaXtraBackup（支持InnoDB熱備份）D.直接復(fù)制數(shù)據(jù)文件（需要停服務(wù)）答案：C（PerconaXtraBackup是專門用于InnoDB熱備份的工具，支持在線備份）二、填空題1.MySQL中，用于臨時保存用戶會話變量的關(guān)鍵字是__________（如設(shè)置會話字符集）。答案：SETSESSION2.若要將“成績表”中“score”字段的默認值設(shè)置為60，應(yīng)使用的ALTER語句是：ALTERTABLE成績表__________scoreSETDEFAULT60。答案：ALTERCOLUMN3.事務(wù)的四個特性（ACID）是原子性、__________、隔離性、持久性。答案：一致性4.InnoDB引擎中，行級鎖的實現(xiàn)依賴于__________（如索引字段），若查詢未使用索引，會升級為表級鎖。答案：索引5.復(fù)合索引（col1,col2,col3）能加速的查詢條件包括：col1=值、__________、col1=值A(chǔ)NDcol2=值A(chǔ)NDcol3>值等。答案：col1=值A(chǔ)NDcol2=值（需滿足最左前綴原則）6.若要查詢“訂單表”中每個客戶的訂單總金額，應(yīng)使用__________函數(shù)和__________子句（如GROUPBYcustomer_id）。答案：SUM；GROUPBY7.存儲過程中，用于條件判斷的語句是__________，用于循環(huán)的語句有WHILE和REPEAT。答案：IF...THEN...ELSE8.MySQL中，二進制日志（binlog）的主要作用是__________（如數(shù)據(jù)恢復(fù)、主從復(fù)制）。答案：記錄所有修改數(shù)據(jù)的SQL語句9.若要限制用戶“user1”只能從本地主機（localhost）連接數(shù)據(jù)庫，應(yīng)在GRANT語句中指定主機名為__________。答案：localhost10.執(zhí)行“EXPLAINSELECTFROMtableWHEREcol=1”時，輸出中的“type”字段若為“__________”，表示通過索引直接找到匹配行，是最優(yōu)的查詢類型。答案：const三、簡答題1.簡述主鍵（PRIMARYKEY）與唯一約束（UNIQUE）的區(qū)別。答案：-主鍵約束列不允許NULL值，唯一約束允許一個NULL值（取決于存儲引擎，InnoDB允許一個NULL，MyISAM允許多個）；-一個表只能有一個主鍵，但可以有多個唯一約束；-主鍵自動創(chuàng)建聚簇索引（InnoDB），唯一約束創(chuàng)建輔助索引；-主鍵通常用于標識記錄的唯一性（如ID），唯一約束用于其他需要唯一標識的字段（如郵箱）。2.說明LEFTJOIN、INNERJOIN、FULLOUTERJOIN的區(qū)別，并舉例說明。答案：-INNERJOIN（內(nèi)連接）：返回兩個表中滿足連接條件的交集記錄。例如，學(xué)生表和成績表內(nèi)連接，僅返回有成績的學(xué)生。-LEFTJOIN（左連接）：返回左表所有記錄，右表無匹配時用NULL填充。例如，學(xué)生表左連接成績表，返回所有學(xué)生，無成績的學(xué)提供績字段為NULL。-FULLOUTERJOIN（全外連接）：返回左右表所有記錄，無匹配時用NULL填充。MySQL不直接支持FULLOUTERJOIN，可通過UNIONLEFTJOIN和RIGHTJOIN實現(xiàn)。3.什么是索引失效？列舉至少3種導(dǎo)致索引失效的場景。答案：索引失效指查詢時無法使用索引，導(dǎo)致全表掃描。常見場景：-查詢條件使用函數(shù)或表達式（如WHEREYEAR(reg_time)=2023）；-字符串類型字段未加引號（如WHEREphone若phone是VARCHAR類型，可能觸發(fā)全表掃描）；-復(fù)合索引未遵循最左前綴原則（如索引(col1,col2)，查詢條件僅用col2）；-使用LIKE以通配符開頭（如WHEREnameLIKE'%張%'）；-字段類型隱式轉(zhuǎn)換（如INT類型字段與字符串比較，WHEREid='123'可能觸發(fā)全表掃描）。4.簡述事務(wù)的隔離級別及其對應(yīng)的問題（如臟讀、不可重復(fù)讀、幻讀）。答案：-讀未提交（READUNCOMMITTED）：允許讀取未提交的事務(wù)，會導(dǎo)致臟讀、不可重復(fù)讀、幻讀；-讀已提交（READCOMMITTED）：只讀取已提交的事務(wù)，解決臟讀，但可能出現(xiàn)不可重復(fù)讀（同一事務(wù)兩次查詢結(jié)果不同）；-可重復(fù)讀（REPEATABLEREAD，InnoDB默認）：確保同一事務(wù)內(nèi)多次讀取結(jié)果一致，解決不可重復(fù)讀，但可能出現(xiàn)幻讀（新插入的記錄）；-串行化（SERIALIZABLE）：事務(wù)串行執(zhí)行，解決所有問題，但并發(fā)性最低。5.如何優(yōu)化MySQL的查詢性能？（至少列出5點）答案：-合理設(shè)計表結(jié)構(gòu)，避免冗余字段（如范式化）；-為高頻查詢字段創(chuàng)建索引（如WHERE、JOIN、ORDERBY涉及的字段）；-避免全表掃描，優(yōu)化查詢條件（如使用索引、減少函數(shù)計算）；-分頁查詢時，若數(shù)據(jù)量過大，避免使用LIMIToffset,size（改用覆蓋索引或記錄上次查詢的最大ID）；-對大表進行分區(qū)（如按時間范圍分區(qū)）或分表（垂直/水平分表）；-調(diào)整MySQL配置參數(shù)（如innodb_buffer_pool_size，增加緩存命中率）；-分析慢查詢（使用EXPLAIN和慢查詢?nèi)罩荆?，定位低效語句；-避免SELECT，只查詢需要的字段，減少數(shù)據(jù)傳輸量。四、操作題（根據(jù)以下需求編寫SQL語句）背景：某公司需設(shè)計“員工-部門”數(shù)據(jù)庫，表結(jié)構(gòu)如下：-部門表（department）：dept_id（部門ID，主鍵，INT）、dept_name（部門名稱，VARCHAR(50)，唯一）、location（辦公地點，VARCHAR(100)）。-員工表（employee）：emp_id（員工ID，主鍵，INT）、emp_name（姓名，VARCHAR(20)）、salary（薪資，DECIMAL(10,2)）、hire_date（入職日期，DATE）、dept_id（所屬部門ID，外鍵，引用department.dept_id）。1.創(chuàng)建部門表，要求dept_name唯一且非空，location允許NULL。答案：CREATETABLEdepartment(dept_idINTPRIMARYKEY,dept_nameVARCHAR(50)NOTNULLUNIQUE,locationVARCHAR(100));2.創(chuàng)建員工表，要求salary≥2000（CHECK約束），hire_date默認值為當(dāng)前日期，外鍵dept_id在部門表被刪除時級聯(lián)更新（ONUPDATECASCADE）。答案：CREATETABLEemployee(emp_idINTPRIMARYKEY,emp_nameVARCHAR(20)NOTNULL,salaryDECIMAL(10,2)CHECK(salary>=2000),hire_dateDATEDEFAULTCURDATE(),dept_idINT,FOREIGNKEY(dept_id)REFERENCESdepartment(dept_id)ONUPDATECASCADE);3.向部門表插入一條記錄：dept_id=101，dept_name=“技術(shù)部”，location=“北京”。答案：INSERTINTOdepartment(dept_id,dept_name,location)VALUES(101,'技術(shù)部','北京');4.查詢薪資在10000到20000之間（含邊界），且入職時間在2020-01-01之后的員工姓名、薪資、部門名稱（需關(guān)聯(lián)部門表）。答案：SELECTe.emp_name,e.salary,d.dept_nameFROMemployeeeINNERJOINdepartmentdONe.dept_id=d.dept_idWHEREe.salaryBETWEEN10000AND20000ANDe.hire_date>='2020-01-01';5.統(tǒng)計每個部門的員工數(shù)量及平均薪資，結(jié)果按平均薪資降序排列（顯示部門名稱、員工數(shù)量、平均薪資）。答案：SELECTd.dept_name,COUNT(e.emp_id)ASemp_count,AVG(e.salary)ASavg_salaryFROMdepartmentdLEFTJOINemployeeeONd.dept_id=e.dept_idGROUPBYd.dept_id,d.dept_nameORDERBYavg_salaryDESC;6.將“技術(shù)部”（dept_name=“技術(shù)部”）的所有員工薪資增加5%。答案：UPDATEemployeeeINNERJOINdepartmentdONe.dept_id=d.dept_idSETe.salary=e.salary1.05WHEREd.dept_name='技術(shù)部';五、綜合題需求：設(shè)計一個小型“圖書管理系統(tǒng)”數(shù)據(jù)庫，包含圖書、讀者、借閱三個實體，要求：-圖書表：ISBN（主鍵，VARCHAR(13)）、書名（VARCHAR(100)）、作者（VARCHAR(50)）、出版日期（DATE）、庫存數(shù)量（INT，≥0）。-讀者表：reader_id（主鍵，INT）、姓名（VARCHAR(20)）、手機號（VARCHAR(11)，唯一）、注冊時間（DATETIME，默認當(dāng)前時間）。-借閱表：borrow_id（主鍵，INT自增）、ISBN（外鍵，引用圖書表ISBN）、reader_id（外鍵，引用讀者表reader_id）、借閱日期（DATE）、應(yīng)還日期（DATE）、實際歸還日期（DATE，允許NULL）。1.編寫創(chuàng)建圖書表、讀者表、借閱表的SQL語句（需包含約束）。答案：--圖書表CREATETABLEbook(ISBNVARCHAR(13)PRIMARYKEY,book_nameVARCHAR(100)NOTNULL,authorVARCHAR(50)NOTNULL,publish_dateDATE,stockINTCHECK(stock>=0));--讀者表CREATETABLEreader(reader_idINTPRIMARYKEY,nameVARCHAR(20)NOTNULL,phoneVARCHAR(11)UNIQUENOTNULL,reg_timeDATETIMEDEFAULTCURRENT_TIMESTAMP);--借閱表CREATETABLEborrow(borrow_idINTAUTO_INCREMENTPRIMARYKEY,ISBNVARCHAR(13),reader_idINT,borrow_dateDATENOTNULL,due_dateDATENOTNULL,return_dateDATE,FOREIGNKEY(ISBN)REFERENCESbook(ISBN)ONDELETERESTRICT,FOREIGNKEY(reader_id)REFERENCESreader(reader_id)ONDELETERESTRICT);2.編寫SQL語句，查詢2023年1月1日之后注冊的讀者，且其借閱過的圖書中包含“數(shù)據(jù)庫系統(tǒng)概念”（書名）的讀者姓名、手機號、借閱日期。答案：SELECT,r.phone,b.borrow_dateFROMreaderrINNERJOINborrowbrONr.reader_id=br.reader_idINNERJOINbookbkONbr.ISBN=bk.ISBNWHEREr.reg_time>='2023-01-01'ANDbk.book_name='數(shù)據(jù)庫系統(tǒng)概念';3.編寫存儲過程，實現(xiàn)圖書借閱功能（參數(shù)：ISBN、reader_id、借閱天數(shù)），要求：-檢查圖書庫存是否≥1，若不足則報錯；-檢查讀者是否存在；-插入借閱記錄（借閱日期為當(dāng)前日期，應(yīng)還日期=借閱日期+借閱天數(shù)）；-庫存數(shù)量減1。答案：DELIMITER$$CREATEPROCEDUREBorrowBook(INp_ISBNVARCHAR(13),INp_reader_idINT,INp_daysINT)BEGINDECLAREv_stockINT;DECLAREv_reader_existsINT;--檢查圖書庫存SELECTstockINTOv_stockFROMbookWHEREISBN=p_ISBN;IFv_stock<