【SQL とは?】初心者が練習すべき SQL コマンド一覧

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

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

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

https://www.geeksforgeeks.org/sql-ddl-dml-tcl-dcl/

本記事では、上から順にコピペして実行することで、よく利用する 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 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 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 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  | (中略)
SELECT user, host FROM mysql.user;
+----------+-----------+
| user     | host      |
+----------+-----------+
| hogetech | localhost |
| mysql.sys| localhost |
+----------+-----------+
DROP USER 'hogetech'@'localhost';
SELECT user, host FROM mysql.user;
+----------+-----------+
| user     | host      |
+----------+-----------+
| mysql.sys| localhost |
+----------+-----------+
スポンサーリンク

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

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

DML は、主に CRUD 操作を行う言語です。

CRUD
CRUD とは、データの作成 (Create)/読取 (Read)/更新 (Update)/削除 (Delete) です。

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 文で確認できます。

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 など (集計関数)

集計関数とは、行を集計して、新しいテーブルを作成する関数です。

今回は、合計を求める集計関数 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を使います。

OVER (Window 関数)

OVER (Window 関数) は、現在の行に関連する別の行を取得します。
SELECT Window 関数() OVER() FROM テーブル名

今回は、前の行を取得する LAG() を例に説明します。(Window 関数の一覧はこちら)

SELECT id, LAG(id) OVER() FROM tbl;
+------+----------------+
| id   | LAG(id) OVER() |
+------+----------------+
|    1 |           NULL |
|    2 |              1 |
|    2 |              2 |
+------+----------------+

LAG(id) は、id の1行前の値を表示していることがわかります。

集計関数と Window 関数

集計関数に OVER() をつけると、Window 関数になります。

集計関数は行を集約しますが、Window 関数は行を集約しません。

SELECT COUNT(id) FROM tbl;
+-----------+
| COUNT(id) |
+-----------+
|         3 |
+-----------+
SELECT COUNT(id) OVER() FROM tbl;
+------------------+
| COUNT(id) OVER() |
+------------------+
|                3 |
|                3 |
|                3 |
+------------------+

※ COUNT() は行数を数えます。Window 関数は行ごとに COUNT の結果を表示します。

WINDOW (名前付き Window)

WINDOW は、OVER で指定する条件に名前をつけます。
SELECT Window 関数 OVER() FROM テーブル名 WINDOW Window名 AS (OVER の条件)

WINDOW は、同じ OVER の条件を使い回す場合に使います。

SELECT
id,
LAG(id) OVER (ORDER BY id),
LAG(str) OVER (ORDER BY id)
FROM tbl;
SELECT
id,
LAG(id) OVER w,
LAG(str) OVER w
FROM tbl
WINDOW w AS (ORDER BY id);

どちらも同じ結果となります。

+------+----------------------------+-----------------------------+
| id   | LAG(id) OVER (ORDER BY id) | LAG(str) OVER (ORDER BY id) |
+------+----------------------------+-----------------------------+
|    1 |                       NULL | NULL                        |
|    2 |                          1 | hoge                        |
|    2 |                          2 | foo                         |
+------+----------------------------+-----------------------------+

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

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 条件
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 は、テーブルの行を削除します。
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 |
+------+--------+
スポンサーリンク

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

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

DCL には、主に以下の 2 種類の 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 コマンドです。
トランザクショントランザクションとは、複数の処理を1まとめに処理することです。

トランザクションの詳細については、以下の記事をご覧ください。

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

BEGIN (トランザクションを開始)

BEGIN は、新しいトランザクションを開始します。
BEGIN;
BEGIN;

ここからは、トランザクションがどのような効果をもたらすのか、見ていきます。

SELECT * FROM tbl;
+------+--------+
| id   | str    |
+------+--------+
|    1 | update |
+------+--------+
INSERT INTO tbl VALUES(2,'foo');

この状態で、トランザクション中の端末1と、別の端末2でテーブルを確認してみます。

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

端末1のトランザクション中に実行した INSERT の結果が、端末2ではまだ反映されていません。

ROLLBACK (トランザクションの取消)

ROLLBACK は、現在のトランザクションの変更を取り消します。
ROLLBACK
SELECT * FROM tbl;
+------+--------+
| id   | str    |
+------+--------+
|    1 | update |
|    2 | foo    |
+------+--------+
SELECT * FROM tbl;
+------+--------+
| id   | str    |
+------+--------+
|    1 | update |
+------+--------+

先ほどの BEGIN (トランザクション) を始める前のテーブルに戻りました。

COMMIT (トランザクションの永続化)

COMMIT は、現在のトランザクションの変更を確定します。
COMMIT
BEGIN;
INSERT INTO tbl VALUES(2,'foo');
SELECT * FROM tbl;
+------+--------+
| id   | str    |
+------+--------+
|    1 | update |
|    2 | foo    |
+------+--------+
SELECT * FROM tbl;
+------+--------+
| id   | str    |
+------+--------+
|    1 | update |
|    2 | foo    |
+------+--------+

トランザクションの結果が確定し、別の端末2からでも INSERT の結果が反映されています。

なお、COMMIT した結果を ROLLBACK することはできません。

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

SAVEPOINT (トランザクションのセーブポイントを設定)

SAVEPOINT は、トランザクションロールバック地点 (セーブポイント) を設定します。
SAVEPOINT <セーブポイント名>

トランザクションでテーブルを更新し、セーブポイントを設定してみます。

SELECT * FROM tbl;
+------+--------+
| id   | str    |
+------+--------+
|    1 | update |
|    2 | foo    |
+------+--------+
BEGIN;
INSERT INTO tbl VALUES(3,'before');
SELECT * FROM tbl;
+------+--------+
| id   | str    |
+------+--------+
|    1 | update |
|    2 | foo    |
|    3 | before |
+------+--------+

ここで、セーブポイント save1 を設定します。

SAVEPOINT save1;

セーブポイント後に、さらにテーブルを更新します。

INSERT INTO tbl VALUES(4,'after');

セーブポイント save1 までロールバックします。

SELECT * FROM tbl;
+------+--------+
| id   | str    |
+------+--------+
|    1 | update |
|    2 | foo    |
|    3 | before |
|    4 | after  |
+------+--------+
ROLLBACK TO save1;
SELECT * FROM tbl;
+------+--------+
| id   | str    |
+------+--------+
|    1 | update |
|    2 | foo    |
|    3 | before |
+------+--------+

トランザクション開始時点ではなく、セーブポイントまでロールバックしています。

なお、通常のロールバックを行うと、トランザクション開始前のテーブルに戻ります。

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

関連記事

データベースの基礎知識

参考記事

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