MySQL基礎

#テーブル作成

```
create table テーブル名 (
    カラム名 データ型 default デフォルト値 制約 comment 'コメント',
    ...,
    表制約
) ENGINE = [INNODB | MyISAM];
```


##データ型
INT: 整数値
FLOAT: 浮動小数点
DATETIME: 日時
TIMESTAMP: 日時
CHAR: 固定長文字列
VARCHAR: 可変長文字列
BLOB: バイナリデータ（画像や音声、動画など）

:::note info
正の値に限定する場合は unsigned を使用。
整数型は正の数と負の数を扱うことができますが、データ型の後に UNSIGNED を付けると 0 と正の数しか格納できなくなります。このようなデータ型を符号なし整数型といいます。
:::

##制約
UNIQUE: 一意制約
NOT NULL: NOT NULL制約
CHECK: チェック制約
PRIMARY KEY: 主キー制約
FOREIGN KEY: 外部キー制約

:::note info
- 表制約
表（テーブル）に対して行う制約
例）複合主キー、外部キー制約など

- 列制約
列に対して行う制約
例）NOT NULL 制約など
:::

:::note warn
ENGINEを指定しない場合はINNODBがデフォルトとなる
:::

##主キー

:::note info
一つのテーブルにつき一つの主キーのみ作成可能。
一意の値であること。NOT NULLであること。
:::

```
-- 単一主キー
create table test_db.test_table (
	key1 int(6) PRIMARY KEY
);

-- 複合主キー
create table test_db.test_table (
	key1 int(6),
	key2 int(6),
    PRIMARY key pk1(key1, key2)
);
```

##外部キー

:::note warn
・型情報は合わせる
・インデックスの自動付与
（すでに有効なキーが存在する場合には作成しない。
　例：複合主キーの一番最初はインデックスが作成されない。）
:::

:::note info
ON DELETE: レコードが削除された際のアクション
ON UPDATE: レコードが更新された際のアクション

CASCADE:
親テーブルの行を削除または更新し、子テーブル内の一致する行を自動的に削除または更新します。

RESTRICT:
親テーブルに対する削除または更新操作を拒否します。
ON DELETE または ON UPDATE 句を省略することと同義。
:::

```
//外部キーの作成
alter table テーブル名
add constraint 制約名（※削除する際に使用）
foreign key (対象のキー名)
      references 親テーブル名(テーブルキー名)
      on update cascade 
      on delete restrict; -- 省略可
```

#トランザクションテーブルとマスタテーブルの識別

##トランザクションテーブル
アプリからデータを頻繁に挿入、更新するようなテーブル。
エントリーテーブルとも呼ぶ。

例）オーダー情報、顧客情報、請求情報など

先頭にENT, TXN, TRNなどを付ける場合が多い。

##マスタテーブル
参照値を保持する用のテーブル。
アプリからは基本的に値を挿入、変更しない。

例）商品一覧、店舗一覧など

先頭にMSTとつけることが多い。

##論理削除フラグの導入(delete_flg)
レコードの有効性を識別するためのフラグ

例）delete_flg = 1の場合には無効レコードとして扱う。

##更新日、更新者の導入(updated_at, updated_by)
レコードがいつ、誰によって変更されたのかの証跡を保持するための属性

※外部キー制約はつける場合とつけない場合がある

##外部キーを設定したテーブルの作成例

```
create table mst_products (
  id int(10) unsigned auto_increment primary key,
  name varchar(20) not null,
  delete_flg int(1) not null default 0,
  updated_at timestamp default current_timestamp on update current_timestamp, 
  updated_by varchar(20) not null
);

create table mst_prefs (
  id int(2) unsigned auto_increment primary key,
  name varchar(10) not null,
  delete_flg int(1) not null default 0,
  updated_at timestamp default current_timestamp on update current_timestamp, 
  updated_by varchar(20) not null
);

create table mst_shops (
  id int(10) unsigned auto_increment primary key,
  name varchar(50) not null,
  pref_id int(2) unsigned not null,
  delete_flg int(1) not null default 0,
  updated_at timestamp default current_timestamp on update current_timestamp, 
  updated_by varchar(20) not null,
  constraint fk_pref_id
    foreign key(pref_id)
    references mst_prefs (id)
    on update cascade 
);

create table txn_stocks (
  product_id int unsigned,
  shop_id int unsigned,
  amount int unsigned not null,
  delete_flg int(1) not null default 0,
  updated_at timestamp default current_timestamp on update current_timestamp, 
  updated_by varchar(20) not null,
  primary key (product_id, shop_id),
  constraint fk_product_id
    foreign key (product_id)
    references mst_products (id)
    on update cascade,
  constraint fk_shop_id
    foreign key (shop_id)
    references mst_shops (id)
    on update cascade
);
```

#limitとoffset

limit
取得レコード数の上限

```
select * from test_db.txn_stocks
order by product_id desc,
shop_id asc
limit 2;
```

offset
取得レコードの開始位置

```
// オフセット付き（0がデフォルト値）
// オフセット, レコード数
select * from test_db.txn_stocks limit 1 offset 2;
//offsetを省略した場合の書き方（offsetが先に記述される）
select * from test_db.txn_stocks limit 2, 1;
```

#テーブル結合

##内部結合（INNER JOIN）

```
select * from テーブル１
inner join テーブル２
on テーブル１.値が一致する属性 = テーブル２.値が一致する属性;

-- 例
select
	ms.name "店舗名", mp.name "都道府県名"
from
	test_db.mst_shops ms 
inner join test_db.mst_prefs mp 
on ms.pref_id = mp.id;

-- where を使った結合
select
	ms.name "店舗名", mp.name "都道府県名"
from
	test_db.mst_shops ms,
    test_db.mst_prefs mp 
where ms.pref_id = mp.id;
```

##外部結合（LEFT [OUTER] JOINとRIGHT [OUTER] JOIN）

```
-- LEFT JOIN
select
	mp.name "都道府県名", ms.name "店舗名"
from
	test_db.mst_prefs mp 
left join test_db.mst_shops ms 
on ms.pref_id = mp.id;

-- RIGHT JOIN
select 
	mp.name "都道府県名", ms.name "店舗名" 
from 
	test_db.mst_shops ms
right join test_db.mst_prefs mp
on ms.pref_id = mp.id;
```

#TIMESTAMPとDATETIMEの違い

##TIMESTAMP
4 bytes
'1970-01-01 00:00:01' UTC to '2038-01-09 03:14:07'

:::note warn
タイムゾーンを考慮
タイムゾーンを変更した時にタイムゾーンに合わせて値が変化する
例）日本から海外のDBに移行した際に値が移行先のタイムゾーンに変更される
:::

##DATETIME
5 bytes(旧バージョンでは8bytes)
'1000-01-01 00:00:00' to '9999-12-31 23:59:59' 

:::note warn
タイムゾーンを考慮しない（DBの移行が考えられる場合には重要）
TIMESTAMPと違い、タイムゾーンを変更しても値を挿入した時の値が保持される
:::

#文字コード（character set）

データベース、テーブル、カラムに設定可能

##utf8
3bytes
一部、表示できない文字が存在する。
 
##utf8mb4
4bytes
絵文字などにも対応。

:::note warn
※MySQL の今後のバージョンでは、utf8 が 4 バイトの utf8 になり、
3 バイトの utf8 を指定するときに utf8mb3 を示す必要が生じる可能性があります。
:::