SQLの基本

# はじめに
本記事は、SQLのデータ操作に関する基本的なことについてまとめました。

## SQLの書き方
SQLを書くときのポイントは以下になります。

- 大文字と小文字
予約語は大文字、列名やテーブル名は小文字で書くことで可読性が上がります。

- SQLへの準拠
一部のSQLは、データベースにより、サポートしていないSQLがあります。

## 環境
本記事の環境はSQLite3になります。

データベース：SQLite3

以下のテーブルを題材にしています。

- Adressテーブル

```text
name        phone_nbr     address     sex         age       
----------  ------------  ----------  ----------  ----------
Aaron       075-XXXXXXXX  London      Man         30        
Edna        075-XXXXXXXX  London      Woman       21        
Dud         075-XXXXXXXX  London      Man         45        
Ham         075-XXXXXXXX  Manchester  Man         32        
Jessica                   Manchester  Woman       55        
Lucy        075-XXXXXXXX  Oxford      Woman       19        
Megan                     Oxford      Woman       20        
Sally       075-XXXXXXXX  Cambridge   Woman       25        
Ken         075-XXXXXXXX  Liverpool   Man         32 
```

- Adress2テーブル

```text
name        phone_nbr     address     sex         age       
----------  ------------  ----------  ----------  ----------
Ken         075-XXXXXXXX  Liverpool   Man         32        
Lucy        075-XXXXXXXX  Oxford      Woman       19        
Duke                      Oxford      Man         20        
Steve                     Liverpool   Man         70 
```

- hobbiesテーブル

```text
name        hobby     
----------  ----------
Aaron       Soccer    
Edna        Soccer    
Ken         Baseball  
Dud         Baseball  
Jessica     basketball
Aaron       Baseball  
Edna        Baseball  
Zat         football
```

## データ操作
SQLの文法の種別は、以下の3つに大別されます。
本記事では、データ操作言語（DML）について記載しています。

- データ定義言語 (DDL: Data Definition Language)
- データ操作言語 (DML: Data Manipulation Language)
- データ制御言語 (DCL: Data Control Language)

### SELECT文
データベースのテーブルから、検索してデータを取り出す場合は、SELECT文を使用します。
SELECT句に列、FROM句にテーブル名を指定します。テーブルの列は、カンマで区切って複数指定ができます。

- 全てのレコードを選択

```sql
sqlite> SELECT * FROM Address;
```
- 実行結果

```text
name        phone_nbr     address     sex         age       
----------  ------------  ----------  ----------  ----------
Aaron       075-XXXXXXXX  London      Man         30        
Edna        075-XXXXXXXX  London      Woman       21        
Dud         075-XXXXXXXX  London      Man         45        
Ham         075-XXXXXXXX  Manchester  Man         32        
Jessica                   Manchester  Woman       55        
Lucy        075-XXXXXXXX  Oxford      Woman       19        
Megan                     Oxford      Woman       20        
Sally       075-XXXXXXXX  Cambridge   Woman       25        
Ken         075-XXXXXXXX  Liverpool   Man         32 
```

### WHERE文
SELECT文で特定のレコードを選択する場合は、WEARE句を使用します。

- 住所がオックスフォードを選択

```sql
sqlite> SELECT name, address FROM Address WHERE address = 'Oxford';
```
- 実行結果

```text
name        address   
----------  ----------
Lucy        Oxford    
Megan       Oxford    
```
- WHERE句で使用できる代表的な演算子

|      演算子         |       意味         |
|:------------------:|:------------------:|
|        =           |      〜と等しい      |
|        <>          |      〜と等しくない   |
|        >=          |        〜以上       |
|        >           |     〜より大きい      |
|        <=          |       〜以下        |
|        <           |     〜より小さい      |

#### WHERE句の条件指定

- 年齢が30歳以上を選択

```sql
sqlite> SELECT name, address FROM Address WHERE age >= 30;
```
- 実行結果

```text
name        address   
----------  ----------
Aaron       London    
Dud         London    
Ham         Manchester
Jessica     Manchester
Ken         Liverpool 
```
- 住所がロンドン以外を選択

```sql
sqlite> SELECT name, address FROM Address WHERE address <> 'London';
```
- 実行結果

```text
name        address   
----------  ----------
Ham         Manchester
Jessica     Manchester
Lucy        Oxford    
Megan       Oxford    
Sally       Cambridge 
Ken         Liverpool
```

- 住所がロンドンかつ、年齢が30歳以上を選択

```sql
sqlite> SELECT name, address, age FROM Address WHERE address = 'London' AND age >= 30;
```
- 実行結果

```text
name        address     age       
----------  ----------  ----------
Aaron       London      30        
Dud         London      45  
```

- 住所がロンドンまたは、年齢が30歳以上を選択

```sql
sqlite> SELECT name, address, age FROM Address WHERE address = 'London' or age >= 30;
```
- 実行結果

```text
name        address     age       
----------  ----------  ----------
Aaron       London      30        
Edna        London      21        
Dud         London      45        
Ham         Manchester  32        
Jessica     Manchester  55        
Ken         Liverpool   32
```

#### IN
INを使用することで、ORを使用することなく、複数の条件指定を簡略化できます。

- 住所がロンドン、マンチェスター、オックスフォードのいずれかを選択

```sql
sqlite> SELECT name, address FROM Address WHERE address IN ('London', 'Manchester', 'Oxford');
```
- 実行結果

```text
name        address   
----------  ----------
Aaron       London    
Edna        London    
Dud         London    
Ham         Manchester
Jessica     Manchester
Lucy        Oxford    
Megan       Oxford
```
- INを使わない場合

```SQL
sqlite> SELECT name, address FROM Address WHERE address = 'London' OR address = 'Manchester' OR address = 'Oxford';
```

#### NULL
NULLLのレコードを選択する場合は、IS NULLを使用します。また、NULLでないレコードを選択する場合は、IS NOT NULLを使用します。

- NULLのレコードを選択

```sql
sqlite> SELECT name, phone_nbr FROM Address WHERE phone_nbr IS NULL;
```
- 実行結果

```text
name        phone_nbr 
----------  ----------
Jessica               
Megan
```

- NULL以外のレコードを選択

```sql
sqlite> SELECT name, phone_nbr FROM Address WHERE phone_nbr IS NOT NULL;
```
- 実行結果

```text
name        phone_nbr   
----------  ------------
Aaron       075-XXXXXXXX
Edna        075-XXXXXXXX
Dud         075-XXXXXXXX
Ham         075-XXXXXXXX
Lucy        075-XXXXXXXX
Sally       075-XXXXXXXX
Ken         075-XXXXXXXX
```
#### 正規表現
正規表現は、LIKE句を使用します。

- 名前の最後が"n"で終わる人を選択

```sql
sqlite> SELECT * FROM Address WHERE name LIKE '%n';
```

- 実行結果

```text
name        phone_nbr     address     sex         age       
----------  ------------  ----------  ----------  ----------
Aaron       075-XXXXXXXX  London      Man         30        
Megan                     Oxford      Woman       20        
Ken         075-XXXXXXXX  Liverpool   Man         32
```
- 名前が5文字で最後が"n"で終わる人を選択

```sql
sqlite> SELECT * FROM Address WHERE name LIKE '____n';
```

- 実行結果

```text
name        phone_nbr     address     sex         age       
----------  ------------  ----------  ----------  ----------
Aaron       075-XXXXXXXX  London      Man         30        
Megan                     Oxford      Woman       20 
```

- LIKE句で使用できる代表的な演算子

|      演算子         |       意味         |
|:------------------:|:------------------:|
|        %           | 任意の0文字以上の文字列     |
|        _          |      任意の1文字   |

#### BETWEEN
指定した範囲のレコードを検索する場合はBETWEEN句を使用します。

- 年齢が20歳から29歳までの人を選択

```sql
sqlite> SELECT name, age FROM Address WHERE age BETWEEN 20 AND 29;
```

- 実行結果

```text
name        age       
----------  ----------
Edna        21        
Megan       20        
Sally       25 
```

### GROUP BY
GROUP BY句を使用することで、合計や平均などの集計演算ができます。

- 性別でグループ毎にカウント

```sql
sqlite> SELECT sex, COUNT(*) FROM Address GROUP BY sex;
```
- 実行結果

```text
sex         COUNT(*)  
----------  ----------
Man         4         
Woman       5
```

- SQLの代表的な集計用の関数

|      演算子         |       意味         |
|:------------------:|:------------------:|
|        COUNT       |      レコード数を数える|
|        SUM         |      数値を合計する  |
|        AVG         |      数値を平均する  |
|        MAX         |      最大値を求める  |
|        MIN         |      最小値を求める  |

- 住所毎にカウント

```sql
sqlite> SELECT address, COUNT(*) FROM Address GROUP BY address;
```
- 実行結果

```text
address     COUNT(*)  
----------  ----------
Cambridge   1         
Liverpool   1         
London      3         
Manchester  2         
Oxford      2 
```

- 全てを集計

```sql
sqlite> SELECT COUNT(*) FROM Address;
```

```text
COUNT(*)  
----------
9 
```

- GROUP BY()はサポートしてない

```sql
sqlite> SELECT COUNT(*) FROM Address GROUP BY();
Error: near ")": syntax error
```

### HAVING
HAVING句はWHERE句と同じようにレコードを特定します。
違いとしては、WHERE句がレコードに対して条件指定するのに対し、HAVING句はレコードの集合に対して条件指定を行います。

- 1人だけの住所を選択

```sql
sqlite> SELECT address, COUNT(*) FROM Address GROUP BY address HAVING COUNT(*) = 1;
```
- 実行結果

```text
address     COUNT(*)  
----------  ----------
Cambridge   1         
Liverpool   1
```

### ORDER BY
SQLでデータを取り出す時の動きは、基本的にランダムにデータを取り出します。
よって、明示的にデータを取り出す場合はORDER BY句を使用します。
デフォルトはASC（昇順）で取り出します。逆から取り出す場合はDESC（降順）を指定します。

- 年齢で降順

```sql
sqlite> SELECT name, age FROM Address ORDER BY age DESC;
```
- 実行結果

```text
name        age       
----------  ----------
Jessica     55        
Dud         45        
Ham         32        
Ken         32        
Aaron       30        
Sally       25        
Edna        21        
Megan       20        
Lucy        19
```

### VIEW
ビューは仮想的なテーブルを事前に作成することで、あたかもテーブルのデータを操作しているように中身を保護します。

- 住所別の人数を求めるSELECT文のビューを保存

```sql
sqlite> CREATE VIEW CountAddress (address, cnt) AS SELECT address, COUNT(*) FROM Address GROUP BY address;
sqlite> SELECT address, cnt FROM CountAddress;
```

- 実行結果

```text
address     cnt       
----------  ----------
Cambridge   1         
Liverpool   1         
London      3         
Manchester  2         
Oxford      2
```
#### サブクエリ
ビューの中身にSELECT文を行うことをサブクエリと呼びます。

```sql
sqlite> SELECT address, cnt FROM (SELECT address, COUNT(*) AS cnt FROM Address GROUP BY address) AS CountAddress;
```

- 実行結果

```text
address     cnt       
----------  ----------
Cambridge   1         
Liverpool   1         
London      3         
Manchester  2         
Oxford      2 
```

- AddressテーブルからAddress2テーブルにいる人を選択

```sql
sqlite> SELECT name FROM Address WHERE name IN (SELECT name FROM Address2);
```

- 実行結果

```text
name      
----------
Lucy      
Ken
```

### CASE
SQLの条件分岐はCASE文を使用します。CASE式の構文は「単純CASE式」と「検索CASE式」があり、検索CASE式は単純CASE式の機能をすべて含んでいます。

- 検索CASE式で住所の結果表示を地方に分類

```sql
sqlite> SELECT name, address, CASE WHEN address = 'London' THEN 'Central city' WHEN address = 'Manchester' THEN 'North' ELSE NULL END AS district FROM Address;
```

- 実行結果

```text
name        address     district    
----------  ----------  ------------
Aaron       London      Central city
Edna        London      Central city
Dud         London      Central city
Ham         Manchester  North       
Jessica     Manchester  North       
Lucy        Oxford                  
Megan       Oxford                  
Sally       Cambridge               
Ken         Liverpool
```

### UNION
UNIONは重複するレコードを削除した和集合を求めます。

- AddressテーブルとAddressテーブル2がある場合の和集合を求める

```sql
sqlite> SELECT * FROM Address UNION SELECT * FROM Address2;
```

- 実行結果

```text
name        phone_nbr     address     sex         age       
----------  ------------  ----------  ----------  ----------
Aaron       075-XXXXXXXX  London      Man         30        
Dud         075-XXXXXXXX  London      Man         45        
Duke                      Oxford      Man         20        
Edna        075-XXXXXXXX  London      Woman       21        
Ham         075-XXXXXXXX  Manchester  Man         32        
Jessica                   Manchester  Woman       55        
Ken         075-XXXXXXXX  Liverpool   Man         32        
Lucy        075-XXXXXXXX  Oxford      Woman       19        
Megan                     Oxford      Woman       20        
Sally       075-XXXXXXXX  Cambridge   Woman       25        
Steve                     Liverpool   Man         70
```

### INTERSECT
INTERSECTは積集合を求めます。

- AddressテーブルとAddressテーブル2に共通するレコードを求める

```sql
sqlite> SELECT * FROM Address INTERSECT SELECT * FROM Address2;
```

- 実行結果

```text
name        phone_nbr     address     sex         age       
----------  ------------  ----------  ----------  ----------
Ken         075-XXXXXXXX  Liverpool   Man         32        
Lucy        075-XXXXXXXX  Oxford      Woman       19 
```

### EXCEPT
EXCEPTは差集合を求めます。
EXCEPTの注意点は、SQLで指定するテーブルの順番により結果が異なることです。
（UNIONやINTERSECTはどちらを先に書いても同じ）

- AddressテーブルからAddressテーブル2に存在するレコードを除外する

```sql
sqlite> SELECT * FROM Address EXCEPT SELECT * FROM Address2;
```

- 実行結果

```text
name        phone_nbr     address     sex         age       
----------  ------------  ----------  ----------  ----------
Aaron       075-XXXXXXXX  London      Man         30        
Dud         075-XXXXXXXX  London      Man         45        
Edna        075-XXXXXXXX  London      Woman       21        
Ham         075-XXXXXXXX  Manchester  Man         32        
Jessica                   Manchester  Woman       55        
Megan                     Oxford      Woman       20        
Sally       075-XXXXXXXX  Cambridge   Woman       25
```

### INNER JOIN
複数のテーブルを結合する場合は、JOINを使用します。
INNER JOINは内部結合を行います。

- Addressテーブルの「名前」と「住所」、hobbiesテーブルの「趣味」を結合して取り出す（2つのテーブルの共通部分）

```sql
sqlite> SELECT A.name, A.address, H.hobby FROM Address AS A INNER JOIN Hobbies AS H ON A.name = H.name;
```

- 実行結果

```text
name        address     hobby     
----------  ----------  ----------
Aaron       London      Baseball  
Aaron       London      Soccer    
Edna        London      Baseball  
Edna        London      Soccer    
Dud         London      Baseball  
Jessica     Manchester  basketball
Ken         Liverpool   Baseball
```

### OUTER JOIN
OUTER JOINは外部結合を行います。

- Addressテーブルの「名前」と「住所」、hobbiesテーブルの「趣味」を結合して取り出す（2つのテーブルに含まれている全てのデータ（※））

```sql
sqlite> SELECT A.name, A.address, H.hobby FROM Address AS A LEFT OUTER JOIN Hobbies AS H ON A.name = H.name;
```

- 実行結果

```text
name        address     hobby
----------  ----------  ----------
Aaron       London      Baseball  
Aaron       London      Soccer    
Edna        London      Baseball  
Edna        London      Soccer    
Dud         London      Baseball  
Ham         Manchester            
Jessica     Manchester  basketball
Lucy        Oxford                
Megan       Oxford                
Sally       Cambridge             
Ken         Liverpool   Baseball
```
（※）本記事では、左外部結合を行っているため、hobbiesテーブルのみに含まれてるデータは出力されません。hobbiesテーブルのみに含まれてるデータを出力させる場合は、「RIGHT」を指定します。また、どちらか一方ではなく、全てのデータを出力させる場合は「FULL」を指定します。

## データの更新
SQLでデータを更新する場合は、以下の3種類に分類されます。

- 挿入（INSERT）
- 削除（DELETE）
- 更新（UPDATE）

### INSERT
データベースに1行のレコードを挿入する場合は、INSERT文を使用します。
ポイントはSQLの列のリストと値のリストは、並び順が対応している必要があります。
また、文字型は必ず引用符でくくり、数値型はくくりません。「NULL」を挿入する場合は、そのままNULLを指定します。

- Addressテーブルに1行挿入する

```sql
sqlite> INSERT INTO Address (name, phone_nbr, address, sex, age) VALUES ('Aaron', 'Aaron', 'London', 'Man', 30);
```

### DELETE
データベースのレコードを削除する場合はDELETE文を使用します。

- 全てのレコードを削除

```sql
sqlite> DELETE FROM Address;
```

- 特定のレコードを削除

```sql
sqlite> DELETE FROM Address WHERE address = 'Oxford';
```

### UPDATE
データの更新は、UPDATE文を使用します。

- 特定のレコードを更新

```sql
sqlite> UPDATE Address SET phone_nbr = '075-12345678' WHERE name = 'Aaron';
```

- NULLクリア

```sql
sqlite> UPDATE Address SET address = NULL;
```

- 複数の列を同時に更新

```sql
sqlite> UPDATE Address SET phone_nbr = '075-12345678', age = 31 WHERE name = 'Aaron';
```

# おわりに
SQLの学習に[SQLZOO](http://sqlzoo.net/wiki/SQL_Tutorial)がオススメです。