最近買了一本二手的 MySQL 來溫故知新一下，紀錄一下一些少用或是重要的觀念 SQL query 函式…

mysql 基礎查詢

* 表所有欄位，非常不建議這樣寫，全表查詢
= 也可用 <=>
優先順序 NOT, AND, OR, XOR
判斷是否 NULL 用 IS NULL
mysql 為左匹配
字串 LIKE ‘w%’ 會搜到 w開頭 0~多個的字串，ex: w, waaaa, wb
字串為匹配預設不分大小寫
order by 預設 asc
LIMIT 3, 5 表示 offset 3, limit 5
CONCAT, CONCAT_WS 可以連接欄位值或字串

SQL

# CONCAT 連接欄位用 , 隔開
SELECT 
    CustomerName, 
    CONCAT(Address, ", ", PostalCode, ", ", City) AS Address
FROM Customers

SQL

mysql

# CONCAT_WS 連接欄位用 , 隔開
SELECT 
    CustomerName,
    CONCAT_WS(', ', Address, PostalCode, City) AS Address
FROM Customers;

LOCATE(字串1, 字串2)，字串1 在字串2的位置，不存在回 0

SQL

mysql

# LOCATE 搜顧客名中間有空白的
SELECT CustomerName 
FROM Customers
WHERE LOCATE(' ', CustomerName) <> 0;

IF 去判斷成立與否，去執行後續運算

SQL

mysql

# IF 判斷是否美國人
SELECT 
	CustomerName,
    IF(Country = 'USA', '美國人', '非美國人') AS '是否美國人'
FROM Customers;

CASE WEHN 運算式 THEN 運算式 ELSE 運算式，盡可能不要這樣寫拉XD
COUNT 如果運算式為 NULL 不會列入計數
GROUP_CONCAT 搭配 DISTINCT

SQL

mysql

# GROUP_CONCAT 找出 Country 為 USA 的所有顧客的城市用 , 區隔
SELECT 
    GROUP_CONCAT(DISTINCT City order by City asc SEPARATOR ',')
FROM Customers
WHERE Country = 'USA';

Group By 做分群計算

SQL

mysql

# Group By 以 CategoryID 做分群計算不同分類的 TotalPrice
SELECT 
	CategoryID,
	SUM(Price) AS TotalPrice
FROM Products
GROUP BY CategoryID;

WITH ROLLUP 可以增加總計，看起來更像樞紐分析表

SQL

mysql

# WITH ROLLUP 多一列顯示所有分類總價格的加總 ex: category1 TotalPrice 500, category2 TotalPrice 300, 多一列 800
SELECT 
	CategoryID,
	SUM(Price) AS TotalPrice
FROM Products
GROUP BY CategoryID WITH ROLLUP;

GROUP BY 不可以用 WHERE
GROUP BY 搭配 HAVING 去做群組篩選器

SQL

mysql

# HAVING 可以篩選分類總價格 <= 200 都剔除，只留下 200 以上
SELECT 
	CategoryID,
	SUM(Price) AS TotalPrice
FROM Products
GROUP BY CategoryID
HAVING TotalPrice > 200;

UNION，把兩個查詢結合在一起，但選取欄位必須兩者完全相同，使用情境是為了讓 SQL 更好讀

SQL

mysql

# UNION 將兩個查詢合併，讓SQL更好讀
SELECT
    OrderID,
	EmployeeID 
FROM Orders
WHERE OrderDate <= '1996-08-17' and ShipperID = 3
UNION
SELECT
    OrderID,
	EmployeeID 
FROM Orders
WHERE OrderDate >= '1996-08-13' and ShipperID = 1;

DESC or Describe table 可以總覽這個 table 資訊，包含 field, type, 是否接受null, Key 等等
可以搭配 ON DUPLICATE UPDATE 去避免重複建立重複一筆

SQL

mysql

1
2
3

# ON DUPLICATE UPDATE
# 想像有一個公司旅遊投票 方案/價格/支持人數，如果還沒有人支持就新增，有人支持 counter + 1
INSERT INTO Decisions VALUES ('A', '7900', 1) ON DEPLICATE KEY UPDATE counter = counter + 1;

REPLACE 可以用來新增以及修改一整列的資料，如有重複索引值，則取代，否則新增

SQL

mysql

1 2	`# REPLACE 遇到相同的索引值 4 則會修改，否則會新增 REPLACE INTO Shippers VALUES (4, 'SEA', '(502) 999-9998');`

UPDATE/DELETE 也可以搭配 ORDER BY and LIMIT
TRUNCATE TABLE Shippers，會將此表 Shippers 資料全部刪除

MySQL basic learning

目錄

mysql 基礎查詢

Jamie Huang