テーブル作成

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

データ型

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

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

制約

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

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

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

主キー

一つのテーブルにつき一つの主キーのみ作成可能。一意の値であること。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)
);

外部キー

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

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'

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

DATETIME

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

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

文字コード（character set）

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

utf8

3bytes
一部、表示できない文字が存在する。

utf8mb4

4bytes
絵文字などにも対応。

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

MySQL基礎