結構化查詢語言/INSERT 2
提示：小心，並停用 AUTOCOMMIT.
INSERT 命令的基本語法和語義在 INSERT 頁面中描述。該頁面提供了一些將固定值插入表的單行的示例。本頁描述瞭如何使用子查詢來使命令更動態。
首先，可以透過讀取系統時間或其他（準）常量來相對嚴格地評估要插入的值。
-- Use the key word CURRENT_DATE to determine the actual day.
INSERT INTO person ( id, firstname, lastname, date_of_birth, place_of_birth, ssn, weight)
VALUES (101, 'Larry, no. 101', 'Goldstein', CURRENT_DATE, 'Dallas', '078-05-1120', 95);
COMMIT;
接下來，可以透過 標量值子查詢 來評估要插入的值。這意味著，基於同一表或另一表的行，可以在執行時計算單個值。
-- Count the number of rows to determine the next ID. Caution: This handling of IDs is absolutely NOT recommended for real applications!
INSERT INTO person ( id, firstname, lastname, date_of_birth, place_of_birth, ssn, weight)
VALUES ((SELECT COUNT(*) + 1000 FROM person), -- This scalar-value subquery computes a single value, the new ID.
-- VALUES ((Select * FROM (SELECT COUNT(*) + 1000 FROM person) tmp), -- MySQL insists in using an intermediate table
'Larry, no. ?', 'Goldstein', CURRENT_DATE, 'Dallas', '078-05-1120', 95);
COMMIT;
與之前從標量值子查詢獲取單個標量值的示例類似，可以使用 表子查詢 獲取多行，並將它們插入到一個 INSERT 命令中指定的表中。此版本能夠使用單個語句插入數千行。除了其動態特性外，它還可以節省除一次之外的應用程式和 DBMS 之間的往返次數，因此比許多基於單行的 INSERT 快得多。
-- The statement doubles the number of rows within the table. It omits in the table subquery the WHERE clause and therefore
-- it reads all existing rows. Caution: This handling of IDs is absolutely NOT recommended for real applications!
INSERT INTO person (id, firstname, lastname, date_of_birth, place_of_birth, ssn, weight)
SELECT id + 1100, firstname, lastname, date_of_birth, place_of_birth, ssn, weight
FROM person;
COMMIT;
語法發生變化，以便使用完整的子查詢替換關鍵字 'VALUES'及其值列表（通常稱為'subselect'），該列表以關鍵字 'SELECT' 開頭。當然，所選列的數量和型別必須與關鍵字 'INSERT INTO' 後面的指定列列表中的列的數量和型別相對應。在子查詢中，SELECT 語句的全部功能都可以使用：JOIN、WHERE、GROUP BY、ORDER BY，尤其是其他子查詢以遞迴方式使用。存在各種各樣的用例：建立具有增加版本號的行、具有百分比增加工資的行、具有實際時間戳的行、來自同一表或另一表的行的固定值等等。
-- The next two statements compute different weights depending on the old weight
INSERT INTO person (id, firstname, lastname, date_of_birth, place_of_birth, ssn, weight)
-- the subquery starts here
SELECT id + 1200, firstname, lastname, date_of_birth, place_of_birth, ssn,
CASE WHEN weight < 40 THEN weight + 10
ELSE weight + 5
END
FROM person
WHERE id <= 10; -- only the original 10 rows from the example database
COMMIT;
-- The same semantic with a more complex syntax (to demonstrate the power of subselect)
INSERT INTO person (id, firstname, lastname, date_of_birth, place_of_birth, ssn, weight)
-- the first subquery starts here
SELECT id + 1300, firstname, lastname, date_of_birth, place_of_birth, ssn,
-- here starts a subquery of the first subquery. The CASE construct evaluates different
-- weights depending on the old weight.
(SELECT CASE WHEN weight < 40 THEN weight + 10
ELSE weight + 5
END
FROM person ssq -- alias for the table name in sub-subquery
WHERE sq.id = ssq.id -- link the rows together
)
FROM person sq -- alias for the table name in subquery
WHERE id <= 10; -- only the original 10 rows from the example database
COMMIT;
在 結構化查詢語言/示例資料庫資料#Grow_up 中所示的技術，它將現有資料（例如，用於測試目的）倍增，就是基於這樣的表子查詢。
DELETE FROM person WHERE id > 100;
COMMIT;
插入一個新的聯絡人，id 為 1301，姓氏為 'Mr. Mean'，姓氏為最小的姓氏（按字元編碼，使用 min() 函式）。其體重為所有聯絡人的平均體重（使用 avg() 函式）。
-- Two columns are computed during runtime
INSERT INTO person (id, firstname, lastname, weight)
VALUES (1301,
'Mr. Mean',
(SELECT MIN(lastname) FROM person),
(SELECT AVG(weight) FROM person)
-- the MySQL version with its intermediate tables
-- (SELECT * FROM (SELECT MIN(lastname) FROM person) tmp1),
-- (SELECT * FROM (SELECT AVG(weight) FROM person) tmp2)
);
COMMIT;
-- Check your result
SELECT * FROM person WHERE id = 1301;
為每個家庭（=姓氏）插入一個額外的聯絡人，姓氏為 'An extraordinary family member'，姓氏為家庭姓氏。僅合併原始示例資料庫中 id<=10 的行。
-- Two columns are computed during runtime. The number of involved rows is delimited by the WHERE clause.
INSERT INTO person (id, firstname, lastname)
-- here starts the subquery
SELECT MAX(id) + 1310, -- in this case the max() function works per group
'An extraordinary family member',
lastname
FROM person
WHERE id <= 10
GROUP BY lastname;
COMMIT;
-- Check your result
SELECT * FROM person WHERE id > 1310;
清理您的資料庫。
DELETE FROM person WHERE id > 1300;
COMMIT;