SQLでINSERT(挿入)、UPDATE(更新)、DELETE(削除)を練習する記事です。testidkanriというテーブルでIDの管理をする設定で、INSERT文、UPDATE文、DELETE文の練習問題を解いていきたいと思います。
実際にSQLを打って、手を動かして勉強してもらえれば嬉しいです!!
- INSERT文の構文
- UPDATE文の構文
- DELETE文の構文
- COMMIT文
- ROLLBACK文
- 準備1.練習用テーブル(testidkanri)の作成
- 準備2.練習用データの挿入
- 練習問題1
- 練習問題2
- 練習問題3
- 練習問題4
- 練習問題5
- 練習問題6
INSERT文の構文
INSERT文はテーブルに行を追加できます。
<構文1.列名を指定する方法>
INSERT INTO 表名 (列名1,列名2,列名3,・・・・)
VALUES (値1,値2,値3,・・・・);
<構文2.列名を指定しない方法>
INSERT INTO 表名
VALUES(値1,値2,値3,・・・・);
※全列に値を指定する必要があります。NULLの列にはNULLを明記します。
UPDATE文の構文
UPDATE文はテーブルの値を更新できます。
<構文>
UPDATE 表名
SET 列名1 = 値1,列名2 = 値2,・・・・
[WHERE 条件式];
注意:WHERE句がない場合、テーブルの指定した列の全ての値が変更されてしまいます。業務などで数十万件のデータが全て更新されたら、リカバリーも大変なのでWHERE句の指定漏れには注意が必要です。
DELETE文の構文
DELETE文は行を削除することができます。
<構文>
DELETE FROM 表名 [WHERE 条件式];
注意:UPDATE文と同様にWHERE句の指定漏れに注意が必要です。WHERE句がない場合はテーブルの全ての行を削除してしまいます。
COMMIT文
INSERT、UPDATE、DELETEはDML(データ操作文)なので、実行後にそのトランザクション内の変更を確定するためのCOMMIT文を実行する必要があります。
※SQL実行ツールの設定によってはCOMMIT文が不要な場合もあります。
<構文>
COMMIT;
ROLLBACK文
INSERT、UPDATE、DELETEを実行後に間違いに気が付いた場合、ROLLBACK文を実行することで、そのトランザクションの変更を全て取り消すことが可能です(COMMITする前なら可能。COMMIT後は不可)。
※SQL実行ツールの設定でCOMMIT文を使わず、処理を確定させている場合はROLLBACKできないので設定には注意が必要です。
<構文>
ROLLBACK;
準備1.練習用テーブル(testidkanri)の作成
今回、INSERT、UPDATE、DELETEを練習するための練習用テーブルtestidkanriを、以下のSQLを実行して作成します。
create table testidkanri (
id NUMBER(6) CONSTRAINT pk_idkanriID PRIMARY KEY,
name VARCHAR2(15) NOT NULL,
department_id NUMBER(3),
country_id NUMBER(3),
hiredate DATE
);
準備2.練習用データの挿入
以下の4つのINSERT文で4レコード挿入します。
INSERT INTO testidkanri
VALUES(1,'sato',1,1,'1990/1/1');
INSERT INTO testidkanri
VALUES(2,'suzuki',1,1,'1970/4/1');
INSERT INTO testidkanri
VALUES(3,'tanaka',3,1,'1980/1/10');
INSERT INTO testidkanri
VALUES(4,'okada',2,1,'1999/2/1');
練習問題1
以下の2名のユーザーが増えたので、testidkanriテーブルに追加してください。
<追加1>
ID:5
NAME:asai
DEPARTMENT_ID:NULL
COUNTRY_ID:2
HIREDATE;2020/4/1
<追加2>
ID:6
NAME:chiba
DEPARTMENT_ID:NULL
COUNTRY_ID:2
HIREDATE:2020/4/1
練習問題1の回答
2名のユーザーを追加するので「INSERT」文でユーザーの情報をテーブルに挿入する必要があります。
<列を指定した場合>
INTO testidkanri(id, name, country_id, hiredate)
VALUES (5, 'asai', 2, '2020/4/1');
INTO testidkanri(id, name, country_id, hiredate)
VALUES (6, 'chiba', 2, '1990/4/1');
<列を指定しなかった場合>
INTO testidkanri
VALUES (5, 'asai', NULL, 2, '2020/4/1');
INTO testidkanri
VALUES (6, 'chiba', NULL, 2, '1990/4/1');
練習問題2
練習問題1で追加した2名のDEPARTMENT_IDが決まったので、DEPARTMENT_IDを以下の通り更新してください。
ID:5
DEPARTMENT_ID:3
ID:6
DEPARTMENT_ID:4
練習問題2の回答
更新なのでUPDATE文でテーブルを更新します。
SET
department_id = 3
WHERE
id = 5;
SET
department_id = 4
id = 6;
where id IN ('5', '6');
練習問題3
IDが「1」のユーザーが退職のため、テーブルから情報を削除する必要があります。
練習問題3の回答
削除のため、DELETE文を使います。
DELETE FROM testidkanri WHERE id = 1;
以下のSELECT文でDELETE後のテーブルの情報を確認します。
select * from testidkanri;
DELETE文を確定するために以下のSQL(COMMIT)を実行します。
練習問題4
hiredateが1995年4月1日以前のユーザーが異動のため、対象ユーザーをDEPARTMENT_IDを6に変更してください。
練習問題4の回答
hiredateが1995年4月1日以前という条件を指定して、UPDATEする必要があります。
UPDATEする前に対象のユーザーを確認します。
select * from testidkanri where hiredate < '1995/4/1';
UPDATEをします。この時の条件(WHERE文以降)は、上記でSELECTしたものをそのままコピー&ペーストして使います。
update testidkanri
set
department_id = 6
where
hiredate < '1995/4/1';
COMMITで処理を確定します。
COMMIT;
練習問題5
以下の5つのユーザーを登録してください。IDとNAME以外はNULLです。
ID:1 NAME:araki
ID:7 NAME:aragaki
ID:8 NAME:yamazaki
ID:9 NAME:aoi
ID:10 NAME:nakahara
練習問題5の回答
INSERT文でレコードを追加します。ID、NAME以外はNULLなので、値が入るIDとNAMEを指定したINSERT文で対応します。
※全列指定などその他の方法でも良いです。
INSERT
INTO testidkanri(id,name)
VALUES (1,'araki');
INSERT
INTO testidkanri(id,name)
VALUES (7,'aragaki');
INSERT
INTO testidkanri(id,name)
VALUES (8,'yamazaki');
INSERT
INTO testidkanri(id,name)
VALUES (9,'aoi');
INSERT
INTO testidkanri(id,name)
VALUES (10,'nakamura');
COMMIT;
練習問題6
ID1、7、8はDEPARTMENT_IDが10、COUNTRY_IDが5、HIREDATEが2020/6/1
ID9、10はDEPARTMENT_IDが11、COUNTRY_IDが3、HIREDATEが2020/6/15でテーブルを更新してください。
練習問題6の回答
2つのUPDATE文で対応します。
<ID1、7、8用のUPDATE文>
UPDATE testidkanri
SET department_id =10, country_id = 5, hiredate = '2020/6/1'
WHERE id IN ('1','7','8');
COMMIT;
<ID9、10用のUPDATE文>
UPDATE testidkanri
SET department_id =11, country_id = 3, hiredate = '2020/6/15'
WHERE id IN ('9','10');
COMMIT;