MYSQL - MySQL Data Import and Export methods and tools introduced (3 - Exporting Data)MYSQL

MySQL Data Import and Export methods and tools introduced (3 - Exporting Data)

MySQL Data Import and Export methods and tools introduced (3 - Exporting Data)
Export data approach: Methods of Exporting Data

Translation:
This paper content from Sam's Teach Yourself MySQL in 21 Days part of the contents of the book, by Mark Maslakowski
The original English Copyright 2005 original author, a Chinese translation of the part of some additions and deletions; original book about the place too clear cut, stressing the increasing local unclear if the translation is wrong with or without the right place, correction.

Translation: David Euler, SCU. De_euler-david@www.yahoo.com.cn
Time: 2004/04/24 in Sichuan University

You can see that there are many MySQL can be imported data, but these are data transmission in half. Another is generally derived from the data in a MySQL database. There are many reasons we need to export data. One important reason is for backup database. Cost data is often expensive and require careful handling them. Regular backup can help prevent the loss of valuable data; Another reason is, perhaps you want to export data to share. In this ever-growing information technology in the world, sharing data is becoming increasingly common.

For example, Macmillan USA safeguardæŠ¤ç€a book to be published in the large-scale database. The database shared between many bookstores, so that they know what is to be published shortly. More hospitals to adopt paperless medical records so that these records can be ready to follow you. Is becoming smaller and smaller, the information is also being shared more and more. There are many export data in a way that they are related to imported data is very similar. Because, after all, these are just a perspective in the way. Data from the database is derived from the other end into the data. We are not here to discuss a wide range of other database data derived method, you will learn how to use data derived MySQL.

Use mysqldump:

(Mysqldump command in mysql / bin / directory)
Mysqldump tool many respects similar to the role of tools mysqlimport contrary. They have some of the same options. But mysqldump can do more things. It can load the entire database to a separate text file. This document will contain all the reconstruction of the database you need SQL commands. Have access to all of this command mode (Schema, followed by interpretation) and will convert it into DDL syntax (CREATE statements that the database definition language), have access to all the data, and data from these create INSERT statements. This tool will be all your database design reverse. Because all the things that are contained in a text file. This text file can use a simple batch and an appropriate SQL statements in the Introduction to MySQL. This tool is incredibly simple and fast. Will never have the slightest headache to local people.

So, if you like Meet_A_Geek load the entire database to the contents of a document, you can use the following command:
Bin / mysqldump-p Meet_A_Geek> MeetAGeek_Dump_File.txt

This statement also allows you to specify a table dump (backup / Export / loading?). If you just want to in the table in the database Meet_A_Geek Orders in the export to the entire contents of a document, you can use the following command:
Bin / mysqldump-p Meet_A_Geek Orders> MeetAGeek_Orders.txt

This is a very flexible, you can even use WHERE clause to select the records you need to export documents. To achieve this purpose, you can use similar to the following:
Bin / mysqldump-p-where = "Order_ID> 2000" Meet_A_Geek Orders> Special_Dump.txt

Mysqldump tool for a large number of options, some options following table:
Options / Option role / Action Performed
— Add-drop-table
This option will be a table in front of the DROP TABLE IF EXISTS statements, and this may lead to ensure that the MySQL database when not go wrong, because each lead back to the time table will first check whether there exist delete
— Add-locks
This option will be an INSERT statement in Kunshang LOCK TABLE and UNLOCK TABLE statement. This was to prevent these records into the database again when other users on the operating table

- C or - complete_insert
This option makes mysqldump command to each INSERT statement with a (field)

Name. When the data derived I. In addition, a database this option useful.
— Delayed-insert into the INSERT command options DELAY
F or flush—logs With this option, in the implementation of export will be set before the MySQL server log.
- F or the use of force-this option, even if there are mistakes in the future will continue to be derived
— Full this option to Add Additional information is also in the CREATE TABLE statements
- L or-lock-tables use this option, the derived table when the server will be locked to the table.
- T or-no-create-info
This option enables the mysqldump orders do not create the CREATE TABLE statement, this option you only need data without the need to DDL (database definition language), very convenient.

- D or-no-data of this option to mysqldump order not to create INSERT statements.
You only need DDL statements, you can use this option.
— Opt this option will be open all export documents would enhance the speed and create a faster introduction of a document options.
- Q or quick-This option makes the whole export MySQL will not read the contents of memory to the implementation of export, but in the read-write when documents.
- T path or tab = path-this option will be to create two documents, a file containing statements or table to create DDL statements, and the other file contains data. DDL files are named table_name.sql, data files are named table_name.txt. Path are two documents stored directories. Contents must already exist, and users orders of the document is privileged.

- W "WHERE Clause" or-where = "Where clause"
As previously mentioned, you can use this option to put the screening will be derived document data.

Assume that you need to use a form to establish an account documents, the managers look at the year (2004) All orders (Orders), they are not interested in the DDL, and the need for a comma-delimited file, since it easily imported into an Excel. To accomplish this figure, you can use the following sentence:
Bin / mysqldump-p-where "Order_Date> ='2000-01-01 '"
- Tab = / home / mark-no-create-info-fields-terminated-by =, Meet_A_Geek Orders
This will be the results you want.

Schema: Model
The set of statements, expressed in data definition language, and that completely describe the structure of a data base.
A data definition language to express the statement sets, the statement sets complete description of the database structure.

SELECT INTO OUTFILE:
If you feel cool enough mysqldump tool, the use of SELECT INTO OUTFILE bar, a MySQL with the same LOAD DATA INFILE orders contrary role of the order, and this is SELECT INTO OUTFILE order, the two orders have a lot of similarities. First of all, they have the option of almost all the same. Now you need to complete before using mysqldump completed the functions of the following steps in accordance with the instructions:

1. Ensure that mysqld process (service) has been in operation
2. Cd / usr / local / mysql
3. Bin / mysqladmin ping; / / If this sentence Qualcomm, however, can use this: mysqladmin-u root-p ping
Mysqladmin ping used to detect mysqld state that is running is alive, and that mistakes may be required user name and password.
4. Start MySQL monitor procedures.
5. Bin / mysql-p Meet_A_Geek; / / mysql command-line access, and open database Meet_A_Geek need to enter a password
6. Command line, enter this command:
SELECT * INTO OUTFILE '/ home / mark / Orders.txt'
FIELDS
TERMINATED BY = ','
FROM Orders
WHERE Order_Date> ='2000-01-01 '

You pressed the Return (Enter), the document created. The rules of the sentence like a SELECT statement, but would like to redirect the output to the screen in the document. This means that you can use to achieve multi-table JOIN senior enquiries. This feature can also be used as a generator statements.
For example, you can mix this chapter discuss ways to create a very interesting enquiries, try this:

Mysql in the establishment of a directory called Report_G.rpt text files, by adding the following line:
USE Meet_A_Geek;
INSERT INTO Customers (Customer_ID, Last_Name, First_Name)
VALUES (NULL, "Kinnard", "Vicky");
INSERT INTO Customers (Customer_ID, Last_Name, First_Name)
VALUES (NULL, "Kinnard", "Steven");
INSERT INTO Customers (Customer_ID, Last_Name, First_Name)
VALUES (NULL, "Brown", "Sam");
SELECT Last_Name INTO OUTFILE '/ home / mark / Report.rpt'
FROM Customers WHERE Customer_ID> 1;
Then confirm mysql process in the operation, and you mysql in the directory, enter the following command:
Bin / mysql <Report_G.rpt check your name as the output file will contain all of your input in the Customers table in the customer's name. If you can see, you can use today to learn the import / export (import / export) to help get statements.