Help us understand the problem. What is going on with this article?

よく使うMySQLコマンド集

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

MySQLモニター

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

ログイン

shell
# 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
ホスト名: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 > 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 [フィールド名]=[値] [条件式]

<サンプル>

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

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした