ã¯ããã«
è¿å¹ŽãããŒã¿é§ååã®ææ決å®ãããžãã¹ã®çŸå Žã§éèŠèŠãããããã«ãªã£ãŠããŸããããã®äžå¿ã«äœçœ®ããã®ããããŒã¿ããŒã¹èšèªã§ããSQLã§ãããã®ã»ã¯ã·ã§ã³ã§ã¯ãSQLã®åºæ¬çãªæŠå¿µãšããªãå€ãã®äººã ãåŠã¶äŸ¡å€ãæããŠããã®ãã«ã€ããŠè§£èª¬ããŸãã
SQLãšã¯äœãïŒ
SQLïŒStructured Query LanguageïŒã¯ããªã¬ãŒã·ã§ãã«ããŒã¿ããŒã¹ç®¡çã·ã¹ãã ïŒRDBMSïŒãšåŒã°ããããŒã¿ããŒã¹ãšããåããããããã®èšèªã§ããããŒã¿ã®æ€çŽ¢ãæ¿å ¥ãæŽæ°ãåé€ãªã©ãããŒã¿ããŒã¹ã«å¯Ÿããæ§ã ãªæäœãè¡ãããšãã§ããŸããSQLã¯ãããžãã¹ã®çŸå Žã§ã®ããŒã¿åæãããŠã§ãã¢ããªã±ãŒã·ã§ã³ã®ããã¯ãšã³ãã§ã®ããŒã¿ããŒã¹æäœãªã©ãå€å²ã«ãããå Žé¢ã§äœ¿çšãããŠããŸãã
äŸãã°ãããECãµã€ãã§ã®ååã®å£²äžã©ã³ãã³ã°ãååŸãããå Žåããç¹å®ã®æ¡ä»¶ãæºãã顧客ã®ãªã¹ããæœåºãããå Žåãªã©ãSQLã䜿ã£ãŠç°¡åã«æ å ±ãååŸããããšãã§ããŸãããã®ãããªããŒã¿æäœãæ軜ã«ããã€å¹ççã«è¡ãããã®ããŒã«ãšããŠãSQLã¯å€ãã®å°é家ã«æ¯æãããŠããŸãã
ãªãSQLãåŠã¶ã®ãïŒ
çŸä»£ã®ããžãã¹ç°å¢ã§ã¯ãããŒã¿ã¯æ°ãããç³æ²¹ããšã称ãããã»ã©ã®äŸ¡å€ãæã£ãŠããŸãããã®ããŒã¿ãå¹æçã«æŽ»çšããããšã§ãããžãã¹ã®ç«¶äºåãé«ããããšãã§ããã®ã§ããSQLã¯ããã®ããŒã¿ãæ±ãããã®ããŒãšãªãã¹ãã«ã§ãã
ãŸããå€ãã®äŒæ¥ãããŒã¿é§ååã®ææ決å®ãæšé²ããŠããäžãSQLãç解ããŠããããšã¯ããã£ãªã¢ã¢ããã®ããã®å€§ããªã¢ããã³ããŒãžãšãªããŸããããŒã¿ãµã€ãšã³ãã£ã¹ããããŒã¿ã¢ããªã¹ãã¯ãã¡ãããããŒã±ãã£ã³ã°ãå¶æ¥ãªã©ã®æ¥åã§ããããŒã¿ã掻çšããææ決å®ãæ±ãããããããSQLã®ã¹ãã«ã¯éåžžã«äŸ¡å€ããããŸãã
ããã«ãSQLãåŠã¶ããšã§ãããŒã¿ã®æ§é ãããŒã¿ããŒã¹ã®ä»çµã¿ãç解ããããšãã§ããããé«åºŠãªããŒã¿æäœãåæãå¯èœãšãªããŸããããã«ãããããžãã¹ã®çŸå Žã§ã®åé¡è§£æ±ºèœåããæ°ãã䟡å€ãçã¿åºãèœåãåäžããŸãã
ãã®ããã«ãSQLãåŠã¶ããšã¯ãçŸä»£ã®ããžã¿ã«æ代ã«ãããŠãå€ãã®ã¡ãªããããããããã®ãšãªã£ãŠããŸãã
SQLåŠç¿ã®åºç€
ããŒã¿ããŒã¹ãšã¯ãæ å ±ãå¹ççã«ä¿åã管çãæ€çŽ¢ããããã®ã·ã¹ãã ã§ãããã®ã»ã¯ã·ã§ã³ã§ã¯ãããŒã¿ããŒã¹ã®åºæ¬çãªæŠå¿µãšãSQLã®äž»ãªçš®é¡ãšãã®ç¹åŸŽã«ã€ããŠè§£èª¬ããŸãã
ããŒã¿ããŒã¹ã®åºæ¬æŠå¿µ
ããŒã¿ããŒã¹ã¯ãæ å ±ãæŽçããŠä¿åããããã®ãç®±ãã®ãããªãã®ãšèããããšãã§ããŸãããã®ãç®±ãã®äžã«ã¯ãããŒãã«ãšåŒã°ããæ å ±ã®ãŸãšãŸããå ¥ã£ãŠãããããããã®ããŒãã«ã¯ç¹å®ã®æ å ±ãæã£ãŠããŸããäŸãã°ã顧客æ å ±ã®ããŒãã«ãååæ å ±ã®ããŒãã«ãªã©ãèããããŸãã
ããŒãã«ã®äžã«ã¯ãã¬ã³ãŒããšåŒã°ããæ å ±ã®è¡ããããããããã®ã¬ã³ãŒãã¯ç¹å®ã®ããŒã¿ãæã£ãŠããŸããäŸãã°ã顧客æ å ±ã®ããŒãã«ã®1ã€ã®ã¬ã³ãŒãã¯ã1人ã®é¡§å®¢ã®ååãäœæãé»è©±çªå·ãªã©ã®æ å ±ãæã£ãŠãããšèããããšãã§ããŸãã
ãŸããããŒãã«ã®äžã«ã¯ã«ã©ã ãšåŒã°ããæ å ±ã®åããããããããã®ã«ã©ã ã¯ç¹å®ã®çš®é¡ã®ããŒã¿ãæã£ãŠããŸããäŸãã°ã顧客æ å ±ã®ããŒãã«ã®ã«ã©ã ã«ã¯ãååãäœæãé»è©±çªå·ãªã©ã®æ å ±ãå ¥ã£ãŠããŸãã
SQLã®çš®é¡ãšç¹åŸŽ
SQLã¯ãããŒã¿ããŒã¹ãšããåããããããã®èšèªã§ãããå®ã¯ããã€ãã®çš®é¡ãååšããŸããäž»ãªãã®ãšããŠã¯ãDDLïŒData Definition LanguageïŒãDMLïŒData Manipulation LanguageïŒãDCLïŒData Control LanguageïŒããããŸãã
DDLã¯ãããŒã¿ããŒã¹ã®æ§é ãå®çŸ©ããããã®èšèªã§ãããŒãã«ã®äœæãå€æŽãåé€ãªã©ã®æäœãè¡ãããšãã§ããŸããå ·äœçãªã³ãã³ããšããŠã¯ãCREATEãALTERãDROPãªã©ããããŸãã
DMLã¯ãããŒã¿ããŒã¹ã®ããŒã¿ãæäœããããã®èšèªã§ãããŒã¿ã®æ€çŽ¢ãæ¿å ¥ãæŽæ°ãåé€ãªã©ã®æäœãè¡ãããšãã§ããŸããå ·äœçãªã³ãã³ããšããŠã¯ãSELECTãINSERTãUPDATEãDELETEãªã©ããããŸãã
DCLã¯ãããŒã¿ããŒã¹ã®ããŒã¿ãžã®ã¢ã¯ã»ã¹æš©éã管çããããã®èšèªã§ãããŒã¿ãžã®ã¢ã¯ã»ã¹æš©éã®ä»äžãåãæ¶ããè¡ãããšãã§ããŸããå ·äœçãªã³ãã³ããšããŠã¯ãGRANTãREVOKEãªã©ããããŸãã
ãããã®SQLã®çš®é¡ãç解ããããšã§ãããŒã¿ããŒã¹ãšã®ããåããããã¹ã ãŒãºã«ãªããå¹ççãªããŒã¿æäœãå¯èœãšãªããŸãã
SQLåŠç¿ã®ããã®ç¡æãµã€ãäžèŠ§
SQLã®åŠç¿ãå§ããéãå€ãã®äººãå©çšããã®ããªã³ã©ã€ã³ã®åŠç¿ãµã€ãã§ãããããã®ãµã€ãã¯ãåå¿è ããäžçŽè ãŸã§å¹ åºãã¬ãã«ã®åŠç¿è ã«å¯Ÿå¿ããŠãããæ軜ã«åŠç¿ãå§ããããšãã§ããŸããä»åã¯ãç¹ã«ããããã®ç¡æåŠç¿ãµã€ããããã€ã玹ä»ããŸãã
Progateã®ç¹åŸŽãšå©ç¹
Progateã¯ãã¹ã©ã€ã圢åŒã§åŠç¿ãé²ããããšãã§ãããµã€ãã§ããåå¿è ã«åªããå 容ãšãªã£ãŠãããåºç€ãããã£ãããšåŠã¶ããšãã§ããŸãããŸããå®éã«ã³ãŒããæžããªããåŠç¿ãé²ããããšãã§ããã®ã§ãå®è·µçãªã¹ãã«ã身ã«ã€ããããšãã§ããŸãã
TRYCODEã®ç¹åŸŽãšå©ç¹
TRYCODEã¯ãå®éã®åé¡ã解ããªããåŠç¿ãé²ããããšãã§ãããµãŒãã¹ã§ããå ·äœçãªããžãã¹ã®ã·ãŒã³ãæ³å®ããåé¡ãå€ããå®éã®æ¥åã§åœ¹ç«ã€ã¹ãã«ã身ã«ã€ããããšãã§ããŸãã
paizaã©ãŒãã³ã°ã®ç¹åŸŽãšå©ç¹
paizaã©ãŒãã³ã°ã¯ãåç»ãèŠãªããã³ãŒããæžã圢åŒã§åŠç¿ãé²ããããšãã§ãããµã€ãã§ããããžã¥ã¢ã«ãªåŠç¿ãåŸæãªäººã«ç¹ã«ããããã§ãåç»ã§ã®è§£èª¬ãèããªãããå®éã«ã³ãŒããæžãããšã§ç解ãæ·±ããããšãã§ããŸãã
SQLæ»ç¥ã®ç¹åŸŽãšå©ç¹
SQLæ»ç¥ã¯ãæç« ãèªã¿ãªããåŠç¿ãé²ããããšãã§ãããµã€ãã§ããå šãŠã®åé¡ãç¡æã§æŒç¿ããããšãã§ããåå¿è ããäžçŽè ãŸã§å¹ åºãã¬ãã«ã®åŠç¿è ã«å¯Ÿå¿ããŠããŸãã
Tech Pjinã®ç¹åŸŽãšå©ç¹
Tech Pjinã¯ãçŽ70åã®æŒç¿åé¡ãçšæãããŠãããµã€ãã§ããããããã®åé¡ã«ã¯ãçãã®è£è¶³èª¬æãä»ããŠãããèªåã®è§£çãšç §ããåãããªããåŠç¿ãé²ããããšãã§ããŸãã
ãããã®ãµã€ãã¯ãããããç°ãªãç¹åŸŽãå©ç¹ããããŸãã®ã§ãèªåã®åŠç¿ã¹ã¿ã€ã«ãç®çã«åãããŠéžã¶ããšãéèŠã§ãããŸããè€æ°ã®ãµã€ããçµã¿åãããŠåŠç¿ãé²ããããšã§ãããå¹æçãªåŠç¿ãå¯èœãšãªããŸãã
å¹æçãªåŠç¿æ¹æ³
SQLã®åŠç¿ãå§ãããšãå€ãã®äººããã©ã®ããã«åŠç¿ãé²ããã°å¹æçãããšããçåãæã€ããšã§ãããããã®ã»ã¯ã·ã§ã³ã§ã¯ãå®è·µçãªæŒç¿ã®éèŠæ§ããå®æçãªåŸ©ç¿ã®æ¹æ³ã«ã€ããŠè§£èª¬ããŸãã
å®è·µçãªæŒç¿ã®éèŠæ§
SQLã®åŠç¿ã«ãããŠãå®è·µçãªæŒç¿ã¯éåžžã«éèŠã§ããçç±ãšããŠã¯ãSQLã¯ã䜿ã£ãŠèŠãããã¿ã€ãã®ã¹ãã«ã§ãããããå®éã«æãåãããŠã³ãŒããæžãããšã§ãç解ãæ·±ãŸãããã§ãã
äŸãã°ãããã¹ããåç»ã§åŠãã ç¥èããããã«å®éã®ããŒã¿ããŒã¹ã§è©ŠããŠã¿ãããšã§ããã®ç¥èãå®éã®æ¥åã§ã©ã®ããã«åœ¹ç«ã€ã®ããäœæããããšãã§ããŸãããŸããèªåã§èããŠã³ãŒããæžãããšã§ãç¥èãå®çãããããªããŸãã
ããã«ãå®éã®æ¥åãæ³å®ããåé¡ã解ãããšã§ãå®åã§ã®å¿çšåãé€ãããšãã§ããŸãããã®ãããªå®è·µçãªæŒç¿ãç¹°ãè¿ãããšã§ãåå¿è ããäžçŽè ãäžçŽè ãžãšã¹ãããã¢ããããããšãã§ããã®ã§ãã
å®æçãªåŸ©ç¿ã®ã¹ã¹ã¡
åŠç¿ããç¥èãé·æçã«èŠããŠããããã«ã¯ãå®æçãªåŸ©ç¿ãæ¬ ãããŸãããç¹ã«ãSQLã®ãããªæè¡çãªç¥èã¯ã䜿ããªããšå¿ããŠããŸãããšãå€ããããå®æçã«åŸ©ç¿ãè¡ãããšã§ç¥èãå®çãããããšãã§ããŸãã
埩ç¿ã®æ¹æ³ãšããŠã¯ãéå»ã«åŠãã å 容ãå床ããã¹ããåç»ã§ç¢ºèªããã ãã§ãªããå®éã«ããŒã¿ããŒã¹ã§ã³ãŒããæžããŠã¿ãããšãããããã§ãããŸããåŠç¿ããå 容ãä»ã®äººã«æããããšã§ãèªåã®ç解ãæ·±ããããšãã§ããŸãã
ããã«ãå®æçã«æ°ããç¥èãåŠã¶ããšã§ãæ¢ã«åŠãã ç¥èãšã®é¢é£æ§ãèŠã€ããããšãã§ããå šäœãšããŠã®ç解ãæ·±ãŸãããšããããŸãããã®ããã«ãå®æçãªåŸ©ç¿ãšæ°ããç¥èã®åŠç¿ãçµã¿åãããããšã§ãå¹æçãªåŠç¿ãå¯èœãšãªããŸãã
SQLåŠç¿ã®é²ãæ¹
SQLã®åŠç¿ãå§ããéãã©ã®ããã«é²ããã°è¯ãã®ããã©ã®ã¹ããŒãžã§äœã«æ³šæãã¹ããã¯éåžžã«éèŠã§ãããã®ã»ã¯ã·ã§ã³ã§ã¯ãåå¿è ãé¥ãããããã©ããããäžçŽè ã«ãªãããã®ã¹ãããã«ã€ããŠè§£èª¬ããŸãã
åå¿è ãé¥ãããããã©ãã
SQLã®åŠç¿ãå§ãããšãå€ãã®åå¿è ãããã€ãã®ãã©ããã«é¥ããã¡ã§ãããŸããé床ãªæ å ±åéã«æéãåãããããšããããŸããå€ãã®æ å ±ãæã«å ¥ãçŸä»£ã§ã¯ãã©ã®ææãè¯ãã®ãè¿·ã£ãŠããŸããã¡ã§ãããå®éã«æãåãããŠåŠç¿ãå§ããããšã倧åã§ãã
ãŸããå®ç§ãæ±ããããåŸåããããŸããåããŠã®åŠç¿ã§ã¯ãå šãŠãå®ç§ã«ç解ããã®ã¯é£ãããã®ã§ãã倧åãªã®ã¯ãäžã€äžã€ã®ã¹ããããèžã¿ãªãããåŸã ã«ç解ãæ·±ããŠããããšã§ãã
ããã«ãç¶ç¶çãªåŠç¿ãé£ãããšæããããšããããŸããæ¯æ¥å°ããã€åŠç¿ãé²ããããšã§ãç¥èãå®çãããããªããŸããçæéã§ãè¯ãã®ã§ãæ¯æ¥ã®ç¿æ £ãšããŠåŠç¿ãç¶ããããšãããããã§ãã
äžçŽè ã«ãªãããã®ã¹ããã
åå¿è ãåæ¥ããäžçŽè ã«ãªãããã«ã¯ãããã€ãã®ã¹ããããèžãå¿ èŠããããŸãããŸããåºæ¬çãªææ³ããã£ãããšç解ããããšã倧åã§ããSELECTæãJOINãGROUP BYãªã©ã®åºæ¬çãªææ³ããã¹ã¿ãŒããããšã§ãå€ãã®ããŒã¿ããŒã¹æäœãå¯èœãšãªããŸãã
次ã«ãå®éã®ããŒã¿ããŒã¹ã§ã®æŒç¿ãå¢ããããšãéèŠã§ããå®éã®ããŒã¿ã䜿ã£ãŠåé¡ã解ãããšã§ãå®åã§ã®å¿çšåãé€ãããšãã§ããŸãã
ãŸããä»ã®åŠç¿è ãšã®æ å ±äº€æãããããã§ãããªã³ã©ã€ã³ã®ã³ãã¥ããã£ãå匷äŒãªã©ã§ãä»ã®åŠç¿è ãšæ å ±ã亀æããããšã§ãæ°ããç¥èãèŠç¹ãåŸãããšãã§ããŸãã
æåŸã«ãå®æçãªåŸ©ç¿ãå¿ããã«è¡ãããšã§ãç¥èãé·æçã«å®çãããããšãã§ããŸããç¹ã«ãå¿ããã¡ãªéšåãé£ããéšåãäžå¿ã«åŸ©ç¿ãè¡ãããšãããããã§ãã
ãã®ããã«ãäžã€äžã€ã®ã¹ããããèžã¿ãªãããç¶ç¶çã«åŠç¿ãé²ããããšã§ãäžçŽè ãšããŠã®ã¹ãã«ã身ã«ã€ããããšãã§ããŸãã
ãŸãšã
ãã®èšäºãéããŠãSQLã®åŠç¿æ¹æ³ãé²ãæ¹ãããããã®åŠç¿ãµã€ããªã©ãå€ãã®æ å ±ãæäŸããŸãããããããæã倧åãªã®ã¯ãåŠç¿ã®æ ã楜ããããšã§ãã以äžã§ã¯ããã®ããã®ãã€ã³ããããã€ã玹ä»ããŸãã
SQLåŠç¿ã®æ ã楜ããããã«
SQLã®åŠç¿ã¯ãæã«ã¯é£ããæããããšãããããšæããŸãããããã楜ããããšãå¿ããã«ãèªåã®ããŒã¹ã§åŠç¿ãé²ããããšã倧åã§ãã以äžã¯ãåŠç¿ã®æ ã楜ããããã®ãã€ã³ãã§ãã
ãŸããèªåã®ç®æšãæ確ã«ããããšã§ããäœã®ããã«SQLãåŠã¶ã®ãããã®ç®çãæ確ã«æã€ããšã§ãåŠç¿ã®ã¢ãããŒã·ã§ã³ãä¿ã€ããšãã§ããŸãããŸããå°ããªæåäœéšãç©ã¿éããããšã§ãåŠç¿ã®æ¥œãããæããããšãã§ããŸãã
次ã«ãä»ã®åŠç¿è ãšã®äº€æµã倧åã«ããããšã§ãããªã³ã©ã€ã³ã®ã³ãã¥ããã£ãå匷äŒãªã©ã§ãä»ã®åŠç¿è ãšæ å ±ã亀æããããšã§ãæ°ããç¥èãèŠç¹ãåŸãããšãã§ããŸãããŸããåŠç¿ã®é²æãæ©ã¿ãå ±æããããšã§ãå±ãŸãåãããšãã§ããŸãã
æåŸã«ãå®æçãªæ¯ãè¿ããè¡ãããšã§ããåŠç¿ãé²ããäžã§ãã©ã®éšåãç解ã§ããã®ããã©ã®éšåããŸã é£ããã®ããæ¯ãè¿ãããšã§ã次ã®ã¹ããããæ確ã«ããããšãã§ããŸãã
ãã®ããã«ãåŠç¿ã®æ ã楜ããããã®ãã€ã³ãã¯å€ããããŸãããæã倧åãªã®ã¯ãèªåèªèº«ã®ããŒã¹ã§åŠç¿ãé²ããããšã§ããçŠããã楜ããã§åŠç¿ãé²ããŠãããŸãããã