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

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

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

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

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

42-ͱ͸

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

@2020 Torana, Inc. 42-ͱ͸ σʔλϕʔεʹ͸৭Μͳछྨ͕͋Γ·͢ɻ ༗໊Ͳ͜Ζͩͱʮ.Z42-ʢϚΠΤεΩϡʔΤϧɺϚΠγʔΫΣϧʣʯɺ ʮ1PTUHSF42-ʢϙετάϨεɺϙεάϨʣʯɺʮ42-4FSWFSʢγʔΫΣ ϧαʔόʔɺΤεΩϡʔΤϧαʔόʔʣʯ͋ͨΓͰ͠ΐ͏͔ɻ ฐࣾͰ͸.Z42-Λ࢖༻͍ͯ͠·͢ɻ ਖ਼֬ʹ͸"NB[PO"VSPSB
3%4 <>Ͱ͢ɻ [1]: https://aws.amazon.com/jp/rds/aurora/

؆୯ͳσʔλͷࢀর

@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

@2020 Torana, Inc. ؆୯ͳσʔλΛࢀর ࣮ࡍ͸΋ͬͱෳࡶͳࢦఆΛ͢Δ͜ͱ΋ՄೳͰ͕͢ɺසग़͢Δจ๏Λ֮͑ɺ ඞཁͳͱ͖ʹௐ΂Δͱ4&-&$5จ͕؆୯ʹ͔͚ΔΑ͏ʹͳΓ·͢ɻ ී௨ͷޠֶֶशʢFHӳޠʣͱಉ͡Ͱɺ؆୯ͳจ๏͔ΒΑΓෳࡶͳจ๏Λ ͍֮͑ͯ͘ΠϝʔδͰߏ͍·ͤΜɻ
ಛʹ4&-&$5จ͸ɺҰਓশɺೋਓশɺࡾਓশɺϞϊɺώτɺࠃɺੑผ ʢFHϑϥϯεޠͳͲʣͰจ๏ɾ୯ޠΛ෼͚Δͱ͍ͬͨྫ֎ॲཧ͕ͳ͍ͷ Ͱɺൺֱత֮͑΍͍͢ޠֶͱ΋ݴ͑·͢ɻ

@2020 Torana, Inc. ؆୯ͳσʔλΛࢀর 4&-&$5จҎ֎ʹ΋61%"5&จɺ*/4&35จɺ%&-&5&จɺ$3&"5& 5"#-&จͳͲଞͷจ๏΋ଘࡏ͠·͕͢ɺຊࢿྉͰ͸ֶशൣғ͕޿͘ͳͬ ͯ͠·͏ͨΊׂѪ͠·͢ɻ ͳ͓ɺ౾஌ࣝͰ͕͢ɺ͜ͷΑ͏ʹʮ௥Ճʯʮࢀরʯʮߋ৽ʯʮ࡟আʯͷ
छྨͷૢ࡞ํ๏Λ$SFBUF3FBE6QEBUF%FMFUFͱ͠ɺ   ͜ΕΛলུ͠$36% Ϋϥου ͱݺশ͢Δ͜ͱ͕͋Γ·͢ɻ ࠓճ͜ͷࢿྉͰ཈͑Δൣғ͸ʮࢀরʯͷ෦෼ͷΈͰ͢ɻ

@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 ͜ͷܗࣜΛ֮͑·͠ΐ͏ʂ

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` ͷΑ͏ʹ͢Δͱ   ԋࢉ݁ՌΛฦ͠·͢ɻ

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 ౳Ͱ͢ɻ

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 Λڐ༰͢Δ͔Ͳ͏͔Ͱ͢ ࣍ͷηΫγϣϯͰ΋͏গ͠ৄ͘͠ղઆ͠·͢

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 (...) ͍ͣΕ͔Λؚ·ͳ͍

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 ۟ͱ͸ҟͳΓ·͢ɻ

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 ۟͸औಘͨ͠ϨίʔυͷιʔτΛߦ͏۟Ͱ͢ɻ   ෳ਺ࢦఆ͢Δ͜ͱ͕Ͱ͖·͢ɻ

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 ͸ɺͲͷϨίʔυ͔Βࢀর͢Δ͔Λࢦఆ͢Δ͜ͱ͕Ͱ͖· ͢ɻ

ଞͷςʔϒϧΛ૊Έ߹Θͤͨࢀর

@2020 Torana, Inc. ଞͷςʔϒϧΛ૊Έ߹Θͤͨࢀর ۀ຿Λ͢Δ্Ͱʮސ٬৘ใʯ͚ͩΛࢀর͢Δ͜ͱ͸গͳ͍ͱࢥ͍·͢ɻ ྫ͑͹ɺސ٬ʹඥ෇͍ͨൃૹ৘ใΛऔಘ͍ͨ͠ɺൃૹ৘ใʹඥ෇͍͓ͨ ΋ͪΌ৘ใΛऔಘ͍ͨ͠ɺͳͲଞͷςʔϒϧͷ৘ใ΋ซͤͯࢀর͍ͨ͠ ͱ͍͏έʔε͕͋Δ͔ͳͱࢥ͍·͢ɻ
͜ͷ࣌࢖͏ͷ͕*//&3+0*/΍-&'5+0*/Ͱ͢ɻ

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

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

JOIN ςʔϒϧ B ON ςʔϒϧ A.id = ςʔϒϧ B.{ςʔϒϧ A ͷ୯਺ܗ}_id ฐࣾͷ৔߹ɺςʔϒϧ໊͸ݪଇෳ਺ܗͰ͢ɻ

JOIN ςʔϒϧ B ON ςʔϒϧ A.id = ςʔϒϧ B.{ςʔϒϧ A ͷ୯਺ܗ}_id جຊతʹ͸͜ͷܗͰ֮͑Ε͹ OK! ෳ਺ͷςʔϒϧΛܨ͍͛ͨ৔߹͸͜ΕΛ૿΍ͤ͹ OK

@2020 Torana, Inc. ଞͷςʔϒϧΛ૊Έ߹Θͤͨࢀর ྫ͑͹ൃૹ৘ใ͔Βސ٬؅ཧΛࢀর͢Δ৔߹ SELECT * FROM ൃૹ৘ใ
INNER JOIN ސ٬ͷܖ໿৘ใ ON ސ٬ͷܖ໿৘ใ.id = ൃૹ৘ใ.ސ٬ͷܖ໿৘ใ_id INNER JOIN ސ٬৘ใ ON ސ٬৘ใ.id = ސ٬ͷܖ໿৘ใ.ސ٬৘ใ_id

@2020 Torana, Inc. ଞͷςʔϒϧΛ૊Έ߹Θͤͨࢀর ͔͠͠ଞςʔϒϧΛ݁߹ͨ͠ࡍʹɺۀ຿ଆͰ໰୊ʹͳΔͷ͕ɺ   ಉ༷ͷϨίʔυ͕ෳ਺Ͱ͖ͯ͠·͏͜ͱͰ͢ɻ ͳͥग़དྷͯ͠·͏ͷ͔ɻྫ͑͹ൃૹ৘ใͱൃૹࡁΈ͓΋ͪΌΛྫʹݟͯ
Έ·͢ɻ

@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 ͭͷςʔϒϧΛ ݁߹ͯ͠ΈΔͱ

@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 ࡞੒೔࣌ 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 ࡞੒೔࣌ 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` ͷΑ͏ʹɺςʔϒϧ໊Λ໌ࣔͯ͋͛͠Δඞཁ͕͋Γ·͢ɻ ͜Ε͸શͯͷ۟Ͱಉ༷Ͱ͢ɻ

@2020 Torana, Inc. ଞͷςʔϒϧΛ૊Έ߹Θͤͨࢀর ͷςʔϒϧಉ࢜ͷ݁߹Ͱ͋Ε͹ɺ໰୊ͳ͍ͷͰ͕͢/ͷςʔϒϧ ಉ࢜ͷ݁߹ͷ৔߹͸ɺઌఔͷղઆͷΑ͏ʹ૿͑·͢ɻ ͜͏͍ͬͨࣄྫͷ৔߹ʹɺ͏·͘׆༻͢΂͖ͳͷ͕ɺ(3061#:۟ɺ ͭ·ΓϨίʔυͷάϧʔϐϯάͰ͢ɻ
ྫ͑͹ൃૹ৘ใʹඥ෇͍͓ͨ΋ͪΌͷ਺ΛऔΓ͍ͨͱ͍͏͜ͱͰɺ͓΋ ͪΌ৘ใΛ݁߹͢Δ͚ͩͰ͸ͳ͘ɺ͓΋ͪΌͷ఺਺Λ਺͑ΔͨΊʹά ϧʔϐϯάΛ͢Δඞཁ͕͋Γ·͢ɻ  

@2020 Torana, Inc. ଞͷςʔϒϧΛ૊Έ߹Θͤͨࢀর ͓΋ͪΌͷ఺਺͸ҎԼͷΑ͏ʹ਺͑ΒΕ·͢ɻ SELECT ൃૹ৘ใ.*, COUNT(ൃૹࡁΈ͓΋ͪΌ.id) AS
"͓΋ͪΌͷ఺਺" FROM ൃૹ৘ใ INNER JOIN ൃૹࡁΈ͓΋ͪΌ ON ൃૹࡁΈ͓΋ͪΌ.ൃૹ_id = ൃૹ৘ใ.id GROUP BY ൃૹࡁΈ͓΋ͪΌ.ൃૹ৘ใ_id LIMIT 100

@2020 Torana, Inc. ଞͷςʔϒϧΛ૊Έ߹Θͤͨࢀর ʢ஫ҙʣ(3061#:͸৚݅จ౳Λࢦఆ͠ͳ͍ͱඇৗʹαʔόʔʹෛՙ ͕͔͔ΔͷͰ8)&3&۟͸ඞͣࢦఆ͢ΔΑ͏ʹ͠·͠ΐ͏ɻ ʢ஫ҙʣ-*.*5ͳͲΛ͚ͭΔΑ͏ʹบ͚ͭΔͱɺऔಘ͢Δσʔλͷ ݅਺͕ߜΒΕΔͷͰɺ࢖͏Α͏ʹบ͚͍͚ͭͯͨͩΔͱॿ͔Γ·͢ɻ
 

ؔ਺ͱ৚݅จ

@2020 Torana, Inc. ؔ਺ͱ৚݅จ 42-ʹ͸ؔ਺<>͕ଟ͘ଘࡏ͠·͢ɻάϧʔϐϯάʹӨڹͷ͋Δ΋ͷͱ ͳ͍΋ͷ͕͋Γ·͢ɻΤϯδχΞͰ΋ؔ਺͸શ͍֮ͯ͑ͯΔΘ͚Ͱ͸ͳ ͘ɺௐ΂ͳ͕Β࢖༻͍ͯ͠·͢ɻ ΤΫηϧͷؔ਺ͱࣅ͍ͯ·͢ɻͨͩ͠ɺ4&-&$5จʹ͸ΤΫηϧͷؔ਺
ͷ*'ͷΑ͏ͳ΋ͷ͕ແ͘4&-&$5จͰ͸$"4&ࣜͱ͍͏΋ͷΛ࢖༻ ͢Δ͜ͱʹͳΓ·͢ɻ [1]: https://dev.mysql.com/doc/refman/8.0/ja/built-in-function-reference.html

@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

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

@2020 Torana, Inc. ؔ਺ͱ৚݅จ SELECT COUNT(*) FROM ςʔϒϧ A GROUP
BY ςʔϒϧA.xxx_id SELECT COUNT(*) FROM ςʔϒϧ A άϧʔϐϯά͞ΕͨϨίʔυͷ COUNT άϧʔϐϯά͞Ε͍ͯͳ͍Ϩίʔυͷ COUNT

@2020 Torana, Inc. ؔ਺ͱ৚݅จ $"4&ࣜ͸ɺѻ͍࢝Ί͸෼͔ΓͮΒ͍͔΋͠Ε·ͤΜ͕ɺ   ఆܗϑΥʔϚοτͳͷͰɺ҉ه͢Ε͹ѻ͑ΔΑ͏ʹͳΓ·͢ɻ

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

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

αϒΫΤϦ

@2020 Torana, Inc. αϒΫΤϦ αϒΫΤϦ͸ผ్ʮ෭ΫΤϦʯ΍ʮ෭໰߹ͤʯͱ΋ݴͬͨΓ͠·͢ɻ ؆୯ʹݴ͏ͱ4&-&$5จͷதʹ4&-&$5จΛॻ͘ͱ͍͏͜ͱͰ͢ɻ 8)&3&۟ͷ*/Ͱɺ4&-&$5จΛ࢖ͬͯऔΓग़͢ͱ͍ͬͨΑ͏ʹɺ
  ΑΓෳࡶͳ৚݅จΛॻ͘ࡍʹ༻͍ΒΕΔ͜ͱ͕ଟ͍Ͱ͢ɻ

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

͓΋ͪΌ৘ใ_id IN ( SELECT id FROM toys WHERE id >= 1234 ) ҎԼͷΑ͏ʹ࢖༻Ͱ͖·͢

͓΋ͪΌ৘ใ_id IN ( SELECT id FROM toys WHERE id >= 1234 AND status = ςʔϒϧA.xxx_status )

͓΋ͪΌ৘ใ_id IN ( SELECT id FROM toys WHERE id >= 1234 AND status = ςʔϒϧA.xxx_status ) ςʔϒϧ A ͷΧϥϜ΋   ࢖༻ՄೳͰ͢

ΑΓෳࡶͳ42-จΛٻΊͯ

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

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

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

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

More Decks by memory

Other Decks in Business

Featured

Transcript