Some Important MySQL Commands

DB Backup Related

Database Backup

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysqldump -u root -p123 DatabaseName > C:\FileName

Database Restore

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -u root -p123 DatabaseName < C:\FileName

Export

 select * from TableName into outfile 'C:\\filename'  

Import

 load data local infile 'C:\\filename.txt' into table TableName;  

Delete duplicates for making unique column value

1. Approach 1

 ALTER IGNORE TABLE table_name ADD UNIQUE (col_name1, col_name2, ...);  

2. Approach 2

 CREATE TABLE new_table as select col1, ..., colN from old_table group by col1, ..., colN having count(*) > 1;  
 DROP TABLE old_table;  
 RENAME TABLE new_table to old_table;   

Change column property

 ALTER TABLE table_name   
 ADD|DROP|CHANGE|MODIFY old_name new_name data_type;  

Max nth value

 SELECT * FROM employee ORDER BY salary DESC LIMIT (n - 1), 1  

Note: limit x,y

x: the record that you want to display(n-1) because it starts from 0.

y: the how many records you want to display.

Join

Self Join / Inner Join

 SELECT t1.name as Employee, t2.name as Manager   
 FROM table t1 , table t2   
 WHERE t1.id = t2.id;  

Outer Join

 SELECT table_1.name as Employee, COALESCE(table_2.name, 'No manager') as Manager  
 FROM table_1 LEFT OUTER JOIN table_2   
 ON table_1.id = table_2.id;  

Inner Join (bad way)

 SELECT column_name(s)  
 FROM table_1, table_2  
 WHERE table_1.column_name = table2.column_name  

Inner, Left, Right Join

 SELECT column_name(s)  
 FROM table_1  
 INNER | LEFT | RIGHT JOIN table_2  
 ON table_1.column_name = table_2.column_name  

Note: MySQL does not support FULL JOIN

Order by use in multiple column

 SELECT * FROM table_name   
 ORDER BY col1 DESC, col2 ASC, ... , col N ASC|DESC;  

Set Operation

Union

 SELECT column_name(s) FROM table_name1  
 UNION  
 SELECT column_name(s) FROM table_name2  

 SELECT column_name(s) FROM table_name1  
 UNION ALL  
 SELECT column_name(s) FROM table_name2  

Intersection

 SELECT column_name(s) FROM table_name1  
 WHERE column_name IN  
 (SELECT column_name FROM table_name2)  

Subtraction

 SELECT column_name(s) FROM table_name1  
 WHERE column_name NOT IN  
 (SELECT column_name FROM table_name2)