18 common commands under the MySQL command line
A lot of SQL statements are used in daily website maintenance and management.
Skillful use has a lot of benefits to website management, especially when it comes to site group management.
Here are some commonly used commands for notes.
1. Display the database
Show databases
Display table
Show tables
2. Create a user
Create a root user password of 123
Use mysql; grant all on *. * to root@'%' identified by '123' with grant option; commit
3. Change the 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 a database testdb:
Create database testdb
5. Preventive creation of database:
Create database if not testdb
6. Create tables:
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','hahh'); commit
10. Query table table1:
Select * from table1
11. Change the data:
Update table1 set password='hehe' where username='hellokitty'; commit
12. Delete data:
Delete from table1 where username='hellokitty'; commit
13. Add a column to the table:
Alter table table1 add column (sex varchar (2) comment 'gender', age date not null comment 'age'); commit
14. Modify the table structure
Create a table table1 from the query:
Create table tmp as select * from table1
15. Delete table table1:
Drop table if exists table1; drop table if exists tmp
16. Back up the database testdb
Mysqldump-h 192.168.3.143-u root-p pwd-x-- default-character-set=gbk > C:\ testdb.sql
17. Delete database testdb
Drop database testdb
18. Restore the testdb database
First set up the testdb database, and then use the following command for local recovery
Mysql-u root-pleizhimin testdb
