LoginSignup
1083
1134

More than 1 year has passed since last update.

@CyberMergina

よく使うMySQLコマンド&構文集

Last updated at Posted at 2015-10-22

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

MySQLモニター

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

ログイン

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

1083
1134
2

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1083
1134