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

生のSQLを書く機会はそこまでないですが、いざ必要になった時ようにまとめました。

#### 関連記事
- [SQL 一覧シート2（INSERT, DELETE, UPDATE）](https://qiita.com/mitsuooo/items/a7355d2a284df55e3b9d)

## CREATE
- データベース作成

```sql
CREATE DATABASE データベース名;
```

- テーブル作成

```sql
CREATE TABLE テーブル名
(列名1 データ型 制約,
 列名2 データ型 制約,
 .
 .
 PRIMARY KEY (列名1, ..)
);
```

#### データ型
- INTEGER : 整数
- CHAR(最大文字数) : 固定長文字列
    - 最大文字数に満たない場合、半角スペースで埋める
    - (例) CHAR(4) :  'a` `` `` `'
- VARCHAR(最大文字数) : 可変長文字列
    - 最大文字数に満たない場合でも、半角スペースで埋めない
- DATE : 日付（年月日）

#### 制約
- NOT NULL : NULL(無記入)の場合エラー
- PRIMARY KEY (~~_id) : 主キー制約

## DROP
- テーブルの削除

```sql
DROP TABLE テーブル名;
-- 削除したテーブルとそのデータは復活できない
```

## ALTER
- テーブルの列追加

```sql
ALTER TABLE テーブル名 ADD COLUMN 列名, データ型, 制約;
```

- テーブルの列削除

```sql
ALTER TABLE テーブル名 DROP COLUMN 列名;
-- DROP TABLE文と同様、元に戻せない
```

## INSERT
- テーブルへデータを登録

```sql
BEGIN TRANSACTION;
-- MySQLの場合、START TRANSACTION;

INSERT INTO テーブル名　VALUES ('~~', '~~', ..);
INSERT INTO テーブル名　.....
.
.

COMMIT;
```

## RENAME
- テーブル名の変更

```sql
ALTER TABLE 変更前テーブル名 RENAME TO 変更後テーブル名;

-- MySQLは、
-- RENAME TABLE 変更前テーブル名 to 変更後テーブル名;
```

## SELECT
- 基本的なSELECT文

```sql
SELECT 列名1, 列名2, .. 
  FROM テーブル名；
```

- 全ての列を出力

```sql
SELECT * FROM テーブル名;
```

- 列に別名をつける

```sql
SELECT 列名1 AS 別名1, 列名2 AS 別名2, .. 
  FROM テーブル名;
```

- 重複を省く

```sql
SELECT DISTINCT 列名1 FROM テーブル名;

-- 例 : SELECT DISTINCT name age FROM User;
-- 複数指定可能
```

### WHERE句
- 基本の形

```sql
SELECT 列名, .. 
  FROM テーブル名 
 WHERE 条件式;

-- 例
-- SELECT name FROM User WHERE name = 'Bob';
```

**WHERE句は必ずFROM句の直後に**

#### NULLの扱い
- NULLを含んだ計算は、全てNULLに

```sql
1 + NULL = NULL
4 / NULL = NULL
NULL / 0 = NULL
-- エラーにならない
```

- NULLには比較演算子が使えない
- NULLかどうか判別するためには、専用の演算子**IS NULL**を使用

```sql
SELECT name, age 
  FROM User 
 WHERE age IS NULL;

-- 反対にNULLではない行を選択するときは、 IS NOT NULL
```

#### SQLの比較演算子
- 他の言語と違うもの

```
=  : ~と等しい
<> : ~と等しくない
```

#### SQLの論理演算
- 例

```sql
SELECT name, age 
  FROM User 
 WHERE NOT age >= 10;
```

```sql
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  


(例)

```sql
SELECT COUNT(*) FROM User;

-- 仮にageにNULLがあるユーザーを省いた総数が知りたい時は、
-- SELECT COUNT(age) FROM User;
```

- 列名を引数にとった際にNULLは除外されることで、適切に計算される

```sql
SELECT SUM(age) FROM User;
```

- SUM/AVG関数は、数値型の列のみ
- MAX/MIN関数は、原則どのデータ型にも適用可能（日付等）

- 重複を除外して集約関数を使用

```sql
SELECT SUM(DISTINCT age) FROM User;

-- 重複あるageを除外してから合計するため、上のようなDISTINCTの位置になる。
```

## GROUP BY 句
- 基本の形

```sql
SELECT 列名1, .. 
  FROM テーブル名, 
 GROUP BY 列名1, ..;
```

**GROUP BY句の位置は、必ずFROM句の後ろ、WHERE句がある場合はその後ろ**

- WHERE句とGROUP BY句の併用例

```sql
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句 : **グループ**に対する条件指定
    - グループ化後の条件

- 基本の形

```sql
SELECT 列名1, ..
  FROM テーブル名
 GROUP BY 列名1, ..
HAVING グループの値に対する条件
```

記述順序は、**SELECT ⇨ FROM ⇨ WHERE ⇨ GROUP BY ⇨ HAVING**

(例)

```sql
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句

- 基本の形

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