MySQL复制表-SELECT INTO FROM

* from staff; +----+----------+-------+ | id | name | slary | +----+----------+-------+ | 3 | haofugui | 10000 | | 4 | guoming | 3500 | | 5 | haotian | 2900 | +----+----------+-------+ 3 rows in set (0.00 sec) mysql> describe staff; +-------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | char(20) | YES | | NULL | | | slary | int(11) | YES | | NULL | | +-------+----------+------+-----+---------+----------------+ 3 rows in set (0.00 sec)

 

语句1：SELECT [field1, field2 | *] into Table2 [IN externaldatabase] from Table1 where condition;

语句2：SELECT A.field1,B.field2 into C [IN externaldatabase] from A [inner | left | right] join B on [condition1] where condition2;

说明：[IN externaldatabase]可以实现跨数据库的数据复制。

注意：目标表（即Table2或C）不要存在，MySQL会自动创建，这是与INSERT INTO SELECT的一个很大的不同点。

 

示例：

mysql> SELECT * INTO Persons_backup FROM Persons;

 

mysql> SELECT *      //跨数据库的数据复制
    -> INTO Persons IN ‘Backup.mdb‘
    -> FROM Persons;

　　

mysql> SELECT LastName,FirstName
    -> INTO Persons_backup
    -> FROM Persons;

　　

mysql> SELECT LastName,Firstname
    -> INTO Persons_backup
    -> FROM Persons
    -> WHERE City=‘Beijing‘;

　　

mysql> SELECT Persons.LastName,Orders.OrderNo  //表连接复制，即多表复制
    -> INTO Persons_Order_Backup
    -> FROM Persons
    -> INNER JOIN Orders
    -> ON Persons.Id_P=Orders.Id_P;

　　

MySQL复制表-SELECT INTO FROM

标签：log   gui   str   mysql   语句   ast   order   csharp   extra