您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

實用 SQL 技巧集錦：複製表、拷貝資料、外連接、排程提醒、刪除主表孤兒資料

編輯：MySQL 資料庫相關知識

技巧速查卡

需求	SQL	說明
僅複製結構	`SELECT * INTO b FROM a WHERE 1<>1`	SQL Server/Access 語法；MySQL 用 `CREATE TABLE b LIKE a;`
拷貝資料	`INSERT INTO b(a,b,c) SELECT d,e,f FROM a;`	欄位須一一對應
文章/提交人/最後回覆	`SELECT a.title, a.username, b.adddate FROM table a, (SELECT MAX(adddate) adddate FROM table WHERE table.title=a.title) b`	相關子查詢
左外連接	`SELECT a.a, a.b, a.c, b.c, b.d, b.f FROM a LEFT OUTER JOIN b ON a.a = b.c`	保留左表所有列
排程提前 5 分鐘提醒	`SELECT * FROM schedule WHERE DATEDIFF(minute, start_time, GETDATE()) > 5`	SQL Server `DATEDIFF`；MySQL 用 `TIMESTAMPDIFF(MINUTE, start_time, NOW()) > 5`
刪除主表中副表無對應資料	`DELETE FROM info WHERE NOT EXISTS (SELECT * FROM infobz WHERE info.infid = infobz.infid)`	`NOT EXISTS` 效能優於 `NOT IN`

複雜報表範例（庫存對帳）

SELECT A.NUM, A.NAME, B.UPD_DATE, B.PREV_UPD_DATE
FROM TABLE1 A,
     (SELECT X.NUM, X.UPD_DATE, Y.UPD_DATE PREV_UPD_DATE
      FROM (SELECT NUM, UPD_DATE, INBOUND_QTY, STOCK_ONHAND
            FROM TABLE2
            WHERE TO_CHAR(UPD_DATE, 'YYYY/MM') = TO_CHAR(SYSDATE, 'YYYY/MM')) X,
           (SELECT NUM, UPD_DATE, STOCK_ONHAND
            FROM TABLE2
            WHERE TO_CHAR(UPD_DATE, 'YYYY/MM') = TO_CHAR(TO_DATE(TO_CHAR(SYSDATE, 'YYYY/MM') || '/01', 'YYYY/MM/DD') - 1, 'YYYY/MM')) Y
      WHERE X.NUM = Y.NUM(+)
        AND X.INBOUND_QTY + NVL(Y.STOCK_ONHAND,0) <> X.STOCK_ONHAND) B
WHERE A.NUM = B.NUM;

註：Oracle 語法（(+) 外連接、NVL、TO_CHAR），MySQL 需改寫為 LEFT JOIN、IFNULL、DATE_FORMAT。

學生資料篩選

SELECT * FROM studentinfo
WHERE NOT EXISTS (SELECT * FROM student WHERE studentinfo.id = student.id)
  AND department = 'CS' AND major = 'AI'
ORDER BY gender, origin, gaokao_score DESC;