1. ã¯ããã«
ããŒã¿ã¯æã ã®ç掻ã®ããããåŽé¢ãèŠå®ãããã®ã§ããããã®ããŒã¿ãäžæã«æ±ãèœåã¯21äžçŽã®ç掻ã«ãããŠéåžžã«éèŠãªã¹ãã«ãšãªã£ãŠããŸããç¹ã«ããŒã¿ã®ç®¡çãšåŠçã«é¢ããŠäž»å°çãªåœ¹å²ãæããSQLã®éèŠæ§ã¯å¹Žã é«ãŸã£ãŠããŸãã
ãã®èšäºã§ã¯ãSQLç°å¢æ§ç¯ã®ã¹ããããã€ã¹ãããã¬ã€ããæäŸããŸããSQLãšã¯äœãããªãéèŠãªã®ããããã䜿ã£ãŠäœãã§ããã®ãã«ã€ããŠåŠã³ãæçµçã«ã¯èªåèªèº«ã§SQLç°å¢ãèšå®ããããšãã§ããããã«ãªãããšããã®ã¬ã€ãã®ç®çã§ãã
ãŸããæé ã ãã§ãªããSQLã䜿ã£ãå®éã®ããŒã¿æäœã®åºæ¬ãã«ããŒããèšå®ãå®äºããåŸã®åºæ¬çãªæäœãåŠã¹ãããã«ããŸãã
ãã®ã¬ã€ãã¯åå¿è 察象ã«äœæãããŠããã誰ã§ãç解ããããããã«è©³çŽ°ãªèª¬æãšãšãã«ç°¡åã«ããäžé£ã®æé ã§é²ããŠãããŸãã
ããã§ã¯ãäžç·ã«æ°ããªã¹ãã«ãåŠã³ãããŒã¿ã®åã解ãæŸã¡ãŸãããã
2. SQLãšã¯äœãïŒ
SQLãããã¯Structured Query LanguageïŒã¹ãã©ã¯ãã£ãŒã ã¯ãšãª ã©ã³ã²ãŒãžïŒã®ç¥ç§°ã§ãããŒã¿ããŒã¹ã«æ å ±ãåãåããããæäœããããããšãã«çšããããã°ã©ãã³ã°èšèªã®äžã€ã§ããç¹åŸŽçãªã®ã¯ããã®çŽæçãªåœä»€äœç³»ã§ã人éãèªç¶ã«ç解ããããè¡šçŸæ³ãå€ã䜿ãããŠããç¹ã§ããããã«ãããæ å ±ãååŸãæŽæ°ãåé€ãããªã©ã®æäœãç°¡æã«ããããå¹ççã«è¡ãããšãå¯èœãšãªããäžçäžã®å€ãã®ããŒã¿ããŒã¹ç®¡çã·ã¹ãã ã§æ¡çšãããŠããŸãã
2.1 SQLã®æŽå²ãšãã®éèŠæ§
SQLã®æŽå²ã¯1970幎代ã«é¡ããŸããã¢ã¡ãªã«ã®æ å ±æè¡äŒæ¥IBMãç 究éçºãããSEQUELãããã®èµ·æºãšãããŠããŸãããã®åŸãæ§ã ãªæ¹è¯ãçµãŠçŸåšã®SQLã«è³ãããã§ããããã®éã«ããã®éèŠæ§ã¯å¢ããŠãããŸããããªããªããããŒã¿ã®äŸ¡å€ã絶倧ã§ããçŸä»£ç€ŸäŒã«ãããŠãããŒã¿ãå¹æçã«æäœãã掻çšããããã®ããŒã«ãšããŠãSQLã¯æ¥µããŠæå¹ã ããã§ããçŸåšã§ã¯ãããŒã¿ããŒã¹ã·ã¹ãã ãåãããæ å ±ã®åãæ±ãã«ãããŠæ±çšçã«äœ¿çšãããŠããã®ãSQLãªã®ã§ãã
2.2 SQLã䜿çšããäž»ãªç®ç
ãã®äœ¿ãéãšããŠã¯ãæ å ±ã®ååŸïŒSELECTæïŒãæ å ±ã®æŽæ°ïŒUPDATEæïŒãæ°ããªæ å ±ã®è¿œå ïŒINSERTæïŒãæ å ±ã®åé€ïŒDELETEæïŒãªã©ããããŸãããããã®åºæ¬çãªæäœãéããŠãå©çšè ã¯å¿ èŠãªæ å ±ãè¿ éã«åŒãåºãããšãå¯èœãšãªããŸããããã«ãè€éãªããŒã¿é¢ä¿ãæŽçããããããç¬æã«è§£æããããšã§ãããžãã¹ã®ææ決å®ããã質ã®é«ããã®ã«ãããªã©ãSQLã®æŽ»çšã¯ç¡é倧ã§ãã
2.3 SQLã®åºæ¬çãªã³ãã³ã
ç解ãæ·±ããããã«ãåºæ¬çãªSQLã³ãã³ãã«ã€ããŠã觊ããŠãããããšæããŸãã ãŸããSELECTæã§ããããã¯ãããŒã¿ããŒã¹ããç¹å®ã®ããŒã¿ãååŸããéã«äœ¿çšããŸãã äŸãã°ããSELECT * FROM Employeesãã®ããã«èšè¿°ãããšããEmployeesãããŒãã«ã«ååšããå šãŠã®ããŒã¿ãååŸããŸãã 次ã«ãINSERTæã¯æ°ããªããŒã¿ãããŒã¿ããŒã¹ã«è¿œå ããéã«äœ¿çšããŸãã ãINSERT INTO Employees (Name, Age) VALUES ('Taro', 22)ããšããã³ãã³ãã¯ããEmployeesãããŒãã«ã«æ°ããªåŸæ¥å¡ã®ããŒã¿ïŒååã¯'Taro'ã幎霢ã¯22æ³ïŒãè¿œå ãããã®ã§ãã ãããŠãUPDATEæã¯æ¢åã®ããŒã¿ãæŽæ°ããããã«äœ¿ããŸãã äŸãã°ããUPDATE Employees SET Age = 23 WHERE Name = 'Taro'ããšããã³ãã³ãã¯ããEmployeesãããŒãã«ã«ååšããååã'Taro'ã®åŸæ¥å¡ã®å¹Žéœ¢ã23æ³ã«æŽæ°ãããã®ã§ãã æåŸã«ãDELETEæã¯ããŒã¿ããŒã¹ããããŒã¿ãåé€ããŸãã ãDELETE FROM Employees WHERE Name = 'Taro'ããšããã³ãã³ãã¯ããEmployeesãããŒãã«ããååã'Taro'ã®åŸæ¥å¡ã®ããŒã¿ãåé€ããŸãã ãããã¯ãããŸã§åºæ¬çãªã³ãã³ãã§ãããããã«è€éãªããŒã¿ã®æäœãè¡ãããã«ã¯ãJOINãWHEREãGROUP BYãªã©ã®æ©èœãé§äœ¿ããå¿ èŠããããŸãã
3. SQLç°å¢æ§ç¯ãããããã®åæç¥è
SQLç°å¢ãæ§ç¯ããŠãããŒã¿æäœãè¡ãåã«å¿ èŠãªåºæ¬çãªç¥èãç解ããŸããããããã¯ããŒã¿ããŒã¹ã®åºæ¬æŠå¿µãäž»èŠãªããŒã¿ããŒã¹ã®çš®é¡ããããŠããŒã¿ããŒã¹ãšSQLã®é¢ä¿æ§ã§ãã
3.1 ããŒã¿ããŒã¹ãšã¯äœãïŒ
ããŒã¿ããŒã¹ãšã¯ãæ å ±ãå¹ççã«ç®¡çããããã«æŽçã»æ ŒçŽãããããŒã¿ã®éåäœã§ããããã¯ãååæ å ±ã顧客æ å ±ãååŒå±¥æŽã瀟å¡æ å ±ãªã©ãããŸããŸãªããŒã¿ãå®å šã«ä¿ç®¡ããå¿ èŠãªãšãã«è¿ éã«ã¢ã¯ã»ã¹ã§ããæ§é ãæã£ãŠããŸããããŒã¿ããŒã¹ã䜿çšããããšã§ã倧éã®ããŒã¿ãè¿ éã«æ€çŽ¢ã»æŽæ°ã»åé€ã§ããããŒã¿ã®æŽåæ§ãä¿ã€ããšãå¯èœãšãªããŸãã
3.2 ããŒã¿ããŒã¹ã®çš®é¡ãšéã
ããŒã¿ããŒã¹ã«ã¯ããŸããŸãªçš®é¡ããããŸããäž»ã«RDBMSïŒãªã¬ãŒã·ã§ãã«ããŒã¿ããŒã¹ç®¡çã·ã¹ãã ïŒãšNoSQLïŒNot Only SQLïŒã®2ã€ããããŸãã
RDBMSã¯ã1970幎代ã«æå±ãããåŸæ¥åã®ããŒã¿ããŒã¹ã§ãåããŒã¿ããããŒãã«ããšåŒã°ããè¡ãšåã®è¡šåœ¢åŒã§ç®¡çããŸããäžæ¹ãNoSQLã¯ã倧éããŒã¿ã®é«éåŠçãåæ£åŠçãç®çãšããæ°ãã圢åŒã®ããŒã¿ããŒã¹ã§ãJSON圢åŒãããŒããªã¥ãŒåœ¢åŒãªã©ã®éæ§é åããŒã¿ã管çããŸãã
ããããã®ããŒã¿ããŒã¹ã®éžæè¢ã¯ãã¢ããªã±ãŒã·ã§ã³ã®çš®é¡ãèŠä»¶ãããŒã¿ã®çš®é¡ã«ãã£ãŠæ±ºãŸããŸãã
3.3 ããŒã¿ããŒã¹ãšSQLã®é¢ä¿æ§
SQLãšã¯ãããŒã¿ããŒã¹ã®ããŒã¿ãå¹ççã«æäœããããã®ããã°ã©ãã³ã°èšèªã§ããããŒã¿ããŒã¹ãå¹ççã«æäœããã«ã¯ãã»ãšãã©ã®RDBMSã§SQLã䜿çšãããŠããŸãã
å ·äœçã«ã¯ãSQLã䜿çšããŠããŒãã«ããããŒã¿ãéžæãããïŒSELECTïŒãæ°ããªããŒã¿ãæ¿å ¥ãããïŒINSERTïŒãæ¢åã®ããŒã¿ãæŽæ°ïŒUPDATEïŒããããäžèŠãªããŒã¿ãåé€ïŒDELETEïŒãããããŸãããããã®æäœãéããŠããŒã¿ãè¿ éãã€å¹ççã«ç®¡çãããŸãã
4. SQLç°å¢æ§ç¯ã«å¿ èŠãªããŒã«
SQLç°å¢æ§ç¯ã«ã¯ãäž»ã«3ã€ã®ããŒã«ãå¿ èŠã§ãããããã¯ããŒã¿ããŒã¹ç®¡çã·ã¹ãã ïŒDBMSïŒãSQLã¯ã©ã€ã¢ã³ãããŒã«ããããŠããŒã¿ã¢ããªã³ã°ããŒã«ã§ãããããã®å·¥å ·ã¯ããããç°ãªã圹å²ãæãããŸãããå šãŠã¯ããŒã¿ããŒã¹ã®æäœãšç®¡çã«äžå¯æ¬ ãªãã®ãšãªã£ãŠããŸãããã®ã»ã¯ã·ã§ã³ã§ã¯ããããã®ããŒã«ã«ã€ããŠè©³ãã解説ããŸãã
4.1 ããŒã¿ããŒã¹ç®¡çã·ã¹ãã ïŒDBMSïŒ
ãŸãå§ãã«ãããŒã¿ããŒã¹ç®¡çã·ã¹ãã (DBMS) ã«ã€ããŠã§ããDBMSã¯SQLã䜿çšããŠããŒã¿ããŒã¹ãæäœããããã®ãœãããŠã§ã¢ã§ãããŒã¿ã®ç®¡çãæäœã®ããã«å¿ èŠãªæ©èœãæäŸããŠããŸããäŸãã°ãããŒã¿ã®è¿œå ãæŽæ°ãåé€ãšãã£ãåºæ¬çãªæäœãããããŒã¿æ€çŽ¢ãéèšãããã«ã¯ãã©ã³ã¶ã¯ã·ã§ã³ç®¡çãªã©ãããŒã¿ããŒã¹æäœã«é¢ããæ§ã ãªæ©èœãå«ãŸããŠããŸãããŸããDBMSã«ã¯ããŸããŸãªçš®é¡ããããMySQLãOracleãPostgreSQLãªã©ãåºãå©çšãããŠããŸãã
4.2 SQLã¯ã©ã€ã¢ã³ãããŒã«
次ã«ãSQLã¯ã©ã€ã¢ã³ãããŒã«ã«ã€ããŠè§£èª¬ããŸããSQLã¯ã©ã€ã¢ã³ãããŒã«ã¯DBMSãšéä¿¡ããDBMSã«åœä»€ãåºãããã®ã€ã³ã¿ãŒãã§ãŒã¹ã§ããSQLã¯ã©ã€ã¢ã³ãããŒã«ãæäœããããšã§ããŒã¿ããŒã¹ãžã®åãåããïŒSQLã¯ãšãªïŒãéä¿¡ããããçµæã衚瀺ãããããããšãå¯èœã§ããSQLã¯ã©ã€ã¢ã³ãããŒã«ã«ã¯ãphpMyAdminãTablePlusãªã©ããããŸãã
4.3 ããŒã¿ã¢ããªã³ã°ããŒã«
æåŸã«ãããŒã¿ã¢ããªã³ã°ããŒã«ã«ã€ããŠèª¬æããŸããããŒã¿ã¢ããªã³ã°ããŒã«ã¯ãããŒã¿ããŒã¹ã®èšèšãæ¯æŽããããã®ããŒã«ã§ããããšã³ãã£ãã£ãå±æ§ããªã¬ãŒã·ã§ã³ã·ãããèŠèŠçã«æç»ããããšãå¯èœã§ããããã«ããããŒã¿ããŒã¹èšèšè ã¯ããŒã¿ããŒã¹ã®æ çµã¿ãç解ãããããªãããŸãèšèšã®éçšã§èµ·ããå¯èœæ§ã®ããåé¡ãäºåã«ç¹å®ããããšãã§ããŸããããŒã¿ã¢ããªã³ã°ããŒã«ãšããŠã¯ãER/StudioãSparx Systems Enterprise Architectãªã©ããããŸãã
5. SQLç°å¢æ§ç¯ã®æé
SQLç°å¢ã®æ§ç¯ã¯ããŸãã¯åºæ¬çãªç¥èã身ã«ã€ãããšããããå§ããŸãããããã玹ä»ããæé ã«åŸã£ãŠé²ããããšã§ãèªåã ãã®SQLç°å¢ãå¹ççã«æ§ç¯ããããšãã§ããŸãã
5.1 ããŒã¿ããŒã¹ç®¡çã·ã¹ãã ïŒDBMSïŒã®ã€ã³ã¹ããŒã«
SQLç°å¢ãæ§ç¯ããæåã®ã¹ãããã¯ããŒã¿ããŒã¹ç®¡çã·ã¹ãã (DBMS)ã®ã€ã³ã¹ããŒã«ã§ããDBMSã¯ããŠãŒã¶ãŒãããŒã¿ããŒã¹ãšå¯Ÿè©±ããã¡ã€ã³ããŒã«ã§ãããã®äžã§ã MySQLã PostgreSQLãªã©ãåºã䜿ãããŠããŸããDBMSã®ã€ã³ã¹ããŒã«ã¯ãå ¬åŒãµã€ãããããŠã³ããŒãããã€ã³ã¹ããŒã©ãŒã䜿çšããŠè¡ããŸããããŠã³ããŒããå®äºããããã€ã³ã¹ããŒã©ãŒãå®è¡ããç»é¢ã®æ瀺ã«åŸã£ãŠã€ã³ã¹ããŒã«ãé²ããŠãã ããã
5.2 SQLã¯ã©ã€ã¢ã³ãããŒã«ã®ã€ã³ã¹ããŒã«
次ã«ãSQLã¯ã©ã€ã¢ã³ãããŒã«ãã€ã³ã¹ããŒã«ããŸããããã«ããããŒã¿ããŒã¹ãžã®ãªã¢ãŒãã¢ã¯ã»ã¹ããåçš®ãŠãŒã¶ãŒã€ã³ã¿ãŒãã§ãŒã¹ãå©çšããŠSQLã³ãã³ããå®è¡ããããšãå¯èœãšãªããŸãã SQLã¯ã©ã€ã¢ã³ãããŒã«ãå ¬åŒãµã€ãããããŠã³ããŒãããã€ã³ã¹ããŒã©ãŒãå®è¡ããŸãããã®éãäœãåé¡ããªããã°ããã©ã«ãã®èšå®ããã®ãŸãŸäœ¿çšããå¿ èŠã«å¿ããŠåã ã®é ç®ãã«ã¹ã¿ãã€ãºããŸãã
5.3 ããŒã¿ããŒã¹ãšSQLã¯ã©ã€ã¢ã³ãã®æ¥ç¶
ã€ã³ã¹ããŒã«ãçµãã£ããã次ã«ããŒã¿ããŒã¹ãšSQLã¯ã©ã€ã¢ã³ããæ¥ç¶ããŸããSQLã¯ã©ã€ã¢ã³ãããŒã«ã®äžãã ãæ°èŠæ¥ç¶ã ã ãæ¥ç¶ã®èšå®ã ãªã©ã®ãªãã·ã§ã³ãéžã³ãæ¥ç¶æ å ±ãå ¥åããŸããæ¥ç¶æ å ±ã¯ãDBMSã®ã€ã³ã¹ããŒã«æã«èšå®ãããã®ã䜿çšããŸããããããã®ãã£ãŒã«ãã«é©åãªæ å ±ãå ¥åããããã¹ãæ¥ç¶ãããæ¥ç¶ãã§ãã¯ããªã©ã®ãªãã·ã§ã³ãã¯ãªãã¯ããŠãæ¥ç¶ãæåããã確èªããŸãã
5.4 åæèšå®
æåŸã«ãåæèšå®ãè¡ããŸããããã§ã¯ãããŒã¿ããŒã¹ã®æ§èœãæ倧éã«åŒãåºãããã®èšå®ããæ¥ã ã®äœæ¥ãã¹ã ãŒãºã«é²ããããã®èšå®ãªã©ãè¡ããŸããäŸãã°ãããŒã¿ããŒã¹ãèªåèµ·åããèšå®ãããã°ã®ä¿åå Žæãã¡ã¢ãªã®æ倧䜿çšéãªã©ãèšå®ããŸãããããã®èšå®ã¯äžåºŠèšå®ãããšãåŸããã§ãå€æŽããããšãå¯èœãªã®ã§ãå®å¿ãã ããã
以äžããåºæ¬çãªSQLç°å¢ã®æ§ç¯æé ã§ããæ £ããªãéšåãå€ããããããŸããããäžã€ãã€çå®ã«é²ããŠããããšã§ãå®æãŸã§ã®éã®ããããé ãã¯ãããŸããã
6. SQLç°å¢æ§ç¯ã®ãã©ãã«ã·ã¥ãŒãã£ã³ã°
SQLç°å¢æ§ç¯ã®éçšã§ã¯ãåå¿è ã®æ¹ã¯åé¡ã«ééããå¯èœæ§ããããŸããããããå¿é ã¯ãããŸããã以äžã§ãããããåé¡ãšãããã«å¯Ÿãã解決çã«ã€ããŠè©³ãã説æããŸãã
6.1 ãããããã©ãã«ãšãã®å¯Ÿç
æåã«ããœãããŠã§ã¢ã®ã€ã³ã¹ããŒã«ãäžæããããªããšããåé¡ããããŸãããã®åé¡ã¯ãOSãšãœãããŠã§ã¢ã®äºææ§ãŸãã¯ãå¿ èŠãªäŸåé¢ä¿ã®ãœãããŠã§ã¢ãäžè¶³ããŠããããšãåå ãšãªããŸãã察çãšããŠãOSãšãœãããŠã§ã¢ã®ããŒãžã§ã³ãåã£ãŠããããšã確èªããããå¿ èŠãªãœãããŠã§ã¢ãå šãŠã€ã³ã¹ããŒã«ãããŠããããšã確èªããŸãã
次ã«ãããŒã¿ããŒã¹ãšã¯ã©ã€ã¢ã³ãããŒã«ã®æ¥ç¶ãããŸããããªããšããåé¡ã§ããããã¯ãæ¥ç¶èšå®ã®ãã¹ããããã¯ãŒã¯ã®åé¡ã«ãã£ãŠèµ·ãããŸããããã«å¯Ÿãã察çãšããŠãæ¥ç¶èšå®ãééã£ãŠãªãã確èªãããããããã¯ãŒã¯ã®ç¶æ ããã§ãã¯ããŸãã
6.2 ãšã©ãŒã¡ãã»ãŒãžãšãã®å¯Ÿå¿ç
ãšã©ãŒã¡ãã»ãŒãžã¯åé¡ã®çºçæºãç¹å®ããã®ã«åœ¹ç«ã¡ãŸããããããåå¿è ã«ãšã£ãŠã¯ããšã©ãŒã¡ãã»ãŒãžã¯ç解ããã®ãé£ãããããããŸããã
äŸãã°ã“‘mysql’ is not recognized as an internal or external command, operable program or batch file.”ãšãããšã©ãŒã¡ãã»ãŒãžã衚瀺ãããå Žåãããã¯ç°å¢å€æ°ã®PATHãé©åã«èšå®ãããŠããªãããšã瀺ããŠããŸãããã®ãããªå Žåã¯ãMySQLãã€ã³ã¹ããŒã«ãããŠãããã£ã¬ã¯ããªãç°å¢å€æ°PATHã«è¿œå ããŸãã
ãã“Unknown database ‘test'”ãšãããšã©ãŒã¡ãã»ãŒãžã衚瀺ãããå Žåããã㯑test’ãšããååã®ããŒã¿ããŒã¹ãååšããªããšããæå³ã§ãããã®ãããªå Žåã¯ãååšããããŒã¿ããŒã¹ã®ååã«ééãããªãã確èªããŸãã
ãããã®äŸã¯äžéšã§ããããšã©ãŒã¡ãã»ãŒãžã¯åé¡è§£æ±ºã®ããã®éèŠãªãã³ãã§ããã¡ãã»ãŒãžã®æå³ãåãããªãå Žåã¯ãã€ã³ã¿ãŒãããã§ãã®ãšã©ãŒã¡ãã»ãŒãžãæ€çŽ¢ãããšãããå ·äœçãªè§£æ±ºçãèŠã€ããããšãã§ããŸãã
7. SQLç°å¢æ§ç¯åŸã®åºæ¬æäœ
ããã§ã¯ãSQLç°å¢æ§ç¯åŸã®åºæ¬æäœã«ã€ããŠèª¬æããŸããç¹ã«ãããŒã¿ããŒã¹ã®äœæãããŒãã«ã®äœæããããŠããŒã¿ã®æ¿å ¥ãæŽæ°ãåé€ã«ã€ããŠã¿ãŠãããŸãããã
7.1 ããŒã¿ããŒã¹ã®äœæ
ç°å¢æ§ç¯ãå®äºããåŸãæåã«è¡ãããšã¯ãããŒã¿ããŒã¹ã®äœæãã§ããããã§äœæããããŒã¿ããŒã¹ã¯ãåŸã«äœæããããŒãã«ãããŒã¿ã®æ ŒçŽå ŽæãšãªããŸãã
CREATE DATABASE database_name;
äžèšã®SQLã³ãã³ããå®è¡ããããšã§ãæå®ããååã®ããŒã¿ããŒã¹ãäœæãããŸããã³ãã³ãäžã®”database_name”ã¯ãäœæãããããŒã¿ããŒã¹åã«çœ®ãæããŠäœ¿çšããŸãã
7.2 ããŒãã«ã®äœæ
ããŒã¿ããŒã¹ã®äœæãå®äºãããã次ã«ãããŒãã«ã®äœæããè¡ããŸããããŒãã«ã¯ããŒã¿ããŒã¹å ã«ããæ å ±ã®åéäœã§ãããããã®ããŒã¿ãè¡ãšåã®åœ¢åŒã§è¡šçŸããŸãã
CREATE TABLE table_name ( column1 datatype, column2 datatype, column3 datatype, ⊠);
以äžã®ã³ãã³ããå®è¡ããããšã§ã”table_name”ãšããååã®ããŒãã«ãäœæããããã®äžã«”column1″ã”column2″ã”column3″ãšãã£ãã«ã©ã ã眮ãããŸãããŸããåã«ã©ã ã«ã¯å¥é”datatype”ã§ããŒã¿ã®çš®é¡ãå®çŸ©ããå¿ èŠããããŸãã
7.3 ããŒã¿ã®æ¿å ¥ãæŽæ°ãåé€
ããŒãã«ãäœæãããåŸã¯ãããŒã¿ã®ãæ¿å ¥ãããæŽæ°ãããåé€ãæäœãè¡ãããšãã§ããŸãããããã®æäœã¯ãããŒã¿ããŒã¹å ã®æ å ±ã管çããããã«é »ç¹ã«äœ¿çšãããã³ãã³ãã§ãã
ãŸããããŒã¿ã®æ¿å ¥ã¯ä»¥äžã®SQLã³ãã³ãã§è¡ããŸãã
INSERT INTO table_name (column1, column2, column3, âŠ) VALUES (value1, value2, value3, âŠ);
äžèšã³ãã³ããå®è¡ãããšã”table_name”ã®ããŒãã«ã«ãæå®ããã«ã©ã ”column1″ã”column2″ã”column3″ãž”value1″ã”value2″ã”value3″ãšããããŒã¿ãæ¿å ¥ãããŸãã
次ã«ãããŒã¿ã®æŽæ°ã¯ä»¥äžã®ããã«è¡ããŸãã
UPDATE table_name SET column1 = value1, column2 = value2, ⊠WHERE condition;
äžèšã³ãã³ãã§ã”table_name”ã®ããŒãã«å 㧔WHERE condition”ã«äžèŽããè¡ã®”column1″ã”column2″ã®ããŒã¿ããããã”value1″ã”value2″ã«æŽæ°ããŸãã
æåŸã«ãããŒã¿ã®åé€ã¯ä»¥äžã®SQLã³ãã³ãã§è¡ããŸãã
DELETE FROM table_name WHERE condition;
äžèšã³ãã³ããå®è¡ãããšã”table_name”ã®ããŒãã«ãã”WHERE condition”ã«äžèŽããè¡ãåé€ãããŸãããã®æäœã¯åãæ¶ããå¹ããªãããã確èªäœæ¥ãå¿ èŠã§ãã
ãããã®åºæ¬æäœãç解ãã䜿ãããªãããšãSQLç°å¢ã§ã®ããŒã¿ç®¡çã«ãããŠéèŠãšãªããŸãã
8. å®è·µæŒç¿
ããã§ã¯ããããŸã§åŠãã SQLç°å¢æ§ç¯ã®ç¥èã掻ãããå®éã®ããŒã¿ãçšããã¯ãšãªã®äœæãšæ§ã ãªSQLã³ãã³ãã®å®è¡ã«ã€ããŠå®è·µçã«åŠãã§ãããŸããããããã«ãããçè«çãªç¥èã ãã§ãªããå®éã®æäœæããããŒã¿ããŒã¹ãšå¯Ÿè©±ããæèŠãæŽãããšãã§ããŸãã
8.1 å®éã®ããŒã¿ãçšããã¯ãšãªã®äœæ
ãŸãã¯ãå®éã®ããŒã¿ãçšããŠã¯ãšãªãäœæããŸããããäŸãã°ãååã®å£²äžããŒã¿ã管çããããŒãã«ããããšããŸãããã®ããŒãã«ã«ã¯ãååIDã販売æ¥ã販売æ°éãè²©å£²äŸ¡æ Œãšãã£ãæ å ±ãæ ŒçŽãããŠãããšããŸããããããã§ãã2020幎ã®åååã®å£²äžç·é¡ãèšç®ãããã¯ãšãªãäœæãããšã以äžã®ããã«ãªããŸã:
SELECT product_id, SUM(sales_price*sales_quantity) as total_sales FROM sales WHERE sales_date >= '2020-01-01' AND sales_date <= '2020-12-31' GROUP BY product_id;
ãã®ã¯ãšãªã¯ãåååIDããšã«ãè²©å£²äŸ¡æ Œãšè²©å£²æ°éãæãåããããã®ãåèšããããã売äžç·é¡ãšããŠè¡šç€ºããŠããŸãããŸããWHEREå¥ã§ãæå®ããæé(2020幎)ã®ããŒã¿ã ããæœåºããŠããŸãã
8.2 æ§ã ãªSQLã³ãã³ãã®å®è¡
次ã«ãæ§ã ãªSQLã³ãã³ããå®è¡ããŠã¿ãŸããããããã§ã¯ãSELECT ã³ãã³ãã䜿ã£ãããŒã¿ã®æœåºããUPDATEã³ãã³ããçšããããŒã¿ã®æŽæ°ããããŠæ°ããªè¡ãè¿œå ãã INSERTã³ãã³ããšãã£ãåºæ¬çãªã³ãã³ããé ãè¿œã£ãŠå®è¡ããŠã¿ãŸãããã
ãŸããããååã®æ å ±ãæœåºãããå Žåã次ã®ãããªã³ãã³ãã«ãªããŸã:
SELECT * FROM products WHERE product_id = 100;
次ã«ãããååã®äŸ¡æ ŒãæŽæ°ãããå Žåã次ã®ãããªã³ãã³ãã«ãªããŸã:
UPDATE products SET price = 2000 WHERE product_id = 100;
æåŸã«ãæ°ããªååããŒã¿ãè¿œå ãããå Žåã次ã®ãããªã³ãã³ãã«ãªããŸã:
INSERT INTO products (product_id, product_name, price) VALUES (101, 'æ°åå', 3000);
以äžã®ããã«ãSQLã³ãã³ãã䜿ãããšã§ãããŒã¿ããŒã¹ã«å¯Ÿããæ§ã ãªæäœãå¯èœã«ãªããŸããå®è·µæŒç¿ãéããŠããããŸã§åŠãã ç¥èã掻ãããå®éã«æãåãããŠã¿ãŸãããã
9. ãŸãšã
æ¬èšäºã§ã¯ãåããŠSQLç°å¢ãæ§ç¯ããæ¹ã ããã¹ã ãŒãºãã€å¹ççã«èªèº«ã®SQLç°å¢ãæ§ç¯ã§ãããããåèã«ãªãæ å ±ãæäŸããŸããã
å§ãã«ãSQLã®åºæ¬çãªæŠå¿µãç®çãã³ãã³ãã«ã€ããŠè§£èª¬ããŸãããããã¯ãSQLç°å¢æ§ç¯ã®åæãšãªãéèŠãªç¥èã§ãã次ã«ãSQLç°å¢æ§ç¯ã«å¿ èŠãªããŒã«ã®å°å ¥æ¹æ³ã瀺ããŸãããDBMSãSQLã¯ã©ã€ã¢ã³ãããŒã«ãããŒã¿ã¢ããªã³ã°ããŒã«ãªã©ãåããŒã«ã®åœ¹å²ãšéžå®æ¹æ³ã«ã€ããŠèª¬æããŸããã
ãã®åŸãå ·äœçãªSQLç°å¢æ§ç¯ã®æé ã«ã€ããŠè©³çŽ°ã«è§£èª¬ããŸãããDBMSã®ã€ã³ã¹ããŒã«ãããSQLã¯ã©ã€ã¢ã³ãããŒã«ã®ã€ã³ã¹ããŒã«ãããŒã¿ããŒã¹ãšSQLã¯ã©ã€ã¢ã³ãã®æ¥ç¶ãåæèšå®ãŸã§ã®å šãŠã®ããã»ã¹ãã«ããŒãããŠããŸãã
ãããŠãæ§ç¯éçšã§ééå¯èœãªãã©ãã«ã·ã¥ãŒãã£ã³ã°ã«ã€ããŠã解説ããŸãããå ·äœçãªãšã©ãŒã¡ãã»ãŒãžãšãã®å¯Ÿå¿çã瀺ãããšã§ãæ§ç¯ã®éã®åé¡ãè¿ éã«è§£æ±ºããæå©ããšãªãããšãæã¿ãŸãã
æåŸã«ãSQLç°å¢æ§ç¯åŸã®åææäœã«ã€ããŠèª¬æããŸãããããŒã¿ããŒã¹ãããŒãã«ã®äœæãããŒã¿ã®æ¿å ¥ãæŽæ°ãåé€ãªã©ãSQLã®åºæ¬æäœãç解ã§ããããã«ãªããŸããã
æ¬èšäºããããªãã®SQLç°å¢æ§ç¯ã®äžå©ãšãªãããšãå¿ããé¡ã£ãŠãããŸããåŒãç¶ããSQLã®åŠç¿ã«å±ã¿ãããŒã¿åæã®äžçãæ¢æ±ããŠãããŸãããã