はじめに
業務上でよく使うSQLをまとめます。
今回の記事に書くSQLは、RDBMSの中では基本的に以下のものに対応しています。
(例外的に対応していない可能性もありますのでご了承ください)
- DB2
- PostgreSQL
- MySQL
参考書は、「SQL-第2版-ゼロからはじめるデータベース操作」を活用させていただきました。
SELECT文
基本的なSELECT文
すべての列
SELECT * FROM <テーブル名>;
列を指定
SELECT <列名>, <列名> FROM <テーブル名>;
列に別名を付ける
SELECT <列名> AS <別名>, <列名> AS <別名> FROM <テーブル名>;
(実行例)
SELECT shohin_id AS "商品ID",
shohin_mei AS "商品名",
shiire_tanka AS "仕入単価"
FROM Shohin;
(出力例)
商品ID | 商品名 | 仕入単価
--------+----------------+----------
0001 | Tシャツ | 500
0002 | 穴あけパンチ | 320
定数の出力
SELECT <定数> AS <表示名>, <定数> AS <表示名> FROM <テーブル名>;
(実行例)
SELECT shohin_id AS "商品ID",
38 AS kazu,
'2009-02-24' AS hizuke
FROM Shohin;
(出力例)
商品ID | kazu | hizuke
--------+---------+---------------
0001 | 38 | 2009-02-24
0002 | 38 | 2009-02-24
結果から重複行を省く
対象カラムの重複を省いた形で出力
SELECT DISTINCT <対象列名> FROM <テーブル名>;
※NULLも1種類のデータとして扱われる
※DISTINCTキーワードは先頭の列名の前にしか書けない
WHERE句による行の選択
SELECT <列名>, <列名> FROM <テーブル名> WHERE <条件式>;
コメントの書き方
１行コメント
-- コメントです
複数行コメント
/*複数行
コメントです*/
演算子について
算術演算子
SELECT <列名>, <対象列名> * 2 AS "<表示名>" FROM <テーブル名>;
(出力例)
列名 | 表示名
--------+------------
0001 | 対象列×2
0002 | 対象列×2
| 意味 | 記号 |
|---|---|
| 足し算 | + |
| 引き算 | - |
| 掛け算 | * |
| 割り算 | / |
※通常の計算式と同じようにカッコ()も使える
※NULLを含んだ演算は問答無用でNULLになる
比較演算子
WHERE句の条件式には比較演算子が必要
| 意味 | 記号 |
|---|---|
| 等しい | = |
| 等しくない | <> |
| 以上 | >= |
| より大きい | > |
| 以下 | <= |
| より小さい | < |
※文字列型の順序の原則は辞書順。数値の大小順序と混合してはいけない。
※NULLは演算子に使用できない(レコードが取得できない)ため、IS NULL演算子、IS NOT NULL演算子を使う。
論理演算子
NOT演算子
SELECT <列名>, <列名> FROM <テーブル名> WHERE NOT <条件式>;
AND演算子、OR演算子
SELECT <列名>, <列名> FROM <テーブル名> WHERE <条件式> AND <条件式> OR <条件式>;
※条件を強くしたいときは()を付ける
集約と並べ替え
集約関数(集合関数)
- COUNT：レコードを数える
- SUM：数値列のデータを合計する
- AVG：数津列のデータを平均する
- MAX：任意の列データの最大値を求める
- MIN：任意の列データの最小値を求める
COUNT関数
全行を数える
COUNT * FROM <テーブル名>;
指定した列のNULLを除外して行数を数える
SELECT COUNT (<列名>) FROM <テーブル名>;
SUM関数
指定した列の合計を求める
SELECT SUM (<列名>) FROM <テーブル名>;
※「COUNT(*)」以外の集約関数はNULLを除外する
AVG関数
指定した列の平均値を求める
SELECT AVG (<列名>) FROM <テーブル名>;
MAX関数・MIN関数
最大値を求める
SELECT MAX (<列名>) FROM <テーブル名>;
最小値を求める
SELECT MIN (<列名>) FROM <テーブル名>;
重複値を除外して集約関数を使う
DISTINCTキーワードを使って、値の重複値を除いて行数を数える
SELECT COUNT (DISTINCT <列名>) FROM <テーブル名>;
テーブルをグループに切り分ける
GROUP BY句
集約
SELECT <列名1>, <列名2> FROM <テーブル名> GROUP BY <列名1>, <列名2>;
指定した列の分類ごとに行数を数える
SELECT <列名>, COUNT(*) FROM <テーブル名> GROUP BY <列名>;
(出力例)
列名 | count
-------------+------------
グループA | 3
グループB | 2
グループC | 4
句の記述順は不変『SELECT→FROM→(WHERE)→GROUP BY』
※集約キーにNULLが含まれる場合、結果にも「不明」行(空行)として現われる
※集約関数を書ける場所はSELECT句とHAVING句(とORDER BY句)だけ
集約した結果に条件を指定する
HAVING句
集約したグループに対する条件指定
SELECT <列名1>, <列名2>
FROM <テーブル名>
GROUP BY <列名1>, <列名2>
HAVING <条件式>;
記述順序『SELECT→FROM→WHERE→GROUP BY→HAVING』
HAVING句にかける要素
- 定数
- 集約関数
- GROUP BY句で指定した列名(集約キー)
※HAVING句はグループに対する条件指定であるため、集約キーに対する条件はHAVING句ではなくWHERE句に書く方がよい(処理速度も速い)
検索結果を並べ替える
ORDER BY句
SELECT <列名>, <列名>
FROM <テーブル名>
ORDER BY <並べ替えの基準となる列1>, <並べ替えの基準となる列2>;
| 設定値 | 結果 |
|---|---|
| デフォルト | 昇順 |
| ASC | 昇順 |
| DESC | 降順 |
句の記述順序『SELECT→FROM→WHERE→GROUP BY→HAVING→ORDER BY』
※NULLは先頭または末尾にまとめて表示される
※ORDER BY句では別名での指定が許されている(GROUP BY句では別名は使えない)
データの更新
INSERT文
テーブルにデータを1行登録する
INSERT INTO <テーブル名> (列1, 列2, 列3...) VALUES (値1, 値2, 値3...);
- テーブル名の後の列リストはテーブルの全行に対してINSERTを行る場合、省力可能
- 列リストと値リストの列数が不一致だとエラーになる
- 原則として1回の実行で1行を挿入する
(1回の実行で複数行をINSERTする機能は複数行INSERTと呼ぶ)
テーブルにデータを複数行登録する(複数行INSERT)
INSERT INTO <テーブル名> VALUES
(値1, 値2, 値3...),
(値1, 値2, 値3...),
(値1, 値2, 値3...)...;
他のテーブルからデータをコピーする
テーブルAのデータをテーブルBにコピー
INSERT INTO <テーブルB> (値1, 値2, 値3...)
SELECT (値1, 値2, 値3...) FROM <テーブルA>;
INSERT文内のSELECT文では、WHERE句やGROUP BY句など、どんなSQL構文も使うことができる
DELETE文
DROP TABLE → テーブルごとすべて削除
DELETE → テーブル自体は残して行のみをすべて削除
テーブルは残したまま、すべての行を削除する
DELETE FROM <テーブル名>;
一部の行だけを削除する探索型DELETE
DELETE FROM <テーブル名>WHERE <条件>;
※必ずテーブルを全行削除する場合TRUNCATEを使う方が高速
TRUNCATE <テーブル名>;
UPDATE文
テーブルのデータを変更する
UPDATE <テーブル名> SET <列名> = <式>;
一部の行だけを変更する探索型UPDATE
UPDATE <テーブル名> SET <列名> = <式>
WHERE <条件>;
※列をNULLで更新することをNULLクリアと呼ぶ
(NOT NULL制約や主キー制約の付いていない列に限る)
トランザクション
トランザクションとは「データベースに対する一つ以上の更新をまとめて呼ぶときの名称」である
DBMSのトランザクションの制約「ACID特性」
- 原子性(Atomicity)
- 一貫性(Consistency)
- 独立性(Isolation)
- 永続性(Durability)
トランザクション開始文;
DML文①;
DML文②;
DML文③;
・
・
・
トランザクション終了文(COMMIT または ROLLBACK);
DML文にはINSERT文、DELETE文、UPDATE文があてはまる
※SQL Server、PostgreSQL、MySQLなどはデフォルト設定が自動コミットモードになってる
トランザクション開始文
DBMSによって違いがある
- SQL Server、PostgreSQL
BEGIN TRANSACTION - MySQL
START TRANSACTION
トランザクション終了文
COMMIT -処理の確定(上書き保存)
ROLLBACK -処理の取り消し(保存せずに終了)
述語
述語とは、戻り値が真理値(TRUE/FALSE/UNKNOWN)になる関数のことです。
[述語例]
- LIKE
- BETWEEN
- IS NULL, IS NOT NULL
- IN
LIKE述語 -文字列の部分一致検索
部分一致には以下の3種類がある
- 前方一致
- 中間一致
- 後方一致
前方一致
SELECT * FROM <テーブル名> WHERE <列名> LIKE '<文字列>%';
中間一致
SELECT * FROM <テーブル名> WHERE <列名> LIKE '%<文字列>%';
後方一致
SELECT * FROM <テーブル名> WHERE <列名> LIKE '%<文字列>';
※ %は文字数がわかっている場合_に置き換えることができる
(_は任意の一文字を意味する)
BETWEEN述語 -範囲検索
(実行例)
販売単価が100～1000円の商品を選択
SELECT shohin_mei, hanbai_tanka FROM Shohin
WHERE hanbai_tanka BETWEEN 100 AND 1000;
(出力例)
shohin_mei | hanbai_tanka
------------+-------------
Tシャツ | 1000
フォーク | 500
↑ BETWEENで記載する場合、両端の値(100, 1000)も含まれる
IS NULL、IS NOT NULL -NULLか非NULLかの判定
(実行例)
仕入単価がNULLの商品を選択
SELECT shohin_mei, shiire_tanka FROM Shohin
WHERE shiire_tanka IS NULL;
(出力例)
shohin_mei | shiire_tanka
------------+--------------
フォーク |
スプーン |
(実行例)
仕入単価がNULL以外の商品を選択
SELECT shohin_mei, shiire_tanka FROM Shohin
WHERE shiire_tanka IS NOT NULL;
IN述語 -ORの便利な省略形
(実行例)
ORで複数の仕入単価を指定して検索
SELECT shohin_mei, shiire_tanka FROM Shohin
WHERE shiire_tanka = 320
OR shiire_tanka = 500
OR shiire_tanka = 5000;
(出力例)
shohin_mei | shiire_tanka
--------------+--------------
Tシャツ | 500
ボールペン | 320
圧力鍋 | 5000
(実行例)
INで複数の仕入単価を指定して検索(実行結果は上記と同じ)
SELECT shohin_mei, shiire_tanka FROM Shohin
WHERE shiire_tanka IN (320, 500, 5000);
反対に指定した値以外を選択したい場合はNOT INを使う
※INやNOT INはNULLを選択することはできない
※INやNOT INの後にサブクエリの記載も可能
集合演算
テーブルの足し算と引き算
UNION -テーブルの足し算
UNIONによるテーブルの足し算
SELECT <列名>, <列名>... FROM <テーブルA>;
UNION
SELECT <列名>, <列名>... FROM <テーブルB>;
※演算対象となるレコードの列数が同じであることと、足し算の対象となるレコードの列のデータ型が一致している必要がある
※UNIONに限らず集合演算子は、通常は重複行が削除される
ALLオプション -重複行を残す
UNIONによるテーブルの足し算で重複行を排除しない
SELECT <列名>, <列名>... FROM <テーブルA>;
UNION ALL
SELECT <列名>, <列名>... FROM <テーブルB>;
INTERSECT -テーブルの共通部分の選択
INTERSECTによるテーブル共通部分の選択
SELECT <列名>, <列名>... FROM <テーブルA>;
INTERSECT
SELECT <列名>, <列名>... FROM <テーブルB>;
EXCEPT -レコードの足し算
EXCEPTによるレコードの引き算
SELECT <列名>, <列名>... FROM <テーブルA>;
EXCEPT
SELECT <列名>, <列名>... FROM <テーブルB>;
結合
結合(JOIN)とは別のテーブルから列を持ってきて列を増やす集合演算です。
INNER JOIN -内部結合
(実行例)
SELECT TS.tenpo_id, TS.tenpo_mei, TS.shohin_id, S.shohin_mei, S.hanbai_tanka
FROM TenpoShohin AS TS INNER JOIN Shohin AS S
ON TS.shohin_id = S.shohin_id
ORDER BY tenpo_id;
- 結合を行うときは、FROM句に複数のテーブルを記述する
- 内部結合ではON句は必須。記述場所はFROMとWHEREの間
ONによるテーブル足し算(和集合)のイメージ
- 結合を使った場合のSELECT句の列は、すべての<テーブルの別名>.<列名>の形式で書く
OUTER JOIN -外部結合
(実行例)
SELECT TS.tenpo_id, TS.tenpo_mei, S.shohin_id, S.shohin_mei, S.hanbai_tanka
FROM TenpoShohin TS RIGHT OUTER JOIN Shohin S
ON TS.shohin_id = S.shohin_id
ORDER BY tenpo_id;
- 外部結合のポイントは、片方のテーブル情報がすべて出力されること
- LEFTを使った場合はFROM句の左側に書いたテーブルをマスタとし、RIGHTを使った場合は右側のテーブルをマスタとして使う
(LEFTとRIGHTはどちらを使ってもテーブルの記載を入れ替えれば結果は同じになる)
CROSS JOIN -クロス結合
クロス結合は、2つのテーブルのレコードについて、すべての組み合わせを作る結合 方法(結果は2つのテーブル行数を掛け算した数になる)
(実行例)
SELECT TS.tenpo_id, TS.tenpo_mei, TS.shohin_id, S.shohin_mei
FROM TenpoShohin AS TS CROSS JOIN Shohin AS S;
※クロス結合の場合、ON句は指定することができない