【データベース入門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(作成)

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 を触る方におすすめ