SQL文まとめ(MySQL)

参考

集中演習 SQL入門 Google BigQueryではじめるビジネスデータ分析

テーブルイメージ

sports

id	num	name	kind	referee
1	5	"basketball"	"ball"	true
2	9	"baseball"	"ball"	true
3	1	"tennis"	"racket"	true
4	11	"soccer"	"ball"	true

players

id	sports_id	name
1	2	"Otan"
2	1	"Jordan"
3	1	"Hachi"

teams

id	sports_id	name
1	4	"Barce"

コメント

-- コメント
# コメント
/*
コメント
*/

基本

SELECT * From sports # 全てのフィールドを取得

SELECT name, num From sports # 特定のフィールドを取得

SELECT * EXCEPT(name, num) From sports # 特定のフィールドを除いて取得

並び替え ORDER BY DESC

SELECT * From sports 
ORDER BY id # idの昇順

SELECT * From sports 
ORDER BY id DESC # idの降順

SELECT * From sports 
ORDER BY id, name DESC # 複数指定

SELECT name, num From sports
ORDER BY 1,2 # 得られるテーブルの列番号で指定

四則演算 + - * /

SELECT (num+1)*2 From sports
SELECT num * team_num From sports

別名 AS

SELECT num * team_num AS play_num From sports # フィールドに別名をつける
SELECT num * team_num  play_num From sports # ASは省略可能
SELECT name, num people From sports # ASは省略可能

一部を取得 LIMIT

SELECT * From sports LIMIT 30
SELECT * From sports LIMIT 5 OFFSET 70 # 70~75番目を取得

条件式

= : 等しい
<>,!= : 等しくない
>,>= : 不等号
AND
OR

SELECT * From sports
where num = 5

SELECT * From sports
where num in (5,9,11)

SELECT * From sports
where num not in (5,9,11)

LIKE

% : 任意の文字
_ : 任意の一文字
\ : エスケープ

SELECT * From sports
where name LIKE '%ボール'

SELECT * From sports
where name NOT LIKE '%ボール'

SELECT * From sports # 4以上5以下
where num BETWEEN 4 and 5

null,true

IFNULL : nullの時、代わりに入れる値を指定できる
COALESCE : IFNULLとほぼ同じだが、引数にカラム名を指定できる

SELECT * From sports
where referee IS TRUE # falseもある

SELECT * From sports
where referee IS NULL

SELECT * From sports
where referee IS NOT TRUE # falseとnull

SELECT name, IFNULL(kind,"特殊") From sports # kindがnullの時,"特殊"を入れる
where referee IS NULL

SELECT name, COALESCE(kind,name) From sports # kindがnullの時,nameを入れる
where referee IS NULL

テーブル無しでも実行できる

SELECT CURRENT_DATE AS today # CURRENT_DATEは、日付を取得する。それにtodayという名前を付ける

グループ化

COUNT(*) : 数を数える
COUNT(フィールド名) : nullでない個数を数える
COUNT(DISTINCT フィールド名) : フィールド名が固有な値の個数を数える
SUM : 合計
AVG : 平均
MAX : 最大値
MIN : 最小値

GROUP BYをつけないと、テーブル全体を集約

標準偏差とかを求められる関数もある

SELECT kind, AVG(num) From sports
GROUP BY kind

SELECT kind, COUNT(num) From sports # kindごとに、このkindにしか無いnumの値を持っていたら、その個数を数える
GROUP BY kind

SELECT COUNT(num) From sports # テーブル全体のレコード数

HAVING

SELECT kind, AVG(num) as average From sports
GROUP BY kind
HAVING average >= 5

SELECT kind, AVG(num) as avearage From sports
where name like '%ボール'
GROUP BY kind
HAVING average >= 5
ORDER BY 2
LIMIT 10

条件式 IF,CASE

SELECT if(birth_year > 1024,true,false ) as is_millennium, COUNT(*) From sports GROUP BY is_millennium# エクセルのif関数と同じ使い方

SELECT if(num in (1,2,3,4,5), "5人以下", "5人より多い") as more_5,COUNT(*)   From sports GROUP BY  more_5

SELECT
CASE num
WHEN 1 THEN "1対1"
WHEN 2 THEN "2対2"
WHEN 3 THEN "3対3"
ELSE "4人以上" as team_num, count(*)
From sports GROUP BY team_num

SELECT
CASE
WHEN num=1 THEN "1対1"
WHEN num=2 THEN "2対2"
WHEN num=3 THEN "3対3"
ELSE "4人以上" as team_num, count(*)
From sports GROUP BY team_num

テーブルの結合

横に繋ぐ:JOIN
縦に繋ぐ:UNION
INNER JOIN : 両方にデータがあるものだけ取得
LEFT OUTER JOIN : 先に指定したテーブルにデータがあるもの全てを取得。後に指定したテーブルにデータがない部分はnullになる
RIGHT OUTER JOIN
FULL OUTER JOIN : 両方のテーブルの全レコードを残す
CROSS JOIN : 総当たりで結合。レコード10のテーブルとレコード15のテーブルからレコード150のテーブルができる。特定のレコードと、それ以外の全てのレコードを比べたいみたいな時とか
同じテーブル同士でも結合できる。前年の結果と今年の結果を横に並べたいとか

SELECT sp.id, pl.name FROM sports AS sp
INNER JOIN players AS pl
ON sp.id = pl.sports_id

SELECT sp.id, pl.name FROM sports AS sp
INNER JOIN players AS pl
USING (id) # sp.id = pl.id という意味になる

SELECT sp.id, pl.name FROM sports AS sp
LEFT OUTER JOIN players AS pl
ON sp.id = pl.sports_id

SELECT sp.id, pl.name FROM sports AS sp
RIGHT OUTER JOIN players AS pl
ON sp.id = pl.sports_id

SELECT sp.id, pl.name FROM sports AS sp
FULL OUTER JOIN players AS pl
ON sp.id = pl.sports_id

# 3つのテーブル
SELECT sp.id, pl.name, te.name FROM sports AS sp
JOIN players AS pl
ON sp.id = pl.sports_id
JOIN teams AS te
ON sp.id = te.sports_id

集合演算

• ほとんど同じテーブルが2つある時とかに使う
  UNION : 和集合
  INTERSECT : 積集合
  EXCEPT : 差集合

SELECT id,name FROM players
UNION DISTINCT # 重複を許さない 今回の場合、同じスポーツで、選手の名前とチーム名が同じものがあった場合、片方しか表示されない
SELECT id,name FROM teams

SELECT sports_id,name FROM players
UNION ALL # 重複を許す
SELECT sports_id,name FROM teams

SELECT sports_id,name FROM players
INTERSECT DISTINCT
SELECT sports_id,name FROM teams

SELECT sports_id,name FROM players
EXECPT DISTINCT
SELECT sports_id,name FROM teams

仮想テーブル

• このSQLでだけ使えるテーブルを作れるイメージ
• 仮想テーブルに対してSQLを実行すると、仮想テーブルを作成するSQLも再実行される

# sports_nameという名の仮想テーブル作成
WITH sports_names AS (
SELECT name FROM sporsts;
)

サブクエリ

SELECT * FROM sports
WHERE num > (SELECT AVG(num) FROM sports)

SELECT * FROM players
WHERE sports_id in (SELECT id FROM sports where num >= 5)

RECORD型

• カラムの型の1つ
• フィールドにテーブルを入れられるイメージ
• UNNESTで普通のテーブルのように操作できる

関数

FLOOR(x) # xよりも小さい最大の整数 3.4=>3
CEIL(x) # x以上の最小の整数 3.4=>4
ROUND(x,3) # 小数第4位を四捨五入し、第3位に丸める。 1.2345=>1.235
ROUND(x,-3) # 整数3桁目を四捨五入し、4桁に丸める。 1234.5=> 1000
ABS(x) # 絶対値
CAST(x AS INT64) # 型変換
CAST(x AS FLOAT64)
CAST(x AS STRING)
CAST(x AS BOOL)
CAST(x AS DATE)
CONCAT("a","b") #=>"ab" 文字列の結合
SUBSTRING("abcdefg",3) #=>"cdefg" 3文字目以降を抜き出す
SUBSTRING("abcdefg",3,4) #=>"cdef" 3文字目から4文字を抜き出す
SUBSTRING("abcdefg",-3,2) #=>"ef" 後ろから3文字目から2文字を抜き出す
LEFT("abcdefg",3) #=>"abc"
RIGHT("abcdefg",3) #=>"efg"
INSTR("abcdefg","cd") #=>3 出現開始位置を返す
INSTR("abcdabcd","cd",4) #=>7 4文字目以降から探す
INSTR("abcdabcd","cd",4, 1) #=>7 4文字目以降、1番目に出てくるものを探す
STRPOS("abcdabcd","cd") # INSTRの第1,2引数のみの関数
REPLACE("abcdabcd","ab","置き換え") #=> "置き換えcd置き換えcd"
LENGTH("abcdefg") #=>7
(LENGTH(x)-LENGTH(REPLACE(x,"ab","")))/LENGTH("ab") # "ab"が何回出てくるか調べられる

正規表現

REGEXP_CONTAINS("abcd", r"^a") # 正規表現の文字列が含まれるかをbool型で返す
REGEXP_EXTRACT("abcd", r"^a") # 正規表現に当てはまる文字列を返す
REGEXP_EXTRACT("abcd", r"^a", 2, 3) # 2文字目以降で3番目に出てくる文字列を返す
REGEXP_REPLACE("abcd", r"^a", "置き換え") # 正規表現に当てはまる文字列を置き換える

日付

DATE(2025,12,25,12,25,55)
DATE("2025-12-25")
DATE(DATETIME("2025-12-25 12:25:55"))
CURRENT_DATE # DATE型 2025-12-25
CURRENT_DATE("Asia/Tokyo")
CURRENT_DATE ("+9")

# YEAR QUARTER MONTH WEEK DAYが使える
DATE_ADD(a,INTERVAL 1 MONTH) # aに追加
DATE_DIFF(a,b,YEAR) # a-bを年で返す
DATE_TRUNC(a,MONTH) # 2025-12-01 00:00:00のように、月単位で丸まる。月毎に集計したい時とかに便利

# YEAR QUARTER MONTH WEEK DAYOFYEAR DAY DAYOFWEEKが使える
EXTRACT(YEAR FROM a) # int型の要素を取り出す。特定の曜日だけとかで集計したい時に便利 
FORMAT_DATE("%A",a)

a+1 # 日を足し引きできる

%A	Sunday
%a	Sun
%B	December
%b	Dec
%c	Sun Dec 25 06:25:55 2025
%d	09	日付
%e	9	日付
%F	2025-12-25
%H	06	時刻
%M	25	分
%m	12	月
%S	55	秒
%T	06:25:55
%X	06:25:55	%Tと同じ
%Y	2025

DATETIME(2025,12,25,12,25,55)
DATETIME("2025-12-25 12:25:55")
DATETIME(DATE("2025-12-25"))
CURRENT_DATIME # DATETIME型 2025-12-25T12:25:55.123456
CURRENT_DATETIME("Asia/Tokyo")
CURRENT_DATETIME("+9")

DATETIME_ADD(a,INTERVAL 1 MONTH) # YEAR QUARTER MONTH WEEK DAY HOUR MINUTE SECOND MILLISECOND MICROSECONDが使える
DATETIME_DIFF(a,b,YEAR) # a-bを年で返す YEAR QUARTER MONTH WEEK DAY HOUR MINUTE SECOND MILLISECOND MICROSECONDが使える
DATETIME_TRUNC(a,MONTH) YEAR QUARTER MONTH WEEK DAY HOUR MINUTE SECOND MILLISECOND MICROSECONDが使える

CURRENT_TIME # TIME型 12:25:55.123456
CURRENT_TIMESTAMP # TIMESTAMP型 2025-12-25T12:25:55.123456 UTC

ウィンドウ関数

RANK	順位
ROW_NUMBER	行番号
NTILE	均等な数に分割
FIRST_VALUE	最初の値
LAST_VALUE	最後の値
NTH_VALUE	任意の順番の値
LEAD	直後の値
LAG	直前の値
PRRCENTILE_COUNT	五数要約の指数
SUM	合計
AVG	平均

ウィンドウフレーム
範囲を絞れるイメージ
ORDER BYで並び替えられたものに対して適用される

UNBOUNDED PRECEGIND	バーティションの上限
3 PRECEDING	現在の行から3行上
CURRENTROW	現在の行
3 FOLLOWING	現在の行から3行下
UNBOUNDED FOLLOWING	パーティションの下限

SELECT name, num, SUM(num) OVER (
PARTITION BY kind # 同じkind内でのSUMを取得できる
ORDER BY num# パーティション内でどのように並べ替えるかを指定できる
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW # WINDOWフレームという。 2つ前の行~現在の行のSUMを出す。 
# ROWではなくRANGEを用いると、行ではなく値が基準になる
# (2つ前の値~現在の値のSUM。numが5のレコードに対して、numが3~5の合計求める。)
)
FROM sports

RANK

• 順位

SELECT name, num, 
RANK() OVER(
PARTITION BY kind # kindごとのnumランキング
ORDER BY num
)
FROM sports

RANK_NUMBER

• 連番をつける

SELECT name, num, ROW_NUMBER() OVER(
PARTITION BY kind # kindごとに連番を振る
ORDER BY num
)
FROM sports

NTILE

• ABC分析のようなことができる関数
• フィールドの数が均等になるように等分し、それぞれのグループに整数を割り振る

SELECT name, num, 
NTILE(10) OVER ( # フィールドを10個に分けて1~10の番号を振る。
ORDER BY num
)
FROM sports

FIRST_VALUE

• 最初の値を取得
• ウィンドウフレームを使える

SELECT name FIRST_VALUE(name) OVER ( # スポーツごとの、idが一番小さい選手名を取得する
PARTITION BY sports_id
ORDER BY id
)
FROM 

NTH_VALUE

• n番目の値を取得
• ウィンドウフレームを使える

SELECT name NTH_VALUE(name, 2) OVER ( # スポーツごとの、idが2番目に小さい選手名を取得する
PARTITION BY sports_id
ORDER BY id
)

LAST_VALUE

• 最後の値を取得
• ウィンドウフレームを使える

SELECT name LAST_VALUE(name) OVER ( # スポーツごとの、idが一番大きい選手名を取得する
PARTITION BY sports_id
ORDER BY id
)

LEAD

• 1つ下のレコードの情報を取得できる

SELECT name LEAD(name) OVER ( # スポーツごとの、1下のidの選手名を取得する
PARTITION BY sports_id
ORDER BY id
)

LAG

• 1つ上のレコードの情報を取得できる

SELECT name LAG(name) OVER ( # スポーツごとの、1個上のidの選手名を取得する
PARTITION BY sports_id
ORDER BY id
)

PERCENTILE_COUNT

• 五数要約
  • 最小値 : 0
  • 第1四分位数 : 0.25
  • 中央値 : 0.5
  • 第3四分位数 : 0.75
  • 最大値 : 1

SELECT name PERCENTILE_COUNT(num, 0.5) OVER ( # 種類ごとの、人数の中央値
PARTITION BY kind
ORDER BY num
)

SUM

• 合計値を出す
• ウィンドウフレームを使える

SELECT name, num, SUM(num) OVER (  # 同じkind内でのSUMを取得できる
PARTITION BY kind
)
FROM sports

AVG

• 平均値を出す
• ウィンドウフレームを使える

SELECT name, num, AVG(num) OVER (  # 同じkind内でのAVGを取得できる
PARTITION BY kind
)
FROM sports