MySQL演習：整理、取得してみよう！

2024.05.18

MySQLのステートメント(SQL文)

前回までのMySQLの課題で、基本的なステートメントを扱ってきましたが、このセクションでは、開発でよく使われるものを、今まで作ったテーブルをもとに紹介していきます。すでに触れているものも含みますが、一つずつ実行してみて、身につけていくようにしましょう！

PRIMARY KEY

PRIMARY KEY(プライマリーキー)とは作成したテーブルの中の1つまたは複数のカラムの組み合わせに対して設定するもので、テーブルに格納されているデータをに格納されているデータを識別するための目印のようなものです。また、各レコードが他のレコードと区別され、データの重複や不整合が防止されます。m_usersなどのテーブル作成時に、すでに触れているものとなっております。以下のように、作成時、idカラムにPRIMARY KEYが設定されていることがわかります。

テーブルの作成
1
2
3
4
5
6
7
8
9
//　すでに作成済みのため、実行しなくて良い
mysql> CREATE TABLE `m_users` (
          `id` int NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT "ID",
          `user_name` VARCHAR(100) NOT NULL COMMENT "ユーザー名",
          `mail_address` VARCHAR(200) NOT NULL COMMENT "メールアドレス",
          `password` VARCHAR(100) NOT NULL COMMENT "パスワード",
          `created` datetime DEFAULT NULL COMMENT "登録日",
          `modified` datetime DEFAULT NULL COMMENT "更新日"
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

下記は前回までに作った「m_users」テーブルの内容を4行目の「user_name」を1行目の名前と同じになるように、UPDATE句で変更したものです。こちらを元に、中身を確認していきます。

カラム情報の取得

以下のコマンドを実行することで、テーブルのカラム情報が確認できます。「Key」のところに「PRI」とある箇所が、PRIMARY KEYが設定のあるカラムになります。

1
2
3
4
5
6
7
8
9
10
11
12
mysql> SHOW COLUMNS FROM m_users;
+--------------+--------------+------+-----+---------+----------------+
| Field        | Type         | Null | Key | Default | Extra          |
+--------------+--------------+------+-----+---------+----------------+
| id           | int(11)      | NO   | PRI | NULL    | auto_increment |
| user_name    | varchar(100) | NO   |     | NULL    |                |
| mail_address | varchar(200) | NO   |     | NULL    |                |
| password     | varchar(100) | NO   |     | NULL    |                |
| created      | datetime     | YES  |     | NULL    |                |
| modified     | datetime     | YES  |     | NULL    |                |
+--------------+--------------+------+-----+---------+----------------+
6 rows in set (0.22 sec)

AUTO_INCREMENTの設定

テーブルを作成するときにカラムに AUTO_INCREMENT をつけると、データを追加した時にカラムに対して現在格納されている最大の数値に 1 を追加した数値を自動で格納することができます。カラムに連続した数値を自動で格納したい場合に便利です。以下のように、作成時、idカラムにAUTO_INCREMENTが設定されていることがわかります。

テーブルの作成
1
2
3
4
5
6
7
8
9
//　すでに作成済みのため、実行しなくて良い
mysql> CREATE TABLE `m_users` (
          `id` int NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT "ID",
          `user_name` VARCHAR(100) NOT NULL COMMENT "ユーザー名",
          `mail_address` VARCHAR(200) NOT NULL COMMENT "メールアドレス",
          `password` VARCHAR(100) NOT NULL COMMENT "パスワード",
          `created` datetime DEFAULT NULL COMMENT "登録日",
          `modified` datetime DEFAULT NULL COMMENT "更新日"
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

カラム情報の取得

以下のコマンドを実行することで、テーブルのカラム情報が確認できます。「Extra」のところに「auto_increment」とある箇所が、AUTO_INCREMENTが設定のあるカラムになります。

1
2
3
4
5
6
7
8
9
10
11
12
mysql> SHOW COLUMNS FROM m_users;
+--------------+--------------+------+-----+---------+----------------+
| Field        | Type         | Null | Key | Default | Extra          |
+--------------+--------------+------+-----+---------+----------------+
| id           | int(11)      | NO   | PRI | NULL    | auto_increment |
| user_name    | varchar(100) | NO   |     | NULL    |                |
| mail_address | varchar(200) | NO   |     | NULL    |                |
| password     | varchar(100) | NO   |     | NULL    |                |
| created      | datetime     | YES  |     | NULL    |                |
| modified     | datetime     | YES  |     | NULL    |                |
+--------------+--------------+------+-----+---------+----------------+
6 rows in set (0.22 sec)

レコードの追加時のidカラム

auto_incrementをidカラムに設定することで、レコードの挿入時、自動的にidの最大数値＋１の数値が割り当てられます。

レコードの挿入
1
2
mysql> INSERT INTO m_users (user_name, mail_address, password, created, modified)
           VALUES ("new_records", "new@gmail.com", "123123", now(), now());

上記のコマンドで21個目のレコードを挿入したあと、下記コマンドでテーブルの中身を確認すると、idカラムの21行目が、「21」となっていることがわかります。

1
mysql> select * from m_users;

パターンマッチングを行う(LIKE演算子)

WHERE 句で条件を指定するときに LIKE 演算子を使用すると、カラムの値と文字列が一致するかどうかを比較する時に特殊な文字 ‘%’ と ‘_’ を使ってパターンマッチングを行うことができます。

下記例題で、以前追加したuser_nameカラムが「new_records」を検索してみよう

「%」を使用した例
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
//「%」任意の0文字以上の文字列
 
//実行例①
SELECT * FROM m_users WHERE user_name LIKE 'new%';
+----+-------------+---------------+----------+---------------------+---------------------+
| id | user_name   | mail_address  | password | created             | modified            |
+----+-------------+---------------+----------+---------------------+---------------------+
| 21 | new_records | new@gmail.com | 123123   | 2024-05-18 17:37:44 | 2024-05-18 17:37:44 |
+----+-------------+---------------+----------+---------------------+---------------------+
1 row in set (0.00 sec)
 
//実行例②
mysql> SELECT * FROM m_users WHERE user_name LIKE 'n%w%';
+----+-------------+---------------+----------+---------------------+---------------------+
| id | user_name   | mail_address  | password | created             | modified            |
+----+-------------+---------------+----------+---------------------+---------------------+
| 21 | new_records | new@gmail.com | 123123   | 2024-05-18 17:37:44 | 2024-05-18 17:37:44 |
+----+-------------+---------------+----------+---------------------+---------------------+
1 row in set (0.01 sec)
「 _ 」をを使用した例
1
2
3
4
5
6
7
8
9
10
11
12
//「 _ 」　任意の1文字(「 _ 」の個数分)を検索
 
//「new_records」が11文字のため「 _ 」を11個で検索
mysql> SELECT * FROM m_users WHERE user_name LIKE '___________';
+----+-------------+-----------------+----------+---------------------+---------------------+
| id | user_name   | mail_address    | password | created             | modified            |
+----+-------------+-----------------+----------+---------------------+---------------------+
|  1 | Yamada Taro | yamda@gmail.com | 123123   | 2024-04-30 18:32:38 | 2024-04-30 18:32:38 |
|  4 | Yamada Taro | test1@gmail.com | 123123   | 2024-04-30 18:32:38 | 2024-04-30 18:32:38 |
| 21 | new_records | new@gmail.com   | 123123   | 2024-05-18 17:37:44 | 2024-05-18 17:37:44 |
+----+-------------+-----------------+----------+---------------------+---------------------+
3 rows in set (0.00 sec)
特殊文字を含む文字列を検索
1
2
3
4
5
6
7
8
9
10
11
//以下では「 _ 」を含むuser_nameを検索
 
// LIKE '%_%'で実行すると、任意の一文字と認識されてしまうので、
// _のまえに¥(ターミナルでは\に変換される)をつけて検索しよう
mysql> SELECT * FROM m_users WHERE user_name LIKE '%\_%';
+----+-------------+---------------+----------+---------------------+---------------------+
| id | user_name   | mail_address  | password | created             | modified            |
+----+-------------+---------------+----------+---------------------+---------------------+
| 21 | new_records | new@gmail.com | 123123   | 2024-05-18 17:37:44 | 2024-05-18 17:37:44 |
+----+-------------+---------------+----------+---------------------+---------------------+
1 row in set (0.03 sec)
補足

上記の検索結果の中に「Yamada Taro」が2件出てきているが、idがわかっていれば、どちらの「Yamada Taro」の情報が欲しいか一目瞭然になる。また、重複を防止するPRIMARY KEYを設定し、最大数値+1で数値を追加するようにAUTO_INCREMENTを設定したidカラムで検索することで、間違った検索をしなくなる。

1
2
3
4
5
6
7
mysql> SELECT * FROM m_users WHERE id = '1';
+----+-------------+-----------------+----------+---------------------+---------------------+
| id | user_name   | mail_address    | password | created             | modified            |
+----+-------------+-----------------+----------+---------------------+---------------------+
|  1 | Yamada Taro | yamda@gmail.com | 123123   | 2024-04-30 18:32:38 | 2024-04-30 18:32:38 |
+----+-------------+-----------------+----------+---------------------+---------------------+
1 row in set (0.17 sec)

コメント