フロントエンジニアがMySQLで確認するなら覚えるべき40のコマンド

まえがき

MySQLというと一般的にはバックエンドが使用するものだが、フロントエンドの人でも使用できるとデータの検索や変更の業務効率が向上する。

この記事では私がよく使用するMySQLのコマンドを40通り記載した。

記事タイトルにも記述してあるが「フロントエンジニアがMySQLで確認するなら覚えるべき40のコマンド」なのでcreateやdropなどは記載していない。

1. show databases

データベースの一覧を表示する

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sample             |
+--------------------+

2. use データベース名

データベースを指定

mysql> use sample;
Database changed

3. select database();

useで指定したデータベースを表示

mysql> select database();
+------------+
| database() |
+------------+
| sample     |
+------------+

4. show tables;

テーブルの一覧を表示

mysql> show tables;
+------------------+
| Tables_in_sample |
+------------------+
| members          |
+------------------+

5. show tables; をlikeで絞る

mysql> show tables like "t%";
+-----------------------+
| Tables_in_sample (t%) |
+-----------------------+
| term                  |
| terms                 |
| toeic                 |
+-----------------------+

6. desc テーブル名で定義表示

show columns from テーブル名;という書き方もあるが、コマンドが長いので同じ結果のdescを使用したほうが良い。

mysql> desc staff;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| no    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

6. insert into テーブル名 values (値, ...);

テーブルに行を追加(1行)

mysql> insert into staff values (1, 'iwabe', 32);

テーブルに行を追加(複数行)

mysql> insert into staff values
    -> (2, 'sato', 24),
    -> (3, 'tanaka', 40);

7. select * from テーブル名

テーブルのデータをすべて表示

mysql> select * from staff;
+------+--------+------+
| no   | name   | age  |
+------+--------+------+
|    1 | iwabe  |   32 |
|    2 | sato   |   24 |
|    3 | tanaka |   40 |
+------+--------+------+

テーブルのデータを指定した列だけ表示

mysql> select no, name from staff;
+------+--------+
| no   | name   |
+------+--------+
|    1 | iwabe  |
|    2 | sato   |
|    3 | tanaka |
+------+--------+

8. delete from テーブル名 where 条件

テーブルの指定した行を削除

mysql> delete from staff where no = 3;
mysql> select * from staff;
+------+-------+------+
| no   | name  | age  |
+------+-------+------+
|    1 | iwabe |   32 |
|    2 | sato  |   24 |
+------+-------+------+

9. updateでテーブルの値を更新

mysql> update staff set age=18 where name='sato';
mysql> select * from staff;
+------+-------+------+
| no   | name  | age  |
+------+-------+------+
|    1 | iwabe |   32 |
|    2 | sato  |   18 |
+------+-------+------+

10. 特定の文字列で開始する値だけupdate

特定の文字列ではじまる値だけupdateしたい場合はwhereでlikeを使用する。

mysql> select * from staff;
+------+--------+------+
| no   | name   | age  |
+------+--------+------+
|    1 | iwabe  |   32 |
|    2 | sato   |   18 |
|    3 | saitou |   24 |
|    4 | sano   |   46 |
|    5 | kasai  |   20 |
+------+--------+------+

# nameがsaではじまる値だけageを0にする。

mysql> update staff set age=0 where name like 'sa%';
mysql> select * from staff;
+------+--------+------+
| no   | name   | age  |
+------+--------+------+
|    1 | iwabe  |   32 |
|    2 | sato   |    0 |
|    3 | saitou |    0 |
|    4 | sano   |    0 |
|    5 | kasai  |   20 |
+------+--------+------+

逆にnameがsaではじまらない値だけ表示する場合はnot like 'sa%'となる。

11. 氏名をconcatで結合して表示する

名字と名前がわかれて保存する場合はconcatで結合して表示したほうが見やすい。

mysql> select * from names;
+------+-------+-------+
| no   | lname | fname |
+------+-------+-------+
|    1 | sato  | aiko  |
|    2 | sato  | bekky |
|    3 | sato  | chris |
+------+-------+-------+

mysql> select no, concat(lname, ' ', fname) as name from names;
+------+------------+
| no   | name       |
+------+------------+
|    1 | sato aiko  |
|    2 | sato bekky |
|    3 | sato chris |
+------+------------+

13. 特定の複数の値はinを使用

whereで特定の複数の値を指定する際に演算子を使用すると式が長くなるのでinを使用。

mysql> select * from staff where name in ('kasai', 'yamaki');
+------+--------+------+
| no   | name   | age  |
+------+--------+------+
|    2 | kasai  |   20 |
|    3 | yamaki |   44 |
+------+--------+------+

14. 特定の範囲の値はbetweenを使用

whereで特定の範囲を指定する際に演算子を使用すると式が長くなるのでbetweenを使用。

mysql> select * from staff where age between 20 and 40;
+------+-------+------+
| no   | name  | age  |
+------+-------+------+
|    1 | iwabe |   32 |
|    2 | kasai |   20 |
+------+-------+------+

15. MySQLにもfloorなどがある

MySQLにもfloor, ceil, roundがあるので用途に応じて使用する。

mysql> select * from scores;
+------+-------+
| no   | score |
+------+-------+
|    1 | 87.39 |
|    2 | 56.87 |
|    3 | 71.25 |
+------+-------+

mysql> select no, floor(score) from scores;
+------+--------------+
| no   | floor(score) |
+------+--------------+
|    1 |           87 |
|    2 |           56 |
|    3 |           71 |
+------+--------------+

小数点第1位まで表示したい場合はtruncateを使用する

mysql> select no, truncate(score, 1) from scores;
+------+--------------------+
| no   | truncate(score, 1) |
+------+--------------------+
|    1 |               87.3 |
|    2 |               56.8 |
|    3 |               71.2 |
+------+--------------------+

16. UNIXTIMEはそのまま使用しない

MySQLで日時がDATETIME型などではなくUNIXタイムスタンプの数値で保存されている場合は、from_unixtimeで変換すると見やすくなる。

mysql> select * from term;
+------+------------+
| no   | unixtime   |
+------+------------+
|    1 | 1577156400 |
|    2 | 1577242800 |
|    3 | 1577329200 |
+------+------------+

mysql> select no, from_unixtime(unixtime) from term;
+------+-------------------------+
| no   | from_unixtime(unixtime) |
+------+-------------------------+
|    1 | 2019-12-24 12:00:00     |
|    2 | 2019-12-25 12:00:00     |
|    3 | 2019-12-26 12:00:00     |
+------+-------------------------+

whereで期間を指定する場合はUNIX_TIMESTAMPを併用する。

mysql> select no, from_unixtime(unixtime) from term 
    -> where unixtime between unix_timestamp('2019-12-24 00:00:00')
    -> and unix_timestamp('2019-12-25 23:59:59');
+------+-------------------------+
| no   | from_unixtime(unixtime) |
+------+-------------------------+
|    1 | 2019-12-24 12:00:00     |
|    2 | 2019-12-25 12:00:00     |
+------+-------------------------+

17. 並び変えて上位3件だけ表示

並び替えはorder byとlimitを使用する。

デフォルトは昇順で降順はdescを追記。

mysql> select * from staff;
+------+--------+------+
| no   | name   | age  |
+------+--------+------+
|    1 | iwabe  |   32 |
|    2 | kasai  |   20 |
|    3 | yamaki |   44 |
|    4 | sato   |   60 |
|    5 | tanaka |   55 |
+------+--------+------+

mysql> select * from staff order by age limit 3;
+------+--------+------+
| no   | name   | age  |
+------+--------+------+
|    2 | kasai  |   20 |
|    1 | iwabe  |   32 |
|    3 | yamaki |   44 |
+------+--------+------+

mysql> select * from staff order by age desc limit 3;
+------+--------+------+
| no   | name   | age  |
+------+--------+------+
|    4 | sato   |   60 |
|    5 | tanaka |   55 |
|    3 | yamaki |   44 |
+------+--------+------+

18. count(*)で行数を取得

select count(*) from テーブル名で行数を取得可能。

mysql> select count(*) from staff;
+----------+
| count(*) |
+----------+
|        5 |
+----------+

19. sum()で合計値を取得

mysql> select sum(age) from staff;
+----------+
| sum(age) |
+----------+
|      211 |
+----------+

20. avg()で平均値を取得

mysql> select avg(age) from staff;
+----------+
| avg(age) |
+----------+
|  42.2000 |
+----------+

21. max()で最大値を取得

mysql> select * from staff where age=(select max(age) from staff);
+------+------+------+
| no   | name | age  |
+------+------+------+
|    4 | sato |   60 |
+------+------+------+

22. min()で最小値を取得

mysql> select * from staff where age=(select min(age) from staff);
+------+-------+------+
| no   | name  | age  |
+------+-------+------+
|    2 | kasai |   20 |
+------+-------+------+

23. group byでグループ分け

mysql> select * from meibo;
+------+--------+------+
| id   | gender | age  |
+------+--------+------+
|    1 | male   | 34   |
|    2 | female | 22   |
|    3 | male   | 18   |
|    4 | male   | 44   |
|    5 | female | 55   |
|    6 | female | 27   |
+------+--------+------+

mysql> select gender, round(avg(afe), 2) from meibo group by gender;
+--------+---------------------+
| gender | round(avg(age), 2)  |
+--------+---------------------+
| female |               34.67 |
| male   |               32.00 |
+--------+---------------------+

24. datediff(A, B)でB - Aの日数取得

もしも終了日時を開始日時で引いてマイナスの日数が表示された場合は指定ミス。

mysql> select * from dterm;
+------+---------------------+---------------------+
| id   | date_start          | date_end            |
+------+---------------------+---------------------+
|    1 | 2019-12-15 00:00:00 | 2019-12-18 00:00:00 |
|    2 | 2019-12-20 00:00:00 | 2019-12-10 00:00:00 |
|    3 | 2019-12-24 00:00:00 | 2019-12-26 00:00:00 |
+------+---------------------+---------------------+

mysql> select *, datediff(date_end, date_start) as diff from dterm;
+------+---------------------+---------------------+------+
| id   | date_start          | date_end            | diff |
+------+---------------------+---------------------+------+
|    1 | 2019-12-15 00:00:00 | 2019-12-18 00:00:00 |    3 |
|    2 | 2019-12-20 00:00:00 | 2019-12-10 00:00:00 |  -10 |
|    3 | 2019-12-24 00:00:00 | 2019-12-26 00:00:00 |    2 |
+------+---------------------+---------------------+------+

25. timestampdiff(hour, A, B)でAとBのhourの差を見る

日数ではなく何時間の差があるか調べる場合はtimestampdiff(hour, A, B)で取得する。

mysql> select *, timestampdiff(hour, date_start, date_end) as diff from dterm;
+------+---------------------+---------------------+------+
| id   | date_start          | date_end            | diff |
+------+---------------------+---------------------+------+
|    1 | 2019-12-15 00:00:00 | 2019-12-18 00:00:00 |   72 |
|    2 | 2019-12-20 00:00:00 | 2019-12-10 00:00:00 | -240 |
|    3 | 2019-12-24 00:00:00 | 2019-12-26 00:00:00 |   48 |
+------+---------------------+---------------------+------+

26. dateformatで日時の表示形式を指定

mysql> select id, date_format(date_start, '%Y年%m月%d日') as 日時 from dterm;
+------+-------------------+
| id   | 日時              |
+------+-------------------+
|    1 | 2019年12月15日    |
|    2 | 2019年12月20日    |
|    3 | 2019年12月24日    |
+------+-------------------+

27. dateformatで日時を曜日付きで表示

ちなみに見た目は悪いが%WでSunday, Mondayのように英語の曜日が取得できる。

mysql> select id, date_format(date_start, '%Y年%m月%d日(%W)') as day from dterm;
+------+----------------------------+
| id   | day                        |
+------+----------------------------+
|    1 | 2019年12月15日(Sunday)     |
|    2 | 2019年12月20日(Friday)     |
|    3 | 2019年12月24日(Tuesday)    |
+------+----------------------------+

caseを併用すれば(日)のような表示もできるがかなり長くなってしまう。

mysql> select id,
    -> (case date_format(date_start, '%w') 
    ->   when 0 then concat(date_format(date_start, '%Y年%m月%d日('), '日)')
    ->   when 1 then concat(date_format(date_start, '%Y年%m月%d日('), '月)')
    ->   when 2 then concat(date_format(date_start, '%Y年%m月%d日('), '火)')
    ->   when 3 then concat(date_format(date_start, '%Y年%m月%d日('), '水)')
    ->   when 4 then concat(date_format(date_start, '%Y年%m月%d日('), '木)')
    ->   when 5 then concat(date_format(date_start, '%Y年%m月%d日('), '金)')
    ->   when 6 then concat(date_format(date_start, '%Y年%m月%d日('), '土)')
    -> end) as day
    -> from dterm;
+------+------------------------+
| id   | day                    |
+------+------------------------+
|    1 | 2019年12月15日(日)     |
|    2 | 2019年12月20日(金)     |
|    3 | 2019年12月24日(火)     |
+------+------------------------+

28. テーブルを結合

mysql> select * from staff;
+------+--------+------+
| no   | name   | age  |
+------+--------+------+
|    1 | iwabe  |   32 |
|    2 | kasai  |   20 |
|    3 | yamaki |   44 |
|    4 | sato   |   60 |
|    5 | tanaka |   55 |
+------+--------+------+

mysql> select * from toeic;
+------+-------+
| id   | score |
+------+-------+
|    1 |   640 |
|    2 |   400 |
|    3 |   550 |
|    4 |   800 |
|    5 |   720 |
+------+-------+

mysql> select * from staff 
    -> right join toeic on staff.no = toeic.id;
+------+--------+------+------+-------+
| no   | name   | age  | id   | score |
+------+--------+------+------+-------+
|    1 | iwabe  |   32 |    1 |   640 |
|    2 | kasai  |   20 |    2 |   400 |
|    3 | yamaki |   44 |    3 |   550 |
|    4 | sato   |   60 |    4 |   800 |
|    5 | tanaka |   55 |    5 |   720 |
+------+--------+------+------+-------+

29. セパレーターを指定して連結

連結する際のセパレーターの文字が共通の場合はconcatではなくconcat_wsを使用する。

mysql> select concat_ws('-', n1, n2, n3) as number from phones;
+--------------+
| number       |
+--------------+
| 90-1111-1111 |
| 90-2222-2222 |
| 90-3333-3333 |
+--------------+

30. 文字数をカウント

全角・半角を1文字でカウントするならchar_lengthを使用する。

mysql> select char_length('あいうabc');
+-----------------------------+
| char_length('あいうabc')      |
+-----------------------------+
|                           6 |
+-----------------------------+

バイト数はlengthを使用する。

全角ひらがなは3なので'あいうabc'は3*3+1*3=12となる。

mysql> select length('あいうabc');
+------------------------+
| length('あいうabc')      |
+------------------------+
|                     12 |
+------------------------+

31. 数字を指定した桁だけ0でうめる

いわゆるゼロパディング。もちろん'0'以外も指定可能。

mysql> select lpad(1, 3, '0');
+-----------------+
| lpad(1, 3, '0') |
+-----------------+
| 001             |
+-----------------+

32. 文字列の左から○文字表示

leftを使用する。substringではないため注意。

mysql> select left('foobar', 3);
+-------------------+
| left('foobar', 3) |
+-------------------+
| foo               |
+-------------------+

33. ランダムで1つだけ表示

order by rand()とlimitを併用する。

mysql> select * from staff order by rand() limit 1;
+------+-------+------+
| no   | name  | age  |
+------+-------+------+
|    2 | kasai |   20 |
+------+-------+------+

mysql> select * from staff order by rand() limit 1;
+------+--------+------+
| no   | name   | age  |
+------+--------+------+
|    5 | tanaka |   55 |
+------+--------+------+

34. concatの代わりに||で結合

MySQLは文字列を+で結合できないためconcatを一般的には使用するが、sql_modeに'PIPES_AS_CONCAT'を指定すれば||で文字列を結合できる。

mysql> set global sql_mode = 'PIPES_AS_CONCAT';
mysql> select 'foo' || 'bar';
+----------------+
| 'foo' || 'bar' |
+----------------+
| foobar         |
+----------------+

35. databaseの書き出すと読み込み

mysqldump -u ユーザー名 -p データベース名 > sample.dump

mysql -u ユーザー名 -p < sample.dump

36. .sqlファイルから実行

ログインしていない場合

$ mysql < /tmp/sample.sql

ログインしている場合

mysql> source /tmp/sample.sql

37. trimで不要な前後の半角スペース除去

mysql> select trim(' foo ');
+---------------+
| trim(' foo ') |
+---------------+
| foo           |
+---------------+

38. メールアドレスのドメイン取得

mysql> select substring_index('foo@gmail.com', '@', -1);
+-------------------------------------------+
| substring_index('foo@gmail.com', '@', -1) |
+-------------------------------------------+
| gmail.com                                 |
+-------------------------------------------+

39. MySQLのバージョン取得

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.18    |
+-----------+

40. テーブルをテキストファイルに書き出す

テーブルをテキストファイルに書き出すときはinto outfileを使用する。

mysql> select * from staff into outfile '/private/tmp/staff.txt'

brew install mysqlでインストールした際は別途書き出し先を設定をしていないとinto outfileを使用しても以下のようなエラーが表示されてしまう；

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

このエラーが発生する場合はSELECT @@global.secure_file_priv;でNULLが表示されるため、/usr/local/etc/my.cnfに保存先を下記のように追記する必要がある。

mysql> SELECT @@global.secure_file_priv;
+---------------------------+
| @@global.secure_file_priv |
+---------------------------+
| NULL                      |
+---------------------------+

[mysqld]
# Only allow connections from localhost
bind-address = 127.0.0.1
secure-file-priv = "/tmp"

追記後はmysql.server restartで再起動すれば保存先が表示される。

mysql> SELECT @@global.secure_file_priv;
+---------------------------+
| @@global.secure_file_priv |
+---------------------------+
| /private/tmp/             |
+---------------------------+