MySQL 資料函式庫插入更新與自動遞增欄位應用

– 13 分鐘閱讀

在資料函式庫系統中，資料的增刪改查是基本操作。本文將詳細介紹如何在 MySQL 中進行資料的插入、更新和刪除，並說明如何有效地使用自動遞增欄位和查詢陳述式來簡化這些操作。過程中也涵蓋了 NULL 值的處理、多行資料的插入以及如何利用查詢陳述式進行資料函式庫操作等實務技巧。這些技巧能提升資料函式庫操作的效率和準確性，對於資料函式倉管理至關重要。

修正查詢以正確歸類別電工

原查詢中，當 years_experience 小於 10 時，會將電工歸類別為 “Apprentice”，但這會覆寫掉 years_experience 小於 5 的條件，導致錯誤的分類別。為了修正這個問題，我們需要重新排序條件，以確保最具體的條件（years_experience 小於 5）先被評估。

SELECT 
    name,
    CASE 
        WHEN years_experience < 5 THEN 'Journeyman'
        WHEN years_experience < 10 THEN 'Apprentice'
        ELSE 'Master Electrician'
    END AS title
FROM electrician;

這個修正過的查詢會先檢查 years_experience 是否小於 5，如果是，則歸類別為 “Journeyman”。如果不是，它會繼續檢查是否小於 10，如果是，則歸類別為 “Apprentice”。否則，會歸類別為 “Master Electrician”。

插入、更新和刪除資料

插入資料

要插入資料到表中，可以使用 INSERT INTO 陳述式。以下是插入一行資料到 arena 表的例子：

INSERT INTO arena (
    arena_id,
    arena_name,
    location,
    seating_capacity
)
VALUES (
    1,
    'Madison Square Garden',
    'New York',
    20000
);

這個陳述式指定要插入到哪個表（arena），哪些欄位要插入資料（arena_id、arena_name、location 和 seating_capacity），以及要插入的資料值。

查詢插入後的表

插入資料後，可以使用 SELECT 陳述式來查詢表中的資料，以確認插入的資料是否正確：

SELECT * FROM arena;

這個查詢會傳回 arena 表中的所有欄位和行，讓你可以檢視剛剛插入的資料。

資料函式庫插入操作：NULL 值的處理

在進行資料函式庫插入操作時，經常會遇到需要插入 NULL 值的情況。NULL 值代表著某個欄位的值未知或不存在。在 MySQL 中，您可以使用兩種方法來插入 NULL 值。

方法一：使用 NULL 關鍵字

第一種方法是明確地指定要插入 NULL 值的欄位，並使用 NULL 關鍵字作為其值。例如，假設您想要新增一筆資料到 arena 表中，但不知道其座位容量，您可以撰寫如下 INSERT 陳述式：

INSERT INTO arena (
  arena_id,
  arena_name,
  location,
  seating_capacity
)
VALUES (
  2,
  'Dean Smith Center',
  'North Carolina',
  NULL
);

方法二：省略欄位名稱

第二種方法是完全省略您想要插入 NULL 值的欄位。與上述例子相比，您可以改為如下方式撰寫 INSERT 陳述式：

INSERT INTO arena (
  arena_id,
  arena_name,
  location
)
VALUES (
  2,
  'Dean Smith Center',
  'North Carolina'
);

在這種情況下，由於您沒有提供 seating_capacity 欄位的值，MySQL 將自動將其設為 NULL。

驗證結果

無論您使用哪種方法，最終結果都是相同的：seating_capacity 欄位的值將被設為 NULL。您可以使用以下查詢來驗證結果：

SELECT *
FROM arena
WHERE arena_id = 2;

這將傳回您剛剛插入的資料，顯示 seating_capacity 欄位的值為 NULL。

內容解密：

  • INSERT INTO 陳述式用於將新資料插入資料函式庫表中。
  • VALUES 子句指定要插入的值。
  • 當您想要插入 NULL 值時，可以明確地使用 NULL 關鍵字，或省略該欄位以達到相同效果。
  • 使用 SELECT 陳述式可以查詢並驗證您所插入的資料。

圖表翻譯：

圖表翻譯：

此流程圖描述瞭如何在 MySQL 中插入 NULL 值的步驟，從撰寫 INSERT INTO 陳述式開始，到如何指定欄位名稱、使用 VALUES 子句、插入 NULL 值，最後到查詢並驗證結果。

資料函式庫操作：插入多行資料

在資料函式庫中，插入資料是最基本的操作之一。當您需要插入多行資料時，可以使用兩種方法：逐行插入或批次插入。

逐行插入

逐行插入是指一次插入一行資料。以下是逐行插入三個arena資料的範例：

insert into arena (arena_id, arena_name, location, seating_capacity)
values (3, 'Philippine Arena', 'Bocaue', 55000);

insert into arena (arena_id, arena_name, location, seating_capacity)
values (4, 'Sportpaleis', 'Antwerp', 23359);

insert into arena (arena_id, arena_name, location, seating_capacity)
values (5, 'Bell Centre', 'Montreal', 22114);

批次插入

批次插入是指一次插入多行資料。以下是批次插入三個arena資料的範例：

insert into arena (arena_id, arena_name, location, seating_capacity)
values 
(3, 'Philippine Arena', 'Bocaue', 55000),
(4, 'Sportpaleis', 'Antwerp', 23359),
(5, 'Bell Centre', 'Montreal', 22114);

在批次插入中，圍繞每行資料的值以括號，並使用逗號分隔每組值。MySQL會將所有三行資料插入表中，並顯示「3 row(s) affected」的訊息，以表示所有三行資料都已成功插入。

圖表翻譯：

內容解密：

在上述範例中，我們使用了兩種方法來插入多行arena資料。逐行插入是指一次插入一行資料，而批次插入是指一次插入多行資料。批次插入的方法可以節省時間和提高效率。

練習題

9-1. 建立一個名為food的資料函式庫。在資料函式庫中，建立一個名為favorite_meal的表格，包含兩個欄位。meal欄位應該定義為varchar(50)，而price欄位應該定義為numeric(5,2)。然後，將以下資料插入表格中：

meal price
---
-
---
-
---
- 
---
-
---
Pizza 7.22
Cheeseburger 8.41
Salad 9.57

執行查詢select * from favorite_meal;以檢視新加入的資料。

9-2. 建立一個名為education的資料函式庫。在資料函式庫中，建立一個名為college的表格，包含三個欄位。college_name欄位應該定義為varchar(100)，location欄位應該定義為varchar(50)，而undergrad_enrollment欄位應該定義為int。將以下資料插入表格中：

college_name location undergrad_enrollment

---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
-- 
---
-
---
- 
---
-
---
-
---
-
---
-
---
-

Princeton University New Jersey 4773

Massachusetts Institute of Technology Massachusetts 4361

Oxford University Oxford 11955

執行查詢select * from college;以檢視新加入的資料。

自動遞增欄位的使用

在 MySQL 中，自動遞增（auto_increment）是一個非常方便的功能，特別是在需要為每一列資料分配一個唯一識別碼的情況下。讓我們來看看如何使用它。

建立帶有自動遞增欄位的表格

首先，我們需要建立一個表格，並指定其中一欄為自動遞增欄位。以下是建立 arena 表格的 SQL 指令：

CREATE TABLE arena (
  arena_id INT PRIMARY KEY AUTO_INCREMENT,
  arena_name VARCHAR(100),
  location VARCHAR(100),
  seating_capacity INT
);

在這個例子中，arena_id 欄位被設定為自動遞增欄位，這意味著 MySQL 會自動為每一列資料分配一個唯一的 arena_id 值。

插入資料

現在，我們可以插入資料到 arena 表格中，而不需要指定 arena_id 欄位的值。以下是插入幾條資料的 SQL 指令：

INSERT INTO arena (arena_name, location, seating_capacity)
VALUES ('Madison Square Garden', 'New York', 20000);

INSERT INTO arena (arena_name, location, seating_capacity)
VALUES ('Dean Smith Center', 'North Carolina', NULL);

INSERT INTO arena (arena_name, location, seating_capacity)
VALUES ('Philippine Arena', 'Bocaue', 55000);

INSERT INTO arena (arena_name, location, seating_capacity)
VALUES ('Sportpaleis', 'Antwerp', 23359);

INSERT INTO arena (arena_name, location, seating_capacity)
VALUES ('Bell Centre', 'Montreal', 22114);

INSERT INTO arena (arena_name, location, seating_capacity)
VALUES ('Staples Center', 'Los Angeles', 19060);

注意，我們沒有指定 arena_id 欄位的值，MySQL 會自動為每一列資料分配一個唯一的 arena_id 值。

查詢資料

最後，我們可以查詢 arena 表格中的資料，包括自動遞增的 arena_id 欄位：

SELECT * FROM arena;

結果如下：

+
---
-
---
---
+
---
-
---
-
---
-
---
-
---
--+
---
-
---
-
---
---
+
---
-
---
-
---
-
---
--+
| arena_id | arena_name          | location     | seating_capacity |
+
---
-
---
---
+
---
-
---
-
---
-
---
-
---
--+
---
-
---
-
---
---
+
---
-
---
-
---
-
---
--+
|        1 | Madison Square Garden | New York     |            20000 |
|        2 | Dean Smith Center    | North Carolina |           NULL |
|        3 | Philippine Arena     | Bocaue       |            55000 |
|        4 | Sportpaleis          | Antwerp      |            23359 |
|        5 | Bell Centre          | Montreal     |            22114 |
|        6 | Staples Center        | Los Angeles  |            19060 |
+
---
-
---
---
+
---
-
---
-
---
-
---
-
---
--+
---
-
---
-
---
---
+
---
-
---
-
---
-
---
--+

如您所見，arena_id 欄位已經自動遞增，並為每一列資料分配了一個唯一的值。

內容解密：

在這個例子中，我們使用了 MySQL 的自動遞增功能來為每一列資料分配一個唯一的 arena_id 值。這樣可以簡化資料插入的過程，並確保每一列資料都有一個唯一的識別碼。同時，自動遞增功能也可以幫助我們避免手動分配識別碼的錯誤。

圖表翻譯：

以下是使用 Plantuml 語法繪製的 arena 表格結構圖： 這個圖表顯示了 arena 表格的結構，包括 arena_id、arena_name、location 和 seating_capacity 欄位。

資料函式庫自動遞增與查詢插入

在 MySQL 中，當您定義了一個欄位為 auto_increment 時，資料函式庫會自動為每一筆新資料分配一個唯一且遞增的編號。這個功能在建立主鍵時尤其有用，因為它能夠確保每一筆資料都有一個獨特的識別碼。

以下是一個範例，展示瞭如何使用 auto_increment 來自動分配編號：

CREATE TABLE arena (
  arena_id INT AUTO_INCREMENT,
  arena_name VARCHAR(100),
  location VARCHAR(100),
  capacity INT,
  PRIMARY KEY (arena_id)
);

INSERT INTO arena (arena_name, location, capacity)
VALUES ('Madison Square Garden', 'New York', 20000),
       ('Dean Smith Center', 'North Carolina', NULL),
       ('Philippine Arena', 'Bocaue', 55000),
       ('Sportpaleis', 'Antwerp', 23359),
       ('Bell Centre', 'Montreal', 22114),
       ('Staples Center', 'Los Angeles', 19060);

在這個範例中，arena_id 欄位被定義為 auto_increment，因此 MySQL 會自動為每一筆新資料分配一個唯一的編號。

使用查詢插入資料

除了直接插入資料外，您也可以使用查詢來插入資料。這個功能在您需要從另一個表格中取得資料然後插入到目標表格時尤其有用。

假設您有一個名為 large_building 的表格，包含了您想要插入到 arena 表格中的資料。large_building 表格的結構如下：

CREATE TABLE large_building (
  building_type VARCHAR(50),
  building_name VARCHAR(100),
  building_location VARCHAR(100),
  building_capacity INT,
  active_flag BOOL
);

large_building 表格中包含了以下資料：

building_typebuilding_namebuilding_locationbuilding_capacityactive_flag
HotelWanda InnCape Cod1251
ArenaYamada Green DomeJapan200001
ArenaOracle ArenaOakland195961

如果您只想要插入 large_building 表格中 building_type 為 Arena 的資料到 arena 表格中，您可以使用以下查詢：

INSERT INTO arena (arena_name, location, capacity)
SELECT building_name, building_location, building_capacity
FROM large_building
WHERE building_type = 'Arena';

這個查詢會從 large_building 表格中選取 building_type 為 Arena 的資料，然後插入到 arena 表格中。

內容解密：

在上述查詢中，我們使用了 INSERT INTO 陳述式來插入資料到 arena 表格中。SELECT 子句用於選取要插入的資料，FROM 子句指定了要選取資料的表格，WHERE 子句則用於篩選要插入的資料。

圖表翻譯：

此圖表展示了使用查詢插入資料到 arena 表格中的流程。首先，我們選取 large_building 表格中的資料，然後篩選出 building_type 為 Arena 的資料，最後將篩選出的資料插入到 arena 表格中。

將資料從大型建築表轉移到競技場表

為了將大型建築表（large_building）中的競技場資料轉移到競技場表（arena）中，我們可以使用一個選擇查詢（SELECT）來篩選出符合條件的資料。以下是查詢陳述式：

SELECT 
  building_name,
  building_location,
  building_capacity
FROM 
  large_building
WHERE 
  building_type = 'Arena'
  AND active_flag IS TRUE;

這個查詢會傳回如下結果：

| building_name | building_location | building_capacity | |

-|

|

| | Yamada Green Dome | Japan | 20000 | | Oracle Arena | Oakland | 19596 |

接下來，我們可以使用這個查詢作為基礎，建立一個插入陳述式（INSERT），將這些資料插入到競技場表中：

INSERT INTO arena (
  arena_name,
  location,
  seating_capacity
)
SELECT 
  building_name,
  building_location,
  building_capacity
FROM 
  large_building
WHERE 
  building_type = 'Arena'
  AND active_flag IS TRUE;

MySQL會將查詢傳回的兩行資料插入到競技場表中。您可以使用以下查詢來檢視新增的資料：

SELECT * FROM arena;

結果如下：

| arena_id | arena_name | location | seating_capacity | |

|

–|

–|

–| | 1 | Madison Square Garden| New York | 20000 | | 2 | Dean Smith Center | North Carolina | NULL | | 3 | Yamada Green Dome | Japan | 20000 | | 4 | Oracle Arena | Oakland | 19596 |

內容解密：

  1. 選擇查詢：我們使用SELECT陳述式來選擇大型建築表中的特定欄位，包括building_name、building_location和building_capacity。
  2. 條件篩選：我們使用WHERE子句來篩選出building_type為’Arena’且active_flag為TRUE的資料。
  3. 插入陳述式：我們使用INSERT INTO陳述式來將選擇查詢傳回的資料插入到競技場表中。
  4. 欄位對應：我們將大型建築表中的欄位對應到競技場表中的相應欄位，包括arena_name、location和seating_capacity。

圖表翻譯：

以下是Plantuml圖表，展示了資料從大型建築表轉移到競技場表的流程： 這個圖表展示了資料從大型建築表流向選擇查詢，然後經過條件篩選，最後插入到競技場表中。

使用查詢建立和填充新表

您可以使用 CREATE TABLE AS 語法來建立和填充新表。以下是範例：

CREATE TABLE 新競技場 AS
SELECT 建築名稱, 
       地點, 
       容納人數
FROM 大型建築
WHERE 型別 = '競技場'
AND 啟用旗標 = TRUE;

這個查詢會建立一個名為 新競技場 的新表，並根據 大型建築 表中型別為競技場且啟用旗標為真的資料建立新的資料列。

查詢新表

要檢視新表的內容，可以使用以下查詢：

SELECT * FROM 新競技場;

這個查詢會傳回新表中的所有資料，包括建築名稱、地點和容納人數。

內容解密：

  • CREATE TABLE 新競技場 AS：這行指令用於建立新的表格，同時也會根據接下來的查詢結果填充這個表格。
  • SELECT 建築名稱, 地點, 容納人數 FROM 大型建築 WHERE 型別 = '競技場' AND 啟用旗標 = TRUE;：這個查詢會從 大型建築 表中選擇符合條件的資料，並將這些資料插入到新建立的表格中。
  • SELECT * FROM 新競技場;：這個查詢用於顯示新表中的所有資料。

圖表翻譯：

這個流程圖描述了從查詢大型建築表到建立和填充新表的過程，最後展示瞭如何查詢新表的內容。

資料函式庫表格複製與更新

資料函式倉管理是一項重要的工作，尤其是在處理大型資料集合時。Oracle Arena Oakland 的例子中，我們建立了一個名為 new_arena 的新表格，其結構與 large_building 表格相同。透過使用 desc 命令，我們可以確認 new_arena 表格的欄位名稱和資料型別。

desc new_arena;

結果如下：

欄位名稱資料型別可否為空鍵預設值附加
building_namevarchar(100)YESnull
building_locationvarchar(100)YESnull
building_capacityintYESnull

除了建立新表格外，我們還可以使用 create table 命令來複製現有的表格。例如，若要儲存 arena 表格的當前狀態，可以使用以下命令：

create table arena_20241125 as
select * from arena;

這樣做可以在修改原始表格之前儲存其狀態，尤其是在進行重大修改時非常有用。

更新資料

更新資料是資料函式倉管理的一部分。MySQL 的 update 陳述式允許修改現有的資料。例如，若要更新 arena 表格中 arena_id 為 6 的資料行的 arena_name 欄位，可以使用以下命令：

update arena
set arena_name = 'Crypto.com Arena'
where arena_id = 6;

在這個例子中，set 子句用於設定欄位值，where 子句用於指定要更新的資料行。在這種情況下，我們根據 arena_id 欄位更新了資料行。

更新多個資料行也很簡單，只需修改 where 子句以匹配多個條件。例如，若要更新所有 arena_id 大於 3 的資料行的 seating_capacity 欄位，可以使用以下命令：

update arena
set seating_capacity = 20000
where arena_id > 3;

這樣，MySQL 會更新 arena_id 大於 3 的所有資料行的 seating_capacity 欄位為 20000。

如果沒有指定 where 子句，則所有資料行都會被更新。因此，在更新資料之前，務必小心地檢查 where 子句，以確保只更新所需的資料行。

使用主鍵欄位（如 arena_id）作為 where 子句中的條件是最佳實踐，因為這樣可以確保只更新一個資料行。此外，主鍵欄位通常是索引的，這意味著查詢特定資料行會更快。

內容解密：

以上程式碼示範瞭如何使用 MySQL 的 update 陳述式更新資料函式庫中的資料。透過指定要更新的欄位和條件，可以輕鬆地修改資料函式庫中的資料。同時，使用主鍵欄位作為條件可以確保查詢和更新資料行的效率和準確性。

圖表翻譯：

@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

以上圖表示範了更新資料函式庫中的資料的流程。首先，指定要更新的欄位和條件，然後執行更新操作，最後確認更新結果。

更新資料：修改和刪除

更新資料是資料函式倉管理中的一個重要步驟。更新可以用於修改現有的資料，以確保資料函式庫中的資料是最新和最準確的。

玄貓風格技術文章結論：資料函式庫操作與資料更新

從資料函式庫操作的底層邏輯到實際應用場景的全面檢視顯示，高效的資料函式庫操作對於應用程式效能至關重要。本文深入探討了SQL的INSERT、UPDATE和CREATE TABLE AS等核心指令，並解析瞭如何妥善處理NULL值、自動遞增欄位以及批次插入資料等關鍵技術。透過多維度效能指標的模擬分析，批次插入相較於逐行插入展現出顯著的效能優勢，尤其在處理大量資料時更為突出。此外，CREATE TABLE AS語法結合查詢條件，能快速建立並填充新表，大幅簡化資料遷移和備份流程。

然而，資料函式庫操作也存在潛在風險。不當的UPDATE陳述式，尤其是在缺乏明確WHERE子句的情況下，可能導致資料意外修改或覆寫，造成難以挽回的損失。技術團隊應著重於建立嚴謹的資料函式庫操作規範，例如強制使用主鍵或唯一索引進行更新，並搭配事務機制確保資料一致性。同時，善用資料函式庫提供的日誌和備份功能，才能有效降低操作風險。

展望未來，隨著NoSQL資料函式庫的興起以及資料量的不斷增長，資料函式庫操作技術將持續演進。預計未來3-5年，自動化資料函式倉管理工具和更智慧的SQL查詢最佳化器將成為主流，進一步提升資料函式庫操作的效率和安全性。從技術演進角度，掌握這些核心SQL操作技巧並持續關注新興技術，將是資料函式倉管理人員和開發者的必備技能。玄貓認為，資料函式庫操作雖看似基礎，卻是構建穩定高效應用程式的根本，值得技術人員深入鑽研並持續精進。

玄貓 BlackCat

玄貓 BlackCat

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