本記事はデータベース入門記事のうち、第２回「SQL コマンド」です。
本記事は以下の書籍を参考に執筆しています。
一番初めに読む本
経験者向けの入門本
MySQL を触る方におすすめ
その他のデータベースの入門記事ついては以下の記事をご確認ください。
- 【データベース入門１】データベースとは
- 【データベース入門２】SQL コマンドとは、SQL 文の一覧 ←今ここ
- 【データベース入門３】トランザクションと ACID 特性とは
- 【データベース入門４】バックアップ・クラスター・レプリケーション
- 【データベース入門５】テーブル設計・正規化
- 【データベース入門６】オプティマイザー・実行計画
SQL コマンドとは
SQL は DBMS （データベース管理システム）のデータを操作するための言語です。
SQL はプログラミングをせずにデータが操作できることから、急速に広まりました。
SQL の文法は以下の３つに大別されます。
- データ定義言語 (DDL: Data Definition Language)
- データ操作言語 (DML: Data Manipulation Language)
- データ制御言語 (DCL: Data Control Language)
以降では MySQL を利用して具体的な SQL コマンドの動作を紹介します。
また、MySQL をまだインストールしていない場合は以下の記事を参考に MySQL をインストールしてください。
データ定義言語 (DDL: Data Definition Language)
DDL とは、データベースオブジェクト (データベースやテーブル) の作成・削除・定義の変更を行う SQL コマンドです。
DDL に相当する SQL コマンドの例は以下のとおりです。
CREATE（作成）
CREATE DATABASE
CREATE DATABASE <データベース名>
CREATE DATABASE ステートメントは、データベースを作成します。
+--------------------+
| Database |
+--------------------+
| db |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+db という名前のデータベースが作成できたことを確認できます。
CREATE TABLE
CREATE TABLE <データベース>.<テーブル>(<カラム名> <データ型>)
CREATE TABLE ステートメントは、データベースにテーブルを作成します。
+--------------+
| Tables_in_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 ステートメントは、テーブルにカラムを追加します。
以下に利用例を示します。
+-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int | YES | | NULL | | | str | varchar(10) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+
+-----------+-------------+------+-----+---------+-------+
| 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 ステートメントは、テーブルのカラム名を変更します。
以下に利用例を示します。
+-----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-----+---------+-------+ | id | int | YES | | NULL | | | str | varchar(10) | YES | | NULL | | | add_field | char(5) | YES | | NULL | | +-----------+-------------+------+-----+---------+-------+
+--------------+-------------+------+-----+---------+-------+
| 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 ステートメントは、テーブルのカラムを削除します。
以下に利用例を示します。
+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| str | varchar(10) | YES | | NULL | |
| rename_field | char(5) | YES | | NULL | |
+--------------+-------------+------+-----+---------+-------++-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int | YES | | NULL | | | str | varchar(10) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+
テーブルから rename_field カラムが削除されたことを確認できます。
DROP (削除)
DROP TABLE
DROP TABLE <テーブル>
DROP TABLE ステートメントは、テーブルを削除します。
以下に利用例を示します。
Empty set (0.00 sec)
tbl テーブルが削除されたことを確認できます。
DROP DATABASE
DROP DATABASE <データベース>
DROP DATABASE ステートメントは、データベースを削除します。
以下に利用例を示します。
+--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+
db データベースが削除されたことを確認できます。
データ操作言語 (DML: Data Manipulation Language)
DML とは、テーブルの持つ行に対して、CRUD 操作を行う SQL コマンドです。
なお、CRUD とは以下の操作の頭文字です。
- Create（作成）
- Read（読み取り）
- Update（更新）
- Delete（削除）
上記の操作に対応する SQL コマンドは以下のとおりです。
| CRUD 操作 | SQL コマンド | 説明 |
|---|---|---|
| Create | INSERT | テーブルに行を挿入 |
| Read | SELECT | テーブルから行を取得 |
| Update | UPDATE | テーブルの行を更新 |
| Delete | DELETE | テーブルから行を削除 |
以降では次のテーブルに対して操作を行います。
+-------+-------------+------+-----+---------+-------+ | 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 ステートメントは、テーブルに行を挿入します。
+------+------+
| id | str |
+------+------+
| 1 | hoge |
+------+------+INSERT INTO ~ SELECT
現時点では SELECT 句の説明をしていないので、後述します。
SELECT (取得)
SELECT <カラム> FROM <テーブル>
SELECT ステートメントは、テーブルから行を取得します。
+------+------+ | id | str | +------+------+ | 1 | hoge | +------+------+
WHERE (条件)
SELECT <カラム> FROM <テーブル> WHERE <条件>
WHERE 句は、SELECT ステートメントで取得する行の条件を指定します。
利用例は以下のとおりです。
+------+------+
| id | str |
+------+------+
| 1 | hoge |
| 2 | foo |
+------+------++------+------+
| id | str |
+------+------+
| 1 | hoge |
+------+------+WHERE で指定したカラムの値を持つ行のみを表示します。
DISTINCT (重複削除)
SELECT DISTINCT <カラム> FROM <テーブル>
DISTINCT 句は、SELECT ステートメントで取得する行から重複する行を削除します。
利用例は以下のとおりです。
+------+------+ | id | str | +------+------+ | 1 | hoge | | 2 | foo | | 2 | foo | +------+------+
+------+------+
| id | str |
+------+------+
| 1 | hoge |
| 2 | foo |
+------+------+GROUP BY (集約)
SELECT <カラム> FROM <テーブル> GROUP BY <条件>
GROUP BY 句は、SELECT ステートメントで取得する行を指定したカラムで集約します。
利用例は以下のとおりです。
+------+------+ | id | str | +------+------+ | 1 | hoge | | 2 | foo | | 2 | foo | +------+------+
+------+------+
| id | str |
+------+------+
| 1 | hoge |
| 2 | foo |
+------+------+GROUP BY は以下のように集約関数（指定したカラムで集約し、新しいテーブルを作成する関数）と合わせて利用することもあります。
+------+------------+ | str | COUNT(str) | +------+------------+ | hoge | 1 | | foo | 2 | +------+------------+
上記の例では、集約関数 COUNT() を利用することで、str カラムに存在する重複する値をカウントしています。
DISTINCT と GROUP BY で何が違うの？という方は以下の記事をご覧ください。
結論だけ言うと、重複排除を行うなら「どっちでもいい」
ですが、DISTINCT の方が SQL が短くて綺麗に見えます。
一応以下の基準で使い分ければいいのではないでしょうか。
HAVING
SELECT <カラム> FROM <テーブル> GROUP BY <集約条件> HAVING <表示条件>
HAVING 句は、GROUP BY で集約したテーブルから指定した条件の行のみを表示します。
+------+------------+ | str | COUNT(str) | +------+------------+ | hoge | 1 | | foo | 2 | +------+------------+
+------+------------+ | str | COUNT(str) | +------+------------+ | foo | 2 | +------+------------+
HAVING で指定した COUNT(str) の結果が２より大きい行のみが表示されます。
なお、式の評価順が以下のとおりのため、GROUP BY（集約関数）の結果を元にフィルタリングするには「WHERE」ではなく、「HAVING」を利用する必要があります。
- WHERE
- GROUP BY
- 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() | パーティション内の現在の行数 |
Window 関数で計算した結果の表示方法は、OVER 句を利用して指定する必要があります。
■ 今回は、１行前の行を表示する LAG() Window 関数を例に説明します。
※ ORDER BY 句は行をソートするものです。
+------+---------------------------+ | id | LAG(id) OVER(ORDER by id) | +------+---------------------------+ | 1 | NULL | | 2 | 1 | | 2 | 2 | +------+---------------------------+
「LAG(id)」は、id の１行前の値を表示していることがわかります。
■ Window 関数を複数利用すると、毎回同じ OVER 句を記載する必要があります。
+------+----------------------------+------+-----------------------------+ | 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 <名前付きウィンドウ>」で利用できます。
+------+----------------+------+-----------------+ | 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 句は、指定した条件順に行をソートします。
+------+------+ | id | str | +------+------+ | 1 | hoge | | 2 | foo | | 2 | foo | +------+------+
+------+------+ | id | str | +------+------+ | 2 | foo | | 2 | foo | | 1 | hoge | +------+------+
LIMIT
SELECT <カラム> FROM <テーブル> LIMIT <値>
LIMIT とは、指定した値の行数のみ結果を表示します。
+------+------+ | id | str | +------+------+ | 1 | hoge | | 2 | foo | +------+------+
２行しか表示されてないことが確認できます。
INTO
SELECT <カラム> INTO <変数> FROM <テーブル>
+------+ | @var | +------+ | 1 | +------+
INSERT INTO ~ SELECT
INSERT INTO <挿入先テーブル> SELECT <カラム> FROM <挿入元テーブル>
INSERT INTO ~ SELECT ステートメントは、SELECT 文の結果をテーブルに挿入できます。
+------+------+ | id | str | +------+------+ | 1 | hoge | | 2 | foo | | 2 | foo | +------+------+
UPDATE (更新)
UPDATE <テーブル> SET <カラム> = <値> WHERE <条件>
UPDATE ステートメントは、既存のカラムを新しい値に更新します。
+------+------+ | id | str | +------+------+ | 1 | hoge | | 2 | foo | | 2 | foo | +------+------+
+------+--------+
| id | str |
+------+--------+
| 1 | update |
| 2 | foo |
| 2 | foo |
+------+--------+DELETE (削除)
DELETE FROM <テーブル> WHERE <行の条件>
DELETE ステートメントは、テーブルの行を削除します。
+------+--------+ | id | str | +------+--------+ | 1 | update | | 2 | foo | | 2 | foo | +------+--------+
+------+--------+ | id | str | +------+--------+ | 1 | update | +------+--------+
JOIN (横に結合)
JOIN は「２つのテーブルから１つのテーブルを作成する」句で、「SELECT・DELETE・UPDATE」文利用可能です。
JOIN には次の５種類が存在します。
- CROSS JOIN（直積結合）
- INNER JOIN（内部結合）
- LEFT JOIN（左外部結合）
- RIGHT JOIN（右外部結合）
- FULL JOIN（完全外部結合）
CROSS JOIN（直積結合）
CROSS JOIN は２つのテーブルの直積を持つテーブルを作成します。
以下のテーブルを持つ tbl と tbl2 を CROSS JOIN してみます。
+------+--------+ | id | str | +------+--------+ | 1 | update | | 2 | test | +------+--------+
+------+------+ | id | str | +------+------+ | 1 | hoge | | 2 | foo | | 3 | bar | +------+------+
+------+--------+------+------+ | 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 | +------+--------+------+------+
２つのテーブル（tbl と tbl2）の直積を持つ、新しいテーブルを作成したことがわかります。
INNER JOIN（内部結合）
INNER JOIN は２つのテーブルの CROSS JOIN（直積）から、ON 句の条件に一致する行のみを表示します。
+------+--------+------+------+ | 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 | +------+--------+------+------+
+------+--------+------+------+ | id | str | id | str | +------+--------+------+------+ | 1 | update | 1 | hoge | | 2 | test | 2 | foo | +------+--------+------+------+
tbl.id と tbl2.id が一致する行のみ表示しています。
なお、MySQL では「JOIN = CROSS JOIN = INNER JOIN」です。標準 SQL では区別されています。
MySQL では、
https://dev.mysql.com/doc/refman/8.0/ja/join.htmlJOIN、CROSS JOIN、およびINNER JOINは構文上同等です (互いに置き換えることができます)。 標準 SQL では、それらは同等ではありません。INNER JOINはON句とともに使用され、CROSS JOINはそれ以外のときに使用されます。
LEFT JOIN（左外部結合）
左側のテーブルの行を全て表示し、条件に一致する右側のテーブルをくっつけます（結合）。
+------+--------+ | id | str | +------+--------+ | 1 | update | | 2 | test | +------+--------+
+------+------+ | id | str | +------+------+ | 1 | hoge | | 2 | foo | | 3 | bar | +------+------+
+------+--------+------+------+ | id | str | id | str | +------+--------+------+------+ | 1 | update | 1 | hoge | | 2 | test | 2 | foo | +------+--------+------+------+
RIGHT JOIN (右外部結合)
右側のテーブルの行を全て表示し、条件に一致する左側のテーブルをくっつけます（結合）。
+------+--------+ | id | str | +------+--------+ | 1 | update | | 2 | test | +------+--------+
+------+------+ | id | str | +------+------+ | 1 | hoge | | 2 | foo | | 3 | bar | +------+------+
+------+--------+------+------+ | id | str | id | str | +------+--------+------+------+ | 1 | update | 1 | hoge | | 2 | test | 2 | foo | | NULL | NULL | 3 | bar | +------+--------+------+------+
対応する左側のテーブルが無い場合は、NULL となります。
FULL JOIN (完全外部結合)
左右両方のテーブルの行を全て表示し、条件に一致するもう片方のテーブルをひっつけます。
残念ながら現時点では MySQL に FULL JOIN はありません。
UNION (縦に結合)
SELECT <カラム> FROM <テーブル> UNION SELECT <カラム> FROM <テーブル>
UNION は、複数の SELECT 文の結果を、１つの結果セットに結合します。
+------+--------+ | id | str | +------+--------+ | 1 | update | | 2 | test | +------+--------+
+------+------+ | id | str | +------+------+ | 1 | hoge | | 2 | foo | | 3 | bar | +------+------+
+------+--------+ | id | str | +------+--------+ | 1 | update | | 2 | test | | 1 | hoge | | 2 | foo | | 3 | bar | +------+--------+
２つのテーブルが縦に結合され、１つになったことがわかります。
サブクエリ
最初に、クエリとは 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.
https://stackoverflow.com/questions/4735856/difference-between-a-statement-and-a-query-in-sql
Statements, which may have a persistent effect on schemas and data, or which may control transactions, program flow, connections, sessions, or diagnostics.
サブクエリは以下のように、「文の中」に「別のSELECT 文（クエリ）」がある文のことです。
SELECT <カラム> FROM <テーブル> WHERE <条件> = (SELECT <カラム> FROM <テーブル>)
+------+--------+ | id | str | +------+--------+ | 1 | update | | 2 | test | +------+--------+
+------+------+ | id | str | +------+------+ | 1 | hoge | | 2 | foo | | 3 | bar | +------+------+
+------+--------+ | id | str | +------+--------+ | 1 | update | +------+--------+
１つ目のSELECT の WHERE 句に２つ目の SELECT の結果が利用できていることを確認できました。
上記のサブクエリ「(SELECT id FROM tbl2 WHERE str = 'hoge')」では、１行のみを返しますが、複数行の結果を返す場合は「HAVING」や「ANY」等を利用します。
サブクエリーで使用されている場合、ワード
INは= ANYのエイリアスです。ワード
https://dev.mysql.com/doc/refman/8.0/ja/any-in-some-subqueries.htmlSOMEはANYのエイリアスです。
+------+------+ | id | str | +------+------+ | 1 | hoge | | 2 | foo | +------+------+
サブクエリ(SELECT id FROM tbl); の結果（id =1, 2）のいずれかに一致する行を表示します。
データ制御言語 (DCL: Data Control Language)
DCL とは、データベースの権限やトランザクションの制御を行う SQL コマンドです。
DCL に相当する SQL コマンドの例は以下のとおりです。
- GRANT （特定のデータベース利用者に特定の作業を行う権限を与える）
- REVOKE （特定のデータベース利用者からすでに与えた権限を剥奪する）
- SET TRANSACTION （トランザクションモード（分離レベル）の設定）
- BEGIN （トランザクションの開始）
- COMMIT （トランザクションの確定）
- ROLLBACK （トランザクションの取り消し）
- SAVEPOINT （任意にロールバック地点を設定する）
- LOCK（テーブルなどの資源を占有する）
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.や%など)
https://dev.mysql.com/doc/refman/8.0/ja/validate-password.html
MEDIUMポリシーでは、パスワードに少なくとも 1 つの数字、1 つの小文字、1 つの大文字および 1 つの特殊文字 (英数字以外) を含める必要があるという条件が追加されます。
+------------------+-----------+ | user | host | +------------------+-----------+ | hogetech | localhost | | mysql.infoschema | localhost | | mysql.session | localhost | | mysql.sys | localhost | | root | localhost | +------------------+-----------+
ユーザーが作成されていることを確認できます。
GRANT (権限割り当て)
GRANT <権限> ON <権限レベル> TO '<ユーザー>'@'<ホスト>'
GRANT ステートメントは、MySQL のユーザーアカウントに権限を割り当てます。
+------------------------------------------------------+
| Grants for hogetech@localhost |
+------------------------------------------------------+
| GRANT USAGE ON *.* TO `hogetech`@`localhost` |
| GRANT SELECT ON `db`.`tbl` TO `hogetech`@`localhost` |
+------------------------------------------------------+別の端末を開き、MySQL のユーザーアカウント hogetech としてログインして権限の動作を確認します。
+------+--------+ | id | str | +------+--------+ | 1 | update | | 2 | test | +------+--------+
ERROR 1142 (42000): SELECT command denied to user 'hogetech'@'localhost' for table 'tbl2'
権限を付与していないテーブルに対する操作は、DENIED となることを確認できました。
REVOKE (権限取り消し)
REVOKE <権限> ON <権限レベル> FROM '<ユーザー>'@'<ホスト>'
REVOKE は、ユーザーから権限を取り消すことが出来ます。
+------------------------------------------------------+
| Grants for hogetech@localhost |
+------------------------------------------------------+
| GRANT USAGE ON *.* TO `hogetech`@`localhost` |
| GRANT SELECT ON `db`.`tbl` TO `hogetech`@`localhost` |
+------------------------------------------------------+※ denied エラーが出た場合は MySQL のユーザーアカウント root で操作しているか確認してください。
+----------------------------------------------+ | Grants for hogetech@localhost | +----------------------------------------------+ | GRANT USAGE ON *.* TO `hogetech`@`localhost` | +----------------------------------------------+
BEGIN (トランザクション開始)
BEGIN
トランザクションとは、複数の処理を１つにまとめたものです。
トランザクションの詳細については以下の記事に記載しています。
トランザクション分離レベルに応じて、他のトランザクションに影響を分離します。
例えば、トランザクション分離レベルが READ-COMMITTED の場合、２つのトランザクションは以下のとおりとなります。
+------+--------+ | id | str | +------+--------+ | 1 | update | | 2 | test | +------+--------+
+------+--------+ | id | str | +------+--------+ | 1 | update | | 2 | test | | 3 | foo | +------+--------+
この状態で２つ目の端末（トランザクション２）でログインし、テーブルを確認します。
+------+--------+ | id | str | +------+--------+ | 1 | update | | 2 | test | +------+--------+
トランザクション２※では、トランザクション１の INSERT の結果がまだ反映されていません。
※BEGIN をしてない場合、１行ごとにトランザクション（BEGIN = START TRANSACTION）が実行される扱いとなります。
デフォルトでは、MySQL は自動コミットモードが有効になった状態で動作します。 つまり、特にトランザクション内にない場合、各ステートメントは
https://dev.mysql.com/doc/refman/8.0/ja/commit.htmlSTART TRANSACTIONおよびCOMMITで囲まれているかのようにアトミックです。
COMMIT (トランザクションを永続化)
COMMIT
COMMIT は、現在のトランザクションの変更を永続的なものにします。
これにより、Atomicity (原始性) と Durability (永続性) を実現します。
なお、Atomicity (原始性) と Durability (永続性) については以下の記事で紹介しています。
先程の BEGIN で行ったトランザクションの続きから開始します。
端末２（トランザクション２）でテーブルを確認します。
+------+--------+ | id | str | +------+--------+ | 1 | update | | 2 | test | +------+--------+
端末１（トランザクション１）で COMMITしてみます。
次に端末２（トランザクション２）でテーブルを確認します。
+------+--------+ | id | str | +------+--------+ | 1 | update | | 2 | test | | 3 | foo | +------+--------+
トランザクション１で INSERT した結果がトランザクション２に反映されました。
ROLLBACK (トランザクションの取り消し)
ROLLBACK
ROLLBACK は、現在のトランザクションの変更を取り消します。
これにより、Atomicity (原始性) を実現します。
なお、Atomicity (原始性) については以下の記事で紹介しています。
ロールバックの動作を確認するために、まずトランザクションで変更処理を行います。
+------+--------+ | id | str | +------+--------+ | 1 | update | | 2 | test | | 3 | foo | +------+--------+
+------+--------+ | id | str | +------+--------+ | 1 | update | | 2 | test | | 3 | foo | | 4 | bar | +------+--------+
トランザクションで変更したこの状態でロールバックします。
+------+--------+ | id | str | +------+--------+ | 1 | update | | 2 | test | | 3 | foo | +------+--------+
４行目の「4, bar」が消え、トランザクション開始時のテーブルにロールバックしていることを確認しました。
SET TRANSACTION (トランザクション分離レベルの設定)
SET <スコープ> TRANSACTION ISOLATION LEVEL <トランザクション分離レベル>
SET TRANSACTION ISOLATION LEVEL で、トランザクション分離レベルを指定できます。
トランザクション分離レベル (Isolation = トランザクションの分離性) については、以下の記事で説明しています。
+---------------------------------+ | @@SESSION.transaction_isolation | +---------------------------------+ | REPEATABLE-READ | +---------------------------------+
+---------------------------------+ | @@SESSION.transaction_isolation | +---------------------------------+ | READ-COMMITTED | +---------------------------------+
セッションのトランザクション分離レベルが READ-COMMITTED に変更できたことを確認しました。
SAVEPOINT (トランザクションセーブポイントの設定)
SAVEPOINT <セーブポイント名>
SAVEPOINT はトランザクションのセーブポイントを設定します。ロールバックした場合は（トランザクションの開始地点ではなく）、セーブポイントまで戻ります。
セーブポイントの動作を確認するために、まずトランザクションで変更処理を行います。
+------+--------+ | id | str | +------+--------+ | 1 | update | | 2 | test | | 3 | foo | +------+--------+
+------+--------+ | id | str | +------+--------+ | 1 | update | | 2 | test | | 3 | foo | | 4 | bar | +------+--------+
ここでセーブポイント save1 を設定します。
+------+--------+ | id | str | +------+--------+ | 1 | update | | 2 | test | | 3 | foo | | 4 | bar | | 5 | save | +------+--------+
セーブポイント save1 までロールバックします。
+------+--------+ | id | str | +------+--------+ | 1 | update | | 2 | test | | 3 | foo | | 4 | bar | +------+--------+
セーブポイントまでロールバックしていることを確認できました。
（トランザクション開始時まで戻っていません）
+------+--------+ | id | str | +------+--------+ | 1 | update | | 2 | test | | 3 | foo | +------+--------+
通常のロールバックにより、トランザクション開始時点まで戻りました。
LOCK TABLES (ロック取得)
LOCK TABLES <テーブル> <ロックタイプ>
LOCK TABLES は、テーブルロックを取得します。
ロックタイプが READ の場合、テーブルロックの動作は以下のとおりです。
ここから端末２で操作を行います。
+------+--------+ | id | str | +------+--------+ | 1 | update | | 2 | test | | 3 | foo | +------+--------+
ロックタイプが READ なので、読み込みは成功します。
次に書き込みを行います。
テーブルが端末１にロックされているので、いつまで経っても端末２の INSERT は完了しません。
UNLOCK TABLES (ロック解除)
UNLOCK TABLES
UNLOCK TABLES は、テーブルロックを解除します。
この作業は先程の LOCK 操作の続きです。
端末１の全てのテーブルのロックが解除されたため、端末２でロックされていた INSERT 処理が完了していることが確認できます。
最後に
データベース入門記事「SQL コマンド」に関する説明は以上となります。
その他のデータベースの入門記事ついては以下の記事をどうぞ。
- 【データベース入門１】データベースとは
- 【データベース入門２】SQL コマンドとは、SQL 文の一覧 ←今ここ
- 【データベース入門３】トランザクションと ACID 特性とは
- 【データベース入門４】バックアップ・クラスター・レプリケーション
- 【データベース入門５】テーブル設計・正規化
- 【データベース入門６】オプティマイザー・実行計画
参考資料・おすすめの書籍
一番初めに読む本
経験者向けの入門本
MySQL を触る方におすすめ
