SQL コマンドは、プログラミングをせずにデータベースを操作できるため急速に広まりました。
SQL コマンドは、主に次の４種類が存在します。
本記事は、上からコピペすることで、よく利用する SQL コマンドを理解できる構成にしています。
SQL コマンドの実行環境が無い場合は、以下のいずれかの記事で構築してください。
| 関連記事：データベースの基礎知識編 | |||||
|---|---|---|---|---|---|
| 学習ロードマップ | |||||
|---|---|---|---|---|---|
DDL (Data Definition Language/データ定義言語)
DDL には、主に以下の 3 種類の SQL コマンドが存在します。
CREATE 文 (作成)
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 テーブル名(列名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 'ユーザー名'@'ホスト名' 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!';
+----------+-----------+ | user | host | +----------+-----------+ | hogetech | localhost | | mysql.sys| localhost | +----------+-----------+
ユーザーが作成されていることを確認できます。
ALTER 文 (変更)
ALTER TABLE テーブル名 ADD COLUMN 列名;
ALTER TABLE tbl ADD COLUMN add_column char(5);
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 変更前の列名 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 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 tbl;
SHOW TABLES IN db;
Empty set (0.00 sec)
PostgreSQL でテーブルの構造を確認
\d
No relations found.
テーブル(tbl) が削除されたことを確認できます。
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 'ユーザー名'@'ホスト名';
PostgreSQL でユーザーを削除する場合
DROP USER hogetech;
\du
Role name | (中略)
-----------+-------
postgres | (中略)
hogetech | (中略)
\du
Role name | (中略) -----------+------- postgres | (中略)
DML (Data Manipulation Language/データ操作言語)
DML は、主に CRUD 操作を行う言語です。
DML には、主に以下の 4 種類の SQL コマンドが存在します。
以降に利用するデータベースとテーブル
CREATE DATABASE db;
USE db;
CREATE TABLE tbl(id int, str varchar(10));
※ USE db は MySQL のみ。PostgreSQL では不要
INSERT 文 (挿入/作成)
INSERT INTO テーブル名 VALUES(列の値1, 列の値2...)
INSERT INTO tbl VALUES(1,'hoge');
挿入した結果は、次に紹介する SELECT 文で確認できます。
SELECT 文 (取得/読取)
SELECT 列名 FROM テーブル名
全ての列を取得する場合は * を使います。
SELECT * FROM tbl;
+------+------+ | id | str | +------+------+ | 1 | hoge | +------+------+
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 の行のみを取得できました。
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 | +------+------+
重複している行が削除されていいます。
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 の違いは以下のとおりです。
| DISTINCT | GROUP 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 の合計が表示されます。
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 は式の評価順が異なります。
句の式の評価順は以下のとおりです。
- WHERE
- GROUP BY (集計関数)
- HAVING
そのため、GROUP BY を使う場合は HAVING、それ以外は WHEREを使います。
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 の１行前の値を表示していることがわかります。
集計関数と 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 の結果を表示します。
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;
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 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 | +------+----------------------------+-----------------------------+
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 | +------+------+
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 | +------+------+
２行しか表示されなくなりました。
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 | +------+------+
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');
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 |
+------+----------+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 |
+------+----------+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 |
+------+----------+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 |
+------+----------+※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 | 3anotherSELECT * FROM tbl;
+------+------+
| id | str |
+------+------+
| 1 | hoge |
| 2 | foo |
| 2 | foo |
+------+------+SELECT * FROM another;
+------+----------+
| id | str |
+------+----------+
| 1 | 1another |
| 2 | 2another |
| 3 | 3another |
+------+----------+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 列名1 FROM テーブル名1 WHERE 列名1 = (SELECT 列名2 FROM テーブル名2);A query is a synonym for a SELECT statement.
https://stackoverflow.com/questions/4735856/difference-between-a-statement-and-a-query-in-sql
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.
クエリー
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 テーブル名 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 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 には、主に以下の 2 種類の SQL コマンドが存在します。
- GRANT (ユーザーに権限を与える)
- REVOKE (ユーザーから権限を取り消す)
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 権限 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` | +------------------------------------------------------+
TCL(TransactionControlLanguage/トランザクション制御言語)
トランザクションの詳細については、以下の記事をご覧ください。
TCL には、主に以下の 4 種類の SQL コマンドが存在します。
BEGIN;
BEGIN;
ここからは、トランザクションがどのような効果をもたらすのか、見ていきます。
SELECT * FROM tbl;
+------+--------+ | id | str | +------+--------+ | 1 | update | +------+--------+
INSERT INTO tbl VALUES(2,'foo');
この状態で、トランザクション中の端末１と、別の端末２でテーブルを確認してみます。
SELECT * FROM tbl;
+------+--------+
| id | str |
+------+--------+
| 1 | update |
| 2 | foo |
+------+--------+SELECT * FROM tbl;
+------+--------+ | id | str | +------+--------+ | 1 | update | +------+--------+
端末１のトランザクション中に実行した INSERT の結果が、端末２ではまだ反映されていません。
ROLLBACK
SELECT * FROM tbl;
+------+--------+
| id | str |
+------+--------+
| 1 | update |
| 2 | foo |
+------+--------+SELECT * FROM tbl;
+------+--------+ | id | str | +------+--------+ | 1 | update | +------+--------+
先ほどの BEGIN (トランザクション) を始める前のテーブルに戻りました。
COMMIT
SELECT * FROM tbl;
+------+--------+
| id | str |
+------+--------+
| 1 | update |
| 2 | foo |
+------+--------+SELECT * FROM tbl;
+------+--------+
| id | str |
+------+--------+
| 1 | update |
| 2 | foo |
+------+--------+トランザクションの結果が確定し、別の端末２からでも INSERT の結果が反映されています。
なお、COMMIT した結果を ROLLBACK することはできません。
SELECT * FROM tbl;
+------+--------+ | id | str | +------+--------+ | 1 | update | | 2 | foo | +------+--------+
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 のどちらを使えば効率が良いのか迷うということがおきた。結論から言ってしまうとどちらの関数も重複行を…
qiita.com