MySQLをプロジェクトに導入し接続は完了したものの、次のステップである具体的な操作方法で躓いてしまう方も多いと思います。
そこで今回の記事ではテーブルの作成方法やデータの確認方法など、MySQLの基本的な使い方について解説します。
この記事は以下のような方々に最適です
- MySQLに接続はできたが、テーブルの作成方法がわからない方
- データの取得方法を学びたい方
- SQL文の基本的な書き方を理解したい方
- MySQLコマンドの便利なチートシートが欲しい方
PHPやRubyなどのサーバーサイド言語を扱えるエンジニアでも、データベース操作ができなければ、その市場価値は大きく変わってきます。
今回の記事ではMySQLやデータベースに関する一般的な誤解を2つ取り上げ、その後でMySQLコマンドやSQL文の基本的な使い方を詳しく説明していきます。
この記事を通じて、データベース操作もこなせるWebエンジニアへの一歩を踏み出しましょう。
MySQLとデータベースの違い
初心者の方によくある勘違いでMySQLがデータベースだと思われている方がいますが、MySQLはデータベースではなく、データベースを管理するためのシステムです。
MySQLというデータベース管理システムの中に複数のデータベースが存在出来るという形で覚えておきましょう。
データベースとテーブルの違い
これもよくある勘違いなのですがデータベースとテーブルは別物で、1つのデータベースの中に複数のテーブルが存在する事が出来ます。
LaravelやRailsなどでは先にデータベースを作成し、その中にusersテーブルなどのテーブルを入れていく流れになります。
MySQL、データベース、テーブルの3つの用語を理解しておくだけで、Google検索をする際のキーワードにも迷いがなくなります。
MySQLコマンド – 接続編
まずは接続と接続を終了する方法です。
今回の記事で紹介している各コマンドの先頭にある$ やmysql>は入力不要な点に注意しましょう。
MySQLに接続
shell
# localhostのMySQLサーバに接続する (ユーザー名: himakuroの場合)
$ mysql -u himakuro -p
直接パスワードの入力が非推奨な理由
理由はいくつかありますが一例を紹介します。Linuxコマンドにはhistoryという過去に入力した内容を出力するコマンドがあります。もしアプリケーションを動かしているサーバーで直接パスワードを入力してMySQLに接続していて、悪意のあるユーザーにサーバーへの侵入を許してしまった場合、このhistoryコマンドでパスワードがバレてしまい大切なデータが保管されているデータベースへの侵入も許してしまいます。
MySQLに接続（Laradockの場合）
Laradockのデータベースに接続する場合はLaradockを起動している状態で、下記のコマンドを実行する必要があります。
shell
# MySQLが起動されているコンテナの中に入る
$ docker-compose exec mysql bash
# defaultユーザーとしてmysqlに接続する
$ mysql -u default -p
もし上記の方法でLaradockを起動していてMySQLに接続が出来ない場合は、下記の記事を参考に見直してみましょう。
Laradockを使ってLaravel6の環境を構築する手順をコマンド付きで解説！データベースの設定もあり！
Laravelの環境構築をしたい！Laradockの使い方がわからない！Lardockのデータベースがつながらない！
今回の...
Ruby On RailsでMySQLに接続が出来ない場合は下記の記事を参考にしてください。
【初心者向け】RailsでMySQLを使うための手順をコマンド付きで解説！
RailsでMySQLを使いたいけど変更方法が分からなくて困っている人
RailsでMySQLを使うようにしたいけど変更方法が...
MySQLの接続を終了する
一度MySQLに繋いだ後は接続を終了しないと、普段のターミナルで入力するコマンドが実行出来ません。
mysql
mysql> exit
MySQLに接続するためのコマンドと、接続を終了するためのコマンドは頻繁に利用するので、出来る限り暗記しておきましょう。
MySQLコマンド – データベース編
データベース一覧を表示
mysql
mysql> show databases;
---------------- 以下実行結果 ----------------
mysql> show databases;
+-----------------------------------+
| Database |
+-----------------------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+-----------------------------------+
MySQLには予め複数のデータベースが用意されています。ただしこれらのデータベースの中に自分でテーブルを追加することは基本ありません。
データベースの作成
mysql
# db_nameという名前のデータベースを作成
mysql> CREATE DATABASE db_name;
# db_nameというデータベースが作成されたかを確認する
mysql> show databases;
---------------- 以下実行結果 ----------------
+-----------------------------------+
| Database |
+-----------------------------------+
| db_name |
| information_schema |
| mysql |
| performance_schema |
| sys |
+-----------------------------------+
今回は例としてdb_nameという名前でデータベースを作成しましたが、ここでは好きな名前のデータベースを作成出来ます。
データベースの選択
データベースの中に作成したテーブルの中身を確認するには、どのデータベースを使うかをMySQLに教えてあげる必要があります。
mysql
# db_nameのデータベースを選択
mysql> use db_name;
---------------- 以下実行結果 ----------------
Database changed
mysql
$ mysql -u default -p db_name
データベースの削除
データベースを削除すると中にあるテーブルも全て削除されるので実行には注意しましょう。
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)
新規で作成したデータベースの場合はまだテーブルが存在しないので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など)を入らないようにしています。
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)
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)
「*」を指定すると全カラム情報を取得します。
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
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)
