Ȼ/14

Top / Ȼ / 14

ǡμ갷ˤĤ

絬Ϥäʣäꤹǡ뤳Ȥϥԥ塼ǤϤ褯뤳Ȥ 褯뤳ȤǤΤǡǡ֤ɤΤ褦¸뤫ס¸ǡ ɤΤ褦˼ФפȤǤªưεʤˤΤäȤäƤ̲뤳ȤǡŵŪʺȤˤĤƤϥץޤ٤ȤͤʤǺѤޤ뤳ȤǤ褦ˤʤ*1 ͤΤηȤäΤʲ˽Ҥ٤ǡ١եȥʤɤΤΡȻפɤ

ǡ١(եȥ)Ȥ

绨Ĥ˸С줿ǡνΤΤΤؤ. ޤϡǡ¸뤳ȡޤΥ롼ˤäѤǡ椫ɬפʥǡФ뤳ȡΥǡФεǽäץ饤֥ǡ١եȥǡ١饤֥ʤɤȸƤӡάΤȤƥǡ١ȸƤ֡

ǡ١եȥζߤϤޤϤĤޤꡤʤɤ̤Ǥ뤳Ȥˤ롥 ޤǡ갷ȤòƤ뤳Ȥˤꡤˤǡ˲ǡ»⤿Ƥꡤ䥹ȥ졼ѸΨ⤯ʤäƤꡤʤɤ®ǤäȡιⵡǽǤ롥

ⵡǽϥեȥ粽ѻȶؤꡤ饤ȥ桼ߤȤäƻȤˤ狼ˤȤˤҤ롥



warning.png äơ֤Υǡ¤ˤΤߡòץ ѥǡ١եȥͥǤ뤳ȤϽʬˤꤦ롥 оݤȤǡμ谷ˤ⤽ǡ١եȥȤ٤ɤ ޤϺǽ˸Ƥۤ

ǡ١եȥμ

绨ĤʬΤʤСʰפʤΤ絬ϤʤΤޤǰʲΤ褦3ĤʬǤ

  • ɽ׻եȡñʤƥȥե+Unixޥɷ : ˹ªʤФϤäȤʰפʥǡ١եȥȤ
  • RDBMS(졼ʥǡ١ƥ): ǡ١եȥμήȸʤΤǤ롥ǡˤϼ SQL Ȥ줬Ȥ롥 եȥȤƤϴʰפʤΤϥ饤֥ꡤޥɤȤƼ졤ܳŪʤΤϥФȤƼ롥
  • ¤ȥ졼(NoSQL): Ķ絬Ϥʥǡ䡤ꥢ륿׻뤹褦ʡ RDB(졼ʥǡ١)Ǥϰˤǡ갷  SQL ǤޤʤʤȤȤǤ⤢ΤǡNoSQL (Not Only SQL) ȤƤФ롥

ǡεϤȥǡ١եȥδط

ǡñ㡤⤷ϵϤ

ǡ󥽡 1꤭륱ʤɡǡϤϡǡ١Ƴ֤ǡ륽եΤȥǡδ֤˥ǡ١եȥޤʬäƥȹˤʤ꤫ͤʤ ̵˥ǡ١եȥƳʤƤɤ*2 ñʤƥȥե˥ǡǼƤȤɽ׻եȥʤɤѤ̵ʤ

ޤ㤨ñʴ¬ǡ¤ǤȤ褦˥ǡñʾϤòϤѰդ̤ۤ褯®٤䵭̤ΰʤɤ̤¿ Ͳϡͷ׻ʤɤξϤ¿

ǡʣ⤷ϵϤ礭

ŪȤƤϽϿ䥽եȥ͡¸ʤɤΤ 줰餤εϤ顤ǡ١ѤƤͤǤƤ롥

ʣ䵬ϤۤɤǤʤʳǤϡ

  • SQLite ʤɤΡRDBMS ΤʤǤ⥵ХƥǤϤʤ褦ʷ̥ǡ١եȥ
  • ñʤƥȥե + ʥ륹ץȤȤ߹碌
    餤(ŪȤ̤)Ŭڤȹͤ롥

ǡʣ⤷絬Ϥʾ

⤦ǡʣ⤷絬ϤʥϤˤʤ롥 ŪˤϡҤγƼ̳ѥǡ䥪饤󥷥åԥ󥰥ȤΥǡʤ оݤ ϤǤϼ RDBMS ȤƤ롥 ǯǤϥԥ塼ǽˤꡤñʤƥȥե+륹ץ ǤӤ⽼ʬޤʤȤư⤢롥

ǡĶʣ⤷Ķ絬Ϥʾ

ǡʣäꤽι¤äꡤ⤷ϵϤ 1˼ޤʤ餤絬Ϥäꤹ硤RDB ԸǤ롥 Ϲ¤ȥ졼(NoSQL)ФȤ롥

SQLite

ܼȤѤ SQLite ȤϡRDBMS ΤʤǤǤ̤ǹȤƤ*3եȥ(ǰֹȤƤ SQL 󥸥ȼĥƤ)Ƽѥ饤֥䥳ޥɤȤƼ롥 ФǤϤʤʣʥ󥹥ȡȤפǡȤȤ롥 ޤǡ١ĤˤĤեĺΤᡤХååפʤɤפΤǤ롥

ǡ١ SQL ǹԤᡤ¾ RDBMS ȤޤѤʤԤ롥 ǡΡַ׵ᤷʤȤͻ̤ĤΤǡñ˻Ȥ롥

˻ȤС 2 3Ǥ뤬Ǥ 3(ver.3)Ѥ롥

ζ׻ƥǤϡcygwin, linux(vmware) ξ sqlite3 ޥɤ󥹥ȡ뤵ƤΤǡޥɥ饤󤫤餤ĤǤ sqlite3 ȤȤ롥

  • JNorth_arrow-right-sm.png SQLite:
    SQLite ܲ web Ǥ롥 SQLite ɤ뤳ȤǤ뤬cygwin Linux Ǥϥƥबĥץꥱƥǥ󥹥ȡ뤷ۤ

  • SQLite 2: ľ, Ʊ˼(2009).
    ܲ web ǤҲ𤵤ƤҤǤ롥ܤ̤ꡤˤϤޤˤԤäܤȻפ롥 Ԥˤ򤫤ȻפƤϺܤäƤʤ(Ȥ˥졼ʥǡ١ȤƤΰˤĤ)Τǡ԰ʾ¾νҤʤɤȤѰդƤɤ

  • SQLite ȤɤǸƽФƤ褦 web.
    ǫ˽񤫤줿ʬ䤹Τ⤢ꡤѰ٤ˤʤΤǸƤɤ

SQLite

how-to-treat-db-via-sqlite.png

SQLite ϥ饤֥ꡢ⤷ϥޥɤǡ١. ǡ١륵Ф櫓ǤϤʤ



¾̾ RDBMS ϥեȥȤƤϥзȤ뤳Ȥ¿. ϡͥåȥۤ˥ǡ١򰷤ˤϤԹǡ 礭ʵϤΥǡ١Ǥ(ѼԤʣˤʤ뤳Ȥ⤢)ɬפʷȤ롣 Ф򥤥󥹥ȡ롢Ư뤿ˤϥƥδԸ¤ɬפäꡢüʥȤѰդꤻͤФʤ餺Ѥ뤿ϫϤϾʤ. ФSQLite ϥзǤϤʤñʤ륳ޥɡ⤷ϥ饤֥꤫ǡ١ȤñʻȤߤǤ.

ܼȤǤϡäȤñʥޥɤäƳؤ֤ΤȤ.

SQLite Ѥ

SQLite-commands.png

SQLite Ѥˤढ



SQLite ȤäƤˤϡʲबꡢ줾ˤĤưۤʤ(ޥ)Ѥ롥

  • SQLite ΤΤ: Ƽѹɽʤɡ SQLite ޥ ȸƤФ륳ޥɤѤ롥 SQLite ޥɤ .(ԥꥪ)ǻϤޤñǤ뤳ȤħǤ롥

  • ǡ١: ǡ١ؤơ֥κǡϿǡФʤɡΤꤿȡ ˤ SQL ޥ Ѥ롥 SQL ޥɤϤƤɵʲƤ*4ɸŪʵǽȤΤǤ¾Υǡ١եȥƱͤ˰롥

SQLite ˤǡμ谷

database-table-data-diagram.png

SQLite ǤΥǡμ谷. 1ĤΥǡ١ˡɽ(ơ֥=ǡΥꥹ)Ĥޤޤ롥



̾ϡRDBMS ǤϰĤΥǡ١ˡơ֥(ɽ)ʣꡤ Υơ֥˰Ԥķޤäǥǡ(ˡǡ١ʣ)

ơ֥ܤǡʬऴ(㤨иܵҥꥹȤ¤٤ƺܵҥơ֥롤ʥꥹȤ¤٤뾦ʥơ֥롤ïäȤꥹȤ¤٤ơ֥ʤ)˺뤳ȤꤵƤꡤطդ*5ȤΤξ󤬤ޤȤޤ뤳Ȥˤʤ롥 Τ褦ˡꥹȥåפǤơ֥Ȥ¤١δطޤơ֥ˤ롤Ȥ褦ˡδطϤΤξɽΤ绨Ĥ˸Х졼ʥDB Ǥ롥

ʤSQLite ǤϰĤΥǡ١ĤΥեΤʬפХååפ䤹



μȤǤϡǡ١ꤷƤߤ롥 ŪˤϡĤΥǡ١ ʾΥơ֥롢ܵҾΥơ֥롢嵭ϿΥơ֥äƤߤ롥 ΤĤǰʲɤ߿ʤƤߤ褦



SQLite3 εưλ

ޥɤ SQLite ư = ǡ١ؤ³ or ǡ١κ

SQLite ΥޥɤϡʸͿȤ̾Υǡ١(ե)³褦Ȥ롣 ⤷Υǡ١ʤп˥ǡ١롣 Ȥ db1 Ȥ̾Υǡ١Ȥȡ

sqlite3␣db1

ϤФ褤. sqlite (ver.3) ưƥޥɼե⡼ɤˤʤ(ץץȤ sqlite> ѤΤǵŤ).

notes.png (½) Τ褦ˤ SQLite ư褦 ư餽Τޤޤˤơμ½ޤɤ߿ʤ褦

SQLite νλ

ưSQLite λˤϡ

.exit

.quit

Ǥ褤 줾ʸܤԥꥪɤǤ뤳Ȥդ褦



SQLite3

SQLite ưƤ顢ޤ

.show

ȤƤߤ褦 ߤʲΤ褦ɽ(Ƥˤۤʤ)

      echo: off
       eqp: off
   explain: off
   headers: off
      mode: list
 nullvalue: ""
    output: stdout
 separator: "|"
     stats: off
     width:

ƿ¬Ǥ褦ˡϤ뤬ޤϰʲǤ⤷ƤȲȤ狼פʤΤ, ǥǡ١˥ȤϤƤ

.explain␣on

ϡ̽Ϥ狼䤹ǤꡢŪƤȤϤʤˤȽ


notes.png (½) .show ޥɤ˼¹ԤΤ.explain␣on 򤷤Ƥ



SQLite3 ǤΥơ֥ñȡ

ơ֥

졼ʥǡ١Ȥܼϥǡ󤷤Ǥơ֥Ǥ롣 ĤΥơ֥󤵤ǡϤ٤Ʊ¤򤷤ƤΤȤɬפ롣 ι¤ϡơ֥Ƥɬפ롣

SQLite ǤΥơ֥κˤǡϴñ ǡηά뤳ȤǤΤǡŪʤȤƤ(Ƥʤ)Τ褦ˤƤɤ

CREATE␣TABLE␣ơ֥̾␣(id␣INTEGER␣PRIMARY␣KEY␣AUTOINCREMENT, ѿ̾2, ѿ̾3, ...);


warning.png SQLite3 ; (ߥ)ϤޤǤ̿βԤĤΤǡĹޥɤ 1 ԤϤɬפϤʤʹ֤ˤȤäʬפȤDzԤϤ³ΤɤǤ롥


warning.png Ȥ SQL ̿ʸɽ뤬SQLite3 ϾʸǤʤդ롥 ŪϤȤݤ顢ˤ˾ʸΤޤޤϤƤޤΤñ


warning.png Ǥ ѿ̾1 id ȷƤޤäƤ뤬¤ϼͳ˷ɤ ƤʤϤƤȤʤˤȸǽ


warning.png id θ INTEGER PRIMARY KEY AUTOINCREMENT ΰ̣ϡ id Ȥѿפǡ֤祭Ȥơס֥ǡκѤʤפˤȤ̣Ǥ롥 祭ȤΤϡ绨Ĥ˸СɽǤѿФǡͣǤ뤳ȤݾڤѿǤ롢Ȥ̣Ǥ롥


notes.png (½) Τ褦ˤơޤϾʾΥơ֥Ȥ fruits ơ֥äƤ

CREATE␣TABLE␣fruits␣(id␣INTEGER␣PRIMARY␣KEY␣AUTOINCREMENT, name, price);

դǽ񤤤褦ˡԤǰ쵤ϤɬפϤʤǸ ; (ߥ) ǤĤޤǤ̿ϼ¹Ԥʤ



ơ֥

äơ֥Τϴñǡ

DROP␣TABLE␣ơ֥̾;

ɤ
warning.png δñǤä̤Υǡ򼺤ȤΤǡա



ǡ١ˤɤʥơ֥뤬ͭ뤫Ƥߤ

ĤΥǡ١ˤʣΥơ֥ǼǤ롥 ǡǡ١ˤɤʥơ֥뤬뤫Ĵ٤ˡ񤤤Ƥ .tables ̿(SQLite ̿)Ȥä

.tables

ȤǤ褤


notes.png (½) .tables ޥɤѤơΤ fruits ơ֥뤬Ƥ뤳Ȥǧ褦



ơ֥˥ǡɲä

ơơ֥뤬¸ߤʤФ˥ǡɲäǤ롥 ǡɲä INSERT ̿ȤäưʲΤ褦˹Ԥ

INSERT␣INTO␣ơ֥̾␣VALUES(NULL, ѿ2, ѿ3, ...);


warning.png ȡѿ id Ϣ֤ǼưŪ˿Ϥ.


notes.png (½) Τ褦ˤơ fruits ơ֥˾ʥǡϿ褦

INSERT␣INTO␣fruits␣VALUES(NULL, "banana", 100);

INSERT␣INTO␣fruits␣VALUES(NULL, "strawberry", 200);

INSERT␣INTO␣fruits␣VALUES(NULL, "apple", 150);

INSERT␣INTO␣fruits␣VALUES(NULL, "water melon", 1500);


warning.png ¾Υơ֥ǡ١ǡ򥳥ԡƤˡ䡢¾̤Υե뤫ǡ򥳥ԡƤˡʤɤͭ롥 ɬפˤʤäĴ٤褦



ơ֥Υǡɽ, Ф

ơ֥äƤǡޤϸƤߤȤ SELECT ̿ȤäưʲΤ褦ˤ롥

SELECT␣*␣FROM␣ơ֥̾;


warning.png .explain on ȤƤȤɽʬפ褦ˤʤ롥


notes.png (½) SELECT ޥɤѤơfruits ơ֥˥ǡϿ 뤳Ȥǧ褦 ޤäƤСΤ褦ɽϤ

 id    name           pric
 ----  -------------  ----
 1     banana         100
 2     strawberry     200
 3     apple          150
 4     water melon    1500


ˡꤷƥǡ򸫤ƤߤȤϡ SELECT ̿ WHERE ɲäưʲΤ褦ˤ롥

SELECT␣*␣FROM␣ơ֥̾␣WHERE␣;

WHERE ΤȤ³־פϤȽ˻Ǥ롥 ˤ¿ΤǡʲߤƤޤϴФĤ⤦


notes.png (½) SELECT ޥɤѤơfruits ơ֥ñ 1000߰ʾʤꥹȥåפƤߤ褦

SELECT␣*␣FROM␣fruits␣WHERE␣price>1000;

ޤäƤСΤ褦ɽϤ

 id    name           pric
 ----  -------------  ----
 4     water melon    1500


˺٤ϡfruits ơ֥̾ "ۤˤnana" ȤΤꥹȥåפƤߤ褦

SELECT␣*␣FROM␣fruits␣WHERE␣LIKE('%nana',name);

ޤäƤСΤ褦ɽϤ

 id    name           pric
 ----  -------------  ----
 1     banana         100

warning.png LIKE % ϡǤդʸפȤ̣ġܤĴ٤褦


ˡܤ٤ƤǤϤʤιܤȤ SELECT ̿ΤȤ * ǤϤʤ̾󤹤롥

SELECT␣̾1, ̾2, ... ␣FROM␣ơ֥̾␣WHERE␣;


notes.png (½) SELECT ޥɤѤơfruits ơ֥ñ֤ΤߡץꥹȥåפƤߤ褦

SELECT␣price␣FROM␣fruits;

ޤäƤСΤ褦ɽϤ

 pric
 ----
 100
 200
 150
 1500



ơ֥Υǡ

ơ֥˳ǼƤǡˤϡREPLACE ̿ UPDATE ̿᤬Ȥ롥 Թ˹碌ƹȤ


ޤ REPLACE ϡΤ褦˻Ȥ

REPLACE␣INTO␣ơ֥̾␣VALUES(idֹ, ѿ2, ѿ3, ...);

warning.png REPLACE ̿Ȥä idֹ ĥǡʤ硢ΥǡϤ idֹǿˡϿפ롥 ΰտޤʤϴְ㤤ΤȤˤʤΤαդ褦


ˡUPDATE λȤΤ褦ˤ롥

UPDATE␣ơ֥̾␣SET␣ѿ̾n=ѿn␣WHERE␣;

warning.png WHERE ʹߤάȡǡоݤȤʤ롥


notes.png (½) Τ褦 UPDATE ޥɤѤơfruits ơ֥ΥǡΡwater melon βʤ 2000 ˽褦

UPDATE␣fruits␣SET␣price=2000␣WHERE␣id=4;

ޤäƤСSELECT ޥɤǼΤ褦ɽϤ

 id    name           pric
 ----  -------------  ----
 1     banana         100
 2     strawberry     200
 3     apple          150
 4     water melon    2000


notes.png (½:) ˼Τ褦 UPDATE ޥɤѤơ ñ 300̤ξʤܤ;夲褦

UPDATE␣fruits␣SET␣price=2*price␣WHERE␣price<300;

ޤäƤСSELECT ޥɤǼΤ褦ɽϤ

 id    name           pric
 ----  -------------  ----
 1     banana         200
 2     strawberry     400
 3     apple          300
 4     water melon    2000



ơ֥Υǡ

ơ֥ΥǡˤϡUPDATE ޥɤ˻ DELETE ޥɤȤ롥

DELETE␣FROM␣ơ֥̾␣WHERE␣;

warning.png WHERE ʹߤάȡǡоݤȤʤ롥 ĤޤꡢǡäƤޤ! Τǡְ㤨Ƥʤ褦Ĥ褦


notes.png (½) Τ褦 DELETE ޥɤѤơfruits ơ֥ΥǡΡ strawberry ˤĤƤξ褦

DELETE␣FROM␣fruits␣WHERE␣id=2;

ޤäƤСSELECT ޥɤǼΤ褦ɽϤ

 id    name           pric
 ----  -------------  ----
 1     banana         200
 3     apple          300
 4     water melon    2000

warning.png ٻȤ줿 id ֹ 2 ֤ˤʤäƤ뤳Ȥդ褦


notes.png (½) Τ褦 INSERT ޥɤѤƥǡɲä id ɤΤ褦˰뤫İ褦

INSERT␣INTO␣fruits␣VALUES(NULL, "orange", 400);

ޤäƤСSELECT ޥɤǼΤ褦ɽϤ

 id    name           pric
 ----  -------------  ----
 1     banana         200
 3     apple          300
 4     water melon    2000
 5     orange         400

warning.png ưϢֵǽѤ id ֹ 2 ֤ΤޤޤˤʤäƤ뤳Ȥդ褦



SQLite3 ǤΥơ֥ΡߺѤθ

ǡ١ǤΥơ֥ߺѤδܤϡιܤ̥Ȥƥơ֥Τ뤳ȤǤ롥 񤷤ϤʤäƤߤʤȼ´ˤΤǡ½̤Ƴؤ֤Ȥ褦

½̤Ƴؤ֤ˡ򤷤褦 Ūˤϡ⤦ġ¾Υơ֥Ʊǡ١˺褦


notes.png (½) Τ褦 CREATE ޥɤ INSERT ޥɤѤơΥǡ١ˤ⤦2ĤΥơ֥äƤ


ޤܤϡܵҥꥹȤ¤٤ customers ơ֥Ǥ롥 ܵҤȤ 3ͤۤɥǡƤ

CREATE␣TABLE␣customers␣(id␣INTEGER␣PRIMARY␣KEY␣AUTOINCREMENT, name);

INSERT␣INTO␣customers␣VALUES(NULL,"john");

INSERT␣INTO␣customers␣VALUES(NULL,"tom");

INSERT␣INTO␣customers␣VALUES(NULL,"mike");

SELECT ̿ȤߤȼΤ褦ˤʤäƤϤ

 id    name
 ----  -------------
 1     john
 2     tom
 3     mike


ܤϡꥹȤ¤٤ sales ơ֥Ǥ롥 ꥹȤϡ
  id, ܵid, id, 줿Ŀ
ȤƤ

CREATE␣TABLE␣sales␣(id␣INTEGER␣PRIMARY␣KEY␣AUTOINCREMENT, customer_id, fruit_id, quantity);

ơ5 夲Ƥ

INSERT␣INTO␣sales␣VALUES(NULL,2,5,3);

INSERT␣INTO␣sales␣VALUES(NULL,1,1,1);

INSERT␣INTO␣sales␣VALUES(NULL,2,3,2);

INSERT␣INTO␣sales␣VALUES(NULL,2,1,4);

INSERT␣INTO␣sales␣VALUES(NULL,3,4,10);

SELECT ̿ sales ơ֥ȤߤȼΤ褦ˤʤäƤϤ

 id    customer_id    frui  quan
 ----  -------------  ----  ----
 1     2              5     3
 2     1              1     1
 3     2              3     2
 4     2              1     4
 5     3              4     10


warning.png sales ơ֥¾Υơ֥ξ󤬤äƽự뤳Ȥդ褦

̾Ȥ˥ơ֥Τ

ǤΥǡ١ˤϷ 3ĤΥơ֥뤬ͭϤǤ롥 .tables ޥɤ

.tables

Ȥȡ

 customers  fruits     sales

ȽϤ뤳Ȥǧ褦

3ĤΥơ֥Τ¾Υơ֥ξѤˤʤäƤΤ sales ơ֥ǤΤǡ sales ơ֥¾Υơ֥ξ(join)Ƥߤ褦 ŪˤϡSELECT ȰJOIN ̿ȤäơΤơ֥뤫ФȤ롥 ʸˡȤƤϼΤ褦ʴ

SELECT␣*␣FROM␣ơ֥̾1

␣JOIN␣ơ֥̾2␣ON␣ơ֥̾1.̾=ơ֥̾2.̾;

Ǹ = ǷҤƤĤιܤΥơ֥֤Ƕ̤ξǤ롥 ޤμ½äƤߤۤ狼䤹


notes.png (½) Τ褦, sales ơ֥ǡܵҤ̾褦ˤƤߤ褦

SELECT␣*␣FROM␣sales

␣JOIN␣customers␣ON␣sales.customer_id=customers.id;

ȡΤ褦ʴǥǡϤǤ롥

 id    customer_id    fruit_id       quantity       id    name
 ----  -------------  -------------  -------------  ----  -------------
 1     2              5              3              2     tom
 2     1              1              1              1     john
 3     2              3              2              2     tom
 4     2              1              4              2     tom
 5     3              4              10             3     mike

ΤΡ2(sales ơ֥ customer_id) 5(customers ơ֥ id)פƤ뤳ȤǧƤ


ǤϸˤȤ褦ǤнϤ򸫤䤹ΤˤФ褤 㤨мΤ褦ʴ

SELECT␣sales.id, name, fruit_id, quantity

␣FROM␣sales

␣JOIN␣customers␣ON␣sales.customer_id=customers.id;

Τ褦ʽϤϤ

 id    name           fruit_id       quantity
 ----  -------------  -------------  -------------
 1     tom            5              3
 2     john           1              1
 3     tom            3              2
 4     tom            1              4
 5     mike           4              10

warning.png ơ֥ΤȡƱ̾ȤƤ뤳Ȥ롥 ۣ˵Ҥ᤹̿ȥ顼ˤʤΤǡ ơ֥̾.̾ Ȥƻꤷơ̤򤹤롥 Ǥ SELECT оݤ sales.id Ȥƶ̤ƤΤդ褦


JOIN ϤĤǤ⤤!

JOIN ϤĤǤơ֥Τ롥 ñ˸³ƵҤФǤ롥 μ½dzΤ褦


notes.png (½) sales ơ֥ǡܵҤ̾Ǥʤʤ̾ñ⸫褦ˤ褦 Ūˤˤˤ⤦ join 򤹤ɤ

SELECT␣*␣FROM␣sales

␣JOIN␣customers␣ON␣sales.customer_id=customers.id

␣JOIN␣fruits␣ON␣sales.fruit_id=fruits.id;

ȡ(Ĺ)Τ褦ʽϤϤ

 id    customer_id    fruit_id       quantity       id    name           id  name           price
 ----  -------------  -------------  -------------  ----  -------------  --  -------------  ----------
 1     2              5              3              2     tom            5   orange         400
 2     1              1              1              1     john           1   banana         200
 3     2              3              2              2     tom            3   apple          300
 4     2              1              4              2     tom            1   banana         200
 5     3              4              10             3     mike           4   water melon    2000


狼䤹ʤСΤ褦ʴ

SELECT␣sales.id, customers.name, fruits.name, price, quantity

␣FROM␣sales

␣JOIN␣customers␣ON␣sales.customer_id=customers.id

␣JOIN␣fruits␣ON␣sales.fruit_id=fruits.id;

ȡΤ褦ʬפϤ

 id    name           name           price          quan
 ----  -------------  -------------  -------------  ----
 1     tom            orange         400            3
 2     john           banana         200            1
 3     tom            apple          300            2
 4     tom            banana         200            4
 5     mike           water melon    2000           10

ߤС㤨4ܤμǤ tom 200ߤΥХʥʤ4äƤäȤ狼롥


warning.png ݤʤȤ򤷤ʤǡɽΤΤѰդơǡ­ƤФ󤸤ʤȻפͤ⤤ ȡDzѹȲΥǡȤΰ򼺤äƤޤäꡢ Ǥ tom Ƚ¾Ǥ ton ȵܥߥ̿ͰˤʤäƤޤäꤹΤ ʾϾʥơ֥ñȤǡܵҾϸܵҥơ֥ñȤǴǤġξ ¾Υơ֥뤫黲ȤǤ롢ȤΤ RDB ΥݥȤʤΤǤ롥 ϥǡδΡְפ̣롥 ǡ¿ʣˤʤФʤۤɤΤȤν


ơ˥ǡ򸫤ǤϤʤơùƤߤ褦

SELECT␣sales.id, customers.name, fruits.name, price, quantity, price*quantity

␣FROM␣sales

␣JOIN␣customers␣ON␣sales.customer_id=customers.id

␣JOIN␣fruits␣ON␣sales.fruit_id=fruits.id;

ȼΤ褦ˡñxĿ= ꥹȥåפǤ롥

 id    name           name           price          quan  price*quantit
 ----  -------------  -------------  -------------  ----  -------------
 1     tom            orange         400            3     1200
 2     john           banana         200            1     200
 3     tom            apple          300            2     600
 4     tom            banana         200            4     800
 5     mike           water melon    2000           10    20000


() ơˡïˤ᤹٤פ򸫤Ƥߤ褦 ܵҾȤˤޤȤ櫓ˤ GROUP BY Ȥ̿Ȥ(ܤϳƿͤĴ٤褦) ʤפˤñʤ SUM Ȥ̿᤬Ȥ롥

SELECT␣customers.name, SUM(price*quantity)

␣FROM␣sales

␣JOIN␣customers␣ON␣sales.customer_id=customers.id

␣JOIN␣fruits␣ON␣sales.fruit_id=fruits.id

GROUP␣BY␣customers.id;

ȡΤ褦ʽϤ뤳ȤǤ롥

 name  sum(price*qua
 ----  -------------
 john  200
 tom   2600
 mike  20000

ǡ˹פǤʧäƤ館Ф褤Ǥ롥



Ǹ

SQLite 򥳥ޥɥ饤󤫤ľ RDBMS ŪʬǤ褦ƤȤ RDB Ѥ˶ϡ饤֥ͳзͳȤʤ뤿ᡤ򤷤ʤȤʤȤ뤬ܤƱǤ롥 äݤˤ褯狼ʤʤä顤Τ褦ľܥޥɥ饤󤫤鳺򤷤Ƥߤơºݤ˲뤫ǧƤߤΤɤ



Report: ݡ

ʲβˤĤǽ¤긭ĴȹͻԤ
AppliedMath7-Report-14
Ȥ̾Ĥ e-mail ˤƶ˥ݡȤȤФ. ʤݡȤ e-mail TeX Ǻ̤ˤФƤ褤.

Exercises:

  1. μ½³ơˡɤξʤפǤ줿ϤƤߤ补

  2. SQLite ǡե뤫ǡɤ߹ˡĴ٤补

  3. SQLite Υǡ١ GUI Ǥ륢ץꥱ¿¸ߤ롥 äʪõΤΰĤɤΤǼºݤƻƤߤ补

  4. ʬ SQLite Ѥˤʤꤽʥ󤬤ʤͤƤߤ补



about Icons, ClipArts

For details, see JNorth_arrow-right-sm.png this.


*1 ⤽Ǹ OS 󶡤뵡ǽϤ٤ΤФ
*2 ƳƤϤʤɤ
*3 ͭ̾ɤȡfirefox android ǻȤƤ롥
*4 ΤˤϡӤӵʲƤ뤬Ƽǡ١ΡפޤȤ֡
*5 ̤Υǡ١Υơ֥ȴطŤ뤳Ȥ