SQL勉強記録2

https://qiita.com/Ryo_Kitamoto/items/abdd73a76aebf3f2b3e7

上記SQL勉強の続き

## SQL構文集
### 集合演算子(UNION,UNION ALL,EXCEPT,INTERSECT)![スクリーンショット 2023-11-29 12.22.39.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/627182/95213500-f983-82c8-a027-8c87b8f9c774.png)


![スクリーンショット 2023-11-29 12.25.14.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/627182/0d418446-f4ea-7799-2a0a-267e84762a54.png)

・MySQL(Ver8)ではEXCEPTは存在しないためNOT EXISTSで実装する
![スクリーンショット 2023-11-29 12.25.26.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/627182/d3ef2053-bbde-1bab-07c0-5a134e19f60c.png)
![スクリーンショット 2023-11-29 12.25.43.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/627182/1f797575-6bae-74a6-41cf-578a67cbc739.png)

:::note
集合演算子を使う際の注意点
１.各SQLの取得するカラム数を合わせること
2.ORDER BYを利用する老婆愛は１つ目のSQLのカラム名を用いること
:::
UNIONはあるカラムにNULLがあっても重複していると判断してまとめてくれる
```sql
-- UNION(重複を削除)--削除する分処理は少し長い
SELECT * FROM new_students
UNION
SELECT * FROM students
ORDER BY id;

-- UNION ALL(重複を削除しない)
SELECT * FROM new_students
UNION ALL
SELECT * FROM students
ORDER BY id;

-- 同じテーブルに使うことも可能
SELECT * FROM students WHERE id<10
UNION ALL 
SELECT * FROM students WHERE id>250;

-- カラムを合わせるということ
SELECT id,name FROM students WHERE id<10
UNION -- この場合idとageはINT型で同じなので結合できてしまう
SELECT age,name FROM users WHERE id<10
ORDER BY id; -- 一つ目のカラムで並び替える（ageとするとエラーに）

-- こちらはカラムの数が違うので実行できない
SELECT id,name,height FROM students
UNION
SELECT age,name FROM users;
```

### 集計関数(SUM,MIN,MAX,COUNT,AVG)
```sql
-- COUNT
SELECT COUNT(*) FROM customers; -- 何行データが入ってるか(*より主キーで検索するとモレがなく高速で処理できる)
SELECT COUNT(name) FROM customers; -- 列指定（この列に何行入っているか（NULLはカウントしない））

-- MAX,MIN
SELECT MAX(age),MIN(age) FROM users WHERE birth_place="日本";
SELECT MAX(birth_day),MIN(birth_day) FROM users; -- 日付のMAXは最新

-- SUM
SELECT SUM(salary) FROM employees;

-- AVG
SELECT AVG(salary) FROM employees;
-- AVG,NULLの場合の対処
-- 1,2,3,NULLというデータの平均を取りたい時
SELECT AVG(num) FROM tmp_count; -- このままだとNULLは無視され1,2,3の平均の2が算出される
SELECT AVG(COALESCE(num,0)) FROM tmp_count; -- NULLの場合０を入れて計算するため1,2,3,4の平均1.5が算出される
```

### グループごとの集計(GROUP BY)
GROUP BYを使った時によくつまづくのでコツ

:::note
GROUP BYはSELECTする対象で,AVGなどの集計関数を使用しないカラムはGROUP BYに含める
	
:::
```sql
-- GROUP BY

SELECT age,COUNT(*),MAX(birth_day),MIN(birth_day) FROM users WHERE birth_place ="日本"
GROUP BY age -- 年齢でまとめて
ORDER BY COUNT(*); -- 何歳が多いのかを並び替える

SELECT department ,SUM(salary),FLOOR(AVG(salary)),MIN(salary) FROM employees
WHERE id>40
GROUP BY department ;

-- CASEを用いたGROUP BY	
SELECT 
	CASE -- こちらのCASE文はbirth_placeによって新しいカテゴリを作成する
		WHEN birth_place ="日本" THEN '日本人'
		ELSE "その他"
	END AS "国籍",
	COUNT(*), -- 新たに作られたカテゴリの数を数える
	MAX(age) -- そのカテゴリの中での最年長
FROM 
	users
GROUP BY
	CASE -- こちらのCASE文はその作られたカテゴリに基づいてGROUP　BYを実行してまとめる
		WHEN birth_place ="日本" THEN '日本人'
		ELSE "その他"
	END;
	
SELECT 
	CASE 
		WHEN name IN ("香川県","愛媛県","徳島県","高知県") THEN "四国" -- とってきてエイリアスをつけるイメージ
		ELSE "その他"	
	END AS "地域名",
	COUNT(*)
FROM
	prefectures
GROUP BY
CASE 
	WHEN name IN ("香川県","愛媛県","徳島県","高知県") THEN "四国" -- そのエイリアスでまとめる
	ELSE "その他"	
END;

SELECT -- 同じ年齢が何人いるのかの集計と数値によって新たなカラムを作って分類を表示
	age,
	CASE 
		WHEN age<20 THEN "未成年"
		ELSE "成人"
	END AS "分類",
	COUNT(*)
FROM users
GROUP BY age;

```

### グループ化して集計後のしぼりこみ（HAVING）
WHEREと似ているけど順番が違う。
WHERE→GROUP BY→HAVINGの順に実行される。
書き方はGROUP BYの後でORDER BYは最後

```sql
-- HAVING
SELECT department,AVG(salary) FROM employees GROUP BY department  -- 絞り込んだ後に
HAVING AVG(salary) >3980000; -- 集計後に対して条件をつける

SELECT birth_place,age,COUNT(*) FROM users
GROUP BY birth_place ,age
HAVING COUNT(*)>1
ORDER BY birth_place ,COUNT(*) DESC, age DESC; -- ORDER BY,LIMITは最後
```

### テーブルに別名をつける
```sql
-- テーブルに別名をつける(テーブル名 AS エイリアス)
SELECT 
	cs.name, -- 使う時はエイリアス名.カラム名
	cs.id
FROM
	classes AS cs; -- 厳密にはASがなくても可能だがわかりやすいためこちらを採用したい
```

### 副問い合わせ
:::note
SELECT文をFROMやWHEREの中に記述してSELECTの結果を別の処理に用いるSQL文のこと
メインのSELECT文をメインクエリ（主問い合わせ）
メイン以外のSELECT文をサブクエリ（副問い合わせ）という

使いどきは何か取り出したい値や行が２つ以上のクエリ処理を必要とするときかな
例：ageの平均年齢をだしたあと、それより低いレコードを取り出したいとき
①ageの平均年齢をだす　②それより低いレコードを出す　で2回のクエリを必要としている
:::

```sql
/*　副問い合わせは先にサブクエリから組み立てる方がわかりやすいかも*/
-- 部署一覧
SELECT * FROM departments;

/* INで副問い合わせする */
-- →INで普通に絞り込む場合
SELECT * FROM employees WHERE department_id IN (1,2); -- しかしこれでは1と2を知っていなくてはいけないし、何を取り出したのかわかりにくい
-- 別テーブルにある経営企画部と営業部のidが知りたい
SELECT id FROM departments WHERE name IN ("経営企画部","営業部"); -- >>1,2が取得できる
-- じゃあ(1,2)の代わりにSELECT文をいれてしまおうというのが副問い合わせ(NOT INも可)
SELECT * FROM employees WHERE department_id 
IN(SELECT id FROM departments WHERE name IN ("経営企画部","営業部")); -- 無事取り出すことができたし、何を取り出しているのかわかりやすい


SELECT * FROM students;
SELECT * FROM users;

-- 複数カラムを取り出すことも可
SELECT * FROM students
WHERE (first_name,last_name)
IN(SELECT first_name,last_name FROM users);

-- 集計関数を用いて一つの値を取得する副問い合わせ
SELECT MAX(age) FROM employees; -- これをサブクエリとすると
SELECT * FROM employees WHERE age=(SELECT MAX(age) FROM employees); -- こちら
SELECT * FROM employees ORDER BY age DESC LIMIT 1; -- でもこちらでも同じように取り出せる

SELECT * FROM employees WHERE age<(SELECT AVG(age) FROM employees); -- ageの平均より低い人を取得しようとするとクエリが２ついるからこちらはサブクエリが必要かも

```
```sql
-- FROMで副問い合わせを用いる
-- FROMで用いる時はidとそれごとグループをまとめた値など「テーブル状」になっているデータを持ってくるときに使いやすい？
SELECT
MAX(avg_age) AS '部署ごとの平均年齢の最大',
FROM(
SELECT department_id ,AVG(age) AS avg_age FROM employees GROUP BY department_id) AS tmp_emp; -- エイリアスがないとエラーになる

-- 年代の集計
SELECT FLOOR(age/10)*10 AS "年代",COUNT(*) FROM employees
GROUP BY FLOOR(age/10)*10; -- 一度10で割って小数点以下り捨てて10倍すると年代に

-- 上記の年齢の集計をサブクエリに使うと
SELECT 
MAX(age_count),
MIN(age_count)
FROM(
SELECT FLOOR(age/10)*10,COUNT(*) AS age_count FROM employees
GROUP BY FLOOR(age/10)*10
) AS age_summary;

-- メインクエリのSELECTに含める(取る値は一つにする)
SELECT * FROM customers;
SELECT * FROM orders; -- 要はordersテーブル内のcustomer_idと紐付けたい

SELECT
cs.id
cs.first_name,
cs.last_name
FROM customers AS cs
WHERE id<10; -- こちらの至ってシンプルなクエリのSELECT行にサブクエリを追加すると↓


SELECT
cs.id, -- エイリアスをつけたcustomersテーブル
cs.first_name,
cs.last_name,
(
SELECT MAX(order_date) FROM orders WHERE cs.id = orders.customer_id 
) AS "最新注文履歴", -- 取得する値は一つにすること
(
SELECT MIN(order_date) FROM orders WHERE cs.id = orders.customer_id 
) AS "古い注文履歴",
(
SELECT SUM(order_amount*order_price) FROM orders WHERE cs.id = orders.customer_id 
) AS "これまでの注文価格"
FROM customers AS cs
WHERE id<10;

```
```sql
-- CASEの中で使うSELECT副問い合わせ
SELECT id FROM departments WHERE name="経営企画部"; -- 今回はこちらのidを副問い合わせで使いたい

-- それがこちら要はempテーブルのidを使ってdepartmentsテーブルの値に応じてカラムを追加する
-- departmentテーブルを使わずともempテーブルだけで表示できそうな気もするがSQLを見るだけでわかる点はいいかも
SELECT 
	emp.*,
	CASE
		WHEN emp.department_id = (SELECT id FROM departments WHERE name="経営企画部")
		THEN "経営層"
		ELSE "その他"
	END AS "役割"
FROM employees AS emp;

/* 上記はこれとおなじだけどdepartment_id=1ってなに？とはなる
SELECT 
	emp.*,
	CASE
		WHEN emp.department_id=1 THEN "経営層"
		ELSE "その他"
	END AS "役割"
FROM employees AS emp;
*/

-- サブクエリから一つ一つ組み立てればこのように長いコードでも作成可能(従業員ごとのsalaryをまとめ、それがsalary全体平均で高い人のidを抽出し、当てはまる人は「まる」というカラムを追加して表示する)
SELECT
	emp.*,
	CASE 
		WHEN emp.id IN(
			SELECT employee_id FROM salaries GROUP BY employee_id HAVING AVG(payment)>(SELECT AVG(payment) FROM salaries))
			THEN "まる"
			ELSE "ばつ"
	END AS "給料が平均より高いか"
FROM employees AS emp;
```

### INSERT INTO SELECT,CREATE TABLE SELECT
SELECTしたテーブルを別のテーブルに挿入したり作成したりする
```sql
-- CREATE SELECT INSERT 

-- studentsから新たにtmp_studentsというテーブルを作成するが
CREATE TABLE tmp_students
SELECT * FROM students;

SELECT * FROM tmp_students;

DESCRIBE students; -- PRIMARY KEY あり
DESCRIBE tmp_students; -- PRIMARY KEYなどの設定はついてこない

DROP TABLE tmp_students;

CREATE TABLE tmp_students
SELECT * FROM students WHERE id <10; -- 絞り込んである条件だけのテーブルを作成

-- usersテーブルを合体させてみたいがgradeカラムが足りないので追加する
SELECT *,2 AS grade FROM users;
-- id1~10はもうはいっているのでそれを避けてINSERTすると
INSERT INTO tmp_students 
SELECT id+9 AS id,first_name,last_name,2 AS grade FROM users;


-- UNIONでくっつけた結果のテーブルを作成(UNIONなので重複は削除)
CREATE TABLE names
SELECT first_name,last_name FROM students
UNION
SELECT first_name,last_name FROM employees
UNION
SELECT first_name,last_name FROM customers;

SELECT * FROM names;
```

### EXISTSについて(ほぼIN)
初心者がつまづく最初の関門でもあるらしいが、処理速度がはやいらしいので使えるとよりよい。
EXISTSはINでほぼ代替できるのでパフォーマンス的な面から見るとEXISTSに置き換える方がいいかも
※後述するがNOT INは偽を返すのに対してNOT EXISTSは当てはまらないものを返すのでNULLのときの挙動が若干違うのでそこだけ注意
```sql
SELECT * FROM departments;
SELECT * FROM employees;

-- EXISTS(departmentsテーブルのidに存在するidだけemployeesテーブルから取り出す。つまり1~4だけ取り出す。INみたいな使い方ができる)
SELECT * FROM employees AS em
WHERE EXISTS(
SELECT * FROM departments AS dt
WHERE em.department_id =dt.id
); /* ここでEXISTSの中身である
SELECT * FROM departments AS dtは*ではなくてidでも１でもなんでもいい。
わかりやすく1とする場合が多いらしい
*
**/

-- INと組み合わせてみる
SELECT * FROM employees;
-- nameが営業部か開発部のもの＆employeesテーブルのdepartment_idに含まれるものだけ取るサブクエリ
-- SELECT * FROM departments AS dt WHERE dt.name IN('営業部','開発部') AND dt.id=em.department_id

-- 組み合わせると
SELECT * FROM employees AS em
WHERE EXISTS(
	SELECT 1 FROM departments AS dt WHERE dt.name IN('営業部','開発部') AND dt.id=em.department_id
); -- SELECT 1とすると当てはまるものすべてのカラムとってきてきているというがわかりやすいからそうするべし
```
EXISTS続き
```sql
SELECT * FROM customers;
SELECT * FROM orders;

-- orderテーブルのorder_dateカラムに'2020-12-31'が存在する行＆idが一致するものだけ取り出す
SELECT * FROM customers AS ct
WHERE EXISTS(
	SELECT 1 FROM orders AS od WHERE ct.id=od.customer_id AND od.order_date='2020-12-31'
);

-- NOT EXISTS
-- 逆に上記に一致しなかったものすべて返す
SELECT * FROM customers AS ct
WHERE NOT EXISTS(
	SELECT 1 FROM orders AS od WHERE ct.id=od.customer_id AND od.order_date='2020-12-31'
);
-- 同じテーブルでEXISTSを使う
-- manager_idが存在する人だけ取り出す(ややこしくてよくわかってない)
SELECT * FROM employees;

SELECT * FROM employees AS em1
WHERE EXISTS(
	SELECT 1 FROM employees AS em2 WHERE em2.manager_id = em1.id
); -- 誰かのmanager_idに設定されている人を取り出す

-- 
SELECT * FROM employees AS em1
WHERE EXISTS(
	SELECT 1 FROM employees AS em2 WHERE em2.id = em1.manager_id 
); -- manager_idが設定されている人だけ（値がある人）を取り出す
```

### EXISTSでもNULLには注意
EXISTSでは「値が返される」レコードのみ取得するのでNULLのものは返されない
例えばNULL＝NULLになるものなんかはレコードがあるのに返されないといったことになる。

ただしNOT INは偽を返すのに対してNOT EXISTSは当てはまらないものを返すのでNULLのときの挙動が若干違うので注意。NOT INで取り出せなかったNULLのレコードでも、NOT EXISTSではEXISTSに当てはまらないすべてのレコードを返すので必然的にNULLも取り出せることになる。
ただこういったNULLをケアした処理がめんどうなため開発現場ではNULLを使わない動きが広まっている

```sql
-- NULLが存在する時のEXISTSの挙動を確認
SELECT * FROM customers;
SELECT * FROM customers_2;

SELECT * FROM customers AS c1
WHERE EXISTS(
	SELECT * FROM customers_2 AS c2 WHERE c1.first_name =c2.first_name AND c1.last_name = c2.last_name
); -- 名前と苗字だけに絞ると取り出せる

SELECT * FROM customers AS c1
WHERE EXISTS(
	SELECT * FROM customers_2 AS c2 WHERE c1.first_name =c2.first_name AND c1.last_name = c2.last_name
	AND c1.phone_number =c2.phone_number
); -- 名前、苗字、番号すべて一致するものを取り出したいが番号がNULL=NULLになる人はEXISTSに当てはまらないので取り出せなくなる

-- NULL=NULLになるレコードをEXISTSでも取り出したい場合はどうするか
SELECT * FROM customers AS c1
WHERE EXISTS(
	SELECT * FROM customers_2 AS c2 WHERE c1.first_name =c2.first_name AND c1.last_name = c2.last_name
	AND (c1.phone_number =c2.phone_number OR (c1.phone_number IS NULL AND c2.phone_number IS NULL))
); -- ややこしいがORとANDと()をうまく使ってこのように制御


-- NOT EXISTSの挙動を確認
SELECT * FROM customers AS c1
WHERE NOT EXISTS(
	SELECT * FROM customers_2 AS c2 WHERE c1.first_name =c2.first_name AND c1.last_name=c2.last_name AND c1.phone_number=c2.phone_number
); -- EXISTSで取り出せなかったレコードすべてを返す

-- INとNOT　INの挙動を確認
SELECT * FROM customers AS c1
WHERE(first_name,last_name,phone_number) IN(
	SELECT first_name,last_name,phone_number FROM customers_2 AS c2);
	-- idが1の人はINやNOTであれば真でも偽でもなくNULLになるのでとりだせなくなる。かなりややこしくまだよくわかってない
	-- INは全部が一致した人が真
```

### 差集合と積集合をNOT EXISTSとEXISTSで実現する
差集合とはAにあるレコードからBのレコードを除いたレコード。
NOT EXISTSでWHERE Aの全カラム＝Bの全カラムとすることで一致するものだけ取り除ける

積集合とはAとBにある重複したレコードのこと。
同じようにEXSITSで実現

:::note warn
どちらもNULLケアをしないといけないことに注意
:::

```sql
SELECT * FROM customers;
SELECT * FROM customers_2;

-- 差集合をNOT　EXISTSで実現(EXCEPT)（NULLケアは忘れない）
SELECT * FROM customers AS c1
WHERE NOT EXISTS 
	(SELECT * FROM customers_2 AS c2
	WHERE 
		c1.id=c2.id AND 
		c1.first_name=c2.first_name AND 
		c1.last_name=c2.last_name AND 
		(c1.phone_number=c2.phone_number OR(c1.phone_number IS NULL AND c2.phone_number IS NULL)) AND 
		c1.age=c2.age
);

-- 積集合(INTERSECT)
SELECT * FROM customers AS c1
WHERE EXISTS 
	(SELECT * FROM customers_2 AS c2
	WHERE 
		c1.id=c2.id AND 
		c1.first_name=c2.first_name AND 
		c1.last_name=c2.last_name AND 
		(c1.phone_number=c2.phone_number OR(c1.phone_number IS NULL AND c2.phone_number IS NULL)) AND 
		c1.age=c2.age
);

```