平成23年度前期 情報科学III (理系コア科目) PHP と SQL (MySQL) の連携 その3 担当 松永 裕介 月曜日 2限 http://www.c.csce.kyushu-u.ac.jp/~matsunaga/lecture/php/ 本資料の一部は、堀良彰准教授、天野浩文准教授等による 以前の講義資料をもとにしています。 演習問題 テーブルのカラムとして – 商品名 – 価格 – 色 – 重さ を、用意し、「商品名」で検索し、「色」「重さ」はオプションで表示できるようなプログラ ムを作成せよ。 テーブルのカラムとして – テーブル1 • 商品名 • 価格 – テーブル2 • 商品名 • 色 – テーブル3 • 商品名 • 重さ を用意して、「商品名」で検索して、「価格」「色」「重さ」が表示されるプログラムを作 成せよ。 テーブルの作成 mysql> create table table090706p (name char(32), price char(32)); mysql> create table table090706c (name char(32), color char(32)); mysql> create table table090706w (name char(32), weight char(32)); データ投入 mysql> mysql> mysql> mysql> mysql> mysql> mysql> mysql> mysql> mysql> mysql> mysql> mysql> mysql> insert insert insert insert insert insert insert insert insert insert insert insert insert insert into into into into into into into into into into into into into into table090706p table090706p table090706p table090706p table090706c table090706c table090706c table090706c table090706c table090706c table090706w table090706w table090706w table090706w set set set set set set set set set set set set set set name="book", price="1000"; name="pen", price="100"; name="cd", price="3000"; name="dvd", price="4000"; name="book", color="black"; name="pen", color="red"; name="pen", color="green"; name="pen", color=“blue"; name="cd", color="silver"; name="dvd", color="silver"; name="book", weight="100"; name="pen", weight="10"; name="cd", weight="5"; name="dvd", weight="5"; 検索例 mysql> select * from table090706p join table090706c, table090706w; ………. | dvd | 4000 | cd | silver | pen | 10 | | book | 1000 | dvd | silver | pen | 10 | | pen | 100 | dvd | silver | pen | 10 | | cd | 3000 | dvd | silver | pen | 10 | | dvd | 4000 | dvd | silver | pen | 10 | | book | 1000 | book | black | cd | 5 | | pen | 100 | book | black | cd | 5 | | cd | 3000 | book | black | cd | 5 | | dvd | 4000 | book | black | cd | 5 | | book | 1000 | pen | red | cd | 5 | | pen | 100 | pen | red | cd | 5 | | cd | 3000 | pen | red | cd | 5 | | dvd | 4000 | pen | red | cd | 5 | | book | 1000 | pen | green | cd | 5 | | pen | 100 | pen | green | cd | 5 | | cd | 3000 | pen | green | cd | 5 | | dvd | 4000 | pen | green | cd | 5 | | book | 1000 | pen | blue | cd | 5 | | pen | 100 | pen | blue | cd | 5 | | cd | 3000 | pen | blue | cd | 5 | | dvd | 4000 | pen | blue | cd | 5 | | book | 1000 | cd | silver | cd | 5 | | pen | 100 | cd | silver | cd | 5 | | cd | 3000 | cd | silver | cd | 5 | | dvd | 4000 | cd | silver | cd | 5 | | book | 1000 | dvd | silver | cd | 5 | | pen | 100 | dvd | silver | cd | 5 | | cd | 3000 | dvd | silver | cd | 5 | | dvd | 4000 | dvd | silver | cd | 5 | +------+-------+------+--------+------+--------+ 96 rows in set (0.00 sec) 検索例 mysql> select * from table090706p join table090706c, table090706w on table090706p.name=table090706c.name and table090706p.name=table090706w.name; +------+-------+------+--------+------+--------+ | name | price | name | color | name | weight | +------+-------+------+--------+------+--------+ | dvd | 4000 | dvd | silver | dvd | 5 | | book | 1000 | book | black | book | 100 | | pen | 100 | pen | red | pen | 10 | | pen | 100 | pen | green | pen | 10 | | pen | 100 | pen | blue | pen | 10 | | cd | 3000 | cd | silver | cd | 5 | +------+-------+------+--------+------+--------+ 検索例 mysql> select table090706p.name,table090706p.price,table090706c.color,table090706w.weigh t from table090706p join table090706c, table090706w on table090706p.name=table090706c.name and table090706p.name=table090706w.name; +------+-------+--------+--------+ | name | price | color | weight | +------+-------+--------+--------+ | dvd | 4000 | silver | 5 | | book | 1000 | black | 100 | | pen | 100 | red | 10 | | pen | 100 | green | 10 | | pen | 100 | blue | 10 | | cd | 3000 | silver | 5 | +------+-------+--------+--------+ 検索例 mysql> select table090706p.name, table090706p.price,table090706c.color, table090706w.weight from table090706p join table090706c, table090706w on table090706p.name=table090706c.name and table090706p.name=table090706w.name where table090706p.name = "pen"; +------+-------+-------+--------+ | name | price | color | weight | +------+-------+-------+--------+ | pen | 100 | red | 10 | | pen | 100 | green | 10 | | pen | 100 | blue | 10 | +------+-------+-------+--------+ 3 rows in set (0.00 sec) データベースで数字を扱って見る。 mysql> create table table090706pp (name char(32), price int); mysql> create table table090706ww (name char(32), weight int); mysql> mysql> mysql> mysql> insert insert insert insert into into into into table090706pp table090706pp table090706pp table090706pp set set set set name="book", price=1000; name="pen", price=100; name="cd", price=3000; name="dvd", price=4000; mysql> mysql> mysql> mysql> insert insert insert insert into into into into table090706ww table090706ww table090706ww table090706ww set set set set name="book", weight=100; name="pen", weight=10; name="cd", weight=5; name="dvd", weight=5; 検索例 mysql>select table090706pp.name, table090706pp.price, table090706c.color, table090706ww.weight from table090706pp join table090706c, table090706ww on table090706pp.name=table090706c.name and table090706pp.name=table090706ww.name; +------+-------+--------+--------+ | name | price | color | weight | +------+-------+--------+--------+ | book | 1000 | black | 100 | | pen | 100 | red | 10 | | pen | 100 | green | 10 | | pen | 100 | blue | 10 | | cd | 3000 | silver | 5 | | dvd | 4000 | silver | 5 | +------+-------+--------+--------+ 6 rows in set (0.00 sec) 検索例: 価格が2000以下の商品を検索 mysql> select table090706pp.name, table090706pp.price, table090706c.color, table090706ww.weight from table090706pp join table090706c, table090706ww on table090706pp.name=table090706c.name and table090706pp.name=table090706ww.name where table090706pp.price <= 2000; +------+-------+-------+--------+ | name | price | color | weight | +------+-------+-------+--------+ | book | 1000 | black | 100 | | pen | 100 | red | 10 | | pen | 100 | green | 10 | | pen | 100 | blue | 10 | +------+-------+-------+--------+ 4 rows in set (0.00 sec) 検索例: 価格が2000以下で、重さが50以上のものを検索 mysql> select table090706pp.name, table090706pp.price, table090706c.color, table090706ww.weight from table090706pp join table090706c, table090706ww on table090706pp.name=table090706c.name and table090706pp.name=table090706ww.name where table090706pp.price <= 2000 and table090706ww.weight >= 50; +------+-------+-------+--------+ | name | price | color | weight | +------+-------+-------+--------+ | book | 1000 | black | 100 | +------+-------+-------+--------+ 1 row in set (0.00 sec) 練習問題 商品、価格、販売店、販売店距離のデータがあるとき – 価格で商品を選択できるプログラムを作成せよ • 例:100円以下とか – 販売店の距離で選択できるプログラムを作成せよ • 例:500m以内とか – 価格と販売店の距離両方の条件を選択できるプログラムを 作成せよ。 • 例:200円以下で距離が1000m以内 注意 – 普通、全部の販売店で同じ商品があるわけではないと思い ますので、そのあたり注意して設計してください。 まず、SQL で動作を確認すること
© Copyright 2025 ExpyDoc