SQL
SQLの3値論理、真・偽・unknown(不定)
SELECT
同一の列を異なる選択項目に指定できる。
属性がn個ある関係の異なる射影は2ⁿ個
DISTINCT(重複を取り除く)
SELECT DISTINCT 単価
FROM 商品
→同じ単価のもがいくつあっても一つにする。
COALESCE(NULLを処理できる関数)
COALESCE(引数1, 引数2, ...)は、可変長の引数を持ち、NULLでない最初の引数を返す関数。
SELECT 年代, 性別, COALESCE (SUM (B1), 0) A1
FROM 会員
GROUP BY 年代, 性別
→SUM(B1)がNULLでない場合はSUM(B1)を返し、NULLの場合は0を無条件に返す。
CASE(条件式の利用)
SQL文の中で条件式を使用できる。
SELECT 会員番号,
SUM (CASE WHEN 入館時刻 < '1200'
THEN 1 ELSE 0 END) AS B1
FROM ...
ウィンドウ関数(分析関数)
特定の範囲のデータに対して計算等を行い、各行に対して一つの結果を返すことができる関数。
「OVER 句」を用いて表現。その後に「PARTITION BY句」を指定するとグルーピングできる。
AVG、MAX、SUM、などを含む。
| 関数 | 意味 |
|---|---|
| ROW_NUMBER() | 1からの行番号を取得 |
| LAG(列名[.n]) | n行前の[指定した列名の値]を取得 |
| LEAD(列名[.n]) | n行後の[指定した列名の値]を取得 |
| RANK() | 順位付けをする(同じ順位の場合、その後の順位を飛び番にする) |
| DENSE_RANK() | 順位付けをする(同じ順位の場合、その後の順位を飛び番にしない) |
| NTILE(n) | n個に均等に分割し、その分割した集合に対して順位をつける |
LAG関数
SELECT 社員, 年月, 労働時間, LAG(労働時間)
OVER (PARTITION BY 社員 ORDER BY 年月) AS 前月の労働時間
FROM 社員別月間労働時間
→社員別月間労働時間から1か月前(1行前)の労働時間を各行の4番目として抽出。
NTILE関数
顧客を4等分に分類して会員ごとに階級番号を求める。
SELECT 顧客, 月間売上,
NTILE (4) OVER (ODER BY 月間売上 DESC) AS 階級
FROM 売上表 ORDER BY 月間売上 DESC
範囲指定
範囲指定は「BETWEEN 開始地点 AND 終了地点」で指定する。
SELECT 年月, 月間売上,
AVG(月間売上)
OVER (ORDER BY 年月ROWS BETWEEN 2 PRECEDINGAND CURRENT ROW)
AS 移動平均値
FROM 月間売上表
| 開始地点、終了地点に使用 | 意味 |
|---|---|
| CURRENT ROW | 現在の行、または現在の値。ROWS→現在の行。RANGE→現在の値 |
| n PRECEDING | n行前、またはn値前 |
| n FOLLOWING | n行後、またはn値後 |
| UNBOUNDED PRECEDING | 先頭の行 |
| UNBOUNDED FOLLOWING | 末尾の行 |
WHERE(条件設定)
IN
INを使用すると、後に続く()内に指定した値だけが対象となる。
WHERE
受注日 IN ('20220520', '20230425')
LIKE
WEHER 担当者名 LIKE '三好%'
→「三好」で始まるものを指定
WEHER 担当者名 LIKE '_好%'
→1桁目は任意の文字で、2桁目は「好」であるものを指定
三好% = 前方一致
%康彦 = 後方一致
%三好康彦% = 中間一致
NULLのみ抽出
WHERE 電話番号 IS NULL
GROUP BYと集約関数
SELECT 列名, ...
FROM テーブル名
GROUP BY グループ化する列名, ...
[HAVING 条件式]
グループごとに合計値を求めたり、件数をカウントしたりするときは、GROUP BY句を使用する。
集約関数
GROUP BYは集約関数とともに用いられることも多い。
AVG、MAX、SUM、COUNT (*)など。
HAVING
グループ化した結果い対して検索条件を指定したい場合は、HAVING句の後に条件式を記述。
SELECT 受注番号, SUM(数量) AS 数量合計
FROM 受注明細
GROUP BY 受注番号
HAVING COUNT (*) >= 3
ORDER BY
SELECT文での問い合わせ結果を昇順、降順に並べ替えられる。
降順
SELECT 受注番号, SUM(数量) AS 数量合計
FROM 受注明細
GROUP BY 受注番号
ORDER BY 受注番号 DESC
並べるものを指定
SELECT 受注番号, SUM(数量)
FROM 受注明細
GROUP BY 受注番号
ORDER BY 2 ASC
→「2 ASC」SUM(数量)で並べる。
内部結合
結合条件で指定した列の値が、両方の表に存在している行だけを対象として結果を返す。
SELECT 列名, ...
FROM テーブル名1, テーブル名2
WHERE テーブル名1.列名 = テーブル名2.列名
SELECT 列名, ...
FROM テーブル名1 [INEER] JOIN テーブル名2
ON テーブル名1.列名 = テーブル名2.列名
SELECT 列名, ...
FROM テーブル名1 [INNER] JOIN テーブル名1
USING (列名, ...)
自然結合ならNATURAL JOINを指定。(ON、USING句は不要)
ON
結合条件の指定。
ONで指定された条件でテーブル結合を行い、後述のWHEREで条件を絞り込む。
JOINを利用する場合、結合する列名が同じであれば、ONではなく、USINGも使用できる。
外部結合
いずれか一方に値があれば結果を返す対象とする。
左外結合と右外部結合に分けられる。
左外部結合
例) A 外部結合 B
Aの値すべてが結果を返す対象となる。
SELECT 列名, ...
FROM テーブル名1 LEFT [OUTER] JOIN テーブル名2
ON テーブル名1.列名 = テーブル名2.列名
SELECT 列名,...
FROM テーブル名1 LEFT [OUTER] JOIN テーブル名2
USING (列名,...)
右外部結合
例) A 外部結合 B
Bの値すべてが結果を返す対象となる。
SELECT 列名,...
FROM テーブル名1 RIGHT [OUTER] JOIN テーブル名2
ON テーブル名1.列名 = テーブル名2.列名
全外部結合
左側、右側のいずれか一方に値があれば、それら全てが結果を返す対象になる。
SELECT 列名,...
FROM テーブル名1 FULL [OUTER] JOIN テーブル名2
ON テーブル名1.列名 = テーブル名2.列名
例)
SELECT 受注.受注番号, 受注.受注日, 得意先.得意先名
FROM 受注, 得意先
WHERE 受注.得意先コード = 得意先.得意先コード
→受注テーブルと得意先テーブルを得意先コードで結合して、受注番号、受注日、得意先名と表記する。
SELECT X.受注番号, X.受注日, Y.得意先名
FROM 受注 X, 得意先 Y
WEHRE X.得意先コード = Y.得意先コード
(省略ver)
SELECT 受注番号, 受注日, 得意先名
FROM 受注 X, 得意先 Y
WERE X.得意先コード = Y.得意先コード
→受注テーブルと得意先テーブルに別名を指定。
SELECT X.受注番号, X.受注日, Y.得意先名,
Z.行, Z.商品コード, Z.数量
FROM 受注 X, 得意先 Y, 受注明細 Z
WHERE X.得意先コード = Y.得意先コード
AND X.受注番号 = Z.受注番号
→受注テーブルと得意先テーブルを得意先コードで結合し、受注テーブルと受注明細テーブルを受注番号で結合。受注テーブル、受注明細テーブル、得意先テーブルを内部結合して、受注番号、受注日、得意先名、行、商品コード、数量を表示する。
等結合
結合列が重複して保持される。
自然結合
結合列の重複は取り除かれる。
3つ以上の外部結合
SELECT 列名,...
FROM テーブルA
LEFT OUTER JOIN テーブルB ON 結合条件
LEFT OUTER JOIN テーブルC ON 結合条件
LEFT OUTER JOIN テーブルD ON 結合条件
自己結合
一つの表に対して、二つの別名を使うことで、あたかも別々の二つの表を結合したときと同じ結果を得る結合方法。
SELECT X.会員名, Y.会員名 AS 上司の名前
FROM 会員 X, 会員Y
WHERE x.上司会員番号 = Y.会員番号
演算(和・差・直積・積・商)
和
UNIONを用いて表現。
重複行をまとめる
SELECT 商品価格 FROM りんご
UNION
SELECT 商品価格 FROM バナナ
重複行も、その行数文そのまま抽出
SELECT 商品価格 FROM りんご
UNION ALL
SELECT 商品価格 FROM バナナ
次数 = 属性の数
対応する属性のドメイン = 並びとタイプ
差
EXCEPTを用いて表現。
SELECT 商品価格 FROM りんご
EXCEPT
SELECT 商品価格 FROM バナナ
積
INTERSECTを用いて表現。
SELECT 商品番号 FROM 東京店
INTERSECT
SELECT 商品番号 FROM 大阪店
直積
二つの関係から任意のタプルを1個ずつ取り出して連結したタプルの集合になる。
R
| 属性A | 属性B |
|---|---|
| 1 | a |
| 2 | b |
S
| 属性C | 属性D |
|---|---|
| 3 | c |
| 4 | d |
| 5 | e |
↓
R * S
| 属性A | 属性B | 属性C | 属性D |
|---|---|---|---|
| 1 | a | 3 | c |
| 1 | a | 4 | d |
| 1 | a | 5 | e |
| 2 | b | 3 | c |
| 2 | b | 4 | d |
| 2 | b | 5 | e |
商
divisionを用いて表現。