Oracle Database SQL文法のややこしい部分まとめ
Oracle Database SQL文法のややこしい部分まとめ
どうも、小さいころ見た映画のリメイクを見て懐かしい気持ちになったhashiです。
先日、Oracle Master 12c Bronze SQL基礎に合格しました！
そこで、今回は勉強時のメモを記事にまとめて、今勉強中の方の助けになるようにしたいと思います。
引用符の使い分け
一重引用符
文字リテラル'moziretu'や日時リテラル'2010-04-01'を示す際に使います。
二重引用符
テーブル名や列名などオブジェクト名として使われます。
オブジェクト名は、英数字のみで構成され、かつ大文字と小文字の区別がされないのが通常ですが、二重引用符を使うと、記号を含めたり、大文字と小文字を区別したりできます。
create table test-1(id integer); --これはエラー create table "test-1"(id integer); --"test-1"というテーブルが作られる create table "Test-1"(val text); --"Test-1"というテーブルが作られる select * from Test-1; --これもエラー select * from "Test-1"; --こうしないと指定できない
結合
表接頭辞をつけるかどうかがややこしいのでまとめました。
NATURAL JOIN(自然結合)
・同名同型の列の存在を前提とした結合
・どの列を使うかの指定は不可能→USINGやONは使えない
・結合列の指定に表接頭辞を使ってはいけない
JOIN(USING句を使用した結合)
・同名同型の列の存在を前提とした結合
・どの列を使うかの指定が可能
・結合列の指定に表接頭辞を使ってはいけない
JOIN(ON句を使用した結合)
・どの列を使うかの指定が可能
・結合列に表接頭辞を指定する必要がある
oracle独自の結合
・結合する表名は,(カンマ)で区切ってFROM句に指定
・結合条件はWHERE句に指定
・結合条件以外の条件は結合条件の後にAND演算子で指定する
関数
LISTAGG
LISTAGG(連結して表示する列名 [, 'デリミタ']) WITHIN GROUP(ORDER BY ソートする項目 [ASC | DESC])
複数行の列の値を連結して1行で表示する関数です。
WITHIN GROUP(ORDER BY …)の順番は覚えるほかないですね…
ROUND
数値や日付に対して四捨五入を行う関数です。
どの桁に丸めるかの指定が直感的にわかりにくいのでまとめます。
数値の場合
ROUND(x [, 'y'])
・数値xの四捨五入をして小数点第y位「に」丸める
・yは省略可能。省略するとy＝0となり、1の位に丸める
・yには負数も指定可能で、y=-1なら1の位で四捨五入して10の位に丸める
日付の場合
ROUND(x [, 'y'])
・日付xの四捨五入をして書式yに丸める
・yは省略可能で、デフォルトは'DD'
| 書式 | 説明 |
|---|---|
| YEAR | 6月30日以前ならその年の1月1日、7月1日以後なら次の年の1月1日を返す |
| MONTH | 15日以前ならその月の1日、16日以後なら次の月の1日を返す |
| DD | 午前ならその日の午前0時、午後なら次の日の午前0時を返す |
TRUNC
TRUNC(x [, 'y'])
ROUNDと同様の形ですが四捨五入ではなく切り捨てを行う関数です。
MOD
MOD(x,y)
xをyで割った余りを返す関数です。
POWER
POWER(m, n)
mをn乗した値を返す関数です。
TO_DATE
TO_DATE(文字列 [, 'format'] [, NLSパラメータ])
第一引数の文字列を第二引数で指定したフォーマットor既定のフォーマットで日付データに変える関数です。
第一引数にdate型を入れることも可能で、以下の文はエラーになりません。
暗黙の型変換によって前もって文字列に変換されるためです。
TO_DATE(SYSDATE,'YYYY/MM/DD')
TO_CHAR
数値や日付、NCHAR、NVARCHAR2、CLOBまたはNCLOBデータを十分な長さのVARCHAR2に変換する関数です。
日付データには少し注意が必要で、以下の文はエラーとなります。
文字列を暗黙的に日付に変換してから文字列に変換はしてくれないためです。
TO_CHAR('2000-01-01', 'YYYY"年"MM"月"DD"日"')
TO_NUMBER
TO_NUMBER(文字列 [, '数値書式'] [, NLSパラメータ])
文字列を指定された書式に従って数値に変換する関数です。
数値書式は第1引数で指定した文字列を数値に変換する際のフォーマットで、以下に示す文字を使って表します。
| 書式 | 説明 |
|---|---|
| 9 | 任意の数字を示す |
| 0 | 0埋めに使用。先頭なら先頭に0の数だけ0を追加。末尾も同様 |
| D | (1個以下限定)小数点の位置を指定する |
| S | (先頭or末尾限定)この文字を置いた位置に符号を追加する |
| MI | (末尾限定)負の数の末尾に-を追加する |
| G | (小数点より左限定) 桁区切りの記号を挿入 |
| , | (小数点より左限定) ,を挿入 |
| L | (小数点より左限定) 通貨の記号を挿入 |
| $ | (小数点より左限定) $を挿入 |
NLSパラメータは数値に使う記号(通貨記号など)を指定するものです。
複合問合せのORDER BY句
ORDER BY句は文の最後に置き、指定する列は一番左の表にある列名にする必要があります。
そのため、以下の複合問い合わせ結果のソートを行う際、ORDER BY sample21やORDER BY x2は無効となります。
SELECT sample11 x1, sample12 y1 FROM table1 INTERSECT SELECT sample21 x2, sample22 y2 FROM table2
副問い合わせによる表の作成
引き継ぐもの
・列のデータ型
・NOT NULL制約(制約はこれのみ。PRIMARY KEYなどは引き継がれない)
個々に指定できるもの
・制約
指定できないもの
・データ型
CREATE TABLE employees2 (id PRIMARY KEY) AS SELECT employee_id FROM employees;
デフォルト値
CREATE TABLE employees2 (id, name, hiredate DEFAULT SYSDATE) AS SELECT employee_id, employee_name, hiredate FROM employees;
条件式
CASE
単純CASE式
・式の値と条件の値を比較し、一致したら対応する戻り値を返す
・NULL値の評価が不可能
・最後まで一致しない場合、ELSE句のデフォルトの戻り値が返る(ELSE句がなければNULLが返る)
CASE 式 WHEN 条件1 THEN 戻り値1 [WHEN 条件2 THEN 戻り値2 ...] [ELSE デフォルトの戻り値] END
検索CASE式
・条件を判定し、真と判定されたら対応する戻り値を返す
・最後まで真と判定されない場合、ELSE句のデフォルトの戻り値が返る(ELSE句がなければNULLが返る)
CASE WHEN 条件1 THEN 戻り値1 [WHEN 条件2 THEN 戻り値2 ...] [ELSE デフォルトの戻り値] END
DECODE関数
・式の値と条件の値を比較し、一致したら対応する戻り値を返す
・DECODE関数では、単純CASE式と違ってNULL値の評価が可能
・最後まで真と判定されない場合、デフォルトの戻り値が存在すればそれが返る(なければNULLが返る)
DECODE(式, 条件1, 戻り値1 [, 条件2, 戻り値2 …] [, デフォルトの戻り値])
まとめ
練習問題を解く中で自分の混乱したことや、分かりにくいと感じたことを整理しました。
わからなかったことをまとめておいて、のちに復習する方法はどんなことにも有効なので続けていきたいです。
