MySQLコマンド(早見表)

2021/09/14

普段、MySQLコマンドを触ることがないので、いざ使うときにコマンドを忘れて困ったりします。

そんなときのために、今回は MySQLコマンドの早見表をエントリーします。

その前に。。はじめてWindows のローカル環境で MySQL をコマンドで操作する場合は、はじめに環境変数の PATH の設定が必要です。

あらかじめ、PATH で実行ファイルの場所を指定しておくと、どのディレクトリからも MySQL.exe が実行可能となります。

PATHの設定の仕方ですが、以下のサイトがとてもわかりやすかったです。

初心者のためのMySQL講座~接続編~

 
INDEX

 

ログイン


$ mysql -u ユーザ名 -p

# root でログイン
$ mysql -u root

# ユーザ名:default パスワード:secret でログイン
$ mysql -u default -psecret

# ユーザ名:default パスワード:secret DB名:laravel でログイン
$ mysql -u default -psecret laravel

-p オプションを使ってパスワードを指定する場合は、 -p とパスワードの間に空白をいれずに指定します。

 

ユーザ操作

ユーザー作成


CREATE USER `user_name`@`host_name` IDENTIFIED BY 'password';

チェックシングルクォートとバッククォートの使い分けを理解しておきましょう。
詳細は以下のページをご確認ください。

 

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


MySQLでは、GRANT文でユーザの作成とそのユーザに対する権限付与を同時に行います。


# 構文
GRANT ALL PRIVILEGES ON データベース名.* to ユーザー名@localhost IDENTIFIED BY 'パスワード';

# 実例 データベース名:laravel_dev / ユーザ名:username / パスワード:password
GRANT ALL PRIVILEGES ON laravel_dev.* TO username@localhost IDENTIFIED BY 'password';

 

ユーザー削除


DROP USER user_name@host_name;

 

ユーザーパスワード変更


# この構文は MySQL8.0 ではドキュメントから姿を消しています。
# MySQL のバージョンアップを行う際には気をつけてください。
SET PASSWORD FOR root@localhost=password('新パスワード');

 

データベース操作

データベース一覧表示


SHOW DATABASES;

 

新規作成


CREATE DATABASE データベース名;

# 実例 laravel_devで文字エンコーディングはUTF-8で統一
CREATE DATABASE `laravel_dev` DEFAULT CHARACTER SET utf8;

 

文字セット

既に作成済みのデータベース名を指定し、属性の変更を行えます。


ALTER DATABASE db_name CHARACTER SET utf8;

 

使用するデータベースを選択


USE db_name

 

データベースの削除


DROP DATABASE db_name;

 

照合順序の確認


SHOW TABLE STATUS FROM データベース名

 

テーブル操作

テーブル一覧を表示


SHOW TABLES;

 

カラム一覧を表示


DESC table_name;

 

テーブルを空にする


DELETE FROM テーブル名;

// id 103 から 1080 まで削除
DELETE FROM users WHERE id between 103 and 1080;

 

テーブルの削除


DROP TABLE table_name;

 

カラムの追加


# 書式
ALTER TABLE {テーブル名} ADD COLUMN {新規カラム名} {カラムの型情報 オプション};

# 例
ALTER TABLE users ADD COLUMN nickname varchar(255) NOT NULL;

ポイント
追加するカラムの順序を指定

「FIRST」を使えば一番最初のカラムに、「AFTER」を使えば指定カラムの後に新規カラムが追加されます。


ALTER TABLE {テーブル名} ADD {新規カラム名} {カラムの型情報} AFTER {既存カラム名};

 

カラムの削除


# 書式
ALTER TABLE `{テーブル名}` DROP COLUMN `{カラム名}`;

# 例
ALTER TABLE `users` DROP COLUMN `nickname`;

 

レコードの操作

取得するデータの行数の上限を設定する(LIMIT句)


// 最大で5つのデータを取得する
SELECT * FROM user LIMIT 5;

// 3番目のデータから最大で5つのデータを取得する
SELECT * FROM user LIMIT 2,5;

 

INSERT文


### 一般的な使い方
INSERT INTO テーブル名 (カラム1の名前, カラム2の名前, ...) VALUES (カラム1の値, カラム2の値, ...);

# 実例
INSERT INTO users (name, email) VALUES ('山田', 'yamada@example.com');
INSERT INTO users (name, email) VALUES ('山田', 'yamada@example.com'), ('山本', 'yamamoto@example.com');

### 一般的な使い方(省略形)
# 全カラムに順番に値をセットする場合は、カラム名を省略できる
INSERT INTO テーブル名 VALUES (カラム1の値, カラム2の値, ...);

# 実例
INSERT INTO users VALUES ('山田', 'yamada@example.com');
INSERT INTO users VALUES ('山田', 'yamada@example.com'), ('山本', 'yamamoto@example.com');

### SETを使った書式
# 個人的にはこの書式で記述することが多い
INSERT INTO テーブル名 SET カラム1の名前=カラム1の値, カラム2の名前=カラム2の値,・・・;

# 実例
INSERT INTO users SET name = '山田', email = 'yamada@example.com';

 
全てのカラムに値を指定する場合に限ってカラムの記述を省略することができます。


USE db_name;
INSERT INTO tbl_name VALUES (value1, value2,...);

 

UPDATE文


### 構文
UPDATE テーブル名 SET カラム名=値;
UPDATE テーブル名 SET カラム名1=値1, カラム名2=値2, ...

### 実例
UPDATE uriage SET price = '1000' WHERE id = '10';
UPDATE contents SET pub_date = '2021-03-15' WHERE pub_date = '2021-04-01';
UPDATE users SET grade = '2年生' WHERE grade = '1年生';

 

DELETE文

idカラムの値が10のデータを削除する


### 構文
DELETE FROM テーブル名 WHERE 条件;

### 実例
DELETE FROM users WHERE id = 10;
DELETE FROM users WHERE age >= 25;

 

制約

ユニーク

カラムの値を重複させない(一意性)制約です。


CREATE TABLE テーブル名( id INT UNIQUE, name VARCHAR(10));

 

NOT NULL

カラムに格納する値として NULL を禁止したい場合に使用します。


ALTER TABLE テーブル名(カラム名 NOT NULL, ...);

 

PRIMARY KEY(主キー)

PRIMARY KEY 制約は主キー制約とも呼ばれ、レコードの識別に利用されるカラムに指定する制約です。
UNIQUE制約NOT NULL制約を合わせた制約です。


CREATE TABLE テーブル名(カラム名 PRIMARY KEY, ...);

 

FOREIGN KEY(外部キー)

FOREIGN KEY制約は外部キー制約とも呼ばれています。関連したテーブル間を結ぶために設定する列のことです。
データの整合性をデータベースに保証させるために利用します。

 
親テーブル(参照される側のテーブル)


CREATE TABLE 親テーブル名(親カラム名 データ型) ENGINE=InnoDB;

 
子テーブル(参照する側のテーブル)


CREATE TABLE 子テーブル名(子カラム名 データ型,
  FOREIGN KEY(子カラム名)
  REFERENCES 親テーブル名(親カラム名)
) ENGINE=InnoDB;

-- 例
create table sales(
  id int, name varchar(10), d date, index(name),
  foreign key(name)
  references goods(name)
) engine=InnoDB;

 

インデックス

 
Indexを作成するべき列の特長
1)テーブル内のデータ量が多く、少量のレコードを検索する場合
2)WHERE句の条件、結合の条件、ORDER BY 句の条件として頻繁に利用する場合
3)NULL値が多いデータから、NULL値以外の検索をする場合


ALTER TABLE テーブル名 ADD INDEX インデックス名(カラム名);
-- 書式例
ALTER TABLE db_01.USER_DATA ADD INDEX index01(user_id);

 

検索

LIKE検索+複数


SELECT * FROM テーブル名 Where カラム名 like ('%特定の値%') OR カラム名 like ('%特定の値%');

 

特定の値

MySQLでデータを抽出するときに、特定のデータを抽出する


// 単数
SELECT * FROM テーブル名 WHERE カラム名 IN("特定の値");
// 複数の場合
SELECT * FROM テーブル名 IN カラム名("特定の値","特定の値","特定の値");

 

特定の値以外

MySQLでデータを抽出するときに、特定の値以外のデータを抽出する


SELECT * FROM テーブル名 WHERE カラム名 NOT IN("特定の値");

 

結合

内部結合


# 基本構文
SELECT カラム名1, カラム名2 FROM テーブル名1 INNER JOIN テーブル名2 ON 結合の条件;

# 例
SELECT * FROM dogs INNER JOIN owners ON dogs.owner_id = owners.id;

 

両方のテーブルにあるデータのみを取り出すのが内部結合です。
2つのテーブルにあって、合体できるデータのみ を取り出します。
 
どちらかのテーブルにデータがあれば取り出すのが外部結合です。
1つのテーブルしかなくて、合体しないデータも 取り出します。

 

内部結合 + 条件


SELECT * From テーブル名1 
INNER JOIN テーブル名2
 ON テーブル名1.カラム名1 = テーブル名2.カラム名2
WHERE テーブル.カラム > 3000; -- ※ WHERE句のあとは抽出条件

 

3つのテーブル結合


SELECT テーブル名.カラム名, ... -- ※ 取得したいカラム名
FROM テーブル名1 
INNER JOIN テーブル名2
 ON テーブル名1.カラム名1 = テーブル名2.カラム名2 
INNER JOIN テーブル名3
 ON テーブル名1.カラム名1 = テーブル名3.カラム名3 

 

4つのテーブル結合


SELECT
 o.id,
 u.first_name,
 o.amount,
 p.name
FROM
 `orders` o
INNER JOIN `details` d
  ON o.id = d.order_id
INNER JOIN `product` p
  ON d.product_id = p.id
INNER JOIN `users` u
  ON o.user_id = u.id;

基本は INNER JOIN を繋げていくだけです。
テーブル名は別名をつけておくと解読しやすいです。

 

エクスポート&ダンプ&インポート(sqlファイル)

エクスポート


# 構文
mysql> SELECT * FROM テーブル名 INTO OUTFILE '出力先ファイルパス';
# 実例
mysql> SELECT * FROM favorites INTO OUTFILE '/var/lib/mysql-files/favorite.csv';

【 現在の設定を確認 】
MySQL にログインした状態で以下のコマンドを使用して、ファイルの作成権限を確認。
もし値が NULL の場合は、どこへの出力も許可していません。
また、この値を空文字 ("") に変更すると、どこにでもファイルを出力できるようにできます。


mysql> SELECT @@global.secure_file_priv;


 
もしこのフォルダを変更したい場合は、my.ini もしくは my.cnf に次のような設定を行い、MySQLサーバーを再起動することで変更できます。


:
[mysqld]
# /tmp 以下ならCSVエクスポートできる
secure-file-priv="/tmp"

# 以下のように値を空文字にすると、どこにでもファイルを出力できる
secure-file-priv=""
:

【 MySQLのCSV出力が権限によってできない場合の対処法 】
CSVファイルを出力したくても、環境によってはユーザ権限の問題などでエラーが発生するケースもあります。

 
出力結果をパイプするやり方だと制限がないので上手く行きます。


# 出力結果をパイプで繋げる
$ mysql -u ユーザ名 -p DB名 -e 'SELECT * FROM テーブル名' | sed -e 's/\t/,/g' | iconv -c -f utf8 -t sjis > '/tmp/ファイル名.csv';

# INNER JOIN で CSV 出力
# テーブル名はバッククォートで括る
$ mysql -u ユーザ名 -p DB名 -e 'SELECT * FROM テーブル名① INNER JOIN テーブル名② ON テーブル名①.カラム名① = テーブル名②.カラム名②' | sed -e 's/\t/,/g' | iconv -c -f utf8 -t sjis > '/tmp/ファイル名.csv';

 

ダンプ


# 全テーブル
mysqldump -u ユーザ名 -p DB名 > ダンプファイル名.sql
# テーブル指定
mysqldump -u ユーザ名 -p -t DB名 テーブル名 > ダンプファイル名.sql

 

インポート(sqlファイル)


# 全テーブル/テーブル指定
$ mysql -u ユーザ名 -p DB名 < ダンプファイル名.sql

# 実例
$ mysql -u homestead -psecret DB名 < C:\\tmp\\20210402users.sql

# エラーが出て上手くいかない場合(文字コードの問題)
#  ⇒「ERROR at line 2790: Unknown command '\".」
$ mysql -u homestead -psecret DB名 --default-character-set=utf8 < C:\\tmp\\20210402users.sql

テーブルだけのインポートでも -t は要りません。

 

集計


# 基本
SELECT COUNT(*) FROM users;

# カラムに別名をつける
SELECT COUNT(*) AS COUNT FROM users;

# 絞り込み条件
SELECT count(*) AS COUNT FROM users WHERE status = 1;

 

Laravelアプリ専用のユーザとDB作成


#DBにログイン
[root@example ~]#mysql -u root -p

# DBの作成
mysql> CREATE DATABASE laravel_db;

# DBユーザの作成
# パスワードの箇所はバッククォートではなくシングルクォートで囲む
mysql> CREATE USER `laravel_user`@`localhost` IDENTIFIED BY 'laravel_pass';

# 権限の割り当て
mysql> GRANT ALL PRIVILEGES ON `laravel_db`.* TO `laravel_user`@`localhost`;

MySQL5.7.8以降では脆弱なパスワードは以下のように怒られてしまいます。
実際にはもっと複雑なパスワード(8文字以上+数字+大文字+小文字+特殊文字)を設定してください。

 

auto_increment(オートインクリメント)の連番をリセット

既存のレコードが残っているままの状態でも OK です。


# 変数セット
SET @i := 0;

# 1行目で指定した数値 +1 から番号が振られるので、1 から連番が振られる
UPDATE テーブル名 SET カラム名 = (@i := @i +1);

 

 

一覧を表示する順番を指定

ORDER BY で並び替え
DEC は小さくなっていく(降順)
ASC は大きくなっていく(昇順)
ASC は省略可能


# 新しい順
mysql> SELECT * FROM users ORDER BY created_at DESC;

# 古い順
mysql> SELECT * FROM users ORDER BY created_at ASC;

 

CSVファイルをデータベースにインポート


# 構文
LOAD DATA LOCAL INFILE 'ファイル名' INTO TABLE テーブル名 FIELDS TERMINATED BY '区切り文字' OPTIONALLY ENCLOSED BY ''囲み文字;

 

FIELDS TERMINATED BY で指定する「区切り文字」は、フィールドの区切り文字です。CSVの場合は「,」(コンマ)になります。

・CSVではフィールドの値が「"」(ダブルクォート)で囲まれていることがあるので、 OPTIONALLY ENCLOSED BY の「囲み文字」に「"」を指定します。

 

1)ローカル環境でファイルが見つからない(Windows)

→ Windows上でのファイルパスの書き方

LOAD DATA LOCAL INFILE "C:\\tmp\\users.csv" INTO TABLE contents FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"';

 

2)ERROR 2 (HY000): File 'ファイル名' not found (Errcode: 2)

→ CSVファイル、またはそれが置かれているディレクトリに付与されている権限によって、MySQLユーザからファイルが見えない状態になっている

・ファイル事態に、他ユーザーから見える権限を付与する
・ファイルを他ユーザーから見える場所に移動する(/tmp とかでOK)

【 インポートするCSVで気を付けること 】
・「id」カラム(AUTO_INCREMENT)のところは空にする
・UTF-8 BOM 無しで保存
・先頭にカラム名があったら削除しておく

 

MySQLコマンド履歴を見たい

Linux の history コマンドのように一覧表示したい場合。


# ホームディレクトリに移動
$ cd ~

# .mysql_history をテキストエディタで開く
$ less -iNM .mysql_history

 

【 point 】
・mysql_history というファイルをテキストエディタで開く
・mysql_history はホームディレクトリ直下にあることが多い

 
何か気づいたら、随時追加予定です。

本庄マサノリ

仕事で Laravel を使っています。気づいたことや新しい発見など情報を発信していきます。問い合わせはこちら

>> Twitter をフォローする

 

-周辺知識