はじめに
SQLの基礎的な使い方をまとめています。
初学者の覚書ですので、もし誤りがあれば指摘いただけますと幸いです。
SELECT文での取得方法あれこれ
並べ替え
ORDER BY句で検索結果を並び替えて取得できる。
特に指定しないとASC(昇順)になる。
並べ替え.sql
SELECT column_name
FROM table_name
ORDER BY column_1, column_2 desc;
-- カラム1の昇順で並び替え、カラム1に同値があればカラム2の降順で並び替える
行数を絞る
OFFSET句で先頭から除外する行数を指定し、LIMITもしくはFETCH句で取得する行数を指定できる。
※対応しているかはDBMSによりけり
行数限定.sql
SELECT column_name
FROM table_name
ORDER BY column_1
LIMIT 1 OFFSET 1;
-- MySQL,PostgreSQL,SQLite,MariaDB等
-- 先頭から2行目だけ取得する
SELECT column_name
FROM table_name
ORDER BY column_1
OFFSET 0 ROWS
FETCH NEXT 10 ROWS ONLY;
-- Oracle等
-- 先頭から10件取得する
重複している行を取り除く
DISTINCT句で結果表から重複している行を除外できる。
重複除外.sql
SELECT DISTINCT column_name
FROM table_name;
複数テーブルの検索結果を組み合わせる
集合演算子を使って複数テーブルのSELECTの結果をまとめられる。
重複がある行は1行にまとめてくれるが、ALLを付けると重複をまとめずに結果を返す。
取得するそれぞれのテーブルの列数とデータ型が一致している必要がある。
集合演算子.sql
SELECT column_1
UNION -- 和集合
SELECT column_2;
SELECT column_1
EXCEPT -- カラム1-カラム2の差集合
SELECT column_2;
SELECT column_1
INTERSECT ALL -- 積集合。重複した行をまとめない。
SELECT column_2;
WHERE句あれこれ
WHEREの後ろに条件式を記述し、真と評価されるデータが処理対象となる。
比較演算子.sql
SELECT column_name
FROM table_name
WHERE column_1 = 'hoge'
AND column_2 > 0
AND column_2 <= 10
AND column_3 <> 0;
複数の条件式の優先度
論理演算子の優先順位は以下の通り。
1.NOT
2.AND
3.OR
論理演算子.sql
SELECT column_name
FROM table_name
WHERE (column_1 = 'カテゴリ1' OR column_1 = 'カテゴリ2')
AND (column_2 = 'サブカテゴリ1' OR column_2 = 'サブカテゴリ2');
-- 括弧でくくると優先順位が上がる
LIKEで文字列検索
対象カラムに対して曖昧検索をかけられる。
ワイルドカードとして「%」は0文字以上の任意の文字列、「_」は任意の1文字を意味する。
LIKE.sql
SELECT column_name
FROM table_name
WHERE column_1 LIKE '%hoge%' -- 「hoge」を含む文字列
AND column_2 LIKE 'fuga_' -- 「fuga」から始まり、その後ろに任意の1文字
AND column_3 LIKE '_A%' -- 2文字目が「A」の文字列
AND column_4 NOT LIKE '%piyo'; -- 末尾が「piyo」ではない文字列
IN / ANY / ALL で複数値の比較
IN演算子は複数の値との合致を判定できる。
ANY演算子とALL演算子は複数の値との大小を比較できる。
複数比較.sql
SELECT column_name
FROM table_name
WHERE column_a IN (1, 2, 3) -- 1,2,3のいずれかと一致
AND column_b NOT IN (1, 2, 3) -- 1,2,3のいずれとも一致しない
AND column_c > ANY (1, 2, 3) -- 1,2,3のいずれかより大きい
AND column_d > ALL (1, 2, 3); -- 1,2,3の全てより大きい
NULLの判定
NULLの判定には=や<>は使えない。
NULL判定.sql
SELECT column_name
FROM table_name
WHERE column_1 IS NULL
AND column_2 IS NOT NULL;
関数あれこれ
検索結果を集計する
集計関数は、集計対象の全行をまとめて、1回の集計処理を実行し1行の結果を返す。
集計関数.sql
SELECT
SUM(column_name), -- 合計
AVG(column_name), -- 相加平均
MAX(column_name), -- 最大値
MIN(column_name) -- 最小値
FROM table_name;
SELECT
COUNT(*), -- NULLを含めて行数カウント
COUNT(column_name) -- NULLは無視して行数カウント
FROM table_name;
文字列を置き換える
REPLACE関数は文字列の一部を別の文字列に置換し、置換後の文字列を返す。
文字列置換.sql
UPDATE table_name
SET column_name = REPLACE(column_name, 'hoge', 'fuga')
-- 指定した列の「hoge」という文字列をすべて「fuga」に置き換える
文字列の一部を抽出する
SUBSTR(SUBSTRING)関数は文字列の一部を取り出して返す。
一部抽出.sql
SELECT column_name
FROM table_name
WHERE SUBSTR(column_name, 1, 3) LIKE '%A%'
-- 指定列で1～3文字目に「A」が含まれるレコードを検索する
SELECT SUBSTR(column_name, 1, 5) || '以下略' AS column_alias
FROM table_name
-- 指定列から1～5文字目を取り出し「以下略」を連結した文字列を表示する
NULLを置き換える
COALESCE関数は引数を左から順に評価し、NULLでない値を見つければその値を返す。
返す値の型は1つに定める必要がある(以下のコード例ではいずれの場合も文字列が返る想定)
COALESCE関数.sql
SELECT COALESCE(column_1, column_2, 'NULLです') AS column_alias
FROM table_name
-- カラム1がNULLでなければカラム1の値を、カラム1がNULLでカラム2がNULLでなければカラム2の値を、カラム1とカラム2の両方がNULLでれば「NULLです」の文字列を返す
CASE式あれこれ
CASE演算子で条件分岐処理ができる。
ELSE節を省略した場合、一致する値がなければNULLが返る。
CASE演算子.sql
SELECT column_1,
CASE column_1
WHEN 'a' THEN '上'
WHEN 'b' THEN '中'
WHEN 'C' THEN '下'
ELSE 'N/A'
END AS column_alias
FROM table_name;
-- カラム1の値に基づき条件を評価し、別名の列に結果が表示される
SELECT column_1,
CASE
WHEN column_2 < 0 THEN 'NG'
WHEN column_2 >= 1 AND column_2 <100 THEN 'OK'
ELSE 'N/A'
END AS column_alias
FROM table_name;
-- 直接指定した条件を評価し、別名の列に結果が表示される
SELECT column_1,
CASE
WHEN column_1 = 'a' THEN
CASE
WHEN column_2 < 0 THEN '上の下'
WHEN column_2 >= 1 AND column_2 <100 THEN '上の中'
ELSE '上の上'
END
WHEN column_1 = 'b' THEN
CASE
WHEN column_2 < 0 THEN '中の下'
WHEN column_2 >= 1 AND column_2 <100 THEN '中の中'
ELSE '中の上'
END
ELSE 'N/A'
END AS column_alias
FROM table_name;
-- 入れ子にすることも可能。ある条件に応じて更に別の条件を評価し結果を返す。
UPDATE table_name
SET column_3 = CASE
WHEN column_2 < 0 THEN column_3 * 0.9
WHEN column_2 >= 100 THEN column_3 * 1.1
ELSE column_3
END
WHERE column_1 in ('a', 'b')
-- UPDATE文で使うと特定条件に基づきテーブルの値を更新できる
おわりに
備忘録として適宜更新していきます。