18 commonly used MySQL commands

In daily website maintenance and MYSQL database management, we often use a lot of MYSQL commands. To facilitate your organization, the editor has listed 18 of the most commonly used commands when managing MYSQL databases.

In daily website maintenance and management, a lot of SQL statements are used.
Proficient use has many benefits for website management, especially when managing site groups.

The following are some commonly used commands for reference.

1. Show databases
show databases
Display tables
show tables;

2. Create users
Create the root user with a 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 database testdb:

##

create database testdb;

5. Preventatively create a database:

create database if not testdb;

## 6. Create a table:

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

7. Preventive creation of 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. Query table table1:

select * from table1;

11. Change 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 '性别', 
 age date not null comment '年龄' 
); 
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 database testdb

mysqldump -h 192.168.3.143 -u root -p pwd -x --default-character-set=gbk >C:\testdb.sql

17. Delete the database testdb

drop database testdb;

18. Restore the testdb database

First create the testdb database, and then use the following command to restore locally

mysql -u root -pleizhimin testdb <C:\testdb.sql

These 18 MYSQL Commands are often used by administrators in daily maintenance. Proficient use of these commands will make your work very easy

Related recommendations:

Teach you how to use MySQL: MySQL List of commonly used commands_PHP tutorial

MYSQL commonly used commands and practical skills

MySQL commonly used command line summary collection

The above is the detailed content of 18 commonly used MySQL commands. For more information, please follow other related articles on the PHP Chinese website!