A detailed explanation of SELECT INTO statement usage in MySQL

Syntax Introduction:

Insert all columns into a new table

The code is as follows	Copy Code
SELECT * into new_table_name [in externaldatabase] from Old_tablename

Just insert the desired column into the new table

The code is as follows	Copy Code
SELECT column_name1, column_name2 into new_table_name [in externaldatabase] from Old_tablename

Example 1: Making a backup file for the "Persons" table

The code is as follows	Copy Code
SELECT * into Persons_backup from Persons

Instance 2: With the in option, copy the table to another database

The code is as follows	Copy Code
SELECT * into Persons by ' Backup.mdb ' from Persons

Example 3: Extracts information from the "Persons" table for people residing in "Beijing" and creates a table with two columns named "Persons_backup"

The code is as follows	Copy Code
SELECT LastName, Firstname into Persons_backup from Persons WHERE city= ' Beijing '

Example 4: Connecting tables, the following example creates a new table named "Persons_order_backup" that contains information obtained from the Persons and Orders two tables

The code is as follows	Copy Code
SELECT Persons.lastname, Orders.orderno into Persons_order_backup from Persons INNER JOIN Orders on persons.id_p=orders.i D_p

Syntax error note

It is important to note that the nested query section must finally have a table alias set, as follows:

SELECT * FROM (select f1,f2 from B JOIN c) as TB

That the last as TB is required (TB is a name that can be arbitrarily taken), that is, specify an alias. Each derived new table must have an alias specified, otherwise the following error will be reported in MySQL:

ERROR 1248 (42000): Every derived TABLE must have its own alias

In addition, insert into select in MySQL cannot add values, that is, cannot be written in the following form:

INSERT into Db1_name (field1,field2) VALUES SELECT field1,field2 from Db2_name