MySQL Common basic operations

MySQL Common operations
See which libraries show databases;
View a library's table use library name; Show tables;
View the table's field desc table name;
Which user is currently Select users ();
Query all users select User,host from Mysql.user;
Current Library Select database ();
Creating a library Create database db1;
CREATE TABLE TB1 (' id ' int (4), ' name ' char (40));
View the Build Table statement Show create TABLE Tb1\g
Insert a statement INSERT into TB1 value (1, ' Zhangsan '); Insert into TB1 (' name ', ' ID ') VALUES (' Lisi ', 2);
Insert INTO TB1 (' id ') values (2);
View the statements under the table; select * from TB1;
View database version select version ();
View MySQL status show status;
Modify 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 ' 111111 ';
Grant all on db1.* to ' user2 ' @ ' IP ' identified by ' 111111 ';
Grant all on db1.* to ' User3 ' @ ' percent ' identified by ' 111111 ';% represents all
Grant Select,update on db1.* to ' user3 ' @ '% ' identified by ' 111111 '
Reclaim user Rights revoke update on db1.* from ' user3 ' @ '% '; Revoke all on db1.* from ' user3 ' @ '% ';
Delete users drop user ' user3 ' @ '% ';
MySQL queries all the users
——-
into MySQL library in MySQL
Mysql>use MySQL;
Then select User Table
Mysql>select * from user;
Or
SELECT * from Mysql.user;
See what permissions a user has: Show grants for ' user ' @ ' IP ';

Change Password UPDATE mysql.user SET password=password ("Newpwd") WHERE user= ' username ';
Query select COUNT (*) from Mysql.user; SELECT * from Mysql.db; SELECT * from mysql.db where host like ' 10.0.% ';
Empty tables truncate TABLE db1.tb1;
Delete tables drop table db1.tb1;
Delete database drop databases db1;
Fix table repair tables tb1;

MySQL Backup and recovery
Backup Mysqldump-uroot-p DB >1.sql
Recover Mysql-uroot-p DB <1.sql
Back up only one table Mysqldump-uroot-p db tb1 > 2.sql
Specify the character set Mysqldump-uroot-p–default-character-set=utf8 db >1.sql (character set depending on the format of the statement) when backing up
Restore also specifies the character set Mysql-uroot-p–default-character-set=utf8 db < 1.sql

MySQL Common basic operations