Experience in using common MySQL commands

The following articles mainly describe the experience of using common MySQL commands. In practice, we often use MySQL commands, so here we want to summarize the specific usage of common MySQL commands. The common methods are as follows:

MySQL Common commands 1. Use of MySQLdump commands

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 
 
 

Common MySQL commands 2. Use MySQL commands

Save query results to files

 
 

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

Searches for redundant duplicate records in the Table. duplicate records are determined based on the peopleId of a field.

 
 

  
  
select * from people where peopleId in (select peopleId from people group by   
  
  
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); 
 
 

Delete duplicate records in a table. duplicate records are determined based on the title of a field.

 
 

  
  
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 last_action datetime NOT NULL default '0000-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 '123456';  
  
  
MySQL> FLUSH PRIVILEGES; 
 
 

The above content is an introduction to commonly used MySQL commands. I hope you will get some benefits.