SQL DML:基本操作(CRUD)

概要

DML

DataManipurationLanguage
Insert/Update/Delete等の処理

DML文でエラーが発生した場合はその処理のみが取り消され、
その前の処理は保持される。※文レベルロールバック(暗黙のセーブポイント)

CRUD

Create=Insert
Read=Select
Update
Delete

SELECT

※フィールドを限定する
情報処理資格的に言うと「射影」

All/DISTINCT

SELECT
  フィールド名1,
  フィールド名2,
  フィールド名3
FROM
 [テーブル名]

–全フィールド
SELECT
  *
FROM
  [テーブル名]
  

DISTINCT
Selectするレコードをユニークなものに限定する
重複するレコードをSelectしない。
(ユニーク=一意…同じもののない事)
SELECT DISTINCT col1 FROM [テーブル名]

All
指定した条件の全レコードをSelectする。
省略可能。
SELECT All col1 FROM [テーブル名]
SELECT col1 FROM [テーブル名]と同じ

SELECT UNIQUE col1 FROM [テーブル名]
SELECT DISTINCT col1 FROM [テーブル名]と同じ

TOP ~

先頭~レコードのみ取得する

5件取得
SELECT * FROM 表名 WHERE ROWNUM <= 5
ROWNUM : 内部に定義されたディクショナリ情報

ソートした結果から取得する場合
SELECT * FROM ~
WHERE ROWNUM <= 5
ORDER BY ~

では×
ROWNUMが生成されてからソートされる為

↓が〇
SELECT * FROM
(
 SELECT * FROM ~ ORDER BY ~
 ここでROWNUMが生成される
)
WHERE ROWNUM <= 5

SELECT
 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 GROUP  HAVING ORDER
SELECT FROM WHERE HAVING GROUP  ORDER
GROUP HAVING は入れ替え可

INSERT

フィールドと値を指定して挿入

INSERT INTO
挿入先テーブル名(フィールド名1, フィールド名2, フィールド名3)
VALUES(値1, 値2, 値3)
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(値1, 値2, 値3)
INSERT INTO Table1 VALUES ('5', 'b', 'い')
※挿入先フィールドは全指定という意味で不足は認められない。
挿入する値の個数は挿入先テーブルのフィールド数と一致する必要がある。

UPDATE

※レコードのうち、指定したフィールドをの値を変更する

値を指定して更新

UPDATE [テーブル名] SET フィールド名 = 値 WHERE ~
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を使用して条件を指定

・ORDER BYでソート
・ROWNUM <= 1 で1件のみ指定
という条件でのUPDATEはできない
→PL/SQLで主キーを取得し、主キーを条件に使用する
SQL DML:制御文/SELECT文から取得」参照

SELECT結果(副問い合わせ)を更新

SQL DML:副問い合わせ/副問い合わせ結果を用いたデータ操作」参照

結合した列を更新

SQL DML:結合(JOIN)/結合した列を更新」参照

MERGE

データが有ればUPDATE、無ければINSERT

MERGE INTO MERGE先表名 USING MERGE元表名
 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:副問い合わせ/副問い合わせ結果を用いたデータ操作」参照

バックアップ

BACKUP DATABASE [DBNM]
 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