MySQL: SQL 查詢思想小記

曾經寫 SQL 寫得嚇嚇叫，但是自從一陣子沒接觸之後，整個退化得特別厲害，所以有必要來寫一下筆記，讓未來如果長久沒寫 SQL，還可以回憶一下。

速查資源:

https://devhints.io/sql-join
https://devhints.io/knex

SELECT EXISTS 子查詢 - 檢查帳號+密碼是否存在

SELECT EXISTS(SELECT id FROM members WHERE uid = ? AND passowrd = ?) AS is_exists;

LIMIT, 分頁功能及基本要素

製作分頁的基本要素有下列:

1. 每一頁長度限制:  (傳進) limit
2. 目前分頁:  (傳進) page
3. 在以上條件之下，有幾頁: pageSize
4. 總資料數量:  total
5. 該頁資料:  data

過濾資料:

1. linit:  limit 要大於 1
2. page: 務必了解是從 0 開始算
3. pageSize: 就算是 0 筆資料，也是總共 1 頁
4. total:  選取所有資料大小

查詢該分頁資料: 

SELECT * FROM warehouse GROUP BY id ORDER BY create_at DESC LIMIT ?, ?;

假設 (?, ?) 為 5, 20 ，這裡就可以了解是從第 5 頁開始，後 20 筆資料，如果使用 knex ORM 查詢，分頁效果會像:

knex("warehouse").offset(page*limit).limit(limit)

也就是，從第 5*20 筆資料開始偏移，查 20 筆資料。

查詢大小，並製作 pageSize, total

SELECT count(*) as length FROM warehouse;

然後，該 length 即為 toatl，pageSize 是: 

(length <= limit) ? 1 : Math.ceil(length / limit, 1)

也就是如果除出來小於 limit，得到的結果會小於 1 ，所以如果筆數小於限制大小，就直接給他 1 的結果，如果大於 limit，全部都使用無條件進位。

HAVING, WHERE 差異

group by 是一種 summary column 的做法， having 是給 group by 指令去搜尋條件用的，簡單的說:

WHERE 是給 SELECT 用的， HAVING 是給 GROUP BY 用的。

IF IS  NULL - 如果為空，就回傳 0

SELECT IF(SUM(balance) IS NULL, 0 ,SUM(balance)) as available FROM virtualcashoffer WHERE expire > NOW()

上面這是一段檢查時效性購物金的做法，可能會有如果購物金都過期，則回傳會變空的情形，此時可以用這個方法解決，讓 null 變成 0。

DATE, INTERVAL - 日期增減

減法:

SELET * FROM virtualcashoffer WHERE expire > DATE_SUB(NOW(), INTERVAL 3 MONTH)

上面這是一段可以取得該用戶最近 3 個月的購物金使用、獲得紀錄。

加法:

SELET * FROM virtualcashoffer WHERE expire > NOW() AND DATE_ADD(NOW(), INTERVAL 1 MONTH) > expire

上面這是一段可以取得該用戶一個月內快到期的購物金。

JOIN + 子查詢 - 特殊結構查詢 JOIN 方式

SELECT * FROM products p
LEFT JOIN (SELECT * FROM spec GROUP BY product_id) s ON s.product_id = p.id

上面這一段，是希望查找商品底下附屬的所有商品規格，在這個情形下，子查詢沒有使用 WHERE 去限定規則，而是在外部的 ON 做限定規則，我的理解下，這是做兩次大規模的處理，也就是子查詢做了一個很大的 GROUP 表，然後在外面才做 ON 過濾，效能表現尚不一定很理想。

WHERE FOR UPDATE - 更新最後查詢時間的做法

START TRANSACTION;
SELECT * FROM orders o WHERE o.id IN (?) ORDER BY o.id FOR UPDATE;
UPDATE orders SET last_export_at = NOW() WHERE id IN (?);
COMMIT;

以上的做法只能在 InnoDB 下實作，這裡也使用到 TRANSACTION 去避免 Race Condition。

DISTINCT - 試著查詢不重複的 id 有幾個?

SELECT count(DISTINCT warehouse_group_id) as length FROM warehouse

以上的例子，是想要計算不重複的商品群組有幾個，意思是，warehouse 底下，可能有很多人共用了同一個商品群組，但在不使用 GROUP BY 的情況下，想直接查詢有幾個不同的群組，速度應該會較 GROUP BY 快些。

查找最大元素的理解

要找最後一筆插入的元素，跟去找目前最新的元素，是不一樣的事，前者會常常使用 LAST INSERT 的方式去找，但這件事的前提事你需要在同 SECTION 做 INSERT ，才可以查到。

但如果要查找最新的元素，基於 AUTO INCREMENT 的情形下，只需要找 MAX(id) 就可以了。

SELECT MAX(id) FROM warehouse

另外一種查法，是透過更大的外表資訊去查詢:

SELECT `AUTO_INCREMENT` FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '[DB Name]' AND TABLE_NAME   = '[Table Name]';

重設 AUTO INCREMENT

強制重設

ALTER TABLE table_name AUTO_INCREMENT = 1;

注意，這個方法一但重設之後，你不可以重設比目前最大的值還要小，假設你目前還有一個 id 為 50 的 row data，那你重設 AUTO INCREMENT 要比 50 還大。

截斷表

TRUNCATE TABLE table_name;

注意，截斷資料會把所有底下的 AUTO INCREMENT column 歸零，不管有沒有 foreign key constraint ，都會歸零。

WHERE IN - 多重資料一次完成查詢

SELECT * FROM product WHERE id IN (?);

這裡可以放進多個 id。

MariaDB JSON 使用 - 包含資料在陣列中

SELECT JSON_CONTAINS( 
            (SELECT JSON_EXTRACT(CONCAT('[',replace(group_concat(product_spec_id),',',','),']'), '$') FROM orderproducts WHERE order_id = 2), -- 先做子查詢找出該訂單購買哪些項目 (orderproducts) 
                    JSON_EXTRACT(d.condition_value, '$'))

以上的做法，是先把 TEXT 轉成 Maria JSON Array，即 '[ 1, 2, 3, 4]' 字串會真的變字串，然後再去查詢是否購買的商品包含在陣列中，注意，這邊有比較先後順序問題: 不定值先在前面，後面放的是固定值。

鎖表機制

LOCK TABLES warehouse write;
-- 操作之後
UNLOCK TABLES;
                    

鎖表是蠻 LOW 的，畢竟這會卡住所有人的寫入，盡量避免去鎖寫表，除非是很重要的資料及情境。

JOIN 圖表

SELECT * FROM `A` INNER JOIN `B`

┌────────┐
│ A  ┌───┼────┐
│    │ ∩ │    │
└────┼───┘  B │
     └────────┘

Inner join	∩
Left outer join	A + ∩
Right outer join	∩ + B
Full outer join	A + ∩ + B

取自: https://devhints.io/sql-join