生のSQLを書く機会はそこまでないですが、いざ必要になった時ようにまとめました。
関連記事
CREATE
- データベース作成
CREATE DATABASE データベース名;
- テーブル作成
CREATE TABLE テーブル名
(列名1 データ型 制約,
列名2 データ型 制約,
.
.
PRIMARY KEY (列名1, ..)
);
データ型
- INTEGER : 整数
- CHAR(最大文字数) : 固定長文字列
- 最大文字数に満たない場合、半角スペースで埋める
- (例) CHAR(4) : 'a
'
- VARCHAR(最大文字数) : 可変長文字列
- 最大文字数に満たない場合でも、半角スペースで埋めない
- DATE : 日付（年月日）
制約
- NOT NULL : NULL(無記入)の場合エラー
- PRIMARY KEY (~~_id) : 主キー制約
DROP
- テーブルの削除
DROP TABLE テーブル名;
-- 削除したテーブルとそのデータは復活できない
ALTER
- テーブルの列追加
ALTER TABLE テーブル名 ADD COLUMN 列名, データ型, 制約;
- テーブルの列削除
ALTER TABLE テーブル名 DROP COLUMN 列名;
-- DROP TABLE文と同様、元に戻せない
INSERT
- テーブルへデータを登録
BEGIN TRANSACTION;
-- MySQLの場合、START TRANSACTION;
INSERT INTO テーブル名 VALUES ('~~', '~~', ..);
INSERT INTO テーブル名 .....
.
.
COMMIT;
RENAME
- テーブル名の変更
ALTER TABLE 変更前テーブル名 RENAME TO 変更後テーブル名;
-- MySQLは、
-- RENAME TABLE 変更前テーブル名 to 変更後テーブル名;
SELECT
- 基本的なSELECT文
SELECT 列名1, 列名2, ..
FROM テーブル名；
- 全ての列を出力
SELECT * FROM テーブル名;
- 列に別名をつける
SELECT 列名1 AS 別名1, 列名2 AS 別名2, ..
FROM テーブル名;
- 重複を省く
SELECT DISTINCT 列名1 FROM テーブル名;
-- 例 : SELECT DISTINCT name age FROM User;
-- 複数指定可能
WHERE句
- 基本の形
SELECT 列名, ..
FROM テーブル名
WHERE 条件式;
-- 例
-- SELECT name FROM User WHERE name = 'Bob';
WHERE句は必ずFROM句の直後に
NULLの扱い
- NULLを含んだ計算は、全てNULLに
1 + NULL = NULL
4 / NULL = NULL
NULL / 0 = NULL
-- エラーにならない
- NULLには比較演算子が使えない
- NULLかどうか判別するためには、専用の演算子IS NULLを使用
SELECT name, age
FROM User
WHERE age IS NULL;
-- 反対にNULLではない行を選択するときは、 IS NOT NULL
SQLの比較演算子
- 他の言語と違うもの
= : ~と等しい
<> : ~と等しくない
SQLの論理演算
- 例
SELECT name, age
FROM User
WHERE NOT age >= 10;
SELECT name, age
FROM User
WHERE name = 'Bob'
AND age = 23
＊ OR演算子よりAND演算子の方が優先されるため、複雑になるときは、カッコ()を使用する
- SQLは、真/偽/不明(= NULL)の3値論理
NULLが偽の扱いにならないことに注意が必要。
そのため、比較演算子等では、真でも偽でもないため表示されない。IS NULL等の、専用の演算子で取得。
集約関数
下記の5つが基本
- COUNT
- SUM
- AVG
- MAX
- MIN
(例)
SELECT COUNT(*) FROM User;
-- 仮にageにNULLがあるユーザーを省いた総数が知りたい時は、
-- SELECT COUNT(age) FROM User;
- 列名を引数にとった際にNULLは除外されることで、適切に計算される
SELECT SUM(age) FROM User;
- SUM/AVG関数は、数値型の列のみ
MAX/MIN関数は、原則どのデータ型にも適用可能（日付等）
重複を除外して集約関数を使用
SELECT SUM(DISTINCT age) FROM User;
-- 重複あるageを除外してから合計するため、上のようなDISTINCTの位置になる。
GROUP BY 句
- 基本の形
SELECT 列名1, ..
FROM テーブル名,
GROUP BY 列名1, ..;
GROUP BY句の位置は、必ずFROM句の後ろ、WHERE句がある場合はその後ろ
- WHERE句とGROUP BY句の併用例
SELECT name, age
FROM User
WHERE name = 'Bob'
GROUP BY nation;
実行順序は、FROM ⇨ WHERE ⇨ GROUP BY ⇨ SELECT
集約関数とGROUP BY句でよくある間違い
1. SELECT句に余計な列を書く
まず、集約関数を使用する時、SELECT句に記述可能なものは、
- 定数
- 集約関数
- GROUP BY句で指定した列名（集約キー）
GROUP BY句で記述していない列名を表示したいと、SELECT文に追加するとエラーになる。
原因：何らかのキーでグループ化した場合、結果に出てくる1行あたりの単位もそのグループになっている。その状態で列名を追加すると、その行の結果と、追加した列名に表示したい値が1対1対応にならないから
2. GROUP BY句に列の別名を書く
SELECT句に、ASを使用して設定した別名をGROUP BY句で使用できない
原因：読み込まれる順番は、GROPU BY句の方がSELECT句より先だから。
3. WHERE句に集約関数を書く
COUNTなどの集約関数を書くことができる場所は、
- SELECT句
- HAVING句
- ORDER BY句 のみ
WHERE句に集約関数を使いたいと思ったときは、HAVING句に記述する
HAVING句
- WHERE句 : レコード(行)に対する条件指定
- レコード抽出段階の条件
-
HAVING句 : グループに対する条件指定
- グループ化後の条件
基本の形
SELECT 列名1, ..
FROM テーブル名
GROUP BY 列名1, ..
HAVING グループの値に対する条件
記述順序は、SELECT ⇨ FROM ⇨ WHERE ⇨ GROUP BY ⇨ HAVING
(例)
SELECT name, COUNT(*)
FROM User
GROUP BY nation
HAVING COUNT(*) = 3;
HAVING句に記述できる要素
GROUP BY句を使ったSELECT句と同じ。
- 定数
- 集約関数
- GROUP BY句で指定した列名（集約キー）
GROUP BY句で記述していない列名を、HAVING句で使用するとエラーになる
原因：GROUP BY句を実行した段階で出来た集約後のテーブルに対してHAVING句の内容を実行するため、この時点で、GROUP BY句で指定していない列名以外は存在していないから
反対に、GROUP BY句で指定した列名は記述可能であり、それに対しては、WHERE句に書くべき。それはレコード(行)に対しての条件だから。
HAVING句より、WHERE句に条件を記述する方が、処理速度が速い
理由：HAVING句は、ソートが終わってグループ化してから実行するため。WHERE句を使った場合は、ソートの前に行を絞り込むため、ソート対象の行数が減る。
また、WHERE句で指定する列には、インデックスを作成することが可能なため。
ORDER BY句
- 基本の形
SELECT 列名1, ..
FROM テーブル名
ORDER BY 並び替えの基準に設定したい列1, .. DESC;
-- ORDER BY句に書く列名を「ソートキー」
-- 順序の指定を書かない場合、デフォルトで昇順
記述順序は、SELECT ⇨ FROM ⇨ WHERE ⇨ GROUP BY ⇨ HAVING ⇨ ORDER BY
- ASC : 昇順（小⇨大）（デフォルト）
- DESC : 降順
NULLには比較演算子は使えないため、数値との順序づけは出来ない。したがって、NULLは先頭または末尾にまとめられる。
ORDER BY句で記述できるもの
- SELECT句でつけた列の別名が使用可能
内部的な実行順序が、
FROM ⇨ WHERE ⇨ GROUP BY ⇨ HAVING ⇨ SELECT ⇨ ORDER BY
のため、既に指定されているから可能。
- SELECT句に含まれていない列や集約関数が使用可能
参考
「SQL ゼロから始めるデータベース操作」