本記事はデータベース入門記事のうち、第2回「SQL コマンド」です。
本記事は以下の書籍を参考に執筆しています。
一番初めに読む本
経験者向けの入門本
MySQL を触る方におすすめ
その他のデータベースの入門記事ついては以下の記事をご確認ください。
- 【データベース入門1】データベースとは
- 【データベース入門2】SQL コマンドとは、SQL 文の一覧 ←今ここ
- 【データベース入門3】トランザクションと ACID 特性とは
- 【データベース入門4】バックアップ・クラスター・レプリケーション
- 【データベース入門5】テーブル設計・正規化
- 【データベース入門6】オプティマイザー・実行計画
SQL コマンドとは
SQL は DBMS (データベース管理システム)のデータを操作するための言語です。
SQL はプログラミングをせずにデータが操作できることから、急速に広まりました。
SQL の文法は以下の3つに大別されます。
- データ定義言語 (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) の結果が2より大きい行のみが表示されます。
なお、式の評価順が以下のとおりのため、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 句を利用して指定する必要があります。
■ 今回は、1行前の行を表示する LAG() Window 関数を例に説明します。
※ ORDER BY 句は行をソートするものです。
+------+---------------------------+ | id | LAG(id) OVER(ORDER by id) | +------+---------------------------+ | 1 | NULL | | 2 | 1 | | 2 | 2 | +------+---------------------------+
「LAG(id)」は、id の1行前の値を表示していることがわかります。
■ 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 | +------+------+
2行しか表示されてないことが確認できます。
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 は「2つのテーブルから1つのテーブルを作成する」句で、「SELECT
・DELETE
・UPDATE
」文利用可能です。
JOIN には次の5種類が存在します。
- CROSS JOIN(直積結合)
- INNER JOIN(内部結合)
- LEFT JOIN(左外部結合)
- RIGHT JOIN(右外部結合)
- FULL JOIN(完全外部結合)
CROSS JOIN(直積結合)
CROSS JOIN は2つのテーブルの直積を持つテーブルを作成します。

以下のテーブルを持つ 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 | +------+--------+------+------+
2つのテーブル(tbl と tbl2)の直積を持つ、新しいテーブルを作成したことがわかります。
INNER JOIN(内部結合)
INNER JOIN は2つのテーブルの 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 文の結果を、1つの結果セットに結合します。
+------+--------+ | 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 | +------+--------+
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.
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 | +------+--------+
1つ目のSELECT の WHERE 句に2つ目の SELECT の結果が利用できていることを確認できました。
上記のサブクエリ「(SELECT id FROM tbl2 WHERE str = 'hoge')」では、1行のみを返しますが、複数行の結果を返す場合は「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
トランザクションとは、複数の処理を1つにまとめたものです。
トランザクションの詳細については以下の記事に記載しています。
トランザクション分離レベルに応じて、他のトランザクションに影響を分離します。
例えば、トランザクション分離レベルが READ-COMMITTED の場合、2つのトランザクションは以下のとおりとなります。
+------+--------+ | id | str | +------+--------+ | 1 | update | | 2 | test | +------+--------+
+------+--------+ | id | str | +------+--------+ | 1 | update | | 2 | test | | 3 | foo | +------+--------+
この状態で2つ目の端末(トランザクション2)でログインし、テーブルを確認します。
+------+--------+ | id | str | +------+--------+ | 1 | update | | 2 | test | +------+--------+
トランザクション2※では、トランザクション1の INSERT の結果がまだ反映されていません。
※BEGIN をしてない場合、1行ごとにトランザクション(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 で行ったトランザクションの続きから開始します。
端末2(トランザクション2)でテーブルを確認します。
+------+--------+ | id | str | +------+--------+ | 1 | update | | 2 | test | +------+--------+
端末1(トランザクション1)で COMMITしてみます。
次に端末2(トランザクション2)でテーブルを確認します。
+------+--------+ | id | str | +------+--------+ | 1 | update | | 2 | test | | 3 | foo | +------+--------+
トランザクション1で INSERT した結果がトランザクション2に反映されました。
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行目の「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 の場合、テーブルロックの動作は以下のとおりです。
ここから端末2で操作を行います。
+------+--------+ | id | str | +------+--------+ | 1 | update | | 2 | test | | 3 | foo | +------+--------+
ロックタイプが READ なので、読み込みは成功します。
次に書き込みを行います。
テーブルが端末1にロックされているので、いつまで経っても端末2の INSERT は完了しません。
UNLOCK TABLES (ロック解除)
UNLOCK TABLES
UNLOCK TABLES は、テーブルロックを解除します。
この作業は先程の LOCK 操作の続きです。
端末1の全てのテーブルのロックが解除されたため、端末2でロックされていた INSERT 処理が完了していることが確認できます。
最後に
データベース入門記事「SQL コマンド」に関する説明は以上となります。
その他のデータベースの入門記事ついては以下の記事をどうぞ。
- 【データベース入門1】データベースとは
- 【データベース入門2】SQL コマンドとは、SQL 文の一覧 ←今ここ
- 【データベース入門3】トランザクションと ACID 特性とは
- 【データベース入門4】バックアップ・クラスター・レプリケーション
- 【データベース入門5】テーブル設計・正規化
- 【データベース入門6】オプティマイザー・実行計画
参考資料・おすすめの書籍
一番初めに読む本
経験者向けの入門本
MySQL を触る方におすすめ