MySQL Command Line 18 Common Commands

In the daily website maintenance and management, many SQL statements will be used.
Skillful use has many benefits for website management, especially when managing stations and groups.

Here are some common commands for note.

1. Display the database
show databases
Display table
show tables;

2. Create a user
Create root user password 123

use mysql; 
grant all on *.* to root@'%' identified by '123' with grant option; 
commit;

3. Change your password

grant all on *.* to xing@'localhost' identified by '123456' with grant option; 
update user set password = password('newpwd') where user = 'xing' and host='localhost'; 
flush privileges;

4. Create the database testdb:

create database testdb;

5. Preventive database creation:

create database if not testdb;

6. Create a table:

use testdb; 
create table table1( 
username varchar(12), 
password varchar(20));

7. Preventive creation table aaa:

create table if not exists aaa(ss varchar(20));

8. View the table structure:

describe table1;

9. Insert data into table table1:

insert into table1(username,password) values 
('leizhimin','lavasoft'), 
('hellokitty','hahhahah'); 
commit;

10. Lookup table table1:

select * from table1;

11. Change data:

update table1 set password='hehe' where username='hellokitty'; 
commit;

12. Delete data:

grant all on *.* to xing@'localhost' identified by '123456' with grant option; 
update user set password = password('newpwd') where user = 'xing' and host='localhost'; 
flush privileges;

0

13. Add 1 column to the table:

grant all on *.* to xing@'localhost' identified by '123456' with grant option; 
update user set password = password('newpwd') where user = 'xing' and host='localhost'; 
flush privileges;

1

14. Modify the table structure

Create 1 table table1 from the query:

grant all on *.* to xing@'localhost' identified by '123456' with grant option; 
update user set password = password('newpwd') where user = 'xing' and host='localhost'; 
flush privileges;

2

15. Delete table table1:

drop table if exists table1; 
drop table if exists tmp;

16. Backup database testdb

grant all on *.* to xing@'localhost' identified by '123456' with grant option; 
update user set password = password('newpwd') where user = 'xing' and host='localhost'; 
flush privileges;

4

17. Delete the database testdb

grant all on *.* to xing@'localhost' identified by '123456' with grant option; 
update user set password = password('newpwd') where user = 'xing' and host='localhost'; 
flush privileges;

5

18. Restore testdb database
First, set up the testdb database, and then restore locally with the following command

grant all on *.* to xing@'localhost' identified by '123456' with grant option; 
update user set password = password('newpwd') where user = 'xing' and host='localhost'; 
flush privileges;

6

These 18 MYSQL commands are often used by administrators in daily maintenance, and skillful use of these commands will make your work very easy