MySQL查询将记录从一个表复制到另一个具有不同列的表
为此，您可以使用INSERTINTOSELECT语句。让我们首先创建一个表-
mysql> create table DemoTable1900 ( ClientId int NOT NULL AUTO_INCREMENT PRIMARY KEY, ClientName varchar(20), ClientAge int default 29 ) auto_increment=1000;
使用插入命令在表中插入一些记录-
mysql> insert into DemoTable1900(ClientName,ClientAge) values('Chris',45);
mysql> insert into DemoTable1900(ClientName,ClientAge) values('David',29);
mysql> insert into DemoTable1900(ClientName,ClientAge) values('Mike',37);
mysql> insert into DemoTable1900(ClientName) values('Sam');使用select语句显示表中的所有记录-
mysql> select * from DemoTable1900;
这将产生以下输出-
+----------+------------+-----------+ | ClientId | ClientName | ClientAge | +----------+------------+-----------+ | 1000 | Chris | 45 | | 1001 | David | 29 | | 1002 | Mike | 37 | | 1003 | Sam | 29 | +----------+------------+-----------+ 4 rows in set (0.00 sec)
这是创建第二个表的查询-
mysql> create table DemoTable1901 ( EmployeeId int, EmployeeName varchar(20), EmployeeAge int );
这是将记录从一个表复制到另一个具有不同列的表的查询-
mysql> insert into DemoTable1901(EmployeeId,EmployeeName,EmployeeAge) select ClientId,ClientName, ClientAge from DemoTable1900; Records: 4 Duplicates: 0 Warnings: 0
使用select语句显示表中的所有记录-
mysql> select * from DemoTable1901;
这将产生以下输出-
+------------+--------------+-------------+ | EmployeeId | EmployeeName | EmployeeAge | +------------+--------------+-------------+ | 1000 | Chris | 45 | | 1001 | David | 29 | | 1002 | Mike | 37 | | 1003 | Sam | 29 | +------------+--------------+-------------+ 4 rows in set (0.00 sec)