スッキリわかるSQL入門 第2版を読みながら、主に自分用の備忘録として残します。
dokoQLというサイトを使うと実際にSQL文を実行できます。
https://dokoql.com/d/Dokoqlv/index.html
実際のSQLでは--でコメントアウトですが、著者は//のコメントアウトが慣れているので、そのように記載します。
また、サンプルコードに関しては動作確認していないので、もしコードにバグや間違い等ありましたらご指摘頂けると幸いです。
#超基本
##データの検索：SELECT
SELECT 列名
FROM テーブル名
//ex：Expensesテーブルのすべての列を表示
SELECT *
FROM Expenses
##条件：WHERE
//WHEREで条件を加える
SELECT *
FROM Expenses
WHERE expenditure > 3000
##データの追加：INSERT INTO, VALUES
INSERT INTO テーブル名 （オプション：カンマ区切りで列名の指定）
VALUES (各列カンマ区切りで内容を入力)
//ex1：列名の指定なしで、すべての列にデータを追加（SELECT *と同じ順序で記載）
INSERT INTO Expenses
VALUES ('2018-02-25', 'housing', 'March rent', 0, 85000)
//ex2：列名を指定して、その列のみデータを追加（順序自由）
INSERT INTO Expenses (entry, date, expenditure)
VALUES ( 'housing', '2018-02-25', 85000)
//ex3：VALUESを使わず、副問い合わせによってデータを追加
INSERT INTO ExpenseArchives
SELECT * FROM Expenses
WHERE date < '2021-01-01';
##データの更新：UPDATE, SET
UPDATE テーブル名
SET 列 = 値
（必要に応じてWHEREで条件）
//ex
UPDATE Expenses
SET expenditure = 90000
WHERE date = '2018-02-25'
##データの削除：DELETE
DELETE FROM テーブル名
（必要に応じてWHEREで条件）
//ex
DELETE FROM Expenses
WHERE date = '2018-02-25'
#基本
##比較演算子
NULLの判定はこの比較演算子ではできないので、この下のNULLの判定を使ってください
=：等しい
<>：等しくない
<：左辺が小さい
>：左辺が大きい
<=：左辺が右辺以下
>=：左辺が右辺以上
NULLの判定
//NULLである
式 IS NULL
//NULLでない
式 IS NOT NULL
//ex
SELECT *
FROM Expenses
WHERE expenditure IS NULL
##比較（一致）：IN, NOT IN
比較演算子だと1つしか比較できませんが、このINなどを使えば複数の比較ができます
INでは含むもの、NOT IN では全て含まないものが抽出対象になります
式 IN/NOT IN (値1, 値2, ...)
//ex
//entryにfoodかsocializingが含まれているレコードを表示
SELECT *
FROM Expenses
WHERE entry NOT IN ('food', 'socializing')
//ex2
//entryにfoodとsocializingが含まれていないレコードを表示
SELECT *
FROM Expenses
WHERE entry NOT IN ('food', 'socializing')
複数の条件の組み合わせ：AND, OR
条件式1 AND/OR 条件式2
//ex
SELECT *
FROM Expenses
//expenditureが3000以上または、1000以下のデータを抽出
WHERE expenditure => 3000 OR expenditure =< 1000
##真偽値の逆転：NOT
論理演算子の実行順序はNOT>AND>ORの順番で処理されるので、その点注意して下さい
NOT 条件式
//ex
SELECT *
FROM Expenses
//storeがB以外の時
WHERE NOT store = 'B'
##パターンマッチング：LIKE, ESCAPE
パターン文字列を使うことでパターンにマッチした文字列を表現できる
文字列として%や_を使いたい場合はESCAPEが必要になる
式 LIKE パターン文字列（下記参照）
%：任意の0文字以上の文字列
_：任意の1文字
//ex1
//例えば以下のパターンマッチではExpensesのmemo列で「january 3rd」や「2021 january rent」などと表現されているレコードを抜き出せる
SELECT * FROM Expenses
WHERE メモ LIKE '%january%'
//ex2
//ESCAPEを書くことにより、$がエスケープ文字として認識され、エスケープ文字の直後の%や_は文字列として認識される（下記の例の場合january_3rdみたいな表記の検索ができる）
SELECT * FROM Expenses
WHERE メモ LIKE '%january$_%' ESCAPE '$'
##間の検索：BETWEEN AND
式 BETWEEN 値1 AND 値2
//ex
//expenditureが100～3000のレコードを取得
SELECT *
FROM Expenses
WHERE expenditure BETWEEN 100 AND 3000
##計算式を含むSQL
//ex1
//expenditureと100円増しのexpenditureの列を表示
SELECT expenditure , expenditure + 100 AS 100円増しのexpenditure
FROM Expenses
//ex2
//すべてのexpenditureを100円足して更新
UPDATE Expenses
SET expenditure = expenditure + 100
##値を評価し好きな値へ変換：CASE WHEN THEN END
CASE 評価したい式や列 WHEN 値1 THEN 変える値1
(WHEN 値2 THEN 変える値2)
(ELSE デフォルト値)
END
//ex
//entryの値に応じて、housingとutilityはfixed costで、それ以外はvariable costとして表示する
SELECT entry, expenditure,
CASE entry WHEN 'housing' THEN 'fixed cost'
WHEN utility THEN 'fixed cost'
ELSE 'variable cost'
END
FROM Expenses WHERE expenditure >0
#検索結果の加工
##重複行の排除：DISTINCT
DISTINCTを使ってSELECTを行うと、重複がなくなるのでユニーク（一意）な値だけが表示されます
SELECT * DISTINCT income
FROM Expenses
##並べ替える：ORDER BY
SELECTの中身としては変わらないが、昇順・降順で並べ替えられます
ちなみに並べ替える列にNULL値があると、NULLは一番大きい値（昇順で一番下）として認識されるみたいです
ORDER BY 列名 並び順（下記参照）, 列名 並び順, ...（並列可能）
ASC：昇順（デフォルト）
DESC：降順（要指定）
//ex1
SELECT * FROM Expenses
//expenditureが降順で表示
ORDER BY income DESC, expenditure DESC
//ex2
SELECT * FROM Expenses
//列番号でも可
ORDER BY 4 DESC, 5 DESC
##出力する行数制限：LIMIT
SELECT * FROM Expenses
//上位3レコードのみ表示
ORDER BY income ASC LIMIT 3
##集合演算子：UNION, (EXCEPT, INTERSECTION)
UNIONであればSELECT文を和集合として表示でき、同様にEXCEPTは差集合、INTERSECTIONは積集合として表示できる
選択列のリストの列数とそれぞれのデータ型が同じでなければ使えない
UNION単体なら重複は削除、ALLのオプション付けると重複もすべて表示
EXCEPT, INTERSECTIONはmysqlでは使うことができない上、使い方が同じな為UNIONのみ紹介する
SELECT 文1
UNION オプション：ALL
SELECT 文2
//ex
SELECT * FROM Expenses
UNION ALL
SELECT * FROM ExpensesArchive
##計算式に別名を付ける：AS
//expenditureの列が出費という名前で表示される
SELECT expenditure AS 出費
FROM Expenses
#グループ化と集計関数
##集計関数：SUM, MAX, MIN, AVG, COUNT
SUM, MAX, MIN, AVG, COUNTは名前の通りそれぞれ合計、最大、最小、平均、行数のカウントを返します
COUNT(*)はNULLを行数としてカウントするのに対して、COUNT(列名)はNULLを行数としてカウントしない点に注意
そして基本的にこれの関数はSELCT文と後述のORDER BY, HAVINGの中だけで使え、WHEREなどでは使用できません
//ex1
SELECT SUM(expenditure) AS expenditureSum
FROM Expenses
//ex2
//Expensesの（NULLを含む）行数をrowCountとして出力
SELECT COUNT(*) AS rowCount
FROM Expeses
##グループ化：GROUP BY
SELECT 列や集計関数等
FROM テーブル名
(WHERE)
GROUP BY グループの基準列名
//ex
//entryをもとにグループ化が行われ、SUMの結果もentryごとに出力される
SELECT entry, SUM(expenditure)
FROM Expenses
GROUP BY entry
##グループ化したときの条件：HAVING
集計関数で述べたように、WHEREで集計関数はSQLの実行順序的に使えません（詳細はおまけの実行順序参照）
GROUP BYで集計したのち、その集計したものをさらに条件をかけたい場合にWHEREと同じ要領でHAVINGを使います
SELECT 列や集計関数等
FROM テーブル名
(WHERE)
GROUP BY グループの基準列名
HAVING 条件
//ex
//グループ化して、entry毎にexpenditureが1000以上の物のみ表示（絞り込み）
SELECT entry, SUM(expenditure)
FROM Expenses
GROUP BY entry
HAVING SUM(expenditure) => 1000
#テーブルの結合
##内部結合：JOIN ON
内部結合を行います（結合できるレコードがない時はすべて表示しない）
SELECT 選択列リスト
FROM テーブルA
JOIN テーブルB
ON 両テーブルの結合条件
//ex
SELECT DATE, NAME
FROM Expenses
JOIN Incomes
ON Expenses.ID = Incomes.ID
##外部結合：LEFT JOIN, RIGHT JOIN, FULL JOIN
LEFT JOIN：左外部結合（結合できるレコードがない時、左側のテーブルのみ全て表示して右側のテーブルは表示しない）
RIGHT JOIN：右外部結合（結合できるレコードがない時、右側のテーブルのみ全て表示して左側のテーブルは表示しない）
FULL JOIN：完全外部結合（結合できるレコードがない時としても全て表示）
//ex
SELECT DATE, NAME
FROM Expenses
LEFT JOIN Incomes
//左や右は=の左側か右側かで判断される
ON Expenses.ID = Incomes.ID
#便利な関数
##文字列
###文字数を数える：LENGTH
引数の文字列を出力
//ex
SELECT memo, LENGTH(memo) AS memo length
FROM Expenses
###文字列の余白を削除：TRIM
左右の余白を削除
LTRIM：左側の余白を削除
RTRIM：右側の余白を削除
使いどころとしては、CHAR(10)にabcと入力すると、右側に7個余白がついている状態になるので、主にそれを削除するために使われる
//ex
SELECT memo, TRIM(memo) AS trimmed memo
FROM Expenses
###文字列を置換：REPLACE
REPLACE(置換対象の列, 置換前の文字列, 置換後の文字列)
//ex
//この場合はメモ列に入っているbuyという文字列を買ったという文字列に置換している
UPDATE Expenses
SET memo = REPLACE(memo, 'buy', '買った')
###文字列の一部を抽出：SUBSTRING
SUBSTRING(文字列を表す列, 抽出を開始する位置, そこから抽出する文字数)
//ex
//entry列の1文字目から3文字を抜き出して、それが「～費」というパターンマッチに適合したレコードのみ表示する
SELECT * FROM Expenses
WHERE SUBSTRING(entry, 1, 3) LIKE '%費'
###文字列の結合：CONCAT, ||
純粋な文字列の結合は||だけでできますが、列同士で文字列の結合を行う場合はCONCATを使いましょう
CONCAT(文字列, 文字列 [, 文字列...])
//ex
//entry : memoの状態で列が結合される
SELECT CONCAT(entry, ' : ' || memo)
FROM Expenses
##数値
###指定桁で四捨五入：ROUND
ROUND(四捨五入する数値, 有効桁数)
有効桁数：正の数は小数点以下の有効桁数、負の数は指定した分の下桁数
//ex
//-2は下2桁が四捨五入され、100円単位になる
SELECT expenditure,
ROUND (expenditure, -2) AS 百円単位の出金額
FROM Expenses
###指定桁で切り捨て：TRUNC
0を指定すると小数点以下切り捨てて、整数にできる
その他詳細は四捨五入と同じなので省略
TRUNC(切り捨てを行う数値, 有効桁数)
###べき乗を計算する：POWER
POWER(べき乗する数値, 何乗するか)
//ex
//expenditureを3乗する
POWER(expenditure, 3)
##その他
###副問い合わせを使って比較：ANY, ALL
比較演算子と組み合わせて、
ANY：値の中でいずれか1つでも真であれば真を返す
ALL：値の中ですべて真であれば真を返す
式 比較演算子 ANY/ALL (値1, 値2, ...)
//ex
//副問い合わせを使い、5月のすべての出費（5月の一番高い買い物）よりも高い6月の出費を出力する
SELECT *
FROM Expenses
WHERE date >= '2021-06-01'
AND date <= '2021-06-30'
AND expenditure > ALL(
SELECT expenditure FROM Expenses
WHERE date >= '2021-05-01'
AND date <= '2021-05-31')
###NULLの置き換えができる関数1：COALESCE
コアレスと読みます
行っている内容としては、引数を2つ以上取り、その中で最初にNULLではない値を取得するというものです
少し考え方を変えて、NULLがあったら次の引数を出力をするという書き方ができます(ex2参照)
//ex1
//下記コマンドを入力すると、「hello」という文字列が取得できます
COALESCE(NULL, NULL, NULL, 'hello', NULL)
//ex2
//今回はmemo列を指定し、もしmemoの内容がNULLであったら、NULLを出力する代わりに'NULLだよ'という文字列を出力できます
SELECT date, entry, COALESCE(memo, 'NULLだよ') AS memo
FROM 家計簿
###NULLの置き換えができる関数2：IFNULL
こちらの関数はシンプルで、1つ目に指定した引数がNULLであった場合、2つ目の引数が返されます
COALESCEと比べて使い関数の方を使えば良いと思います
//ex
//COALESCEのex2とまったく同じことをやっています
SELECT date, entry, IFNULL(memo, 'NULLだよ') AS memo
FROM 家計簿
###現在の日付や時間を得る：CURRENT_DATE, CURRENT_TIME
CURRENT_DATE：現在日付（YYYY-MM-DD）
CURRENT_TIME：現在の時刻（HH:MM::SS）
//ex
INSERT INTO Expenses
VALUES(CURRENT_DATE, 'food', 'donuts', 0, 260)
###データ型の変換：CAST
CAST(変換する値 AS 変換する型)
//ex
CAST(expenditure AS VARCHAR(20))
#おまけ
##実行順序
SQLは上から書いた順番に実行される訳ではなく、以下の順番で処理されます
- FROM
- WHERE
- GROUP BY
- HAVING
- SELECT
- ORDER BY
##扱っていないコマンドを軽く紹介
- OFFSET：先頭から指定行数除外
- FETCH：除外後何行出力するか
- EXIST：相関副問い合わせ