ã¯ããã«
æè¡æåãæ¥éãªããŒã¹ã§é²åãç¶ããããŒã¿ã®éèŠæ§ãé«ãŸãæšä»ãããŒã¿åæã¹ãã«ã¯éåžžã«äŸ¡å€ããã¹ãã«ãšãªããŸããããã®äžã§ãããŒã¿ããŒã¹ãçŽæ¥æ±ãããã®èšèªãSQLã¯ããŒã¿ãå¹æçã«ææ¡ãæäœããããã®å¿ é ã®ããŒã«ãšãªã£ãŠããŸãã
SQLãšã¯äœã
SQLïŒStructured Query LanguageïŒã¯ããæ§é åã¯ãšãªèšèªããšèš³ãããããšãå€ãèšèªã§ããããã¯ãäž»ã«RDBMSïŒRelational Database Management SystemïŒããªãã¡ããªã¬ãŒã·ã§ãã«ããŒã¿ããŒã¹ç®¡çã·ã¹ãã ãšããç¹å®ã®ã¿ã€ãã®ããŒã¿ããŒã¹ãšå¯Ÿè©±ããããã®éå ·ãšãªããŸãã
ãªã¬ãŒã·ã§ãã«ããŒã¿ããŒã¹ã¯ãããŒã¿ããããŒãã«ãã®åœ¢åŒã§ä¿åããããšãç¹åŸŽãšããŠããŸãã
SQLã¯ãããŒãã«ã«ããŒã¿ãè¿œå ãæŽæ°ãåé€ããŸãã¯ç¹å®ã®ãã¿ãŒã³ã«åã£ãããŒã¿ãæœåºãããªã©ã®äžé£ã®æäœãå¯èœã«ããŸãã
ãªãSQLãåŠã¶ã¹ãã
ããŒã¿ã¯çŸä»£ã®ããžãã¹ã«ãããŠæ°ããªãç³æ²¹ãã§ãããšèšãããŸãããã®ãããªéèŠãªããŒã¿ã«ã¢ã¯ã»ã¹ããå©çšå¯èœãªæ å ±ã«å€æããããã«ã¯ãSQLã®ãããªããŒã¿æäœã¹ãã«ãå¿ èŠãšãªã£ãŠããŸãã
ããã«ãããŒã¿ãµã€ãšã³ã¹ãããŒã¿åæã®äžçã«é²åºããããšèããŠããæ¹ã«ãšã£ãŠããSQLã¯å¿ é ã®ç¥èãšãªããŸããããã¯ãæ©æ¢°åŠç¿ã¢ãã«ãæ§ç¯ããããã®ããŒã¿ã»ãããæ§ç¯ããããåæçµæãããŒã¿ããŒã¹ã«ä¿åãããããããã®èšèªãšãªããŸãã
æãéèŠãªããšã¯ãSQLã¯æ¯èŒçç°¡åã«åŠã¹ãèšèªã§ãããšããããšã§ãã ããã°ã©ãã³ã°ã®åºç€ç¥èããªãæ¹ã§ããçæéã§åºæ¬çãªSQLæäœããã¹ã¿ãŒããèªåèªèº«ã®ããŒã¿ã掻çšããããšãå¯èœã«ãªããŸãã
SQLãžã®ç¬¬äžæ©
ããã°ã©ãã³ã°ã®äžçã«å ¥ãåã«å¿ èŠãªç¥èããããSQLåŠç¿ã®åæç¥èã§ããããã§ã¯ãã®å 容ã«ã€ããŠäžç·ã«èŠãŠãããŸãããã
SQLãåŠã¶ããã®åæç¥è
SQLãåŠã¶ããã«å¿ èŠãªåæç¥èãšã¯äœã§ããããïŒãŸãçåã«æããããããŸããããå®ã¯ç¹å¥ãªåæç¥èã¯ããŸãå¿ èŠãããŸããããã®çç±ã®äžã€ãšããŠãSQLã¯éæç¶ãåèšèªã§ãããšããç¹ãæããããŸããã€ãŸããã©ã®ãããªæé ã§åé¡ã解ãããæ瀺ããã®ã§ã¯ãªããäœãã»ããã®ããæå®ããã ãã§ããã®çµæãåŸãããšãã§ããŸãã
ãã¡ãããããã°ã©ãã³ã°ã®åºç€ãã¢ã«ãŽãªãºã ã®ç解ãªã©ããããšç解ãé²ã¿ããããªãã¯ããŸããããããããªããŠãååã«SQLãåŠã¹ãŸããåŠç¿ãé²ããäžã§åºæ¬çãªããŒã¿ããŒã¹ã®æŠå¿µãããŒãã«ã®æ§é ãããã«ã¯é¢ä¿æ§ãªã©ã«ã€ããŠç解ããŠããããšãæ±ããããã§ãããã
SQLã®ç°å¢æ§ç¯ãšã¯
次ã«ãSQLã®åŠç¿ãå§ããããã«ã¯ç°å¢æ§ç¯ãå¿ èŠã«ãªããŸããç°å¢æ§ç¯ãšã¯ãSQLãå®è¡ããããã®ããŒã¿ããŒã¹ãèªåã®ãã·ã³ã«æ§ç¯ããããšãæããŸããæ§ã ãªããŒã¿ããŒã¹ç®¡çã·ã¹ãã (DBMS)ããããŸããããã®äžã§ãååŠè ã«ãããããªã®ããMySQLãSQLiteãPostgreSQLã§ãã
ãããã®DBMSã¯ãªãŒãã³ãœãŒã¹ã§ãããèªç±ã«å©çšããããšãã§ããŸãããŸããããããå ¬åŒãµã€ãããããŠã³ããŒãããããšãå¯èœã§ãã€ã³ã¹ããŒã«ã¬ã€ããçšæãããŠããŸãã®ã§ãããã«åŸã£ãŠã€ã³ã¹ããŒã«ãé²ããããšã§ç°å¢æ§ç¯ãé²ããããšãã§ããŸãã
ãœãããŠã§ã¢ã®ã€ã³ã¹ããŒã«ãçµãã£ããã次ã«SQLãæžãèšãããã®ãšãã£ã¿ãå¿ èŠã«ãªããŸãããšãã£ã¿ã«ã¯å€ãã®çš®é¡ããããŸããããã®äžã§ãååŠè ã«ã¯VSCodeãSublime Textãªã©ãããããã§ããããããå ¬åŒãµã€ãããããŠã³ããŒãããã€ã³ã¹ããŒã«ãè¡ãããšãã§ããŸãã
以äžã®æé ã§SQLã®ç°å¢æ§ç¯ãå®äºããããã§ããªãã¯SQLã®äžçãžäžæ©èžã¿åºãããšãã§ããŸããã次ãžé²ãåã«ãã€ã³ã¹ããŒã«ãããœãããŠã§ã¢ãæ£ããåäœããã確èªããŸãããããããŠãäœãåé¡ãããã°ãã©ãã«ã·ã¥ãŒãã£ã³ã°ãè¡ãããã«ãšã©ãŒã¡ãã»ãŒãžãGoogleã§æ€çŽ¢ããŠã¿ãããšããªã¹ã¹ã¡ããŸãã
SQLã®åºæ¬ææ³
SQLã®åºæ¬ææ³ãšããŠãŸãèŠããªããã°ãªããªãã®ããããŒã¿ãæäœããããã®åºæ¬çãªã³ãã³ãã§ããSELECTæãINSERTæãUPDATEæãDELETEæã§ãããŸãããããã®ã³ãã³ããå ·äœçãªããŒã¿ã«å¯ŸããŠé©çšããããã«ã¯ãWHEREå¥ãçšããããŒã¿ã®ãã£ã«ã¿ãªã³ã°ãéèŠã«ãªããŸãã
SELECTæïŒããŒã¿ãæœåºãã
æåã«çŽ¹ä»ããã®ããSELECTæã§ããSELECTæã¯ããŒã¿ããŒã¹ããç¹å®ã®ããŒã¿ãæœåºããããã®ã³ãã³ãã§ããäŸãã°ããemployeesãããŒãã«ããå šãŠã®ããŒã¿ãååŸããããšãã¯ããSELECT * FROM employees;ããšæžããŸããããã§ãã*ãã¯å šãŠã®åãæå³ããŸããç¹å®ã®åã ããååŸãããå Žåã¯ãã*ãã®ä»£ããã«ååãèšå®ããŸãã
INSERTæïŒããŒã¿ãè¿œå ãã
次ã«ãINSERTæã§ããããŒã¿ããŒã¹ã«æ°ããªããŒã¿ãè¿œå ãããšãã«äœ¿çšããŸããäŸãã°ããemployeesãããŒãã«ã«æ°ããªåŸæ¥å¡ã®ããŒã¿ãè¿œå ããå ŽåããINSERT INTO employees (name, age) VALUES (‘John Doe’, 30);ãã®ããã«æžããŸãã
UPDATEæïŒããŒã¿ãæŽæ°ãã
UPDATEæã¯ãæ¢åã®ããŒã¿ãæŽæ°ããã®ã«äœ¿çšããŸãããemployeesãããŒãã«ã§ãååã’John Doe’ã®åŸæ¥å¡ã®å¹Žéœ¢ã31ã«æŽæ°ããå ŽåããUPDATE employees SET age = 31 WHERE name = ‘John Doe’;ããšæžããŸãã
DELETEæïŒããŒã¿ãåé€ãã
ããŒã¿ãåé€ããéã«ã¯ãDELETEæãçšããããŸããäŸãã°ããemployeesãããŒãã«ããååã’John Doe’ã®ããŒã¿ãåé€ããããšãã«ã¯ããDELETE FROM employees WHERE name = ‘John Doe’;ããšæžãããšã§å®è¡ã§ããŸãã
WHEREå¥ïŒãã£ã«ã¿ãªã³ã°ã«ããç¹å®ã®ããŒã¿ãæäœãã
以äžã®ã³ãã³ããç¹å®ã®ããŒã¿ã«é©çšããããã«ã¯ãWHEREå¥ã®æŽ»çšãéèŠã§ãããemployeesãããŒãã«ãã30æ³ä»¥äžã®åŸæ¥å¡ã®ããŒã¿ã ããæœåºããã«ã¯ããSELECT * FROM employees WHERE age >= 30;ããšæžããŸããåæ§ã«ãç¹å®ã®æ¡ä»¶ã«ãããããããŒã¿ãæŽæ°ãŸãã¯åé€ãããšããWHEREå¥ã䜿çšããŸãã
ãããã®åºæ¬ææ³ã身ã«ã€ãã°ãSQLã§ããŒã¿ãèªç±ã«æäœã§ããããã«ãªããŸããããããããã ãã§ã¯SQLã®ãã¯ãŒãååã«çºæ®ã§ããŸããã次ç¯ã§ã¯ãããé«åºŠãªããŒã¿ã®æäœãå¯èœã«ããSQLã®å¿çšçãªææ³ãåŠã³ãŸãããã
å®è·µïŒSQLã§ããŒã¿åæãå§ããã
ããŠãããããã¯ããå®è·µçãªSQLã®äœ¿çšæ¹æ³ãåŠãã§ãããŸããããå ·äœçã«ã¯ãJOINãGROUP BYãšéèšé¢æ°ããããŠSUBQUERYãšVIEWã«ã€ããŠè§£èª¬ããŸãã
JOINã®æŽ»çšïŒè€æ°ã®ããŒãã«ããããŒã¿ãæœåºãã
SQLã§ããŒã¿ããŒã¹ãæ±ãéã¯ããã°ãã°æ å ±ãè€æ°ã®ããŒãã«ã«åæ£ããŠæ ŒçŽãããŠããŸãããã®ãããçµåïŒJOINïŒãå©çšããŠãè€æ°ã®ããŒãã«ããå¿ èŠãªããŒã¿ãæœåºããæè¡ã¯éåžžã«éèŠã§ãã
SELECT Orders.OrderID, Customers.CustomerName FROM Orders JOIN Customers ON Orders.CustomerID = Customers.CustomerID;
äžèšã®äŸã§ã¯ãOrdersããŒãã«ãšCustomersããŒãã«ãCustomerIDã«ãã£ãŠçµåãããå泚æã«å¯Ÿå¿ãã顧客åãååŸã§ããŸãã
GROUP BYãšéèšé¢æ°ïŒããŒã¿ãéèšãã
次ã«ãããŒã¿ãäžå®ã®æ¡ä»¶ã§ãŸãšããŠéèšããããã®GROUP BYå¥ãšéèšé¢æ°ã«ã€ããŠèŠãŠãããŸããããäŸãã°ãç¹å®ã®é ç®ããšã«å¹³åãåºãå Žåããåã«ããŽãªã«äœä»¶ã®ããŒã¿ãããããæ°ããå Žåãªã©ã«äœ¿çšããŸãã
SELECT Customers.Country, COUNT(Orders.OrderID) FROM Orders JOIN Customers ON Orders.CustomerID = Customers.CustomerID GROUP BY Customers.Country;
ãã®SQLæã§ã¯ãããããã®åœã®é¡§å®¢ãã©ãã ãã®æ³šæããããããåœããšã«éèšããŠããŸãã
SUBQUERYãšVIEWïŒè€éãªããŒã¿æœåº
æåŸã«ãè€éãªããŒã¿æœåºãå¯èœã«ããSUBQUERYãšVIEWã«ã€ããŠåŠã³ãŸããããSUBQUERYã¯äžã€ã®SQLæã®äžã«ããã«SQLæãçµã¿èŸŒãããšã§ãããè€éãªããŒã¿æœåºããããŒã¿ãå å·¥ããããã®åŠçãå®çŸããããã®æ©èœã§ãããããŠVIEWã¯ãµãã¯ãšãªã®çµæãããŒãã«ã®ããã«æ±ããããäœæããä»®æ³çãªããŒãã«ãæå³ããŸãã
SELECT Customers.CustomerName, CountOrders.NumOrders FROM Customers JOIN (SELECT CustomerID, COUNT(OrderID) as NumOrders FROM Orders GROUP BY CustomerID) CountOrders ON Customers.CustomerID = CountOrders.CustomerID;
äžèšã®SQLæã§ã¯ããµãã¯ãšãªãçšããŠå顧客ãè¡ã£ã泚æã®æ°ãéèšãããã®çµæã䜿ã£ãŠé¡§å®¢ããŒãã«ãšçµåããŠããŸããããã«ãã顧客åãšæ³šææ°ãäžç·ã«è¡šç€ºããããšãã§ããŸãã
ãããã®ç¥èã䜿ããšãSQLã§ã®ããŒã¿åæãããã«ãã¯ãŒã¢ããããŸããç¹°ãè¿ãæŒç¿ãè¡ãããšã§ããã®ç¥èã身ã«ã€ããŸãããã
SQLã®å¿çš
ãããŸã§ã«åŠãã åºæ¬çãªSQLã®ææ³ã䜿ãã°ãããŒã¿ã®è¿œå ãæŽæ°ãåé€ãªã©èªç±èªåšã«ããŒã¿æäœãå¯èœãšãªããŸããããããããŒã¿éãå¢ãããšãããããè€éãªããŒã¿æäœãè¡ãå Žåã«ã¯ãSQLã®å¿çšçãªæè¡ãæ±ããããŸããç¹ã«ãã€ã³ããã¯ã¹ã掻çšãããã©ãŒãã³ã¹ãåäžãããæ¹æ³ãããã©ã³ã¶ã¯ã·ã§ã³ãç解ãããŒã¿ã®æŽåæ§ãä¿ã€æè¡ã¯ãæ¥ã ã®ããŒã¿ããŒã¹ã®éçšã«ãããŠéèŠã§ãã
ã€ã³ããã¯ã¹ãšã¯äœãïŒããã©ãŒãã³ã¹ãåäžããã
ã€ã³ããã¯ã¹ãšã¯ãããŒã¿ããŒã¹å ã®ããŒã¿ã®æ€çŽ¢é床ãåäžãããããã®ä»çµã¿ã§ããæ¬ãèªããšãã«ç®æ¬¡ãèŠã€ãããšãã®ããã«ãããŒã¿ããŒã¹å ã®ç¹å®ã®ããŒã¿ãçŽ æ©ãèŠã€ããããšãã§ããŸããç¹ã«ãããŒã¿éã倧ããå Žåããã£ã«ã¿ãªã³ã°ããœãŒããå€çšããå Žåã«ã€ã³ããã¯ã¹ã¯å€§ããªå¹æãçºæ®ããŸãã
ã€ã³ããã¯ã¹ã¯ãããŒã¿ã®è¿œå ãåé€ããããã³ã«èªåçã«æŽæ°ãããŸãããããã£ãŠãã€ã³ããã¯ã¹ãèšå®ããããšã§èªã¿åãã®é«éåã ãã§ãªããããŒã¿ã®è¿œå ãåé€ã«å¯Ÿããããã©ãŒãã³ã¹ã®äœäžãé¿ããããšãå¯èœã§ãã
äžæ¹ã§ãã€ã³ããã¯ã¹ã¯ããŒã¿ããŒã¹ã®ãã£ã¹ã¯ã¹ããŒã¹ãæ¶è²»ããŸãããŸããã€ã³ããã¯ã¹ãå€ããããšãããŒã¿ã®è¿œå ãåé€ãé ããªãããšããããŸããã§ããããã©ã®ã«ã©ã ãã€ã³ããã¯ã¹ã«ããããåªå 床ãèãé©åã«æ±ºå®ããå¿ èŠããããŸãã
ãã©ã³ã¶ã¯ã·ã§ã³ãšã¯äœãïŒããŒã¿ã®æŽåæ§ãä¿ã€
次ã«ãã©ã³ã¶ã¯ã·ã§ã³ã«ã€ããŠè§£èª¬ããŸãããã©ã³ã¶ã¯ã·ã§ã³ãšã¯ãäžé£ã®ããŒã¿æäœããŸãšããŠäžã€ã®äœæ¥åäœãšããŠæ±ãããšã§ããäŸãã°ãéè¡ã®å£åº§éã§ã®ééã¯äºã€ã®åŠçãåŒãåºããšé ãå ¥ããããæãç«ã¡ãŸãããããã¯äºã€ã®æäœãäžã€ã®ãã©ã³ã¶ã¯ã·ã§ã³ãšããŠæ±ããŸãã
ãªããã©ã³ã¶ã¯ã·ã§ã³ãå¿ èŠããšãããšããŒã¿ã®æŽåæ§ãä¿ã€ããã§ããäŸãã°ãåŒãåºãã®åŠçã¯ã§ããã®ã«é ãå ¥ãã®åŠçãã§ããªãã£ãå Žåãééã¯å®æããã«ãéãæ¶ãããšããäºæ ãé²ãããšãã§ããŸãããã©ã³ã¶ã¯ã·ã§ã³å ã®å šãŠã®æäœãå®äºããªããšããã®ãã©ã³ã¶ã¯ã·ã§ã³ã¯ç¡ãã£ãããšã«ãããŸããããããååæ§ããšåŒã³ãŸãã
ãŸããè€æ°äººãåæã«åãããŒã¿ã«ã¢ã¯ã»ã¹ããå Žåã§ãããã©ã³ã¶ã¯ã·ã§ã³ãçšããŠããŒã¿ã®æŽåæ§ãä¿ã€ããšãã§ããŸããããããéé¢æ§ããšåŒã³ãŸãã
ãã®ããã«ããã©ã³ã¶ã¯ã·ã§ã³ã¯ããŒã¿ããŒã¹ã®ä¿¡é Œæ§ãä¿ã€ããã«å¿ èŠäžå¯æ¬ ãªæŠå¿µã§ãã
SQLåŠç¿ã®ãã€ã³ã
ãããŸã§SQLã®åºæ¬çãªææ³ããå¿çšãŸã§ãèŠãŠããŸããããããããã ç¥èãé ã«å ¥ããã ãã§ã¯ãSQLã®çé«ãã€ããããšã¯ã§ããŸãããããã§ãããã§ã¯SQLã®åŠç¿ã«ããããã€ã³ãã«ã€ããŠè§£èª¬ããŸãã
å¹ççãªåŠç¿æ³
ãŸã倧åãªããšã¯ãå®éã«æãåããããšã§ããç¥èã ãã§ã¯ãªããå®è·µçµéšãSQLã¹ãã«ã磚ãäžã§éèŠã§ããç¹ã«SQLã¯ãè€éãªããŒã¿æäœãå®éã«è©ŠããªããåŠã¶ããšã§ç解ãæ·±ãŸããã®ã§ãããã®ãããã§ããã ãèªåã§ããœã³ã³ã«åãã£ãŠSQLãæžããå®è¡ããŠã¿ãä»çµã¿ã確èªããããšããå§ãããŸãã
ãŸãããªã³ã©ã€ã³è³æºãæå¹ã«æŽ»çšããŸããããWebäžã«ã¯ããŸããŸãªSQLã®åŠç¿ãµã€ããååšããŠãããçåç¹ã調ã¹ãããå ·äœçãªSQLæã®æžãæ¹ãåŠãã ãããããšãã§ããŸãã䜿çšããããŒã¿ããŒã¹ã·ã¹ãã ãéãã°ãSQLã®ææ³ãå°ãç°ãªããããèªåã䜿çšããŠããSQLã®ããŒãžã§ã³ã«åã£ãæ å ±ãæ¢ãããšãéèŠã§ãã
ãã©ãã«ã·ã¥ãŒãã£ã³ã°ã®åºæ¬
SQLãæžããŠãããšå¿ ããšèšã£ãŠããã»ã©ãšã©ãŒã«ééããŸããããã¯åŠç¿ã®äžç°ã§ãããåé¡è§£æ±ºã®çµéšã¯ããªãã®ã¹ãã«ãåäžããã絶奜ã®ãã£ã³ã¹ã§ãã
ãšã©ãŒã¡ãã»ãŒãžã¯èªåãäœãééããŠããã®ããç¥ãæãããã§ãããã®ããããŸãã¯ãšã©ãŒã¡ãã»ãŒãžããã£ãããšèªã¿ããã®æå³ãç解ããŸãããããšã©ãŒã¡ãã»ãŒãžãææããŠããç®æã ãã§ãªãããã®ååŸããã§ãã¯ããããšã§ãæãã¬èª€ããèŠã€ããããšããããŸãã
ãŸãããšã©ãŒè§£æ±ºã®ããã®æçšãªæ å ±ã¯ã€ã³ã¿ãŒãããäžã«ããããããããŸãããšã©ãŒã¡ãã»ãŒãžããã®ãŸãŸæ€çŽ¢ãšã³ãžã³ã«è²Œãä»ããŠæ€çŽ¢ããã ãã§ãã解決çãèŠã€ããããšãå€ãã§ãã
ãŸãšã
SQL(Sysytem Query Language)ã¯ãããŒã¿ããŒã¹ãšã®ãããšãã®ããã®èšèªã§ãããããŒã¿ã®æ€çŽ¢ãæ¿å ¥ãæŽæ°ãåé€ãšãã£ãæäœãå¯èœã§ããç¹ã«å€§èŠæš¡ãªããŒã¿ãåãæ±ãéã«ã¯ãã®å¹çã®è¯ãããå€ãã®çŸå Žã§å©çšãããŠããŸãã
åºæ¬çãªSQLã®ææ³ãåœä»€ã¯éåžžã«ã·ã³ãã«ã§ãSELECTãINSERTãUPDATEãDELETEã®åæãå§ããWHEREå¥ãçšããŠç¹å®ã®ããŒã¿ãéžæããããšãåºæ¥ãããã«ãªãã°åçŽã¬ãã«ã®SQLã¯ãã¹ã¿ãŒã§ãããšèšããŸãã
ãããŠããæ¬æ ŒçãªããŒã¿åæãè¡ãéã«ã¯ãè€æ°ã®ããŒãã«ããããŒã¿ãæœåºããJOINã®æŽ»çšããããŒã¿ãéããŠæäœããGROUP BYãšéèšé¢æ°ãããã«ã¯è€éãªããŒã¿ã®æœåºãå¯èœã«ããSUBQUERYãšVIEWã®ç解ãæ±ããããŸãã
ãŸããSQLã®ããã©ãŒãã³ã¹ãåäžãããããããŒã¿ã®æŽåæ§ãä¿ã€ããã«ã¯ãã€ã³ããã¯ã¹ããã©ã³ã¶ã¯ã·ã§ã³ãšãã£ãç¥èãå¿ èŠãšãªã£ãŠããŸãã
æ¬èšäºãéããŠSQLã®åºæ¬çãªæäœããå¿çšãŸã§ãåŠã¶ããšãã§ããããšã§ããããããããããæ·±ãSQLãç¿åŸããããã«ã¯ãå®éã«æãåãããŠå€ãã®SQLæãæžãããšãäœããéèŠã§ããåžžã«æ°ããç¥èãæ±ãç¶ããèºããæã§ãè«Šããã«åãçµã¿ç¶ããããšã§ãSQLã®ã¹ãã«ã¯å¿ ãäžéããŸãã