结构化查询语言/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;