(ゆるりと更新) 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 逆引きリファレンス

デフォルトの文字コード

(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

文字コードの設定

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

[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

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でユーザを作成し、権限を設定する方法 | サービス | プロエンジニア
  • MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.7.1.4 GRANT 構文

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

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				前回ログイン日時

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

※ 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)

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)

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
• MySQL :: MySQL 5.6 リファレンスマニュアル :: 12.7 日付および時間関数

指定の日付(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)