よく使うMariaDB か MySQLのSQLコマンドメモ

MySQL への SQL コマンドは色々あるけど毎回忘れちゃうのでメモしておきます。

# MySQLログインの基本コマンド
mysql と打つだけでログインできます。

```bash:MySQLにログイン
mysql
```
ただし root ユーザーのパスワードなどがない場合です。

## MySQLにユーザーを指定してログイン
`-u`オプションつけます。

```bash:MySQLにログイン
mysql -u ユーザー名
```

### rootユーザーでログイン
rootユーザーを指定してログインします。

```bash:MySQLにログイン
mysql -u root
```

## パスワードを入力してログイン
`-p`オプションつけます。

```bash:MySQLにログイン
mysql -u root -p
```
パスワードを要求されます。

## データベースを指定してログイン
特にオプションを付けずに、データベース名を後ろにつけます。

```bash:MySQLにログイン
mysql -u ユーザー名 データベース名
```
以下はrootユーザーでpugi_databaseにログインする例です。

```bash:MySQLにログイン
mysql -u root pugi_database

略

MariaDB [pugi_development]>
```
データベースを指定すると、ログイン後に必ず実行する[use データベース名](http://qiita.com/pugiemonn/items/247dccb6dc56e373ba92#%E4%BD%BF%E7%94%A8%E3%81%99%E3%82%8B%E3%83%87%E3%83%BC%E3%82%BF%E3%83%99%E3%83%BC%E3%82%B9%E3%82%92%E9%81%B8%E6%8A%9E%E3%81%99%E3%82%8B)というコマンドを省略出来るので、なるべく指定したほうが良いです。


## ホストを入力してログイン
`-h`オプションつけます。

```bash:MySQLにログイン
mysql -u root -p -h ホスト名
```
ホストを指定するとことができます。

## ポートを指定してログイン
`-P`オプションつけます。

```bash:MySQLにログイン
mysql -u root -p -P 3037
```
上記は3037ポートを利用する場合です。

# 使用するデータベースを選択する
MySQL はログイン後にデータベースの選択が必要です。

```mysql:
use データベース名;
```
use コマンドを省略したい場合は、[データベースを指定してログイン
](http://qiita.com/pugiemonn/items/247dccb6dc56e373ba92#%E3%83%87%E3%83%BC%E3%82%BF%E3%83%99%E3%83%BC%E3%82%B9%E3%82%92%E6%8C%87%E5%AE%9A%E3%81%97%E3%81%A6%E3%83%AD%E3%82%B0%E3%82%A4%E3%83%B3) にあるように、ログイン時にデータベースを指定すると良いです。


# データベース一覧を確認する

```mysql:
show databases;
```

# データベース作成する

```mysql:
create database データベース名;
```

# データベース削除する

```mysql:
drop database データベース名;
```

# SELECTでレコードを検索する
MySQL や MariaDB で SQL を勉強したい場合は、まずは SELECT 文で覚えるといいですね。

```mysql:レコードを検索
SELECT * FROM テーブル名;
```
`*`を指定すると全カラムを表示します。

```mysql:レコードを検索の例
SELECT * FROM posts;
```
posts テーブルを検索する例です。


## WHERE句での絞込み

```mysql:レコードを絞込み検索
SELECT * FROM posts WHERE id = 10;
```
posts テーブルのidが10のレコードだけを検索します。

## LIKEで文字列検索
LIKE 検索というのがとても便利です。

```mysql:レコードを絞込み検索
SELECT * FROM posts WHERE post_contents LIKE('% style="font-size: 12pt;"%');
```
上記は WordPress の投稿本文中に、インラインスタイルである` style="font-size: 12pt;"`が入っているレコードを検索できます。前後に`%`をつけるとカラム内のどこにあってもヒットしてくれます。


# INSERTでレコードを追加
INSERT 文を使用してレコードを追加します。

```mysql:基本構文
INSERT INTO テーブル名 (カラム名) VALUES (値);
```

```mysql:INSERT例
INSERT INTO users (name) VALUES ('ぷぎえもん');
```
usersテーブルのnameというカラムに`ぷぎえもん`という文字列を追加した例です。

## SELECTの検索結果をINSERTで追加
検索結果を追加したいような場合は、INSERT文内にSELECT文を書きます。
テーブル１にテーブル２の値を移すような場合に使えます。

```mysql:SELECT結果をINSERT
INSERT INTO テーブル名１ (カラム名１) SELECT カラム名２ FROM テーブル名２ WHERE テーブル名２.カラム名 = 値;
```

以下は、usersテーブルの名前をprofilesテーブルに移すという例です。

```mysql:SELECT結果をINSERT例
INSERT INTO profiles (name) SELECT users FROM users WHERE users.id = 1;
```

# UPDATEでカラムのデータを更新する

複数更新するときはカンマ`,`で区切ります。

```mysql:カラムを更新
UPDATE テーブル名 SET カラム1=1, カラム2=10 WHERE id=1;
```


```mysql:カラムを更新例
UPDATE users SET name='hoge', status=3 WHERE id=1;
```

## REPLACEで文字列を置換して更新
REPLACE を使うと文字列を置換できます。正規表現は不要で置換できますが、一方で REPLACE は正規表現を使用できないようです。

```mysql:REPLACEで置換、LIKE絞りあり
UPDATE posts SET post_content = REPLACE(post_content, ' style="font-size: 12pt;"', '') WHERE post_content LIKE('% style="font-size: 12pt;"%');
```
こちらは WordPress の本文から` style="font-size: 12pt;"`というインラインスタイルを削除する SQL です。

# DELETEでデータを削除する
削除は `DELETE` です。

```mysql:DELETE構文
DELETE FROM テーブル名 WHERE 条件;
```

```mysql:DELTEの例
DELETE FROM members WHERE id=13;
```

# バージョンの確認
MySQL のバージョンを知りたい場合は以下を使用します。

```mysql:バージョンの確認
SELECT VERSION();
```
# MySQLのシステム変数の値を見る
MySQLのシステム変数をドバっと表示します。

```mysql:システム変数の値を表示
SHOW VARIABLES;
```

## 文字コード、character_set周りを確認したとき
`SHOW VARIABLES`にlikeを組み合わせることで、知りたい変数を限定することができます。

```mysql:
SHOW VARIABLES LIKE "char%";
```

## slowquery周りを確認したいとき

```mysql:
SHOW VARIABLES LIKE 'slow%';
```

# プライマリーキーの追加

```mysql:プライマリーキーの追加
ALTER TABLE テーブル名 ADD PRIMARY KEY (カラム名);
```

# カラムの名前、型を変えたい
`posts`テーブルの`txt`というカラムを`caption`というカラムで`VARCHAR`型に変えたい場合です。

```mysql:カラム名と型を変更
ALTER TABLE `posts` CHANGE COLUMN `txt` `caption` VARCHAR(255);
```

カラムの名前だけを変更する場合でも、上記のように型指定が必要です。

`not null`制約を追加する場合です。

```mysql:カラム名の制約を追加
ALTER TABLE `posts` CHANGE COLUMN `txt` `caption` VARCHAR(255) not null;
```

# カラムの型を変えたい
`ALTER TABLE` の `MODIFY COLUMN` を使用します。

```mysql:
ALTER TABLE `comments` MODIFY COLUMN `post_id` BIGINT NOT NULL;
```
# カラムを追加したい
`ALTER TABLE`と`ADD`を使用してカラムを追加します。

```mysql:カラムを追加
ALTER TABLE テーブル名 ADD カラム名 型;
```
実際の例は以下のような感じです。

```mysql:カラムを追加
ALTER TABLE `users` ADD `pugi_id` int(11) DEFAULT NULL;
```
usersテーブルで`pugi_id`を追加する例です。

## 特定カラムの後ろにカラムを追加したい
カラムの追加位置をコントロールしたい場合は、`AFTER`を使用します。

```mysql:特定カラムの後ろにカラムを追加
ALTER TABLE テーブル名 ADD 追加したいカラム名　AFTER 追加したい直前のカラム名;
```
先程の例にAFTERを追加します。

```mysql:カラムを追加
ALTER TABLE `users` ADD `pugi_id` int(11) DEFAULT NULL AFTER `password`;
```
上記を実行すると、`password`カラムの後ろに、`pugi_id`カラムが追加されます。

# カラム削除
カラムを削除する場合です。

```
ALTER TABLE テーブル名 DROP カラム名;
```

# テーブル削除
テーブルを削除する場合です。

```
ALTER TABLE テーブル名;
```

# テーブルの中味を捨てる
`TRUNCATE` を使用するとtableを破棄せず、中身のデータだけを消すことができます。しかも　`TRUNCATE`　の実行は非常に早いです。

```mysql:テーブルの中味を捨てる
TRUNCATE テーブル名;
```

以下の例では、ログテーブルを空にします。

```mysql:ログテーブルの中味を捨てる
TRUNCATE logs;
```
# ユーザーを追加
MySQLで使用するユーザーの追加です。

```mysql:ユーザーを追加
CREATE USER ユーザー名;
```

```mysql:pugiemonnユーザーを追加
CREATE USER pugiemonn;
```

# ユーザーのパスワードを変更
ユーザーのパスワードを変更した場合は、SET PASSWORD が便利

```mysql:パスワードの変更方法
SET PASSWORD FOR ユーザー名@ホスト=PASSWORD('パスワード');
```

以下はpugiemonnユーザーにパスワードを設定する例です。

```
SET PASSWORD FOR pugiemonn@localhost=PASSWORD('pugiemonn_password');
```

# ユーザーを削除

```mysql:
DROP USER ユーザー名@ホスト名;
```

# ユーザー権限を確認

```mysql:ユーザーの権限を確認
SHOW GRANTS FOR ユーザー名@ホスト名;
```

wordpressユーザーの権限を確認します。

```mysql:wordpressユーザーの権限を確認
SHOW GRANTS FOR wordpress@localhost;
```

pugiemonnユーザーの例

```mysql:pugiemonnユーザーの権限を確認
SHOW GRANTS FOR 'pugiemonn'@'%';
```

# 権限を追加
特定のテーブルに権限を追加する場合 `GRANT` を使用します。

```mysql:権限を追加
GRANT 権限 ON `データベース名`.`テーブル名` TO 'ユーザー名'@'ホスト'
```

wordpress_databaseの全てのテーブルに権限を追加する例です。

```mysql:wordpressユーザーの権限を追加
GRANT ALL PRIVILEGES ON `wordpress_database`.* TO 'wordpress'@'localhost';
```
pugiemonnユーザーにSELECT権限を追加する例です。

```mysql:pugiemonnユーザーの権限を追加
GRANT SELECT ON `pugidb`.`users` TO 'pugiemonn'@'%';
```

# 文字コードを UTF-8 にする
文字化けしているのだけど確認したい時、さくっとUTF-8にしたい場合に使えます。

```mysql:UTF-8を指定
SET NAMES UTF8;
```

#参考
- http://dev.mysql.com/doc/refman/5.6/ja/show-variables.html
- https://dev.mysql.com/doc/refman/5.6/ja/insert-select.html
- http://qiita.com/pugiemonn/items/f93984fc8de83ba53e13
- http://stackoverflow.com/questions/10177208/update-a-column-value-replacing-part-of-a-string