SQL DML:演算子

比較演算子

=
>
>=
<
<=
否定
<>
!=
^=

論理演算子

優先順位
() → NOT → AND → OR
例:
SELECT ~ WHERE 列1 = 値1 OR 列2 = 値2 AND 列3 = 値3
||
SELECT ~ WHERE 列1 = 値1 OR ( 列2 = 値2 AND 列3 = 値3 )

算術演算子

演算子名 演算子 操作 結果 優先順位
単項演算子 + – 同一 反転 +5、 -10 5、 -10 1
二項演算子 * / 乗算 除算 2*5、 9/3 10、 3 2
+ – 加算 減算 2+5、 9-3 7、 6 3
* / + – 乗除加減算 X + NULL NULL

文字列結合演算子

演算子 操作 結果
|| 文字列連結 'AA' || 'BB' 'AABB'
'AA' || NULL 'AA'
NULL || NULL ''

集合演算子

SQL DML:複合問い合わせ(UNION)」参照

演算子の優先順位

算術演算子(* / + -)

文字列結合演算子(||)

比較演算子(= > >= < <=)

集合演算子(IN LIKE)、IS NULL

BETWEEN

比較演算子:否定(<> != ^=)

論理演算子(NOT AND OR)

SQL DML:データ型

一覧

タイプ データ型 最大 桁未指定時 備考
文字型 CHAR 2,000byte 1byte 固定長 ‘AAA ‘
VARCHAR2 4,000byte エラー 可変長 ‘AAA’
LONG 2G 可変長 ‘AAA’
CLOB 4G 可変長 ‘AAA’
数値型 NUMBER 38桁 整数38桁 111.11
日付型 DATE 年月日 内部で時分秒を保持している ‘2016-01-01’
TIMESTAMP DATE + 秒以下 DATE + 秒以下(6桁) ‘2016-01-01 10:00:00.333000’
INTERVAL YEAR TO MONTH
INTERVAL DAY TO SECOND
バイナリ型 RAW 2,000byte
LONG RAW 2G
BLOB 4G
BFILE 4G
ROWID型 ROWID 一位のBASE64文字列 AAASRSAAEAAAASOAAA

型の種類

CHAR

文字列型(固定長)
n:1~2000
省略可。省略した場合n:1
指定した桁未満の値を格納した場合、末尾にスペースが自動で割り当てられる。

VARCHAR2

文字列型(可変長)
n:1~2000
省略不可
指定した桁未満の値を格納した場合でも、その値で格納される。
末尾にスペースは割り当てられない

LONG/CLOB

Long型
文字列型(可変長)
最大2GB
表につき1列のみ使用可
CLOB型で代用可
Where/OrderBy/GroupBy/サブクエリー適用不可

CLOB型
LONG型の改良型:文字列型(可変長)
表につき1列のみ使用可→制限無し

NUMBER/NUMERIC
Number(p,s)/Number(p)
数値(小数点を含めた桁数)、小数点(桁指定)
p:1~38
s:-84~127

Number(3,1)
1234.5→エラー
123.45→134.5

Numeric(p,s)
数値(小数点を含めた桁数)、小数点(桁指定)

999.99
-99.99

日付/日時型
‘で囲む必要あり。’2014-04-01’

DATE型
内部で時刻を持っているが、デフォルトでは表示されない。
デフォルト書式:”RR-MM-DD”

TIMESTAMP型
内部で時刻を持っており、デフォルトで表示される。
デフォルト書式:”RR-MM-DD HH24:MI:SS”

INTERVAL YEAR TO MONTH型
年と月の期間

INTERVAL DAY TO SECOND型
日、時、分、秒の期間

Date型
YYYY-MM-DD

Time型
hh:mm:ss[. nnnnnnn]型

DateTime型
YYYY-MM-DD hh:mm:ss[. nnn]

RR年書式(2000年問題対応)
【2050年~2099年】
99/01/01(1999/01/01のつもり)
select to_date(’99-01-01′,’YY/MM/DD’) from mytable;
→99-01-01

99/01/01(1999/01/01のつもり)→4桁へ
select to_char(to_date(’99-01-01′,’YY-MM-DD’),’YYYY/MM/DD’) from mytable;
2099/01/01 ※不足分はシステム日付(2015/~/~)の西暦(2000)から補われる
select to_char(to_date(’99-01-01′,’RR-MM-DD’),’YYYY/MM/DD’) from mytable;
1999/01/01 ※不足分はシステム日付(2015/~/~)の西暦-1(1999)から補われる

【2000年~2049年】
00/01/01(2000/01/01のつもり)
select to_date(’00-01-01′,’YY/MM/DD’) from mytable;
→00-01-01

00/01/01(2000/01/01のつもり)→4桁へ
select to_char(to_date(’00-01-01′,’YY-MM-DD’),’YYYY/MM/DD’) from mytable;
→2000/01/01 ※不足分はシステム日付(2015/~/~)の西暦(2000)から補われる
select to_char(to_date(’00-01-01′,’RR-MM-DD’),’YYYY/MM/DD’) from mytable;
→2000/01/01 ※不足分はシステム日付(2015/~/~)の西暦(2015)から補われる

指定日と現在の年数の下二桁によって上二桁が変化する。

指定された年が 0~49、現在が 0~49 → 現在の年の上二桁
指定された年が50~99、現在が 0~49 → 現在の年の上二桁 - 1 (1900年代)
指定された年が 0~49、現在が50~99 → 現在の年の上二桁 + 1 (2100年代)
指定された年が50~99、現在が50~99 → 現在の年の上二桁
※現在:2000年代の場合

OBJECT型
大容量のオブジェクトを格納できるデータ型
表とは別場所にデータを保存、表にはポインタ情報のみを格納する事も可能
1つの表に複数のObject型を定義可能

・CLOB Char Large Object
文字型データを格納

・BLOB Binary Large Object
バイナリ型データを格納

・BFILE Binary File
OS上のファイルへのポインタ情報を格納

TYPE・ROWTYPE型
【TYPE型】
=指定表指定列と同じ型

DECLARE
value1 emp.ename1%TYPE;
emp表ename1列と同じ型に定義される
value2 emp.ename2%TYPE;
emp表ename2列と同じ型に定義される
BEGIN
SELECT結果を変数へ格納
SELECT ename1, ename2INTO value1, value2 FROM emp WHERE ~ = 1;
型が同じなので格納できる
value1 := ~
value2 := ~
END;

【ROWTYPE型】
=指定表内の列と同じ型(複数列)
=構造体

DECLARE
tmpTable emp%ROWTYPE;
emp表内の列と同じ列を持つ表を定義
BEGIN
tmpTable.Col1 := 10;
emp.Col1と同じ型
tmpTable.Col2 := ‘XXXXXXX’;
tmpTable.~ := ~;
empが持つ列と同じ列を操作できる
END;

ユーザ定義型
TYPE TESTRECORD IS RECORD (
COL1 NUMBER,
COL2 VARCHAR(8)
COL3 emp.COL3%TYPE
);
TEST TESTRECORD;

TEST.COL1:=1
TEST.COL2:=’XXXXX’

SELECT結果の格納
SELECT * INTO TEST FROM (表名) WHERE ~
SELECT結果(複数列)をまとめて格納できる

JSONB型
select
col1,
col2,
data->’col3′,
data->’col4′
from
table
;
insert into 表名 valus(1, ‘aaa’, ‘{“col3”:1, “col4″:”aaa”}’);
update 表名 set JSON型列名 = ‘{“col3”:1, “col4″:”aaa”}’ where ~;

JSON項目の個別updateはSQL DML:基本操作(CRUD) 値を指定して更新参照

NULL値

NULL:「どこのリテラル領域も参照していない」の意味。
0でも空文字(”)でもない。
NULL値の計算、連結
SQL DML:演算子」参照
WHERE句におけるNULL値の抽出方法
SELECT ~ FROM ~ WHERE 列名 IS NULL
SELECT ~ FROM ~ WHERE 列名 IS NOT NULL
SELECT ~ FROM ~ WHERE 列名 = NULL は×
SELECT ~ FROM ~ WHERE 列名 <> NULL は×
NULLの表示順については「SQL DML:基本操作(CRUD)」参照
NULLの集計については「SQL グループ関数/NULLの集計」参照
NULL値の判定・変換については「SQL 変換関数/NULL値の判定・変換」参照

固定長と可変長の違い

固定長
Char(8):’a_______’
可変長
Varhar2(8):’a’

SQLにおける文字表現

日付型、文字、文字列型は’で囲む
’01-JAN-15’、’AAA’

文字としての’を表現する場合は”と続ける。
‘Aを含む文字列を持つレコードを選択する場合
WHERE 列名 LIKE ‘%”A%’

代用引用符
代用引用符で囲まれた文字列は”もスペースもそのまま文字列として解釈される。
代用引用符に使用する文字列は何でも良い
Q’文字文字
q’文字 ~ 文字’
Q’AB’B”B”’A’
→B’B”B”’
Q'(” ”)’
→” ”
Q’Aa’
→エラー

文字連結
SQL DML:演算子」参照

WHERE句における文字指定

任意の文字列

SELECT ~ FROM ~
任意の文字列(空文字”含む)
WHERE ~ LIKE ‘検索文字%’
⇒検索文字AAA、検索文字AA、検索文字A、検索文字
WHERE ~ LIKE ‘%検索文字
⇒AAA検索文字、AA検索文字、A検索文字、検索文字
WHERE ~ LIKE ‘%検索文字%’
⇒AAA検索文字、AA検索文字、A検索文字、検索文字、検索文字A、検索文字AA、検索文字AAA

任意の一文字

任意の一文字(空文字”含まない)
WHERE ~ LIKE ‘検索文字_’
⇒検索文字A
WHERE ~ LIKE ‘_検索文字
⇒A検索文字
WHERE ~ LIKE ‘_検索文字_’
⇒A検索文字A

特殊文字のエスケープ

特殊文字を含む文字列を指定する場合に利用
例 ‘_A’ という文字列を含むデータを選択する場合
(この場合の’_’は任意の一文字ではなく、文字としての’_’)
WHERE ~ LIKE ‘%\_A%’ ESCAPE\
⇒AAA_A、AA_A、AA_A、_A、_AA、_AAA、_AAAA
※\の後の文字は特殊文字(ワイルドカード)では無く、文字であるという宣言
※ESCAPE に続く一文字は何でも良い( _ % 以外)

‘100%_’
で始まる文字列を検索する場合
WHERE 列 LIKE ‘100\%\_%’ ESCAPE ‘\’;
\%\_%
||
\% \_ %
||
%(文字) _(文字) %(ワイルドカード)

暗黙の型変換

列(型:数値)←’数値’:OK
※数値に変換される
列(型:文字列型)←数値:OK
※文字列に変換される

徹底攻略OracleMasterBronze SQL基礎問題集


 

概要

Oracle認定資格(OracleMsterBronzeSQL基礎)の為の問題集。
解説が大半で2~3割が問題集
後述する理由により問題集の割合を増やしてほしい所
解説については今は別に本でなくてもどこでも調べられるし・・・
問題の少なさをカバーする為にも複数冊で問題を解く事が必要だと思います。

OracleMaster資格試験の特徴

・PC上で受験
・範囲が広い
・問題数が多い
・業務で慣れていても試験問題は別
ギリギリ業務に必要無い様な所を付いてくる
・とは言うものの、これに受かるくらい勉強していると業務も楽
・広い範囲の勉強を継続する為には本を読むだけでは理解した気になっただけで危険。
問題を説いて間違った所を勉強するの繰り返しがモチベーション的にも良い。

受験テクニック

・短い問題から順にやる
長い問題は適当に答えを選択して「レビュー」チェックを入れ、後で再度考える
・とは言え、ほぼ全ての問題を自信を持って答えられるくらいでないと合格できない。
実力を証明する為の資格
実力無しで、運やテクニックで合格しても最終的に業務で使う場合に実力を証明できないと意味が無い。
・複数選択式問題(チェックボックス)は合計何個選ぶのが正解なのか分からないので最初に全てチェック
→2~3つ選択して「上限越え」の警告が出して判断する

資格証明書
oracle_master_bronze
OracleMasterBronze DBAも合格して初めてOracleMasterBronzeの資格取得となる。


 

SQL DML:制御文

手続き型言語

ProceduralLanguageSQL
OracleDatabas用プログラム言語
非手続き型言語であるSQLを手続き型言語に拡張している。
変数、CASE、LOOP等の制御文を利用した上でSQLを利用できる。
辞書/手続き型・非手続き型言語」参照
・ストアドプログラムの内部に記述
・SQL*Plus等のクライアントプログラムから実行するスクリプトファイルに記述
・SQL*Plus等のクライアントプログラムから直接記述
等の使い方ができる。
PL/SQLを元にした言語。名称はPL/pgSQL

PL/SQLブロック

DECLARE
 宣言部
 変数 型;
BEGIN
 変数 := 値;
 
 SELECT ~ WHERE 列 = 変数; ←エラー
 PL/SQL内のSQLへ変数を埋め込むには置換変数、バインド変数を利用する

 SELECT ~ WHERE 列 = :バインド変数;
 SELECT ~ WHERE 列 = &置換変数;
END;
/

VAR myVal NUMBER;
SQL*Plusの変数(バインド変数)はPL/SQLブロック外で宣言
Oracle SQL*Plus使用方法/バインド変数」参照
DECLARE
 :myVal = 5;
 SQL*Plus変数も操作可能
BEGIN
 
END;
/

DECLARE
 宣言部
 変数 型;
BEGIN
 処理
END;

ストアド

ストアドプログラム内でPL/SQLを使用できる。
SQL DDL:ストアド定義」参照

例外処理

メッセージ表示設定
SET SERVEROUTPUT ON

DECLARE
 独自の例外を定義
 MYERROR EXCEPTION;
BEGIN
 ~
 IF ~ THEN
  例外を発生させる
  RAISE MYERROR;
 END IF;
 
EXCEPTION
 WHEN MYERROR THEN
  画面にメッセージ表示
  DBMS_OUTPUT.PUT_LINE("独自エラー");
  ROLLBACK;
  
 WHEN SQLERROR THEN
  DBMS_OUTPUT.PUT_LINE("DBエラー");
  ROLLBACK;
  
 WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE("その他のエラー");
  ROLLBACK;
END;
/

BEGIN TRY
  ~
END TRY

BEGIN CATCH
  
END CATCH;

ローカルサブプログラム(インライン関数)

DECLARE
 PROCEDURE TEST(prm NUMBER)
 IS
 BEGIN
  UPDATE ~ SET ~ = prm;
 END;

BEGIN
 ローカルサブプログラム実行
 TEST(5);
END;

変数

定義

DECLARE
 変数 型;
 VAL1 NUMBER;
 VAL2 VARCHAR(10);
 VAL3 表名.列名%TYPE;
 「SQL DML:データ型」参照
BEGIN
 :VAL1 = 5;
 :VAL2 = '5';
END;
RETURNS
 INT;
 戻り値の型
 VOID;
 戻り値無しの場合
DECLARE
 変数 型;
 VAL1 INT;
 VAL2 VARCHAR(10) := '5';
 宣言と同時に初期化も可能
BEGIN
 VAL1 := 5;
 VAL2 := '5';
END;

SELECT文から取得

SET SERVEROUTPUT ON

DECLARE
 VAL1 表名.列名1%TYPE;
 VAL1 表名.列名2%TYPE;
BEGIN
 抽出結果を格納
 PL/SQLでは単純なSELECT文発行はエラー
 SELECT 列名1, 列名2 INTO VAL1,VAL2
 FROM 表名
 WHERE ROWNUM <= 1
 ORDER BY ~;
 
 DBMS_OUTPUT.PUT_LINE(VAL1);
 DBMS_OUTPUT.PUT_LINE(VAL2);

 
 ORACLEではUPDATE文にORDER BYを使用できない為、↑で主キーを取得して条件とする
 UPDATE 表名 SET 列名 = ~
 WHERE 列名1 = VAL1 AND 列名2 = VAL2;
 
END;
/

RETURNS
 INT;
DECLARE
 VAL INT;
BEGIN
 SELECT 列 INTO VAL FROM 表;
 RETURN VAL;
END;

制御文

IF文

DECLARE
  myNum Number := 10;
  myChar VARCHAR2(255);
BEGIN
  IF (myNum = 0) THEN
    myChar := 'ZERO';
  ELSIF (myNum > 0) THEN
  ELSE IFでもELSEIFでもない
    myChar := 'OVER';
  ELSE
    myChar := 'UNDER';
  END IF;
  
  SYS.DBMS_OUTPUT.PUT_LINE(myChar);
END;

1つのブロックを BEGIN ~ END で囲む

IF (@prmTEST = 1)
  BEGIN
  
  END
ELSE IF (@prmTEST = 2)
  BEGIN
  
  END
ELSE
  BEGIN
    IF (@prmTEST = 3)
      BEGIN
      
      END
  END
  

CASE文・CASE式

CASE式
CASE 判定対象 WHEN 判定対象 THEN ~
=しか評価できない。IS NULL や 不等号等は×。

SELECT
 NAME,
 CASE NAME
  WHEN 'AAA' THEN
  WHEN NULL THEN ~:×
  WHEN (NULL) THEN ~:エラー
  WHEN IS (NULL) THEN ~:エラー

  ELSE
 END NullOrNotNull
FROM MYTABLE;

CASE文
CASE WHEN 判定対象 評価式 判定対象 THEN ~

SELECT
 CASE
  WHEN ID IN (1,3,5) THEN 'GroupA'
  WHEN ID > 10 THEN 'GroupB'
  ELSE 'GroupC'
 END ID,
 CASE
  WHEN NAME IS NULL THEN 'NULL'
  WHEN NAME IS NOT NULL THEN 'NOT NULL'
 END NAME,
 CASE
  WHEN MYDATE BETWEEN '2000-01-01' AND '2010-12-31' THEN
  ELSE
 END MYDATE
FROM MYTABLE;

(参考:DECODE関数使用時)
SELECT
 NAME,
 DECODE(NAME ,
  'AAA' ,
  'BBB' ,
  ~ (デフォルト値))
 END FieldNm
FROM MYTABLE;

※デフォルト値省略時、指定条件に当てはまらない場合は(NULL)

↑をCASE文で表す場合
SELECT
 NAME,
 CASE NAME
  WHEN 'AAA' THEN ~
  WHEN 'BBB' THEN ~
  ELSE ~
 END FieldNm
FROM MYTABLE;

SELECT
  ProjCd,
  DiaCd,
  CASE DiaCd
    WHEN '1' THEN '平日'
    WHEN '2' THEN '土日祝'
    ELSE ''
  END  AS 'DiaNm'
FROM
  TimeTei

SELECT
  ProjCd,
  DiaCd,
  CASE
    WHEN DiaCd IN ('1') THEN '平日'
    WHEN DiaCd IN ('2','3') THEN '休日'
  END  AS 'DiaNm'
FROM
  TimeTei

WHERE句でのCASE文

列 = CASE 条件 THEN 0
↓ こういう書き方はできない
CASE
  WHEN 条件 THEN 列 = ~
  ELSE 列 = ~
END

Select * From 表名
Where
(
 CASE
  WHEN 条件 THEN colmA
  WHEN 条件 THEN colmB
 END
) = 100
AND colC =
(
 CASE
  WHEN 条件 THEN 100
  WHEN 条件 THEN 200
 END
)


Select * From table1
Where colmA = 100 AND colC = 100

SELECT
  *
WHERE

  dbo.TNM_WorkTerm.SiyoKbn = @UseKbnON
  
  AND dbo.TBL_Proj.ProjectCd >=
    CASE
      WHEN @ProjectCd = 0 THEN 1
      ELSE @ProjectCd
    END '※AND dbo.TBL_Proj.ProjectCd >= 1
  
  AND dbo.TBL_Proj.ProjectCd <=
    CASE
      WHEN @ProjectCd = 0 THEN 999999
      ELSE @ProjectCd
    END '※AND dbo.TBL_Proj.ProjectCd <= 999999

For文

DECLARE
  ~
BEGIN
 for i in 1..3 loop
  for j in 1..3 loop
   @test.sql d s
  loop;
 end loop;
END;

DECLARE
 i INT;
 j INT;
BEGIN
 for i in 1..3 loop
  for j in 1..3 loop
   INSERT INTO 表 VALUES(i, j);
  loop;
 end loop;
END;

Loop文

BEGIN
 loop
  ~
  exit when 条件式
  exit when 値 > 5;
 end loop;
END;

出力

ログ

SET SERVEROUTPUT ON;
BEGIN
 DBMS_OUTPUT.PUT_LINE(~);
 
EXCEPTION
 DBMS_OUTPUT.PUT_LINE('デバッグメッセージ');
 DBMS_OUTPUT.PUT_LINE('SQL コード = ' || SQLCODE);
 DBMS_OUTPUT.PUT_LINE('エラー内容 = ' || SQLERRM(SQLCODE));
END;

画面

RAISE (エラーレベル) (メッセージ文字列)
RAISE INFO 'aaa%aaa%aaa', 111, 222;
→aaa111aaa222aaa

動的SQL

SQL文を文字列で生成・実行

WK_SQL VARCHAR(2000);

WK_SQL := ''; 
WK_SQL := WK_SQL || 'UPDATE ~ FROM ~ WHERE ';

IF ~ THEN
 WK_SQL := WK_SQL || ' (列名1) = ''oracle'' AND ';
END IF;
IF ~ THEN
 WK_SQL := WK_SQL || ' (列名2) = ''pl/sql'' AND ';
END IF;

↑SQL文を実行
EXECUTE IMMEDIATE WK_SQL;

SQL文への埋め込み

WK_SQL VARCHAR(2000);

INSERT/UPDATE/DELETE文
WK_SQL := ''; 
WK_SQL := WK_SQL || 'UPDATE ~ FROM ~ ';
WK_SQL := WK_SQL || 'WHERE ~ = :=val1 AND ~ = val2 ';

↑SQL文に値をバインドして実行
EXECUTE IMMEDIATE WK_SQL USING '1', '1';
※バインド変数:val1、val2は宣言しなくて良い

INTO句
EXECUTE IMMEDIATE WK_SQL INTOUSING '1', '1';
INTO句は動的SQL文の文字列に入れられない
EXECUTE IMMEDIATE 'SELECT (列) INTO (変数) FROM (表)' は×
EXECUTE IMMEDIATE 'SELECT (列) FROM (表)' INTO (変数) は○

カーソル内のSELECT文
OPEN cu FOR 'SELECT ~ FROM ~ WHERE ~ = :=val1 AND ~ = val2 ' USING 1, '1';
カーソルOPEN時にバインド変数に値を埋め込み
LOOP
 FETCH cu INTO r;
 EXIT WHEN cu%NOTFOUND;
 ~
END LOOP;
CLOSE cu;

カーソル

フェッチ、カーソル移動

declare
 rec 受注m%rowtype;
 cursor cu
 is
  select col1 from 受注m
 ;
begin
 open cu;
 loop
  fetch cu into rec;
  exit when cu%notfound;
  データ取得対象が無い場合は処理を抜ける
  ~;
 end loop;
 close cu;
end;

DECLARE
 cur CURSOR FOR SELECT 列 FROM 表;
 または
 cur CURSOR IS SELECT 列 FROM 表;
 rec RECORD;
BEGIN
 OPEN cur;
 LOOP
  FETCH cur INTO rec;
  EXIT WHEN NOT FOUND;
  または
  IF NOT FOUND THEN
   
   EXIT;
  END IF;
  INSERT INTO 表 VALUES ( rec.列 );
 END LOOP;
END

(1)カーソルの宣言
DECLARE [カーソル名]CURSOR FOR[SELECT文]


DECLARE TestCursor CURSOR FOR
 SELECT ~ FROM TestTable

(2)カーソルを開く
OPEN [カーソル名]

OPEN TestCursor

(3)1行ごとにデータを取り出す
FETCH NEXT FROM [カーソル名] INTO [カーソル用変数]
WHILE [条件式]
 BEGIN
 [処理内容]
 END


DECLRE @TestColumn1 INT
DECLRE @TestColumn2 INT

WHILE (@@fetch_status = 0)
カーソル全件Loop
 BEGIN
  FETCH NEXT FROM TestCursor INTO @TestColumn1,@TestColumn2
  カーソルから値を取り出し、@TestColumn1、2へ格納
  
  PRINT @TestColumn1 + ' ' + TestColumn2
 END

(4)カーソルを閉じる
CLOSE [カーソル名]
DEALLOCATE [カーソル名]

CLOSE TestCursor
DEALLOCATE TestCursor

ForLoop

declare
 cursor cu is select * from 受注m where 品番='a001';
begin
 for cu_rec in cu loop
  ~;
 end loop;
end;
変数(cu_rec)は宣言不要
カーソルのOPEN、データ取得、終了、CLOSE処理は記述不要
変数の型はTYPE、ROWTYPE型

DECLARE
 cur CURSOR FOR SELECT 列 FROM 表;
BEGIN
 FOR rec IN cur LOOP
  INSERT INTO 表 VALUES ( rec.列 );
 END LOOP;
END

カーソルをOPEN時に定義

TYPE CURSOR_INFO IS REF CURSOR;
cu CURSOR_INFO;

BEGIN
 OPEN cu FOR 'SELECT ~ FROM ~';
 LOOP
  FETCH cu INTO r;
  EXIT WHEN cu%NOTFOUND;
  ~
 END LOOP;
CLOSE cu;
END

BEGIN
 FOR rec IN ( SELECT 列 FROM 表 ) LOOP
 FOR rec IN SELECT 列 FROM 表 LOOP
  INSERT INTO 表 VALUES ( rec.列 );
 END LOOP;
END

カーソルを動的に変更

IF ( ~ ) THEN
 OPEN cu FOR 'SELECT ~ FROM ~';
ELSIF ( ~ ) THEN
 OPEN cu FOR 'SELECT ~ FROM ~ WHERE ~ = :=val1 ' USING 1;
ELSE ( ~ ) THEN
 OPEN cu FOR 'SELECT ~ FROM ~ WHERE ~ = :=val1 AND ~ = val2 ' USING 1, '1';
END IF;

LOOP
 FETCH cu INTO r;
 EXIT WHEN cu%NOTFOUND;
 ~
END LOOP;
CLOSE cu;

DECLARE
 引数を持つ
 cur CURSOR(prm1 INT, prm2 TEXT) FOR SELECT 列 FROM 表 WHERE 列1 = prm1 AND 列2 = prm2;
BEGIN
 FOR rec IN cur(5, 'aaa') LOOP
  INSERT INTO 表 VALUES ( rec.列 );
 END LOOP;
END

SQL DML:複合問い合わせ(UNION)

概要

集合演算子
UNION
UNION ALL
MINUS/EXCEPT
INTERSECT

を用いて複数の問い合わせデータを組み合わせる問い合わせ

・列の数、データ型も同じである必要がある。
※サイズ、列名は違っていても良い。
・ORDER BY句は複合問い合わせ分の最後に1回だけ指定可能。
・最初のSELECT文の列名が表示される。
※最初のSELECT文の列名を指定
・UNION ALLを除き、第一列でソートされる。

UNION

重複無し
union2

SELECT * FROM 表1
UNION
SELECT * FROM 表2
※表1 + 表1
結果はソートされる

UNION ALL

重複有り
union
SELECT * FROM 表1
UNION ALL
SELECT * FROM 表2
※表1 + 表1
結果はソートされない

MINUS/EXCEPT

差分

minus

SELECT * FROM 表1
MINUS
SELECT * FROM 表2
※表1 – 表2

結果はソートされる

A表
値1
(NULL)
(NULL)

B表
値2
(NULL)

の場合、A表 MINUS B表の複合問い合わせ結果は、
値1
※(NULL)は返されない。(NULL)は1つの値として扱われる。

SELECT * FROM 表1
EXCEPT
SELECT * FROM 表2

INTERSECT

重複しているレコードのみ抽出
intersect
SELECT * FROM 表1
INTERSECT
SELECT * FROM 表1
結果はソートされる

SQL DML:副問い合わせ

概要

物理的に実在する表、行、列でなく、論理的に作成した表、行、列を利用する問い合わせ。
不定の値に基づいてデータを検索できる。

副問い合わせ可能な「句」

・SELECT文
SELECT
 (SELECT 列名 FROM ~)
FROM ~

・FROM句
SELECT
 列名1 , 列名2
FROM
 (SELECT 列名1 , 列名2 FROM ~)

・WHERE句
SELECT ~ FROM ~
WHERE
 (SELECT 列名1 FROM ~) = ~

・HAVING
SELECT ~ FROM ~ GROUP BY ~
HAVING
 (SELECT ~ FROM ~) = ~

SELECT句、FROM句、WHERE句、ORDER BY句で利用可能

SELECT
  int,
  alf,
  kana,
  ( SELECT MAX(int) FROM [Table1] ) AS MaxInt
FROM
  [Table1]

テーブル:Table1&2
int alf kana MaxInt
Record1 1 a あ 6
Record2 2 b い 6
Record3 3 c う 6
Record4 4 あ 6
Record5 5 い 6
Record6 6 う 6

副問い合わせ結果を用いたデータ操作

【INSERT文】
INSERT INTO 表名 SELECT ~ FROM ~
副問い合わせではINSERT先表の全列を指定する必要あり

【UPDATE文】
UPDATE 表名 SET (列名1 , 列名2) = (SELECT 列名1 , 列名2 FROM ~ WHERE ~) WHERE ~

【DELETE文】
DELETE FROM 表名 (列名1 , 列名2) IN (SELECT 列名1 , 列名2 FROM ~ WHERE ~)
DELETE FROM 表名 (列名1 , 列名2) ALL (SELECT 列名1 , 列名2 FROM ~ WHERE ~)

【CREATE文】
CREATE TABLE 表名 AS SELECT ~ FROM ~
CREATE VIEW 表名 AS SELECT ~ FROM ~

単一行(列)問い合わせ

最高給与額取得者を問い合わせる
SELECT temp.氏名
FROM 給与
WHERE 給与.給与額 = (SELECT MAX(給与額) FROM 給与)
※副問い合わせ結果が複数行ある様な問い合わせはエラー
SELECT temp.氏名
FROM 給与
WHERE 給与.給与額 = (SELECT 給与額 FROM 給与 WHERE 給与 > 500,000)

複数行(列)問い合わせ

特定の部門に所属する社員を問い合わせる

IN句

SELECT * FROM 社員
WHERE 職務 IN (SELECT 職務 FROM 社員 WHERE 部門 = 10)

ANY句

リストの最小値より大きい
SELECT * FROM 社員
WHERE 給与 >= ANY (SELECT 給与 FROM 社員 WHERE 部門 = 10)

リストの最大値より小さい
SELECT * FROM 社員
WHERE 給与 <= ANY (SELECT 給与 FROM 社員 WHERE 部門 = 10)

リストのいずれかを含む
SELECT * FROM 社員
WHERE 職務 = ANY (SELECT 職務 FROM 社員 WHERE 部門 = 10)
WHERE 職務 IN (SELECT 職務 FROM 社員 WHERE 部門 = 10) と同じ

否定
WHERE ~ NOT > ANY (~) = WHERE ~ <= ALL (~)
WHERE ~ NOT < ANY (~) = WHERE ~ >= ALL (~)

ALL句

リストの最大値より大きい
SELECT * FROM 社員
WHERE 給与 >= ALL (SELECT 給与 FROM 社員 WHERE 部門 = 10)

リストの最小値より小さい
SELECT * FROM 社員
WHERE 給与 <= ALL (SELECT 給与 FROM 社員 WHERE 部門 = 10)

NOT IN (!= ALL)

列1 列2
1  A
2  B
3  NULL

SELECT 列1 FROM 表 WHERE 列2 IS NULL
→ 3

SELECT 列1 FROM 表 WHERE 列2 IN (NULL)
→ NULL (該当無し)

SELECT 列1 FROM 表 WHERE 列2 IN (SELECT 列2 FROM 表)
SELECT 列1 FROM 表 WHERE 列2 IN ('A', 'B', NULL)
→ 1
→ 1

SELECT 列1 FROM 表 WHERE 列2 NOT IN ('A', 'B', NULL)
SELECT 列1 FROM 表 WHERE 列2 != ALL (SELECT 列2 FROM 表)
SELECT 列1 FROM 表 WHERE 列2 != ALL ('A', 'B', NULL)
→ NULL (該当無し)

相関副問合せ(EXISTS/NOT EXISTS)

【EXISTS】
SELECT * FROM 商品M
WHERE EXISTS (
 SELECT 売上ID FROM 売上 WHERE 売上.売上ID = 商品M.商品ID
);



↓結果

フロー
1)主問い合わせ(SELECT * FROM 商品M) を実行
2)1の結果をカーソルの様に使用して全件Loop。内1レコード対して、
 副問い合わせ(SELECT 売上ID FROM 売上 WHERE 売上.売上ID = 商品M.商品ID) を実行
3)2の結果を判定

フロー説明
1)主問い合わせ(SELECT * FROM 商品M)を実行

2) 1レコード目:主問い合わせの1レコード目に対して、副問い合わせ
 (SELECT 売上ID FROM 売上 WHERE 売上.売上ID = 商品M.商品ID)を実行
 ※1レコード目の場合は、商品M.商品IDは'1'

3) 1レコード目:2の結果を判定

→2の副問い合わせ結果は、「真」である為、結果、主問い合わせの1レコード目はSELECTされる

2) 2レコード目:主問い合わせの2レコード目に対して、副問い合わせ
 (SELECT 売上ID FROM 売上 WHERE 売上.売上ID = 商品M.商品ID)を実行
 ※2レコード目の場合は、商品M.商品IDは'2'

3) 2レコード目:2の結果を判定

→2の副問い合わせ結果は、「偽」である為、結果、主問い合わせの2レコード目はSELECTされない

【NOT EXISTS】
SELECT * FROM 商品M
WHERE NOT EXISTS (
 SELECT 売上ID FROM 売上 WHERE 売上.売上ID = 商品M.商品ID
);



↓結果

共通テーブル

≠一時テーブル
Create/Dropは不要
≒Join

WITH CommonTableA AS
(
  SELECT
    FieldA,
    FieldB,
    FieldC
  FROM
    TestTableA
),
CommonTableB AS
(
  SELECT
    FieldA,
    FieldB,
    FieldC
  FROM
    TestTableB
)

SELECT
  CommonTableA.FieldA,
  CommonTableA.FieldB,
  (SELECT FieldC FROM CommonTableB WHERE FieldA = CommonTableA.FieldA) AS FieldC
FROM
  CommonTableA

;
WITH CommonTableA AS
(
  SELECT
    FieldA,
    FieldB,
    FieldC
  FROM
    TestTableA
),
CommonTableB AS
(
  SELECT
    FieldA,
    FieldB,
    FieldC
  FROM
    TestTableB
)

SELECT
  CommonTableA.FieldA,
  CommonTableA.FieldB,
  (SELECT FieldC FROM CommonTableB WHERE FieldA = CommonTableA.FieldA) AS FieldC
FROM
  CommonTableA

SQL DML:集計(グループ化)

グループ化

概要

指定列における、各行の同じ値でグループを作成。
更にグループ関数を用いる事でグループ毎の集計を行う事もできる。
グループ関数については「SQLグループ関数」参照

EMPNO ENAME DEPTNO SAL COMM HIREDATE CITYNAME
1 ‘C#’ 1 2000000 1000000 2000/1/1 ‘西宮’
2 ‘C++’ 2 1000000 2000000 2000/1/1 ‘西宮’
3 ‘VB.NET’ 3 1000000 1000000 2000/1/1 ‘西宮’
4 ‘VB6’ 2 1000000 5000000 2000/1/1 ‘西宮’
5 ‘JAVA’ 3 1500000 1000000 2000/1/1 ‘西宮’
6 ‘JS’ 1 2000000 1000000 2000/1/1 ‘神戸’
7 ‘PERL’ 3 1000000 100000 2000/1/1 ‘神戸’

CITYNAMEのグループ化
SELECT CITYNAME FROM EMPLOYEE GROUP BY CITYNAME;
CITYNAME
-----
神戸
西宮

DEPTNOのグループ化
SELECT DEPTNO FROM EMPLOYEE GROUP BY DEPTNO;
DEPTNO
-----
1
2
3

グループ関数

CITYNAMEのグループ化
SELECT TO_CHAR(AVG(SAL),'99,999,999') AS AVG, CITYNAME FROM EMPLOYEE GROUP BY CITYNAME;
AVG CITYNAME
----- -----
1,500,000 神戸
1,300,000 西宮

DEPTNOのグループ化
SELECT TO_CHAR(AVG(SAL),'99,999,999') AS AVG, DEPTNO FROM EMPLOYEE GROUP BY DEPTNO;
AVG DEPTNO
----- -----
2,000,000 1
1,000,000 2
1,166,667 3

グループ列の省略

SELECT TO_CHAR(AVG(SAL),'99,999,999') AS AVG FROM EMPLOYEE;
AVG

-----
1,357,143
グループ化する列を指定しない場合、グループ化せずに集計のみを行う。
各行でグループ化しているとも言える。

グループ結果の条件付き抽出

SELECT TO_CHAR(AVG(SAL),’99,999,999′) AS AVG, CITYNAME FROM EMPLOYEE GROUP BY CITYNAME HAVING AVG(SAL) >= 1500000;
AVG CITYNAME
----- -----
1,500,000 神戸

SELECT TO_CHAR(AVG(SAL),’99,999,999′) AS AVG, DEPTNO FROM EMPLOYEE GROUP BY DEPTNO HAVING AVG(SAL) >= 1500000;
AVG DEPTNO
----- -----
2,000,000 1

複数列のグループ化

SELECT CITYNAME, DEPTNO FROM EMPLOYEE
ORDER BY CITYNAME, DEPTNO;
CITYNAME DEPTNO
----- -----
神戸 1
神戸 3
西宮 1
西宮 2
西宮 2
西宮 3
西宮 3

SELECT CITYNAME, DEPTNO FROM EMPLOYEE
GROUP BY CITYNAME, DEPTNO
ORDER BY CITYNAME, DEPTNO;
CITYNAME DEPTNO
----- ----
神戸 1
神戸 3
西宮 1
西宮 2
西宮 3
CITYNAME DEPTNO
----- -----
神戸 1 → 神戸 1 
----- -----
神戸 3 → 神戸 3
----- -----
西宮 1 → 西宮 1
----- -----
西宮 2 → 西宮 2
西宮 2
----- -----
西宮 3 → 西宮 3
西宮 3
CITYNAMEでグループ分けし(神戸/西宮)、
このグループを更にDEPTNOでグループ分けする(1/2/3)。

グループ化&集計
SELECT COUNT(*) AS CNT, CITYNAME, DEPTNO FROM EMPLOYEE
GROUP BY CITYNAME, DEPTNO
ORDER BY CITYNAME, DEPTNO;
CNT CITYNAME DEPTNO
-- ----- ----
1 神戸 1
1 神戸 3
1 西宮 1
2 西宮 2
2 西宮 3
CNT CITYNAME DEPTNO
-- ----- -----
1 神戸 1 → 1 神戸 1 
-- ----- -----
1 神戸 3 → 1 神戸 3
-- ----- -----
1 西宮 1 → 1 西宮 1
-- ----- -----
1 西宮 2 → 2 西宮 2
1 西宮 2
-- ----- -----
1 西宮 3 → 2 西宮 3
1 西宮 3

グループ化SQLの書式(順番)

○:SELECT ~ FROM ~ GROUP BY ~ HAVING ~ ORDER BY ~
○:SELECT ~ FROM ~ HAVING ~ GROUP BY ~ ORDER BY ~
GROUP BY とHAVING はどちらが先でも良い
×:SELECT ~ FROM ~ ORDER BY ~ GROUP BY ~ HAVING ~
×:SELECT ~ FROM ~ GROUP BY ~ ORDER BY ~ HAVING ~
ORDER BY は最後

列単位のGROUP化

COUNT() OVER()

※簡易GROUP BY

SELECT
  COUNT( A ) OVER( PARTITION BY A,B,C ) AS SEQ
  A,
  B
FROM
  TESTTABLE

RANK() OVER()

※順位付け
–ランキング(担当者別に売上額の高い順にソートして順位を付ける)

SELECT
  RANK() OVER(ORDER BY ~ DESC, ORDER BY ~ ASC) AS 'ランク',
  ~
FROM
  ~
ORDRER BY
  'ランク'
  
SELECT
  RANK() OVER( ORDER BY SUM(Uriagegaku) DESC ) AS 'ランク',
  TantosyaCD AS '担当者',
  SUM(Uriagegaku) AS '売上額'
FROM
  TESTTABLE
GROUP BY
  TantosyaCD

ROW_NUMBER() OVER()

※連番を振る

SELECT
  ROW_NUMBER() OVER( PARTITION BY A,B,C ORDER BY A,B,C ) AS SEQ
  A,
  B
FROM
  TESTTABLE

SQL DML:結合(JOIN)

概要/結合方法

※複数のテーブルをくっつける。
その上でSELECTしたりする。
情報処理資格的に言うと「結合」

(1)テーブルを , で区切り、WHERE句で絞る
SELECT * FROM 表1, 表2
WHERE 表1.列 = 表2.列 AND ~
※WHERE句無しの場合:全組み合わせ表示
(「単純結合」参照)

(2)JOIN句で結合条件を指定
SELECT * FROM テーブル1
JOIN テーブル2 ON テーブル1.列1 = テーブル2.列2

等価結合_非等価結合

等価結合
自然結合、ON句、USING句を使用した結合時の条件に「=」を用いて列指定を行う結合。

非等価結合
ON句を使用した結合時の条件に「=」を用いない列指定を行う結合。
BETWEEN、IN、LIKE等のWHERE句に用いられる検索条件を利用できる。
特定の範囲で条件を指定する結合方法

給与テーブル
氏名 給与 級
伊藤 180,000 ?
田中 195,000 ?
白井 260,000 ?
等級テーブル
級 最低 最高
3 150,001 200,000
4 200,001 300,000
5 300,001 400,000
SELECT *
FROM 給与 JOIN 等級
ONBETWEEN 最低 AND 最高
氏名 給与 等級
伊藤 180,000 3
田中 195,000 4
白井 260,000 4

内部結合

結合対象
列名
結合対象
列型
複数列 結合列の
SELECT
メリット
NATURAL
JOIN
別名称× 別型×
(暗黙の型変換)
強制AND条件 列修飾× 記述が楽
JOIN
USING
別名称× 別型×
(暗黙の型変換)
条件指定○ 列修飾× 表示列を減らせる
JOIN
ON
別名称○ 別型△
(明示的型変換)
条件指定○ 列修飾○ 正確

INNER JOIN

結合対象の表(テーブル)、両方に共通するレコードのみを結合・抽出する結合方法
SELECT 生徒.*, テスト.*
FROM 生徒 INNER JOIN テスト
FROM 生徒 JOIN テスト ※INNERは省略可
ON 生徒.ID = テスト.ID

EMPNO ENAME DEPTNO
1 C# 1
2 C++ 2
3 VB.NET 1
4 VB6 1
5 JAVA 2
6 JS 3
7 PERL 3

↑↓を結合

DEPTNO DNAME BOSS
1 MicroSoft 1
2 Almighty 2
3 WEB 3

↓ 結果

EMPNO ENAME DEPTNO DNAME
1 C# 1 MicroSoft
4 VB6 1 MicroSoft
3 VB.NET 1 MicroSoft
5 JAVA 2 Almighty
2 C++ 2 Almighty
7 PERL 3 WEB
6 JS 3 WEB
USING句

~ FROM (テーブル名1) JOIN (テーブル名2)
USING 列名

↓と同じ
~ FROM (テーブル名1) JOIN (テーブル名2)
ON (テーブル名1).列名 = (テーブル名2).列名

SELECT *
FROM テーブル1 JOIN テーブル2
USING (列1)
※×:NATURAL JOIN ○:JOIN

USING句を使うのに適している場合
(自然結合と比較して)
・結合する表に同名で別型の列があり、自然結合するとエラーになってしまう場合
 エラーとなる列を結合条件から外せる
結合する表に同名でNULL許容の列があり、自然結合するとエラーになってしまう場合

DELETE
FROM 表1
USING 表2
WHERE
  表2 .列 = 表1 .列 --結合条件
AND ~ --条件他

外部結合

結合条件に一致するデータに加えて、結合対象の表(テーブル)、どちらか片方にだけあるレコードも抽出・結合する結合方法
このどちらか片方にだけある表の指定によって、
・LEFT OUTER JOIN
・RIGHT OUTER JOIN
のやり方がある。

※結合前テーブル

EMPNO ENAME DEPTNO
1 C# 1
2 C++ (NULL)
3 VB.NET 1
4 VB6 1
5 JAVA 2
6 JS 3
7 PERL 3

DEPTNO DNAME
1 MicroSoft
2 Almighty
3 WEB
4 Assembly
左外部結合

SELECT E.EMPNO, E.ENAME, D.DEPTNO, D.DNAME
FROM EMPLOYEE E LEFT OUTER JOIN DEPARTMENT D ON E.DEPTNO = D.DEPTNO
ORDER BY E.EMPNO;

EMPNO ENAME DEPTNO DNAME
1 C# 1 MicroSoft
2 C++ (NULL) (NULL)
3 VB.NET 1 MicroSoft
4 VB6 1 MicroSoft
5 JAVA 2 Almighty
6 JS 3 WEB
7 PERL 3 WEB

※左の表:EMPLOYEEのC++におけるDEPTNOは(NULL)で、
表:DEPARTMENTに該当するレコードは無いが、
結合後の表のDNAMEも(NULL)として表示している。
=左表のレコードは全て表示されている一方、右表のAssemblyのレコードは表示されていない。

右外部結合

SELECT E.EMPNO, E.ENAME, D.DEPTNO, D.DNAME
FROM EMPLOYEE E RIGHT OUTER JOIN DEPARTMENT D ON E.DEPTNO = D.DEPTNO
ORDER BY E.EMPNO;

EMPNO ENAME DEPTNO DNAME
1 C# 1 MicroSoft
3 VB.NET 1 MicroSoft
4 VB6 1 MicroSoft
5 JAVA 2 Almighty
6 JS 3 WEB
7 PERL 3 WEB
(NULL) (NULL) 4 Asesembly

※左の列:EMPLOYEEにおいて、
右の表:DEPARTMENTのAssemblyをDEPTNO列に持つレコードは無いが、
結合後の表にはAssemblyレコードが表示されている。
=右表のレコードは全て表示されている一方、
左表のC++のレコードはDEPTNOを持たない為、表示されていない。

外部結合演算子(+)

SELECT ~ FROM MYTABLE M, EMPLOYEE E WHERE M.ID = E.ID;
※ ↓と同じ
FROM MYTABLE M INNER JOIN EMPLOYEE E ON M.ID = E.ID;

SELECT ~ FROM MYTABLE M, EMPLOYEE E WHERE M.ID(+) = E.ID;
※ ↓と同じ
FROM MYTABLE M RIGHT OUTER JOIN EMPLOYEE E ON M.ID = E.ID;

SELECT ~ FROM MYTABLE M, EMPLOYEE E WHERE M.ID = E.ID(+);
※ ↓と同じ
FROM MYTABLE M LEFT OUTER JOIN EMPLOYEE E ON M.ID = E.ID;

完全外部結合

・LEFT OUTER JOIN
・RIGHT OUTER JOIN
の両方

SELECT E.EMPNO, E.ENAME, D.DEPTNO, D.DNAME
FROM EMPLOYEE E FULL OUTER JOIN DEPARTMENT D ON E.DEPTNO = D.DEPTNO

EMPNO ENAME DEPTNO DNAME
1 C# 1 MicroSoft
2 C++ (NULL) (NULL)
3 VB.NET 1 MicroSoft
4 VB6 1 MicroSoft
5 JAVA 2 Almighty
6 JS 3 WEB
7 PERL 3 WEB
(NULL) (NULL) 4 Asesembly

左外部結合では表示されなかったAssemblyのレコードが、
右外部結合では表示されなかったC++のレコードが表示される。

自然結合

NATURAL JOIN
結合条件指定無しの結合(条件を指定できない)
列名、データ型、サイズの列を自動で抽出して結合する。
・結合条件に合致する列が無い場合、単純結合になる。
・結合条件に合致する列が1つの場合、内部結合になる。
・2つ、3つ・・・と自然結合を行う場合、左外部結合(LEFT OUTER JOIN)になる。
・結合条件列が複数ある場合、~ LEFT OUTER JOIN ~ ON ~ AND ~ と同様、結合条件が絞られる。

SELECT *
FROM テーブル1 NATURAL JOIN テーブル2 NATURAL JOIN テーブル3

自己結合

1つの表内で論理的な複数の表を作成した上で、それぞれを結合する結合

※①元テーブル

EMPNO ENAME BOSS
1 C# 1
2 C++ 2
3 VB.NET 1
4 VB6 1
5 JAVA 2
6 JS 7
7 PERL 7

※②元テーブル内のBOSSを抽出したテーブル

EMPNO ENAME
1 C#
2 C++
7 PERL

※①と②をBOSS = EMPNOで結合
SELECT E.EMPNO, E.ENAME, E.BOSS, B.ENAME BOSSNAME
FROM EMPLOYEE E LEFT OUTER JOIN EMPLOYEE B
ON E.BOSS = B.EMPNO

EMPNO ENAME BOSS BOSSNAME
1 C# 1 C#
2 C++ 2 C++
3 VB.NET 1 C#
4 VB6 1 C#
5 JAVA 2 C++
6 JS 7 PERL
7 PERL 7 PERL

単純結合

別名:直積結合/クロス結合
デカルト積(直積)が生成される。

SELECT *
FROM テーブル1 CROSS JOIN テーブル1
↓と同じ
(1)SELECT * FROM テーブル1, テーブル2
(2)SELECT * FROM テーブル1 NATURAL JOIN テーブル2
※共通列無しの場合
(3)SELECT * FROM テーブル1 (INNER) JOIN テーブル2
※結合条件無し
(4)SELECT * FROM テーブル1 (INNER) JOIN テーブル2
ON テーブル1.列 = テーブル1.列
※無意味な結合条件

速度改善

SELECT * FROM テスト JOIN (SELECT * 生徒 WHERE 性別 = 男) AS 男生徒
 ON 男生徒.NO = テスト.NO
※男だけに絞った生徒テーブルとテストをNO列の条件で結合(結合行が少ない)
SELECT * FROM テスト JOIN 生徒
 ON 生徒.NO = テスト.NO
WHERE 性別 = 男
※両テーブルにおけるNO列の条件で全件結合した後、男だけに絞る(結合行が多い)

結合した列を更新

UPDATE
(
 SELECT 列 FROM 表1 JOIN 表2 ON ~
)
SET 列 = '値'

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

結合した列を削除

SQL DML:結合(JOIN)/結合した列を条件に削除」参照

バックアップ

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

SQL DDL/DML/DCL

SQL

リレーショナルデータベース操作言語
DML
DCL
DDL
に大別される。

DML

DataManipulationLanguage
データ操作言語。
データの操作(検索・挿入・更新・削除)
DML文での操作はRollBackで取消可能。
・ SELECT(検索)
・ INSERT(挿入)
・ UPDATE(更新)
・ DELETE(削除)

SQL DML:副問い合わせ」参照
SQL DML : データ型」参照
SQL DML:制御文」参照
SQL DML:複合問い合わせ(UNION)」参照
SQL DML : 集計(グループ化)」参照
SQL DML : 結合(JOIN)」参照
SQL DML : 基本操作(CRUD)」参照

DCL

DataControlLanguage
データ制御言語。
・データベースのユーザー権限管理
・データのトランザクション処理
・ GRANT(権限の付加)
・ REVOKE(権限の剥奪)
(以下はトランザクション言語に分類される事もある)
・ COMMIT(変更の確定)
・ ROLLBACK(変更の取り消し)
・ SAVEPOINT(トランザクション中のポインタ)

SQL DCL:シノニム」参照
SQL DCL:ユーザー、ロール、権限」参照
SQL DCL : トランザクション」参照

DDL

DataDefinitionLanguage
データ定義言語。
データベース、テーブル、ビュー等の定義・作成
DDL文はRollBackできない。
・ CREATE(作成)
・ ALTER(変更)
・ DROP(削除)
・ TRUNCATE(削除)

SQL DDL:プロファイル」参照
SQL DDL:表領域」参照
SQL DDL:トリガー定義」参照
SQL DDL:ドメイン定義」参照
SQL DDL:ストアド定義」参照
SQL DDL : シーケンス定義」参照
SQL DDL : インデックス定義」参照
SQL DDL : View定義」参照
SQL DDL : テーブル定義」参照