MySQLコマンドリファレンス

MySQLを使用するにあたって基本的なコマンドを紹介します。尚、基本的なSQLについてはこちらを参照してみてください☆

■データベースの作成・削除

CREATE DATABASE データベース名;
DROP DATABASE データベース名;

作成したデータベースを使用するには以下のコマンドを打ちます

USE 作成したデータベース名;

■テーブルの作成・削除のサンプル

CREATE TABLE テーブル名 (カラム名１ VARCHAR(5), カラム名２ INT);
DROP TABLE テーブル名

作成したテーブル情報をみるには以下のコマンドを打ちます

SHOW TABLES;
DESC 作成したテーブル名;

以下はMySQLで使用できるカラムの基本的なデータ型です。

【数値型】

型	内容
TINYINT	１バイト（符号あり：-128～127、符号なし：0～255）
SMALLINT	２バイト
MEDIUMINT	３バイト
INT	４バイト（符号あり：約－２１億～２１億、符号なし：約４２億）
BIGINT	８バイト

他には浮動小数点として、FLOATやDOUBLEやREAL
文字列として格納される数値としてDECIMALやDECやNUMERICやFIXED等があります

【日付型】

型	内容
DATETIME	YYYY-MM-DD HH:MM:SSで1000-01-01 00:00:00～9999-12-31 23:59:59の範囲
DATE	YYYY-MM-DDで1000-01-01～9999-12-31の範囲
TIMESTAMP	YYYY-MM-DD HH:MM:SSで1970-01-01 00:00:00～2037-12-31 23:59:59の範囲（レコードの作成・更新時に現在の日時が自動的に格納される）
TIME	HH:MM:SS形式
YEAR	YYYYで1901～2155まで

【文字列型】

型	内容
CHAR(L)	固定長文字列で０～２５５バイト
VARCHAR(L)	可変長文字列で０～２５５バイト
TINYBLOB	最大長２５５バイトで大文字小文字の区別をする
TINYTEXT	最大長２５５バイトで大文字小文字の区別をしない
BLOB	最大長６５５３５バイトで大文字小文字の区別をする
TEXT	最大長６５５３５バイトで大文字小文字の区別をしない
MEDIUMBLOB	最大長16777215バイトで大文字小文字の区別をする
MEDIUMTEXT	最大長16777215バイトで大文字小文字の区別をしない
LONGBLOB	最大長4294967295バイトで大文字小文字の区別をする
LONGTEXT	最大長4294967295バイトで大文字小文字の区別をしない

▼カラムの数値型に負の数が入らないように制限する

INT UNSIGNED

▼デフォルト値を設定する

カラム名 VARCHAR(10) DEFAULT 'テスト'

▼ＮＵＬＬを禁止する

カラム名 INT NOT NULL

▼オートインクリメント機能を付加する

カラム名 INT AUTO_INCREMENT

■テーブル名やカラムを変更・削除する

テーブル名を変更する方法は以下の２つがあります

ALTER TABLE 元テーブル名 RENAME 新テーブル名;
RENAME TABLE 元テーブル名 TO 新テーブル名;

▼カラム名やデータ型を変更する

ALTER TABLE テーブル名 CHANGE カラム名 新カラム名 新データ型;
ALTER TABLE テーブル名 MODIFY カラム名 新データ型;

▼カラムを追加する

ALTER TABLE テーブル名 ADD 追加カラム定義;

デフォルトではテーブルの最後にカラムが追加されますが、「追加カラム定義 FIRST」や「追加カラム定義 AFTER カラム名」で追加する場所を指定する事も可能です

▼カラムやキーを削除する

ALTER TABLE テーブル名 DROP COLUMN カラム名;
ALTER TABLE テーブル名 DROP PRIMARY KEY;
ALTER TABLE テーブル名 ALTER カラム名 DROP DEFAULT;

■インデックスを作成・削除する

CREATE INDEX インデックス名 ON テーブル名(インデックス用の指定カラム名);
ALTER TABLE テーブル名 ADD INDEX インデックス名(インデックス用の指定カラム名);

DROP INDEX インデックス名 ON テーブル名;
ALTER TABLE テーブル名 DROP INDEX インデックス名;

尚、インデックスはテーブル作成時に同時に作成する事も可能です。
以下はテーブル作成時にプライマリーキーやインデックスを作成するサンプル

CREATE TABLE TEST(id INT, aa VARCHAR(200), bb VARCHAR(50), PRIMARY KEY (id), INDEX test_idx(aa, bb) );

また、インデックスを作成した後は、そのインデックスがどの程度有効かどうか以下のコマンドでSELECT文を解析した方が良いです

EXPLAIN SELECT文;

■データのインポート・エクスポート

「/home/test.csv」ファイル（「"」で囲まれたカンマ区切り）を「aho」テーブルにインポートするサンプル（１行目はカラムタイトルなので「IGNORE 1 LINES」で無視して改行コードは「CR+LF」）

LOAD DATA INFILE "/home/test.csv" INTO TABLE aho
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES
TERMINATED BY "\r\n" IGNORE 1 LINES;

「aho」テーブルのデータを「/home/test2.csv」ファイルにタブ区切りでエクスポートするサンプル

SELECT * INTO OUTFILE '/home/test2.csv'
LINES TERMINATED BY '\r\n' FROM aho;

■データベースのバックアップと復元

データベースのバックアップはLINUX上から以下のコマンドを打ちます

mysqldump データベース名 > "保存ファイル名" -u ユーザ名 -pパスワード

全てのデータベースのバックアップはLINUX上から以下のコマンドを打ちます

mysqldump --all-databases > "保存ファイル名" -u ユーザ名 -pパスワード

複数のデータベースのバックアップはLINUX上から以下のコマンドを打ちます

mysqldump --databases データベース名１ データベース名２ > "保存ファイル名" -u ユーザ名 -pパスワード

尚、復元はLINUX上から以下のコマンドを打ちます

mysql データベース名 < "保存ファイル名" -u ユーザ名 -pパスワード

テーブル単位のバックアップの場合はMySQLに接続して以下のコマンドを打ちます

BACKUP TABLE テーブル名 TO "保存ファイル名";

テーブルの復元はMySQLに接続して以下のコマンドを打ちます

RESTORE TABLE テーブル名 FROM "保存ファイル名";

■その他（良く使うコマンドや便利な関数等）

ＳＥＬＥＣＴ文の結果、先頭から５件のみデータを取得するサンプル

SELECT * FROM aho LIMIT 5;

ＮＵＬＬを条件に値を変える（ORACLEのNVL関数）サンプル（ccの項目がNULLなら0、NULL以外ならccを出力する）

SELECT IFNULL(cc,0) FROM aho;

指定の文字数で文字列を切り出すサンプル（「あいうえお」の２文字目から３文字分切り出し「いうえ」を出力する）

SELECT SUBSTRING('あいうえお',2,3);

日付を指定の形式で表示する（ORACLEのTO_CHAR関数相当）サンプル

SELECT DATE_FORMAT('日付','形式');

形式は例えば「YYYY-MM-DD」で表示したい場合は「%Y-%m-%d」、「YY/MM/DD HH:MM:SS」で表示したい場合は「%y/%m/%d %H:%m:%s」