この記事について

SQLを体系的に学習した経験が無い人間が一連の機能を触りつつ学ぶ過程で備考や噛み砕いたことをつらつらと書きます。自習に伴って記載しているため、記事を通して書きっぷりが多少変化していることはご容赦ください。

学習にあたって参考にしている書籍は『基礎からのMySQL ¹』です。

サンプルに使用したデータは、スマートフォンアプリ『メダロットS』の中にあるメダロットのアルバムの中から数値や名前をお借りしています。

MySQL環境

本記事ではDockerでさくっとMySQLを導入して実際に操作しながら学習していきます。
この記事の後、SQL AlchemyによるORMを学習する予定のため、それを意識した構成にしています。

以下はディレクトリ構造( initdb.d と conf.d はファイルではなくディレクトリ)

tree

.
├── docker
│   ├── mysql
│   │   ├── Dockerfile
│   │   ├── conf.d
│   │   │   └── my.cnf
│   │   ├── initdb.d
│   │   └── mysql.env
│   └── python
│       └── Dockerfile
├── docker-compose.yml
└── log
    └── mysql

./docker-compose.yml

version: "3.3"

services:
  training_db:
    container_name: "training_db"
    build: ./docker/mysql
    restart: always
    tty: true    # ポート待ち受けしていないコンテナを起動し続けるようにするためのコマンド?
    environment:
      MYSQL_DATABASE: medabots_db
      MYSQL_USER: user
      MYSQL_PASSWORD: password
      MYSQL_ROOT_PASSWORD: password
      TZ: "Asia/Tokyo"
    ports:
      - "3306:3306"
    volumes:
      - ./docker/mysql/initdb.d:/docker-entrypoint-initdb.d    # デフォルトのSQLコマンド実行
      - ./docker/mysql/conf.d:/etc/mysql/conf.d    # MySQLの基本設定(文字化け対策)
      - ./log/mysql:/var/log/mysql    # ログの保存
    networks:
      - db-network
  python:
    links:    # dbコンテナが起動してから実行する
      - training_db
    container_name: "python"
    build: ./docker/python
    tty: true
    ports:
      - "8000:8000"
    volumes:
      - .:/usr/src/server
    networks:
      - db-network

# ネットワークブリッジ
networks:
  db-network:
    driver: bridge

./docker/mysql/Dockerfile

FROM mysql:8.0

./docker/mysql/mysql.env

MYSQL_DATABASE=medabots_db
MYSQL_USER=root
MYSQL_ROOT_PASSWORD=password
MYSQL_PASSWORD=password

./docker/mysql/conf.d/my.cnf

[mysqld]
character-set-server=utf8
skip-character-set-client-handshake
default-storage-engine=INNODB
explicit-defaults-for-timestamp=1
general-log=1
general-log-file=/var/log/mysql/mysqld.log

[mysqldump]
default-character-set=utf8

[mysql]
default-character-set=utf8

[client]
default-character-set=utf8

./docker/python/Dockerfile

FROM python:3.9

RUN apt update \
    && apt install -y default-mysql-client \
    && apt clean

WORKDIR /usr/src/server
ADD requirements.txt .
RUN pip install --trusted-host pypi.python.org -r requirements.txt

./docker/python/requirements.txt

mysqlclient==2.0.1

テーブルやデータベースの操作

ここからMySQL基礎練スタート

テーブル作成までのみちのり(Database選択まで)

Docker-composeを起動してしてDBサーバーをスタートする

$ docker-compose up -d

"python"コンテナに入る

docker-compose run python bash

mysqlコマンドを実行する(rootユーザで入る)

ユーザー作成や権限を与える項目は一旦飛ばす
mysql -h [コンテナ(ホスト)名] -u [ユーザー名] -p[パスワード]
パスワードをくっつけて書くの最初びっくりした
ホスト名を指定せずに起動するとエラーが発生 Can't connect to local MySQL server through socket 'var/run/mysqld/mysqld.sock' (2)
コンテナ越しにつないでいるのでホスト名の指定が必須であったらしい

mysql -h training_db -u root -ppassword

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.26 MySQL Community Server - GPL

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MySQL [(none)]>

この状態ではまだ使用するデータベースを指定していない(none)ので使用できるデータベースを表示する

僕だけかもしれないがDATABASE(単数形)ではなくDATABASES(複数形)なのに注意。エラーでびっくりする。

使用できるデータベース一覧を表示

MySQL [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| medabots_db        |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.006 sec)

しめしめ、 ./docker/mysql/mysql.env で作成しておいた medabots_db があるのでこれを指定する。

選択しているDBをnoneからmedabots_dbに変更できた

MySQL [(none)]> USE medabots_db;
Database changed
MySQL [medabots_db]>

SELECT DATABASE(); で現在使用しているDatabaseを確認できる

MySQL [medabots_db]> SELECT DATABASE();
+-------------+
| DATABASE()  |
+-------------+
| medabots_db |
+-------------+
1 row in set (0.001 sec)

テーブル作成

テーブルを定義していく[ head_tb ]

	型番	メダロット名	頭パーツ名	装甲
カラム名 (列名)	model_no	name	head	hit_point
データ型	VARCHAR(10)	VARCHAR(10)	VARCHAR(10)	INT

定義したので CREATE TABLE する

CREATE TABLE [テーブル名] ([カラム名] [データ型], [カラム名] [データ型]....);

MySQL [medabots_db]> CREATE TABLE head_tb (model_no VARCHAR(10), name VARCHAR(10), head VARCHAR(10), hit_point INT);
Query OK, 0 rows affected (0.033 sec)

Query OK, と出ればいいらしい
Tableが正しく作成されたかを確認する

MySQL [medabots_db]> SHOW TABLES;
+-----------------------+
| Tables_in_medabots_db |
+-----------------------+
| head_tb               |
+-----------------------+
1 row in set (0.005 sec)

できてる！！
そして、定義したテーブルの構造を確認するためには DESC を使用する

MySQL [medabots_db]> DESC head_tb;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| model_no  | varchar(10) | YES  |     | NULL    |       |
| name      | varchar(10) | YES  |     | NULL    |       |
| head      | varchar(10) | YES  |     | NULL    |       |
| hit_point | int         | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
4 rows in set (0.006 sec)

よさそう(Type以外にも設定できそうな項目が出てきてびっくりしたけど)
よさそう

データを追加

INSERT INTO [テーブル名] VALUES([値], [値], [値]・・・・); とFieldの順番(今回はmodel_no, name, head, hit_pointの順)に連続して入力していく。

5レコード入力した
VALUESのあとに半角スペースを入れたら文法エラーに><
以下の記載内容を一気に貼り付けても無事入力された

INSERT INTO head_tb VALUES('DFL00', 'さくらちゃんZ', 'マンメンエガオ', 3349);
INSERT INTO head_tb VALUES('GRA04', 'びーすとだすたー', 'ですぶらすと', 3099);
INSERT INTO head_tb VALUES('IHS00', 'アイアンホース', 'ツーリスタン', 3099);
INSERT INTO head_tb VALUES('BIC01', 'アインラート', 'ユニサイクラー', 2949);
INSERT INTO head_tb VALUES('WDN00', 'アクアクラウン', 'アクアヘッド', 3199);

データの参照

SELECT [見たいフィールド名] FROM [テーブル名] でテーブルの中身を参照できる

[見たいフィールド名]を*(ワイルドカード)にするとすべてのFieldを参照する

MySQL [medabots_db]> SELECT * FROM head_tb;
+----------+--------------------------+-----------------------+-----------+
| model_no | name                     | head                  | hit_point |
+----------+--------------------------+-----------------------+-----------+
| DFL00    | さくらちゃんZ            | マンメンエガオ        |      3349 |
| GRA04    | びーすとだすたー         | ですぶらすと          |      3099 |
| IHS00    | アイアンホース           | ツーリスタン          |      3099 |
| BIC01    | アインラート             | ユニサイクラー        |      2949 |
| WDN00    | アクアクラウン           | アクアヘッド          |      3199 |
+----------+--------------------------+-----------------------+-----------+
5 rows in set (0.001 sec)

MySQL [medabots_db]> 
MySQL [medabots_db]> 
MySQL [medabots_db]> SELECT head, hit_point FROM head_tb;
+-----------------------+-----------+
| head                  | hit_point |
+-----------------------+-----------+
| マンメンエガオ        |      3349 |
| ですぶらすと          |      3099 |
| ツーリスタン          |      3099 |
| ユニサイクラー        |      2949 |
| アクアヘッド          |      3199 |
+-----------------------+-----------+
5 rows in set (0.002 sec)

SELECTはテーブルを指定せずにデータベースと関係ない値を表示させることも出来る

任意の文字列を表示する例

MySQL [medabots_db]> SELECT 'なにに使うんだろう(； ･`д･´)';
+----------------------------------------------+
| なにに使うんだろう(； ･`д･´)                 |
+----------------------------------------------+
| なにに使うんだろう(； ･`д･´)                 |
+----------------------------------------------+
1 row in set (0.002 sec)

テーブルのコピー

CREATE TABLE [コピー先のテーブル名] SELECT * FROM [コピー元のテーブル名];

CREATE TABLE head_tb_A SELECT * FROM head_tb;

コピー元と同じテーブル構造 & データが全てコピーされた

MySQL [medabots_db]> SHOW TABLES;
+-----------------------+
| Tables_in_medabots_db |
+-----------------------+
| head_tb               |
| head_tb_A             |
+-----------------------+
4 rows in set (0.003 sec)

MySQL [medabots_db]> DESC head_tb_A;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| model_no  | varchar(10) | YES  |     | NULL    |       |
| name      | varchar(10) | YES  |     | NULL    |       |
| head      | varchar(10) | YES  |     | NULL    |       |
| hit_point | int         | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
4 rows in set (0.007 sec)

MySQL [medabots_db]> SELECT * FROM head_tb_A;
+----------+--------------------------+-----------------------+-----------+
| model_no | name                     | head                  | hit_point |
+----------+--------------------------+-----------------------+-----------+
| DFL00    | さくらちゃんZ            | マンメンエガオ        |      3349 |
| GRA04    | びーすとだすたー         | ですぶらすと          |      3099 |
| IHS00    | アイアンホース           | ツーリスタン          |      3099 |
| BIC01    | アインラート             | ユニサイクラー        |      2949 |
| WDN00    | アクアクラウン           | アクアヘッド          |      3199 |
+----------+--------------------------+-----------------------+-----------+
5 rows in set (0.002 sec)

ALTER TABLEコマンド

ALTER TABLE コマンド一覧

用途	コマンド
カラムを追加	ALTER TABLE [テーブル名] ADD [カラム名] [データ型];
カラムの定義を変更	ALTER TABLE [テーブル名] MODIFY [カラム名] [データ型];
カラムの名前と定義を変更	ALTER TABLE [テーブル名] CHANGE [変更前カラム名] [変更後カラム名] [データ型];
カラムを削除	ALTER TABLE [テーブル名] DROP [カラム名]

一通りのカラム操作を実施してみる

カラムの追加

add_date を INT 型で作成

わざと変な型で作成している

MySQL [medabots_db]> ALTER TABLE head_tb ADD add_date INT;
Query OK, 0 rows affected (0.043 sec)
Records: 0  Duplicates: 0  Warnings: 0

MySQL [medabots_db]> DESC head_tb;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| model_no  | varchar(10) | YES  |     | NULL    |       |
| name      | varchar(10) | YES  |     | NULL    |       |
| head      | varchar(10) | YES  |     | NULL    |       |
| hit_point | int         | YES  |     | NULL    |       |
| add_date  | int         | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
5 rows in set (0.004 sec)

インサート出来るか確認

MySQL [medabots_db]> INSERT INTO head_tb VALUES('GWF00', 'アシュトン', 'ブースターヘッド', 3099, 20210923);

MySQL [medabots_db]> SELECT * FROM head_tb;
+----------+--------------------------+--------------------------+-----------+----------+
| model_no | name                     | head                     | hit_point | add_date |
+----------+--------------------------+--------------------------+-----------+----------+
| DFL00    | さくらちゃんZ            | マンメンエガオ           |      3349 |     NULL |
| GRA04    | びーすとだすたー         | ですぶらすと             |      3099 |     NULL |
| IHS00    | アイアンホース           | ツーリスタン             |      3099 |     NULL |
| BIC01    | アインラート             | ユニサイクラー           |      2949 |     NULL |
| WDN00    | アクアクラウン           | アクアヘッド             |      3199 |     NULL |
| GWF00    | アシュトン               | ブースターヘッド         |      3099 | 20210923 |
+----------+--------------------------+--------------------------+-----------+----------+
6 rows in set (0.002 sec)

カラムの定義を変更

なんとなく日付型のように入力したadd_dateのレコードですが、カラムの定義をINTからDATETIMEに変更するとどうなのか、

予想:キャストできずNULLになる

MySQL [medabots_db]> ALTER TABLE head_tb MODIFY add_date DATETIME;

MySQL [medabots_db]> DESC head_tb;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| model_no  | varchar(10) | YES  |     | NULL    |       |
| name      | varchar(10) | YES  |     | NULL    |       |
| head      | varchar(10) | YES  |     | NULL    |       |
| hit_point | int         | YES  |     | NULL    |       |
| add_date  | datetime    | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
5 rows in set (0.003 sec)

MySQL [medabots_db]> SELECT * FROM head_tb;
+----------+--------------------------+--------------------------+-----------+---------------------+
| model_no | name                     | head                     | hit_point | add_date            |
+----------+--------------------------+--------------------------+-----------+---------------------+
| DFL00    | さくらちゃんZ            | マンメンエガオ           |      3349 | NULL                |
| GRA04    | びーすとだすたー         | ですぶらすと             |      3099 | NULL                |
| IHS00    | アイアンホース           | ツーリスタン             |      3099 | NULL                |
| BIC01    | アインラート             | ユニサイクラー           |      2949 | NULL                |
| WDN00    | アクアクラウン           | アクアヘッド             |      3199 | NULL                |
| GWF00    | アシュトン               | ブースターヘッド         |      3099 | 2021-09-23 00:00:00 |
+----------+--------------------------+--------------------------+-----------+---------------------+
6 rows in set (0.002 sec)

結果: (INTからDATETIMEにキャストが)で、できてしまった…

カラムの名前と定義を変更

MySQL [medabots_db]> ALTER TABLE head_tb CHANGE add_date update_date DATE; 

MySQL [medabots_db]> DESC head_tb;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| model_no    | varchar(10) | YES  |     | NULL    |       |
| name        | varchar(10) | YES  |     | NULL    |       |
| head        | varchar(10) | YES  |     | NULL    |       |
| hit_point   | int         | YES  |     | NULL    |       |
| update_date | date        | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+
5 rows in set (0.003 sec)

MySQL [medabots_db]> SELECT * FROM head_tb;
+----------+--------------------------+--------------------------+-----------+-------------+
| model_no | name                     | head                     | hit_point | update_date |
+----------+--------------------------+--------------------------+-----------+-------------+
| DFL00    | さくらちゃんZ            | マンメンエガオ           |      3349 | NULL        |
| GRA04    | びーすとだすたー         | ですぶらすと             |      3099 | NULL        |
| IHS00    | アイアンホース           | ツーリスタン             |      3099 | NULL        |
| BIC01    | アインラート             | ユニサイクラー           |      2949 | NULL        |
| WDN00    | アクアクラウン           | アクアヘッド             |      3199 | NULL        |
| GWF00    | アシュトン               | ブースターヘッド         |      3099 | 2021-09-23  |
+----------+--------------------------+--------------------------+-----------+-------------+
6 rows in set (0.002 sec)

カラムを削除

一通り遊んだので削除

MySQL [medabots_db]> ALTER TABLE head_tb DROP update_date;
Query OK, 0 rows affected (0.049 sec)
Records: 0  Duplicates: 0  Warnings: 0

MySQL [medabots_db]> DESC head_tb;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| model_no  | varchar(10) | YES  |     | NULL    |       |
| name      | varchar(10) | YES  |     | NULL    |       |
| head      | varchar(10) | YES  |     | NULL    |       |
| hit_point | int         | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
4 rows in set (0.004 sec)

主キーの設定

テーブルを作成するときに主キーを設定する

CREATE TABLE medabots_tb(id INT PRIMARY KEY, name VARCHAR(10));

MySQL [medabots_db]> DESC medabots_tb;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.002 sec)

設定できた

主キーの特徴

一意(重複しない)
Nullは入力されない
tableに基本的に一つ(tableを検索するための特殊なカラムのため)だが、2つのカラムで主キーを構成する複合主キーと言うパターンも存在するらしい

一意キーの設定

一意であるというだけであれば、UNIQUEを設定すれば良い

主キーとの差異はNullを許容する点

MySQL [medabots_db]> ALTER TABLE medabots_tb ADD (type VARCHAR(10) UNIQUE);

MySQL [medabots_db]> DESC medabots_tb;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
| type  | varchar(10) | YES  | UNI | NULL    |       |
+-------+-------------+------+-----+---------+-

↓実際に値を入れてみると、typeカラムに置い重複は許されなかったがNullは許容された

MySQL [medabots_db]> INSERT INTO medabots_tb VALUES(0, 'カイゼルビートル', 'KBT-01NF');
Query OK, 1 row affected (0.005 sec)

MySQL [medabots_db]> INSERT INTO medabots_tb VALUES(1, 'プーパビートル', 'KBT-01NF');
ERROR 1062 (23000): Duplicate entry 'KBT-01NF' for key 'medabots_tb.type'

MySQL [medabots_db]> INSERT INTO medabots_tb VALUES(1, 'プーパビートル', Null);
Query OK, 1 row affected (0.005 sec)


MySQL [medabots_db]> SELECT * FROM medabots_tb;
+----+--------------------------+----------+
| id | name                     | type     |
+----+--------------------------+----------+
|  0 | カイゼルビートル         | KBT-01NF |
|  1 | プーパビートル           | NULL     |
+----+--------------------------+----------+
2 rows in set (0.002 sec)

オートインクリメントするカラムの設定

主キー(id)にオートインクリメント(自動的に次の番号を挿入する定義)を付与(しようとした)

(失敗した)

MySQL [medabots_db]> ALTER TABLE medabots_tb MODIFY id INT AUTO_INCREMENT PRIMARY KEY;
ERROR 1068 (42000): Multiple primary key defined

MySQL [medabots_db]> ALTER TABLE medabots_tb MODIFY id INT AUTO_INCREMENT;
ERROR 1062 (23000): ALTER TABLE causes auto_increment resequencing, resulting in duplicate entry '1' for key 'medabots_tb.PRIMARY'

おそらくテーブル定義を最初からすれば出来るかもしれないが、
ALTER TABLEで編集できなかった原因を知りたいので、
SQLは数値の始まりが0ではなく1がデフォルトかもしれないという仮説で、もう一度やってみる。
(つまりidに入力されていた値が0始まりだったので書き換えが必要でエラーになった可能性について検証する)

MySQL [medabots_db]> DROP TABLE medabots_tb;Query OK, 0 rows affected (0.014 sec)

MySQL [medabots_db]> CREATE TABLE medabots_tb(id INT PRIMARY KEY, name VARCHAR(10), type VARCHAR(10) UNIQUE);Query OK, 0 rows affected (0.024 sec)
MySQL [medabots_db]> MySQL [medabots_db]> INSERT INTO medabots_tb VALUES(1, 'カイゼルビートル', 'KBT-01NF');INSERT INTO medabots_tb VALUES(2, 'プーパビートル', Null);
Query OK, 1 row affected (0.008 sec)

Query OK, 1 row affected (0.007 sec)

MySQL [medabots_db]> DESC medabots_tb;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
| type  | varchar(10) | YES  | UNI | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.003 sec)


MySQL [medabots_db]> ALTER TABLE medabots_tb MODIFY id INT AUTO_INCREMENT;Query OK, 2 rows affected (0.056 sec)
Records: 2  Duplicates: 0  Warnings: 0

MySQL [medabots_db]> DESC medabots_tb;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int         | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10) | YES  |     | NULL    |                |
| type  | varchar(10) | YES  | UNI | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.003 sec)

MySQL [medabots_db]>

あらかじめ入力されていた値が1始まりのidであったとき、
オートインクリメントがALTER TABLEで追加できた。
(もしかしてSQLでは0をあまり扱わないのか？)

最後にオートインクリメントしてくれるかを確認

MySQL [medabots_db]> INSERT INTO medabots_tb VALUES(Null, 'めたびー', 'KBT-00');
Query OK, 1 row affected (0.008 sec)

MySQL [medabots_db]> SELECT * FROM medabots_tb;
+----+--------------------------+----------+
| id | name                     | type     |
+----+--------------------------+----------+
|  1 | カイゼルビートル         | KBT-01NF |
|  2 | プーパビートル           | NULL     |
|  3 | めたびー                 | KBT-00   |
+----+--------------------------+----------+
3 rows in set (0.002 sec)

できたー！

デフォルトの値が入っているカラムの設定

MySQL [medabots_db]> ALTER TABLE medabots_tb ADD medal VARCHAR(10) DEFAULT 'カブト';
Query OK, 0 rows affected (0.020 sec)
Records: 0  Duplicates: 0  Warnings: 0


MySQL [medabots_db]> DESC medabots_tb;
+-------+-------------+------+-----+-----------+----------------+
| Field | Type        | Null | Key | Default   | Extra          |
+-------+-------------+------+-----+-----------+----------------+
| id    | int         | NO   | PRI | NULL      | auto_increment |
| name  | varchar(10) | YES  |     | NULL      |                |
| type  | varchar(10) | YES  | UNI | NULL      |                |
| medal | varchar(10) | YES  |     | カブト    |                |
+-------+-------------+------+-----+-----------+----------------+
4 rows in set (0.003 sec)

値を入力して反映されているかを確認したらちょっと失敗した

ちょっと失敗

MySQL [medabots_db]> INSERT INTO medabots_tb VALUES(NUll, 'メタビー', 'KBT-01', NULL);
Query OK, 1 row affected (0.008 sec)

MySQL [medabots_db]> INSERT INTO medabots_tb VALUES(NUll, 'ロクショウ', 'KWG-01', 'クワガタ');
Query OK, 1 row affected (0.005 sec)


MySQL [medabots_db]> SELECT * FROM medabots_tb;
+----+--------------------------+----------+--------------+
| id | name                     | type     | medal        |
+----+--------------------------+----------+--------------+
|  1 | カイゼルビートル         | KBT-01NF | カブト       |
|  2 | プーパビートル           | NULL     | カブト       |
|  3 | めたびー                 | KBT-00   | カブト       |
|  5 | メタビー                 | KBT-01   | NULL         |
|  6 | ロクショウ               | KWG-01   | クワガタ     |
+----+--------------------------+----------+--------------+
5 rows in set (0.002 sec)

ここで気づいたが、NULLと空欄は明確に区別されていて上記のINSERTではmedalのデフォルト値が反映されず、NULLになってしまう。
デフォルト値を利用する場合以下のようにINSERTを実行するひつようがあるらしい。

MySQL [medabots_db]> INSERT INTO medabots_tb (name, type)VALUES('ベイアニット', 'KBT-02');
Query OK, 1 row affected (0.004 sec)

MySQL [medabots_db]> SELECT * FROM medabots_tb;
+----+--------------------------+----------+--------------+
| id | name                     | type     | medal        |
+----+--------------------------+----------+--------------+
|  1 | カイゼルビートル         | KBT-01NF | カブト       |
|  2 | プーパビートル           | NULL     | カブト       |
|  3 | めたびー                 | KBT-00   | カブト       |
|  5 | メタビー                 | KBT-01   | NULL         |
|  6 | ロクショウ               | KWG-01   | クワガタ     |
|  7 | ベイアニット             | KBT-02   | カブト       |
+----+--------------------------+----------+--------------+
6 rows in set (0.001 sec)

インデックス

インデックスとは

データベースが大規模になるとデータ検索の索引として機能し、処理速度に影響する(早くなることも遅くなることもある)
主キーを設定すると自動的に設定されている
YES/NOなど著しく値の種類が少ないカラムには設定すべきではない

インデックスの確認

MySQL [medabots_db]> SHOW INDEX FROM medabots_tb \G;
*************************** 1. row ***************************
        Table: medabots_tb
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 5
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
      Visible: YES
   Expression: NULL
*************************** 2. row ***************************
        Table: medabots_tb
   Non_unique: 0
     Key_name: type
 Seq_in_index: 1
  Column_name: type
    Collation: A
  Cardinality: 5
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
      Visible: YES
   Expression: NULL
2 rows in set (0.003 sec)

ERROR: No query specified

いくつか気になることがあったのでピックアップ

あれ、主キーだけじゃなくてUNIQUEをせっていしたカラムもINDEXが貼られている
- これ理由わからなかった
謎のエラー ERROR: No query specified
- \G で表示を変更した場合 ; をつけなくて良いらしい

インデックスの追加・削除

機能	コマンド
インデックスの追加	CREATE INDEX [インデックス名] ON [テーブル名]([カラム名]);
インデックスの削除	DROP INDEX [インデックス名] ON [テーブル名];

感想: インデックス名はカラム名とは別に管理するんだなぁ

やってみる: medabots_tbのnameカラムにインデックスを貼り、状態を確認してからインデックスを削除する。

MySQL [medabots_db]> CREATE INDEX test_index ON medabots_tb(name);
Query OK, 0 rows affected (0.021 sec)
Records: 0  Duplicates: 0  Warnings: 0

MySQL [medabots_db]> DESC medabots_tb;
+-------+-------------+------+-----+-----------+----------------+
| Field | Type        | Null | Key | Default   | Extra          |
+-------+-------------+------+-----+-----------+----------------+
| id    | int         | NO   | PRI | NULL      | auto_increment |
| name  | varchar(10) | YES  | MUL | NULL      |                |
| type  | varchar(10) | YES  | UNI | NULL      |                |
| medal | varchar(10) | YES  |     | カブト    |                |
+-------+-------------+------+-----+-----------+----------------+
4 rows in set (0.005 sec)

MySQL [medabots_db]> SHOW INDEX FROM medabots_tb\G
*************************** 1. row ***************************
        Table: medabots_tb
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 5
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
      Visible: YES
   Expression: NULL
*************************** 2. row ***************************
        Table: medabots_tb
   Non_unique: 0
     Key_name: type
 Seq_in_index: 1
  Column_name: type
    Collation: A
  Cardinality: 5
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
      Visible: YES
   Expression: NULL
*************************** 3. row ***************************
        Table: medabots_tb
   Non_unique: 1
     Key_name: test_index
 Seq_in_index: 1
  Column_name: name
    Collation: A
  Cardinality: 6
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
      Visible: YES
   Expression: NULL
3 rows in set (0.005 sec)

MySQL [medabots_db]> DROP INDEX test_index ON medabots_tb;
Query OK, 0 rows affected (0.009 sec)
Records: 0  Duplicates: 0  Warnings: 0

MySQL [medabots_db]> SHOW INDEX FROM medabots_tb\G
*************************** 1. row ***************************
        Table: medabots_tb
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 5
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
      Visible: YES
   Expression: NULL
*************************** 2. row ***************************
        Table: medabots_tb
   Non_unique: 0
     Key_name: type
 Seq_in_index: 1
  Column_name: type
    Collation: A
  Cardinality: 5
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
      Visible: YES
   Expression: NULL
2 rows in set (0.003 sec)

結果: インデックスを付けるとテーブルのKeyにMULが追加されたのに気がついた。

外部キー制約といって、参照時の整合性を保つためのものらしい。(今後出てきそうなので今はスルー)

テーブルのレコードをコピー・削除

機能	コマンド
テーブルのカラム構造とレコードをコピー	CREATE TABLE [新規テーブル名] SELECT * FROM [コピー元のテーブル名];
テーブルの絡む構造のみをコピー	CREATE TABLE [新規テーブル名] LIKE [コピー元のテーブル名];
テーブルからレコードをコピー	INSERT INTO [コピー先のテーブル名] SELECT * FROM [コピー元のテーブル名];

実践: 以下の手順で実行した。

medabots_tbのカラム構造とレコードをコピーしてmedabots_tb_Aを作成(カラム構造とレコードをチェック)
medabots_tbのカラム構造だけをコピーしてmedabots_tb_Bを作成(カラム構造をチェック、レコードが入っていないことをチェック)
medabots_tb_Bにmedabots_tb_Aのレコードをコピー(レコードをチェック)

MySQL [medabots_db]> SHOW TABLES;
+-----------------------+
| Tables_in_medabots_db |
+-----------------------+
| head_tb               |
| head_tb_A             |
| medabots_tb           |
+-----------------------+
3 rows in set (0.002 sec)

MySQL [medabots_db]> CREATE TABLE medabots_tb_A SELECT * FROM medabots_tb;
Query OK, 6 rows affected (0.021 sec)
Records: 6  Duplicates: 0  Warnings: 0

MySQL [medabots_db]> DESC medabots_tb_A;
+-------+-------------+------+-----+-----------+-------+
| Field | Type        | Null | Key | Default   | Extra |
+-------+-------------+------+-----+-----------+-------+
| id    | int         | NO   |     | 0         |       |
| name  | varchar(10) | YES  |     | NULL      |       |
| type  | varchar(10) | YES  |     | NULL      |       |
| medal | varchar(10) | YES  |     | カブト    |       |
+-------+-------------+------+-----+-----------+-------+
4 rows in set (0.003 sec)

MySQL [medabots_db]> SELECT * FROM medabots_tb_A;
+----+--------------------------+----------+--------------+
| id | name                     | type     | medal        |
+----+--------------------------+----------+--------------+
|  1 | カイゼルビートル         | KBT-01NF | カブト       |
|  2 | プーパビートル           | NULL     | カブト       |
|  3 | めたびー                 | KBT-00   | カブト       |
|  5 | メタビー                 | KBT-01   | NULL         |
|  6 | ロクショウ               | KWG-01   | クワガタ     |
|  7 | ベイアニット             | KBT-02   | カブト       |
+----+--------------------------+----------+--------------+
6 rows in set (0.001 sec)

MySQL [medabots_db]> CREATE TABLE medabots_tb_B LIKE medabots_tb;
Query OK, 0 rows affected (0.016 sec)

MySQL [medabots_db]> DESC medabots_tb_B;
+-------+-------------+------+-----+-----------+----------------+
| Field | Type        | Null | Key | Default   | Extra          |
+-------+-------------+------+-----+-----------+----------------+
| id    | int         | NO   | PRI | NULL      | auto_increment |
| name  | varchar(10) | YES  |     | NULL      |                |
| type  | varchar(10) | YES  | UNI | NULL      |                |
| medal | varchar(10) | YES  |     | カブト    |                |
+-------+-------------+------+-----+-----------+----------------+
4 rows in set (0.003 sec)

MySQL [medabots_db]> SELECT * FROM medabots_tb_B;
Empty set (0.002 sec)

MySQL [medabots_db]> INSERT INTO medabots_tb_B SELECT * FROM medabots_tb_A;
Query OK, 6 rows affected (0.004 sec)
Records: 6  Duplicates: 0  Warnings: 0

MySQL [medabots_db]> SELECT * FROM medabots_tb_B;
+----+--------------------------+----------+--------------+
| id | name                     | type     | medal        |
+----+--------------------------+----------+--------------+
|  1 | カイゼルビートル         | KBT-01NF | カブト       |
|  2 | プーパビートル           | NULL     | カブト       |
|  3 | めたびー                 | KBT-00   | カブト       |
|  5 | メタビー                 | KBT-01   | NULL         |
|  6 | ロクショウ               | KWG-01   | クワガタ     |
|  7 | ベイアニット             | KBT-02   | カブト       |
+----+--------------------------+----------+--------------+
6 rows in set (0.004 sec)

機能	コマンド
データベースを削除	DROP [削除するデータベース名];
テーブルを削除	DROP TABLE [削除するテーブル名];
レコードの全削除	DELETE FROM [レコードを削除するテーブル名];

実践: 以下の手順で実行した。

damerot_db を作成(データベース一覧を確認)
damerot_db を削除(データベース一覧で確認)
medabots_tb_B を削除(テーブル一覧で確認)
medabots_tb_A のレコードを削除(レコード表示できないことを確認)

MySQL [medabots_db]> CREATE DATABASE damerot_db;
Query OK, 1 row affected (0.006 sec)

MySQL [medabots_db]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| damerot_db         |
| information_schema |
| medabots_db        |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.001 sec)

MySQL [medabots_db]> DROP DATABASE damerot_db;
Query OK, 0 rows affected (0.012 sec)

MySQL [medabots_db]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| medabots_db        |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.002 sec)

MySQL [medabots_db]> SHOW TABLES;
+-----------------------+
| Tables_in_medabots_db |
+-----------------------+
| head_tb               |
| head_tb_A             |
| medabots_tb           |
| medabots_tb_A         |
| medabots_tb_B         |
+-----------------------+
5 rows in set (0.004 sec)

MySQL [medabots_db]> DROP TABLE medabots_tb_B;
Query OK, 0 rows affected (0.013 sec)

MySQL [medabots_db]> SHOW TABLES;
+-----------------------+
| Tables_in_medabots_db |
+-----------------------+
| head_tb               |
| head_tb_A             |
| medabots_tb           |
| medabots_tb_A         |
+-----------------------+
4 rows in set (0.002 sec)

MySQL [medabots_db]> SELECT * FROM medabots_tb_A;
+----+--------------------------+----------+--------------+
| id | name                     | type     | medal        |
+----+--------------------------+----------+--------------+
|  1 | カイゼルビートル         | KBT-01NF | カブト       |
|  2 | プーパビートル           | NULL     | カブト       |
|  3 | めたびー                 | KBT-00   | カブト       |
|  5 | メタビー                 | KBT-01   | NULL         |
|  6 | ロクショウ               | KWG-01   | クワガタ     |
|  7 | ベイアニット             | KBT-02   | カブト       |
+----+--------------------------+----------+--------------+
6 rows in set (0.001 sec)

MySQL [medabots_db]> DELETE FROM medabots_tb_A;
Query OK, 6 rows affected (0.003 sec)

MySQL [medabots_db]> SELECT * FROM medabots_tb_A;
Empty set (0.001 sec)

データの抽出

カラムを入れ替えて表示

SELECTでカラム名を指定する時に、取り出したい順番で記載する

MySQL [medabots_db]> SELECT * FROM head_tb;
+----------+--------------------------+--------------------------+-----------+
| model_no | name                     | head                     | hit_point |
+----------+--------------------------+--------------------------+-----------+
| DFL00    | さくらちゃんZ            | マンメンエガオ           |      3349 |
| GRA04    | びーすとだすたー         | ですぶらすと             |      3099 |
| IHS00    | アイアンホース           | ツーリスタン             |      3099 |
| BIC01    | アインラート             | ユニサイクラー           |      2949 |
| WDN00    | アクアクラウン           | アクアヘッド             |      3199 |
| GWF00    | アシュトン               | ブースターヘッド         |      3099 |
+----------+--------------------------+--------------------------+-----------+
6 rows in set (0.002 sec)

MySQL [medabots_db]> SELECT head, name FROM head_tb;
+--------------------------+--------------------------+
| head                     | name                     |
+--------------------------+--------------------------+
| マンメンエガオ           | さくらちゃんZ            |
| ですぶらすと             | びーすとだすたー         |
| ツーリスタン             | アイアンホース           |
| ユニサイクラー           | アインラート             |
| アクアヘッド             | アクアクラウン           |
| ブースターヘッド         | アシュトン               |
+--------------------------+--------------------------+
6 rows in set (0.002 sec)

エイリアス名をつける

カラム名を好きな名前に置き換えて表示出来る。

機能	コマンド
エイリアスをつける	SELECT [カラム名] AS [エイリアス名] FROM [テーブル名];

失敗

MySQL [medabots_db]> SELECT name, head AS 'メダロット名', '頭パーツ名' FROM head_tb;
+--------------------------+--------------------------+-----------------+
| name                     | メダロット名             | 頭パーツ名      |
+--------------------------+--------------------------+-----------------+
| さくらちゃんZ            | マンメンエガオ           | 頭パーツ名      |
| びーすとだすたー         | ですぶらすと             | 頭パーツ名      |
| アイアンホース           | ツーリスタン             | 頭パーツ名      |
| アインラート             | ユニサイクラー           | 頭パーツ名      |
| アクアクラウン           | アクアヘッド             | 頭パーツ名      |
| アシュトン               | ブースターヘッド         | 頭パーツ名      |
+--------------------------+--------------------------+-----------------+
6 rows in set (0.003 sec)

感想: まとめて指定するものではなかった。一つづつエイリアスをつけよう(いましめ)。

MySQL [medabots_db]> SELECT name AS 'メダロット名',head AS '頭パーツ名' FROM head_tb;
+--------------------------+--------------------------+
| メダロット名             | 頭パーツ名               |
+--------------------------+--------------------------+
| さくらちゃんZ            | マンメンエガオ           |
| びーすとだすたー         | ですぶらすと             |
| アイアンホース           | ツーリスタン             |
| アインラート             | ユニサイクラー           |
| アクアクラウン           | アクアヘッド             |
| アシュトン               | ブースターヘッド         |
+--------------------------+--------------------------+
6 rows in set (0.002 sec)

計算して表示

数値型のカラムは計算して表示できる

実践: 以下を実行

装甲値(hit_point)を表示
エイリアスをつけて装甲値に250追加した値を表示

MySQL [medabots_db]> SELECT hit_point  FROM head_tb;
+-----------+
| hit_point |
+-----------+
|      3349 |
|      3099 |
|      3099 |
|      2949 |
|      3199 |
|      3099 |
+-----------+
6 rows in set (0.002 sec)

MySQL [medabots_db]> SELECT hit_point + 250 AS '装甲+250' FROM head_tb;
+------------+
| 装甲+250   |
+------------+
|       3599 |
|       3349 |
|       3349 |
|       3199 |
|       3449 |
|       3349 |
+------------+
6 rows in set (0.002 sec)

関数を使う

平均:AVG() や合計:SUM() 個数:COUNT() などの用意されている関数を使用して表示できる

MySQL [medabots_db]> SELECT AVG(hit_point) FROM head_tb;
+----------------+
| AVG(hit_point) |
+----------------+
|      3132.3333 |
+----------------+
1 row in set (0.001 sec)

MySQL [medabots_db]> SELECT SUM(hit_point) FROM head_tb;
+----------------+
| SUM(hit_point) |
+----------------+
|          18794 |
+----------------+
1 row in set (0.003 sec)

MySQL [medabots_db]> SELECT COUNT(hit_point) FROM head_tb;
+------------------+
| COUNT(hit_point) |
+------------------+
|                6 |
+------------------+
1 row in set (0.001 sec)

文字列を結合:CONCAT()

MySQL [medabots_db]> SELECT CONCAT(name, 'の頭パーツは', head) AS '頭パーツ言えるかな'FROM he
ad_tb;
+--------------------------------------------------------------+
| 頭パーツ言えるかな                                           |
+--------------------------------------------------------------+
| さくらちゃんZの頭パーツはマンメンエガオ                      |
| びーすとだすたーの頭パーツはですぶらすと                     |
| アイアンホースの頭パーツはツーリスタン                       |
| アインラートの頭パーツはユニサイクラー                       |
| アクアクラウンの頭パーツはアクアヘッド                       |
| アシュトンの頭パーツはブースターヘッド                       |
+--------------------------------------------------------------+
6 rows in set (0.002 sec)

現在を表示

MySQL [medabots_db]> SELECT NOW() AS '現在時刻';
+---------------------+
| 現在時刻            |
+---------------------+
| 2021-09-28 08:23:07 |
+---------------------+
1 row in set (0.002 sec)

条件を指定

レコード数を決めて表示する(LIMIT)

MySQL [medabots_db]> SELECT * FROM head_tb LIMIT 3;
+----------+--------------------------+-----------------------+-----------+
| model_no | name                     | head                  | hit_point |
+----------+--------------------------+-----------------------+-----------+
| DFL00    | さくらちゃんZ            | マンメンエガオ        |      3349 |
| GRA04    | びーすとだすたー         | ですぶらすと          |      3099 |
| IHS00    | アイアンホース           | ツーリスタン          |      3099 |
+----------+--------------------------+-----------------------+-----------+
3 rows in set (0.004 sec)

レコード数を決めて表示する場合の開始位置を設定する(OFFSET)

MySQL [medabots_db]> SELECT * FROM head_tb LIMIT 3 OFFSET 2;
+----------+-----------------------+-----------------------+-----------+
| model_no | name                  | head                  | hit_point |
+----------+-----------------------+-----------------------+-----------+
| IHS00    | アイアンホース        | ツーリスタン          |      3099 |
| BIC01    | アインラート          | ユニサイクラー        |      2949 |
| WDN00    | アクアクラウン        | アクアヘッド          |      3199 |
+----------+-----------------------+-----------------------+-----------+
3 rows in set (0.003 sec)

比較演算子
実践: 以下を行った

装甲値が3000以上
装甲値が3000以下
装甲値が3099以外
装甲値が3099
装甲値が3000~3099の間(数値は含む)
装甲値が3000~3100の間にない(排他)

MySQL [medabots_db]> SELECT * FROM head_tb WHERE hit_point > 3000;
+----------+--------------------------+--------------------------+-----------+
| model_no | name                     | head                     | hit_point |
+----------+--------------------------+--------------------------+-----------+
| DFL00    | さくらちゃんZ            | マンメンエガオ           |      3349 |
| GRA04    | びーすとだすたー         | ですぶらすと             |      3099 |
| IHS00    | アイアンホース           | ツーリスタン             |      3099 |
| WDN00    | アクアクラウン           | アクアヘッド             |      3199 |
| GWF00    | アシュトン               | ブースターヘッド         |      3099 |
+----------+--------------------------+--------------------------+-----------+
5 rows in set (0.001 sec)

MySQL [medabots_db]> SELECT * FROM head_tb WHERE hit_point < 3000;
+----------+--------------------+-----------------------+-----------+
| model_no | name               | head                  | hit_point |
+----------+--------------------+-----------------------+-----------+
| BIC01    | アインラート       | ユニサイクラー        |      2949 |
+----------+--------------------+-----------------------+-----------+
1 row in set (0.002 sec)

MySQL [medabots_db]> SELECT * FROM head_tb WHERE hit_point <> 3099;
+----------+-----------------------+-----------------------+-----------+
| model_no | name                  | head                  | hit_point |
+----------+-----------------------+-----------------------+-----------+
| DFL00    | さくらちゃんZ         | マンメンエガオ        |      3349 |
| BIC01    | アインラート          | ユニサイクラー        |      2949 |
| WDN00    | アクアクラウン        | アクアヘッド          |      3199 |
+----------+-----------------------+-----------------------+-----------+
3 rows in set (0.001 sec)

MySQL [medabots_db]> SELECT * FROM head_tb WHERE hit_point = 3099;
+----------+--------------------------+--------------------------+-----------+
| model_no | name                     | head                     | hit_point |
+----------+--------------------------+--------------------------+-----------+
| GRA04    | びーすとだすたー         | ですぶらすと             |      3099 |
| IHS00    | アイアンホース           | ツーリスタン             |      3099 |
| GWF00    | アシュトン               | ブースターヘッド         |      3099 |
+----------+--------------------------+--------------------------+-----------+
3 rows in set (0.019 sec)

MySQL [medabots_db]> SELECT * FROM head_tb WHERE hit_point BETWEEN 3000 AND 3099;
+----------+--------------------------+--------------------------+-----------+
| model_no | name                     | head                     | hit_point |
+----------+--------------------------+--------------------------+-----------+
| GRA04    | びーすとだすたー         | ですぶらすと             |      3099 |
| IHS00    | アイアンホース           | ツーリスタン             |      3099 |
| GWF00    | アシュトン               | ブースターヘッド         |      3099 |
+----------+--------------------------+--------------------------+-----------+
3 rows in set (0.002 sec)

MySQL [medabots_db]> SELECT * FROM head_tb WHERE hit_point NOT BETWEEN 3000 AND 3099;
+----------+-----------------------+-----------------------+-----------+
| model_no | name                  | head                  | hit_point |
+----------+-----------------------+-----------------------+-----------+
| DFL00    | さくらちゃんZ         | マンメンエガオ        |      3349 |
| BIC01    | アインラート          | ユニサイクラー        |      2949 |
| WDN00    | アクアクラウン        | アクアヘッド          |      3199 |
+----------+-----------------------+-----------------------+-----------+
3 rows in set (0.002 sec)

文字列の一致検索

MySQL [medabots_db]> SELECT * FROM head_tb WHERE name='アインラート';
+----------+--------------------+-----------------------+-----------+
| model_no | name               | head                  | hit_point |
+----------+--------------------+-----------------------+-----------+
| BIC01    | アインラート       | ユニサイクラー        |      2949 |
+----------+--------------------+-----------------------+-----------+
1 row in set (0.002 sec)

文字列のあいまい検索

LIKEを使ってあいまい検索ができる
NOT LIKEで含まない検索ができる

ワイルドカード	内容
%	任意の長さの文字列
_	任意の一文字

MySQL [medabots_db]> SELECT * FROM head_tb WHERE name LIKE 'ア%';
+----------+-----------------------+--------------------------+-----------+
| model_no | name                  | head                     | hit_point |
+----------+-----------------------+--------------------------+-----------+
| IHS00    | アイアンホース        | ツーリスタン             |      3099 |
| BIC01    | アインラート          | ユニサイクラー           |      2949 |
| WDN00    | アクアクラウン        | アクアヘッド             |      3199 |
| GWF00    | アシュトン            | ブースターヘッド         |      3099 |
+----------+-----------------------+--------------------------+-----------+
4 rows in set (0.001 sec)


MySQL [medabots_db]> SELECT * FROM head_tb WHERE name NOT LIKE 'ア%';
+----------+--------------------------+-----------------------+-----------+
| model_no | name                     | head                  | hit_point |
+----------+--------------------------+-----------------------+-----------+
| DFL00    | さくらちゃんZ            | マンメンエガオ        |      3349 |
| GRA04    | びーすとだすたー         | ですぶらすと          |      3099 |
+----------+--------------------------+-----------------------+-----------+
2 rows in set (0.003 sec)

NULLを使った検索

WHERE [カラム名] = NULL はできない
WHERE [カラム名] IS NULL を使う

MySQL [medabots_db]> SELECT * FROM medabots_tb;
+----+--------------------------+----------+--------------+
| id | name                     | type     | medal        |
+----+--------------------------+----------+--------------+
|  1 | カイゼルビートル         | KBT-01NF | カブト       |
|  2 | プーパビートル           | NULL     | カブト       |
|  3 | めたびー                 | KBT-00   | カブト       |
|  5 | メタビー                 | KBT-01   | NULL         |
|  6 | ロクショウ               | KWG-01   | クワガタ     |
|  7 | ベイアニット             | KBT-02   | カブト       |
+----+--------------------------+----------+--------------+
6 rows in set (0.003 sec)

MySQL [medabots_db]> SELECT * FROM medabots_tb WHERE medal=NULL;
Empty set (0.001 sec)

MySQL [medabots_db]> SELECT * FROM medabots_tb WHERE medal IS NULL;
+----+--------------+--------+-------+
| id | name         | type   | medal |
+----+--------------+--------+-------+
|  5 | メタビー     | KBT-01 | NULL  |
+----+--------------+--------+-------+
1 row in set (0.001 sec)

CASEを使う

MySQL [medabots_db]> SELECT CASE WHEN hit_point>3100 THEN 'つよい(๑•ㅂ•)و✧' WHEN hit_point<3000 THEN '若干つよい_(:3」∠)_' ELSE 'ちょっとつよい・ω・'END AS つよさ FROM head_tb;
+----------------------------------+
| つよさ                           |
+----------------------------------+
| つよい(๑•̀ㅂ•́)و✧                |
| ちょっとつよい・ω・              |
| ちょっとつよい・ω・              |
| 若干つよい_(:3」∠)_              |
| つよい(๑•̀ㅂ•́)و✧                |
| ちょっとつよい・ω・              |
+----------------------------------+
6 rows in set (0.003 sec)

###並べ替え

昇順に並べ替える: ORDER BY [並び替えするカラム名] ASC

デフォルトが昇順となっているのでASCはつけないくても良い

MySQL [medabots_db]> SELECT * FROM head_tb ORDER BY hit_point ASC;
+----------+--------------------------+--------------------------+-----------+
| model_no | name                     | head                     | hit_point |
+----------+--------------------------+--------------------------+-----------+
| BIC01    | アインラート             | ユニサイクラー           |      2949 |
| GRA04    | びーすとだすたー         | ですぶらすと             |      3099 |
| IHS00    | アイアンホース           | ツーリスタン             |      3099 |
| GWF00    | アシュトン               | ブースターヘッド         |      3099 |
| WDN00    | アクアクラウン           | アクアヘッド             |      3199 |
| DFL00    | さくらちゃんZ            | マンメンエガオ           |      3349 |
+----------+--------------------------+--------------------------+-----------+
6 rows in set (0.002 sec)

降順に並べ替える: ORDER BY [並び替えするカラム名] DESC

MySQL [medabots_db]> SELECT * FROM head_tb ORDER BY hit_point DESC;
+----------+--------------------------+--------------------------+-----------+
| model_no | name                     | head                     | hit_point |
+----------+--------------------------+--------------------------+-----------+
| DFL00    | さくらちゃんZ            | マンメンエガオ           |      3349 |
| WDN00    | アクアクラウン           | アクアヘッド             |      3199 |
| GRA04    | びーすとだすたー         | ですぶらすと             |      3099 |
| IHS00    | アイアンホース           | ツーリスタン             |      3099 |
| GWF00    | アシュトン               | ブースターヘッド         |      3099 |
| BIC01    | アインラート             | ユニサイクラー           |      2949 |
+----------+--------------------------+--------------------------+-----------+
6 rows in set (0.001 sec)

データの編集

機能	コマンド
カラムのすべてのデータを修正する	UPDATE[テーブル名] SET [カラム名]=[設定するデータ];
特定の条件に当てはまるカラムの値を修正する	UPDATE [テーブル名] SET [カラム名]=[設定するデータ] WHERE [条件];

実践: 以下の操作を行った

tinpetカラムを作成
すべての値をM(男性型)にする
女性型メダロットの値をFにする
N型メダロットの値をNにする

MySQL [medabots_db]> SELECT * FROM head_tb;
+----------+--------------------------+--------------------------+-----------+--------+
| model_no | name                     | head                     | hit_point | tinpet |
+----------+--------------------------+--------------------------+-----------+--------+
| DFL00    | さくらちゃんZ            | マンメンエガオ           |      3349 | NULL   |
| GRA04    | びーすとだすたー         | ですぶらすと             |      3099 | NULL   |
| IHS00    | アイアンホース           | ツーリスタン             |      3099 | NULL   |
| BIC01    | アインラート             | ユニサイクラー           |      2949 | NULL   |
| WDN00    | アクアクラウン           | アクアヘッド             |      3199 | NULL   |
| GWF00    | アシュトン               | ブースターヘッド         |      3099 | NULL   |
+----------+--------------------------+--------------------------+-----------+--------+
6 rows in set (0.002 sec)

MySQL [medabots_db]> UPDATE head_tb SET tinpet='M';
Query OK, 6 rows affected (0.004 sec)
Rows matched: 6  Changed: 6  Warnings: 0

MySQL [medabots_db]> SELECT * FROM head_tb;
+----------+--------------------------+--------------------------+-----------+--------+
| model_no | name                     | head                     | hit_point | tinpet |
+----------+--------------------------+--------------------------+-----------+--------+
| DFL00    | さくらちゃんZ            | マンメンエガオ           |      3349 | M      |
| GRA04    | びーすとだすたー         | ですぶらすと             |      3099 | M      |
| IHS00    | アイアンホース           | ツーリスタン             |      3099 | M      |
| BIC01    | アインラート             | ユニサイクラー           |      2949 | M      |
| WDN00    | アクアクラウン           | アクアヘッド             |      3199 | M      |
| GWF00    | アシュトン               | ブースターヘッド         |      3099 | M      |
+----------+--------------------------+--------------------------+-----------+--------+
6 rows in set (0.001 sec)

MySQL [medabots_db]> UPDATE head_tb SET tinpet='F' WHERE name='アイアンホース' OR name='アクアクラウン';
Query OK, 2 rows affected (0.004 sec)
Rows matched: 2  Changed: 2  Warnings: 0

MySQL [medabots_db]> UPDATE head_tb SET tinpet='N' WHERE name='さくらちゃんZ';Query OK, 1 row affected (0.004 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MySQL [medabots_db]> SELECT * FROM head_tb;
+----------+--------------------------+--------------------------+-----------+--------+
| model_no | name                     | head                     | hit_point | tinpet |
+----------+--------------------------+--------------------------+-----------+--------+
| DFL00    | さくらちゃんZ            | マンメンエガオ           |      3349 | N      |
| GRA04    | びーすとだすたー         | ですぶらすと             |      3099 | M      |
| IHS00    | アイアンホース           | ツーリスタン             |      3099 | F      |
| BIC01    | アインラート             | ユニサイクラー           |      2949 | M      |
| WDN00    | アクアクラウン           | アクアヘッド             |      3199 | F      |
| GWF00    | アシュトン               | ブースターヘッド         |      3099 | M      |
+----------+--------------------------+--------------------------+-----------+--------+
6 rows in set (0.001 sec)

グループごとに表示

GROUP BYを使って、グループごとに集計できる

失敗

MySQL [medabots_db]> SELECT * FROM head_tb GROUP BY tinpet;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'medabots_db.head_tb.model_no' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

原因: MySQL5.7から集約できないカラムがSELECTされていたらエラーが出るようになったらしい
対策: COUNT()関数で集約出来る形にして実行

MySQL [medabots_db]> SELECT tinpet, COUNT(*) FROM head_tb GROUP BY tinpet;
+--------+----------+
| tinpet | COUNT(*) |
+--------+----------+
| N      |        1 |
| M      |        3 |
| F      |        2 |
+--------+----------+
3 rows in set (0.001 sec)

実践: 装甲値の平均をだす

MySQL [medabots_db]> SELECT tinpet, AVG(hit_point) AS '装甲値平均' FROM head_tb GROUP BY tinpet;
+--------+-----------------+
| tinpet | 装甲値平均      |
+--------+-----------------+
| N      |       3349.0000 |
| M      |       3049.0000 |
| F      |       3149.0000 |
+--------+-----------------+
3 rows in set (0.001 sec)

複数テーブルの操作

UNIONで単純に縦に結合

カラム名は最初にSELECTされる方が優先されるらしい

MySQL [medabots_db]> (SELECT name, model_no FROM head_tb) UNION (SELECT name, type FROM medabots_tb);
+--------------------------+----------+
| name                     | model_no |
+--------------------------+----------+
| さくらちゃんZ            | DFL00    |
| びーすとだすたー         | GRA04    |
| アイアンホース           | IHS00    |
| アインラート             | BIC01    |
| アクアクラウン           | WDN00    |
| アシュトン               | GWF00    |
| カイゼルビートル         | KBT-01NF |
| プーパビートル           | NULL     |
| めたびー                 | KBT-00   |
| メタビー                 | KBT-01   |
| ロクショウ               | KWG-01   |
| ベイアニット             | KBT-02   |
+--------------------------+----------+
12 rows in set (0.002 sec)

tinpetの性別ごとに表示

それならソートで良いのですが、UNIONのテストということで行っています

MySQL [medabots_db]> (SELECT * FROM head_tb WHERE tinpet='M') UNION (SELECT * FROM head_tb WHERE tinpet='F') UNION (SELECT * FROM head_tb WHERE tinpet='N');
+----------+--------------------------+--------------------------+-----------+--------+
| model_no | name                     | head                     | hit_point | tinpet |
+----------+--------------------------+--------------------------+-----------+--------+
| GRA04    | びーすとだすたー         | ですぶらすと             |      3099 | M      |
| BIC01    | アインラート             | ユニサイクラー           |      2949 | M      |
| GWF00    | アシュトン               | ブースターヘッド         |      3099 | M      |
| IHS00    | アイアンホース           | ツーリスタン             |      3099 | F      |
| WDN00    | アクアクラウン           | アクアヘッド             |      3199 | F      |
| DFL00    | さくらちゃんZ            | マンメンエガオ           |      3349 | N      |
+----------+--------------------------+--------------------------+-----------+--------+
6 rows in set (0.003 sec)

横方向に結合する

の前に結合用のテーブルを準備

MySQL [medabots_db]> SELECT * FROM leg_tb;
+--------------------------+--------------------------+
| name                     | leg                      |
+--------------------------+--------------------------+
| さくらちゃんZ            | フユーンポッド           |
| びーすとだすたー         | すぱげてー               |
| アイアンホース           | スピードエンジー         |
| アインラート             | NULL                     |
| アクアクラウン           | アクアフィン             |
| アタックティラノ         | アタックレッグ           |
+--------------------------+--------------------------+
6 rows in set (0.002 sec)

内部結合: ONで指定したカラムの値が2つのテーブルどちらにも含まれるものだけ結合

MySQL [medabots_db]> SELECT * FROM head_tb JOIN leg_tb ON head_tb.name=leg_tb.name;
+----------+--------------------------+-----------------------+-----------+--------+--------------------------+--------------------------+
| model_no | name                     | head                  | hit_point | tinpet | name                     | leg                      |
+----------+--------------------------+-----------------------+-----------+--------+--------------------------+--------------------------+
| DFL00    | さくらちゃんZ            | マンメンエガオ        |      3349 | N      | さくらちゃんZ            | フユーンポッド           |
| GRA04    | びーすとだすたー         | ですぶらすと          |      3099 | M      | びーすとだすたー         | すぱげてー               |
| IHS00    | アイアンホース           | ツーリスタン          |      3099 | F      | アイアンホース           | スピードエンジー         |
| BIC01    | アインラート             | ユニサイクラー        |      2949 | M      | アインラート             | NULL                     |
| WDN00    | アクアクラウン           | アクアヘッド          |      3199 | F      | アクアクラウン           | アクアフィン             |
+----------+--------------------------+-----------------------+-----------+--------+--------------------------+--------------------------+
5 rows in set (0.003 sec)

左外部結合: 結合する方のONで指定されたカラムに含まれる値をすべて残して結合

MySQL [medabots_db]> SELECT * FROM head_tb LEFT JOIN leg_tb ON head_tb.name=leg_tb.name;
+----------+--------------------------+--------------------------+-----------+--------+--------------------------+--------------------------+
| model_no | name                     | head                     | hit_point | tinpet | name                     | leg                      |
+----------+--------------------------+--------------------------+-----------+--------+--------------------------+--------------------------+
| DFL00    | さくらちゃんZ            | マンメンエガオ           |      3349 | N      | さくらちゃんZ            | フユーンポッド           |
| GRA04    | びーすとだすたー         | ですぶらすと             |      3099 | M      | びーすとだすたー         | すぱげてー               |
| IHS00    | アイアンホース           | ツーリスタン             |      3099 | F      | アイアンホース           | スピードエンジー         |
| BIC01    | アインラート             | ユニサイクラー           |      2949 | M      | アインラート             | NULL                     |
| WDN00    | アクアクラウン           | アクアヘッド             |      3199 | F      | アクアクラウン           | アクアフィン             |
| GWF00    | アシュトン               | ブースターヘッド         |      3099 | M      | NULL                     | NULL                     |
+----------+--------------------------+--------------------------+-----------+--------+--------------------------+--------------------------+
6 rows in set (0.003 sec)

右外部結合: 結合される方のONで指定されたカラムに含まれる値をすべて残して結合

MySQL [medabots_db]> SELECT * FROM head_tb RIGHT JOIN leg_tb ON head_tb.name=leg_tb.name;
+----------+--------------------------+-----------------------+-----------+--------+--------------------------+--------------------------+
| model_no | name                     | head                  | hit_point | tinpet | name                     | leg                      |
+----------+--------------------------+-----------------------+-----------+--------+--------------------------+--------------------------+
| DFL00    | さくらちゃんZ            | マンメンエガオ        |      3349 | N      | さくらちゃんZ            | フユーンポッド           |
| GRA04    | びーすとだすたー         | ですぶらすと          |      3099 | M      | びーすとだすたー         | すぱげてー               |
| IHS00    | アイアンホース           | ツーリスタン          |      3099 | F      | アイアンホース           | スピードエンジー         |
| BIC01    | アインラート             | ユニサイクラー        |      2949 | M      | アインラート             | NULL                     |
| WDN00    | アクアクラウン           | アクアヘッド          |      3199 | F      | アクアクラウン           | アクアフィン             |
| NULL     | NULL                     | NULL                  |      NULL | NULL   | アタックティラノ         | アタックレッグ           |
+----------+--------------------------+-----------------------+-----------+--------+--------------------------+--------------------------+
6 rows in set (0.035 sec)

サブクエリ

数値をクエリで渡して抽出する

MySQL [medabots_db]> SELECT * FROM head_tb WHERE hit_point > (SELECT AVG(hit_point) FROM head_tb);
+----------+-----------------------+-----------------------+-----------+--------+
| model_no | name                  | head                  | hit_point | tinpet |
+----------+-----------------------+-----------------------+-----------+--------+
| DFL00    | さくらちゃんZ         | マンメンエガオ        |      3349 | N      |
| WDN00    | アクアクラウン        | アクアヘッド          |      3199 | F      |
+----------+-----------------------+-----------------------+-----------+--------+
2 rows in set (0.002 sec)

カラムをクエリで渡して抽出

MySQL [medabots_db]> SELECT * FROM leg_tb WHERE name IN (SELECT name FROM head_tb WHERE hit_point < 3100);
+--------------------------+--------------------------+
| name                     | leg                      |
+--------------------------+--------------------------+
| びーすとだすたー         | すぱげてー               |
| アイアンホース           | スピードエンジー         |
| アインラート             | NULL                     |
+--------------------------+--------------------------+
3 rows in set (0.001 sec)

「基礎からの MySQL 改訂版」西沢夢路、SB Creative、2014 ↩