09 06/23 PHP ? SQL (MySQL) ??? ??3

1
09 06/23 PHP ? SQL (MySQL) ??? ??3
??21?? ????III (??????2??)

• ?? ????
• ??? 2?

http//okaweb.ec.kyushu-u.ac.jp/lectures/jk3/
?????????????????????????? ?????????????????
2
????

• ???????????
• ???
• ??
• ?
• ??
• ????????????????????????????????????????????????
  ?
• ???????????
• ????1
• ???
• ??
• ????2
• ???
• ?
• ????3
• ???
• ??
• ?????????????????????????????????????????????

3
???????

• mysqlgt create table table090706p (name char(32),
  price char(32))
• mysqlgt create table table090706c (name char(32),
  color char(32))
• mysqlgt create table table090706w (name char(32),
  weight char(32))

4
?????

• mysqlgt insert into table090706p set name"book",
  price"1000"
• mysqlgt insert into table090706p set name"pen",
  price"100"
• mysqlgt insert into table090706p set name"cd",
  price"3000"
• mysqlgt insert into table090706p set name"dvd",
  price"4000"
• mysqlgt insert into table090706c set name"book",
  color"black"
• mysqlgt insert into table090706c set name"pen",
  color"red"
• mysqlgt insert into table090706c set name"pen",
  color"green"
• mysqlgt insert into table090706c set name"pen",
  colorblue"
• mysqlgt insert into table090706c set name"cd",
  color"silver"
• mysqlgt insert into table090706c set name"dvd",
  color"silver"
• mysqlgt insert into table090706w set name"book",
  weight"100"
• mysqlgt insert into table090706w set name"pen",
  weight"10"
• mysqlgt insert into table090706w set name"cd",
  weight"5"
• mysqlgt insert into table090706w set name"dvd",
  weight"5"

5
???

• mysqlgt 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

6
???

• mysqlgt select from table090706p join
  table090706c, table090706w on table090706p.nameta
  ble090706c.name and table090706p.nametable090706w
  .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
• -----------------------------------------

7
???

• mysqlgt select table090706p.name,table090706p.price
  ,table090706c.color,table090706w.weight from
  table090706p join table090706c, table090706w on
  table090706p.nametable090706c.name and
  table090706p.nametable090706w.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
• -----------------------------

8
???

• mysqlgt select table090706p.name,
  table090706p.price,table090706c.color,
  table090706w.weight from table090706p join
  table090706c, table090706w on table090706p.nameta
  ble090706c.name and table090706p.nametable090706w
  .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)

9
????????????????

• mysqlgt create table table090706pp (name char(32),
  price int)
• mysqlgt create table table090706ww (name char(32),
  weight int)
• mysqlgt insert into table090706pp set name"book",
  price1000
• mysqlgt insert into table090706pp set name"pen",
  price100
• mysqlgt insert into table090706pp set name"cd",
  price3000
• mysqlgt insert into table090706pp set name"dvd",
  price4000
• mysqlgt insert into table090706ww set name"book",
  weight100
• mysqlgt insert into table090706ww set name"pen",
  weight10
• mysqlgt insert into table090706ww set name"cd",
  weight5
• mysqlgt insert into table090706ww set name"dvd",
  weight5

10
???

• mysqlgtselect table090706pp.name,
  table090706pp.price, table090706c.color,
  table090706ww.weight from table090706pp join
  table090706c, table090706ww on table090706pp.name
  table090706c.name and table090706pp.nametable0907
  06ww.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)

11
??? ???2000????????

• mysqlgt select table090706pp.name,
  table090706pp.price, table090706c.color,
  table090706ww.weight from table090706pp join
  table090706c, table090706ww on table090706pp.name
  table090706c.name and table090706pp.nametable0907
  06ww.name where table090706pp.price lt 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)

12
??? ???2000???????50????????

• mysqlgt select table090706pp.name,
  table090706pp.price, table090706c.color,
  table090706ww.weight from table090706pp join
  table090706c, table090706ww on table090706pp.name
  table090706c.name and table090706pp.nametable0907
  06ww.name where table090706pp.price lt 2000 and
  table090706ww.weight gt 50
• ----------------------------
• name price color weight
• ----------------------------
• book 1000 black 100
• ----------------------------
• 1 row in set (0.00 sec)

13
????

• ????????????????????????
• ?????????????????????
• ?100?????
• ??????????????????????
• ?500m????
• ???????????????????????????????
• ?200???????1000m??
• ??
• ?????????????????????????????????????????????????
• ???SQL ??????????

14
????

• ???????????????
• ????????
• ????????2?????????????(join ????)?????????
• ?????????????????????????????(?????????)
• 7/13(????)??????????????(?????????????????????????
  ??????)?????????
• ?????????
• Web ??????PHP ???
• ??????????????????????????????????