スッキリわかる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：相関副問い合わせ