MySQL 必知必會

1.瞭解SQL

資料庫（database）儲存有組織的資料的容器（通常是一個檔案或一組檔案）。

表 (table）某種特定型別資料的結構化清單。

模式（schema）關於資料庫和表的佈局及特性的資訊。

列（column）表中的一個欄位。所有的表都是由一個或多個列組成的。

行（row）表中的一個記錄。

資料型別（datatype）所容許的資料的型別。

主鍵（primary key)一列（或一組列），其值能夠唯一區分表中每個行。

SQL（發音為字母S-Q-L或sequel）是結構化查詢語言（Structured Query Language)的縮寫。SQL是一種專門用來與資料庫通訊的語言。

表中的任何列都可以作為主鍵，只要它滿足以下條件：

• 任意兩行都不具有相同的主鍵值；
• 每個行都必須具有一個主鍵值（主鍵列不允許 NULL 值）。

SQL 有如下的優點

• SQL 不是某個特定資料庫供應商專有的語言。幾乎所有重要的 DBMS 都支援 SQL ，所以，學習此語言使你幾乎能與所以資料庫打交道。
• SQL 簡單易學。它的語句全都是由描述性很強的英語單片語成，而且這些單詞的數目不多。
• SQL 儘管看上去很簡單，但它實際上使一種強有力的語言，靈活使用其語言元素，可以進行非常複雜和高階的資料庫操作。

2.運算元據庫表

show databases;                   // 返回當前資料庫的列表
use crash;                        // 選擇使用 crash 資料庫
show tables;                      // 返回當前選擇資料庫內的表的列表
show columns from customers;      // 顯示 customers 表列資訊
show status;                      // 用於顯示廣泛的伺服器狀態資訊
show create database 和 show create table 分別用來顯示建立資料庫或表的mysql語句
show errors 和 show warnings 用來顯示伺服器錯誤或警告資訊

3.檢索資料

檢索單個列
// 利用 select 語句從 products 表中檢索一個名為 prod_name的列
select prod_name from products;

檢索多個列
// 在選擇多個列時，一定要在列明之間加上逗號，最後一個列名後不加
select prod_id, prod_name, prod_price from products;

檢索所有列
// 使用萬用字元* 檢索所有列
select * from products;

檢索不同的行
// 使用 distinct 關鍵字，將返回不同的值
// distinct 將應用於所有列，而不僅僅是它前置的列
select distinct vend_id from products;

限制結果
/*
* limit 5 , 5 返回行5開始的5行。
* 第一個數為開始位置，第二個數為要檢索的行數
* 帶一個值的 limit 總是從第一行開始，給出的數為返回的行數。
* 帶兩個值的 limit 可以指定從行號為第一個值的位置開始。
*/
select prod_name from products limit 5 , 5;

4.排序檢索資料

// 排序資料
// order by 對 prod_name列以字母順序排序資料
select prod_name from products order by prod_name;

// 按多個列排序
// order by 指定列名，列名之間用逗號分隔
select prod_id , prod_price , prod_name from products order by prod_price , prod_name;

// 指定排序方向
// order by 預設使用升序，為了進行降序排序，可以指定 desc 關鍵字
select prod_id , prod_price , prod_name from products order by prod_price desc;

/*
* 如果想在多個列上進行降序，必須要對每個列指定 desc 關鍵字
* order by 應該位於 from 之後。如果使用 limit ，它必須位於 order by 之後
*/

5.過濾資料

// 資料根據 where 子句中指定的搜尋條件進行過濾
select prod_name , prod_price from products where prod_price = 2.1;

// between
select * from prod_price between 5 and 10;

// IS NULL
select cust_id from customers where cust_email IS NULL;

where子句運算子

運算子	說明
=	等於
<>	不等於
!=	不等於
<	小於
<=	小於等於
>	大於
>=	大於等於
between	在指定的兩個值之間

6.資料過濾

// and | or 運算子用來聯結或改變 where 子句中的子句的關鍵字。也稱邏輯運算子
select prod_name , prod_price from products where vend_id = 1002 or vend_id ==1003 and prod_price >= 10;

// 在 where 子句中使用圓括號明確分組運算子。使用圓括號沒什麼壞處，還能消除歧義。
select prod_name , prod_price from products where （vend_id = 1002 or vend_id ==1003） and prod_price >= 10;

// in 運算子用來指定條件範圍，範圍中的每個條件都可以進行匹配
select prod_name , prod_price from products where vend_id in (1002,1003);

// not 運算子有且只有一個功能，那就是否定它之後所跟的任何條件
// not 支援in、between和exists
select prod_name , prod_price from products where vend_id not in (1002,1003);

7.用萬用字元過濾

萬用字元（wildcard）用來匹配值的一部分的特殊字元

搜尋模式（search pattern）由字面值、萬用字元或兩者組合構成的搜尋條件。

百分號（%）萬用字元，% 表示任何字元出現任意次數。

區分大小寫 根據MySQL的配置方式，搜尋可以是區分大小寫的。

// 找出所有以jet起頭的產品
select prod_id , prod_name from products where prod_name like 'ject%';

下劃線（ ）萬用字元 下劃線的用途跟 % 一樣，但只匹配單個字元而不是多個字元

與 % 能匹配 0 個字元不一樣， 總是匹配一個字元，不能多不能少。

// 找出後面跟有指定文字內容的產品
select prod_id , prod_name from products where prod_name like '_ ton anvil';

使用萬用字元技巧

正如所見，MySQL的萬用字元很有用。但這種功能是有代價的：萬用字元搜尋的處理一般要比其他搜尋所花時間更長。

• 不要過度使用萬用字元。如果其他運算子能達到相同的目的，應該使用其他運算子。
• 在確實需要使用萬用字元時，除非絕對有必要，否則不要把它們用在搜尋模式的開始處。把萬用字元置於搜尋模式的開始出，搜尋起來是最慢的。
• 仔細注意萬用字元的位置。如果放錯地方，可能不會返回想要的資料。

8.用正規表示式進行搜尋

使用正規表示式來更好的控制資料過濾。

// 基本字元過濾
// 檢索列 prod_name 包含文字 1000 的所有行
select prod_name from products where prod_name regexp '1000';

// ( . )點匹配任意一個字元
select prod_name from products where prod_name regexp '.000';

// or 進行匹配 , 匹配其中之一
select prod_name from products where prod_name regexp '1000|2000';

// [] 匹配幾個字元之一
// 事實上，正規表示式[123]為[1|2|3]的縮寫，也可以使用後者

// 匹配範圍 [1-9] 匹配1到9開頭
select prod_name from products where prod_name regexp '[1-9] Ton';

// 匹配特殊字元  要用 \ 轉義
// 為了匹配特殊字元，必須用 \\ 為前導。\\- 表示查詢- ，\\. 表示查詢.
select vend_name from vendors where vend_name regexp '\\.';

空白元字元

元字元	說明
\\f	換頁
\\n	換行
\\r	回車
\\t	製表
\\v	縱向製表

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

多數正規表示式實現使用單個反斜槓跳脫字元，以便能使用這些字元本身。
但MySQL要求兩個反斜槓（MySQL自己解釋一個，正規表示式庫解釋另外一個）。

匹配字元類

類 1	說明
[ :alnum: ]	任意字母和數字（同[a-zA-Z0-9]）
[ :alpha: ]	任意字元（同[a-zA-Z]）
[ :blank: ]	空格和製表（同[\\t]）
[ :cntrl: ]	ASCII控制字元（ASCII 0 到 31 和 127）
[ :digit: ]	任意數字（同[0-9]）
[ :graph: ]	與[ :print: ] 相同，但不包括空格
[ :lower: ]	任意小寫字母（同[a-z]）
[ :print: ]	任意可列印字元
[ :punct: ]	既不在[ :alnum: ]又不在[ :cntrl: ] 中的任意字元
[ :space: ]	包括空格在內的任意空白字元（同[\\f\\n\\r\\t\\v]）
[ :upper: ]	任意大寫字母
[ :xdigit: ]	任意十六進位制數字（同[a-fA-F0-9]）

匹配多個例項

目前為止使用的所有正規表示式都試圖匹配單次出現。

有時候需要對匹配的數目進行更強的控制。

例如，你可能想尋找一個單詞並且還能夠適應一個尾隨的s，等等

重複元字元

元字元	說明
*	0 個或多個匹配
+	1 個或多個匹配（ 等於{1，} ）
？	0 個或1個匹配（ 等於{0，1} ）
{n}	指定數目的匹配
{n,}	不少於指定數目的匹配
{n,m}	指定數目的範圍（ m 不超過255 ）

// [0-9] 匹配任意數字，sticks? 匹配 stick 和 sticks
// 沒有 ? , 匹配 stick 和 sticks 會非常困難
select prod_name from products where prod_name regexp '\\([0-9] sticks?\\)';

定位符

目前為止的所有例子都是匹配一個串中的任意的文字。為了匹配特定位置的文字，需要使用定位符

定位元字元

元字元	說明
^	文字的開始
$	文字的結尾
[ [ :<: ] ]	詞的開始
[ [ :>: ] ]	詞的結尾

// 只有開始為任意數字或 . 才會匹配
select prod_name from products where prod_name regexp '^[0-9\\.]';

like 與 regexp 的區別

select prod_name from products where prod_name like '1000';
select prod_name from products where prod_name regexp '1000';

執行上述兩條語句，會發現第一條語句不返回資料，而第二條語句返回資料。為什麼呢？

這是因為 like 匹配整個列，而匹配的文字在列值中出現，like 將不會找到它
而 regexp 在列值內進行匹配，如果被匹配的文字在列值中出現，regexp 將會找到它

而當 regexp 使用定位符 ^ 開始表示式 $ 結束表示式
regexp 的作用就和 like 一樣了

// 簡單的正規表示式測試
select 'hello' regexp '[0-9]';

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

9.建立計算欄位

欄位（field）基本上與列（ column ）的意思相同，不過資料庫列一般稱為列。

拼接（ concatenate ）將值聯結到一起構成單個值。

• 如果想在一個欄位中既顯示公司名，又顯示公司地址，但這兩個資訊一般包含在不同的表列中。
• 列資料是大小寫混合的，但報表程式需要把所有資料按大寫表示出來
• 需要根據表資料進行總數、平均數計算或其他計算。

  在上述例子中，儲存在表中的資料都不是應用程式所需要的。我們需要直接從資料庫中檢索出轉換、計算或格式化過的資料；而不是檢索出資料，然後再在客戶機應用程式或報告程式中重新格式化。
  
  這就是計算欄位發揮作用的所在了。

// concat()函式，拼接兩個列
select concat(vend_name,'(',vend_country,')') from vendors;
輸出：ACME ( USA )

從前面輸出中可以看到，select語句拼接地址欄位工作得很好。

但此新計算列的名字是什麼呢？實際上它沒有名字，它只是一個值。

但是，一個未命名的列不能用於客戶機應用中，因為客戶機沒有辦法引用它。

別名（alias）是一個欄位或值得替換名。別名用 AS 關鍵字賦予。

// 將計算好的值賦予 vend_name
select concat(vend_name,'(',vend_country,')') as vend_title from vendors;

// 執行算術計算
// 彙總物品得價格（單價乘以訂購數量）賦予別名 expanded_price
select prod_id , quantity , item_price , quantity*item_price as expanded_price from orderitems;

10.使用資料處理函式

SQL支援利用函式來處理資料。

常用的文字處理函式

函式	說明
left()	返回串左邊的字元
length()	返回串的長度
locate()	找出串的一個子串
lower()	將串轉換為小寫
ltrim()	去掉串左面的空格
right()	返回串右邊的字元
rtrim()	去掉串右邊的空格
soundex()	返回串的SOUNDEX值
substring()	返回子串的字元
upper()	將串轉換為大寫

// SOUNDEX()函式 SOUNDEX是將一個將任何文字串轉換為描述其語音表示的字母數字模式的演算法。
// SOUNDEX考慮了類似發音字元和音節，使得能對串進行發音比較而不是字母比較。

// 使用SOUNDEX（）函式搜尋，它匹配所有發音類似於 Y . Lie 的聯絡名
select cust_name , cust_contact from customers where cust_contact = 'Y . Lie';
// 查詢出 Y Lee

日期和時間處理函式

函式	說明
AddDate()	增加一個日期（天、周等）
AddTime()	增加一個日期（時、分等）
CurDate()	返回當前日期
CurTime()	返回當前時間
Date()	返回日期時間的日期部分
DateDiff()	計算兩個日期之差
Date_Add()	高度靈活的日期運用函式
Date_Format()	返回一個格式化的日期或時間串
Day()	返回一個日期的天數部分
DayOfWeek()	對於一個日期，返回對應的星期幾
Hour()	返回一個時間的小時部分
Minute()	返回一個時間的分鐘部分
Month()	返回一個時間的月份部分
Now()	返回當前日期和時間
Second()	返回一個日期的秒部分
Time()	返回一個日期時間的時間部分
Year（）	返回一個日期的年份部分

// 日期比較
select cust_id , order_num form orders where Date(order_date) = ' 2015-09-01';

常用數值處理函式

函式	說明
Abs()	返回一個數的絕對值
Cos()	返回一個角度的餘弦
Exp()	返回一個數的指數值
Mod()	返回除操作的餘數
Pi()	返回圓周率
Rand()	返回一個隨機數
Sin()	返回一個角度的正弦
Sqrt()	返回一個數的平方根
Tan()	返回一個角度的正切

11.彙總資料

聚集函式（ aggregate function ）執行在行組上，計算和返回單個值的函式。

SQL聚集函式

函式	說明
AVG()	返回某列的平均值
COUNT()	返回某列的行數
MAX()	返回某列的最大值
MIN()	返回某列的最小值
SUM()	返回某列值之和

12.分組資料

分組允許把資料分為多個邏輯組，以便能對每個組進行聚集計算。

理解分組最好的辦法是看一個例子：

// 返回每個供應商能提供的產品數目
select vend_id , count(*) as num_prods from products group by vend_id;

vend_id	num_prods
1001	3
1002	2
1005	7

在具體使用 GROUP BY 子句前，需要知道一些重要的規定。

• GROUP BY子句可能包含任意數目的列。這使得能對分組進行巢狀，為資料分組提供更細緻的控制。
• 如果在 GROUP BY 子句中巢狀了分組，資料將在最後規定的分組上進行彙總。換句話說，在建立分組時，指定的所有列都一起計算（所以不能從個別的列取回資料）。
• GROUP BY 子句中列出的每個列都必須是檢索列或有效的表示式（但不是能是聚集函式）。如果在 SELECT 中使用表示式，則必須在 GROUP BY 子句中指定相同的表示式。不能使用別名。
• 除聚集計算語句外，SELECT 語句中的每個列都必須在 GROUP BY 子句中給出。
• 如果分組列中具有 NULL 值，則 NULL 將作為一個分組返回。如果列中有多行 NULL值，它們將分為一組。
• GROUP BY 子句 必須出現在 WHERE 子句後， ORDER BY 子句之前。

// 使用 ROLLUP 關鍵字，可以得到每個分組以及每個分組彙總級別（針對每個分組）的值
select vend_id , count(*) as num_prods from products group by vend_id with rollup;

with rollup 的用法

過濾分組

除了能用 GROUP BY 分組資料外， MySQL 還允許過濾分組，規定包括那些分組，排除那些分組。

但是 WHERE 不能完成任務，因為 WHERE 過濾指定的是行而不是分組。

事實上，WHERE 沒有分組的概念。

MySQL 為此提供了另外的子句，那就是 having 。

事實上，目前為止所學過的所有型別的 WHERE 子句都可以用 HAVING 來代替。

唯一的差別是 WHERE 過濾行，而 HAVING 過濾分組。

// 過濾兩個訂單以下的分組
select cust_id , count(*) as order from orders group by cust_id having count(*) >= 2;

HAVING 和 WHERE 的差別

這裡有另一種理解方法，WHERE在資料分組前進行過濾，HAVING 在資料分組後進行過濾。

WHERE 排除的行不包括分組中，從而影響 HAVING 子句中基於這些值過濾的分組。

// 過濾訂單價錢小於10元，兩個訂單以下的分組
SELECT vend_id , COUNT(*) as num_prods from products where prod_price >= 10 GROUP BY vend_id HAVING COUNT(*) >= 2;

不要忘記 order by 一般在使用 group by 子句時，應該也給出 order by 子句。這是保證資料正確排序的唯一方法。千萬不要僅依賴 group by 排序資料。

//  排序按照 group by 指定的 order_num排序
select order_num , SUM(quantity*item_price) as ordertotal from orderitems group by order_num having SUM(quantity*item_price) >= 50;

// 這時候想用 ordertotal 訂單價格排序，使用 order by 就好了
select order_num , SUM(quantity*item_price) as ordertotal from orderitems group by order_num having SUM(quantity*item_price) >= 50 order by ordertotal;

select 子句使用必須遵循的次序

子句	說明	是否必須使用
select	要返回的列或表示式	是
from	從中檢索資料的表	僅在從表選擇資料時使用
where	行級過濾	否
group by	分組說明	僅在按組計算聚集時使用
having	組級過濾	否
order by	輸出排序順序	否
limit	要檢索的行數	否

13.使用子查詢

子查詢（subquery），即巢狀在其他查詢中的查詢。

訂單儲存在兩個表中。

對於包含訂單號、客戶ID、訂單日期的每個訂單，order表儲存一行。

各訂單的物品儲存在相關的 orderitems 表中。

orders 表不儲存客戶資訊。它只儲存客戶的ID。

實際的客戶資訊儲存在 customers 表中。

1. 檢索包含物品 TNT2的所有訂單的編號。

   SELECT order_num FROM orderitems WHERE prod_id = 'TNT2';

   order_num
   20005
   20007

2. 檢索具有前一步列出的訂單編號的所有客戶的ID。

   SELECT cust_id FROM orders WHERE order_num IN (20005,20007);

   cust_id
   10001
   10004

3. 檢索前一步驟返回的所有客戶ID的客戶資訊。

   SELECT cust_name,cust_contact FROM customers WHERE cust_id IN (10001,10004);

   cust_name	cust_contact
   Coyote Inc.	Y Lee
   Yosemite	Y Sam

   上述每個步驟都可以單獨作為一個查詢來執行。可以把一條 SELECT 語句返回的結果用於另一條 SELECT 語句的 WHERE 子句。
   
   也可以使用另一條 SELECT 語句的 WHERE 子句。

   SELECT cust_name,cust_contact FORM customers WHERE cust_id IN (
   SELECT cust_id 
   FROM orders
   WHERE order_num IN (
   SELECT order_num 
   FROM orderitems
   WHERE prod_id = 'TNT2'
   )
   );                                                                                                                  

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

13.使用子查詢

列必須匹配 在 WHERE 子句中使用子查詢，應該保證 SELECT 語句具有與 WHERE 子句中相同數目的列。通常子查詢將返回單個列並且與單個列匹配，但如果需要也可以使用多個列。

作為計算欄位使用子查詢

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

子查詢中的 WHERE 子句與前面的 WHERE子句稍有不同，因為它使用了完全限定列名。

子查詢SQL 與當前 customers表中檢索 cust_id。

這種型別叫做相關子查詢。

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

14.聯結表

本作品採用《CC 協議》，轉載必須註明作者和本文連結