MySQL複製表-SELECT INTO FROM

標籤：log   gui   str   mysql   語句   ast   order   csharp   extra   

基礎Table：

mysql> select * 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