[後半]MySQLのより複雑な操作・演習までやってみた。[Paiza]

前回の続き～～～～🥺
https://zenn.dev/takeda_kaho/articles/7e47d315f38780

後半で使うテーブルデータ

-- 定義部分
CREATE TABLE Products
(
id INTEGER NOT NULL,
name CHAR(32),
category CHAR(32),
selling_price INTEGER,
cost_price INTEGER,
registration_date DATE,
PRIMARY KEY (id)
)
;

-- 入力部分
INSERT
INTO Products(id, name, category, selling_price, cost_price, registration_date)
VALUES
(1, 'Tシャツ', '衣類', 1500, 500, '2018-04-05'),
(2, 'ボールペン', '事務用品', 100, 30, '2018-06-03'),
(3, '包丁', 'キッチン用品', 1200, 400, '2018-03-30'),
(4, 'Yシャツ', '衣類', 2300, 300, '2018-07-23'),
(5, 'コピー用紙', '事務用品', 500, 200, '2018-02-19'),
(6, '圧力鍋', 'キッチン用品', 5900, 2000, '2018-11-26'),
(7, 'カッター', '事務用品', 130, 50, '2018-05-11'),
(8, 'プリンター', '事務用品', 9800, 2800, '2019-01-12')
;

条件式を簡単に書く[応用]

WHERE句の中に条件式を書くには比較演算子(< / >= とか)ほかの書き方もある😼

• LIKE
• BETWEEN
• IN

LIKE

文字列の部分一致検索をするのにLIKEをつかう！🙃🌟

例

// TシャツとYシャツの値段を取得する
SELECT name, selling_price FROM Products WHERE name LIKE "%シャツ";

突然の「％」は何。🙁
部分一致のには前方一致・中間一致・後方一致の３種類がある。
「％」は「０文字以上の任意の文字列」を表す。

1. 前方一致:最初に指定した文字が含まれる（＜検索文字＞％）
2. 中間一致:どこかに指定した文字が含まれる（％＜検索文字＞％）
3. 後方一致:末尾に指定した文字が含まれる（％＜検索文字＞）

さっきのだと「％シャツ」は最初に％がついてるから末尾に指定した文字が含まれる
＝「”シャツ”で終わるすべての文字列」を検索しているってこと！！！！
(なんちゃら～”シャツ”を検索って意味🤓)

練習

例) 「〇〇用品」というカテゴリの商品の名前と販売価格を販売価格が高い順に取得
SELECT name, selling_price FROM Products WHERE category LIKE "%用品" ORDER BY selling_price DESC;

BETWEEN

検索範囲（上限と下限を設定した検索）を行うにはBETWEENを使う。

例

例) 販売価格が500円から2000円までの商品名を取得
SELECT name FROM Products WHERE selling_price BETWEEN 500 AND 2000;
// BETWEEN使わなかった場合（ちょい長いかな）
SELECT name FROM Products　WHERE selling_price >= 500 AND selling_price <= 2000;

注意点
BETWEENを使用した場合、両端の値を含むことを覚えておく！！！
上記の例で500円・2000円は含みたくないよーって場合はBTEWEENをつかわない。
(比較演算子で「＝」なしで書く)

例

SELECT name FROM Products WHERE selling_price > 500 AND selling_price < 2000;

練習

例) Productsテーブルから原価が450円より低く、100円より高い商品の名前と原価を取得してみよう
SELECT name, cost_price FROM Products WHERE cost_price < 450 AND cost_price > 100;

IN

ORで複数の条件式をつなぐ必要がある場合はINを使うとすっきりして良い。

例) 販売価格が100円、2300円、9800円の商品の名前と値段を取得する場合

// OR の場合
SELECT name, selling_price FROM Products
WHERE selling_price = 100
OR selling_price = 2300
OR selling_price = 9800;

// INの場合
SELECT name, selling_price FROM Products
WHERE selling_price IN (100, 2300, 9800); // 圧倒的楽！🌟😼

NOT IN

複数の条件をつなぐ必要があり、～以外というときはNOT INを使う🤧

例) 販売価格が100円,2300円,9800円以外の商品と値段を取得する場合

SELECT name, selling_price FROM Products WHERE selling_price NOT IN (100, 2300, 9800);

やってみよう！

// Productsテーブルから販売価格が100円,500円,1500円以外の商品の名前と原価率を取得
SELECT name AS "商品名", (cost_price / selling_price *100) AS "原価率" FROM Products WHERE selling_price NOT IN(100, 500, 1500);

---

ビューとサブクエリ

より複雑な問い合わせをするためにはビューとサブクエリを使う

ビュー

よく使うSELECT文を保存して使いまわす事ができる機能。。。詳しく言うと
特定のSELECT文を頻繁に書くならばそのSELECT文に名前を付けて保存して名前を呼ぶだけでそのSELECT文を使えるようにする機能🙁🌟ええやん。

書き方

CREATE VIEW ビュー名(ビュー名のカラム1, ビュー名のカラム2,...)
AS
SELECT文;

例) 商品カテゴリごとの個数をカウントしたテーブルを取得するビューを定義
// 定義
CREATE VIEW ProductSum (category, count_product) AS SELECT category, COUNT(*) FROM Products GROUP BY category;
// ビューの利用
SELECT category, count_product FROM ProductSum;

ビューとテーブルの違いは実データを保存しているかどうか
productsテーブルは実データを保存しているがビューはSELECT文を保存しているだけなのでいくつかメリットがある！！

1. データ容量を節約できる（どんなに大きなテーブルでもSELECT文を保存しているだけだから）
2. データベースの変更に強い（DB＜テーブル＞を保存しているわけではないから）

定義したビューの削除方法

DROP VIEW ビュー名;

例) ProductSumを削除する
DROP VIEW ProductsSum;

演習問題

// 以下の条件を満たすビューをProductsテーブルから定義しビューからすべてのカラムを取得しよう
// 1. 販売価格が1000円以上
// 2. 登録日が2018年7月1日以降
// 3. カラムはname, sellng_price, registation_date

CREATE VIEW Product1(name, selling_price, registration_date)
AS SELECT name, selling_price, registration_date From Products
WHERE selling_price >= 1000 AND registration_date > "2018-07-01";

SELECT name AS "商品名", selling_price AS "販売価格", registration_date AS "登録日" FROM Product1;

サブクエリ

サブクエリは、ビュー定義のSELECT文をFROM句に書き込んだもので、一時的に作成したデータセットを使い捨てるための強力な手段です。(ビューとの違いは繰り返し使用するか一度きりか！)
特に、ビューを作成するまでもないが、一部のデータを抽出・加工したい場合に役立ちます。

さっきのビュー文コード

CREATE VIEW ProductSum (category, count_product) AS SELECT category, COUNT(*) FROM Products GROUP BY category;

サブクエリを使って書いたコード

SELECT category, count_product FROM(SELECT category, COUNT(*) AS count_product FROM Products GROUP BY category) AS ProductSum;

POINT

• ビューとして定義せずFROM句に直接SELECT文を書いている。
• サブクエリの後にAS サブクエリ名と書くことでサブクエリの名前（今回はProductSUM）を定義している。

どんな時に使うの？？

サブクエリの使いどころとしてサブクエリで集計した結果をさらに分析する際に有効

例) 商品分類ごとの個数の平均値を取得

SELECT AVG(count_product) FROM (SELECT category, COUNT(*) AS count_product FROM Products GROUP BY category) AS ProductSum;

この例題では、２ステップおこなってくれてる。

1. サブクエリで「各カテゴリーの商品の数（count_product）」を計算。
2. 外側のクエリでその平均を算出。

その他の例👇

• サブクエリは内側から順に実行されていく！
• サブクエリの中にまたサブクエリを入れることも可能ではなるが、階層が深くなり処理速度が落ちるので階層は深くしすぎないことがPOINT～

[補足]スカラサブクエリ

取得する値が単一の値（スカラ）になるサブクエリの事。

例) 販売価格の平均値(2678.7500)よりも販売価格が高い商品名と販売価格を取得

SELECT name, selling_price From Products WHERE selling_price > (SELECT AVG(selling_price) FROM Products);

// WHERE　以降~
WHERE selling_price > 2678.7500 // と同じ意味

ここでは平均値である2678.7500という単一の値を取得してるので、これはスカラサブクエリ🤓

練習) 原価率(原価÷販売価格)の平均値より原価率が低い商品の名前と販売価格と原価率を取得しよう

SELECT name AS "商品名", selling_price AS "販売価格", (cost_price / selling_price *100) AS "原価率" FROM Products
WHERE (cost_price / selling_price *100) < ( SELECT AVG(cost_price / selling_price *100) FROM Products);

CASE式

場合分け(条件分け)を記述するときに使う。
CASE式には単純CASE式と検索CASE式の２種類がある😪
（単純CASE式は検索CASE式でも表すことが出来る為今回は検索CASE式のみ扱う）

検索CASE式

書き方

CASE WHEN <条件式> THEN <値・式>
     WHEN <条件式> THEN <値・式>
     :
     ELSE <値・式>
END

例) セール時の価格を表示する(1000円以上は1割引き、5000円以上は2割引き)

SELECT name, 
       CASE WHEN selling_price >= 5000 THEN selling_price * 0.8
       CASE WHEN selling_price >= 1000 THEN selling_price * 0.9
       ELSE selling_price
END
FROM Products;

• 条件式は上から順に適応していくので大きいものから書いていく

いっつもname, selling_priceみたいに書くけどcase~ ENDは１つの式なので結果的にはname,selling_price(条件付き)....つまり今までと一緒ってことか🥴💗ｳﾋﾋ

行列変換

さっきから何か出力するとテーブルで出力されるので複数行になってた。
けど一行で取得したいという場合はCASE式を使う事で解決できる🌟

例) 商品カテゴリごとの販売価格の合計を一行で取得する。

SELECT
SUM(CASE WHEN category ="キッチン用品" THEN selling_price ELSE 0 END) AS "キッチン用品",
SUM(CASE WHEN category ="事務用品" THEN selling_price ELSE 0 END) AS "事務用品",
SUM(CASE WHEN category ="衣類" THEN selling_price ELSE 0 END) AS "衣類"
FROM Products;

解説
categoryが”キッチン用品”だったらselling_priceの値を返して、それ以外なら0を返す。
categoryが”事務用品”だったらselling_priceの値を返して、それ以外なら0を返す。
categoryが”衣類”だったらselling_priceの値を返して、それ以外なら0を返す。

演習問題やってみよう

題) 販売単価が1000円未満の低額商品、1000円以上5000円未満の中額商品、5000円以上の高額商品の個数をそれぞれ取得しよう（🌟一列で）

SELECT
SUM(CASE  WHEN selling_price <= 1000 THEN 1 ELSE 0 END) AS "低額商品",
SUM(CASE WHEN selling_price >= 1000 AND selling_price <= 5000 THEN 1 ELSE 0 END) AS "中額商品",
SUM(CASE WHEN selling_price >= 5000 THEN 1 ELSE 0 END) AS "高額商品"
FROM Products;

🤓(多分値段高い順にした方が良かった、、、、けど正しく出力されたから放置)

---

演習後コード

-- 定義部分
CREATE TABLE Products
(
id INTEGER NOT NULL,
name CHAR(32),
category CHAR(32),
selling_price INTEGER,
cost_price INTEGER,
registration_date DATE,
PRIMARY KEY (id)
)
;

-- 入力部分
INSERT
INTO Products(id, name, category, selling_price, cost_price, registration_date)
VALUES
(1, 'Tシャツ', '衣類', 1500, 500, '2018-04-05'),
(2, 'ボールペン', '事務用品', 100, 30, '2018-06-03'),
(3, '包丁', 'キッチン用品', 1200, 400, '2018-03-30'),
(4, 'Yシャツ', '衣類', 2300, 300, '2018-07-23'),
(5, 'コピー用紙', '事務用品', 500, 200, '2018-02-19'),
(6, '圧力鍋', 'キッチン用品', 5900, 2000, '2018-11-26'),
(7, 'カッター', '事務用品', 130, 50, '2018-05-11'),
(8, 'プリンター', '事務用品', 9800, 2800, '2019-01-12')
;

.
.
// LIKE
.
-- SELECT name, selling_price FROM Products WHERE name LIKE "%シャツ";
.
-- SELECT name, selling_price FROM Products WHERE category LIKE "%用品" ORDER BY selling_price DESC;
.
.
.
// BETWEEN
.
-- SELECT name FROM Products WHERE selling_price BETWEEN 500 AND 2000;
.
-- SELECT name, cost_price FROM Products WHERE cost_price < 450 AND cost_price > 100;
.
.
.
// IN
.
-- SELECT name, selling_price FROM Products WHERE selling_price IN (100, 2300, 9800);
.
-- SELECT name, selling_price FROM Products WHERE selling_price NOT IN (100, 2300, 9800);
/
-- SELECT name AS "商品名", (cost_price / selling_price 100) AS "原価率" FROM Products WHERE selling_price NOT IN(100, 500, 1500);
.
.
.
.
// ビュー
-- CREATE VIEW ProductSum (category, count_product) AS SELECT category, COUNT() FROM Products GROUP BY category;
-- SELECT category, count_product FROM ProductSum;
.
.
-- CREATE VIEW Product1(name, selling_price, registration_date) AS SELECT name, selling_price, registration_date From Products WHERE selling_price >= 1000 AND registration_date > "2018-07-01";
-- SELECT name AS "商品名", selling_price AS "販売価格", registration_date AS "登録日" FROM Product1;
.
.
.
// サブクエリ
-- SELECT category, count_product FROM(SELECT category, COUNT() AS count_product FROM Products GROUP BY category) AS ProductSum;
.
-- SELECT AVG(count_product) FROM (SELECT category, COUNT() AS count_product FROM Products GROUP BY category) AS ProductSum;
.
.
// スカラサブクエリ
-- SELECT name, selling_price From Products WHERE selling_price > (SELECT AVG(selling_price) FROM Products);
.
-- SELECT name AS "商品名", selling_price AS "販売価格", (cost_price / selling_price *100) AS "原価率" FROM Products WHERE (cost_price / selling_price *100) < ( SELECT AVG(cost_price / selling_price *100) FROM Products);
.
.
.
// CASE式
-- SELECT name,
-- CASE WHEN selling_price >= 5000 THEN selling_price * 0.8
-- WHEN selling_price >= 1000 THEN selling_price * 0.9
-- ELSE selling_price
-- END
-- FROM Products;
・
・
-- SELECT
-- SUM(CASE WHEN category ="キッチン用品" THEN selling_price ELSE 0 END) AS "キッチン用品",
-- SUM(CASE WHEN category ="事務用品" THEN selling_price ELSE 0 END) AS "事務用品",
-- SUM(CASE WHEN category ="衣類" THEN selling_price ELSE 0 END) AS "衣類"
-- FROM Products;
.
.
-- SELECT
-- SUM(CASE WHEN selling_price <= 1000 THEN 1 ELSE 0 END) AS "低額商品",
-- SUM(CASE WHEN selling_price >= 1000 AND selling_price <= 5000 THEN 1 ELSE 0 END) AS "中額商品",
-- SUM(CASE WHEN selling_price >= 5000 THEN 1 ELSE 0 END) AS "高額商品"
-- FROM Products;

[番外編]集合演算

• ２つのテーブルのレコードを足し算する＝＞UNIZON
  （カラム名が同じであることが条件）
  普通に足すと中身も同じものは排除されて表示される
  もし中身が同じものも表示したい場合はUNIZON ALLと書く

SELECT id,  name FROM Products
UNIZON ALL
SELECT id, name FROM Products2;

• 共通部分のみを取り出すには＝＞INTRESECTを使う。(MySQLは非対応)

ProductsテーブルとProducts2テーブルの共通部分のみ取得

SELECT id, name FROM Products
INTRESECT
SELECT id, name FROM Products2;

• レコードの引き算には＝＞EXCEPTを使う(MySQLは非対応)
  どちらからどちらを引くかで当たり前やけど答え変わるから注意！🥺

ProductsテーブルからProducts2テーブルのレコードを引いて取得

SELECT id, name FROM Products
EXCEPT
SELECT id, name FROM Products2;

• じゃあ非対応のMYSQLではどう書けばいい？？
  INとサブクエリで実現可能🥴

---

SQL終了～～～割と楽しかった～～～何個か脳内放置はあるけどね。。。。