メモ〜MySQL/MariaDB関係

オプションの説明
オプション	説明
`-?, -I, --help`	ヘルプを表示して終了します。
`--auto-rehash`	自動再ハッシュ（automatic rehashing）を有効にする。テーブルを取得し、フィールドを完成させるのに「再ハッシュ」する必要はないが、起動と再接続にはより時間がかかる。自動再ハッシュを無効にするには `--disable-auto-rehash` オプションを指定。
`-hホスト名 --host=ホスト名`	指定したホストに接続する。【例】リモートのmysqlサーバ「remote.server」に接続する mysql -uuser -ppass -hremote.server mydb ※サーバの接続ポート（デフォルトは3306番）が開いている必要あり。
`-pパスワード --password=パスワード`	サーバに接続する際に使用するパスワードを指定。パスワードを指定せず`-p`や`--password`だけを指定した場合、コマンドラインから尋ねられる。
`-Pポート番号` `--port=ポート番号`	サーバに接続する際に使用するポート番号を指定する。デフォルトの3306番で接続する場合は指定不要。
`-uMySQLユーザ名 --user=MySQLユーザ名`	現在のログインユーザとは別のユーザ名でMySQLサーバにログインしたい場合、ログインするユーザ名を指定。
`-V --version`	バージョン情報を出力して終了。

ユーザの作成

CREATE USER構文はUSAGE権限のMySQLユーザを作成する。同名のユーザが既に存在してい場合はエラーになる。この操作を行うにはCREATE USER 権限または INSERT 権限が必要。

create user 'ユーザ名'@'ホスト名またはIPアドレス' IDENTIFIED BY パスワード, ユーザ名 IDENTIFIED BY パスワード...

@'ホスト名またはIPアドレス'部分を省略すると、「@'%'」全てのアクセス元に対する設定を意味する「@'%'」を指定したのと同じ。

ユーザを作成しただけでは権限が与えられていないので、実際使用するには続いてGRANTで権限を与える必要がある。

パスワードの設定・変更

SET PASSWORDは、既存のMySQLユーザアカウントにパスワードの設定を行う。この操作には mysql データベースに対する UPDATE 権限が必要。

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

【例】ユーザ「kenshiro」の「localhost」におけるMySQLパスワードを「hokutoshinken」に変更
SET PASSWORD FOR 'kenshiro'@'localhost'=PASSWORD('hokutoshinken');

あるいは、端末からmysqladminコマンドを用いて変更する。

$ /usr/bin/mysqladmin -u MySQLユーザ名 password '新パスワード'
$ /usr/bin/mysqladmin -u MySQLユーザ名 -p password '新パスワード'

ユーザ名の変更

RENEME USER構文は１つ以上のMySQLユーザのユーザ名を変更する。ホスト名またはIPアドレスを省略すると全ホスト（%）を指定したものとみなされる。

RENAME USER '旧ユーザ名'@'ホスト名またはIPアドレス' TO '新ユーザ名'@'ホスト名またはIPアドレス'
         , '旧ユーザ名'@'ホスト名またはIPアドレス' TO '新ユーザ名'@'ホスト名またはIPアドレス'...

ユーザの削除

DROP USER構文は１つ以上のMySQLユーザを削除する。この操作を行うにはCREATE USER 権限または DELETE 権限が必要。

DROP USER 'ユーザ名'@'ホスト名またはIPアドレス', ユーザ名...

@'ホスト名またはIPアドレス'部分を省略すると、「@'%'」全てのアクセス元に対する設定を意味する「@'%'」を指定したのと同じ。

アクセス権限の追加・削除・閲覧

GRANT構文で権限の追加、REVOKE構文で権限の削除を行う。これらの操作を行うには GRANT OPTION 権限を持っているMySQLユーザである必要がある。

【書式】
GRANT 権限内容 ON データベース名.* TO 'MySQLユーザ名'@'ホスト名またはIPアドレス'
REVOKE 権限内容 ON データベース名.* FROM 'MySQLユーザ名'@'ホスト名またはIPアドレス'

【例１】ユーザtestuserに対して、ローカルからの接続のみデータベース名menagerieへの全ての操作を許可する
mysql> GRANT ALL ON menagerie.* TO 'testuser'@'localhost'; 
Query OK, 1 row affected (0.00 sec)

【例２】ユーザtestuserに対して、どこからの接続でも全データベースへの全操作を許可する（パスワードなし）
mysql> GRANT ALL ON *.* TO 'testuser'@'%'; 

【例３】ユーザtestuserに対して、どこからの接続でも全データベースへの全操作を許可する（パスワードはtestpass）
mysql> GRANT ALL ON *.* TO 'testuser'@'%' IDENTIFIED BY 'testpass'; 

【例４】ユーザtestuserに対して、どこからの接続でも全データベースへの全操作を許可する（パスワードはなしに設定）
mysql> GRANT ALL ON *.* TO 'testuser'@'%' IDENTIFIED BY '';

許可内容（権限）の種類は以下の通り

指定値	操作種別
	データ操作				5.FILE	テーブル構造編集			DB操作			ユーザ編集		SLAVE
	1.読	2.更	3.追	4.削	5.FILE	6.T追	7.T編	8.T削	9.LOCK	10.RELOAD	11.全DB	12.U編	13.権限	SLAVE
`ALL PRIVILEGES`	○	○	○	○	○	○	○	○	○	○	○	○	−	−
`ALTER`	−	−	−	−	−	−	○	−	−	−	−	−	−	−
`CREATE`	−	−	−	−	−	○	−	−	−	−	−	−	−	−
`CREATE USER`	−	−	−	−	−	−	−	−	−	−	○	−	−	−
`DELETE`	−	−	−	○	−	−	−	−	−	−	−	−	−	−
`DROP`	−	−	−	−	−	−	−	○	−	−	−	−	−	−
`FILE`	−	−	−	−	○	−	−	−	−	−	−	−	−	−
`INSERT`	−	−	○	−	−	−	−	−	−	−	−	−	−	−
`LOCK TABLES`	−	−	−	−	−	−	−	−	○	−	−	−	−	−
`RELOAD`	−	−	−	−	−	−	−	−	−	○	−	−	−	−
`SELECT`	○	−	−	−	−	−	−	−	−	−	−	−	−	−
`SHOW DATABASES`	−	−	−	−	−	−	−	−	−	−	−	○	−	−
`UPDATE`	−	○	−	−	−	−	−	−	−	−	−	−	−	−
`USAGE`	−	−	−	−	−	−	−	−	−	−	−	−	−	−
`GRANT OPTION`	−	−	−	−	−	−	−	−	−	−	−	−	○
`REPLICATION SLAVE`	−	−	−	−	−	−	−	−	−	−	−	−	−	○

読：テーブルからのデータ読み込み（SELECT）
更：テーブルのデータ更新（UPDATE）
追：テーブルへのデータ追加（INSERT INTO）
削：テーブルからのデータ削除（DELETE FROM）
FILE：ファイルからのデータインポート、ファイルへのデータエクスポート（SELECT ... INTO OUTFILE、LOAD DATA INFILE）
T追：テーブルの追加作成（CREATE TABLE）
T編：テーブル構造の編集（ALTER TABLE）
T削：テーブル構造の削除（DROP TABLE）
LOCK：テーブルのロック・ロック解除（LOCK TABLE / UNLOCK TABLE）
RELOAD：FLUSH
U編：ユーザの作成、削除、ユーザ名変更など（CREATE USER、DROP USER、RENAME USER、REVOKE ALL PRIVILEGES）
全DB：SHOW DATABASESで全てのデータベースを表示する
権限：権限編集
SLAVE：複製スレーブサーバからのアクセス許可

権限設定の確認はSHOW GRANTS。

【例】
mysql> SHOW GRANTS FOR 'user'@'localhost';
+--------------------------------------------------------+
| Grants for user@localhost                              |
+--------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user'@'localhost'               | 
| GRANT ALL PRIVILEGES ON `mydb`.* TO 'user'@'localhost' | 
+--------------------------------------------------------+
2 rows in set (0.00 sec)

rootのパスワードリセット

rootのパスワードが分からない時、rootパスワードをリセットする方法^[9][16]。

MySQLサービスを停止

$ sudo service mysqld stop

リモート接続を無効化し、rootとしてアクセスできるようにする

$ sudo /usr/bin/mysqld_safe --skip-grant-tables --skip-networking &

rootでローカルのMySQLサーバに接続

$ mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.1.73-log Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>

全ユーザのパスワードを変更できるようにする

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.13 sec)

ここで、「ERROR 1146 (42S02): Table 'mysql.host' doesn't exist」と表示されたら、データベースディレクトリを（再）指定してやるといいらしい¹⁴

[root@server ~]# mysql_install_db --datadir=/var/lib/mysql --user=mysql

rootのパスワードを再設定

mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('password');
Query OK, 0 rows affected (0.01 sec)

再度flush

これはいらない？

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

MySQLを出てサービス再起動

mysql> exit
Bye
$ sudo service mysqld stop
$ sudo service mysqld start

データベースの作成と使用

MySQL接続後の各種操作について。各コマンドは（１）セミコロンで終える（２）途中改行可能。

データベース：一覧表示、作成、削除、選択（USE）
テーブル：一覧表示、作成、削除、構造表示、構造編集、名称変更
データ：レコード追加、更新、削除、表示、表示（重複除去）、テキストから読み込み、テキストへ出力
アクセス権限設定→ユーザ管理のユーザ権限の追加・削除・閲覧

データベース一覧表示

SHOW DATABASE 構文によりサーバ上にあるデータベースの一覧を表示することができる。

表示対象はLIKE構文やWHERE構文を使って対象条件を指定し限定できる。条件を省略するとSHOW DATABASE権限のあるデータベースをすべて表示する。

【書式1】SHOW DATABASES権限のあるすべてのデータベースを表示
SHOW DATABASES;
【書式2】SHOW DATABASES権限のあるデータベースの中でパターンに適合するものを表示
SHOW DATABASES LIKE 'パターン';
【書式3】SHOW DATABASES権限のあるデータベースの中で条件に適合するものを表示
SHOW DATABASES WHERE '条件';

【例】
mysql> SHOW DATABASES;
+----------+
| Database |
+----------+
| mysql    |
| test     |
| tmp      |
+----------+
mysql> SHOW DATABASES LIKE 'm%';
+----------+
| Database |
+----------+
| mysql    |
+----------+
mysql> SHOW DATABASES WHERE `Database` REGEXP '^t..t$';
+----------+
| Database |
+----------+
| test     |
+----------+

データベース作成

CREATE DATABASE 構文によりデータベースを作成することができる。データベース名は大文字小文字が区別される。作成されるだけで自動的に選択はされない。実際に使用するには、作成後 USE コマンドを実行する必要がある。

【書式】
CREATE DATABASE IF NOT EXISTS データベース名 データベース特性;

IF NOT EXISTS

指定したデータベースが既に存在したとき、IF NOT EXISTSの指定がないとエラーが発生するがIF NOT EXISTSを指定するとエラーは発生せずデータベース作成操作は実施されない。

データベース特性

CHARACTER SET = 文字コード名: データベースで使用する文字コードの既定値を設定する。使用できる文字コード名はSHOW CHARACTER SET構文で確認できる。
COLLATE = 文字コード名: データベース照合順序の既定値を設定する。

【例】menagerieという名前のデータベースを作成する
mysql> CREATE DATABASE menagerie;

データベース削除

DROP DATABASE 構文により既存のデータベースを削除することができる。データベース名は大文字小文字が区別される。

【書式】
DROP DATABASE IF EXISTS データベース名;

IF EXISTS: 指定したデータベースが存在しなかったとき、IF EXISTSの指定がないとエラーが発生するがIF EXISTSを指定するとエラーは発生せずデータベース削除操作は実施されない。

【例】menagerieという名前のデータベースを削除する
mysql> CREATE DATABASE menagerie;

使用データベース選択

USE 構文は、以降の構文がデフォルトで扱うデータベースを指定する。このコマンドは末尾にセミコロンを付ける必要はない（付けても害ははない）。

【書式】
USE データベース名
【例】testという名前のデータベースを使用する
mysql> USE test
Reading table imformation for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

データベース名を指定せずにMySQLに接続した際、接続直後はデータベースが選択されていないため、この操作によって使用するデータベースを選択する必要がある。接続時にデータベース名を指定した場合この操作は必要ない（→コマンドラインからMySQLに接続参照）。また、このコマンドによって接続データベースを変更することができる。

shell> mysql -h mysqluser -p menagerie
Enter password: （入力したパスワードは表示されない）
Reading table imformation for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ;or ¥g.
Your MySQL connection id is 9
Server version: 5.0.67 MySQL Community Server (GPL)

Type 'help;' or '¥h' for help. Type '¥c' to clear the buffer.

テーブル一覧表示

SHOW TABLES 構文により特定データベース内にあるテーブルの名前一覧を表示する。なおTEMPORARYテーブルは表示されない。

表示対象はLIKE構文やWHERE構文を使って対象条件を指定し限定できる。条件を省略するとSHOW DATABASE権限のあるデータベースをすべて表示する。

【書式1】TEMPORARYを除くデータベース内の全テーブルの名前一覧を表示
SHOW TABLES データベース名;
【書式2】データベース内でパターンに適合するテーブルの名前一覧を表示
SHOW TABLES データベース名 LIKE 'パターン';
【書式3】データベース内でパターンに適合するテーブルの名前一覧を表示
SHOW TABLES データベース名 WHERE '条件';

データベース名: 対象とするデータベースを指定する。指定がなかった場合はMySQLサーバ接続時に指定したデータベースまたはUSE構文で指定したデータベースが対象となる。

【例】
mysql> SHOW TABLES;
+-------+
| Table |
+-------+
| mysql |
| test  |
| tmp   |
+-------+
mysql> SHOW TABLES IN otherdb;
+------------------+
| Table_in_otherdb |
+------------------+
| mysql            |
| anothertest      |
+------------------+
mysql> SHOW TABLES LIKE 'm%';
+-------+
| Table |
+-------+
| mysql |
+-------+
mysql> SHOW TABLES WHERE `Table` REGEXP '^t..t$';
+-------+
| Table |
+-------+
| test  |
+-------+

テーブル作成

CREATE TABLE 構文により指定された名前を持つテーブルを作成することができる。データ型についてはデータ型・文字コード参照。LIKEを使えば、既存のテーブルと同じ構造を持つ空のテーブルが作成できる（書式２）。

【書式１】新規のテーブル構造を定義
CREATE TABLE IF NOT EXIST テーブル名 (カラム名 データ型 オプション,...,PRIMARY KEY (カラム名,...));
【例１】
mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),
    -> species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);

【書式２】既存テーブル構造を利用して新規テーブル構造を定義
CREATE TABLE 新テーブル名 LIKE 既存テーブル名;

同名のテーブルが既に存在した場合、IF NOT EXIST を指定していないとエラーになる。オプションの内容は以下の通り。

NOT NULL、NULL

値がない状態を許可するかどうかの設定。NOT NULLまたはNULLどちらか一方を指定する。特に指定がない場合の既定値は NULL（値がない状態を許可する）。

DEFAULT 既定値

値の指定がない場合の既定値を指定する。BLOB型およびTEXT型のカラムに既定値を設定することはできない。またTIMESTAMP型カラムを除き定数でないもの（NOW()、CURRENT_DATEのような関数や式）は指定できない。TIMESTAMP型カラムについてはCURRENT_TIMESTAMPを既定値を指定可能。既定値を指定しない場合の既定値は以下の通り。

カラム型	既定値
AUTO_INCREMENT属性を持つ整数型	最大値＋1
AUTO_INCREMENT属性付き整数型、以外の数値型	0
日付型、時刻型	0
TIMESTAMP型	現在の日付と時刻
ENUM型以外の文字列型	空の文字列
ENUM型	リストの最初の値

【例】
CREATE TABLE test (id KEY AUTO_INCREMENT, name varcher(64), lastmod timestamp DEFAULT CURRENT_TIMESTAMP);

AUTO_INCREMENT

整数型カラムに限り追加で指定できるオプションで、この属性を指定すると、1から始まり1ずつ加算された値が自動的に設定される。新たに追加される値は現在テーブルの中にあるカラムの最大値＋１の値となる。また、この指定により指定値の追加、既定値を指定ができなくなる（指定の際はNULLを指定する）。最も最近に生成された値を得るには"SELECT LAST_INSERT_ID();" SQL文を実行する。

PRIMARY KEY

PRIMARY KEY（単にKEYだけでもよい）は、一次キーカラムの指定を行う。この指定を行うと、暗黙のうちにNOT NULLの指定が行われNULL値を設定できなくなる。

複数のカラムで一次キーを構成することもできる。この場合、個々のカラムにはPRIMARY KEYの指定を行わず、別途キーリストを指定する記述を行う^[11]。

【例】
name,year,month,countのカラムを持つstatテーブルを作る。
一次キーカラムはname,year,month
CREATE TABLE stat
(name VARCHAR(256) COMMENT '項目名',
 year YEAR COMMENT '年',
 month ENUM('1','2','3','4','5','6','7','8','9','10','11','12') COMMENT '月',
 count INT UNSIGNED COMMENT 'アクセス数',
 PRIMARY KEY (name,year,month));

UNIQUE KEY

UNIQUE KEY（単にUNIQUEだけでもよい）は、カラム内にNULLを除く同一の値が存在できないようにする。もし同一の値を追加しようとするとエラーになる。

COMMENT 'コメント'

カラムの説明を記述する。字数は255文字（バイト？）まで。コメントはSHOW CREATE TABLE テーブル名または、SHOW FULL COLUMNS テーブル名を実行した際に表示される。

テーブルを削除

DROP TABLE 構文により指定された名前を持つテーブルのデータおよびテーブル構造を全て削除する。

【書式】
DROP TABLE テーブル名 , テーブル名 ...;

テーブル構造表示

SHOW COLUMNS 構文により指定したテーブルの構造を表示できる。テーブルのフィールド名（カラム名）やデータ型を確認するのに使える。

この構文の別名としてDESCRIBE 構文やEXPLAIN 構文も使える。

【書式】
SHOW COLUMNS FROM テーブル名;
DESCRIBE テーブル名;
EXPLAIN テーブル名;
【例】petテーブルの構造を表示する。
mysql> DESCRIBE pet;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name    | varchar(20) | YES  |     | NULL    |       |
| owner   | varchar(20) | YES  |     | NULL    |       |
| species | varchar(20) | YES  |     | NULL    |       |
| sex     | char(1)     | YES  |     | NULL    |       |
| birth   | date        | YES  |     | NULL    |       |
| death   | date        | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+

より詳細な情報を得るにはSHOW FULL COLUMNS FROM テーブル名を用いる。Collation、Privileges、Commentも表示される。

カラムの編集

テーブルのカラム追加、編集、削除を行うにはALTER TABLE 構文構文を使う。テーブルにカラムを追加する方法は以下の通り。

【書式】カラムの追加（ADD）
ALTER TABLE テーブル名 ADD (追加するカラム名 データ型 FIRST | AFTER カラム名 オプション);
※複数のカラムを追加する場合は各カラム定義をコンマで区切って記述する。

※主キーに設定する場合はデータ型の後に「KEY」と記す（カラム位置記述の前）
ALTER TABLE テーブル名 ADD (追加するカラム名 データ型 KEY FIRST | AFTER カラム名);

【書式】カラムの削除（DROP）
ALTER TABLE テーブル名 DROP カラム名

【書式】カラムの編集（名称変更も可能、CHANGE）
ALTER TABLE テーブル名 CHANGE 既存カラム名 新カラム名 新しいデータ型
 FIRST | AFTER 挿入位置直前となる既存カラム名 CHARACTER SET 文字コード名;

【書式】カラムの編集（名称はそのままで、属性や位置などのみを変更、MODIFY）
ALTER TABLE テーブル名 MODIFY カラム名 新しいデータ型
 FIRST | AFTER 挿入位置直前となる既存カラム名 SET DEFAULT 既定値 DROP DEFAULT 既定値 CHARACTER SET 文字コード名;

【書式】カラムの既定値を変更（ALTER）
ALTER TABLE テーブル名 ALTER カラム名 SET DEFAULT 既定値 | DROP DEFAULT 既定値;

FIRST／AFTER カラム名

「FIRST」を指定すれば一番最初に、「AFTER カラム名」を指定すれば指定カラムの直後にカラムを追加する。いずれの指定もない場合、一番最後にカラムが追加される（CHANGE/MODIFYの場合は現在の位置を維持）。

DEFAULT 既定値

CREATE TABLE参照。既定値を NULLに変更する場合、NULLを許可する設定になっている必要あり。そうなっていなければ、NULLを指定し、NULLを許可するよう設定変更を合わせて行う。

【例】last_update の既定値を CURRENT_TIMESTAMP から NULL に変更する。
mysql> describe test;
+-------------+-------------+------+-----+-------------------+-------+
| Field       | Type        | Null | Key | Default           | Extra |
+-------------+-------------+------+-----+-------------------+-------+
| id          | varchar(4)  | NO   | PRI | NULL              |       | 
| name        | varchar(64) | YES  |     | NULL              |       | 
| last_update | timestamp   | NO   |     | CURRENT_TIMESTAMP |       | 
+-------------+-------------+------+-----+-------------------+-------+
3 rows in set (0.00 sec)

mysql> ALTER TABLE test MODIFY last_update TIMESTAMP DEFAULT NULL;
ERROR 1067 (42000): Invalid default value for 'last_update'
↑NULLが許可されていないのでエラーになる
↓今度はNULLを許可する設定変更も追加→無事変更された
mysql> ALTER TABLE test MODIFY last_update TIMESTAMP NULL DEFAULT NULL;
Query OK, 564 rows affected (0.00 sec)
Records: 564  Duplicates: 0  Warnings: 0
mysql> describe test;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| id          | varchar(4)  | NO   | PRI | NULL    |       | 
| name        | varchar(64) | YES  |     | NULL    |       | 
| last_update | timestamp   | YES  |     | NULL    |       | 
+-------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

データ型についてはデータ型・文字コードの章参照。オプションについては、テーブル作成の章参照。

カラム名の変更を伴う、カラム位置、データ型、文字コードを編集するには、ALTER TABLE ... CHANGE を用いる。カラム名を変更しない場合は、変更後カラム名に変更前と同じ名前を指定して処理できるが、ALTER TABLE ... MODIFYを使った方が変更後カラム名の指定が要らず便利。文字コード名には、sjis、eucjp、utf8などを指定。MODIFYは型変更の他、カラム位置の変更にも使える。

カラム名に'SELECT'、'ORDER'などの予約語を使う場合はバッククォート（`）で囲む（→MySQL :: MySQL 5.6 リファレンスマニュアル :: 9.3 予約語）。

SET DEFAULTで既定値を設定、DROP DEFAULTで設定されている既定値の削除を行う。削除された後、特に既定の状態がなければ既定値はNULLになる。

mysql> DESCRIBE pet; ←テーブル構造表示
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name    | varchar(20) | YES  |     | NULL    |       |
| owner   | varchar(20) | YES  |     | NULL    |       |
| species | varchar(20) | YES  |     | NULL    |       |
| sex     | char(1)     | YES  |     | NULL    |       |
| birth   | date        | YES  |     | NULL    |       |
| death   | date        | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
【例１】petという名前のテーブルの最終列にsynonym（可変長最大20文字）のカラムを追加する
mysql> ALTER TABLE pet ADD synonym VARCHAR(20);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESCRIBE pet;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name    | varchar(20) | YES  |     | NULL    |       |
| owner   | varchar(20) | YES  |     | NULL    |       |
| species | varchar(20) | YES  |     | NULL    |       |
| sex     | char(1)     | YES  |     | NULL    |       |
| birth   | date        | YES  |     | NULL    |       |
| death   | date        | YES  |     | NULL    |       |
| synonym | varchar(20) | YES  |     | NULL    |       | ←新規追加された
+---------+-------------+------+-----+---------+-------+
【例２】petという名前のテーブルにある、synonymカラムをsynonym2（可変長最大30文字）に変更
mysql> ALTER TABLE pet CHANGE synonym synonym2 VARCHAR(30);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESCRIBE pet;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| name     | varchar(20) | YES  |     | NULL    |       |
| owner    | varchar(20) | YES  |     | NULL    |       |
| species  | varchar(20) | YES  |     | NULL    |       |
| sex      | char(1)     | YES  |     | NULL    |       |
| birth    | date        | YES  |     | NULL    |       |
| death    | date        | YES  |     | NULL    |       |
| synonym2 | varchar(30) | YES  |     | NULL    |       | ←変更された
+----------+-------------+------+-----+---------+-------+
【例３】petという名前のテーブルにある、synonym2カラム位置をspeciesの後に変更（データ型は可変長最大30文字のまま）
mysql> ALTER TABLE pet MODIFY synonym2 VARCHAR(30) AFTER species;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESCRIBE pet;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| name     | varchar(20) | YES  |     | NULL    |       |
| owner    | varchar(20) | YES  |     | NULL    |       |
| species  | varchar(20) | YES  |     | NULL    |       |
| synonym2 | varchar(30) | YES  |     | NULL    |       | ←変更された
| sex      | char(1)     | YES  |     | NULL    |       |
| birth    | date        | YES  |     | NULL    |       |
| death    | date        | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
【例４】synonym2の既定値を'dog'に設定する
mysql> ALTER TABLE pet ALTER synonym2 SET DEFAULT 'dog' ;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESCRIBE pet;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| name     | varchar(20) | YES  |     | NULL    |       |
| owner    | varchar(20) | YES  |     | NULL    |       |
| species  | varchar(20) | YES  |     | NULL    |       |
| synonym2 | varchar(30) | YES  |     | dog     |       | ←変更された
| sex      | char(1)     | YES  |     | NULL    |       |
| birth    | date        | YES  |     | NULL    |       |
| death    | date        | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
7 rows in set (0.00 sec)
mysql> ALTER TABLE pet ADD (weight decimal, length decimal);
Query OK, 606 rows affected (0.02 sec)
Records: 606  Duplicates: 0  Warnings: 0
mysql> describe pet;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| name     | varchar(20) | YES  |     | NULL    |       |
| owner    | varchar(20) | YES  |     | NULL    |       |
| species  | varchar(20) | YES  |     | NULL    |       |
| synonym2 | varchar(30) | YES  |     | dog     |       |
| sex      | char(1)     | YES  |     | NULL    |       |
| birth    | date        | YES  |     | NULL    |       |
| death    | date        | YES  |     | NULL    |       |
| weight   | decimal     | YES  |     | NULL    |       | 
| length   | decimal     | YES  |     | NULL    |       | 
+----------+-------------+------+-----+---------+-------+
9 rows in set (0.00 sec)

AUTO_INCREMENT属性のカラムで、最大値のレコードを削除してもAUTO_INCREMENTの値はリセットされない。リセットするにはテーブルのAUTO_INCREMENT値を最大値＋１に設定する。

mysql> DESCRIBE test
+----------+-------------+------+-----+-------------------+----------------+
| Field    | Type        | Null | Key | Default           | Extra          |
+----------+-------------+------+-----+-------------------+----------------+
| momid    | smallint(5) | NO   | PRI | NULL              | auto_increment | 
| year     | varchar(64) | YES  |     | NULL              |                | 
+----------+-------------+------+-----+-------------------+----------------+
2 rows in set (0.00 sec)
mysql> SELECT * FROM test
+----+-------+
| id | name  |
+----+-------+
|  1 | Tom   |
|  2 | James |
+----+-------+
2 rows in set (0.00 sec)
mysql> INSERT INTO test SET (id='', name='Thomas');
Query OK, 1 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> SELECT * FROM test
+----+--------+
| id | name   |
+----+--------+
|  1 | Tom    |
|  2 | James  |
|  3 | Thomas |
+----+--------+
3 rows in set (0.00 sec)
mysql> DELETE FROM test WHERE id='3';
Query OK, 1 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> INSERT INTO test SET (id='', name='Watson');
Query OK, 1 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> SELECT * FROM test
+----+--------+
| id | name   |
+----+--------+
|  1 | Tom    |
|  2 | James  |
|  4 | Waston | ←3ではなく4になる
+----+--------+
3 rows in set (0.00 sec)
mysql> DELETE FROM test WHERE id='3';
Query OK, 1 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> ALTER TABLE test AUTO_INCREMENT='3';
Query OK, 1 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> INSERT INTO test SET (id='', name='Watson');
mysql> ALTER TABLE test AUTO_INCREMENT='3';
Query OK, 1 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> SELECT * FROM test
+----+--------+
| id | name   |
+----+--------+
|  1 | Tom    |
|  2 | James  |
|  3 | Waston | ←3になった
+----+--------+
3 rows in set (0.00 sec)

テーブル名変更

RENAME TABLE 構文によりテーブルの名称を変更できる。

【書式】
RENAME TABLE 旧テーブル名 TO 新テーブル名,旧テーブル名2 TO 新テーブル名2...;

レコード追加

INSERT 構文によりテーブルにデータを追加できる。不明値は NULL で指定。

【書式】
INSERT INTO テーブル名 VALUES ('第1列データ','第2列データ',...),('第1列データ','第2列データ',...),...;
【例】
mysql> INSERT INTO pet
    -> VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);
Query OK, 1 row affected (0.00 sec)

カラム名を指定することもできる。

【書式１】
INSERT INTO テーブル名 (カラム名1,...) VALUES (カラム1の値, ... ),(カラム1の値, ... ),...;
【書式２】
INSERT INTO テーブル名 SET カラム名1=カラム2の値, ... 
【例】
mysql> INSERT INTO pet SET name=Puffball;
Query OK, 1 row affected (0.00 sec)

データ更新

UPDATE 構文によりテーブルにある既存のデータを更新できる。LIKE 構文やWHERE 構文で条件を指定しないと全レコードが更新の対象となるので注意。

【書式】
UPDATE テーブル名 SET カラム名1 = "データ1" ,カラム名2 = "データ2",... WHERE カラム名 = "データ";
【例】
mysql> UPDATE pet SET birth = "1989-08-31" WHERE name = "Bowser";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warning: 0

NULL値をセットしたい場合は、「SET カラム名 = NULL」（NULLはクォート記号で囲まないこと）とする。WHERE節における条件指定でNULL値を指定する場合「カラム名 is NULL」とは指定方法が異なるので注意（条件節では=NULLの判定結果が常にfalseであるため、条件に合うレコードは常に０件になって意図した通りに動作しない）^3ja-4。

データ削除

DELETE 構文によりテーブルにある既存のデータを削除できる。LIKE 構文やWHERE 構文で条件を指定しないと全レコードが削除される。削除されるのはデータだけで、テーブル構造自体は削除されない。テーブル構造も含めて削除する場合はDROP TABLE 構文を用いる。

【書式】
DELETE FROM テーブル名 WHERE 条件;

データ取り出し

SELECT 構文によりテーブルからデータを取り出すことができる。カラムリストに * を指定した場合は、全てのカラムを指定したことになる。LIKE 構文やWHERE 構文で条件を指定し対象を限定することができる。文字列の比較では通常大文字小文字は区別されない。ORDER BY節を指定すると、指定したカラムの値をキーにして昇順（小さい順）に並べ替えを行って結果が出力される。降順にしたければカラム名の後１つ空白を空けて DESC と記述する（複数指定する場合は個々に）。並べ替えは通常大文字小文字は区別されないが、区別したければ ORDER BY の代わりに ORDER BY BINARY 節を用いる。

全データ取得
カラム値で抽出対象を限定
カラム値で並べ替えて出力
【例12】レコード数を数える
【例13】カラム値ごとにレコード数を集計して表示
【例14】条件のあいまい指定（LIKE節、０文字以上）
【例15】条件のあいまい指定（LIKE節、１文字）
【例16】条件の正規表現による指定
【例17】複数のカラム値を結合して１つのカラム値とし取り出す（CONCAT）
【例18】カラム名の別名をつける（AS）
【例19】出力行数を絞る（LIMIT）

全データ取得

条件節（WHERE）を付けないと指定テーブルの全データが返る。抽出対象とするカラム名に「*」を指定すると、全カラムのデータが取り出される。カラム名はコンマで区切って複数併記できる。

【書式】
SELECT カラム名 FROM データ取得元テーブル;

【例】テーブルpetから全てのデータを取り出す
mysql> SELECT * FROM pet;
+----------+--------+---------+------+------------+------------+
| name     | owner  | species | sex  | birth      | death      |
+----------+--------+---------+------+------------+------------+
| Fluffy   | Harold | cat     | f    | 1993-02-04 | NULL       |
| Claws    | Gwen   | cat     | m    | 1994-03-17 | NULL       |
| Buffy    | Harold | dog     | f    | 1989-05-13 | NULL       |
| Fang     | Benny  | dog     | m    | 1990-08-27 | NULL       |
| Bowser   | Diane  | dog     | m    | 1979-08-31 | 1995-07-29 |
| Chirpy   | Gwen   | bird    | f    | 1998-09-11 | NULL       |
| Whistler | Gwen   | bird    | NULL | 1997-12-09 | NULL       |
| Slim     | Benny  | snake   | m    | 1996-04-29 | NULL       |
| Puffball | Diane  | hamster | f    | 1999-03-30 | NULL       |
+----------+--------+---------+------+------------+------------+
9 rows in set (0.00 sec)

【例】テーブルpetからカラムnameとbirthの値を全て取り出す
mysql> SELECT name, birth FROM pet;
+----------+------------+
| name     | birth      |
+----------+------------+
| Fluffy   | 1993-02-04 | 
| Claws    | 1994-03-17 | 
| Buffy    | 1989-05-13 | 
| Fang     | 1990-08-27 | 
| Bowser   | 1989-08-31 | 
| Chirpy   | 1998-09-11 | 
| Whistler | 1997-12-09 | 
| Slim     | 1996-04-29 | 
| Puffball | 1999-03-30 | 
+----------+------------+
9 rows in set (0.00 sec)

カラム値で抽出対象を限定

条件節（WHERE）を後に付加し、カラム名とカラム値を指定することで、取り出すデータの対象を指定することができる（→抽出条件記述（where）参照）。

【書式】
SELECT 選択するカラムリスト FROM データ取得元テーブル WHERE カラム名='カラム値';
【例】テーブルpetからカラムnameの値がBowserであるデータを全て取り出す
mysql> SELECT * FROM pet WHERE name = "Bowser";
+--------+-------+---------+------+------------+------------+
| name   | owner | species | sex  | birth      | death      |
+--------+-------+---------+------+------------+------------+
| Bowser | Diane | dog     | m    | 1989-08-31 | 1995-07-29 |
+--------+-------+---------+------+------------+------------+
1 row in set (0.00 sec)

論理演算子を使って複数の指定を組み合わせることもできる（→条件の併記、否定参照）。

【例】テーブルpetからカラムspeciesの値がsnakeまたはbirdであるデータを全て取り出す
mysql> SELECT * FROM pet WHERE species = 'snake' OR species = 'bird';
+----------+-------+---------+------+------------+-------+
| name     | owner | species | sex  | birth      | death |
+----------+-------+---------+------+------------+-------+
| Chirpy   | Gwen  | bird    | f    | 1998-09-11 | NULL  | 
| Whistler | Gwen  | bird    | NULL | 1997-12-09 | NULL  | 
| Slim     | Benny | snake   | m    | 1996-04-29 | NULL  | 
+----------+-------+---------+------+------------+-------+
3 rows in set (0.00 sec)

【例】テーブルpetから雄の犬だけを取り出す
mysql> SELECT * FROM pet WHERE species='dog' AND sex='m';
+----------+--------+---------+------+------------+------------+
| name     | owner  | species | sex  | birth      | death      |
+----------+--------+---------+------+------------+------------+
| Fang     | Benny  | dog     | m    | 1990-08-27 | NULL       |
| Bowser   | Diane  | dog     | m    | 1979-08-31 | 1995-07-29 |
+----------+--------+---------+------+------------+------------+
2 rows in set (0.00 sec)

値を範囲で指定することもできる。

【例】テーブルpetからカラムbirthの値が1998-1-1以上（以降）であるデータを全て取り出す
mysql> SELECT * FROM pet WHERE birth >= "1998-1-1";
+----------+-------+---------+------+------------+-------+
| name     | owner | species | sex  | birth      | death |
+----------+-------+---------+------+------------+-------+
| Chirpy   | Gwen  | bird    | f    | 1998-09-11 | NULL  |
| Puffball | Diane | hamster | f    | 1999-03-30 | NULL  |
+----------+-------+---------+------+------------+-------+
2 rows in set (0.00 sec)

カラム値で並べ替えて出力

ORDER BY節を記せば指定カラムの値が昇順または降順となるよう並べ替えて結果を出力することができる（→並べ替え（order）参照）。

【書式】
SELECT 選択するカラムリスト FROM データ取得元テーブル ORDER BY カラム名 ASC | DESC;
【例】テーブルpetからカラムnameとbirthの値を全て取り出し、nameのアルファベット順（昇順）に並べ替えて表示する
mysql> SELECT name, birth FROM pet ORDER BY name; 
+----------+------------+ ↑nameとbirthの項目を抽出、nameで並べる（昇順）。
| name     | birth      |
+----------+------------+
| Bowser   | 1989-08-31 | 
| Buffy    | 1989-05-13 | 
| Chirpy   | 1998-09-11 | 
| Claws    | 1994-03-17 | 
| Fang     | 1990-08-27 | 
| Fluffy   | 1993-02-04 | 
| Puffball | 1999-03-30 | 
| Slim     | 1996-04-29 | 
| Whistler | 1997-12-09 | 
+----------+------------+
9 rows in set (0.00 sec)
【例】テーブルpetからカラムnameとbirthの値を全て取り出し、nameのアルファベット順（昇順）に並べ替えて表示する
mysql> SELECT name, birth FROM pet ORDER BY name ASC; 
+----------+------------+ ↑nameとbirthの項目を抽出、nameで並べる。
| name     | birth      | （昇順指定、指定しなくても同じ）
+----------+------------+
| Bowser   | 1989-08-31 | 
| Buffy    | 1989-05-13 | 
| Chirpy   | 1998-09-11 | 
| Claws    | 1994-03-17 | 
| Fang     | 1990-08-27 | 
| Fluffy   | 1993-02-04 | 
| Puffball | 1999-03-30 | 
| Slim     | 1996-04-29 | 
| Whistler | 1997-12-09 | 
+----------+------------+
9 rows in set (0.00 sec)
【例】テーブルpetからカラムnameとbirthの値を全て取り出し、nameのアルファベット逆順（降順）に並べ替えて表示する
mysql> SELECT name, birth FROM pet ORDER BY name DESC; 
+----------+------------+ ↑nameとbirthの項目を抽出、nameで並べる（降順指定）。
| name     | birth      | 
+----------+------------+
| Whistler | 1997-12-09 | 
| Slim     | 1996-04-29 | 
| Puffball | 1999-03-30 | 
| Fluffy   | 1993-02-04 | 
| Fang     | 1990-08-27 | 
| Claws    | 1994-03-17 | 
| Chirpy   | 1998-09-11 | 
| Buffy    | 1989-05-13 | 
| Bowser   | 1989-08-31 | 
+----------+------------+
9 rows in set (0.00 sec)
【例】並べ替えて出力（数値として）
カラムの型設定がVARCHARなどの文字列設定になっている場合、並べ替えは各文字の文字コード順となる（例：101、11、111…）。
数値の大小で比較して並べ替えたい場合はCAST関数を使って数値へ型変換を行う必要がある。
mysql> SELECT name, age FROM pet WHERE age REGEXP '[0-9]+' ORDER BY age;
+----------+-----+ ↑本文、条件節（where）、ソート節（order by）の順で記述する
| name     | age | 
+----------+-----+
| Whistler |   1 | 
| Slim     |  11 | 
| Puffball |   2 | 
| Fluffy   |   3 | 
+----------+-----+
4 rows in set (0.00 sec)
mysql> SELECT name, age FROM pet WHERE age REGEXP '[0-9]+' ORDER BY CAST(`age` AS SIGNED); 
+----------+-----+                     カラム名はバッククォート（`）で囲む↑
| name     | age | 
+----------+-----+
| Whistler |   1 | 
| Puffball |   2 | 
| Fluffy   |   3 | 
| Slim     |  11 | 
+----------+-----+
4 rows in set (0.00 sec)

【書式】
SELECT 選択するカラムリスト FROM データ取得元テーブル WHERE 条件 ORDER BY カラム名;

【例12】テーブルpetのレコード数を数える
mysql> SELECT COUNT(*) FROM pet; ←レコード数を数える。
+----------+
| COUNT(*) |
+----------+
|        9 |
+----------+
1 row in set (0.00 sec)

【例13】テーブルpetをカラムspecies毎にレコード数を集計して表示する
mysql> SELECT species,COUNT(*) FROM pet GROUP BY species; ←レコード数を数える。
+---------+----------+
| species | COUNT(*) |
+---------+----------+
| cat     |        2 |
| dog     |        3 |
| bird    |        2 |
| snake   |        1 |
| hamster |        1 |
+---------+----------+
5 rows in set (0.00 sec)

【例14】テーブルpetでカラムnameの値がCで始まっているレコードの、カラムnameとbirthの値を表示する
mysql> SELECT name,birth FROM pet WHERE name LIKE "C%"; LIKE節でパーセント(%)は任意の０文字以上を示す
+----------+------------+
| name     | birth      | 
+----------+------------+
| Claws    | 1994-03-17 | 
| Chirpy   | 1998-09-11 | 
+----------+------------+
2 rows in set (0.00 sec)

【例15】テーブルpetでカラムnameがCで始まる５文字（"C"の後に任意の４文字）の値であるレコードの、
カラムnameとbirthの値を表示する（LIKE節を使った場合）。
mysql> SELECT name,birth FROM pet WHERE name LIKE "C____"; LIKE節でアンダーバー(_)は任意の１文字を示す
+----------+------------+
| name     | birth      | 
+----------+------------+
| Claws    | 1994-03-17 | 
+----------+------------+
1 row in set (0.00 sec)

【例16】テーブルpetでカラムnameがCで始まる５文字（"C"の後に任意の４文字）の値であるレコードの、
カラムnameとbirthの値を表示する（正規表現を使った場合）。
mysql> SELECT name,birth FROM pet WHERE name REGEXP "C.{4}";
+----------+------------+　↑.（ドット）は任意の１文字を、{}内の数値は直前の正規表現の繰り返し回数を示す
| name     | birth      | 
+----------+------------+
| Claws    | 1994-03-17 | 
+----------+------------+
1 row in set (0.00 sec)

【例17】テーブルpetのname、'species'と'sex'をアンダースコアで結合した値を取り出す。
mysql> SELECT name,CONCAT(species,'_',sex) FROM pet;
+----------+-------------------------+
| name     | CONCAT(species,'_',sex) | 
+----------+-------------------------+
| Fluffy   | cat_f                   |
| Claws    | cat_m                   |
| Buffy    | dog_f                   |
| Fang     | dog_m                   |
| Bowser   | dog_m                   |
| Chirpy   | bird_f                  |
| Whistler | bird_NULL               |
| Slim     | snake_m                 |
| Puffball | hamster_f               |
+----------+-------------------------+
9 rows in set (0.00 sec)

【例18】前例でカラム名に別名'species_sex'をつける（→ASを用いる）。
mysql> SELECT name,CONCAT(species,'_',sex) AS species_sex FROM pet;
+----------+-------------+
| name     | species_sex | 
+----------+-------------+
| Fluffy   | cat_f       |
| Claws    | cat_m       |
| Buffy    | dog_f       |
| Fang     | dog_m       |
| Bowser   | dog_m       |
| Chirpy   | bird_f      |
| Whistler | bird_NULL   |
| Slim     | snake_m     |
| Puffball | hamster_f   |
+----------+-------------+
9 rows in set (0.00 sec)

select colname from dbname limit startrow,number_of_rows;
startraw starts from 0.

なおNULL値を検索する際、"=NULL" ではなく "IS NULL" を用いる。

【例】テーブルpetのカラムbirthの値がNULLであるレコードを表示する。
SELECT * FROM pet WHERE birth IS NULL;

データ取り出し（重複するものは１回だけ）

SELECT DISTINCT 構文もSELECT 構文と同様にテーブルからデータを取り出すが、重複する値は１回しか取り出さない点が異なる。

【書式】
SELECT DISTINCT カラム名 FROM テーブル名;

テキストからデータ読み込み

LOAD DATA INFILE 構文は、テーブルにデータを読み込む。ファイル名にはデータが格納されているテキストファイルの名前を指定する。カラムの区切り文字の既定値はタブとLF。ファイル名を相対パスで記述した場合、データベースのあるディレクトリ（/var/lib/mysql/dbname/）からのパスとみなされる。不明な値は ¥n で記述しておく。逆にテキストへデータを出力するにはSELECT カラム名 FROM DB名 INTO OUTFILE ファイル名を用いる。

【書式】
LOAD DATA LOCAL INFILE "ファイル名" INTO TABLE テーブル名 FIELDS TERMINATED BY 'フィールド区切り文字' 
ENCLOSED BY 'フィールド値囲み文字' LINES TERMINATED BY '行末文字';

既定の書式は以下の通り。

各行（レコード）の最後は改行（\n）
各要素（フィールド）はタブ（\t）区切り
各要素の値を囲む引用符はなし
タブ、改行、バックスラッシュの直前にバックスラッシュ（\）があった場合、各要素の値の一部と解釈（エスケープ文字はバックスラッシュ）

上記内容は下記の通り設定したのと同じ

FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'
LINES TERMINATED BY '\n' STARTING BY ''

【例１】~/temp/menagerie-db/pet.txt の内容をpetテーブルに読み込む。
mysql> LOAD DATA LOCAL INFILE "~/temp/menagerie-db/pet.txt" INTO TABLE pet;
Query OK, 8 rows affected (0.03 sec)
Records:8  Deleted: 0  Skipped: 0  Warnings: 0

『例２』例１で、フィールド区切りをカンマ（,）、フィールド値囲み文字をダブルクォート（"）、行末文字をCR＋LFに指定。
mysql> LOAD DATA LOCAL INFILE "~/temp/menagerie-db/pet.txt" INTO TABLE pet FIELDS TERMINATED BY ',' 
    -> ENCLOSED BY '"' LINES TERMINATED BY '\r\n';
Query OK, 8 rows affected (0.03 sec)
Records:8  Deleted: 0  Skipped: 0  Warnings: 0

SELECT INTO OUTFILE を使うと LOAD DATA INFILE とは逆にMySQLデータを外部ファイルに書き出すことができる。バックアップの手段として利用できる。詳細はデータベースバックアップ参照。

テキストファイルにデータ書き込み

LOAD DATA INFILE 構文 SELECT 構文に INTO OUTFILE を付ける。LOAD DATA INFILE の逆で指定したファイルに出力内容を書き込む。

【書式】      
SELECT カラム名 FROM テーブル名 INTO OUTFILE ファイル名 FIELDS TERMINATED BY '区切り文字' ENCLOSED BY '値を囲む文字';

【例】CSV（コンマ区切り）、ダブルクォートくくりで出力
SELECT name,birthday FROM data INTO OUTFILE '/tmp/data.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"';

このクエリを実行するには FILE 権限が必要。また書き込み先ディレクトリに mysql 実行ユーザ（通常 mysql）が書き込む権限を持っている必要がある。ファイル名を相対パスで記述した場合、データベースのあるディレクトリ（/var/lib/mysql/dbname/）からのパスとみなされる。

抽出条件記述（where）

SELECTやUPDATEなどの際、指定する条件はWHERE節で指定。

条件の併記、否定（AND, OR, NOT）
選択（IN）
部分一致（LIKE）
正規表現（REGEXP）
参考文献・サイト

条件の併記、否定（AND, OR, NOT）

ANDやORの論理演算子はそのまま記述すれば良い。否定はNOT、<>、!=。

【例】テーブルpetから、カラムspeciesがdogでもcatでもないものを全て抽出。
SELECT * FROM pet WHERE species<>"dog" AND species<>"cat";

【例】テーブルpetから、カラムspeciesがNULLでないものを全て抽出。
SELECT * FROM pet WHERE species IS NOT NULL;

選択（IN）

同一カラムで複数の値を条件に指定する場合など、IN を使って値を列挙し条件を記述することができる。OR でも記述できるが、同一カラムであれば IN を使った方が簡略に表記できる。

【例】テーブルmomindexから、momidが'100','111','113'であるレコードのmomidとrelmomを表示
SELECT momid,relmom FROM momindex WHERE momid IN ('100','111','113');

+-------+--------+
| momid | relmom |
+-------+--------+
|   100 | 58     | 
|   111 | 32     | 
|   113 | 76     | 
+-------+--------+
3 rows in set (0.00 sec)
※下記SQL文でも動作は同じ
SELECT momid,relmom FROM momindex WHERE momid='100' or momid='111' or momid='113';

部分一致（LIKE）

前方一致は "LIKE 'hoge%'" のように記述する（"%" が0文字以上の文字列を意味する、一般的なワイルドカードの "*" に相当）。後方一致なら "LIKE '%hoge'"、どこかに含まれていればいいという部分一致なら "LIKE '%hoge%'"。

MySQL SQL文	一般的なワイルドカード	内容
`%`	`*`	任意の0文字以上の文字列
`_`	`?`	任意の1文字

% や _ の文字そのものを指定したい時は、これらの文字の直前にバックスラッシュ（\）を記してエスケープ処理を行う。

【例】テーブルproteinから、nameが"B"で始まるものを出力
mysql> SELECT id,name FROM protein WHERE name like 'B%';
+-----+-----------------------+
|  id | name                  |
+-----+-----------------------+
|   2 | Bacteriophage phiX174 | 
|  27 | Bacteriorhodopsin     | 
| 115 | beta-Secretase        | 
+-----+-----------------------+
3 rows in set (0.00 sec)

【例】テーブルproteinから、nameが"B"または"D"で始まるものをアルファベット順に出力
mysql> SELECT id,name FROM protein WHERE name LIKE 'B%' OR name LIKE 'D%' ORDER BY name ASC;
+-----+-------------------------+
|  id | name                    |
+-----+-------------------------+
|   2 | Bacteriophage phiX174   | 
|  27 | Bacteriorhodopsin       | 
| 115 | beta-Secretase          | 
| 103 | Dengue Virus            | 
|  70 | Designer Proteins       | 
|  34 | Dihydrofolate Reductase | 
|  23 | DNA                     | 
|  55 | DNA Ligase              | 
|   3 | DNA Polymerase          | 
+-----+-------------------------+
9 rows in set (0.00 sec)

なおLIKEによる部分一致では大文字小文字は区別されない。区別したい場合は LIKE の代わりに LIKE BINARY を用いる。

【例】
mysql> SELECT id,name FROM protein WHERE name LIKE 'b%';
+-----+-----------------------+
|  id | name                  |
+-----+-----------------------+
|   2 | Bacteriophage phiX174 | 
|  27 | Bacteriorhodopsin     | 
| 115 | beta-Secretase        | 
+-----+-----------------------+
3 rows in set (0.00 sec)
mysql> SELECT id,name FROM protein WHERE name LIKE BINARY 'b%';
+-----+----------------+
|  id | name           |
+-----+----------------+
| 115 | beta-Secretase | 
+-----+----------------+
1 row in set (0.00 sec)

正規表現（REGEXP）

正規表現による条件指定を行うにはREGEXP演算子を用いる。正規表現の特殊文字とその役割は以下の通り。

正規表現内容例

^ 文字列の先頭に一致 SELECT 'foopuu' REGEXP '^foo';→1(true)
SELECT 'puufoo' REGEXP '^foo';→0(false)

$ 文字列の末尾に一致 SELECT 'puufoo' REGEXP 'foo$';→1(true)
SELECT 'foopuu' REGEXP 'foo$';→0(false)

. 任意の1文字に一致（改行も含む） SELECT 'pou' REGEXP 'p.u';→1(true)
SELECT 'pom' REGEXP 'p.u';→0(false)

[ ] いずれか1文字に一致（"["の文字を指定する場合は先頭に記載） SELECT 'abcde' REGEXP '[bcd]';→1(true)
SELECT 'abc[de' REGEXP '[[xyz]';→1(true)
SELECT 'aacee' REGEXP '[bcd]';→1(true)

[^ ] 記載の文字以外に一致 SELECT 'axyze' REGEXP '[^bcd]';→1(true)
SELECT 'bcdbc' REGEXP '[^bcd]';→0(false)

[ - ] 範囲の文字に一致 SELECT 'abcde' REGEXP '[b-d]';→1(true)
SELECT 'abcde' REGEXP '[f-g]';→0(false)

* 直前の正規表現を0回以上繰り返し
（{0,}と同じ） SELECT 'f' REGEXP 'fo*';→1(true)
SELECT 'fo' REGEXP 'fo*';→1(true)
SELECT 'foooo!' REGEXP 'fo*';→1(true)
SELECT 'puuuu!' REGEXP 'fo*';→0(false)

+ 直前の正規表現を1回以上繰り返し
（{1,}と同じ） SELECT 'fuu!' REGEXP 'fu+';→1(true)
SELECT 'fu!' REGEXP 'fu+';→1(true)
SELECT 'f' REGEXP 'fu+';→0(false)

? 直前の正規表現を0回または1回繰り返し
（{0,1}と同じ） SELECT 'fp' REGEXP 'fu?p';→1(true)
SELECT 'fup' REGEXP 'fu?p';→1(true)
SELECT 'fuup' REGEXP 'fu?p';→0(false)

() 表現をグループ化 SELECT 'apia' REGEXP 'pi|apa';→1(true)
SELECT 'apia' REGEXP '^(pi|apa)$';→0(false)
SELECT 'pipi' REGEXP '^(pi)*$';→1(true)
SELECT 'pip' REGEXP '^(pi)*$';→0(false)

{n},{n,}{n,m} 直前の正規表現の回数指定（0〜255） SELECT 'au' REGEXP '^(au){1}$';→1(true)
SELECT 'auauau' REGEXP '^(au){1}$';→0(false)
SELECT 'auauau' REGEXP '^(au){1,}$';→1(true)
SELECT 'auauau' REGEXP '^(au){1,3}$';→1(true)
SELECT 'auauau' REGEXP '^(au){4,5}$';→1(true)

[:文字クラス:]

クラスに属する全ての文字と一致

クラス名	別表記	内容
alnum	[0-9a-zA-Z]	英数字
alpha	[a-zA-Z]	英字（大文字小文字）
lower	[a-z]	英小文字
upper	[A-Z]	英大文字

正規表現の特殊文字そのものを指定したい時は、バックスラッシュを２つ直前につけてエスケープ処理を行う（"1+2"そのものを示したい時は"1\\+2"）。

なお正規表現を扱う"REGEXP"表現はマルチバイト文字列には対応しないらしい。マルチバイト文字列にも対応した"mregexp"がひろせまさあき氏により作られ配布されている。

【例】テーブルpdbから、値に'9606'を含むtxidカラムの値を表示（txidは1つ以上の数値をコンマで区切ってつなげた文字列を格納）
SELECT txid FROM pdb WHERE txid REGEXP '(^9606,)|(,9606$)|(,9606,)|(^9606$)';

9606で始まる＝^9606,
9606で終わる＝9606$
9606が中間＝,9606,
9606単独＝^9606$
以上４つのどれか

並べ替え（order）

グループ化（group）

GROUP BY構文で、指定カラム値ごとに集計することができる。

mysql> SELECT date,count(*) FROM table GROUP BY date;
+----------+----------+
|   date   | count(*) |
+----------+----------+
| 2009/9/1 |      731 | 
| 2009/9/2 |     1440 | 
| 2009/9/3 |     1440 | 
| 2009/9/4 |     1440 | 
+----------+----------+
4 rows in set (0.00 sec)

SELECT文などで指定するカラム名のうち、GROUP BYで指定したカラム以外は全て集約関数を指定しておく必要がある。集約関数には以下のようなものがある。

関数	説明
AVG()	平均値を返す
BIT_AND()	ビット単位の論理積（AND）を返す
BIT_OR()	ビット単位の論理和（OR）を返す
BIT_XOR()	ビット単位の排他的論理和（XOR）を返す
COUNT(DISTINCT)	値の件数を返す。但し同一の値はいくつあっても1つと数える。
COUNT()	値の件数を返す。同一の値があっても別々のものとして数える。
GROUP_CONCAT()	連結した文字列を返す
MAX()	最大値を返す
MIN()	最小値を返す
STD()	母標準偏差を返す
STDDEV_POP()	母標準偏差を返す
STDDEV_SAMP()	標本標準偏差を返す
STDDEV()	母標準偏差を返す
SUM()	合計（和）を返す
VAR_POP()	母標準分散を返す
VAR_SAMP()	標本分散を返す
VARIANCE()	母標準分散を返す

テーブルの結合（join）

２つ以上のテーブルを結合する際、両方に存在するレコードのみを取り出す「内部結合」と、一方の全レコードを全て取り出して結合し、結合相手に該当するレコードがなければNULLで埋める「外部結合」とがある。

mysql> SHOW TABLES;

+----------+
| Table    |
+----------+
| tbl_line |
| tbl_stn  |
+----------|
2 rows in set (0.00 sec)
mysql> SELECT * FROM tbl_line;
+--------+--------------------------+
| symbol | name                     |
+--------+--------------------------+
| M      | 御堂筋                | 
| T      | 谷町                   | 
| Y      | 四つ橋                | 
| C      | 中央                   | 
| S      | 千日前                | 
| K      | 堺筋                   | 
| N      | 長堀鶴見緑地       | 
| I      | 今里筋                | 
| P      | 南港ポートタウン | 
+--------+--------------------------+
9 rows in set (0.00 sec)
mysql> SELECT * FROM tbl_stn;
+--------+--------+-----------------------+
| symbol | number | name                  |
+--------+--------+-----------------------+
| M      |     16 | 梅田                | 
| T      |     20 | 東梅田             | 
| Y      |     15 | なんば             | 
| C      |     16 | 本町                | 
| S      |     13 | 南森町             | 
| K      |      9 | コスモスクエア | 
| H      |      8 | 名古屋             | 
| E      |      7 | 名古屋港          | 
+--------+--------+-----------------------+
8 rows in set (0.00 sec)
# tbl_line.symbol と tbl_stn.symbol で両方に一致する値があるもののみ抽出（内部結合）
mysql> SELECT l.symbol,s.number,l.name,s.name FROM tbl_line AS l INNER JOIN tbl_stn AS s ON l.symbol=s.symbol;
# "inner" "as" は省略可能
+--------+--------+-----------+-----------------------+
| symbol | number | name      | name                  |
+--------+--------+-----------+-----------------------+
| M      |     16 | 御堂筋 | 梅田                | 
| T      |     20 | 谷町    | 東梅田             | 
| Y      |     15 | 四つ橋 | なんば             | 
| C      |     16 | 中央    | 本町                | 
| S      |     13 | 千日前 | 南森町             | 
| K      |      9 | 堺筋    | コスモスクエア | 
+--------+--------+-----------+-----------------------+
6 rows in set (0.00 sec)
#「SELECT l.symbol,s.number,l.name,s.name FROM tbl_line AS l,tbl_stn AS s WHERE l.symbol=s.symbol;」でも同じ
# tbl_lineのsymbolカラムに"H","E"という値がないので、「H 8 名古屋」と「E 7 名古屋港」は結果に出てこない。

# tbl_stn にある全レコードと 両テーブルのsymbolカラムで同じ値を持つような tbl_line の name カラムを抽出（外部結合）
mysql> SELECT s.symbol,s.number,s.name,l.name FROM tbl_stn s LEFT JOIN tbl_line l ON s.symbol=l.symbol;
+--------+--------+-----------------------+-----------+
| symbol | number | name                  | name      |
+--------+--------+-----------------------+-----------+
| M      |     16 | 梅田                | 御堂筋 | 
| T      |     20 | 東梅田             | 谷町    | 
| Y      |     15 | なんば             | 四つ橋 | 
| C      |     16 | 本町                | 中央    | 
| S      |     13 | 南森町             | 千日前 | 
| K      |      9 | コスモスクエア | 堺筋    | 
| H      |      8 | 名古屋             | NULL      | ←値"H"が tbl_lineのsymbolカラムに存在しないので tbl_line の nameはNULL
| E      |      7 | 名古屋港          | NULL      | ←値"E"が tbl_lineのsymbolカラムに存在しないので tbl_line の nameはNULL
+--------+--------+-----------------------+-----------+
8 rows in set (0.00 sec)
# 「LEFT JOIN」は先に書いた方のテーブルを全て返す。
  「RIGHT JOIN」は後に書いた方のテーブルを全て返す。

文字列処理

文字列結合

文字列結合

MySQLの場合、CONCAT関数を用いる^[8]。

mysql> SELECT * FROM table WHERE pdbid='1f66';
+-------+-----------+-----------------+---------------------+
| pdbid | entity_id | scientific_name | common_name         |
+-------+-----------+-----------------+---------------------+
| 1f66  |         2 | Xenopus laevis  | African clawed frog |
| 1f66  |         3 | Mus musculus    | house mouse         |
| 1f66  |         4 | Homo sapiens    | human               |
| 1f66  |         5 | Xenopus laevis  | African clawed frog |
+-------+-----------+-----------------+---------------------+
4 rows in set (0.00 sec)
mysql> SELECT CONCAT(pdbid,'-',entity_id) AS pdbent, 
    -> CONCAT(scientific_name,' (',common_name,')') AS biolname FROM table WHERE pdbid='1f66';
+--------+--------------------------------------+
| pdbent | biolname                             |
+--------+--------------------------------------+
| 1f66-2 | Xenopus laevis (African clawed frog) |
| 1f66-3 | Mus musculus (house mouse)           |
| 1f66-4 | Homo sapiens (human)                 |
| 1f66-5 | Xenopus laevis (African clawed frog) |
+--------+--------------------------------------+
4 rows in set (0.00 sec)

データ型・文字コード

データ型一覧
型宣言
扱う文字の文字コード設定
文字コード名

データ型一覧

以下にデータ型を列挙する^3ja-2。

カテゴリ	種別	内容
数値	`BIT[(M)]`	Mビットの値、Mを省略すると1。値は「b'２進数値'」の形で指定する。ビット値を出力可能な形式で表示するには0を足すか、またはBINなどの変換関数を用いる^[]。 mysql> CREATE TABLE t (b BIT(8)); mysql> INSERT INTO t SET b = b'11111111'; mysql> INSERT INTO t SET b = b'1010'; mysql> INSERT INTO t SET b = b'0101'; mysql> SELECT b+0, BIN(b+0), OCT(b+0), HEX(b+0) FROM t; +------+----------+----------+----------+ \| b+0 \| BIN(b+0) \| OCT(b+0) \| HEX(b+0) \| +------+----------+----------+----------+ \| 255 \| 11111111 \| 377 \| FF \| \| 10 \| 1010 \| 12 \| A \| \| 5 \| 101 \| 5 \| 5 \| +------+----------+----------+----------+
	`TINYINT[(M)] [UNSIGNED] [ZEROFILL]`	符合あり-128〜127、符合なし0〜255。UNSIGNEDが指定されると符合なし（以下同じ）。ZEROFILLを指定すると、空の桁が0で埋められる（指定しないと空白、以下同様）。Mは表示最小幅を指定。TINYINT(2)は１桁時には10の位に空白（または0）で埋める。２桁以上はそのまま表示。
	`BOOL、BOOLEAN`	`TINYINT(1)`と同じ。0=false,0以外=true、true→1、false→0
	`SMALLINT[(M)] [UNSIGNED] [ZEROFILL]`	符号あり-32768〜32767、符号なし0〜65535。
	`MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]`	符号あり-8388608〜8388607、符号なし0〜16777215。
	`INT[(M)] [UNSIGNED] [ZEROFILL]`	符号あり-2147483648〜2147483647、符号なし0〜4294967295。型名をINTEGERとしても同じ。
	`BIGINT[(M)] [UNSIGNED] [ZEROFILL]`	符号あり-9223372036854775808〜9223372036854775807、符号なし0〜18446744073709551615。
	`FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]`	単精度小数点数。許容値は -3.402823466E+38 〜 -1.175494351E-38、0、1.175494351E-38 〜 3.402823466E+38（IEEE基準に基づいた理論的限界値、ハードウェアやOSによっては、これより小さくなりうる）。M は桁数合計、D は小数点以下の桁数。M と D を省略した場合、値はハードウェアが許容する限界まで格納される。概ね小数第７位まで正確。
	`DOUBLE[(M、D)] [UNSIGNED] [ZEROFILL]`	倍精度小数点数。許容値は -1.7976931348623157E+308 〜 -2.2250738585072014E-308、0、2.2250738585072014E-308 〜 1.7976931348623157E+308（IEEE基準に基づいた理論的限界値、ハードウェアやOSによっては、これより小さくなりうる）。M は桁数合計、D は小数点以下の桁数。M と D を省略した場合、値はハードウェアが許容する限界まで格納される。概ね小数第15位まで正確。
	`DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]`	固定小数点値。M は桁数合計、D は小数点以下の桁数の合計。小数点とマイナス記号‘-’は M の中に含まない。D が0の時は整数。DECIMAL の桁数合計(M)の最大は65、小数点以下桁数(D)の最大は30。D を省略した時の既定値は0。M を省略した時の既定値は10。
日付時刻	`DATE`	日付値。'1000-01-01' 〜 '9999-12-31'
	`DATETIME`	日付と時刻の組み合わせ。'1000-01-01 00:00:00' 〜 '9999-12-31 23:59:59'
	`TIMESTAMP`	積算時刻値。'1970-01-01 00:00:01' UTCから 2037年の途中まで。'1970-01-01 00:00:00'からの積算秒数。
	`TIME`	時刻値。'-838:59:59' 〜 '838:59:59' 。
	`YEAE[(2\|4)]`	西暦を示す値。(2)を指定すると西暦下２桁で1970年〜2069年を示す。(4)を指定すると西暦４桁値で、1901年〜2155年を示す。既定値は４桁。
文字列	`CHAR(M)`	固定長文字列。Mは文字列の長さで0〜255文字。CHARACTERも同じ。CHAR(0)は使わないがないと動作しない古いアプリケーション対応や、２値さえあればよいカラムに利用。CHAR(0) NULLと定義したカラムは1ビットを使用し、NULLと''（空の文字列）のどちらかを格納できる。文字数(M)を省略した時の既定値は1。
	`VARCHAR(M)`	可変長文字列。Mはカラムの最大長で0〜65535。CHARACTER VARYINGの省略表現
	`BINARY(N)`	固定長バイナリ文字列格納用。文字コードを考慮せず、バイナリレベルで比較などを行う。N はバイト数。
	`VARBINARY(N)`	BINARY(N)の可変長版。
	`TINYBLOB`	最長255バイトのバイナリデータ（Binary Large OBject, BLOB）格納用。何でも入る。既定値は設定できない。インデックスを設定する際、インデックス長の指定が必須。
	`TINYTEXT`	最長255バイトの文字列データ格納用。既定値は設定できない。インデックスを設定する際、インデックス長の指定が必須。インデックスを設定する場合、インデックスに取り込む時に後ろに空白が詰められるため、末尾の空白有無だけの違いの値があれば重複キーエラーになる。
	`BLOB[(M)]`	最長65535バイトのバイナリデータ格納用。何でも入る。既定値は設定できない。インデックスを設定する際、インデックス長の指定が必須。長さMを指定すればMバイト格納に必要なだけの領域を確保。
	`TEXT[(M)]`	BLOBの文字列版。
	`MEDIUMBLOB`	最長16777215（2²⁴−1）バイトのバイナリデータ格納用。何でも入る。既定値は設定できない。インデックスを設定する際、インデックス長の指定が必須。
	`MEDIUMTEXT`	MEDIUMBLOBの文字列版。
	`LONGBLOB`	最長4,294,967,295（2³²−1）バイト（≒4GB）のバイナリデータ格納用。何でも入る。既定値は設定できない。インデックスを設定する際、インデックス長の指定が必須。実際有効な最大長は、クライアント・サーバのプロトコルと使用可能なメモリ量に依存する。
	`LONGTEXT`	LONGBLOBの文字列版。
	`ENUM('値1','値2'`,...)	列挙した値の中から１つだけ選択する形式の値。最大65,535個の値を列挙できる。内部的には何番の値が選択されているかを示す整数値として格納される。既定値は最初の列挙値。
	`SET('値1','値2'`,...)	列挙した値の中から０個以上任意数選択する形式の値。最大64個の値を列挙できる。内部的には何番の値が選択されているかを示す整数値として格納される（→MySQL :: MySQL 5.6 リファレンスマニュアル :: 11.4.5 SET 型）。

テーブルの結合

内部結合
外部結合
参考文献・サイト

内部結合

両方のテーブルに共通して存在するレコードだけを抜き出す。

mysql> SELECT * FROM pdb;
+-------+----------+
| pdbid | ec       |
+-------+----------+
| 1ca2  | 4.2.1.1  |
| 1htb  | 1.1.1.1  |
| 1oco  | 1.9.3.1  |
| 2frv  | 1.12.7.2 |
| 3ldh  | 1.1.1.27 |
+-------+----------+
5 rows in set (0.00 sec)
mysql> SELECT * FROM ec;
+----------+-------------------------+
| ec       | name                    |
+----------+-------------------------+
| 1.1.1.1  | Alcohol dehydrogenase   |
| 1.1.1.21 | Aldehyde reductase      |
| 1.1.1.27 | L-lactate dehydrogenase |
| 1.1.1.37 | Malate dehydrogenase    |
| 1.9.3.1  | Cytochrome-c oxidase    |
| 4.2.1.1  | Carbonate dehydratase   |
+----------+-------------------------+
6 rows in set (0.00 sec)
mysql> SELECT * FROM pdb INNER JOIN ec ON pdb.ec=ec.ec;
+-------+----------+----------+-------------------------+
| pdbid | ec       | ec       | name                    |
+-------+----------+----------+-------------------------+
| 1htb  | 1.1.1.1  | 1.1.1.1  | Alcohol dehydrogenase   | 
| 3ldh  | 1.1.1.27 | 1.1.1.27 | L-lactate dehydrogenase | 
| 1oco  | 1.9.3.1  | 1.9.3.1  | Cytochrome-c oxidase    | 
| 1ca2  | 4.2.1.1  | 4.2.1.1  | Carbonate dehydratase   | 
+-------+----------+----------+-------------------------+
4 rows in set (0.00 sec)
mysql> SELECT pdb.pdbid, pdb.ec, ec.name FROM pdb INNER JOIN ec ON pdb.ec=ec.ec;
+-------+----------+-------------------------+
| pdbid | ec       | name                    |
+-------+----------+-------------------------+
| 1htb  | 1.1.1.1  | Alcohol dehydrogenase   | 
| 3ldh  | 1.1.1.27 | L-lactate dehydrogenase | 
| 1oco  | 1.9.3.1  | Cytochrome-c oxidase    | 
| 1ca2  | 4.2.1.1  | Carbonate dehydratase   | 
+-------+----------+-------------------------+
4 rows in set (0.00 sec)

外部結合

LEFT JOINはクエリ文で先（左）に出てくるテーブル名にあるレコードは全部返す。もし後（右）に出てくるテーブル中に該当するカラム値がなかったらNULLで埋められる。RIGHT JOINは逆に右に出てくるテーブルにあるレコードは全て返す。左右のテーブルの結合条件はONの後に記す。

mysql> SELECT * FROM pdb LEFT JOIN ec ON pdb.ec=ec.ec;
+-------+----------+----------+-------------------------+
| pdbid | ec       | ec       | name                    |
+-------+----------+----------+-------------------------+
| 1ca2  | 4.2.1.1  | 4.2.1.1  | Carbonate dehydratase   | 
| 1htb  | 1.1.1.1  | 1.1.1.1  | Alcohol dehydrogenase   | 
| 1oco  | 1.9.3.1  | 1.9.3.1  | Cytochrome-c oxidase    | 
| 2frv  | 1.12.7.2 | NULL     | NULL                    | 
| 3ldh  | 1.1.1.27 | 1.1.1.27 | L-lactate dehydrogenase | 
+-------+----------+----------+-------------------------+
5 rows in set (0.00 sec)
mysql> SELECT * FROM pdb RIGHT JOIN ec ON pdb.ec=ec.ec;
+-------+----------+----------+-------------------------+
| pdbid | ec       | ec       | name                    |
+-------+----------+----------+-------------------------+
| NULL  | NULL     | 1.1.1.37 | Malate dehydrogenase    | 
| NULL  | NULL     | 1.1.1.21 | Aldehyde reductase      | 
| 1ca2  | 4.2.1.1  | 4.2.1.1  | Carbonate dehydratase   | 
| 1htb  | 1.1.1.1  | 1.1.1.1  | Alcohol dehydrogenase   | 
| 1oco  | 1.9.3.1  | 1.9.3.1  | Cytochrome-c oxidase    | 
| 3ldh  | 1.1.1.27 | 1.1.1.27 | L-lactate dehydrogenase | 
+-------+----------+----------+-------------------------+
6 rows in set (0.00 sec)

NATURAL LEFT/RIGHT JOINを使うと、左右のテーブルにある同じカラムキー名のカラム値を比較し、一致するものをもとに結合する。この場合ON条件節を記すとエラーになる。

mysql> SELECT * FROM pdb NATURAL LEFT JOIN ec;
+----------+-------+-------------------------+
| ec       | pdbid | name                    |
+----------+-------+-------------------------+
| 4.2.1.1  | 1ca2  | Carbonate dehydratase   | 
| 1.1.1.1  | 1htb  | Alcohol dehydrogenase   | 
| 1.9.3.1  | 1oco  | Cytochrome-c oxidase    | 
| 1.12.7.2 | 2frv  | NULL                    | 
| 1.1.1.27 | 3ldh  | L-lactate dehydrogenase | 
+----------+-------+-------------------------+
5 rows in set (0.00 sec)
下記クエリの結果は上記のものと同じ
mysql> SELECT pdb.ec, pdb.pdbid, ec.name FROM pdb LEFT JOIN ec ON pdb.ec=ec.ec;

関数

各種関数、演算子について^3ja-3。

文字列処理、型変換
演算関係
日付時刻関係

文字列処理、型変換

文字列処理、型変換などに関する関数。

文字の結合（CONCAT）
件数を数える（COUNT）
最大値（MAX）
最小値（MIN）
文字列の左一部を返す（LEFT）
文字列の右一部を返す（RIGHT）
文字列の一部を返す（SUBSTRING）

関数	内容
ASCII(文字列)	指定した文字列の１文字目（１バイト目）の文字コードを返す。空文字の場合は0、NULLの場合はNULLを返す。マルチバイト文字も１文字として扱いたい場合はORDを用いる。 mysql> select ascii('abc'); +--------------+ \| ascii('abc') \| +--------------+ \| 97 \| ←'a'のキャラクターコード +--------------+ 1 row in set (0.01 sec)
BIN(数値)	十進数値をバイナリ値（２進数）に変換した文字列を返す。「CONV(数値,10,2)」と同じ。 mysql> select bin(12); +---------+ \| bin(12) \| +---------+ \| 1100 \| +---------+ 1 row in set (0.02 sec)
CONCAT(文字列1,文字列2...)	複数の文字列を結合する。カラム指定でも使える。 mysql> SELECT company,line,CONCAT(company,'-',line) AS compline FROM railway WHERE company='大阪市交通局'; +--------------+------------------+-------------------------------+ \| company \| line \| compline \| +--------------+------------------+-------------------------------+ \| 大阪市交通局 \| 御堂筋 \| 大阪市交通局-御堂筋 \| \| 大阪市交通局 \| 谷町 \| 大阪市交通局-谷町 \| \| 大阪市交通局 \| 四つ橋 \| 大阪市交通局-四つ橋 \| \| 大阪市交通局 \| 中央 \| 大阪市交通局-中央 \| \| 大阪市交通局 \| 千日前 \| 大阪市交通局-千日前 \| \| 大阪市交通局 \| 堺筋 \| 大阪市交通局-堺筋 \| \| 大阪市交通局 \| 長堀鶴見緑地 \| 大阪市交通局-長堀鶴見緑地 \| \| 大阪市交通局 \| 今里筋 \| 大阪市交通局-今里筋 \| \| 大阪市交通局 \| 南港ポートタウン \| 大阪市交通局-南港ポートタウン \| +--------------+------------------+-------------------------------+ 9 row in set (0.00 sec)
CONV(数値,変換前基数,変換後基数)	数値の基数（何進数か）を変換して変換後の数値を返す。引数のいずれかがNULLであればNULLを返す。指定できる基数の範囲は2〜36、変換後基数が負数の場合は符合付き数を返す。 mysql> select conv(12,10,2),conv(12,10,16); +---------------+----------------+ \| conv(12,10,2) \| conv(12,10,16) \| +---------------+----------------+ \| 1100 \| C \| +---------------+----------------+ 1 row in set (0.00 sec) mysql> select conv('ab',16,10),conv('ab',16,2); ※非数字文字列はクォート記号で囲まないとエラーになる +------------------+-----------------+ \| conv('ab',16,10) \| conv('ab',16,2) \| +------------------+-----------------+ \| 171 \| 10101011 \| +------------------+-----------------+ 1 row in set (0.00 sec)
HEX(数値)、HEX(文字列)	引数に10進数値を指定した場合、16進数に変換した文字列を返す。「CONV(数値,10,16)」と同じ。引数に文字列を指定した場合、各文字のキャラクターコードを16進数表現した値を返す。 mysql> select hex(255); +----------+ \| hex(255) \| +----------+ \| FF \| ←10進数の255を16進数に変換した値 +----------+ 1 row in set (0.00 sec) mysql> select hex('abc'); +------------+ \| hex('abc') \| +------------+ \| 616263 \| ←各文字のキャラクターコードは16進数表記で61、62、63 +------------+ 1 row in set (0.00 sec)
LEFT(文字列,バイト数)	LEFTは指定文字列の左端から、指定バイト分だけを取り出して返す。マルチバイト文字列の場合、１文字のバイト数を考慮する必要あり（UTF-8の場合、日本語文字列は１文字当たり通常３バイトを取る）。【例】文字コードがUTF-8の場合 mysql> SELECT name FROM table WHERE id='100'; +--------------------+ \| name \| +--------------------+ \| アドレナリン受容体 \| +--------------------+ mysql> SELECT LEFT(name,18) FROM table WHERE id='100'; +-----------------+ \| LEFT(name,18) \| +-----------------+ \| アドレナリン \| +-----------------+
ORD(文字列)	指定した文字列の１文字目の文字コードを返す。マルチバイト文字も１文字として処理する？
RIGHT(文字列,バイト数)	RIGHTは指定文字列の右端から、指定バイト分だけを取り出して返す。マルチバイト文字列の場合、１文字のバイト数を考慮する必要あり（UTF-8の場合、日本語文字列は１文字当たり通常３バイトを取る）。
SUBSTRING(文字列,位置,長さ) SUBSTRING(文字列 FROM 位置 FOR 長さ)	指定した文字列の一部を返す。引数をコンマで区切る書式と FROM, FORを使う書式、どちらでも機能は同じ（後者は標準のSQL構文）。SUBSTR、MIDはSUBSTRINGの別名として使える。位置切り出し開始位置を指定する。自然数を指定すると先頭から、負数を指定すると末尾から文字数（正確にはバイト数）を数えた位置指定とみなされる（先頭が1、末尾が-1）。指定位置の文字は切り出し対象に含まれる。長さ何文字切り出すかを指定する。指定がない場合は末尾まで全てが切り出し対象となる。【例】 mysql> SELECT enname,janame FROM table WHERE id='100'; +----------------------+--------------------+ \| enname \| janame \| +----------------------+--------------------+ \| Adrenergic Receptors \| アドレナリン受容体 \| +----------------------+--------------------+ 1 row in set (0.00 sec) mysql> SELECT SUBSTRING(enname,12) FROM table WHERE id='100'; +----------------------+ \| SUBSTRING(enname,12) \| +----------------------+ \| Receptors \| +----------------------+ 1 row in set (0.00 sec) mysql> SELECT SUBSTRING(janame,19,6) FROM table WHERE id='100'; +------------------------+ \| SUBSTRING(janame,19,6) \| # UTF-8の場合、日本語は１文字３バイト +------------------------+ # 7文字目は19バイト目から6バイト分となる \| 受容 \| +------------------------+ 1 row in set (0.00 sec) mysql> SELECT SUBSTRING(janame,-18,6) FROM table WHERE id='100'; +-------------------------+ \| SUBSTRING(janame,-18,6) \| +-------------------------+ \| ナリ \| +-------------------------+ 1 row in set (0.00 sec)

演算関係

参考：MySQL :: MySQL 5.6 リファレンスマニュアル :: 12.6 数値関数と演算子

余り（剰余）

関数	内容
被除数 DIV 除数	被除数を除数で割った商を返す。FLOORに似ているが BIGINTでも問題なく扱える。/（スラッシュ）による除算は小数点以下まで計算するが、DIVは整数の範囲内で商を返す。
MOD(被除数,除数) 被除数 % 除数被除数 MOD 除数	被除数を除数で割った余りを返す。
POW(基数,乗数) POWER(基数,乗数)	基数を乗数乗した値を返す。

日付時刻関係

参考：MySQL :: MySQL 5.6 リファレンスマニュアル :: 12.7 日付および時間関数

関数内容

DATE_FORMAT(日付時刻値,書式指定)

指定した書式で日付時刻値を出力する。書式指定に用いる指定子は以下の通り。

グループ	指定子	内容
年	`%Y`	西暦4桁例： mysql> SELECT DATE_FORMAT('2011-05-26 15:00:00','%Y'); +-----------------------------------------+ \| DATE_FORMAT('2011-05-26 15:00:00','%Y') \| +-----------------------------------------+ \| 2011 \| +-----------------------------------------+ 1 row in set (0.03 sec)
	`%y`	西暦下2桁
	`%X`	西暦4桁、但し年の開始は年最初の日曜。%V と併用する。
	`%x`	西暦4桁、但し年の開始は年最初の月曜。%v と併用する。
月	`%M`	正式英語月名（January-December）
	`%b`	簡略英語月名（Jan-Dec）
	`%m`	月2桁固定（00-12、00は未設定時の値）
	`%c`	月桁数可変（0-12、0は未設定時の値）
週	`%U`	週2桁固定日曜始まり（00-53、00は未設定時の値）
	`%u`	週2桁固定月曜始まり（00-53、00は未設定時の値）
	`%V`	週2桁固定日曜始まり（01-53）、%X と併用。
	`%v`	週2桁固定月曜始まり（01-53）、%x と併用。
曜日	`%W`	正式英語曜日名（Sunday,..,Saturday）
	`%a`	簡略英語曜日名（Sun,..,Sat）
	`%w`	曜日を数値表記（0=Sunday,..,6=Saturday）
日	`%d`	日2桁固定（00-31、00は未設定時の値）
	`%e`	日桁数可変（0-31、0は未設定時の値）
	`%D`	日桁数可変英語接頭辞付き（0th,1st,2nd,3rd,4th,...、0thは未設定時の値）
	`%j`	年間通日3桁固定（000-366、000は未設定時の値）
午前午後	`%p`	午前午後（AM または PM）
時	`%H`	24時制の時、2桁固定（00-23）
	`%h`	12時制の時、2桁固定（01-12）
	`%I`	12時制の時、2桁固定（01-12）
	`%k`	24時制の時、桁可変（0-23）
	`%h`	12時制の時、桁可変（1-12）
分	`%i`	分、2桁固定（00-59）
秒	`%S`	秒、2桁固定（00-59）
秒	`%s`	秒、2桁固定（00-59）
マイクロ秒	`%f`	マイクロ秒、6桁固定（000000-999999）
時分秒	`%T`	24時制の時分秒（hh:mm:ss）
時分秒	`%r`	12時制の時分秒＋午前午後（hh:mm:ss AMまたはPM）
その他	`%上記にない文字`	文字そのもの

UNIX_TIMESTAMP(日付時刻値) 指定された日付時刻値に対応するUNIXタイムスタンプ（1970/1/1 00:00:00 UTC からの積算秒数）を返す。値は現在の時間帯における日付時刻値であると解釈され、相当するUTC値に変換される。指定がない場合は現在時刻に対応する値が返る。

FROM_UNIXTIME(UNIXタイムスタンプ)

指定されたUNIXタイムスタンプ（1970/1/1 00:00:00 UTC からの積算秒数）に対応する日付時刻値を返す。

mysql> select FROM_UNIXTIME(1465266814);
+---------------------------+
| FROM_UNIXTIME(1465266814) |
+---------------------------+
| 2016-06-07 11:33:34       | 
+---------------------------+
1 row in set (0.00 sec)

二重化・複製・バックアップ

データの信頼性向上や障害対策のために、サーバの二重化やデータの複製を行うことができる。

サーバ間でのデータベース複製
データベースバックアップ
サーバの二重化

サーバ間でのデータベース複製

複製用ユーザの作成（マスタ）
マスタの設定
マスタ複製情報の取得
データベーススナップショットの作成
スレーブの設定
スナップショットの取り込み（スレーブ）
スレーブ設定２
レプリケーション再構築
古いバイナリログの削除
参考文献・サイト

複製用ユーザの作成（マスタ）

複製用のMySQLユーザをマスタ・スレーブの両方に用意する。既存ユーザでも構わないが、REPLICATION SLAVE権限を持っている必要がある。新規に複製用ユーザを作成するコマンドの書式は以下の通り。

【書式】
GRANT REPLICATION SLAVE ON 複製するデータベース.複製するテーブル TO 'ユーザ名'@'ドメイン'
IDENTIFIED BY 'パスワード';
※4.0.2より古いMySQLには「REPLICATION SLAVE」という権限は存在しないので、代わりに「FILE ON」を指定する。

【例】ドメイン mydomain.com 内のすべてのホストに全データベースの複製を許可するユーザとして repl を設定する。
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%.mydomain.com' IDENTIFIED BY 'slavepass';

※この類の操作（ファイル権限の操作）は全体にしか適用できず、「複製するデータベース」を限定することはできないらしい⁶（そのような指定を行うと「ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES」エラーとなる）。つまり「複製するデータベース.複製するテーブル」には「*.*」しか指定できないということ。

複製の監視・管理には REPLICATION CLIENT の権限も必要。合わせて権限を追加しておく方が便利らしい³。

マスタの設定

マスタサーバの/etc/my.cnfにserver-idの設定と、バイナリロギングを有効化する設定を行う。idは my.cnfがない場合はテンプレートから作成→こちら。

【マスタ my.cnf の書式】
[mysqld]
log-bin   = バイナリログファイル名 # 複製マスタサーバになるのに必要なバイナリログを作る設定
server-id = 整数 # サーバを特定するサーバごとの固有値

【マスタ my.cnf の例】
[mysqld]
log-bin   = mysql-bin
server-id = 100

バイナリログファイル名: 指定がない場合はサーバのホスト名が使われるが、ホスト名が変わる場合は問題になる可能性がある。マスタとスレーブで同じ名前を指定しておくとスレーブからマスタへの昇格が容易になる。
server-id値: 他と重複しない一意なもので、1〜2³²-1の整数。但し、1 や 2 はマスタやスレーブの既定値として使われるため、避けた方が望ましい。マスタおよびスレーブが同一のサブネット内にあり、IPアドレスが変化しないのであれば最終オクテット値を使うのも一つの手（例：192.168.0.100 なら 100）。

/etc/my.cnf設定保存後、MySQLサービスを再起動。

server-shell> /etc/rc.d/init.d/mysqld restart

※システムによってはコマンド名が異なることもある。
※restartをサポートせず、停止と起動を分けて行う必要がある場合もある。
server-shell> /etc/rc.d/init.d/mysql stop
server-shell> /etc/rc.d/init.d/mysql start
※root権限が必要なので、必要に応じ前に sudo を付ける

マスタ複製情報の取得

server-mysql> SHOW MASTER STATUS;
+---------------+----------+--------------+------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------+----------+--------------+------------------+
| mysql-bin.001 | 73       |              |                  |
+---------------+----------+--------------+------------------+
1 row in set (0.00 sec)

この値は、スレーブで必要となるので控えておく。

データベーススナップショットの作成

(1)未書き込みのキャッシュを処理して、データベースの書き込みを禁止する（read lock）
server-mysql> echo "FLUSH TABLES WITH READ LOCK;" | mysql -uroot -ppassword
※ユーザ指定（-uMySQLユーザ名）はRELOAD権限のあるユーザを使用する。

(2)ダンプファイル生成
【全データベースを対象にする】
server-shell> mysqldump --single-transaction --master-data -uMySQLユーザ名 -pパスワード --all-databases
              > スナップショットファイル名
【データベースを指定】
server-shell> mysqldump --single-transaction --master-data -uMySQLユーザ名 -pパスワード データベース名
              > スナップショットファイル名
※--single-transaction：トランザクション開始時点でのデータがダンプされる。
トランザクショナルテーブル（トランザクションテーブル、処理によってレコード数が増えていくテーブル）を使っていない
（＝参照（マスタテーブル）しか使わない）場合、--lock-all-tables
※mysqldumpコマンドについての詳細はmysqldump参照。

(3)書き込みを禁止ロックを解除
MySQLに戻ってロック解除
server-mysql> echo "UNLOCK TABLES;" | mysql -uroot -ppassword

【例】３ステップをまとめたスクリプト
#!/bin/sh
echo "FLUSH TABLES WITH READ LOCK;" | mysql -uroot -ppassword
mysqldump --single-transaction --master-data -uroot -ppassword --all-databases > mysql.dump
echo "UNLOCK TABLES;" | mysql -uroot -ppassword

スレーブの設定

マスタと同様に/etc/my.cnfの設定を行う。

【スレーブ my.cnf の書式】
[mysqld]
server-id= 整数
log_bin  = バイナリログファイル名
relay_log = リレーログファイル名

replicate-do-db=複製対象データベース名
replicate-ignore-db=複製除外データベース名

【スレーブ my.cnf の例】
[mysqld]
server-id = 101
log_bin   = mysql-bin
relay_log = mysql-relay-bin

server-idには複製マスタや他のスレーブと重複しない一意なもので、1〜2³²-1の整数を指定する。master-hostにはマスタのホスト名またはIPアドレスを指定する。master-hostを指定しないとスレーブスレッドは開始されない²→この指定はmy.cnfに書かず、CHANGE MASTER TO構文を使用した方がいいらしい³

/etc/my.cnf設定保存後、MySQLサービスを停止。すぐにスレーブへの複製を開始しないオプション--skip-slaveを付けてMySQLサービス起動。

slave-shell> /etc/rc.d/init.d/mysqld stop
slave-shell> /etc/rc.d/init.d/mysqld start --skip-slave

スナップショットの取り込み（スレーブ）

スレーブに既存のデータがある場合、(1)マスタで作成したスナップショットファイルをスレーブにコピーして、(2)スレーブのMySQLデータベースにインポート。スレーブにはまだデータがない場合や、データベースを別途手作業で削除（DROP）して、最初のバイナリログ位置から同期するなら不要。

slave-shell> rsync -avz -e ssh
              マスタサーバユーザ名@マスタサーバホスト名.マスタサーバドメイン名:スナップショットファイルフルパス名 ./
slave-shell> mysql -uMySQLユーザ名 -pパスワード < スナップショットファイル名

スレーブ設定２

マスタの設定を行った上でスレーブ動作を開始する。なおスナップショットを作成する際、--master-dataオプションをつけておけば、CHANGE MASTER TO の内容がスナップショットに含まれるので START SLAVE でスレーブ動作を開始するだけでよい？

slave-shell> mysql -uMySQLユーザ名 -pパスワード
slave-mysql> STOP SLAVE; ←スレーブとして動作しているならスレーブ動作を止める
slave-mysql> CHANGE MASTER TO
          -> MASTER_HOST='マスタサーバホスト名.サーバドメイン名,
          -> MASTER_USER='複製を行うマスタサーバユーザ名,
          -> MASTER_PASSWORD='複製ユーザのパスワード',
          -> MASTER_LOG_FILE='ログファイル名', ←マスタ複製情報の取得で確認した情報より
          -> MASTER_LOG_POS=ログ位置; ←マスタ複製情報の取得で確認した情報より（バイナリログの最初を指定するなら0）
slave-mysql> START SLAVE;

スレーブが動作を開始すると、定期的に複製が行われる。同期しているかどうかは、マスタとスレーブそれぞれのステータスを確認し、Master_Log_file、Read_Master_Log_Pos の値が両者で一致していることで確認できる。

mysql-master> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |    40482 |              |                  | 
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
mysql-slave> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
            Slave_IO_State: Waiting for master to send event ←このメッセージならOK
               Master_Host: pdbjkw1.pdbj.org
               Master_User: repl
               Master_Port: 3306
             Connect_Retry: 60
           Master_Log_File: mysql-bin.000004
       Read_Master_Log_Pos: 341476
            Relay_Log_File: mysqld-relay-bin.000002
             Relay_Log_Pos: 306
     Relay_Master_Log_File: mysql-bin.000004
          Slave_IO_Running: Yes ←YesになっていたらOK
         Slave_SQL_Running: Yes ←YesになっていたらOK
...
1 row in set (0.00 sec)

レプリケーション再構築

マスタでディスクが満タンになってバイナリログが追加できなくなり（→下記mysqld.log参照）、レプリケーションが止まってしまい、スレーブステータス情報で "Slave_IO_State: Connection to master."、"Slave_IO_Running: No" となった場合の対処方法（よりスマートな方法があるのかもしれないが、とりあえず以下の方法で回復した）。

---- /var/log/mysqld.log
100806  9:50:10 [ERROR] /usr/libexec/mysqld: Disk is full writing './mysql-bin.000006' (Errcode: 28). 
Waiting for someone to free space... Retry in 60 secs

[Slave]スレーブ動作停止
[Slave]レプリケーション対象となっているデータベースをスレーブ側で一旦削除する
[Master]データベーススナップショットの作成
[Master]マスタステータス情報取得
[Slave]データベーススナップショットの取り込み
[Slave]マスタ設定
[Slave]スレーブ動作開始

【例】wikidb, momdbを削除して再構築
1. [Slave]スレーブ動作停止
slave-shell> mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1234
Server version: 5.0.77-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

slave-mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

2. [Slave]データベース削除
slave-mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema | 
| momdb              | # 削除対象
| mysql              | 
| test               | 
| wikidb             | # 削除対象 
+--------------------+
5 rows in set (0.00 sec)
slave-mysql> drop database wikidb;
Query OK, 41 rows affected (0.04 sec)
slave-mysql> drop database momdb;
Query OK, 7 rows affected (0.01 sec)
+--------------------+
| Database           |
+--------------------+
| information_schema | 
| mysql              | 
| test               | 
+--------------------+
3 rows in set (0.00 sec)
※以下のエラーで削除できない場合、データディレクトリを直接削除することで対処可能^[5]
slave-mysql> drop database momdb;
ERROR 1010 (HY000): Error dropping database (can't rmdir './momdb/', errno: 17)
slave-mysql> exit
slave-shell> rm -rf /var/lib/mysql/momdb # パスは環境に依存する

3. [Master]データベーススナップショットの作成
master-shell> ./create_dbdump.sh # スナップショット作成スクリプト、データベーススナップショットの作成参照
master-shell> ls mysql.dump
mysql.dump

4. [Master]マスタステータス情報取得
master-shell> mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1234
Server version: 5.0.77-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

slave-mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000007 |   180008 |              |                  | 
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

5. [Slave]データベーススナップショットの取り込み
slave-shell> rsync -avz root@master:~/mysql.dump .
root@server's password: 
receiving file list ... done
mysql.dump

sent 42 bytes  received 1636001 bytes  363565.11 bytes/sec
total size is 12747000  speedup is 7.79
slave-shell> mysql -uroot -p < mysql.dump
Enter password: 

6. [Slave]マスタ設定
slave-shell> mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1234
Server version: 5.0.77-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

slave-mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

slave-mysql> change master to 
          -> master_host='master',
          -> master_user='repl',
          -> master_password='hoge',
          -> master_log_file='mysql-bin.000007', # Step4で確認した値
          -> master_log_pos=180008; # Step4で確認した値
Query OK, 0 rows affected (0.00 sec)

7. [Slave]スレーブ動作開始
slave-mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
slave-mysql> show slave status\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event # スレーブ動作OK
                Master_Host: master
                Master_User: repl
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000007
        Read_Master_Log_Pos: 180008
             Relay_Log_File: mysqld-relay-bin.000002
              Relay_Log_Pos: 235
      Relay_Master_Log_File: mysql-bin.000007
           Slave_IO_Running: Yes # スレーブ動作OK
          Slave_SQL_Running: Yes # スレーブ動作OK
（後略）

古いバイナリログの削除

マスタサーバにおけるバイナリログは蓄積していって肥大化するので、適宜古いバイナリログは削除する。

master-shell> ls /var/lib/mysql/mysql.bin*
mysql-bin.000001  mysql-bin.000004  mysql-bin.000007
mysql-bin.000002  mysql-bin.000005
mysql-bin.000003  mysql-bin.000006
master-shell> mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1234
Server version: 5.0.77-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

master-mysql> purge master logs to 'mysql-bin.000007';
Query OK, 0 rows affected (0.05 sec)
※"PURGE MASTER LOGS before now();"で、ファイル名を確認しなくても操作可能との情報あり^[6]（未確認）

一定期間が経過したバイナリログを自動削除するには、システム変数「expire_logs_days」を設定する。MySQL設定ファイル（/etc/my.cnfなど）の「[mysqld]」節に以下の記述を追加する^[10]。

例：保存期間を30日に設定する場合
# vi /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
...
set-variable = expire_logs_days=30
...

設定変更後はmysqldを再起動するか、または以下のコマンドで変数設定を行う。

mysql> SET GLOBAL expire_logs_days=30
Query OK, 0 rows affected (0.00 sec)

mysql> show global variables like 'expire_logs_days';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| expire_logs_days | 30    | 
+------------------+-------+
1 row in set (0.00 sec)

参考文献・サイト

MySQL :: MySQL 5.6 リファレンスマニュアル :: 17.1.1.8 既存のデータによるレプリケーションのセットアップ
MySQL :: MySQL 5.6 リファレンスマニュアル :: 17.1.4.3 レプリケーションスレーブのオプションと変数 #master-host
Baron Schwartz ほか『実践ハイパフォーマンスMySQL 第２版』「８章レプリケーション」、伊藤直也ほか訳、オーラリー・ジャパン、2009年、ISBN 978-4-87311-426-2、pp353-420
MySQL レプリケーションの設定 - maruko2 Note.
データベースの削除ができない - ぱんくず
あんじーのテクニカルブログ: mysql-bin.000001が肥大化する

データベースバックアップ

データベースをバックアップするする方法として（１）SQL文でデータを出力する（２）mysqldump または mysqlhotcopy を使う。またこのバックアップを書き戻すには

SQLベースでのバックアップ
mysqldumpによるバックアップ
バックアップの復元
参考文献・サイト

SQLベースでのバックアップ

SELECT ... INTO OUTFILE 構文で外部ファイルへの出力を行う。この構文は外部ファイルからの入力を行う LOAD DATA INFILE とは逆の動作を行う構文。なおこの操作に先立ってデータの編集が行われないよう LOCK TABLES（要 LOCK TABLES 権限、SELECT権限→権限設定はGRANT参照）による編集禁止と、FLUSH TABLES（要 RELOAD 権限）によるキャッシュの書き込みを行っておく。出力が済んだら UNLOCK TABLES でロックを解除する。

shell> mysql -uMySQLユーザ名 -pパスワード データベース名
mysql> FLUSH TABLES テーブル名 WITH READ ROCK ←ディスクへの書き込みを確実に行ってから読み取り専用にする
mysql> SELECT カラム名 FROM テーブル名 INTO OUTFILE ファイル名 ←ファイル名は既存のものであってはならない
mysql> UNLOCK TABLES ←テーブルのロックを解除する
mysql> exit ←MySQL終了

mysqldumpによるバックアップ

mysqldumpコマンドは、データベースのバックアップを行う。通常はSQL文で出力され、他のSQLサーバ（MySQLサーバでなくてもよい）にバックアップしたり転送したりすることができる。また、CSV（コンマ区切り）形式、他の区切り文字で区切ったテキスト形式、およびXML形式で出力することもできる。

【書式１】テーブル指定（テーブルを指定しなければ指定データベース内の全テーブルがダンプ対象）
mysqldump オプション データベース名 テーブル名
【書式２】データベース指定
mysqldump オプション --databases データベース名 データベース名...
【書式３】全データベースを対象にする
mysqldump オプション --all-databales

このコマンドは「information_schema」データベース（MySQLのユーザ関連情報を格納したデータベース）は明示しない限り対象としない。

オプション（情報表示）

-?, --help: ヘルプメッセージを表示して終了する。
-V, --version: ヘルプメッセージを表示して終了する。

オプション

--add-locks: テーブルをダンプする前のテーブルへの書き込み禁止処理（LOCK TABLES）と、ダンプした後の書き込み禁止解除（UNLOCK TABLES）の処理を行うようにする。この処理によりダンプファイルを再読込する際、より速く挿入が行われるようになる。
-A, --all-databases: 全てのデータベースをダンプ対象にする。--databasesオプションで全てのデータベース名を列挙したのと同じ。
-c, --complete-insert: カラム名を含んだ完全なSQL挿入文を使ってダンプします。
-h ホスト名, --host=ホスト名: ホスト名に指定したMySQLサーバからデータをダンプする。既定値は localhost。
-l, --lock-tables: ダンプする前に全てのテーブルをロックする。テーブルはREAD LOCALでロックされる。複数データベースをダンプする際は、各データベースのテーブルを個別にロックするため、このオプションはダンプファイル内のテーブルがデータベース間で矛盾していないことは保証しない。InnoDBやBDBのようなトランザクションテーブルの場合 --single-transaction を使う方がよい。なぜなら、テーブルをロックする必要が全くないから。
-x, --lock-all-tables: 全データベースの全テーブルをロック（書き込みを禁止）する。ダンプ操作中グローバルリードロックの権利を取得することによって行われる。このオプションを指定すると自動的に --single-transaction と --lock-tables のオプションはオフになる。
--single-transaction: このオプションはサーバからデータをダンプする前に開始SQL文を発行する。InnoDBやBDBのようなトランザクショナルテーブルでのみ役立つ。なぜならあらゆるアプリケーションをブロックすることなく開始SQL文が発行される時の一貫したデータベース状態をダンプするから。このオプションを使用する際、InnoDBテーブルは一貫した状態でダンプされることに留意する必要がある。例えば、いかなるMyISAMテーブルやメモリテーブルもこのオプションを使っている間は状態が変化しない。このオプションと --lock-tablesオプションは互いに排他的である。なぜなら LOCK TABLESによってあらゆる未確定のトランザクションは暗黙のうちに確定されるからである。 --single-transactionモードでのダンプが処理されている間は有効なダンプファイルである（テーブル内容とバイナリログ位置が正しい）ことを保証するために、他の接続でALTER TABLE、DROP TABLE、RENAME TABLE、TRUNCATE TABLEを含むSQL文を使うべきではない。これらのSQL文を使うと一貫した読み取りが保証されなくなるため、ダンプ中にこれらを用いるとテーブル内容を抽出するためmysqldumpによって実行されたSELECT文は不正な内容を取得するか、あるいは取得に失敗するという結果を招きうる。このオプションはMySQLクラスタテーブルはサポートしない。NDBCLUSTERストレージエンジンはREAD COMMITTEDトランザクションレベル（コミットされていないトランザクションの内容は他のトランザクションから見えない分離レベル）だけをサポートするという事実により、結果は一貫性が保証されたものとなりえない。この場合はNDBバックアップとリストアを代替手段として用いるべきである。
--master-data=1 | 2: スレーブ複製サーバで取り込むためのダンプファイルをマスター複製サーバで作成する場合に指定する。このオプションがあるとバイナリログの位置を指定するCHANGE MASTER TOの命令がダンプファイルに追加される。値に 2 を指定するとCHANGE MASTER TO命令はSQLコメントとして書き込まれる。つまり、参考情報として記録はされるが、ダンプファイルを読み込む際は処理されない。値に 1 を指定するとダンプファイルを読み込む際にCHANGE MASTER TO命令も合わせて実行される。値を指定しなかった場合の既定値は 1。このオプションは RELOAD 権限と、バイナリログが有効化されていることが必要。またこのオプションを指定すると、--lock-tablesオプションの無効化、--lock-all-tablesの有効化が自動的に行われる。--single-transactionオプションも指定された場合は、ダンプ開始時に短時間だけ全体の書き込み禁止（global read lock）がかかる。いずれの場合もダンプ中に行われた操作は全てログに記録されます。
--user=MySQLユーザ名
-u MySQLユーザ名: MySQLサーバへの接続に用いるMySQLユーザ名を指定する。
--password=MySQLユーザのパスワード
-p MySQLユーザのパスワード: MySQLサーバへの接続に用いるMySQLユーザのパスワードを指定する。

【例】hogedbのバックアップを行う
shell> mysqldump --user=hoge --single-transaction hogedb > backup_hogedb.sql

バックアップの復元

MySQLダンプファイルの内容をMySQLサーバ接続コマンドにリダイレクトすればよい^[3]

mysql -uMySQLユーザ名 -pMySQLユーザパスワード MySQLデータベース名 <MySQLダンプファイル名

参考文献・サイト

サーバの二重化

参考：MySQL :: MySQL 5.1 リファレンスマニュアル :: 12.6 複製ステートメント

shell> mysql -p
Enter Password: （入力値は表示されない）
mysql> CHANGE MASTER TO 
    -> MASTER_HOST = 'ホスト名'
    -> MASTER_USER = 'ユーザ名'
    -> MASTER_PASSWORD = 'ユーザ名'

ホスト名

メモ〜MySQL/MariaDB関係

インストール

MySQLの起動と停止

コマンドラインからのMySQL操作

SQL文記述法

Prepared Statementを使ったSQL文記述法

ユーザ管理

ユーザの作成

パスワードの設定・変更

ユーザ名の変更

ユーザの削除

アクセス権限の追加・削除・閲覧

rootのパスワードリセット

データベースの作成と使用

データベース一覧表示

データベース作成

データベース削除

使用データベース選択

テーブル一覧表示

テーブル作成

テーブルを削除

テーブル構造表示

カラムの編集

テーブル名変更

レコード追加

データ更新

データ削除

データ取り出し

全データ取得

カラム値で抽出対象を限定

カラム値で並べ替えて出力

データ取り出し（重複するものは１回だけ）

テキストからデータ読み込み

テキストファイルにデータ書き込み

抽出条件記述（where）

条件の併記、否定（AND, OR, NOT）

選択（IN）

部分一致（LIKE）

正規表現（REGEXP）

並べ替え（order）

グループ化（group）

テーブルの結合（join）

文字列処理

文字列結合

データ型・文字コード

データ型一覧

型宣言

扱う文字の文字コード設定

文字コード名

テーブルの結合

内部結合

外部結合

関数

文字列処理、型変換

演算関係

日付時刻関係

二重化・複製・バックアップ

サーバ間でのデータベース複製

複製用ユーザの作成（マスタ）

マスタの設定

マスタ複製情報の取得

データベーススナップショットの作成

スレーブの設定

スナップショットの取り込み（スレーブ）

スレーブ設定２

レプリケーション再構築

古いバイナリログの削除

参考文献・サイト

データベースバックアップ

SQLベースでのバックアップ

mysqldumpによるバックアップ

バックアップの復元

参考文献・サイト

サーバの二重化

管理

MySQLユーザの作成

PHPでのMySQL利用例

設定ファイル（my.cnf）

リモートサーバへの接続

リンク集