MySQL Database Common Command usage

In the daily work, will be a simple use of MySQL, so the common Command operation summary, commonly used in the following ways

Use of the mysqldump command:

Backing up and exporting databases

Mysqldump-h database_ip-u username-p--opt databasename > Backup-file.sql

Export database table structure only

Mysqldump-h database_ip-d-u username-p databasename >database_structure.sql

Export only one table in the database

Mysqldump--opt--add-drop-table-u username-p databasename tablename > Dump.sql

Use the--password parameter if you do not want to manually enter your password

Mysqldump-h database_ip-u Username--password=123456--opt databasename > Backup-file.sql

Mysqldump-h database_ip-d-u Username--password=123456 databasename >database_structure.sql

MySQL commands use:

Save query results to a file

Select title from book into outfile '/tmp/outfile.txt ';

Find redundant records in a table, and duplicate records are judged by a field (Peopleid)

SELECT * from people where Peopleid into (select Peopleid from People GROUP by

Peopleid having count (Peopleid) > 1);

Do not duplicate records in a query table (excluding duplicate records)

SELECT * from phome_ecms_wma where title is (select DISTINCT title from phome_ecms_wma);

Deletes a duplicate record in a table, which is judged by a field (title)

Select *,count (distinct title) into outfile '/tmp/table.bak ' to phome_ecms_wma Group by title;

Delete from phome_ecms_wma;

LOAD DATA INFILE '/tmp/table.bak ' REPLACE into table phome_ecms_wma character set UTF8;

Query Database Current encoding

Mysql> Show variables like "character_set%";

Modify a table field type

Mysql> ALTER TABLE table_name change last_action last_action datetime not NULL default ' 0000-00-00 00:00:00 ';

Add a new field to a table

mysql> ALTER TABLE host ADD Ks_mac VARCHAR (100);

Remove a field from a table

mysql> ALTER TABLE table_name DROP field_name;

Renaming tables

Mysql>alter table T1 rename T2;

To index a field

Mysql> ALTER TABLE tablename ADD index index name (field name 1[, field Name 2 ...]);

Mysql> ALTER TABLE tablename Add index Emp_name (name);

Index of the Primary keyword

Mysql> ALTER TABLE TableName ADD PRIMARY key (ID);

Index with unique restriction criteria

Mysql> ALTER TABLE tablename add unique emp_name2 (cardnumber);

Delete an index

Mysql>alter table tablename DROP index emp_name;

Remote access MySQL settings

Mysql> GRANT all privileges in database_test.* to root@192.168.1.9 identified by ' 123456 ';

mysql> FLUSH privileges;