DB Backup Related
Database Backup
C:\Program Files\MySQL\MySQL Server 5.0\bin>mysqldump -u root -p123 DatabaseName > C:\FileNameDatabase Restore
C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -u root -p123 DatabaseName < C:\FileNameExport
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
- Approach 1
- Approach 2
ALTER IGNORE TABLE table_name ADD UNIQUE (col_name1, col_name2, ...);
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
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
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)
