SQL 基礎備忘録

## DISTINCT

SQLでみる`DISTINCT`
なんとなく使っていたが、どういった動作をするのか
どういった時に使うのかを説明

##### `DISTINCT`
重複したレコードの省いて結果を得たい時に使用する。
```
SELECT DISTINCT category 
 FROM examples;

 category
 ---------
 test
 test1
 test2
 ```
#### 集約関数に対して`DISTINCT`を使用する
exampelsテーブルのcategoryのカラムには
test
test1
test2
と3種類のデータがあり、それぞれデータが2件ずつあったとする。
その際、`COUNT`関数を使用しても、3 * 2の6件だが、categoryカラムの種類の個数を求める際は`DISTINCT`を使用できる。
```
/* COUNT関数の引数としてDISTINCTを使用 */
SELECT COUNT(DISTINCT category) AS category FROM examples;

category
---------
    3

/* DISTINCTはCOUNT以外の集約関数でも使用可能 */
SELECT SUM(DISTINCT stock) AS stock ,
       COUNT(category) AS category
           FROM examples;
           
 total |　category
-------+-----------
   10  |     6
```


 ### DISTINCTを使用する際の注意点

* `DISTINCT`はNULLも1種類のデータとして取り扱われる
NULLのデータはいらないといった時はWHERE句を使用する。
* 複数のカラムデータを取得する際に1つのカラムの情報だけをDISTINCTを使用するといったことはできない

全てにおいて言えることだが、使用する際はなぜ`DISTINCT`を使用しているのかを明確にしたい。

### `GROUP BY` と`DISTINCT`の使い分け
例えば
```
SELECT category FROM examples GROUP BY category;
SELECT DISTINCT category FROM examples;

 category
 ---------
 test
 test1
 test2
```
上記の結果は同じです。
では、どちらを使うべきなのかを考えてみます。
`DISTINCT`と`GROUP BY`は実行速度も同等で、速度的な判断はできません。
ということは、要件にあった方を選択するべきということです。
<b>重複結果を除外したい</b> : `DISTINCT`
<b>集約した結果を求めたい</b> : `GROUP BY`
これらを意識して、適切な方を使うとようにすることを心がけましょう。

## AND ORの罠
WHERE句を利用してデータの検索条件を設定している際によく使うのが`AND` `OR` 
特に今回は初心者が一回は必ず引っかかる罠について

例えば　examplesというテーブルのcategoryが'test'かつcreate'2024-10-06'もしくは'2024-10-05'のデータを取得したい場合、`AND` `OR`の優先順位がわからないと以下のクエリになりがちです。
```
SELECT * FROM examples
    WHERE category = 'test'
    AND create = '2024-10-06'
    OR create = '2024-10-5';
```
上記のようなクエリで実行すると、
```
 id | category |  create  
---------------------------
1   | test     | 2024-10-6
3   | test2    | 2024-10-5
4   | test3    | 2024-10-6
```

期待したデータではない結果が返ってきてしまう。
なぜcategoryが'test'以外のデータがあるのか最初は意味がわからなかった。

#### 理由
`OR`演算子よりも`AND`演算子を優先するから。

条件式は
[category = 'test' AND create = '2024-10-06']
OR create = '2024-10-5'
といった形で解釈されます。
つまり
* categoryが'test'かつcreate'2024-10-06'
または
* createが'2024-10-05'
のデータを取得してしまいます。

#### 解決法
`AND`演算子よりも`OR`演算子を優先したいときは()を使う
```
SELECT * FROM examples
    WHERE category = 'test'
    AND (create = '2024-10-06'
      OR create = '2024-10-5');
```
上記で実行した結果
```
 id | category |  create  
---------------------------
1   | test     | 2024-10-6
```
取得したい条件のデータの結果を得られる。
`WHERE`句でよく使う`AND` `OR`の優先順位は間違えやすい罠になりがちなので気を付けるようにしましょう。


## 集約関数について

SQLの集約関数

* COUNT : レコード数の合算
* SUM   : 数値列のデータ合計
* AVG   : 数値列のデータ平均
* MAX   : データの最大値
* MIN   : データの最小値

```
/* テーブルに保存されたレコード数の合算 */
SELECT COUNT(*) FROM examples;

 count
-------
   10 

/* カラム単位で指定しNULLを除外する */
SELECT COUNT(category) FROM examples;

 count
-------
   7

/* それぞれのデータを一度にレコード数を表示 */
SELECT COUNT(*) AS total, 
       COUNT(category) AS category 
    FROM examples;

 total |　category
-------+-----------
   10  |     7
```

```
/* SUM関数は*を引数にすることはできない */
/* NULLは除外される */
SELECT SUM(stock) FROM examples;

  sum
-------
   20

/* COUNT同様、別カラムを同時に結果を求める事が可能 */
SELECT SUM(stock) AS stock
       SUM(number) AS number
    FROM examples;

 stock |　number
-------+-----------
   20  |    10
```

## SELECT句で処理される順序について

SELECT句で`WHERE`や`ORDER BY`、`GROUP BY`といった処理を実施した際に、順番がわからなくなってしまうことがあるかもしれません。
その時は、処理される順序がわかっていることで大抵解決します。
#### SELECT句内部的な実行順
`FROM` → `WHERE` → `GROUP BY` → `HAVING` → `SELECT` → `ORDER BY`
上記の順番で実行されます。
`ORDER BY`でなぜ`AS`の別名が使用できるのか？
`SELECT`実行後だからという説明に納得できると思います。決してそういうものといった理由ではなく、順番を知ることで納得できると思います。

## 他のテーブルからデータをコピーする方法

テーブルの定義変更やバックアップなどをする際、データを一時的に他のテーブルにコピーを取ることがあるかもしれません。
その際には`INSERT`と`SELECT`を併用することが可能です。
```
INSERT INTO example_copys 
    (category, stock, price, create)
    SELECT category, stock, price, create FROM example;
```
もちろん、`WHERE`や`GROUP BY`なども利用できます。
必要なデータがわかっているならそのデータのみなどの指定をする事が可能。

## データの削除について

DBのデータを削除と言っても、`DROP`と`DELETE`があります。
`DROP`はテーブルの削除
`DELETE`はレコードの削除
それぞれ動作は違います。

またそれらとは別に`TRANCATE`というコマンドがあります。
`TRANCATE`はidなどの`AUTO INCREMENT(自動加算)`で管理しているカラムなどのデータもリセットされます。
`DELETE`よりも高速に動作するため、単純にテーブルのレコードを全件削除したい場合は`TRANCATE`を使用すると良いでしょう。

いずれにせよ、間違って実行しデータを消してしまった場合は復旧に時間は掛かります。
```
/* テーブルの削除 */
DROP TABLE examples;

/* レコードの削除
　　条件を指定して削除することも可能
*/
DELETE FROM examples WHERE stock > 0;

/* レコードを全て削除したい場合 */
TRANCATE examples;
```

## サブクエリについて
案件で相関サブクエリを使用することがあるので備忘として

```
/* エラーになるサブクエリ */
SELECT id, category,price 
    FROM examples
    WHERE price > (SELECT AVG(price)
                    FROM examples
                    GROUP BY category);
```
上記でエラーとなる原因は、WHERE句でサブクエリを使用する際は、結果が必ず1行である必要があるためです。

### エラーを解決する方法は、相関サブクエリを使用する
```
/* 相関サブクエリ */
SELECT id, category,price 
    FROM examples AS e1
    WHERE price > (SELECT AVG(price)
                        FROM examples AS e2
                        WHERE e1.category = e2.category
                    GROUP BY category);
```
相関サブクエリは、テーブル全体ではなく、テーブルの一部のレコードの分類に限定した比較を行う際に使用する。
注意点としては、結合の条件はサブクエリ内に書くこと。
当たり前だが、例で出したサブクエリのe2はサブクエリないでしか使用できない。
内から外のスコープは見えるが、外から内のスコープは見えない。

## LIKEを使用したあいまい検索
```
/* 前方一致 */
SELECT * FROM examples WHERE memo LIKE 'test%';
testから始まる全ての文字列を対象とした検索

/* 後方一致 */
SELECT * FROM examples WHERE memo LIKE ’%test’;
testで終わる全ての文字列を対象とした検索

/* 部分一致 */
SELECT * FROM examples WHERE memo LIKE '%test%';
testを含んだ全ての文字列を含むレコードを対象とした検索

```

## BETWEENを使用した範囲検索
##### ※結構使うので重要
```
SELECT * FROM examples
    WHERE created BETWEEN 2024-10-14 00:00:00 
        AND 2024-10-15 00:00:00;
```
上記は、2024年10月14日の0時から2024年10月15日0時までのレコードを取得する条件です。

数値同士の比較としてBETWEENを使用したい場合は、比較して範囲を設定できます。
```
SELECT * FROM examples
    WHERE price >= 1000 AND
        price <= 2000;
```
上記は、priceが1000から2000までのレコードを対象とした範囲検索です。