MySQLとSQLのメモメモ

MySQLの備忘録。
GolangでMySQLのClientにGORMを使った場合についてメモ

```terminal
$ mysql --version
mysql  Ver 14.14 Distrib 5.7.17, for osx10.12 (x86_64) using  EditLine wrapper
```
# 初期設定
## rootのPWを変更
```mysql
> UPDATE mysql.user SET 
authentication_string=PASSWORD('rootchan') WHERE User='root';
```
## 新しくデータベースを作成する
```mysql
> CREATE DATABASE dbo;
> CREATE DATABASE dbo_test;
```
## 新しくユーザーを作成する
```mysql
> CREATE USER 'hoge'@'%' IDENTIFIED BY 'hogechang';
> UPDATE mysql.user SET authentication_string=PASSWORD('hogechan') WHERE User='hoge';
> SELECT user,host from mysql.user;
```
## ユーザーの権限を付与する(hoge系のDBの全テーブルにGrant Option以外の権限付与)
see https://dev.mysql.com/doc/refman/5.7/ja/grant.html

```mysql
> GRANT ALL PRIVILEGES ON dbo.* TO 'hoge'@'%';
> GRANT ALL PRIVILEGES ON dbo_test.* TO 'hoge'@'%';
> SHOW GRANTS for 'hoge'@'%'; # 権限確認
```

## 設定を反映する
```mysql
> FLUSH PRIVILEGES;
```
## SQL Modeの変更
  - GoのDate型は値型で初期値が'0000-00-00'であり、これがMySQLのError Code 1292: Incorrect datetime valueにあたり それを許容するため変更する。
  - 本当はDate型のポインタ型にし、NullをMySQLに入れたい。しかし、Datastoreではポインタ型のDate型は禁止のため、基底クラスの互換性を優先する
 
```mysql
> SELECT @@GLOBAL.sql_mode;
> SET GLOBAL sql_mode = 'ALLOW_INVALID_DATES';
> SELECT @@GLOBAL.sql_mode;
```
## 再起動
```shell
$ mysql.server restart
```

# コネクション

## GORMでMySQLのコネクション
- gorm.OpenメソッドでTCP接続を開始したら1コネクションとなる。
- 明示的にdb.Close()をした場合は、コネクションを切れる。
- MySQLのコンソールから累積接続数を確認するとそれが分かる。
- また、gorm.DBオブジェクトはスレッドセーフなので、グローバル変数において使用するのもOK。


```go
db, err := gorm.Open("mysql", args)
defer db.Close()
```

## GORMでMySQLのコネクションループ
- コネクションループをする場合は、db.Close()をしなければ良い。
- ただし、 タイムアウトするので、go-sql-driver/mysqlなどのパッケージのオプション(`SetConnMaxLifetime`など)を設定する必要がある。



## 現在の接続数
```mysql
> show global status like 'Threads_connected';
```

## 最大接続数設定(上限)
```mysql
> show global variables like 'max_connections';
```
## 起動してからの累積接続数
```mysql
> show global status like 'Connections';
```

## 起動してからこれまでの最大同時接続数
```mysql
> show global status like 'Max_used_connections';
```

## 接続プロセスのタイムアウトの確認
```mysql
> show global variables like 'wait_timeout%';
```
## 最大接続数を超えた場合のエラー(Too many connections)について
```mysql
> show processlist;
```

# テストデータ投入
```mysql
> source ./hoge.sql
```

```hoge.sqlƒ
INSERT INTO
  hoges
VALUES(
  2,
  "23t3223",
  "http://google.com",
  '2016-10-10',
  '2016-10-10'
);


```

# ディレクトリ確認
```mysql
> SHOW VARIABLES WHERE Variable_name LIKE "%dir"
+---------------------------+------------------------------------------------------+
| Variable_name             | Value                                                |
|---------------------------+------------------------------------------------------|
| basedir                   | /usr/local/Cellar/mysql/5.7.17/                      |
| character_sets_dir        | /usr/local/Cellar/mysql/5.7.17/share/mysql/charsets/ |
| datadir                   | /usr/local/var/mysql/                                |
| innodb_data_home_dir      |                                                      |
| innodb_log_group_home_dir | ./                                                   |
| innodb_tmpdir             |                                                      |
| lc_messages_dir           | /usr/local/Cellar/mysql/5.7.17/share/mysql/          |
| plugin_dir                | /usr/local/Cellar/mysql/5.7.17/lib/plugin/           |
| slave_load_tmpdir         | /var/folders/5z/ks1lxtfn1djddq6lvdx7vj1r0000gn/T/    |
| tmpdir                    | /var/folders/5z/ks1lxtfn1djddq6lvdx7vj1r0000gn/T/    |
+---------------------------+------------------------------------------------------+
10 rows in set
Time: 0.003s
```

## Truncate
foreign key constraintがあるときのTruncate

```
SET FOREIGN_KEY_CHECKS = 0; 
TRUNCATE table $table_name; 
SET FOREIGN_KEY_CHECKS = 1;
```

# インデックス

## 例
- hogesテーブルは次の2つのインデックスを持つ
  - age_cdとline_noの複合index
  - customer_codeのindex

```mysql
mysql> show index from hoges \G;
*************************** 1. row ***************************
        Table: hoges
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 2. row ***************************
        Table: hoges
   Non_unique: 1
     Key_name: index_hoges_on_age_cd_and_line_no
 Seq_in_index: 1
  Column_name: age_cd
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 3. row ***************************
        Table: hoges
   Non_unique: 1
     Key_name: index_hoges_on_age_cd_and_line_no
 Seq_in_index: 2
  Column_name: line_no
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 4. row ***************************
        Table: hoges
   Non_unique: 1
     Key_name: index_hoges_on_customer_code
 Seq_in_index: 1
  Column_name: customer_code
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:

```


# その他
## 接続
```console
$ mysql -uroot -ppassword -h localhost hoge_development_db
```

## SQLの一括投入

ターミナルから

```terminal
$ ls -al data
hogehoge.sql  ageage.sql   import.sh   user.conf
$ cd data
$ chmod 600 user.cnf
$ sh import.sh ./user.conf
```


```data/user.conf
[client]
user = root
password = root
host = localhost
database = app
```

```data/import.sh
#!/bin/bash

sqlfiles=*.sql
 
for filepath in ${sqlfiles}
do
    mysql --defaults-extra-file=$1 < ${filepath}
done
```

# Schema

## Key

```sql
> DESCRIBE hoges; 
> DESC hoges; 
> SHOW COLUMNS FROM hoges;
```

| Key |説明
:--|:--
PRI | 主キー
UNI | ユニークキー
MUL | 主キーでもユニークキーでもないインデックス

- KeyがMUL場合、列は、列内で指定された値の複数の出現が許可される非一意索引の最初の列です。
  - refs https://code.i-harness.com/ja/q/512501

## Kind of Index
| Key |説明
|:--|:--|
| 主キー | テーブルの中のデータを識別する（重複する値のない）１つまたは複数のフィールド（１つのみ設定、Nullは含まない） |
| ユニークキー | 重複する値のない１つまたは複数のフィールド（複数設定可能でNullを許可） |
| インデックス | 指定されたフィールドのみ検索対象として、検索速度を早くする（主キー、ユニークキー以外で複数設定可能、Nullを許可） |
| フルテキスト | SELECT文での検索可能（MyISAM型のデータベースでのみ使用可能） |



# 設計

## 量の名前
量の名前| 次元
---|---
スカラ|0次元、値
ベクトル|1次元、配列
テンソル|2次元、多次元配列


## 正規化
正規化| 説明
---|---
第1|データをスカラ値にする
第2|PKで分ける
第3|FKで分ける

## アソシエーション

### 1:多
- 芸能人と一般人のパターン
- 多が1を知る必要がある(FK)

### 多:多
- タグ付けのテーブルなどで使われるパターン
- 2次元では表現できないので、中間テーブルを用意する

### 1:1
- 目的ごとに2つのテーブルを分けるパタン
- 意味論的と変更可能性を考慮して設計する
  - 例えば、UserテーブルとそのUserのCredentialのテーブルの場合、
  - UserがCredentialのIDを所有する
      - 意味論的に`users.credential_id`の方が`credentials.user_id`より良いから
      - もし、`credentials.user_id`にすると、credentailを作る時に、userが必要になるから
  - ただし、今後の変更で1:多になるなら
      - CredentialがUserのIDを所有する
      - 正規化ではベクトルは許容されないから



# 構文

## 指定順序と実行順序
The conceptual order of query processing is:

指定順序 | 実行順序 | 句 | 内容
---|---|---|---
2 | 1 | FROM(Including JOINs) | 実行対象のテーブルを指定する
3 | 2 | WHERE | テーブルに対してレコードの抽出条件を指定する
4 | 3 | GROUP BY | レコードをグループ化する
5 | 4 | HAVING | グループ化した結果に対して抽出条件を指定する
1 | 5 | SELECT | 取得（表示）する列を指定する
6 | 6 | ORDER BY | 取得した列を並び替える


## CRUD

## コメント
```sql
-- コメント
/*
   複数行
　 コメント
*/
```
## 条件分岐
### CASE式

## まとめ系
### GROUP BY句
- 複数のレコードをグルーピングする
- データ操作には、FROM句とWHERE句の結果が使用される
- GROUP BYを使った場合、SELECT句に加工なしで使える項目は、GROUP BY句に使った項目に依存する
- 要はGROUP BY句で指定していない、重複するカラムをSELECT文に集計関数抜きなどでは指定できない

### HAVING句
- GROPU BY用のWHERE句
- HAVING句の指定はグループ(複数行のレコード)に対して行われる
- HAVING構文は、集計関数を含めることができる点を除けば、WHERE 構文と同じ
- WHERE 検索条件がグループ化操作の前に適用されるのに対し、HAVING 検索条件はグループ化操作の発生後に適用される

## エイリアス
```
fields AS name1
(
) AS name2
```

## サブクエリ
- 主にSELECT, JOIN, FROM, WHERE, HAVINGなどで括弧と共に使う。
- 相関サブクエリはエイリアスなどを使って、外側の結果を参照したもの
- 基本的にサブクエリは一つの関数として読む

タイプ|読み方
---|---
(非相関)サブクエリ|サブクエリの引数はなく。1度だけ実行される
相関サブクエリ|サブクエリの引数があるため、引数が変わったタイミングで実行される

### ANY, ALL, EXISTS演算子
- ANY, ALL, EXISTS演算子はサブクエリの「TRUE」または「FALSE」を評価する

### ANY(SOME)
- 比較演算子と共に使用する
- 結果セットの値のいずれかが比較演算子の関係を満たす場合にTRUEを返す

### ALL
- 比較演算子と共に使用する
- 結果セットの値の全てが比較演算子の関係を満たす場合にTRUEを返す

### EXISTS
- サブクエリの生成した値が存在する場合はTRUEを返す
- 否定はNOT EXISTS

## DISTINCT


## 結合系

### UNION
和集合

- 重複を削除する場合

```sql
/* BLUE + RED */

SELECT * FROM BLUE
UNION
SELECT * FROM RED ;
```

- 重複を削除しない場合 

```sql
/* BLUE + RED */

SELECT * FROM BLUE
UNION ALL
SELECT * FROM RED ;
```

### EXPECT
差集合

```sql
/* BLUE - RED(BLUEにはあって、REDにはないもの) */

SELECT * FROM BLUE
EXCEPT
SELECT * FROM RED ;
```


### INTERSECT
積集合

```sql
/* BLUE * RED */
 
SELECT * FROM BLUE
INTERSECT
SELECT * FROM RED ;
```



## 集計関数

| 関数名 | 機能 |
|:--|:--|
| COUNT | 集計対象の行数を出力 |
| MAX | 集計対象のなかで最大のものを出力 |
| MIN | 集計対象のなかで最小のものを出力 |
| SUM | 集計対象の合計値を出力 |
| AVG | 集計対象の平均を出力 |

## 例

### GROPU BYしてその中の最大の値を含む行を取得する

```sql

-- table

id  home  datetime     player   resource
---|-----|------------|--------|---------
1  | 10  | 04/03/2009 | john   | 399 
2  | 11  | 04/03/2009 | juliet | 244
5  | 12  | 04/03/2009 | borat  | 555
3  | 10  | 03/03/2009 | john   | 300
4  | 11  | 03/03/2009 | juliet | 200
6  | 12  | 03/03/2009 | borat  | 500
7  | 13  | 24/12/2008 | borat  | 600
8  | 13  | 01/01/2009 | borat  | 700

-- wants result

id  home  datetime     player   resource 
---|-----|------------|--------|---------
1  | 10  | 04/03/2009 | john   | 399
2  | 11  | 04/03/2009 | juliet | 244
5  | 12  | 04/03/2009 | borat  | 555
8  | 13  | 01/01/2009 | borat  | 700


```

```sql
SELECT tt.*
FROM topten tt
INNER JOIN
    (SELECT home, MAX(datetime) AS MaxDateTime
    FROM topten
    GROUP BY home) groupedtt 
ON tt.home = groupedtt.home 
AND tt.datetime = groupedtt.MaxDateTime

```

### 別テーブルに存在するIDの合計値を取得したい


```
- 全社員の売上の合計を表示したい。

社員マスタ
社員コード	社員名	部署コード	年齢
1	テスト社員1	1	22
2	テスト社員2	2	30
3	テスト社員3	null	20

売上明細
売上NO	社員コード	売上日	売上金額
1	1	20150401	200
2	1	20150402	300
3	2	20150408	100
4	2	20150501	150
5	3	20150505	550

```

```sql
SELECT
   MAX(T1.社員名) AS 社員名
   ,SUM(T2.売上金額) AS 売上金額
FROM
  社員マスタ AS T1
LEFT JOIN
  売上明細 AS T2
ON
  T1.社員コード = T2.社員コード
GROUP BY
  T1.社員コード


--実行結果

社員名	売上金額
テスト社員1	500
テスト社員2	250
テスト社員3	550
```



### 別テーブルにIDが存在していたら、元のレコードを取得する

```
- 売上のある社員のみ表示したい。

社員マスタ
社員コード	社員名	部署コード	年齢
1	テスト社員1	1	22
2	テスト社員2	2	30
3	テスト社員3	null	20
4	テスト社員4	1	30

売上明細
売上NO	社員コード	売上日	売上金額
1	1	20150401	200
2	1	20150402	300
3	2	20150408	100
4	2	20150501	150
5	3	20150505	550
```

```sql
SELECT
     社員名
FROM
     社員マスタ AS T1
WHERE
     EXISTS(
          SELECT
               1
          FROM
               売上明細 AS S1
          WHERE
               T1.社員コード = S1.社員コード
     )


--↓EXISTSを使わない場合
SELECT
     MAX(T1.社員名) AS 社員名
FROM
     社員マスタ AS T1
JOIN
     売上明細 AS T2
ON
     T1.社員コード = T2.社員コード
GROUP BY
     T1.社員コード
```

### 自己結合

- テーブルが再帰的(同一テーブルへのparent_idを持っている)時などに有効


| 従業員コード | 従業員名 | 上司コード |
|:--|:--|:--|
| 00001 | チャーリー・ブラウン | <NULL> |
| 00002 | ペパーミント・パティ | <NULL> |
| 00003 | ルシール・ヴァン・ぺルト | 00001 |
| 00004 | ライナス・ヴァン・ぺルト | 00003 |
| 00005 | シュローダー | 00001 |
| 00006 | マーシー | 00002 |

```sql 
SELECT
     部下.従業員コード
     ,部下.従業員名
     ,上司.従業員名 AS 上司の名前
FROM
     従業員 AS 部下
JOIN 
     従業員 AS 上司
ON
     部下.上司コード = 上司.従業員コード
ORDER BY
    部下.従業員コード
```


| 従業員コード | 従業員名 | 上司の名前名 |
|:--|:--|:--|
| 00003 | ルシール・ヴァン・ぺルト | チャーリー・ブラウン |
| 00004 | ライナス・ヴァン・ぺルト | ルシール・ヴァン・ぺルト |
| 00005 | シュローダー | チャーリー・ブラウン |
| 00006 | マーシー | ペパーミント・パティ |


## Gist

- 外部テーブルとJOINしたい時の例

```sql

/*
  　パターン1
*/


SELECT
        i.*,
        c.name AS rank
FROM
        inquiries AS i
INNER JOIN
        constants AS c
ON
        c.key = i.rank
WHERE
        i.id = 1
        AND
        c.field = 'RANK'
\G

/*
  　パターン2
*/

SELECT
        i.*,
        c2.name AS rank
FROM
        inquiries AS i
INNER JOIN (
        SELECT
                *
        FROM
                constants  AS c
        WHERE
                c.field = 'RANK'
) AS c2
ON
        c2.key = i.rank
WHERE
        i.id = 1
\G
```

- cd値でGroup byし、最小の行(no)の値をもつレコードを取得し、その中に既読がないもののカウント

```sql
SELECT
        COUNT(*) AS unread_count
FROM
        letters AS i
INNER JOIN (
        SELECT
                cd,
                MIN(no) AS first_letter_no
        FROM
                letters
        GROUP BY
                cd
) AS i2
ON
        i.cd = i2.cd AND
        i.no = i2.first_letter_no
WHERE 0 = ANY(
        SELECT
                opend
        FROM
                letters AS i3
        WHERE
                i.cd = i3.cd
)
\G;
```


# Datatypes

## charとvarcharの違い

| type | 違い |
:--|:--
| char | 固定長。最大255byte。CD値なのに |
| varchar | 可変長。0~65535文字まで。可変長なのでメモリ効率がいい |

NOTE
- 文字数を記録するために1バイト消費する点に注意が必要。
- 例えばvarchar(4)に"abcd"と格納した場合，5バイト分メモリを消費する．



## varcharとtextの違い


| type | 初期値 | 最大文字長 | 最大sizeの数の意味 |
|:--|:--|:--|:--|
| varchar | 指定可 | 指定可 | 文字数で指定(日本語でも、最大65535文字まで入る) |
| text | 指定不可 | 指定不可 | byteで指定(1文字3byteのutf8で日本語入れたら65535 の 1/3文字 入る) |

- InnoDBの8KBの壁に当たることもあるので、varcharで良い

## 制約
- FK制約
- UNIQUE
- PRIMARY
- NOT NULL

### 確認方法
```mysql
> SHOW CREATE TABLE something\G
```

## Key
- MUL 重複可能インデックス
- PRI primary key
- UNI unique

## JOINの使い分け
 - INNER JOIN
   - 所謂絞り込みで使用する
 - OUTER JOIN
   - 全件取得など

## Loading
  - Eager loading 
  - Lazy Loading

# 参考文献
http://tamata78.hatenablog.com/entry/2015/10/30/140000
https://github.com/jinzhu/gorm/issues/1053#issuecomment-265344516
https://stackoverflow.com/questions/5452760/how-to-truncate-a-foreign-key-constrained-table
http://omokichi.sakura.ne.jp/blog/mysql%E3%81%ABsql%E3%83%95%E3%82%A1%E3%82%A4%E3%83%AB%E3%82%92%E4%B8%80%E6%8B%AC%E3%82%A4%E3%83%B3%E3%83%9D%E3%83%BC%E3%83%88%E3%81%99%E3%82%8B%E6%96%B9%E6%B3%95/
http://blog.doli.jp/blog/2012/post490/
http://hrt0kmt.hatenablog.com/entry/2014/07/09/000000
http://write-remember.com/archives/4805/
https://dev.classmethod.jp/server-side/db/difference-where-and-having/
https://stackoverflow.com/questions/24127932/mysql-query-clause-execution-order
https://qiita.com/suzukito/items/edcd00e680186f2930a8
http://d.hatena.ne.jp/CAMUS/20060626/1151334328
https://stackoverflow.com/questions/612231/how-can-i-select-rows-with-maxcolumn-value-distinct-by-another-column-in-sql
https://stackoverflow.com/questions/30749913/which-performs-first-where-clause-or-join-clause
http://omachizura.com/sql/GROUP%20BY%E5%8F%A5%E3%81%A7%E3%83%87%E3%83%BC%E3%82%BF%E3%81%AE%E9%9B%86%E8%A8%88%E3%83%BB%E9%9B%86%E7%B4%84%E3%82%92%E8%A1%8C%E3%81%86.html
http://www.techscore.com/tech/sql/index.html/
https://doruby.jp/users/takeshita/entries/%E6%84%8F%E8%AD%98%E3%81%97%E3%81%AA%E3%81%84%E3%81%A8%E3%81%BE%E3%81%9A%E3%81%84%EF%BC%9F-ActiveRecord-SQL%E3%82%AD%E3%83%A3%E3%83%83%E3%82%B7%E3%83%A5%E3%81%AE%E7%BD%A0
https://stackoverflow.com/questions/1299374/what-is-eager-loading