Closed17
ちょっと覚えたいSQLコマンドをまとめるだけ
ピン留めされたアイテム
yuckey前提
- MySQLサーバーへ接続している状態であること(SQL文がいつでも使える状態であること)
読み方
- 基本的に上から流し読みで進められるようにまとめています
- 各SQL文はコマンドと実行例の2部構成でまとめています
- コマンドにはSQL文のフォーマットを記載しています。任意の名称を入力する必要があるものは[]で括っています。そのままコピペして使えないものもあるので注意
- 実行例には実際に入力したコマンドとその実行結果等を記述しています。必要に応じて展開しながら読み進めてみてください
目次
あとで書きます(忘れる)
yuckey存在するデータベース一覧を表示
SHOW DATABASES;
実行例
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| hoge-db |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.01 sec)
データベースを新規作成(create)
CREATE DATABASE [データベース名];
実行例
"testdb"という名前のデータベースを作成したい場合
mysql> create database testdb;
Query OK, 1 row affected (0.01 sec)
作成できたら"testdb"というデータベースが作成されたか確認する
結果に"testdb"があればOK
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| hoge-db |
| information_schema |
| mysql |
| performance_schema |
| sys |
| testdb |
+--------------------+
6 rows in set (0.00 sec)
使用するデータベースを選択
USE [データベース名];
実行例
"testdb"というデータベースを使用する場合
mysql> use testdb;
Database changed
今どのデータベースを使用しているか確認
SELECT DATABASE();
実行例
"testdb"を使用している場合、以下のような結果になる
mysql> select database();
+------------+
| database() |
+------------+
| testdb |
+------------+
1 row in set (0.01 sec)
ちなみに、何も使用していない状態だとNULLになる
mysql> select database();
+------------+
| database() |
+------------+
| NULL |
+------------+
1 row in set (0.01 sec)
テーブルを新規作成(create)
CREATE TABLE [テーブル名] ([カラム名(※)] [データ型]);
(※)カラム名 = 列名
実行例
テーブル名を"test_table"、カラムを"id"と"name"の2つにしてテーブルを作成する場合
カンマ(,)で区切ることで複数のカラムを作成することも可能
mysql> create table test_table (id integer, name varchar(255));
Query OK, 0 rows affected (0.03 sec)
【topic】データ型について
テーブルの列には、どういった種類のデータかを定義する必要があります。
例えば、人の年齢であれば数値型、名前であれば文字列型…のように型を指定しないといけません。
以下のような型がよく使われます。
| データ型 | 分類 | 説明 | 例 |
|---|---|---|---|
| INTEGER | 数値型 | 整数値 | 年齢、ID、数量 |
| FLOAT | 数値型 | 単精度浮動小数点数 | 身長、体重 |
| CHAR(n) | 文字列型 | 固定長文字列 (n:文字数) | 郵便番号、電話番号 |
| VARCHAR(n) | 文字列型 | 可変長文字列 (n:最大文字数) | 名前、住所 |
| TEXT | 文字列型 | 長い文字列 | 記事の内容、説明文 |
| DATE | 日付・時刻型 | 年月日 | 生年月日、契約日 |
| TIME | 日付・時刻型 | 時分秒 | 出勤時間、退勤時間 |
| DATETIME | 日付・時刻型 | 年月日時分秒 | イベント日時 |
| BOOLEAN | 論理型 | 真偽値 (true, false) | 有効/無効、性別 |
yuckeyテーブル一覧を表示
SHOW TABLES;
実行例
"test_table"というテーブルを作成している場合
mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| test_table |
+------------------+
1 row in set (0.01 sec)
テーブルの構造を確認
DESCRIBE [テーブル名];
実行例
"test_table"というテーブルの構造を確認する場合
"id"と"name"という列名や、各列のデータ型などが確認できる
mysql> describe test_table;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(255) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
【topic】テーブル構造について
テーブルのカラムには、以下のように"Field"や"Type"などの属性が付与されています。
これらの属性は、主にCREATE TABLE ~でテーブルを作成した時に必要に応じて付与することが可能です。
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(255) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
- Field カラム名(必須)
- Type: カラムのデータ型(必須)
- Null: Nullを許容するかどうか(YESならNullを許すが、NOならNullになるのを許さない)
- Key: そのカラムがテーブルのキー(※別途説明)として使用されるかどうか
- Default: カラムの初期値(値が何も入ってない時に挿入される値)
- Extra: カラムの追加情報(特に覚えなくていい)
yuckeyテーブルにデータを挿入(insert)
INSERT INTO [テーブル名] ([カラム名(※)]) VALUES ([値]);
実行例
カンマ(,)で複数のカラムと値を指定可能
mysql> insert into test_table (id, name) values (1, 'google');
Query OK, 1 row affected (0.02 sec)
また、挿入するデータは複数まとめて作成することも可能
mysql> insert into test_table (id, name) values (1, 'apple'), (2, 'twitter');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
テーブルからデータを取得(select)
SELECT [カラム名] FROM [テーブル名];
実行例
"test_table"というテーブルから"name"のデータを全て取得して表示したい場合
mysql> select name from test_table;
+---------+
| name |
+---------+
| google |
| apple |
| twitter |
+---------+
3 rows in set (0.00 sec)
カンマ(,)で複数のカラムを選択することも可能
mysql> select id,name from test_table;
+------+---------+
| id | name |
+------+---------+
| 1 | google |
| 1 | apple |
| 2 | twitter |
+------+---------+
3 rows in set (0.00 sec)
テーブルから全データを取得(select)
アスタリスク(*)がすべてのカラムを選択するという意味になる
SELECT * FROM [テーブル名];
実行例
"test_table"というテーブルに格納されたデータを全て表示したい場合
mysql> select * from test_table;
+------+---------+
| id | name |
+------+---------+
| 1 | google |
| 1 | apple |
| 2 | twitter |
+------+---------+
3 rows in set (0.00 sec)
条件を指定してテーブルからデータを取得(select)
条件を指定したい場合はWHERE句を使う
SELECT [カラム名] FROM [テーブル名] WHERE [条件文];
実行例
"id"が1のデータのみ抽出
mysql> select * from test_table where id=1;
+------+--------+
| id | name |
+------+--------+
| 1 | google |
| 1 | apple |
+------+--------+
2 rows in set (0.01 sec)
"name"が"apple"のデータのみ抽出
mysql> select * from test_table where name="apple";
+------+-------+
| id | name |
+------+-------+
| 1 | apple |
+------+-------+
1 row in set (0.01 sec)
【topic】条件文の例
比較演算子
| 演算子 | 説明 | 例 |
|---|---|---|
| = | 等しい | age = 30 (年齢が30歳のレコード) |
| != or <> | 等しくない | name != '山田' (名前が山田でないレコード) |
| > | より大きい | salary > 100000 (給料が10万円を超えるレコード) |
| < | より小さい | birth_date < '2000-01-01' (2000年1月1日より前に生まれたレコード) |
| >= | 以上 | height >= 170 (身長が170cm以上のレコード) |
| <= | 以下 | price <= 1000 (価格が1000円以下のレコード) |
論理演算子
| 演算子 | 説明 | 例 |
|---|---|---|
| AND | かつ | age = 30 AND gender = '男性' (年齢が30歳かつ性別が男性のレコード) |
| OR | または | department = '営業' OR department = '開発' (部署が営業または開発のレコード) |
| NOT | ではない | NOT is_deleted = 1 (削除されていないレコード) |
特殊な条件
| 条件 | 説明 | 例 |
|---|---|---|
| BETWEEN ... AND ... | 範囲指定 | price BETWEEN 100 AND 200 (価格が100円から200円の間のレコード) |
| IN (...) | リストから選択 | gender IN ('男性', '女性') (性別が男性または女性のレコード) |
| LIKE | パターンマッチング | name LIKE '山田%' (名前が山田で始まるレコード) |
| IS NULL | NULL値の判定 | address IS NULL (住所が未入力のレコード) |
| IS NOT NULL | NULL値ではない判定 | address IS NOT NULL (住所が入力されているレコード) |
yuckey並び替えてテーブルからデータを取得(select)
取得結果を並び替えたい場合はORDER BY句を使う
SELECT [カラム名] FROM [テーブル名] ORDER BY [カラム名] [ASCかDESC(※)];
(※)昇順にするならASC、降順にするならDESCを指定する
実行例
"id"の昇順で表示
mysql> select * from test_table order by id asc;
+------+---------+
| id | name |
+------+---------+
| 1 | google |
| 1 | apple |
| 2 | twitter |
+------+---------+
3 rows in set (0.00 sec)
"name"の降順で表示
mysql> select * from test_table order by name desc;
+------+---------+
| id | name |
+------+---------+
| 2 | twitter |
| 1 | google |
| 1 | apple |
+------+---------+
3 rows in set (0.00 sec)
テーブルのデータを更新する(update)
UPDATE [テーブル名] SET [カラム名] = [値] WHERE [条件文];
実行例
"name"が"twitter"なら"x"に更新したい場合
更新前のテーブルの中身を確認
mysql> select * from test_table;
+------+---------+
| id | name |
+------+---------+
| 1 | google |
| 1 | apple |
| 2 | twitter |
+------+---------+
3 rows in set (0.00 sec)
"twitter"を"x"に更新
mysql> update test_table set name = "x" where name = "twitter";
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
更新前のテーブルの中身を確認
mysql> select * from test_table;
+------+--------+
| id | name |
+------+--------+
| 1 | google |
| 1 | apple |
| 2 | x |
+------+--------+
3 rows in set (0.00 sec)
※注意点(WHERE句を使わないとどうなるか)
実は、WHEREで条件をフィルタリングしないと、全てのデータが対象になってしまいます。
例えば、以下のように書くと…
mysql> update test_table set name = "secret";
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3 Changed: 3 Warnings: 0
"name"の値が全て"secret"になってしまう
mysql> select * from test_table;
+------+--------+
| id | name |
+------+--------+
| 1 | secret |
| 1 | secret |
| 2 | secret |
+------+--------+
3 rows in set (0.00 sec)
なので、カラムの値をまとめて変更したい場合を除いて、WHERE句は一緒に記述した方がいいです。
yuckeyCASE式を用いた条件分岐(一致)
CASE WHEN ~を使用する
SELECT [カラム名]
CASE [カラム名]
WHEN [条件値1] THEN [結果1]
WHEN [条件値2] THEN [結果2]
ELSE [デフォルト結果]
END AS [結果出力先カラム名]
FROM [テーブル名];
実行例
以下のようなテーブルを対象に実施する
mysql> select * from devices;
+----+---------------+-------+
| id | name | value |
+----+---------------+-------+
| 1 | iPhone15 | 1000 |
| 2 | iPad | 800 |
| 3 | Google Pixel3 | 600 |
| 4 | Galaxy s25 | 900 |
| 5 | iPhone16 | 1100 |
+----+---------------+-------+
5 rows in set (0.00 sec)
生成SQL文(参考)
-- テーブルの作成
CREATE TABLE devices (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
value INTEGER NOT NULL
);
-- データの挿入
INSERT INTO devices (name, value) VALUES
('iPhone15', 1000),
('iPad', 800),
('Google Pixel3', 600),
('Galaxy s25', 900),
('iPhone16', 1100);
例えば、"status"というカラムを後ろに追加し、
- nameの値が"iPhone15"であれば"used"を
- "Macbook Air"であれば"used(sub)"を
- それ以外であれば"Unknown"
という値を入れたい場合は以下のようにする
SQL文
SELECT name, value,
CASE name
WHEN 'iPhone15' THEN 'used'
WHEN 'Macbook Air' THEN 'used(sub)'
ELSE 'Unknown'
END AS status
FROM
devices;
結果
+---------------+-------+---------+
| name | value | status |
+---------------+-------+---------+
| iPhone15 | 1000 | used |
| iPad | 800 | Unknown |
| Google Pixel3 | 600 | Unknown |
| Galaxy s25 | 900 | Unknown |
| iPhone16 | 1100 | Unknown |
+---------------+-------+---------+
5 rows in set (0.01 sec)
条件分岐は文字列だけでなく数値でも可能(その他の型も可)
SQL文
SELECT name, value,
CASE value
WHEN 1000 THEN 'OK'
WHEN 600 THEN 'NG'
ELSE 'Unknown'
END AS status
FROM
devices;
結果
+---------------+-------+---------+
| name | value | status |
+---------------+-------+---------+
| iPhone15 | 1000 | OK |
| iPad | 800 | Unknown |
| Google Pixel3 | 600 | NG |
| Galaxy s25 | 900 | Unknown |
| iPhone16 | 1100 | Unknown |
+---------------+-------+---------+
5 rows in set (0.00 sec)
CASE式を用いた条件分岐(比較)
SELECT [カラム名]
CASE [カラム名]
WHEN [条件値A] [条件文(<や=>など)] [条件値B] THEN [結果]
ELSE [デフォルト結果]
END AS [結果出力先カラム名]
FROM [テーブル名];
実行例
- valueが1000未満(<)なら"OK"
- valueが1000以上(>=)なら"NG"
- それ以外なら"Unknown"
としたい場合は以下のように記述
SQL文
SELECT name, value,
CASE
WHEN value < 1000 THEN 'OK'
WHEN value >= 1000 THEN 'NG'
ELSE 'Unknown'
END AS status
FROM
devices;
結果
+---------------+-------+--------+
| name | value | status |
+---------------+-------+--------+
| iPhone15 | 1000 | NG |
| iPad | 800 | OK |
| Google Pixel3 | 600 | OK |
| Galaxy s25 | 900 | OK |
| iPhone16 | 1100 | NG |
+---------------+-------+--------+
5 rows in set (0.01 sec)
関数を用いた出力処理
関数はデータを操作するための機能で、SUM(合計)やCOUNT(行数カウント)、MAX(最大値)などの結果を出力することができる(Excelの関数とよく似てる)
SELECT [関数]([カラム名]) AS [結果出力先カラム名]
FROM [テーブル名];
実行例
LENGTH関数を使用してnameの長さを取得したい場合
mysql> SELECT id, name, LENGTH(name) AS name_length FROM devices;
+----+---------------+-------------+
| id | name | name_length |
+----+---------------+-------------+
| 1 | iPhone15 | 8 |
| 2 | iPad | 4 |
| 3 | Google Pixel3 | 13 |
| 4 | Galaxy s25 | 10 |
| 5 | iPhone16 | 8 |
+----+---------------+-------------+
5 rows in set (0.00 sec)
MAX関数を使用してvalueの最大値を取得したい場合
mysql> SELECT MAX(value) AS max_value FROM devices;
+-----------+
| max_value |
+-----------+
| 1100 |
+-----------+
1 row in set (0.00 sec)
このスクラップは5ヶ月前にクローズされました