Mysql Import and Export command detailed _mysql

/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 into the MySQL database

On the Internet to see some summary of the information, do not know if there is no use for everyone, useful words also do not waste me a press button a painstaking; P

1.mysql-u root-p Database name </file path (add file name directly under Bin)
2.mysqlimport Syntax Introduction:
Mysqlimport, located in the Mysql/bin directory, 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 the database and table you specify. Let's say we want to import the 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 the text file where we want to import the 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 the same as the record format in the Customers table, Otherwise the Mysqlimport command will be faulted.
The name of the table is the file string preceding the first period (.) of the import file, 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 example above, only two parameters are used and no more options are used, and the following mysqlimport options are described below:
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 continued insertion of data regardless of whether an error has been encountered
-I or--ignore mysqlimport skips or ignores rows with the same unique keyword, the data in the import file is ignored.
-L or-lock-tables Lock the table before the data 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; This option will replace the record for the same unique key in the representative.
--fields-enclosed-by= char Specifies what is included in the record of the data in the text file, in many cases the data is enclosed in double quotes. By default, data is not Fu Guachi by character.
--FIELDS-TERMINATED-BY=CHAR Specifies the separator between the values of each data, and the separator is a period in a file separated by a period. You can specify the separator between data by using this option. The default delimiter is Hop Geff (TAB)
--lines-terminated-by=str This option to specify a delimited string or character of data between rows and lines in a text file. By default, Mysqlimport is newline as a row separator. You can choose to replace a single character with a string: A new line or a carriage return.
The Mysqlimport command commonly used options include the-v display (version),-p prompts for a password (password), and so on.
Example: The record format for importing a row in a comma-delimited file file 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, we use this command:
Bin/mysqlimport–prl–fields-enclosed-by= "–fields-terminated-by=, Meet_a_geek Orders.txt
Several use cases for export import commands used in 3.mysql database
================================
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 Database
Common source Commands
Enter the MySQL database console,
such as Mysql-u root-p
Mysql>use Database
Then use the source command, followed by the script file (such as the. SQL used here)
Mysql>source D:\wcnc_db.sql
4. Refer to MySQL data export and import tools: mysqldump
====================================
Batch processing is a way to run a MySQL program that is not interactive, and you will still use these commands, just as you would for the commands you use in MySQL. In order to implement batch processing, you redirect a file to a 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, use the file containing the following text (file name New_data.sql, of course, we can also be named New_data.txt and any other legitimate names, not necessarily after the end of the 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 inserts the data into the Insert command.
Below we will import the above file into the database, before importing to confirm that the database is already running, that is, the mysqld process (or service, Windows NT hereinafter referred to as "services", UNIX is "process") already running. Then run the following command:
Bin/mysql–p Then press the prompt to enter the password, if the statement in the above file does not have errors, then the data is imported into the database.
The command line uses the load data INFILE to import data from a file into the database:
Now you might ask yourself, "Why on earth do I have to enter all these SQL statements into a file and then run them through the program?" "It looks like it's going to take a lot of work. Well, you're probably right about that. 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 records in the database. Most log contains the original SQL commands that are 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 trouble of typing.
LOAD DATA INFILE
This is the last way we're going to introduce the data into the MySQL database. This command is very similar to Mysqlimport, but this method can be used on the MySQL command line. That means you can use this command in all programs that use APIs. With this approach, you can import the data you want to import into your application.
Before using this command, the MYSQLD process (service) must already be running. To start the MySQL command line:
Bin/mysql–p
Press the prompt to enter the password, after successfully entered 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, and there are some parameters to choose from. For example, you need to import data from your computer to a remote database server, and 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 the server is the server you are logging 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, if you want to mark it as low priority (low_priority), then MySQL will wait until no one else reads the table to insert 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 ignore duplicate key values in the file and datasheet when inserting data. Syntax to override duplicate key values:
LOAD DATA low_priority INFILE "/home/mark/data.sql" REPLACE into TABLE Orders;
The above sentence looks a bit clumsy, but puts the keyword in the place where your profiler can understand it.
The following pair of options describes the file's record 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 profiler would like 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 parameters and their usage in Mysqlimport. The
Terminated by describes the separator for the field, which is the tab character by default (\ t)
Enclosed by describes the enclosing character of the field. For example, enclose each field in quotation marks.
Escaped by description of the escape character. The default is the Counter bar (backslash:\).
The following example of the previous Mysqlimport command is still used to import the same file into the database using the load DATA infile statement:
LOAD DATA INFILE "/home/mark/orders.txt" REPLACE into TABLE Orders FIELDS terminated by ', '
Enclosed by ' ";
There is no feature in a Mysqlimport tool in the Load data INFILE statement: Load data INFILE can import files into the database in the specified columns.
This feature is important when we want to import part of the data into the content. For example, when 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 requirements. At this point, the data in our Access database is still available, but because the column (field) of the data is no longer matched in MySQL, the Mysqlimport tool cannot be used again. Nevertheless, we can still use the load data INFILE, and the following example shows how to import data into a specified column (field):
LOAD DATA INFILE "/home/order.txt" into TABLE Orders (Order_number, order_date, customer_id);
As you can see, we could specify the required columns (fields). These specified fields are still enclosed in parentheses, separated by commas, and if you omit any of them, MySQL will remind you of ^_^.