Upgrade to Pro — share decks privately, control downloads, hide ads and more …

PHPerに知ってほしいRDBな事

 PHPerに知ってほしいRDBな事

PHPカンファレンス2016@北海道の資料です

http://phpcon.sapporo-php.net/2016/

soudai sone

April 16, 2016
Tweet

More Decks by soudai sone

Other Decks in Technology

Transcript

  1. ࣮ߦܭը CREATE TABLE `demo`.`users` ( `id` INT NOT NULL AUTO_INCREMENT

    COMMENT '', `name` VARCHAR(45) NOT NULL COMMENT '', `age` INT NOT NULL COMMENT '', `created` DATETIME NOT NULL DEFAULT NOW () COMMENT '', PRIMARY KEY (`id`) COMMENT '' ); ——શ݅Λબ୒͢ΔͷͰςʔϒϧεΩϟϯ SELECT * FROM demo.users; ——PRIMARY KEYʢINDEXʣΛར༻ͨ͠ྫ SELECT * FROM demo.users WHERE id > 100; ——INDEX͕ແ͍ͷͰςʔϒϧεΩϟϯ SELECT * FROM demo.users WHERE age > 20;
  2. αϒΫΤϦ —— INDEXͱͯ͠PRIMARY KEY͕ޮ͍͍ͯΔ SELECT * FROM users WHERE id

    < 1000 AND id > 800 —— 1ճ͔࣮͠ߦ͞Εͳ͍ SELECT * FROM (SELECT * FROM users WHERE id < 1000 AND id > 800) AS dummy
  3. JOIN SELECT * FROM users INNER JOIN users AS tmp

    ON tmp.id = users.id AND tmp.id BETWEEN 10 AND 100000 WHERE users.created = '2016-02-27 04:31:32'
  4. ෳ਺ར༻ͨ͠INDEX CREATE TABLE public.users ( id integer NOT NULL DEFAULT

    nextval('users_id_seq'::regclass), name text NOT NULL, age integer NOT NULL, created timestamp without time zone NOT NULL DEFAULT now(), CONSTRAINT users_pkey PRIMARY KEY (id)); CREATE INDEX users_created_idx ON public.users USING tree (created); ——idͱcreatedͷINDEXΛར༻͢Δ SELECT * FROM users WHERE id < 100 AND created < '2016-02-27 05:41:28';
  5. ૬ؔαϒΫΤϦ SELECT * FROM users WHERE age IN (SELECT age

    FROM users WHERE id BETWEEN 10 AND 100000)
  6. JE ໊લ ճ౴  TPOF IPHF  40/& GVHB 

    TPVEBJ GPP  UBLFUPNP CBS
  7. JE OBNF  TPOF  40/&  TPVEBJ  UBLFUPNP

    JE ࣭໰  ࣭໰̍  ࣭໰̎  ࣭໰̏  ࣭໰̐ ճ౴ऀ@JE ࣭໰@JE ճ౴ ճ౴೔   IPHF    GVHB    GPP    CBS  ճ౴ऀ ࣭໰ ճ౴
  8. ঎඼໊ ച্ ച্೔ ϓϩάϥϚͷͨΊͷ42-   ಺෦ߏ଄͔ΒֶͿ   ιϑτ΢ΣΞσβΠϯ

      42-࣮ફೖ໳   ཧ࿦͔ΒֶͿσʔλϕʔε   42-ٯҾ͖େશͷۃҙ   ϓϩάϥϚͷͨΊͷ42-   42-࣮ફೖ໳   ཧ࿦͔ΒֶͿσʔλϕʔε   42-࣮ફೖ໳   Ϧʔμϒϧίʔυ   Ϧʔμϒϧίʔυ   σʔλϕʔεɾϦϑΝΫλ   ୡਓʹֶͿ%#ઃܭ   ɿ ɿ ɿ ɿ ɿ ɿ ˞࣮຿Ͱ͸ฤ͸ਖ਼نԽ͢Δ΂͖
  9. ࣮ࡍͷSQL SELECT to_char( sum(“ച্"),'FM999,999,999' ) AS "೔ผച্" , "ച্೔" FROM

    "ച্ද" GROUP BY “ച্೔" ORDER BY "ച্೔" ܻ۠੾ΓͰΧϯϚΛೖΕΔ
  10. ೔ผച্ ച্೔        

                                                    ɿ ɿ ɿ ɿ
  11. ࣮ࡍͷSQL SELECT * , lag("೔ผച্") OVER (ORDER BY "ച্೔")AS લ೔ച্

    FROM (SELECT to_char(sum("ച্"),'FM999,999,999') AS "೔ผച্" , "ച্೔" FROM "ച্ද" GROUP BY "ച্೔") AS "ूܭ"
  12. ࣮ࡍͷSQL SELECT * , lag("೔ผച্") OVER (ORDER BY "ച্೔")AS લ೔ച্

    FROM (SELECT to_char(sum("ച্"),'FM999,999,999') AS "೔ผച্" , "ച্೔" FROM "ച্ද" GROUP BY "ച্೔") AS "ूܭ"
  13. ࣮ࡍͷSQL SELECT * , lag("೔ผച্") OVER (ORDER BY "ച্೔")AS લ೔ച্

    FROM (SELECT to_char(sum("ച্"),'FM999,999,999') AS "೔ผച্" , "ച্೔" FROM "ച্ද" GROUP BY "ച্೔") AS "ूܭ" ઌ΄ͲͷΫΤϦΛαϒΫΤϦʹ͢Δ
  14. ࣮ࡍͷSQL SELECT * , lag("೔ผച্") OVER (ORDER BY "ച্೔")AS લ೔ച্

    FROM (SELECT to_char(sum("ച্"),'FM999,999,999') AS "೔ผച্" , "ച্೔" FROM "ച্ද" GROUP BY "ച্೔") AS "ूܭ" 8JOEPXؔ਺ͷର৅Λࢦఆ ઌ΄ͲͷΫΤϦΛαϒΫΤϦʹ͢Δ
  15. ࣮ࡍͷSQL SELECT * , lag("೔ผച্") OVER (ORDER BY "ച্೔")AS લ೔ച্

    FROM (SELECT to_char(sum("ച্"),'FM999,999,999') AS "೔ผച্" , "ച্೔" FROM "ച্ද" GROUP BY "ച্೔") AS "ूܭ" 8JOEPXؔ਺ͷର৅Λࢦఆ ઌ΄ͲͷΫΤϦΛαϒΫΤϦʹ͢Δ લߦͷऔಘ͢ΔΧϥϜΛࢦఆ
  16. ೔ผച্ ച্೔ લ೔ച্     /6--  

                                                                                             ɿ ɿ ɿ ɿ ɿ ɿ
  17. ೔ผച্ ച্೔ લ೔ച্     /6--  

                                                                                             ɿ ɿ ɿ ɿ ɿ ɿ લߦ͕ແ͍ͷͰ/6--
  18. ؔ਺ આ໌ SPX@OVNCFS ߦ൪߸ SBOL ϥϯΩϯά ಉ཰Ͱ൪߸Λඈ͹͢ EFOTF@SBOL ϥϯΩϯά ಉ཰Ͱ൪߸Λඈ͹͞ͳ͍

    QFSDFOU@SBOL ϥϯΩϯά Ͱදࣔ  SBOL  શߦ਺ DVNF@EJTU QFSDFOU@SBOLʹྨࣅ ݱࡏͷߦͷҐஔ  શߦ਺ OUJMF / ϥϯΩϯά /ʹ෼ׂ MBH WBMVF P⒎TFU EFGBVMU ιʔτঢ়ଶͰͷલͷߦͷ஋ MFBE WBMVF P⒎TFU EFGBVMU ιʔτঢ়ଶͰͷޙͷߦͷ஋ pSTU@WBMVF WBMVF ࠷ॳͷ஋ MBTU@WBMVF WBMVF ࠷ޙͷ஋ OUI@WBMVF WBMVF / /൪໨ͷ஋ ͔Β਺͑Δ