SELECT... Specific usage of INTO

catalogue1 SELECT… INTO Introduction2 SELECTINTOFROM statement3 The difference between select in from and insert in to select:From the official manual address of MySQL 5.71 SELECT… INTO IntroductionSELECT… INTO is used to store query results in variables or write them to files

From the official manual address of MySQL 5.7

1 SELECT… INTO Introduction

SELECT… INTO is used to store query results in variables or write them to files.

SELECT...... [INTOOUTFILE'file_name'[CHARACTERSETcharset_name]export_ options|INTODUMPFILE'file_ name'|INTOvar_ name[,var_name]]

There are usually three uses:

• SELECT… INTOvar_ List, storing the query results in variables;
• SELECT… INTOOUTFILE writes query results to a file and can also specify column and row terminators to generate specific output formats.
• SELECT… INTODUMPFILE writes a single line of data to a file without any formatting.

In the syntax display of SELECT, the INTO clause is at the end of the entire statement. But let the INTO clause closely follow the select_ After the expr list.
An INTO clause should not be used within an embedded SELECT, as such a SELECT must return its query results to an external context.

2 SELECTINTOFROM statement

The SQL SELECT INTO statement can be used to create backup copies of tables.

Grammar:

You can insert all columns into a new table:

SELECT*INTOnew_ table_ name[INexternaldatabase]FROMold_ tablename

Or simply insert the desired columns into the new table:

SELECTcolumn_ name(s)INTOnew_ table_ name[INexternaldatabase]FROMold_ tablename

SQLSELECTINTO instance - making backup copies

The following example will create&quo; Persons” Backup copy of table:

SELECT*INTOPersons_ backupFROMPersons

The IN clause can be used to copy tables to another database:

SELECT*INTOPersonsIN'Backup.mdb'FROMPersons

If we want to copy certain fields, we can list them after the SELECT statement:

SELECTLastName,FirstNameINTOPersons_ backupFROMPersons

SQLSELECTINTO instance - with WHERE clause

We can also add a WHERE clause.

The following example is done by starting from&quo; Persons” Extracting Residences from the Table; Beijing” Created a person's information with two columns named&quo; Persons_ backup” Table:

SELECTLastName,FirstnameINTOPersons_ backupFROMPersonsWHERECity='Beijing'

SQLSELECTINTO Instance - Connected Tables

It is also possible to select data from more than one table.

The following example will create a file called&quo; Persons_ Order_ Backup” The new table contains information obtained from the Tables Persons and Orders:

SELECTPersons.LastName,Orders.OrderNoINTOPersons_ Order_ BackupFROMPersonsINNERJOINOrdersONPersons.Id_ P=Orders.Id_

3 The difference between select in from and insert in to select:

1. InsertiNTOSELECT statement
The statement form is: Insert into Table2 (field1, field2,…) select value1, value2,… fromTable1
The target table Table2 must exist. Since the target table Table2 already exists, we can insert constants in addition to the fields in the source table Table1

2. SELECTINTOFROM statement
The statement format is: SELECTvale1, value2intoTable2FromTable1
The target table Table2 does not exist, as it will be automatically created during insertion and the specified field data in Table1 will be copied to Table2.

Compare the differences between the two statements and choose the one that suits your situation.
In addition, ispass is a processed field whose value type may not be automatically created. Therefore, it is recommended to use the first form of statement to create a table and then insert the query.

This is about SELECT That's all for the article on the specific usage of INTO. For more related SELECTINTO content, please search for previous articles of Script Home or continue browsing the following related articles. We hope everyone can support Script Home more in the future!