20. MySQL - 続き(1)
今日は、PHPのレッスンからは少しはずれますが、MySQLの続きをやります。
■テーブル抽出
SELECT文でデータを抽出する時、いろいろと条件を指定して抽出する必要がある
場合があります。ここでは、いくつかの例をご紹介します。
まず、条件で絞り込みを行う場合は、WHERE句を使用します。
-----------------------------------------------------------------
select
name,
price
from
drink
where
price >= 300
-----------------------------------------------------------------
例えば、以下のようなテーブルがあった時、
NAME PRICE
Beer 500
Juice 300
Coke 150
Tea 250
Coffee 200
上記の条件だと、PRICEが300以上のものを抽出しますので、結果は、
NAME PRICE
Beer 500
Juice 300
となります。
このWHERE句は、プログラムと同様で、複数の条件も抽出できます。
-----------------------------------------------------------------
select
name,
price
from
drink
where
price <= 300
and
price >= 250
-----------------------------------------------------------------
こうすると、PRICEが250以上、300以下の条件となりますので、
NAME PRICE
Juice 300
Tea 250
Coffee 200
が抽出されます。ANDはどちらも条件を満たす時ですが、どちらか一方の条件を
満たす場合にしたい時は、
-----------------------------------------------------------------
select
name,
price
from
drink
where
price < 200
or
price > 300
-----------------------------------------------------------------
のように、ORを使用します。この場合、PRICEが200より小さいか、300より大きい
ものですので、
NAME PRICE
Beer 500
Coke 150
が抽出されます。
抽出した結果を並び替えたい場合は、ORDER BYを使用します。
-----------------------------------------------------------------
select
name,
price
from
drink
where
price >= 200
order by
price
-----------------------------------------------------------------
こうすると、PRICEが200以上で、値段順に並べますので、
NAME PRICE
Coffee 200
Tea 250
Juice 300
Beer 500
となります。
また、逆順に並び替えるには、ORDER BY 〜 DESCと書きます。
-----------------------------------------------------------------
select
name,
price
from
drink
where
price < 500
order by
price desc
-----------------------------------------------------------------
上記は、PRICEが500未満で、値段の逆順ですので、
NAME PRICE
Juice 300
Tea 250
Coffee 200
Coke 150
となります。
■複数のテーブルの抽出
複数のテーブルから条件を一度に取り出す場合は、テーブルを結合させます。
ここでは、2つの結合方法をご紹介します。
ID NAME PRICE
1 Beer 500
2 Juice 300
3 Coke 150
4 Tea 250
5 Coffee 200
というDRINKテーブルと、
ID MAKER
1 Asahi
3 CokaColla
4 Kirin
5 Dydo
というMAKERテーブルがあったとします。
ここで、IDに紐付けられたMAKERとNAMEを抽出したい場合は、以下のようにします。
-----------------------------------------------------------------
select
name,
maker
from
drink,
maker
where
drink.id = maker.id
-----------------------------------------------------------------
こうすると、結果は以下のようになります。
NAME MAKER
Beer Asahi
Coke CokaColla
Tea Kirin
Coffee Dyho
これで 2つのテーブルを結合することができました。
しかし、MAKERテーブルの方にないID = 2のものが出力されません。
DRINKテーブルの方にあるものをすべて抽出したい場合は、LEFT JOINを使用します。
-----------------------------------------------------------------
select
name,
maker
from
drink
left join
maker
on
drink.id = maker.id
-----------------------------------------------------------------
こうすると、
NAME MAKER
Beer Asahi
Joice
Coke CokaColla
Tea Kirin
Coffee Dyho
となります。
また、テーブル名を書くのが面倒な場合、テーブル名を省略することもできます。
-----------------------------------------------------------------
select
d.name,
m.maker
from
drink d
left join
maker m
on
d.id = m.id
-----------------------------------------------------------------
抽出結果は同じです。
★今日のまとめサンプルプログラム
-----------------------------------------------------------------
<HTML>
<BODY>
<?
//ホスト名
$db_host = "localhost";
//データベースユーザ
$db_user = "test_user";
//パスワード
$db_pass = "test_pass";
//データベース名
$db_name = "db_test";
//データベース接続
$dbh = mysql_connect($db_host, $db_user, $db_pass);
//DBの接続に失敗した場合はエラー表示をおこない処理中断
if ($dbh == False) {
print("can not connect db");
exit;
}
//データベース選択
if (!(mysql_select_db($db_name, $dbh)))
{
print("faild to select db");
exit;
}
// データベース読み込み
$sql = "
select
name,
price
from
drink
where
price >= 200
order by
price
";
if (!($rs = mysql_query($sql, $dbh)))
{
print("SQL Failed " . $sql);
exit;
}
//結果の取得
while ($arr = mysql_fetch_array($rs)) {
print $arr["name"] . ",";
print $arr["price"] . "<br>\n";
}
// データベース読み込み
$sql = "
select
d.name,
m.maker
from
drink d
left join
maker m
on
d.id = m.id
";
if (!($rs = mysql_query($sql, $dbh)))
{
print("SQL Failed " . $sql);
exit;
}
//結果の取得
while ($arr = mysql_fetch_array($rs)) {
print $arr["name"] . ",";
print $arr["maker"] . "<br>\n";
}
//データベース切断
mysql_close($dbh);
?>
</BODY>
</HTML>
-----------------------------------------------------------------
■解説
レッスンの中で使用したテーブルをそのまま使っています。
ID NAME PRICE
1 Beer 500
2 Juice 300
3 Coke 150
4 Tea 250
5 Coffee 200
というDRINKテーブルと、
ID MAKER
1 Asahi
3 CokaColla
4 Kirin
5 Dydo
サンプルを動かす場合は、上のテーブルを作成してください。
★課題
1. 上のまとめサンプルのテーブルから、以下の条件でデータを抽出してください。
(1) DRINKテーブルから、NAMEが'Coke'のものを抽出してください。
(2) DRINKテーブルから、IDが 3以上のものを抽出してください。
(3) DRINKテーブルから、すべてを抽出し、NAMEがアルファベット順になるように
並べ替えてください。
(4) DRINKテーブルと、MAKERテーブルの中から、すべてを抽出し、ID、NAME、PRICE、
MAKERをすべて表示してください。
★前回の課題の解答
1. MySQLにて、以下のSQL文を実行してテーブルを作成した後、各処理を行う
プログラムを作成してください。
→下のサンプルを参照してください。
-----------------------------------------------------------------
<HTML>
<BODY>
<?
//ホスト名
$db_host = "localhost";
//データベースユーザ
$db_user = "test_user";
//パスワード
$db_pass = "test_pass";
//データベース名
$db_name = "db_test";
//データベース接続
$dbh = mysql_connect($db_host, $db_user, $db_pass);
//DBの接続に失敗した場合はエラー表示をおこない処理中断
if ($dbh == False) {
print("can not connect db");
exit;
}
//データベース選択
if (!(mysql_select_db($db_name, $dbh)))
{
print("faild to select db");
exit;
}
// (1) 全てのレコードを抽出し、HTMLに表示してください。
$sql = "select * from product";
if (!($rs = mysql_query($sql, $dbh)))
{
print("SQL Failed " . $sql);
exit;
}
//結果の取得
while ($arr = mysql_fetch_array($rs)) {
print $arr["id"] . ",";
print $arr["name"] . ",";
print $arr["stock"] . ",";
print $arr["comment"] . "<br>\n";
}
// (2) PHPプログラムにて、以下のデータを挿入してください。
$sql = "insert into product (id, name, stock, comment) values (4, 'ヘッドホン DKA-B02', 25, '小型ヘッドホン')";
if (!($rs = mysql_query($sql, $dbh)))
{
print("SQL Failed " . $sql);
exit;
}
// (3) ID = 2 のレコードのSTOCKを 31 に変更するプログラムを作成してください。
$sql = "update product set stock = 31 where id = 2";
if (!($rs = mysql_query($sql, $dbh)))
{
print("SQL Failed " . $sql);
exit;
}
// (4) ID = 3 のレコードを削除するプログラムを作成してください。
$sql = "delete from product where id = 3";
if (!($rs = mysql_query($sql, $dbh)))
{
print("SQL Failed " . $sql);
exit;
}
// 再度取得
$sql = "select * from product";
if (!($rs = mysql_query($sql, $dbh)))
{
print("SQL Failed " . $sql);
exit;
}
//結果の取得
while ($arr = mysql_fetch_array($rs)) {
print $arr["name"] . ",";
print $arr["price"] . "<br>\n";
}
//データベース切断
mysql_close($dbh);
?>
</BODY>
</HTML>
-----------------------------------------------------------------
■解説
前回のサンプルを参考にすれば、すぐにできるかと思います。
最後は以下のテーブルになります。
ID NAME STOCK COMMENT
1 ヘッドホン DK-A01 52 高音質ヘッドホン
2 ヘッドホン CL-E22 31 コードレスヘッドホン
4 ヘッドホン DKA-B02 25 小型ヘッドホン
|