這是 《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;
第二種方法與第一種方法效果相同
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 關鍵字。
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 的行
=
等於
<>
不等於
!=
不等於
<
小於
<=
小於等於
>
大於
>=
大於等於
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)
AND
與
OR
或
mysql>SELECT prod_name FROM products
->WHERE vend_id IN (1002, 1003)
->ORDER BY prod_name;
查詢供應商1002和1003制造的所有產品
IN WHERE 子句中用來指定要匹配值得清單的關鍵字,功能與 OR 相當
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 子句取反
%
任何字符出現任意次數,不能匹配 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()
返回某列值之和
mysql>SELECT AVG(prod_price) FROM products;
返回所有products表中產品的平均價格
mysql>SELECT AVG(prod_price) FROM products
->WHERE vend_id = 1003;
返回特定供應商所提供產品的平均價格
AVG() 函數只應用於單個列,忽略列值為NULL的行
mysql>SELECT COUNT(*) FROM customers;
對所有行進行計數,包括列值為NULL的行
mysql>SELECT COUNT(name) FROM tmall_user;
只對有昵稱的行進行計數,忽略列值為NULL的行
mysql>SELECT MAX(prod_price) FROM products;
返回最貴的物品的價格
MAX()函數一般用於找出最大的數值或日期值,但也可以返回任意列中的最大值。在用於文本數據時,按照相應順序排序,返回最後一行。
MAX()函數忽略列值為NULL的行
MIN()函數與MAX()函數功能相反。
mysql>SELECT SUM(quantity) FROM orderitems
->WHERE order_num = 20005;
查詢所訂購物品的總數(所有quantity之和)
mysql>SELECT SUM(item_price*quantity) FROM orderitems
->WHERE order_num = 20005;
查詢所訂購物品的總金額。
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
要返回的列或表達式
FROM
要查詢的表
WHERE
行過濾
GROUP BY
分組
HAVING
分組過濾
ORDER BY
輸出排序順序
LIMIT
行數
子查詢
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 操作符)
全文本搜索
插入數據
更新和刪除數據
不要省略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;
規范