資料函式庫物件與MySQL基本操作

資料函式庫物件是資料函式庫系統中不可或缺的組成部分，用於組織、管理和操作資料。常見的資料函式庫物件包括檢視、函式、程式、觸發器和事件等。檢視可以簡化資料的存取，函式和程式可以封裝常用的 SQL 邏輯，觸發器可以自動執行預定義的操作，而事件可以根據時間間隔執行特定任務。理解這些物件的特性和用法，有助於提升資料函式庫開發和管理的效率。MySQL 是一個常用的關聯式資料函式倉管理系統，本文也介紹了 MySQL 的基本操作，包含安裝 MySQL 伺服器和客戶端工具，以及使用 SQL 語法進行資料函式庫和表格的管理。學習如何建立和管理資料函式庫、表格，以及定義主鍵和外部索引鍵，是資料函式庫開發的基礎。

資料函式庫物件

資料函式庫物件是資料函式庫中的重要組成部分，包括檢視、函式、程式、觸發器和事件等。這些物件可以幫助我們更好地管理和操作資料。

檢視

檢視是一種虛擬表格，根據一個查詢陳述式建立。它可以讓我們更方便地存取和操作資料，同時也可以用來隱藏敏感資料。

建立檢視

建立檢視的語法如下：

CREATE VIEW 檢視名稱 AS
SELECT 列名1, 列名2,...
FROM 表名
WHERE 條件;

例如：

CREATE VIEW employee_view AS
SELECT id, name, department
FROM employees
WHERE department = 'Sales';

使用檢視

檢視可以像普通表格一樣使用，例如：

SELECT * FROM employee_view;

函式和程式

函式和程式是兩種不同型別的資料函式庫物件。函式是一種可以傳回值的程式碼塊，而程式是一種可以執行多個陳述式的程式碼塊。

建立函式

建立函式的語法如下：

CREATE FUNCTION 函式名稱(引數1, 引數2,...)
RETURNS 傳回型別
BEGIN
    -- 函式內容
END;

例如：

CREATE FUNCTION get_employee_name(id INT)
RETURNS VARCHAR(50)
BEGIN
    DECLARE name VARCHAR(50);
    SELECT name INTO name FROM employees WHERE id = id;
    RETURN name;
END;

建立程式

建立程式的語法如下：

CREATE PROCEDURE 程式名稱(引數1, 引數2,...)
BEGIN
    -- 程式內容
END;

例如：

CREATE PROCEDURE insert_employee(name VARCHAR(50), department VARCHAR(50))
BEGIN
    INSERT INTO employees (name, department) VALUES (name, department);
END;

觸發器

觸發器是一種特殊的程式，當資料表中的資料發生變化時會自動執行。

建立觸發器

建立觸發器的語法如下：

CREATE TRIGGER 觸發器名稱
BEFORE/AFTER INSERT/UPDATE/DELETE ON 表名
FOR EACH ROW
BEGIN
    -- 觸發器內容
END;

例如：

CREATE TRIGGER update_employee_trigger
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
    IF NEW.department!= OLD.department THEN
        INSERT INTO employee_history (id, name, department)
        VALUES (OLD.id, OLD.name, OLD.department);
    END IF;
END;

事件

事件是一種特殊的程式，根據預定的時間間隔自動執行。

建立事件

建立事件的語法如下：

CREATE EVENT 事件名稱
ON SCHEDULE EVERY 時間間隔
DO
BEGIN
    -- 事件內容
END;

例如：

CREATE EVENT backup_database_event
ON SCHEDULE EVERY 1 DAY
DO
BEGIN
    BACKUP TABLE employees TO 'backup.sql';
END;

內容解密：

• 檢視是一種虛擬表格，根據一個查詢陳述式建立。
• 函式是一種可以傳回值的程式碼塊，而程式是一種可以執行多個陳述式的程式碼塊。
• 觸發器是一種特殊的程式，當資料表中的資料發生變化時會自動執行。
• 事件是一種特殊的程式，根據預定的時間間隔自動執行。

圖表翻譯：

下面是本章中使用的Plantuml圖表： 這個圖表展示了資料函式庫物件之間的關係，包括檢視、函式、程式、觸發器和事件等。

安裝MySQL和工具

要開始使用資料函式庫，您需要安裝MySQL的免費版本，稱為MySQL Community Server，以及兩個方便的工具：MySQL Workbench和MySQL命令列客戶端。這些軟體可以從MySQL網站免費下載。您將使用這些工具來完成後續的專案和練習。

MySQL架構

MySQL使用客戶端/伺服器架構，如圖1-1所示。 圖1-1：客戶端/伺服器架構 在這個架構中，伺服器端主機和管理資源或服務，客戶端則需要存取這些資源。這意味著，在生產環境中，MySQL Community Server軟體將在專用的電腦上執行，該電腦存放MySQL資料函式庫。用於存取資料函式庫的工具，例如MySQL Workbench和MySQL命令列客戶端，將安裝在使用者的電腦上。 由於您正在設定一個開發環境，因此您將在同一臺電腦上安裝MySQL客戶端工具和MySQL Community Server軟體。換句話說，您的電腦將同時扮演客戶端和伺服器的角色。

安裝MySQL

要安裝MySQL，您需要前往MySQL網站，然後按照以下步驟進行：

1. 前往MySQL網站，點選「下載」按鈕。
2. 選擇您要下載的MySQL版本，然後點選「下一步」。
3. 選擇您的作業系統，然後點選「下一步」。
4. 選擇您要安裝的MySQL產品，然後點選「下一步」。
5. 閱讀並接受授權協定，然後點選「下一步」。
6. 選擇安裝位置，然後點選「下一步」。
7. 點選「安裝」按鈕開始安裝過程。

安裝MySQL Workbench

要安裝MySQL Workbench，您需要前往MySQL網站，然後按照以下步驟進行：

1. 前往MySQL網站，點選「下載」按鈕。
2. 選擇您要下載的MySQL Workbench版本，然後點選「下一步」。
3. 選擇您的作業系統，然後點選「下一步」。
4. 選擇您要安裝的MySQL Workbench產品，然後點選「下一步」。
5. 閱讀並接受授權協定，然後點選「下一步」。
6. 選擇安裝位置，然後點選「下一步」。
7. 點選「安裝」按鈕開始安裝過程。

MySQL命令列客戶端

當您安裝MySQL Community Server或MySQL Workbench時，MySQL命令列客戶端應該會自動安裝。這個客戶端允許您從命令列介面連線到MySQL資料函式庫，您可以使用它來執行SQL陳述式或執行SQL指令碼檔案。

MySQL 基本命令與資料函式倉管理

MySQL 的命令不論您使用哪種工具連線到 MySQL，都是一致的。在這裡，我們將使用 MySQL Workbench 來示範如何操作。

顯示資料函式庫

首先，讓我們來顯示 MySQL 中的所有資料函式庫。您可以在 MySQL Workbench 的上方面板中輸入 show databases; 命令，注意必須包含結尾的分號。然後，點選閃電符號圖示來執行命令。結果將出現在結果網格面板中，您會看到一個可用的 MySQL 資料函式庫列表。

資料函式庫列表中可能包含一些系統資料函式庫，這些資料函式庫是在 MySQL 安裝時自動建立的，例如 information_schema、mysql 和 performance_schema。其他資料函式庫可能是我們自己建立的。任何您建立的資料函式庫都應該出現在這個列表中。

資料函式庫術語

在 MySQL 中，術語 “schema” 與 “database” 是同義的。您可以使用 show databases 或 show schemas 來傳回所有資料函式庫的列表。注意，在 MySQL Workbench 中，使用的是 “schemas” 這個術語。有時，術語 “database” 可能與 “database system” 或 “relational database management system (RDBMS)” 混淆。例如，某人可能問您，“您使用哪個資料函式庫，Oracle、MySQL 還是 PostgreSQL?” 實際上，他們問的是，“您使用哪個資料函式庫系統?” MySQL 資料函式庫是一個用於組織資料的地方，而資料函式庫系統是用於儲存和檢索資料的軟體。

瀏覽資料函式庫

您也可以透過瀏覽的方式來檢視資料函式庫。點選面板底部的 “Schemas” 標籤，即可顯示資料函式庫列表。然後，點選右箭頭來檢查您的資料函式庫內容。請注意，透過瀏覽器瀏覽時，系統自動建立的系統資料函式庫可能不會顯示在 Navigator 面板中。

建立新資料函式庫

現在，您已經知道如何檢視 MySQL 中的資料函式庫列表了，是時候嘗試建立自己的資料函式庫了。為此，您需要使用 create database 命令，並指定要建立的資料函式庫名稱：

create database circus;
create database finance;
create database music;

您的資料函式庫名稱應該描述儲存在那裡的資料型別。例如，名為 “circus” 的資料函式庫可能包含關於小丑、緊繩走鋼絲演員和空中飛人表演的資料表。名為 “finance” 的資料函式庫可能包含應收賬款、收入和現金流的資料表。而名為 “music” 的資料函式庫可能包含關於樂隊、歌曲和專輯的資料表。

刪除資料函式庫

如果您想要刪除一個資料函式庫，可以使用 drop database 命令：

drop database circus;
drop database finance;
drop database music;

這些命令將刪除您剛剛建立的三個資料函式庫，包括其中的所有資料表和資料。

練習時間

1. 執行 show databases 命令，看看您有多少個資料函式庫？
2. 使用 MySQL Workbench 建立一個名為 “cryptocurrency” 的資料函式庫。建立後，執行 show databases 命令，看看新的資料函式庫是否出現在您的列表中？現在您有多少個資料函式庫？
3. 刪除 “cryptocurrency” 資料函式庫，然後再次執行 show databases 命令。資料函式庫是否從您的列表中消失了？

建立新表格

在這個例子中，您將建立一個新表格來儲存全球人口資料，並指定表格可以包含的資料型別。

首先，建立一個名為 land 的資料函式庫，然後使用 use 命令指定要使用的資料函式庫。接下來，使用 create table 命令建立一個名為 continent 的表格，該表格包含三列：continent_id、continent_name 和 population。每列都有一個對應的 MySQL 資料型別，用於控制該列允許的資料型別。

• continent_id 列被定義為 int，以便接受整數資料。每個洲都有一個唯一的 ID 號碼。
• continent_name 列被定義為 varchar(20)，以便接受最多 20 個字元的字元資料。
• population 列被定義為 bigint，以便接受大整數，因為洲的人口數量可能非常大。

當您執行這個 create table 陳述式時，MySQL 會建立一個空表格。表格具有表格名稱，其列已被定義，但尚未包含任何列。

如果您嘗試新增一列具有不符合列資料型別的資料的列，MySQL 將拒絕整個列。例如，由於 continent_id 列被定義為 int，MySQL 不允許該列儲存包含字母的值，如 “Continent #1” 或 “A”。MySQL 也不允許您在 continent_name 列中儲存超過 20 個字元的值。

約束

當您建立自己的資料函式庫表格時，MySQL 允許您在資料上新增約束或規則。一旦您定義了約束，MySQL 將強制執行它們。約束有助於維護資料完整性，即保持資料函式庫中的資料準確和一致。例如，您可能想要在 continent 表格中新增一個約束，以便不能有兩列具有相同值的列。

MySQL 中可用的約束包括主鍵、外部索引鍵、非空、唯一、檢查和預設值。

主鍵

在表格中識別主鍵是資料函式庫設計的一個基本部分。主鍵由一列或多列組成，唯一識別表格中的列。當您建立資料函式庫表格時，您需要確定哪些列應該組成主鍵，因為這些資訊將幫助您稍後檢索資料。

如果您結合多個表格中的資料，您需要知道每個表格應該傳回多少列以及如何結合表格。您不希望在結果集中有重複或缺失的列。

考慮這個具有 customer_id、first_name、last_name 和 address 列的客戶表格：

| customer_id | first_name | last_name | address | | :

| :

| :

– | :

| | 1 | Bob | Smith | 12 Dreary Lane | | 2 | Sally | Jones | 76 Boulevard Meugler | | 3 | Karen | Bellyacher | 354 Main Street |

要決定表格的主鍵應該是哪些列，您需要識別哪些列唯一識別表格中的列。對於這個表格，主鍵應該是 customer_id，因為每個 customer_id 僅對應於表格中的一列。無論將來可能新增到表格中的多少列，都永遠不會有兩列具有相同的 customer_id。這不能被其他列所證明。多個人可以具有相同的名字、姓氏或地址。

主鍵可以由多個列組成，但即使是 first_name、last_name 和 address 列的組合也不保證能夠唯一識別列。例如，Bob Smith 可能與他的同名兒子一起住在 12 Dreary Lane。

要指定 customer_id 列為主鍵，您可以在建立客戶表格時使用主鍵語法，如清單 2-1 所示：

CREATE TABLE customer (
  customer_id INT,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  address VARCHAR(100),
  PRIMARY KEY (customer_id)
);

內容解密：

在上述 SQL 陳述式中，我們建立了一個名為 customer 的新表格，其中包含四列：customer_id、first_name、last_name 和 address。每列都有一個對應的 MySQL 資料型別，用於控制該列允許的資料型別。然後，我們使用 PRIMARY KEY 語法指定 customer_id 列為主鍵，這保證了每個 customer_id 僅對應於表格中的一列。

圖表翻譯：

以下是使用 Plantuml 語法繪製的客戶表格結構圖： 在這個圖中，我們定義了兩個實體：CUSTOMER 和 ADDRESS。每個客戶都有一個唯一的地址，因此我們使用 “one to one” 關係來連線這兩個實體。客戶實體包含四列：customer_id、first_name、last_name 和 address。地址實體包含四列：street、city、state 和 zip。

主鍵（Primary Key）與外部索引鍵（Foreign Key）之間的關係

在關聯式資料函式庫中，主鍵（Primary Key）和外部索引鍵（Foreign Key）是兩個非常重要的概念。主鍵是一個唯一的識別符號，用於標識資料表中的每一行，而外部索引鍵則是一個欄位，它參考另一個資料表的主鍵，以建立兩個資料表之間的關係。

主鍵（Primary Key）

主鍵是一個資料表中的欄位或欄位組合，它用於唯一地標識每一行。主鍵有以下幾個特性：

• 唯一性：主鍵的值必須是唯一的，不能有重複的值。
• 不可為空：主鍵的值不能為空。
• 索引：主鍵會自動建立一個索引，以提高查詢效率。

建立主鍵的語法如下：

CREATE TABLE customer (
  customer_id INT PRIMARY KEY,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  address VARCHAR(200)
);

在這個例子中，customer_id 欄位被定義為主鍵。

外部索引鍵（Foreign Key）

外部索引鍵是一個欄位或欄位組合，它參考另一個資料表的主鍵，以建立兩個資料表之間的關係。外部索引鍵有以下幾個特性：

• 參考完整性：外部索引鍵的值必須存在於被參考的資料表中。
• 可為空：外部索引鍵的值可以為空。

建立外部索引鍵的語法如下：

CREATE TABLE complaint (
  complaint_id INT PRIMARY KEY,
  customer_id INT,
  complaint VARCHAR(200),
  FOREIGN KEY (customer_id) REFERENCES customer(customer_id)
);

在這個例子中，customer_id 欄位被定義為外部索引鍵，參考 customer 資料表中的 customer_id 主鍵。

主鍵和外部索引鍵之間的關係

主鍵和外部索引鍵之間的關係是非常重要的。當你建立一個外部索引鍵時，你必須確保被參考的資料表中的主鍵值存在於外部索引鍵中。這樣可以確保資料的一致性和完整性。

例如，在 complaint 資料表中，customer_id 欄位是外部索引鍵，參考 customer 資料表中的 customer_id 主鍵。如果你試圖插入一個不存在於 customer 資料表中的 customer_id 值，資料函式庫會拒絕這個操作，以確保資料的一致性。

內容解密：

在上面的例子中，我們建立了兩個資料表：customer 和 complaint。customer 資料表中有一個 customer_id 主鍵，而 complaint 資料表中有一個 customer_id 外部索引鍵，參考 customer 資料表中的 customer_id 主鍵。

當你插入一行資料到 complaint 資料表中時，資料函式庫會檢查 customer_id 值是否存在於 customer 資料表中。如果存在，則可以插入；否則，資料函式庫會拒絕這個操作。

這樣可以確保 complaint 資料表中的 customer_id 值始終與 customer 資料表中的 customer_id 主鍵值相符，從而維護資料的一致性和完整性。

圖表翻譯：

@startuml
skinparam backgroundColor #FEFEFE
skinparam componentStyle rectangle

title 資料函式庫物件與MySQL基本操作

package "資料庫架構" {
    package "應用層" {
        component [連線池] as pool
        component [ORM 框架] as orm
    }

    package "資料庫引擎" {
        component [查詢解析器] as parser
        component [優化器] as optimizer
        component [執行引擎] as executor
    }

    package "儲存層" {
        database [主資料庫] as master
        database [讀取副本] as replica
        database [快取層] as cache
    }
}

pool --> orm : 管理連線
orm --> parser : SQL 查詢
parser --> optimizer : 解析樹
optimizer --> executor : 執行計畫
executor --> master : 寫入操作
executor --> replica : 讀取操作
cache --> executor : 快取命中

master --> replica : 資料同步

note right of cache
  Redis/Memcached
  減少資料庫負載
end note

@enduml

在這個圖表中，我們定義了兩個實體：CUSTOMER 和 COMPLAINT。CUSTOMER 實體中有一個 customer_id 屬性，而 COMPLAINT 實體中有一個 customer_id 屬性，參考 CUSTOMER 實體中的 customer_id 屬性。

這樣可以確保 COMPLAINT 實體中的 customer_id 值始終與 CUSTOMER 實體中的 customer_id 值相符，從而維護資料的一致性和完整性。

從資料函式庫系統架構的底層設計到實際應用場景的全面檢視顯示，資料函式庫物件和基礎操作是構建高效能、易維護資料函式庫系統的根本。本文深入探討了檢視、函式、儲存程式、觸發器和事件等核心物件的建立和使用方法，並結合MySQL的安裝、基本命令和資料函式倉管理，以及資料表設計中的主鍵和外部索引鍵約束，提供了一個完整的學習路徑。

多維比較分析顯示，檢視簡化了資料存取，同時提升了安全性；函式和儲存程式則封裝了複雜的業務邏輯，提高了程式碼的可重用性和可維護性；觸發器和事件則實作了自動化的資料處理和任務排程，進一步提升了資料函式庫系統的效率。然而，過度使用觸發器可能導致效能瓶頸，而事件的排程需要謹慎規劃以避免資源衝突。技術團隊應著重於理解這些物件的特性和限制，並根據實際需求選擇合適的工具。

展望未來，隨著雲原生資料函式庫和Serverless技術的興起，資料函式庫物件的管理和佈署方式也將不斷演進。預計將出現更多自動化工具和平臺，簡化資料函式庫物件的開發和維運流程。同時，資料函式庫物件的安全性也將面臨新的挑戰，需要更強大的安全機制和最佳實踐來保障資料安全。對於重視長期穩定性的企業，密切關注這些新興技術趨勢，並逐步整合至現有系統，將帶來最佳平衡。玄貓認為，掌握資料函式庫物件的核心概念和實務操作，對於構建和管理現代資料函式庫系統至關重要。