SQL 備忘録

# はじめに
SQL の学習をしたので、備忘録を残す。

# 内容
## select
```
$ SELECT a FROM X

$ SELECT a, b, c FROM X

$ SELECT * FROM X

$ SELECT a, b, c FROM X WHERE c > 1000
```
## insert
```
$ INSERT INTO X VALUES ('2022-01-01', '居住費', '3月の家賃', 0, 85000)

$ INSERT INTO X (a, b, c) VALUES ('2022-01-01', '居住費', '3月の家賃')
```
## update
```
$ UPDATE X SET a = 1000 WHERE b = '2022-01-01'
```
## delete
```
$ DELETE FROM X WHERE a = '2022-01-01'
```
## as
```
$ SELECT a AS A, b AS B, c AS C FROM X
```
## where
```
$ DELETE FROM X WHERE a > 0

$ SELECT * FROM X WHERE IS NULL

// %: 0文字以上
// _: 1文字
$ SELECT * FROM X WHERE a LIKE '%100_'

$ SELECT * FROM X WHERE a BETWEEN 100 AND 1000

$ SELECT * FROM X WHERE a IN ('食費', '交際費')

$ SELECT * FROM X WHERE a NOT IN ('食費', '交際費')

$ SELECT * FROM X WHERE a ANY ('A', 'B', 'C')

$ SELECT * FROM X WHERE a ALL ('A', 'B', 'C')

$ UPDATE X SET a = 1000 WHERE b = 'B' AND c = 'C'

$ SELECT * FROM X WHERE a = 'A' OR b = 'B'
```
## select 修飾
```
// 重複を削除
$ SELECT DISTINCT * FROM X

$ SELECT * FROM X ORDER BY a ASC/DESC

// OFFSET: 何番目, FETCH NEXT: 何個
$ SELECT * FROM X ORDER BY a OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY

// 上と同じ結果
$ SELECT * FROM X ORDER BY a LIMIT 3

// 和集合
$ SELECT * FROM X UNION SELECT * FROM Y 

// 差集合
$ SELECT * FROM X EXCEPT SELECT * FROM Y

// 積集合
$ SELECT * FROM X INSERT SELECT * FROM Y
```
## 式・関数
```
// a1,a2,その他の場合返却する値
$ SELECT * 
 CASE a WHEN 'a1' THEN 'A'
        WHEN 'a2' THEN 'B'
        ELSE 'C'
 END AS A
FROM X 

$ SELECT * 
 CASE WHEN a < 1000 THEN 'A'
      WHEN a < 2000 THEN 'B'
      ELSE 'C'
 END AS A
FROM X 
WHERE a > 0

// 長さ
$ SELECT a LENGTH (a) AS a の長さ FROM X

$ SELECT a LENGTH (a) AS a の長さ FROM X WHRE LENGTH(a) <= 10

// 空行削除
$ SELECT a TRIM(a) AS 空白削除した a FROM X

// 置換
$ UPDATE X SET a = REPLACE(a, ',', ' ') 

// 抽出(1~3文字目に「費」があるもの)
$ SELECT * FROM X WHERE SUBSTRING(a, 1, 3) LIKE '%費%'

// 繋げる
$ SELECT CONCAT(a, ':' || b) FROM X

// 四捨五入
$ SELECT a, ROUND(a, -2) AS 百単位の a from X

// 現在日時
$ INSERT INTO X VALUES (CURRENT_DATE, '食費', 'ドーナッツ購入', 0, 260)

// 一番最初に見つかった null 出ないものを返す
$ SELECT COALESCE('A', 'B', 'C'); // 'A'
$ SELECT COALESCE(NULL, 'B', NULL); // 'B'

$ SELECT COALESCE(a, 'NULL です') AS a, b, c FROM X
```
## 集計
```
$ SELECT SUM(a) AS a の合計 FROM X

$ SELECT SUM(a) AS a の合計, AVG(b) AS b の平均, MAX(c) AS c の最大, MIN(d) AS d の最小 FROM X

$ SELECT COUNT(a) AS a の行数 FROM X WHERE b = ‘B’

// null を 0 とする
$ SELECT AVG(COALESCE(a, 0)) as a の平均 FROM X

$ SELECT a, SUM(b) AS b の合計 FROM X WHERE SUM(b) > 0 GROUP BY a

// 集計結果に対する絞り込み条件
$ SELECT a FROM X GROUP BY b HAVING SUM(a) > 0
```
## 副問い合わせ
```
// X の b の最大値の箇所の a,b
$ SELECT a, b FROM X WHERE b = (SELECT MAX(b) FROM X)

// d = 'D' の a に Y の b > 0 かつ c = 'C' の平均値をセット
$ UPDATE X SET a = (SELECT AVG(b) FROM Y WHERE b > 0 AND c = ‘C’) WHERE d = ‘D’ 

// Y の d = 'D' の a,b,c, X の e = 'E' の d
$ SELECT a, b, c, (SELECT d FROM X WHERE e = ‘E‘) AS - FROM Y WHERE d = ‘D‘

// Y の b の重複を除いた値を含む X の a 
$ SELECT * FROM X WHERE a IN (SELECT DISTINCT b FROM Y)

// X の a と Y の a = 'A' の b のいずれかに一致する b の全て
$ SELECT * FROM X WHERE a = ‘A‘ AND b < ANY (SELECT b FROM Y WHERE a = ‘A‘)

// Y の a が null でない a を含む X の a の全て
$ SELECT * FROM X WHERE a IN (SELECT a FROM Y WHERE a IS NOT NULL)

// 上と同じだが、null の場合は「NULL です」
$ SELECT * FROM X WHERE a IN (SELECT COALESCE(a, ‘NULL です’) FROM Y)

// X の b,c,a と Y の b >= 'B' かつ b <= 'BB' の b,c,a を SUB として、SUB.a を取得
$ SELECT SUM(SUB.a) AS a の合計 FROM (SELECT b, c, a FROM X UNION SELECT b, c, a FROM Y WHERE b >= ‘B‘ AND b <= ‘BB‘) AS SUB

// X の a,b,c,d に Y の e > 0 の a でまとめた a,e の合計,e の平均,0を挿入
$ INSERT INTO X (a, b, c, d) SELECT a, SUM(e), AVG(e), 0 FROM Y WHERE e > 0 GROUP BY a
```
## 結合
```
$ SELECT * FROM X LEFT JOIN Y ON X.a = Y.b

$ SELECT * FROM X RIGHT JOIN Y ON X.a = Y.b

$ SELECT * FROM X FULL JOIN Y ON X.a = Y.b

$ SELECT A.a, B.b FROM X AS A JOIN Y AS B ON A.c = B.d

$ SELECT a, Y.b, Z.c FROM X JOIN Y ON X.d = Y.e JOIN Z ON Y.f = Z.g

$ SELECT a, B.b, C.c FROM X JOIN (SELECT * FROM Y WHERE d = 1) AS B ON X.e = B.f
```

# 参考
[スッキリわかるSQL入門 第3版 ドリル256問付き！　（スッキリわかるシリーズ）](https://books.rakuten.co.jp/rk/1be007be9e57339b89fbcfeb5479de5c/?l-id=item-c-kobo-title)