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

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

MySQLログインの基本コマンド

mysql と打つだけでログインできます。

MySQLにログイン

mysql

ただし root ユーザーのパスワードなどがない場合です。

MySQLにユーザーを指定してログイン

-uオプションつけます。

MySQLにログイン

mysql -u ユーザー名

rootユーザーでログイン

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

MySQLにログイン

mysql -u root

パスワードを入力してログイン

-pオプションつけます。

MySQLにログイン

mysql -u root -p

パスワードを要求されます。

データベースを指定してログイン

特にオプションを付けずに、データベース名を後ろにつけます。

MySQLにログイン

mysql -u ユーザー名 データベース名

以下はrootユーザーでpugi_databaseにログインする例です。

MySQLにログイン

mysql -u root pugi_database

略

MariaDB [pugi_development]>

データベースを指定すると、ログイン後に必ず実行するuse データベース名というコマンドを省略出来るので、なるべく指定したほうが良いです。

ホストを入力してログイン

-hオプションつけます。

MySQLにログイン

mysql -u root -p -h ホスト名

ホストを指定するとことができます。

ポートを指定してログイン

-Pオプションつけます。

MySQLにログイン

mysql -u root -p -P 3037

上記は3037ポートを利用する場合です。

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

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

use データベース名;

use コマンドを省略したい場合は、データベースを指定してログイン
にあるように、ログイン時にデータベースを指定すると良いです。

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

show databases;

データベース作成する

create database データベース名;

データベース削除する

drop database データベース名;

SELECTでレコードを検索する

MySQL や MariaDB で SQL を勉強したい場合は、まずは SELECT 文で覚えるといいですね。

レコードを検索

SELECT * FROM テーブル名;

*を指定すると全カラムを表示します。

レコードを検索の例

SELECT * FROM posts;

posts テーブルを検索する例です。

WHERE句での絞込み

レコードを絞込み検索

SELECT * FROM posts WHERE id = 10;

posts テーブルのidが10のレコードだけを検索します。

LIKEで文字列検索

LIKE 検索というのがとても便利です。

レコードを絞込み検索

SELECT * FROM posts WHERE post_contents LIKE('% style="font-size: 12pt;"%');

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

INSERTでレコードを追加

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

基本構文

INSERT INTO テーブル名 (カラム名) VALUES (値);

INSERT例

INSERT INTO users (name) VALUES ('ぷぎえもん');

usersテーブルのnameというカラムにぷぎえもんという文字列を追加した例です。

SELECTの検索結果をINSERTで追加

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

SELECT結果をINSERT

INSERT INTO テーブル名１ (カラム名１) SELECT カラム名２ FROM テーブル名２ WHERE テーブル名２.カラム名 = 値;

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

SELECT結果をINSERT例

INSERT INTO profiles (name) SELECT users FROM users WHERE users.id = 1;

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

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

カラムを更新

UPDATE テーブル名 SET カラム1=1, カラム2=10 WHERE id=1;

カラムを更新例

UPDATE users SET name='hoge', status=3 WHERE id=1;

REPLACEで文字列を置換して更新

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

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 です。

DELETE構文

DELETE FROM テーブル名 WHERE 条件;

DELTEの例

DELETE FROM members WHERE id=13;

バージョンの確認

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

バージョンの確認

SELECT VERSION();

MySQLのシステム変数の値を見る

MySQLのシステム変数をドバっと表示します。

システム変数の値を表示

SHOW VARIABLES;

文字コード、character_set周りを確認したとき

SHOW VARIABLESにlikeを組み合わせることで、知りたい変数を限定することができます。

SHOW VARIABLES LIKE "char%";

slowquery周りを確認したいとき

SHOW VARIABLES LIKE 'slow%';

プライマリーキーの追加

プライマリーキーの追加

ALTER TABLE テーブル名 ADD PRIMARY KEY (カラム名);

カラムの名前、型を変えたい

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

カラム名と型を変更

ALTER TABLE `posts` CHANGE COLUMN `txt` `caption` VARCHAR(255);

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

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

カラム名の制約を追加

ALTER TABLE `posts` CHANGE COLUMN `txt` `caption` VARCHAR(255) not null;

カラムの型を変えたい

ALTER TABLE の MODIFY COLUMN を使用します。

ALTER TABLE `comments` MODIFY COLUMN `post_id` BIGINT NOT NULL;

カラムを追加したい

ALTER TABLEとADDを使用してカラムを追加します。

カラムを追加

ALTER TABLE テーブル名 ADD カラム名 型;

実際の例は以下のような感じです。

カラムを追加

ALTER TABLE `users` ADD `pugi_id` int(11) DEFAULT NULL;

usersテーブルでpugi_idを追加する例です。

特定カラムの後ろにカラムを追加したい

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

特定カラムの後ろにカラムを追加

ALTER TABLE テーブル名 ADD 追加したいカラム名　AFTER 追加したい直前のカラム名;

先程の例にAFTERを追加します。

カラムを追加

ALTER TABLE `users` ADD `pugi_id` int(11) DEFAULT NULL AFTER `password`;

上記を実行すると、passwordカラムの後ろに、pugi_idカラムが追加されます。

カラム削除

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

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

テーブル削除

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

ALTER TABLE テーブル名;

テーブルの中味を捨てる

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

テーブルの中味を捨てる

TRUNCATE テーブル名;

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

ログテーブルの中味を捨てる

TRUNCATE logs;

ユーザーを追加

MySQLで使用するユーザーの追加です。

ユーザーを追加

CREATE USER ユーザー名;

pugiemonnユーザーを追加

CREATE USER pugiemonn;

ユーザーのパスワードを変更

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

パスワードの変更方法

SET PASSWORD FOR ユーザー名@ホスト=PASSWORD('パスワード');

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

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

ユーザーを削除

DROP USER ユーザー名@ホスト名;

ユーザー権限を確認

ユーザーの権限を確認

SHOW GRANTS FOR ユーザー名@ホスト名;

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

wordpressユーザーの権限を確認

SHOW GRANTS FOR wordpress@localhost;

pugiemonnユーザーの例

pugiemonnユーザーの権限を確認

SHOW GRANTS FOR 'pugiemonn'@'%';

権限を追加

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

権限を追加

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

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

wordpressユーザーの権限を追加

GRANT ALL PRIVILEGES ON `wordpress_database`.* TO 'wordpress'@'localhost';

pugiemonnユーザーにSELECT権限を追加する例です。

pugiemonnユーザーの権限を追加

GRANT SELECT ON `pugidb`.`users` TO 'pugiemonn'@'%';

文字コードを UTF-8 にする

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

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