【データベース入門2】SQL コマンドとは、SQL 文の一覧

本記事はデータベース入門記事のうち、第2回「SQL コマンド」です。

本記事は以下の書籍を参考に執筆しています。

一番初めに読む本

経験者向けの入門本

MySQL を触る方におすすめ

その他のデータベースの入門記事ついては以下の記事をご確認ください。

スポンサーリンク

SQL コマンドとは

SQL は DBMS (データベース管理システム)のデータを操作するための言語です。

SQL はプログラミングをせずにデータが操作できることから、急速に広まりました。

SQL の文法は以下の3つに大別されます。

以降では MySQL を利用して具体的な SQL コマンドの動作を紹介します。

また、MySQL をまだインストールしていない場合は以下の記事を参考に MySQL をインストールしてください。

スポンサーリンク

データ定義言語 (DDL: Data Definition Language)

DDL とは、データベースオブジェクト (データベースやテーブル) の作成・削除・定義の変更を行う SQL コマンドです。

DDL に相当する SQL コマンドの例は以下のとおりです。

  • CREATE (データベースオブジェクト(テーブルなど)の定義)
  • ALTER (データベースオブジェクトの定義変更)
  • DROP (データベースオブジェクトの削除)

CREATE(作成)

CREATE DATABASE

CREATE DATABASE <データベース名>

CREATE DATABASE ステートメントは、データベースを作成します。

CREATE DATABASE db;
SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| db                 |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

db という名前のデータベースが作成できたことを確認できます。

CREATE TABLE

CREATE TABLE <データベース>.<テーブル>(<カラム名> <データ型>)

CREATE TABLE ステートメントは、データベースにテーブルを作成します。

CREATE TABLE db.tbl(id int, str varchar(10));
SHOW TABLES IN db;
+--------------+
| Tables_in_db |
+--------------+
| tbl          |
+--------------+
DESC db.tbl;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| str   | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

tbl という名前のテーブルが作成できたことを確認できます。

なお、テーブルの Consistency (一貫性) を保証する制約の設定方法については、以下の記事で紹介しています。

ALTER (変更)

ALTER TABLE ADD

ALTER TABLE <データベース>.<テーブル> ADD COLUMN <カラム>

ALTER TABLE ADD ステートメントは、テーブルにカラムを追加します。

以下に利用例を示します。

DESC db.tbl;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| str   | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
ALTER TABLE db.tbl ADD COLUMN add_field char(5);
DESC db.tbl;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| id        | int         | YES  |     | NULL    |       |
| str       | varchar(10) | YES  |     | NULL    |       |
| add_field | char(5)     | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+

テーブルに add_field カラムを追加したことが確認できます。

ALTER TABLE RENAME

ALTER TABLE <データベース>.<テーブル> RENAME COLUMN <変更前のカラム名> TO <変更後のカラム名>

ALTER TABLE RENAME ステートメントは、テーブルのカラム名を変更します。

以下に利用例を示します。

DESC db.tbl;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| id        | int         | YES  |     | NULL    |       |
| str       | varchar(10) | YES  |     | NULL    |       |
| add_field | char(5)     | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
ALTER TABLE db.tbl RENAME COLUMN add_field TO rename_field;
DESC db.tbl;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| id           | int         | YES  |     | NULL    |       |
| str          | varchar(10) | YES  |     | NULL    |       |
| rename_field | char(5)     | YES  |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+

テーブルの add_field カラムを rename_field カラムに変更したことが確認できます。

ALTER TABLE DROP

ALTER TABLE <データベース>.<テーブル> DROP COLUMN <カラム名>

ALTER TABLE DROP ステートメントは、テーブルのカラムを削除します。

以下に利用例を示します。

DESC db.tbl;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| id           | int         | YES  |     | NULL    |       |
| str          | varchar(10) | YES  |     | NULL    |       |
| rename_field | char(5)     | YES  |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+
ALTER TABLE db.tbl DROP COLUMN rename_field;
DESC db.tbl;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| str   | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

テーブルから rename_field カラムが削除されたことを確認できます。

DROP (削除)

DROP TABLE

DROP TABLE <テーブル>

DROP TABLE ステートメントは、テーブルを削除します。

以下に利用例を示します。

DROP TABLE db.tbl;
SHOW TABLES IN db;
Empty set (0.00 sec)

tbl テーブルが削除されたことを確認できます。

DROP DATABASE

DROP DATABASE <データベース>

DROP DATABASE ステートメントは、データベースを削除します。

以下に利用例を示します。

DROP DATABASE db;
SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

db データベースが削除されたことを確認できます。

スポンサーリンク

データ操作言語 (DML: Data Manipulation Language)

DML とは、テーブルの持つ行に対して、CRUD 操作を行う SQL コマンドです。

なお、CRUD とは以下の操作の頭文字です。

  • Create(作成)
  • Read(読み取り)
  • Update(更新)
  • Delete(削除)

上記の操作に対応する SQL コマンドは以下のとおりです。

CRUD 操作SQL コマンド説明
CreateINSERTテーブルに行を挿入
ReadSELECTテーブルから行を取得
UpdateUPDATEテーブルの行を更新
DeleteDELETEテーブルから行を削除

以降では次のテーブルに対して操作を行います。

CREATE DATABASE db;
CREATE TABLE db.tbl(id int, str varchar(10));
USE db;
DESC tbl;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| str   | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

なお、「USE db」コマンドにより、以降ではデータベース名を省略可能となります。(例えば、db.tbl は tbl で指定可能)

INSERT (挿入)

INSERT INTO

INSERT INTO <テーブル> VALUES(<カラム1の値>, <カラム2の値>...)

INSERT INTO ステートメントは、テーブルに行を挿入します。

INSERT INTO tbl VALUES(1,'hoge');

挿入結果は後述する SELECT 文で確認可能です。

SELECT * FROM tbl;
+------+------+
| id   | str  |
+------+------+
|    1 | hoge |
+------+------+

INSERT INTO ~ SELECT

現時点では SELECT 句の説明をしていないので、後述します。

SELECT (取得)

SELECT <カラム> FROM <テーブル>

SELECT ステートメントは、テーブルから行を取得します。

SELECT * FROM tbl;
+------+------+
| id   | str  |
+------+------+
|    1 | hoge |
+------+------+

WHERE (条件)

SELECT <カラム> FROM <テーブル> WHERE <条件>

WHERE 句は、SELECT ステートメントで取得する行の条件を指定します。

利用例は以下のとおりです。

INSERT INTO tbl VALUES(2,'foo');
SELECT * FROM tbl;
+------+------+
| id   | str  |
+------+------+
|    1 | hoge |
|    2 | foo  |
+------+------+
SELECT * FROM tbl WHERE id = 1;
+------+------+
| id   | str  |
+------+------+
|    1 | hoge |
+------+------+

WHERE で指定したカラムの値を持つ行のみを表示します。

DISTINCT (重複削除)

SELECT DISTINCT <カラム> FROM <テーブル>

DISTINCT 句は、SELECT ステートメントで取得する行から重複する行を削除します。

利用例は以下のとおりです。

INSERT INTO tbl VALUES(2,'foo');
SELECT * FROM tbl;
+------+------+
| id   | str  |
+------+------+
|    1 | hoge |
|    2 | foo  |
|    2 | foo  |
+------+------+
SELECT DISTINCT * FROM tbl;
+------+------+
| id   | str  |
+------+------+
|    1 | hoge |
|    2 | foo  |
+------+------+

GROUP BY (集約)

SELECT <カラム> FROM <テーブル> GROUP BY <条件>

GROUP BY 句は、SELECT ステートメントで取得する行を指定したカラムで集約します。

利用例は以下のとおりです。

SELECT * FROM tbl;
+------+------+
| id   | str  |
+------+------+
|    1 | hoge |
|    2 | foo  |
|    2 | foo  |
+------+------+
SELECT * FROM tbl GROUP BY id, str;
+------+------+
| id   | str  |
+------+------+
|    1 | hoge |
|    2 | foo  |
+------+------+

GROUP BY は以下のように集約関数(指定したカラムで集約し、新しいテーブルを作成する関数)と合わせて利用することもあります。

SELECT str, COUNT(str) FROM tbl GROUP BY str;
+------+------------+
| str  | COUNT(str) |
+------+------------+
| hoge |          1 |
| foo  |          2 |
+------+------------+

上記の例では、集約関数 COUNT() を利用することで、str カラムに存在する重複する値をカウントしています。

DISTINCTGROUP BY で何が違うの?という方は以下の記事をご覧ください。

重複行のまとめ方はGROUP BY?DISTINCT? - Qiita
使うべきはどちらなのか? 重複レコードをまとめる時に、ふとGROUP BY と DISTINCT のどちらを使えば効率が良いのか迷うということがおきた。 結論から言ってしまうとどちらの関数も重複行をまとめるという目的で使われる...

結論だけ言うと、重複排除を行うなら「どっちでもいい」

ですが、DISTINCT の方が SQL が短くて綺麗に見えます。

一応以下の基準で使い分ければいいのではないでしょうか。

  • 重複排除では DISTINCT
  • グループ化して集約関数を利用するなら GROUP BY

HAVING

SELECT <カラム> FROM <テーブル> GROUP BY <集約条件> HAVING <表示条件>

HAVING 句は、GROUP BY で集約したテーブルから指定した条件の行のみを表示します。

SELECT str, COUNT(str) FROM tbl GROUP BY str;
+------+------------+
| str  | COUNT(str) |
+------+------------+
| hoge |          1 |
| foo  |          2 |
+------+------------+
SELECT str, COUNT(str) FROM tbl GROUP BY str HAVING COUNT(str) >= 2;
+------+------------+
| str  | COUNT(str) |
+------+------------+
| foo  |          2 |
+------+------------+

HAVING で指定した COUNT(str) の結果が2より大きい行のみが表示されます。

なお、式の評価順が以下のとおりのため、GROUP BY(集約関数)の結果を元にフィルタリングするには「WHERE」ではなく、「HAVING」を利用する必要があります。

  1. WHERE
  2. GROUP BY
  3. HAVING

WINDOW

WINDOW (MySQL 8.0 から)を理解するために、まずは Window 関数について説明します。

Window 関数は各行に対して計算を実行する関数で、MySQL 8.0 では以下の関数を指します。

名前説明
CUME_DIST()累積分布値
DENSE_RANK()パーティション内の現在の行のランク (ギャップなし)
FIRST_VALUE()ウィンドウフレームの最初の行からの引数の値
LAG()パーティション内の現在行より遅れている行の引数の値
LAST_VALUE()ウィンドウフレームの最後の行からの引数の値
LEAD()パーティション内の現在の行の先頭行からの引数の値
NTH_VALUE()ウィンドウフレームの N 番目の行からの引数の値
NTILE()パーティション内の現在の行のバケット番号。
PERCENT_RANK()パーセントランク値
RANK()パーティション内の現在の行のランク (ギャップあり)
ROW_NUMBER()パーティション内の現在の行数
https://dev.mysql.com/doc/refman/8.0/ja/window-function-descriptions.html

Window 関数で計算した結果の表示方法は、OVER 句を利用して指定する必要があります。

■ 今回は、1行前の行を表示する LAG() Window 関数を例に説明します。

ORDER BY 句は行をソートするものです。

SELECT id, LAG(id) OVER(ORDER by id) FROM tbl;
+------+---------------------------+
| id   | LAG(id) OVER(ORDER by id) |
+------+---------------------------+
|    1 |                      NULL |
|    2 |                         1 |
|    2 |                         2 |
+------+---------------------------+

「LAG(id)」は、id の1行前の値を表示していることがわかります。

■ Window 関数を複数利用すると、毎回同じ OVER 句を記載する必要があります。

SELECT id, LAG(id) OVER (ORDER BY id), str, LAG(str) OVER (ORDER BY id) FROM tbl WINDOW w AS (ORD ER BY id);
+------+----------------------------+------+-----------------------------+
| id   | LAG(id) OVER (ORDER BY id) | str  | LAG(str) OVER (ORDER BY id) |
+------+----------------------------+------+-----------------------------+
|    1 |                       NULL | hoge | NULL                        |
|    2 |                          1 | foo  | hoge                        |
|    2 |                          2 | foo  | foo                         |
+------+----------------------------+------+-----------------------------+

この Window 関数で何度も同じ OVER 句の記載を避けるために、定義した条件を使い回す方法が「名前付きウィンドウ(WINDOW 句)」です。

■ 名前付きウィンドウは「SELECT <Window 関数> OVER <名前付きウィンドウで定義した名前> FROM <テーブル> WINDOW <名前付きウィンドウ>」で利用できます。

SELECT id, LAG(id) OVER w, str, LAG(str) OVER w FROM tbl WINDOW w AS (ORDER BY id);
+------+----------------+------+-----------------+
| id   | LAG(id) OVER w | str  | LAG(str) OVER w |
+------+----------------+------+-----------------+
|    1 |           NULL | hoge | NULL            |
|    2 |              1 | foo  | hoge            |
|    2 |              2 | foo  | foo             |
+------+----------------+------+-----------------+

ORDER BY

SELECT <カラム> FROM <テーブル> ORDER BY <条件>

ORDER BY 句は、指定した条件順に行をソートします。

SELECT * FROM tbl ORDER BY id ASC;
+------+------+
| id   | str  |
+------+------+
|    1 | hoge |
|    2 | foo  |
|    2 | foo  |
+------+------+
SELECT * FROM tbl ORDER BY id DESC;
+------+------+
| id   | str  |
+------+------+
|    2 | foo  |
|    2 | foo  |
|    1 | hoge |
+------+------+

LIMIT

SELECT <カラム> FROM <テーブル> LIMIT <値>

LIMIT とは、指定した値の行数のみ結果を表示します。

SELECT * FROM tbl LIMIT 2;
+------+------+
| id   | str  |
+------+------+
|    1 | hoge |
|    2 | foo  |
+------+------+

2行しか表示されてないことが確認できます。

INTO

SELECT <カラム> INTO <変数> FROM <テーブル>

INTO 句は、クエリの結果を変数やファイルに代入します。

SELECT id INTO @var FROM tbl;
SELECT @var;
+------+
| @var |
+------+
|    1 |
+------+

INSERT INTO ~ SELECT

INSERT INTO <挿入先テーブル> SELECT <カラム> FROM <挿入元テーブル>

INSERT INTO ~ SELECT ステートメントは、SELECT 文の結果をテーブルに挿入できます。

CREATE TABLE tbl2(id int, str varchar(10));
INSERT INTO tbl2 SELECT * FROM tbl;
SELECT * FROM tbl2;
+------+------+
| id   | str  |
+------+------+
|    1 | hoge |
|    2 | foo  |
|    2 | foo  |
+------+------+

UPDATE (更新)

UPDATE <テーブル> SET <カラム> = <値> WHERE <条件>

UPDATE ステートメントは、既存のカラムを新しい値に更新します。

SELECT * FROM tbl;
+------+------+
| id   | str  |
+------+------+
|    1 | hoge |
|    2 | foo  |
|    2 | foo  |
+------+------+
UPDATE tbl SET str = 'update' WHERE str = 'hoge';
SELECT * FROM tbl;
+------+--------+
| id   | str    |
+------+--------+
|    1 | update |
|    2 | foo    |
|    2 | foo    |
+------+--------+

DELETE (削除)

DELETE FROM <テーブル> WHERE <行の条件>

DELETE ステートメントは、テーブルの行を削除します。

SELECT * FROM tbl;
+------+--------+
| id   | str    |
+------+--------+
|    1 | update |
|    2 | foo    |
|    2 | foo    |
+------+--------+
DELETE FROM tbl WHERE id = 2;
SELECT * FROM tbl;
+------+--------+
| id   | str    |
+------+--------+
|    1 | update |
+------+--------+

JOIN (横に結合)

JOIN は「2つのテーブルから1つのテーブルを作成する」句で、「SELECTDELETEUPDATE」文利用可能です。

JOIN には次の5種類が存在します。

CROSS JOIN(直積結合)

CROSS JOIN は2つのテーブルの直積を持つテーブルを作成します。

直積

以下のテーブルを持つ tbl と tbl2 を CROSS JOIN してみます。

SELECT * FROM tbl;
+------+--------+
| id   | str    |
+------+--------+
|    1 | update |
|    2 | test   |
+------+--------+
SELECT * FROM tbl2;
+------+------+
| id   | str  |
+------+------+
|    1 | hoge |
|    2 | foo  |
|    3 | bar  |
+------+------+
SELECT * FROM tbl CROSS JOIN tbl2;
+------+--------+------+------+
| id   | str    | id   | str  |
+------+--------+------+------+
|    2 | test   |    1 | hoge |
|    1 | update |    1 | hoge |
|    2 | test   |    2 | foo  |
|    1 | update |    2 | foo  |
|    2 | test   |    3 | bar  |
|    1 | update |    3 | bar  |
+------+--------+------+------+

2つのテーブル(tbl と tbl2)の直積を持つ、新しいテーブルを作成したことがわかります。

INNER JOIN(内部結合)

INNER JOIN は2つのテーブルの CROSS JOIN(直積)から、ON 句の条件に一致する行のみを表示します。

SELECT * FROM tbl CROSS JOIN tbl2;
+------+--------+------+------+
| id   | str    | id   | str  |
+------+--------+------+------+
|    2 | test   |    1 | hoge |
|    1 | update |    1 | hoge |
|    2 | test   |    2 | foo  |
|    1 | update |    2 | foo  |
|    2 | test   |    3 | bar  |
|    1 | update |    3 | bar  |
+------+--------+------+------+
SELECT * FROM tbl INNER JOIN tbl2 ON tbl.id=tbl2.id;
+------+--------+------+------+
| id   | str    | id   | str  |
+------+--------+------+------+
|    1 | update |    1 | hoge |
|    2 | test   |    2 | foo  |
+------+--------+------+------+

tbl.id と tbl2.id が一致する行のみ表示しています。

なお、MySQL では「JOIN = CROSS JOIN = INNER JOIN」です。標準 SQL では区別されています。

MySQL では、JOINCROSS JOIN、および INNER JOIN は構文上同等です (互いに置き換えることができます)。 標準 SQL では、それらは同等ではありません。INNER JOINON 句とともに使用され、CROSS JOIN はそれ以外のときに使用されます。

https://dev.mysql.com/doc/refman/8.0/ja/join.html

LEFT JOIN(左外部結合)

左側のテーブルの行を全て表示し、条件に一致する右側のテーブルをくっつけます(結合)。

SELECT * FROM tbl;
+------+--------+
| id   | str    |
+------+--------+
|    1 | update |
|    2 | test   |
+------+--------+
SELECT * FROM tbl2;
+------+------+
| id   | str  |
+------+------+
|    1 | hoge |
|    2 | foo  |
|    3 | bar  |
+------+------+
SELECT * FROM tbl LEFT JOIN tbl2 ON tbl.id=tbl2.id;
+------+--------+------+------+
| id   | str    | id   | str  |
+------+--------+------+------+
|    1 | update |    1 | hoge |
|    2 | test   |    2 | foo  |
+------+--------+------+------+

RIGHT JOIN (右外部結合)

右側のテーブルの行を全て表示し、条件に一致する左側のテーブルをくっつけます(結合)。

SELECT * FROM tbl;
+------+--------+
| id   | str    |
+------+--------+
|    1 | update |
|    2 | test   |
+------+--------+
SELECT * FROM tbl2;
+------+------+
| id   | str  |
+------+------+
|    1 | hoge |
|    2 | foo  |
|    3 | bar  |
+------+------+
SELECT * FROM tbl RIGHT JOIN tbl2 ON tbl.id=tbl2.id;
+------+--------+------+------+
| id   | str    | id   | str  |
+------+--------+------+------+
|    1 | update |    1 | hoge |
|    2 | test   |    2 | foo  |
| NULL | NULL   |    3 | bar  |
+------+--------+------+------+

対応する左側のテーブルが無い場合は、NULL となります。

FULL JOIN (完全外部結合)

左右両方のテーブルの行を全て表示し、条件に一致するもう片方のテーブルをひっつけます。

残念ながら現時点では MySQL に FULL JOIN はありません。

MySQL :: MySQL 8.0 Reference Manual :: 13.2.10.2 JOIN Clause

UNION (縦に結合)

SELECT <カラム> FROM <テーブル> UNION SELECT <カラム> FROM <テーブル>

UNION は、複数の SELECT 文の結果を、1つの結果セットに結合します。

SELECT * FROM tbl;
+------+--------+
| id   | str    |
+------+--------+
|    1 | update |
|    2 | test   |
+------+--------+
SELECT * FROM tbl2;
+------+------+
| id   | str  |
+------+------+
|    1 | hoge |
|    2 | foo  |
|    3 | bar  |
+------+------+
SELECT * FROM tbl UNION SELECT * FROM tbl2;
+------+--------+
| id   | str    |
+------+--------+
|    1 | update |
|    2 | test   |
|    1 | hoge   |
|    2 | foo    |
|    3 | bar    |
+------+--------+

2つのテーブルが縦に結合され、1つになったことがわかります。

サブクエリ

最初に、クエリとは SELECT ステートメント(文)のことです。

クエリー
SQL で、1 つ以上のテーブルから情報を読み取る操作。

歴史的な理由から、ステートメントの内部処理のディスカッションでは、DDL ステートメントや DML ステートメントなどの他のタイプの MySQL ステートメントを含む、より広範な意味で 「query」 が使用される場合があります。

https://dev.mysql.com/doc/refman/8.0/ja/glossary.html#glos_query

A query is a synonym for a SELECT statement.
A statement is any SQL command such as SELECT, INSERT, UPDATE, DELETE.

Queries, which retrieve data based on specific criteria.
Statements, which may have a persistent effect on schemas and data, or which may control transactions, program flow, connections, sessions, or diagnostics.

https://stackoverflow.com/questions/4735856/difference-between-a-statement-and-a-query-in-sql

サブクエリは以下のように、「文の中」に「別のSELECT 文(クエリ)」がある文のことです。

SELECT <カラム> FROM <テーブル>
WHERE <条件> =
 (SELECT <カラム> FROM <テーブル>)
SELECT * FROM tbl;
+------+--------+
| id   | str    |
+------+--------+
|    1 | update |
|    2 | test   |
+------+--------+
SELECT * FROM tbl2;
+------+------+
| id   | str  |
+------+------+
|    1 | hoge |
|    2 | foo  |
|    3 | bar  |
+------+------+
SELECT * FROM tbl
WHERE id =
(SELECT id FROM tbl2 WHERE str = 'hoge');
+------+--------+
| id   | str    |
+------+--------+
|    1 | update |
+------+--------+

1つ目のSELECT の WHERE 句に2つ目の SELECT の結果が利用できていることを確認できました。

上記のサブクエリ「(SELECT id FROM tbl2 WHERE str = 'hoge')」では、1行のみを返しますが、複数行の結果を返す場合は「HAVING」や「ANY」等を利用します。

サブクエリーで使用されている場合、ワード IN= ANY のエイリアスです。

ワード SOMEANY のエイリアスです。

https://dev.mysql.com/doc/refman/8.0/ja/any-in-some-subqueries.html
SELECT * FROM tbl2
WHERE id = ANY
(SELECT id FROM tbl);
+------+------+
| id   | str  |
+------+------+
|    1 | hoge |
|    2 | foo  |
+------+------+

サブクエリ(SELECT id FROM tbl); の結果(id =1, 2)のいずれかに一致する行を表示します。

データ制御言語 (DCL: Data Control Language)

DCL とは、データベースの権限やトランザクションの制御を行う SQL コマンドです。

DCL に相当する SQL コマンドの例は以下のとおりです。

CREATE USER (ユーザー作成)

CREATE USER <ユーザー>@<ホスト> IDENTIFIED BY '<パスワード>'

CREATE USER ステートメントは、MySQL ユーザーアカウントを作成します。

アカウント名のホスト名部分は、省略すると'%'にデフォルト設定されます。

https://dev.mysql.com/doc/refman/8.0/ja/create-user.html

ワイルドカード文字 (.% など)

https://dev.mysql.com/doc/refman/8.0/ja/account-names.html

MEDIUM ポリシーでは、パスワードに少なくとも 1 つの数字、1 つの小文字、1 つの大文字および 1 つの特殊文字 (英数字以外) を含める必要があるという条件が追加されます。

https://dev.mysql.com/doc/refman/8.0/ja/validate-password.html
CREATE USER 'hogetech'@'localhost' IDENTIFIED BY 'Hoge111!';
SELECT user, host FROM mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| hogetech         | localhost |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+

ユーザーが作成されていることを確認できます。

GRANT (権限割り当て)

GRANT <権限> ON <権限レベル> TO '<ユーザー>'@'<ホスト>'

GRANT ステートメントは、MySQL のユーザーアカウントに権限を割り当てます。

GRANT SELECT ON db.tbl TO 'hogetech'@'localhost';
SHOW GRANTS FOR 'hogetech'@'localhost';
+------------------------------------------------------+
| Grants for hogetech@localhost                        |
+------------------------------------------------------+
| GRANT USAGE ON *.* TO `hogetech`@`localhost`         |
| GRANT SELECT ON `db`.`tbl` TO `hogetech`@`localhost` |
+------------------------------------------------------+

別の端末を開き、MySQL のユーザーアカウント hogetech としてログインして権限の動作を確認します。

mysql -u hogetech -pHoge111!
SELECT * FROM db.tbl;
+------+--------+
| id   | str    |
+------+--------+
|    1 | update |
|    2 | test   |
+------+--------+
SELECT * FROM db.tbl2;
ERROR 1142 (42000): SELECT command denied to user 'hogetech'@'localhost' for table 'tbl2'

権限を付与していないテーブルに対する操作は、DENIED となることを確認できました。

REVOKE (権限取り消し)

REVOKE <権限> ON <権限レベル> FROM '<ユーザー>'@'<ホスト>'

REVOKE は、ユーザーから権限を取り消すことが出来ます。

SHOW GRANTS FOR 'hogetech'@'localhost';
+------------------------------------------------------+
| Grants for hogetech@localhost                        |
+------------------------------------------------------+
| GRANT USAGE ON *.* TO `hogetech`@`localhost`         |
| GRANT SELECT ON `db`.`tbl` TO `hogetech`@`localhost` |
+------------------------------------------------------+

denied エラーが出た場合は MySQL のユーザーアカウント root で操作しているか確認してください。

REVOKE SELECT ON db.tbl FROM 'hogetech'@'localhost';
SHOW GRANTS FOR 'hogetech'@'localhost';
+----------------------------------------------+
| Grants for hogetech@localhost                |
+----------------------------------------------+
| GRANT USAGE ON *.* TO `hogetech`@`localhost` |
+----------------------------------------------+

BEGIN (トランザクション開始)

BEGIN

BEGIN は、新しいトランザクションを開始します。

トランザクションとは、複数の処理を1つにまとめたものです。

トランザクションの詳細については以下の記事に記載しています。

BEGIN;

トランザクション分離レベルに応じて、他のトランザクションに影響を分離します。

例えば、トランザクション分離レベルが READ-COMMITTED の場合、2つのトランザクションは以下のとおりとなります。

SELECT * FROM tbl;
+------+--------+
| id   | str    |
+------+--------+
|    1 | update |
|    2 | test   |
+------+--------+
INSERT INTO tbl VALUES(3,'foo');
SELECT * FROM tbl;
+------+--------+
| id   | str    |
+------+--------+
|    1 | update |
|    2 | test   |
|    3 | foo    |
+------+--------+

この状態で2つ目の端末(トランザクション2)でログインし、テーブルを確認します。

SELECT * FROM db.tbl;
+------+--------+
| id   | str    |
+------+--------+
|    1 | update |
|    2 | test   |
+------+--------+

トランザクション2※では、トランザクション1の INSERT の結果がまだ反映されていません。

※BEGIN をしてない場合、1行ごとにトランザクション(BEGIN = START TRANSACTION)が実行される扱いとなります。

デフォルトでは、MySQL は自動コミットモードが有効になった状態で動作します。 つまり、特にトランザクション内にない場合、各ステートメントは START TRANSACTION および COMMIT で囲まれているかのようにアトミックです。

https://dev.mysql.com/doc/refman/8.0/ja/commit.html

COMMIT (トランザクションを永続化)

COMMIT

COMMIT は、現在のトランザクションの変更を永続的なものにします。

これにより、Atomicity (原始性) と Durability (永続性) を実現します。

なお、Atomicity (原始性) と Durability (永続性) については以下の記事で紹介しています。

先程の BEGIN で行ったトランザクションの続きから開始します。

端末2(トランザクション2)でテーブルを確認します。

SELECT * FROM db.tbl;
+------+--------+
| id   | str    |
+------+--------+
|    1 | update |
|    2 | test   |
+------+--------+

端末1(トランザクション1)で COMMITしてみます。

COMMIT;

次に端末2(トランザクション2)でテーブルを確認します。

SELECT * FROM db.tbl;
+------+--------+
| id   | str    |
+------+--------+
|    1 | update |
|    2 | test   |
|    3 | foo    |
+------+--------+

トランザクション1で INSERT した結果がトランザクション2に反映されました。

ROLLBACK (トランザクションの取り消し)

ROLLBACK

ROLLBACK は、現在のトランザクションの変更を取り消します。

これにより、Atomicity (原始性) を実現します。

なお、Atomicity (原始性) については以下の記事で紹介しています。

ロールバックの動作を確認するために、まずトランザクションで変更処理を行います。

SELECT * FROM tbl;
+------+--------+
| id   | str    |
+------+--------+
|    1 | update |
|    2 | test   |
|    3 | foo    |
+------+--------+
BEGIN;
INSERT INTO tbl VALUES(4,'bar');
SELECT * FROM tbl;
+------+--------+
| id   | str    |
+------+--------+
|    1 | update |
|    2 | test   |
|    3 | foo    |
|    4 | bar    |
+------+--------+

トランザクションで変更したこの状態でロールバックします。

ROLLBACK;
SELECT * FROM tbl;
+------+--------+
| id   | str    |
+------+--------+
|    1 | update |
|    2 | test   |
|    3 | foo    |
+------+--------+

4行目の「4, bar」が消え、トランザクション開始時のテーブルにロールバックしていることを確認しました。

SET TRANSACTION (トランザクション分離レベルの設定)

SET <スコープ> TRANSACTION ISOLATION LEVEL <トランザクション分離レベル>

SET TRANSACTION ISOLATION LEVEL で、トランザクション分離レベルを指定できます。

トランザクション分離レベル (Isolation = トランザクションの分離性) については、以下の記事で説明しています。

SELECT @@SESSION.transaction_isolation;
+---------------------------------+
| @@SESSION.transaction_isolation |
+---------------------------------+
| REPEATABLE-READ                 |
+---------------------------------+
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT @@SESSION.transaction_isolation;
+---------------------------------+
| @@SESSION.transaction_isolation |
+---------------------------------+
| READ-COMMITTED                  |
+---------------------------------+

セッションのトランザクション分離レベルが READ-COMMITTED に変更できたことを確認しました。

SAVEPOINT (トランザクションセーブポイントの設定)

SAVEPOINT <セーブポイント名>

SAVEPOINT はトランザクションのセーブポイントを設定します。ロールバックした場合は(トランザクションの開始地点ではなく)、セーブポイントまで戻ります。

セーブポイントの動作を確認するために、まずトランザクションで変更処理を行います。

SELECT * FROM tbl;
+------+--------+
| id   | str    |
+------+--------+
|    1 | update |
|    2 | test   |
|    3 | foo    |
+------+--------+
BEGIN;
INSERT INTO tbl VALUES(4,'bar');
SELECT * FROM tbl;
+------+--------+
| id   | str    |
+------+--------+
|    1 | update |
|    2 | test   |
|    3 | foo    |
|    4 | bar    |
+------+--------+

ここでセーブポイント save1 を設定します。

SAVEPOINT save1;
INSERT INTO tbl VALUES(5,'save');
SELECT * FROM tbl;
+------+--------+
| id   | str    |
+------+--------+
|    1 | update |
|    2 | test   |
|    3 | foo    |
|    4 | bar    |
|    5 | save   |
+------+--------+

セーブポイント save1 までロールバックします。

ROLLBACK TO save1;
SELECT * FROM tbl;
+------+--------+
| id   | str    |
+------+--------+
|    1 | update |
|    2 | test   |
|    3 | foo    |
|    4 | bar    |
+------+--------+

セーブポイントまでロールバックしていることを確認できました。

(トランザクション開始時まで戻っていません)

ROLLBACK;
SELECT * FROM tbl;
+------+--------+
| id   | str    |
+------+--------+
|    1 | update |
|    2 | test   |
|    3 | foo    |
+------+--------+

通常のロールバックにより、トランザクション開始時点まで戻りました。

LOCK TABLES (ロック取得)

LOCK TABLES <テーブル> <ロックタイプ>

LOCK TABLES は、テーブルロックを取得します。

ロックタイプが READ の場合、テーブルロックの動作は以下のとおりです。

LOCK TABLES tbl READ;

ここから端末2で操作を行います。

SELECT * FROM db.tbl;
+------+--------+
| id   | str    |
+------+--------+
|    1 | update |
|    2 | test   |
|    3 | foo    |
+------+--------+

ロックタイプが READ なので、読み込みは成功します。

次に書き込みを行います。

INSERT INTO db.tbl VALUES(5,'lock');

テーブルが端末1にロックされているので、いつまで経っても端末2の INSERT は完了しません。

UNLOCK TABLES (ロック解除)

UNLOCK TABLES

UNLOCK TABLES は、テーブルロックを解除します。

この作業は先程の LOCK 操作の続きです。

UNLOCK TABLES;

端末1の全てのテーブルのロックが解除されたため、端末2でロックされていた INSERT 処理が完了していることが確認できます。

最後に

データベース入門記事「SQL コマンド」に関する説明は以上となります。

その他のデータベースの入門記事ついては以下の記事をどうぞ。

参考資料・おすすめの書籍

一番初めに読む本

経験者向けの入門本

MySQL を触る方におすすめ