Open13
SQL個人的チートシート
メタグロス中島すべてMySQLの構文
データ定義言語(DDL)
データベースの操作
-- データベースを作成
CREATE DATABASE データベース名;
-- データベースを修正
ALTER DATABASE データベース名 変更したい内容;
-- 例
ALTER DATABASE test COLLATE 'utf8_general_ci';
-- データベースを削除
DROP DATABASE データベース名;
-- [補足]
-- データベース一覧を表示
SHOW DATABASES;
-- 作成したデータベースを利用
USE テーブル名;
テーブルの操作
-- テーブル作成
CREATE TABLE テーブル名 (
カラム名 型 制約(任意),
カラム名 型 制約(任意),
...,
制約(任意),
);
-- 例
CREATE TABLE tests (
code INTEGER,
name VARCHAR(20) NOT NULL,
PRYMARY KEY(code)
);
-- カラムを追加
ALTER TABLE テーブル名 ADD カラム名 型;
-- カラムを修正
ALTER TABLE テーブル名 MODIFY カラム名 型;
-- カラムを削除
ALTER TABLE テーブル名 DROP カラム名;
-- テーブル削除
DROP TABLE テーブル名;
-- 補足
-- テーブル一覧を表示
SHOW TABLES;
-- テーブルのカラムを表示
SHOW COLUMNS FROM テーブル名;
データ操作言語(DML)
SELECT
SELECT カラム名, カラム名, .. FROM テーブル名
WHERE 条件
GROUP BY カラム名, カラム名, ..
HAVING グループの値に対する条件
ORDER BY カラム名, カラム名, ..
-- 例
SELECT gender, AVG(weight) FROM students
WHERE height > 170
GROUP BY gender = 0
HAVING AVG(weight) >= 50;
ORDER BY height DESC;
実行順はおおざっぱに以下
WHERE → GROUP BY → HAVING → SELECT → ORDER BY
SELECT文のあとにORDER BYが実行されるため、SELECT文でつけた別名(AS ~)がORDER BYでは利用できる
GROUP BY および HAVING の注意事項
GROUP BYのSELECT句には以下しか指定できず、HAVING句には以下しか記載できない。
- 定数
- 集約関数
- GROUP BYで指定した列名(集約キー)
-- OK (genderは集約キーであり、AVG(weight)は集約関数、50は定数であるため)
SELECT gender, AVG(weight) FROM students
GROUP BY gender = 0
HAVING AVG(weight) >= 50;
-- NG (nameは集約キーではないため)
SELECT name, AVG(weight) FROM students
GROUP BY gender = 0
HAVING AVG(weight) >= 50;
-- NG (heightは集約キーではないため)
SELECT gender, AVG(weight) FROM students
GROUP BY gender = 0
HAVING height > 170;
INSERT
INSERT INTO テーブル名 ( カラム名, カラム名, .. )
VALUES ( 値1, 値2, .. );
- NULLを明示的に割り当てる、もしくはデフォルト値がない場合に省略した場合はNULLが挿入される。
- デフォルト値がある場合にDEFAULTを明示的に割り当てる、もしくは省略した場合はデフォルト値が挿入される
- すべての列に値を挿入する場合はカラム名を省略可能
INSERT SELECT文
他のテーブルから値を選択することも可能。
INSERT INTO テーブル名 ( カラム名, カラム名, .. )
SELECT カラム名, カラム名, .. FROM コピー元テーブル名
もちろんGROUP BYなども利用可能。
UPDATE
-- WHERE句を省略してしまうと全て更新されてしまうため注意
UPDATE テーブル名 SET カラム名 = 値 WHERE 条件式;
DELETE
-- WHERE句を省略してしまうと全て削除されてしまうため注意
DELETE FROM テーブル名 SET カラム名 = 値 WHERE 条件式;
-- 全削除の場合は効率がよいTRUNCATEというものもある
-- DDLの場合もあるため利用しているDBの仕様を確認すること
TRUNCATE テーブル名
トランザクション
トランザクションの開始
-- PostgreSQL, SQL Server
BEGIN TRANSACTION
-- MySQL
BEGIN
-- or
START TRANSACTION
トランザクションの確定
COMMIT
トランザクションの取り消し
ROLLBACK
ACID特性
トランザクションは以下が守られていなければならない。
- 原子性
トランザクションが終了するとき、すべて実行されたかすべて実行されていない状態であること。
コミットされたかロールバックされたかのどちらか。 - 一貫性
トランザクションは制約を満たしていること。 - 独立性
トランザクションが別のトランザクションの影響を受けないこと。 - 永続性
ログなどを出力して、データの状態が保存されていることが保証されていること。
システムトラブルなどでデータが消えたときに復旧できる。
メタグロス中島ビュー
ビューはクエリを保存している。テーブルを作るわけではないため記憶容量を節約できたり使わいましが効いたりする。
ビューの作成
CREATE VIEW ビュー名 (ビューの列名, ビューの列名)
AS <SELECT文>
-- 呼び出し(テーブルと同じように使える)
SELECT * FROM ビュー名
-- 例
CREATE VIEW female_students (id, name, height)
AS SELECT id, name, height FROM students WHERE gender = 2;
SELECT * FROM female_students WHERE height > 160;
ビューを扱う上での注意点
- 多段ビュー(ビュー内でビューを呼び出すこと)は可能であるが、パフォーマンスが落ちるので注意。
- ORDER BY 句ではビューは使えない。
メタグロス中島サブクエリ
サブクエリは使い捨てのVIEW。
サブクエリの具体例
-- 上記のビューと同じ結果が得られる
SELECT id, name, height FROM (
SELECT id, name, height FROM students WHERE gender = 2
) AS female_students WHERE height > 160;
スカラ･サブクエリ
平均身長より高い生徒を選択する
-- WHEREには集約関数が使えない😥(HAVING句では使える)
SELECT * FROM students WHERE height > AVG(height);
-- ここで単一の値を返すスカラ･サブクエリを用いる！
SELECT * FROM students WHERE height > (
SELECT AVG(height) FROM students
);
他にもSELECT句での利用例
-- GROUP BYを用いず、SELECT句で集約関数と非集約列が混合しているとエラー(MySQL以外)😥
SELECT id, name, SUM(height) FROM students;
-- スカラ･サブクエリならOK!(スカラ・サブクエリは1つの値を返すため)
SELECT id, name, (SELECT SUM(height) FROM students) FROM students;
相関サブクエリ
男性の平均身長より高い男性と女性の平均身長より高い女性を一度に取得する。
SELECT id, name, height FROM students AS s1 WHERE height > (
SELECT AVG(height) FROM students AS s2
WHERE s1.gender = s2.gender
);
TypeScriptに置き換えるとこんなことをしているはず。
type Student = {
id: number
name: string
height: number
gender: 0 | 1 | 2,
}
const query = () => {
const s1: Student[] = students
// 性別による平均身長を計算するサブクエリ
const subQuery = (s1Record: Student) => {
const s2: Student[] = students
const where = s2.filter((s2Record) => s1Record.gender === s2Record.gender)
// averageは平均を返す関数
return average(where.map(record => record.height))
}
return s1.filter((s1Record: Student) => {
// サブクエリに各行を渡して計算している理解をすると納得できた
return s1Record.height > subQuery(s1Record)
}).map((s1Record: Student) => {
return [s1Record.id, s1Record.name, s1Record.height]
})
}
関数
集約関数
主に使われる集約関数。
これらは実行前にNULLを除外して計算する。
COUNT(<列名>) -- 対象の列のNULLを除いたレコード数を求める
COUNT(*) -- テーブルすべてのレコード数を求める
SUM(<列名>) -- 対象の列の合計を求める
AVG(<列名>) -- 対象の列の平均を求める
MAX(<列名>) -- 対象の列の最大値を求める
MIN(<列名>) -- 対象の列の最小値を求める
-- [補足] DISTINCT キーワード
COUNT(DISTINCT <列名>) -- 重複を除いてレコード数を求める
AVG(DISTINCT <列名>) -- 重複を除いて平均値を求める
算術関数
四則演算(+, -, *, / )以外を記載。
NULLが与えられた場合はNULLを返す。
あくまでPostgreSQLの仕様。
ABS(数値) -- 絶対値を求める(AbsoluteからABS)
MOD(被除数, 除数) -- 余剰(mod)を求める
ROUND(数値) -- 四捨五入
ROUND(数値, 桁数) -- 指定した桁数で四捨五入
CEIL(数値) -- 引数より小さくない最小の整数
FLOOR(数値) --引数より大きくない最大の整数
TRUNC(数値) -- 切り捨て(単純に小数点以下を切り捨て)
TRUNC(数値, 桁数) -- 桁数で切り捨て
-- 例
ROUND(42.5) -- 43
CEIL(42.5) -- 43
FLOOR(42.5) -- 42
TRUNC(42.5) -- 42
ROUND(-42.5) -- -43
CEIL(-42.5) -- -42 (引数より小さくない最小の整数。 -42.5(引数) < -42 であるため 42)
FLOOR(-42.5) -- -43 (引数より大きくない最小の整数 -43 < -42.5(引数) であるため 43)
TRUNC(-42.5) -- -42 (単純に小数点以下を切り捨て)
文字列関数
|| -- 文字列を結合する
-- 例
'メタグロス' || '中島';
-- MySQLではCONCAT
CONCAT('メタグロス' , '中島');
-- SQL Serverでは+演算子
'メタグロス' + '中島';
LENGTH(<文字列>) -- 文字列の長さを返却する
-- SQL ServerではLEN
LEN(<文字列>)
-- MySQLではバイト数を数えている
SELECT LENGTH('中島'); -- 4
-- MySQLで文字列の長さが欲しい場合はCHAR_LENGTHを用いる
CHAR_LENGTH('中島'); -- 2
LOWER(<文字列>) -- 小文字化
UPPER(<文字列>) -- 大文字化
REPLACE(<文字列>, <置換対象の文字列>, <置換後の文字列>) -- 文字列の置換
SUBSTRING(<文字列> FROM <切り出し開始位置> FOR <切り出す文字数> ) -- 文字列の切り出し
-- 例
SELECT SUBSTRING('メタグロス中島' FROM 4 FOR 3); -- ロス中
-- SQL Serverでは簡略されている
SELECT SUBSTRING('メタグロス中島', 4, 3);
-- Oracle/DB2
SELECT SUBSTR('メタグロス中島', 4, 3);
日付関数
OracleやSQL Serverの書き方は省略している。
主にMySQLとPostgreSQLを記載。
CURRENT_DATE -- 現在の日付 YYYY-MM-DDが返る。
-- 例
SELECT CURRENT_DATE; -- 2024-08-18
CURRENT_TIME -- 現在の時刻が返る。
-- 例
SELECT CURRENT_TIME; -- 00:43:54.385856+09
CURRENT_TIMESTAMP -- 現在のタイムスタンプが返る。
-- 例
SELECT CURRENT_TIMESTAMP; -- 2024-08-18 00:45:01.079752+09
EXTRACT -- 日付要素の取り出し
-- 例
SELECT EXTRACT(YEAR FROM CURRENT_TIMESTAMP); -- 2024
変換関数
CAST(変換前の値 AS 変換するデータ型) -- 型変換
-- 例
SELECT CAST('0001' AS INTEGER);
-- MySQL
SELECT CAST('0001' AS SIGNED INTEGER);
COALESCE(データ, データ, データ) -- NULLを値へ変換(コアレスと読む)
-- 例
SELECT COALESCE(NULL, NULL, '0001'); -- 0001
述語
述語は関数の戻り値が真理値になること
→ 戻りがTRUE / FALSE / UNKNOWN
実は =, <, >, <>などの比較演算子は比較述語。
LIKE -- 部分一致検索
-- %は任意の文字列を表し、_は任意の1文字を表す
-- 前方一致検索
SELECT name FROM students WHERE name = '中%'
-- 中島、中山、中大兄皇子
-- 後方一致検索
SELECT name FROM students WHERE name = '%中'
-- 田中、山中
-- 中間一致検索
SELECT name FROM students WHERE name = '%中%'
-- 中島、中山、中大兄皇子、田中、山中、川中島
-- パーセントではなくアンダーバーを用いるとアンダーバーの数の文字が必要になる
SELECT name FROM students WHERE name = '中____'
-- 中大兄皇子
SELECT * FROM students WHERE name = '_中_'
-- 川中島
BETWEEN -- 範囲指定
-- 値の範囲
SELECT * FROM students WHERE height BETWEEN 150 AND 155;
-- 日付も可能
SELECT * FROM students WHERE birthday BETWEEN '2000-11-10' AND '2000-12-10';
IS NULL -- NULLの判定
IS NOT NULL -- 非NULL
IN -- Array.prototype.includes()と同じ
SELECT * FROM students WHERE birthday IN ('2000-11-10', '2000-12-10');
-- これらはサブクエリを条件に用いることができる
-- 他の(上記の)述語はスカラ･サブクエリでないとならない
SELECT * FROM students WHERE birthday IN (
SELECT holiday FROM holidaies
);
NOT IN -- !Array.prototype.includes()と同じ
SELECT * FROM students WHERE birthday NOT IN ('2000-11-10', '2000-12-10');
-- NOT INのサブクエリの結果が空の場合、NOT INは常にTRUEとして評価される
SELECT * FROM students WHERE student_id NOT IN (
SELECT student_id FROM expelled_students WHERE 1 = 2
);
-- ⚠️NOT INの中にNULLが含まれると件数が0件になってしまう。
SELECT * FROM students WHERE birthday NOT IN ('2000-11-10', '2000-12-10', NULL);
-- 0件
CASE式
単純CASE式と検索CASE式がある
- 単純CASE式：普通のJavaScriptのswitch文と同じ
- 検索CASE式：JavaScriptでswitch文でswitch(true)としてcaseに判定式を書くのと同じ
-- CASE式
SELECT name,
CASE WHEN class = 1 THEN '松組'
WHEN class = 2 THEN '竹組'
WHEN class = 3 THEN '梅組'
ELSE NULL -- 書かなくてもNULLが返るが明示的に書くと良い
END AS class_name -- 書き忘れがち
FROM students;
-- CASE式は行列変換にも使える
SELECT
AVG(CASE WHEN class = 1 THEN height ELSE NULL) AS class_1_height_average,
AVG(CASE WHEN class = 2 THEN height ELSE NULL) AS class_2_height_average,
AVG(CASE WHEN class = 3 THEN height ELSE NULL) AS class_3_height_average
FROM students;
/* Result
class_1_height_average | class_2_height_average | class_3_height_average
---------------------------------------------------------------------------------
150| 152| 153
*/
集合演算 (テーブル自体の足し算引き算)
集合演算をするにあたり注意事項
- 演算対象のレコードの列数は同じであること
- レコード列のデータ型が一致していること
- ORDER BYは最後に1回
-- OK
SELECT id, name FROM Students1
UNION
SELECT id, name FROM Students2 -- 1, 2. 型が一致しており列数も等しい
ORDER BY id; -- 3. ORDER BYは最後に1回
-- NG例
-- 列数が異なる
SELECT id, name FROM Students1
UNION
SELECT id, name, gender FROM Students2 -- ここだけgenderが選択されている
-- 型が異なる
SELECT id, name FROM Students1
UNION
SELECT CAST(id AS VARCHER(10)), name FROM Students2 -- 型違い
UNION -- 和集合(A ∪ B, A or B)
-- 例
-- 重複は削除される
SELECT * FROM Students1
UNION
SELECT * FROM Students2;
-- ALLをつけると重複が残る
SELECT * FROM Students1
UNION ALL
SELECT * FROM Students2;
INTERSECT -- 積集合(A ∩ B, A and B)
-- 例
SELECT * FROM Students1
INTERSECT
SELECT * FROM Students2;
-- ALLをつけると重複が残る
SELECT * FROM Students1
INTERSECT ALL
SELECT * FROM Students2;
EXCEPT -- 差集合（A - B）
-- 例
SELECT * FROM Students1
EXCEPT
SELECT * FROM Students2;
-- Students1からStudents1 ∩ Students2の列が引かれる。
-- ALLをつけると重複が残る
SELECT * FROM Students1
EXCEPT ALL
SELECT * FROM Students2;
結合
内部結合(INNER JOIN)
-- 例：生徒テーブルとクラステーブルを結合して生徒名、クラスID、クラス名を表示する
SELECT S.name, S.class_id, C.class_name -- すべての列を<テーブル名>.<列名>で書くのが望ましい
FROM students AS S
INNER JOIN classes AS C ON S.class_id = C.id;
-- もちろんWHERE句やGROUP BYなども使うことができる
-- 松組の性別による体重平均のうち、50kg以上の性別の性別と平均体重を表示
SELECT S.gender, AVG(weight) AS avg_weight
FROM students AS S
INNER JOIN classes AS C ON S.class_id = C.id
WHERE C.name = '松組'
GROUP BY S.gender
HAVING avg_weight > 50
ORDER BY S.gender;
外部結合(LEFT OUTER JOIN, RIGHT OUTER JOIN)
LEFT OUTER JOINは、左側のテーブル（students）の全ての行を返し、右側のテーブル（classes）に一致する行がない場合はNULLを返す。
-- 転向や退学してクラスに属していない生徒(studentsのclass_idがNULL)も出力される！
SELECT S.name, S.class_id, C.class_name
FROM students AS S
LEFT JOIN classes AS C ON S.class_id = C.id; -- LEFT OUTER JOINでもOK
RIGHT OUTER JOINは右側のテーブルがマスターになる。
ただ、基本的に左側のテーブルと右側のテーブルを入れ替えればよいので、LEFT OUTER JOINが使われることが多い。
注意事項
CROSS JOINとWHERE句を用いて以下のように結合できるが、古い構文であるため使ってはならない！
理由
- ひとめで内部結合か外部結合かわからないため
- 結合条件とレコードの抽出条件か区切りがわかりづらいため
- この構文が消される可能性があるため
-- NG例
SELECT S.name, S.class_id, C.class_name
FROM students AS S, classes AS C
WHERE S.class_id = C.id;
ウインドウ関数(OLAP関数)
OLAP･･･OnLine Analytical Processing
データベースを使ってリアルタイムにデータ分析を行う処理のこと
構文
<ウインドウ関数> OVER (PARTITION BY <列> ORDER BY <ソート列>)
-- PARTITION BY <列>は省略可能
RANK関数
-- 科目毎に順位付けして出力
SELECT student_id, subject, RANK() OVER (PARTITION BY subject ORDER BY score) AS ranking
FROM tests;
- RANK(): 同じ順位のスコアがある場合、次の順位が飛ばされる（例: 1位が2人いる場合、次は3位）。
- DENSE_RANK(): 順位を飛ばさず、連続した順位を付ける。
- ROW_NUMBER(): 各行に連番を振る。同じスコアでも連番が付けられる。
メタグロス中島EXISTS述語
レコードが存在する場合はTRUE, そうでない場合はFALSEを返す。
- EXISTS句は引数を1つしか取らない
- EXISTS句の引数は常にサブクエリ
- EXISTS句のSELECT文は常に*で取得するのが一般的
-- 例
-- テストID0001を受験した生徒の名前と性別を表示する
SELECT name, gender FROM students AS S
WHERE EXISTS (
SELECT * FROM tests AS T
WHERE S.id = T.student_id AND T.id = '0001'
)
GROUPING演算子
GROUPING演算子には以下の3つがある
- ROLLUP
- CUBE
- GROUPING SETS
-- 例
-- 例1: ROLLUP
-- テストの科目毎の平均と、全平均を求める
SELECT subject, AVG(score) AS score_average FROM tests
GROUP BY ROLLUP(subject);
/*
| subject | score_average |
|----------|---------------|
| Math | 82.5 |
| English | 82.5 |
| Science | 88.0 |
| NULL | 83.6 | -- 全体平均
*/
-- 例2: ROLLUP
-- テストの学期ごと、科目ごとの平均と、それらの学期ごとの平均と、全平均を求める
SELECT subject, term, AVG(score) AS score_average FROM tests
GROUP BY ROLLUP(subject, term);
/*
| subject | term | score_average |
|----------|-------|---------------|
| Math | Term1 | 80.0 |
| Math | Term2 | 85.0 |
| Math | NULL | 82.5 | -- Mathの全体平均
| English | Term1 | 75.0 |
| English | Term2 | 90.0 |
| English | NULL | 82.5 | -- Englishの全体平均
| Science | Term1 | 88.0 |
| Science | NULL | 88.0 | -- Scienceの全体平均
| NULL | NULL | 83.6 | -- 全体平均
*/
-- 例3: CUBE
-- テストの科目ごと、学期ごと、全ての組み合わせに対する平均を求める
SELECT subject, term, AVG(score) AS score_average FROM tests
GROUP BY CUBE(subject, term);
/*
| subject | term | score_average |
|----------|-------|---------------|
| Math | Term1 | 80.0 |
| Math | Term2 | 85.0 |
| Math | NULL | 82.5 |
| English | Term1 | 75.0 |
| English | Term2 | 90.0 |
| English | NULL | 82.5 |
| Science | Term1 | 88.0 |
| Science | NULL | 88.0 |
| NULL | Term1 | 81.0 | -- Term1の全体平均
| NULL | Term2 | 87.5 | -- Term2の全体平均
| NULL | NULL | 83.6 | -- 全体平均
*/
-- 例4: GROUPING SETS
-- 特定のグループ組み合わせに対してのみ平均を求める
SELECT subject, term, AVG(score) AS score_average FROM tests
GROUP BY GROUPING SETS ((subject, term), (term));
/*
| subject | term | score_average |
|----------|-------|---------------|
| Math | Term1 | 80.0 |
| Math | Term2 | 85.0 |
| English | Term1 | 75.0 |
| English | Term2 | 90.0 |
| Science | Term1 | 88.0 |
| NULL | Term1 | 81.0 | -- Term1の全体平均
| NULL | Term2 | 87.5 | -- Term2の全体平均
*/