MySQL database import and Export method summary (it is time to summarize)

Note: The mysqldump is executed under the operating system command line.

Not executed under the MySQL command line, go to the Bin folder under the MySQL folder, such as: C:\Program files\mysql\mysql Server 5.5\bin>

general form: mysqldump-h ip-u username-p Database name > exported file name

(1)-P can not add password, only can be entered as 1 (2) mysqldump is the command under CMD, no longer mysql below. That is not allowed to enter the MySQL (that is, use dpname, you have to exit MySQL talent enough. ）

One: Export of database (backup)

0: (Back up the database to export all the tables and the data is not added-D)

Mysqldump-h localhost-u root-p Test > G:\arcgisworkspace\zypdoc\test.sql

1: (Back up the database to export all table structure)

C:\Program files\mysql\mysql Server 5.5\bin>mysqldump-h localhost-u root-p-D Test > G:\arcgisworkspace\zypdoc\te St.sql

ENTER password:******

2: (The table structure for exporting a table does not contain data)

Mysqldump-h localhost-u root-p-D test Pollution > G:\arcgisworkspace\zypdoc\test.sql

3: (Export table structure and data for a table.) Do not add-D)

Mysqldump-h 127.0.0.1-u root-p Test pollution > G:\arcgisworkspace\zypdoc\test.sql

0: syntax for backing up multiple databases C:\Program files\mysql\mysql Server 5.5\bin>

Mysqldump-h 127.0.0.1-u root-p--databases Test Bank > G:\arcgisworkspace\zypdoc\test.sql

or mysqldump-h 127.0.0.1-u root-p--databases Test Bank > G:\arcgisworkspace\zypdoc\test.txt is not practical, because the format is still SQL.

1: The syntax for backing up all databases C:\Program files\mysql\mysql Server 5.5\bin>
Mysqldump-h 127.0.0.1-u root-p--all-databases > G:\arcgisworkspace\zypdoc\test.sql (Temporary did not pass, do not know why)

II: Database import (Restore)

0: Import the database (first to create data, then import) C:\Program files\mysql\mysql Server 5.5\bin>

Mysql-h localhost-u root-p (enter MySQL below)

CREATE DATABASE ABC;

show databases; (You can see all the databases that already exist.) And the database ABC you just created)

Use ABC; (enter below the ABC database)

Show tables (see all the tables below the ABC database, empty)

SOURCE G:\arcgisworkspace\zypdoc\test.sql (Import database table)

Show tables (see all tables below the ABC database to see the table)

desc pollution; (view table structure design)

SELECT * from pollution;

Exit (or CTRL + C) quit MySQL

Three: MySQL How to export text files (backup for Csv,txt, etc., more practical)

0:mysql the external arbitrary file type exported as data in the specified format mysql>

Use dbname

Database Changed

SELECT * from pollution into outfile ' g:\\arcgisworkspace\\zypdoc\\text.txt '; (Guide the data, note the escape character OH)

SELECT * from pollution to outfile ' g:\\arcgisworkspace\\zypdoc\\text.csv ' fields TERMINATED by ' \, '; (output format Control)

The result is:

1, auto Exhaust, 200

2, building sand, 180

3, car painting, 160

4, coal, 240

5, other, 80

Knowledge Supplement:

General form: SELECT [column name] from TableName [where]

into outfile ' target file path ' [option]

5 options that are used frequently in option parameters

Fields TERMINATED by ' string ': Sets the string to the field's cut character, the default value is \ t;

field enclosed by ' character ': Sets the string to be surrounded by character fields such as char varchar text, with the default value no matter what symbol.

Fields optionally enclosed by ' character ': Sets the value of the field in the string, with the default value no matter what symbol.

LINES starting by ' string ': Sets the character at the beginning of each line, with the default value no matter what character;

Fields escaped by ' character ': Sets the escape character. The default value is \;

LINES TERMINATED by ' string ': Sets the end of each line, with the default value \ n.

Such as:

SELECT * from pollution to outfile ' g:\\arcgisworkspace\\zypdoc\\text2.csv '

Fields TERMINATED by ' \, ' optionally-enclosed by ' \ '

LINES starting by ' \> ' TERMINATED by ' \ r \ n ';

The result is:

>1, "Auto exhaust", "200"
>2, "Building Yang Sha", "180"
>3, "Car painting", "160"
>4, "Coal", "240"
>5, "Other", "80"

1:mysqldump the external arbitrary file type exported as data in the specified format C:\Program files\mysql\mysql Server 5.5\bin>

Mysqldump-u root-p-T G:\arcgisworkspace\zypdoc\ ABC pollution "--fields-terminated-by=," (remember not to do whatever extra space.) Do not have to transfer the character;-P don't write password behind;

The label folder is a folder. The file name is the table name. suffix is txt file)

Knowledge Supplement:

Mysqldump-u root-p-T target folder dbname tablename [option]

option is the same as above MySQL, just to change to

"--fields-terminated-by= character" (no matter what extra space, refers to fields-terminated-by tightly connected)

Four: How MySQL imports text files (more practical)

(1)external arbitrary file type exported as data in the specified format under MySQL mysql>

First step: Create a data table for the corresponding fields

CREATE TABLE Csv_test2 (ID int (8) Primary key,name varchar (), value Int (32));

(2) Insert: The type of the increment value starts with the wrong design, how to change: Alter TABLE CSRV_TEST2 MODIFY column value varchar (32);

(3) Step two: Import external data

LOAD DATA INFILE ' d:\\tjdata_metro\\test\\mysql_infile3.csv ' into TABLE csv_test2fields TERMINATED by ' \, ' optionally Enclosed by ' \ ' ' LINES TERMINATED by ' \ r \ n ' Ignore 1 LINES (Id,name,value);

The above lines terminated by ' \ r \ n ' is a newline symbol that requires line breaks for Windows
The above ignore 1 lines is the header row that ignores the first row.

Five: Performance optimization of data

(1) Using index optimization query: Improve the speed of queries; but be sure to avoid creating indexes after bulk INSERT, and to avoid the application of the% start: where name like '%zz '

(2) Optimize the database structure: decomposition of the field is very many tables into multiple tables, some fields are used very low frequency, can be disassembled;

(3) Some fields of some tables are used frequently, and union queries are very slow, which requires creating an intermediate table or view.

such as: Make a shopping cart, has done a user table. A list of items, detailed fields are shown in the chart. There is also a table shopcar that contains the goodsname in the goods table. Goodsprice, username in the users table

CREATE TABLE Shopcar (gname varchar), Gprice float (), uname varchar (+), foreign key (Gname) references goods (goodname), Foreign key (Gprice)  references Goods (goodprice), foreign Key (uname) references users (username))

(4) Comparison of views and intermediate tables in a database (not very well understood)

(5) Temporary table: You make a query that expects several tables to be associated together when a federated query forms a new "virtual table". The most common thing is that when you do a multi-table connection, multiple nested subqueries appear to be more messy and cut into

The temporary table looks clearer, the use is also more flexible

MySQL database import and Export method summary (it is time to summarize)