Sep26th

oracle master bronze SQL基礎Ⅰ

Top / oracle master bronze SQL基礎Ⅰ

■SQL文の区分
DML(データ操作言語)

select
insert
update
delete
merge

DDL(データ定義言語)

create
alter
drop
rename
truncate
commit

DCL(データ制御言語)

grant
revoke

■二重引用符

SELECT ename, sal*12 "Annual Salary" FROM emp;

■連結演算子

SELECT 'Dear ' || cust_name || ',' FROM cust;

■SQLPLUSの置換変数(「&置換変数」)

SQL> SELECT empno, ename FROM emp WHERE empno = &empno;
empnoに値を入力してください: 7900
旧1: SELECT empno, ename FROM emp WHERE empno = &empno
新1: SELECT empno, ename FROM emp WHERE empno = 7900

EMPNO
----------
7900
ENAME
----------
JAMES

■LIKE(文字パターン)

WHERE 列名 LIKE '文字パターン' ESCAPE 'エスケープ記号'
SELECT empno, ename FROM emp WHERE job LIKE '%SA\_%' ESCAPE '\';

"_"は任意の1文字


文字関数
LOWER

小文字に変換
LOWER('ABC')→abc

UPPER

大文字に変換
UPPER('abc')→ABC

INITCAP

単語の先頭を大文字、残りを小文字に変換
INITCAP('ABC DEF')→Abc Def

CONCAT

文字データを連結。CONCAT('str1', 'str2')のように2つの引数を取り、連結して戻す。||演算子と同じ
CONCAT('ABC', 'DEF')→ABCDEF

SUBSTR

文字データの切り出し。SUBSTR('str', m, n)のように3つの引数を取り、文字列strのm番目から、nで指定した数の文字を戻す
・mが負の値の場合は、終わりからm番目の文字位置となる
・nを省略した場合は、文字列末尾までを戻す
SUBSTR('ABCDE', 2, 3)→BCD
SUBSTR('ABCDE', -3, 2)→CD
SUBSTR('ABCDE', 2)→BCDE

LENGTH

引数で指定した文字データの文字数を戻す
LENGTH('ABCD')→4

INSTR

文字位置を戻す。INSTR('str1', 'str2', m, n)のように4つの引数を取り、文字列str1に文字列str2が出現した位置を戻す。mとnは オプションで、検索開始位置と出現回数を指定できる。mとnのデフォルトは共に1
INSTR('ABCABC', 'B')→2
INSTR('ABCABC', 'B', 1, 2)→5

LPAD

文字データを右ぞろえにする。LPAD('str1', m, 'str2')のように3つの引数を取り、文字列str1がmで指定した文字数になるように左側に文字列str2を埋め込む。str2のデフォルトは空白
LPAD('ABC', 5, 'x')→xxABC

RPAD

文字データを左ぞろえにする。RPAD('str1', m, 'str2')のように3つの引数を取り、文字列str1をmで指定した文字数になるように右側に文字列str2を埋め込む。str2のデフォルトは空白
RPAD('ABC', 5, 'x')→ABCxx

TRIM

文字データの前後の文字を切り捨てる
・TRIM('str')の場合、前後の空白を切り捨てる
・TRIM('str1' FROM 'str2')の場合、str2の前後に含まれるstr1を切り捨てる
・TRIM(LEADING ['str1' FROM] 'str2')の場合、str2の先頭に含まれるstr1を切り捨てる
・TRIM(TRAILING ['str1' FROM] 'str2')の場合、str2の末尾に含まれるstr1を切り捨てる
TRIM(' ABC ')→ABC
TRIM('x' FROM 'xxABCxx')→ABC
TRIM(LEADING 'x' FROM 'xxABCxx')→ABCxx
TRIM(TRAILING 'x' FROM 'xxABCxx')→xxABC

REPLACE

文字データの置換。REPLACE('str1', 'str2', 'str3')のように3つの引数を取る。文字列str1から文字列str2を検索し、検出すれば文字列str3に置換した結果を戻す
REPLACE('ABCABC', 'B', 'x')→AxCAxC

■数値関数
ROUND

数値データの四捨五入。デフォルトは小数点以下を四捨五入する。ROUND(m, n)のように2つの引数を取る場合、nの値が正か負かで四捨五入の位置が変わる
・nの値が正の場合、小数点以下nになるようにn+1の位置で四捨五入
・nの値が負の場合、整数部nけたの位置で四捨五入
ROUND(123.5)→124
ROUND(123.456, 2)→123.46
ROUND(150.45, -2)→200

TRUNC

数値データの切り捨て。デフォルトは小数点以下を切り捨てる。TRUNC(m, n)というように2つの引数を取る場合、nの値が正か負かで切り捨て位置が変わる
・nの値が正の場合、小数点以下がnになるようにn+1の位置で切り捨て
・nの値が負の場合、整数部nけたの位置で切り捨て
TRUNC(123.5)→123
TRUNC(123.456, 2)→123.45
TRUNC(150.45, -2)→100

MOD

MOD(m, n)のように2つの引数を取り、mをnで除算した余りを戻す
MOD(10, 3)→1

■日付関数
SYSDATE

現在の日付と時刻を戻す
SYSDATE→'2005-07-15 13:25:55'

MONTHS_BETWEEN

2つの日付間の月数を求める。
MONTHS_BETWEEN('date1', 'date2')のように2つの引数を取り、その間の月数(日以下は小数)を表示。date1よりdate2の方が 大きい場合は、負の値として表示する
MONTHS_BETWEEN('2005-07-15', '2004-07-10')→12.1612903
MONTHS_BETWEEN('2004-07-10', '2005-07-15')→-12.1612903

ADD_MONTHS

日付に月数を加算する。ADD_MONTHS('date', m)のように2つの引数を取り、dateにm月を加算する。mの値が負の場合は、m月を減算する
ADD_MONTHS('2005-01-15', 6)→'2005-07-15'
ADD_MONTHS('2005-01-15', -6)→'2004-07-15'

NEXT_DAY

次の指定曜日の日付を求める。NEXT_DAY('date', 'day')のように2つの引数を取り、dateの後のday曜日の日付を戻す
NEXT_DAY('2005-07-12','金曜日')→'2005-07-15'

LAST_DAY

その月の月末の日付を求める
LAST_DAY('2005-07-15')→'2005-07-31'

ROUND

日付を四捨五入する。デフォルトは時刻(時間、分、秒)を四捨五入する(午前11時59分59秒を境とする)。ROUND('date', 'str')のように2つの引数を取る場合、strに指定した日付要素で四捨五入の位置が決まる
・年('YYYY')→6月30日を境とする
・月('MM')→15日を境とする
・日('DD')→午前11時59分59秒を境とする
・時間('HH24')→29分を境とする
・分('MI')→29秒を境とする
ROUND('2005-07-01 12:00:00')→'2005-07-02 00:00:00'
ROUND('2005-07-01 10:30:55', 'YYYY')→'2006-01-01 00:00:00'
ROUND('2005-07-16 10:30:55', 'MM')→'2005-08-01 00:00:00
ROUND('2005-07-16 12:00:00', 'DD')→'2005-07-17 00:00:00'
ROUND('2005-07-16 12:00:30', 'MI')→'2005-07-16 12:01:00'

TRUNC

日付を切り捨てる。デフォルトは時刻(時間、分、秒)を切り捨てる(その日の午前0時にする)。TRUNC ('date', 'str')のように2つの引数を取る場合、strに指定した日付要素で切り捨て位置が決まる
・年('YYYY')→その年の1月1日にする
・月('MM)→その月の1日にする
・日('DD')→その日の午前0時にする
・時間('HH24')→その時間の00分にする
・分('MI')→その分の00秒にする
TRUNC('2005-07-01 12:00:00')→'2005-07-01 00:00:00'
TRUNC('2005-07-01 10:30:55','YYYY')→'2005-01-01 00:00:00'
TRUNC('2005-07-16 10:30:55','MM')→'2005-07-01 00:00:00'
TRUNC('2005-07-16 12:00:00', 'DD')→'2005-07-16 00:00:00'
TRUNC('2005-07-16 12:00:30', 'MI')→'2005-07-16 12:00:00'

■日付関数の計算
日付+数値

日付
日付に日数を加算する
'2005-07-15'+10→2005-07-25

日付-数値

日付
日付から日数を減算する

'2005-07-15'-10→2005-07-05
日付-日付

日数
日付間の日数を求める

'2005-07-15'-'2005-07-10'→5
日付+数値/24

日付
日付に時間を加算する(n/24)
'2005-07-15 00:00:00'+2/24→'2005-07-15 02:00:00'

日付+数値/1440

日付
日付に分を加算する(n/24*60)
'2005-07-15 00:00:00'+30/1440→'2005-07-15 00:30:00'

日付+数値/86400

日付
日付に秒を加算する(n/24*60*60)
'2005-07-15 00:00:00'+30/86400→'2005-07-15 00:00:30'

※「日付-日付」はできますが、「日付+日付」はできないことに注意してください。

デフォルトでは年、月、日のみ表示されるので、時間部分を出力する場合は、次のようにNLS_DATE_FORMATセッションパラメータを変更

SQL> ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
SQL> SELECT SYSDATE FROM dual;
SYSDATE
-------------------
2005-07-13 12:26:01

■暗黙変換
CHAR、VARCHAR2

NUMBER
数値型の列に対して「WHERE 列 > '1000'」と指定された場合、'1000'を数値データに変換して比較する

CHAR、VARCHAR2

DATE
日付型の列に対して「WHERE 列 > '1985-01-01'」と指定された場合、'1985-01-01'を日付データに変換して比較する

NUMBER

VARCHAR2
文字型の列に対して「WHERE 列 = 1000」と指定された場合、1000を文字データに変換して比較する

DATE

VARCHAR2
文字型の列に対して「WHERE 列 = SYSDATE」と指定された場合、SYSDATEを文字データに変換して比較する

■変換関数
TO_CHAR(数値データ)

数値データを文字データに変換する。TO_CHAR(m, 'fmt')のように2つの引数を取る場合、fmtで指定した書式モデルで変換される
TO_CHAR(1000)→'1000'
TO_CHAR(1000, 'L99,999.00')→'\1,000.00'

TO_CHAR(日付データ)

日付データを文字データに変換する。TO_CHAR('date', 'fmt')のように2つの引数を取る場合、fmtで指定した書式モデルで変換される
TO_CHAR('2005-08-15')→'2005-08-15'
TO_CHAR(SYSDATE, 'YYYY"年"MM"月"DD"日"')→2005年08月12日

TO_NUMBER(文字データ)

文字データを数値データに変換する。TO_NUMBER('str', 'fmt')のように2つの引数を取る場合、strはfmtで指定された書式であると判断される
TO_NUMBER('1000')→1000
TO_NUMBER('$1,000', '$9,999')→$1,000

TO_DATE(文字データ)

文字データを日付データに変換する。TO_DATE('str', 'fmt')のように2つの引数を取る場合、strはfmtで指定された書式であると判断される
TO_DATE('2005-08-15')→'2005-08-15'
TO_DATE('20050815', 'YYYYMMDD')→'2005-08-15'

■一般関数
NVL

NULL値を実際の値に置き換える。NVL('expr1', 'expr2')のように2つの引数を取り、expr1に値があればexpr1を戻し、expr1が  NULL値ならばexpr2を戻す
NVL(comm, 0)
comm列がNULL→0
comm列が1000→1000

NVL2

値がある場合もNULL値の場合も値を置き換える。NVL2('expr1', 'expr2', 'expr3')のように3つの引数を取り、expr1に値があればexpr2を戻し、expr1がNULL値ならばexpr3を戻す
NVL2(comm, 1, 0)
comm列がNULL→0
comm列が1000→1

NULLIF

NULLIF('expr1', 'expr2')のように2つの引数を取り、expr1とexpr2が等しければNULL値を戻し、等しくなければexpr1を戻す
NULLIF(deptno, 99)
deptno列が10→10
deptno列が99→NULL

COALESCE

COALESCE(式リスト)のように複数の引数を取り、リスト内の最初のNULL値以外の値を戻す
COALESCE(c1, c2, c3)
c1列に10→10
c1列がNULL、c2列に20→20
c1、c2列がNULL、c3列に30→30

CASE

条件に一致した値を戻す。構文は次のとおり。
CASE 式 WHEN 条件1 THEN 値1
    [WHEN 条件n THEN 値n]
    [ELSE デフォルト値]
END
式と一致する条件nの値nを戻し、どの条件とも一致しない場合、デフォルト値を戻す
CASE c1
WHEN 'A' THEN 1
WHEN 'B' THEN 2
ELSE 0
END

DECODE

条件に一致した値を戻す。構文は次のとおり。

  DECODE(式, 条件1, 値1 [, 条件n, 値n] [, デフォルト値]

式と一致する条件nの値nを戻し、どの条件とも一致しない場合、デフォルト値を戻す
DECODE(c1, 'A', 1, 'B', 2, 0)
c1列が'A'→1
c1列が'B'→2
c1列が'C'→0

■グループ関数
AVG

平均値	AVG(c1)→250

COUNT

行数。*を使用すると、NULL値を含めた行数を求める	COUNT(*)→5
COUNT(c1)→4

MAX

最大値	MAX(c1)→400

MIN

最小値	MIN(c1)→100

SUM

合計値	SUM(c1)→1000

STDDEV

標準偏差	STDDEV(c1)→129.099445

VARIANCE

平方偏差	VARIANCE(c1)→16666.6667

COUNT(*)を除き、NULL値は無視されます。NULL値を計算(個数)に含めるのであれば、NVL関数などを使用して値に置き換える必要があります。

GROUP BY

SELECT {* | [DISTINCT] 列名 | 式} [列別名] [,...] FROM 表名
[WHERE 条件式]
GROUP BY グループ列名 [,...]
ORDER BY {列名 | 式 | 列別名 | 列位置}[,...] [ASC | DESC];

HAVING

GROUP BY句を使用して行をグループ化する場合、WHERE句による行の制限とHAVING句による行の制限が行えます。
             WHERE句による制限   HAVING句による制限
制限のタイミング     グループ化する前     グループ化した後
条件式でのグループ関数  使用不可能         使用可能
SELECT {* | [DISTINCT] 列名 | 式} [列別名] [,...] FROM 表名
[WHERE 条件式]
GROUP BY グループ列名 [,...]
HAVING 条件式
ORDER BY {列名 | 式 | 列別名 | 列位置}[,...] [ASC | DESC];
例)SELECT student_id, AVG(gra)
  FROM student
  WHERE selector_end IN ('1999-12-30', '2000-06-30')
  GROUP BY student_id
  HAVING AVG(gra) < 2.0;

■結合

等価結合   結合条件によって、特定の列の値が等しいものを結び付ける
非等価結合  結合条件によって、特定の列の値が特定の列の範囲内であるものを結び付ける
       (BETWEEN、<、<=、>、>=)

結合条件を満たすデータを戻すかどうかで、内部結合と外部結合に分類されます。

内部結合   結合条件を満たすデータだけを戻す
外部結合   結合条件を満たさないデータも含めて戻す

■内部結合
クロス結合

2つの表のすべての組み合わせを作成する。直積、デカルト積とも呼ばれる
 SELECT 列名リスト FROM 表名1 CROSS JOIN 表名2;
  ⇒2つの表のすべての組み合わせが戻される。

自然結合

2つの表の同じ名前の列すべてを、自動的に等価結合の結合条件として使用する
 SELECT 列名リスト FROM 表名1 NATURAL JOIN 表名2;
  ⇒2つの表の同じ名前の列は、同じデータ型である必要がある。

USING句

2つの表の同じ名前の列のうち、USING句で指定した列のみを等価結合の結合条件として使用する
 SELECT 列名リスト FROM 表名1 JOIN 表名2 USING(結合列);
  ⇒2つの表に2つ以上同じ列名のものがあり、そのうちの一部の列のみを結合条件にしたい場合。

ON句

2つまたは1つの表から、ON句で指定した結合条件を満たすもののみを戻す
 SELECT 列名リスト FROM 表名1 JOIN 表名2 ON(結合条件);

自己結合

1つの表から結合条件を満たすもののみを戻す。ON句を使用する
 SELECT 列名リスト FROM 表名1 別名1 JOIN 表名1 別名2 ON(結合条件);
  ⇒1つの表を別名を利用して2つの表に見せ掛け、結合することができます。

■外部結合