GitのCodeSpacesを使ってMySQLをざっくり学ぶ② テーブルの作成／削除・データの取得／追加／削除／更新

本記事はMySQLについて学習する初学者向けの記事となります。
環境としてCodeSpacesを使用していますが、今回の記事においてはほとんどMySQLについての内容となります。

ちなみにGithubのアカウントが既に登録されている前提で進んでいきますのでご注意ください。

また、GitでCodeScapesを使用する方法・MySQLをインストールする方法については１つ前の記事に記述していますのでそちらをご覧ください。
GitのCodeSpacesを使ってMySQLをざっくり学ぶ① DBの作成／削除／切替・ユーザーの作成・SQLファイル実行

前提

まず、今回の学習環境は下記のようになります。
・Git CodeSpaces
・MySQL（Ver 8.0.40-0）

リポジトリを作成して、CodeSpacesを開いた状態から始めます。

まず最初にMySQLを起動します。

terminal

$ sudo service mysql start

下記のtest_file.sqlを作成します。（ファイル名等は任意です）

test_file.sql

drop database if exists testdb;
create database testdb;
drop user if exists tester01@localhost;
create user tester01@localhost identified by '12345';
grant all on testdb.* to tester01@localhost;

こちらのSQLの内容ですが、ざっくりDB諸々を初期化するものです。
１行ずつの詳細につきましては前回の記事を参照いただけますと幸いです。

下記コマンドでファイルの実行を行います。DBとその操作が行えるユーザーが作成されます。

terminal

$ sudo mysql -u root < test_file.sql

テーブルの作成

現在testdbというDBがありますが、その中にまだテーブルが作成されていません。
このtestdbの中にテーブルを作ってみましょう。

まず、操作対象となるDB（testdb）を指定します。

terminal

mysql> use testdb;

次にテーブルを作成します。テーブル名の命名時は基本的に複数形にします。
括弧()内にそのテーブルに設定したいカラムを指定します。
括弧内の書き方は「(カラム名 データ型, カラム名 データ型,...)」のようにカラム名とデータ型の組み合わせをカンマ区切りで設定します。

（プロジェクト次第では日本語名で管理することもあります。）

terminal

create table students(id int unsigned, name varchar(32), age int, class varchar(32));
->
Query OK, 0 rows affected (0.01 sec)

次にテーブルが作成できたか確認します。

terminal

mysql> show tables;

 ->
+------------------+
| Tables_in_testdb |
+------------------+
| students         |
+------------------+
1 row in set (0.00 sec)

これでテーブルが作成できたことを確認できました。
ちなみに、カラムの内容もちゃんと反映されているかどうかも確認しておきましょう。

terminal

mysql> desc students;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int unsigned | YES  |     | NULL    |       |
| name  | varchar(32)  | YES  |     | NULL    |       |
| age   | int          | YES  |     | NULL    |       |
| class | varchar(32)  | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

先ほど指定した「id」「name」「age」「class」がそれぞれ設定したデータ型でテーブル内に導入されていることが分かります。ちなみにunsignedは負の値を持たず、0から始まる正の値のみを表現する符号なし整数のことです。

テーブルの削除

テーブル削除のコマンドは以下です。このコマンドで先ほど作成したstudentsテーブルを削除してみましょう。
「Query OK...」と表示されたら、削除成功です。

terminal

mysql> drop table students;

-> Query OK, 0 rows affected (0.01 sec)

実際に削除されたかどうか確認してみましょう。

terminal

mysql> show tables;

->Empty set (0.01 sec)

「Empty set」と表示されました。空という意味なので、テーブルの削除が行えたことが確認できました。

データの取得

次にデータの取得を行います。現時点ではテーブルも、テーブルに入ったデータもない状態ですので、
DB初期化SQLファイルに下記のようにコードを追加します。

追加したのは操作対象のDBを設定するコード、テーブルを作成するコード、テーブル内にデータを追加するコードです。
テーブル内にデータを追加するコードについての説明は「データの追加」にて行います。

test_file.sql

drop database if exists testdb;
create database testdb;
drop user if exists tester01@localhost;
create user tester01@localhost identified by '12345';
grant all on testdb.* to tester01@localhost;
-- 追加↓
use testdb;
create table students(id int unsigned, name varchar(32), age int, class varchar(32));
INSERT INTO students (id, name, age, class) VALUES
(1, '太郎', 20, 'A'),
(2, '花子', 22, 'B'),
(3, '次郎', 19, 'C'),
(4, '美咲', 21, 'D'),
(5, '健太', 23, 'C');

これをルート権限で実行します。

terminal

$ sudo mysql -u root < test_file.sql

次に、SQL文で作成されたユーザーでログインします。

terminal

$ sudo mysql -u tester01 -p

DB「testdb」を操作対象とします。

terminal

mysql>  use testdb;

次に、データの取得を行います。

全件取得

下記のようにコードを記述します。ここに記載されている「*」は「全て」という意味を持ちます。
これでstudentsテーブルの全データが出力されます。

terminal

mysql> select * from students;

->
+------+--------+------+-------+
| id   | name   | age  | class |
+------+--------+------+-------+
|    1 | 太郎   |   20 | A     |
|    2 | 花子   |   22 | B     |
|    3 | 次郎   |   19 | C     |
|    4 | 美咲   |   21 | D     |
|    5 | 健太   |   23 | C     |
+------+--------+------+-------+
5 rows in set (0.00 sec)

部分的に取得

例えばnameとageの値のみ取得したい場合は、下記のようにコードを記述します。

terminal

mysql> select name,age from students;

結果としてnameとageの値のみ表示されるようになります。

terminal

+--------+------+
| name   | age  |
+--------+------+
| 太郎   |   20 |
| 花子   |   22 |
| 次郎   |   19 |
| 美咲   |   21 |
| 健太   |   23 |
+--------+------+

条件を指定して取得

次は条件を指定してみましょう。
条件といっても様々なものがあります。

文字列の条件

完全一致
…文字列の完全一致でレコードを取得する場合、「=」を使用します。

terminal

mysql> select * from students where name = '太郎';

下記のように完全一致する結果が表示されます。

->
+------+--------+------+-------+
| id   | name   | age  | class |
+------+--------+------+-------+
|    1 | 太郎   |   20 | A     |
+------+--------+------+-------+
1 row in set (0.00 sec)

%の使用
…LIKE演算子と組み合わせて使用され、部分一致検索を行います。%の部分は検索対象の文字数制限がありません。
　ここではnameにおいて「太」を含むデータを取得します。

terminal

mysql> select * from students where name like '%太%';

結果としては下記のように出力されます。

terminal

->
+------+--------+------+-------+
| id   | name   | age  | class |
+------+--------+------+-------+
|    1 | 太郎   |   20 | A     |
|    5 | 健太   |   23 | C     |
+------+--------+------+-------+

もう一つ例を出します。先ほど「太」を含むデータは２件ありましたが、次は「太」から始まるデータ・「太」で終わるデータを取得してみましょう。

「太」から始まるデータ

terminal

mysql> select * from students where name like '太%';

->
+------+--------+------+-------+
| id   | name   | age  | class |
+------+--------+------+-------+
|    1 | 太郎   |   20 | A     |
+------+--------+------+-------+
1 row in set (0.00 sec)

「太」で終わるデータ

terminal

mysql> select * from students where name like '%太';

->
+------+--------+------+-------+
| id   | name   | age  | class |
+------+--------+------+-------+
|    5 | 健太   |   23 | C     |
+------+--------+------+-------+
1 row in set (0.00 sec)

このように%をつける位置で条件の内容を変更することができます。

もう一つ文字列の条件として「_」を紹介します。

_の使用
…LIKE 演算子と組み合わせて使用され、部分一致検索を行います。_の部分で検索対象の文字数を制限することができます。
　ここではnameにおいて「太」を含み、文字数制限のあるデータを取得します。

terminal

mysql> select * from students where name like '太_';

このように記述した場合は「太」から始まる２文字の文字列を条件とします。

結果は下記のようになります。

terminal

->
+------+--------+------+-------+
| id   | name   | age  | class |
+------+--------+------+-------+
|    1 | 太郎   |   20 | A     |
+------+--------+------+-------+
1 row in set (0.00 sec)

次に「太」から始まる３文字の文字列を条件とします。

terminal

mysql> select * from students where name like '太__';
->
Empty set (0.00 sec)

テーブル内に「太〇〇」という、「太」から始まる３文字のデータは存在しないため、空であるというような結果が表示されます。
このように「_」（アンダースコア）を使用することによって、文字数を制限した条件の指定が可能です。

数値の条件

文字列だけではなく、数値を条件として取り出すレコードを指定できます。

数値の完全一致
年齢が20の学生を取得する場合、下記のように記述します。

terminal

mysql> SELECT * FROM students WHERE age = 20;
->
+------+--------+------+-------+
| id   | name   | age  | class |
+------+--------+------+-------+
|    1 | 太郎   |   20 | A     |
+------+--------+------+-------+
1 row in set (0.00 sec)

特定の範囲内の値を指定
…数値～数値の間を指定してデータを取得することができます。内容は下記コードのようになります。

terminal

mysql> SELECT * FROM students WHERE age BETWEEN 18 AND 22;

->
+------+--------+------+-------+
| id   | name   | age  | class |
+------+--------+------+-------+
|    1 | 太郎   |   20 | A     |
|    2 | 花子   |   22 | B     |
|    3 | 次郎   |   19 | C     |
|    4 | 美咲   |   21 | D     |
+------+--------+------+-------+
4 rows in set (0.00 sec)

特定の値より大きいレコードを取得
…特定の値より大きいレコードを取得する際は下記のように書きます。

terminal

SELECT * FROM students WHERE age >= 21;

->
+------+--------+------+-------+
| id   | name   | age  | class |
+------+--------+------+-------+
|    2 | 花子   |   22 | B     |
|    4 | 美咲   |   21 | D     |
|    5 | 健太   |   23 | C     |
+------+--------+------+-------+
3 rows in set (0.00 sec)

特定の値より小さいレコードを取得
…特定の値より小さいレコードを取得する際は下記のように書きます。

terminal

mysql> SELECT * FROM students WHERE age <= 21;

->
+------+--------+------+-------+
| id   | name   | age  | class |
+------+--------+------+-------+
|    1 | 太郎   |   20 | A     |
|    3 | 次郎   |   19 | C     |
|    4 | 美咲   |   21 | D     |
+------+--------+------+-------+
3 rows in set (0.00 sec)

NULLかどうかの条件

NULLは「空文字列」や「ゼロ」とは異なり、値が「存在しない」ことを意味します。
データの値がNULLかどうかで条件を指定することもできます。

NULLの値を持つレコードを取得
…NULLの値を持つレコードを取得する場合は下記のようなコードになります。

terminal

SELECT * FROM students WHERE age IS NULL;

->
Empty set (0.00 sec)

今回のデータの中にはageがNUllの値を持つレコードが無いため、結果は空として表示されます。

NULLではない値を持つレコードを取得
…NULLではない値を持つレコードを取得する場合は下記のようなコードになります。

terminal

SELECT * FROM students WHERE age IS NOT NULL;

->
+------+--------+------+-------+
| id   | name   | age  | class |
+------+--------+------+-------+
|    1 | 太郎   |   20 | A     |
|    2 | 花子   |   22 | B     |
|    3 | 次郎   |   19 | C     |
|    4 | 美咲   |   21 | D     |
|    5 | 健太   |   23 | C     |
+------+--------+------+-------+
5 rows in set (0.00 sec)

今回のデータの中にはすべてのレコードがageに値を持っているため、上記のように結果が表示されます。

件数制限

…取得するレコードの件数自体を制限することも可能です。その場合は下記のようにコードを記述します。

最初の３件のレコードを取得する例

terminal

select * from students limit 3;
->
+------+--------+------+-------+
| id   | name   | age  | class |
+------+--------+------+-------+
|    1 | 太郎   |   20 | A     |
|    2 | 花子   |   22 | B     |
|    3 | 次郎   |   19 | C     |
+------+--------+------+-------+
3 rows in set (0.00 sec)

データの追加

insert文を使用してデータの追加を行うことができます。
先ほどのデータ投入時に使用したSQL文を確認してみましょう。

test_file.sql

insert into students (id, name, age, class) values
(1, '太郎', 20, 'A'),
(2, '花子', 22, 'B'),
(3, '次郎', 19, 'C'),
(4, '美咲', 21, 'D'),
(5, '健太', 23, 'C');

「students」テーブルに、「id」「name」「age」「class」カラムに値（values）を設定しています。
上記のようにカンマ区切りで値を設定すれば、複数の値を一気に設定してデータを追加することが可能です。
(複数の値を設定する場合「;」セミコロンを最後に忘れがちなので、要注意です。)

データの削除

データを削除するときは下記のように記述します。
WHEREで条件を指定しなければ、すべてのレコードが削除されることになるため、データの削除を行う場合には注意が必要です。

terminal

DELETE FROM students WHERE id = 1;
->
Query OK, 1 row affected (0.00 sec)

削除が成功したら「Query OK」と表示されます。
レコードが削除されたかどうか確認してみましょう。

terminal

select * from students;
->
+------+--------+------+-------+
| id   | name   | age  | class |
+------+--------+------+-------+
|    2 | 花子   |   22 | B     |
|    3 | 次郎   |   19 | C     |
|    4 | 美咲   |   21 | D     |
|    5 | 健太   |   23 | C     |
+------+--------+------+-------+
4 rows in set (0.00 sec)

データの更新

下記のように記述すれば、データの更新も行うことができます。こちらも業務で本番データを扱う際に注意が必要な作業となります。
更新内容はidが2のレコードのageを20に変更するというものです。

terminal

UPDATE students SET age = 20 WHERE id = 2;

結果を確認しましょう。id=2のレコードのageが20になっていれば良いです。

terminal

select * from students;
+------+--------+------+-------+
| id   | name   | age  | class |
+------+--------+------+-------+
|    2 | 花子   |   20 | B     |
|    3 | 次郎   |   19 | C     |
|    4 | 美咲   |   21 | D     |
|    5 | 健太   |   23 | C     |
+------+--------+------+-------+
4 rows in set (0.00 sec)