SQL DML：基本操作(CRUD)
目次
概要
DML
DataManipurationLanguage
Insert/Update/Delete等の処理
その前の処理は保持される。※文レベルロールバック(暗黙のセーブポイント)
CRUD
Create＝Insert
Read=Select
Update
Delete
SELECT
※フィールドを限定する
情報処理資格的に言うと「射影」
All/DISTINCT
フィールド名1,
フィールド名2,
フィールド名3
FROM
[テーブル名]
–全フィールド
SELECT
*
FROM
[テーブル名]
DISTINCT
Selectするレコードをユニークなものに限定する
重複するレコードをSelectしない。
(ユニーク=一意…同じもののない事)
SELECT DISTINCT col1 FROM [テーブル名]
指定した条件の全レコードをSelectする。
省略可能。
SELECT All col1 FROM [テーブル名]
SELECT col1 FROM [テーブル名]と同じ
SELECT UNIQUE col1 FROM [テーブル名]
SELECT DISTINCT col1 FROM [テーブル名]と同じ
TOP ～
先頭～レコードのみ取得する
SELECT * FROM 表名 WHERE ROWNUM <= 5
ROWNUM ： 内部に定義されたディクショナリ情報
ソートした結果から取得する場合
SELECT * FROM ～
WHERE ROWNUM <= 5
ORDER BY ～
では×
ROWNUMが生成されてからソートされる為
↓が〇
SELECT * FROM
(
SELECT * FROM ～ ORDER BY ～
ここでROWNUMが生成される
)
WHERE ROWNUM <= 5
LIMIT 5
SELECT * FROM [テーブル名]
LIMIT 5
5行飛ばす。6行目から取得
OFSET 5
LIMIT 5
SELECT * FROM [テーブル名]
LIMIT 5
5行飛ばす。6行目から取得
OFSET 5
TOP 5
*
FROM
[テーブル名]
ORDER BY
～
WHERE
※レコードを特定の条件で限定する。
情報処理資格的に言うと「選択」
SELECT フィールド名 FROM [テーブル名]
WHERE フィールド名 = 値
SELECT * FROM Table1
WHERE int = ‘1’
IN (～,～)
SELECT * FROM [テーブル名]
WHERE フィールド名 IN (1,5)
※～ WHERE フィールド名 = 1 OR フィールド名 = 5 と同じ
BETWEEN
SELECT * FROM TEST_TBL
WHERE TEST_FLD BETWEEN 1 AND 10
WHERE 1 <= TEST_FLD AND TEST_FLD <= 10 と同じ
※BETWEEN句で指定した数値は含む。
ORDER BY
※並び方を指定する
「昇順」/「降順」
SELECT * FROM ～ ORDER BY ～ ASC
SELECT * FROM ～ ORDER BY ～ DESC
ASC(昇順)、ascendingの略：小→大
DESC(降順)、descendingの略：大→小
ASC、DESCを省略するとASC扱い
SELECT * FROM ～ ORDER BY 1, 2
※1つ目のSelect項目, 2つ目のSelect項目
Null値の表示順
ORDER BY ～ ASC ⇒ Null値は最後に
ORDER BY ～ DESC ⇒ Null値は最初に
ORDER BY ～ NULLS FIRST ⇒ Null値は最初に
ORDER BY ～ NULLS LAST ⇒ Null値は最後に
デフォルトはNULLS LAST ※NULLは無限大扱い
列別名
SELECT フィールド名1 列別名1 FROM ～
SELECT フィールド名1 AS 列別名1 FROM ～
小文字を使いたい/命名規則に反する別名を定義する場合
SELECT フィールド名1 “列別 名1” FROM ～
SELECT フィールド名1 “aaAAaaAA” FROM ～
→小文字と大文字が定義したまま表示される
SELECT フィールド名1 AS “999列別名” FROM ～
・定義場所
SELECT フィールド名1 列別名1 FROM ～
SELECT フィールド名1 列別名1 FROM ～ ORDER BY 列別名1 = ～
・注意事項
全て大文字に変換されて表示される。※”～”で定義した場合を除く
‘～’ での列別名定義は×
SUBSTR()等、関数を用いた列別名定義は×
WHERE句、GROUP BY句、HAVING句では使用不可
句の順序
SELECT FROM WHERE HAVING GROUP ORDER
GROUP HAVING は入れ替え可
INSERT
フィールドと値を指定して挿入
INSERT INTO
挿入先テーブル名(フィールド名１, フィールド名２, フィールド名３)
VALUES(値１, 値２, 値３)
INSERT INTO Table1 (int, alf, kana) VALUES (‘5’, ‘b’, ‘い’)
※挿入先フィールドに不足があった場合、そのフィールドはNULL値となる。
INSERT INTO Table1 (int, kana) VALUES (‘5’, ‘い’)
※alf列に(NULL)が挿入される
INSERT INTO Table1 (int, alf, kana) VALUES (‘5’, (NULL), ‘い’)
INSERT INTO Table1 (int, alf, kana) VALUES (‘5’, DEFAULT, ‘い’)
も同じ
SELECT結果(副問い合わせ)を挿入
「SQL DML：副問い合わせ/副問い合わせ結果を用いたデータ操作」参照
値だけ指定して挿入
INSERT INTO
挿入先テーブル名
VALUES(値１, 値２, 値３)
INSERT INTO Table1 VALUES (‘5’, ‘b’, ‘い’)
※挿入先フィールドは全指定という意味で不足は認められない。
挿入する値の個数は挿入先テーブルのフィールド数と一致する必要がある。
複数のレコードを一括挿入
INSERT INTO テーブル名 (列1, 列2, 列3) VALUES
(値1, 値2, 値3),
(値1, 値2, 値3),
(値1, 値2, 値3);
SELECT結果を一括挿入
INSERT INTO テーブル名 (列1, 列2, 列3)
SELECT 列1, 列2, 列3 FROM テーブル名;
UPDATE
※レコードのうち、指定したフィールドをの値を変更する
値を指定して更新
UPDATE Table1 SET int = ’10’ WHERE ～;
UPDATE Table1 SET int = ’10’ , kana = ‘あ’ WHERE ～
| 列名 | 列A | 列B | 列C(JSONB型) | |
|---|---|---|---|---|
| A列 | B列 | |||
| 値 | 1 | aaa | ‘{“A列”:1, “B列”:”AAA”}’ | |
| 値 | 2 | bbb | ‘{“A列”:2, “B列”:”BBB”}’ | |
update 表名 set C列 = jsonb_set( C列, ‘{“B列”}’, ‘”XXX”‘) where C列->’B列’ = ‘”BBB”‘;
| 列名 | 列A | 列B | 列C(JSONB型) | |
|---|---|---|---|---|
| A列 | B列 | |||
| 値 | 1 | aaa | ‘{“A列”:1, “B列”:”AAA”}’ | |
| 値 | 2 | bbb | ‘{“A列”:2, “B列”:”XXX”}’ | |
ORDER BYを使用して条件を指定
・ROWNUM <= 1 で1件のみ指定
という条件でのUPDATEはできない
→PL/SQLで主キーを取得し、主キーを条件に使用する
「SQL DML：制御文/SELECT文から取得」参照
SELECT結果(副問い合わせ)を更新
「SQL DML：副問い合わせ/副問い合わせ結果を用いたデータ操作」参照
結合した列を更新
MERGE
データが有ればUPDATE、無ければINSERT
ON 条件
WHEN MATCHD THEN
UPDATE 文
WHEN NOT MATCHED THEN
INSERT 文
MERGE INTO MERGE先表名 USING MERGE元表名
ON (
先.列名1 = 元.列名1 AND
先.列名2 = 元.列名2
)
WHEN MATCHD THEN
UPDATE SET
先.列名3 = 元.列名3,
先.列名4 = 元.列名4
WHEN NOT MATCHED THEN
INSERT (列名1, 列名2, 列名3, 列名4)
VALUES(元.列名1, 元.列名2, 元.列名3, 元.列名4)
;
先.列名1 = 元.列名1 AND 先.列名2 = 元.列名2のデータが有れば、
先.列名3 = 元.列名3,
先.列名4 = 元.列名4
のUPDATEが実行され、無ければ、
INSERT (列名1, 列名2, 列名3, 列名4) INTO MERGE先表名 VALUES(元.列名1, 元.列名2, 元.列名3, 元.列名4)
のINSERTが実行される
INSERT先の列名を省略(全列挿入)
MERGE INTO MERGE先表名 USING MERGE元表名 ON ( ～ )
WHEN MATCHD THEN UPDATE SET ～
WHEN NOT MATCHED THEN
INSERT
VALUES(
元.列名1,
元.列名2
)
;
MERGE元に副問い合わせを指定
MERGE INTO MERGE先表名 USING (SELECT ～ FROM ～) ON ( ～ )
WHEN MATCHD THEN UPDATE SET ～
WHEN NOT MATCHED THEN INSERT ～;
DELETE
※レコードを削除する
DELETE [テーブル名] WHERE ～
DELETE FROM [テーブル名] WHERE ～
DELETE FROM Table1 SELECT * WHERE kana = ‘い’ OR alf = ‘c’
×：DELETE * [テーブル名]
×：DELETE * FROM [テーブル名]
SELECT結果(副問い合わせ)を条件に削除
「SQL DML：副問い合わせ/副問い合わせ結果を用いたデータ操作」参照
結合した列を削除
「SQL DML：結合(JOIN)/結合した列を条件に削除」参照
バックアップ
TO DISK = ‘C:\test.bak’
TO TAPE = ‘～’
WITH
差分バックアップ(未指定時：完全バックアップ)
DIFFERENTIAL,
バックアップセット名(≠ファイル名)。未指定可。
NAME = N’DBNM-完全 データベース バックアップ’,
説明文
DESCRIPTION = N’～’,
バックアップファイルの上書き(規定：NOFORMAT)
NOFORMAT | FORMAT,
バックアップファイルの上書き(規定：NOINIT)
NOINIT | INIT ,
上書き時の確認有無(規定：NOSKIP)
NOSKIP | SKIP,
テープにバックアップする際に巻き戻す
REWIND | NOREWIND(既定値：REWIND),
バックアップ完了後、テープの巻き戻しおよびアンロードを行う(規定：UNLOAD)
UNLOAD | NOUNLOAD,
指定した数字割合が完了する毎にメッセージを表示
STATS = 10
