SQLãããã«SQLãããïŒ
å®åã«éããé«åºŠãªã¯ãšãªèœåãè²ãŠãŸãã
ã¯ãšãªåé¡ã
ããŸã解決ã§ããŸããïŒ
以äžã¯ãå®åã«ãããå
±éã®éèŠãªã¯ãšãªåé¡ã§ãã
- ãã©ã¡ãŒã¿ãšããŠé
åïŒããŒãã«ïŒããŒã¿ãæž¡ãããŸããŸãªæ¹æ³
- TOP åçåŠçãã§ããªãå Žåã®åé¡
- Row-to-Colæäœã®ããã®STRING_AGGïŒïŒ
- åå€ã§è¡ãäœæããVALUES()
- Composable DMLã§UPDATE + SELECT + INSERTã1éšå±ã«
- INNER JOINã§ããã OUTER JOINã誀ã£ãŠäœ¿çšããå Žå
- 亀差éå/åéå/å·®éåã®æ°åŠçç¹æ§ãšSQLã®éã
- INãšNOT INã®éèŠãªéã
- CTEïŒå
±éããŒãã«åŒïŒã®éèŠãªæ©èœ
- éèšããã³çµ±èšé¢æ°ã®OVER()æ¡åŒµæ©èœ
- äœçœ®é¢æ°ãšååžé¢æ°ãçšããæç³»åããã³å皮統èšåŠç
- Paging åºæ¬ã¯ãšãªåºæºããŸããŸãªå®è£
æ¹æ³
- GROUPING SETSãæŽ»çšããæ§ã
ãªå°èšã»ç·èšåŠç
- ä»ã®SQLã¯ãšãªãSQLã®ããã«äœ¿çšããããã®éèŠãªæŠå¿µãçè§£ãã
DBã³ã³ãµã«ãã£ã³ã°ããã¯ãã«ã«ãµããŒããè¬çŸ©ãªã©ãããŸããŸãªäŒæ¥ã§éçºãããã¯ãšãªãèŠããšãSQLèšèªã®ç¹æ§ãæŠå¿µãååã«çè§£ã§ãããé©åã«æžãããŠããªãã¯ãšãªãåçŽã§äŸ¿å©ãªé«åºŠãªã¯ãšãªãç¥ããã«é£ããäœæãããã¯ãšãªãããã³SQL ServerãæäŸããããŸããŸãªæ©èœãç¥ãããå¶éãããæ¹æ³ã§é£ããå®è£
ããå Žåãå€ãèŠãããŸãã
ãã¬ãŒãã³ã°ãéããŠããããã®ã¯ãšãªèœåãããã«äžæ©ã¬ãã«ã¢ããããã®ã«åœ¹ç«ã¡ãŸãã
SQL Server ã³ã³ãµã«ã¿ã³ã
SQLé«åºŠãªã¯ãšãªã®ç§å¯ïŒ
- â
SQLã¯ãšãªã®åºæ¬çãªç¹æ§ã®çè§£
- â
éåãšé¢ä¿æŒç®åã®æ£ãã䜿çš
- â
é«åºŠãªSQLã¯ãšãªæ©èœãšå®å掻çšåŠç¿
- â
ææ°ããŒãžã§ã³ã®SQLæ©èœã®åŠç¿ãšæŽ»çš
åãçµæãåŸãããã«ãå€ãã®å ŽåãããŸããŸãªå®è£
ãå¯èœã§ããããšããSQLèšèªã®ç¹åŸŽã§ãã
ç¹ã«ããã®ããã»ã¹ã§ã¯ãå®åã§å
±éããŠããéèŠãªã¯ãšãªåé¡ãšèª€ã£ãäœ¿çšæ¹æ³ã«å ããŠãããŸããŸãªè§£æ±ºçãåãäžããŠããŸãã
ããã«ãææ°ããŒãžã§ã³ã®SQL Serverã§æäŸãããŠããæ°ããæ§æã颿°ãããã³ã³ãã³ãã®åŠç¿ã«ãããã¢ããã°ã¬ãŒããããã·ã¹ãã ãšSQL ServerãããæŽ»çšããã®ã«åœ¹ç«ã¡ãŸãã
誰ãèããšããã§ããïŒ
SQL ServerããŒã¹ã®å®åè
ããŠãŒã¶ãŒãªã誰ã§ãèãããšãã§ããŸãã
SQL ServerããŒã¹
åïœäžçŽéçºè
SQL ServerããŒã¹
ãœãªã¥ãŒã·ã§ã³éçºè
SQL ServerããŒã¹
è¶
ïœäžçŽDB管çè
ããŒã¿ãšã³ãžãã¢ã
ããŒã¿ã¢ããªã¹ã
ãããªæ©ã¿ããã£ããæ³šç®ïŒ
ãSQL Serverã®äœ¿çšåå¿è
ã§ãããåè¬ããŠãããã§ããïŒã
ð SQLã®åºç€ã¯ç¥ã£ãŠããã¹ãå
容ãçè§£ããåŸãã®ã«ç°¡åã«ãªãã§ãããã
ããã®ãã¬ãŒãã³ã°ã§ããããã©ãŒãã³ã¹ã®é«ãã¯ãšãªãäœæããã®ã«åœ¹ç«ã¡ãŸããïŒã
ðã©ã®ã¯ãšãªãããã©ãŒãã³ã¹äžããè¯ãããæ±ããŸããããå®éã®ãã¯ãšãªãã¥ãŒãã³ã°ãã³ãŒã¹ã®éžæããã»ã¹ãšããŠå¿
èŠãªã®ããã®é«åºŠãªSQLã³ãŒã¹ã§ãã
ãSQL Serverã¯ææ°ããŒãžã§ã³ã䜿çšããŠããŸãããå®åã¯ãšãªã¯ãŸã å€ãæ©èœã䜿çšããŠããŸããã
ð ã¯ãããã®éçšã§åãããã¯ããšã«ææ°æ©èœã掻çšããå
容ã玹ä»ããŸãã®ã§ãæè²ãéããŠããææ°æ©èœã§å®åã¯ãšãªãæ¹åããã®ã«åœ¹ç«ã¡ãŸãã
äžæ®µéé«ã
SQLã¯ãšãªãäœæããããã
éèŠãªã¯ãšãªã®äœæå
容ãš
ããŸããŸãªå®è£
æ¹æ³ã®åŠç¿
SQLã«é©ãã
ã¯ãšãªãæžãããã®çè§£
ã¯ãšãªãã¥ãŒãã³ã°çš
é«åºŠãªã¯ãšãªæè¡ãŸã§
- å®åã§äžè¬çã«æ±ããããéèŠãªã¯ãšãªã®äœæãšããŸããŸãªå®è£
æ¹æ³ãåŠã¶ããšãã§ããŸãã
- SQLã®ç¹æ§ãšéåããã³é¢ä¿æäœã®åºæ¬æŠå¿µãçè§£ããããšã§ãSQLã«é©ããã¯ãšãªãäœæã§ããŸãã
- SQLã¯ãšãªã®ãã¥ãŒãã³ã°ã«ãã¬ã€ã€ãŒã®æ¡ä»¶ãšããŠå¿
èŠãªé«åºŠãªã¯ãšãªãã¯ããã¯ãç¿åŸã§ããŸãã
次ã®å
容
åŠç¿ããŸãã
1. SELECTé«åºŠãªæ©èœ
select-listå¥ã§éèŠã«äœ¿çšããã屿§ãRow-to-ColãFormatãNULLãTOPãCASEæãä¹±æ°ããŒã¿ã®åŠçæ¹æ³ãªã©ãæ±ããŸãã
2. DML (Data Manipulation Language) é«åºŠãªæ©èœã掻çš
ããŒãã«å€ã®ã³ã³ã¹ãã©ã¯ã¿ãUPDATE SETå¥ã®é«åºŠãªæŽ»çšãOUTPUTå¥ãComposable DMLã®æŽ»çšãMERGEã¹ããŒãã¡ã³ããæ¡çªã³ãŒãã®äŸãSEQUENCEãªããžã§ã¯ãã®çè§£ãäžæããŒãã«ãšããŒãã«å€æ°ã®ç¹æ§ã®çè§£ãåçSQLãšsp_executesqlã®æŽ»çšã®ãã³ããããã·ãŒãžã£ã®é
åïŒãŸãã¯ããŒãã«æ§é ïŒã®å€ãæž¡ãããŸããŸãªæ¹æ³ã«ã€ããŠèª¬æããŸãã
3. éåãšé¢ä¿æŒç®ïŒçµåãšãµãã¯ãšãªïŒã®é«åºŠãªæŽ»çš
çµåã®èæ¯ãšç®çãCROSS JOINã䜿çšããè¡ã¬ããªã±ãŒã·ã§ã³æè¡ã®æŽ»çšã亀差éå/åéå/å·®éåãšSQLããã¹ãããã³ãµãã¯ãšãªã®ç¹æ§çè§£ãšæŽ»çšã掟çããŒãã«æŽ»çšãCTEããã³ååž°åŒã³åºãååãã®æŽ»çšæ¹æ³ã«ã€ããŠèª¬æããŸãã
4.éèšãçµ±èšãåæé¢æ°ãè¡å眮æãå°èšãç·èšã®é«åºŠãªæŽ»çš
éèšé¢æ°ã®ç¹æ§ã®çè§£ãOVER()ãçšããéèšé¢æ°ã®æ¡åŒµæ©èœã®çè§£ãWindowãšFrameã®çè§£ã«ããé«åºŠãªéèšäœæ¥ã®æŽ»çšãåæé¢æ°ã§ããäœçœ®é¢æ°ãšååž/å颿°ãå©çšãã倿§ãªçµ±èšäœæ¥ã®æŽ»çšãPIVOT/UNPIVOTãçšããè¡å眮æãå°èšãšç·èšã®å€æ§ãªæŽ»çšæ©èœãåãäžããŸãã
ç¥èå
±æè
ã®ç޹ä»
ãã ã»ãžã§ã³ãœã³
å±¥æŽ
- ã±ã·ãŒã¯ãªãïŒwww.sqlroad.comïŒä»£è¡šã³ã³ãµã«ã¿ã³ã/åç· åœ¹ïŒ2005幎ããïŒ
- SQL Serverã³ã³ãµã«ãã£ã³ã°ãæè¡æ¯æŽãè¬çŸ©ããœãªã¥ãŒã·ã§ã³éçº
- SQLBigEyesïŒSQL Serverããã©ãŒãã³ã¹ã¢ãã¿ãªã³ã°ãœãªã¥ãŒã·ã§ã³ãwww.sqlbigeyes.comïŒã®éçº
- SQLBigEyesïŒSQL Serverããã©ãŒãã³ã¹ã¢ãã¿ãªã³ã°ãœãªã¥ãŒã·ã§ã³ãwww.sqlbigeyes.comïŒã®éçº
- Microsoft Data Platform MVP
- 2002幎ããïŒçŸåš2023å¹ŽåºæºïŒ21幎éåè³ã2008幎MVPååŽè³åè³
è³æ ŒãšèªèšŒ
- MCTãAzure DBAãMCITPãªã©
è¬çŸ©æŽ»åïŒ1999幎ããïŒ
- éå±±æ
å ±ç£æ¥æ¯èé¢ãã¹ãã«ãµããŒãæè²ã»ã³ã¿ãŒãã©ã³ãã³ã°ãŠã§ã€ã³ãªã¢æè²ã»ã³ã¿ãŒããµã ã¹ã³ãã«ããã£ã³ãã¹ãªã©å€æ°
- SQL Serverèªå·±éçºããã»ã¹ã®é²è¡ïŒã¯ãšãªãã¥ãŒãã³ã°ãDBãã¥ãŒãã³ã°ã倧容éããŒã¿ã³ã¢ãã¯ãããžã®çè§£ãªã©ïŒ
å€éšæŽ»å
- SQL Server MVP Deep Dives Vol 2 (Manning Pubs)å
±èè
ã«åå
- ãã ã»ãžã§ã³ãœã³ã®SQL Serverã³ã³ãµã«ãã£ã³ã°ã¹ããŒãªãŒã·ãŒãºã³13幎éé²è¡äž
- SQL Unpluggedã»ãããŒ5幎éé²è¡
- SQL PASS Korea Chapter Leader
- Microsoft IgniteãBITECãTech CampãMVP CampãTechEdãMSDNãªã©ã®Speakerã«åå
äºæ³ããã質å Q&A
Q. éå°æ»è
ãèããè¬çŸ©ã§ããïŒ
SQLã®åºç€ã䜿çšçµéšãããã°èãããšãã§ããŸããå°ãäžè¶³ããŠãåè¬äžã«å¿
èŠãªåºç€ã䞊è¡åŠç¿ããã°å¯èœã§ãã
Q. ãAdvancedãã®åºæºã¯ã©ããªããŸããïŒ
è€æ°ã®æžç±ãåºçç©ãã»ãããŒãªã©ã§é«çŽã¬ãã«ãšè¡šçŸããéã®ã¬ãã«ãç®å®ã«ããŸããããå®éã«ã¯å人ããšã«ã°ãã€ãããããŸãã
Q. è¬çŸ©ãèãåã«æºåãã¹ãããšã¯ãããŸããïŒ
SQL Server ãæäŸãã SSMS ããã°ã©ã ãã¯ãšãªäœ¿çšå¯èœãªããã°ã©ã ãã€ã³ã¹ããŒã«ããŠããããã³ãŒã¹ç޹ä»ãã§æäŸããããã¢çš DB ãš SQL ã¹ã¯ãªããããããããããŠã³ããŒãããŠããããšãã§ããŸãã
Q. åè¬ã«é¢ããæ³šæäºé
ïŒå¿
èŠãªç°å¢ããã®ä»ã®çæäºé
ïŒã¯ãããŸããïŒ
ãã¢ãšèª²é¡ãå®è¡ããããã«SQL Serverãå¿
èŠã§ããå¯èœã§ããã°ãææ°ããŒãžã§ã³ïŒ2021幎çŸåšã®2019幎ïŒãè¯ãã以åã®ããŒãžã§ã³ãåé¡ãããŸããã