(ゆるりと更新) MySQL / SQLメモ

SQL関連で覚えたこと・チェックしたことを書いていきます。(今のところMySQL)

# MySQLのコマンド

## DB一覧

```
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| zz_java_study      |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)
```

## テーブル一覧

※ 対象DBが未指定の場合は先に`use <table-name>;`でDBを指定する

```
mysql> show tables;
+-------------------------+
| Tables_in_zz_java_study |
+-------------------------+
| cart_detail_table       |
| category_table          |
| history_table           |
| item_table              |
| user_id_table           |
+-------------------------+
5 rows in set (0.00 sec)
```

## カラム一覧(テーブル定義)

```
mysql> describe item_table;
+-------------------+-------------+------+-----+---------+----------------+
| Field             | Type        | Null | Key | Default | Extra          |
+-------------------+-------------+------+-----+---------+----------------+
| item_id           | int(11)     | NO   | PRI | NULL    | auto_increment |
| item_name         | varchar(64) | YES  |     | NULL    |                |
| price             | int(11)     | NO   |     | 0       |                |
| registration_date | datetime    | NO   |     | NULL    |                |
| category_id       | int(11)     | NO   |     | NULL    |                |
| point             | int(11)     | NO   |     | NULL    |                |
+-------------------+-------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
```

DATETIME型をNOT NULL制約にする場合、要件的に問題なければdefault値を`now()`とかしておくと、insert時に省略できて便利かも。

## レコード一覧

これは普通にSQL叩く

```
mysql> select * from item_table;
+---------+------------+-------+---------------------+-------------+-------+
| item_id | item_name  | price | registration_date   | category_id | point |
+---------+------------+-------+---------------------+-------------+-------+
|       1 | 白菜       |   300 | 2018-11-10 00:00:00 |           1 |    50 |
|       2 | トイレ洗剤 |   200 | 2018-11-10 00:00:00 |           2 |    15 |
|       3 | サンマ     |   150 | 2018-11-10 00:00:00 |           3 |     5 |
|       4 | お茶       |   150 | 2018-11-10 00:00:00 |           4 |    15 |
|       5 | おにぎり   |   100 | 2018-11-10 00:00:00 |           5 |    10 |
|       6 | ビール     |   150 | 2018-11-10 00:00:00 |           4 |    15 |
+---------+------------+-------+---------------------+-------------+-------+
```

## DBの文字コード

- windows10

```
mysql> show create database memoapp_db;
+------------+---------------------------------------------------------------------------------------------------+
| Database   | Create Database                                                                                   |
+------------+---------------------------------------------------------------------------------------------------+
| memoapp_db | CREATE DATABASE `memoapp_db` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ |
+------------+---------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
```

- ubuntu 18.04

```
mysql> show create database memoapp_db;
+------------+-----------------------------------------------------------------------+
| Database   | Create Database                                                       |
+------------+-----------------------------------------------------------------------+
| memoapp_db | CREATE DATABASE `memoapp_db` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+------------+-----------------------------------------------------------------------+
1 row in set (0.00 sec)
```

## テーブルの文字コード

ubuntu 18.04で実行

```
mysql> show create table memo_data;
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                                                                                                                                                                                                                                                                                                                                        |
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| memo_data | CREATE TABLE `memo_data` (
  `memo_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `category` int(11) DEFAULT NULL COMMENT '????',
  `title` varchar(64) DEFAULT NULL COMMENT '????',
  `memo` text COMMENT '??',
  `create_date` datetime DEFAULT NULL COMMENT '???',
  `modified_date` datetime DEFAULT NULL COMMENT '???',
  PRIMARY KEY (`memo_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 |
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> 
```

[データベースの文字コードを調べる - MySQL 逆引きリファレンス](http://mysql.javarou.com/dat/000573.html)

## デフォルトの文字コード

(ubuntu18.04で実行)

```
mysql> show variables like '%char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

```

[MySQL 文字コード確認 - Qiita](https://qiita.com/yukiyoshimura/items/d44a98021608c8f8a52a)

## 文字コードの設定

サーバの設定ファイルに以下を追加し再起動する

```ini
[mysqld]
character-set-server=utf8mb4

[client]
default-character-set=utf8mb4
```

ubuntu18.04であれば`/etc/mysql/mysql.conf.d`以下の`.cnf`ファイルを読み込んでくれるので、
例えば `/etc/mysql/mysql.conf.d/charset.cnf` ファイルを作成すれば良い。

## 作成済みDB/tableの文字コードの変更

ubuntu18.04上でlatin1になっているdatabaseの文字コードをutf8に変更する。

※ latinをutf8mb4に変更しても、すでに存在するレコードの文字化けは治らなかったので、作り直したほうがよさげ


```
mysql> alter database memoapp_db character set utf8mb4;
Query OK, 1 row affected (0.00 sec)

mysql> show create database memoapp_db;
+------------+------------------------------------------------------------------------+
| Database   | Create Database                                                        |
+------------+------------------------------------------------------------------------+
| memoapp_db | CREATE DATABASE `memoapp_db` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+------------+------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> 
```

DBのデフォルトエンコードがutf8になった。
次にテーブル

```
mysql> alter table memo_data character set utf8mb4;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table memo_data;
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                                                                                                                                                                                                                                                                                                             |
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| memo_data | CREATE TABLE `memo_data` (
  `memo_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `category` int(11) DEFAULT NULL COMMENT '????',
  `title` varchar(64) DEFAULT NULL,
  `memo` text,
  `create_date` datetime DEFAULT NULL COMMENT '???',
  `modified_date` datetime DEFAULT NULL COMMENT '???',
  PRIMARY KEY (`memo_id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 |
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
```

カラム

```
mysql> describe memo_data;
+---------------+-------------+------+-----+---------+----------------+
| Field         | Type        | Null | Key | Default | Extra          |
+---------------+-------------+------+-----+---------+----------------+
| memo_id       | int(11)     | NO   | PRI | NULL    | auto_increment |
| category      | int(11)     | YES  |     | NULL    |                |
| title         | varchar(64) | YES  |     | NULL    |                |
| memo          | text        | YES  |     | NULL    |                |
| create_date   | datetime    | YES  |     | NULL    |                |
| modified_date | datetime    | YES  |     | NULL    |                |
+---------------+-------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

mysql> alter table memo_data modify title varchar(64) character set utf8mb4;
Query OK, 2 rows affected (0.04 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> alter table memo_data modify memo text character set utf8mb4;
Query OK, 2 rows affected (0.12 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> 
```

[MySQLの文字コードをutf8mb4に変更 - Qiita](https://qiita.com/deco/items/bfa125ae45c16811536a)

# DBとユーザの作成

- DB作成

rootとかの管理ユーザでmysqlのCLI起動して

```
mysql> create database example_db;
Query OK, 1 row affected (0.05 sec)
```

- ユーザ作成

ユーザ`zaki`(パスワードも`zaki`)のアカウントを作成する

```
mysql> create user zaki identified by "zaki";
Query OK, 0 rows affected (0.06 sec)
```

- 作成したユーザに指定DBに対する全権限を与える

```
mysql> grant all on example_db.* to zaki;
Query OK, 0 rows affected (0.11 sec)
```

基本的な書式は `grant <権限> on <データベース名>.<テーブル名> to <ユーザ名>@<ホスト名>;`
ホスト名は`crate user`時に指定していない場合は不要。

- 参考
    - [MySQLでユーザを作成し、権限を設定する方法 | サービス | プロエンジニア](http://proengineer.internous.co.jp/content/columnfeature/6638)
    - [MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.7.1.4 GRANT 構文](https://dev.mysql.com/doc/refman/5.6/ja/grant.html)


ユーザの作成と権限の付与ができれば、ログインできる

```
C:\Users\zaki>mysql -u zaki -p
Enter password: ****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 8.0.13 MySQL Community Server - GPL

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| example_db         |
| information_schema |
+--------------------+
2 rows in set (0.00 sec)

mysql>
```

※ 権限のあるデータベースのみ見える

# テーブル作成

テーブル`user_info`を作ってみる

|カラム名|PK|データ型|サイズ|NOT NULL|デフォルト値|コメント|その他|
|-------|---|------|-----|--------|----------|---------|----|
|user_id|✓|int     |11   |✓       |       |ユーザID|auto_increment|
|user_name||VARCHAR|64  |✓       |       |ユーザ名||
|point|    |int     |11   |✓       |0       |ポイント||
|signup_date||DATETIME| |✓      |現在時刻|登録日時||
|last_login||DATETIME|  |        |       |前回ログイン日時||

```sql
create table user_info(
    user_id INT(11) auto_increment not null comment 'ユーザID'
    , user_name VARCHAR(64) not null comment 'ユーザ名'
    , point INT(11) default 0 not null comment 'ポイント'
    , signup_date DATETIME default now() not null comment '登録日時'
    , last_login DATETIME comment '前回ログイン日時'
    , primary key (user_id)
);
```

このSQLを実行すればテーブル作成される

```
mysql> create table user_info(
    ->     user_id INT(11) auto_increment not null comment 'ユーザID'
    ->     , user_name VARCHAR(64) not null comment 'ユーザ名'
    ->     , point INT(11) default 0 not null comment 'ポイント'
    ->     , signup_date DATETIME default now() not null comment '登録日時'
    ->     , last_login DATETIME comment '前回ログイン日時'
    ->     , primary key (user_id)
    -> );
Query OK, 0 rows affected (0.16 sec)

mysql>
mysql> show tables;
+----------------------+
| Tables_in_example_db |
+----------------------+
| user_info            |
+----------------------+
1 row in set (0.00 sec)

mysql> describe user_info;
+-------------+-------------+------+-----+-------------------+-------------------+
| Field       | Type        | Null | Key | Default           | Extra             |
+-------------+-------------+------+-----+-------------------+-------------------+
| user_id     | int(11)     | NO   | PRI | NULL              | auto_increment    |
| user_name   | varchar(64) | NO   |     | NULL              |                   |
| point       | int(11)     | NO   |     | 0                 |                   |
| signup_date | datetime    | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
| last_login  | datetime    | YES  |     | NULL              |                   |
+-------------+-------------+------+-----+-------------------+-------------------+
5 rows in set (0.00 sec)
```

- 参考
    - [MySQLで主キー制約を設定する「PRIMARY KEY」 | UX MILK](https://uxmilk.jp/12787)

※ DATETIMEのデフォルト値は`now()`じゃなくて別の指定があるっぽい…あとで調べる
※ PK設定なしでauto_incrementをセットしてテーブルを作ろうとするとエラーになる

```
mysql> create table user_info(
    ->     user_id INT(11) auto_increment not null comment 'ユーザID'
    ->     , user_name VARCHAR(64) not null comment 'ユーザ名'
    ->     , signup_date DATETIME not null comment '登録日時'
    ->     , last_login DATETIME comment '前回ログイン日時'
    -> );
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
```

# レコードの追加(insert)

```sql
mysql> insert into user_info (user_name) values ("zaki");
Query OK, 1 row affected (0.07 sec)

mysql> select * from user_info;
+---------+-----------+-------+---------------------+------------+
| user_id | user_name | point | signup_date         | last_login |
+---------+-----------+-------+---------------------+------------+
|       1 | zaki      |     0 | 2018-11-20 21:54:15 | NULL       |
+---------+-----------+-------+---------------------+------------+
1 row in set (0.00 sec)

mysql>
```

# レコードの更新(update)

```sql
mysql> update user_info set point = 30 where user_id = 1;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from user_info;
+---------+-----------+-------+---------------------+------------+
| user_id | user_name | point | signup_date         | last_login |
+---------+-----------+-------+---------------------+------------+
|       1 | zaki      |    30 | 2018-11-20 21:54:15 | NULL       |
+---------+-----------+-------+---------------------+------------+
1 row in set (0.00 sec)

mysql>
```

複数のカラムを更新する場合は、`,`で続ける

`update user_info set point = 30, user_name = 'zaki-lknr' where user_id = 1;`

# テーブルの削除

レコードがあろうがなかろうが問答無用で削除。
`rm -rf <directory>`みたいな感じかな

```
mysql> show tables;
+----------------------+
| Tables_in_example_db |
+----------------------+
| user_info            |
+----------------------+
1 row in set (0.00 sec)

mysql> drop table user_info;
Query OK, 0 rows affected (0.13 sec)

mysql> show tables;
Empty set (0.00 sec)
```

# DATETIME型について

insertとかのとき、どう指定すればいいの？

基本は公式ドキュメントを見よう

- [MySQL :: MySQL 8.0 Reference Manual :: 12.7 Date and Time Functions](https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html)
- [MySQL :: MySQL 5.6 リファレンスマニュアル :: 12.7 日付および時間関数](https://dev.mysql.com/doc/refman/5.6/ja/date-and-time-functions.html)

## 指定の日付(YYYYmmdd)

```
mysql> insert into item_table (item_name, price, registration_date, category_id) values ("白菜", 300, cast('2018-11-10' as date), 1);
Query OK, 1 row affected (0.03 sec)

mysql>
mysql> select * from item_table;
+---------+-----------+-------+---------------------+-------------+
| item_id | item_name | price | registration_date   | category_id |
+---------+-----------+-------+---------------------+-------------+
|       1 | 白菜      |   300 | 2018-11-10 00:00:00 |           1 |
+---------+-----------+-------+---------------------+-------------+
1 row in set (0.00 sec)
```

この場合、時刻は0時0分0秒になる。

レコードのdatetimeカラムの更新テスト

```
mysql> update item_table set registration_date = cast('2018-11-13' as date) where item_id = 1;
Query OK, 1 row affected (0.09 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from item_table where item_id = 1;
+---------+-----------+-------+---------------------+-------------+-------+
| item_id | item_name | price | registration_date   | category_id | point |
+---------+-----------+-------+---------------------+-------------+-------+
|       1 | 白菜      |   300 | 2018-11-13 00:00:00 |           1 |    50 |
+---------+-----------+-------+---------------------+-------------+-------+
1 row in set (0.00 sec)
```

## 指定の日時(YYYYmmdd HHMMss)

```
mysql> update item_table set registration_date = cast('2018-11-13 22:15:31' as datetime) where item_id = 1;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from item_table where item_id = 1;
+---------+-----------+-------+---------------------+-------------+-------+
| item_id | item_name | price | registration_date   | category_id | point |
+---------+-----------+-------+---------------------+-------------+-------+
|       1 | 白菜      |   300 | 2018-11-13 22:15:31 |           1 |    50 |
+---------+-----------+-------+---------------------+-------------+-------+
1 row in set (0.00 sec)
```

`as datetime`にすればOK  

※ 時刻まで指定しながら`as date`にする(`'2018-11-13 22:15:31' as date`を指定する)と、時刻部分はやはり00:00:00になるので注意

## 今の日付を指定(YYYYmmdd)

`now()`を使う

```
mysql> update item_table set registration_date = cast(now() as date) where item_id = 1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from item_table where item_id = 1;
+---------+-----------+-------+---------------------+-------------+-------+
| item_id | item_name | price | registration_date   | category_id | point |
+---------+-----------+-------+---------------------+-------------+-------+
|       1 | 白菜      |   300 | 2018-11-13 00:00:00 |           1 |    50 |
+---------+-----------+-------+---------------------+-------------+-------+
1 row in set (0.00 sec)
```

2018年11月13日に実行するとこうなる

## 今の日時を指定(YYYYmmdd HHMMss)

`now()`にくわえて`as datetime`を指定

```
mysql> update item_table set registration_date = cast(now() as datetime) where item_id = 1;
Query OK, 1 row affected (0.07 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from item_table where item_id = 1;
+---------+-----------+-------+---------------------+-------------+-------+
| item_id | item_name | price | registration_date   | category_id | point |
+---------+-----------+-------+---------------------+-------------+-------+
|       1 | 白菜      |   300 | 2018-11-13 22:21:20 |           1 |    50 |
+---------+-----------+-------+---------------------+-------------+-------+
1 row in set (0.00 sec)
```

## 日付を加算 ( ADDDATE() )

今日から5日後

```
mysql> update item_table set registration_date = cast(adddate(now(), 5) as date) where item_id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from item_table where item_id = 1;
+---------+-----------+-------+---------------------+-------------+-------+
| item_id | item_name | price | registration_date   | category_id | point |
+---------+-----------+-------+---------------------+-------------+-------+
|       1 | 白菜      |   300 | 2018-11-18 00:00:00 |           1 |    50 |
+---------+-----------+-------+---------------------+-------------+-------+
1 row in set (0.00 sec)
```

先週

```
mysql> update item_table set registration_date = cast(adddate(now(), -7) as date) where item_id = 1;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from item_table where item_id = 1;
+---------+-----------+-------+---------------------+-------------+-------+
| item_id | item_name | price | registration_date   | category_id | point |
+---------+-----------+-------+---------------------+-------------+-------+
|       1 | 白菜      |   300 | 2018-11-06 00:00:00 |           1 |    50 |
+---------+-----------+-------+---------------------+-------------+-------+
1 row in set (0.00 sec)
```

## 時刻を加算 ( ADDTIME() )

3時間半前

```
mysql> update item_table set registration_date = cast(addtime(now(), '-3:30:00') as datetime) where item_id = 1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from item_table where item_id = 1;
+---------+-----------+-------+---------------------+-------------+-------+
| item_id | item_name | price | registration_date   | category_id | point |
+---------+-----------+-------+---------------------+-------------+-------+
|       1 | 白菜      |   300 | 2018-11-13 18:59:47 |           1 |    50 |
+---------+-----------+-------+---------------------+-------------+-------+
1 row in set (0.00 sec)
```