SQL 一覧シート1（テーブル作成からSELECT文）

制約

• 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 ゼロから始めるデータベース操作」