今までGUIでやっていたのですが、やっぱり覚えていた方がかっこいいなということで
個人的なメモ程度によく使うMySQLコマンドを載せていきます。
随時更新します～～
※ MySQL8には一部対応してないのでご注意ください

MySQLモニター

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

# localhostのMySQLサーバに接続する場合
$ mysql -u [ユーザー名] -p

# localhostのMySQLサーバに接続する場合（ワンラインでパスワードまで渡す）
$ mysql -u [ユーザー名] -p[パスワード ※ 平文で渡すとコマンド履歴にパスワードが載ってしまうので避けましょう]

# 外部MySQLサーバに接続する場合
$ mysql -u [ユーザー名] -p -h [host名] -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 ※ MySQL8以上では
ホスト名：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 > SELECT table_name, column_name FROM information_schema.columns WHERE column_name = [検索条件];

テーブルの作成

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;

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

テーブルの削除

mysql-shell

mysql > DROP TABLE [テーブル名]

TABLEが存在しない時にエラーで止めたくなければ、
下記のように IF EXISTS を追加することでTABLEが存在する時のみ DROP TABLE 文を実行するようにできます。

mysql-shell

mysql > DROP TABLE IF EXISTS [テーブル名]

テーブル名の変更

mysql-shell

mysql > ALTER TABLE [旧テーブル名] RENAME [新テーブル名]

テーブルにカラムの追加

mysql-shell

mysql > ALTER TABLE [テーブル名] ADD [追加カラム名] [型] [必要であればオプション等];

# 例(動作確認してないのであやふやですがこんな感じ)
#  users に tel という int型のカラム を デフォルトNULL で定義し、コメントには 電話番号 といれておき、 mail_addressカラムの後ろ に追加する
mysql > ALTER TABLE users ADD tel int DEFAULT NULL COMMENT "電話番号"  AFTER mail_address

テーブル設計の確認

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 [フィールド名]=[値] WHERE [条件式]

＜サンプル＞

mysql-shell

mysql > UPDATE m_users SET user_name="Qii Takao", mail_address="qiitakao@hoge.com" WHERE id = 5;

『WHERE [条件式]』が無しだと、対象が全レコードになります。
カンマ区切りで複数フィールド更新できます。

削除

全レコード削除

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;

トランザクション

デフォルトではクエリの実行が即座にコミット（反映）されるようになっています。
ただ、システム運用をしている上でクエリを実行する必要が出てくるシーンに遭遇することもあると思います。

トランザクションの機能を活用すると、実際にUPDATEなどのクエリを実行してSELECTで結果を確認したあとに、
　意図通りに変更できていれば、反映
　意図通りに変更できてなければ、反映させない
ということができます。

トランザクションはCOMMIT/ROLLBACKをすると終了します。

< 例 >

mysql-shell

mysql > START TRANSACTION;
mysql > UPDATE m_users SET user_name="Huga Hogeo", mail_address="huga@hoge.com" WHERE id = 5;
mysql > UPDATE m_users SET user_name="Hoge Hugao", mail_address="hoge@huga.com" WHERE id = 6;
mysql > SELECT * FROM m_users WHERE id IN(5,6);
# ここで状態確認をしたりする
mysql > COMMIT;

詳しくはこちら https://dev.mysql.com/doc/refman/5.6/ja/commit.html
START TRANSACTION;: トランザクションの開始
COMMIT;: コミット

トランザクションの開始

mysql-shell

mysql > START TRANSACTION;

コミット

mysql-shell

mysql > COMMIT;

ロールバック

mysql-shell

mysql > ROLLBACK;

データベースのダンプ

ダンプをとる

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

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が５未満を対象とする

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

MySQLモニター

ログイン

ログアウト

ヘルプ

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

ユーザー情報取得

ユーザーの追加

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

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

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

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

データベース関連

データベース一覧の表示

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

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

テーブル関連

テーブル一覧の表示

全テーブルから特定のフィールド検索

テーブルの作成

テーブルの削除

テーブル名の変更

テーブルにカラムの追加

テーブル設計の確認

レコード操作関連

追加

更新

削除

全レコード削除

一部レコード削除

トランザクション

トランザクションの開始

コミット

ロールバック

データベースのダンプ

ダンプをとる

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

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

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

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

出力ファイル名について

リストア

その他

見え方の変更

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

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

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

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