MySQLのコマンドの使い方が分からなくて困っている人
MySQLをプロジェクトに入れて接続は出来るようになったけど、そこからの操作方法が全くわからない…。テーブルの作り方とかデータの見方を教えて欲しい…！
今回の記事ではこんな方に向けて記事を書きます。
本記事の対象読者
- MySQLに接続は出来たけどテーブルの作り方がわからない方
- データの取得方法が知りたい方
- SQL文の書き方を知りたい方
- MySQLコマンドのチートシートが欲しい
こんにちは、himakuro(@himanakuroneko)です！
RubyやPHPのようなサーバーサイド言語を仮に書けたとしても、データベースの操作方法がわからないエンジニアは市場価値が一気に低下します。
そこで今回の記事では初心者の方向けに、MySQLやデータベースの内容でよくある勘違いを2つ紹介した後にMySQLコマンドやSQL文の書き方を使い方を解説して行きます。
今回の記事を読みデータベースの操作も出来るWebエンジニアを目指して行きましょう。
目次
MySQLとデータベースの違い
初心者の方によくある勘違いでMySQLがデータベースだと思われている方がいますが、MySQLはデータベースではなく、データベースを管理するためのシステムです。
MySQLというデータベース管理システムの中に複数のデータベースが存在出来るという形で覚えておきましょう。
データベースとテーブルの違い
これもよくある勘違いなのですがデータベースとテーブルは別物で、1つのデータベースの中に複数のテーブルが存在する事が出来ます。
LaravelやRailsなどでは先にデータベースを作成し、その中にusersテーブルなどのテーブルを入れていく流れになります。
himakuro
MySQL、データベース、テーブルがそれぞれ何なのかを理解しておくと、Googleなどで検索するときの検索キーワードにも迷いがなくなるよ！
MySQLコマンド – 接続編
まずは接続と接続を終了する方法です。
今回の記事で紹介している各コマンドの先頭にある$ やmysql>は入力不要な点に注意しましょう。
MySQLに接続
shell
# localhostのMySQLサーバに接続する (ユーザー名: himakuroの場合)
$ mysql -u himakuro -p
MySQLに接続（Laradockの場合）
Laradockのデータベースに接続する場合はLaradockを起動している状態で、下記のコマンドを実行する必要があります。
shell
# MySQLが起動されているコンテナの中に入る
$ docker-compose exec mysql bash
# defaultユーザーとしてmysqlに接続する
$ mysql -u default -p
もし上記の方法でLaradockで起動しているMySQLに接続が出来ない場合は、下記の記事を参考にLaradock側の各種設定を見直してみましょう。
MySQLの接続を終了する
一度MySQLに繋いだ後は接続を終了しないと、普段のターミナルで入力するコマンドが実行出来ません。
mysql
mysql> exit
himakuro
MySQLに接続するためのコマンドと、接続を終了するためのコマンドはよく利用するので、出来る限り暗記しておこう！
MySQLコマンド – データベース編
データベース一覧を表示
mysql
mysql> show databases;
---------------- 以下実行結果 ----------------
mysql> show databases;
+-----------------------------------+
| Database |
+-----------------------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+-----------------------------------+
himakuro
MySQLには予め複数のデータベースが用意されているよ。ただしこれらのデータベースの中に自分でテーブルを追加することはない点に注意しよう！
データベースの作成
mysql
# db_nameという名前のデータベースを作成
mysql> CREATE DATABASE db_name;
# db_nameというデータベースが作成されたかを確認する
mysql> show databases;
---------------- 以下実行結果 ----------------
+-----------------------------------+
| Database |
+-----------------------------------+
| db_name |
| information_schema |
| mysql |
| performance_schema |
| sys |
+-----------------------------------+
himakuro
今回は例としてdb_nameという名前でデータベースを作成したけど、ここでは好きな名前のデータベースを作成出来るよ。
データベースの選択
データベースの中に作成したテーブルの中身を確認するには、どのデータベースを使うかをMySQLに教えてあげる必要があります。
mysql
# db_nameのデータベースを選択
mysql> use db_name;
---------------- 以下実行結果 ----------------
Database changed
データベースの削除
データベースを削除すると中にあるテーブルも全て削除されるので実行には注意しましょう。
mysql
# db_nameのデータベースを削除
mysql> drop database db_name;
---------------- 以下実行結果 ----------------
Query OK, 0 rows affected (0.03 sec)
MySQLコマンド – テーブル編
テーブル一覧を表示
mysql
# テーブル一覧を表示
mysql> show tables;
---------------- 以下実行結果 ----------------
Empty set (0.00 sec)
himakuro
新規で作成したデータベースの場合はまだテーブルが存在しないからEmpty set(空)と表示されるよ
テーブルの作成
テーブルの作成時のコマンドは少し複雑なものになっています。
テーブル作成のテンプレート
mysql
mysql> create table [テーブル名] (
[フィールド名] [データ型] [オプション]
) ENGINE=[InnoDB/MyISAM] DEFAULT CHARSET=[文字コード];
usersテーブルの作成例
mysql
mysql> CREATE TABLE `users` (
`id` bigint UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT "ユーザーID",
`name` VARCHAR(255) NOT NULL COMMENT "ユーザー名",
`age` int(10) UNSIGNED NOT NULL COMMENT "年齢",
`created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT "登録日",
`updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT "更新日"
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
今回はusersテーブルに自動採番のidカラム、名前を保存するnameカラム、年齢を保存するageカラム、作成時間を保存するcreated_atカラム、更新時間を保存するupdated_atカラムを用意しました。
idを自動採番カラムにすることでレコード作成時に1から連番の値が自動で入るようになります。またupdated_atのカラムはレコードを更新時に自動で更新時間が入るような設定にしています。
ageカラムにはUNSIGNEDというのを付ける事で負の値(-1など)を入らないようにしています。
himakuro
INSERT編で実際にこのテーブルにデータを入れていくので、idカラムに実際にどのような値が入るかを確認しておこう！そしてUPDATE編ではupdated_atの値に注目しながら進めよう！
テーブルの構成を確認
作成されているテーブルのカラムなどの情報は2種類の方法で確認が可能です。
mysql
mysql> desc users;
---------------- 以下実行結果 ----------------
+------------+-----------------+------+-----+-------------------+-----------------------------------------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-----------------+------+-----+-------------------+-----------------------------------------------+
| id | bigint unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(255) | NO | | NULL | |
| age | int unsigned | NO | | NULL | |
| created_at | datetime | YES | | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
| updated_at | datetime | YES | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+------------+-----------------+------+-----+-------------------+-----------------------------------------------+
5 rows in set (0.00 sec)
mysql
mysql> show create table users;
---------------- 以下実行結果 ----------------
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| users | CREATE TABLE `users` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT 'ユーザーID',
`name` varchar(255) NOT NULL COMMENT 'ユーザー名',
`age` int unsigned NOT NULL COMMENT '年齢',
`created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '登録日',
`updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新日',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
himakuro
descコマンドはカラムの情報をメインで見たい時に活用し、show create tableはテーブル作成時に実行したcreate tableのSQL文を確認したい時に活用しよう。
MySQLコマンド – INSERT編(作成)
次は作成したテーブルにレコード（データ）を入れて行きます。
複数のレコードを一括で入れる方法も紹介するので合わせて覚えておきましょう。
INSERTのSQL文のテンプレート
mysql
mysql> insert into [テーブル名] [フィールド名] values [値]
usersテーブルにレコードをインサート
mysql
mysql> insert into users (name,age) values ('himakuro','100');
---------------- 以下実行結果 ----------------
Query OK, 1 row affected (0.01 sec)
usersテーブルに複数のレコードを一括でインサート
mysql
mysql> insert into users (name,age) values ('himakuro','100'), ('test_user', 50);
---------------- 以下実行結果 ----------------
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
MySQLコマンド – SELECT編(取得)
次は作成したレコード（データ）を検索していきます。
SELECTのSQL文のテンプレート
mysql
mysql> select [カラム名] from [テーブル名] [条件式]
テーブルに存在する全レコードの全カラムを取得
mysql
mysql> select * from users;
---------------- 以下実行結果 ----------------
+----+-----------+-----+---------------------+---------------------+
| id | name | age | created_at | updated_at |
+----+-----------+-----+---------------------+---------------------+
| 1 | himakuro | 100 | 2020-08-31 18:24:00 | 2020-08-31 18:24:00 |
| 2 | test_user | 50 | 2020-08-31 18:24:00 | 2020-08-31 18:24:00 |
+----+-----------+-----+---------------------+---------------------+
2 rows in set (0.00 sec)
himakuro
「*」を指定すると全カラム取得してくるよ！
WHEREで条件を付けてレコードを検索
mysql
mysql> select * from users where name = 'himakuro';
---------------- 以下実行結果 ----------------
+----+----------+-----+---------------------+---------------------+
| id | name | age | created_at | updated_at |
+----+----------+-----+---------------------+---------------------+
| 1 | himakuro | 100 | 2020-08-31 18:24:00 | 2020-08-31 18:24:00 |
+----+----------+-----+---------------------+---------------------+
1 row in set (0.00 sec)
複数条件を付けてレコードを検索
mysql
mysql> select * from users where age > 80 and created_at > '2020-08-31 00:00:00';
---------------- 以下実行結果 ----------------
+----+----------+-----+---------------------+---------------------+
| id | name | age | created_at | updated_at |
+----+----------+-----+---------------------+---------------------+
| 1 | himakuro | 100 | 2020-08-31 18:24:00 | 2020-08-31 18:24:00 |
+----+----------+-----+---------------------+---------------------+
1 row in set (0.00 sec)
取得したいカラムを指定して検索する
mysql
mysql> select id,name,age from users where name = 'himakuro';
---------------- 以下実行結果 ----------------
+----+----------+-----+
| id | name | age |
+----+----------+-----+
| 1 | himakuro | 100 |
+----+----------+-----+
1 row in set (0.00 sec)
LIMITを付けて取得する件数に制限をかける
mysql
mysql> select * from users limit 1;
---------------- 以下実行結果 ----------------
+----+----------+-----+---------------------+---------------------+
| id | name | age | created_at | updated_at |
+----+----------+-----+---------------------+---------------------+
| 1 | himakuro | 100 | 2020-08-31 18:24:00 | 2020-08-31 18:24:00 |
+----+----------+-----+---------------------+---------------------+
1 row in set (0.00 sec)
ORDER BYで順序を並び替えて検索
mysql
# 並び順を指定しない場合
mysql> select * from users order by age;
---------------- 以下実行結果 ----------------
+----+-----------+-----+---------------------+---------------------+
| id | name | age | created_at | updated_at |
+----+-----------+-----+---------------------+---------------------+
| 2 | test_user | 50 | 2020-08-31 18:24:00 | 2020-08-31 18:24:00 |
| 1 | himakuro | 100 | 2020-08-31 18:24:00 | 2020-08-31 18:24:00 |
+----+-----------+-----+---------------------+---------------------+
2 rows in set (0.00 sec)
mysql
# 並び順を降順で指定した場合
mysql> select * from users order by age desc;
---------------- 以下実行結果 ----------------
+----+-----------+-----+---------------------+---------------------+
| id | name | age | created_at | updated_at |
+----+-----------+-----+---------------------+---------------------+
| 1 | himakuro | 100 | 2020-08-31 18:24:00 | 2020-08-31 18:24:00 |
| 2 | test_user | 50 | 2020-08-31 18:24:00 | 2020-08-31 18:24:00 |
+----+-----------+-----+---------------------+---------------------+
2 rows in set (0.00 sec)
MySQLコマンド – UPDATE編(更新)
続いて作成したレコード（データ）を更新していきます。
FROMが無いことに注意
UPDATEのSQL文のテンプレート
mysql
mysql> update [テーブル名] set [フィールド名]=[値] [条件式]
全てのレコードを更新
mysql
# 並び順を降順で指定した場合
mysql> update users set age = 10;
---------------- 以下実行結果 ----------------
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0
himakuro
updateが成功したらselectのSQL文を実行して、本当に値が変化しているか確認してみよう！
WHEREで条件を付けてレコードを更新
mysql
# id=1のレコードのageを200に変更
mysql> update users set age = 200 where id = 1;
---------------- 以下実行結果 ----------------
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MySQLコマンド – DELETE編(削除)
最後に作成したレコードを削除して行きます。
DELETEのSQL文のテンプレート
mysql
mysql> delete from [テーブル名] [条件式]
全てのレコードを削除
mysql
# 並び順を降順で指定した場合
mysql> delete from users;
---------------- 以下実行結果 ----------------
Query OK, 2 rows affected (0.01 sec)
WHEREで条件を付けてレコードを削除
mysql
# 並び順を降順で指定した場合
mysql> delete from users where name = 'himakuro';
---------------- 以下実行結果 ----------------
Query OK, 1 rows affected (0.01 sec)