MySQL utility command

1) connect MYSQL:
Format: mysql-h host address -u username -p user password
1. Example 1: connect to MYSQL on the local machine
First open the DOS window, then enter the bin directory under the mysql installation directory, such as: D:\mysql\bin, and then enter the command mysql-uroot-p, enter the password, if you just installed MYSQL, super user root is not password, so directly enter MYSQL, MYSQL prompt is: mysql >
2. Example 2: MYSQL connected to a remote host
assumes 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 Spaces, and the others are the same)
3, exit MYSQL command
exit (enter)
(2) change password:
Format: mysqladmin-u username -p old password password new password
Example 1: add password 123 to root. First enter the directory C:\mysql\bin under DOS and then type the following command:
mysqladmin -uroot -password 123
Note: since root did not have a password at the beginning, the old password 1 item of -p can be omitted.
2. Example 2: change the password of root to 456
mysqladmin -uroot -pab12 password 456
(3) add new users: ( note: unlike above, the following commands are terminated with a semicolon because they are commands in the MYSQL environment)
Format: grant select on database.* to username @ login host identified by "password"
Add one user test1 password to abc, so that he can log in on any host, and all databases have the right to query, insert, modify, delete. First use root to connect to MYSQL, and then type the following command:
grant select,insert,update,delete on *.* to test1@"%" Identified by "abc";
If someone knows the password for test1, they can log into your mysql database from any computer on internet and do whatever they want with your data. The solution is shown in example 2.
Case 2, add 1 user test2 password for abc, let he can login on localhost only, and can be conducted on database mydb query, insert, modify, delete operations (localhost refers to the local host, namely MYSQL database to the host), so that users use know test2 password, he cannot directly access the database from the internet, only through web MYSQL hosts to access the page.
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 cancel the password.
grant select,insert,update,delete on mydb.* to test2@localhost identified by "";
(4) displays the command
1. Display database list:
show databases;
We started with two databases: mysql and test. The mysql library is very important because it has the system information of MYSQL in it, so we change the password and add new users, and we actually use this library to operate.
2. Data table in the display library:
use mysql; / / open the library
show tables;
3. Structure of display data table:
describe table name;
4. Library construction:
create database library name;
5. Table building:
use library name;
create table table name (list of field Settings);
6. Delete libraries and delete tables:
drop database library name;
drop table table name;
7. Empty the records in the table:
delete from table name;
8. Show the records in the table:
select * from table name;