MySQL必知必會1-20章讀書筆記

MySQL備忘

目錄

目錄

• 使用MySQL
• 檢索數據
• 排序檢索數據
• 過濾數據
• 數據過濾
• 用通配符進行過濾
• 用正則表達式進行搜索
• 創建計算欄位
• 使用數據處理函數
• 數值處理函數
• 彙總數據
• 分組數據
• 使用子查詢
• 作為計算欄位使用子查詢
• 聯結表
• 創建高級聯結
• 組合查詢
• 全文本搜索
• 插入數據
• 更新和刪除數據

使用MySQL

• mysql -u<usr> -p -h<host> -P <port> 分別指明用戶名，主機名，埠號

• show columns from <table> 對每個欄位返回一行，其中的信息分別是欄位名，數據類型，是否允許為空，鍵信息，預設值及其他信息

  describe <table> 與上同

• show status 顯示廣泛的伺服器狀態信息
• SHOW CREATE DATABASE <database>和SHOW CREATE TABLE <table>，分別用來顯示創建特定資料庫或表的MySQL語句
• SHOW GRANTS，用來顯示授予用戶（所有用戶或特定用戶）的安全許可權
• SHOW ERRORS和SHOW WARNINGS用來顯示伺服器錯誤或警告消息

檢索數據

​ 通過select * from somewhere選擇所有的列時，列的順序一般情況下是列在表定義中出現的順序，但有時也不是，表的模式的變化（如添加或刪除列）可能會導致順序的變化。

一般情況不要使用通配符，除了的確需要，使用通配符會降低檢索和應用程式的性能

• select distinct <variable> from <table> 來指示MySQL只返回不同的值

註意：

不能部分使用DISTINCT DISTINCT關鍵字應用於所有列而不僅是前置它的列。如果給出SELECT DISTINCT vend_id, prod_price，除非指定的兩個列都相同，否則所有行都將被檢索出來。也就是說select distinct a, b, c from table相當於select a, b, c from table group by a, b, c會選出所有a, b, c的不同組合。

• select <variable> from <table> limit <n> 使用LIMIT子句來返回結果的第一行或前幾行

​ 限制返回結果不多於5行，此外，還可以指定喲檢索的開始行和行數。

行0 檢索出來的第一行為行0而不是行1。因此，LIMIT 1, 1將檢索出第二行而不是第一行。

在行數不夠時 LIMIT中指定要檢索的行數為檢索的最大行數。如果沒有足夠的行（例如，給出LIMIT 10, 5，但只有13行），MySQL將只返回它能返回的那麼多行。

MySQL 5的LIMIT語法 LIMIT 3, 4的含義是從行4開始的3行還是從行3開始的4行？如前所述，它的意思是從行3開始的4行，這容易把人搞糊塗。由於這個原因，MySQL 5支持LIMIT的另一種替代語法。LIMIT 4 OFFSET 3意為從行3開始取4行，就像LIMIT 3, 4一樣

排序檢索數據

​ 檢索出的數據並不是以純粹的隨機順序顯示的。如果不排序，數據一般將以它在底層表中出現的順序顯示。這可以是數據最初添加到表中的順序。但是，如果數據後來進行過更新或刪除，則此順序將會受到MySQL重用回收存儲空間的影響。因此，如果不明確控制的話，不能（也不應該）依賴該排序順序。關係資料庫設計理論認為，如果不明確規定排序順序，則不應該假定檢索出的數據的順序有意義。

子句（clause） SQL語句由子句構成，有些子句是必需的，而有的是可選的。一個子句通常由一個關鍵字和所提供的數據組成。子句的例子有SELECT語句的FROM子句

• select <variables> from <table> order by <variable> [desc] 為了明確地排序用select語句檢索出的數據，可以使用order by子句。 order by 子句取一個或者多個列的名字，據此對數據的數據進行排序。

通過非選擇列進行排序 通常，ORDER BY子句中使用的列將是為顯示所選擇的列。但是，實際上並不一定要這樣，用非檢索的列排序數據是完全合法的。

​ 按列降序排列：

DESC關鍵字只應用到直接位於其前面的列名。

在多個列上降序排序 如果想在多個列上進行降序排序，必須對每個列指定DESC關鍵字。

區分大小寫和排序順序 在對文本性的數據進行排序時，A與a相同嗎？a位於B之前還是位於Z之後？這些問題不是理論問題，其答案取決於資料庫如何設置。在字典（dictionary）排序順序中，A被視為與a相同，這是MySQL（和大多數資料庫管理系統）的預設行為。但是，許多資料庫管理員能夠在需要時改變這種行為（如果你的資料庫包含大量外語字元，可能必須這樣做）。這裡，關鍵的問題是，如果確實需要改變這種排序順序，用簡單的ORDER BY子句做不到。你必須請求資料庫管理員的幫助。

​ 這裡通過order by 和limit的組合使用來得到價格最高的商品。

ORDER BY子句的位置 在給出ORDER BY子句時，應該保證它位於FROM子句之後。如果使用LIMIT，它必須位於ORDER BY之後。使用子句的次序不對將產生錯誤消息。請註意，order by子句必須是select語句中的最後一個子句。否則將產生錯誤信息。

過濾數據

​ 在SELECT語句中，數據根據WHERE子句中指定的搜索條件進行過濾。WHERE子句在表名（FROM子句）之後給出

SQL過濾與應用過濾 數據也可以在應用層過濾。為此目的，SQL的SELECT語句為客戶機應用檢索出超過實際所需的數據，然後客戶機代碼對返回數據進行迴圈，以提取出需要的行。通常，這種實現並不令人滿意。因此，對資料庫進行了優化，以便快速有效地對數據進行過濾。讓客戶機應用（或開發語言）處理資料庫的工作將會極大地影響應用的性能，並且使所創建的應用完全不具備可伸縮性。此外，如果在客戶機上過濾數據，伺服器不得不通過網路發送多餘的數據，這將導致網路帶寬的浪費。

​ 接著看一個特例：

檢查WHERE prod_name=‘fuses’語句，它返回prod_name的值為Fuses的一行。MySQL在執行匹配時預設不區分大小寫，所以fuses與Fuses匹配。

何時使用引號 如果仔細觀察上述WHERE子句中使用的條件，會看到有的值括在單引號內（如前面使用的'fuses'），而有的值未括起來。單引號用來限定字元串。如果將值與串類型的列進行比較，則需要限定引號。用來與數值列進行比較的值不用引號。

​ 為了檢查某個範圍的值，可使用BETWEEN操作符。其語法與其他WHERE子句的操作符稍有不同，因為它需要兩個值，即範圍的開始值和結束值。例如，BETWEEN操作符可用來檢索價格在5美元和10美元之間或日期在指
定的開始日期和結束日期之間的所有產品。

從這個例子中可以看到，在使用BETWEEN時，必須指定兩個值——所需範圍的低端值和高端值。這兩個值必須用AND關鍵字分隔。BETWEEN匹配範圍中所有的值，包括指定的開始值和結束值。

​ 在創建表時，表設計人員可以指定其中的列是否可以不包含值。在一個列不包含值時，稱其為包含空值NULL。

NULL 無值（no value），它與欄位包含0、空字元串或僅僅包含空格不同。

​ 通過IS NULL子句過濾含空值的行：

NULL與不匹配 在通過過濾選擇出不具有特定值的行時，你可能希望返回具有NULL值的行。但是，不行。因為未知具有特殊的含義，資料庫不知道它們是否匹配，所以在匹配過濾或不匹配過濾時不返回它們。因此，在過濾數據時，一定要驗證返回數據中確實給出了被過濾列具有NULL的行。

​ 也就是說，當我們需要某一行時，尤其是哪一行某個屬性為空值，需要特別註意。

數據過濾

​ MySQL中的邏輯操作符有AND，OR, NOT。可以通過組合使用與或非和WHERE子句來獲得更複雜的條件。與絕大多數語言一樣，有優先順序

參考：[菜鳥教程](https://www.runoob.com/mysql/mysql-operator.html)

備註：<=>用以比較和NULL是否相等，比如NULL <=> NULL返回1，而只當一個操作碼為NULL的時候，返回0。註意，不用<=>時，NULL表現出傳染性。

​ 同樣的，類似於絕大多數語言，在條件表達式含糊不清時，使用( )來顯示的指明運算優先次序。

• select <variables> from <table> where <variable> in <tuple> order by <variable> 通過使用in (...)操作符來指定條件範圍。

​ 為什麼要使用IN操作符呢？

1. 在使用長的合法選項清單時，IN操作符的語法更清楚且更直觀。
2. 在使用IN時，計算的次序更容易管理（因為使用的操作符更少）。
3. IN操作符一般比OR操作符清單執行更快。(IN操作符是O(log(N))級別的複雜度，而OR是O(N)級別的複雜度)
4. IN的最大優點是可以包含其他SELECT語句

​ WHERE子句中的NOT操作符有且只有一個功能，那就是否定它之後所跟的任何條件。

MySQL中的NOT MySQL支持使用NOT 對IN 、BETWEEN 和EXISTS子句取反，這與多數其他DBMS允許使用NOT對各種條件取反有很大的差別。

用通配符進行過濾

• select <variables> from <table> where <variable> like <pattern> 而pattern中的%通配符表示任何字元出現任意次數。

區分大小寫 根據MySQL的配置方式，搜索可以是區分大小寫的。如果區分大小寫，'jet%'與JetPack 1000將不匹配。

​ 這個時候可以在like後加binary來表示區分大小寫。

註意：

1. 這裡的binary不是操作符，而是類型轉換運算符，將後面的字元串轉換成ASCII碼，以此來區分大小寫。
2. 如果%使用在查詢單詞的開頭，這個sql語句將不走索引，查詢效率會降低。

註意尾空格 尾空格可能會幹擾通配符匹配。例如，在保存詞anvil 時， 如果它後面有一個或多個空格， 則子句WHERE prod_name LIKE '%anvil'將不會匹配它們，因為在最後的l後有多餘的字元。解決這個問題的一個簡單的辦法是在搜索模式最後附加一個%。一個更好的辦法是使用函數去掉首尾空格。

註意NULL 雖然似乎%通配符可以匹配任何東西，但有一個例外，即NULL。即使是WHERE prod_name LIKE '%'也不能匹配用值NULL作為產品名的行。(可以理解成NA的傳染性)

• select prod_id, prod_name from products where prod_name like '_ton anvil' 這裡出現的_通配符用途與%一致，但下劃線只匹配單個字元而不是多個字元。

​ MySQL的通配符很有用，但是這種有用是有代價的：通配符搜索的處理一般要比前面討論的其他搜索要花的時間更長。

一些使用通配符的技巧：

1. 不要過度使用通配符。如果其他操作符能達到相同的目的，應該使用其他操作符。
2. 在確實需要使用通配符時，除非絕對有必要，否則不要把它們用在搜索模式的開始處。把通配符置於搜索模式的開始處，搜索起來是最慢的。
3. 仔細註意通配符的位置。如果放錯地方，可能不會返回想要的數據。

用正則表達式進行搜索

​ 正則表達式是用來匹配文本的特殊的串（字元集合）。MySQL用WHERE子句對正則表達式提供了初步的支持，允許你指定正則表達式，過濾SELECT檢索出的數據。

• select <variables> from <table> where <variable> rlike/regexp <pattern> 其中RLIKE或者REGEXP後所跟的模式即為正則表達式。

​ 例如select prod_name from products where prod_name regexp '1000' order by prod_name篩選出所有prod_name包含1000的行。

LIKE與REGEXP 在LIKE和REGEXP之間有一個重要的差別。請
看以下兩條語句：

如果執行上述兩條語句，會發現第一條語句不返回數據，而第二條語句返回一行。為什麼？正如第8章所述，LIKE匹配整個列。如果被匹配的文本在列值中出現，LIKE將不會找到它，相應的行也不被返回（除非使用通配符）。而REGEXP在列值內進行匹配，如果被匹配的文本在列值中出現，REGEXP將會找到它，相應的行將被返回。這是一個非常重要的差別。那麼，REGEXP能不能用來匹配整個列值（從而起與LIKE相同的作用）？答案是肯定的，使用^和$定位符（anchor）即可

匹配不區分大小寫 MySQL中的正則表達式匹配（自版本3.23.4後）不區分大小寫（即，大寫和小寫都匹配）。為區分大小寫，可使用BINARY關鍵字，如WHERE prod_name REGEXP BINARY 'JetPack .000'。

匹配\ 為了匹配反斜杠（\）字元本身，需要使用\\。

\或\? 多數正則表達式實現使用單個反斜杠轉義特殊字元，以便能使用這些字元本身。但MySQL要求兩個反斜杠（MySQL自己解釋一個，正則表達式庫解釋另一個）。

匹配字元類

存在找出你自己經常使用的數字、所有字母字元或所有數字字母字元等的匹配。為更方便工作，可以使用預定義的字元集，稱為字元類（character class）。

定位符

使REGEXP起類似LIKE的作用 利用定位符，通過用^開始每個表達式，用$結束每個表達式，可以使REGEXP的作用與LIKE一樣。

簡單的正則表達式測試 可以在不使用資料庫表的情況下用SELECT來測試正則表達式。REGEXP檢查總是返回0（沒有匹配）或1（匹配）。可以用帶文字串的REGEXP來測試表達式，並試
驗它們。相應的語法如下：

select 'help' regexp '[0-9]'

這個例子顯然將返回0（因為文本hello中沒有數字）。

創建計算欄位

​ 存儲在表中的數據往往都不是應用程式所需要的。我們需要直接從資料庫中檢索出轉換、計算或格式化過的數據；而不是檢索出數據，然後再在客戶機應用程式或報告程式中重新格式化。計算欄位是在select語句內創建的。

客戶機與伺服器的格式 可在SQL語句內完成的許多轉換和格式化工作都可以直接在客戶機應用程式內完成。但一般來說，在資料庫伺服器上完成這些操作比在客戶機中完成要快得多，因為DBMS是設計來快速有效地完成這種處理的。

• select Concat(RTrim(vend_name), '(', RTrim(vend_country), ')') from vendors order by vend_name 通過Concat()函數連接字元串與變數，通過Trim()函數去除字元創兩端的空格。

• select Concat(RTrim(vend_name), '(', RTrim(vend_country), ')') as vend_title from vendors order by vend_name 使用AS關鍵字來指定別名。

  此外，還可以直接在select語句中創建計算欄位。如：

  

使用數據處理函數

函數沒有SQL的可移植性強 能運行在多個系統上的代碼稱為可移植的（portable）。相對來說，多數SQL語句是可移植的，在SQL實現之間有差異時，這些差異通常不那麼難處理。而函數的可移植性卻不強。幾乎每種主要的DBMS的實現都支持其他實現不支持的函數，而且有時差異還很大。為了代碼的可移植，許多SQL程式員不贊成使用特殊實現的功能。雖然這樣做很有好處，但不總是利於應用程式的性能。如果不使用這些函數，編寫某些應用程式代碼會很艱難。必須利
用其他方法來實現DBMS非常有效地完成的工作。如果你決定使用函數，應該保證做好代碼註釋，以便以後你（或其他人）能確切地知道所編寫SQL代碼的含義。

大多數SQL實現支持以下類型的函數：

• 用於處理文本串（如刪除或填充值，轉換值為大寫或小寫）的文本函數。
• 用於在數值數據上進行算術操作（如返回絕對值，進行代數運算）的數值函數。
• 用於處理日期和時間值並從這些值中提取特定成分（例如，返回兩個日期之差，檢查日期有效性等）的日期和時間函數。
• 返回DBMS正使用的特殊信息（如返回用戶登錄信息，檢查版本細節）的系統函數。

一些常用的文本處理函數：

[1] SOUNDEX是一個將任何文本串轉換為描述其語音表示的字母數字模式的演算法。SOUNDEX考慮了類似的發音字元和音節，使得能對串進行發音比較而不是字母比較。雖然SOUNDEX不是SQL概念，但MySQL（就像多數DBMS一樣）都提供對SOUNDEX的支持

一個使用Soundex()的例子：

日期和時間常用的處理函數：

具體用法參照：極客學院

應該總是使用4位數字的年份 支持2位數字的年份，MySQL處理00-69為2000-2069，處理70-99為1970-1999。雖然它們可能是打算要的年份，但使用完整的4位數字年份更可靠，因為
MySQL不必做出任何假定。

數值處理函數

具體用法：極客學院

彙總數據

​ 我們經常需要彙總數據而不用把它們實際檢索出來，為此MySQL提供了專門的函數。使用這些函數，MySQL查詢可用於檢索數據，以便分析和報表生成。

以下是MySQL的五個聚集函數：

標準偏差 MySQL還支持一系列的標準偏差聚集函數。詳情戳：MySQL 5.7 Reference Manual

因為上面五個函數都是語義清晰的，因此只講一些註意事項：

AVG()函數：

只用於單個列 AVG()只能用來確定特定數值列的平均值，而且列名必須作為函數參數給出。為了獲得多個列的平均值，必須使用多個AVG()函數。

NULL值 AVG()函數忽略列值為NULL的行。

COUNT()函數的兩種使用方法：

• 使用COUNT(*)對錶中行的數目進行計數，不管表列中包含的是空值（NULL）還是非空值。
• 使用COUNT(column)對特定列中具有值的行進行計數，忽略NULL值。

MAX()函數：

對非數值數據使用MAX() 雖然MAX()一般用來找出最大的數值或日期值，但MySQL允許將它用來返回任意列中的最大值，包括返迴文本列中的最大值。在用於文本數據時，如果數據按相應的列排序，則MAX()返回最後一行。

MIN()函數：

對非數值數據使用MIN() MIN()函數與MAX()函數類似，MySQL允許將它用來返回任意列中的最小值，包括返迴文本列中的最小值。在用於文本數據時，如果數據按相應的列排序，則MIN()返回最前面的行。

SUM()函數：

​ 除了統計某一列的變數的和，還可以用來合計計算值。如：

在多個列上進行計算 如上所示，利用標準的算術操作符，所有聚集函數都可用來執行多個列上的計算。

聚集不同值：

對以上的5個聚集函數，都有如下選項：

• 對所有的行執行計算，指定ALL參數或不給參數（因為ALL是預設行為）；
• 只包含不同的值，指定DISTINCT參數。

ALL為預設 ALL參數不需要指定，因為它是預設行為。如果不指定DISTINCT，則假定為ALL。

重要：

註意 如果指定列名，則DISTINCT只能用於COUNT()。DISTINCT不能用於COUNT(*)，因此不允許使用COUNT（DISTINCT），否則會產生錯誤。類似地，DISTINCT必須使用列名，不能用
於計算或表達式。

​ 理由想想就知道：COUNT(*)的目的是統計表中有多少行，而DISTINCT選項會忽略掉一些NULL值，是衝突的。同樣對於計算或表達式，可能不同的計算式或表達式會產生相同的結果，如果不加考慮的直接去重，會丟失很多數據。

將DISTINCT用於MIN()和MAX() 雖然DISTINCT從技術上可用於MIN()和MAX()，但這樣做實際上沒有價值。一個列中的最小值和最大值不管是否包含不同值都是相同的。

重要：

取別名 在指定別名以包含某個聚集函數的結果時，不應該使用表中實際的列名。雖然這樣做並非不合法，但使用唯一的名字會使你的SQL更易於理解和使用（以及將來容易排除故障）。

​ 聚集函數用來彙總數據。MySQL支持一系列聚集函數，可以用多種方法使用它們以返回所需的結果。這些函數是高效設計的，它們返回結果一般比你在自己的客戶機應用程式中計算要快得多。

分組數據

​ MySQL通過SELECT語句中的GROUP BY子句創建分組。如：

select <variables> from <table> GROUP BY <flag> 其中是某一列名。

一些規定：

• GROUP BY子句可以包含任意數目的列。這使得能對分組進行嵌套，為數據分組提供更細緻的控制。例如經常使用的：group by year, month, day按天分組
• 如果在GROUP BY子句中嵌套了分組，數據將在最後規定的分組上進行彙總。換句話說，在建立分組時，指定的所有列都一起計算（所以不能從個別的列取回數據）。i.e.: group by year, month, day按天使用聚集函數
• GROUP BY子句中列出的每個列都必須是檢索列或有效的表達式（但不能是聚集函數）。如果在SELECT中使用表達式，則必須在GROUP BY子句中指定相同的表達式。 不能使用別名。（應該是說GROUP BY子句中不允許使用列的別名，但是select語句中是可以指定列的別名的）。
• 除聚集計算語句外，SELECT語句中的每個列都必須在GROUP BY子句中給出。
• 如果分組列中具有NULL值，則NULL將作為一個分組返回。如果列中有多行NULL值，它們將分為一組。
• GROUP BY子句必須出現在WHERE子句之後，ORDER BY子句之前。

也就是說，根據我們已經學的MySQL表達式，一套比較完整的流程是：

select var1, var2, COUNT(*) as n
from table
where Condition(var)
group by var1, var2
order by var1, var2 DESC

i.e.：我們必須先篩選，再分組，最後對分組進行排序。

此外：

使用ROLLUP 使用WITH ROLLUP關鍵字，可以得到每個分組以及每個分組彙總級別（針對每個分組）的值，如下所示：

select vend_id, count(*) as num_prods
from products
group by vend_id with rollup;

​ 可以看到with rollup選項實際上做的是一個超分組的操作，即對分組的數據再次進行彙總。最後一行的NULL實際上代表為空，效果是這樣滴：

​ 那問題就來了，如果本來的分組值裡面有個NULL怎麼辦呢？

​ 註意，同所有數據分析的工具一樣，如果不加with rollup選項，NA值會附加在末尾，而一旦加了with rollup選項，空值會自動調整到第一行。

​ 除了能用GROUP BY分組數據外，MySQL還允許過濾分組，規定包括哪些分組，排除哪些分組。我們已經看到了WHERE子句的作用。

​ 但是，在這個需求下WHERE不能完成任務，因為WHERE過濾指定的是行而不是分組。事實上，WHERE沒有分組的概念。那麼，不使用WHERE使用什麼呢？MySQL為此目的提供了另外的子句，那就是HAVING子句。HAVING非常類似於WHERE。事實上，目前為止所學過的所有類型的WHERE子句都可以用HAVING來替代。唯一的差別是WHERE過濾行，而HAVING過濾分組。

HAVING支持所有WHERE操作符 之前提到過的所有可以在where子句中使用過的條件都可以在HAVING子句中使用。

HAVING和WHERE的差別 這裡有另一種理解方法，WHERE在數據分組前進行過濾，HAVING在數據分組後進行過濾。這是一個重要的區別，WHERE排除的行不包括在分組中。這可能會改變計算值，從而影響HAVING子句中基於這些值過濾掉的分組。

分組與排序

​ 我們經常發現用GROUP BY分組的數據確實是以分組順序輸出的。但情況並不總是這樣，它並不是SQL規範所要求的。此外，用戶也可能會要求以不同於分組的順序排序。僅因為你以某種方式分組數據（獲得特定的分組聚集值），並不表示你需要以相同的方式排序輸出。應該提供明確的ORDER BY子句，即使其效果等同於GROUP BY子句也是如此。

不要忘記ORDER BY 一般在使用GROUP BY子句時，應該也給出ORDER BY子句。這是保證數據正確排序的唯一方法。千萬不要僅依賴GROUP BY排序數據。

SELECT子句順序

SELECT var1, var2...
FROM <table>
WHERE <conditions>
GROUP BY var
HAVING <conditions>
ORDER BY var [DESC]
LIMIT [start,] num

說明：

使用子查詢

​ 子查詢，即嵌套在其他查詢中的查詢。

例如：現在需要列出訂購物品TNT2的所有客戶：

格式化SQL 包含子查詢的SELECT語句難以閱讀和調試，特別是它們較為複雜時更是如此。如上所示把子查詢分解為多行並且適當地進行縮進，能極大地簡化子查詢的使用。

​ 尤其要註意這個格式化的過程，因為外部查詢實際上需要的是內部查詢返回一個,分隔的tuple。

​ 可見，在WHERE子句中使用子查詢能夠編寫出功能很強並且很靈活的SQL語句。對於能嵌套的子查詢的數目沒有限制，不過在實際使用時由於性能的限制，不能嵌套太多的子查詢。

​ 雖然子查詢一般與IN操作符結合使用，但也可以用於測試等於（=）、不等於（<>）等。

子查詢和性能 這裡給出的代碼有效並獲得所需的結果。但是，使用子查詢並不總是執行這種類型的數據檢索的最有效的方法。

作為計算欄位使用子查詢

​ 計算欄位，即跟著select語句的一些欄位，是使用子查詢的另外一種方式。

​ 例：假如需要顯示customers表中每個客戶的訂單總數，為此，我們必須執行兩個步驟：

• 從customers表中檢索客戶列表。
• 對於檢索出的每個客戶，統計其在orders表中的訂單數目。

​ 有：

SELECT cust_name,
	   cust_state,
	   (SELECT COUNT(*)
        FROM orders
        WHERE orders.cust_id = customers.cust_id) AS orders
 FROM customers
 ORDER BY cust_name;

​ 註意這裡被嵌套的查詢where子句需要使用完全限定的列名。

相關子查詢（correlated subquery） 涉及外部查詢的子查詢。

此外，如上給出的sql語句並不是最高效的辦法。後續會進行改進。

逐漸增加子查詢來建立查詢 用子查詢測試和調試查詢很有技巧性，特別是在這些語句的複雜性不斷增加的情況下更是如此。用子查詢建立（和測試）查詢的最可靠的方法是逐漸進行，這與MySQL處理它們的方法非常相同。首先，建立和測試最內層的查詢。然後，用硬編碼數據建立和測試外層查詢，並且僅在確認它正常後才嵌入子查詢。這時，再次測試它。對於要增加的每個查詢，重覆這些步驟。這樣做僅給構造查詢增加了一點點時間，但節省了以後（找出查詢為什麼不正常）的大量時間，並且極大地提高了查詢一開始就正常工作的可能性。

​ 如上提供了SQL的調試辦法。

聯結表

​ 將不同表中的數據合併就是聯結。

維護引用完整性 重要的是，要理解聯結不是物理實體。換句話說，它在實際的資料庫表中不存在。聯結由MySQL根據需要建立，它存在於查詢的執行當中。在使用關係表時，僅在關係列中插入合法的數據非常重要。回到這裡的例子，如果在products表中插入擁有非法供應商ID（即沒有在vendors表中出現）的供應商生產的產品，則這些產品是不可訪問的，因為它們沒有關聯到某個供應商。為防止這種情況發生，可指示MySQL只允許在products表的供應商ID列中出現合法值（即出現在vendors表中的供應商）。這就是維護引用完整性，它是通過在表的定義中指定主鍵和外鍵來實現的。

內連接(自然連接)的兩種使用方法：

​ 如果我們要查詢供應商所有的商品及其價格，我們可以：

SELECT vend_name, prod_name, prod_price
FROM vendors, products
WHERE vendors.vend_id = products.vend_id
ORDER BY vend_name, prod_name;

​ 還可以：

SELECT vend_name, prod_name, prod_price 
FROM vendors INNER JOIN products
ON vendors.vend_id = products.vend_id
ORDER BY vend_name, prod_name;

​ 其中前者是通常的where子句寫法，後者明確的表示這是個內連接。通過ON子句傳遞條件。

特別要註意是：where子句和on子句傳遞的條件必須不能忘記，否則直接返回一個笛卡爾積。

不要忘了WHERE子句 應該保證所有聯結都有WHERE子句，否則MySQL將返回比想要的數據多得多的數據。同理，應該保證WHERE子句的正確性。不正確的過濾條件將導致MySQL返回
不正確的數據。

叉聯結 有時我們會聽到返回稱為叉聯結（cross join）的笛卡兒積(cartesian product)的聯結類型。

反過來看一下子查詢中查詢訂購產品TNT2的客戶那個例子：

# 子查詢
SELECT cust_name, cust_contact
FROM customers
WHERE cust_id IN (SELECT cust_id
				  FROM orders
				  WHERE order_num IN (SELECT order_num
				  					  FROM orderitems
				  					  WHERE prod_id = 'TNT2'))
				  					  
# 內連接
SELECT cust_name, cust_contact
FROM customers, orders, orderitems
WHERE customers, cust_id = orders.cust_id
	AND orderitems.order_num = orders.order_num
	AND prod_id = 'TNT2';

多做實驗 正如所見，為執行任一給定的SQL操作，一般存在不止一種方法。很少有絕對正確或絕對錯誤的方法。性能可能會受操作類型、表中數據量、是否存在索引或鍵以及其他一些條件的影響。因此，有必要對不同的選擇機制進行實驗，以找出最適合具體情況的方法。

創建高級聯結

​ MySQL不僅支持給計算欄位取別名，同時也支持給表取別名。這樣做主要有以下兩個理由：

• 縮短SQL語句；
• 允許單條SELECT語句中多次使用相同的表。

​ 很容易想到的一點是：表別名只在查詢執行中使用，與列別名不一樣，表別名不返回到客戶機。

不同類型的連接：

​ 之前談到過內連接，這裡介紹其他的三種連接: 自連接，自然連接，外連接。其中外連接包括左外連接，右外連接，但是MySQL並沒有提供全連接的語法支持。不過是可以通過某些方法模擬出來全連接的。

自連接：

自連接通過給同一張表取別名的方式，多次連接同一張表。並且進行條件運算。如：

註意： MySQL中進行連接時，如果要選擇的計算欄位語義不明，必須給定表名限定的欄位名。如上所示。同時，既可以使用from <INNER/LEFT/RIGHT> JOIN on...也可以使用from ... where ...。

用自聯結而不用子查詢 自聯結通常作為外部語句用來替代從相同表中檢索數據時使用的子查詢語句。雖然最終的結果是相同的，但有時候處理聯結遠比處理子查詢快得多。應該試一下兩種方法，以確定哪一種的性能更好。

自然連接：

無論何時對錶進行聯結，應該至少有一個列出現在不止一個表中（被聯結的列）。標準的聯結（笛卡爾積）返回所有數據，甚至相同的列多次出現。自然聯結排除多次出現，使每個列只返回一次。

怎樣完成這項工作呢？答案是，系統不完成這項工作，由你自己完成它。自然聯結是這樣一種聯結，其中你只能選擇那些唯一的列。這一般是通過對錶使用通配符（SELECT *），對所有其他表的列使用明確的子集來完成的。下麵舉一個例子：

SELECT c.*, o.order_num, o.order_date,
						oi.prod_id, oi.quantity, oi.item_price
FROM customers AS c, orders AS o, orderitems AS oi
WHERE c.cust_id = o.cust_id
	AND oi.order_num = o.order_num
	AND prod_id = 'FB';

外部連接：

SELECT customers.cust_id, orders.order_num
FROM customers LEFT JOIN orders
	ON customers.cust_id = orders.cust_id;

MySQL左外連接，右外連接的語法支持分別是 LEFT JOIN和RIGHT JOIN。

沒有=操作符 MySQL不支持簡化字元=和=*的使用，這兩種操作符在其他DBMS中是很流行的。

外部聯結的類型 存在兩種基本的外部聯結形式：左外部聯結和右外部聯結。它們之間的唯一差別是所關聯的表的順序不同。換句話說，左外部聯結可通過顛倒FROM或WHERE子句中表的順序轉換為右外部聯結。因此，兩種類型的外部聯結可互換使用，而究竟使用哪一種純粹是根據方便而定

此外，聚集函數也可以在各種連接中使用。

使用連接和鏈接條件：

• 註意所使用的聯結類型。一般我們使用內部聯結，但使用外部聯結也是有效的。
• 保證使用正確的聯結條件，否則將返回不正確的數據。
• 應該總是提供聯結條件，否則會得出笛卡兒積。
• 在一個聯結中可以包含多個表，甚至對於每個聯結可以採用不同的聯結類型。雖然這樣做是合法的，一般也很有用，但應該在一起測試它們前，分別測試每個聯結。這將使故障排除更為簡單。

組合查詢

組合查詢/複合查詢：或者說，不同查詢結果的並（UNION）。

兩種情況：

• 在單個查詢中從不同的表返回類似結構的數據；
• 對單個表執行多個查詢，按單個查詢返回數據。

組合查詢和多個WHERE條件 多數情況下，組合相同表的兩個查詢完成的工作與具有多WHERE子句條件的單條查詢完成的工作相同。換句話說，任何具有多個WHERE子句的SELECT語句都可以作為一個組合查詢給出，在以下段落中可以看到這一點。這兩種技術在不同的查詢中性能也不同。因此，應該試一下這兩種技術，以確定對特定的查詢哪一種性能更好。

​ 可用UNION操作符來組合數條SQL查詢。利用UNION，可給出多條SELECT語句，將它們的結果組合成單個結果集。例如：篩選價格小於等於5的所有物品的一個列表，而且還想包括供應商1001和1002生產的所有物品（不考慮價格）。雖然可以很簡單的用where子句完成，但這裡也可以選用UNION操作符：

​

​ 使用UNION操作符的一些規則：

• UNION必須由兩條或兩條以上的SELECT語句組成，語句之間用關鍵字UNION分隔（因此，如果組合4條SELECT語句，將要使用3個UNION關鍵字）。
• UNION中的每個查詢必須包含相同的列、表達式或聚集函數（不過各個列不需要以相同的次序列出）。
• 列數據類型必須相容：類型不必完全相同，但必須是DBMS可以隱含地轉換的類型（例如，不同的數值類型或不同的日期類型）。

此外，UNION的預設行為會去重，如果想要覆蓋掉這種預設行為，可以：

SELECT vend_id, prod_id, prod_price
FROM products
WHERE prod_price <= 5
UNION ALL
SELECT vend_id, prod_id, prod_price
FROM products
WHERE vend_id IN (1001, 1002)

使用UNION ALL，MySQL不取消重覆的行。

UNION與WHERE UNION幾乎總是完成與多個WHERE條件相同的工作。UNION ALL為UNION的一種形式，它完成WHERE子句完成不了的工作。如果確實需要每個條件的匹配行全部出現（包括重覆行），則必須使用UNION ALL而不是WHERE。

最後，如果想要對UNION的結果排序，只能在最後一行加一行ORDER BY對整個表進行排序。不能以一種方式對一部分排序，以另一種方式對另一部分排序。並且，前面提到過的，可以對不同的表應用組合查詢，而不僅限於例子中的單表。

之前在創建高級連接的時候提到過，mysql並沒有語法層面的對full join的支持，學完UNION後，我們看看怎樣實現一個FULL JOIN:

可能第一次會是這樣：

SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
UNION
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id

然後這就沒問題了嗎？不對，一個比較通用的FULL JOIN是不會自動去重的，所以第二次你可能會寫出這樣的code：

(The query above works for special cases where a FULL OUTER JOIN operation would not produce any duplicate rows. The query above depends on the UNION set operator to remove duplicate rows introduced by the query pattern. We can avoid introducing duplicate rows by using an anti-join pattern for the second query, and then use a UNION ALL set operator to combine the two sets. In the more general case, where a FULL OUTER JOIN would return duplicate rows, we can do this:)

SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
UNION ALL
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id
WHERE t1.id IS NULL

但是這樣就完了嗎？不對，可以看到最後一行做了判斷t1.id IS NULL，對問題來了，怎麼保證t1.id是允許為空的，上面那個查詢一定會是語法正確的嗎？

參考：

Stack Overflow How to do a FULL OUTER JOIN in MySQL?

Stack Overflow Why does MySQL report a syntax error on FULL OUTER JOIN?

MySQL Reference Manual

全文本搜索

MySQL是一個多引擎架構的關係資料庫。但是並非所有的引擎都支持全文本搜索。

並非所有引擎都支持全文本搜索 MySQL支持幾種基本的資料庫引擎。並非所有的引擎都支持全文本搜索。兩個最常使用的引擎為MyISAM和InnoDB，前者支持全文本搜索，而後者不支持。這就是為什麼例子中絕大多數表格創建時使用的都是InnoDB ， 而有一個樣例表（productnotes表）卻使用MyISAM的原因。如果你的應用中需要全文本搜索功能，應該記住這一點。

註意，在新版MySQL5.6.24中也允許在InnoDB上建全文本索引了。

那為什麼有了通配操作符和正則表達式之後還需要全文本搜索呢？這是因為雖然這些機制非常有用，但有幾個重要的限制：

• 性能——通配符和正則表達式匹配通常要求MySQL嘗試匹配表中所有行（而且這些搜索極少使用表索引）。因此，由於被搜索行數不斷增加，這些搜索可能非常耗時。
• 明確控制——使用通配符和正則表達式匹配，很難（而且並不總是能）明確地控制匹配什麼和不匹配什麼。例如，指定一個詞必須匹配，一個詞必須不匹配，而一個詞僅在第一個詞確實匹配的情況下才可以匹配或者才可以不匹配。（我表示懷疑，正則表達式是萬能的）
• 智能化的結果——雖然基於通配符和正則表達式的搜索提供了非常靈活的搜索，但它們都不能提供一種智能化的選擇結果的方法。例如，一個特殊詞的搜索將會返回包含該詞的所有行，而不區分包含單個匹配的行和包含多個匹配的行（按照可能是更好的匹配來排列它們）。類似，一個特殊詞的搜索將不會找出不包含該詞但包含其他相關詞的行。

接下來就是重頭戲了：所有這些限制以及更多的限制都可以用全文本搜索來解決。在使用全文本搜索時，MySQL不需要分別查看每個行，不需要分別分析和處理每個詞。MySQL創建指定列中各詞的一個索引，搜索可以針對這些詞進行。這樣，MySQL可以快速有效地決定哪些詞匹配（哪些行包含它們），哪些詞不匹配，它們匹配的頻率，等等。

​ 為了進行全文本搜索，必須索引被搜索的列，而且要隨著數據的改變不斷地重新索引。在對錶列進行適當設計後，MySQL會自動進行所有的索引和重新索引。在索引之後，SELECT可與Match()和Against()一起使用以實際執行搜索。

​ 一般在創建表時啟用