MYSQL Import and Export commands

/Usr/local/mysql/bin/mysql-uroot-proot test-e "load data infile '/usr/1.txt' replace into table test fields terminated by '\ t' (name, address )"

Info:
1. txt
Zhangsan wuhan
Lishi wuhan
Insert the 1.txt file to the mysql database.

I read some summary on the Internet and don't know if it is useful to everyone. If it is useful, I will not waste my effort when I press the button. P

1. mysql-u root-p database name </file path (add the file name directly under bin)
2. mysqlimport syntax introduction:
Mysqlimport is located in the mysql/bin directory and is a very effective tool for mysql to load (or import) data. This is a command line tool. There are two parameters and a large number of options to choose from. This tool imports a text file into your specified database and table. Finally, we want to import data from the customers.txt file to the Custermers table in the Meet_A_Geek database:
Mysqlimport Meet_A_Geek Customers.txt
Note: customers.txt is the text file for data import, while Meet_A_Geek is the database to be operated, and the table name in the database is Customers, the data format of the text file must be the same as that of the record in the MERs table. Otherwise, the mysqlimport command will fail.
The table name is the file string before the first period (.) of the imported file. Another example is as follows:
Mysqlimport Meet_A_Geek Cus.to.mers.txt
Then we will import the content in the file to the Cus table in the database Meet_A_Geek. In the above example, only two parameters are used and no more options are used. The following describes the mysqlimport options:
Option
-D or -- delete all information in the data table before the new data is imported into the data table.
-F or -- force: No matter whether an error occurs or not, mysqlimport will force data insertion to continue.
-I or -- ignore mysqlimport skips or ignores rows with the same unique keyword. Data in the import file will be ignored.
-L or-lock-tables: The table is locked before data is inserted. This prevents you from affecting your queries and updates when updating the database.
-R or-replace is opposite to-I. This option replaces records with the same unique keywords in the table.
-- Fields-enclosed-by = char specifies the data record in a text file. In many cases, the data is enclosed by double quotation marks. By default, data is not enclosed by characters.
-- Fields-terminated-by = char specifies the delimiter between values of each data. In a file separated by periods, the Delimiter is a period. You can use this option to specify the delimiter between data. The default Delimiter is a Tab)
-- Lines-terminated-by = str this option specifies the Data Separator string or character between the row and row in a text file. By default, mysqlimport uses newline as the line separator. You can choose to use a string to replace a single character: a new line or a carriage return.
Common options of the mysqlimport command include-v display version and-p prompt for password.
Example: to import a file with a comma as the delimiter, the row record format is as follows:
"1", "ORD89876", "1 Dozen Roses", "19991226"
Our task is to import the data in this file to the table Orders in the database Meet_A_Geek. We use this command:
Bin/mysqlimport-Prolactin-fields-enclosed-by = "-fields-terminated-by =, Meet_A_Geek Orders.txt
3. Use Cases for mysql Database Export and Import commands
======================================
Several common use cases:
1. Export the entire database
Mysqldump-u username-p Database Name> exported file name
Mysqldump-u wcnc-p smgp_rj_wcnc> wcnc. SQL
2. Export a table
Mysqldump-u user name-p database name Table Name> exported file name
Mysqldump-u wcnc-p smgp_rj_wcnc users> wcnc_users. SQL
3. Export a database structure
Mysqldump-u wcnc-p-d -- add-drop-table smgp_apps_wcnc> d: \ wcnc_db. SQL
-D no data -- add-drop-table adds a drop table before each create statement.
4. Import the database
Common source commands
Go to the mysql Database Console,
For example, mysql-u root-p
Mysql> use Database
Then run the source command. The following parameter is the script file (for example,. SQL used here)
Mysql> source d: \ wcnc_db. SQL
4. See MySQL Data export and import tool: mysqldump
==========================================
Batch processing is a non-interactive way to run mysql programs. Like the commands you use in mysql, you will still use these commands. To implement batch processing, you need to redirect a file to the mysql program. First, we need a text file containing the same text as the command we entered in mysql. The suffix and any other legal names do not have to end with SQL ):
USE Meet_A_Geek;
Insert into MERs (Customer_ID, Last_Name) VALUES (NULL, "Block ");
Insert into MERs (Customer_ID, Last_Name) VALUES (NULL, "Newton ");
Insert into MERs (Customer_ID, Last_Name) VALUES (NULL, "Simmons ");
Note that the syntax of these sentences must be correct, and each sentence ends with a semicolon. Select the database for the above USE command, and INSERT the command to INSERT data.
Next, we need to import the above files to the database. Before importing the files, we need to confirm that the database is running, that is, the mysqld process (or service, which is called "service" under Windows NT). unix is running. Then run the following command:
Bin/mysql-p Enter the password as prompted. If the statements in the above file are correct, the data will be imported to the database.
Use load data infile in the command line to import DATA from the file to the database:
Now you may ask yourself, "Why do I need to input all these SQL statements into the file and then run them through the program ?" This seems to require a lot of work. That's good. You may be right. But what if you have a log record generated from all these commands? This is great now. Well, most databases will automatically generate the log of event records in the database. Most logs contain original SQL commands that have been used. Therefore, if you cannot export data from your current database to a new mysql database, you can use the batch processing features of log and mysql, to quickly and conveniently import your data. Of course, this saves the trouble of typing.
LOAD DATA INFILE
This is the last method to import data to the MySQL database. This command is very similar to mysqlimport, but this method can be used in the mysql command line. That is to say, you can use this command in all the programs that use the API. With this method, you can import the data you want to import in the application.
Before using this command, the mysqld process (service) must be running. Start mysql command line:
Bin/mysql-p
Enter the password as prompted. after entering the mysql command line, enter the following command:
USE Meet_A_Geek;
Load data infile "/home/mark/data. SQL" INTO TABLE Orders;
To put it simply, the content in the file data. SQL will be imported into the table Orders. Like the mysqlimport tool, this command also has some optional parameters. For example, if you want to import data from your computer to a remote database server, run the following command:
Load data local infile "C: \ MyDocs \ SQL .txt" INTO TABLE Orders;
The LOCAL parameter above indicates that the file is a LOCAL file, and the server is the server you log on. In this way, you can use ftp to upload files to the server, and MySQL completes the process for you.
You can also set the priority of the insert statement. If you want to mark it as LOW_PRIORITY, MySQL will wait until no one else reads the table, to insert data. You can use the following command:
Load data LOW_PRIORITY INFILE "/home/mark/data. SQL" INTO TABLE Orders;
You can also specify whether to replace or ignore duplicate key values in the file and data table when inserting data. Syntax to replace duplicate key values:
Load data LOW_PRIORITY INFILE "/home/mark/data. SQL" REPLACE INTO TABLE Orders;
The above sentence looks a little clumsy, but it puts the keywords in a place that your parser can understand.
The following options describe the file record format. These options are also available in mysqlimport. They look a little different here. First, you need to use the FIELDS keyword. If you use this keyword, the MySQL parser wants to see at least one of the following options:
Terminated by character
Enclosed by character
Escaped by character
These keywords have The same usage as their parameters in mysqlimport.
Terminated by delimiter used to describe the field. It is a tab character (\ t) BY default)
Enclosed by describes the starting characters of a field. For example, enclose each field in quotation marks.
Escape characters described by escaped. Backslash :\).
The following example uses the mysqlimport command to import the same file to the database using the load data infile statement:
Load data infile "/home/mark/Orders.txt" replace into table Orders fields terminated ','
Enclosed '"';
A mysqlimport tool in the load data infile statement does not have the following features: load data infile can import files to the database based on specified columns.
This feature is important when we want to import part of the data. For example, when upgrading from an Access database to a MySQL database, you need to add some columns (columns/fields) to the MySQL database to meet some additional requirements. At this time, the data in our Access database is still available, but because the columns (fields) of the data are no longer matched with those in MySQL, The mysqlimport tool cannot be used. Even so, we can still use load data infile. The following example shows how to import DATA to a specified field:
Load data infile "/home/Order.txt" into table Orders (Order_Number, Order_Date, Customer_ID );
As you can see, we can specify the required topic (fields ). These specified fields are still enclosed in parentheses and separated by commas. If you omit any of them, MySQL will remind you of them.