从具有不同结构的一个表插入到MySQL中的另一个表？
为此，请使用INSERTINTOSELECT语句。让我们首先创建一个表-
create table DemoTable1 -> ( -> PersonId int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> PersonName varchar(20), -> PersonAge int, -> PersonCountryName varchar(20) -> );
使用插入命令在表中插入一些记录-
insert into DemoTable1(PersonName,PersonAge,PersonCountryName) values('Chris Brown',24,'US');
insert into DemoTable1(PersonName,PersonAge,PersonCountryName) values('John Doe',26,'UK');
insert into DemoTable1(PersonName,PersonAge,PersonCountryName) values('David Miller',23,'AUS');使用select语句显示表中的所有记录-
select * from DemoTable1;
这将产生以下输出-
+----------+--------------+-----------+-------------------+ | PersonId | PersonName | PersonAge | PersonCountryName | +----------+--------------+-----------+-------------------+ | 1 | Chris Brown | 24 | US | | 2 | John Doe | 26 | UK | | 3 | David Miller | 23 | AUS | +----------+--------------+-----------+-------------------+ 3 rows in set (0.00 sec)
这是创建第二个表的查询-
create table DemoTable2 -> ( -> EmployeeId int, -> EmployeeFullName varchar(30), -> EmployeeAge int, -> EmployeeCountryName varchar(20), -> EmployeeSalary int default 20000 -> );
这是从一个结构不同的表插入到另一个表的查询-
insert into DemoTable2(EmployeeId,EmployeeFullName,EmployeeAge,EmployeeCountryName) select PersonId,PersonName,PersonAge,PersonCountryName from DemoTable1; Records: 3 Duplicates: 0 Warnings: 0
使用select语句显示表中的所有记录-
select * from DemoTable1;
这将产生以下输出-
+------------+------------------+-------------+---------------------+----------------+ | EmployeeId | EmployeeFullName | EmployeeAge | EmployeeCountryName | EmployeeSalary | +------------+------------------+-------------+---------------------+----------------+ | 1 | Chris Brown | 24 | US | 20000 | | 2 | John Doe | 26 | UK | 20000 | | 3 | David Miller | 23 | AUS | 20000 | +------------+------------------+-------------+---------------------+----------------+ 3 rows in set (0.00 sec)