view (檢視表)

view 就是一張虛擬的 table。
使用時機：做報表

舉例 :

users 表 : id, username, password, nickname
products 表 : id, name, price
oreders 表 : id, user_id, product_id, quantity, price

為甚麼 orders 有了 price 但 products 還放了 price ? 這樣不是違反正規化原則嗎 ? 為甚麼不是將 orders 的 price 拿掉，直接用 products 的 price 呢 ?

因為 products 的價格有可能修正，但 orders 要存當下賣出的價格。

假設，現在老闆想知道是誰買了哪個產品，他不想看到 user_id, product_id，所以我們可以寫一個 query 將要的東西先撈出來，query 的內容才是老闆想看到的東西

SELECT o.id, o.user_id, u.username, p.name, o.quantity, o.price, o.quantity * o.price as total
FROM
  orders as o
JOIN
  users as u on o.user_id = u.id
JOIN
  products as p on o.product_id = p.id
ORDER BY
  o.id ASC

接下來有兩個方法，

方法一、將這個 query 包成 php 的 function。最後再去 call 這個 function

方法二、view
view 可以在資料庫中，建立一個只讓我檢視的表格。他就是一個虛擬表格。可以決定他的 base 是甚麼 query，改變 view 即改變 sql query 的長相。

基本上，對於這個表格，不太能做編輯、新增、刪除的功能。這些資料不是真的存在資料庫中，而是透過 sql query 給抓出來。這些東西不太會對他做 crud 的操作，crud 功能亂用的話會很難維護。

語法 : CREATE VIEW 表單 AS query

CREATE VIEW order_detail AS
  SELECT o.id, o.user_id, u.username, p.name, o.quantity, o.price, o.quantity * o.price as total
  FROM
    orders as o
  JOIN
    users as u on o.user_id = u.id
  JOIN
    products as p on o.product_id = p.id
  ORDER BY
    o.id ASC

缺點：試用場合不多

若資料庫無人維護，新進員工會不知道我怎麼會有這個 table 或這個 table 背後的 sql query 是甚麼
view 上面再疊加一個 view => 不易拆解

優點：隱藏資訊
如果今天要開資料庫給外人使用，如 : 其他部門的人，可是不想給對方看到我的資料庫長相或我不想給他看的資料，只想給他讀資料，可以藉由 view，只放要給他看的資料。

就像 SQL 的 function： Stored procedure

sql function 有兩種

第一種 : SQL 提供的內建函式
如 SUM()
SELECT SUM(price) FROM order_detail

第二種：自己寫 SQL 的函式

Stored procedure

另一種更完整的函式，不是在 Query 裡用可以取代整個 Query。同樣是把某些東西放在 sql query 裡面。

舉例：常取某個消費者買了甚麼東西
一、一次一次查

SELECT * FROM orders WHERE user_id = 1

二、自己寫 function
可以放 php 裡面，或放在 sql 中，放在 sql/資料庫中，就是 Stored procedure

function getUserOrder(id) {
  $sql = 'SELECT * FROM orders WHERE user_id = ' . $id
}

三、stored procedure
參數要給型態，begin 後面接要下的 sql query，query 結束要下一個分號代表結束的意思

CREATE PROCEDURE GetOrders(id INT)
  BEGIN
    SELECT * FROM orders WHERE user_id = id ;
  END

執行後會出錯，因為在 sql 語法中會將 ; 當成這整段的分號。

解決方式：改分隔符號
DELIMITER 是分隔符號的意思，預設值為分號，現在改成 //
在 END 加 // 表示這個 query 完成。

DELIMITER // 
CREATE PROCEDURE GetOrders(id INT)
  BEGIN
    SELECT * FROM orders WHERE user_id = id ;
  END //
DELIMITER ;

接著在 sql 中，call function

CALL GetOrders(1) 會有 user_id = 1 的東西 
CALL GetOrders(2)

檢視建立的 store procedure => DB 預存程序

優缺：
邏輯層放入資料庫，PHP 只要一直 CALL 就好了，但 PHP Debug 要去資料庫看，

發生事情前/後，要做甚麼事情：triggers

有點像 GIT HOOK (HOOK 表示可以在某些事件前後做一些事情)

發生事情 : 新增、更新、刪除

常見用途

從 LOG 推出某個時間點，他的值是甚麼、誰改了甚麼東西。防止別人亂動或撈資料進行復原。Trigger 也可以改資料。

舉例：
php 中建立 function，但如果有一天偷懶直接 call update products set ... where product_id = id，products_audit 就不會有這筆資料。

所以通常要做這類用途會直接在 DB 操作。

function updateProduct() {
  update products set ... where product_id = id
  insert into products_audit
}

products 表記載產品所有資訊，所以希望有個表是存 products 的 log
products_audit 紀載任何 products 的操作

products_audit 表
id 
product_id
name
price
update_at
action (enum 枚舉他有哪些類型)

當這個 products 的每一個 row 在更新前，都會執行這個 trigger。

DELIMITER // 
CREATE Trigger before_product_updated
  BEFORE UPDATE ON products
  FOR EACH ROW
BEGIN
  INSERT INTO products_audit(product_id, name, price, action)
  VALUES(OLD.id, OLD.name, OLD.price, 'UPDATE');
END // 
DELIMITER ;

可在 DB 的觸發器中檢視用途。