mysql table common sql statements personal experience sharing

Connection: ES0en-ES1en Host address -u user name -p user password (note :u and root can be used without Spaces, other is the same)
Disconnect: exit (enter)

Create authorization: grant select on database * to username @login host identified by \" Password \"
Change password: ES17en-ES18en Username -p Old password password new password
Remove authorization: revoke select insert, update, delete om *. * from test2 @ localhost;

Display database: show databases;
Display table: show tables;
Display table structure: describe table name;

Create library: create database library name;
Delete library: drop database library name;
Use the library (select the library) : use library name;

Create table: create table table name (field setting list);
Delete table: drop table table name;
Modification table: alter table t1 rename t2
Query table: select * from table name;
Empty the table: delete from table name;
Backup table: ES69en-ES70en (ip) - ES72en-ES73en (password) databasename tablename > tablename.sql
Recovery table: ES80en-ES81en (ip) -ES83en-ES84en (password) databasename tablename < tablename.sql (delete original table before operation)

Additional columns: ALTER TABLE t2 ADD INT UNSIGNED NULL AUTO_INCREMENT,ADD INDEX (c);
Modified column: ALTER TABLE t2 MODIFY a TINYINT NULL, CHANGE b c CHAR(20);
Column: ALTER TABLE t2 DROP COLUMN c

Backup database: mysql\bin\ ES131en-ES132en (ip) - ES134en-ES135en (password) databasename > database.sql
Restore database: mysql\bin\ ES143en-ES144en (ip) - ES146en-ES147en (password) databasename < database.sql
Replication database: mysql\bin\mysqldump -- ES156en-ES157en > all-databases.sql
Repair database: mysqlcheck-ES162en-ES163en-ES164en-p54ES166en

load data local infile \" Filename \" into table table name;

Data import export: mysql\bin\mysqlimport database tables.txt