Some lesser known sql functions

useful_sql.txt

Replace a table using data and structure from another one:

    DROP TABLE IF EXISTS a
    CREATE TABLE a LIKE b
    INSERT INTO a SELECT * FROM b

Insert into table with possibility of duplicate:

    INSERT INTO a(`f1`,`f2`) VALUES ('x','y') ON DUPLICATE KEY UPDATE `f2`='y'
    or
    INSERT IGNORE INTO a(`f1`,`f2`) VALUES ('x','y')

Nesting select statements:

    SELECT SUM(tbl_inner.Total) AS Total
    FROM (SELECT SUM(t) AS Total FROM tbl) tbl_inner

Concatenating tables vertically (union):

    SELECT * FROM ((SELECT * FROM `Account`) UNION ALL ( SELECT * FROM `Account`)) a

Double left join for same table:

    LEFT JOIN Prices AS TodaysPrices ON Prices.Date=CURDATE()
    LEFT JOIN Prices AS YesterdaysPrices ON Prices.Date=SUBDATE(CURDATE(), 1)
    
Order by with like:

    ORDER BY CASE
        WHEN name LIKE "%John%" THEN 1 
        WHEN name LIKE "%Doen%" THEN 2 
        ELSE 3 
    END