SQLの書き方

## はじめに
　SQL文の書き方についてまとめた記事。
 
　内容は以下である
　&emsp;1. 基礎構文
　&emsp;2. 詳細な指定をするための各句の役割

:::note warn
　DBMSの種類によってSQLの構文や挙動が異なるため、
　この記事に記載したものが必ずしも正しいものでない。
:::

<br>





<br>

## 用語
|||
|:--:|:--:|
|**文**|SQLの実行単位であり、複数の句で構成|
|**句**|キーワードと式(定数、条件、列名などのまとまり)で構成|
|**キーワード**|SELECT, WHEREなどのSQLコマンド|

<br>





<br>

## 記述順序
　各句は、上から順番に記述していく。

||
|:---:|
|**SELECT**|
|**FROM**|
|**JOIN ON**|
|**WHERE**|
|**GROUP BY**|
|**HAVING**|
|**ORDER BY**|
|**LIMIT**|



## 実行順序
　各句は、上から順番に実行される。

||
|:---:|
|**FROM**|
|**JOIN ON**|
|**WHERE**|
|**GROUP BY**|
|**HAVING**|
|**SELECT**|
|**UNION**|
|**DISTINCT**|
|**ORDER BY**|
|**LIMIT**|

<br>





<br>
 
## テーブル操作
　テーブル構造の定義や変更に関するSQL文。
### テーブル作成
:::note info
<font color="blue">**CREATE TABLE**</font> テーブル名 (カラム名1　データ型　制約,
　　　　　　　　　　　　 [カラム名1　データ型　制約,...], 
　　　　　　　　　　　　 PRIMARY KEY ());
:::
|データ型|役割|
|:---:|:---:|
|INTEGER|整数値|
|CHER()|固定長文字列 <br> (文字数に満たない場合、半角スペースで補完する)|
|VARCHAR()|可変長文字列|
|DATA|日付|

|制約|役割|
|:---:|:---:|
|NOT NULL|値の未定義を許可しない|
|DEFAULT|デフォルト値を設定する|

<br>

### テーブル削除
:::note info
<font color="blue">**DROP TABLE**</font> テーブル名;
:::

<br>

### カラムの追加/削除
:::note info
追加：<font color="blue">**ALTER TABLE**</font> テーブル名 <font color="blue">**ADD COLUMN**</font> カラム名 データ型 制約 [挿入位置];
:::

・挿入位置指定は、FIRSTやAFTER カラム名など。

:::note info
削除：<font color="blue">**ALTER TABLE**</font> テーブル名 <font color="blue">**DROP COLUMN**</font> カラム名;
:::

<br>

### テーブル名変更
:::note info
<font color="blue">**ALTER TABLE**</font> テーブル名 <font color="blue">**RENAME TO**</font> 変更後テーブル名;
:::

<br>

### カラム名変更
:::note info
<font color="blue">**ALTER TABLE**</font> テーブル名 <font color="blue">**RENAME COLUMN**</font> 列名 <font color="blue">**TO**</font> 変更後カラム名;
:::

<br>

### カラム定義変更
:::note info
<font color="blue">**ALTER TABLE**</font> テーブル名 <font color="blue">**MODIFY COLUMN**</font> 列名 変更後データ型 変更後制約;
:::

<br>

### レコード全削除
:::note info
<font color="blue">**TRUNCATE TABLE**</font> テーブル名;
:::

・DELETE FROMとの違いは次の2点、
　条件(WHERE句)を指定できない。
　DDLであるため、DBMSによってロールバックすることができない。

<br>





<br>

## 基本レコード操作
　レコードに対する4つの基本的なSQL文。
### 抽出　
:::note info
<font color="blue">**SELECT**</font> カラム名 <font color="blue">**FROM**</font> テーブル名;
:::

### 更新
:::note info
<font color="blue">**UPDATE**</font> テーブル名 <font color="blue">**SET**</font> カラム名1 = 値1[, カラム名2 = 値2...]　[WHERE 条件];
:::

　条件を指定しない場合、全レコードが対象になる。

### 挿入
:::note info
<font color="blue">**INSERT INTO**</font> テーブル名 (カラム名1[, カラム名2...]) <font color="blue">**VALUES**</font> (値1[, 値2...]);
:::

　テーブルにあるカラムの内、一部しか指定しない場合、
　指定されていないカラムにはデフォルト値が設定される。

### 削除
:::note info
<font color="blue">**DELETE FROM**</font> テーブル名 [WHERE 条件式];
:::

　条件を指定しない場合、全レコードが削除される。
<br>





<br>

## 詳細操作
　各句の役割や処理フローについて触れる。
### WHERE句
　テーブルのレコードを1行ずつ条件に合致するか評価していき、
　trueとなる行を選択する。
 
　WHERE句では集約関数を使用することはできない。

:::note warn
集約関数は使用不可：
　WHERE句の役割は抽出するレコードを選定する句である
　一方、集約関数は抽出したレコードに対する処理である。
　そのため、WHERE句では使用できないが、WHERE句以降に実行される
　GROUP BY、HAVING、SELECT句で使用することができる。
　(<INS>あくまで個人的な推測なので真実ではない</INS>)
:::

<br>

### GROUP BY句
　指定したカラムが持つ値毎に、レコードをグループ分けする。
 
　GROUP BY句を用いた場合、SELECT句に指定できるものはGROUP BY句に指定したカラム、
　もしくは集約関数である。

:::note warn
理由：
　GROUP BY句は指定したカラムの値毎に該当する複数レコードを1つにまとめる。
　例えば次のようなテーブルが存在するとして、
 
|カラム1 |カラム2  |
|---|---|
|1  |100  |
|1  |110  |
|2  |200  |
|2  |210  |

　上のテーブルに対してGROUP BY カラム1とすると下記の様になる。

|カラム1 |カラム2  |
|---|---|
|1  |100, 110  |
|2  |200, 210  |

　この状態でカラム2をSELECT句で指定すると、1つのレコードのカラムに値が複数
　存在している(100, 110など)ため、どの値を抽出すればよいのか判断できない。
　集約関数においては、全ての値を対象に演算を行うため複数値が存在して
　いても有効である。
:::

<br>

### 集約関数
　テーブルの複数行に対して、操作や計算を行う。
　集約関数を指定することができるのは、SELECT句、HAVING句、ORDER BY句のみである。

 |関数名|役割|
|:---:|:---:|
|COUNT()|行数を数える|
|SUM()|合計値を算出|
|AVG()|平均値を算出|
|MAX()|最大値を取得|
|MIN()|最小値を取得|

:::note warn
NULLの扱い：
　集約関数は、NULL値を除外して処理するが、
　COUNT(*)の場合、NULL値を含めてカウントを行う。
:::

<br>

### HAVING句
　GROUP BY句の結果に対して、抽出条件を指定する。
　指定できるものは、GROUP BY句で指定したカラム、もしくは集約関数の比較式。

<br>

### ORDER BY句
　抽出結果に対してソートを行う。
　並び順は、デフォルトでは昇順(ASC)に設定されていて、降順はDESCを指定する。
　複数カラムを指定した場合、左に記述したカラムから順に整列される。

```sql:例
  SELECT * FROM users WHERE age > 20 ORDER BY name;      /*ASC順*/
  SELECT * FROM users WHERE price > 20 ORDER BY age DESC;
  SELECT * FROM users WHERE price > 20 ORDER BY age DESC, name ASC; /*複数指定*/
```



<br>

### INNER JOIN句
　2つのテーブルを結合する。
　INNER JOIN句でテーブル名を指定し、ON句で結合条件を指定する。
 
　結合条件に指定したカラムの値の内、テーブル間で一致する値を持つレコードのみ
　を結合させる。

```sql:例
  SELECT *
  FROM book_sales
    INNER JOIN stores
      ON book_sales.store_id = stores.id  /*book_salesとstoresのどちらかにしかない*/
  WHERE price >= 3500                     /*カラム値を持つレコードは結合されない*/
  ;
```


<br>

### OUTER JOIN句
　2つのテーブルを結合する。
　OUTER JOIN句でテーブル名を指定し、ON句で結合条件を指定する。
 
　条件に指定したカラムの値の内、一方のテーブル(結合元)に存在する全ての
　レコードを結合させる。
　もう一方のテーブル(結合先)に値が存在しない場合、NULLが格納される。
 
　LEFT OUTER JOINの場合、結合元はfrom句で指定したテーブルになる。
　RIGHT OUTER JOINの場合、結合元はRIGHT OUTER JOIN句で指定したテーブルになる。

```sql:例
  SELECT *
  FROM book_sales
    LEFT OUTER JOIN stores                  /*LEFTの場合、book_salesを基準にして*/
      ON book_sales.store_id = stores.id    /*storesを結合させる*/
  WHERE price >= 3500
  ;
```

<br>

### UNION/UNION ALL句
　2つのSELECT文の結果を統合する。
 
　UNIONはSELECT結果のカラムが、同じ数でかつ同じデータ型でなければいけない。
　UNIONは重複行を1行にまとめる、UNION ALLはそれをしない。

```sql:例
  SELECT id, name, Department FROM users1
  UNION
  SELECT id, name, Department FROM users2;
```

<br>

### サブクエリ
　入れ子になったクエリの内の、内側のクエリのこと。
　外側のクエリ(メインクエリ)の色々な句に記述することができる。
　サブクエリは、カッコ()内に記述する。 
 
　実行順はメインクエリよりも、サブクエリが先に実行される。

```sql:例
  SELECT id, name, price
  FROM book_sales
  WHERE price IN (     /*サブクエリの返り値が複数行になる場合、どの値と比較するか*/
    SELECT MAX(price)  /*わからなくなるため、複数値扱えるINやANY句などが必要になる*/
    FROM book_sales
    GROUP BY store_id
  );
```

### 相関サブクエリ
　サブクエリ内でメインクエリのテーブルを参照しているサブクエリ。
 
　相関サブクエリはメインクエリのテーブルから1行参照し、それを用いて
　自身の処理を行いメインクエリに結果を渡す。それを全ての行分繰り返す。

```sql:例
  SELECT *
  FROM book_sales AS mainquery_t
  WHERE price = (
    SELECT MAX(price)
    FROM book_sales AS subquery_t
    WHERE mainquery_t.store_id = subquery_t.store_id
  );
  /*mainquery_tから1行参照しサブクエリの処理が行われ、結果をメインクエリのWHERE句で比較。
  その後、同じようにmainquery_tから次の1行参照し･･･を繰り返していく。*/
```

<br>

### DISTINCT句
　SELECT句で抽出した結果から重複行を削除する。
　複数カラムを指定した場合、全てのカラムの値が一致するレコードを削除する。
 
```sql:例
  SELECT DISTINCT department FROM users;
```

<br>
 
### LIMIT句
　抽出した結果の内、先頭から指定した行数分だけ取得する。
　OFFSET句で、先頭から除外する行数を指定できる。

```sql:例
  SELECT * FROM users LIMIT 10;           /*先頭から1～10行目分取得*/
  SELECT * FROM users LIMIT 10 OFFSET 10; /*先頭から11～20行目分取得*/
```

<br>

### EXISTS句
　条件に合致するレコードが存在するか確認し、真偽値を返す。
　サブクエリと併用して使用される。
 
　条件に1つでも当てはまるレコードがあればtrue、なければfalseを返す。

<br>

### IN句
　指定した値もしくはサブクエリの結果が、カラムに含まれているかを調べる。
　返り値はレコード。

```sql:例
  SELECT * FROM users WHERE name IN('a', 'b');
  SELECT * FROM users WHERE name NOT IN('a', 'b');   /*NOT INで否定を表す*/
  SELECT * FROM users WHERE name IN(select name from users where age >= 20);  /*サブクエリ*/
```

<br>

### AS句
　テーブルやカラムに別名を付ける。

<br>

### LIKE句
　カラムに対して文字列検索を行う。
|ワイルドカード|役割|
|:---:|:---:|
|%|0文字以上の任意の文字列|
|_|任意の1文字|

```sql:例
  SELECT * FROM users WHERE name LIKE '%太郎%';
```

<br>

### 演算子
|比較演算子|役割|
|:---:|:---:|
|=|	左右が等しい|
|<>, !=|	左右が等しくない|
|<|	左が右より小さい|
|<=|	左が右以下|
|>|	左が右より大きい|
|>=|	左が右以上|

|論理演算子|役割|
|:---:|:---:|
|AND|	かつ|
|OR|	または|
|NOT|	否定|

:::note warn
優先順位：
　論理演算子には処理される順番が決まっていて、
　NOT、AND、ORの順で演算される。
:::

```sql:例
SELECT * FROM users WHERE NOT age = 20;  /*NOTは先頭に書く*/
SELECT * FROM books WHERE (age = 21 OR age = 22) AND gender = male;
/*()内に書かれた処理が一番優先されるため、ANDより先に実行される*/
```