How to use SQL statements to import and export data in MySQL?

How to use SQL statements to import and export data in MySQL?

MySQL is a widely used relational database management system that provides a concise and powerful SQL language to operate and manage data. In practical applications, we often need to import data into a MySQL database or export data in the database to an external file. This article will introduce how to use SQL statements to import and export data in MySQL, and provide specific code examples.

1. Data import

1. The prerequisite for data import is that there is a parameter file, and the content of the file is the data you want to import into the MySQL database.
2. Use the LOAD DATA INFILE statement to import data into the database. The specific syntax is as follows:

LOAD DATA INFILE '文件路径'
INTO TABLE `表名`
FIELDS TERMINATED BY '字段分隔符'
LINES TERMINATED BY '行分隔符'

• File path: Specify the file path to be imported;
• Table name: Specify which table to import the data into;
• Field separator: specifies the separator between fields;
• Row separator: specifies the separator between lines.

For example, to import a comma-delimited CSV file into a table named "students", the file path is "/tmp/data.csv", the statement is as follows:

LOAD DATA INFILE '/tmp/data.csv'
INTO TABLE `students`
FIELDS TERMINATED BY ','
LINES TERMINATED BY '
'

2. Data export

1. Use the SELECT INTO OUTFILE statement to export data to an external file. The specific syntax is as follows:

SELECT 列名1, 列名2, ...
INTO OUTFILE '文件路径'
FIELDS TERMINATED BY '字段分隔符'
LINES TERMINATED BY '行分隔符'
FROM `表名`

• Column name 1, column name 2, ...: Specify the column name to be exported;
• File path: Specify the export file Path;
• Field delimiter: specifies the delimiter between fields;
• Row delimiter: specifies the delimiter between rows;
• Table name: specifies which Export data from the table.

For example, to export the data in the "students" table to a tab-delimited file, the file path is "/tmp/students.txt", the statement is as follows:

SELECT *
INTO OUTFILE '/tmp/students.txt'
FIELDS TERMINATED BY '    '
LINES TERMINATED BY '
'
FROM `students`

The above is how to use SQL statements to import and export data in MySQL. By using the LOAD DATA INFILE and SELECT INTO OUTFILE statements, we can easily import data into a MySQL database or export data in the database to an external file. In practical applications, we can make appropriate adjustments and expansions as needed. Hope this article is helpful to you!

The above is the detailed content of How to use SQL statements to import and export data in MySQL?. For more information, please follow other related articles on the PHP Chinese website!