Edited at

よく使うMySQLコマンド集

今まで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ログイン後)


ユーザー情報取得


mysql-shell

mysql > SELECT Host, User, Password FROM mysql.user;

+------------------+------+----------+
| Host | User | Password |
+------------------+------+----------+
| localhost | root | |
| 127.0.0.1 | root | |
| ::1 | root | |
| localhost | | |
+------------------+------+----------+
4 rows in set (0.00 sec)


ユーザーの追加

ユーザー名: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の出力結果をファイルにはきだしたいとき