今日も、MySQLの続きをやります。

■テーブル抽出（その２）
データを抽出する際に、文字列検索する場合、完全一致なら'='を使用しますが、
それ以外の場合の検索方法をご紹介します。

-----------------------------------------------------------------
select
name,
price
from
drink
where
name like '%c%'
-----------------------------------------------------------------

前回と同じテーブルを使用します。

NAME PRICE
Beer 500
Juice 300
Coke 150
Tea 250
Coffee 200

上記の場合、NAMEの中に、'c'という文字があった場合に、抽出されます。
'%'は、0字以上の任意の文字を意味します。

上記を実行すると、

NAME PRICE
Juice 300
Coke 150
Coffee 200

が抽出されることになります。

-----------------------------------------------------------------
select
name,
price
from
drink
where
name like 'c%'
-----------------------------------------------------------------

とすると、先頭に'c'が来る文字ですので、

NAME PRICE
Coke 150
Coffee 200

が抽出されます。

次に、文字列が一致しないものを抽出したい場合は、'<>'を使用します。

-----------------------------------------------------------------
select
name,
price
from
drink
where
name <> 'Coke'
-----------------------------------------------------------------

とすると、NAMEが'Coke'以外のものが抽出されます。

NAME PRICE
Beer 500
Juice 300
Tea 250
Coffee 200

■MySQLの関数
次に、MySQL内で使用できる関数を少しご紹介します。

COUNT レコード数を取得します
SUM 抽出したデータの合計を計算します
NOW 現在時刻を取得します
DATE_FORMAT 日時を任意のフォーマットの文字列に変換します

よく使うのは、上記のものが多いです。

-----------------------------------------------------------------
select
count(*)
from
drink
where
price > 200
-----------------------------------------------------------------

これでDRINKテーブルの登録されているレコード数を取得します。もちろん、
条件を指定することもできます。

次は、SUMです。

-----------------------------------------------------------------
select
sum(price)
from
drink
-----------------------------------------------------------------

こうすると、PRICEの合計を抽出することができます。こちらもWHEREを書けば
条件を指定することができます。

次は、NOWです。これは日付に関わるものですので、以下のテーブルを作成して
ください。

NAME PRICE UP_DATE
Beer 500 2005/01/01 00:00:00
Juice 300 2005/02/23 00:00:00
Coke 150 2004/12/31 00:00:00
Tea 250 2005/04/01 00:00:00
Coffee 200 2005/10/01 00:00:00

このテーブルで、例えば、

-----------------------------------------------------------------
select
name,
price,
up_date
from
drink
where
up_date > now()
-----------------------------------------------------------------

とした場合、UP_DATEが現在時刻よりも新しいものが抽出されます。現在日時が
2005/03/01 00:00:00とすると、

NAME PRICE UP_DATE
Tea 250 2005/04/01 00:00:00
Coffee 200 2005/10/01 00:00:00

が抽出されます。

次に、DATE_FORMATは、自分の好きなフォーマットで日時を取得することが
できます。

-----------------------------------------------------------------
select
name,
price,
date_format(up_date, '%Y年%m月%d日 %H時%I分%S秒')
from
drink
-----------------------------------------------------------------

フォーマットの'%Y'などの指定子については、いろいろと用意されていますので、
MySQLのマニュアルを参照してください。
http://dev.mysql.com/doc/mysql/ja/index.html

MySQL関数は、他にもたくさんありますが、紹介するのはここまでにします。
興味があれば、MySQLのマニュアルを調べてみてください。

■トランザクションについて
データベースの更新時は必ずトランザクション処理を意識します。

抽出のみの処理なら必要ありませんが、データベースの更新がある場合、
トランザクション処理をしないと、トラブルの元となります。

トランザクションとは、1つ1つ処理をつなげた一連の処理のことを言います。

例えば、カウンタをデータベースとプログラムで作成した場合、

○データベースからカウンタ(10)を取得
　 ↓
○カウンタをアップ(11)
　 ↓
○データベースにカウンタ(11)を書き込む

という流れになりますが、ここで複数のユーザが同時にアクセスした場合、
どのようなことが起こりうるか考えてみましょう。

○Aさんがアクセスして、データベースからカウンタ(10)を取得
　 ↓
○Bさんがアクセスして、データベースからカウンタ(10)を取得
　 ↓
○Aさんのカウンタアップ(11)
　 ↓
○Bさんのカウンタアップ(11)
　 ↓
○Aさんのカウンタ(11)をデータベースに書き込む
　 ↓
○Bさんのカウンタ(11)をデータベースに書き込む

ここで、AさんとBさんの二人がアクセスしたのですが、カウンタは10から11しか
上がりませんでした。

1秒間に何千人とアクセスするサイトでは、このようなことだと、恐ろしいことが
起こったりします。

これを防ぐために、1つのデータベースへの処理に対して、他のアクセスを待たせる
というトランザクション管理が必要になります。

トランザクションを開始してから、終了するまでの間、他のユーザはデータベースに
アクセスすることができません。

上の例で言うと、

○Aさんがアクセスして、データベースからカウンタ(10)を取得
　 ↓
○Bさんがアクセスしたが、トランザクションの終了待ち
　 ↓
○Aさんのカウンタアップ(11)
　 ↓
○Aさんのカウンタ(11)をデータベースに書き込む
　 ↓
○Bさんがアクセスして、データベースからカウンタ(11)を取得
　 ↓
○Bさんのカウンタアップ(12)
　 ↓
○Bさんのカウンタ(12)をデータベースに書き込む

どんなに同時にアクセスしても、上記のような処理になり、不具合が生じなく
なります。

では、具体的にトランザクション処理の方法をご説明します。

やり方は簡単で、SQL文で、'BEGIN'を実行し、'COMMIT'でトランザクションを終了
します。

-----------------------------------------------------------------
# トランザクション開始
$st = $conn->prepare("begin");
$res = $st->execute;
if(!$res)
{
print("トランザクション開始処理に失敗しました");
exit;
}

# トランザクション終了
$st = $conn->prepare("commit");
$res = $st->execute;
if(!$res)
{
print("コミット処理に失敗しました");
exit;
}
-----------------------------------------------------------------

カウンタの場合などは、データベース読み込み前にトランザクションを開始する
必要があります。

また、1つの処理しかなくても、データベースを更新する時は、必ずトランザク
ション処理をしてください。これをしないと、同時にデータベースに書き込もうと
した時に、データが消えるなどの不具合が発生する可能性があります。

抽出だけの処理であれば、トランザクション処理は必要ありませんが、前後に
処理を入れても問題ありません。

■ロールバック
トランザクション処理中に、何らかのエラーが発生した場合、そこで終了して
しまうと、データベースの更新が中途半端になり、様々な不具合を生じる可能性が
あります。

そこで、トランザクション処理中にエラーが発生した場合は、トランザクション
開始前に、データベースの更新状態を戻すことができます。

-----------------------------------------------------------------
# ロールバック
$st = $conn->prepare("rollback");
$res = $st->execute;
if(!$res)
{
print("ロールバックに失敗しました");
exit;
}
-----------------------------------------------------------------

更新を 2回以上行う場合で、エラーが発生する可能性がある場合は、ロールバックを
必ず行うようにしてください。

エラーがない場合は、最後に"commit"でトランザクションを終了します。

★今日のまとめサンプルプログラム
-----------------------------------------------------------------
#! /usr/local/bin/perl

print "Content-type:text/html\n\n";

print << "END_OF_HTML";
<HTML>
<BODY>
END_OF_HTML

use DBI;

$dbn = "test";
$user = "admin";
$pswd = "admin";

# データベース接続
$conn = DBI->connect('DBI:mysql:' . $dbn . ':localhost', $user, $pswd);
if(!$conn)
{
print("データベースの接続に失敗しました");
exit();
}

# トランザクション開始
$st = $conn->prepare("begin");
$res = $st->execute;
if(!$res)
{
print("トランザクション開始処理に失敗しました");
exit;
}

# データベース読み込み
$query = "
select
name,
price
from
drink
where
name like '%c%'
";
$st = $conn->prepare($query);
$res = $st->execute;
if(!$res)
{
print("SELECT SQL文に失敗しました");
exit;
}

while(@res = $st->fetchrow)
{
print $res[0] . ", ";
print $res[1] . "<br>\n";
}

# データベース読み込み
$query = "
select
count(*)
from
drink
where
up_date > now()
";
$st = $conn->prepare($query);
$res = $st->execute;
if(!$res)
{
print("SELECT SQL文に失敗しました");
exit;
}

while(@res = $st->fetchrow)
{
print $res[0] . "<br>\n";
}

# トランザクション終了
$st = $conn->prepare("commit");
$res = $st->execute;
if(!$res)
{
print("コミット処理に失敗しました");
exit;
}

# データベースクローズ
$st->finish;
$conn->disconnect;

print << "END_OF_HTML";
</BODY>
</HTML>
END_OF_HTML

exit;
-----------------------------------------------------------------

■解説
レッスンの中で使用したテーブルをそのまま使っています。

ID NAME PRICE UP_DATE
1 Beer 500 2005/01/01 00:00:00
2 Juice 300 2005/02/23 00:00:00
3 Coke 150 2004/12/31 00:00:00
4 Tea 250 2005/04/01 00:00:00
5 Coffee 200 2005/10/01 00:00:00

サンプルを動かす場合は、上のテーブルを作成してください。

上のサンプルでは、更新処理がないので、トランザクション処理は必要ありません
が、練習のため、トランザクション処理をしています。トランザクション処理を
実行するタイミングを確認してください。

トランザクション処理を行う時の注意点としては、トランザクション処理を行う
時間は、限りなく短くした方が、パフォーマンスが良いということです。

データベースに接続してすぐにトランザクションを開始してから、いろいろと
文字列のエラーチェックや、文字列操作をするよりも、文字列操作が終わって
から、トランザクションを開始して、データの取得や更新を行った方が良いです。

これは、トランザクション中は、他のユーザが待ち状態になるので、なるべく
トランザクション処理は短くした方が良いからです。

★課題
1. 上のまとめサンプルのテーブルから、以下の条件でデータを抽出してください。

(1) DRINKテーブルから、NAMEの文字に'ee'の文字が含まれるレコードの、PRICEの
合計を表示してください。
(2) DRINKテーブルから、すべてを抽出し、ID、NAME、PRICEと、UP_DATEの日時を
'2005/01/01 24:00'のフォーマットで表示してください。

2. トランザクション管理を行って、データベースを使用した簡易的なカウンタを
作成してください。

★前回の課題の解答
1. 上のまとめサンプルのテーブルから、以下の条件でデータを抽出してください。

→下のサンプルを参照してください。

-----------------------------------------------------------------
#! /usr/local/bin/perl

print "Content-type:text/html\n\n";

print << "END_OF_HTML";
<HTML>
<BODY>
END_OF_HTML

use DBI;

$dbn = "test";
$user = "admin";
$pswd = "admin";

# データベース接続
$conn = DBI->connect('DBI:mysql:' . $dbn . ':localhost', $user, $pswd);
if(!$conn)
{
print("データベースの接続に失敗しました");
exit();
}

# (1) DRINKテーブルから、NAMEが'Coke'のものを抽出してください。
$query = "
select
id,
name,
price
from
drink
where
name = 'Coke'
";
$st = $conn->prepare($query);
$res = $st->execute;
if(!$res)
{
print("SELECT SQL文に失敗しました");
exit;
}

while(@res = $st->fetchrow)
{
print $res[0] . ", ";
print $res[1] . ", ";
print $res[2] . "<br>\n";
}

# (2) DRINKテーブルから、IDが 3以上のものを抽出してください。
$query = "
select
id,
name,
price
from
drink
where
id >= 3
";
$st = $conn->prepare($query);
$res = $st->execute;
if(!$res)
{
print("SELECT SQL文に失敗しました");
exit;
}

while(@res = $st->fetchrow)
{
print $res[0] . ", ";
print $res[1] . ", ";
print $res[2] . "<br>\n";
}

# (3) DRINKテーブルから、すべてを抽出し、NAMEがアルファベット順になるように
# 並べ替えてください。
$query = "
select
id,
name,
price
from
drink
order by
name
";
$st = $conn->prepare($query);
$res = $st->execute;
if(!$res)
{
print("SELECT SQL文に失敗しました");
exit;
}

while(@res = $st->fetchrow)
{
print $res[0] . ", ";
print $res[1] . ", ";
print $res[2] . "<br>\n";
}

# (4) DRINKテーブルと、MAKERテーブルの中から、すべてを抽出し、ID、NAME、PRICE、
# MAKERをすべて表示してください。
$query = "
select
d.id,
d.name,
d.price,
m.maker
from
drink d
left join
maker m
on
d.id = m.id
";
$st = $conn->prepare($query);
$res = $st->execute;
if(!$res)
{
print("SELECT SQL文に失敗しました");
exit;
}

while(@res = $st->fetchrow)
{
print $res[0] . ", ";
print $res[1] . ", ";
print $res[2] . ", ";
print $res[3] . "<br>\n";
}

# データベースクローズ
$st->finish;
$conn->disconnect;

print << "END_OF_HTML";
</BODY>
</HTML>
END_OF_HTML

exit;
-----------------------------------------------------------------

■解説
抽出条件がある場合は、レッスンの通りにWHEREを使用してデータベースを抽出して
ください。

二つのテーブルを結合してデータを抽出する場合は、LEFT JOINを使用します。
ONの後に、抽出条件を記述します。

上の解答例を実行して、結果を確認してください。