SQL(MySQL) 備忘録

# データベースの作成・削除
```sql
create database test_db;
drop database test_db;
```

# テーブルの作成
```sql
create table test_db.test_table (
	id int(6) unsigned default 0 comment 'ID',
	val varchar(20) default 'hello' comment '値'
);
```
※int unsignedで正の整数を指定できる

# テーブルの削除
```sql
drop table test_db.test_table;
```

# テーブル定義の確認
いずれかで確認可能（表示方法は違う）

```sql
desc test_db.test_table;
show full columns from test_db.test_table;
show create table test_db.test_table;
```

# DBのアクティブ切り替え
```sql
use test_db; --切り替え
select database(); --確認方法
```

# 制約
UNIQUE: 一意制約
NOT NULL: NOT NULL制約
CHECK: チェック制約（MySQL8.0より　条件式が使える）
PRIMARY KEY: 主キー制約
FOREIGN KEY: 外部キー制約

```sql
create table test_table (
	id int not null default 0 comment 'ID',
	val varchar(20) unique comment '値'
);
```

#キー
主キー

```sql
create table test_db.test_table (
	key1 int primary key
);
```

複合主キー

```sql
create table test_db.test_table (
	key1 int,
	key2 int,
	primary key (key1, key2)
);
```

# 自動IDの付与

* auto_incrementはindexが振られていないといけない<br>
  (primary key, uniqueなどは自動で振られている)
* 一つのテーブルに一つのみ使用可能
* default値は使用不可

```sql
create table test_db.test_table (
	key1 int auto_increment primary key
);
```

# indexの確認方法

```
show index from test_table;
```

#カラム追加
```sql
alter table test_db.test_table
add column key2 varchar(20) not null,
add column key3 varchar(20) not null;

-- 指定してカラムのあとに追加
alter table test_db.test_table
add column key4 varchar(20) not null after key2;

-- 先頭に追加
alter table test_db.test_table
add column key5 varchar(20) not null first;
```

# カラムの修正
```sql
alter table test_db.test_table
modify column key5 int;
```

# カラムの削除
```sql
alter table test_db.test_table
drop column key5;

-- 主キーの削除m
alter table test_db.test_table
drop primary key;
```

# 外部キーの設定

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

RESTRICT:
親テーブルに対する削除または更新操作を拒否します。

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

alter table test_db.stocks 
add constraint fk_product_id
foreign key (product_id)
references products(id)
on update cascade
on delete restrict,
add constraint fk_shop_id
foreign key (shop_id)
references shops(id)
on update cascade
on delete restrict;
```

# トランザクションテーブルとマスタテーブル
トランザクションテーブル
アプリから頻繁にデータ更新を行うテーブル
テーブル名の先頭にENT, TXN, TRNをつける場合が多い

マスタテーブル
参照値を保持するテーブル。アプリから値を挿入、変更はしない
先頭にMSTとつける場合が多い

# 論理削除
レコードの有効性を示すためにカラムに追加する
例）delete_flg = 1の場合には無効

# 更新日、更新者の導入
レコードがいつ誰によって保存されたかを示すためにカラムに追加する

```sql
updated_at timestamp default current_timestamp on update current_timestamp,
updated_by varchar(20) not null,
```

# レコードの追加
```sql
insert into mst_prefs (name, updated_by) values ("山形", "yy"),
("青森", "yy");
```

# レコードの削除
```sql
delete from mst_prefs;
```

# レコードの検索
```sql
select id, name as "都道府県名" from mst_prefs ;
```
重複を省くときはdistinctを使用する

# auto_incrementの初期化
```sql
alter table test_db.mst_prefs auto_increment = 1;
```

# auto_incrementの確認方法
```sql
show table status where name = '対象テーブル名';
```

#where条件
```sql
-- = : 一致
select * from txn_stocks ts 
where product_id  = 1;
-- <>, != : 非一致
select * from txn_stocks ts 
where product_id  <> 1;
-- >, >=, <, <= : 数値の比較
select * from txn_stocks ts 
where amount  > 50;
-- A and B : A かつ B
select * from txn_stocks ts 
where product_id  = 1 and shop_id = 1;
-- A or B : A または B
select * from txn_stocks ts 
where product_id  = 1 OR shop_id = 1;
-- () : 条件をくくる
select * from txn_stocks ts 
where (product_id  = 1 and shop_id = 1)
or (product_id  = 2 and shop_id = 2);
-- like : %で部分一致検索 _で１文字
select * from mst_shops
where name like '__A';
-- in : いずれかの値に一致
select * from mst_shops
where name in ("店舗A", "店舗B");
-- not in : いずれの値にも一致しない
select * from mst_shops
where name not in ("店舗A", "店舗B");
-- between A and B: A から B の値 
select * from txn_stocks
where amount between 60 and 100;
-- is not null : null以外に一致
select * from txn_stocks
where amount is not null;	
-- is null : nullに一致
select * from txn_stocks
where amount is	null;	
```

# ソートの方法
```sql
-- 単一ソート, desc 降順
SELECT * FROM txn_stocks
WHERE amount > 50
order by amount desc;
-- 複数のソート
SELECT * FROM txn_stocks
order by product_id desc,
shop_id asc;
```
#個数指定
```sql
SELECT * FROM txn_stocks
order by product_id desc,
shop_id asc
limit 2 offset 1;
-- offsetは0から始まる
-- 下記でも同じ意味
limit 1, 2
```

# データの更新
```sql
UPDATE txn_stocks set amount = amount - 10, delete_flg = 1
WHERE product_id = 1 AND shop_id = 1;
```
# テーブルの内部結合
```sql
SELECT ms.name "店舗名", mp.name as "都道府県名" FROM mst_shops ms
inner join mst_prefs mp 
on ms.pref_id = mp.id
where ms.id = 1;

-- これでも同じ意味
SELECT ms.name "店舗名", mp.name as "都道府県名" 
FROM mst_shops ms, mst_prefs mp 
where ms.pref_id = mp.id;
```

# テーブルの外部結合
```sql
SELECT mp.name "都道府県名", ms.name "店舗名" FROM mst_prefs mp 
left join mst_shops ms 
ON mp.id = ms.pref_id ;
```

# トランザクション
```sql
start transaction;
-- 処理
commit; --成功時にDBに反映
rollback; --start transaction を宣言した地点まで戻る
```

# 処理待機中の確認
```sql
select * from information_schema.innodb_lock_waits
```
#デッドロックの確認
```sql
show engine innodb status;
```

#truncate テーブルの切り捨て
* rollbackで戻すことができない
* whereは使用不可
* deleteより高速
* auto_incrementは初期値になる

```sql
truncate テーブル名
```
# システム変数
システム変数の確認

```sql
-- 確認方法
show variables;
-- like検索
show variables like "%auto%";
--session内のみ確認
show session variables;
--全体の確認
show global variables;
-- @@を用いた確認方法
select @@local.autocommit;
-- 更新
set session autocommit = 1;
```
ユーザー定義変数

```sql
-- set方法
set @s_id = 2;

-- 以下でもset可能
select @s_name := name from mst_shops ms
where ms.id = 2;
```
#日時の違い
TIMESTANP 4byte
'1970-01-01 00:00:01' UTC to '2038-01-09 03:14:07'

DATETIME 5byte
'1000-01-01 00:00:00' to '9999-12-31 23:59:59'
※タイムゾーンを考慮しない

# ユーザーの確認と作成
現在のユーザーの確認
root@localhostはrootにはlocalhostからしか入れない

```sql
select user();
```

ユーザーの追加
create user {ユーザー名}@{接続元のホスト名} identified by 'password';

```sql
create user 'test_user'@'localhost' identified by 'pwd';
```
ユーザーの確認

```sql
select * from mysql.`user`;
```
権限の付与
grant {権限名} on {対象のDBオブジェクト} to {ユーザー};

```sql
grant select on test_db.* to 'test_user'@'localhost';
grant update, insert on test_db.* to 'test_user'@'localhost';
grant create, alter on test_db.* to 'test_user'@'localhost';

-- 全てのデータベースの全てのオブジェクトに対してかかる
grant create, alter on *.* to 'test_user'@'localhost';
-- 全てのデータベースの全てのオブジェクトに全権限を与える
grant all on *.* to 'test_user'@'localhost';
-- 権限の確認
show grants for 'test_user'@'localhost';
-- 権限の削除
revoke all on test_db.* from 'test_user'@'localhost';
```

#文字コード
```sql
-- Databaseの文字コード指定
create database utf8mb4_db
    character set 'utf8mb4';

-- テーブルへの設定
create table tbl_name (column) character set 'utf8mb4';

CREATE TABLE test_db.char_test (
	mb4 varchar(20) character set 'utf8mb4',
	mb3 varchar(20) character set 'utf8'
);
```

# 照合順序
一覧の表示

```sql
show collation where charset like '%utf8%';
```
テーブル作成時に設定

```sql
create table test_db.collate_test (
  col varchar(20)
) 
character set 'utf8mb4'
collate 'utf8mb4_general_ci';
```
検索

```sql
select col "UNI" from test_db.collate_test where col COLLATE utf8mb4_unicode_ci = 'はは';
```