Oct20th

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つの表に見せ掛け、結合することができます。

■外部結合