Shulou(Shulou.com)06/01 Report--

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.