Go MySQL Import and Export command detailed

Online to read some of the summarized information, do not know that there is no use for everyone, useful words will not be wasted I press the button a piece of painstaking.

/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
Inserting a 1.txt file into the MySQL database

Read some of the information on the Internet, do not know that there is no use for everyone, useful words will not waste me a push button a piece of painstaking; P

1.mysql-u root-p Database name </file path (directly add file name under Bin)
Introduction to 2.mysqlimport Syntax:
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 number of options to choose from. This tool imports a text file into the database and table you specify. Let's say we want to import data from the file Customers.txt into the table custermers in the database Meet_a_geek:
Mysqlimport Meet_a_geek Customers.txt
Note: Here Customers.txt is our text file to import data, and Meet_a_geek is the database we want to manipulate, the table name in the database is customers, where the data format of the text file must be consistent with the record format in the Customers table. Otherwise the Mysqlimport command will be faulted.
The name of the table is the first full period (.) file string in the import file, and another example:
Mysqlimport Meet_a_geek Cus.to.mers.txt
Then we will import the contents of the file into 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 options for Mysqlimport:
Options feature
-D or--delete all information in the data table before the new data is imported into the datasheet
-F or--force Mysqlimport will force the data to be inserted regardless of whether or not an error is encountered
-I or--ignore mysqlimport skips or ignores rows that have the same unique keyword, and the data in the import file is ignored.
-L or-lock-tables locks the table before it is inserted, which prevents the user's queries and updates from being affected when you update the database.
-R or-replace This option is the opposite of the-I option, and this option will replace the record with the same unique keyword in the representative.
--fields-enclosed-by= char Specifies, in many cases, the data in a text file is enclosed in double quotation marks. By default, the data is not surround by the word.
--FIELDS-TERMINATED-BY=CHAR Specifies the delimiter between the values of each data, in a period-delimited file, where the delimiter is a period. You can use this option to specify a delimiter between data. The default delimiter is Jump Geff (Tab)
--LINES-TERMINATED-BY=STR This option specifies the delimited string or character of data between rows and lines in a text file. By default, Mysqlimport with newline as the line delimiter. You can choose to replace a single character with a string: A new line or a carriage return.
The common options for the Mysqlimport command are the-v display (version), the-P prompt for a password (password), and so on.
Example: Importing a comma-delimited file format for a row of files is this:
"1", "ORD89876", "1 dozen Roses", "19991226"
Our task is to import the data from this file into the table orders in the database Meet_a_geek, and we use this command:
Bin/mysqlimport–prl–fields-enclosed-by= "–fields-terminated-by=, Meet_a_geek Orders.txt
Several use cases of the 3.mysql database commonly exported import commands
================================
Several common use cases:
1. Export the entire database
Mysqldump-u user name-p database name > exported file name
Mysqldump-u wcnc-p SMGP_APPS_WCNC > Wcnc.sql
2. Export a table
Mysqldump-u user name-P database name Table name > exported file name
Mysqldump-u wcnc-p SMGP_APPS_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 add a drop table before each CREATE statement
4. Import the database
Common source Commands
Go to MySQL Database console,
such as Mysql-u root-p
Mysql>use Database
Then use the source command, followed by the script file (for example, the. SQL used here)
Mysql>source D:\wcnc_db.sql
4. See the Export and Import tool for MySQL data: mysqldump
====================================
Batching is a non-interactive way to run a MySQL program, and you will still use these commands, just like the commands you use in MySQL. In order to implement batching, you redirect a file into the MySQL program, first we need a text file that contains the same text as the command we entered in MySQL. For example, we want to insert some data, using a file containing the following text (the file name is New_data.sql, of course we can also be named New_data.txt and any other legal name, do not necessarily end with a suffix SQL):
Use Meet_a_geek;
INSERT into Customers (customer_id, last_name) VALUES (NULL, "Block");
INSERT into Customers (customer_id, last_name) VALUES (NULL, "Newton");
INSERT into Customers (customer_id, last_name) VALUES (NULL, "Simmons");
Note that the syntax of the above sentences must be correct, and each sentence ends with a semicolon. The use command above selects the database, and the INSERT command inserts the data.
Below we want to import the above file into the database, before the import to confirm that the database is already running, that is, the mysqld process (or service, Windows NT is called "service", Unix under "process") is already running. Then run the following command:
Bin/mysql–p Then follow the prompts to enter the password, if the above file is not an error in the statement, then this data is imported into the database.
The command line uses load data INFILE to import from the file to the database:
Now you might ask yourself, "Why do I have to enter all these SQL statements into a file and then run them through the program?" "It looks like it needs a lot of work. Well, it's probably right that you think so. But what if you have log records from all of these commands? Now that's great, well, most databases will automatically generate log of event logs in the database. Most of the logs contain the original SQL commands that were useful. So, if you can't export data from your current database to a new MySQL database, you can use the log and MySQL batch features to quickly and easily import your data. Of course, this eliminates the hassle of typing.
LOAD DATA INFILE
This is the last method we will introduce to import data into the MySQL database. This command is very similar to Mysqlimport, but this method can be used on the MySQL command line. This means that you can use this command in all programs that use the API. With this approach, you can import the data you want to import in your application.
Before using this command, the MYSQLD process (service) must already be running. To start the MySQL command line:
Bin/mysql–p
Enter the password as prompted, and after successfully entering the MySQL command line, enter the following command:
Use Meet_a_geek;
LOAD DATA INFILE "/home/mark/data.sql" into TABLE Orders;
Simply put, this will import the contents of the file Data.sql into the table orders, like the Mysqlimport tool, which also has some parameters to choose from. For example, if you need to import data from your computer to a remote database server, you can use the following command:
LOAD DATA LOCAL INFILE "C:\MyDocs\SQL.txt" into TABLE Orders;
The local parameter above indicates that the file is a native file and that the server is the server you are logged on to. This eliminates the use of FTP to upload files to the server, MySQL for you to complete.
You can also set the priority of the INSERT statement, and if you want to mark it as low priority (low_priority), then MySQL will wait until no one else reads the table before inserting the 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 omit duplicate key values in the file and data table when inserting data. Syntax to override duplicate key values:
LOAD DATA low_priority INFILE "/home/mark/data.sql" REPLACE into TABLE Orders;
The sentence above looks a bit clumsy, but it puts the keyword in a place that your profiler can understand.
The following pair of options describe the file format, which is also available in the Mysqlimport tool. They look a little different here. First, 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 are the same as their arguments, as in Mysqlimport. The
TERMINATED by describes the delimiter for the field, which by default is the tab character (\ t)
Enclosed by describes the enclosed character of a field. For example, enclose each field in quotation marks.
Escaped by describes the escape character. The default is the Counter-bar (backslash:\).
The following is still an example of the previous Mysqlimport command, using the load DATA infile statement to import the same file into the database:
LOAD DATA INFILE "/home/mark/orders.txt" REPLACE into TABLE Orders fields TERMINATED by ', '
Enclosed by ' ";
There is no feature in the load data INFILE statement in a mysqlimport tool: Load data INFILE can import files into the database by the specified columns.
This feature is important when we want to import part of the data. For example, when we are upgrading from an Access database to a MySQL database, we need to add some columns (column/field/field) to the MySQL database to accommodate some additional needs. At this point, the data in our Access database is still available, but because the columns (field) of the data are no longer matched in MySQL, the Mysqlimport tool cannot be used anymore. However, we can still use the load data INFILE, which shows how to import data into a specified field:
LOAD DATA INFILE "/home/order.txt" into TABLE Orders (Order_number, order_date, customer_id);
As you can see, we may specify the required fields. These specified fields are still enclosed in parentheses, separated by commas, and if you omit any of them, MySQL will alert you to ^_^.

Go MySQL Import and Export command detailed