返回文章列表
玄貓（BlackCat）

SQL資料定義與操縱語言詳解

本文探討SQL資料定義語言（DDL）和資料操縱語言（DML），涵蓋使用CREATE SEQUENCE建立序列、使用CREATE TABLE建立資料表，並結合主鍵與序列、使用ALTER TABLE修改資料表結構、使用CREATE

資料函式庫 SQL
SQL DDL DML 資料函式庫 資料表 檢視表

資料函式庫應用中，定義和操作資料至關重要。本文介紹SQL的資料定義語言（DDL）和資料操縱語言（DML），包含建立和管理資料表、序列和檢視表，以及插入、更新和刪除資料。首先，我們會使用CREATE SEQUENCE建立序列，並將其與主鍵結合用於CREATE TABLE陳述式中，確保資料表主鍵的自動遞增和唯一性。接著，我們會使用ALTER TABLE陳述式修改現有資料表結構，例如新增或刪除欄位。為簡化複雜查詢，我們會使用CREATE VIEW陳述式建立檢視表，提供一個抽象層。最後，我們會探討INSERT陳述式，包含單行和多行資料插入、處理主鍵衝突、從SELECT陳述式結果插入資料，以及從外部CSV檔案和API介面匯入資料。同時，我們也會使用DESCRIBE陳述式驗證資料結構，並使用SELECT陳述式確認資料操作的正確性，確保資料函式庫操作的完整性和一致性。

3.3 資料定義語言查詢

在資料函式倉管理中，正確定義資料表結構是至關重要的。本文將介紹如何使用SQL的資料定義語言（DDL）來建立、修改和刪除資料表，以及如何使用序列和檢視表來簡化資料操作。

3.3.1 使用序列和主鍵

序列是一種在資料函式庫中儲存的數值，可以用來自動產生唯一的識別碼。在DuckDB中，可以使用CREATE SEQUENCE陳述式來建立序列。

CREATE SEQUENCE IF NOT EXISTS prices_id
INCREMENT BY 1 MINVALUE 10;

接下來，建立一個名為prices的資料表，使用序列來產生主鍵。

CREATE TABLE IF NOT EXISTS prices (
  id INTEGER PRIMARY KEY DEFAULT(nextval('prices_id')),
  value DECIMAL(5,2) NOT NULL,
  valid_from DATE NOT NULL,
  CONSTRAINT prices_uk UNIQUE (valid_from)
);

內容解密：

  1. CREATE SEQUENCE prices_id：建立一個名為prices_id的序列。
  2. INCREMENT BY 1：序列的值每次增加1。
  3. MINVALUE 10：序列的最小值為10。
  4. DEFAULT(nextval('prices_id'))：使用序列的下一個值作為id欄位的預設值。
  5. CONSTRAINT prices_uk UNIQUE (valid_from)：確保valid_from欄位的值是唯一的。

為什麼不使用valid_from作為主鍵？在初始應用中，可能只處理銷售價格，但未來可能會處理購買價格。使用valid_from作為主鍵會限制資料表的彈性。

3.3.2 ALTER TABLE陳述式

在實際應用中，資料表結構可能會隨著需求的變化而需要調整。ALTER TABLE陳述式可以用來新增、刪除或重新命名欄位。

ALTER TABLE prices
ADD COLUMN IF NOT EXISTS valid_until DATE;

內容解密：

  1. ALTER TABLE prices：修改名為prices的資料表。
  2. ADD COLUMN IF NOT EXISTS valid_until DATE：新增一個名為valid_until的日期欄位，如果該欄位不存在。

此外，ALTER TABLE陳述式還可以用來刪除或重新命名欄位，以及重新命名資料表。但是，某些操作（如新增或刪除約束）目前在DuckDB中不受支援。

3.3.3 CREATE VIEW陳述式

檢視表是一種虛擬的資料表，根據查詢結果建立。可以使用CREATE VIEW陳述式來建立檢視表。

CREATE OR REPLACE VIEW v_power_per_day AS
SELECT system_id,
       date_trunc('day', read_on) AS day,
       round(sum(power) / 4 / 1000, 2) AS kWh
FROM readings
GROUP BY system_id, day;

內容解密：

  1. CREATE OR REPLACE VIEW v_power_per_day：建立或取代一個名為v_power_per_day的檢視表。
  2. SELECT system_id, date_trunc('day', read_on) AS day：選擇system_id和按天匯總的read_on日期。
  3. round(sum(power) / 4 / 1000, 2) AS kWh：計算每系統每天的總功率，並轉換為千瓦時（kWh）。
  4. GROUP BY system_id, day：按系統和日期分組。

檢視表可以用來簡化複雜的查詢，並提供一個穩定的介面給外部應用程式。當底層計算邏輯變更時，可以重新建立檢視表而不影響外部應用程式。

執行SQL查詢

無論底層資料表是否為空，只要它們存在，就可以建立檢視表（view）。雖然我們已經建立了readings表，但尚未插入任何資料，因此查詢檢視表v_power_per_day將傳回空結果。我們將在3.4.1節中再次討論這個檢視表，並在接下來的幾個例子中使用它。

DESCRIBE陳述式

幾乎所有的關聯式資料函式庫都支援使用DESCRIBE陳述式來查詢資料函式庫架構。最基本的實作中，它通常適用於資料表和檢視表。

關聯式資料函式庫是根據關聯模型和關聯代數。關聯模型最早由埃德加·F·科德於1970年提出。基本上，所有資料都以元組（tuple）的集合形式儲存在關係（relation）中。元組是有序的屬性列表，可以把它想像成資料表的欄位列表。因此，資料表是元組的關係，檢視表也是元組的關係，查詢結果同樣是元組的關係。與關聯式資料函式庫不同，圖資料函式庫儲存的是實體之間的實際關係。不過在本文中，我們使用的是關聯模型中定義的術語。

在DuckDB中，DESCRIBE陳述式不僅適用於資料表，也適用於所有型別的關係：檢視表、查詢、集合等。你可以使用DESCRIBE readings;來描述readings表。結果應該類別似如下：

┌─────────────┬──────────────┬─────────┬─────────┬─────────┬───────┐
│ column_name │ column_type │ null │ key │ default │ extra │
│ varchar │ varchar │ varchar │ varchar │ varchar │ int32 │
├─────────────┼──────────────┼─────────┼─────────┼─────────┼───────┤
│ system_id │ INTEGER │ NO │ PRI │ │ │
│ read_on │ TIMESTAMP │ NO │ PRI │ │ │
│ power │ DECIMAL(8,3) │ NO │ │ 0 │ │
└─────────────┴──────────────┴─────────┴─────────┴─────────┴───────┘

描述從任何資料表中選取的特定欄位子集（新的元組），例如DESCRIBE SELECT read_on, power FROM readings;，將傳回如下結果：

┌─────────────┬──────────────┬─────────┬─────────┬─────────┬─────────┐
│ column_name │ column_type │ null │ key │ default │ extra │
│ varchar │ varchar │ varchar │ varchar │ varchar │ varchar │
├─────────────┼──────────────┼─────────┼─────────┼─────────┼─────────┤
│ read_on │ TIMESTAMP │ YES │ │ │ │
│ power │ DECIMAL(8,3) │ YES │ │ │ │
└─────────────┴──────────────┴─────────┴─────────┴─────────┴─────────┘

最後，描述任何建構的元組，例如DESCRIBE VALUES (4711, '2023-05-28 11:00'::timestamp, 42);，同樣有效：

┌─────────────┬─────────────┬─────────┬─────────┬─────────┬─────────┐
│ column_name │ column_type │ null │ key │ default │ extra │
│ varchar │ varchar │ varchar │ varchar │ varchar │ varchar │
├─────────────┼─────────────┼─────────┼─────────┼─────────┼─────────┤
│ col0 │ INTEGER │ YES │ │ │ │
│ col1 │ TIMESTAMP │ YES │ │ │ │
│ col2 │ INTEGER │ YES │ │ │ │
└─────────────┴─────────────┴─────────┴─────────┴─────────┴─────────┘

在任何你不確定資料形狀的情況下，都可以使用DESCRIBE陳述式。它適用於所有型別的關係，包括本地和遠端檔案。使用的檔案型別會影響DuckDB最佳化DESCRIBE陳述式的效率。例如，遠端檔案（如Parquet格式）可以非常快速地被描述，而CSV格式的檔案由於不攜帶架構資訊，通常需要更長的時間來描述，因為引擎需要取樣其內容。

資料操縱語言查詢

在資料函式庫的上下文中，所有插入、刪除、修改和讀取資料的陳述式都被稱為資料操縱語言（DML）。本文將首先介紹INSERT和DELETE陳述式，然後討論查詢資料。我們不會詳細介紹UPDATE陳述式。SQL查詢的美妙之處在於它們非常自然地組合在一起，因此你將學到的所有內容，例如關於WHERE子句的知識，也適用於用於INSERT、DELETE、UPDATE和SELECT陳述式中的子句。

INSERT陳述式

當建立資料時，使用INSERT陳述式。插入資料是一項任務，從簡單的“fire-and-forget”陳述式到複雜的陳述式，以緩解衝突並確保高資料品質。我們從簡單和天真的開始，填充在清單3.3中建立的價格表。一個INSERT陳述式首先指定要插入的位置，然後是要插入的內容。位置是一個表名——在這裡是價格表。要插入的內容可以是欄位值的列表，但它們必須與表的欄位型別和順序相匹配。在我們的例子中，我們正在插入一行四個值，其中兩個是數字，兩個是字串，後者會自動轉換為日期：

INSERT INTO prices
VALUES (1, 11.59, '2018-12-01', '2019-01-01');

內容解密：

  1. INSERT INTO prices：指定要插入資料的目標表是 prices。
  2. VALUES (1, 11.59, '2018-12-01', '2019-01-01')：提供要插入的值，分別對應 prices 表的欄位。
    • 第一個值 1 對應 id 欄位，型別為整數。
    • 第二個值 11.59 對應某個價格欄位，假設為 price，型別為小數。
    • 第三和第四個值 是字串，分別代表日期範圍的開始和結束，會被自動轉換為 DATE 型別。

前面的查詢在幾種情況下是脆弱的。首先，依賴欄位的順序會在目標表變更時破壞你的陳述式。此外，我們明確地使用 1 作為唯一鍵。如果你第二次執行查詢，它將正確地失敗，因為表中已經包含具有給定鍵的行。第二行違反了主鍵必須是唯一的約束：

INSERT INTO prices
VALUES (1, 11.59, '2018-12-01', '2019-01-01');
Error: Constraint Error: Duplicate key "id: 1" violates primary key constraint.

內容解密：

  1. Error: Constraint Error: Duplicate key "id: 1" violates primary key constraint.：錯誤訊息指出，由於 id 為 1 的記錄已經存在，再次插入相同的主鍵值違反了主鍵唯一性約束。
  2. 這種錯誤提醒我們在設計插入操作時需要考慮主鍵的唯一性，避免重複插入相同的鍵值。

執行 SQL 查詢的資料操作語言

在前面的章節中，我們已經探討瞭如何建立表格和索引。本文將探討如何使用資料操作語言（DML）查詢來插入、更新和刪除資料。

插入資料

要將資料插入表格中，我們可以使用 INSERT INTO 陳述式。這個陳述式的基本語法是指定要插入的表格名稱和要插入的列值。

插入單一行資料

INSERT INTO prices(id, value, valid_from, valid_until)
VALUES (1, 11.59, '2018-12-01', '2019-01-01');

插入多行資料

INSERT INTO prices(value, valid_from, valid_until)
VALUES 
(11.47, '2019-01-01', '2019-02-01'),
(11.35, '2019-02-01', '2019-03-01'),
(11.23, '2019-03-01', '2019-04-01'),
(11.11, '2019-04-01', '2019-05-01'),
(10.95, '2019-05-01', '2019-06-01');

處理主鍵衝突

當插入資料時，如果主鍵已經存在，就會發生衝突。我們可以使用 ON CONFLICT 子句來處理這種情況。

INSERT INTO prices(id, value, valid_from, valid_until)
VALUES (1, 11.59, '2018-12-01', '2019-01-01')
ON CONFLICT DO NOTHING;

使用 SELECT 陳述式插入資料

我們也可以使用 SELECT 陳述式的結果來插入資料。

INSERT INTO prices(value, valid_from, valid_until)
SELECT * FROM 'prices.csv';

內容解密：

此範例展示瞭如何使用 SELECT 陳述式從 CSV 檔案中讀取資料並將其插入 prices 表格中。需要確保 CSV 檔案的結構與 prices 表格的欄位相符。

從其他表格或檔案插入資料

從 CSV 檔案插入資料到 systems 表格

首先，我們需要了解 CSV 檔案的結構，可以使用 DESCRIBE 陳述式來檢視。

INSTALL 'httpfs';
LOAD 'httpfs';
DESCRIBE SELECT * FROM 'https://oedi-data-lake.s3.amazonaws.com/pvdaq/csv/systems.csv';

然後，我們可以使用 SELECT DISTINCT 陳述式來插入不重複的資料到 systems 表格中。

INSERT INTO systems(id, name)
SELECT DISTINCT system_id, system_public_name
FROM 'https://oedi-data-lake.s3.amazonaws.com/pvdaq/csv/systems.csv'
ORDER BY system_id ASC;

內容解密：

此範例展示瞭如何使用 DESCRIBE 陳述式來檢視 CSV 檔案的結構，並使用 SELECT DISTINCT 陳述式來插入不重複的資料到 systems 表格中。

從 API 插入資料到 readings 表格

我們可以使用 read_csv_auto 函式來讀取 API 傳回的 CSV 資料，並將其插入 readings 表格中。

INSERT INTO readings(system_id, read_on, power)
SELECT SiteId, "Date-Time",
CASE
    WHEN ac_power < 0 OR ac_power IS NULL THEN 0
    ELSE ac_power
END
FROM read_csv_auto('https://developer.nrel.gov/api/pvdaq/v3/data_file?api_key=DEMO_KEY&system_id=34&year=2019');

內容解密：

此範例展示瞭如何使用 read_csv_auto 函式來讀取 API 傳回的 CSV 資料，並將其插入 readings 表格中。使用了 CASE 陳述式來處理 ac_power 小於 0 或為 NULL 的情況，將其轉換為 0。

驗證資料

我們可以使用 SELECT 陳述式來驗證資料是否正確插入。

SELECT * FROM readings WHERE date_trunc('day', read_on) = '2019-08-26' AND power <> 0;

以及檢視檢視 v_power_per_day 的結果。

SELECT * FROM v_power_per_day WHERE day = '2019-08-26';

內容解密：

此範例展示瞭如何使用 SELECT 陳述式來驗證資料是否正確插入 readings 表格中，以及檢視檢視 v_power_per_day 的結果。使用了日期截斷函式 date_trunc 來過濾特定日期的資料。