MySQL INSERT INTO SELECT
简介：在本教程中，您将学习如何使用MySQL
INSERT INTO SELECT语句将SELECT语句结果数据插入表中。
MySQL INSERT INTO SELECT概述
在上一个教程中，您学习了如何使用INSERT带有VALUES子句中指定的列值列表的语句将一行或多行添加到表中。
INSERT INTO table_name(c1,c2,...) VALUES(v1,v2,..);除了在
VALUES子句中使用行值之外，还可以使用SELECT语句的结果作为INSERT语句的数据源。
以下说明了INSERT INTO SELECT语句的语法：
INSERT INTO table_name(column_list) SELECT select_list FROM another_table;如您所见，您可以使用
SELECT语句，而不是使用VALUES子句。SELECT语句可以从一个或多个表中检索数据。
当您想要将其他表中的数据复制到表时，INSERT INTO SELECT语句非常有用。
MySQL INSERT INTO SELECT 实例
假设我们有以下suppliers表格，结构如下：
CREATE TABLE suppliers (
supplierNumber INT AUTO_INCREMENT,
supplierName VARCHAR(50) NOT NULL,
phone VARCHAR(50),
addressLine1 VARCHAR(50),
addressLine2 VARCHAR(50),
city VARCHAR(50),
state VARCHAR(50),
postalCode VARCHAR(50),
country VARCHAR(50),
customerNumber INT,
PRIMARY KEY (supplierNumber)
);
请注意，您将在以后的教程中学习如何创建新表。现在，您只需执行此语句即可创建 suppliers表。
由于新合同，来自美国加利福尼亚州的所有客户都成为公司的供应商。以下查询查找美国加利福尼亚州的所有客户：
SELECT
customerNumber,
customerName,
phone,
addressLine1,
addressLine2,
city,
state,
postalCode,
country
FROM
customers
WHERE
country = 'USA' AND
state = 'CA';
这是输出：
+----------------+------------------------------+------------+---------------------------+--------------+---------------+-------+------------+---------+ | customerNumber | customerName | phone | addressLine1 | addressLine2 | city | state | postalCode | country | +----------------+------------------------------+------------+---------------------------+--------------+---------------+-------+------------+---------+ | 124 | Mini Gifts Distributors Ltd. | 4155551450 | 5677 Strong St. | NULL | San Rafael | CA | 97562 | USA | | 129 | Mini Wheels Co. | 6505555787 | 5557 North Pendale Street | NULL | San Francisco | CA | 94217 | USA | | 161 | Technics Stores Inc. | 6505556809 | 9408 Furth Circle | NULL | Burlingame | CA | 94217 | USA | | 205 | Toys4GrownUps.com | 6265557265 | 78934 Hillside Dr. | NULL | Pasadena | CA | 90003 | USA | | 219 | Boards & Toys Co. | 3105552373 | 4097 Douglas Av. | NULL | Glendale | CA | 92561 | USA | | 239 | Collectable Mini Designs Co. | 7605558146 | 361 Furth Circle | NULL | San Diego | CA | 91217 | USA | | 321 | Corporate Gift Ideas Co. | 6505551386 | 7734 Strong St. | NULL | San Francisco | CA | 94217 | USA | | 347 | Men 'R' US Retailers, Ltd. | 2155554369 | 6047 Douglas Av. | NULL | Los Angeles | CA | 91003 | USA | | 450 | The Sharp Gifts Warehouse | 4085553659 | 3086 Ingle Ln. | NULL | San Jose | CA | 94217 | USA | | 475 | West Coast Collectables Co. | 3105553722 | 3675 Furth Circle | NULL | Burbank | CA | 94019 | USA | | 487 | Signal Collectibles Ltd. | 4155554312 | 2793 Furth Circle | NULL | Brisbane | CA | 94217 | USA | +----------------+------------------------------+------------+---------------------------+--------------+---------------+-------+------------+---------+ 11 rows in set (0.01 sec)现在，您需要将
customers表中的 这些客户插入 suppliers表中。以下INSERT INTO SELECT语句可帮助您：
INSERT INTO suppliers (
supplierName,
phone,
addressLine1,
addressLine2,
city,
state,
postalCode,
country,
customerNumber
)
SELECT
customerName,
phone,
addressLine1,
addressLine2,
city,
state ,
postalCode,
country,
customerNumber
FROM
customers
WHERE
country = 'USA' AND
state = 'CA';
MySQL返回以下消息：
Query OK, 11 rows affected (0.05 sec) Records: 11 Duplicates: 0 Warnings: 0这意味着
customers表中的11行 已成功插入 suppliers表中，没有重复或警告。
以下查询suppliers在插入后返回表中的数据 ：
SELECT
*
FROM
suppliers;
运行结果：
+----------------+------------------------------+------------+---------------------------+--------------+---------------+-------+------------+---------+----------------+ | supplierNumber | supplierName | phone | addressLine1 | addressLine2 | city | state | postalCode | country | customerNumber | +----------------+------------------------------+------------+---------------------------+--------------+---------------+-------+------------+---------+----------------+ | 1 | Mini Gifts Distributors Ltd. | 4155551450 | 5677 Strong St. | NULL | San Rafael | CA | 97562 | USA | 124 | | 2 | Mini Wheels Co. | 6505555787 | 5557 North Pendale Street | NULL | San Francisco | CA | 94217 | USA | 129 | | 3 | Technics Stores Inc. | 6505556809 | 9408 Furth Circle | NULL | Burlingame | CA | 94217 | USA | 161 | | 4 | Toys4GrownUps.com | 6265557265 | 78934 Hillside Dr. | NULL | Pasadena | CA | 90003 | USA | 205 | | 5 | Boards & Toys Co. | 3105552373 | 4097 Douglas Av. | NULL | Glendale | CA | 92561 | USA | 219 | | 6 | Collectable Mini Designs Co. | 7605558146 | 361 Furth Circle | NULL | San Diego | CA | 91217 | USA | 239 | | 7 | Corporate Gift Ideas Co. | 6505551386 | 7734 Strong St. | NULL | San Francisco | CA | 94217 | USA | 321 | | 8 | Men 'R' US Retailers, Ltd. | 2155554369 | 6047 Douglas Av. | NULL | Los Angeles | CA | 91003 | USA | 347 | | 9 | The Sharp Gifts Warehouse | 4085553659 | 3086 Ingle Ln. | NULL | San Jose | CA | 94217 | USA | 450 | | 10 | West Coast Collectables Co. | 3105553722 | 3675 Furth Circle | NULL | Burbank | CA | 94019 | USA | 475 | | 11 | Signal Collectibles Ltd. | 4155554312 | 2793 Furth Circle | NULL | Brisbane | CA | 94217 | USA | 487 | +----------------+------------------------------+------------+---------------------------+--------------+---------------+-------+------------+---------+----------------+ 11 rows in set (0.01 sec)在本教程中，您学习了如何使用MySQL
INSERT INTO SELECT语句将数据从结果集插入表中。 