サーバー屋以外でも使えるSQL コマンド閲覧編 #SQL

SQLとは

RDBMSにおけるデータ操作や定義を行うための問い合わせ言語
DBを使用するアプリケーションは全て、裏でこれを打っている
コマンドラインでこれを操作できると運用管理が爆速になる
直接DBを触らないクライアントエンジニアでも、閲覧コマンドを覚えればいちいち担当者に問い合わせをしなくて済む

使い方

用意

今回はWordpressサイトを立ち上げてDBを覗いてみましょう。

docker-composeはこちら。
基本的に公式が公開しているものにMySQLへのポートを開けただけです。
これ書いてる時別の開発で3306番を使ってたんでポートは3307で。

$ docker-compose run

接続

$ mysql -u {USER_NAME} -p -h {接続先ホスト} -P {接続先ポート番号(3306)}

よく使うオプションは大体これだけ。
-p{PASSWORD} みたいに -p オプションとの間に スペースを入れずにパスワードを入れれば 、後からパスワードを聞かれませんが、セキュリティの観点から基本的に非推奨。

$ mysql -u wordpress -pwordpress -h 127.0.0.1 -P 3307

$ mysql -u wordpress -p -h 127.0.0.1 -P 3307
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.25 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]>

存在しているデータベース一覧を確認する

正確に言えば、接続ユーザーに許可されているデータベースの一覧ですが、rootユーザーなら全てが見れます。

MySQL [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| wordpress          |
+--------------------+
2 rows in set (0.001 sec)

データベースを選択

使用するデータベースを選択する。

MySQL [(none)]> use wordpress
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MySQL [wordpress]>

検索

テーブル一覧の確認

選択したデータベースにどんなテーブルが存在しているかの確認。
これも正確にいえば権限が与えられているもの。

MySQL [wordpress]> SHOW TABLES;
+-----------------------+
| Tables_in_wordpress   |
+-----------------------+
| wp_commentmeta        |
| wp_comments           |
| wp_links              |
| wp_options            |
| wp_postmeta           |
| wp_posts              |
| wp_term_relationships |
| wp_term_taxonomy      |
| wp_termmeta           |
| wp_terms              |
| wp_usermeta           |
| wp_users              |
+-----------------------+
12 rows in set (0.001 sec)

テーブル構造の確認

SHOW CREATE TABLE {テーブル名}

MySQL [wordpress]> SHOW CREATE TABLE wp_users;
+----------+-------------------------------------------------------------------------
| wp_users | CREATE TABLE `wp_users` (
  `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_login` varchar(60) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
  `user_pass` varchar(255) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
  `user_nicename` varchar(50) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
  `user_email` varchar(100) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
  `user_url` varchar(100) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
  `user_registered` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `user_activation_key` varchar(255) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
  `user_status` int(11) NOT NULL DEFAULT '0',
  `display_name` varchar(250) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`ID`),
  KEY `user_login_key` (`user_login`),
  KEY `user_nicename` (`user_nicename`),
  KEY `user_email` (`user_email`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci |
+----------+-----------------------------------------------------------------------------
1 row in set (0.001 sec)

データ検索

テーブル全てを取得する
一番簡単なやつ。レコード数が少ないテーブルとかに使用する。

SELECT * FROM {テーブル名} LIMIT 100;

MySQL [wordpress]> SELECT * FROM wp_terms LIMIT 100;
+---------+-----------------------+-----------------------------------------------------------------+------------+
| term_id | name                  | slug                                                            | term_group |
+---------+-----------------------+-----------------------------------------------------------------+------------+
|       1 | Uncategorised         | uncategorised                                                   |          0 |
|       2 | #お礼                 | uncategorised                                                   |          0 |
|       3 | ホスピタリティ        | %e3%83%9b%e3%82%b9%e3%83%94%e3%82%bf%e3%83%aa%e3%83%86%e3%82%a3 |          0 |
|       4 | #Integrity            | integrity                                                       |          0 |
|       5 | #Delivery             | delivery                                                        |          0 |
|       6 | #神アプデ             | %e7%a5%9e%e3%82%a2%e3%83%97%e3%83%87                            |          0 |
+---------+-----------------------+-----------------------------------------------------------------+------------+
6 rows in set (0.001 sec)

条件つき検索

おそらく一番使うタイプ。

SELECT * FROM {テーブル名} WHERE {ターゲットにしたいカラム名} = {ターゲットの値};

MySQL [wordpress]> SELECT * FROM wp_terms WHERE term_id = 3;
+---------+-----------------------+-----------------------------------------------------------------+------------+
| term_id | name                  | slug                                                            | term_group |
+---------+-----------------------+-----------------------------------------------------------------+------------+
|       3 | ホスピタリティ        | %e3%83%9b%e3%82%b9%e3%83%94%e3%82%bf%e3%83%aa%e3%83%86%e3%82%a3 |          0 |
+---------+-----------------------+-----------------------------------------------------------------+------------+
1 row in set (0.000 sec)

MySQL [wordpress]> SELECT * FROM wp_terms WHERE name = 'ホスピタリティ';
+---------+-----------------------+-----------------------------------------------------------------+------------+
| term_id | name                  | slug                                                            | term_group |
+---------+-----------------------+-----------------------------------------------------------------+------------+
|       3 | ホスピタリティ        | %e3%83%9b%e3%82%b9%e3%83%94%e3%82%bf%e3%83%aa%e3%83%86%e3%82%a3 |          0 |
+---------+-----------------------+-----------------------------------------------------------------+------------+
1 row in set (0.001 sec)

いろいろな条件

WHEREで使える条件式は何種類かあります。

これらは全て同義のもの。

SELECT * FROM wp_posts WHERE id > 1 AND id <= 5;
SELECT * FROM wp_posts WHERE id IN (2,3,4,5);
SELECT * FROM wp_posts WHERE id BETWEEN 2 AND 5;
SELECT * FROM wp_posts WHERE id=2 OR id=3 OR id=4 OR id=5;

検索カラムの制限

見づらい・・・

そんなときは、SELECT {表示カラム} FROM {テーブル名} WHERE ~~~~; で見たいカラムだけを選択する。

気をつけるべきこと

ここまでが基本的なSELECTの方法。
実務でデータ処理する場合は注意すべき点がいくつかあります。

SELECTは常に最低限の範囲にする
時間がかかるクエリは打たない
用が済んだらすぐに切断する

1. SELECTは常に最低限の範囲にする

実際の運用では一つのテーブルに数億レコードが収められているという場合が往々にして存在します。
そこまで巨大なテーブルとなるとデータ量だけで数ギガバイトの容量。
これを出力するだけでDBにそこそこの負荷をかけたり、帯域を消費してしまいます。
何よりそんな大量のデータを人が見てどうこうできるわけがない。

例えば…

SELECT * FROM wp_posts WHERE id = 5; #必要なレコードだけ出力
SELECT * FROM wp_posts LIMIT 10; #表示するレコード数を10までに制限する

特に LIMIT はなるべくつける癖をつけたほうがいいです。
間違えて打ってしまった場合、一応クエリの途中で Ctrl + cで中止することもできる。

2. 時間がかかるクエリは打たない

前のとかぶっている部分もありますが、処理に時間がかかるクエリは極力避ける。

同時に捌けるクエリ数には上限があります。
うっかり時間がかかるクエリを打ってしまうと、DBを専有してしまうことになる。

例えば…

巨大なテーブルに対する複雑なWHERE条件
巨大なテーブルに対するインデックスが効かないクエリ
巨大なテーブルに対するあれこれ

相手が巨大な時ほど注意

3. 用が済んだらすぐに切断する

意図しないクエリを防ぐのと、コネクション数を節約するため。
DBには max_connections という設定項目があり、同時接続数に上限があります。
これを浪費しないようにしましょう。

終了は Ctrl + c や、 exit コマンドを使います。

MySQL [wordpress]> exit
Bye

$

結論

判断の基準となるのは、極力DBに負荷をかけないということです。
ローカルに立てている自分だけしか使用しないDBなら好きにしてもらえばいいですが、
開発テスト環境でも複数人での使用が考えられますし、本番環境ならいわずもがな。

SQLを打つときは、常に自分に問いかけてください。 このクエリはDBにやさしいか？と。

まあ、慣れないうちはGUIツール使ったほうがいいですよ