MySQL import data and export database SQL statements

1, Example 1: Connect to MySQL on this computer

First in the Open DOS window, and then into the MySQL installation directory under the bin directory, such as: D:mysqlbin, and then type the command mysql-uroot-p, enter after the prompts you to lose the password, if you have just installed a good MySQL, superuser root is no password, So you can enter the direct return to MySQL, the MySQL prompt is:mysql>

2, Example 2: Connect to the remote host MySQL

Suppose the IP of the remote host is: 10.0.0.1, the username is root, and the password is 123. Type the following command:
Mysql-h10.0.0.1-uroot-p123
(Note: U and root can be without spaces, others are the same)

3, now you can import and export the database

To import a database:

Common source Commands

Enter the MySQL database console,
such as Mysql-u root-p

The code is as follows	Copy Code
Mysql>use Database

Then use the source command, followed by the script file (such as the. SQL used here)

The code is as follows	Copy Code
Mysql>source D:222.sql (Note: If you write source D:222.sql, you will report a syntax error)

To export a database:

1. Export the entire database

The code is as follows	Copy Code
Mysqldump-u user name-p database name > exported file name Mysqldump-u wcnc-p SMGP_APPS_WCNC > Wcnc.sql

2. Export a table

The code is as follows	Copy Code
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

The code is as follows	Copy Code
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

Before importing the Utf-8 database, it is best to add sql> set names ' UTF8 '; Otherwise it may lead to garbled!

Finally we know that there is also a command to import data Mysqlimport,

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:

The code is as follows	Copy Code
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.