MySQL practical commands
Wikipedia, the free encyclopedia 1) Connect to MYSQL:
Format: mysql -h host address -u username -p user password
1. Example 1: Connect to MYSQL on this machine
First open the DOS window, and then enter the bin directory under the mysql installation directory, for example: D:mysqlbin, then type the command mysql -uroot -p, press Enter and you will be prompted to enter your password. If MYSQL has just been installed, 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
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 applies to others)
3. Exit the MYSQL command
exit (carriage return)
(2) Change password:
Format: mysqladmin -u username -p old password password new password
1. Example 1: Add a password of 123 to root. First enter the directory C:mysqlbin 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: Change the root password to 456
mysqladmin -uroot -pab12 password 456
(3) Add a new user: (Note: Unlike the above, the following are commands in the MYSQL environment, so they are followed by a semicolon 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 test1@"% " Identified by "abc";
But the user added in Example 1 is very dangerous. If someone knows the password of test1, then he can log in to your mysql database on any computer on the Internet and do whatever he wants with your data. Solution See example 2.
Example 2: Add a user test2 with the password 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 password of test2, he cannot directly access the database from the Internet, and can only access it 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 type another command to eliminate the password.
grant select,insert,update,delete on mydb.* to test2@localhost identified by "";
(4) Display command
1. Display the 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 table in the library:
use mysql; //Open the library
show tables;
3. Display the structure of the data table:
describe table name;
4. Build database:
create database library name;
5. Create table:
use library name;
create table table name (field setting list);
6. Delete database and 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;
MySQL import and export commands
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 >d:wcnc_db.sql
-d no data --add-drop-table adds a drop table before each create statement
4. Import the common source command into the database and enter the mysql database console.
Such as mysql -u root -p
mysql>use database and then use the source command, the following parameters are script files (such as .sql used here)
mysql>source d:wcnc_db.sql (Note: If it is written as source d:wcnc_db.sql, a syntax error will be reported)
Note: The database file using the import database command must be in the bin directory of mysql.