MySQL daily use of commands

MySQL Database Usage Summary

This article mainly records some MySQL daily use of the commands for future inquiries.

1. Change root password

Mysqladmin-uroot password ' yourpassword '

2. Remote login MySQL Server

Mysql-uroot-p-hip-p3306

3. Querying the Database

show databases;

4. Access to a database

Use DatabaseName;

5. List tables in the database

Show tables;

6. View all fields of a table

Desc Slow_log;

Show CREATE TABLE slow_log\g; (not only can display table information, you can also display the Build table statement)

7. View Current User

Select User ();

8. View your current database

Select Database ();

9. Create a new database (you can specify a character set)

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 Database Status

Show status; Current session state

Show global status; Global database state

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; (The restart database will fail, to be modified in the configuration file)

15. View the current database queue

Show Processlist;

16. Create a regular user and authorize a database

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

17. Query table Data

SELECT * from Mysql.db; Query all the fields in the table

Select COUNT (*) from Mysql.user; COUNT (*) indicates how many rows are in the table

Select Db,user from Mysql.db; Multiple fields in a query table

SELECT * from mysql.db where host like ' 10.0.% '; You can use the universal match '% ' in a query statement

18. Insert a row of data

INSERT into DB1.T1 values (1, ' abc ');

19. Change a row of data in a table

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

20. Clear Table Data

TRUNCATE TABLE db1.t1;

21. Delete Table

drop table db1.t1;

22. Empty all tables in the database (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 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. New Normal User

CREATE USER name identified by ' Ssapdrow ';

27. Change common user Password

SET PASSWORD for Name=password (' FDDDFD ');

28. View name User rights

Show grants for name;

29. Database remote backup (export)

Mysqldump-uroot-p ' YourPassword '-hip MySQL >/tmp/mysql.sql

30. Database Recovery (Import)

Source/temp/mysql.sql

Url:http://www.cnblogs.com/jtnote/p/6233611.html