Zenn
📚

SQL ~ゼロからはじめるデータベース操作~ を読んで【⑤】

2024/09/16に公開
SQL
DB
tech

前回の続き。
https://zenn.dev/db_engineer/articles/46877da731ee42

第4章　データの更新

・INSERT文

テーブル内にデータを登録する際にINSERT文を使用します。

shop=# -- データが空の状態
shop=# SELECT * FROM ShohinIns;
 shohin_id | shohin_mei | shohin_bunrui | hanbai_tanka | shiire_tanka | torokubi
-----------+------------+---------------+--------------+--------------+----------
(0 rows)

shop=#

shop=# -- 単体のデータを登録する
shop=# INSERT INTO ShohinIns (shohin_id, shohin_mei, shohin_bunrui, hanbai_tanka, shiire_tanka, torokubi) VALUES ('0001', 'Tシャツ', '衣服', 1000, 500, '2009-09-20');
INSERT 0 1
shop=#

shop=# -- データが登録されたことを確認
shop=# SELECT * FROM ShohinIns;
 shohin_id | shohin_mei | shohin_bunrui | hanbai_tanka | shiire_tanka |  torokubi
-----------+------------+---------------+--------------+--------------+------------
 0001      | Tシャツ    | 衣服          |         1000 |          500 | 2009-09-20
(1 row)

shop=#

shop=# -- 複数のデータを登録する
shop=# INSERT INTO ShohinIns VALUES ('0002', '穴あけパンチ', '事務用品', 500, 320, '2009-09-11'), ('0003', 'カッターシャツ', '衣服', 4000, 2800, NULL), ('0004', '包丁', 'キッチン用品', 3000, 2800, '2009-09-20');
INSERT 0 3
shop=#

shop=# -- データが登録されたことを確認
shop=# SELECT * FROM ShohinIns;
 shohin_id |   shohin_mei   | shohin_bunrui | hanbai_tanka | shiire_tanka |  torokubi
-----------+----------------+---------------+--------------+--------------+------------
 0001      | Tシャツ        | 衣服          |         1000 |          500 | 2009-09-20
 0002      | 穴あけパンチ   | 事務用品      |          500 |          320 | 2009-09-11
 0003      | カッターシャツ | 衣服          |         4000 |         2800 |
 0004      | 包丁           | キッチン用品  |         3000 |         2800 | 2009-09-20
(4 rows)

shop=#

-- デフォルト値の利用（テーブル作成時に販売単価を「DEFAULT 0」を設定）
INSERT INTO ShohinIns VALUES ('0007', 'おろしがね', 'キッチン用品', DEFAULT, 790, '2009-04-28');

shop=# -- データが登録されたことを確認(デフォルト値が設定されている)
shop=# SELECT * FROM ShohinIns WHERE shohin_id = '0007';
 shohin_id | shohin_mei | shohin_bunrui | hanbai_tanka | shiire_tanka |  torokubi
-----------+------------+---------------+--------------+--------------+------------
 0007      | おろしがね | キッチン用品  |            0 |          790 | 2009-04-28
(1 row)
※DEFAULT値が設定されていない場合はNULLとなる
shop=#

shop=# -- コピーテーブル（コピー前）
shop=# SELECT * FROM ShohinCopy;
 shohin_id | shohin_mei | shohin_bunrui | hanbai_tanka | shiire_tanka | torokubi
-----------+------------+---------------+--------------+--------------+----------
(0 rows)

shop=#

shop=# -- 商品テーブルのデータを商品コピーてブルへコピーする
shop=# INSERT INTO ShohinCopy (shohin_id, shohin_mei, shohin_bunrui, hanbai_tanka, shiire_tanka, torokubi) SELECT shohin_id, shohin_mei, shohin_bunrui, hanbai_tanka, shiire_tanka, torokubi FROM Shohin;
INSERT 0 8
shop=#

shop=# -- コピーテーブル（コピー後）
shop=# SELECT * FROM ShohinCopy;
 shohin_id |   shohin_mei   | shohin_bunrui | hanbai_tanka | shiire_tanka |  torokubi
-----------+----------------+---------------+--------------+--------------+------------
 0001      | Tシャツ        | 衣服          |         1000 |          500 | 2009-09-20
 0002      | 穴あけパンチ   | 事務用品      |          500 |          320 | 2009-09-11
 0003      | カッターシャツ | 衣服          |         4000 |         2800 |
 0004      | 包丁           | キッチン用品  |         3000 |         2800 | 2009-09-20
 0005      | 圧力鍋         | キッチン用品  |         6800 |         5000 | 2009-01-15
 0006      | フォーク       | キッチン用品  |          500 |              | 2009-09-20
 0007      | おろしがね     | キッチン用品  |          880 |          790 | 2008-04-28
 0008      | ボールペン     | 事務用品      |          100 |              | 2009-11-11
(8 rows)

shop=#

shop=# -- 商品分類毎にまとめたテーブル
shop=# CREATE TABLE ShohinBunrui (shohin_bunrui VARCHAR(32) NOT NULL, sum_hanbai_tanka INTEGER, sum_shiire_tanka INTEGER, PRIMARY KEY (shohin_bunrui));
CREATE TABLE
shop=#

shop=# -- 商品テーブルの販売単価と仕入単価の合計を商品分類テーブルへ挿入
shop=# INSERT INTO ShohinBunrui (shohin_bunrui, sum_hanbai_tanka, sum_shiire_tanka) SELECT shohin_bunrui, SUM(hanbai_tanka), SUM(shiire_tanka) FROM Shohin GROUP BY shohin_bunrui;
INSERT 0 3
shop=# SELECT * FROM shohinBunrui;
 shohin_bunrui | sum_hanbai_tanka | sum_shiire_tanka
---------------+------------------+------------------
 キッチン用品  |            11180 |             8590
 衣服          |             5000 |             3300
 事務用品      |              600 |              320
(3 rows)

shop=#

・DELETE文

テーブル内のデータを削除する際にDELETE文を使用します。

shop=# -- DELETE前
shop=# SELECT * FROM shohincopy;
 shohin_id |   shohin_mei   | shohin_bunrui | hanbai_tanka | shiire_tanka |  torokubi
-----------+----------------+---------------+--------------+--------------+------------
 0001      | Tシャツ        | 衣服          |         1000 |          500 | 2009-09-20
 0002      | 穴あけパンチ   | 事務用品      |          500 |          320 | 2009-09-11
 0003      | カッターシャツ | 衣服          |         4000 |         2800 |
 0004      | 包丁           | キッチン用品  |         3000 |         2800 | 2009-09-20
 0005      | 圧力鍋         | キッチン用品  |         6800 |         5000 | 2009-01-15
 0006      | フォーク       | キッチン用品  |          500 |              | 2009-09-20
 0007      | おろしがね     | キッチン用品  |          880 |          790 | 2008-04-28
 0008      | ボールペン     | 事務用品      |          100 |              | 2009-11-11
(8 rows)

shop=#

shop=# -- DELETE実行
shop=# DELETE FROM shohincopy;
DELETE 8
shop=#

shop=# -- DELETE後
SELECT * FROM shohincopy;
 shohin_id | shohin_mei | shohin_bunrui | hanbai_tanka | shiire_tanka | torokubi
-----------+------------+---------------+--------------+--------------+----------
(0 rows)

shop=#

shop=# -- 販売単価が4000円以上のレコードを削除
shop=# -- 削除前
shop=# SELECT * FROM Shohin;
 shohin_id |   shohin_mei   | shohin_bunrui | hanbai_tanka | shiire_tanka |  torokubi
-----------+----------------+---------------+--------------+--------------+------------
 0001      | Tシャツ        | 衣服          |         1000 |          500 | 2009-09-20
 0002      | 穴あけパンチ   | 事務用品      |          500 |          320 | 2009-09-11
 0003      | カッターシャツ | 衣服          |         4000 |         2800 |
 0004      | 包丁           | キッチン用品  |         3000 |         2800 | 2009-09-20
 0005      | 圧力鍋         | キッチン用品  |         6800 |         5000 | 2009-01-15
 0006      | フォーク       | キッチン用品  |          500 |              | 2009-09-20
 0007      | おろしがね     | キッチン用品  |          880 |          790 | 2008-04-28
 0008      | ボールペン     | 事務用品      |          100 |              | 2009-11-11
(8 rows)

shop=#

shop=# -- レコード削除を実行
shop=# DELETE FROM Shohin WHERE hanbai_tanka >= 4000;
DELETE 2

shop=# -- 削除後
shop=# SELECT * FROM Shohin;
 shohin_id |  shohin_mei  | shohin_bunrui | hanbai_tanka | shiire_tanka |  torokubi
-----------+--------------+---------------+--------------+--------------+------------
 0001      | Tシャツ      | 衣服          |         1000 |          500 | 2009-09-20
 0002      | 穴あけパンチ | 事務用品      |          500 |          320 | 2009-09-11
 0004      | 包丁         | キッチン用品  |         3000 |         2800 | 2009-09-20
 0006      | フォーク     | キッチン用品  |          500 |              | 2009-09-20
 0007      | おろしがね   | キッチン用品  |          880 |          790 | 2008-04-28
 0008      | ボールペン   | 事務用品      |          100 |              | 2009-11-11
(6 rows)

shop=#

・UPDATE文

テーブル内のデータを削除する際にUPDATE文を使用します。

shop=# -- UPDATE前
shop=# SELECT * FROM Shohin;
 shohin_id |  shohin_mei  | shohin_bunrui | hanbai_tanka | shiire_tanka |  torokubi
-----------+--------------+---------------+--------------+--------------+------------
 0001      | Tシャツ      | 衣服          |         1000 |          500 | 2009-09-20
 0002      | 穴あけパンチ | 事務用品      |          500 |          320 | 2009-09-11
 0004      | 包丁         | キッチン用品  |         3000 |         2800 | 2009-09-20
 0006      | フォーク     | キッチン用品  |          500 |              | 2009-09-20
 0007      | おろしがね   | キッチン用品  |          880 |          790 | 2008-04-28
 0008      | ボールペン   | 事務用品      |          100 |              | 2009-11-11
(6 rows)

shop=#

shop=# -- UPDATE実行（登録日を全て2009/10/10に変更）
shop=# UPDATE Shohin SET torokubi = '2009-10-10';
UPDATE 6
shop=#

shop=# -- UPDATE後
shop=# SELECT * FROM Shohin;
 shohin_id |  shohin_mei  | shohin_bunrui | hanbai_tanka | shiire_tanka |  torokubi
-----------+--------------+---------------+--------------+--------------+------------
 0001      | Tシャツ      | 衣服          |         1000 |          500 | 2009-10-10
 0002      | 穴あけパンチ | 事務用品      |          500 |          320 | 2009-10-10
 0004      | 包丁         | キッチン用品  |         3000 |         2800 | 2009-10-10
 0006      | フォーク     | キッチン用品  |          500 |              | 2009-10-10
 0007      | おろしがね   | キッチン用品  |          880 |          790 | 2009-10-10
 0008      | ボールペン   | 事務用品      |          100 |              | 2009-10-10
(6 rows)

shop=#

shop=# -- UPDATE実行（キッチン用品の販売単価を10倍に変更）
shop=# UPDATE Shohin SET hanbai_tanka = hanbai_tanka * 10 WHERE shohin_bunrui = 'キッチン用品';
UPDATE 3
shop=#

shop=# -- UPDATE後
shop=# SELECT * FROM Shohin;
 shohin_id |  shohin_mei  | shohin_bunrui | hanbai_tanka | shiire_tanka |  torokubi
-----------+--------------+---------------+--------------+--------------+------------
 0001      | Tシャツ      | 衣服          |         1000 |          500 | 2009-10-10
 0002      | 穴あけパンチ | 事務用品      |          500 |          320 | 2009-10-10
 0008      | ボールペン   | 事務用品      |          100 |              | 2009-10-10
 0004      | 包丁         | キッチン用品  |        30000 |         2800 | 2009-10-10
 0006      | フォーク     | キッチン用品  |         5000 |              | 2009-10-10
 0007      | おろしがね   | キッチン用品  |         8800 |          790 | 2009-10-10
(6 rows)

shop=#

shop=# -- UPDATE実行（商品名：ボールペンの登録日をNULLに変更）
shop=# UPDATE Shohin SET torokubi = NULL WHERE shohin_mei = 'ボールペン';
UPDATE 1
shop=#

shop=# -- UPDATE後
shop=# SELECT * FROM Shohin;
 shohin_id |  shohin_mei  | shohin_bunrui | hanbai_tanka | shiire_tanka |  torokubi
-----------+--------------+---------------+--------------+--------------+------------
 0001      | Tシャツ      | 衣服          |         1000 |          500 | 2009-10-10
 0002      | 穴あけパンチ | 事務用品      |          500 |          320 | 2009-10-10
 0004      | 包丁         | キッチン用品  |        30000 |         2800 | 2009-10-10
 0006      | フォーク     | キッチン用品  |         5000 |              | 2009-10-10
 0007      | おろしがね   | キッチン用品  |         8800 |          790 | 2009-10-10
 0008      | ボールペン   | 事務用品      |          100 |              |
(6 rows)

shop=#

shop=# -- 複数列のUPDATE実行（キッチン用品の販売単価を10倍、仕入れ単価を1/2に変更）
shop=# UPDATE Shohin SET hanbai_tanka = hanbai_tanka * 10, shiire_tanka = shiire_tanka / 2 WHERE shohin_bunrui = 'キッチン用品';
UPDATE 3
shop=#

shop=# -- UPDATE後
shop=# SELECT * FROM Shohin;
 shohin_id |  shohin_mei  | shohin_bunrui | hanbai_tanka | shiire_tanka |  torokubi
-----------+--------------+---------------+--------------+--------------+------------
 0001      | Tシャツ      | 衣服          |         1000 |          500 | 2009-10-10
 0002      | 穴あけパンチ | 事務用品      |          500 |          320 | 2009-10-10
 0008      | ボールペン   | 事務用品      |          100 |              |
 0004      | 包丁         | キッチン用品  |       300000 |         1400 | 2009-10-10
 0006      | フォーク     | キッチン用品  |        50000 |              | 2009-10-10
 0007      | おろしがね   | キッチン用品  |        88000 |          395 | 2009-10-10
(6 rows)

shop=#

・トランザクション

トランザクションとは1セットで実行される処理のことです。
UPDATEやDELETEなどのDML文の複数実行を1つの更新処理としてまとめることが可能です。
DBMSの種類によって異なりますが、DML文の最初と最後にトランザクションの開始文と終了文の挿入が必要になります。ただし、自動コミットモードが設定されている場合は不要です。

DBMS 開始文 終了文 取り消し文
SQL Serber
PostgresSQL		 BEGIN TRANSACTION COMMIT ROLLBACK
MySQL START TRANSACTION COMMIT ROLLBACK
Oracle
DB2		 - ※無し COMMIT ROLLBACK 
開始文;
UPDATE Shohin.......;
DELETE FROM Shohin.....;
終了文;（取り消し文;）

・ACID特性

ACID特性とは、データベースのトランザクションにおける4つの重要な特性を指します。

特性名 説明
Atomicity トランザクションは全ての操作が完了（COMMIT）するか、全て取り消される(ROLLBACK)かのどちらかを保証する。
Consistency トランザクション実行後も、データベースは常に定義されたルールや制約に従い、一貫した状態を保つ。
Isolation 同時に実行される複数のトランザクションは、互いに影響を与えず、独立して処理される。
Durability トランザクション完了後、結果はシステム障害が発生しても永続的に保存される。

書評

データ修正（INSERT、DELETE、UPDATE）のコマンドを学んだ。
トランザクションやACIDは何となくは知っていたが、実際のコマンド処理の流れは知らなかったので、勉強になった。

Discussion