資料函式倉管理SQL操作與表格限制條件設定

資料函式倉管理的基礎概念離不開 CRUD 操作，也就是建立、讀取、更新和刪除資料。在實際應用中，這些操作通常透過 SQL 語法實作。不同的關聯式資料函式倉管理系統（RDBMS），例如 MySQL、Oracle、PostgreSQL、SQL Server 和 SQLite，雖然核心概念相同，但在語法和功能上存在差異。瞭解這些差異對於跨平台開發和資料函式倉管理至關重要。除了 CRUD 操作，交易管理也是資料函式倉管理中不可或缺的一環，它確保了資料函式庫操作的原子性、一致性、隔離性和永續性，維護資料的完整性。此外，正確地設定表格限制條件，例如主鍵、外部索引鍵、NOT NULL、UNIQUE 和 CHECK 等，可以有效地約束資料，避免資料冗餘和錯誤，提升資料函式庫的效能和可靠性。

資料函式倉管理與SQL操作基礎

在資料函式倉管理中，CRUD（建立、讀取、更新、刪除）是四項基本操作。本章節將著重於建立、更新和刪除資料函式庫、表格、索引和檢視等物件的操作，並介紹交易管理的概念。

資料函式庫基礎

資料函式庫是用於組織儲存資訊的容器。在資料函式庫中，可以建立各種資料函式庫物件來儲存或參照資料，例如表格、限制條件、索引和檢視等。資料模型或架構描述了這些物件如何在資料函式庫內部組織。

資料模型與架構

在設計資料函式庫時，首先會開發出資料模型，即資料函式庫在高層級上的組織方式。資料模型可能包含表格名稱、表格之間的關聯方式等資訊。接著，會根據資料模型建立架構，即在資料函式庫中實作資料模型，定義表格、限制條件、主鍵和外部索引鍵等。

常見的資料函式庫架構

一種常見的資料函式庫架構是「星型架構」（Star Schema），它包含一個事實表格位於中心，周圍環繞著多個維度表格（也稱為查詢表格）。事實表格記錄了已發生的操作（如銷售記錄），並包含指向維度表格的識別碼。

管理資料函式庫

顯示現有資料函式庫

在進行任何操作之前，需要先了解目前有哪些資料庫存在。以下是在不同關聯式資料函式倉管理系統（RDBMS）中顯示現有資料函式庫的指令：

MySQL：SHOW databases;
Oracle：SELECT * FROM global_name;
PostgreSQL：\l
SQL Server：SELECT name FROM master.sys.databases;
SQLite：.database 或在檔案瀏覽器中尋找 .db 檔案

顯示目前的資料函式庫

在寫入查詢之前，可能需要確認目前所在的資料函式庫。以下是各RDBMS中顯示目前資料函式庫名稱的指令：

MySQL：SELECT database();
Oracle：SELECT * FROM global_name;
PostgreSQL：SELECT current_database();
SQL Server：SELECT db_name();
SQLite：.database

切換到其他資料函式庫

如果需要使用其他資料函式庫中的資料，可以切換到該資料函式庫。以下是各RDBMS中切換到其他資料函式庫的指令：

MySQL 和 SQL Server：USE another_db;
Oracle：通常不切換資料函式庫，但可以透過 connect another_user 切換使用者
PostgreSQL：\c another_db
SQLite：.open another_db

建立新資料函式庫

如果具備CREATE許可權，可以建立新的資料函式庫。以下是各RDBMS中建立新資料函式庫的指令：

-- MySQL, Oracle, PostgreSQL, SQL Server
CREATE DATABASE my_new_db;

-- SQLite
> sqlite3 my_new_db.db

刪除資料函式庫

如果具備DELETE許可權，可以刪除現有的資料函式庫。以下是各RDBMS中刪除資料函式庫的指令：

-- MySQL, Oracle, PostgreSQL, SQL Server
DROP DATABASE my_new_db;

-- SQLite
-- 直接在檔案瀏覽器中刪除 .db 檔案

重點整理

資料函式庫是用於組織儲存資訊的容器，包含多種資料函式庫物件。
資料模型描述了資料函式庫物件的組織方式，而架構則是資料模型的實作。
星型架構是一種常見的資料函式庫架構，包含一個事實表格和多個維度表格。
不同RDBMS有不同的指令來顯示現有資料函式庫、目前資料函式庫、切換資料函式庫、建立新資料函式庫和刪除資料函式庫。

內容解密：

本章節介紹了資料函式倉管理的基本概念和操作，包括顯示現有資料函式庫、切換資料函式庫、建立新資料函式庫和刪除資料函式庫等。不同RDBMS有不同的指令和特性，瞭解這些差異對於有效地管理和操作資料函式庫至關重要。

為什麼需要了解不同RDBMS的指令差異？

跨系統相容性：瞭解不同RDBMS的指令差異，可以幫助開發者撰寫更具相容性的程式碼，方便在不同系統之間遷移。
最佳化效能：不同的RDBMS有不同的效能最佳化策略，瞭解其指令特性可以幫助開發者針對特定系統進行最佳化。
安全管理：瞭解不同RDBMS的安全機制和許可權管理，可以幫助管理員更好地控制存取許可權，保護資料安全。

如何選擇適合的RDBMS？

專案需求：根據專案的需求選擇合適的RDBMS，例如對於需要高並發讀寫的系統，可能需要選擇支援高效並發控制的RDBMS。
擴充套件性：考慮RDBMS的擴充套件能力，是否能夠支援未來可能的擴充套件需求。
社群支援：選擇具有活躍社群和豐富資源的RDBMS，可以獲得更好的支援和維護。

交易管理簡介

交易管理是確保資料一致性和完整性的重要機制。在進行多個相關的操作時，交易管理可以確保這些操作要麼全部成功，要麼全部失敗，避免部分完成導致的資料不一致問題。

交易管理的特性

原子性（Atomicity）：交易中的所有操作要麼全部完成，要麼全部不完成，不會停留在中間狀態。
一致性（Consistency）：交易完成後，資料函式庫必須處於一致的狀態，滿足所有的約束條件。
隔離性（Isolation）：多個交易平行執行時，每個交易的執行應該像是在獨立執行一樣，不會相互幹擾。
永續性（Durability）：一旦交易提交，其結果就應該是永久性的，即使發生系統故障也不會丟失。

資料函式庫表格的建立與管理

在資料函式倉管理系統中，建立和管理表格是至關重要的操作。本篇文章將探討如何使用SQL來建立表格、插入資料、以及設定各種限制條件。

建立簡單的表格

首先，我們需要定義表格的結構。以下是一個範例，建立一個名為my_simple_table的表格，包含三個欄位：id、country和name。

CREATE TABLE my_simple_table (
    id INTEGER,
    country VARCHAR(2),
    name VARCHAR(15)
);

接下來，我們可以向這個表格中插入資料。以下是插入單一行資料的範例：

INSERT INTO my_simple_table (id, country, name)
VALUES (1, 'US', 'Sam');

內容解密：

CREATE TABLE my_simple_table：建立一個名為my_simple_table的新表格。
id INTEGER：定義一個整數型別的欄位id。
country VARCHAR(2)和name VARCHAR(15)：定義兩個字串型別的欄位，分別為country和name，其最大長度分別為2和15個字元。
INSERT INTO my_simple_table：向my_simple_table表格中插入資料。
VALUES (1, 'US', 'Sam')：指定要插入的資料值。

插入多行資料

不同的資料函式倉管理系統（RDBMS）對於插入多行資料的語法有所不同。以下是各大RDBMS的範例：

-- 適用於MySQL, PostgreSQL, SQL Server, SQLite
INSERT INTO my_simple_table (id, country, name)
VALUES 
    (2, 'US', 'Selena'),
    (3, 'CA', 'Shawn'),
    (4, 'US', 'Sutton');

-- 適用於Oracle
INSERT ALL
    INTO my_simple_table (id, country, name) VALUES (2, 'US', 'Selena')
    INTO my_simple_table (id, country, name) VALUES (3, 'CA', 'Shawn')
    INTO my_simple_table (id, country, name) VALUES (4, 'US', 'Sutton')
SELECT * FROM dual;

內容解密：

INSERT INTO my_simple_table：向my_simple_table表格中插入資料。
VALUES列表：包含多組要插入的資料值。
Oracle使用INSERT ALL語法，並需要一個SELECT * FROM dual來完成操作。

顯示現有表格

在建立新表格之前，檢查資料函式庫中是否已存在同名表格是一個好習慣。以下是各RDBMS用於顯示現有表格的命令：

-- MySQL
SHOW tables;

-- Oracle
SELECT table_name FROM user_tables;

-- PostgreSQL
\dt

-- SQL Server
SELECT table_name FROM information_schema.tables;

-- SQLite
.tables

內容解密：

各RDBMS提供不同的命令來列出當前資料函式庫中的所有表格。

使用限制條件建立表格

限制條件（constraint）是用來規範可以插入表格中的資料。以下範例建立兩個表格，並設定多種限制條件：

CREATE TABLE another_table (
    country VARCHAR(2) NOT NULL,
    name VARCHAR(15) NOT NULL,
    description VARCHAR(50),
    CONSTRAINT pk_another_table PRIMARY KEY (country, name)
);

CREATE TABLE my_table (
    id INTEGER NOT NULL,
    country VARCHAR(2) DEFAULT 'CA',
    name VARCHAR(15),
    cap_name VARCHAR(15),
    CONSTRAINT pk PRIMARY KEY (id),
    CONSTRAINT fk1 FOREIGN KEY (country, name) REFERENCES another_table (country, name),
    CONSTRAINT unq_country_name UNIQUE (country, name),
    CONSTRAINT chk_upper_name CHECK (cap_name = UPPER(name)),
    CONSTRAINT chk_country CHECK (country IN ('CA', 'US'))
);

內容解密：

NOT NULL：確保欄位不能為空。
PRIMARY KEY：定義主鍵，用於唯一標識每行資料。
FOREIGN KEY：定義外部索引鍵，用於與其他表格建立關聯。
UNIQUE：確保欄位中的值是唯一的。
CHECK：自定義檢查條件，用於確保資料符合特定規則。
DEFAULT：為欄位設定預設值。

資料函式庫表格建立與限制條件設定

在資料函式倉管理中，建立表格是基礎且關鍵的一步。透過設定適當的限制條件，可以確保資料的完整性和一致性。

限制條件型別

NOT NULL 限制: 確保某個欄位不能為空值。例如，在建立表格時，可以指定某個欄位不允許 NULL 值。

CREATE TABLE my_table ( id INTEGER NOT NULL, country VARCHAR(2) NULL, name VARCHAR(15) );

   在此範例中，`id` 欄位被設定為 NOT NULL，意味著在插入資料時必須提供 `id` 的值。

2. **DEFAULT 限制**: 為欄位設定預設值，當插入資料時如果未指定該欄位的值，則自動使用預設值。
   ```sql
CREATE TABLE my_table (
   id INTEGER,
   country VARCHAR(2) DEFAULT 'CA',
   name VARCHAR(15)
);

在此範例中，如果插入資料時未指定 country 的值，則預設為 ‘CA’。

CHECK 限制: 限制欄位值的範圍或條件。例如，限制 country 欄位只能是 ‘CA’ 或 ‘US’。

CREATE TABLE my_table ( id INTEGER, country VARCHAR(2) CHECK (country IN (‘CA’, ‘US’)), name VARCHAR(15) );

   或者將 CHECK 條件放在所有欄位定義之後：
   ```sql
CREATE TABLE my_table (
   id INTEGER,
   country VARCHAR(2),
   name VARCHAR(15),
   CHECK (country IN ('CA', 'US'))
);

也可以結合多個欄位進行檢查：

CREATE TABLE my_table (
id INTEGER,
country VARCHAR(2),
name VARCHAR(15),
CONSTRAINT chk_id_country CHECK (id > 100 AND country IN ('CA', 'US'))
);

UNIQUE 限制: 確保欄位或欄位組合的值是唯一的。例如，確保 id 欄位的值是唯一的。

CREATE TABLE my_table ( id INTEGER UNIQUE, country VARCHAR(2), name VARCHAR(15) );

   或者將 UNIQUE 條件放在所有欄位定義之後：
   ```sql
CREATE TABLE my_table (
   id INTEGER,
   country VARCHAR(2),
   name VARCHAR(15),
   UNIQUE (id)
);

也可以確保多個欄位的組合是唯一的：

CREATE TABLE my_table (
id INTEGER,
country VARCHAR(2),
name VARCHAR(15),
CONSTRAINT unq_country_name UNIQUE (country, name)
);

主鍵與外部索引鍵

主鍵（PRIMARY KEY）: 用於唯一標識表格中的每一行資料。主鍵可以由一個或多個欄位組成，並且必須滿足 NOT NULL 和 UNIQUE 的條件。

CREATE TABLE my_table ( id INTEGER PRIMARY KEY, country VARCHAR(2), name VARCHAR(15) );

   或者：
   ```sql
CREATE TABLE my_table (
   id INTEGER,
   country VARCHAR(2),
   name VARCHAR(15),
   PRIMARY KEY (id)
);

組合主鍵範例：

CREATE TABLE my_table (
id INTEGER NOT NULL,
country VARCHAR(2),
name VARCHAR(15) NOT NULL,
CONSTRAINT pk_id_name PRIMARY KEY (id, name)
);

外部索引鍵（FOREIGN KEY）: 用於建立表格之間的關聯。外部索引鍵指向另一個表格的主鍵。

CREATE TABLE orders ( o_id INTEGER PRIMARY KEY, o_location VARCHAR(20), o_price DECIMAL(6,2) );

CREATE TABLE customers ( id INTEGER PRIMARY KEY, order_id INTEGER, name VARCHAR(15), location VARCHAR(20), FOREIGN KEY (order_id) REFERENCES orders (o_id) );


#### 主鍵使用建議
- 每個表格都應該有主鍵，以確保資料的唯一性和可識別性。
- 建議使用具有唯一性的識別符號作為主鍵，例如 `country_id` 和 `name_id` 的組合，而不是直接使用 `country` 和 `name`。
- 主鍵應該是不可變的，以確保資料的一致性和追蹤能力。

玄貓 BlackCat

技術愛好者，專注於分享程式開發、雲端技術與 AI 應用的心得體會。