とりあえずこれ！SQLまとめ
はじめに
みさなんこんにちは、ALH開発事業部のREIYAです。また会いましたね
今回はWebエンジニアが業務で使う上で「これさえ覚えておけば問題ない！」をテーマにSQLの使い方をまとめました。なんとなく難易度順にしています。
これだけでとりあえずOKという基準は、完全に私の独断と偏見なので異論は大いに受け付けております。
要所で詳しい解説はしていませんので、部分的にわからないものがあれば別途調べながらひと通りマスターしていきましょう！
手を動かしたい方向け
本記事で紹介するSQL内容にまだ慣れておらず手を動かしたい！という方はぜひ動作確認をしながら呼んでみてください。
動作確認をするに当たって環境つくるの面倒って方多いですよね？私もです。安心してください。
手元の環境でパッと動かせるお手軽DB、SQLiteを使いましょう。これなどを参考にすぐに使えます。なんと「ファイル1枚」をDB扱いできるんです。
WindowsならA5Mk2であったり、MacならDBeaverなどでDBを見ると思いますが、同じ手順でSQLiteも見れます。
注意
細かい文法がOracle、PostgreSQL、MySQLで異なることもあります。
なるべく差分を書きますが、網羅していないかもしれませんのでご注意ください。
レベル1: 基本CRUD
まずはCreate、Read、Update、Deleteです。
テーブル作成関連
CREATE TABLE table_name (
col_name VARCHAR(64) NOT NULL,
col1 VARCHAR(64) NOT NULL,
-- 略
);
-- コメント追加 ちょいちょい差があるので注意
ALTER TABLE table_name COMMENT 'MySQLテーブル論理名';
COMMENT ON TABLE table_name IS 'PostgreSQL';
-- postgresカラムへコメント
COMMENT ON COLUMN table_name.col1 IS '論理名';
-- 主キー制約追加
ALTER TABLE table_name ADD CONSTRAINT some_name PRIMARY KEY(col_name);
-- カラム追加
ALTER TABLE table_name ADD new_column VARCHAR(64) DEFAULT NULL COMMENT 'コメント' AFTER new_column;データ投入
-- 列名を指定してinsert
INSERT INTO table_name (col1, col2) VALUES ('val1', 'val2');
-- バルクインサート
INSERT INTO table_name (col1, col2) VALUES ('val1', 'val2')
, ('val1', 'val2')
, ('val1', 'val2')
-- 略
;データ取得
-- 全カラム取得
SELECT * FROM table_name WHERE column_name = 'some condition';
-- 指定
SELECT col1, col2 FROM table_name WHERE colA IS NOT NULL;データ更新
UPDATE table_name SET
col1 = 'val1'
, col2 = 'val2'
WHERE colA = 'condition';削除
-- 行削除
DELETE FROM table_name WHERE colA = 'test';
-- 全レコード削除
DELETE FROM table_name;
-- 全レコード削除（テーブルを消滅させてから再構成する、ロールバックできないが高速ではある）
TRUNCATE TABLE table_name;
-- テーブル削除
DROP TABLE table_name;余談1
-- 「あれば」の句
DROP TABLE IF EXISTS table_name;
-- なければ
CREATE TABLE IF NOT EXISTS table_name(
-- 略
);
SELECT someting FROM table1 WHERE
EXISTS (
SELECT 1 FROM ...
);余談2
-- ビューは毎回SELECTするイメージ
CREATE VIEW someview (
col1
, col1
, ...
) AS
SELECT
--
FROM ...;
-- マテビューは作った断面のデータ保持する。だから早い。
-- けど元テーブルに更新があっても反映されないので
-- マテビューリフレッシュ必要。
CREATE MATERIALIZED VIEW name AS ...また型だったりその他もろもろ話題は豊富ですが、いったんこのくらいにしておきます。
レベル2: サブクエリ
副問合せというやつです。
SELECT
a.col_alias
FROM
(
SELECT
b.name col_alias
FROM
table2 b
WHERE
b.id = 5
) a
WHERE a.col_alias = 'some';取得したもの、から取得するということ。
後述のJOINやEXISTS句等と組み合わせたりします。こういうことしまくれば、もうSQLだけで全部行ける気がしませんか？
しかしネストが多くなったりして読みにくいのでWITH句を使いましょう。
WITH a AS (
SELECT
b.name col_alias
FROM
table2 b
WHERE
b.id = 5
)
SELECT
a.col_alias
FROM
a
WHERE a.col_alias = 'some';WITHであらかじめSELECTしといたものから、とできます。
複数宣言もできます。
レベル3: JOINとUNION
JOINはテーブルを横にくっつけます。
クエリの実行中だけテーブルデータをくっつけます。
テーブルデータがマージされてしまう訳ではないのでご安心あれ。
内部と外部
とりあえずINNER JOINとLEFT JOINだけ使っておけばよいです。
CROSS JOINは存在だけ知っておいてください。
内部結合
AにもBにもある行だけになります。NULL無視です。
SELECT
a.id
, a.name
, b.name
FROM
tableA a
INNER JOIN
tableB b
ON
a.id = b.id
-- ⇩ここから下は不要。
a.id IS NOT NULL -- INNERならこの条件があると思えば良い
WHERE
a.id IS NOT NULL -- INNERならこの条件があると思えば良い
;イメージ
a: 2, 4
b: 1,2,3,4
取得結果は2行！
2 4チューニング向けの知識として覚えておいていただきたいのが
テーブルaについて、WHERE句で絞るより
結合条件のONで絞る方が性能が良いことが多いです。
外部結合
SELECT
a.id
, a.name
, b.name
FROM
tableA a
LEFT JOIN
tableB b
ON
a.id = b.id
;イメージ
a: 2, 4
b: 1,2,3,4
取得結果は4行！
NULL 2 NULL 4a.idがNULL、b.idが値あり、みたいな行も取れます。
多く行が取れます。
怖かったら、とりあえずLEFT JOINしとけばOK
取りすぎだったら後からIS NOT NULLいれるとかINNER JOINに変えればいいです。
そのうち慣れてきたらINNERとLEFT使い分けられるようになります。
UNION
同じカラムで、2個取得できたとしましょう。
5行ぶんと7行ぶん取れて、クエリ全体で13行ぶんを返す方法です。
JOINが横向きに結びつけるなら、UNIONは縦にがっちゃんこ。
SELECT val AS NN FROM table1 WHERE id = 1
UNION ALL
SELECT name AS NN FROM table2 WHERE id = 2本来はサブクエリが入り乱れまくった後とかでUNIONすることが多いイメージですが、正直テーブルを2回スキャンしちゃうのであんまり良くはないです。
レベル4: 分岐と関数
分岐
IF文みたいなことができます。
CASE column_name
WHEN 'value1' THEN 1
WHEN 'value2' THEN 2
ELSE 0
ENDCASE
WHEN col = 'value1' OR col2 = 'v2' THEN 1
WHEN col IN ('value1', 'v2') THEN 2
ELSE 0
END基本関数
とりあえずCOUNTだけは覚えておきましょう。
1億レコードあるテーブルでSELECT * FROMとかするとえらい負荷がかかってしまうので、文法を確認したいだけ！みたいな場合は
SELECT COUNT(*) FROM table_name;
SELECT COUNT(column_name) FROM table_name;とかってしましょう。
他にもこういった関数がいっぱい。このあたり方言ありますので、ご使用の際は公式ドキュメントを確認しましょう。
SELECT MAX(column) FROM ... -- 最大値、文字列でもok
SELECT MIN(column) FROM ... -- 最小値
SELECT SUM(column) FROM ... -- 合計
SELECT AVG(column) FROM ... -- 算術平均
SELECT LEN(column) FROM ... -- 文字列長
SELECT CURRENT_TIMESTAMP -- 現在日時
SELECT CURRENT_DATE -- 現在日付SELECT CAST(100 AS VARCHAR(10)); -- 数値→文字
SELECT CAST('2018-01-01' AS DATE); -- 文字→日付
-- ※他にもTO_CHARなどあるがキャスト系は省略します-- 文字操作系
SELECT CONCAT('A', '-', 'B'); -- A-Bと結合
SELECT STRING_AGG(col, ','); -- colを全てカンマでaggrigate
SELECT 'test' || 'value'; -- testvalueが出る。
SELECT LPAD('AA', 5, 'X'); -- XXXAAとLeftにPAD(埋める)aggはaggrigate(=集計)の略称です。
ちなみに
INSERT INTO table1 (val, created_at)
VALUES ('val1', CURRENT_TIMESTAMP);とかもいけます。
レベルA: Groupと集計関数
このあたりから「とりあえず覚えとけレベルではない」気がします。
SQLを覚えたての方はやや混乱するかもしれませんが
覚えておいて損はないので紹介します。
なにができるかだけでも知っておけばかなり違います。
グルーピングですが、データのイメージがつきにくい方はご自分で具体例をいっぱい作って試すのが手っ取り早いです。
覚えるべきことはSQLくんの気持ちになって
「グループ項目以外はどれ採用したらええねん！」
「集計してあげよう」
SELECT
name -- 2️⃣ 取得する！
, MAX(col) -- 3️⃣ 同じnameな行が複数あったら
FROM -- colはどれを採用したらいいの！？
table1 -- どれれもいいならMAXでOK
WHERE
id IN (0, 1, 2)
GROUP BY
name -- 1️⃣ nameが同じ行ごとに
-- 以下余談
ORDER BY name DESC -- 余談 ソート
LIMIT 1; -- 余談 上から1行分(oracleは書き方違う)レベルA: Window関数
取得結果そのものをグループでまとめるのでなく
クラス、人名、テスト点数
があったとき
クラスごとの点数合計を求めたいですよね？
SELECT SUM(score), classname FROM table1
GROUP BY classname;これだと、人名とれませんね？
SELECT name, classname,
SUM(score) OVER (PARTITION BY classname)
FROM table1;これでとれます。
取得結果全体でなく、「その項目のみ」グルーピングした集計結果にするものです。
レベルA: UPSERT
入れようとしているデータのキー重複していればUPDATE、なければINSERTという処理がUPSERTです。
OracleではMERGEと言います。
MySQLとPostgreSQLでもだいぶ違うので注意。
※手元のSQLiteで試したい！という方はこちらやこちらを一読ください。
oracle
MERGE INTO
table1
USING
table2
ON (table1.id = table2.tab1_id)
WHEN
MATCHED
THEN
-- table2側の値で更新
UPDATE SET col1 = table2.col1, col2 = table2.col2
WHEN
NOT MATCHED
THEN
-- table2の値をinsertする
INSERT (col1, col2) VALUES (table2.col1, table2.col2);mysql
INSERT INTO table1 (col1, col2)
VALUES
-- bulk insertでいけます
('val1', 'val2')
, ('val2_1', 'val2_2')
ON DUPLICATE KEY UPDATE
-- VALUES()関数で、insertしようとしてた値を使える
col1 = VALUES(col1),
col2 = VALUES(col2)
;postgres
INSERT INTO table1 (col1, col2)
VALUES
-- bulk insertでいけます
('val1', 'val2')
, ('val2_1', 'val2_2')
-- mysqlと違い重複キー名を指定する必要
ON CONFLICT (did) DO UPDATE SET
-- EXCLUDED.名で、insertしようとしてた値を使える
col1 = EXCLUDED.col1;レベルA: シノニム
こちらを参照ください。
Oracleで別DBにリンクできるんですが、そのDBへの呼び名みたいなもんです。
分散型で使うことあるかなーくらいですが、まぁそれだけです。
レベルS: ロック
ここからはより物議を醸す話題になります。
知らない方は知識として、ご存知の方は頭の整理がてら呼んで欲しいです。
楽観ロックと悲観ロックはこちらなどを見てざっくり理解しておきましょう。
とりあえずロックのやり方、するとどうなるかだけ覚えてください。こちらも参考になります。
また、変なことして永遠にロックされる状態をデッドロックと言いますが、表へのロックなんかは特に注意しましょう。
※やってしまった場合はこれやトランザクションを解放するなどしてどうにかロックを解放しましょう。
行ロック
指定行を、解放するまでロックします。
ロックされると、その行への更新はできません。SELECTはぜんぜんおっけー。
※これを排他ロックと言います。
SELECT * FROM table1 WHERE id = 5 FOR UPDATE;またロック済み行へのロック取得も解放されるまでできません。
さて、一応FOR SHAREのほうもあるのですが、とりあえず片方だけ覚えておけば、そっちじゃないほうと分かるので割愛しますね。
ギャップロック
idが10までしかない場合で以下にようにすると、11から100まではINSERTできません。
SELECT * FROM table1 WHERE id BETWEEN 1 AND 100 FOR UPDATE;へんな行ロックしないようにしましょうね
表ロック
オプションはPostgresのものです。
LOCK TABLE table1 IN EXCLUSIVE MODE;基本的にトランザクションコミットでロックは解放されます。
レベルS: pivot
ちなみにピポットと読みます。バスケのアレです。
Key-Value形式をご存知でしょうか？
詳しい説明がここにあってとてもわかりやすいです。
縦横変換。pivot句があるものもありますが、生SQLで紹介しておきます
pivot
Key-Value形式を普通のテーブル形式へ
SELECT t_id,
MAX(CASE WHEN key = 'col1' THEN value END) AS col1,
MAX(CASE WHEN key = 'col2' THEN value END) AS col2,
MAX(CASE WHEN key = 'col3' THEN value END) AS col3
FROM table1
GROUP BY t_id
;※unpivotについて
普通のテーブルをKey-Value形式へ、といきたいところですが
そんなことはせん。
本当にどうしようもなく必要な場合は仕方ないですが、基本的にKey-Value型はアンチパターンです。テーブル設計が悪いのでまずは正規化をしましょう。
とはいえ現行システムの影響でどうしても必要なときもあります。
私自身、こういったやつを駆使して2000行越えのSQLをたくさん作ったこともあります。保守性が...
レベルS: プロシージャ
適当に作ったものがこちらや、PostgreSQLでのあいまい検索関数を作っております。
さすがに長くなりすぎるので触れるだけにしておきます。
レベルS: 実行計画
EXPLAIN PLAN FOR
対象SQL;
SELECT * FROM TABLE(dbms_xplan.display());こうでもよい。
EXPLAIN SELECT * FROM table1;基本的にEXPLAINをつけた時、そのクエリの実行計画をプランナが作成して教えてくれます。SELECTで主に使います。
しかし、INSERTなどはどうでしょうか？一応JOINやWHERE句等を指定することもありますので探索コストは確認したいですが、SELECTで代用するのもなんか違います。
-- 実際にSQL文を実行します！！！注意！！！
EXPLAIN ANALYZE 対象SQL;データ投入の実際の時間を見たいなどの場合はANALYZEをつけても良いでしょう。
読み方
とりあえずこのあたり読みましょう。
大きく分けると
表スキャン
結合
ソート
集計
とかに分かれ、
それぞれのうち最もコストが重い部分を解消することが、ボトルネック解消型のチューニングとなります。
インデックスの話が矢面に立ちがちですが
インデックスを付与すると挿入コストは上がりますので、やればいいということでもありません。
本当にちゃんと理解する場合はB木から始まるbbst (balanced binary search tree) 平行二分探索木を完全に理解し、実装したりする必要があります。言い過ぎかも。
レベルEx: チューニング
チューニングは何もSQLの書き方を工夫するだけではなく、DBサーバの設定を触ることで改善が見込めることもあります。
またDBの内部での仕組みを理解することでSQLの書き方にもより明るくなります。
たとえばOracleの場合こちらなどの記事で、問合せSQLが実行される際どういった仕組みになっているのかを見ていきましょう。
また、DBパラメータの設定等も含めPostgreSQLのものではこれがとても良かったです。私も全部見た訳ではないですが大体他も似てるはずなので参考になります。
Oracleのオプティマイザの公式です。
余談ですが文字コードの話で面白い記事があったので置いておきます。
レベルEx: ACID
トランザクション分離レベルの話です。これがとてもよくまとまっています。
Webシステムに置いてそのほとんどがDBへのCRUDを最終的な成果物とし、またDBへのアクセスは複数同時を前提とします。この際トランザクションの管理は必須であり、特性による設計が必要となります。
よってACID特性を理解した上でデータ設計を行うことで、とてもよいシステムが作れるのではないでしょうか。
ちなみにアプリケーションレイヤ側でのトランザクション管理がDBプールへのものまで含んでいる場合は、DB側というよりもORMapper側の特性を注視すべきだと思います。
長くなるのでこの辺りにしておきましょう。
最後に
最後の方は「そういうのもあるんだ」程度でも大丈夫です。正直私も全部を理解はしていません。
余裕が出てきたらDBが内部でどう処理してくれているのか、をなんとなく意識してあげると良いです。
ALHについて知る
↓ ↓ ↓ 採用サイトはこちら ↓ ↓ ↓
↓ ↓ ↓ コーポレートサイトはこちら ↓ ↓ ↓
↓ ↓ ↓ もっとALHについて知りたい？ ↓ ↓ ↓