SQLコマンドと実行例をまとめた #Linux

はじめに

データベース関連の学習をしたついでに記事にも残しておこうと思う

実行環境はこちら

Linux(Ubuntu)
バージョン: 22.04
MySQL
バージョン: 8.0.34

目的
SQLのコマンドを忘れてしまったときに思い出せるようにまとめておきたい

コマンド集

ユーザ追加（CREATE USER）

構文はこちら
CREATE USER '[user]'@'[host]' IDENTIFIED BY '[password]'

user: 任意のユーザ名
host: 対象ホスト
password : 任意のパスワード

ユーザ登録権限が付与されているユーザ（rootとか）でMySQLに接続

MySQL

-- 実行例
mysql> CREATE USER 'user_test'@'localhost' IDENTIFIED BY 'p@ssWord1';
Query OK, 0 rows affected (0.02 sec)
-- 確認
mysql> SELECT user,host FROM mysql.user WHERE user = 'user_test'\G
*************************** 1. row ***************************
user: user_test
host: localhost
1 row in set (0.00 sec)

ユーザ削除（DROP USER）

構文はこちら
DROP USER '[user]'@'[host]'

user: 削除対象のユーザ名
host: 削除対象ユーザのホスト名

ユーザ削除権限が付与されているユーザ（rootとか）でMySQLに接続

MySQL

-- 実行例
mysql> DROP USER 'user_test'@'localhost';
Query OK, 0 rows affected (0.01 sec)
-- 確認
mysql> SELECT * FROM mysql.user WHERE user = 'user_test'\G
Empty set (0.00 sec)

権限設定

特権を持つユーザー（rootとか）で実行する
セキュリティ上の理由から、必要最小限の権限だけを付与することが推奨される
※「とりあえず全権限を付与しておこう！」みたいな考えはよくない

追加されたユーザの初期状態はこれ

MySQL

mysql> SHOW GRANTS FOR 'user_test'@'localhost';
+-----------------------------------------------+
| Grants for user_test@localhost                |
+-----------------------------------------------+
| GRANT USAGE ON *.* TO `user_test`@`localhost` |
+-----------------------------------------------+
1 row in set (0.00 sec)

USAGEは権限なしと同じような意味
DB接続もできないしレコード操作もできない

ユーザに権限を付与（GRANT）

構文はこちら
GRANT [付与する権限] ON [database].* TO '[user]'@'[host]'

付与する権限: SELECT, UPDATE, ... など

権限の種類

ALL [PRIVILEGES]
ALTER
ALTER ROUTINE
CREATE
CREATE ROLE
CREATE ROUTINE
CREATE TABLESPACE
CREATE TEMPORARY TABLES
CREATE USER
CREATE VIEW
DELETE
DROP
DROP ROLE
EVENT
EXECUTE
FILE
GRANT OPTION
INDEX
INSERT
LOCK TABLES
PROCESS
PROXY
REFERENCES
RELOAD
REPLICATION CLIENT
REPLICATION SLAVE
SELECT
SHOW DATABASES
SHOW VIEW
SHUTDOWN
SUPER
TRIGGER
UPDATE
USAGE

MySQL

-- 実行例
mysql> GRANT SELECT, INSERT, DELETE, UPDATE ON test_database.* TO 'user_test'@'localhost';
Query OK, 0 rows affected (0.01 sec)
-- 確認
mysql> SHOW GRANTS FOR 'user_test'@'localhost';
+--------------------------------------------------------------------------------------+
| Grants for user_test@localhost                                                       |
+--------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `user_test`@`localhost`                                        |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `test_database`.* TO `user_test`@`localhost` |
+--------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

"test_database"内のすべてのテーブル＋オブジェクトに対する操作権限を追加

ユーザ権限を削除（REVOKE）

構文はこちら
REVOKE [削除する権限] ON ~ FROM '[user]'@'[host]'

MySQL

-- 実行例
mysql> REVOKE SELECT, INSERT, UPDATE, DELETE ON `test_database`.* FROM `user_test`@`localhost`;
Query OK, 0 rows affected (0.01 sec)
-- 確認
mysql> SHOW GRANTS FOR 'user_test'@'localhost';
+-----------------------------------------------+
| Grants for user_test@localhost                |
+-----------------------------------------------+
| GRANT USAGE ON *.* TO `user_test`@`localhost` |
+-----------------------------------------------+
1 row in set (0.00 sec)

ロール

ロールの考え方
例えば、user1,user2,user3,user4 のMySQLユーザがいるとする

admin : 全ての権限
developer : select,update,delete,drop,insert
common : select
というロールをそれぞれ用意したとして

各ユーザにロールを割り当てる
user1 : admin
user2 : developer
user3 : common
user4 : common

ユーザごとにそれぞれ GRANT ON で権限付与するよりも簡単に権限の付与と管理ができる

ロール作成（CREATE ROLE）

構文はこちら
CREATE ROLE [role]

role: 任意のロール名

「ロール作成→ユーザにロールを付与」の実行例

MySQL

-- ロール作成
mysql> CREATE ROLE role_admin;
Query OK, 0 rows affected (0.01 sec)
-- ロール確認
mysql> SELECT user,host FROM mysql.user WHERE user='role_admin';
+------------+------+
| user       | host |
+------------+------+
| role_admin | %    |
+------------+------+
-- 初期の権限
mysql> SHOW GRANTS FOR 'role_admin'@'%';
+----------------------------------------+
| Grants for role_admin@%                |
+----------------------------------------+
| GRANT USAGE ON *.* TO `role_admin`@`%` |
+----------------------------------------+
-- ロールに権限を付与
mysql> GRANT SELECT, INSERT, DELETE, UPDATE ON test_s_work_database.* TO 'role_admin'@'%';
Query OK, 0 rows affected (0.01 sec)
-- 権限確認
mysql> SHOW GRANTS FOR 'role_admin'@'%';
+--------------------------------------------------------------------------------------+
| Grants for role_admin@%                                                              |
+--------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `role_admin`@`%`                                               |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `test_s_work_database`.* TO `role_admin`@`%` |
+--------------------------------------------------------------------------------------+
--　ユーザにロールを付与
mysql> GRANT 'role_admin' to 'user_test'@'localhost';
Query OK, 0 rows affected (0.01 sec)
-- ユーザにロールが付与される
mysql> SHOW GRANTS FOR 'user_test'@'localhost';
+---------------------------------------------------+
| Grants for test_user@localhost                    |
+---------------------------------------------------+
| GRANT USAGE ON *.* TO `user_test`@`localhost`     |
| GRANT `role_admin`@`%` TO `user_test`@`localhost` |
+---------------------------------------------------+

ロール削除（DROP ROLE）

MySQL

-- ユーザに付与されているロールを外す
REVOKE 'role_admin' FROM 'user_test'@'localhost';
Query OK, 0 rows affected (0.01 sec)
-- ユーザからロールが消える
mysql> SHOW GRANTS FOR 'test_user'@'localhost';
+-----------------------------------------------+
| Grants for test_user@localhost                |
+-----------------------------------------------+
| GRANT USAGE ON *.* TO `test_user`@`localhost` |
+-----------------------------------------------+
-- ロール削除
mysql> DROP ROLE 'role_admin';
Query OK, 0 rows affected (0.02 sec)
-- 確認
mysql> SELECT user,host FROM mysql.user WHERE user='role_admin';
Empty set (0.01 sec)

データベース作成（CREATE DATABASE）

構文はこちら
CREATE DATABASE [database]

database: 任意のデータベース名

CREATE権限が付与されているユーザでMySQLに接続

MySQL

-- 実行例
mysql> CREATE DATABASE test_database;
Query OK, 1 row affected (0.02 sec)
-- 確認
mysql> SHOW DATABASES LIKE 'test_database';
+--------------------------+
| Database (test_database) |
+--------------------------+
| test_database            |
+--------------------------+
1 row in set (0.00 sec)

データベース削除（DROP DATABASE）

構文はこちら
DROP DATABASE [database]

database: 削除対象のデータベース名

DROP権限が付与されているユーザでMySQLに接続

MySQL

-- 実行例
mysql> DROP DATABASE test_database;
Query OK, 0 rows affected (0.01 sec)
-- 確認
mysql> SHOW DATABASES LIKE 'test_database';
Empty set (0.00 sec)

接続ポート番号の確認

MySQL

mysql> SHOW VARIABLES LIKE 'port';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 3306  |
+---------------+-------+
1 row in set (0.01 sec)

接続中データベースの確認

MySQL

mysql> SELECT DATABASE();
+----------------------+
| DATABASE()           |
+----------------------+
| test_database        |
+----------------------+
1 row in set (0.01 sec)

現在接続しているユーザを確認

MySQL

mysql> SELECT current_user();
+----------------------+
| current_user()       |
+----------------------+
| user_test@localhost  |
+----------------------+
1 row in set (0.00 sec)

現在の接続ホスト名を確認

MySQL

mysql> SELECT @@hostname;
+------------+
| @@hostname |
+------------+
| localhost  |
+------------+
1 row in set (0.00 sec)

システム変数`@@~`を使って確認できるもの

MySQLバージョン情報: SELECT @@version;
サーバーのタイムゾーン: SELECT @@global.time_zone;
デフォルトの文字セット: SELECT @@character_set_database;
現在のセッションの接続ID: SELECT @@session_id;
サーバーが許可する最大接続数: SELECT @@max_connections;
現在のSQLモード: SELECT @@sql_mode;
サーバーのポート番号: SELECT @@port;
ストレージエンジン: SELECT @@default_storage_engine;
接続のタイムアウト設定: SELECT @@connect_timeout;
MySQLのインストールディレクトリ: SELECT @@basedir;
現在のクライアントの文字セット: SELECT @@character_set_client;
接続の文字セット: SELECT @@character_set_connection;
サーバーのデフォルトのコロケーション: SELECT @@collation_server;
クライアントのデフォルトのコロケーション: SELECT @@collation_connection;
データベースのデータディレクトリ: SELECT @@datadir;
スロークエリログの状態: SELECT @@slow_query_log;
スロークエリログファイルのパス: SELECT @@slow_query_log_file;
一時ファイルのディレクトリ: SELECT @@tmpdir;
サーバーの稼働時間（秒）: SELECT @@uptime;
デフォルトの認証プラグイン: SELECT @@default_authentication_plugin;
自動コミットの設定を確認: SELECT @@autocommit;
外部キー制約のチェック状態を確認: SELECT @@foreign_key_checks;
現在のトランザクション分離レベル: SELECT @@transaction_isolation;
最大パケットサイズ: SELECT @@max_allowed_packet;
クエリキャッシュのサイズ: SELECT @@query_cache_size;
InnoDBのバッファプールサイズ: SELECT @@innodb_buffer_pool_size;
スレッドキャッシュサイズ: SELECT @@thread_cache_size;
クエリキャッシュのタイプ: SELECT @@query_cache_type;
一時テーブルの最大サイズ: SELECT @@tmp_table_size;
結合操作に使用するバッファのサイズ: SELECT @@join_buffer_size;
キーキャッシュのサイズ: SELECT @@key_buffer_size;
メモリ内テーブルの最大サイズ: SELECT @@max_heap_table_size;
InnoDBのログファイルサイズ: SELECT @@innodb_log_file_size;
トランザクションコミット時のInnoDBのログフラッシュ設定: SELECT @@innodb_flush_log_at_trx_commit;
InnoDBのテーブルごとのファイル使用設定: SELECT @@innodb_file_per_table;
最大許容パケットサイズ: SELECT @@max_allowed_packet;
'AUTO_INCREMENT'カラムのNULL値に対する設定: SELECT @@sql_auto_is_null;
バルク挿入時のバッファサイズ: SELECT @@bulk_insert_buffer_size;
各ユーザーが許可される最大接続数: SELECT @@max_user_connections;
開いているテーブルキャッシュのサイズ: SELECT @@table_open_cache;
InnoDBのスレッド並行性を制御する設定: SELECT @@innodb_thread_concurrency;
InnoDBのロック待機タイムアウト: SELECT @@innodb_lock_wait_timeout;
InnoDBのフラッシュメソッド: SELECT @@innodb_flush_method;
一般クエリログの状態: SELECT @@general_log;
一般クエリログファイルのパス: SELECT @@general_log_file;
リードバッファサイズ: SELECT @@read_buffer_size;
ランダムリードバッファサイズ: SELECT @@read_rnd_buffer_size;
ソートバッファサイズ: SELECT @@sort_buffer_size;
トランザクション分離レベル: SELECT @@tx_isolation;

これらのシステム変数は、MySQLやMariaDBの設定やパフォーマンスを調査するために非常に役立つ。他にもあるようだがこの辺で。

テーブル作成（CREATE TABLE）

構文はこちら
CREATE TABLE [table](...)

table: 任意のテーブル名
(...): (
カラム名1 データ型1 制約1,
カラム名2 データ型2 制約2,
...
カラム名N データ型N 制約N
)

実行パターンは３つくらいある
どの方法でも良い

MySQL接続（DB指定なし）⇒CREATE TABLE [database].[table](...)
MySQL接続（DB指定なし）⇒USE [database]⇒CREATE TABLE [table](...)

MySQL接続（DB指定あり）⇒CREATE TABLE [table](...)

実行例

MySQL

-- MySQL接続(パターン3)
-- mysql -uuser_test -ppassword test_database

-- 実行例
mysql> CREATE TABLE persons (
    ->     id INT PRIMARY KEY,
    ->     first_name VARCHAR(50),
    ->     last_name VARCHAR(50),
    ->     age INT
    -> );
Query OK, 0 rows affected (0.05 sec)
--確認
mysql> SHOW TABLES LIKE 'persons';
+-----------------------------------+
| Tables_in_test_database (persons) |
+-----------------------------------+
| persons                           |
+-----------------------------------+
1 row in set (0.00 sec)

テーブル定義の確認

いろいろある

DESC [table]

実行例

MySQL

mysql> DESC persons;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id         | int         | NO   | PRI | NULL    |       |
| first_name | varchar(50) | YES  |     | NULL    |       |
| last_name  | varchar(50) | YES  |     | NULL    |       |
| age        | int         | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

SHOW CREATE TABLE [table]

実行例

MySQL

mysql> SHOW CREATE TABLE persons\G
*************************** 1. row ***************************
       Table: persons
Create Table: CREATE TABLE `persons` (
  `id` int NOT NULL,
  `first_name` varchar(50) DEFAULT NULL,
  `last_name` varchar(50) DEFAULT NULL,
  `age` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = '[table]'

実行例

MySQL

mysql> SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = 'persons'\G
*************************** 1. row ***************************
           CONSTRAINT_CATALOG: def
            CONSTRAINT_SCHEMA: test_database
              CONSTRAINT_NAME: PRIMARY
                TABLE_CATALOG: def
                 TABLE_SCHEMA: test_database
                   TABLE_NAME: persons
                  COLUMN_NAME: id
             ORDINAL_POSITION: 1
POSITION_IN_UNIQUE_CONSTRAINT: NULL
      REFERENCED_TABLE_SCHEMA: NULL
        REFERENCED_TABLE_NAME: NULL
       REFERENCED_COLUMN_NAME: NULL
1 row in set (0.00 sec)

よく使うのはDESC [table]だと思う
「制約の詳細が知りたい」などといった場合にその他の方法を使う機会があるのかな

カラム追加

ALTER TABLE persons ADD sample varchar(20)

テーブル削除（DROP TABLE）

構文はこちら
DROP TABLE [table]

table: 削除対象のテーブル名

MySQL

-- 実行例
mysql> DROP TABLE persons;
Query OK, 0 rows affected (0.02 sec)
--確認
mysql> SHOW TABLES LIKE 'persons';
Empty set (0.00 sec)

ビュー作成（CREATE VIEW）

基本構文はこちら
CREATE VIEW [view] AS SELECT [column1, column2, ...] FROM [table] WHERE [condition]

view: 任意のビュー名
table: 参照テーブル名

MySQL

-- 実行例
mysql> CREATE VIEW view_persons AS SELECT * FROM persons WHERE age BETWEEN 20 and 29;
Query OK, 0 rows affected (0.02 sec)
-- 確認
mysql> SHOW TABLES;
+-------------------------+
| Tables_in_test_database |
+-------------------------+
| persons                 |
| view_persons            |
+-------------------------+
2 rows in set (0.00 sec)

カラムを指定することもできる

MySQL

-- 実行例
mysql> CREATE VIEW view_persons(id, f_name) AS SELECT id, first_name  FROM persons WHERE age BETWEEN 20 and 29;
Query OK, 0 rows affected (0.01 sec)

インデックス

実は制約条件付与時にもインデックスは設定されている

インデックス付与（CREATE INDEX）

どちらでも良い

CREATE INDEX [index] ON [table]([column])
ALTER TABLE [table] ADD INDEX [index]([column])

table: テーブル名
index: 任意のインデックス名
column: カラム名

MySQL

-- 実行例
/* 1. インデックス確認*/
mysql> SHOW INDEX FROM test_table;
+------------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table      | Non_unique | Key_name     | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+------------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| test_table |          0 | PRIMARY      |            1 | id           | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| test_table |          0 | display_name |            1 | display_name | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+------------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.00 sec)
/* 2. CREATE INDEX  */
CREATE INDEX id_index ON test_table(id);
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0
/* 3. ALTER TABLE ADD INDEX */
ALTER TABLE test_table ADD INDEX test_table(age)
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0
/* 4. インデックス確認*/
mysql> SHOW INDEX FROM test_table;
+------------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table      | Non_unique | Key_name     | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+------------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| test_table |          0 | PRIMARY      |            1 | id           | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| test_table |          0 | display_name |            1 | display_name | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| test_table |          1 | id_index     |            1 | id           | A         |           5 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| test_table |          1 | test_table   |            1 | age          | A         |           1 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+------------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
4 rows in set (0.01 sec)

「3. ALTER TABLE ADD INDEX」でインデックス名をtest_tableにしていたことに後で気が付いた
Column_name = 'age'のkey_nameが'test_table'になってしまったが学習用なので良しとする

インデックス削除（DROP INDEX）

どちらでも良い

DROP INDEX [index] ON [table]
ALTER TABLE [table] DROP INDEX [index]

table: テーブル名
index: 削除するインデックス名(Key_name)

MySQL

/* 1. インデックス確認*/
mysql> SHOW INDEX FROM test_table;
+------------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table      | Non_unique | Key_name     | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+------------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| test_table |          0 | PRIMARY      |            1 | id           | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| test_table |          0 | display_name |            1 | display_name | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| test_table |          1 | id_index     |            1 | id           | A         |           5 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| test_table |          1 | test_table   |            1 | age          | A         |           1 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+------------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
4 rows in set (0.01 sec)
/* 2. DROP INDEX  */
DROP INDEX id_index ON test_table;
>Query OK, 0 rows affected (0.03 sec)
>Records: 0  Duplicates: 0  Warnings: 0
/* 3. ALTER TABLE DROP INDEX */
ALTER TABLE test_table DROP INDEX test_table;
>Query OK, 0 rows affected (0.04 sec)
>Records: 0  Duplicates: 0  Warnings: 0
/* 4. インデックス確認*/
mysql> SHOW INDEX FROM test_table;
+------------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table      | Non_unique | Key_name     | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+------------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| test_table |          0 | PRIMARY      |            1 | id           | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| test_table |          0 | display_name |            1 | display_name | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+------------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.00 sec)

ドメイン定義（CREATE DOMAIN）

MySQLではサポートされていなかった…けど書いておく

MySQL

-- ドメインの定義
CREATE DOMAIN email_domain AS VARCHAR(255) CHECK (VALUE LIKE '%@%');
CREATE DOMAIN phone_number AS VARCHAR(13) CHECK (VALUE LIKE '___-____-____');
-- テーブルの作成とドメインの使用
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email email_domain,
    phone phone_number
);

考え方や使い方はロールに近い気がする
これは便利な機能だと思う

トリガーの設定（CREATE TRIGGER）

条件付きで予め定義したコマンドを自動実行するような仕組み
ストアドプロシージャみたいなもの

メールのフィルタ設定に考え方は近いと思う
「Aの条件に合致するメールを受信したら→Bフォルダーに振り分ける」みたいな

-- 使用例: レビューシステム
-- 想定するテーブルとして、products テーブルと reviews テーブルがあるとする
-- products テーブルに新しい商品が追加されるたびに、その商品に関する初期のレビューを reviews テーブルに自動的に追加するトリガーを作成

-- トリガーの作成
CREATE TRIGGER insert_product_review
AFTER INSERT ON products
FOR EACH ROW
BEGIN
    INSERT INTO reviews (product_id, rating, comment)
    VALUES (NEW.id, 0, 'No reviews yet');
END;

テーブル結合（JOIN）

内部結合（INNER JOIN）

INNER JOIN [table] ON [条件]

MySQL

-- コマンド例
SELECT *
FROM test_order_details AS det
INNER JOIN test_products AS pro ON det.product_id = pro.product_id
INNER JOIN test_buyers AS buy ON det.buyer_id = buy.buyer_id;

左外部結合（LEFT OUTER JOIN）

左（FROMで指定したテーブル）を基準にテーブル結合

MySQL

-- コマンド例
SELECT *
FROM test_buyers AS buy
LEFT OUTER JOIN test_order_details AS det ON buy.buyer_id = det.buyer_id;

これはよく使う印象

右外部結合（RIGHT OUTER JOIN）

MySQL

-- コマンド例
SELECT *
FROM test_order_details AS det
RIGHT OUTER JOIN test_buyers AS buy ON det.buyer_id = buy.buyer_id;

完全外部結合（FULL OUTER JOIN）

MySQL

-- コマンド例
SELECT *
FROM test_order_details AS det
FULL OUTER JOIN test_products AS pro ON det.product_id = pro.product_id
FULL OUTER JOIN test_buyers AS buy ON det.buyer_id = buy.buyer_id;

「USING」を使った結合

[前提]
両方の表に同じ名前の列があり、その列を使って等結合を行う場合にのみ使用可能

MySQL

-- コマンド例
SELECT *
FROM test_order_details AS det
JOIN test_products AS pro USING(product_id)
JOIN test_buyers AS buy USING(buyer_id);

WHERE句を使った結合

MySQL

-- コマンド例
SELECT *
FROM test_order_details AS det, test_products AS pro, test_buyers AS buy
WHERE det.product_id = pro.product_id AND det.buyer_id = buy.buyer_id;

これはよく使う印象

CASE関数

他の言語でいうif文のようなものかな
１．条件AだったらAの出力
２．条件BだったらBの出力
３．それ以外だったらCの出力
といったようなことができる

CASE WHEN ... THEN ... END

MySQL

-- コマンド例
SELECT CASE WHEN (pro.unit_price * det.quantity) <= 5000 THEN 5000
            WHEN (pro.unit_price * det.quantity) > 5000 AND (pro.unit_price * det.quantity) <= 10000 THEN 10000
            WHEN (pro.unit_price * det.quantity) > 10000 AND (pro.unit_price * det.quantity) <= 50000 THEN 50000
            ELSE 100000
       END AS price_range
FROM test_order_details AS det
JOIN test_products AS pro ON det.product_id = pro.product_id
JOIN test_buyers AS buy ON det.buyer_id = buy.buyer_id;

この例だと「単価（unit_price）*数量(quantity)」の合計金額の値で条件分岐させている

WHEN (pro.unit_price * det.quantity) <= 5000 THEN 5000
合計金額が5000以下の場合は5000を出力
WHEN (pro.unit_price * det.quantity) > 5000 AND (pro.unit_price * det.quantity) <= 10000 THEN 10000
5000 < 合計金額 <= 10000 の場合は10000を出力
WHEN (pro.unit_price * det.quantity) > 10000 AND (pro.unit_price * det.quantity) <= 50000 THEN 50000
10000 < 合計金額 <= 50000 の場合は50000を出力
ELSE 100000（=上記以外）
1~3のいづれの条件も満たさない場合は100000を出力

このような意味合い

COALESCE関数

列の値がNULLではないときは指定した値を返す

COALESCE([column], [任意の値])

MySQL

-- 通常SELECT
mysql> select * from test_table;
+----+---------+----------------+------+----------+---------------+
| id | name    | display_name   | age  | location | phone_number  |
+----+---------+----------------+------+----------+---------------+
|  1 | NO_NAME | NULL           |    0 | NULL     | 000-0000-0000 |
|  2 | NO_NAME | NULL           |    0 | NULL     | 000-0000-0000 |
|  3 | NO_NAME | display_name_3 |    0 | NULL     | 000-0000-0000 |
|  4 | NO_NAME | NULL           |    0 | NULL     | 000-0000-0000 |
|  5 | NO_NAME | NULL           |    0 | NULL     | 000-0000-0000 |
+----+---------+----------------+------+----------+---------------+
-- COALESCEを使用
mysql> SELECT id, name, COALESCE(display_name, 'None') as display_name, age, location, phone_number FROM test_table;
+----+---------+----------------+------+----------+---------------+
| id | name    | display_name   | age  | location | phone_number  |
+----+---------+----------------+------+----------+---------------+
|  1 | NO_NAME | None           |    0 | NULL     | 000-0000-0000 |
|  2 | NO_NAME | None           |    0 | NULL     | 000-0000-0000 |
|  3 | NO_NAME | display_name_3 |    0 | NULL     | 000-0000-0000 |
|  4 | NO_NAME | None           |    0 | NULL     | 000-0000-0000 |
|  5 | NO_NAME | None           |    0 | NULL     | 000-0000-0000 |
+----+---------+----------------+------+----------+---------------+

CONCAT関数

値を結合する

-- 文字列を指定
mysql> SELECT CONCAT('AB', 'CD', 'EF');
+--------------------------+
| CONCAT('AB', 'CD', 'EF') |
+--------------------------+
| ABCDEF                   |
+--------------------------+
-- カラム指定
mysql> SELECT id, CONCAT(name, ' : ', display_name) FROM test_table;
+----+-----------------------------------+
| id | CONCAT(name, ' : ', display_name) |
+----+-----------------------------------+
|  1 | NULL                              |
|  2 | NULL                              |
|  3 | NO_NAME : display_name_3          |
|  4 | NULL                              |
|  5 | NULL                              |
+----+-----------------------------------+

※NULL含むカラムがあるとNULLが返る

ウインドウ関数

これ面白い
非常に便利な機能だと思う
機会があれば積極的に使っていきたい

SQL順序を扱うウインドウ関数専用の関数

ROW_NUMBER(): 各行に順に一意となる行番号を付与
RANK(): ランキング（同率で番号を飛ばした値）を付与
DENSE_RANK(): ランキング（同率で番号を飛ばさない値）を付与
LAG(): n行前の行の値を取得（n省略時は1）
LEAD(): n行後の行の値を取得（n省略時は1）

MySQL

-- 実行例
mysql> SELECT det.order_id, det.product_id, ROUND(pro.unit_price) as unit_price, det.quantity,
    ->        ROW_NUMBER() OVER(ORDER BY ROUND(pro.unit_price)) as '*row_num*',
    ->        RANK() OVER(ORDER BY ROUND(pro.unit_price)) as '*rank*',
    ->        DENSE_RANK() OVER(ORDER BY ROUND(pro.unit_price)) as '*dense_rank*',
    ->        LAG(pro.unit_price) OVER(ORDER BY ROUND(pro.unit_price)) as '*previous_price*',
    ->        LEAD(pro.unit_price) OVER(ORDER BY ROUND(pro.unit_price)) as '*next_price*'
    -> FROM test_order_details AS det, test_products AS pro, test_buyers AS buy
    -> WHERE det.product_id = pro.product_id AND det.buyer_id = buy.buyer_id;
+------------+------------+------------+----------+-----------+--------+--------------+------------------+--------------+
| order_id   | product_id | unit_price | quantity | *row_num* | *rank* | *dense_rank* | *previous_price* | *next_price* |
+------------+------------+------------+----------+-----------+--------+--------------+------------------+--------------+
| BBB01YZ005 | Z20ABC0009 |        423 |       17 |         1 |      1 |            1 |             NULL |      1274.00 |
| BBB01YZ007 | Z20ABC0002 |       1274 |        1 |         2 |      2 |            2 |           423.00 |      1346.00 |
| BBB01YZ001 | Z20ABC0003 |       1346 |        3 |         3 |      3 |            3 |          1274.00 |      1346.00 |
| BBB01YZ009 | Z20ABC0003 |       1346 |        2 |         4 |      3 |            3 |          1346.00 |      1346.00 |
| BBB01YZ003 | Z20ABC0003 |       1346 |        3 |         5 |      3 |            3 |          1346.00 |      1347.00 |
| BBB01YZ002 | Z20ABC0005 |       1347 |        5 |         6 |      6 |            4 |          1346.00 |      1347.00 |
| BBB01YZ006 | Z20ABC0005 |       1347 |       24 |         7 |      6 |            4 |          1347.00 |      1853.00 |
| BBB01YZ002 | Z20ABC0016 |       1853 |        8 |         8 |      8 |            5 |          1347.00 |      2002.00 |
| BBB01YZ003 | Z20ABC0022 |       2002 |       12 |         9 |      9 |            6 |          1853.00 |      2204.00 |
| BBB01YZ001 | Z20ABC0006 |       2204 |        2 |        10 |     10 |            7 |          2002.00 |      2290.00 |
| BBB01YZ004 | Z20ABC0030 |       2290 |        8 |        11 |     11 |            8 |          2204.00 |      2395.00 |
| BBB01YZ010 | Z20ABC0019 |       2395 |        7 |        12 |     12 |            9 |          2290.00 |      2395.00 |
| BBB01YZ003 | Z20ABC0019 |       2395 |        5 |        13 |     12 |            9 |          2395.00 |      5093.00 |
| BBB01YZ008 | Z20ABC0020 |       5093 |        3 |        14 |     14 |           10 |          2395.00 |      6754.00 |
| BBB01YZ003 | Z20ABC0001 |       6754 |        6 |        15 |     15 |           11 |          5093.00 |      7715.00 |
| BBB01YZ001 | Z20ABC0008 |       7715 |        1 |        16 |     16 |           12 |          6754.00 |      8895.00 |
| BBB01YZ003 | Z20ABC0028 |       8895 |       11 |        17 |     17 |           13 |          7715.00 |         NULL |
+------------+------------+------------+----------+-----------+--------+--------------+------------------+--------------+

集約関数をウインドウ関数として使う

「区分ごとの平均を算出する」みたいなこともできる

MySQL

/* order_idごとの合計金額の平均を算出 */
mysql> SELECT det.order_id, det.product_id, ROUND(pro.unit_price) as unit_price, det.quantity,
    ->        CAST(AVG(det.quantity*pro.unit_price) OVER (PARTITION BY det.order_id) AS SIGNED) AS '*avg_total*'
    -> FROM test_order_details AS det, test_products AS pro, test_buyers AS buy
    -> WHERE det.product_id = pro.product_id AND det.buyer_id = buy.buyer_id;
+------------+------------+------------+----------+-------------+
| order_id   | product_id | unit_price | quantity | *avg_total* |
+------------+------------+------------+----------+-------------+
| BBB01YZ001 | Z20ABC0008 |       7715 |        1 |        5387 |
| BBB01YZ001 | Z20ABC0003 |       1346 |        3 |        5387 |
| BBB01YZ001 | Z20ABC0006 |       2204 |        2 |        5387 |
| BBB01YZ002 | Z20ABC0016 |       1853 |        8 |       10780 |
| BBB01YZ002 | Z20ABC0005 |       1347 |        5 |       10780 |
| BBB01YZ003 | Z20ABC0003 |       1346 |        3 |       35681 |
| BBB01YZ003 | Z20ABC0001 |       6754 |        6 |       35681 |
| BBB01YZ003 | Z20ABC0019 |       2395 |        5 |       35681 |
| BBB01YZ003 | Z20ABC0028 |       8895 |       11 |       35681 |
| BBB01YZ003 | Z20ABC0022 |       2002 |       12 |       35681 |
| BBB01YZ004 | Z20ABC0030 |       2290 |        8 |       18320 |
| BBB01YZ005 | Z20ABC0009 |        423 |       17 |        7191 |
+------------+------------+------------+----------+-------------+

フレーム句

ウインドウの中で細かい指定ができる

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING: グループごとの各行列が対象になる

MySQL

-- 実行例
mysql> SELECT det.order_id, det.product_id, ROUND(pro.unit_price) as unit_price, det.quantity,
    ->        CAST(AVG(det.quantity*pro.unit_price) OVER (
    ->          PARTITION BY det.order_id
    ->          ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ->          ) AS SIGNED) AS '*avg_total*'
    -> FROM test_order_details AS det, test_products AS pro, test_buyers AS buy
    -> WHERE det.product_id = pro.product_id AND det.buyer_id = buy.buyer_id;
+------------+------------+------------+----------+-------------+
| order_id   | product_id | unit_price | quantity | *avg_total* |
+------------+------------+------------+----------+-------------+
| BBB01YZ001 | Z20ABC0008 |       7715 |        1 |        5387 |
| BBB01YZ001 | Z20ABC0003 |       1346 |        3 |        5387 |
| BBB01YZ001 | Z20ABC0006 |       2204 |        2 |        5387 |
| BBB01YZ002 | Z20ABC0016 |       1853 |        8 |       10780 |
| BBB01YZ002 | Z20ABC0005 |       1347 |        5 |       10780 |
| BBB01YZ003 | Z20ABC0003 |       1346 |        3 |       35681 |
| BBB01YZ003 | Z20ABC0001 |       6754 |        6 |       35681 |
| BBB01YZ003 | Z20ABC0019 |       2395 |        5 |       35681 |
| BBB01YZ003 | Z20ABC0028 |       8895 |       11 |       35681 |
| BBB01YZ003 | Z20ABC0022 |       2002 |       12 |       35681 |
| BBB01YZ004 | Z20ABC0030 |       2290 |        8 |       18320 |
| BBB01YZ005 | Z20ABC0009 |        423 |       17 |        7191 |
+------------+------------+------------+----------+-------------+

-- 現在の行と１つ上と下の行を対象とするパターン
mysql> SELECT det.order_id, det.product_id, ROUND(pro.unit_price) as unit_price, det.quantity,
    ->        CAST(AVG(det.quantity*pro.unit_price) OVER (
    ->          PARTITION BY det.order_id
    ->          ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
    ->          ) AS SIGNED) AS '*avg_total*'
    -> FROM test_order_details AS det, test_products AS pro, test_buyers AS buy
    -> WHERE det.product_id = pro.product_id AND det.buyer_id = buy.buyer_id;
+------------+------------+------------+----------+-------------+
| order_id   | product_id | unit_price | quantity | *avg_total* |
+------------+------------+------------+----------+-------------+
| BBB01YZ001 | Z20ABC0008 |       7715 |        1 |        5877 | -- (7715 + 1346*3) / 2 = 5876.5
| BBB01YZ001 | Z20ABC0003 |       1346 |        3 |        5387 | -- (7715 + 1346*3 + 2204*2) / 3 = 5387
| BBB01YZ001 | Z20ABC0006 |       2204 |        2 |        4223 | -- (1346*3 + 2204*2) / 2 = 4223
| BBB01YZ002 | Z20ABC0016 |       1853 |        8 |       10780 |
| BBB01YZ002 | Z20ABC0005 |       1347 |        5 |       10780 |
| BBB01YZ003 | Z20ABC0003 |       1346 |        3 |       22281 |
| BBB01YZ003 | Z20ABC0001 |       6754 |        6 |       18846 |
| BBB01YZ003 | Z20ABC0019 |       2395 |        5 |       50115 |
| BBB01YZ003 | Z20ABC0028 |       8895 |       11 |       44615 |
| BBB01YZ003 | Z20ABC0022 |       2002 |       12 |       60935 |
| BBB01YZ004 | Z20ABC0030 |       2290 |        8 |       18320 |
| BBB01YZ005 | Z20ABC0009 |        423 |       17 |        7191 |
| BBB01YZ006 | Z20ABC0005 |       1347 |       24 |       32328 |
| BBB01YZ007 | Z20ABC0002 |       1274 |        1 |        1274 |
| BBB01YZ008 | Z20ABC0020 |       5093 |        3 |       15279 |
| BBB01YZ009 | Z20ABC0003 |       1346 |        2 |        2692 |
| BBB01YZ010 | Z20ABC0019 |       2395 |        7 |       16765 |
+------------+------------+------------+----------+-------------+
-- これ面白いけど使い時が想像つかない

WITH句

一時テーブルを作成し、それを後続のクエリ内で利用できるようにするための構文
WITH句を使えばクエリを複数のステップに分割し、読みやすくメンテナンスしやすい形で書ける

「Common Table Expressions（CTE）」とも呼ばれるらしい

一時テーブルの作成
WITH 句を使用して、一時的な結果セット（一時テーブル）を定義
この一時テーブルは、後続のクエリ内で参照できる
読みやすさと再利用性:
複雑なクエリを論理的に分割し、各ステップをわかりやすく記述できる
複数のクエリで再利用できる
再帰的クエリ
WITH 句を使用して、再帰的なクエリを実行できる。

MySQL

-- 構文
WITH cte_name (column1, column2, ...) AS (
    SELECT ...
)
SELECT ...
FROM cte_name
WHERE ...

-- cte_name は一時テーブル名
-- (column1, column2, ...) は、一時テーブルの列名を指定。省略可。

MySQL

-- 実行例
-- quantityが10以下のレコードを保持する一時テーブルtmp_order_detailsを作成
-- 一時テーブルとbuyerテーブル,productテーブルを結合
mysql> WITH tmp_order_details AS (
    ->     SELECT *
    ->     FROM test_order_details
    ->     WHERE quantity <= 10
    -> )
    -> SELECT *
    -> FROM tmp_order_details AS det, test_products AS pro, test_buyers AS buy
    -> WHERE det.product_id = pro.product_id AND det.buyer_id = buy.buyer_id;

日付／時刻関数

-- CURRENT_DATE
mysql> SELECT CURRENT_DATE;
+--------------+
| CURRENT_DATE |
+--------------+
| 2023-09-01   |
+--------------+
-- CURRENT_TIMESTAMP
mysql> SELECT CURRENT_TIMESTAMP;
+---------------------+
| CURRENT_TIMESTAMP   |
+---------------------+
| 2023-09-01 15:55:00 |
+---------------------+

データベースエンジンを確認する

どちらの方法でも確認できる

SHOW TABLE STATUS LIKE '[table]'
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '[database]'

MySQL

-- 実行例
mysql>  SELECT TABLE_NAME, ENGINE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'test_database';
+--------------+--------+
| TABLE_NAME   | ENGINE |
+--------------+--------+
| persons      | InnoDB |
| view_persons | NULL   |
+--------------+--------+
2 rows in set (0.00 sec)

データディクショナリを確認する

データベースのメタデータやスキーマ情報など

SELECT * FROM information_schema.tables WHERE table_schema = '[database]'

MySQL クライアントコマンド

mysql は、ユーザーが発行する各 SQL ステートメントを、実行のためサーバーに送信します。 mysql 自体が解釈するコマンドもあります。mysql 自体が解釈するコマンドもあります。これらのコマンドのリストを表示するには、mysql> プロンプトで help または \h と入力します。
引用>MySQL 8.0 リファレンスマニュアル

ニホンゴムズカシイ

mysqlコマンドはSQL文をサーバーに送信する役割がある
MySQLクライアント自体が解釈するコマンドが存在する
→mysqlコマンドはSQL文だけではなく、MySQLクライアント用の特別なコマンドも処理できる

こんな意味合いだろうか
クライアントコマンドはいろいろある

ヘルプを直訳したものを表にしてみた

コマンド	コマンド	説明
?	?	ヘルプを表示
clear	\c	現在の入力ステートメントをクリア
connect	\r	サーバーに再接続。オプションでデータベース名やホストを指定可
delimiter	\d	ステートメントの区切り文字を設定
edit	\e	$EDITORでコマンドを編集
ego	\G	コマンドをMySQLサーバーに送信し、結果を垂直に表示
exit	\q	MySQLクライアントを終了
go	\g	コマンドをMySQLサーバーに送信
help	\h	ヘルプを表示
nopager	\n	ページャーを無効にし、結果を標準出力に表示
notee	\t	outfileに書き込まないようにする
pager	\P	ページャーを設定し、クエリの結果をページャーを介して表示
print	\p	現在のコマンドを表示
prompt	\R	MySQLのプロンプトを変更
quit	\q	MySQLを終了
rehash	\#	補完ハッシュを再構築
source	\.	SQLスクリプトファイルを実行。ファイル名を引数に指定。
status	\s	サーバーからステータス情報を取得
system	!	システムシェルコマンドを実行
tee	\T	outfileを設定し、すべてを指定されたoutfileに追加
use	\u	別のデータベースを使用。データベース名を引数に指定。
charset	\C	別の文字セットに切り替え。マルチバイト文字セットを処理する場合に必要な場合がある。
warnings	\W	各ステートメントの後に警告を表示する
nowarning	\w	各ステートメントの後に警告を表示しない
resetconnection	\x	セッションコンテキストをクリア

例えば、「\G」コマンドを使うとこうなる
結果を縦方向に表示する
カラム数が多いテーブルのレコードを表示させる場合ときなど使うと便利

MySQL

-- 実行例
mysql> SELECT user,host FROM mysql.user WHERE user = 'user_test'\G
*************************** 1. row ***************************
user: user_test
host: localhost
1 row in set (0.00 sec)

他のクライアントコマンドは使ったことがないものがほとんど
そのうち使ってみようと思う

おわりに

データベース関連の学習をしたついでSQLコマンドや実行例をまとめてみたけれど、思ったより量が多かった。記事にまとめたおかげで内容の整理もできたので良かった。

SQLコマンドと実行例をまとめた

はじめに

コマンド集

ユーザ追加（CREATE USER）

ユーザ削除（DROP USER）

権限設定

ユーザに権限を付与（GRANT）

ユーザ権限を削除（REVOKE）

ロール

ロール作成（CREATE ROLE）

ロール削除（DROP ROLE）

データベース作成（CREATE DATABASE）

データベース削除（DROP DATABASE）

接続ポート番号の確認

接続中データベースの確認

現在接続しているユーザを確認

現在の接続ホスト名を確認

システム変数@@~を使って確認できるもの

テーブル作成（CREATE TABLE）

テーブル定義の確認

カラム追加

テーブル削除（DROP TABLE）

ビュー作成（CREATE VIEW）

インデックス

インデックス付与（CREATE INDEX）

インデックス削除（DROP INDEX）

ドメイン定義（CREATE DOMAIN）

トリガーの設定（CREATE TRIGGER）

テーブル結合（JOIN）

内部結合（INNER JOIN）

左外部結合（LEFT OUTER JOIN）

右外部結合（RIGHT OUTER JOIN）

完全外部結合（FULL OUTER JOIN）

「USING」を使った結合

WHERE句を使った結合

CASE関数

COALESCE関数

CONCAT関数

ウインドウ関数

SQL順序を扱うウインドウ関数専用の関数

集約関数をウインドウ関数として使う

フレーム句

WITH句

日付／時刻関数

データベースエンジンを確認する

データディクショナリを確認する

MySQL クライアントコマンド

おわりに

システム変数`@@~`を使って確認できるもの