資料庫小哉問
記錄實習這幾個月的資料庫使用心得，SQL、ORM
在進公司實習之前，資料庫對我而言單純就是一個放東西的空間（好像也沒錯？），只有簡單接觸過 MySql 跟 MongoDb，一直到進公司做後端實習後 ，才開啟了我對資料庫更近一步的了解與熟悉。
大綱
- ORM
- SQL experiences
ORM 是什麼？
在進公司前我對網頁後端完全是個超級大菜鳥，不懂MVC架構、寫的是純SQL、甚至不是前後端分離，於是在接觸了ORM後也是花了好一番時間研究。
WHAT ?
ORM 全文是 Object-Relational Mapping，將關聯式資料庫 (relational database ) map 至物件的資料抽象化技術。
背後的運行是將資料庫的內容映射到物件上，讓我們可以以操作物件的方式操作資料庫，某種程度上實現物件導向。
WHY ?
使用ORM的好處就是不管我們背後使用的資料庫是哪種或哪個版本（MySql, Postgre, Ms SQL …等），使用的都是同一個邏輯。在實作上遇過測試站跟實際上架時使用的是不同資料庫，使用ORM就可以無痛搬遷，不需要去針對SQL 逐行修改。
簡單的例子
我們想在資料庫中撈出前10筆註冊的使用者
//Postgre SQLSELECT *
FROM User
ORDER BY id ASC, name DESC
LIMIT 10//MSSQLSELECT *
FROM User
ORDER BY id ASC, update_time DESC
TOP 10
在曾經使用過的 TypeORM（typescript） 中
createQueryBuilder("User")
.orderBy({
"User.id": "ASC",
"User.name": "DESC"
})
.limit(10)我們可以看到原本大家所習慣的SQL指令，完全變成了物件的操作
PROS & CONS
Pros:
- 有效避免SQL Injection的惡意攻擊
- 跟一坨一坨的 SQL 比，程式碼更加整潔，加速開發效率
- 換資料庫不用去花時間大改DAO層
Cons:
- 無可避免的，ORM 背後執行的還是 SQL 指令，因此還是會多了執行「物件 → SQL 」的時間
- ORM 還無法好好的處理太過複雜的查詢，因此遇到太複雜的查詢還是會倚賴傳統 SQL
Basic SQL
在專案公司接觸到各式各樣的案子，還是會遇到很多寫一般SQL指令的案子，又或是在實作ORM時，也通常會先在 DB 直接先下指令確定該搜尋能不能如預期的執行，因此想記錄下來這些經驗，並分享一些比較複雜的、不常見的指令。
以下分享使用的是Postgre SQL
基本的CRUD
新增（Create）: 新增使用者
INSERT INTO User
(state, user_name, user_tel, user_mobile, user_email)
VALUES
('TESTING', 'Brian', '29291001', '0933001100', 'h@gmail.com')
RETURNING id通常 insert 多會回傳 id(流水號) 以利後續使用
更新（Update）:更新使用者
UPDATE User
SET (
state = 'VERIFYING',
user_tel = '5201314'
)
WHERE user_name = 'Emma'
RETURNING idupdate也多會回傳 id(流水號) 以利後續使用
刪除（Delete）: 刪除使用者
DELETE FROM User
WHERE name = 'Arthur'查詢（Read）: 查詢使用者
SELECT *
FROM User
WHERE 一些條件基本的查詢是最簡單的，但從來沒有案子讓我這麼簡單過，基本上都會配上 Join 同時查詢不同 table。
Join 又可分為 inner, outer, left, right，如下圖
在學校學資料庫的時候，真的是每種都要記，但在實做運用時才發現，基本上會用到的只有 join (預設為Left outer join) 跟 inner join
Left join ：
SELECT u.*, s.name
FROM User u -- postgre 可以以空格代替 AS
LEFT JOIN (
SELECT name, tel
FROM Sibling sib
) s on s.tel = u.user_tel--找出所有使用者，如果有兄弟姐妹就也顯示，否則為null
Inner Join：
SELECT u.*, s.name
FROM User u
INNER JOIN (
SELECT name, tel
FROM Sibling sib
) s on s.tel = u.user_tel--找出所有有兄弟姊妹的使用者
上面可以看到我們在 FROM 之下使用 JOIN，有學過一點資料庫的讀者一定會好奇，明明可以用 WHERE…IN…?
不要，因為效能很差
Advanced SQL
分享一些用過但比較少見的指令
Partition by + row_number()
使用契機是有個存放所有訂單(Order)的Table，而訂單又可以一直做更改，因此會有訂單的修改紀錄(Log)，所以 order → log 就是一個一對多的型態。
這時我們的需求是查出每一筆訂單的最新的紀錄
SELECT order_id , update_at
, row_number() OVER (
PARTITION BY order_id ORDER BY update_at DESC
) as rankFROM verify_approval_log;
我們可以看到每一筆Order都按照我們的條件（order_id）排序好並編號，我們要的是每一個資料的最新一筆，所以只要取出 rank 為1即可
select *
from (
select order_id , update_at , row_number() over (
partition by order_id order by update_at desc
) as rank
from verify_approval_log
) new_log
where new_log.rank = '1'
Group by
其實 Group by 不算很進階的 sql，但我一開始很常因為他跳 error
我們想要找出使用者中薪水最高的，並且以id組（group）起來
SELECT Max(salary), id, user_tel
FROM User
GROUP BY id --(X)
GROUP BY id, user_tel --(O)--如果使用SQL內建的運算函式（Max, Count,,,），沒有被函式刮起來但有SELECT的每一項都要GROUP BY，而不是很直觀的只GROUP BY id
小結
讀者一定會想說，Advanced也太少了！但其實這接近一年的使用經驗下來，基本的CRUD+ join 就足以應付大部分的需求了（也有可能是我還太菜），通常比較複雜的商業邏輯都是撈出來之後再在 logic 層裡做處理。
以上就是我一年下來一些簡單的經驗分享，感謝大家的閱讀！
