Common MySQL database commands

Common MySQL database commands

In daily work, mysql is used simply, so the Common commands are summarized as follows:

Use the mysqldump command:

Back up and export Databases

Mysqldump-h database_ip-u Username-p -- opt databasename> backup-file. SQL

Export only the database table structure

Mysqldump-h database_ip-d-u Username-p databasename> database_structure. SQL

Export only a table in the database

Mysqldump -- opt -- add-drop-table-u Username-p databasename tablename> dump. SQL

If you do not want to manually enter the password, use the -- password parameter.

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 command:

Save query results to files

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

Searches for redundant duplicate records in a table. duplicate records are determined based on a certain field (peopleId ).

Select * from people where peopleId in (select peopleId from people group

PeopleId having count (peopleId)> 1 );

Query non-repeated records in a table (excluding Repeated Records)

Select * from phome_ecms_wma where title in (select distinct title from phome_ecms_wma );

Deletes duplicate records in a table. duplicate records are determined based on a field (title ).

Select *, count (distinct title) into outfile '/tmp/table. bak' from 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 the current encoding of the database

Mysql> show variables like "character_set % ";

Modify Table Field Type

Mysql> alter table table_name change last_action datetime not null default '2017-00-00 00:00:00 ';

Add a new field to the table

Mysql> alter table host ADD ks_mac VARCHAR (100 );

Delete a field from the table

Mysql> alter table table_name DROP field_name;

Rename a table

Mysql> alter table t1 rename t2;

Add an index to a field

Mysql> alter table tablename add index name (field name 1 [, field name 2…]);

Mysql> alter table tablename add index emp_name (name );

Index with primary keywords

Mysql> alter table tablename add primary key (id );

Add an index with unique conditions

Mysql> alter table tablename add unique emp_name2 (cardnumber );

Delete An index

Mysql> alter table tablename drop index emp_name;

Remote Access to mysql settings

Mysql> grant all privileges on database_test. * to root@192.168.1.9 identified by '20140901 ';

Mysql> flush privileges;