100日後にエンジニアになるキミ - 40日目 - データベース - SQLの基礎3

本日もSQL文の続きです。
主に`SELECT`文について取り扱っていきます。

昨日のはこちら

[100日後にエンジニアになるキミ - 39日目 - データベース - SQLの基礎2]
(https://qiita.com/otupy/items/5a12f89c0b95e9df86c6)


## ここの講義用のテーブルについて

講義用のテーブルを作成して`SELECT`文が実行できるようにしておきましょう。

下記のSQL文をコピー時て実行しておきましょう。
SQLiteの`Excute SQL`から実行


```sql
BEGIN TRANSACTION;
CREATE TABLE `select_sample1` (
 `id` INTEGER PRIMARY KEY AUTOINCREMENT,
 `name` TEXT NOT NULL,
 `old` INTEGER,
 `address` TEXT
);
INSERT INTO `select_sample1` VALUES ( '1','アイウエオ','59','東京都');
INSERT INTO `select_sample1` VALUES ( '2','かきくけこ','34','北海道');
INSERT INTO `select_sample1` VALUES ( '3','sasisuseso','53','東京都');
INSERT INTO `select_sample1` VALUES ( '4','たちつてと','47','愛知県');
INSERT INTO `select_sample1` VALUES ( '5','ナニヌネノ','59','東京都');
INSERT INTO `select_sample1` VALUES ( '6','hahihuheho','34','北海道');
INSERT INTO `select_sample1` VALUES ( '7','まみむめも','52','東京都');
INSERT INTO `select_sample1` VALUES ( '8','やいゆえよ','40','福岡県');
INSERT INTO `select_sample1` VALUES ( '9','ラリるれろ','72','東京都');
INSERT INTO `select_sample1` VALUES ('10','わいうえお','31','埼玉県');
INSERT INTO `select_sample1` VALUES ('11','xxyyzz' ,'25','東京都');
INSERT INTO `select_sample1` VALUES ('12','otupy'  ,'42','千葉県');
COMMIT;
```

<img width="511" alt="スクリーンショット 2020-04-29 16.08.43.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/379626/ed1bf176-66d6-d890-ecc2-3d157e71b639.png">



## SELECT文について

SQL=`SELECT`文言っても良いくらいSQLの中で覚えるボリュームが一番多く
なおかつ一番よく使われる文法です。

まず`SELECT`文はいつくかの`句`に分かれています。

基本構文は、上から順番に

```sql
SELECT句
FROM句 テーブル名
----------------
WHERE句
GROUP BY句
HAVING句
ORDER BY句
LIMIT句;
```

となっています。

そのほか`WITH`句,`UNION`句,`JOIN`句,`ON`句などがあります。

`WHERE`句より下は条件付けに使われる句なので無くても
`SELECT`句と`FROM`句だけで`SELECT`文としては成立します。
文の一番最後は`;`を書いて閉めます。

それでは1つ１つの句を見ていきましょう。

### SELECT句

句としては一番上に書く事が多い句です。
`SELECT`句は最終的に出力するものを書くところです。

最初に書く句なのですが、`SELECT`文としては
一番最後に評価される部分になります。

書き方：

`SELECT カラム名 AS 別名`

まずは出力したいカラムを書きます。
書いたカラムの名前はそのまま出力されるので
名称を変更したい場合は`AS`をつけて別名を書きます。

なお文の書き方の中で、句の名称などは`英大文字`
カラム名などは`英数小文字`を用いましょう。
また、カラム名などには日本語は使えません。

カラムを複数出力したい場合はカラムとカラムの間を`,`でつなぎます。
一番最後に書くカラムの後のカンマは不要です。

`SELECT カラム名 AS 別名 , カラム名 AS 別名 , カラム名 AS 別名`

テーブルの用意が良ければ`SELECT`文を実行してみましょう。

```sql
SELECT name 
FROM select_sample1;
```
name
アイウエオ
かきくけこ
sasisuseso
たちつてと
・・・

name列が全て表示されると思います。

`WHERE`句以降の条件付けをしていない場合
`SELECT`句に指定したカラムの情報が全て取得できます。

逆に言うと、データ量が多いテーブルだった場合全てを表示しようとしてしまいます。
データが多すぎて結果が返ってこないこともあるので
`SELECT`文では条件も重要になってきます。

こう書くと全てのカラムの情報が取得されます。
`SELECT *`
ただし、カラムが多いテーブルの場合全てを表示しようとしてしまうので
`SELECT`句ではカラムを指定してデータを取得するようにしましょう。


### FROM句

`FROM`句はどこのテーブルからデータを取得するのかを指定する句です。
一番最初に評価される句で、基本的にはテーブル名を書きます。

書き方：
`FROM テーブル名`

後述しますが`FROM`句ではテーブルの結合もできるので
`JOIN`句,`ON`句を併せて用いる事ができます。

`FROM　テーブル名1 JOIN テーブル名2 ON 条件式`

### WHERE句

`WHERE`句は条件指定をするための句です。
`FROM`句の後に評価される部分になります。

書き方：
`WHERE 条件式`

条件式はカラムに対して条件を書いていくことになります。

例：
`カラム名 演算子 値`

条件が複数ある場合は`論理演算子`をつけてつなぎ
`NOT`を付けると条件をひっくり返す形になります。

試しに条件を加えて`SELECT`文を実行してみましょう。

```sql
SELECT name 
FROM select_sample1 
WHERE old > 50;
```
name
アイウエオ
sasisuseso
ナニヌネノ
まみむめも
ラリるれろ

結果は条件に当てはまるものだけが表示されます。


**条件式**

条件式は`比較演算子`, `論理演算子`を用いる事ができます。

`比較演算子`は以下のようなものがあります。

|比較演算子|用法|意味|
|:--:|:--:|:--|
|=  | a = b | aとbは等しい |
|==  | a == b | aとbは等しい |
|!=  | a != b | aとbは等しくない |
|<> | a <> b | aとbは等しくない |
| >  | a > b | aはbより大きい |
|  >= | a >= b | aはb以上 |
|  < | a < b | aはbより小さい |
|  <= | a <= b | aはb以下 |

`NULL`値の比較の場合は`IS`を用います。
`カラム IS NULL` : カラムの値がNULL値である
`カラム IS NOT NULL` : カラムの値がNULL値出ない

`NOT`を付けると比較の結果がひっくり返されます。

`論理演算子`は以下の二つがあります。
`AND` : 「かつ」 
`OR` : 「もしくは」

`条件1 AND 条件2`
`AND`条件の時は繋いだ条件全てが満たされないと条件に合致しません。

`条件1 OR 条件2`
`OR`条件の時は繋いだ条件のうちどこかが満たされていれば条件に合致します。

複合条件で見てみましょう。

`AND`条件の場合

```sql
SELECT name 
FROM select_sample1 
WHERE old < 30 AND address = '東京都';
```
name
xxyyzz

`OR`条件の場合

```sql
SELECT name 
FROM select_sample1 
WHERE old < 30 OR address = '東京都';
```
name
アイウエオ
sasisuseso
ナニヌネノ
まみむめも
ラリるれろ
xxyyzz

`AND`の時は条件の論理積`OR`の時は条件の論理和になります。
うまく使い分けましょう。

### GROUP BY句

`GROUP BY`句はグルーピングを行い集計をする時などに用いる句です。

書き方：
`GROUP BY カラム名`
`GROUP BY カラム番号`

`GROUP BY カラム名1, カラム名2, ...`
`GROUP BY カラム番号1, カラム番号2, ...`

カラム番号は`SELECT`句に書いた順番のことを指します。
グルーピングするカラムが複数ある場合は`,`でつなぎます。

実行してみましょう。

```sql
SELECT address FROM select_sample1 GROUP BY address;

-- もしくは

SELECT address FROM select_sample1 GROUP BY 1;
```
address
北海道
千葉県
埼玉県
愛知県
東京都
福岡県

どちらも結果は一緒ですが、重複していたデータが一意になって出力されます。
このように`GROUP BY`句は指定した列の値でグルーピングを行い一意にします。

この機能を用いて集計を行うためによく用いられます。

**集計関数**

SQLでは様々な集計関数を用いる事ができます。
`SQLite`で使える集計関数には以下のようなものがあります。

| 関数名 | 意味 |
|:--|:--|
| COUNT(カラム名) | 列の個数を計算する |
| SUM(カラム名) | 列の合計を計算する |
| AVG(カラム名) | 列の平均を計算する |
| MIN(カラム名) | 列の最小値を計算する |
| MAX(カラム名) | 列の最大値を計算する |

平均などを計算する場合、そのカラムのデータ型が数値型である必要があります。
集計関数は単体でも使用できますが、グルーピングして集計する場合は
`GROUP BY`句が必要になります。

グルーピングした列の値を使って平均値を計算してみましょう。

```sql
SELECT address , AVG(old) 
FROM select_sample1 
GROUP BY 1;
```
address AVG(old)
北海道	34.0
千葉県	42.0
埼玉県	31.0
愛知県	47.0
東京都	53.3333333333333
福岡県	40.0

グルーピングした列の値を使って平均値を計算し結果が出力されます。
集計関数を用いると出力したカラムの名称が関数名などになってしまうので
名称変更したい場合は`AS`を用いて別名をつけておきましょう。

### HAVING句

`HAVING`句は`GROUP BY`句の集計後の結果を用いて絞り込みを行います。
つまり、`HAVING`句単体では使えません。`GROUP BY`句が必要になります。

書き方：
`GROUP BY カラム名 ・・・ HAVING 条件式`

先ほどの`GROUP BY`句の結果を利用してみましょう。

```sql
SELECT address , AVG(old) 
FROM select_sample1 
GROUP BY 1 
HAVING AVG(old) >= 42;
```
name  AVG(old)
千葉県	42.0
愛知県	47.0
東京都	53.3333333333333

先ほどは6行の結果が表示されていましたが
今回のは`HAVING`句で条件を絞っているので3行になっています。

`HAVING`句の条件式はカラムの代わりに`SELECT`句で書いた
集計関数部分使い、条件を加えます。
条件を増やす場合は`論理演算子`を用いて繋げます。


### ORDER BY句

`ORDER BY`句は結果の並び替え(ソート)するために用いる句です。

書き方：
`ORDER BY カラム名`
`ORDER BY カラム番号`

カラム番号は`SELECT`句に書いた順番のことを指します。

並び替え(ソート)には昇順と降順がありますが`ORDER BY`句のデフォルトは昇順です。
降順に並び替えしたい場合は`DESC`を付け加えます。

```sql
SELECT address , AVG(old) 
FROM select_sample1 
GROUP BY 1 
ORDER BY 2 DESC;
```
address AVG(old)
東京都	53.3333333333333
愛知県	47.0
千葉県	42.0
福岡県	40.0
北海道	34.0
埼玉県	31.0

この場合は`SELECT`句で２つ目に書いたカラムの集計結果を降順で並び替えしています。

複数並び替え条件がある場合は`,`でつなぎます。
`ORDER BY カラム名1 , カラム名2`
`ORDER BY カラム番号1 , カラム番号2`


### LIMIT句

`LIMIT`句は出力結果を制限するために用いる句です。
単純に出力したい行数を書きます。

書き方：
`LIMIT 行数`

```sql
SELECT address
FROM select_sample1 
LIMIT 3;
```
address
東京都
北海道
東京都

### カラムの値を用いた計算

`SELECT`句ではカラムの値を用いて計算を行う事ができます。
計算は行単位で行われることになります。

文字列の場合は文字列関数などを用いた文字の抽出
数値の場合は四則計算や関数を用いた計算などが行えます。

```sql
SELECT id , old , old - id AS result
FROM select_sample1;
```
id old result
1	59	58
2	34	32
3	53	50
4	47	43
5	59	54

**算術演算子**

四則計算を行うための演算子としてSQLでは以下のような演算子があります。


| 演算子 | 意味 |
|:--:|:--|
|  +  | 加算 |
|  -  | 減算 |
|  *  | 丈算 |
|  /  | 除算 |

`+`,`-`よりも`*`,`/`の方が優先されて計算されるルールは
プログラム言語のルールや算数のルールと一緒です。
計算を優先したい場合は`()`で囲みます。
`(a + b) * c`


### 重複排除

SQLではデータを重複排除の方法として
`DISTINCT`
`GROUP BY`
のふた通りがあります。

書き方：
`SELECT DISTINCT カラム名`
`SELECT カラム名 ・・・ GROUP BY カラム名`

`DISTINCT`を用いてみると、結果から重複が排除された形で出力されます。

```sql
SELECT DISTINCT address FROM select_sample1;
```
address
東京都
北海道
愛知県
福岡県
埼玉県
千葉県

複数カラムを指定すると複数カラムでの重複を排除した結果となります。
`SELECT DISTINCT カラム名 , カラム名`

ユニークな結果を出したい場合はDISTINCTを用いたり
集計結果も添えたい場合は`GROUP BY`を用います。

### 部分一致(LIKE)

`WHERE`句の条件式で、`関係演算子`を用いた場合
基本的には検索は`完全一致`で行われます。

部分一致で条件を絞り込みしたい場合は`WHERE`句で`LIKE`と'%'を用います。
一致させたい文字列を指定し、可変の値の所を`%`で指定します。
条件を否定したい場合は`NOT`を`LIKE`の前につけます。


書き方：
`WHERE カラム名 LIKE '文字列%'`:前方一致した場合
`WHERE カラム名 LIKE '%文字列%'`:部分一致した場合
`WHERE カラム名 LIKE '%文字列'`:後方一致した場合
`WHERE カラム名 NOT LIKE '文字列%'`:前方一致しなかった場合
`WHERE カラム名 NOT LIKE '%文字列%'`:部分一致しなかった場合
`WHERE カラム名 NOT LIKE '%文字列'`:後方一致しなかった場合

```sql
SELECT name,old 
FROM select_sample1
WHERE address LIKE '%県';
```
name old
たちつてと	47
やいゆえよ	40
わいうえお	31
otupy	42

この場合、`県`で終わる住所地のデータが抽出されます。



### サブクエリ

サブクエリとは`SELECT`文の中に書く`SELECT`文の事です。
サブクエリは色々な句の中で書く事ができます。

`SELECT`の結果と言うのはテーブル(表)になっています。
つまり`SELECT`文の結果をテーブルの代わりに使う事で
テーブルとしての代用や条件指定などをする事ができるということになります。

***FROM句の例***

`FROM`句で`SELECT`文を指定することで
`SELECT`文の結果を`FROM`句のテーブルに代用します。

```sql
SELECT name,old 
FROM (SELECT name , old FROM select_sample1 LIMIT 3);
```

**WHERE句の例**

`IN`句を用いて複数条件として指定する事ができます。

`カラム名 IN (値 , 値 , ・・・)`
`カラム名 IN (サブクエリ)`

```sql
SELECT name,old 
FROM select_sample1
WHERE address IN ('福岡県','北海道');
```
name old
かきくけこ	34
hahihuheho	34
やいゆえよ	40

```sql
SELECT name,old 
FROM select_sample1
WHERE address IN (
  SELECT address 
  FROM select_sample1
  ORDER BY 1
  LIMIT 1
  );
```
name old
かきくけこ	34
hahihuheho	34


### UNION句

`UNION`句はテーブルとテーブルを結合します。
繋げるテーブルのカラム名などが同一である必要があります。
結果を縦に繋げるイメージです。

書き方：

`SELECT文　UNION SELECT文`
`SELECT文　UNION ALL SELECT文`

`UNION`,`UNION ALL`の２種類があり、`UNION`は結果の重複を排除しようとします。
`UNION ALL`は結果をそのまま結合して出力します。

```sql
SELECT name,old 
FROM select_sample1
WHERE address IN ('福岡県','北海道')
UNION ALL
SELECT name,old 
FROM select_sample1
WHERE address IN ('埼玉県','北海道');
```
name old
かきくけこ	34
hahihuheho	34
やいゆえよ	40
かきくけこ	34
hahihuheho	34
わいうえお	31

`UNION ALL`は二つの`SELECT`文の結果を縦につなげて出力します。


### JOIN句

`JOIN`句はテーブルとテーブルを結合するのに用いられます。
イメージとしては横に繋げるイメージです。

結合方法としては２通り、`内部結合`と`外部結合`があり
書き方は4通りあります。
※ただし`SQLite`では`INNER JOIN`,`LEFT OUTER JOIN`しかサポートされていないようです。

| 結合方法 | 句 | 意味 |
|:--|:--|:--|
| 内部結合 | INNER JOIN | 両方に存在するデータで結合する |
| 外部結合 | LEFT OUTER JOIN | 左側に合わせてデータを結合する |
| 外部結合 | RIGHT OUTER JOIN | 右側に合わせてデータを結合する |
| 外部結合 | FULL OUTER JOIN | 両方のテーブルのデータを結合する |

通常業務でも、`INNER JOIN`,`LEFT OUTER JOIN`しか使う機会がないと思うので
この２つの使い方を押さえましょう。

書き方：

`FROM テーブル名1 INNER JOIN テーブル名2 ON 結合条件式`
`FROM テーブル名1 LEFT OUTER JOIN テーブル名2 ON 結合条件式`

別名をつけてテーブル名を短くすることもできます。
`FROM テーブル名1 AS 別名1 INNER JOIN テーブル名2 AS 別名2 ON 結合条件式`
`FROM テーブル名1 AS 別名1 LEFT OUTER JOIN テーブル名2 AS 別名2 ON 結合条件式`

なお`OUTER`と`INNER`は省略可能で`JOIN`のみ書いた場合は`INNER JOIN`になります。
FROMの後にすぐに書いた方が`左側`、JOINに書いた方が`右側`になります。

別のテーブルを用意しましょう。

```sql
BEGIN TRANSACTION;
CREATE TABLE `select_sample2` (
 `address` TEXT,
 `pre_id` TEXT
);
INSERT INTO `select_sample2` VALUES ('東京都','A30');
INSERT INTO `select_sample2` VALUES ('北海道','B20');
COMMIT;
```

テーブルを結合してみましょう。
`SELECT句`ではどちらのテーブルからカラムの値を取るのかを指定できます。
`テーブル名.カラム名`、テーブルの別名を用いた場合は`テーブル別名.カラム名`となります。

```sql
SELECT t1.address , t2.pre_id
FROM select_sample1 as t1
INNER JOIN select_sample2 as t2
ON t1.address = t2.address;
```
address pre_id
東京都	A30
北海道	B20
東京都	A30
東京都	A30
北海道	B20
東京都	A30
東京都	A30
東京都	A30

```sql
SELECT t1.address , t2.pre_id
FROM select_sample1 as t1
LEFT OUTER JOIN select_sample2 as t2
ON t1.address = t2.address;
```
東京都	A30
北海道	B20
東京都	A30
愛知県
東京都	A30
北海道	B20
東京都	A30
福岡県
東京都	A30
埼玉県
東京都	A30
千葉県

`INNER JOIN`,`LEFT OUTER JOIN`では結果が変わってきます。
`INNER JOIN`の場合は双方のテーブルにあるカラムの値に合致した行だけが結果に表れます。
`LEFT OUTER JOIN`の場合は左側のテーブルの結果に右側のテーブルをくっつけます。
双方のカラムに合致する値がないところは`NULL`値として出力されます。


### WITH句

`WITH`句は抽出結果などを一時的なテーブルとして定義し、再利用するための句です。
`JOIN`はすでに存在するテーブルしか用いる事ができませんが
`WITH`句を用いると、SQL文の中でしか使えない一時的なテーブルを作る事ができます。

書き方

```sql
WITH 一時テーブル名 AS(
  SELECT文
)
SELECT文;
```

```sql
WITH 一時テーブル名1 AS(
  SELECT文
) , 一時テーブル名2 AS(
  SELECT文
) 
SELECT文;
```

早速使ってみましょう。

```sql
WITH tmp AS (
  SELECT address , AVG(old) as avg_old FROM select_sample1 GROUP BY 1
)
SELECT t1.address , t1.old , t2.avg_old
FROM select_sample1 as t1
LEFT OUTER JOIN tmp as t2
ON t1.address = t2.address
WHERE t1.old > t2.avg_old;
```

こちらの例でははじめに都道府県別のoldの平均値を求めて
元のテーブルに結合しています。
そこから条件として平均よりも高いoldの行だけを抜き出しています。



### CASE句

`CASE`句は`SELECT`句などで条件分岐を行うために用いる句です。

書き方：

```sql
CASE 
  WHEN 条件式 THEN 値 
  ELSE 値 
  END AS 別名
```

条件式はカラム名と関係演算子などで定義します。
値は文字列や数値、計算値などを指定し
条件に合致した時の値を`THEN`の後に
条件に合致しない場合の値を`ELSE`の後に書きます。

条件を増やしたい場合は`WHEN 条件式 THEN 値`を`ELSE`の前に書きます。

```sql
SELECT name, old , 
CASE WHEN old >50 THEN 'おっさん' ELSE '違う' END AS osnflg
FROM select_sample1
LIMIT 5;
```
name old osnflg
アイウエオ	59	おっさん
かきくけこ	34	違う
sasisuseso	53	おっさん
たちつてと	47	違う
ナニヌネノ	59	おっさん

この場合はoldの値を見て、条件にあった場合の値
条件に合わない場合の値を出し分けしています。


### 関数について

関数は`SELECT`文の様々なところで用いる事ができる機能です。
基本的にはカラムの値などを操作するために用います。

以下のような関数があります。

| 関数名 | 内容 |
|:--|:--|
| abs(カラム名) | 絶対値を返します。 |
| random(*) | 乱数を返します。 |
| round(カラム名 , 桁数) | 少数点以下を桁で四捨五入します。 |
| length(カラム名) | 文字列の長さを返します。 |
| lower(カラム名) | 文字列を全て小文字に変換して返します。 |
| upper(Xカラム名 | 文字列を全て大文字に変換して返します。 |
| substr(カラム名,開始場所,文字数) | 文字列の開始場所,文字数で指定した範囲の文字列を返します。 |

基本的な使い方
`SELECT 関数名(カラム名) AS 別名 , ・・・`


他にもSQLには沢山の関数があるので
調べて使ってみましょう。



## まとめ

SELECT文はSQLの基本かつ深淵です。
書いて実行するを繰り返すことで身につくものなので
色々試してみる事をオススメします。


君がエンジニアになるまであと60日

## 作者の情報

乙pyのHP：
http://www.otupy.net/

Youtube：
https://www.youtube.com/channel/UCaT7xpeq8n1G_HcJKKSOXMw

Twitter：
https://twitter.com/otupython