【データベース入門】SQL コマンド(SQL 文)とは

データベース
スポンサーリンク

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 db;
SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| db                 |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

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

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 という名前のテーブルが作成できていることを確認できました。

ALTER

ALTER TABLE ADD

「ALTER TABLE <テーブル> ADD COLUMN <カラム>」でテーブルのカラムを追加します。

最初にテーブルの構造を確認します。

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 <カラム>」で add_field カラムを rename_field にリネームします。

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    |       |
+--------------+-------------+------+-----+---------+-------+

ALTER TABLE DROP

「ALTER TABLE <テーブル> DROP COLUMN <カラム>」でテーブルのカラムを削除します。

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    |       |
+-------+-------------+------+-----+---------+-------+

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

DROP

DROP TABLE

「DROP TABLE <テーブル>」でテーブルを削除します。

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

テーブルが削除されていることを確認できました。

DROP DATABASE

「DROP DATABASE <データベース>」でデータベースを削除します。

DROP DATABASE db;
Empty set (0.00 sec)
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()」で VALUES に指定した値をテーブルに挿入します。

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

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

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

INSERT INTO ~ SELECT

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

SELECT

「SELECT <カラム> FROM <テーブル>」で指定したテーブルのカラムを表示します。

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

WHERE

「SELECT <カラム> FROM <テーブル> WHERE <条件>」で指定した条件の行を取得できます。

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 <テーブル>」は重複する行を削除して結果を表示します。

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 <条件>」で、条件に指定したカラムでグループ化(同じ値を1つに)します。

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 <表示条件>」で GROUP BY で集約したテーブルから、HAVING で指定した条件の行のみを表示します。

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 <条件>」で、指定した条件順に行をソートできます。

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 <値>」で、指定した値の行数のみ結果を表示します。

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

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

SELECT INTO

「SELECT <カラム> INTO <変数> FROM <テーブル>」で、カラム値を変数に代入することができます。

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

INSERT INTO ~ SELECT

「INSERT INTO <テーブル> SELECT <カラム> FROM WHERE」で SELECT 文の結果をテーブルに挿入できます。

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

UPDATE

「UPDATE <テーブル> SET <カラム> = <値> WHERE <条件>」で条件に一致するテーブルのカラムに値をセットします。

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 <行の条件>」でテーブルの行を削除

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 <テーブル>」で、複数の 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 '<パスワード>';」で 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 '<ユーザー>'@'<ホスト>';」で、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 '<ユーザー>'@'<ホスト>';」で、ユーザーから権限を取り消すことが出来ます。

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` |
+----------------------------------------------+

SET TRANSACTION

「SET <スコープ> TRANSACTION ISOLATION LEVEL <トランザクション分離レベル>」で、トランザクション分離レベルを指定できます。

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

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 に変更できたことを確認しました。

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」は、現在のトランザクションの変更を永続的なものにします。

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

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

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

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

COMMIT;

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

トランザクション2でテーブルを確認">
SELECT * FROM db.tbl;
+------+--------+
| id   | str    |
+------+--------+
|    1 | update |
|    2 | test   |
|    3 | foo    |
+------+--------+

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

ROLLBACK

「ROLLBACK」は、ロールバックして現在のトランザクションの変更を取り消します。

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

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」が消え、トランザクション開始時のテーブルにロールバックしていることを確認しました。

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

「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」は、テーブルロックを解除します。

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

UNLOCK TABLES;

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

参考資料

0

コメント