MySQL imports .sql files and common commands

MySQL imports .sql files and common commands

Directly import *.sql scripts in MySQL Qurey Brower. You cannot execute multiple sql commands at one time. The command to execute sql files in mysql:

mysql> source d:/ myprogram/database/db.sql;

Attached are the common mysql commands:

1) Connect to MYSQL:

Format: mysql -h host address -u username -p user password

1. Example 1: Connect to this MYSQL on the machine

First open the DOS window, and then enter the bin directory under the mysql installation directory, for example: D:/mysql/bin, then type the command mysql -uroot -p, press Enter and you will be prompted to enter the password. If MYSQL has just been installed. The super user root does not have a password, so just press Enter to enter MYSQL. The MYSQL prompt is: mysql>

2. Example 2: Connect to MYSQL on the remote host (remote: IP address)

Assume that the IP of the remote host is: 10.0.0.1, the user name is root, and the password is 123. Then type the following command:

mysql -h10.0.0.1 -uroot -p123

(Note: u and root do not need to add spaces, the same goes for others)

3. Exit the MYSQL command

exit (Enter)

(2) Change password:

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

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

mysqladmin -uroot -password 123

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

2. Example 2: Then change the root password to 456

mysqladmin -uroot -pab12 password 456

(3) Add a new user: (Note: Different from the above, the following is a command in the MYSQL environment. So there is a semicolon at the end as the command terminator)

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

Example 1. Add a user test1 with the password abc, so that he can Log in on any host and have query, insert, modify, and delete permissions on all databases. First connect to MYSQL as the root user, and then type the following command: grant select,insert,update,delete on *.* to test2@localhost identified by "abc";

If you don’t want test2 to have a password, you can type another command Delete the password. grant select,insert,update,delete on mydb.* to test2@localhost identified by "";

(4) Display command

1. Display database list:

show databases; At the beginning, there were only two databases: mysql and test. The mysql library is very important. It contains MYSQL system information. When we change passwords and add new users, we actually use this library for operations.

2. Display the data tables in the library:

use mysql; //Open the library show tables;

3. Display the structure of the data table:

describe table name; Database name;

5. Create table:

use database name; create table table name (field setting list);

6. Delete database and table:

drop database database 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;

Export sql script

mysqldump -u username -p Database name > Storage location

mysqldump -u root -p test > c:/a.sql

Import sql script

mysql -u Username -p Database name < Storage location

mysqljump -u root -p test < c:/a.sql

Note that the test database must already exist

Use case of MySQL export import command

1. Export the entire database

mysqldump -u username- p database name > exported file name

mysqldump -u wcnc -p smgp_apps_wcnc > wcnc.sql

2. Export a table

mysqldump -u username -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 & gt;d: wcnc_db.sql

-d has no data--add-drop-table Add a drop table before each create statement

4. Import the database

Common source command

Enter the mysql database console,

For example, mysql -u root -p

mysql>use database

Then use the source command, and the following parameters are script files (such as .sql used here)

mysql>source d:wcnc_db.sql