1. ã¯ããã«ïŒSQLãšã¯äœãïŒ
è¿å¹ŽãããŒã¿é§åã®æ代ãšãèšãããçŸä»£ã«ãããŠãããŒã¿ã®åãæ±ããéåžžã«éèŠãšãªã£ãŠããŸããããã®äžå¿ã«äœçœ®ããã®ããããŒã¿ããŒã¹ãšããã·ã¹ãã ã§ãããã®ã»ã¯ã·ã§ã³ã§ã¯ãããŒã¿ããŒã¹ãšãã®æäœèšèªã§ããSQLã«ã€ããŠãåºæ¬çãªæŠå¿µãšãã®åœ¹å²ã解説ããŸãã
1.1 ããŒã¿ããŒã¹ãšSQLã®é¢ä¿
ããŒã¿ããŒã¹ã¯ãæ å ±ãæŽçããŠä¿åããããã®ã·ã¹ãã ã§ããäŸãã°ããªã³ã©ã€ã³ã·ã§ããã³ã°ãµã€ãã®ååæ å ±ããSNSã®ãŠãŒã¶ãŒæ å ±ãªã©ãç§ãã¡ãæ¥åžžã§å©çšããå€ãã®ãµãŒãã¹ã¯ãããŒã¿ããŒã¹ãå©çšããŠæ å ±ã管çããŠããŸãã
ããããããŒã¿ããŒã¹ãæäœããããã®èšèªãSQLã§ããSQLã¯ãStructured Query Languageãã®ç¥ã§ãããŒã¿ããŒã¹ã«åãåãããè¡ã£ãããããŒã¿ãè¿œå ã»æŽæ°ã»åé€ããããã®åœä»€ãèšè¿°ããããã®èšèªã§ãã
å ·äœçã«ã¯ãååã®åšåº«æ°ã調ã¹ãããæ°ãããŠãŒã¶ãŒãç»é²ããéã«ãSQLãè£åŽã§åããŠããŸããããŒã¿ããŒã¹ãšSQLã¯ããŸãã§æãšå£ã®ãããªé¢ä¿ã§ãããŒã¿ããŒã¹ãæ å ±ãä¿æãããæããšããŠã®åœ¹å²ãæããäžæ¹ãSQLããã®æ å ±ãåãåºããå£ããšããŠã®åœ¹å²ãæãããŸãã
1.2 SQLã®åºæ¬çãªåœ¹å²ãšç¹åŸŽ
SQLã®åœ¹å²ã¯å€§ããåããŠ3ã€ãããŸãã第äžã«ãããŒã¿ã®æ€çŽ¢ãšååŸã§ããããã¯ãç¹å®ã®æ¡ä»¶ã«åèŽããããŒã¿ãããŒã¿ããŒã¹ããåãåºãããã®æäœã§ããäŸãã°ãç¹å®ã®åååãæã€ååã®äŸ¡æ Œãç¥ãããå Žåããç¹å®ã®ãŠãŒã¶ãŒã®æçš¿ã衚瀺ãããå Žåãªã©ã«äœ¿çšãããŸãã
第äºã«ãããŒã¿ã®è¿œå ãæŽæ°ãåé€ã§ããæ°ããååãããŒã¿ããŒã¹ã«è¿œå ãããããŠãŒã¶ãŒæ å ±ãæŽæ°ããããäžèŠã«ãªã£ãããŒã¿ãåé€ããéã«äœ¿çšãããŸãã
第äžã«ãããŒã¿ããŒã¹ã®æ§é ã®å®çŸ©ãå€æŽã§ããããã¯ãæ°ããããŒãã«ãäœæããããæ¢åã®ããŒãã«ã®æ§é ãå€æŽããéã«äœ¿çšãããŸãã
ãããã®åœ¹å²ãæã€SQLã¯ãéåžžã«åŒ·åã§ãããªããããã·ã³ãã«ãªææ³ãæã£ãŠããŸãããã®ãããåå¿è ã§ãæ¯èŒççæéã§åºæ¬çãªæäœãåŠã¶ããšãã§ããŸãããããããã®èåŸã«ã¯è€éãªããŒã¿ããŒã¹ã®æ§é ãåäœåçãããããããç解ããããšã§ãããé«åºŠãªæäœãæé©åãè¡ãããšãã§ããŸãã
2. SQLè³æ Œã®ã¡ãªãã
çŸä»£ã®æ å ±ç€ŸäŒã«ãããŠãããŒã¿ã®åãæ±ãã¯æ¥µããŠéèŠã§ãããã®äžå¿ãšãªãã®ãããŒã¿ããŒã¹æè¡ã§ããããã®æäœã管çã«é¢ããç¥èã¯å€ãã®äŒæ¥ãçµç¹ã§æ±ããããŠããŸãããã®ã»ã¯ã·ã§ã³ã§ã¯ãSQLè³æ ŒãååŸããããšã®ã¡ãªããã«ã€ããŠè©³ãã解説ããŸãã
2.1 ç¥èã®äœç³»çãªç¿åŸ
SQLè³æ ŒãååŸããéçšã§ã¯ãããŒã¿ããŒã¹ã®åºæ¬çãªæŠå¿µããé«åºŠãªæäœãŸã§ãå¹ åºãç¥èãäœç³»çã«åŠã¶ããšãã§ããŸããèªå·±åŠç¿ã®ã¿ã§ç¥èãæ·±ããå ŽåãèŠèœãšããã¡ãªéšåããåŠã¶é åºãé©åã§ãªãããšããããŸããããããè³æ ŒååŸã®ããã®ã«ãªãã¥ã©ã ãææã¯ãåå¿è ããäžçŽè ãŸã§ãèæ ®ããŠæ®µéçã«æ§ç¯ãããŠããŸããããã«ãããå¹ççã«ç¥èãæ·±ããããšãå¯èœãšãªããŸãã
2.2 ã¹ãã«ã®å®¢èŠ³çãªèšŒæ
SQLã®ã¹ãã«ãç¥èãæã£ãŠãããšèªå·±ç³åããã ãã§ã¯ããã®å®åãæ£ç¢ºã«äŒããã®ã¯é£ããã§ããããããå ¬èªã®è³æ ŒãååŸããããšã§ããã®ã¹ãã«ãç¥èã客芳çã«èšŒæããããšãã§ããŸããããã¯ãå°±è·ã転è·ã®éãäŒæ¥ã«èªèº«ã®èœåãã¢ããŒã«ãã倧ããªæŠåšãšãªããŸãã
2.3 å°±è·ã»è»¢è·æŽ»åã§ã®ã¢ããŒã«ãã€ã³ã
å€ãã®äŒæ¥ãçµç¹ã¯ãããŒã¿ããŒã¹ã®å°é家ãæ±ããŠããŸããç¹ã«ã倧æäŒæ¥ãITé¢é£ã®äŒæ¥ã§ã¯ãSQLã®ã¹ãã«ã¯å¿ é ãšãããããšãå€ãã§ããSQLè³æ Œãæã£ãŠããããšã¯ãå±¥æŽæžãè·åçµæŽæžã§ã®å€§ããªã¢ããŒã«ãã€ã³ããšãªããä»ã®å¿åè ãšã®å·®å¥åãå³ãããšãã§ããŸãã
2.4 ãã©ãã«å¯Ÿå¿èœåã®åäž
ããŒã¿ããŒã¹ã®ãã©ãã«ã¯ãäŒæ¥ã®æ¥åã«å€§ããªåœ±é¿ãåãŒãããšããããŸããäŸãã°ãããŒã¿ã®äžæŽåãã·ã¹ãã ã®ããŠã³ãªã©ãæ§ã ãªãã©ãã«ãèããããŸããSQLè³æ ŒãååŸããããšã§ããããã®ãã©ãã«ã«è¿ éãã€é©åã«å¯Ÿå¿ããèœåã身ã«ã€ããŸããããã¯ãäŒæ¥ã®ä¿¡é ŒãåŸãããã®å€§ããªèŠçŽ ãšãªããŸãã
3. 代衚çãªSQLè³æ Œã®çŽ¹ä»
ããŒã¿ããŒã¹æè¡ã®éèŠæ§ãé«ãŸãäžãå€ãã®è³æ ŒãæäŸãããŠããŸãããã®äžã§ããç¹ã«äººæ°ãšå®çšæ§ãé«ãè³æ Œã2ã€ããã¯ã¢ãããã詳ãã玹ä»ããŸãã
3.1 ORACLE MASTER
ORACLE MASTERã¯ãäžççã«æåãªããŒã¿ããŒã¹ãœãããŠã§ã¢ã§ããOracleã®å ¬åŒè³æ Œã§ãããã®è³æ Œã¯ãOracleã®ããŒã¿ããŒã¹ãå¹æçã«éçšããããã®ç¥èãšã¹ãã«ã蚌æããŸãã
3.1.1 è©Šéšå 容ãšç¹åŸŽ
ORACLE MASTERã®è©Šéšã¯ãåºæ¬çãªããŒã¿ããŒã¹ã®æäœããé«åºŠãªèšå®ãæé©åãŸã§ãå¹ åºããããã¯ãã«ããŒããŠããŸããè©Šéšã¯äž»ã«éžæåé¡ãšå®æåé¡ããæ§æãããå®éã®æ¥åã§ã®ã·ãã¥ãšãŒã·ã§ã³ãæ³å®ããåé¡ãåºé¡ãããŸããç¹ã«ãå®æåé¡ã§ã¯ãå®éã®ããŒã¿ããŒã¹ãæäœããã¹ãã«ãåããããããå®åçµéšãããæ¹ãæå©ãšãããŠããŸãã
3.1.2 ååŸã®ã¡ãªãã
ORACLE MASTERãååŸããããšã§ãOracleããŒã¿ããŒã¹ã®ãããã§ãã·ã§ãã«ãšããŠã®ã¹ãã«ã客芳çã«èšŒæããããšãã§ããŸãããŸããå€ãã®å€§æäŒæ¥ãOracleãæ¡çšããŠãããããå°±è·ã»è»¢è·æŽ»åã«ãããŠã倧ããªã¢ããã³ããŒãžãšãªããŸããããã«ãè³æ ŒååŸãéããŠãããŒã¿ããŒã¹ã®æé©åããã©ãã«ã·ã¥ãŒãã£ã³ã°ã®ã¹ãã«ãç¿åŸããããšãã§ããŸãã
3.2 MySQL 5.7 Database Administrator
MySQLã¯ããªãŒãã³ãœãŒã¹ã®ããŒã¿ããŒã¹ãœãããŠã§ã¢ãšããŠåºãå©çšãããŠããŸããMySQL 5.7 Database Administratorã¯ãMySQLã®éçšãšç®¡çã«é¢ããç¥èã蚌æããè³æ Œã§ãã
3.2.1 è©Šéšå 容ãšç¹åŸŽ
ãã®è³æ Œã®è©Šéšã¯ãMySQLã®åºæ¬æäœããããã¯ã¢ããã埩å ãããã©ãŒãã³ã¹ãã¥ãŒãã³ã°ã«é¢ããç¥èãŸã§ãç¶²çŸ ããŠããŸããè©Šéšã¯éžæåé¡äžå¿ã§ãå®éã®éçšã·ãŒã³ãæ³å®ããåé¡ãå€ãåºé¡ãããŸããMySQLãæ¥åžžçã«äœ¿çšããŠããæ¹ããããŒã¿ããŒã¹ã®éçšçµéšãããæ¹ã«ã¯ãååŸãæ¯èŒç容æãšãããŠããŸãã
3.2.2 ååŸã®ã¡ãªãã
MySQL 5.7 Database Administratorã®è³æ Œãæã€ããšã§ãMySQLã®å°é家ãšããŠã®ã¹ãã«ãæ確ã«ã¢ããŒã«ããããšãã§ããŸããç¹ã«ãã¹ã¿ãŒãã¢ããäŒæ¥ãäžå°äŒæ¥ã§ã¯ãMySQLãæ¡çšããã±ãŒã¹ãå€ãããããã®è³æ Œã¯å€§ããªäŸ¡å€ãæã¡ãŸãããŸããè³æ ŒååŸãéããŠãããŒã¿ããŒã¹ã®å®å®éçšããã©ãã«å¯Ÿå¿ã®ããŠããŠãæ·±ããããšãã§ããŸãã
4. SQLè³æ Œã®å匷æ¹æ³
SQLè³æ ŒãååŸããããã«ã¯ãå¹ççãªåŠç¿æ¹æ³ãéžæããããšãéèŠã§ããè³æ ŒååŸãç®æãæ¹ã ã®èæ¯ãåŠç¿ã¹ã¿ã€ã«ã¯æ§ã ã§ãããäžã€ã®æ¹æ³ãå šãŠã®äººã«åãããã§ã¯ãããŸããããã®ã»ã¯ã·ã§ã³ã§ã¯ã代衚çãªåŠç¿æ¹æ³ãšãã®ç¹åŸŽã玹ä»ããŸãã
4.1 æžç±ãå©çšããåŠç¿
æžç±ã¯ãèªåã®ããŒã¹ã§ãã£ãããšåŠç¿ããããšãã§ãããããå€ãã®äººã«éžã°ããåŠç¿æ¹æ³ã®äžã€ã§ããåžè²©ãããŠããSQLã®æç§æžãè³æ Œå¯Ÿçæžã¯ãåå¿è ããäžçŽè ãŸã§å¹ åºãå 容ãç¶²çŸ ããŠããŸããç¹ã«ãå®éã®è©Šéšåé¡ãæš¡æ¬è©Šéšãåé²ãããŠããæžç±ã¯ãè©Šéšå¯ŸçãšããŠéåžžã«æå¹ã§ãã
4.2 eã©ãŒãã³ã°ã§ã®åŠç¿
eã©ãŒãã³ã°ã¯ãã€ã³ã¿ãŒããããå©çšããŠåŠç¿ããæ¹æ³ã§ããåç»ãã€ã³ã¿ã©ã¯ãã£ããªã³ã³ãã³ãã掻çšããŠãå®éã®ããŒã¿ããŒã¹æäœãäœéšããªããåŠã¶ããšãã§ããŸãããŸããèªå® ãå€åºå ã§ã®åŠç¿ãå¯èœãªãããå¿ããæ¹ã移åæéãæå¹æŽ»çšãããæ¹ã«ããããã§ãã
4.3 å®æ©ãçšããå®è·µçãªåŠç¿
ããŒã¿ããŒã¹ã®ç¥èãæ·±ããããã«ã¯ãå®éã®æ©åšã䜿çšããŠã®å®è·µçãªåŠç¿ãäžå¯æ¬ ã§ããèªèº«ã®PCãå©çšããŠãããŒã¿ããŒã¹ãœãããŠã§ã¢ãã€ã³ã¹ããŒã«ããå®éã®ããŒã¿æäœãè¡ãããšã§ãå®åã§ã®å¿çšåã身ã«ã€ããããšãã§ããŸãã
4.4 è³æ ŒååŸã®ããã®è©Šéšå¯Ÿç
è³æ Œè©Šéšã¯ãåºç¯ãªç¥èãšã¹ãã«ãåããããããå¹æçãªè©Šéšå¯Ÿçãå¿ èŠã§ããéå»åã®è§£èª¬ãæš¡æ¬è©Šéšãç¹°ãè¿ãè¡ãããšã§ãè©Šéšã®åŸåãèªèº«ã®åŒ±ç¹ãææ¡ããããšãã§ããŸãããŸããå®éã®è©Šéšç°å¢ãæ³å®ããæš¡æ¬è©Šéšãåéšããããšã§ãè©Šéšåœæ¥ã®ç·åŒµæã軜æžããããšãå¯èœã§ãã
5. SQLè³æ ŒååŸã®ã¹ããããã€ã¹ãããã¬ã€ã
SQLè³æ ŒãååŸããããã®éã®ãã¯ãå人ã®çµéšãç¥èã®èæ¯ã«ãã£ãŠç°ãªããŸããããããäžè¬çãªã¢ãããŒãã泚æç¹ãç¥ãããšã§ãããã¹ã ãŒãºã«è³æ ŒååŸãç®æãããšãã§ããŸãããã®ã»ã¯ã·ã§ã³ã§ã¯ãåå¿è ããäžçŽè ãŸã§ã®æ¹ã ã«åããã¹ããããã€ã¹ãããã®ã¬ã€ããæäŸããŸãã
5.1 åå¿è ããã¹ã¿ãŒãããå Žåã®ã¢ãããŒã
ããŒã¿ããŒã¹ãSQLã«é¢ããç¥èãã»ãšãã©ãªãåå¿è ã®æ¹ã¯ããŸãåºæ¬çãªæŠå¿µã®ç解ããå§ããããšãéèŠã§ããåžè²©ãããŠããå ¥éæžããªã³ã©ã€ã³ã®ææãå©çšããŠãããŒã¿ããŒã¹ã®ä»çµã¿ãSQLã®åºæ¬çãªåœä»€ãåŠã¶ããšãããããããŸãããŸããå®éã«æãåãããŠããŒã¿ããŒã¹ãæäœããããšã§ãçè«ã ãã§ãªãå®è·µçãªã¹ãã«ã身ã«ã€ããããšãã§ããŸãã
5.2 äžçŽè 以äžã®æ¹åãã®ã¢ããã€ã¹
ãã§ã«åºæ¬çãªç¥èãå®åçµéšãæã£ãŠããäžçŽè 以äžã®æ¹ã¯ãè³æ Œè©Šéšã®ç¯å²ãé£æ床ã«åãããŠåŠç¿ãé²ããããšãå¹æçã§ããéå»åã®è§£èª¬ãæš¡æ¬è©Šéšãç¹°ãè¿ãè¡ãããšã§ãè©Šéšã®åŸåãèªèº«ã®åŒ±ç¹ãææ¡ããããšãã§ããŸãããŸããç¹å®ã®ãããã¯ãæè¡ã«é¢ããæ·±ãç¥èãæ±ããããå Žåãå°éæžããªã³ã©ã€ã³ã³ãŒã¹ã掻çšããããšãæå¹ã§ãã
5.3 è³æ ŒååŸåŸã®ãã£ãªã¢ãã¹
SQLè³æ ŒãååŸããåŸã®ãã£ãªã¢ã¯å€å²ã«ããããŸããããŒã¿ããŒã¹ç®¡çè ãããŒã¿ã¢ããªã¹ããã·ã¹ãã ãšã³ãžãã¢ãªã©ãå€ãã®è·çš®ã§SQLã®ã¹ãã«ãæ±ããããŠããŸããè³æ Œã掻ãããŠæ°ããè·å Žãç®æãããšãã§ããŸãããçŸåšã®è·å Žã§ã®ã¹ãã«ã¢ãããæé²ã®ããã®ã¹ããããšããŠæŽ»çšããããšãå¯èœã§ãããŸããè³æ ŒååŸããã£ããã«ããããªãäžäœè³æ Œãé¢é£ããæè¡ã®åŠç¿ãæ·±ããããšã§ãããå°éçãªãã£ãªã¢ãç¯ãããšãèããããŸãã
6. ãŸãšãïŒSQLè³æ ŒãååŸããŠãã£ãªã¢ãå éãããã
ãã®èšäºãéããŠãSQLã®åºæ¬çãªæŠå¿µããè³æ ŒååŸã®ã¡ãªãããå匷æ¹æ³ããããŠãã£ãªã¢ãã¹ã«ã€ããŠè©³ããåŠã¶ããšãã§ããŸãããããŒã¿ããŒã¹æè¡ã¯çŸä»£ã®ããžãã¹ã·ãŒã³ã§éåžžã«éèŠãªåœ¹å²ãæãããŠããããã®äžå¿ã«äœçœ®ããã®ãSQLã§ãã
SQLè³æ ŒãååŸããããšã§ãããŒã¿ããŒã¹ã®å°é家ãšããŠã®ã¹ãã«ãç¥èã客芳çã«èšŒæããããšãã§ããŸããããã¯ãå°±è·ã転è·ã®éã®å€§ããªã¢ããã³ããŒãžãšãªããå€ãã®äŒæ¥ãçµç¹ã§é«ãè©äŸ¡ãããã§ãããã
ãŸããè³æ ŒååŸã®éçšã§åŠã¶ç¥èãã¹ãã«ã¯ãå®åã§ã®ãã©ãã«å¯Ÿå¿ãããŒã¿ããŒã¹ã®æé©åãªã©ãæ§ã ãªã·ãã¥ãšãŒã·ã§ã³ã§åœ¹ç«ã¡ãŸããç¹ã«ãããŒã¿ã®åæ±ããå¢ããçŸä»£ã«ãããŠãå¹ççãªããŒã¿ããŒã¹ã®éçšã¯ããžãã¹ã®æåãå·Šå³ããéèŠãªèŠçŽ ãšãªã£ãŠããŸãã
è³æ ŒååŸãç®æãæ¹ã ã«ãšã£ãŠããã®èšäºãäžã€ã®æåæžãšãªããæåãžã®ç¬¬äžæ©ãšãªãããšãé¡ã£ãŠããŸããæåŸã«ãSQLè³æ Œãæã€ããšã¯ãåãªã蚌ææžä»¥äžã®äŸ¡å€ããããŸããããã¯ãããŒã¿ãšããæ代ã®è³æºãå¹æçã«æŽ»çšããèœåãæã£ãŠããããšã®èšŒã§ãããã®èœåãæã€ããšã§ãããªãã®ãã£ãªã¢ã¯ããã«å éããå€ãã®å¯èœæ§ãåºããã§ãããã