Tips for MySQL

mysql-cs-01-introduction.md

MySQL Cheat Sheet

Databases

Tables

Create

Create ... Select

CREATE TABLE _tableName_ SELECT ...

Truncate

TRANCATE TABLE tableName

Delete

DELETE FROM tableName WHERE tableName.fieldName1 = condition1 AND tableName.fieldName2 = condition2

Delete ... Join

DELETE t1,t2 FROM t1,t2,t3 WHERE t1.id=t2.id AND t2.id=t3.id

or

DELETE FROM t1,t2 USING t1,t2,t3 WHERE t1.id=t2.id AND t2.id=t3.id

OR

DELETE tab1, tab2 FROM tab1 INNER JOIN tab2
WHERE tab1.id = tab2.parent_id
    AND tab2.value > 3;

Update ... Replace

UPDATE my_table SET value = REPLACE(value, 'old value', 'new value');

Alter

ALTER TABLE my_table AUTO_INCREMENT=XXX