MySQL

よく使うMySQLコマンド集

More than 1 year has passed since last update.

今までGUIでやっていたのですが、やっぱり覚えていた方がかっこいいなということで
個人的なメモ程度によく使うMySQLコマンドを載せていきます。
随時更新します~~

MySQLモニター

mysqlのCLI。mysqlをコマンドで操作できるCUIツール。っていうのかな

ログイン

shell
$ mysql -u [ユーザー名] -p

-p オプションはパスワード設定しているときのみだったハズ。

< プチ情報 >
・ログイン後はmysqlコマンドしか使えない。
・ログイン中に打つコマンドは基本的に最後に「;(セミコロン)」を入れる。

ログアウト

いろいろとある。

shell
mysql > \q
mysql > quit
mysql > exit

ヘルプ

shell
mysql > help
mysql > \h

ユーザー操作(rootログイン後)

ユーザーの追加

ユーザー名:testuser
パスワード:password
ホスト名:localhost

mysql-shell
mysql > create user `testuser`@`localhost` IDENTIFIED BY 'password';

このままでは「testuser」はDB作成もできないので、権限を付与してあげます(次の項目)

ユーザーにDB操作権限を付与

対象:testuser@localhost
対象のパスワード:password
操作できるDB名:test_db

mysql-shell
mysql > grant all privileges on test_db.* to testuser@localhost IDENTIFIED BY 'password';

ユーザーにパスワードをセットする

ログイン中のユーザーのパスワードを設定する場合

mysql-shell
mysql > set password = password('hogehoge123');

特定のユーザーのパスワードを設定する場合

ユーザー名:testuser
新パスワード:hogehoge123
ホスト名:localhost

mysql-shell
mysql > set password for 'testuser'@'localhost' = password('hogehoge123');

データベース関連

データベース一覧の表示

mysql-shell
mysql > show databases;

データベースの追加(test_dbを追加する場合)

mysql-shell
mysql > create database test_db;

データベースの選択(test_dbを選択する場合)

mysql-shell
mysql > use test_db;

テーブル関連

テーブル一覧の表示

mysql-shell
mysql > show tables;

もっと詳細が知りたければ

mysql-shell
mysql > show table status;

テーブルの作成

mysql-shell
mysql > CREATE TABLE [テーブル名] (
  [フィールド名] [データ型] [オプション]
) ENGINE=[InnoDB/MyISAM] DEFAULT CHARSET=[文字コード];

< サンプル >

mysql-shell
mysql > CREATE TABLE `m_users` (
          `id` int NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT "ID",
          `user_name` VARCHAR(100) NOT NULL COMMENT "ユーザー名",
          `mail_address` VARCHAR(200) NOT NULL COMMENT "メールアドレス",
          `password` VARCHAR(100) NOT NULL COMMENT "パスワード",
          `created` datetime DEFAULT NULL COMMENT "登録日",
          `modified` datetime DEFAULT NULL COMMENT "更新日"
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

括弧以降は書かなくてもおk。
MySQLの設定ファイルできちんと設定できれいれば、
設定された値(もしくはデフォルト値)がセットされるので。

テーブルの削除

mysql-shell
mysql > DROP TABLE [テーブル名]

テーブル設計の確認

mysql-shell
mysql > desc [テーブル名]
+------------------+--------------+------+-----+---------+----------------+
| Field            | Type         | Null | Key | Default | Extra          |
+------------------+--------------+------+-----+---------+----------------+
| id               | int(11)      | NO   | PRI | NULL    | auto_increment |
| user_name        | varchar(100) | NO   |     | NULL    |                |
| mail_address     | varchar(200) | NO   |     | NULL    |                |
| password         | varchar(100) | NO   |     | NULL    |                |
| created          | datetime     | YES  |     | NULL    |                |
| modified         | datetime     | YES  |     | NULL    |                |
+------------------+--------------+------+-----+---------+----------------+

もっと詳細が知りたければ

mysql-shell
mysql > SHOW FULL COLUMNS FROM [テーブル名];
+------------------+--------------+-----------------+------+-----+---------+----------------+---------------------------------+----------------+
| Field            | Type         | Collation       | Null | Key | Default | Extra          | Privileges                      | Comment        |
+------------------+--------------+-----------------+------+-----+---------+----------------+---------------------------------+----------------+
| id               | int(11)      | NULL            | NO   | PRI | NULL    | auto_increment | select,insert,update,references | ID             |
| user_name        | varchar(100) | utf8_general_ci | NO   |     | NULL    |                | select,insert,update,references | ユーザー名     |
| mail_address     | varchar(200) | utf8_general_ci | NO   |     | NULL    |                | select,insert,update,references | メールアドレス |
| password         | varchar(100) | utf8_general_ci | NO   |     | NULL    |                | select,insert,update,references | パスワード     |
| created          | datetime     | NULL            | YES  |     | NULL    |                | select,insert,update,references | 登録日         |
| modified         | datetime     | NULL            | YES  |     | NULL    |                | select,insert,update,references | 更新日         |
+------------------+--------------+-----------------+------+-----+---------+----------------+---------------------------------+----------------+

レコード操作関連

追加

mysql-shell
mysql > INSERT INTO [テーブル名] [フィールド名] VALUES [値]

<サンプル>

mysql-shell
mysql > INSERT INTO m_users (user_name, mail_address, password, created, modified)
          VALUES ("Qii Taro", "qiitaro@hoge.com", "123123", now(), now())

now() ・・・ 現在の日時が入力される関数

更新

mysql-shell
mysql > UPDATE [テーブル名] SET [フィールド名]=[値] [条件式]

<サンプル>

mysql-shell
mysql > UPDATE m_users SET user_name="Qii Takao", mail_address="qiitakao@hoge.com" WHERE id = 5;
  • 条件式が無しだと、対象が全レコードになります。
  • カンマ区切りで複数フィールド更新できます。

削除

全レコード削除

mysql-shell
mysql > DELETE FROM [テーブル名]

一部レコード削除

mysql-shell
mysql > DELETE FROM [テーブル名] WHERE [条件式]

<サンプル>

mysql-shell
mysql > DELETE FROM m_users WHERE id > 5 AND del_flg = 1;

データベースのダンプ

ダンプをとる

全データベースを対象とする

shell
$ mysqldump -u [ユーザー名] -p -x --all-databases > [出力ファイル名]

test_dbデータベースを対象とする

shell
$ mysqldump -u [ユーザー名] -p -x test_db > [出力ファイル名]

test_dbデータベースのusersテーブルを対象とする

shell
$ mysqldump -u [ユーザー名] -p -x test_db users > [出力ファイル名]

test_dbデータベースのusersテーブルのデータ内でidが5未満を対象とする

shell
$ mysqldump -u [ユーザー名] -p -x test_db users --where="id < 5" > [出力ファイル名]

出力ファイル名について

 拡張子は何でもよかったと思います。
 自分は「***.dump」とかにします。

リストア

shell
$ mysql -u[ユーザー名] -p new_db < [ダンプファイル名]

その他

見え方の変更

@ promptコマンドで「mysql > ....」という形をカスタムする

デフォルトで mysql> という形だが、カスタマイズできるらしい。
ほぉ。
こちらのサイト様を参考に書いてみました。

mysql-shell
mysql> \R \d(\U) >\_
PROMPT set to '\d(\U) >\_'
test_db(root@localhost) >

・『/d』⇒ 利用中のデータベース名
・『/U』⇒ ユーザー名@ホスト名

@ pagerコマンドでクエリの結果を見やすくする ~pager less~

pagerというコマンドが便利だとの情報をコメントからいただきましたので、
pager less をさっそく使ってみました。(tukiyo3さんありがとうございます!)

mysql-shell
mysql> pager less -S
PAGER set to 'less -S'
mysql>

この状態で横になが~くなってしまう、テーブル詳細を表示すると
ビューアーが立ち上がり、結果が表示されました!
結果が折り返されず左右矢印キーで操作できます。
ビューアーの終了はqキー、
lessの設定を終わるときは nopager というコマンドで元に戻ります。

参考にさせていただいた記事の方にlessの他のオプションも書かれております。

SQL実行結果をファイルに出力

shell
$ mysql -uroot -p -e "select * from users" test_db > /tmp/mysql.txt

参考サイト様:MySQLの出力結果をファイルにはきだしたいとき