åºç« ïŒããã°ã©ãã³ã°åŠç¿ã®éèŠæ§ã«ã€ããŠ
ITæè¡ãæ¥éã«é²åãç¶ããçŸä»£ç€ŸäŒã«ãããŠãããã°ã©ãã³ã°ã¹ãã«ã¯å€ãã®è·æ¥ã§å¿ é ãšãªã£ãŠããŸããããã¯æ¥µããŠè€éãªåé¡ã解決ããããã®äžã€ã®æ段ã§ãããéåžžã«åŒ·åãªæèããŒã«ãšããªããŸãã
ãªãããã°ã©ãã³ã°ãåŠã¶ã¹ããªã®ã
ããã°ã©ãã³ã°ãåŠã¶ãšããããšã¯ãäºå®äžãæãæå¹ãªåé¡è§£æ±ºã®æ段ã身ã«ã€ããããšãšèšããŸããããã°ã©ãã³ã°ã¯è«ççæèãéãã匷åãªæ段ã§ãããäžåºŠç¿åŸãããšããã¯äžçãã®ã®ã¹ãã«ãšãªããŸãããŸããããã°ã©ãã³ã°ã¹ãã«ã¯ããŒã¿åæãããŠã§ãéçºãã²ãŒã äœæãŸã§ãå¹ åºã掻çšç¯å²ãæã£ãŠããŸãããŸããä»åŸãŸããŸãITæè¡ã«äŸåããããã«ãªã瀟äŒã«ãããŠãããã°ã©ãã³ã°ãç解ããŠããããšã¯ç掻ã®è³ªãåäžãããã ãã§ãªãããã£ãªã¢ãã¹ã倧ããåºããå¯èœæ§ãç§ããŠããŸãã
ããã°ã©ãã³ã°åŠç¿ã®å¹æãšã¯
ããã°ã©ãã³ã°ãåŠãã çµæãã©ã®ãããªèœåã身ã«ã€ãã®ã§ããããïŒ ãã®äžã€ãè«ççæèã§ããããã°ã©ãã³ã°ã¯ã³ã³ãã¥ãŒã¿ã«å¯Ÿããæ瀺ãäœæãããããè«ççãªæèãå¿ èŠãšãªããŸããããã¯å ·äœçãªåé¡ãææ¡ãããã®è§£æ±ºçãæ§ç¯ããéçšã§éããããŸãã
ãŸãã匷åãªåé¡è§£æ±ºã¹ãã©ããžãŒãšããŠãããã°ã©ãã³ã°ã¯ã«ã¹ã¿ãã€ãºãããããŒã«äœæãå¯èœã«ããŸããäŸãã°ãæ¯æ¥ã®ã«ãŒãã£ã³äœæ¥ãèªååããããŒã«ããç¹å®ã®åé¡ã解決ããããã®å°çšã®ãœãããŠã§ã¢ãªã©ãèªåã§äœæããããšãå¯èœãšãªããŸãã
ããã«ãããã°ã©ãã³ã°ã¯ååäœæ¥ã®èœåãåäžããããããããŸãããã³ãŒãã¯ãã°ãã°ããŒã ã§äœæãããã¬ãã¥ãŒãããæ¹åãããŸããããã«ãããä»äººãšååããŠå ±éã®ç®æšãžåããããšã®éèŠæ§ãäœæããããšãã§ããŸãã
ãããã¯å šãŠããã°ã©ãã³ã°ãåŠã¶ããšã§åŸãããå¯èœæ§ã®ããå¹æã§ããããããã®ã¹ãã«ã¯ããããåéã«å¿çšå¯èœã§ãã
第äžç« ïŒSQLãšã¯äœã
SQLïŒStructured Query LanguageïŒã¯ãããŒã¿ããŒã¹ã管çããããã®æšæºçãªããã°ã©ãã³ã°èšèªã§ããæ å ±ã®ååŸãæäœãè¡ãããã«åºã䜿çšãããåçšããŒã¿ããŒã¹ãããŒãžã¡ã³ãã·ã¹ãã ïŒDBMSïŒãå«ãå€ãã®ã·ã¹ãã ã§æšæºèšèªãšããŠæ¡çšãããŠããŸãã
SQLã®æŠèŠãšãã®ç¹æ§ã«ã€ããŠ
SQLã¯ããªã¬ãŒã·ã§ãã«ããŒã¿ããŒã¹å ã®ããŒã¿ã管çããããã«èšèšãããç¹åããèšèªã§ããããŒã¿ããŒã¹ã®äœæãã¯ãšãªãæŽæ°ã管çãªã©ãããŒã¿ããŒã¹ç®¡çã·ã¹ãã ïŒDBMSïŒã§è¡ãæ§ã ãªäœæ¥ãå¹ççã«è¡ãããã«çšããããŸãã
SQLã®æãéèŠãªç¹æ§ã®äžã€ã¯ããã®æè»æ§ã§ããSQLã¯è±å¯ãªããŒã¿åããµããŒãããŠãããæ°å€ãæååãæ¥ä»ãããŒã«å€ãªã©ãã»ãŒããããçš®é¡ã®ããŒã¿ãæ±ãããšãã§ããŸãããŸããé«åºŠãªã¯ãšãªæäœãçµæåæã®ããã®éèšé¢æ°ãªã©ã匷åãªæ©èœãæäŸããŠããŸãã
SQLãåŠã¶ã«ããã£ãŠå¿ èŠãªåºç€ç¥è
SQLã¯æ¯èŒççŽæçãªèšèªã§ãããç¹å¥ãªããã°ã©ãã³ã°çµéšããªããŠãåŠã¶ããšãã§ããŸããããããå¹ççãªSQLã®åŠç¿ã«ã¯ä»¥äžã®åºç€ç¥èã圹ç«ã€ã§ãããã
ãŸãã¯ããªã¬ãŒã·ã§ãã«ããŒã¿ããŒã¹ã®åºæ¬çãªæŠå¿µã«ã€ããŠç解ããããšããå§ããŸããããããŒãã«ãè¡ïŒã¬ã³ãŒãïŒãåïŒå±æ§ïŒãããŒãªã©ã®åºæ¬çãªæŠå¿µãéèŠã§ãã次ã«ãããŒã¿åïŒæ°å€åãæååãæ¥ä»åãªã©ïŒã«ã€ããŠãã£ãããšç解ããŠãããšã¹ã ãŒãºã§ãã
ããã«ãSQLæã®åºæ¬çãªããŒãã§ããã»ã¬ã¯ããããã ãããšã¢ãšãã£ãããŒã¯ãŒããšãã®åœ¹å²ïŒãããããããŒã¿ã®æå®ãããŒã¿å ã®æå®ãæ¡ä»¶æå®ïŒãç解ããããšãäžå¯æ¬ ã§ãããŸããããŒã¿ããŒã¹ç®¡çïŒããŒã¿ããŒã¹ã®äœæãæŽæ°ãåé€ãªã©ïŒã«é¢ããåºæ¬çãªSQLã³ãã³ããèŠãããšå®è·µã§åœ¹ç«ã¡ãŸãã
æåŸã«ãå°ãè€éãªã¯ãšãªãäœæããéã«ã¯ãJOINããµãã¯ãšãªãšãã£ãé«åºŠãªæŠå¿µã«ã€ããŠãç解ãæ·±ããŠãããšããã§ãããã
第äºç« ïŒSQLåŠç¿ã®ããã®äººæ°æè²ãã©ãããã©ãŒã
äžæšæ¥ã«ç¶ããæ¬æ¥ã¯SQLåŠç¿ã®ããã®æè²ãã©ãããã©ãŒã ãéžã¶éã«ã泚æãã¹ããã€ã³ããèŠçŽ ãã玹ä»ããŸãããããŠãçŸåšäººæ°ãåããŠããæè²ãã©ãããã©ãŒã ã®äžèŠ§ãèšèŒããŸããããããSQLãåŠã³ãããšæã£ãŠããæ¹ããããã¯æ¢ã«åŠã³å§ããŠããæ¹ã«ãšã£ãŠåœ¹ç«ã€æ å ±ãæäŸã§ããããã«åªããŸãã
ã©ã®ããã«éžã¶ã¹ããïŒè¯ãæè²ãã©ãããã©ãŒã ã®ç¹æ§
æè²ãã©ãããã©ãŒã ãéžã¶éãäžçªéèŠãªããšã¯èªåã®ç®çãšåŠç¿ã¹ã¿ã€ã«ã«åã£ãŠãããããšããç¹ã§ãã
1. ã³ã³ãã³ãã®è³ªïŒè±å¯ãã€é«å質ãªææãããããšã¯éåžžã«éèŠã§ããåå¿è åãããäžçŽè åããŸã§ãå¹ åºãã¬ãã«ã®ã³ãŒã¹ãããããšã確èªããŸãããã 2. åŠç¿ã¹ã¿ã€ã«ïŒèªåã®åŠç¿æ¹æ³ã«åã£ãæè²ãã©ãããã©ãŒã ãéžã¶ããšã¯å¿ é ã§ããäŸãã°ãããã¹ãããŒã¹ã®åŠç¿ã奜ã人ã¯èªã¿ç©ã®ææãæäŸããŠãããã©ãããã©ãŒã ãéžã³ãããžã¥ã¢ã«åŠç¿ãåŸæãªäººã¯ãããªææãè±å¯ãªãã©ãããã©ãŒã ãé©ããŠããŸãã 3. ãŠãŒã¶ãŒãµããŒãïŒæè²ãã©ãããã©ãŒã ã«ã¯åŠç¿ããµããŒãããæ§ã ãªæ©èœãå¿ èŠã§ããäŸãã°ãåŠç¿é²æã®è¿œè·¡ã課é¡æåºããã£ãŒãããã¯ã®æ©èœãªã©ã§ãã 4. ã³ãã¥ããã£ïŒä»ã®åŠç¿è ãšæ å ±äº€æã§ããå ŽããããšãåŠç¿ããã楜ãããªãã ãã§ãªããå°ã£ãæã«å©ããæ±ããããšãã§ããŸãã
ãããã®ç¹ãèæ ®ããŠãèªåã«æé©ãªSQLåŠç¿ãã©ãããã©ãŒã ãéžã³ãŸãããã
泚ç®ã®æè²ãã©ãããã©ãŒã äžèŠ§
çŸåšãæ§ã ãªæè²ãã©ãããã©ãŒã ããããŸããããã®äžã§ãç¹ã«ããããã®ãã®ãããã€ã玹ä»ããŸãã
1. LeetCodeïŒç¹ã«åé¡è§£æ±ºã¹ãã«ãé€ãããæ¹ã«ã¯æé©ãªãã©ãããã©ãŒã ã§ãã 2. SQLZooïŒSQLã®åºæ¬çãªã³ã³ã»ãããå®è·µçã«åŠã³ããæ¹ã«ããããã®ãµã€ãã§ãã 3. CodecademyïŒåå¿è ããäžçŽè åããŸã§å¹ åºãã¬ãã«ã®SQLææãæäŸããŠããŸãã 4. HackerRankïŒSQLã«ç¹åããã»ã¯ã·ã§ã³ããããåé¡è§£æ±ºèœåãšå®åçµéšã®äž¡æ¹ãåŸãããŸãã 5. DataCampïŒããå®è·µçãªã¹ãã«ãå¿ èŠãšããæ¹ã«ããããã®ãã©ãããã©ãŒã ã§ããããŒã¿ãµã€ãšã³ã¹ãšSQLã®é£æºã«éç¹ã眮ããŠããŸãã
åãã©ãããã©ãŒã ã®ç¹æ§ãç解ããèªåã®ããŒãºã«æé©ãªãã®ãéžãã§ãããªãã®SQLåŠç¿æ è¡ãå§ããŸãããã
第äžç« ïŒããããã®ãã©ãããã©ãŒã ã®ç¹åŸŽãšå©ç¹
SQLåŠç¿ã®ããã®ãã©ãããã©ãŒã ã¯æ°å€ãååšããŸãããã®äžã§ããç¹ã«æ³šç®ãã¹ã3ã€ã®ãã©ãããã©ãŒã ã«ã€ããŠãããããã®ç¹åŸŽãšå©ç¹ã«çŠç¹ãåœãŠãŠè©³ããèŠãŠãããŸãããã
ãã©ãããã©ãŒã Aã®ç¹åŸŽãšãã®é å
ãŸãã¯ãã©ãããã©ãŒã Aã§ãããã®ãã©ãããã©ãŒã ã¯ãæåããµããŒããšè³ªã®é«ãææãé åã§ããåå¿è ã§ãå®å¿ããŠäœ¿çšã§ããããã«ãç·Žç¿åé¡ãæš¡æ¬è©Šéšãªã©ãæäŸããŠãããèªåã®ç解床ã確èªããªããåŠç¿ããããšãå¯èœã§ãããŸããåŠç¿è å士ã§æ å ±ã亀æããããçåã解æ¶ãããã§ããã³ãã¥ããã£æ©èœãæããŠããŸããããã«ãããäžäººã§åŠç¿ããéã®å€ç¬æã軜æžããèªå·±åŠç¿ã®ã¢ãããŒã·ã§ã³ãä¿ã€å©ããšãªããŸãã
ãã©ãããã©ãŒã Bã®ç¹åŸŽãšãã®é å
次ã«ãã©ãããã©ãŒã Bã§ãããã®ãã©ãããã©ãŒã ã¯ãå¹ åºãã¬ãã«ã®ææãæã£ãŠããããšã§è©äŸ¡ãããŠãããŸããSQLã®åºç€ããé«åºŠãªãã¯ããã¯ãŸã§ãäžè²«ããŠåŠç¿ããããšãå¯èœã§ãã¬ãã«ã«å¿ããåŠç¿ãè¡ããŸãããŸããå®éã®æ¥åã§äœ¿çšãããå®è·µçãªã·ããªãªã«åºã¥ãææãè±å¯ã«æããŠãããããåŠç¿ããSQLã®ç¥èãå ·äœçãªæ¥åã«æŽ»ããã€ã¡ãŒãžãæã€ããšãã§ããŸãã
ãã©ãããã©ãŒã Cã®ç¹åŸŽãšãã®é å
æåŸã«ãã©ãããã©ãŒã Cã§ãããã®ãã©ãããã©ãŒã ã¯ãç¡æã§å©çšã§ããææãå å®ããŠãããšããç¹ãç¹åŸŽã§ããããã«ãããåããŠSQLã«æ¥ããæ¹ã§ããç¡åã§åŠç¿è³æºãåŸãããšãã§ããŸããå ããŠãç»é²ãŠãŒã¶ãŒåãã«ææçãæäŸããŠããããã詳现ãªææãã³ãã¥ããã£æ©èœãå°é家ã®ãµããŒããå©çšããããšãã§ããŸããååŠè ããçµéšè ãŸã§å¹ åºã察å¿ããŠãããã®ãã©ãããã©ãŒã ã¯ãSQLåŠç¿ã«ãããŠå€§å€å¹æçãªç°å¢ãæäŸããŠããŸãã
第åç« ïŒå®è·µçãªåŠç¿æ¹æ³ãšå¹æçãªå©çšæ³
ãã®ç« ã§ã¯ãã©ã®ããã«SQLãåŠã³å§ããã¹ããªã®ãããã®å ·äœçãªã¹ããããå¹ççãªåŠç¿æ³ããããŠåŠç¿ç¯å²ãå¿çšã®ããã«æ¡åŒµããæ¹æ³ã«ã€ããŠè§£èª¬ããŸãã
å§ãæ¹ïŒSQLåŠç¿ã®ã¹ãããå¶å®
ãŸããSQLåŠç¿ãå§ããã«ã¯ãåºæ¬çãªã¹ããããç解ããããšãéèŠã§ãããã®ç¬¬äžã¹ãããã¯ãSQLã®åºæ¬çãªæŠå¿µãææ¡ããããšã§ããSQLã®æŽå²ããããäœã§ããããç解ãããã次ã«ããŒã¿ããŒã¹ã®åºæ¬æŠå¿µã«ã€ããŠåŠã³ãŸãããããã®ç¥èã¯ãSQLãã©ã®ããã«æ©èœããããç解ããäžã§äžå¯æ¬ ã§ãã
次ã«ãå ·äœçãªSQLæãæžãå§ããŸããããã§ã¯ãããŒã¿ããŒã¹å ã®ããŒã¿ã®ååŸãæäœãè¡ãããã®åºæ¬çãªSQLæïŒSELECTãINSERTãUPDATEãDELETEãªã©ïŒãåŠã³ãŸãã
æåŸã«ãããè€éãªSQLæãåŠã³ãŸãããããã«ã¯ãJOINããµãã¯ãšãªãè€æ°ã®ããŒãã«ããããŒã¿ãååŸããããã®æãªã©ãå«ãŸããŸãã
å¹ççãªåŠç¿æ³ïŒçè«ãåŠãã§ããå®è·µãããçç±
çè«ãåŠãã§ããå®è·µã«ç§»ãåŠç¿æ³ã®æå¹æ§ã¯ã次ã®ç¹ã«ãããŸããçè«åŠç¿ã¯ãæŠå¿µãæ·±ãç解ããåºç€ãç¯ããŸããããã«ãããå®è·µã«ç§»ã£ãæã«ééããåé¡ãããå¹æçã«è§£æ±ºããèœåãç²åŸããŸãã
ãŸããçè«çãªç¥èãå ã«å ·äœçãªåé¡è§£æ±ºæ段ãåŠã¶ããšã§ãæ§ã ãªã·ããªãªã«å¯Ÿããé©å¿åã身ã«ã€ããããšãå¯èœã«ãªããŸããå®è·µã¯ããã®ç¥èãåºå®åããå®éã®ã·ãã¥ãšãŒã·ã§ã³ã«å¿çšããèœåãéããŸãã
å¿çšæ¹æ³ïŒSQLåŠç¿ç¯å²ãå¿çšã®ããã«æ¡åŒµããæ¹æ³
åºæ¬çãªSQLæã®åŠç¿ãäžéãçµãã£ããã次ã¯å¿çšã®ããã®åŠç¿ã§ããããã«ã¯ãããŒã¿ããŒã¹ã®èšèšããSQLãçšããŠããŒã¿ããŒã¹ã®ããã©ãŒãã³ã¹ãæé©åããæ¹æ³ãªã©ãåçŽã¬ãã«ãè¶ ããããŒããå«ãŸããŸãã
èªåèªèº«ã®ãããžã§ã¯ããç«ã¡äžããŠã¿ãã®ããåŠãã ç¥èãå®éã«å¿çšããäžã€ã®æ¹æ³ã§ãã
äŸãã°ãåŠç¿ããSQLç¥èã䜿ã£ãŠãèªåã ãã®ããŒã¿ããŒã¹ãäœæãããããçšããŠäœãæçšãªæ å ±ãçæããã¢ããªã±ãŒã·ã§ã³ãéçºããŠã¿ãŸãããããã®çµéšã¯ãä»åŸããã®ãšã³ãžãã¢ãšããŠåãéã«éåžžã«æçã§ãã
çµè«ïŒSQLåŠç¿ã§äžæ©å ãž
ããªãããããŸã§ã«åŠãã SQLã®ç¥èãšã¹ãã«ã¯ããœãããŠã§ã¢éçºãããŒã¿åæãªã©ãå¹ åºãåéã§æŽ»èºããããã«å¿ èŠãªåºç€ãšãªããŸããããããããã§ã®åŠç¿ãçµããã§ã¯ãããŸããã
SQLåŠç¿ã®çµãããšå§ãŸã
äžèŠãSQLã®åŠç¿ãå®æããããšæããŸãããããã¯æ°ããªå§ãŸãã«éããŸãããæ¢ç©¶ããã»ã©ã«ãæªã ç¥ããªãæ©èœãæ°ããªäœ¿çšæ¹æ³ãèŠã€ããã§ãããããããSQLã®é¢çœãã§ããããŸããæ¬åœã®æå³ã§SQLãç解ããã«ã¯ãå®éã®åé¡è§£æ±ºã«åãçµã¿ãªããæ·±ãç解ãç©ã¿éããŠããããšããã€ã³ãã§ãã
ãªã¢ã«ãªããŒã¿ã»ããã«å¯ŸããŠãèªåèªèº«ã§ã¯ãšãªãäœæãå®è¡ããŠã¿ãã®ãäžäŸã§ãããã®çµéšã¯ãåŠç¿ããSQLã®ç¥èãèªåã®æã§åœ¢ã«ãªãããŸãã«åŠç¿ã®çµãããšå§ãŸãã象城ããäžç¬ãšãªãã§ãããã
ããããã®ããã°ã©ãã³ã°åŠç¿ãšãã£ãªã¢ãã¹
次ã®ã¹ããããšããŠãä»ã®ããã°ã©ãã³ã°èšèªããã¬ãŒã ã¯ãŒã¯ã®åŠç¿ãé²ãããšããã§ããããäŸãã°ãPythonãRã§ããŒã¿åæã«åãçµã¿ãSQLãšé£æºãããã¹ãã«ã磚ãããšãããŸããŸãããŒã¿ããªãã³ãæ±ããããæ代ã«ãããŠäŸ¡å€ããããŸãã
ãŸãããããã®æè¡ã掻ãããŠãã£ãªã¢ãã¹ãèããŠã¿ãŠã¯ãããã§ãããããããŒã¿ãšã³ãžãã¢ãããŒã¿ã¢ããªã¹ããããŒã¿ãµã€ãšã³ãã£ã¹ããªã©ãããããã®æ代ã«æ±ããããè·çš®ã¯æ°å€ããããŸããããŒã¿ãæ±ãåã¯ãããããããã®è·çš®ã«ãããŠãå¿ èŠäžå¯æ¬ ãªã¹ãã«ã§ãã
ãããŸã§åŠã³ãããããåŠã¶SQLã®äžçã¯ç¡éã®å¯èœæ§ãç§ããŠããŸããããã¯ããªãã®å°æ¥ã«å€§ããªåœ±é¿ãäžããæ°ããªç涯åŠç¿ã®æãéããã£ãããšãªããããããŸããã