MySQL Import SQL files command and MySQL remote login using the detailed _mysql

To import *.sql scripts directly in MySQL Qurey brower, you cannot execute multiple SQL commands at once, and execute SQL file commands in MySQL:

Copy Code code as follows:

Mysql> source D:/myprogram/database/db.sql;

Additional MySQL common commands:

A) connection to MySQL:

Format: mysql-h host address-u user name-P user Password

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:/mysql/bin, 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 (Remote: IP address)

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:

Copy Code code as follows:

Mysql-h10.0.0.1-uroot-p123

(Note: U and root can be without spaces, others are the same)

3, quit the MySQL command

Exit (Enter)

(ii) Change Password:

Format: Mysqladmin-u username-P Old password password new password

1, Example 1: Add a password to root 123. First enter the directory C:/mysql/bin in DOS, and then type the following command:

Copy Code code as follows:

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: And then change the root password to 456

Copy Code code as follows:

MYSQLADMIN-UROOT-PAB12 Password 456

(iii) Adding new users: (Note: Unlike the above, the following is the command in the MySQL environment, 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 test1 password for ABC, so that he can log on any host, and all databases have query, insert, modify, delete permissions. First you connect the root user to MYSQL, and then type the following command: Grant Select,insert,update,delete on *.* to Test2@localhost identified by "ABC";

If you do not want to test2 the password, you can make another command to eliminate the password. Grant Select,insert,update,delete on mydb.* to Test2@localhost identified by "";

(iv) Display commands

1. Display Database list:

show databases; Only two databases were initially: MySQL and test. MySQL Library is very important it has MySQL system information, we change the password and new users, is actually using this library to operate.

2, display the data table in the library:

Copy Code code as follows:

use MySQL;//open library show tables;

3, display the structure of the data table:

Copy Code code as follows:

describe table name;

4, build the library:

Copy Code code as follows:

Create database library name;

5, the establishment of the table:

Copy Code code as follows:

Use library name; CREATE table table name (field set list);

6, delete the database and delete the table:

Copy Code code as follows:

drop Database library name; The drop table table name;

7. Empty the record of the table:

Copy Code code as follows:

Delete from table name;

8, display the records in the table:

Copy Code code as follows:

SELECT * from table name;

Exporting SQL scripts

Copy Code code as follows:

Mysqldump-u user name-p database name > storage location
Mysqldump-u root-p Test > C:/a.sql

Importing SQL scripts

Copy Code code as follows:

Mysql-u user name-p database name < storage location
Mysqljump-u root-p Test < C:/a.sql

Note that the test database must already exist

MySQL export use case for import command

1. Export the entire database

Copy Code code as follows:

Mysqldump-u user name-p database name > exported file name
Mysqldump-u wcnc-p SMGP_APPS_WCNC > Wcnc.sql

2. Export a table

Copy Code code as follows:

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

Copy Code code as follows:

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

4. Import Database

Common source Commands

Enter the MySQL database console,

such as Mysql-u root-p

Mysql>use Database

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

Copy Code code as follows:

Mysql>source D:wcnc_db.sql