是的,我们可以在MySQL中使用select实现嵌套插入,如以下语法所示-insert into yourTableName2(yourColumnName1,yourColumnName2,.....N)
select yourColumnName1,yourColumnName2,....N from yourTableName1 where yourCondition;
我们首先来看一个示例并创建一个表-create table DemoTable1
(
Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
Name varchar(40)
);
使用插入命令在表中插入一些记录-insert into DemoTable1(Name) values('Chris');
insert into DemoTable1(Name) values('David');
insert into DemoTable1(Name) values('Bob');
使用select语句显示表中的所有记录-select *from DemoTable1;
这将产生以下输出-+----+-------+
| Id | Name |
+----+-------+
| 1 | Chris |
| 2 | David |
| 3 | Bob |
+----+-------+
3 rows in set (0.00 sec)
以下是创建第二个表的查询-create table DemoTable1
(
ClientId int,
ClientFirstName varchar(20)
);
以下是使用select实现嵌套插入的查询-insert into DemoTable2(ClientId,ClientFirstName)
select Id,Name from DemoTable1 where Id !=2;
Records : 2 Duplicates: 0 Warnings : 0
使用select语句显示表中的所有记录-select *from DemoTable2;
这将产生以下输出-+----------+-----------------+
| ClientId | ClientFirstName |
+----------+-----------------+
| 1 | Chris |
| 3 | Bob |
+----------+-----------------+
2 rows in set (0.00 sec)