MySQL5

フリーながら大変高機能なデータベースマネージャーです。人気も高いのでヘルプも充実しています。Jakarta Tomcat + struts で 簡単なデータベース連動アプリを作る場合にはお勧めです。
更新日 2016-02-13

インストール

MySQL5.6 + mroongaをご覧下さい。

ユーザーの管理

データベースにアクセスする為のユーザーを作成します。ユーザーには扱えるデータベースとそれに伴う権限を設定できます。

root にパスワードを設定

インストール直後ではroot でパスワード無しとなっていますので、これを設定します。

root ユーザーにパスワードを設ける

初期設定ではパスワード無しなので設定します。
/usr/local/mysql/bin/mysql -u root
set password for root@localhost=password('パスワード');
flush privileges;
ついでにデフォルトで作成されているパスワードが未設定なユーザーも全部削除します。使わないので。
use mysql;
delete from user where password='';
flush privileges;
exit;

ユーザーの作成

通常はroot ユーザーから作成されます。privileges_type は権限の種類で、SELECT やUPDATE、全ての権限ならALL です。 ホスト名はユーザーがどこからアクセスしてきたかによって変わります。% とすると全てのホストに対する設定となります。 (なお同じlocalhost からのアクセスでもDNS によってホスト名が解決できる場合はlocalhost とならない場合もあるので注意です)
GRANT privileges_type ON database_name.table_name TO user_name@host_name 
 IDENTIFIED BY 'password';

ユーザーの削除

削除の前にまずユーザーに割り当てられた権限を取り除き、続いてDROP 文にて削除を行います。
REVOKE ALL PRIVILEGES ON *.* FROM user_name@host_name;
DROP USER user_name@user_name

ユーザー情報を確認する

ユーザーはデータベースやテーブル毎にも権限を設定できるので、SHOW GRANTS を使って確認します。
SHOW GRANTS FOR user_name@host_name;

// グローバル(つまり*.*) に対する情報だけであれば
// 以下でも確認できます。
SELECT user FROM mysql.user\G;

パスワードの変更

SET PASSWORD FOR user_name@host_name = PASSWORD('password';

権限の変更

GRANT privileges_type ON database_name TO user_name@host_name

データベース操作

データベースはテーブルの集合です。MySQL はデータベースマネージャーですので、複数のデータベースを保持できます。通常は、まず データベースを作成する事から始まります。SQL コマンドは慣例として大文字で書きますが、小文字でも問題無いようです。

データベースの作成

CREATE DATABASE database_name;

データベースの削除

DROP DATABASE database_name;

カレント・データベースの指定

テーブル操作の対象としたいデータベースを指定します。
USE  database_name;

データベースの一覧を見る

SHOW DATABASES;

テーブル操作

テーブルとはレコードの集合で、データのレイアウトを規定するものです。一つのデータベースの中に複数のテーブルを作成できます。 データベースを作ったら次にテーブルを作ります。事前に操作対象としたいデータベースを指定しておきましょう。

テーブルの作成

テーブルによるデータレイアウトはC言語で言うところの構造体のようなもので、その内訳を作成時に規定します。実際のデータとなる レコードも、この内訳に従ったものになります。いわゆる列(カラム名)を設定する訳です。
CREATE TABLE table_name (column_name1 TYPE, column_name2 TYPE);

// 例えばid (自動でインデックスが割り当てられる), name(text型) を持つ
// テーブル users を作る場合。index(id) はID をインデックスとして扱う
// 事を指示するもので、カラム名としては評価されません。
CREATE TABLE users(id int auto_increment, name text, index(id));

テーブルの削除

DROP TABLE table_name;

カラム(列) の定義を確認する

SHOW COLUMNS FROM table_name;

カラム(列)を後から追加する

ALTER TABLE table_name ADD column_name type;

カラム(列)を後から削除する

ALTER TABLE table_name DROP column_name;

カラム(列)を後から変更する

ALTER TABLE table_name CHANGE old_column_name new_column_name type;

カラム(列)を後から削除する

ALTER TABLE table_name DROP COLUMN column_name;

レコード操作

レコードとはテーブルに積まれてゆくデータの行そのものです。テーブルのデータレイアウトに沿った形で格納されており、行として扱われます。

レコード(データ行)の挿入・追加

INSERT INTO table_name(column_name0, column_name1) VALUES( value0, value1);

レコード(データ行)の削除

where 文で指定のカラムにおける値を比較し、合致するものを削除の対象とします。where 文を伴わないと全レコードを削除して しまう事になります。
DELETE FROM table_name WHERE column_name='value';

// テーブル内の全レコードを削除
DELETE FROM table_name;

DELETE 構文のデメリット

DELETE 構文は1レコード毎にバイナリログへの書き出しを行う。その為オーバーヘッドがばかにならない。 これを伴わないコマンドとして TRUNCATE 構文がある。

レコード(データ行)の内容を変更

where 文でマッチング処理を行い、合致したレコードを対象に変更を行います。
UPDATE table_nameSET column_name='new value' WHERE column_name='value';

// 式を用いる事もできる。例えば数値を2倍にしたければ
UPDATE table_name SET column_name= column_name * 2 WHERE column_name='value';

レコード(データ行)の検索

データベースのメイン機能です。取り出したいカラム名はコンマで区切って複数指定でき、またワイルドカードも使えます。ソートや特定のカラムの 値が合致するものだけを取り出したりできます。
SELECT column_name FROM table_name;

// where 文によるマッチングで選別する
SELECT column_name FROM table_name WHERE column_name=value;

// order by によるソートを伴う
SELECT column_name FROM table_name ORDER BY column_name;

WHERE 文によるマッチング例

// id が3 以上 6 以下の場合
WHERE id >= 3 AND id <= 6
WHERE id BETWEEN 3 AND 6

// id が5 もしくは name が'ast'
WHRER id = 5 OR name = 'ast'

// id が5 以外
WHERE id <> 5
WHERE NOT id = 5

// name が'ast','user','admin' のいづれかの場合
WHERE name IN ('ast','user','admin')

// 部分一致 (%は任意の文字、? は任意の一文字)
WHERE name LIKE 'ast%'

それぞれの検索結果をぐ(UNION)

(SELECT column_name FROM table_name WHERE age <10) 
UNION
(SELECT column_name FROM table_name WHERE age >=10)

UNION とORDER BY

個別のSELECT 毎にORDER BY を掛けても、結果は芳しくありません。仕様のようです。

バックアップ・リストア

MySQL のデータはファイルに保存されていますので、直接これを管理する方法がまず考えられます。しかし運用を一時停止しなければ いけないなど実用的にはありません。幸いMySQL には複数のバックアップ手段が用意されています。

mysqldump コマンド(バックアップ)

MySQL の実行ファイル(mysqldump) を使ってデータベースの内容をファイルに出力できます。デフォルトではカラム毎にタブが入り、レコード毎に改行されます。
mysqldump -u root -p --all-databases > /all.db
mysqldump -u root -p --databases DATABASE_NAME > /database.db

mysql コマンド(リストア)

mysqldump で取ったバックアップからデータベースを復元できます。
mysql -u  root -p < /all.db

mysqladmin -u root -p flush-privileges  // 忘れずに
もしroot ユーザーにgrant 権限が無い場合
mysql -u root -p
update mysql.user set Grant_priv='Y' where user='root';
flush privileges;

// mysald 要リスタート

リモート接続をする

ユーザーの再設定

MySQL のユーザーにはホスト名も含まれます。つまりここを変更するだけでリモート接続が可能になります。
grant all privileges on *.* to user_name@'192.168.1.0/255.255.255.0';

Tip's

16進数の扱い

int 型に16進文字列を入力する場合はx'2F' のようにする。
insert into test2(num) values(x'2F');
select num from test2;
47 // 10進数で戻る