2022/08/10
普段、MySQLコマンドを触ることがないので、いざ使うときにコマンドを忘れて困ったりします。
そんなときのために、今回は MySQLコマンドの早見表をエントリーします。
その前に。。はじめてWindows のローカル環境で MySQL をコマンドで操作する場合は、はじめに環境変数の PATH の設定が必要です。
あらかじめ、PATH で実行ファイルの場所を指定しておくと、どのディレクトリからも MySQL.exe が実行可能となります。
PATHの設定の仕方ですが、以下のサイトがとてもわかりやすかったです。
INDEX
- ログイン
- ユーザ操作(作成/権限を付与/削除/パスワード変更)
- データベース操作(一覧表示/新規作成/文字セット/選択/削除/照合順序の確認)
- テーブル操作(テーブル一覧表示/カラム一覧表示/テーブルを空/削除/カラムの追加/カラムの削除)
- レコードの操作(LIMIT句/INSERT文/UPDATE文/DELETE文)
- 制約(ユニーク/NOT NULL/PRIMARY KEY/FOREIGN KEY/インデックス)
- 検索(LIKE検索/LIKE検索+複数/特定の値/特定の値以外)
- 結合(内部結合/内部結合+条件/3つのテーブル結合/4つのテーブル結合)
- エクスポート&ダンプ&インポート(sqlファイル)
- 集計
- Laravelアプリ専用のユーザとDB作成
- auto_increment(オートインクリメント)の連番をリセット
- 一覧を表示する順番を指定
- CSVファイルをデータベースにインポート
- MySQLコマンド履歴を見たい
- MySQLで一括置換をかける
- 「IN」をサブクエリとして使う
- テーブル名やカラム名を別名に変更(AS句)
- 副問い合わせ(サブクエリ)
- バージョンの確認
- 起動と停止、再起動
- インストールとアンインストール
ログイン
$ 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文
### 構文
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
ALTER TABLE テーブル名(カラム名 NOT NULL, ...);
PRIMARY KEY(主キー)
UNIQUE制約
と NOT NULL制約
を合わせた制約です。
CREATE TABLE テーブル名(カラム名 PRIMARY 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 パターン;
# 指定したカラム名の値が、ワイルドカードを使ったpターンに一致していないデータを検索できる。
SELECT * FROM テーブル名 WHERE カラム名 NOT LIKE パターン;
ワイルドカードとして使える文字
% | 任意の0文字以上の文字列 |
---|---|
_ | 任意の1文字 |
さまざまなワイルドカードの使い方
本% | 先頭に「本」がつく文字列 |
---|---|
%本 | 末尾に「本」がつく文字列 |
%本% | どこかに「本」がつく文字列 |
本_ | 先頭に「本」がつく2文字の文字列 |
LIKE検索+複数
SELECT * FROM テーブル名 Where カラム名 like ('%特定の値%') OR カラム名 like ('%特定の値%');
特定の値
MySQLでデータを抽出するときに、特定のデータを抽出する
// 単数
SELECT * FROM テーブル名 WHERE カラム名 IN("特定の値");
// 複数の場合
SELECT * FROM テーブル名 IN カラム名("特定の値","特定の値","特定の値");
// 実例
SELECT * FROM fruit WHERE name IN("みかん","りんご");
特定の値以外
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;
# 外部結合
SELECT * FROM dogs LEFT 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;
テーブル名は別名をつけておくと解読しやすいです。
エクスポート&ダンプ&インポート(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=""
:
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';
ダンプ
# 全テーブル
[root@example ~]# mysqldump -u ユーザ名 -p DB名 > ダンプファイル名.sql
# テーブル指定
[root@example ~]# mysqldump -u ユーザ名 -p -t DB名 テーブル名 > ダンプファイル名.sql
su -
コマンドでスーパーユーザ(root)に切り替えておいてください。
インポート(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
集計
# 基本
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`;
auto_increment(オートインクリメント)の連番をリセット
一覧を表示する順番を指定
・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 の「囲み文字」に「"」を指定します。
MySQLコマンドについての備忘録(CSVファイルのインポート)
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)
・「id」カラム(AUTO_INCREMENT)のところは空にする
・UTF-8 BOM 無しで保存
・先頭にカラム名があったら削除しておく
MySQLコマンド履歴を見たい
Linux の history コマンドのように一覧表示したい場合。
# ホームディレクトリに移動
$ cd ~
# .mysql_history をテキストエディタで開く
$ less -iNM .mysql_history
・mysql_history というファイルをテキストエディタで開く
・mysql_history はホームディレクトリ直下にあることが多い
MySQLで一括置換をかける
MySQLのテーブルの内容に一括置換をかけたり場合は、次のようにします。
UPDATE テーブル名 SET カラム名 = REPLACE(カラム名, '検索文字列', '置換文字列');
'
(シングルクォート)で囲みます。・バッククォート
(`)
はテーブル名やフィールド名を囲む・シングルクォート
(')
は値や文字列を囲む
「IN」をサブクエリとして使う
サブクエリとは簡単に言えば「SELECTで取得したデータを使用して、SELECT や UPDATE を使用する」手法です。
要素名 IN ( SELECT 要素名 FROM テーブル名)
// 例
id, time IN (SELECT id, time FROM sub_tbl)
IN 句の中に SELECT 文を入れることで、SELECT 文の結果を IN 句の中に使用しているわけです。
SELECT * FROM テーブルA WHERE 要素名 IN (SELECT 要素名 FROM テーブルB);
このようにすれば「テーブルBの出力結果」を元に「テーブルA」でSELECT文が使用できるということです。
テーブル名やカラム名を別名に変更する(AS句)
テーブル名やカラム名を別名に変更するには、AS句を使います。
# 構文
SELECT 列名 AS カラムの別名 FROM テーブル名 AS テーブルの別名
# 例
SELECT * FROM master_tbl AS m;
# AS句は省略することも可能です。
SELECT * FROM master_tbl m;
副問合せ(サブクエリ)
サブクエリとはSELECT文を入れ子にして使用することです。
サブクエリは、SELECTの項目、FROM句、WHERE句の3か所で使用することができます。
# --------------------------
# WHERE句
# --------------------------
# SQLをかっこでくくることでサブクエリとすることができます。
SELECT * FROM main_tbl WHERE main_tbl.id = (SELECT user_id FROM sub_tbl WHERE sub_tbl.name = 'ほげ')
# サブクエリの結果が複数の場合
SELECT * FROM main_tbl WHERE main_tbl.id IN (SELECT user_id FROM sub_tbl)
# -------------------------
# FROM句
# ------------------------
# FROM句でサブクエリを使用する際は、WHERE句やGROUP BYなどで取得した結果を一時的なテーブルとして使うイメージです。
SELECT SUB.id, SUB.name FROM ( SELECT id, name FROM sub_tbl WHERE sub_tbl.name IN ('山本','田中') SUB
# -------------------------
# SELECT句
# ------------------------
# サブクエリで取得した結果をSELECT項目に追加することができます。
SELECT id, name, student_id, (SELECT COUNT(student_id) FROM sub_tbl) AS SUB FROM main_tbl
# サブクエリのWHEREで親テーブルと紐づけを行うこともできます。
SELECT id, name, student_id, (SELECT COUNT(student_id) FROM sub_tbl WHERE main_tbl.student_id = sub_tbl.student_id) AS SUB FROM main_tbl
バージョンの確認
MySQL / MariaDB のバージョンを確認するタイミングは以下の2つがあります。
・DB サーバへの接続後
DB サーバへの接続前
「sudo」が邪魔なんで、root になってコマンドを発行しています。
// mysql ではなく、mysqld です。
// mysql はクライアント、mysqld はサーバーと覚えてください。
[root@example ~]# mysqld --version
実演
一般的にはデータベースには以下の2つの機能・役割が存在しています。
・サーバー
・クライアント
通常、私たちが知りたいのはサーバーのバージョンです。
以下のコマンドはクライアントのバージョン確認となりますので、気を付けましょう。
$ mysql --version
mysql Ver 15.1 Distrib 10.5.8-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2
DB サーバへの接続後
サーバーのバージョン確認方法は以下の2つの方法があります。
・select version()
MariaDB [(none)]> status
// mysqldコマンド同様にシンプルな結果です。
MariaDB [(none)]> select version();
実演
起動と停止、再起動
#------------------------
# Ubuntu
#------------------------
# MySQLを起動する
systemctl start mysql
# MySQLを停止する
systemctl stop mysql
# MySQLを再起動する
systemctl restart mysql
#------------------------
# CentOS
#------------------------
# MySQLを起動する
systemctl start mysqld
# MySQLを停止する
systemctl stop mysqld
# MySQLを再起動する
systemctl restart mysqld
インストールとアンインストール
# MySQL のインストール(Ubuntu)
$ sudo apt install mysql-server
# MySQL のアンインストール(Ubuntu)
$ sudo apt remove --purge mysql-server
$ sudo apt remove --purge mysql-client
$ sudo apt remove --purge mysql-common
$ sudo apt autoremove --purge
$ sudo rm -r /etc/mysql
$ sudo rm -r /var/lib/mysql
何か気づいたら、随時追加予定です。
以上です。
Udemyを使ったLaravel学習方法
仕事で Laravel を使っています。気づいたことや新しい発見など情報を発信していきます。問い合わせはこちら。