MySQL Common operations

See which libraries > show databases are available;

View the table > use DB for a library; Show tables;

View table fields > Desc TB;

View Build Statement > Show CREATE TABLE TB;

Which user is currently > select User ();

View current Library > select database ();

Creating libraries > CREATE database DB1;

Creating table > Create tables T1 (' id ' int (4), ' name ' char (40));

Insert data > INSERT into TB1 (id,name) VALUES (1, ' aaaa ');

View Database version > select version ();

View MySQL status > show status;

modifying MySQL parameters

> Show variables like ' max_connect% ';

> Set global max_connect_errors = 1000;

View MySQL queue > show processlist;

Create a regular user and authorize

> Grant all on *. User1 identified by ' 123456 ';

> Grant all on db1.* to ' user2 ' @ ' 10.0.2.100 ' identified by ' 111222 ';

> Grant all on db1.* to ' User3 ' @ ' percent ' identified by ' 231222 ';

Change Password > UPDATE mysql.user SET password=password ("Newpwd") WHERE user= ' username ';

Inquire

> select COUNT (*) from Mysql.user;

> select * from Mysql.db; SELECT * from mysql.db where host like ' 10.0.% ';

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

Emptying Tables > TRUNCATE TABLE db1.t1;

Delete table > drop table db1.t1;

Delete databases > drop database db1;

MySQL Backup and recovery

Mysqldump-uroot-p DB >1.sql

Mysql-uroot-p DB <1.sql

Specifying a character set when backing up

Mysqldump-uroot-p--default-character-set=utf8 DB >1.sql

Mysql-uroot-p--default-character-set=utf8 DB < 1.sql

This article is from the "8653294" blog, please be sure to keep this source http://8653294.blog.51cto.com/8643294/1744943

MySQL Common operations