Shulou(Shulou.com)05/31 Report--

This article mainly explains "MySQL commands for exporting and importing data". Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Now let the editor to take you to learn the "MySQL export import data commands" bar!

MySQL export import data command

1. Export the entire

Dump-u user name-p database name > exported file name

Mysqldump-u wcnc-p smgp_apps_wcnc > wcnc.sql

two。 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 adds a drop table before each create statement

4. Import database www.2cto.com

Common source commands

Enter the my console

Such as mysql-u root-p

Mysql > use database

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

Mysql > source d:\ wcnc_db.sql

Www.2cto.com

1. Example 1: connect to the MYSQL on this computer

First, open the DOS window, and then enter the bin directory under the mysql installation directory, for example: d:\ mysql\ bin, and then type the command mysql-uroot-p. Enter prompts you to enter the password. If you have just installed MYSQL, the superuser root does not have a password, so you can enter MYSQL directly by entering the MYSQL. The prompt for MYSQL is: enter >

Example 2: connect to the MYSQL on the remote host

Suppose the IP of the remote host is 10.0.0.1, the user name is root, and the password is 123. Type the following command:

Mysql-h20.0.0.1-uroot-p123

(note: U and root do not have to add spaces, and so do others)

3. Exit the MYSQL command

Exit (enter)

Www.2cto.com

(2) change the password:

Format: mysqladmin-u username-p old password password new password

1. Example 1: add a password to root. First enter the directory C:\ mysql\ bin under DOS, and then type the following command:

Mysqladmin-uroot-password 123

Note: since root does not have a password at the beginning, the-p old password can be omitted.

2. Example 2: change the password of root to 456 www.2cto.com

Mysqladmin-uroot-pab12 password 456

(3) add new users: (note: unlike the above, the following is a command in the MYSQL environment, so it is followed by a semicolon as the command Terminator)

Format: grant select on database. * to user name @ login host identified by "password"

Example 1. Add a user's test1 password to abc, so that he can log in on any host and have the authority to query, insert, modify and delete all databases. First use the root user to connect to MYSQL, and then type the following command:

Grant select,insert,update,delete on *. * to test1@ "%" Identified by "abc"

But the increase in the number of users in example 1 is very dangerous, if you want someone who knows the password of test1, then he can log in to your mysql database on any computer on internet and can do whatever he or she wants with your data.

Example 2, add a user's test2 password to abc, so that he can only log in on localhost, and can query, insert, modify and delete the database mydb (localhost refers to the local host, that is, the host where the MYSQL database is located), so that even if the user knows the test2 password, he can not access the database directly from the internet, only through the web page on the MYSQL host.

Grant select,insert,update,delete on mydb.* to test2@localhost identified by "abc"

If you don't want test2 to have a password, you can issue another command to eliminate the password.

Grant select,insert,update,delete on mydb.* to test2@localhost identified by ""

(4) display command

1. Display a list of databases:

Show databases

At the beginning, there were only two databases: mysql and test. The mysql library is very important. it contains the system information of MYSQL. We actually use this library to change passwords and add users.

2. Display the data table in the library: www.2cto.com

Use mysql; / / Open the library

Show tables

3. Display the structure of the data table:

Describe table name

4. Build the database: www.2cto.com

Create database library name

5. Create a table:

Use library name

Create table table name (list of field settings)

6. Delete the library and the table:

Drop database library name

Drop table table name

7. Clear the records in the table:

Delete from table name

8. Display the records in the table:

Select * from table name

At this point, I believe you have a deeper understanding of the "MySQL command to export and import data". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.