Go to Qiita Advent Calendar 2024 Top
LoginSignup
3
5

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

@zaki-lknrin株式会社エーピーコミュニケーションズ

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

Last updated at Posted at 2018-11-13

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時に指定していない場合は不要。

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

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)

※ 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とかのとき、どう指定すればいいの？

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

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

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
3
5

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?