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

ビジネスサイド向け SQL 講座資料(株式会社トラーナ)

memory
PRO
November 24, 2021

ビジネスサイド向け SQL 講座資料(株式会社トラーナ)

社内勉強会として SQL 講座をビジネスサイド向けに行った際の資料です。

memory
PRO

November 24, 2021
Tweet

More Decks by memory

Other Decks in Business

Transcript

  1. Ί΋Γʔ ϏδωεαΠυ޲͚ 42-ߨ࠲ࢿྉ ࣾ಺ษڧձ

  2. @2020 Torana, Inc. ͓͠ͳ͕͖ 42-ͱ͸ ؆୯ͳσʔλͷࢀর ଞͷςʔϒϧΛ૊Έ߹Θͤͨࢀর ؔ਺ͱ৚݅จ

  3. @2020 Torana, Inc. ͓͠ͳ͕͖ άϧʔϐϯά αϒΫΤϦ ΑΓෳࡶͳΫΤϦΛٻΊͯʜ

  4. ஫ҙ

  5. ˞ຊࢿྉ͸ਖ਼֬ͳఆٛΛٻΊ͍ͯΔΘ͚Ͱ͸͋Γ·ͤΜɻ ॳֶऀ͕ɺ͋Δఔ౓෼͔ͬͨؾ࣋ͪʹͳΕΔΑ͏ʹ 
 טΈࡅ͍ͯղઆ͍ͯ͠ΔࢿྉͰ͢ɻ

  6. 42-ͱ͸

  7. @2020 Torana, Inc. 42-ͱ͸ 42-͸ʮΤεΩϡʔΤϧʯɺʮγʔΫΣϧʯͱಡΈɺσʔλϕʔεΛૢ ࡞͢ΔͨΊͷݴޠͰ͢ɻ Կ͔ͷུͱ͔Ͱ͸ͳ͍Ͱ͕͢ɺ8JLJQFEJB<>ʹΑΔͱʮ*#.͕։ൃ ͨ͠4&26&-ʢ4USVDUVSFE&OHMJTI2VFSZ-BOHVBHFʣ͕ݩʯͱॻ ͔Ε͍ͯ·͢ɻ [1]:

    https://ja.wikipedia.org/wiki/SQL
  8. @2020 Torana, Inc. 42-ͱ͸ σʔλϕʔεʹ͸৭Μͳछྨ͕͋Γ·͢ɻ ༗໊Ͳ͜Ζͩͱʮ.Z42-ʢϚΠΤεΩϡʔΤϧɺϚΠγʔΫΣϧʣʯɺ ʮ1PTUHSF42-ʢϙετάϨεɺϙεάϨʣʯɺʮ42-4FSWFSʢγʔΫΣ ϧαʔόʔɺΤεΩϡʔΤϧαʔόʔʣʯ͋ͨΓͰ͠ΐ͏͔ɻ ฐࣾͰ͸.Z42-Λ࢖༻͍ͯ͠·͢ɻ ਖ਼֬ʹ͸"NB[PO"VSPSB

    3%4 <>Ͱ͢ɻ [1]: https://aws.amazon.com/jp/rds/aurora/
  9. ؆୯ͳσʔλͷࢀর

  10. @2020 Torana, Inc. ؆୯ͳσʔλΛࢀর  .Z42-͔ΒσʔλΛࢀর͢Δͱ͖ʹ࢖ΘΕΔͷ͸ʮ4&-&$5จʯ<> ͱݺ͹Ε͍ͯ·͢ɻ  ͦͯ͠ɺ4&-&$5จʹ͸৭ʑͳ۟ DMBVTF

    ͕͋Γ·͢ɻ  '30.۟ɺ+0*/۟ɺ8)&3&۟ɺ)"7*/(۟ɺ(3061#:۟ɺ 03%&3#:۟ɺ-*.*5۟  ͜ΕΒͷ۟͸සग़͢ΔͷͰɺ֮͑·͠ΐ͏ɻ [1]: https://dev.mysql.com/doc/refman/8.0/ja/select.html
  11. @2020 Torana, Inc. ؆୯ͳσʔλΛࢀর  ࣮ࡍ͸΋ͬͱෳࡶͳࢦఆΛ͢Δ͜ͱ΋ՄೳͰ͕͢ɺසग़͢Δจ๏Λ֮͑ɺ ඞཁͳͱ͖ʹௐ΂Δͱ4&-&$5จ͕؆୯ʹ͔͚ΔΑ͏ʹͳΓ·͢ɻ  ී௨ͷޠֶֶशʢFHӳޠʣͱಉ͡Ͱɺ؆୯ͳจ๏͔ΒΑΓෳࡶͳจ๏Λ ͍֮͑ͯ͘ΠϝʔδͰߏ͍·ͤΜɻ

     ಛʹ4&-&$5จ͸ɺҰਓশɺೋਓশɺࡾਓশɺϞϊɺώτɺࠃɺੑผ ʢFHϑϥϯεޠͳͲʣͰจ๏ɾ୯ޠΛ෼͚Δͱ͍ͬͨྫ֎ॲཧ͕ͳ͍ͷ Ͱɺൺֱత֮͑΍͍͢ޠֶͱ΋ݴ͑·͢ɻ
  12. @2020 Torana, Inc. ؆୯ͳσʔλΛࢀর  4&-&$5จҎ֎ʹ΋61%"5&จɺ*/4&35จɺ%&-&5&จɺ$3&"5& 5"#-&จͳͲଞͷจ๏΋ଘࡏ͠·͕͢ɺຊࢿྉͰ͸ֶशൣғ͕޿͘ͳͬ ͯ͠·͏ͨΊׂѪ͠·͢ɻ  ͳ͓ɺ౾஌ࣝͰ͕͢ɺ͜ͷΑ͏ʹʮ௥Ճʯʮࢀরʯʮߋ৽ʯʮ࡟আʯͷ

    छྨͷૢ࡞ํ๏Λ$SFBUF3FBE6QEBUF%FMFUFͱ͠ɺ 
 ͜ΕΛলུ͠$36% Ϋϥου ͱݺশ͢Δ͜ͱ͕͋Γ·͢ɻ  ࠓճ͜ͷࢿྉͰ཈͑Δൣғ͸ʮࢀরʯͷ෦෼ͷΈͰ͢ɻ
  13. @2020 Torana, Inc. SELECT * FROM ςʔϒϧA INNER JOIN ςʔϒϧB

    ON ςʔϒϧA.id = ςʔϒϧB.xxx_id INNER JOIN ςʔϒϧC ON ςʔϒϧB.id = ςʔϒϧC.xxx_id WHERE ςʔϒϧA.ΧϥϜ໊ = "จࣈྻ" AND ςʔϒϧA.ΧϥϜ໊ IN (1, 2, 3, ...) HAVING COUNT(ςʔϒϧA) > 0 ORDER BY id DESC, created_at ASC LIMIT 100 OFFSET 100 ͜ͷܗࣜΛ֮͑·͠ΐ͏ʂ
  14. @2020 Torana, Inc. SELECT * FROM ςʔϒϧA INNER JOIN ςʔϒϧB

    ON ςʔϒϧA.id = ςʔϒϧB.xxx_id INNER JOIN ςʔϒϧC ON ςʔϒϧB.id = ςʔϒϧC.xxx_id WHERE ςʔϒϧA.ΧϥϜ໊ = "จࣈྻ" AND ςʔϒϧA.ΧϥϜ໊ IN (1, 2, 3, ...) HAVING COUNT(ςʔϒϧA) > 0 ORDER BY id DESC, created_at ASC LIMIT 100 OFFSET 100 SELECT ͷ͋ͱͷ * ͸දࣔ͢ΔΧϥϜΛ 
 ఆٛ͢ΔͨΊͷ΋ͷͰ͢ɻ ΧϥϜ໊ΛߜΔ͜ͱʹΑΓɺऔಘ͢Δ σʔλྔ͕ݮΔͨΊɺ৔߹ʹΑͬͯ͸ܰ͘ͳΓ·͢ ·ͨɺFROM ۟͸࣮͸ඞਢͰ͸ͳ͘ɺ SELECT (1 + 1) AS `calculated` ͷΑ͏ʹ͢Δͱ 
 ԋࢉ݁ՌΛฦ͠·͢ɻ
  15. @2020 Torana, Inc. SELECT * FROM ςʔϒϧA INNER JOIN ςʔϒϧB

    ON ςʔϒϧA.id = ςʔϒϧB.xxx_id INNER JOIN ςʔϒϧC ON ςʔϒϧB.id = ςʔϒϧC.xxx_id WHERE ςʔϒϧA.ΧϥϜ໊ = "จࣈྻ" AND ςʔϒϧA.ΧϥϜ໊ IN (1, 2, 3, ...) HAVING COUNT(ςʔϒϧA) > 0 ORDER BY id DESC, created_at ASC LIMIT 100 OFFSET 100 σʔλΛऔಘ͍ͨ͠ςʔϒϧΛબ୒͠·͢ɻ 
 ฐࣾͷ৔߹ family_customers ΍ shipments ౳Ͱ͢ɻ
  16. @2020 Torana, Inc. SELECT * FROM ςʔϒϧA INNER JOIN ςʔϒϧB

    ON ςʔϒϧA.id = ςʔϒϧB.xxx_id INNER JOIN ςʔϒϧC ON ςʔϒϧB.id = ςʔϒϧC.xxx_id WHERE ςʔϒϧA.ΧϥϜ໊ = "จࣈྻ" AND ςʔϒϧA.ΧϥϜ໊ IN (1, 2, 3, ...) HAVING COUNT(ςʔϒϧA) > 0 ORDER BY id DESC, created_at ASC LIMIT 100 OFFSET 100 ଞͷςʔϒϧͷσʔλΛࢀর͢Δࡍʹ࢖༻͠·͢ɻ INNER JOIN Ҏ֎ʹ΋ LEFT JOIN ͳͲ͕͋Γ·͢ɻ INNER JOIN ͱ LEFT JOIN ͷҧ͍͸ 
 NULL Λڐ༰͢Δ͔Ͳ͏͔Ͱ͢ ࣍ͷηΫγϣϯͰ΋͏গ͠ৄ͘͠ղઆ͠·͢
  17. @2020 Torana, Inc. SELECT * FROM ςʔϒϧA INNER JOIN ςʔϒϧB

    ON ςʔϒϧA.id = ςʔϒϧB.xxx_id INNER JOIN ςʔϒϧC ON ςʔϒϧB.id = ςʔϒϧC.xxx_id WHERE ςʔϒϧA.ΧϥϜ໊ = "จࣈྻ" AND ςʔϒϧA.ΧϥϜ໊ IN (1, 2, 3, ...) HAVING COUNT(ςʔϒϧA) > 0 ORDER BY id DESC, created_at ASC LIMIT 100 OFFSET 100 σʔλΛநग़͢Δࡍͷ৚݅Λࢦఆ͢Δͱ͜ΖʹͳΓ·͢ɻ ৚݅จʹ͸৭ʑͳࢦఆํ๏͕͋Γ·͕͢ɺ֓ͶҎԼΛ֮͑Δͱྑ͍͔ͳ ͱࢥ͍·͢ɻ ·ͨɺ৚݅෼Λͭͳ͛Δͱ͖͸ AND ·ͨ͸ OR Ͱܨ͛·͢ɻ 
 ৚݅จ͸ݪଇࠨ͔Βࢦఆํ๏ͷॲཧͷ༏ઌ౓ʹΑͬͯ 
 ॲཧ͞Ε·͕͢ɺ਺ֶͱಉ༷ʹʮ(ʯͱʮ)ʯ 
 Ͱғ͏ࣄʹΑΓɺͦͷΧοί಺͕༏ઌ౓͕ߴ͘ͳΓ·͢ɻ ྫ͑͹ (A OR B) AND C ͱͨ࣌͠ɺ A OR B ΛධՁͨ݁͠Ռʹରͯ͠ AND C ΛධՁ͢Δ͜ͱ͕Ͱ͖·͢ɻ ࢦఆํ๏ ҙຯ = ಉҰ != ಉҰͰ͸ͳ͍ IS NULL NULL Ͱ͋Δ NOT IS NULL NULL Ͱ͸ͳ͍ IN (...) ͍ͣΕ͔ΛؚΉ NOT IN (...) ͍ͣΕ͔Λؚ·ͳ͍
  18. @2020 Torana, Inc. SELECT * FROM ςʔϒϧA INNER JOIN ςʔϒϧB

    ON ςʔϒϧA.id = ςʔϒϧB.xxx_id INNER JOIN ςʔϒϧC ON ςʔϒϧB.id = ςʔϒϧC.xxx_id WHERE ςʔϒϧA.ΧϥϜ໊ = "จࣈྻ" AND ςʔϒϧA.ΧϥϜ໊ IN (1, 2, 3, ...) HAVING COUNT(ςʔϒϧA) > 0 ORDER BY id DESC, created_at ASC LIMIT 100 OFFSET 100 WHERE ۟ͱྨࣅ͍ͯ͠·͕͢ɺ 
 HAVING ۟͸άϧʔϐϯάͨ݁͠Ռʹର࣮ͯ͠ߦ͢Δ఺͕ WHERE ۟ͱ͸ҟͳΓ·͢ɻ
  19. @2020 Torana, Inc. SELECT * FROM ςʔϒϧA INNER JOIN ςʔϒϧB

    ON ςʔϒϧA.id = ςʔϒϧB.xxx_id INNER JOIN ςʔϒϧC ON ςʔϒϧB.id = ςʔϒϧC.xxx_id WHERE ςʔϒϧA.ΧϥϜ໊ = "จࣈྻ" AND ςʔϒϧA.ΧϥϜ໊ IN (1, 2, 3, ...) HAVING COUNT(ςʔϒϧA) > 0 ORDER BY id DESC, created_at ASC LIMIT 100 OFFSET 100 ORDER BY ۟͸औಘͨ͠ϨίʔυͷιʔτΛߦ͏۟Ͱ͢ɻ 
 ෳ਺ࢦఆ͢Δ͜ͱ͕Ͱ͖·͢ɻ
  20. @2020 Torana, Inc. SELECT * FROM ςʔϒϧA INNER JOIN ςʔϒϧB

    ON ςʔϒϧA.id = ςʔϒϧB.xxx_id INNER JOIN ςʔϒϧC ON ςʔϒϧB.id = ςʔϒϧC.xxx_id WHERE ςʔϒϧA.ΧϥϜ໊ = "จࣈྻ" AND ςʔϒϧA.ΧϥϜ໊ IN (1, 2, 3, ...) HAVING COUNT(ςʔϒϧA) > 0 ORDER BY id DESC, created_at ASC LIMIT 100 OFFSET 100 LIMIT ۟͸ɺऔಘ͢ΔϨίʔυͷ਺Λ੍ݶ͢Δͱ͜ΖͰ͢ɻ 
 ੍ݶ͠ͳ͚Ε͹͍͍͡Όͳ͍͔ɺͱࢥ͏͔΋஌Εͳ͍Ͱ͕͢ɺجຊ తʹฐࣾ͸σʔλྔ͕ଟ͘ɺσʔλྔ͕ଟ͍ͱऔಘʹ࣌ؒɺαʔ όʔ΁ෛՙ͕͔͔ΔͷͰɺͳΔ΂͘ࢦఆ͢ΔΑ͏ʹ͍ͯͩ͘͠͞ɻ OFFSET ͸ɺͲͷϨίʔυ͔Βࢀর͢Δ͔Λࢦఆ͢Δ͜ͱ͕Ͱ͖· ͢ɻ
  21. ଞͷςʔϒϧΛ૊Έ߹Θͤͨࢀর

  22. @2020 Torana, Inc. ଞͷςʔϒϧΛ૊Έ߹Θͤͨࢀর  ۀ຿Λ͢Δ্Ͱʮސ٬৘ใʯ͚ͩΛࢀর͢Δ͜ͱ͸গͳ͍ͱࢥ͍·͢ɻ  ྫ͑͹ɺސ٬ʹඥ෇͍ͨൃૹ৘ใΛऔಘ͍ͨ͠ɺൃૹ৘ใʹඥ෇͍͓ͨ ΋ͪΌ৘ใΛऔಘ͍ͨ͠ɺͳͲଞͷςʔϒϧͷ৘ใ΋ซͤͯࢀর͍ͨ͠ ͱ͍͏έʔε͕͋Δ͔ͳͱࢥ͍·͢ɻ

     ͜ͷ࣌࢖͏ͷ͕*//&3+0*/΍-&'5+0*/Ͱ͢ɻ
  23. @2020 Torana, Inc. ଞͷςʔϒϧΛ૊Έ߹Θͤͨࢀর  *//&3+0*/Λ࢖͏ʹͯ͠΋-&'5+0*/Λ࢖͏ʹͯ͠΋ɺͦͷςʔϒ ϧΛͭͳ͙ɺΧϥϜ͕Կ͔Θ͔Βͳ͍ͱɺܨ͙͜ͱ͕Ͱ͖·ͤΜɻ  ฐࣾͰ͸ɺΧϥϜͷ໋໊ʹϧʔϧΛઃ͚͓ͯΓɺ཈͓͑ͯ͘΂͖ϙΠϯ τ͑͞཈͑Ε͹ɺଞͷςʔϒϧΛܨ͙͜ͱ͕Ͱ͖ΔΑ͏ʹͳΓ·͢ɻ

  24. @2020 Torana, Inc. ଞͷςʔϒϧΛ૊Έ߹Θͤͨࢀর SELECT * FROM ςʔϒϧ A INNER

    JOIN ςʔϒϧ B ON ςʔϒϧ A.id = ςʔϒϧ B.{ςʔϒϧ A ͷ୯਺ܗ}_id
  25. @2020 Torana, Inc. ଞͷςʔϒϧΛ૊Έ߹Θͤͨࢀর SELECT * FROM ςʔϒϧ A INNER

    JOIN ςʔϒϧ B ON ςʔϒϧ A.id = ςʔϒϧ B.{ςʔϒϧ A ͷ୯਺ܗ}_id ฐࣾͷ৔߹ɺςʔϒϧ໊͸ݪଇෳ਺ܗͰ͢ɻ
  26. @2020 Torana, Inc. ଞͷςʔϒϧΛ૊Έ߹Θͤͨࢀর SELECT * FROM ςʔϒϧ A INNER

    JOIN ςʔϒϧ B ON ςʔϒϧ A.id = ςʔϒϧ B.{ςʔϒϧ A ͷ୯਺ܗ}_id جຊతʹ͸͜ͷܗͰ֮͑Ε͹ OK! ෳ਺ͷςʔϒϧΛܨ͍͛ͨ৔߹͸͜ΕΛ૿΍ͤ͹ OK
  27. @2020 Torana, Inc. ଞͷςʔϒϧΛ૊Έ߹Θͤͨࢀর  ྫ͑͹ൃૹ৘ใ͔Βސ٬؅ཧΛࢀর͢Δ৔߹ SELECT * FROM ൃૹ৘ใ

    INNER JOIN ސ٬ͷܖ໿৘ใ ON ސ٬ͷܖ໿৘ใ.id = ൃૹ৘ใ.ސ٬ͷܖ໿৘ใ_id INNER JOIN ސ٬৘ใ ON ސ٬৘ใ.id = ސ٬ͷܖ໿৘ใ.ސ٬৘ใ_id
  28. @2020 Torana, Inc. ଞͷςʔϒϧΛ૊Έ߹Θͤͨࢀর  ͔͠͠ଞςʔϒϧΛ݁߹ͨ͠ࡍʹɺۀ຿ଆͰ໰୊ʹͳΔͷ͕ɺ 
 ಉ༷ͷϨίʔυ͕ෳ਺Ͱ͖ͯ͠·͏͜ͱͰ͢ɻ  ͳͥग़དྷͯ͠·͏ͷ͔ɻྫ͑͹ൃૹ৘ใͱൃૹࡁΈ͓΋ͪΌΛྫʹݟͯ

    Έ·͢ɻ
  29. @2020 Torana, Inc. ଞͷςʔϒϧΛ૊Έ߹Θͤͨࢀর id ސ٬ͷܖ໿৘ใ_id ൃૹ೔ 1 1 2021-11-20

    2 2 2021-11-21 ൃૹ৘ใ ൃૹࡁΈ͓΋ͪΌ id ൃૹ৘ใ_id ͓΋ͪΌ৘ใ_id ࡞੒೔࣌ 1 1 1 2021-11-20 2 1 2 2021-11-20 3 1 3 2021-11-20 4 1 4 2021-11-20 5 1 5 2021-11-20 6 1 6 2021-11-20 7 2 1 2021-11-21 8 2 2 2021-11-21 9 2 3 2021-11-21 10 2 4 2021-11-21 11 2 5 2021-11-21 12 2 6 2021-11-21 ͜ͷ 2 ͭͷςʔϒϧΛ ݁߹ͯ͠ΈΔͱ
  30. @2020 Torana, Inc. ଞͷςʔϒϧΛ૊Έ߹Θͤͨࢀর ൃૹࡁΈ͓΋ͪΌ id ސ٬ͷܖ໿৘ใ_id ൃૹ೔ id ൃૹ৘ใ_id

    ͓΋ͪΌ৘ใ_id ࡞੒೔࣌ 1 1 2021-11-20 1 1 1 2021-11-20 1 1 2021-11-20 2 1 2 2021-11-20 1 1 2021-11-20 3 1 3 2021-11-20 1 1 2021-11-20 4 1 4 2021-11-20 1 1 2021-11-20 5 1 5 2021-11-20 1 1 2021-11-20 6 1 6 2021-11-20 2 2 2021-11-21 7 2 1 2021-11-21 2 2 2021-11-21 8 2 2 2021-11-21 2 2 2021-11-21 9 2 3 2021-11-21 2 2 2021-11-21 10 2 4 2021-11-21 2 2 2021-11-21 11 2 5 2021-11-21 2 2 2021-11-21 12 2 6 2021-11-21 ൃૹ৘ใ
  31. @2020 Torana, Inc. ଞͷςʔϒϧΛ૊Έ߹Θͤͨࢀর ൃૹࡁΈ͓΋ͪΌ id ސ٬ͷܖ໿৘ใ_id ൃૹ೔ id ൃૹ৘ใ_id

    ͓΋ͪΌ৘ใ_id ࡞੒೔࣌ 1 1 2021-11-20 1 1 1 2021-11-20 1 1 2021-11-20 2 1 2 2021-11-20 1 1 2021-11-20 3 1 3 2021-11-20 1 1 2021-11-20 4 1 4 2021-11-20 1 1 2021-11-20 5 1 5 2021-11-20 1 1 2021-11-20 6 1 6 2021-11-20 2 2 2021-11-21 7 2 1 2021-11-21 2 2 2021-11-21 8 2 2 2021-11-21 2 2 2021-11-21 9 2 3 2021-11-21 2 2 2021-11-21 10 2 4 2021-11-21 2 2 2021-11-21 11 2 5 2021-11-21 2 2 2021-11-21 12 2 6 2021-11-21 ൃૹ৘ใ ൃૹ৘ใ͕ൃૹࡁΈ͓΋ͪΌͷ 
 Ϩίʔυ਺෼͚ͩ૿͑ΔΑ͏ʹͳΓ·͢ɻ 
 ͜Ε͸࢓༷Ͱ͢ɻ
  32. @2020 Torana, Inc. ଞͷςʔϒϧΛ૊Έ߹Θͤͨࢀর ൃૹࡁΈ͓΋ͪΌ id ސ٬ͷܖ໿৘ใ_id ൃૹ೔ id ൃૹ৘ใ_id

    ͓΋ͪΌ৘ใ_id ࡞੒೔࣌ 1 1 2021-11-20 1 1 1 2021-11-20 1 1 2021-11-20 2 1 2 2021-11-20 1 1 2021-11-20 3 1 3 2021-11-20 1 1 2021-11-20 4 1 4 2021-11-20 1 1 2021-11-20 5 1 5 2021-11-20 1 1 2021-11-20 6 1 6 2021-11-20 2 2 2021-11-21 7 2 1 2021-11-21 2 2 2021-11-21 8 2 2 2021-11-21 2 2 2021-11-21 9 2 3 2021-11-21 2 2 2021-11-21 10 2 4 2021-11-21 2 2 2021-11-21 11 2 5 2021-11-21 2 2 2021-11-21 12 2 6 2021-11-21 ൃૹ৘ใ ྆ํͷςʔϒϧʹ id ͕ଘࡏ͢ΔͨΊ WHERE ۟ͳͲͰ `id = 1234` ͳͲͱͯ͠΋ɺͲͷςʔϒϧͷ id ͳͷ͔൑ผ͕͔ͭͳ͍Α͏ʹͳΓ·͢ɻ 
 ͦͷͨΊ `ൃૹ৘ใ.id = 1234` ͷΑ͏ʹɺςʔϒϧ໊Λ໌ࣔͯ͋͛͠Δඞཁ͕͋Γ·͢ɻ ͜Ε͸શͯͷ۟Ͱಉ༷Ͱ͢ɻ
  33. @2020 Torana, Inc. ଞͷςʔϒϧΛ૊Έ߹Θͤͨࢀর  ͷςʔϒϧಉ࢜ͷ݁߹Ͱ͋Ε͹ɺ໰୊ͳ͍ͷͰ͕͢/ͷςʔϒϧ ಉ࢜ͷ݁߹ͷ৔߹͸ɺઌఔͷղઆͷΑ͏ʹ૿͑·͢ɻ  ͜͏͍ͬͨࣄྫͷ৔߹ʹɺ͏·͘׆༻͢΂͖ͳͷ͕ɺ(3061#:۟ɺ ͭ·ΓϨίʔυͷάϧʔϐϯάͰ͢ɻ

     ྫ͑͹ൃૹ৘ใʹඥ෇͍͓ͨ΋ͪΌͷ਺ΛऔΓ͍ͨͱ͍͏͜ͱͰɺ͓΋ ͪΌ৘ใΛ݁߹͢Δ͚ͩͰ͸ͳ͘ɺ͓΋ͪΌͷ఺਺Λ਺͑ΔͨΊʹά ϧʔϐϯάΛ͢Δඞཁ͕͋Γ·͢ɻ 

  34. @2020 Torana, Inc. ଞͷςʔϒϧΛ૊Έ߹Θͤͨࢀর  ͓΋ͪΌͷ఺਺͸ҎԼͷΑ͏ʹ਺͑ΒΕ·͢ɻ SELECT ൃૹ৘ใ.*, COUNT(ൃૹࡁΈ͓΋ͪΌ.id) AS

    "͓΋ͪΌͷ఺਺" FROM ൃૹ৘ใ INNER JOIN ൃૹࡁΈ͓΋ͪΌ ON ൃૹࡁΈ͓΋ͪΌ.ൃૹ_id = ൃૹ৘ใ.id GROUP BY ൃૹࡁΈ͓΋ͪΌ.ൃૹ৘ใ_id LIMIT 100
  35. @2020 Torana, Inc. ଞͷςʔϒϧΛ૊Έ߹Θͤͨࢀর  ʢ஫ҙʣ(3061#:͸৚݅จ౳Λࢦఆ͠ͳ͍ͱඇৗʹαʔόʔʹෛՙ ͕͔͔ΔͷͰ8)&3&۟͸ඞͣࢦఆ͢ΔΑ͏ʹ͠·͠ΐ͏ɻ  ʢ஫ҙʣ-*.*5ͳͲΛ͚ͭΔΑ͏ʹบ͚ͭΔͱɺऔಘ͢Δσʔλͷ ݅਺͕ߜΒΕΔͷͰɺ࢖͏Α͏ʹบ͚͍͚ͭͯͨͩΔͱॿ͔Γ·͢ɻ

  36. ؔ਺ͱ৚݅จ

  37. @2020 Torana, Inc. ؔ਺ͱ৚݅จ  42-ʹ͸ؔ਺<>͕ଟ͘ଘࡏ͠·͢ɻάϧʔϐϯάʹӨڹͷ͋Δ΋ͷͱ ͳ͍΋ͷ͕͋Γ·͢ɻΤϯδχΞͰ΋ؔ਺͸શ͍֮ͯ͑ͯΔΘ͚Ͱ͸ͳ ͘ɺௐ΂ͳ͕Β࢖༻͍ͯ͠·͢ɻ  ΤΫηϧͷؔ਺ͱࣅ͍ͯ·͢ɻͨͩ͠ɺ4&-&$5จʹ͸ΤΫηϧͷؔ਺

    ͷ*'ͷΑ͏ͳ΋ͷ͕ແ͘4&-&$5จͰ͸$"4&ࣜͱ͍͏΋ͷΛ࢖༻ ͢Δ͜ͱʹͳΓ·͢ɻ [1]: https://dev.mysql.com/doc/refman/8.0/ja/built-in-function-reference.html
  38. @2020 Torana, Inc. ؔ਺ͱ৚݅จ  Α͘࢖͏ؔ਺ͷ୅දྫΛݟͯΈ·͠ΐ͏ɻ [1]: https://dev.mysql.com/doc/refman/8.0/ja/built-in-function-reference.html άϧʔϐϯάʹӨڹͷ͋Δ΋ͷ άϧʔϐϯάʹӨڹͷͳ͍΋ͷ

    COUNT AVG SUM GROUP_CONCAT CONCAT IFNULL NOW UUID
  39. @2020 Torana, Inc. ؔ਺ͱ৚݅จ SELECT COUNT(*) FROM ςʔϒϧ A GROUP

    BY ςʔϒϧA.xxx_id SELECT COUNT(*) FROM ςʔϒϧ A
  40. @2020 Torana, Inc. ؔ਺ͱ৚݅จ SELECT COUNT(*) FROM ςʔϒϧ A GROUP

    BY ςʔϒϧA.xxx_id SELECT COUNT(*) FROM ςʔϒϧ A άϧʔϐϯά͞ΕͨϨίʔυͷ COUNT άϧʔϐϯά͞Ε͍ͯͳ͍Ϩίʔυͷ COUNT
  41. @2020 Torana, Inc. ؔ਺ͱ৚݅จ  $"4&ࣜ͸ɺѻ͍࢝Ί͸෼͔ΓͮΒ͍͔΋͠Ε·ͤΜ͕ɺ 
 ఆܗϑΥʔϚοτͳͷͰɺ҉ه͢Ε͹ѻ͑ΔΑ͏ʹͳΓ·͢ɻ

  42. @2020 Torana, Inc. ؔ਺ͱ৚݅จ SELECT (CASE ΧϥϜ໊ WHEN ஋1 THEN

    ݁Ռ1 WHEN ஋2 THEN ݁Ռ2 ELSE ͦͷଞ END) AS `ΧϥϜ໊` FROM ςʔϒϧ A
  43. @2020 Torana, Inc. ؔ਺ͱ৚݅จ SELECT (CASE ΧϥϜ໊ WHEN ஋1 THEN

    ݁Ռ1 WHEN ஋2 THEN ݁Ռ2 ELSE ͦͷଞ END) AS `ΧϥϜ໊` FROM ςʔϒϧ A ͜ͷఆܗΛ֮͑Ε͹ OK
  44. αϒΫΤϦ

  45. @2020 Torana, Inc. αϒΫΤϦ  αϒΫΤϦ͸ผ్ʮ෭ΫΤϦʯ΍ʮ෭໰߹ͤʯͱ΋ݴͬͨΓ͠·͢ɻ  ؆୯ʹݴ͏ͱ4&-&$5จͷதʹ4&-&$5จΛॻ͘ͱ͍͏͜ͱͰ͢ɻ  8)&3&۟ͷ*/Ͱɺ4&-&$5จΛ࢖ͬͯऔΓग़͢ͱ͍ͬͨΑ͏ʹɺ

    
 ΑΓෳࡶͳ৚݅จΛॻ͘ࡍʹ༻͍ΒΕΔ͜ͱ͕ଟ͍Ͱ͢ɻ
  46. @2020 Torana, Inc. αϒΫΤϦ SELECT * FROM ςʔϒϧ A WHERE

    ͓΋ͪΌ৘ใ_id IN ( SELECT id FROM toys WHERE id >= 1234 )
  47. @2020 Torana, Inc. αϒΫΤϦ SELECT * FROM ςʔϒϧ A WHERE

    ͓΋ͪΌ৘ใ_id IN ( SELECT id FROM toys WHERE id >= 1234 ) ҎԼͷΑ͏ʹ࢖༻Ͱ͖·͢
  48. @2020 Torana, Inc. αϒΫΤϦ SELECT * FROM ςʔϒϧ A WHERE

    ͓΋ͪΌ৘ใ_id IN ( SELECT id FROM toys WHERE id >= 1234 AND status = ςʔϒϧA.xxx_status )
  49. @2020 Torana, Inc. αϒΫΤϦ SELECT * FROM ςʔϒϧ A WHERE

    ͓΋ͪΌ৘ใ_id IN ( SELECT id FROM toys WHERE id >= 1234 AND status = ςʔϒϧA.xxx_status ) ςʔϒϧ A ͷΧϥϜ΋ 
 ࢖༻ՄೳͰ͢
  50. ΑΓෳࡶͳ42-จΛٻΊͯ

  51. 2ސ٬ͱطఆͷॅॴʹઃఆ͞Εͨސ٬ͷॅॴΛ 
 औಘ͢Δ42-Λॻ͍ͯΈ·͠ΐ͏

  52. 2ސ٬͝ͱʹࠓ·Ͱʹૹ͓ͬͨ΋ͪΌͷ૯਺Λ 
 औಘ͢Δ42-Λॻ͍ͯΈ·͠ΐ͏