資料庫設計 - 有效的使用系統資料 - 第七章 - 資料庫進階功能

單元 1 建立索引

Indexes

加快搜尋資料的速度，提升資料庫搜尋資料的效率

建立 indexes

以 Employees Table 為例，一般查找員工會用「名字」去找: Last Name，不太會用 id

當資料量大時，可以在 LastName 建立 index 加速索引。

1. 打開 Employee Table 點選板手
2. 找到 Indexes Tab
3. 將 LastName 加入 index
   
   
4. Apply
   
   

觀念

用 LastName 搜尋的時候，可以加速 employee 資訊搜尋的效率

單元 2 建立 Triggers

當事件發生時，建立一個觸發程序，能幫你啟動自動化的程式

Triggers

可以根據每一個 Table 去設定

• Before/After Insert
• Before/After Update
• Before/After Delete

以 employees table 為例，按下板手後，到 triggers tab

在新增一筆 employee 的資料後，我們希望可以把這筆資料加到 employee_logs 裡面，紀錄這個員工是在什麼時候輸入這筆資料的

建立 Triggers

1. 點選 AFTER INSERT 旁邊的 +
   
   
2. 會出現
   

   CREATE DEFINER = CURRENT_USER TRIGGER `orders`.`employees_AFTER_INSERT` AFTER INSERT ON `employees` FOR EACH ROW
   BEGIN
   
   END
   

3. 用 insert into 這個方法，將資料塞入 log 中
   

   CREATE DEFINER = CURRENT_USER TRIGGER `orders`.`employees_AFTER_INSERT` AFTER INSERT ON `employees` FOR EACH ROW
   BEGIN
   insert into employees_log(name, date)
   values(new.lastname, sysdate());
   END

   new.lastname 可以使用 lastname 欄位輸入的值
   sysdate() 目前現在時間，日期-時:分:秒
4. 按右下角 Apply
   
   
5. 嘗試新增一筆資料到 employee table
6. 查看 employees_log table 可以發現多了一筆剛剛新增的資料

單元 3 虛擬表格 Views

可以幫助我們在應用層少寫一些 SQL 語法和程式碼，很實用

Views

CREATE VIEW "名稱" AS <<SQL 語法>>

使用時機：

1. 用來給系統以外的人，或其他系統（第三方系統）使用
2. 出報表：一長串常用的 SQL 語法，例如計算每一張訂單的產品平均價格
3. 不用寫在程式裡面，集中在 Views，不同程式使用時就不用一一去改，直接統一改資料庫就好
4. 不希望直接被人看到全部資料，只希望給別人看到部分資料

練習

將計算每一張訂單的產品平均價格的 SQL 語法變成一個 view

Create Views

1. 右鍵 create view
   
   
   
   

   會出現
   

   CREATE VIEW `new_view` AS
   

2. 將 new_view 改成自己的名字 report
3. 將 SQL 語法貼到 SQL 後面
   
   
   
   
   
4. Apply
5. Refresh
   
   
   

User Views

1. 把 report 當成一個 table 來使用
   

   Select * from report;

   
   可以發現兩者結果是一樣的

單元 4 Stored Procedures

在資料庫中撰寫自己的函式，將流程和邏輯包起來，隨時可以使用

將商業邏輯或是比較複雜的程序，包成一個 procedure，呼叫後就可以執行一連串複雜的程序

Stored Procedures

呼叫 procedure 快速執行複雜程序

使用時機：

• 每天/每月，固定時間產生報表
• 把資料匯入另一個 table

練習

Create Procedures

開一個 summary 的報表，包含 sum 與 date 兩個欄位

需要判斷塞入 date 欄位的資料為歷史資料：小於現在的時間

在 stored procedures 裡面做以上的邏輯判斷

1. CREATE TABLE "summary"
   
   
   
   
   
   
2. CREATE PROCEDURE `add_summary` 
   
   
   
   
   
3. () 中放入參數，名稱 + type: sum int, add_time date_time
4. 加入規則到 BEGIN 與 END 之間
   

   CREATE PROCEDURE `add_summary` (sum int, add_time datetime)
   BEGIN
   if add_time < now() then
       insert into summary(sum, date) values(sum, add_time);
   else
       select "error";
   end if;
   END
   

5. Apply
   
   
   
   
   

Use Procedures

1. call  procedure_func(para1, para2) 
   執行
   

   call add_summary(200, str_to_date("2020/10/30", "%Y/%m/%d"));
   

2. summary table 會新增一筆資料
   
   

單元 5 Functions

教你如和使用資料庫中，內建的函式與常用的函式

Functions

像是數字函式庫 sum(), round(), 或是字串函式庫 SUBSTRING()，都是 MySQL 提供給我們的函式，我們也可以自己建立，要做複雜計算時直接使用。

練習

製作一個計算 BMI 值的 Function

BMI = 體重 / 身高 (m) 的平方

Create Functions

1. CREATE FUNCTION `new_function` ()
   
   
   
   
   
   
2. 將 new_function 改成自己的名稱: bmi
3. 定義 return 資料型態
4. 邏輯放在 BEGIN 與 END 之間
5. () 如果需要輸入參數，要放在括弧裡
   

   CREATE FUNCTION `bmi` (w decimal(3, 1), h decimal(3, 1))
   RETURNS decimal(3, 1)
   BEGIN
   
   RETURN w / pow(h, 2);
   END

Use Functions

執行

select bmi(55, 1.64);