SQL入門2

SQL関数

文字関数

LOWER( 文字列 )
引数のアルファベット文字列を、すべて小文字に変換

UPPER( 文字列 )
引数のアルファベット文字列を、すべて大文字に変換

INITCAP( 文字列 )
引数のアルファベット文字列を、各単語の先頭文字を大文字に変換し、残りの文字を小文字に変換

SELECT sno, sname, INITCAP( area ), LOWER( city )
FROM student
WHERE area = UPPER( 'tokyo' )
ORDER BY sno;

       SNO SNAME                INITCAP(AREA)        LOWER(CITY)
---------- -------------------- -------------------- --------------------
         1 Y_YAMADA             Tokyo                shinjuku
         4 H_SATO               Tokyo                shinjuku
         5 N_TAKADA             Tokyo                shinagawa
         8 A_SHIMADA            Tokyo                shinagawa
        10 M_NARITA             Tokyo                suginami
        11 H_TAKAHASHI          Tokyo                setagaya
        13 Y_SUZUKI             Tokyo                saitama
```

:star:CONCAT( 文字列 1, 文字列 2 )
文字列 1 と文字列 2 を 1 つに結合

:star:SUBSTR( 文字列, 数値 1, 数値 2 )
文字列の、数値 1 文字目から、数値 2 の文字数分抜きだす

:star:LENGTH( 文字列 )
文字列の文字数を戻す

:star:INSTR( 文字列 1, 文字列 2 )
文字列 1 のなかで、文字列 2 の位置を調べ、何文字目に現れるか

:star: LPAD( 文字列, 数値, 'パディング文字' )
数値で指定した桁数になるまで、列の値の左側に、指定したパディング文字 を埋め込む

:star: RPAD( 文字列, 数値, 'パディング文字' )
数値で指定した桁数になるまで、列の値の右側に、指定したパディング文字 を埋め込む

:star:TRIM( LEADING | TRAILING | BOTH 削除文字列 FROM 文字列 
文字列の先頭、最後、またはその両方から削除文字列を切り捨てる

:star:REPLACE( 文字列, 検索文字列, 置換文字列 )
文字列から検索文字列を探し、置換文字列に置き換える

:star:TRIM( LEADING | TRAILING | BOTH 削除文字列 FROM 文字列 ) 
:star: REPLACE( 文字列, 検索文字列, 置換文字列 )
TRIM 関数は、文字列の先頭、最後、またはその両方から削除文字列を切り捨てる
REPLACE関数は、文字列から検索文字列を探し、置換文字列に置き換える

 ```
SELECT sno,area,
TRIM( LEADING 'T' FROM area ) AS TrimTest,
REPLACE( area, 'TOKYO', 'SAITAMA' ) AS ReplaceTest
FROM student
ORDER BY sno;

STUDENT 表から、SNO 列、AREA 列、AREA 列の先頭から T を削除したもの、AREA 列の TOKYO を SAITAMA に置き換えたもの
 ```
### 数値関数
:star: CEIL ( 数値 )
引数の数値以上の最も小さい整数

:star: FLOOR ( 数値 )
引数の数値以下の最も大きい整数

:star: POWER ( 数値 1, 数値 2 )
引数の「数値 1」を「数値 2」だけ乗じた値(べき乗)を戻す

:star:SQRT ( 数値 )
引数の数値の平方根

:star:CEIL ( 数値 )、FLOOR ( 数値 )、POWER ( 数値 1, 数値 2 )、SQRT ( 数値 )
それぞれの最も小さい整数、最も大きい整数、乗じた値、平方根

:star:ROUND( 数値 1, 数値 2 )
小数点以下数値 2 の桁まで四捨五入

:star: TRUNC( 数値 1, 数値 2 )
小数点以下数値 2 の桁まで切り捨てた値

:star: MOD( 数値 1, 数値 2 )
数値 1 を 数値 2 で除算した余り

###	日付関数
:star: SYSDATE
データベース 上の現在の日付、および時刻

:star: MONTHS_BETWEEN( 日付 1, 日付 2 )
日付 1 から日付 2 までの月数

:star: ADD_MONTHS( 日付, 数値 )
指定した日付に数値の月数を加算

:star: NEXT_DAY(日付、'文字列')
指定した日付の次に来る指定「曜日」の日付

:star: LAST_DAY ( 日付 )
日付で指定した月の末日

:star: ROUND ( 日付, '表示書式' )
指定した日付を、表示書式で指定した単位に四捨五入

:star: TRUNC ( 日付, '表示書式' )
指定した日付を、表示書式で指定した単位に切り捨てた日付

### 変換関数( 明示的なデータ型変換 )
:star: TO_CHAR ( 数値/日付, 表示書式, nls パラメータ )
数値型、および日付型のデータを、指定した表示書式にしたがって、可変長 の文字型データに変換

:star: TO_NUMBER( 文字, 表示書式, nls パラメータ )
数値を表す文字型のデータを、指定した表示書式にしたがって数値型データに変換

:star: TO_DATE( 文字, 表示書式, nls パラメータ )
日付を表す文字型のデータを、指定した表示書式にしたがって日付型データに変換

### 汎用関数
:star: NVL( 式, 値 )
式の値が NULL 値の場合には、値を戻す

:star: NVL2( 式, 値 1, 値 2 )
式の値が NULL 値以外の場合には、値 1 を戻す

:star: NULLIF( 式 1, 式 2 )
2 つの式の値を比較します。2 つの値が等しい場合には NULL 値を戻し、等しくない場合には式 1 の値を戻す

:star: COALESCE( 式 1, 式 2, ･･･, 式 n )
式リスト のなかの、最初の NULL 値ではない式

:star: GREATEST( 値 1, 値 2, ･･･, 値 n )
値リスト のなかでもっとも大きい値

:star: LEAST( 値 1, 値 2, ･･･, 値 n )
値リストの中でもっとも小さい値

:star: USER
ログイン しているユーザー の名

### 汎用関数
:star: DECODE( 式, 条件 1, 値 1, ･･･, 条件 n, 値 n, デフォルト値 )
式が条件と同じ場合には、対応する値を戻す

:star: CASE 式

```
CASE 式 WHEN 条件 1 THEN 値 1
        WHEN 条件 2 THEN 値 2
        WHEN 条件 n THEN 値 n
        ELSE デフォルト値
END
```
```
eg)
SELECT sno, sname, area, score, CASE area WHEN 'HOKKAIDO' THEN score + 5 WHEN 'TOKYO' THEN score + 3 ELSE score
                                END AS adj_score
FROM student
ORDER BY sno;
```

### グループ関数
:star: AVG( 列名 )
NULL 値を除いた平均値

:star: COUNT( 列名 )
NULL 値を除いた行数

:star: MAX( 列名 )
NULL 値を除いた最大値

:star: MIN( 列名 )
NULL 値を除いた最小値

:star: STDDEV( 列名 )
NULL 値を除いた標準偏差

:star: SUM( 列名 )
NULL 値を除いた合計

:star: VARIANCE( 列名 )
NULL 値を除いた偏差 

```
eg)STUDENT 表から、BIRTH 列の行数、最高値、および最小値を表示

SELECT COUNT( birth ), TO_CHAR( MAX( birth ), 'YYYY-MM-DD' ) MAX_BIRTH, TO_CHAR( MIN( birth ), 'YYYY-MM-DD' ) MIN_BIRTH
FROM student;
```

### データグループの作成
:star: GROUP BY
・グループに分割する前に行を制限するには、WHERE 句を使用

```
SELECT 列名, グループ関数名( 列名 )
FROM 表名
WHERE 条件式
GROUP BY 列名
ORDER BY 列名;
```

:star: HAVING
条件を指定してグループの結果を制限

SELECT 列名, 列名, グループ関数( 列名 )
FROM 表名
WHERE 条件式
GROUP BY 列名
HAVING 条件式
ORDER BY 列名;

## 副問合せ
### 単一行副問合せ

SELECT sno, sname, area
FROM student
WHERE area = ( SELECT area
FROM student
WHERE sno = 1 )
ORDER BY sno;

### 複数行副問合せ

SELECT sno, sname, score, class
FROM student
WHERE score IN ( SELECT MIN( score )
FROM student
GROUP BY class )
ORDER BY sno;

SELECT sno, sname, area, score
FROM student
WHERE score < ANY ( SELECT score
FROM student
WHERE area = 'TOKYO' )
ORDER BY sno;

### インライン・ビュー
FROM 句のなかの表別名 をつけた副問合せ のこと

STUDENT 表から、SNO 列、SNAME 列、SCORE 列、および CLASS 列を検索。また、STUDENT 表から、CLASS 列、および CLASS 列ごとの SCORE 列の最高値をふくむインライン・ビューである B を作成し、B から、CLASS 列ごとの SCORE 列の最高値を表示

SELECT a.sno, a.sname, a.score, a.class, b.maxscore
FROM student a, ( SELECT class, MAX( score ) maxscore
FROM student
GROUP BY class ) b
WHERE a.class = b.class
ORDER BY a.sno;

### トップ N 分析
トップ N 分析は、条件に基づいて、表 から上位 n 個、または下位 n 個の行 を検索する必要がある場合に使用

SELECT ROWNUM, 列名, 列名
FROM ( SELECT 列名, 列名 FROM 表名 ORDER BY 列名 )
WHERE ROWNUM <= 数値;