MySQL - insert、update、delete

 

新增

    INSERT INTO `tb_name` (`欄位1`,`欄位2`) VALUES (數值1,'字串1');

多筆新增

    INSERT INTO `tb_name` (`欄位1`,`欄位2`) VALUES (數值1,'字串1'),(數值2,'字串2');

多筆新增 - 從別的table搜尋資料

    INSERT INTO `tb_name` (`欄位1`,`欄位2`) SELECT `欄位1`,`欄位2` FROM `tb_name2`;

新增失敗就放棄新增

    INSERT IGNORE INTO `tb_name` (`欄位1`,`欄位2`) VALUES (數值1,'字串1');

新增失敗就更新某個值

    INSERT INTO `tb_name` (`欄位1`,`欄位2`) VALUES (數值1,'字串1')
        ON DUPLICATE KEY UPDATE `欄位2`='字串3';

更新

更新全部

    UPDATE `tb_name` SET `欄位1`=數值1, `欄位2`='字串2';

指定更新

    UPDATE `tb_name` SET `欄位1`=數值1, `欄位2`='字串2' WHERE `欄位3`=數值3;

置換

    REPLACE INTO `tb_name` (`欄位1`,`欄位2`) VALUES (數值1,'字串1');

指定置換

    REPLACE INTO `tb_name` SET `欄位1`=數值1, `欄位2`='字串2' WHERE `欄位3`=數值3;

刪除

刪除全部

    DELETE FROM `tb_name`;

刪除全部用這個更快

    TRUNCATE `tb_name`;

指定刪除

    DELETE FROM `tb_name` WHERE `欄位3`=數值3;