【SQL】データ削除 ～ DELETE文 / TRUNCATE TABLE文 ～

SQL

■ DELETE文

 * DELETE文からWHERE句を省略すると、対象のテーブルからすべての行が削除されます
    => DELETE FROM テーブル名

構文

DELETE FROM テーブル名
WHERE 検索条件

例

DELETE FROM person
WHERE id = 1;

※テストデータは、以下「■ 補足：テストデータ」

注意

 * （少なくともMySQLでは）AS句はエラーになる

[X] DELETE FROM person AS p WHERE p.id=1

 => ただし、JOINした時（以下「■ 外部参照されているデータを削除する」）は大丈夫

■ TRUNCATE TABLE文

 * DROP TABLEを行った上で再度同じテーブルを作成し、一気に削除するため、
   DELETEステートメントよりも実行速度が速い
 * ロールバックできない

構文

TRUNCATE TABLE 【テーブル名】;

例

TRUNCATE TABLE person;

■ DELETE文 と TRUNCATE文 の違い

`項目`DELETE文TRUNCATE文
実行速度遅い速い
AUTO_INCREMENTの値初期化されない初期化される
ロールバックできるできない

■ 外部参照されているデータを削除する

 * 結合し、その結果を削除する

例

SET @TARGET_DATETIME = '2018-01-01 00:00:00';

SELECT * FROM `order` AS o INNER JOIN order_detail AS od ON od.order_id=o.id;
SELECT * FROM `order` AS o INNER JOIN order_detail AS od ON od.order_id=o.id WHERE o.order_date < @TARGET_DATETIME;

-- ★ここに注目★
DELETE od, o FROM `order` AS o INNER JOIN order_detail AS od ON od.order_id=o.id WHERE o.order_date < @TARGET_DATETIME;

SELECT * FROM `order` AS o INNER JOIN order_detail AS od ON od.order_id=o.id;

注意：エラー「Cannot delete or update a parent row」が表示される

 * Delete文を実行後、
   エラー「Cannot delete or update a parent row: a foreign key constraint fails」が表示される
解決案 
MySQLの外部キー制約 RESTRICT(親テーブルへのDELETEは拒否される)から、
CASCADE(親テーブルへのDELETEが実施され，子テーブルの一致する行を自動的にDELETEされる) に変更する
MySQLの外部キー制約について
https://qiita.com/suin/items/21fe6c5a78c1505b19cb
http://gihyo.jp/dev/serial/01/mysql-road-construction-news/0063

■ 補足：テストデータ

person

CREATE TABLE IF NOT EXISTS `person` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `sex` char(1) DEFAULT 'f',
  `birth_date` date NOT NULL,
  `createdate` datetime DEFAULT CURRENT_TIMESTAMP,
  `updatedate` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

REPLACE INTO `person` (`id`, `name`, `sex`, `birth_date`, `createdate`, `updatedate`) VALUES
	(1, 'Mike', 'm', '2001-01-09', '2018-04-02 22:14:20', '2010-04-02 22:14:20'),
	(2, 'Tom', 'm', '1982-11-29', '2018-04-02 22:14:20', '2011-04-02 22:14:20'),
	(3, 'Smith', 'm', '1976-03-14', '2018-04-02 22:14:20', '2012-04-02 22:14:20'),
	(4, 'Ken', 'm', '1982-11-29', '2018-04-02 22:14:20', '2013-04-02 22:14:20'),
	(5, 'Naomi', 'f', '1962-06-11', '2018-04-02 22:14:20', '2014-04-02 22:14:20'),
	(6, 'Sam', 'm', '1982-01-30', '2018-04-02 22:14:20', '2015-04-02 22:14:20'),
	(7, 'Amy', 'f', '1933-09-23', '2018-04-02 22:14:20', '2016-04-02 22:14:20'),
	(8, 'Cathy', 'f', '1921-07-12', '2018-04-02 22:14:20', '2017-04-02 22:14:20'),
	(9, 'Bob', 'm', '1956-12-29', '2018-04-02 22:14:20', '2018-04-02 22:14:20'),
	(10, 'Tommy', 'm', '1999-02-21', '2018-04-02 22:14:20', '2018-06-02 22:14:20');

order / order_detail

CREATE TABLE IF NOT EXISTS `order` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `oder_no` varchar(10) DEFAULT NULL,
  `customer_name` varchar(50) DEFAULT NULL,
  `order_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

REPLACE INTO `order` (`id`, `oder_no`, `customer_name`, `order_date`) VALUES
	(1, '00001', 'Mike', '2017-05-24 12:55:12'),
	(2, '00002', 'Tom', '2018-06-21 12:55:33'),
	(3, '00003', 'Smith', '2018-06-24 12:56:52'),
	(4, '00004', 'Naomi', '2018-12-24 12:57:35'),
	(5, '00005', 'Oliva', '2017-06-21 12:58:08');

CREATE TABLE IF NOT EXISTS `order_detail` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `order_id` bigint(20) DEFAULT NULL,
  `item_name` varchar(50) DEFAULT NULL,
  `cost` decimal(10,3) DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX `FK_order_detail_order` (`order_id`),
  CONSTRAINT `FK_order_detail_order` FOREIGN KEY (`order_id`) REFERENCES `order` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8;

REPLACE INTO `order_detail` (`id`, `order_id`, `item_name`, `cost`) VALUES
	(1, 1, 'Orange', 1.110),
	(2, 1, 'Apple', 0.800),
	(3, 2, 'Tomato', 0.780),
	(4, 3, 'Banana', 0.650),
	(5, 3, 'Lemon', 0.380),
	(6, 3, 'Melon', 4.890),
	(7, 4, 'Orange', 1.110),
	(8, 4, 'Apple', 0.800),
	(9, 5, 'Apple', 0.800),
	(10, 5, 'Tomato', 0.780),
	(11, 5, 'Banana', 0.650),
	(12, 5, 'Lemon', 0.380),
	(13, 5, 'Melon', 4.890);

参考資料

http://www.atmarkit.co.jp/fdb/rensai/tsql08/tsql08_2.html
http://www.shift-the-oracle.com/inside/delete-truncate-difference.html
* DELETE文 と TRUNCATE文 の違い
http://tech.aainc.co.jp/archives/4772
http://blog.livedoor.jp/akf0/archives/51149687.html
http://www.dbonline.jp/mysql/insert/index12.html

関連記事

データ追加 ～ INSERT文 ～

http://blogs.yahoo.co.jp/dk521123/16106035.html

データ修正 ～ UPDATE文 ～

http://blogs.yahoo.co.jp/dk521123/18086535.html

データ削除 ～ DELETE文/TRUNCATE TABLE文 ～

http://blogs.yahoo.co.jp/dk521123/18350647.html