Search A.I.
Menu
ホーム

メールマガジン ホームページプログラミングテク

Windowsテク
Javaアプレット サンプル
Java Q & A
JavaScript Q & A
Perl Q & A
Perl レッスン
PHP レッスン
PHPテク
MS-DOS コマンド集
UNIX コマンド集
SQL コマンド集
SEの基礎講座
WEBシステム開発受注します
]無料フォームメール送信サービス
リンク集
Perl レッスン

1. Perl の基礎
2. Perlの文法、条件式
3. Perlの文法、条件式 - 続き
4. 配列について
5. ハッシュ配列について
6. Perlの基礎 - 続き
7. 関数について
8. 関数について - 続き
9. 文字列操作関数
10. 配列操作関数
11. ファイル操作
12. ファイル操作 - 続き
13. パラメータの受け渡し
14. クッキーについて
15. 総合練習
16. メール送信
17. ファイルアップロード
18. パッケージの使用方法
19. MySQL
20. MySQL - 続き(1)
21. MySQL - 続き(2)
22. PostgreSQL
23. 総合練習
24. 総合練習解答



20. MySQL - 続き(1)

今日は、Perlのレッスンからは少しはずれますが、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
-----------------------------------------------------------------

抽出結果は同じです。

 

★今日のまとめサンプルプログラム
-----------------------------------------------------------------
#! /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();
}

# データベース読み込み
$query = "
select
name,
price
from
drink
where
price >= 200
order by
price
";
$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
d.name,
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] . "<br>\n";
}

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

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

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

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

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文を実行してテーブルを作成した後、各処理を行う
プログラムを作成してください。

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

-----------------------------------------------------------------
#! /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) 全てのレコードを抽出し、HTMLに表示してください。

$st = $conn->prepare("select * from product");
$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";
}

# (2) Perlプログラムにて、以下のデータを挿入してください。
$result = $conn->prepare("insert into product (id, name, stock, comment) values (4, 'ヘッドホン DKA-B02', 25, '小型ヘッドホン')");
$rec = $result->execute;
if (!$rec)
{
print("SQL Failed");
exit;
}

# (3) ID = 2 のレコードのSTOCKを 31 に変更するプログラムを作成してください。
$result = $conn->prepare("update product set stock = 31 where id = 2");
$rec = $result->execute;
if (!$rec)
{
print("SQL Failed");
exit;
}

# (4) ID = 3 のレコードを削除するプログラムを作成してください。
$result = $conn->prepare("delete from product where id = 3");
$rec = $result->execute;
if (!$rec)
{
print("SQL Failed");
exit;
}

# 再度取得
$st = $conn->prepare("select * from product");
$res = $st->execute;
if(!$res)
{
print("SELECT SQL文に失敗しました");
exit;
}

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

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

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

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

■解説
前回のサンプルを参考にすれば、すぐにできるかと思います。

最後は以下のテーブルになります。

ID NAME STOCK COMMENT
1 ヘッドホン DK-A01 52 高音質ヘッドホン
2 ヘッドホン CL-E22 31 コードレスヘッドホン
4 ヘッドホン DKA-B02 25 小型ヘッドホン




前の章へ 次の章へ


このエントリーをはてなブックマークに追加


OfficeLance

お問い合わせはこちらから