【入門】SQL コマンドの一覧 (SQL 文/ステートメント)

SQLSQLとは、データベースを操作するための言語です。
SQL コマンド (SQL 文/SQL ステートメント)SQL コマンドとは、データベースを操作するコマンドです。

SQL コマンドは、プログラミングをせずにデータベースを操作できるため急速に広まりました。

本記事では、よく利用する SQL コマンドを上から順にコピペして実行することで、SQL を理解できる構成にしています。

関連記事:データベースの基礎知識
スポンサーリンク

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

DDL(Data Definition Language/データ定義言語)DDL とは、データベース/テーブル/ユーザーなどの、作成/変更/削除を行う SQL です。

DDL には主に、以下の 3 種類の SQL コマンドが存在します。

CREATE 文 (作成)

CREATE DATABASE

CREATE DATABASE は、データベースを作成します。
CREATE DATABASE データベース;
CREATE DATABASE db;
SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| db                 |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

PostgreSQL でデータベースを確認する場合

\l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 db        | postgres | UTF8     | ja_JP.UTF-8 | ja_JP.UTF-8 | 
 postgres  | postgres | UTF8     | ja_JP.UTF-8 | ja_JP.UTF-8 |

データベース (db) が作成できていることが確認できます。

以降、データベース (db) を使用するために、MySQL では次のコマンドを実行します。

use db;

CREATE TABLE

CREATE TABLE は、データベースにテーブルを作成します。
CREATE TABLE テーブル名(列名1 データ型1, 列名2 データ型2...)
CREATE TABLE tbl(id int, str varchar(10));
SHOW TABLES IN db;
+--------------+
| Tables_in_db |
+--------------+
| tbl          |
+--------------+

tbl という名前のテーブルが作成できています。

DESC tbl;
+----------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| str   | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

PostgreSQL でテーブルの存在と構造を確認

\d
        List of relations
 Schema | Name | Type  |  Owner   
--------+------+-------+----------
 public | tbl  | table | postgres

tbl という名前のテーブルが作成できています。

\d tbl;
             Table "public.tbl"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 id     | integer               | 
 str    | character varying(10) | 

CREATE USER

CREATE USER は、ユーザーを作成します。
CREATE USER 'ユーザー名'@'ホスト名' IDENTIFIED BY 'パスワード'

Postgres でユーザーを作成する場合

CREATE USER hogetech PASSWORD 'Hoge111!';
\du
                             List of roles
 Role name |                   Attributes                   | Member of 
-----------+------------------------------------------------+-----------
hogetech   |                                                | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}
CREATE USER 'hogetech'@'localhost' IDENTIFIED BY 'Hoge111!';
SELECT user, host FROM mysql.user;
+----------+-----------+
| user     | host      |
+----------+-----------+
| hogetech | localhost |
| mysql.sys| localhost |
+----------+-----------+

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

ALTER 文 (変更)

ALTER TABLE ADD COLUMN

ALTER TABLE ADD COLUMN は、テーブルに列を追加します。
ALTER TABLE テーブル名 ADD COLUMN 列名;
ALTER TABLE tbl ADD COLUMN add_column char(5);

※MySQL では、8.0 からの機能 (詳しくはここ)

DESC db.tbl;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id         | int(11)     | YES  |     | NULL    |       |
| str        | varchar(10) | YES  |     | NULL    |       |
| add_column | char(5)     | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+

PostgreSQL でテーブルの構造を確認

\d tbl;
               Table "public.tbl"
   Column   |         Type          | Modifiers 
------------+-----------------------+-----------
 id         | integer               | 
 str        | character varying(10) | 
 add_column | character(5)          |

テーブル (tbl) に、列 (add_column) を追加したことが確認できます。

ALTER TABLE RENAME COLUMN

ALTER TABLE RENAME COLUMN は、テーブルの列名を変更します。
ALTER TABLE テーブル名 RENAME COLUMN 変更前の列名 TO 変更後の列名;
ALTER TABLE tbl RENAME COLUMN add_column TO rename_column;
DESC db.tbl;
+------------+-------------+------+-----+---------+-------+
| Field         | Type        | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| id            | int(11)     | YES  |     | NULL    |       |
| str           | varchar(10) | YES  |     | NULL    |       |
| rename_column | char(5)     | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+

PostgreSQL でテーブルの構造を確認

\d tbl;
               Table "public.tbl"
   Column   |         Type          | Modifiers 
------------+-----------------------+-----------
 id         | integer               | 
 str        | character varying(10) | 
 rename_column | character(5)          |
テーブルの add_column 列を rename_column 列に変更したことが確認できます。

ALTER TABLE DROP COLUMN

ALTER TABLE DROP COLUMN は、テーブルの列を削除します。
ALTER TABLE テーブル名 DROP COLUMN 列名;
ALTER TABLE tbl DROP COLUMN rename_column;
DESC db.tbl;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id         | int(11)     | YES  |     | NULL    |       |
| str        | varchar(10) | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+

PostgreSQL でテーブルの構造を確認

\d tbl;
               Table "public.tbl"
   Column   |         Type          | Modifiers 
------------+-----------------------+-----------
 id         | integer               | 
 str        | character varying(10) |

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

DROP 文 (削除)

DROP TABLE

DROP TABLE は、テーブルを削除します。
DROP TABLE テーブル名;
DROP TABLE tbl;
SHOW TABLES IN db;
Empty set (0.00 sec)

PostgreSQL でテーブルの構造を確認

\d
No relations found.

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

DROP DATABASE

DROP DATABASE は、データベースを削除します。
DROP DATABASE データベース;
DROP DATABASE db;
SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

PostgreSQL でテーブルの構造を確認

\l
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | ja_JP.UTF-8 | ja_JP.UTF-8 | 
 template0 | postgres | UTF8     | ja_JP.UTF-8 | ja_JP.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | ja_JP.UTF-8 | ja_JP.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres

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

DROP USER

DROP USER は、ユーザーを削除します。
DROP USER 'ユーザー名'@'ホスト';

PostgreSQL でユーザーを削除する場合

DROP USER hogetech;
\du
 Role name | (中略)
-----------+-------
 postgres  | (中略)
 hogetech  | (中略)
\du
 Role name | (中略)
-----------+-------
 postgres  | (中略)
DROP USER 'hogetech'@'localhost';
SELECT user, host FROM mysql.user;
+----------+-----------+
| user     | host      |
+----------+-----------+
| hogetech | localhost |
| mysql.sys| localhost |
+----------+-----------+
SELECT user, host FROM mysql.user;
+----------+-----------+
| user     | host      |
+----------+-----------+
| mysql.sys| localhost |
+----------+-----------+
スポンサーリンク

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

DML (Data Manipulation Language/データ操作言語)DML とは、データベースのデータを挿入/取得/更新/削除する SQL です。

DML には主に、以下の 4 種類の SQL コマンドが存在します。

以降の説明で利用するテーブル

CREATE DATABASE db;
USE db;
CREATE TABLE tbl(id int, str varchar(10));

USE db は MySQL のみ。PostgreSQL では不要

INSERT 文 (挿入)

INSERT INTO (挿入)

INSERT INTO 文は、テーブルに行を挿入します。
INSERT INTO テーブル名 VALUES(列の値1, 列の値2...)
INSERT INTO tbl VALUES(1,'hoge');

挿入した結果は、次に紹介する SELECT 文で確認できます。

「ERROR 1146 (42S02): Table 'db.tbl' doesn't exist」が発生した場合

データベース (db) とテーブル (tbl) が存在しません。

データベースの作成テーブルの作成をしてください。

CREATE DATABASE db;
USE db;
CREATE TABLE tbl(id int, str varchar(10));

「ERROR: リレーション"tbl"は存在しません」が発生した場合

テーブル (tbl) が存在しません。テーブルの作成をしてください。

CREATE TABLE tbl(id int, str varchar(10));

SELECT 文 (取得)

SELECT (取得)SELECT は、テーブルから行を取得します。
SELECT 列名 FROM テーブル名

全ての列を取得する場合は * を使います。

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

WHERE (条件)

WHERE は、テーブルを条件でフィルタリングします。
SELECT 列名 FROM テーブル名 WHERE 条件
INSERT INTO tbl VALUES(2,'foo');
SELECT * FROM tbl WHERE id = 1;
+------+------+
| id   | str  |
+------+------+
|    1 | hoge |
+------+------+
SELECT * FROM tbl;
+------+------+
| id   | str  |
+------+------+
|    1 | hoge |
|    2 | foo  |
+------+------+

WHERE 句で、id = 1 の行のみを取得できました。

DISTINCT (重複削除)

DISTINCT は、テーブルから重複する行を削除します。
SELECT DISTINCT 列名 FROM テーブル名
INSERT INTO tbl VALUES(2,'foo');
SELECT DISTINCT * FROM tbl;
+------+------+
| id   | str  |
+------+------+
|    1 | hoge |
|    2 | foo  |
+------+------+
SELECT * FROM tbl;
+------+------+
| id   | str  |
+------+------+
|    1 | hoge |
|    2 | foo  |
|    2 | foo  |
+------+------+

重複している行が削除されていいます。

GROUP BY (集約)

DISTINCT は、テーブルを指定した列で集約します。
SELECT 列名 FROM テーブル名 GROUP BY 集約条件
SELECT * FROM tbl GROUP BY id, str;
+------+------+
| id   | str  |
+------+------+
|    1 | hoge |
|    2 | foo  |
+------+------+
SELECT * FROM tbl;
+------+------+
| id   | str  |
+------+------+
|    1 | hoge |
|    2 | foo  |
|    2 | foo  |
+------+------+

重複する行を集約していることが確認できます。

DISTINCT と GROUP BY の違い

DISTINCT と GROUP BY の違いは以下のとおりです。

DISTINCTGROUP BY
句の説明取得した行から、重複を削除取得した行を、グループ化して集計
重複削除△ (できるが、全ての列を書く必要があり面倒)
集計関数×
重複削除はどっちでもできる
集計関数とは集計関数とは、行を集計して、新しいテーブルを作成する関数です。

今回は合計を求める集計関数 SUM() を例に説明します。(集計関数の一覧はこちら)

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

同じ str を持つ id の合計が表示されます。

HAVING (集計後の条件)

HAVING は、GROUP BY で集約したテーブルから、条件でフィルタリングします。
SELECT 列名 FROM テーブル名 GROUP BY 集約条件 HAVING 表示条件
SELECT * FROM tbl GROUP BY id, str HAVING id = 2;
+------+------+
| id   | str  |
+------+------+
|    2 | foo  |
+------+------+
SELECT * FROM tbl GROUP BY id, str;
+------+------+
| id   | str  |
+------+------+
|    1 | hoge |
|    2 | foo  |
+------+------+

HAVING で指定した id = 2 のみを取得できます。

HAVING と WHERE の違い

HAVING と WHERE は式の評価順が異なります

句の式の評価順は以下のとおりです。

  1. WHERE
  2. GROUP BY (集計関数)
  3. HAVING

そのため、GROUP BY を使う場合は HAVINGそれ以外は WHEREを使います。

ORDER BY (ソート)

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

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 関数を例に説明します。

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

LIMIT (行数制限)

LIMIT は、指定した行数のみ結果を表示します。
SELECT 列名 FROM テーブル名 LIMIT 行数
SELECT * FROM tbl LIMIT 2;
+------+------+
| id   | str  |
+------+------+
|    1 | hoge |
|    2 | foo  |
+------+------+
SELECT * FROM tbl;
+------+------+
| id   | str  |
+------+------+
|    1 | hoge |
|    2 | foo  |
|    2 | foo  |
+------+------+

2行しか表示されなくなりました。

INSERT INTO ~ SELECT (別のテーブルから挿入)

INSERT INTO ~ SELECT は、SELECT 文の結果を別のテーブルに挿入できます。
INSERT INTO 挿入先テーブル名 SELECT 列名 FROM ソーステーブル名
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  |
+------+------+

JOIN (横に結合)

JOIN は、複数のテーブルから1つのテーブルを作成します。
SELECT 列名 FROM テーブル名1 JOIN テーブル名2

JOIN には次の 5 種類が存在し、SELECT, UPDATE, DELETE 文で利用できます。

以降の JOIN の説明では、以下のテーブルを使います。

CREATE TABLE another(id int, str varchar(10));
INSERT INTO another VALUES(1,'1another');
INSERT INTO another VALUES(2,'2another');
INSERT INTO another VALUES(3,'3another');
CROSS JOIN (交差結合)
CROSS JOIN は、テーブルの直積 (全ての組み合わせ) です。
SELECT * FROM tbl CROSS JOIN another;
+------+------+------+----------+
| id   | str  | id   | str      |
+------+------+------+----------+
|    1 | hoge |    1 | 1another |
|    2 | foo  |    1 | 1another |
|    2 | foo  |    1 | 1another |
|    1 | hoge |    2 | 2another |
|    2 | foo  |    2 | 2another |
|    2 | foo  |    2 | 2another |
|    1 | hoge |    3 | 3another |
|    2 | foo  |    3 | 3another |
|    2 | foo  |    3 | 3another |
+------+------+------+----------+
SELECT * FROM tbl;
+------+------+
| id   | str  |
+------+------+
|    1 | hoge |
|    2 | foo  |
|    2 | foo  |
+------+------+
SELECT * FROM another;
+------+----------+
| id   | str      |
+------+----------+
|    1 | 1another |
|    2 | 2another |
|    3 | 3another |
+------+----------+
INNER JOIN (内部結合)
INNER JOIN は、両方のテーブルにある行を結合します。
SELECT * FROM tbl INNER JOIN another on tbl.id = another.id;
+------+------+------+----------+
| id   | str  |id   | str      |
+------+------+------+----------+
|    1 | hoge |    1 | 1another |
|    2 | foo  |    2 | 2another |
|    2 | foo  |    2 | 2another |
+------+------+------+----------+
SELECT * FROM tbl;
+------+------+
| id   | str  |
+------+------+
|    1 | hoge |
|    2 | foo  |
|    2 | foo  |
+------+------+
SELECT * FROM another;
+------+----------+
| id   | str      |
+------+----------+
|    1 | 1another |
|    2 | 2another |
|    3 | 3another |
+------+----------+
LEFT JOIN (左外部結合)
LEFT JOIN は、左側のテーブルに、条件が一致する右側のテーブルを結合します。
SELECT * FROM tbl LEFT JOIN another on tbl.id = another.id;
+------+------+------+----------+
| id   | str  | id   | str      |
+------+------+------+----------+
|    1 | hoge |    1 | 1another |
|    2 | foo  |    2 | 2another |
|    2 | foo  |    2 | 2another |
+------+------+------+----------+
SELECT * FROM tbl;
+------+------+
| id   | str  |
+------+------+
|    1 | hoge |
|    2 | foo  |
|    2 | foo  |
+------+------+
SELECT * FROM another;
+------+----------+
| id   | str      |
+------+----------+
|    1 | 1another |
|    2 | 2another |
|    3 | 3another |
+------+----------+
RIGHT JOIN (右外部結合)
RIGHT JOIN は、右側のテーブルに、条件が一致する左側のテーブルを結合します。
SELECT * FROM tbl RIGHT JOIN another on tbl.id = another.id;
+------+------+------+----------+
| id   | str  | id   | str      |
+------+------+------+----------+
|    1 | hoge |    1 | 1another |
|    2 | foo  |    2 | 2another |
|    2 | foo  |    2 | 2another |
| NULL | NULL |    3 | 3another |
+------+------+------+----------+
SELECT * FROM tbl;
+------+------+
| id   | str  |
+------+------+
|    1 | hoge |
|    2 | foo  |
|    2 | foo  |
+------+------+
SELECT * FROM another;
+------+----------+
| id   | str      |
+------+----------+
|    1 | 1another |
|    2 | 2another |
|    3 | 3another |
+------+----------+
FULL OUTER JOIN (完全外部結合)
FULL OUTER JOIN は、左右のテーブルを全て結合します。(存在しない場合は空のセル)

※MySQL では、現時点で FULL OUTER JOIN がサポートされていません。

SELECT * FROM tbl FULL OUTER JOIN another on tbl.id = another.id;
 id | str  | id |   str   
----+------+----+----------
  1 | hoge |  1 | 1another
  2 | foo  |  2 | 2another
  2 | foo  |  2 | 2another
     |         |  3 | 3another
SELECT * FROM tbl;
+------+------+
| id   | str  |
+------+------+
|    1 | hoge |
|    2 | foo  |
|    2 | foo  |
+------+------+
SELECT * FROM another;
+------+----------+
| id   | str      |
+------+----------+
|    1 | 1another |
|    2 | 2another |
|    3 | 3another |
+------+----------+

UNION (縦に結合)

UNION は、複数の SELECT 文の結果を、1つに結合します。
SELECT 列名1 FROM テーブル名1 UNION SELECT 列名2 FROM テーブル名2
SELECT * FROM tbl UNION SELECT * FROM another;
+------+----------+
| id   | str      |
+------+----------+
|    1 | hoge     |
|    2 | foo      |
|    1 | 1another |
|    2 | 2another |
|    3 | 3another |
+------+----------+
SELECT * FROM tbl;
+------+------+
| id   | str  |
+------+------+
|    1 | hoge |
|    2 | foo  |
|    2 | foo  |
+------+------+
SELECT * FROM another;
+------+----------+
| id   | str      |
+------+----------+
|    1 | 1another |
|    2 | 2another |
|    3 | 3another |
+------+----------+

UNION は重複が排除されます。重複を含む場合は、UNION ALL を利用します。

SELECT * FROM tbl UNION ALL SELECT * FROM another;
+------+----------+
| id   | str      |
+------+----------+
|    1 | hoge     |
|    2 | foo      |
|    2 | foo      |
|    1 | 1another |
|    2 | 2another |
|    3 | 3another |
+------+----------+

サブクエリ

サブクエリ は、文の中に別のクエリ (SELECT 文) が含まれるものです。
SELECT 列名1 FROM テーブル名1 WHERE 列名1 = (SELECT 列名2 FROM テーブル名2);
クエリとはクエリとは、狭義の意味で Select 文、広義の意味で文 (Statement) です。

query is a synonym for a SELECT statement.
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

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

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

https://dev.mysql.com/doc/refman/8.0/ja/glossary.html#glos_query
SELECT * FROM tbl WHERE id = (SELECT id FROM another WHERE str = '1another');
+------+------+
| id   | str  |
+------+------+
|    1 | hoge |
+------+------+
SELECT * FROM tbl;
+------+------+
| id   | str  |
+------+------+
|    1 | hoge |
|    2 | foo  |
|    2 | foo  |
+------+------+
SELECT * FROM another;
+------+----------+
| id   | str      |
+------+----------+
|    1 | 1another |
|    2 | 2another |
|    3 | 3another |
+------+----------+

UPDATE 文 (更新)

UPDATE は、新しい値に更新します。
UPDATE テーブル名 SET 列名= WHERE 条件
UPDATE tbl SET str = 'update' WHERE str = 'hoge';
SELECT * FROM tbl;
+------+------+
| id   | str  |
+------+------+
|    1 | hoge |
|    2 | foo  |
|    2 | foo  |
+------+------+
SELECT * FROM tbl;
+------+--------+
| id   | str    |
+------+--------+
|    1 | update |
|    2 | foo    |
|    2 | foo    |
+------+--------+

DELETE 文 (削除)

DELETE は、テーブルの行を削除します。
DELETE FROM テーブル名 WHERE 行の条件
DELETE FROM tbl WHERE id = 2;
SELECT * FROM tbl;
+------+--------+
| id   | str    |
+------+--------+
|    1 | update |
|    2 | foo    |
|    2 | foo    |
+------+--------+
SELECT * FROM tbl;
+------+--------+
| id   | str    |
+------+--------+
|    1 | update |
+------+--------+
スポンサーリンク

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

DCL (Data Control Language)DCL とは、データベースのアクセス制御を行う SQL コマンドです。

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

  • GRANT (ユーザーに権限を与える)
  • REVOKE (ユーザーから権限を取り消す)

GRANT 文(権限割り当て)

GRANT は、ユーザーに権限を割り当てます。
GRANT 権限 ON 権限レベル TO 'ユーザー'@'ホスト'

Postgres で権限を割り当てる場合

GRANT SELECT ON tbl TO hogetech;

ERROR: ロール"hogetech"は存在しません が発生した場合

ユーザーが存在しない可能性があります。ユーザーを作成してください。

CREATE USER hogetech PASSWORD 'Hoge111!';
\dp
postgres=# \dp
                                Access privileges
 Schema |  Name   | Type  |     Access privileges     | Column access privileges 
--------+---------+-------+---------------------------+--------------------------
 public | another | table |                           | 
 public | tbl     | table | postgres=arwdDxt/postgres+| 
        |         |       | hogetech=r/postgres       | 
 public | tbl2    | table |                           |

別の端末から作成したユーザーでログインし、GRANT の動作確認をしてみます。

psql -d postgres
SELECT * FROM tbl;
ERROR:  リレーション tbl への権限がありません
SELECT * FROM tbl;
+------+--------+
| id   | str    |
+------+--------+
|    1 | update |
+------+--------+
GRANT SELECT ON tbl TO 'hogetech'@'localhost';

ERROR 1819 (HY000): Your password does not satisfy the current policy requirements が発生した場合

ユーザーが存在しない可能性があります。ユーザーを作成してください。

CREATE USER 'hogetech'@'localhost' IDENTIFIED BY 'Hoge111!';
SHOW GRANTS FOR 'hogetech'@'localhost';
+------------------------------------------------------+
| Grants for hogetech@localhost                        |
+------------------------------------------------------+
| GRANT USAGE ON *.* TO `hogetech`@`localhost`         |
| GRANT SELECT ON `db`.`tbl` TO `hogetech`@`localhost` |
+------------------------------------------------------+

別の端末から作成したユーザーでログインし、GRANT の動作確認をしてみます。

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

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

REVOKE (権限取り消し)

REVOKE は、ユーザーから権限を取り消します。
REVOKE 権限 ON 権限レベル FROM 'ユーザー'@'ホスト'

Postgres から権限を取り消す場合

REVOKE SELECT ON tbl FROM hogetech;
\dp
[ RECORD 2 ]------------+--------------------------
Schema           | public
Name             | tbl
Type             | table
Access privileges| postgres=arwdDxt/postgres
                 | hogetech=r/postgres
\dp
[ RECORD 2 ]------------+--------------------------
Schema           | public
Name             | tbl
Type             | table
Access privileges| postgres=arwdDxt/postgres
REVOKE SELECT ON tbl FROM '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` |
+------------------------------------------------------+
SHOW GRANTS FOR 'hogetech'@'localhost';
+----------------------------------------------+
| Grants for hogetech@localhost                |
+----------------------------------------------+
| GRANT USAGE ON *.* TO `hogetech`@`localhost` |
+----------------------------------------------+

TCL(TransactionControlLanguage/トランザクション制御言語)

TCL (Transaction Control Language)TCL とは、トランザクションを管理する SQL コマンドです。

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 は、トランザクションの変更を確定します。
GRANT 権限 ON 権限レベル TO 'ユーザー'@'ホスト'
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 処理が完了していることが確認できます。

関連記事

関連記事:データベースの基礎知識

参考記事

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