MYSQL import and export command details

/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 for

On the Internet to see some summarized out of the information, do not know to everyone there is no use, useful words will not waste a button I press a piece of painstaking; P

1. Es23en-u root-p database name < / file path (under bin just add the file name)
2.mysqlimport:
mysqlimport, located in the mysql/bin directory, is a very effective tool for loading (or importing) data from mysql. 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 (text file) into the database and tables you specify. Let's say we import data from file Customers.txt into table Custermers in database Meet_A_Geek:
mysqlimport Meet_A_Geek Customers.txt
Note: here Customers.txt is the text file we want to import data, while Meet_A_Geek is the database we want to operate on. The table name in the database is Customers.
Where the name of the table is the preceding file string of the first period (.) of the imported file, another example:
mysqlimport Meet_A_Geek Cus.to.mers.txt
So we will import the contents of the file into the Cus table in the database Meet_A_Geek. In the above examples, only two parameters are used, and no more options are used. Here is the option of mysqlimport:
Options
-- d or --delete delete all information in the data table before the new data is imported into the data table
-f or --force forces the insertion of
data to continue regardless of whether an error is encountered -i or --ignore mysqlimport skips or ignores lines that have the same unique keyword, and the data in the imported file is ignored.
-l or-lock-tables data is locked before it is inserted. This prevents the user's queries and updates from being affected when you update the database.
-r or-replace is the opposite of -i. This option replaces records that have the same unique keyword in the delegate.
-- fields-enclosed-by = char specifies what to enclose records of data in a text file, often in double quotes. By default, data is not enclosed by characters.
-- es95-terminated-by =char specifies the delimiter between the values of the individual data. In a period delimited file, the delimiter is a period. You can use this option to specify the delimiter between the data. The default delimiter is the skip character (Tab)
This option specifies the delimited string or character of the data between lines in a text file. By default, mysqlimport takes newline as a row separator. You can choose to replace a single character with a string: a new line or a carriage return.
Other common options for the mysqlimport command include -v display version (version), -p prompt for password (password), and so on.
Example: import a comma-delimited file. The format of the record line in the file looks like this:
"1", "ORD89876", "1 Dozen Roses", "19991226"
Our task is to import the data from this file into the table Orders in database Meet_A_Geek. We use this command:
bin/mysqlimport, enclosed-by =", Meet_A_Orders, txt
3. The mysql database commonly exports several use cases of the import command
= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
A few common use cases:
1. Export the entire database
mysqldump -u username -p database name > The exported file name is
mysqldump -u wcnc -p smgp_apps_wcnc > wcnc.sql
2. Export a table
Es152en-u user name -p database name table name > The exported file name is
mysqldump -u wcnc -p smgp_apps_wcnc users > wcnc_users.sql
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 an drop table
before each create statement 4. Import database
The source command
is commonly used Go to mysql database console,
For example, mysql-u root-p
mysql > use database
Then use the source command, followed by the script file (as used here.sql)
mysql > source d:\wcnc_db.sql
4. See the export and import tool for MySQL data :mysqldump
= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
Batch processing is a non-interactive way to run mysql programs, and like the commands you used in mysql, you will still use them. To implement batch processing, you redirect a file to the mysql program. First we need a text file that contains the same text as the command we entered in mysql. For example, if we want to insert some data, use a file containing the following text (file name New_Data.sql, New_Data.txt and any other legal name, not necessarily ending with 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 that each sentence ends with a semicolon. The USE command above selects the database and the INSERT command inserts the data.
Now we need to import the above files into the database. Before importing, we need to make sure that the database is already running, that the mysqld process (or service, Windows NT is called "service" below, and unix is called "process" below) is already running. Then run the following command:
bin/mysql � p < /home/mark/New_Data.sql
Then you are prompted for the password, and if the statements in the file above are correct, the data is imported into the database.
The command line USES LOAD DATA INFILE to import data from the file to the database:
Now you might ask yourself, "why on earth am I typing all these SQL statements into a file and then programmatically running them?" This may seem like a lot of work. Good. You're probably right. But what if you had an log record generated from all of these commands? Now this is great, well, most databases automatically generate log of the event records in the database. Most log contains the original SQL command that was used. So if you can't export data from your current database to the new mysql database, you can use the batch processing features of log and mysql to import your data quickly and easily. Of course, this saves the trouble 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. That is, you can use this command in all programs that use API. Using this method, you can import the data you want in your application.
Before using this command, the mysqld process (service) must already be running. Launch mysql command line:
bin/mysql � p
After successfully entering the mysql command line, type 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 file data.sql into table Orders. Like the mysqlimport tool, this command also has some optional parameters. For example, if you need to import data from your computer into 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 local file and the server is the server on which you are logged. Instead of using ftp to upload files to the server, MySQL does it for you You can also set the priority of the insert statement, and if you mark it as low priority (LOW_PRIORITY), 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 ignore duplicate keys in files and tables when inserting data. Syntax for replacing duplicate key values:
LOAD DATA LOW_PRIORITY INFILE "/home/mark/data.sql" REPLACE INTO TABLE Orders;
The above sentence looks a bit clunky, but it puts the keywords in a place where your parser can understand them.
The following pair of options describe the record format of the file, which are also available in the mysqlimport tool. They look a little different here. First, the FIELDS keyword is used. If this keyword is used, the MySQL parser expects to see at least one of the following options:
TERMINATED BY character
ENCLOSED BY character
ESCAPED BY character
These keywords and their parameters are the same as in mysqlimport The delimiter of the TERMINATED BY description field, tab character (\t)
by default ENCLOSED BY describes the bracketing character of a field. For example, enclose each field in quotation marks.
ESCAPED BY describes escape characters. The default is the backbar (backslash: \).
Here, again using the previous mysqlimport command example, import the same file into the database with the LOAD DATA INFILE statement:
LOAD DATA INFILE "/home/mark/Orders.txt" REPLACE INTO TABLE Orders FIELDS TERMINATED BY ','
ENCLOSED BY '"';
The LOAD DATA INFILE statement has an mysqlimport feature that is not found in the mysqlimport tool: LOAD DATA INFILE can import files into the database by specified columns.
This feature is important when we import a portion of the data. For example, when we upgrade from Access database to MySQL database, we need to add some columns (columns/fields /field) to MySQL database to accommodate some additional needs. At this point, the data in our Access database is still available, but because the columns of this data (field) no longer match those in MySQL, the mysqlimport tool is no longer available. However, we can still use LOAD DATA INFILE. 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 can specify the required columns (fields). The specified fields are still enclosed in parentheses, separated by commas, and if you miss any of them, MySQL will remind you to ^_^.