《MySQL 必知必會》讀書總結

這是 《MySQL 必知必會》 的讀書總結。也是自己整理的常用操作的參考手冊。

? ?

使用 MySQL

• 連接到 MySQL

shell>mysql -u root -p

Enter password:******

• 顯示數據庫

mysql>SHOW DATABASES;

• 選擇數據庫

mysql>USE mytest;

• 顯示數據庫中的表

mysql>SHOW TABLES;

• 顯示表列

mysql>SHOW COLUMNS FROM tmall_user;

mysql>DESCRIBE tmall_user;

第二種方法與第一種方法效果相同

• SHOW 的其他用法

mysql>SHOW STATUS;

顯示服務器的狀態信息

mysql>SHOW CREATE DATABASE mytest;

顯示創建數據庫的語句

mysql>SHOW CREATE TABLE tmall_user;

顯示創建表的語句

mysql>SHOW GRANTS;

顯示授權用戶的安全權限

mysql>SHOW ERRORS;

顯示服務器錯誤

mysql>SHOW WARNINGS;

顯示服務器警告信息

? ?

基本查詢（SELECT 語句）

• 查詢某列

mysql>SELECT username FROM tmall_user;

• 查詢多列

mysql>SELECT username, password, time FROM tmall_user;

• 查詢所有列

mysql>SELECT * FROM tmall_user;

• 查詢不同行

mysql>SELECT DISTINCT password FROM tmall_user;

注：DISTINCT 關鍵字應用於所有列。

• 限制結果

mysql>SELECT username FROM tmall_user LIMIT 5

返回5行

mysql>SELECT username FROM tmall_user LIMIT 5,5

返回從第5行開始的5行

注：查詢結果的第一行為行0，因此，LIMIT 1,1 將檢索出第二行

新語法：LIMIT 4 OFFSET 3 從行3開始取4行，就像 LIMIT 3,4 一樣

• 使用完全限定的列名、表名

mysql>SELECT tmall_user.username FROM mytest.tmall_user;

? ?

排序數據（ORDER BY 子句）

• 排列數據

mysql>SELECT username FROM tmall_user

->ORDER BY username;

對 username 結構列以字母順序排列數據

• 按多個列排序

mysql>SELECT prod_id, prod_price, prod_name FROM products

->ORDER BY prod_peice, prod_name;

查詢三個列，先按照價格排序，然後再按名稱排序

• 指定排序方向

mysql>SELECT prod_id, prod_price, prod_name FROM products

->ORDER BY prod_price DESC

默認的排序是升序排序，DESC 關鍵字指定降序排序（價格由高到低）

• 多個列排序並指定方向

mysql>SELECT prod_id, prod_price, prod_name FROM products

->ORDER BY prod_price DESC, prod_name;

以降序排序產品（價格從高到低），然後再對產品名排序。

注：DESC 關鍵字之應用到直接位於其前面的列名，如果想在多個列上進行降序排序，必須對每個列指定 DESC 關鍵字。

• 使用 ORDER BY 和 LIMIT 的組合，找出一個列中最高或最低的值

mysql>SELECT prod_price FROM products

->ORDER BY prod_price DESC

->LIMIT 1;

查詢價格最貴的那個商品

? ?

查詢條件（WHERE 子句）

• 查詢滿足條件的行

mysql>SELECT prod_name, prod_price FROM products

->WHERE prod_price = 2.50

返回 prod_price 值為 2.50 的行

• WHERE 子句操作符

=

等於

<>

不等於

!=

不等於

<

小於

<=

小於等於

>

大於

>=

大於等於

BETWEEN

在指定的兩個值之間

• 一些操作符的例子

mysql>SELECT prod_name, prod_price FROM products

->WHERE prod_price < 10;

列出價格小於 10 的所有產品

mysql>SELECT vend_id, prod_name FROM products

->WHERE vend_id <> 1003;

列出不是由供應商1003制造的所有產品

mysql>SELECT prod_name, prod_price FROM products

->WHERE prod_price BETWEEN 5 AND 10

查詢價格在 5 和 10 之間的所有產品

mysql>SELECT prod_name FROM products

->WHERE prod_price IS NULL;

查詢價格列值為 NULL 的行。（空值=值為NULL）

• 組合 WHERE 子句（邏輯操作符）

AND

與

OR

或

• IN 操作符

mysql>SELECT prod_name FROM products

->WHERE vend_id IN (1002, 1003)

->ORDER BY prod_name;

查詢供應商1002和1003制造的所有產品

IN WHERE 子句中用來指定要匹配值得清單的關鍵字，功能與 OR 相當

• NOT 操作符

mysql>SELECT prod_name FROM products

->WHERE vend_id NOT (1002, 1003)

->ORDER BY prod_name;

查詢除1002和1003之外的所用供應商制造的產品

NOT WHERE 子句中用來否定後跟條件的關鍵字，MySQL中的 NOT 支持使用 對 IN、BETWEEN 和 EXISTS 子句取反

• LIKE 操作符（通配符過濾）

%

任何字符出現任意次數，不能匹配 NULL

_

任何一個字符

mysql>SELECT prod_name FROM products

->WHERE prod_name LIKE 's%e';

查詢以s開頭以e結尾的產品

? ?

? ?

函數

• 文本處理函數

Left()

返回串左邊的字符

Length()

返回串的長度

Locate()

找出串的一個子串

Ltrim()

去掉串左邊的空格

Right()

返回串右邊的字符

Rtrim()

去掉串右邊的空格

Soundex()

返回串SOUNDEX值（根據發音比較而不是字母比較）

SubString()

返回子串的字符

Upper()

轉換為大寫

mysql>SELECT name FROM tmall_user

->WHERE SOUNDEX(name) = SOUNDEX('雷君')

返回：雷軍

• 時間和日期處理函數

AddDate()

??

AddTime()

??

CurDate()

返回當前日期

CurTime()

返回當前時間

Date()

返回日期時間的日期部分

DateDiff()

計算兩個日期之差

Date_Add()

高度靈活的日期運算函數

Date_Format

返回一個格式化的日期或時間串

Day()

返回日期的天數部分

DayOfWeek()

返回日期對應的星期數

Hour()

返回時間的小時部分

Minute()

返回時間的分鐘部分

Month()

返回日期的月份部分

Now()

返回當前日期和時間

Second()

返回時間的秒數部分

Time()

返回日期時間的時間部分

Year()

返回日期的年份部分

mysql>SELECT username FROM tmall_user

->WHERE DATE(time) = '2016-09-21';

使用日期比較過濾查詢結果

mysql>SELECT count(id) FROM tmall_user

->WHERE DATE(time) BETWEEN '2016-09-01' AND '2016-09-30';

查詢2016年9月注冊的用戶數量

mysql>SELECT count(id) FROM tmall_user

->WHERE Year(time) = 2016 AND Month(time) = 9;

效果與上例相同

• 數值處理函數

Abs()

??

Cos()

??

Mod()

??

Pi()

??

Rand()

??

Sin()

??

Sqrt()

??

Tan()

??

? ?

匯總數據（聚集函數）

• 聚集函數

AVG()

返回某列的平均值

COUNT()

返回某列的行數

MAX()

返回某列的最大值

MIN()

返回某列的最小值

SUM()

返回某列值之和

• AVG() 函數

mysql>SELECT AVG(prod_price) FROM products;

返回所有products表中產品的平均價格

mysql>SELECT AVG(prod_price) FROM products

->WHERE vend_id = 1003;

返回特定供應商所提供產品的平均價格

AVG() 函數只應用於單個列，忽略列值為NULL的行

• COUNT() 函數

mysql>SELECT COUNT(*) FROM customers;

對所有行進行計數，包括列值為NULL的行

mysql>SELECT COUNT(name) FROM tmall_user;

只對有昵稱的行進行計數，忽略列值為NULL的行

• MAX()函數和MIN()函數

mysql>SELECT MAX(prod_price) FROM products;

返回最貴的物品的價格

MAX()函數一般用於找出最大的數值或日期值，但也可以返回任意列中的最大值。在用於文本數據時，按照相應順序排序，返回最後一行。

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

MIN()函數與MAX()函數功能相反。

• SUM()函數

mysql>SELECT SUM(quantity) FROM orderitems

->WHERE order_num = 20005;

查詢所訂購物品的總數（所有quantity之和）

mysql>SELECT SUM(item_price*quantity) FROM orderitems

->WHERE order_num = 20005;

查詢所訂購物品的總金額。

• 聚集不同值（DISTINCT）

mysql>SELECT AVG(DISTINCT prod_price) FROM products

->WHERE vend_id = 1003;

返回特定供應商的產品的平均價格，平均值只考慮各個不同的價格。

• 組合聚集函數

mysql>SELECT COUNT(*), MIN(prod_price), MAX(prod_price),

->AVG(prod_price) FROM products;

單條SELECT語句執行了4個聚集計算，返回4個值

? ?

? ?

? ?

分組數據（GROUP BY 子句和 HAVING 子句）

• 創建分組

mysql>SELECT vend_id, COUNT(*) FROM products

->GROUP BY vend_id;

按vend_id排序並分組數據，COUNT()會對每個 vend_id 分組計算 num_prods 一次而不是對整個表計算。

• 過濾分組

HAVING 非常類似於 WHERE。HAVING 支持所有 WHERE 操作符。他們之間的差別是WHERE 過濾行，而HAVING 過濾分組。

另一種理解：WHERE 在數據分組前進行過濾，HAVING 在數據分組後進行過濾。

mysql>SELECT cust_id, COUNT(*) FROM orders

->GROUP BY cust_id

->HAVING COUNT(*) >= 2;

過濾兩個以上的訂單的分組

mysql>SELECT vend_id, COUNT(*) FROM products

->WHERE prod_price >=10

->GROUP BY vend_id

->HAVING COUNT(*) >= 2;

列出具有兩個以上，價格為10以上的產品的供應商

mysql>SELECT order_num, SUM(quantity*item_price) AS ordertotal

->FROM orderitems

->ORDER BY order_num

->HANVING SUM(quantity*item_price) >= 50

->ORDER BY ordertoal;

GROUP BY 子句按照訂單號分組數據，以便SUM(*)返回總計訂單價格。HAVING 子句過濾數據，使得返回總計訂單價格>=50的訂單。最後用ORDER BY排序輸出。

• SELECT 子句順序

SELECT

要返回的列或表達式

FROM

要查詢的表

WHERE

行過濾

GROUP BY

分組

HAVING

分組過濾

ORDER BY

輸出排序順序

LIMIT

行數

? ?

子查詢

• 查詢訂購物品TNT2的所有客戶

mysql>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'));

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

• 作為計算字段使用子查詢

mysql>SELECT cust_name, cust_state, (SELECT COUNT(*)FROM orders

-> WHERE orders.cust_id = customers.cust_id) AS orders

->FROM customers

->ORDER BY cust_name;

查詢customers表中每個客戶的訂單總數。訂單與相應客戶ID存儲在orders表中

? ?

聯結表

• 創建聯結

mysql>SELECT vend_name, prod_name, prod_price

->FROM vendors, products

->WHERE vendors.vend_id = products.vend_id

->ORDER BY vend_name, prod_name;

這兩個表用WHERE子句聯結，需要完全限定列名。

由沒有聯結條件的表關系返回的結果為笛卡爾積，檢索出的行數=表1的行數×表2的行數。應該保證所有聯結都有WHERE子句。

• 內部聯結（等值聯結）

mysql>SELECT vend_name, prod_name, prod_price

->FROM vendors INNER JOIN products

->ON vendors.vend_id = products.vend_id;

兩個表之間的關系以 INNER JOIN 指定，ON 的條件與上例 WHERE 條件相同

• 聯結多個表（內部聯結）

mysql>SELECT prod_name, vend_name, prod_price, quantity

->FROM orderitems, products, vendors

->WHERE products.vend_id = vendors.vend_id

-> AND orderitems.prod_id = products.prod_id

-> AND order_num = 20005;

mysql>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';

使用聯結查詢訂購物品TNT2的所有客戶

• 使用表別名

mysql>SELECT cust_name, cust_contact

->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 = 'TNT2';

• 自聯結

mysql>SELECT prod_id, prod_name FROM products

->WHERE vend_id = (SELECT vend_id FROM products

-> WHERE prod_id = 'DTNTR');

mysql>SELECT p1.prod_id, p1.prod_name

->FROM products AS p1, products AS p2

->WHERE p1.vend_id = p2.vend_id AND p2.prod_id = 'DTNTR'

選擇某物品的供應商所生產的其他物品，第一種方法使用子查詢，第二種方法使用自聯結，推薦用自聯結而不用子查詢。

使用表別名的主要原因是能在單條SELECT語句中不止一次引用相同的表

• 自然聯結

自然聯結排除多次出現，使每個列只返回一次。這一般是通過對第一個表使用SELECT *，對所有其他的表使用明確的子集來完成的。

mysql>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';

• 外部聯結

mysql>SELECT customers.cust_id, orders.order_num

->FROM customers INNER LEFT OUTER JOIN orders

->ON customers.cust_id = orders.cust_id;

查詢所有客戶及其訂單，包括沒有訂單的客戶,

LEFT 指從左邊的表選擇所有行

RIGHT 指從右邊的表選擇所有行

• 帶聚集函數的聯結

mysql>SELECT customers.cust_name, customers.cust_id,

-> COUNT(orders.order_num) AS num_ord

->FROM customers INNER JOIN orders

-> ON customers.cust_id = orders.cust_id

->GROUP BY customers.cust_id;

檢索所有客戶及每個客戶所下的訂單數

mysql>SELECT customers.cust_name, customers.cust_id,

-> COUNT(orders.order_num) AS num_ord

->FROM customers LEFT OUTER JOIN orders

-> ON customers.cust_id = orders.cust_id

->GROUP BY customers.cust_id;

檢索所有客戶及每個客戶所下的訂單數（包括沒有下訂單的客戶）

• 使用聯結的要點

• 保證正確的聯結條件
• 應該總是提供聯結條件，否則會得出笛卡爾積
• 如果一個聯結中包含多個表，甚至每個聯結采用不同的聯結類型，應當先分別測試每個聯結，這將使故障排除更為簡單。

? ?

組合查詢（UNION 操作符）

? ?

全文本搜索

? ?

插入數據

? ?

更新和刪除數據

• 更新數據（UPDATE 語句）

不要省略WHERE子句，再使用UPDATE時一定要注意細心。因為稍不注意，就會更新表中所有行。

UPDATE customers SET cust_email = '[email protected]'

WHERE cust_id = 10005;

更新多個列

UPDATE customers SET cust_email = 'The Fudds', cust_email = '[email protected]'

WHERE cust_id = 10005;

??

? ?

? ?

規范

• 對 SQL 關鍵字使用大寫，而對所有列和表名使用小寫，這樣做使代碼更易於閱讀和調試。
• 任何時候使用具有 AND 和 OR 操作符的 WHERE 子句，都應該使用圓括號明確地分組操作符。
• 不要過度使用通配符，通配符通常消耗更多的時間。
• 使用聚集函數來匯總數據，比你在自己的軟件中計算要快得多，這些函數是高效設計的。
• 用逐漸子查詢來建立查詢