筆記、View / Stored procedure / trigger


Posted by s103071049 on 2021-07-16

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

缺點:試用場合不多

  1. 若資料庫無人維護,新進員工會不知道我怎麼會有這個 table 或這個 table 背後的 sql query 是甚麼
  2. 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 的觸發器中檢視用途。


#View #Stored Procedure #trigger







Related Posts

陪你讀論文 - Center-based 3D Object Detection and Tracking

陪你讀論文 - Center-based 3D Object Detection and Tracking

D3v4 & Canvas 工作坊 - D3 + Canvas 繪製動態路線圖

D3v4 & Canvas 工作坊 - D3 + Canvas 繪製動態路線圖

Day7 潛藏在你心中的指令即將覺醒

Day7 潛藏在你心中的指令即將覺醒


Comments