MySQLコマンド(早見表)

2020/04/21

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

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

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

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

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

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

 
INDEX

 

ログイン


$ mysql -u ユーザ名 -p

# root でログイン
$ mysql -uroot

# user:default pass:secret でログイン
$ mysql -udefault -psecret

# user:default pass:secret + db:laravelでログイン
$ mysql -udefault -psecret laravel

チェック -p オプションはパスワード設定しているときのみ。
ログイン後は MySQLコマンドしか使えない。またコマンドは最後に「;」(セミコロン)を入れる。

 

ユーザ操作

ユーザー作成


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;

 

データベース操作

データベース一覧表示


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

 

テーブルの削除


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 users SET name = '後藤', age = 23 WHERE name = '佐藤';

 

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 * FROM テーブル名1
INNER JOIN テーブル名2
 ON テーブル名1.カラム名1 = テーブル名2.カラム名2;

 

内部結合 + 条件


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 

 

ダンプ&リストア

ダンプ


mysqldump -u { user_name } -p { db_name } > { dumpfile.sql }

 

リストア


mysql -u { user_name } -p { db_name } < dumpfile.sql

チェックはじめてリストアする場合は、あらかじめリストアさせるDBを作成してください。 CREATE DATABASE db_name;

 

集計


# 基本
SELECT COUNT(*) FROM users;

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

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

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

本庄マサノリ

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

>> Twitter をフォローする

 

-周辺知識