mysql create table 090706 p name char32 price

テーブルの作成 mysql> create table 090706 p (name char(32), price char(32)); mysql> create table 090706 c (name char(32), color char(32)); mysql> create table 090706 w (name char(32), weight char(32));

データ投入 mysql> mysql> mysql> mysql> insert insert insert insert into into into into table 090706 p table 090706 c table 090706 c table 090706 w 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 table 090706 p join table 090706 c, table 090706 w; ………. | 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 table 090706 p join table 090706 c, table 090706 w on table 090706 p. name=table 090706 c. name and table 090706 p. name=table 090706 w. 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 table 090706 p. name, table 090706 p. price, table 090706 c. color, table 090706 w. weigh t from table 090706 p join table 090706 c, table 090706 w on table 090706 p. name=table 090706 c. name and table 090706 p. name=table 090706 w. 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 table 090706 p. name, table 090706 p. price, table 090706 c. color, table 090706 w. weight from table 090706 p join table 090706 c, table 090706 w on table 090706 p. name=table 090706 c. name and table 090706 p. name=table 090706 w. name where table 090706 p. 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 090706 pp (name char(32), price int); mysql> create table 090706 ww (name char(32), weight int); mysql> insert into table 090706 pp set set name="book", price=1000; name="pen", price=100; name="cd", price=3000; name="dvd", price=4000; mysql> insert into table 090706 ww set set name="book", weight=100; name="pen", weight=10; name="cd", weight=5; name="dvd", weight=5;

検索例 mysql>select table 090706 pp. name, table 090706 pp. price, table 090706 c. color, table 090706 ww. weight from table 090706 pp join table 090706 c, table 090706 ww on table 090706 pp. name=table 090706 c. name and table 090706 pp. name=table 090706 ww. 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 table 090706 pp. name, table 090706 pp. price, table 090706 c. color, table 090706 ww. weight from table 090706 pp join table 090706 c, table 090706 ww on table 090706 pp. name=table 090706 c. name and table 090706 pp. name=table 090706 ww. name where table 090706 pp. 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 table 090706 pp. name, table 090706 pp. price, table 090706 c. color, table 090706 ww. weight from table 090706 pp join table 090706 c, table 090706 ww on table 090706 pp. name=table 090706 c. name and table 090706 pp. name=table 090706 ww. name where table 090706 pp. price <= 2000 and table 090706 ww. weight >= 50; +-------+--------+ | name | price | color | weight | +-------+--------+ | book | 1000 | black | 100 | +-------+--------+ 1 row in set (0. 00 sec)