MySQL 8.0數(shù)據(jù)庫管理與應(yīng)用 課件第3、4章 數(shù)據(jù)查詢、索引與視圖

第3章數(shù)據(jù)查詢3.1.1SELECT語句的基本組成SELECT輸出項(xiàng)[,輸出項(xiàng)...][FROM源表[WHERE搜索條件][GROUPBY{列名|表達(dá)式|列位置},...][HAVING搜索條件][ORDERBY{列名|表達(dá)式|列位置}[ASC|DESC],...][LIMIT{[偏移,]行數(shù)|行數(shù)OFFSET偏移}][INTOOUTFILE'文件名'|INTODUMPFILE'文件名'|INTO變量名[,變量名]]]3.1SELECT語句概述3.1.2常用查詢工具1.mysql命令行工具使用mysql命令行工具可以通過交互方式輸入SQL語句或從文件以批處理模式執(zhí)行SQL語句，從而完成在MySQL服務(wù)器上創(chuàng)建數(shù)據(jù)庫、查詢和操作數(shù)據(jù)的任務(wù)。2.MySQLWorkbenchMySQLWorkbench是一種可視化的數(shù)據(jù)庫設(shè)計(jì)和管理工具，可以用于管理MySQL服務(wù)器，也可以用于創(chuàng)建和管理數(shù)據(jù)庫和各種數(shù)據(jù)庫對(duì)象。MySQLWorkbench提供了一個(gè)內(nèi)置的SQL編輯器，可以用來在數(shù)據(jù)庫連接上執(zhí)行各種SQL語句，還可以將編寫的SQL語句或查詢結(jié)果保存到文件中。3.NavicatforMySQLNavicatforMySQL是由PremiumSoft公司出品的用于管理MySQL數(shù)據(jù)庫的桌面應(yīng)用程序，它為MySQL數(shù)據(jù)庫管理、開發(fā)和維護(hù)提供了一款直觀而強(qiáng)大的圖形界面。3.1SELECT語句概述3.2.1選擇所有列如果要查看表中所有列的信息，可在SELECT輸出項(xiàng)列表中使用星號(hào)（*）來選擇源表或視圖中的所有列。如果使用表或視圖名稱進(jìn)行限定，則星號(hào)將被解析為對(duì)指定表或視圖中的所有列的引用。當(dāng)使用星號(hào)選擇所有列時(shí)，查詢結(jié)果中的列順序與創(chuàng)建表或視圖時(shí)所指定的列順序相同。由于SELECT*將查找表中當(dāng)前存在的所有列，因此每次執(zhí)行SELECT*語句時(shí)，表結(jié)構(gòu)的更改（通過添加、刪除或重命名列）都會(huì)自動(dòng)反映出來。3.2.2選擇部分列如果要從表中選擇一部分列作為SELECT查詢的輸出項(xiàng)，則應(yīng)當(dāng)在輸出項(xiàng)列表中明確地列出每個(gè)列名，列名之間用逗號(hào)分隔。假如創(chuàng)建表時(shí)在表名或列名中使用了空格（不符合標(biāo)識(shí)符命名規(guī)則），則編寫SELECT語句時(shí)需要使用反引號(hào)“`”將表名或列名括起來，例如SELECT`studentid`,`studentname`FROM`studenttable`，否則會(huì)出現(xiàn)錯(cuò)誤信息。如果在FROM子句中指定了多個(gè)表，而這些表中又有同名的列，則在使用這些列時(shí)需要在列名前面冠以表名，以指明該列屬于哪個(gè)表。例如，在student和score表都有一個(gè)名稱為stuid的列。若要引用student表中的stuid列，應(yīng)在輸出項(xiàng)列表中寫上student.stuid；若要引用score表中的stuid列，則應(yīng)在輸出項(xiàng)列表中寫上score.stuid。3.2選擇查詢輸出項(xiàng)3.2.3設(shè)置列別名在SELECT語句中，可以使用AS子句來更改結(jié)果集中的列名或?yàn)榕缮蟹峙涿Q：列名[AS]別名3.2.4增加派生列在輸出項(xiàng)列表中，有些列不是指定為對(duì)列的簡(jiǎn)單引用，而是通過表中的其他列值計(jì)算而生成，例如表中有單價(jià)和數(shù)量列，可以在輸出項(xiàng)列表中增加一個(gè)用于計(jì)算總額的表達(dá)式，其值等于單價(jià)乘以數(shù)量，此即派生列。派生列本無名稱，通常應(yīng)使用AS子句為其指定別名。在派生列中，可以對(duì)數(shù)值列或常量使用算術(shù)運(yùn)算符或函數(shù)進(jìn)行計(jì)算，也可以進(jìn)行數(shù)據(jù)類型轉(zhuǎn)換。MySQL支持下列算術(shù)運(yùn)算符：+（加）、－（減）、*（乘）、/（除）、%（模，即取余數(shù)），可以使用算術(shù)運(yùn)算符可以對(duì)數(shù)值數(shù)據(jù)進(jìn)行加、減、乘、除運(yùn)算。此外，也可以使用日期函數(shù)或常規(guī)加或減算術(shù)運(yùn)算符對(duì)日期時(shí)間類型的列進(jìn)行算術(shù)運(yùn)算。3.2選擇查詢輸出項(xiàng)3.2.5消除重復(fù)行在輸出項(xiàng)列表中可以使用ALL和DISTINCT修飾符，用于指定是否應(yīng)返回重復(fù)的行。ALL是默認(rèn)值，指定應(yīng)返回所有匹配的行，包括重復(fù)行在內(nèi)。DISTINCT指定從結(jié)果集中刪除重復(fù)的行。DISTINCTROW是DISTINCT的同義詞。3.2.6沒有表列的SELECT語句在SELECT語句中，只要SELECT子句是必選的，其他子句一律都是可選項(xiàng)。如果SELECT選擇列表中僅包含常量、變量、函數(shù)和其他表達(dá)式，而不包含從任何表或視圖中選擇的列，則沒有必要使用FROM子句。3.2選擇查詢輸出項(xiàng)3.3.1單表查詢當(dāng)從單個(gè)數(shù)據(jù)庫表中查詢數(shù)據(jù)時(shí)，需要在FROM子句中指定要引用的該表的名稱，這種查詢稱為單表查詢。在單表查詢中，可以使用下列兩種方式引用來源表。首先使用USE語句將一個(gè)數(shù)據(jù)庫設(shè)置為當(dāng)前數(shù)據(jù)庫，然后在FROM子句中指定要引用的表名，此時(shí)這個(gè)表就是屬于當(dāng)前數(shù)據(jù)庫。不設(shè)置當(dāng)前數(shù)據(jù)庫，在FROM子句中指定要引用的表名時(shí)在其前面冠以所屬數(shù)據(jù)庫的名稱。如果要從多個(gè)表中查詢數(shù)據(jù)時(shí)，則必須在FROM子句中指定要引用的多個(gè)表名，這種查詢稱為多表查詢。在多表查詢中，需要使用某種方式對(duì)不同的表進(jìn)行連接，因此多表查詢也稱為連接查詢。連接方式分為全連接和JOIN連接，JOIN連接又分為內(nèi)連接、外連接和交叉連接。3.3選擇查詢數(shù)據(jù)源3.3.2全連接查詢?nèi)B接是指在FROM子句中使用逗號(hào)來分隔各個(gè)表，語法格式如下：FROM表名[[AS]別名][,表名[AS]別名],...其中表名指定要引用的表，AS關(guān)鍵字和別名都是可選的。在全連接查詢中，查詢結(jié)果中的列來自各個(gè)表，結(jié)果集是由各個(gè)表中的行進(jìn)行交叉所得到的各種可能的組合，也稱為笛卡兒乘積，結(jié)果集包含的行數(shù)是各個(gè)來源表行數(shù)的乘積。例如，A表包含200行，B表300行，則以全連接方式查詢A表和B表時(shí)，返回的結(jié)果集將包含200*300=60000行數(shù)據(jù)。因此，使用全連接方式時(shí)有可能生成行數(shù)非常大的結(jié)果集。在實(shí)際應(yīng)用中，使用全連接查詢時(shí)通?？梢允褂肳HERE子句來設(shè)置一個(gè)條件表達(dá)式，以控制結(jié)果集的大小。3.3選擇查詢數(shù)據(jù)源3.3.3內(nèi)連接查詢內(nèi)連接是指使用關(guān)鍵詞INNERJOIN來連接要查詢的各個(gè)表，語法格式如下：FROM表名[[AS]別名]INNERJOIN表名[[AS]別名]ON條件表達(dá)式其中表名用于指定要查詢的表，AS關(guān)鍵字和別名是可選的。由于內(nèi)連接是默認(rèn)的連接方式，關(guān)鍵字INNER也可以省略不寫。ON子句主要用于指定表之間的連接條件，其他與表連接無關(guān)的條件應(yīng)放在WHERE子句中。使用內(nèi)連接查詢時(shí)，將根據(jù)ON子句指定的連接條件合并兩個(gè)表并返回滿足條件的行。3.3選擇查詢數(shù)據(jù)源3.3.4外連接查詢外連接是指在FROM子句中使用OUTER關(guān)鍵字來連接要查詢的兩個(gè)表，具體分為以下幾種形式。左外連接（LEFTOUTERJOIN）：結(jié)果集中除了匹配行之外，還包括左表中存在但右表中不匹配的行，對(duì)于這樣的行，從右表中選擇的列設(shè)置為NULL。右外連接（RIGHTOUTERJOIN）：結(jié)果集中除了匹配行之外，還包括右表中存在但左表中不匹配的行，對(duì)于這樣的行，從左表中選擇的列設(shè)置為NULL。自然連接（NATURALJOIN）：其語義定義與設(shè)置ON條件的INNERJOIN相同，可以進(jìn)一步分為自然左連接（NATURALLEFTOUTERJOIN）和自然右連接（NATURALRIGHTOUTERJOIN）。外連接只能用于連接兩個(gè)表。當(dāng)使用各種形式的外連接時(shí)，可以省略O(shè)UTER關(guān)鍵字。3.3.5交叉連接查詢交叉連接是指在FROM子句中使用CROSSJOIN來連接各個(gè)表。如果不使用連接條件，則交叉連接就是對(duì)兩個(gè)表進(jìn)行笛卡兒乘積運(yùn)算，結(jié)果集是由第一個(gè)表的行與第二個(gè)表的行拼接后而形成，結(jié)果集的行數(shù)就等于兩個(gè)表的行數(shù)的乘積。在MySQL中，從語法上講CROSSJOIN與INNERJOIN等同，兩者可以互換。3.3選擇查詢數(shù)據(jù)源3.4.1WHERE子句語法格式在SELECT語句中WHERE子句是一個(gè)可選項(xiàng)，使用時(shí)應(yīng)當(dāng)將其放在FROM子句的后面，語法格式如下：WHERE搜索條件其中搜索條件定義要查詢的行應(yīng)滿足的條件，這個(gè)條件是用運(yùn)算符連接列名、常量、變量、函數(shù)等而得到的表達(dá)式，其取值為TRUE（1）、FALSE（0）或NULL。通過WHERE子句可以為查詢?cè)O(shè)置一系列搜索條件，只有那些滿足搜索條件的行才用于生成結(jié)果集，那些不滿足搜索條件的行則不會(huì)包含在結(jié)果集內(nèi)。3.4.2比較搜索在MySQL中，可以使用比較運(yùn)算符來比較兩個(gè)表達(dá)式的大小，語法格式如下：表達(dá)式1比較運(yùn)算符表達(dá)式2其中表達(dá)式1和表達(dá)式2是要進(jìn)行比較的表達(dá)式，其數(shù)據(jù)類型是除TEXT和BLOB之外的數(shù)據(jù)類型。MySQL支持的比較運(yùn)算符如下。

=：等于 <=：小于等于 >：大于 >=：大于等于

<=>：相等或都是NULL

<>：不等于

!=：不等于如果兩個(gè)表達(dá)式都不是NULL，則比較運(yùn)算符（<=>除外）返回布爾值TRUE或FALSE；如果兩個(gè)表達(dá)式中有一個(gè)為NULL，或兩個(gè)都是NULL，則返回UNKNOWN。3.4設(shè)置查詢條件3.4.3判定空值前面曾經(jīng)介紹了如何使用比較運(yùn)算符“<=>”來判定一個(gè)表達(dá)式是否為NULL值。實(shí)際上，MySQL還專門提供了一個(gè)用于判定空值的比較運(yùn)算符ISNULL，其語法格式如下。表達(dá)式IS[NOT]NULL如果表達(dá)式的值為NULL，則返回TRUE，否則返回FALSE。NOT為可選項(xiàng)，當(dāng)使用NOT時(shí)，將對(duì)運(yùn)算結(jié)果取反。3.4.4范圍搜索在WHERE子句中，可以使用BETWEEN運(yùn)算符來指定要搜索的范圍，也可以使用NOTBETWEEN來查找指定范圍之外的所有行，語法格式如下。表達(dá)式[NOT]BETWEEN起始值A(chǔ)ND終止值其中表達(dá)式給出要測(cè)試的表達(dá)式，其值可能位于指定的范圍內(nèi)。這個(gè)范圍的大小由起始值和終止值指定，起始值和終止值都是任何有效的表達(dá)式。表達(dá)式、起始值和終止值必須具有相同的數(shù)據(jù)類型。NOT指定對(duì)運(yùn)算結(jié)果取反。AND用作一個(gè)占位符，指示要測(cè)試的表達(dá)式的值應(yīng)該處于由起始值與終止值所指定的范圍內(nèi)。BETWEEN運(yùn)算符返回結(jié)果為布爾類型。如果測(cè)試表達(dá)式的值大于或等于起始值，并且小于或等于終止值，則BETWEEN返回TRUE，NOTBETWEEN返回FALSE。如果表達(dá)式的值小于起始值或大于終止值，則BETWEEN返回FALSE，NOTBETWEEN返回TRUE。如果要指定一個(gè)排他性范圍，則應(yīng)使用大于運(yùn)算符（>）和小于運(yùn)算符（<）。3.4設(shè)置查詢條件3.4.5列表搜索在WHERE子句中使用IN運(yùn)算符可以選擇與列表中的任意值匹配的行：表達(dá)式[NOT]IN(值,...)其中表達(dá)式給出要進(jìn)行的測(cè)試的表達(dá)式。如果表達(dá)式等于IN列表中的任何值，則返回TRUE，否則返回FALSE。如果使用NOT關(guān)鍵字，則對(duì)結(jié)果取反。3.4.6模式匹配在WHERE子句中可以使用LIKE運(yùn)算符來判定一個(gè)字符串是否與指定的模式相匹配：表達(dá)式[NOT]LIKE模式[ESCAPE'轉(zhuǎn)義字符']其中表達(dá)式指定要進(jìn)行測(cè)試的表達(dá)式，可以是CHAR、VARCHAR、TEXT或DATETIME等數(shù)據(jù)類型；模式給出要在匹配表達(dá)式中搜索并且可以包含有效通配符的字符串。常用的通配符：百分號(hào)“%”——包含零個(gè)或多個(gè)字符的任意字符串；下畫線“_”——表示任何單個(gè)字符。使用LIKE運(yùn)算符時(shí)，如果測(cè)試表達(dá)式與指定模式匹配，則返回TRUE，否則返回FALSE。如果使用NOT關(guān)鍵字，則對(duì)運(yùn)算結(jié)果取反。ESCAPE子句用于指定轉(zhuǎn)義字符，轉(zhuǎn)義字符必須是單個(gè)字符。如果要通過查詢來搜索通配符（例如“%”和“_”）本身，則可以通過使用ESCAPE子句來定義轉(zhuǎn)義字符。3.4設(shè)置查詢條件3.4.7組合搜索條件在SQL中所有邏輯運(yùn)算符的計(jì)算結(jié)果為TRUE、FALSE或NULL（UNKNOWN），它們?cè)贛ySQL中分別實(shí)現(xiàn)為1（TRUE）、0（FALSE）和NULL。MySQL支持以下邏輯運(yùn)算符。邏輯與運(yùn)算符（AND，&&）：如果所有操作數(shù)都不為零且不為NULL，則結(jié)果為1，如果一個(gè)或多個(gè)操作數(shù)為0，則結(jié)果為0；如果任一操作數(shù)為NULL，則返回NULL。邏輯或運(yùn)算符（OR，||）：當(dāng)兩個(gè)操作數(shù)都不是NULL時(shí)，如果任何操作數(shù)非零，則結(jié)果為1，否則為0。使用NULL操作數(shù)時(shí)，如果另一個(gè)操作數(shù)非零，則結(jié)果為1，否則為NULL。如果兩個(gè)操作數(shù)均為NULL，結(jié)果為NULL。邏輯非運(yùn)算符（NOT，!）：這是一個(gè)單目運(yùn)算符。如果操作數(shù)為0，則結(jié)果為1；如果操作數(shù)不為0，則結(jié)果為0，NOTNULL返回NULL。邏輯異或（XOR）：如果任一操作數(shù)為NULL，則返回NULL。對(duì)于非NULL操作數(shù)，如果奇數(shù)個(gè)操作數(shù)非零，則結(jié)果為1，否則返回0。3.4設(shè)置查詢條件3.4.8正則表達(dá)式搜索在WHERE子句中，可以使用REGEXP運(yùn)算符來判定字符串是否匹配指定模式。使用REGEXP運(yùn)算符時(shí)，應(yīng)遵循以下語法格式。表達(dá)式[NOT][REGEXP|RLIKE]模式3.4設(shè)置查詢條件3.5.1GROUPBY子句語法格式使用SELECT語句從數(shù)據(jù)庫中查詢數(shù)據(jù)時(shí)，可以使用GROUPBY子句對(duì)查詢結(jié)果進(jìn)行分組：GROUPBY{列名|表達(dá)式|列位置},...[WITHROLLUP]GROUPBY后面的分組依據(jù)可以是列名、表達(dá)式和列位置，為輸出選擇的列都可以在這里引用。列位置為整數(shù)，從1開始編號(hào)。在GROUPBY子句中允許使用WITHROLLUP修飾符，此時(shí)在查詢返回的結(jié)果集內(nèi)不僅包含由GROUPBY提供的行，而且還包含附加的匯總行。使用GROUPBY子句時(shí)，SELECT語句針對(duì)每個(gè)組返回一行，每個(gè)組中的行在指定的列中具有相同的值。3.5.2在分組查詢中應(yīng)用搜索條件使用HAVING子句對(duì)GROUPBY子句設(shè)置搜索條件：HAVING條件HAVING語法與WHERE語法類似，兩者的區(qū)別在于：WHERE搜索條件在進(jìn)行分組之前應(yīng)用，而HAVING搜索條件在進(jìn)行分組之后應(yīng)用，而且HAVING可以包含聚合函數(shù)，也可以引用輸出項(xiàng)列表中的任意項(xiàng)。3.5查詢結(jié)果分組3.5.3使用WITHROLLUP匯總數(shù)據(jù)當(dāng)使用GROUPBY子句對(duì)結(jié)果集進(jìn)行分組處理時(shí)，每個(gè)組在結(jié)果集中都會(huì)有一行，結(jié)果集被分成幾個(gè)組就會(huì)返回幾行。如果要對(duì)所有組進(jìn)行匯總計(jì)算，則可以使用WITHROLLUP子句來生成一些附加的匯總行。使用WITHROLLUP子句指定在結(jié)果集內(nèi)不僅包含由GROUPBY提供的行，還包含匯總行。按層次結(jié)構(gòu)順序，從組內(nèi)的最低級(jí)別到最高級(jí)別匯總組。組的層次結(jié)構(gòu)取決于列分組時(shí)指定使用的順序。更改列分組的順序會(huì)影響在結(jié)果集內(nèi)生成的行數(shù)。GROUPBY...WITHROLLUP為列表達(dá)式的每個(gè)組合創(chuàng)建一個(gè)組，而且它將查詢結(jié)果總結(jié)為小計(jì)和總計(jì)。3.5查詢結(jié)果分組3.5.4使用聚合函數(shù)匯總數(shù)據(jù)在MySQL中有很多聚合函數(shù)。聚合函數(shù)對(duì)一組值執(zhí)行計(jì)算并返回單個(gè)值。除了COUNT函數(shù)以外，其他聚合函數(shù)都會(huì)忽略空值。所有聚合函數(shù)均為確定性函數(shù)。也就是說，只要使用一組特定輸入值調(diào)用聚合函數(shù)，它總是返回相同的值。聚合函數(shù)可以用在SELECT子句、ORDERBY子句以及HAVING子句中。3.5查詢結(jié)果分組3.6.1ORDERBY子句語法格式在SELECT語句中，可以通過添加ORDERBY子句來指定返回的查詢結(jié)果所使用的排序順序：ORDERBY{列名|表達(dá)式|列位置}[ASC|DESC],...在ORDERBY后面指定的作為排序依據(jù)可以是列名、表達(dá)式或列位置，列位置從1開始編號(hào)。作為排序依據(jù)的列也可以不包含在由SELECT子句指定的輸出項(xiàng)列表中，計(jì)算列也可以作為排序列。ASC指定按升序排序，即從最低值到最高值對(duì)指定列中的值進(jìn)行排序，這是默認(rèn)排序順序，因此ASC關(guān)鍵字可以省略。DESC指定按降序排序，即從最高值到最低值對(duì)指定列中的值進(jìn)行排序。在排序操作中，空值被視為最低的可能值，因此升序排列時(shí)空值出現(xiàn)在最上方，降序排列時(shí)空值出現(xiàn)在最下方。3.6.2按單列排序在ORDERBY子句中可以指定單個(gè)列、表達(dá)式或列位置作為排序依據(jù)，此時(shí)也可以將聚合函數(shù)用到排序列中。3.6.3按多列排序在ORDERBY子句中也可以指定多個(gè)排序列，不同排序列之間以逗號(hào)分隔。排序列的順序決定結(jié)果集的排序方式，即首先按照前面的列值進(jìn)行排序，如果在兩個(gè)行中該列的值相同，則按照后面的列值進(jìn)行排序，以此類推。3.6查詢結(jié)果排序3.7.1LIMIT子句語法格式LIMIT子句可以用于限制SELECT語句返回的行數(shù)，語法格式如下。LIMIT{[偏移,]行數(shù)|行數(shù)OFFSET偏移}其中偏移和行數(shù)都是非負(fù)整數(shù)常數(shù)；偏移指定要返回的起始行的偏移量，其默認(rèn)值為0，表示第一行的偏移量；行數(shù)指定要返回的最大行數(shù)。例如，LIMIT10表示SELECT語句返回結(jié)果集的前面10行；LIMIT5,20表示從第6行開始返回20行。在實(shí)際應(yīng)用中，經(jīng)常需要按某項(xiàng)標(biāo)準(zhǔn)來制作排行榜，此時(shí)將LIMIT子句與ORDERBY子句一起使用即可。3.7.2檢索到結(jié)果集末尾如果要從特定的偏移量檢索所有行直到結(jié)果集的末尾，則可以使用一些大數(shù)字作為第二個(gè)參數(shù)。例如，下面的語句檢索從第96行到最后一行的所有行：SELECT*FROMtblLIMIT95,18446744073709551615;3.7限制查詢結(jié)果行數(shù)3.8.1標(biāo)量子查詢子查詢的最簡(jiǎn)單形式是返回單一值的標(biāo)量子查詢。標(biāo)量子查詢是一個(gè)單一操作數(shù)。只要單一列值或文字是合法的，并且希望子查詢具有所有操作數(shù)都具有的特性，則可以使用標(biāo)量子查詢。操作數(shù)具有的特性包括：一個(gè)數(shù)據(jù)類型、一個(gè)長度、一個(gè)指示是否可以為NULL值的標(biāo)志等。3.8.2比較子查詢比較子查詢是指將表達(dá)式的值與子查詢返回的結(jié)果進(jìn)行比較，語法格式如下。表達(dá)式比較運(yùn)算符{ALL|SOME|ANY}(SELECT語句)其中表達(dá)式指定要進(jìn)行比較的表達(dá)式，圓括號(hào)內(nèi)的SELECT語句表示一個(gè)子查詢，比較運(yùn)算符包括：>、<、>=、<=、<>、!=、<=>。3.8子查詢3.8.3IN子查詢子查詢可以通過關(guān)鍵字IN引入，語法格式如下。表達(dá)式[NOT]IN(子查詢)其中子查詢?yōu)榱凶硬樵儯祷氐慕Y(jié)果集是單個(gè)列，其中包含0個(gè)或多個(gè)值。使用IN引入的子查詢可以用于集成員測(cè)試，也就是將一個(gè)表達(dá)式的值與子查詢返回的一列值進(jìn)行比較，如果該表達(dá)式的值與此列中的任何一個(gè)值相等，則集成員測(cè)試返回TRUE，否則返回FALSE。關(guān)鍵字NOT用于對(duì)測(cè)試結(jié)果取反。在集成員測(cè)試中，由子查詢返回的結(jié)果集是單個(gè)列值的一個(gè)列表，該列的數(shù)據(jù)類型必須與測(cè)試表達(dá)式的數(shù)據(jù)類型相同。當(dāng)子查詢返回結(jié)果之后，外層查詢將使用這些結(jié)果。使用子查詢時(shí)需要注意限定列名的問題。一般規(guī)則是，語句中的列名通過同級(jí)FROM子句中引用的表來隱性限定。如果子查詢的FROM子句引用的表中不存在子查詢中引用的列，而外部查詢的FROM子句引用的表中存在該列，則這個(gè)子查詢可以正確執(zhí)行。3.8子查詢3.8.4ANY子查詢子查詢可以使用關(guān)鍵字ANY引入：表達(dá)式比較運(yùn)算符{ANY|SOME}(子查詢)ANY關(guān)鍵詞前面必須使用一個(gè)比較運(yùn)算符。子查詢?yōu)榱凶硬樵?，它返回的結(jié)果集是單個(gè)列，其中包含0個(gè)或多個(gè)值。使用ANY關(guān)鍵詞引入子查詢時(shí)，對(duì)表達(dá)式與子查詢返回的列中的任何一個(gè)值進(jìn)行比較，如果比較結(jié)果為TRUE，則返回TRUE。3.8.5ALL子查詢子查詢可以使用關(guān)鍵字ALL引入：表達(dá)式比較運(yùn)算符ALL(子查詢)關(guān)鍵字ALL必須用在一個(gè)比較運(yùn)算符的后面。子查詢是一個(gè)列子查詢，它返回的結(jié)果集是單個(gè)列，其中包含0個(gè)或多個(gè)值。使用ALL引入子查詢時(shí)，將使用比較運(yùn)算符對(duì)表達(dá)式與子查詢返回的列中的所有值進(jìn)行比較，如果比較結(jié)果為TRUE，則返回TRUE。3.8子查詢3.8.6行子查詢行子查詢是一個(gè)能返回一個(gè)單一行的子查詢，它返回的結(jié)果集包含一個(gè)以上的列值（1行N列）。下面給出兩個(gè)例子。SELECT*FROMt1WHERE(1,2)=(SELECTcol1,col2FROMt2);SELECT*FROMt1WHEREROW(1,2)=(SELECTcol1,col2FROMt2);其中表達(dá)式(1,2)和ROW(1,2)稱為行構(gòu)造器，兩者是等效的。如果在表t2的一個(gè)行中，col1=1且col2=2，則WHERE表達(dá)式的值為TRUE。下面兩個(gè)語句在語義上是等效的。SELECT*FROMt1WHERE(col1,col2)=(1,1);SELECT*FROMt1WHEREcol1=1ANDcol2=1;3.8子查詢3.8.7EXISTS子查詢子查詢可以使用關(guān)鍵字EXISTS引入，語法格式如下。[NOT]EXISTS(子查詢)當(dāng)使用EXISTS關(guān)鍵字入子查詢時(shí)，將進(jìn)行存在性測(cè)試。外部查詢的WHERE子句測(cè)試子查詢返回的行是否存在。如果子查詢返回任何的行，則EXISTS條件為TRUE，否則為FALSE。子查詢實(shí)際上不產(chǎn)生任何數(shù)據(jù)，它只返回TRUE或FALSE值。NOT用于對(duì)測(cè)試結(jié)果取反。3.8.8派生表子查詢除了用在SELECT語句的輸出項(xiàng)列表和WHERE子句中，也可以用在FROM子句中，此時(shí)還允許為子查詢?cè)O(shè)置一個(gè)別名。當(dāng)處理具有別名的子查詢時(shí)，會(huì)將返回的結(jié)果存儲(chǔ)到一個(gè)中間表，外部查詢可以從該中間表中查詢數(shù)據(jù)，這種中間表稱為派生表。注意：派生表只是由SELECT語句返回的虛擬表，它并不作為對(duì)象存儲(chǔ)，僅在查詢執(zhí)行期間存在。與子查詢不同的是，派生表必須具有別名，以便能夠在外部查詢中引用其名稱。如果派生表沒有別名，將會(huì)發(fā)生錯(cuò)誤。3.8子查詢3.8.9公用表表達(dá)式公用表表達(dá)式（CTE）是MySQL8.0的新特性之一。具體來說，公用表表達(dá)式就是一個(gè)命名的臨時(shí)結(jié)果集，它存在于單個(gè)語句的范圍內(nèi)，并且可以在該語句中多次引用。CTE的結(jié)構(gòu)包括名稱、可選的列名列表和定義CTE的子查詢。定義CTE后，可以在SELECT語句中引用它。CTE的基本語法格式如下：WITHCTE名稱[(列名,列名,...)]AS(

子查詢)SELECT*FROMCTE名稱;子查詢中的列數(shù)必須與列名列表中的列數(shù)相同。如果省略列名列表，則CTE將使用定義CTE的子查詢的列名列表。與派生表類似，公用表表達(dá)式不作為對(duì)象存儲(chǔ)，僅在查詢執(zhí)行期間存在。與派生表不同的是，公用表表達(dá)式可以遞歸引用，也可以在同一個(gè)查詢中多次引用。公用表表達(dá)式提供了更好的可讀性和性能，使用公用表表達(dá)式可以通過更可讀的方式來構(gòu)建復(fù)雜查詢。3.8子查詢3.9.1UNION語句UNION語句可以將多個(gè)SELECT語句的結(jié)果集組合成一個(gè)新的結(jié)果集，語法格式如下。SELECT...UNION[ALL|DISTINCT]SELECT...[UNION[ALL|DISTINCT]SELECT...]在每個(gè)SELECT語句的對(duì)應(yīng)位置上選擇的列應(yīng)具有相同的數(shù)據(jù)類型。例如，在第一個(gè)語句選擇的第一列應(yīng)與其他語句選擇的第一列具有相同的數(shù)據(jù)類型。在第一個(gè)SELECT語句中所使用的列名稱將作為于結(jié)果集的列名稱。各個(gè)SELECT語句都是常規(guī)的選擇語句，但是受到一些限制，例如只有最后一個(gè)SELECT語句可以使用INTOOUTFILE。3.9組合查詢結(jié)果3.9.2應(yīng)用示例在學(xué)生成績(jī)管理數(shù)據(jù)庫中查詢軟件專業(yè)和網(wǎng)絡(luò)專業(yè)學(xué)號(hào)排在前3位的學(xué)生:USEsams;(SELECTmajorAS專業(yè),classnameAS班級(jí),stuidAS學(xué)號(hào),stunameAS姓名,genderAS性別,birthdateAS出生日期FROMstudentWHEREmajor='軟件'ORDERBYstuidLIMIT3)UNION(SELECTmajor專業(yè),classnameAS班級(jí),stuidAS學(xué)號(hào),stunameAS姓名,genderAS性別,birthdateAS出生日期FROMstudentWHEREmajor='網(wǎng)絡(luò)'ORDERBYstuidLIMIT3);3.9組合查詢結(jié)果第4章索引與視圖4.1.1索引的概念在關(guān)系型數(shù)據(jù)庫中，索引是一種對(duì)表中一列或多列的值進(jìn)行排序的一種存儲(chǔ)結(jié)構(gòu)，它是某個(gè)表中一列或多列的值的集合與指向表中標(biāo)識(shí)這些值的數(shù)據(jù)頁的邏輯指針列表。索引的作用相當(dāng)于圖書的目錄，可以根據(jù)目錄中的頁碼快速找到所需要的內(nèi)容。索引提供指向存儲(chǔ)在表的指定列中的數(shù)據(jù)值的指針，并且根據(jù)指定的排序順序?qū)@些指針進(jìn)行排序。查詢數(shù)據(jù)庫時(shí)可以通過索引找到特定值，然后跟隨指針找到包含該值的行。這樣可以使對(duì)應(yīng)于表的SQL語句執(zhí)行得更快，可以快速訪問數(shù)據(jù)庫表中的特定信息。當(dāng)表中包含大量記錄時(shí)，從表中查詢數(shù)據(jù)通常有以下兩種方式。全表搜索，即從表取出所有的記錄，并與查詢條件逐一進(jìn)行對(duì)比，然后返回滿足條件的記錄，這樣做會(huì)消耗大量系統(tǒng)時(shí)間，并造成大量磁盤I/O操作。事先在表中基于一列或多表創(chuàng)建索引，然后在索引中找到符合查詢條件的索引值，最后通過保存在索引中的行標(biāo)識(shí)（相當(dāng)于頁碼）快速找到表中對(duì)應(yīng)的記錄。使用MySQL數(shù)據(jù)庫時(shí)也不是總要通過索引來優(yōu)化查詢。使用索引固然可以提高檢索數(shù)據(jù)的速度，但創(chuàng)建和維護(hù)索引需要耗費(fèi)一定的時(shí)間，而且存儲(chǔ)索引也需要占用一定的磁盤空間。索引可以提高查詢數(shù)據(jù)的速度，但也會(huì)影響插入數(shù)據(jù)的操作，這是因?yàn)橄蛴兴饕谋碇胁迦胗涗洉r(shí)，這些記錄會(huì)按索引進(jìn)行排序。4.1索引概述4.1.2索引的作用在數(shù)據(jù)庫中創(chuàng)建索引主要有以下作用?？焖僮x取數(shù)據(jù)。保證數(shù)據(jù)記錄的唯一性。實(shí)現(xiàn)表與表之間的參照完整性。在SELECT語句中使用GROUPBY、ORDERBY子句進(jìn)行數(shù)據(jù)查詢時(shí)，通過索引可以縮短分組和排序的時(shí)間。在MySQL中，對(duì)所有數(shù)據(jù)類型的列都可以創(chuàng)建索引，但使用時(shí)需要注意以下幾點(diǎn)。一個(gè)表中最多可以有16個(gè)索引，最大索引長度為256字節(jié)。對(duì)于CHAR和VARCHAR類型的列可以索引列的前綴。這樣，索引的速度更快，并且比索引整個(gè)列所占用的空間要少?？梢栽诙鄠€(gè)列上創(chuàng)建索引，索引列最多可以由15個(gè)列組成。只有當(dāng)表使用InnoDB、BDB或MyISAM存儲(chǔ)引擎時(shí)，才能向具有NULL、TEXT或BLOB的列添加索引。4.1索引概述4.1.3索引的分類在MySQL中，大多數(shù)索引都是以B-樹方式存儲(chǔ)的，這種方式構(gòu)建為多個(gè)節(jié)點(diǎn)的一棵樹。頂部的節(jié)點(diǎn)為索引的起始點(diǎn)，稱為根。每個(gè)節(jié)點(diǎn)中包含索引列的幾個(gè)值，節(jié)點(diǎn)中的每個(gè)值指向另一個(gè)節(jié)點(diǎn)，或者指向表中的一行，一個(gè)節(jié)點(diǎn)中的值必須是有序排列的。指向一行的節(jié)點(diǎn)稱為葉子頁。葉子頁本身也是相互連接的，一個(gè)葉子頁包含一個(gè)指針，該指針指向下一組。索引中的節(jié)點(diǎn)是存儲(chǔ)在文件中的。在MySQL中，一個(gè)表中的索引都保存在同一個(gè)索引文件中。如果在表添加或刪除一行，或者更新表中的值，MySQL會(huì)自動(dòng)地更新索引，因此索引始終與表的內(nèi)容保持一致。以B-樹方式存儲(chǔ)的索引有以下幾種主要類型。普通索引（INDEX）：這是最基本的數(shù)據(jù)類型，它沒有唯一性之類的限制，創(chuàng)建普通索引時(shí)使用關(guān)鍵字INDEX。唯一索引（UNIQUE）：這種索引與普通索引基本相同，所不同的是，索引列的值不能重復(fù)，即必須是唯一的。創(chuàng)建唯一索引時(shí)使用關(guān)鍵字UNIQUE。主鍵（PRIMARYKEY）：這也是一種唯一索引，但一個(gè)表只能有一個(gè)主鍵。主鍵通常是在創(chuàng)建表時(shí)設(shè)置，也可以通過修改表來設(shè)置主鍵。創(chuàng)建主鍵時(shí)使用關(guān)鍵字PRIMARYKEY。全文索引（FULLTEXT）：在定義全文索引的列上支持值的全文查找，允許在這些索引列中插入重復(fù)值和空值。全文索引只能在TEXT、CHAR或VARCHAR類型的列上創(chuàng)建，并且只能在MyISAM表中創(chuàng)建。全文索引可以在創(chuàng)建表或修改表時(shí)創(chuàng)建?？臻g索引（SPATIAL）：這是在MyISAM表的空間數(shù)據(jù)類型的字段上建立的索引。MySQL支持4種空間數(shù)據(jù)類型。創(chuàng)建空間索引時(shí)要使用SPATIAL關(guān)鍵字進(jìn)行擴(kuò)展。4.1索引概述4.1.4索引的設(shè)計(jì)原則索引并非越多越好，一個(gè)表中如有大量的索引，不僅占用磁盤空間，而且會(huì)影響數(shù)據(jù)增刪改操作的性能，因?yàn)楫?dāng)表中數(shù)據(jù)更改時(shí)索引也會(huì)進(jìn)行調(diào)整和更新。避免對(duì)經(jīng)常更新的表設(shè)置過多的索引，并且索引中的列盡可能少。對(duì)于經(jīng)常查詢的字段應(yīng)創(chuàng)建索引，但要避免添加不必要的字段。數(shù)據(jù)量小的表最好不要索引，由于數(shù)據(jù)量小，查詢花費(fèi)的時(shí)間可能比遍歷索引的時(shí)間還要短，索引可能不會(huì)產(chǎn)生優(yōu)化效果。在條件表達(dá)式中經(jīng)常用到的不同值較多的列上創(chuàng)建索引，在不同值很少的列上不要?jiǎng)?chuàng)建索引。當(dāng)唯一性是某種數(shù)據(jù)本身的特征時(shí)應(yīng)創(chuàng)建唯一索引，使用唯一索引可以保證定義的列的數(shù)據(jù)完整性，從而提高查詢速度。在頻繁進(jìn)行排序或分組的列上創(chuàng)建索引，如果要排序的列有多個(gè)，可考慮在這些列上創(chuàng)建組合索引。4.1索引概述4.2.1創(chuàng)建表時(shí)創(chuàng)建索引使用CREATETABLE語句創(chuàng)建表時(shí)可以直接包含索引的定義：CREATE[TEMPORARY]TABLE[IFNOTEXISTS]表名[(列定義,...|索引定義)][表選項(xiàng)]索引定義：[CONSTRAINT[字符串]]PRIMARYKEY[索引類型](索引列,...) /*主鍵*/|{INDEX|KEY}[索引名][索引類型](索引列,...) /*普通索引*/|[CONSTRAINT[字符串]]UNIQUE[INDEX|KEY] /*唯一性索引*/[索引名][索引類型](索引列,...)|{FULLTEXT|SPATIAL}[INDEX|KEY][索引名](索引列,...) /*全文索引*/|[CONSTRAINT[字符串]]FOREIGNKEY /*外鍵*/[索引名](列名,...)引用定義索引類型：USING{BTREE|HASH}索引列：列名[(長度)][ASC|DESC]4.2創(chuàng)建索引4.2.2使用ALTERTABLE創(chuàng)建索引對(duì)于已經(jīng)存在的表，可以使用ALTERTABLE語句在表中指定的列上創(chuàng)建索引：ALTERTABLE表名...|ADD{INDEX|KEY}[索引名] /*添加普通索引*/[索引類型](索引列,...)[索引選項(xiàng)]...|ADD[CONSTRAINT[字符串]]PRIMARYKEY /*添加主鍵*/[索引類型](索引列,...)[索引選項(xiàng)]...|ADD[CONSTRAINT[字符串]]UNIQUE[INDEX|KEY][索引名] /*添加唯一性索引*/[索引類型](索引列,...)[索引選項(xiàng)]...|ADDFULLTEXT[INDEX|KEY][索引名] /*添加全文索引*/(索引列,...)[索引選項(xiàng)]...|ADDSPATIAL[INDEX|KEY][索引名] /*添加空間索引*/(索引列,...)[索引選項(xiàng)]...|ADD[CONSTRAINT[字符串]] /*添加外鍵*/FOREIGNKEY[索引名](列名,...)引用定義|{DISABLE|ENABLE}KEYS4.2創(chuàng)建索引4.2.3使用CREATEINDEX創(chuàng)建索引使用CREATEINDEX語句可以在現(xiàn)有的表中創(chuàng)建索引：CREATE[UNIQUE|FULLTEXT|SPATIAL]INDEX索引名[索引類型]ON表名(索引列,...)索引類型：USING{BTREE|HASH}索引列：列名[(長度)][ASC|DESC]關(guān)鍵字UNIQUE用于創(chuàng)建唯一性索引，F(xiàn)ULLTEXT用于創(chuàng)建全文索引，SPATIAL用于創(chuàng)建空間索引。使用CREATEINDEX語句不能創(chuàng)建主鍵。索引名表示索引的名稱，在一個(gè)表中索引名必須是唯一的。索引類型為BTREE或HASH，只有部分存儲(chǔ)引擎可以在創(chuàng)建索引時(shí)指定索引的類型。索引列可以是一個(gè)或多個(gè)，使用格式為(索引列1,索引列2,...）的列清單可以創(chuàng)建出一個(gè)多列索引。對(duì)于CHAR和VARCHAR列，只用一列的一部分就可以創(chuàng)建索引。創(chuàng)建索引時(shí)，使用列名(長度)格式可以對(duì)前綴編制索引。前綴包括每列值前面的部分字符。BLOB和TEXT列也可以編制索引，但是必須給出前綴長度。4.2創(chuàng)建索引4.2.4查看索引信息使用SHOWINDEX返回表中的索引信息：SHOW[EXTENDED]{INDEX|INDEXES|KEYS}{FROM|IN}表名[{FROM|IN}數(shù)據(jù)庫名][WHERE條件]其中表名指定索引所在的表，數(shù)據(jù)庫名指定索引所在的數(shù)據(jù)庫；“表名FROM數(shù)據(jù)庫名”語法的替代方法是“數(shù)據(jù)庫名.表名”。4.2創(chuàng)建索引4.2.5刪除索引使用DROPINDEX語句從表中刪除索引：DROPINDEX索引名ON表名[算法選項(xiàng)|鎖定選項(xiàng)]...算法選項(xiàng):ALGORITHM[=]{DEFAULT|INPLACE|COPY}鎖定選項(xiàng):LOCK[=]{DEFAULT|NONE|SHARED|EXCLUSIVE}其中索引名指定要?jiǎng)h除的索引，表名指定該索引所在的表。這個(gè)語句映射為一個(gè)ALTERTABLE，用于刪除指定的索引。4.2創(chuàng)建索引4.3.1視圖的概念使用SELECT語句從數(shù)據(jù)庫中查詢數(shù)據(jù)時(shí)，這些語句并不存儲(chǔ)在數(shù)據(jù)庫中，為了重復(fù)使用這些語句通常是把它們存儲(chǔ)在外部腳本文件中。當(dāng)需要執(zhí)行數(shù)據(jù)查詢時(shí)，可以使用命令行客戶端工具mysql或某個(gè)GUI客戶端工具（如MySQLWorkbench或NavicatforMySQL）提供的SQL編輯器來執(zhí)行腳本文件。實(shí)際上，為了便于執(zhí)行比較復(fù)雜的數(shù)據(jù)查詢，一個(gè)更好的方案是將那些常用的SELECT語句以視圖對(duì)象的形式存儲(chǔ)在數(shù)據(jù)庫中。所謂視圖，就是從一個(gè)或多個(gè)表（或視圖）中導(dǎo)出的虛擬表。視圖與SELECT語句的關(guān)系十分密切，視圖中的數(shù)據(jù)就是通過執(zhí)行SELECT語句獲取的。與真實(shí)的表一樣，視圖也包含一系列帶有名稱的列和行數(shù)據(jù)，但這些列和行數(shù)據(jù)來自SELECT語句所引用的表，并且是在引用視圖時(shí)動(dòng)態(tài)生成的，而不是以數(shù)據(jù)集形式存在于數(shù)據(jù)庫中。數(shù)據(jù)庫中的數(shù)據(jù)存儲(chǔ)在表中，創(chuàng)建視圖時(shí)也只存儲(chǔ)的視圖的定義。視圖定義中所引用的表也稱為基表。當(dāng)在數(shù)據(jù)庫中創(chuàng)建視圖以后，視圖也可以像表一樣用在SELECT語句的FROM子句中作為查詢的數(shù)據(jù)源來使用，或者用在數(shù)據(jù)的更新和刪除操作中。當(dāng)通過視圖進(jìn)行數(shù)據(jù)操作時(shí)，MySQL將根據(jù)視圖的定義去操作與視圖相關(guān)聯(lián)的基表。4.3視圖概述4.3.2視圖的作用視圖可以用來集中、簡(jiǎn)化和自定義每個(gè)用戶對(duì)數(shù)據(jù)庫的不同訪問，也可以用作安全機(jī)制。視圖通常用在以下3種場(chǎng)合。簡(jiǎn)化數(shù)據(jù)操作。使用選擇查詢檢索數(shù)據(jù)時(shí)，如果查詢中的數(shù)據(jù)分散在兩個(gè)或多個(gè)表中，或者所用搜索條件比較復(fù)雜，往往要多次使用JOIN運(yùn)算符來編寫很長的SELECT語句。如果需要多次執(zhí)行相同的數(shù)據(jù)檢索任務(wù)，則可以考慮在這些常用查詢的基礎(chǔ)上創(chuàng)建視圖，然后在SELECT語句的FROM子句中引用這些視圖，而不必每次都輸入相同的查詢語句。自定義數(shù)據(jù)。視圖允許用戶以不同方式查看數(shù)據(jù)，即使在他們同時(shí)使用相同的數(shù)據(jù)時(shí)也是如此。這在具有許多不同目的和技術(shù)水平的用戶共用同一數(shù)據(jù)庫時(shí)尤其有用。例如，可以創(chuàng)建一個(gè)視圖以僅檢索由客戶經(jīng)理處理的客戶數(shù)據(jù)，該視圖可以根據(jù)使用它的客戶經(jīng)理的登錄ID來決定檢索哪些數(shù)據(jù)。提高數(shù)據(jù)庫的安全性。通常的作法是讓用戶通過視圖來訪問表中的特定列和行，而不對(duì)他們授予直接訪問基表的權(quán)限。此外，可以針對(duì)不同的用戶定義不同的視圖，在用戶視圖上不包括那些機(jī)密數(shù)據(jù)列，從而提供對(duì)機(jī)密數(shù)據(jù)的保護(hù)。4.3視圖概述4.4.1查看創(chuàng)建視圖權(quán)限MySQL用戶要在數(shù)據(jù)庫中創(chuàng)建視圖，則必須擁有CREATEVIEW權(quán)限和查詢涉及的列的SELECT權(quán)限。這些權(quán)限信息包含在系統(tǒng)數(shù)據(jù)庫mysql的user表中，可以使用下列SELECT語句進(jìn)行查詢。SELECTSelect_priv,Create_view_privFROMmysql.userWHEREuser='用戶名';其中Select_priv列表示用戶是否具有SELECT權(quán)限，如果該列的值為Y，則表示擁有SELECT權(quán)限，如果該列的值為N，則表示沒有此權(quán)限。Create_view_priv列表示用戶是否擁有CREATEVIEW權(quán)限，如果該列的值為Y，則表示擁有CREATEVIEW權(quán)限，如果該列的值為N，則表示沒有此權(quán)限。mysql.user表示MySQL系統(tǒng)數(shù)據(jù)庫mysql中的user表。用戶名的值必須用引號(hào)括起來。4.4創(chuàng)建視圖4.4.2CREATEVIEW語句使用CREATEVIEW語句創(chuàng)建一個(gè)新的視圖：CREATE[ORREPLACE][ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}][DEFINER={用戶|CURRENT_USER}][SQLSECURITY{DEFINER|INVOKER}]VIEW視圖名[(