MYSQL 入門３

-------------LEFT JOIN--------------
mysql> SELECT * FROM USER;
+----+----------+------+---------+---------+
| ID | NAME     | Unit | KOZUKAI | BIKOU   |
+----+----------+------+---------+---------+
|  1 | ASO      |    1 |   10000 | SUKUNAI |
|  2 | KATO     |    4 |   90000 | OUI     |
|  3 | YAMADA   |    2 |   10000 | SUKUNAI |
|  4 | SUZUKI   |    1 |   10000 | SUKUNAI |
|  5 | MIYAMOTO |    3 |   50000 | FUTUU   |
|  6 | TANAKA   |    4 |   90000 | OUI     |
+----+----------+------+---------+---------+


mysql> SELECT * FROM UNIT;
+----+----------+
| ID | NAME     |
+----+----------+
|  1 | SOUMU    |
|  2 | EIGYO    |
|  3 | JIMU     |
|  4 | GIJYUTSU |
+----+----------+
---UNITテーブルのIDにないレコードを追加----
UPDATE USER SET UNIT = '7' WHERE ID = 6;


/*----LEFT JOINでは左のテーブルのレコードは全て表示されます。----*/
mysql> SELECT *
    ->   FROM USER
    -> LEFT JOIN UNIT
    ->   ON USER.UNIT = UNIT.ID ;
+----+----------+------+---------+---------+------+----------+
| ID | NAME     | Unit | KOZUKAI | BIKOU   | ID   | NAME     |
+----+----------+------+---------+---------+------+----------+
|  1 | ASO      |    1 |   10000 | SUKUNAI |    1 | SOUMU    |
|  2 | KATO     |    4 |   90000 | OUI     |    4 | GIJYUTSU |
|  3 | YAMADA   |    2 |   10000 | SUKUNAI |    2 | EIGYO    |
|  4 | SUZUKI   |    1 |   10000 | SUKUNAI |    1 | SOUMU    |
|  5 | MIYAMOTO |    3 |   50000 | FUTUU   |    3 | JIMU     |
|  6 | TANAKA   |    7 |   90000 | OUI     | NULL | NULL     |
+----+----------+------+---------+---------+------+----------+

/*----LEFT RIGHTでは右のテーブルのレコードは全て表示されます。USER.UNIT = UNIT.IDが一致しない
レコードは表示されません----*/
mysql>  SELECT *
    ->    FROM USER
    ->  RIGHT JOIN UNIT
    ->    ON USER.UNIT = UNIT.ID ;
+------+----------+------+---------+---------+----+----------+
| ID   | NAME     | Unit | KOZUKAI | BIKOU   | ID | NAME     |
+------+----------+------+---------+---------+----+----------+
|    1 | ASO      |    1 |   10000 | SUKUNAI |  1 | SOUMU    |
|    4 | SUZUKI   |    1 |   10000 | SUKUNAI |  1 | SOUMU    |
|    3 | YAMADA   |    2 |   10000 | SUKUNAI |  2 | EIGYO    |
|    5 | MIYAMOTO |    3 |   50000 | FUTUU   |  3 | JIMU     |
|    2 | KATO     |    4 |   90000 | OUI     |  4 | GIJYUTSU |
+------+----------+------+---------+---------+----+----------+


-------------------------自己結合----------------------------
/*－－－カテゴリテーブルの作成－－－*/
CREATE TABLE Category (ID INT AUTO_INCREMENT PRIMARY KEY, NAME VARCHAR(100),HOYA_CATEGORY_ID INT);

INSERT INTO CATEGORY(NAME,HOYA_CATEGORY_ID) VALUES('BOOK',NULL),('Fashion',NULL),('electronics',NULL);--親カテゴリ
INSERT INTO CATEGORY(NAME,HOYA_CATEGORY_ID) VALUES('Picture BOOK',1),('IT BOOK',1),('COOKING BOOK',1);--子カテゴリ
INSERT INTO CATEGORY(NAME,HOYA_CATEGORY_ID) VALUES('Man Fashion',2),('WOMAN Fashion',2);--子カテゴリ
INSERT INTO CATEGORY(NAME,HOYA_CATEGORY_ID) VALUES('TV electronics',3),('Music electronics',3),('Cooking electronics',3);--子カテゴリ


mysql> SELECT * FROM CATEGORY;
+----+---------------------+------------------+
| ID | NAME                | HOYA_CATEGORY_ID |
+----+---------------------+------------------+
|  1 | BOOK                |             NULL |
|  2 | Fashion             |             NULL |
|  3 | electronics         |             NULL |
|  4 | Picture BOOK        |                1 |
|  5 | IT BOOK             |                1 |
|  6 | COOKING BOOK        |                1 |
|  7 | TV electronics      |                3 |
|  8 | Music electronics   |                3 |
|  9 | Cooking electronics |                3 |
| 10 | Man Fashion         |                2 |
| 11 | WOMAN Fashion       |                2 |
+----+---------------------+------------------+
--------自己結合によるカテゴリーの表示----------
mysql> SELECT *
    ->   FROM CATEGORY
    -> AS C_1
    ->   JOIN CATEGORY
    -> AS C_2
    -> WHERE C_1.ID = C_2.HOYA_CATEGORY_ID;
+----+-------------+------------------+----+---------------------+------------------+
| ID | NAME        | HOYA_CATEGORY_ID | ID | NAME                | HOYA_CATEGORY_ID |
+----+-------------+------------------+----+---------------------+------------------+
|  1 | BOOK        |             NULL |  4 | Picture BOOK        |                1 |
|  1 | BOOK        |             NULL |  5 | IT BOOK             |                1 |
|  1 | BOOK        |             NULL |  6 | COOKING BOOK        |                1 |
|  3 | electronics |             NULL |  7 | TV electronics      |                3 |
|  3 | electronics |             NULL |  8 | Music electronics   |                3 |
|  3 | electronics |             NULL |  9 | Cooking electronics |                3 |
|  2 | Fashion     |             NULL | 10 | Man Fashion         |                2 |
|  2 | Fashion     |             NULL | 11 | WOMAN Fashion       |                2 |
+----+-------------+------------------+----+---------------------+------------------+


---------------------------------サブクエリー------------------------------
/*サブクエリで最大IDを取得*/
mysql> SELECT * FROM CATEGORY WHERE ID IN (SELECT MAX(ID) FROM CATEGORY);
+----+---------------+------------------+
| ID | NAME          | HOYA_CATEGORY_ID |
+----+---------------+------------------+
| 11 | WOMAN Fashion |                2 |
+----+---------------+------------------+

mysql> SELECT * FROM CATEGORY WHERE ID IN (SELECT MAX(HOYA_CATEGORY_ID) FROM CATEGORY);
+----+-------------+------------------+
| ID | NAME        | HOYA_CATEGORY_ID |
+----+-------------+------------------+
|  3 | electronics |             NULL |
+----+-------------+------------------+

------------商品のカテゴリを表示-------------
CREATE TABLE PRODUCT(ID INT AUTO_INCREMENT PRIMARY KEY,NAME VARCHAR(100),CATEGORY_ID INT);

INSERT INTO PRODUCT(NAME,CATEGORY_ID) VALUES('PC_WORK',5),('IT_WORK',5),('PAN_COOK',6),('CAMERA',4);


mysql> SELECT CAT.NAME AS 'カテゴリ',PRO.NAME AS '商品名'FROM  CATEGORY AS CAT ,PRODUCT AS PRO WHERE CAT.ID = PRO.CATEGORY_ID;
+--------------+----------+
| カテゴリ          | 商品名        |
+--------------+----------+
| IT BOOK      | PC_WORK  |
| IT BOOK      | IT_WORK  |
| COOKING BOOK | PAN_COOK |
| Picture BOOK | CAMERA   |
+--------------+----------+


----------商品が無いカテゴリを表示-----------
SELECT CATEGORY.NAME
  FROM CATEGORY
WHERE  NOT EXISTS (SELECT * FROM PRODUCT WHERE PRODUCT.CATEGORY_ID = CATEGORY.ID)
AND CATEGORY.HOYA_CATEGORY_ID IS NOT NULL
;
+---------------------+
| NAME                |
+---------------------+
| TV electronics      |
| Music electronics   |
| Cooking electronics |
| Man Fashion         |
| WOMAN Fashion       |
| TEST                |
+---------------------+

----------------カテゴリの商品件数-----------------
SELECT CAT.NAME,COUNT(*)
  FROM CATEGORY AS CAT,
        PRODUCT AS PRO
 WHERE CAT.ID = PRO.CATEGORY_ID
 GROUP BY CAT.ID;

 +--------------+----------+
| NAME         | COUNT(*) |
+--------------+----------+
| Picture BOOK |        1 |
| IT BOOK      |        2 |
| COOKING BOOK |        1 |
+--------------+----------+

----------------ビュー-----------------
CREATE VIEW C1
  AS
SELECT ID,NAME
  FROM CATEGORY;
 
SELECT * FROM C1;
+----+---------------------+
| ID | NAME                |
+----+---------------------+
|  1 | BOOK                |
|  2 | Fashion             |
|  3 | electronics         |
|  4 | Picture BOOK        |
|  5 | IT BOOK             |
|  6 | COOKING BOOK        |
|  7 | TV electronics      |
|  8 | Music electronics   |
|  9 | Cooking electronics |
| 10 | Man Fashion         |
| 11 | WOMAN Fashion       |
| 12 | TEST                |
+----+---------------------+

--------自己結合したテーブルのVIEW---------
/*ポイントは同じなのカラム名にならない事*/

mysql> CREATE VIEW C2 AS
    ->   SELECT
    ->     CAT.ID,
    ->     CAT.NAME,
    ->     C1.ID AS KO_ID,
    ->     C1.NAME AS KO_NAME
    ->   FROM CATEGORY AS CAT
    ->     JOIN C1
    ->   WHERE CAT.ID = C1.HOYA_CATEGORY_ID;
Query OK, 0 rows affected (0.08 sec)

mysql> SELECT * FROM C2;
+----+-------------+-------+---------------------+
| ID | NAME        | KO_ID | KO_NAME             |
+----+-------------+-------+---------------------+
|  1 | BOOK        |     4 | Picture BOOK        |
|  1 | BOOK        |     5 | IT BOOK             |
|  1 | BOOK        |     6 | COOKING BOOK        |
|  3 | electronics |     7 | TV electronics      |
|  3 | electronics |     8 | Music electronics   |
|  3 | electronics |     9 | Cooking electronics |
|  2 | Fashion     |    10 | Man Fashion         |
|  2 | Fashion     |    11 | WOMAN Fashion       |
+----+-------------+-------+---------------------+

-------------ビュー上書き-------------
CREATE OR REPLACE VIEW C2 AS SELECT NOW();


-------------ビュのカラム構造の変更-------------
ALTER VIEW C2 AS
SELECT
  CAT.ID,
  CAT.NAME,
  C1.ID AS KO_ID,
  C1.NAME AS KO_NAME
FROM CATEGORY AS CAT
  JOIN C1
WHERE CAT.ID = C1.HOYA_CATEGORY_ID;

-------------ビュ－の削除-------------
/*VIEWがあれば削除します。*/
DROP VIEW IF EXISTS C2;