あらすじ
WEB系のフロントエンジニアがこれから MySQL を勉強しようとしているがそもそも SQL がよくわからないので最低限の操作を覚える必要があるとかないとか。
私の経験を元に、最初に覚えておいたほうがいい事をまとめました。とはいえ最初にどこでつまづいたのか若干記憶が遠のいているのでニーズを満たせるかどうかよくわかっていません。
準備
MacOSX の場合は以下で MySQL が使えるようになります。brew を使った事はないという方は Google にて検索してください。あと Windows ユーザーの人も自力でなんとかしてください。
% brew install mysql
version を確認します。私の手元では 5.7.17 となっていました。 もし version が異なる場合は brew upgrade mysql で最新版に変更できます。
% mysql --version
start, stop
MacOSX で brew install mysql した場合は以下で start, stop ができます。mysql.server status で動いているかどうかを確認することができます。
mysql.server start
mysql.server stop
01: MySQL と対話する
デフォルトだと root ユーザーの場合 nopassword でログインできると思います。
mysql -u root
ターミナルに以下のような画面がでれば MySQL と対話できる状態です
mysql>
すでに作成されている database 一覧を表示します。
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
とりあえず use mysql; を実行してみましょう。
mysql> use mysql;
Database changed
use mysql の状態で show tables; を実行してみましょう。
mysql> show tables;
省略
31 rows in set (0.00 sec)
上記テーブル一覧に user があるのでそれを見てみましょう。
mysql> select Host, User from user;
+-----------+-----------+
| Host | User |
+-----------+-----------+
| localhost | mysql.sys |
| localhost | root |
+-----------+-----------+
2 rows in set (0.00 sec)
表示するカラムを Host, User だけにしているのでシンプルです。全てのカラムを表示したい場合は * アスタリスクをつけます
select * from user;
そうすると横幅が長すぎて見えづらいかと思います。末尾の ; をセミコロンを \G に置き換えると少しみやすくなります。
select * from user\G
exit; を実行すると対話モードを終了する事ができます。
mysql> exit;
02: MySQL への命令をコマンドラインで実行する
以下のようにコマンドラインへ直接命令を書いたりできます。
mysql -u root mysql -e 'show tables;'
もしくは命令文をファイルに保存してそれを実行する事ができます。以下のようにshow tables; と1行だけ書かれたファイルを保存します。
% cat example.sql
show tables;
以下のコマンドを実行して動作を確認してください。
mysql -u root mysql < example.sql
03: 練習用の Database をつくる
以下のコマンドで練習用の database を作ります。
mysql -u root -e 'create database mydb'
以下のコマンドで mydb との対話モードを開始できます。
mysql -u root mydb;
04: 練習用の Table をつくる
とりあえずドリルなので適当にテーブルを作成してみます。下記のような scheme.sql ファイルを作成してください。
DROP TABLE IF EXISTS todos;
CREATE TABLE `todos` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`title` varchar(100) NOT NULL,
`status` int(1) DEFAULT '0',
`created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
以下のコマンドを実行すると todos テーブルを何度でも作り直す事ができます。
mysql -u root mydb < schema.sql
05: 行を追加、参照、更新、削除
行を追加
mysql> INSERT INTO todos (title, status) VALUES ("買い物", 1);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO todos (title, status) VALUES ("料理", 0);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO todos (title, status) VALUES ("粗大ゴミ", 0);
Query OK, 1 row affected (0.01 sec)
参照
mysql> select * from todos;
+----+--------------+--------+---------------------+---------------------+
| id | title | status | created | updated |
+----+--------------+--------+---------------------+---------------------+
| 1 | 買い物 | 1 | 2017-04-06 13:36:00 | 2017-04-06 13:36:00 |
| 2 | 料理 | 0 | 2017-04-06 13:36:00 | 2017-04-06 13:36:00 |
| 3 | 粗大ゴミ | 0 | 2017-04-06 13:36:01 | 2017-04-06 13:36:01 |
+----+--------------+--------+---------------------+---------------------+
3 rows in set (0.00 sec)
完了していないタスクを絞り込んで参照
select * from todos where status = 0;
+----+--------------+--------+---------------------+---------------------+
| id | title | status | created | updated |
+----+--------------+--------+---------------------+---------------------+
| 2 | 料理 | 0 | 2017-04-06 13:36:00 | 2017-04-06 13:36:00 |
| 3 | 粗大ゴミ | 0 | 2017-04-06 13:36:01 | 2017-04-06 13:36:01 |
+----+--------------+--------+---------------------+---------------------+
2 rows in set (0.00 sec)
料理を終えたのでタスクを更新。
mysql> select * from todos where id = 2;
mysql> update todos set status = 1 where id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
削除したい場合は以下
mysql> select * from todos where id = 2;
mysql> delete from todos where id = 2;
where id = 2 を忘れてしまうと全部の行が更新されるので注意しましょう。最初に select 文で where区を確認してからそのクエリの where 区に構文を足すようにすると間違いがないと思います。
06: バックアップとリストア
dump.sql にこれまでの作業を保存します。
% mysqldump -u root mydb > dump.sql
間違えてレコードを全て消してしまったとします。where 句を忘れてしまった場合。
mysql -u root mydb -e 'delete from todos'
mysql -u root mydb -e 'select count(*) from todos'
+----------+
| count(*) |
+----------+
| 0 |
+----------+
先ほどの dump.sql を使ってリストアします。
mysql -u root mydb < dump.sql
無事復元できました。
mysql -u root mydb -e 'select count(*) from todos'
+----------+
| count(*) |
+----------+
| 2 |
+----------+
06: pager の設定
mysql.user の全カラムが全て展開されると画面が崩れて見辛い場合、\G を使う方法を紹介しましたが pager を指定する方法もあります。
mysql> pager less
PAGER set to 'less -S'
この状態で下記コマンドを実行して十字キーで移動してみてください。
select * from mysql.user;
この less -S をやめたい場合は下記のコマンドを実行してください
mysql> nopager
PAGER set to stdout
またこのように長い画面をコピペしたい場合があると思いますがターミナルだと、どうしていいかわからない事もあるでしょう。その場合は出力先をファイルにして後で使いやすいエディタで開くという方法もあります。
mysql> pager cat > output.txt
PAGER set to 'cat > output.txt'
mysql> select * from mysql.user;
2 rows in set (0.00 sec)
mysql> nopager
PAGER set to stdout
07: 表示形式を CSV にする
時々 MySQL からデータを取り出して欲しいと依頼されたエンジニアが以下のような形式で非エンジニアの依頼者にそのまま渡すケースが見受けられます。
よくよく聞くとCSVデータが欲しいがエンジニアが忙しそうなので自分で頑張ってCSV化していたりする可能性があります。
% mysql -u root mydb -e 'select * from todos;'
+----+--------------+--------+---------------------+---------------------+
| id | title | status | created | updated |
+----+--------------+--------+---------------------+---------------------+
| 1 | 買い物 | 1 | 2017-04-06 13:36:00 | 2017-04-06 13:36:00 |
| 3 | 粗大ゴミ | 0 | 2017-04-06 13:36:01 | 2017-04-06 13:36:01 |
+----+--------------+--------+---------------------+---------------------+
もしそういう場面に遭遇した場合は、やり方は以下に載っているので参考にしてみてください。
もしくは MySQL Workbench などのツールを使ってもよいと思います。
まとめ
pager と表示形式に関しては最初は気にしなくてもいいと思いますがとりあえず頭の片隅においておくといいのかなあと思います。おしまい。