MySQLコマンド（早見表）

$ mysql -u ユーザ名 -p

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

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

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

ユーザー作成

「ユーザ作成」を開く

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

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

「ユーザーにDB操作権限を付与」を開く

# 構文
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;

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

カラムの削除

「カラムの追加」を開く

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

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

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

「取得するデータの行数の上限を設定する（LIMIT句）」を開く

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

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

INSERT文

「INSERT文」を開く

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

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

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

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

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

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

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

UPDATE文

「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 = '２年生' WHERE grade = '１年生';

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

「NOT NULL」を開く

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

PRIMARY KEY（主キー）

「PRIMARY KEY」を開く

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;

インデックス

「インデックス」を開く

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

LIKE検索

「LIKE検索＋複数」を開く

## 構文
# 指定したカラム名の値が、ワイルドカードを使ったパターンに一致しているデータを検索できる。
SELECT * FROM テーブル名 WHERE カラム名 LIKE パターン;

# 指定したカラム名の値が、ワイルドカードを使ったpターンに一致していないデータを検索できる。
SELECT * FROM テーブル名 WHERE カラム名 NOT LIKE パターン;

LIKE検索＋複数

「LIKE検索＋複数」を開く

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

特定の値

「特定の値」を開く

// 単数
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;

内部結合 + 条件

「特定の値」を開く

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

3つのテーブル結合

「3つのテーブル結合」を開く

SELECT テーブル名.カラム名, ... -- ※ 取得したいカラム名
FROM テーブル名１ 
INNER JOIN テーブル名２
 ON テーブル名１.カラム名１ = テーブル名２.カラム名２ 
INNER JOIN テーブル名３
 ON テーブル名１.カラム名１ = テーブル名３.カラム名３ 

4つのテーブル結合

「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;

エクスポート

「エクスポート」を開く

# 構文
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';

ダンプ

「ダンプ」を開く

# 全テーブル
[root@example ~]＃ mysqldump -u ユーザ名 -p DB名 > ダンプファイル名.sql
# テーブル指定
[root@example ~]＃ mysqldump -u ユーザ名 -p -t DB名 テーブル名 > ダンプファイル名.sql

インポート（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

# 基本
SELECT COUNT(*) FROM users;

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

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

#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文字以上＋数字＋大文字＋小文字＋特殊文字）を設定してください。

# 変数セット
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;

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

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

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

MySQLコマンドについての備忘録（CSVファイルのインポート）

１）ローカル環境でファイルが見つからない（Windows）

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

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

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

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

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

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

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

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

MySQLのテーブルの内容に一括置換をかけたり場合は、次のようにします。

UPDATE テーブル名 SET カラム名 = REPLACE(カラム名, '検索文字列', '置換文字列');

サブクエリとは簡単に言えば「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句を使います。

# 構文
SELECT 列名 AS カラムの別名 FROM テーブル名 AS テーブルの別名

# 例
SELECT * FROM master_tbl AS m;

# AS句は省略することも可能です。
SELECT * FROM master_tbl m;

サブクエリとはSELECT文を入れ子にして使用することです。

サブクエリは、SELECTの項目、FROM句、WHERE句の３か所で使用することができます。

# --------------------------
# 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 のバージョンを確認するタイミングは以下の２つがあります。

DB サーバへの接続前

「sudo」が邪魔なんで、root になってコマンドを発行しています。

// mysql ではなく、mysqld です。
// mysql はクライアント、mysqld はサーバーと覚えてください。
[root@example ~]＃ mysqld --version

実演

$ mysql --version
mysql  Ver 15.1 Distrib 10.5.8-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

DB サーバへの接続後

サーバーのバージョン確認方法は以下の２つの方法があります。

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