要从查询结果填充表,请使用以下语法:INSERT yourTableName(yourColumnName1,yourColumnName2,yourColumnName3,..........N)
SELECT yourColumnName1,yourColumnName2,yourColumnName3,..........N FROM yourAnotherTableName;
为了理解上述语法,让我们创建一个表。第一个表如下,并带有一些记录。创建表的查询如下:mysql> create table PopulateTableDemo
-> (
-> Id int NOT NULL AUTO_INCREMENT,
-> Name varchar(20),
-> Amount int,
-> ArrivalDateTime datetime,
-> PRIMARY KEY(Id)
-> );
现在,您可以使用insert命令在表中插入一些记录。查询如下:mysql> create table PopulateTableDemo
-> (
-> Id int NOT NULL AUTO_INCREMENT,
-> Name varchar(20),
-> Amount int,
-> ArrivalDateTime datetime,
-> PRIMARY KEY(Id)
-> );
使用insert命令在表中插入一些记录。查询如下:mysql> insert into PopulateTableDemo(Name,Amount,ArrivalDateTime) values('John',456,'2018-02-4');
mysql> insert into PopulateTableDemo(Name,Amount,ArrivalDateTime)
values('Carol',1000,'2019-10-21');
mysql> insert into PopulateTableDemo(Name,Amount,ArrivalDateTime) values('Sam',970,'2019-07-25');
mysql> insert into PopulateTableDemo(Name,Amount,ArrivalDateTime) values('Larry',1050,'2015-10-28');
使用select语句显示表中的所有记录。查询如下:mysql> select *from PopulateTableDemo;
以下是输出:+----+-------+--------+---------------------+
| Id | Name | Amount | ArrivalDateTime |
+----+-------+--------+---------------------+
| 1 | John | 456 | 2018-02-04 00:00:00 |
| 2 | Carol | 1000 | 2019-10-21 00:00:00 |
| 3 | Sam | 970 | 2019-07-25 00:00:00 |
| 4 | Larry | 1050 | 2015-10-28 00:00:00 |
+----+-------+--------+---------------------+
4 rows in set (0.00 sec)
现在,您可以创建第二个表,并从上表中填充值。创建第二个表的查询如下:mysql> create table PopulateQueryFromAnotherTable
-> (
-> Id int NOT NULL AUTO_INCREMENT,
-> UserName varchar(20),
-> Salary int,
-> DepartureDateTime datetime,
-> PRIMARY KEY(Id)
-> );
这是从第一个表PopulateTableDemo到PopulateQueryFromAnotherTable的值中填充值的查询:mysql> insert PopulateQueryFromAnotherTable(UserName,Salary,DepartureDateTime)
-> select Name,Amount,ArrivalDateTime from PopulateTableDemo;
Records: 4 Duplicates: 0 Warnings: 0
现在,使用select语句检查第二个表记录。查询如下:mysql> select *from PopulateQueryFromAnotherTable;
以下是输出:+----+----------+--------+---------------------+
| Id | UserName | Salary | DepartureDateTime |
+----+----------+--------+---------------------+
| 1 | John | 456 | 2018-02-04 00:00:00 |
| 2 | Carol | 1000 | 2019-10-21 00:00:00 |
| 3 | Sam | 970 | 2019-07-25 00:00:00 |
| 4 | Larry | 1050 | 2015-10-28 00:00:00 |
+----+----------+--------+---------------------+
4 rows in set (0.00 sec)