Edit

はじめに

この記事の対象

  • SQL触ったことがない
  • SQLを実行する環境を構築するのが億劫
  • SQLコマンドの備忘録が欲しい

この記事の読み方

  • CREATE DATABASE `db`;←このようにハイライトされたコマンドを上から順に実行するだけで、SQLを実行できる
  • Linuxコマンドは先頭に$をつけて表示している
  • SQLコマンドには先頭に何もつけないで表示している

コマンド/データ型と演算子・関数の一覧

DBの操作

コマンド 操作
CREATE DATABASE `db`; dbという名前のデータベースを作成する
DROP DATABASE `db`; dbという名前のデータベースを削除する
SHOW DATABASES; データベースの一覧を表示する
USE `db`; dbという名前のデータベースを操作対象に設定する

ユーザの操作

コマンド 操作
CREATE USER hoge@localhost IDENTIFIED BY ‘foobar’; 接続元のIPアドレス/ドメインがlocalhostのhogeという名前のユーザをfoobarというパスワードを設定して追加する
SELECT user FROM mysql.user; ユーザの一覧を表示する
DROP USER hoge@localhost; hogeという名前のユーザを削除する
GRANT ALL ON db.* TO hoge@localhost; hogeという名前のユーザに、dbという名前のデータベースにアクセスできるように権限を与える

テーブルの操作

コマンド 操作
CREATE TABLE table1 (id INT, name CHAR(15), time DATETIME); カラムの名前とデータ型を定義してtable1という名前のテーブルを作成する
CREATE TABLE table1 (id INT NOT NULL); idというカラムにNULLを格納できないように制約をかけてテーブルを作成する
CREATE TABLE table1 (id INT NOT NULL PRIMARY KEY); idが重複しないように設定してテーブルを作成する
CREATE TABLE table1 (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT); idが1から昇順の連番を持つように設定してテーブルを作成する
CREATE TABLE table1 (name CHAR(15) NOT NULL DEFAULT ‘abc’); nameというカラムにNULLが格納されたときに、代わりにabcをでデフォルト値として格納するように設定してテーブルを作成する
CREATE TABLE table1 (name CHAR(15) NOT NULL DEFAULT ‘abc’, CHECK (CHAR_LENGTH(name) >= 3)); nameというカラムに3文字以上の文字列しか格納できないように設定してテーブルを作成する
CREATE TABLE table1 (created DATETIME NOT NULL DEFAULT NOW()); レコード作成時の日時をcreatedにデフォルト値として格納するように設定してテーブルを作成する
CREATE TABLE table1 (updated DATETIME NOT NULL DEFAULT NOW() ON UPDATE NOW()); レコードのデータを更新するたびに、更新時の日時をupdatedに格納するように設定してテーブルを作成する
SHOW TABLES; データベース内のテーブルの一覧を表示する
DESC table1; table1という名前のテーブルの構成を表示する
DROP TABLE table1; table1という名前のテーブルを削除する
TRUNCATE TABLE table1; table1という名前のテーブルの中のデータを削除する
ALTER TABLE t1 RENAME TO table1; テーブルを名前をt1からtable1へ改名する

レコードの操作

コマンド 操作
SELECT * FROM table1; table1という名前のテーブルから全レコードを取得する
SELECT CONCAT(name, ‘さん’) AS name_with_san FROM table1; name_with_sanをカラムの別名として扱い、テーブルからnameの値と「さん」を連結したレコードを取得する
SELECT id, name FROM table1 ORDER BY name; テーブルからid, nameの値をnameの文字列をアルファベット順に並び替えた順に取得する
SELECT id, name FROM table1 ORDER BY name DESC; テーブルからレコードをアルファベットの逆順で取得する
SELECT id, name FROM table1 ORDER BY name LIMIT 3; テーブルからレコードを上から3個だけ取得する
SELECT id, name FROM table1 ORDER BY name LIMIT 2,3; テーブルから3(2+1)番目から3個だけレコードを取得する
INSERT INTO table1 (id, name, time) VALUES (0, ‘abc’, NOW()); table1という名前のテーブルに、idを0、nameをabc、timeをコマンドの実行日時として値を格納したレコードを追加する
DELETE FROM table1 WHERE id = 0; table1という名前のテーブルからidの値が0のレコードを削除する
UPDATE table1 SET id = id + 1; table1という名前のテーブルの中のidの値を1だけ足して更新する

WHEREをつけることができるコマンド一覧

コマンド
SELECT
UPDATE
DELETE

カラムの操作

コマンド 操作
ALTER TABLE table1 ADD country CHAR(15); table1という名前のテーブルにcountryというカラムを追加する
ALTER TABLE table1 ADD country CHAR(15) FIRST; テーブルにcountryというカラムを先頭のカラムとして追加する
ALTER TABLE table1 ADD country CHAR(15) AFTER name; テーブルにcountryというカラムをnameの後に追加する
ALTER TABLE table1 RENAME COLUMN time TO created; テーブルのtimeというカラムをcreatedに改名する
ALTER TABLE table1 DROP country; テーブルからcountryというカラムを削除する
SHOW COLUMNS FROM table1; テーブルのカラムの一覧を表示する

データ型

データ型 説明
TINYINT 整数: -128 ~ +127 または 0 ~ 255
INT 整数: -2,147,483,648 ~ +2,147,483,647 または 0 ~ +4,294,967,295
BIGINT 整数: -922,337,236,854,775,808 ~ +922,337,236,854,775,807 または 0 ~ +1,844,674,473,709,551,615
FLOAT 実数: –1.79×10^308 ~ -2.23×10^(-308) または 0 または 2.23×10^(-308) ~ 1.79×10^308
CHAR 文字列: 0 ~ 255文字
VARCHAR 文字列: 0 ~ 65535文字
BOOL 真偽値: TRUE または FALSE
DATE 日付
TIME 時刻
DATETIME 日付と時刻

演算子・関数

演算子・関数 説明
+ 加算
- 減算
* 乗算
/ 除算
% 余算
FLOOR(num) numの端数を切り捨てる
CEIL(num) numの端数を切り上げる
ROUND(num) numの四捨五入する(小数点第n桁で丸める場合は、第2引数にnを入れる)
SUBSTRING(str, n) strのn字以降の文字列を切り出す
SUBSTRING(str, n, m) strのn字以降の文字列をm字だけ切り出す
CONCAT(str, str2) 文字列を結合させる
LENGTH(str) strの文字列の長さを取得する(日本語を入れると挙動が変になる)
CHAR_LENGTH(str) strの文字列の長さを取得する(日本語に対応可能)
YEAR(date_time) date_timeから年を取得する
MONTH(date_time) date_timeから月を取得する
DAY(date_time) date_timeから日を取得する
DATE_FORMAT(date_time, “%Y %M %D %W”) date_timeからY(年) M(月) D(日) W(曜日)というフォーマットの日時を取得する
DATE_ADD(date_time, INTERVAL n DAY) date_timeからn日後の日時を取得する
DATEDIFF(date_time, date_time2) date_time2とdate_timeの差を取得する

コマンドを実行して学習する

SQLの環境構築

  • このレポジトリをcloneする
    • $ git clone git@github.com:e205723/docker_SQL.git
  • 作業ディレクトリをdocker_SQLに変更する
    • $ cd docker_SQL
  • MySQLが入ったコンテナを立てて、コンテナにログインする
    • $ docker-compose up -d
    • $ docker-compose exec db bash

SQLへのログイン+データベースの操作

  • rootユーザでMySQLにログインする
    • $ mysql -u root -p -h localhost
      • パスワードは「password」にしている
  • dbという名前のデータベースを作成する
    • CREATE DATABASE `db`;
  • データベースの一覧を表示して、dbというデータベースが作成されていることを確認する
    • SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| db                 |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)
  • dbという名前のデータベースを削除する
    • DROP DATABASE `db`;
  • データベースの一覧を表示して、dbというデータベースが削除されていることを確認する
    • SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)
  • 再度、dbという名前のデータベースを作成する
    • CREATE DATABASE `db`;

ユーザ操作

  • 接続のIPアドレス/ドメインがlocalhostのhogeという名前のユーザをfoobarというパスワードを設定して追加する
    • CREATE USER hoge@localhost IDENTIFIED BY 'foobar';
  • ユーザの一覧を表示する
    • SELECT user FROM mysql.user;
+------------------+
| user             |
+------------------+
| root             |
| hoge             |
| mysql.infoschema |
| mysql.session    |
| mysql.sys        |
| root             |
+------------------+
6 rows in set (0.00 sec)
  • hogeという名前のユーザを削除する
    • DROP USER hoge@localhost;
  • ユーザの一覧を表示して、hogeという名前のユーザが削除されていることを確認する
    • SELECT user FROM mysql.user;
+------------------+
| user             |
+------------------+
| root             |
| mysql.infoschema |
| mysql.session    |
| mysql.sys        |
| root             |
+------------------+
5 rows in set (0.00 sec)
  • 再度、hogeという名前のユーザをfoobarというパスワードを設定して追加する
    • CREATE USER hoge@localhost IDENTIFIED BY 'foobar';
  • hogeという名前のユーザに、dbという名前のデータベースにアクセスできるように権限を与える
    • GRANT ALL ON db.* TO hoge@localhost;
  • exitする
    • ctrl + dを入力

SQLへのログイン+テーブルの操作

  • hogeというユーザでSQLにログインする
    • $ mysql -u hoge -p -h localhost
      • パスワードはfoobar
  • dbという名前のデータベースを操作対象に設定する
    • USE `db`;
  • カラムの名前とデータ型を定義してtable1という名前のテーブルを作成する
    • CREATE TABLE table1 (id INT, name CHAR(15), time DATETIME);
      • idはINT型
      • nameは15バイト以内のCHAR型
      • timeはDATETIME型
  • データベース内のテーブルの一覧を表示する
    • SHOW TABLES;
+--------------+
| Tables_in_db |
+--------------+
| table1       |
+--------------+
1 row in set (0.01 sec)
  • table1という名前のテーブルの構成を表示する
    • DESC table1;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int      | YES  |     | NULL    |       |
| name  | char(15) | YES  |     | NULL    |       |
| time  | datetime | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)
  • table1という名前のテーブルを削除する
    • DROP TABLE table1;
  • データベース内のテーブルの一覧を表示して、table1という名前のテーブルが削除されていることを確認する
    • SHOW TABLES;
Empty set (0.01 sec)
  • カラムの名前とデータ型を定義してt1という名前のテーブルを作成する
    • CREATE TABLE t1 (id INT, name CHAR(15), time DATETIME);
  • テーブルを名前をt1からtable1へ改名する
    • ALTER TABLE t1 RENAME TO table1;
  • データベース内のテーブルの一覧を表示して、名前の変更が反映されていることを調べる
    • SHOW TABLES;
+--------------+
| Tables_in_db |
+--------------+
| table1       |
+--------------+
1 row in set (0.00 sec)

レコードの操作

  • table1という名前のテーブルに、idを0、nameをabc、timeをコマンドの実行日時として値を格納したレコードを追加する
    • INSERT INTO table1 (id, name, time) VALUES (0, 'abc', NOW());
  • table1という名前のテーブルから全レコードを取得する
    • SELECT * FROM table1;
+------+------+---------------------+
| id   | name | time                |
+------+------+---------------------+
|    0 | abc  | 2021-10-28 01:35:43 |
+------+------+---------------------+
1 row in set (0.00 sec)
  • table1という名前のテーブルからidの値が0のレコードを削除する
    • DELETE FROM table1 WHERE id = 0;
      • WHEREをつけることができるコマンドはDELETEの他に、SELECTやUPDATEがある
  • table1という名前のテーブルから全レコードを取得して、レコードが削除されていることを確認する
    • SELECT * FROM table1;
Empty set (0.00 sec)
  • 2つのレコードを追加する
    • 以下のクエリを実行する
      • INSERT INTO table1 (id, name, time) VALUES (0, 'abc', NOW());
      • INSERT INTO table1 (id, name, time) VALUES (1, 'abc2', NOW());
  • table1という名前のテーブルから全レコードを取得して、2つのレコードが追加されていることを確認する
    • SELECT * FROM table1;
+------+------+---------------------+
| id   | name | time                |
+------+------+---------------------+
|    0 | abc  | 2021-10-28 01:51:31 |
|    1 | abc2 | 2021-10-28 01:51:41 |
+------+------+---------------------+
2 rows in set (0.00 sec)
  • table1という名前のテーブルの中のidの値を1だけ足して更新する
    • UPDATE table1 SET id = id + 1;
  • table1という名前のテーブルから全レコードを取得して、idの値が更新されていることを確認する
    • SELECT * FROM table1;
+------+------+---------------------+
| id   | name | time                |
+------+------+---------------------+
|    1 | abc  | 2021-10-28 01:51:31 |
|    2 | abc2 | 2021-10-28 01:51:41 |
+------+------+---------------------+
2 rows in set (0.00 sec)

カラムの操作

  • テーブルにcountryというカラムを先頭のカラムとして追加する
    • ALTER TABLE table1 ADD country CHAR(15) FIRST;
  • テーブルのカラムの一覧を表示する
    • SHOW COLUMNS FROM table1;
+---------+----------+------+-----+---------+-------+
| Field   | Type     | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| country | char(15) | YES  |     | NULL    |       |
| id      | int      | YES  |     | NULL    |       |
| name    | char(15) | YES  |     | NULL    |       |
| time    | datetime | YES  |     | NULL    |       |
+---------+----------+------+-----+---------+-------+
4 rows in set (0.00 sec)
  • table1という名前のテーブルから全レコードを取得する
    • SELECT * FROM table1;
+---------+------+------+---------------------+
| country | id   | name | time                |
+---------+------+------+---------------------+
| NULL    |    1 | abc  | 2021-10-28 01:51:31 |
| NULL    |    2 | abc2 | 2021-10-28 01:51:41 |
+---------+------+------+---------------------+
2 rows in set (0.01 sec)
  • テーブルからcountryというカラムを削除する
    • ALTER TABLE table1 DROP country;
  • テーブルのカラムの一覧を表示して、countryというカラムが削除されていること確認する
    • SHOW COLUMNS FROM table1;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int      | YES  |     | NULL    |       |
| name  | char(15) | YES  |     | NULL    |       |
| time  | datetime | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)
  • テーブルにcountryというカラムをnameの後に追加する
    • ALTER TABLE table1 ADD country CHAR(15) AFTER name;
  • テーブルのカラムの一覧を表示して、countryがnameの後に追加されたことを確認する
    • SHOW COLUMNS FROM table1;
+---------+----------+------+-----+---------+-------+
| Field   | Type     | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| id      | int      | YES  |     | NULL    |       |
| name    | char(15) | YES  |     | NULL    |       |
| country | char(15) | YES  |     | NULL    |       |
| time    | datetime | YES  |     | NULL    |       |
+---------+----------+------+-----+---------+-------+
4 rows in set (0.00 sec)
  • テーブルからcountryというカラムを削除する
    • ALTER TABLE table1 DROP country;
  • テーブルのtimeというカラムをcreatedに改名する
    • ALTER TABLE table1 RENAME COLUMN time TO created;
  • テーブルのカラムの一覧を表示して、カラムの名前の変更が反映されたことを確認する
    • SHOW COLUMNS FROM table1;
+---------+----------+------+-----+---------+-------+
| Field   | Type     | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| id      | int      | YES  |     | NULL    |       |
| name    | char(15) | YES  |     | NULL    |       |
| created | datetime | YES  |     | NULL    |       |
+---------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

テーブルの応用的な操作

  • table1という名前のテーブルに、idを3として値を格納したレコードを追加する
    • INSERT INTO table1 (id) VALUES (3);
  • table1という名前のテーブルから全レコードを取得する
    • SELECT * FROM table1;
      • idが3のレコードのnameとcreatedの値がNULLになっている、データの中身がNULLではないことが推奨とされている
      • 信頼性の高いデータベースにするためには、NULLが値として入らない様に設定する必要がある
+------+------+---------------------+
| id   | name | created             |
+------+------+---------------------+
|    1 | abc  | 2021-10-28 01:51:31 |
|    2 | abc2 | 2021-10-28 01:51:41 |
|    3 | NULL | NULL                |
+------+------+---------------------+
3 rows in set (0.00 sec)
  • table1という名前のテーブルを削除する
    • DROP TABLE table1;
  • id、name、createdにNULLを格納できないように制約をかけてテーブルを作成する
    • CREATE TABLE table1 (id INT NOT NULL, name CHAR(15) NOT NULL, created DATETIME NOT NULL);
  • ここで以下のコマンドを実行するとエラーが発生する
INSERT INTO table1 (id) VALUES (1);
  • エラーメッセージ
ERROR 1364 (HY000): Field 'name' doesn't have a default value
  • table1という名前のテーブルを削除する
    • DROP TABLE table1;
  • table1という名前のテーブルを作成する
    • CREATE TABLE table1 (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, name CHAR(15) NOT NULL DEFAULT 'abc', updated DATETIME NOT NULL DEFAULT NOW() ON UPDATE NOW(), CHECK (CHAR_LENGTH(name) >= 3));
      • PRIMARY KEY
        • idが重複しないように設定してテーブルを作成する
      • AUTO_INCREMENT
        • idが1から昇順の連番を持つように設定してテーブルを作成する
      • DEFAULT ‘abc’
        • nameというカラムにNULLが格納されたときに、代わりにabcをでデフォルト値として格納するように設定してテーブルを作成する
      • DEFAULT NOW()
        • レコード作成時の日時をupdatedにデフォルト値として格納するように設定してテーブルを作成する
      • DEFAULT NOW() ON UPDATE NOW()
        • レコードのデータを更新するたびに、更新時の日時をupdatedに格納するように設定してテーブルを作成する
      • CHECK (CHAR_LENGTH(name) >= 3)
        • nameというカラムに3文字以上の文字列しか格納できないように設定してテーブルを作成する
  • ここで以下のコマンドを実行するとエラーが発生する
INSERT INTO table1 (name) VALUES ('ab');
  • エラーメッセージ
ERROR 3819 (HY000): Check constraint 'table1_chk_1' is violated.
  • 2つのレコードを追加する
    • 以下のクエリを実行する
      • INSERT INTO table1 () VALUES ();
      • INSERT INTO table1 () VALUES ();
  • table1という名前のテーブルから全レコードを取得する
    • SELECT * FROM table1;
+----+------+---------------------+
| id | name | updated             |
+----+------+---------------------+
|  1 | abc  | 2021-10-28 03:14:32 |
|  2 | abc  | 2021-10-28 03:14:34 |
+----+------+---------------------+
2 rows in set (0.00 sec)
  • ここで以下のコマンドを実行するとエラーが発生する
INSERT INTO table1 (id) VALUES (2);
  • エラーメッセージ
ERROR 1062 (23000): Duplicate entry '2' for key 'table1.PRIMARY'

レコードの応用的な操作

  • table1という名前のテーブルからidの値が2のレコードを削除する
    • DELETE FROM table1 WHERE id = 2;
  • table1にレコードを追加する
    • INSERT INTO table1 () VALUES ();
  • table1という名前のテーブルから全レコードを取得する
    • SELECT * FROM table1;
      • idが2のレコードが削除されたかが、MySQLでは一度使われた連番は使われないので、レコードを新たに追加したときにidに3が格納される
+----+------+---------------------+
| id | name | updated             |
+----+------+---------------------+
|  1 | abc  | 2021-10-28 03:14:32 |
|  3 | abc  | 2021-10-28 03:26:07 |
+----+------+---------------------+
2 rows in set (0.00 sec)
  • table1という名前のテーブルの中のデータを削除する
    • TRUNCATE TABLE table1;
      • 連番の情報もリセットされる
  • table1という名前のテーブルから全レコードを取得して、テーブルの中のデータが全て削除されたことを確認する
    • SELECT * FROM table1;
Empty set (0.00 sec)
  • 6つのレコードを追加する
    • 以下のクエリを実行する
      • INSERT INTO table1 (name) VALUES ("fff");
      • INSERT INTO table1 (name) VALUES ("eee");
      • INSERT INTO table1 (name) VALUES ("ddd");
      • INSERT INTO table1 (name) VALUES ("ccc");
      • INSERT INTO table1 (name) VALUES ("bbb");
      • INSERT INTO table1 (name) VALUES ("aaa");
  • table1という名前のテーブルから全レコードを取得して、テーブルにレコードが追加されたことと連番がリセットされたことを確認する
    • SELECT * FROM table1;
+----+------+---------------------+
| id | name | updated             |
+----+------+---------------------+
|  1 | fff  | 2021-10-28 03:36:23 |
|  2 | eee  | 2021-10-28 03:36:23 |
|  3 | ddd  | 2021-10-28 03:36:23 |
|  4 | ccc  | 2021-10-28 03:36:23 |
|  5 | bbb  | 2021-10-28 03:36:23 |
|  6 | aaa  | 2021-10-28 03:36:23 |
+----+------+---------------------+
6 rows in set (0.00 sec)
  • name_with_sanをカラムの別名として扱い、テーブルからnameの値と「さん」を連結したレコードを取得する
    • SELECT CONCAT(name, 'さん') AS name_with_san FROM table1;
+---------------+
| name_with_san |
+---------------+
| fffさん       |
| eeeさん       |
| dddさん       |
| cccさん       |
| bbbさん       |
| aaaさん       |
+---------------+
6 rows in set (0.00 sec)
  • テーブルからid, nameの値をnameの文字列をアルファベット順に並び替えた順で、上から3個だけ取得する
    • SELECT id, name FROM table1 ORDER BY name LIMIT 3;
+----+------+
| id | name |
+----+------+
|  6 | aaa  |
|  5 | bbb  |
|  4 | ccc  |
+----+------+
3 rows in set (0.00 sec)
  • テーブルからid, nameの値をidの降順に並び替えた順で、3(2+1)番目から3個だけレコードを取得する
    • SELECT id, name FROM table1 ORDER BY id DESC LIMIT 2,3;
+----+------+
| id | name |
+----+------+
|  4 | ccc  |
|  3 | ddd  |
|  2 | eee  |
+----+------+
3 rows in set (0.00 sec)
comments powered by Disqus