【備忘録】SQLのDML, DDL

自分用に備忘録兼やったことを残していこうかなと。基本的なことばかりですが、、
間違っている点、改善したほうが良い点あったら、ご指摘いただければ幸いです。
\#DCLは別途記載しようと思います。

**環境**
・mysql ver 8.0.14
・Windows 7

#初めに
**DML : Data Munipulation Language**
 `SELECT/INSERT/UPDATE/DELETE`などテーブルに対するデータの取得・追加・更新・削除を行うコマンド
**DDL : Data Definition Language**
 `CREATE/DROP/ALTER`などデータベースオブジェクトの生成や削除変更を行うコマンド

#AUTO_INCREMENT
- テーブルを作成する時に設定する場合。KEYが1つも設定されていない場合はAUTO_INCREMENTを設定する値にindexをつけることを忘れない。
- ↓↓ indexを指定しなかった場合のエラー文

>Incorrect table definition; there can be only one auto column and it must be defined as a key

```sql
CREATE TABLE item_category (
	category_id INT AUTO_INCREMENT NOT NULL,
	category_name VARCHAR(256) NOT NULL,
	INDEX(category_id)
);
```

- 既存のカラムに設定を追加する場合。

```sql
ALTER TABLE item_category CHANGE category_id category_id INT AUTO_INCREMENT;
```

- `AUTO_INCREMENT`の値を設定する。次に更新する値に付与する番号を4にする場合。4で登録したレコードを削除した後、次に登録するレコードには5が採番されてしまう為。

```sql
ALTER TABLE item_category AUTO_INCREMENT = 4;
```

#FOREIGN KEY
- テーブル作成時に設定する。

```sql
CREATE TABLE item_list (
	item_id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
	item_name VARCHAR(256) NOT NULL,
	item_price INT NOT NULL,
	category_id INT,
		FOREIGN KEY(category_id) REFERENCES item_category(category_id)
);
```
- 既存カラムに追加。

```sql
ALTER TABLE item_list ADD FOREIGN KEY (category_id) REFERENCES item_category(category_id);
```
- FOREIGN KEY削除。\`item_list_ibfk_1\`は下の`SHOW CREATE TABLE`のCONSTRAINT以下を参照。

```sql
ALTER TABLE item_list DROP FOREIGN KEY `item_list_ibfk_1`;
```

#SHOW CREATE TABLE テーブル名 & DESC テーブル名
テーブルの定義等を確認できる。SHOW CREATE TABLEの方が詳細が分かるかな。

```sql
mysql> SHOW CREATE TABLE item_list;
---------------------
| Table     | Create Table
---------------------
| item_list | CREATE TABLE `item_list` (
  `item_id` int(11) NOT NULL AUTO_INCREMENT,
  `item_name` varchar(256) NOT NULL,
  `item_price` int(11) NOT NULL,
  `category_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`item_id`),
  KEY `category_id` (`category_id`),
  CONSTRAINT `item_list_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `item_category` (`category_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
---------------------
```

```sql
mysql> DESC item_list;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| item_id     | int(11)      | NO   | PRI | NULL    | auto_increment |
| item_name   | varchar(256) | NO   |     | NULL    |                |
| item_price  | int(11)      | NO   |     | NULL    |                |
| category_id | int(11)      | YES  | MUL | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+
```

#カラム追加&削除

```sql
/* 追加（デフォルトは最後の列に追加） */
ALTER TABLE item_list ADD item_price INT;
/* 先頭に追加 */
ALTER TABLE item_list ADD item_price INT FIRST;
/* 特定のカラムの後に追加 */
ALTER TABLE item_list ADD item_price INT AFTER item_name;
/* 削除 */
ALTER TABLE item_list DROP item_price;
```
#テーブル結合
`INNER JOIN`は値がNULLの場合、表示してくれない。もし値がNULLのレコードも出したい場合は`LEFT JOIN`を使う。混在してもOK。

```sql
SELECT
	/* 表示したいデータのカラム */
FROM 
	user_m INNER JOIN gender_m
		ON user_m.gender_cd = gender_m.gender_cd
	LEFT JOIN card_t
		ON user_m.user_id = card_t.user_id
	LEFT JOIN card_m
		ON card_t.card_div_cd = card_m.card_div_cd
;
```

#サブクエリ
SQLの中にSELECT文をいれて、SELECT文の検索結果をSQLに利用させる。FROM句の中でもWHERE句の中でもどちらに対しても使える。

```sql
/* item_mテーブルで基本情報を保持。stock_mテーブルで在庫情報を保持。*/
SELECT
	item_m.item_id,
	item_m.item_name,
	TEMP.allowable_stock_num
FROM
	item_m INNER JOIN (
		SELECT 
			item_id,
                       /* item_idごとの実在庫数 - 引当在庫数の合計を算出 */
			sum(p_inven_num - allocated_stock_num) AS allowable_stock_num
		FROM
			stock_m
		GROUP BY
                /* item_mとstock_mの両テーブルに存在するitem_idでグルーピングをする。*/
			item_id 
	) TEMP	
		ON item_m.item_id = TEMP.item_id
;
```
TEMPテーブルを一時的に生成し、INNER JOINで結合。TEMPテーブルでSELECT結果をSQLで利用する。もっと練習が必要かも、、、

#その他もろもろ
##CONCAT (文字列結合)
ラストネームとファーストネームを結合して1つにする

```sql
SELECT
	CONCAT(user_m.user_l_name, ' ', user_m.user_f_name) AS 漢字,
	CONCAT(user_m.user_l_name_kn, ' ', user_m.user_f_name_kn) AS カタカナ,
        ....
        ....
FROM
       /* 省略 */
```

##CASE式
SELECT文で検索した情報をINSERT文で利用するケース。
SELECT文でitem_mテーブルからitem_idがAAAAAAとCCCCCCの商品情報を取り出し、それをorder_tテーブルに挿入する。
今回はWHERE句で条件を2つ指定している為、ELSEは書いてません。
書くとしたら、ELSE item_num = 0とかになるのか。

```
INSERT INTO order_t
  SELECT
	5 AS order_no,
	item_id,
	item_price,
       /* 検索CASE式。item_idと注文数を紐付け */
	CASE WHEN item_id = 'AAAAAA' THEN 100 
	     WHEN item_id = 'CCCCCC' THEN 90
	END AS item_num
  FROM
  	item_m
  WHERE
  	item_id in ('AAAAAA', 'CCCCCC')
;
```