18 commonly used MySQL commands-Mysql Tutorial-php.cn

18 commonly used MySQL commands

php中世界最好的语言

Release： 2018-03-05 15:00:36

Original

1353 people have browsed it

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@&#39;%&#39; identified by &#39;123&#39; with grant option; 
commit;

Copy after login

3. Change the password

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

Copy after login

4. Create database testdb:

create database testdb;

Copy after login

5. Preventatively create a database:

create database if not testdb;

Copy after login

## 6. Create a table:

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

Copy after login

7. Preventive creation of table aaa:

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

Copy after login

8. View the table structure:

describe table1;

Copy after login

## 9. Insert data into table table1:

insert into table1(username,password) values 
(&#39;leizhimin&#39;,&#39;lavasoft&#39;), 
(&#39;hellokitty&#39;,&#39;hahhahah&#39;); 
commit;

Copy after login

10. Query table table1:

select * from table1;

Copy after login

11. Change data:

update table1 set password=&#39;hehe&#39; where username=&#39;hellokitty&#39;; 
commit;

Copy after login

## 12. Delete data:

delete from table1 where username=&#39;hellokitty&#39;; 
commit;

Copy after login

13. Add a column to the table:

alter table table1 add column( 
 sex varchar(2) comment &#39;性别&#39;, 
 age date not null comment &#39;年龄&#39; 
); 
commit;

Copy after login

14. Modify the table structure

Create a table table1 from the query:

create table tmp as 
select * from table1;

Copy after login

15. Delete table table1:

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

Copy after login

16. Back up database testdb

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

Copy after login

17. Delete the database testdb

drop database testdb;

Copy after login

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

Copy after login

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!