Mysql 简明教程
MySQL - Insert Into Select
The MySQL Insert Into Select Statement
在 MySQL 中， INSERT INTO… SELECT 语句用于将现有表中的一行或多行添加到/插入到目标表。
In MySQL, the INSERT INTO… SELECT statement is used to add/insert one or more rows from an existing table to target table.
此语句结合了两个不同的语句：INSERT INTO 和 SELECT。
This statement is a combination of two different statements: INSERT INTO and SELECT.
-
The MySQL INSERT INTO statement is a commonly used command in database management and it requires only the name of the table and the values to be inserted into a table. However, it is important to ensure that the data being inserted matches the structure and data types of the table columns.
-
The SELECT statement is used to fetch data from an existing database table.
当一起使用上述语句时，SELECT 语句首先从现有表中获取数据， INSERT INTO 语句将检索到的数据插入到另一张表(如果它们具有相同的表结构)。
When the above mentioned statements are used together, the SELECT statement first fetches the data from an existing table and the INSERT INTO statement inserts the retrieved data into another table (if they have same table structures).
Syntax
以下是使用插入到 select 语句的语法 −
Following is the syntax for using insert into select statement −
INSERT INTO table2 (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM table1
WHERE condition;在我们执行以下查询之前，需要考虑以下一些重要事项 −
Following are some important points that we have to consider before we execute the below queries −
-
In the database where we are going to insert data, a table must already exist.
-
Both the source and target tables must match its structure.
Example
首先，让我们使用以下查询创建一个名为 CUSTOMERS 的表：
First of all, let us create a table named CUSTOMERS using the following query −
CREATE TABLE CUSTOMERS (
ID INT NOT NULL,
NAME VARCHAR(20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25),
SALARY DECIMAL (18, 2),
PRIMARY KEY (ID)
);以下查询在上述创建的表中插入 7 条记录 −
The following query inserts 7 records into the above created table −
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 ),
(3, 'Kaushik', 23, 'Kota', 2000.00 ),
(4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
(5, 'Hardik', 27, 'Bhopal', 8500.00 ),
(6, 'Komal', 22, 'Hyderabad', 4500.00 ),
(7, 'Muffy', 24, 'Indore', 10000.00 );执行以下查询以从 CUSTOMERS 表中检索所有记录 −
Execute the following query to retrieve all the records from CUSTOMERS table −
Select * From CUSTOMERS;以下为 CUSTOMERS 表 −
Following is the CUSTOMERS table −
Inserting Required Data from one Table to Another Table
我们可能会遇到某些实例，我们只想向另一张表添加少量记录。这可以通过使用 WHERE 子句来实现，该子句选择查询返回的所有行数。
We may come across some instances where we only want to add small number of records to another table. This can be achieved by using a WHERE clause to select all the number of rows that the query returned.
Example
在此之前，让我们创建一个名为 CUSTOMERS_copy 的另一个表，其结构与先前创建的 CUSTOMERS 表类似 −
Before that, let us create a another table named CUSTOMERS_copy with similar structure of previously created CUSTOMERS table −
CREATE TABLE CUSTOMERS_copy (
ID INT AUTO_INCREMENT,
NAME VARCHAR(20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25),
SALARY DECIMAL (18, 2),
PRIMARY KEY (ID)
);在以下查询中，我们尝试从 CUSTOMERS 表中获取记录并将它们插入到 CUSTOMERS_copy 表中。
In the following query, we are trying to fetch the records from the CUSTOMERS table and insert them into the CUSTOMERS_copy table.
INSERT INTO CUSTOMERS_copy (ID, NAME, AGE, ADDRESS, SALARY)
SELECT ID, NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS
WHERE AGE >= 25;Output
以上程序的输出如下所示：
The output for the program above is produced as given below −
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0Verification
若要确认年龄为 25 岁或以上的 'CUSTOMERS' 表中的记录是否已插入到目标表 'CUSTOMERS_copy' 中，请执行以下查询 −
To confirm if the records from the 'CUSTOMERS' table, where the age is 25 or older, have been inserted to the target table 'CUSTOMERS_copy', execute the following query −
SELECT * FROM CUSTOMERS_copy;年龄为 25 岁或以上的人员的记录如下所示 −
Following are the records whose age is 25 or older −
Inserting the rows with LIMIT
通过使用 MySQL LIMIT 子句，我们可以指定从查询中添加到目标表的行数。
Using the MySQL LIMIT clause, we can specify the number of rows from the query that should be added to the target table.
Example
在继续进行之前，首先让我们使用以下查询截断 CUSTOMERS_copy 表中的所有行 −
Before proceeding further, let us first truncate all rows in the CUSTOMERS_copy table using the following query −
TRUNCATE TABLE CUSTOMERS_copy;现在，我们将使用 LIMIT 子句按客户的 AGE 排序，插入 CUSTOMERS 表中前 3 条记录 −
Now, we are going to insert the top 3 records from CUSTOMERS table sorted by their AGE using the LIMIT clause −
INSERT INTO CUSTOMERS_copy (ID, NAME, AGE, ADDRESS, SALARY)
SELECT ID, NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS
ORDER BY AGE LIMIT 3;Inserting All Columns from one Table to Another Table
我们还可以将从一个表中的每一列插入到另一个表中。若要执行此操作，以下为语法 −
We can also insert every column from one to another table. To do so, following is the syntax −
INSERT INTO table2
SELECT * FROM table1在插入所有记录之前，首先使用该语句截断 CUSTOMERS_copy 表中的所有行 −
Before inserting all the records, first truncate all rows in the CUSTOMERS_copy table by using the statement −
TRUNCATE TABLE CUSTOMERS_copy;在以下查询中，我们尝试将 CUSTOMERS 表中的所有列添加到 CUSTOMERS_copy 表中 −
In the following query, we are trying to add all the columns from the CUSTOMERS table to the CUSTOMERS_copy table −
INSERT INTO CUSTOMERS_copy SELECT * FROM CUSTOMERS;INSERT INTO SELECT Using a Client Program
除了使用 MySQL 查询执行 INSERT INTO … SELECT 语句之外，我们还可以使用 Node.js、PHP、Java 和 Python 等客户端程序来取得相同的结果。
Besides using MySQL queries to perform the INSERT INTO … SELECT statement, we can also use client programs like Node.js, PHP, Java, and Python to achieve the same result.