Summary of common mysql database commands in CentOS

Summary of common mysql database commands in CentOS

Mysql database usage Summary

This article mainly records some mysql commands for future query.

1. Change the root password

Mysqladmin-uroot password 'yourpassword'

2. remotely log on to the mysql server

Mysql-uroot-p-h192.168.137.10-P3306

3. query the database

Show databases;

4. Enter a database

Use databasename;

5. List tables in the database

Show tables;

6. view all fields in a table

Desc slow_log;

Show create table slow_log \ G; (displays not only table information, but also table creation statements)

7. view the current user

Select user ();

8. view the current database

Select database ();

9. Create a new database (character set can be specified)

Create database db1 charset utf8;

10. Create a new table

Create table t1 ('id' int (4), 'name' char (40 ));

11. view the database version

Select version ();

12. view the database status

Show status; current session status

Show global status; global Database status

Show slave status \ G; view Master/slave database status information

13. query database Parameters

Show variables;

14. modify Database Parameters

Show variables like 'max _ connect % ';

Set global max_connect_errors = 1000)

15. view the current database queue

Show processlist;

16. Create a common user and authorize it to a database

Grant all on databasename. * to 'user1' @ 'localhost' identified by '123 ';

17. query table data

Select * from mysql. db; // query all fields in the table

Select count (*) from mysql. user; // count (*) indicates the number of rows in the table.

Select db, user from mysql. db; // query multiple fields in the table

Select * from mysql. db where host like '10. 0.% '; you can use the omnipotent match "%" in the query statement"

18. Insert a row of data

Insert into db1.t1 values (1, 'abc ');

19. Change a row of data in the table

Update db1.t1 set name = 'aaa' where id = 1;

20. Clear table data

Truncate table db1.t1;

21. delete a table

Drop table db1.t1;

22. Clear all tables in the database (the database name is eab12)

Mysql-N-s information_schema-e "select concat ('truncate table', TABLE_NAME, ';') from tables where TABLE_SCHEMA = 'eab12'" | mysql-f eab12

23. delete a database

Drop database db1;

24. Database Backup

Mysqldump-uroot-p 'yourpassword' mysql>/tmp/mysql. SQL

25. database recovery

Mysql-uroot-p 'yourpassword' mysql </tmp/mysql. SQL

26. Create a common user

Create user name identified by 'ssapdrow ';

27. change the password of a common user

Set password for name = PASSWORD ('fdddfd ');

28. View name User Permissions

Show grants for name;

29. Execute the mysql command in the script

Mysql-uuser-ppasswd-e "show databases"

Echo "show databases" | mysql-uuser-ppassword

The following method is used to execute a large number of mysql statements:

Mysql-uuser-hhostname-ppasswd <EOF

Mysql statement

EOF