{"action":"created","issue":{"url":"https://api.github.com/repos/swatanabe214/study_marking_201503/issues/1","labels_url":"https://api.github.com/repos/swatanabe214/study_marking_201503/issues/1/labels{/name}","comments_url":"https://api.github.com/repos/swatanabe214/study_marking_201503/issues/1/comments","events_url":"https://api.github.com/repos/swatanabe214/study_marking_201503/issues/1/events","html_url":"https://github.com/swatanabe214/study_marking_201503/issues/1","id":85556367,"number":1,"title":"指摘事項1 SQLの大文字・小文字","user":{"login":"TakamasaSugiura","id":7827645,"avatar_url":"https://avatars.githubusercontent.com/u/7827645?v=3","gravatar_id":"","url":"https://api.github.com/users/TakamasaSugiura","html_url":"https://github.com/TakamasaSugiura","followers_url":"https://api.github.com/users/TakamasaSugiura/followers","following_url":"https://api.github.com/users/TakamasaSugiura/following{/other_user}","gists_url":"https://api.github.com/users/TakamasaSugiura/gists{/gist_id}","starred_url":"https://api.github.com/users/TakamasaSugiura/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/TakamasaSugiura/subscriptions","organizations_url":"https://api.github.com/users/TakamasaSugiura/orgs","repos_url":"https://api.github.com/users/TakamasaSugiura/repos","events_url":"https://api.github.com/users/TakamasaSugiura/events{/privacy}","received_events_url":"https://api.github.com/users/TakamasaSugiura/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":2,"created_at":"2015-06-05T15:15:17Z","updated_at":"2015-06-16T13:22:50Z","closed_at":null,"body":"インスペクション会でも上がりましたが、\r\nSQLを読みやすくするために、SQLのキーワードを大文字、それ以外を小文字で書きます。\r\n大文字と小文字を分けるのは、キーワードとそれ以外を区別するためで、\r\n一般にSQLのキーワードのほうが出現機会が限られ(文中に占める割合が少ない)、かつ、\r\nキーワードが出てくる箇所が、SQLで注意して見るべき場所なので、このようにします。\r\n\r\n具体的に見てみましょう。\r\n例えば、以下のSQLがあったとします。\r\n```\r\nSELECT\r\n\tURIAGE_HEADER.DENPYO_BANGO,\r\n\tSHOHIN.SHOHIN_CODE,\r\n\tSHOKEI,\r\n\tSHOHIN_MEI,\r\n\tSHOHIN_RYAKUSHO,\r\n\tTANKA,\r\n\tURIAGE_BI,\r\n\tURIAGE_JIKAN,\r\n\tSURYO,\r\n\tGOKEI,\r\n\tZEI,\r\n\tSEKYU_GAKU,\r\n\tTANTOSHA_ID,\r\n\tTANTOSHA_MEI,\r\n\tTENPO_ID,\r\n\tTENPO_MEI,\r\n\tTENPO_JUSHO,\r\n\tCASE\r\n\t\tWHEN KYAKU_SEIBETU = 'F' THEN '女'\r\n\t\tWHEN KYAKU_SEIBETU = 'M' THEN '男'\r\n\t\tWHEN KYAKU_SEIBETU IS NULL THEN '不明'\r\n\t\tELSE '入力エラー'\r\n\tEND AS SEIBETU,\r\n\tCASE\r\n\t\tWHEN KYAKU_NENREI = 1 THEN '10代'\r\n\t\tWHEN KYAKU_NENREI = 2 THEN '20代'\r\n\t\tWHEN KYAKU_NENREI = 3 THEN '30～40代'\r\n\t\tWHEN KYAKU_NENREI = 4 THEN '50～60代'\r\n\t...\r\nFROM\r\n\tURIAGE_MEISAI, SHOHIN, URIAGE_HEADER, TENPO, TANTOSHA ...\r\nWHERE\r\n\tURIAGE_MEISAI.DENPYO_BANGO = URIAGE_HEADER.DENPYO_BANGO\r\n\tAND\r\n\tURIAGE_MEISAI.SHOHIN_CODE = SHOHIN.SHOHIN_CODE\r\n\tAND\r\n\t...\r\n```\r\n全て大文字で書かれているため、どれがSQLのキーワードかわからず、\r\nインデントされているにもかかわらず、ちょっと見にくいです。\r\n\r\n小文字でも同様。\r\n```\r\nselect\r\n\turiage_header.denpyo_bango,\r\n\tshohin.shohin_code,\r\n\tshokei,\r\n\tshohin_mei,\r\n\tshohin_ryakusho,\r\n\ttanka,\r\n\turiage_bi,\r\n\turiage_jikan,\r\n\tsuryo,\r\n\tgokei,\r\n\tzei,\r\n\tsekyu_gaku,\r\n\ttantosha_id,\r\n\ttantosha_mei,\r\n\ttenpo_id,\r\n\ttenpo_mei,\r\n\ttenpo_jusho,\r\n\tcase\r\n\t\twhen kyaku_seibetu = 'f' then '女'\r\n\t\twhen kyaku_seibetu = 'm' then '男'\r\n\t\twhen kyaku_seibetu is null then '不明'\r\n\t\telse '入力エラー'\r\n\tend as seibetu,\r\n\tcase\r\n\t\twhen kyaku_nenrei = 1 then '10代'\r\n\t\twhen kyaku_nenrei = 2 then '20代'\r\n\t\twhen kyaku_nenrei = 3 then '30～40代'\r\n\t\twhen kyaku_nenrei = 4 then '50～60代'\r\n\t...\r\nfrom\r\n\turiage_meisai, shohin, uriage_header, tenpo, tantosha ...\r\nwhere\r\n\turiage_meisai.denpyo_bango = uriage_header.denpyo_bango\r\n\tand\r\n\turiage_meisai.shohin_code = shohin.shohin_code\r\n\tand\r\n\t...\r\n```\r\nキーワードが埋もれてしまっています。\r\n\r\nそこで、SQLキーワードとそれ以外を大文字、小文字で区別するわけですが、\r\n大文字のほうが大きいため、目立ちます。強調できます。\r\n\r\nSQLキーワードを小文字にした場合(それ以外大文字)\r\n```\r\nselect\r\n\tURIAGE_HEADER.DENPYO_BANGO,\r\n\tSHOHIN.SHOHIN_CODE,\r\n\tSHOKEI,\r\n\tSHOHIN_MEI,\r\n\tSHOHIN_RYAKUSHO,\r\n\tTANKA,\r\n\tURIAGE_BI,\r\n\tURIAGE_JIKAN,\r\n\tSURYO,\r\n\tGOKEI,\r\n\tZEI,\r\n\tSEKYU_GAKU,\r\n\tTANTOSHA_ID,\r\n\tTANTOSHA_MEI,\r\n\tTENPO_ID,\r\n\tTENPO_MEI,\r\n\tTENPO_JUSHO,\r\n\tcase\r\n\t\twhen KYAKU_SEIBETU = 'F' then '女'\r\n\t\twhen KYAKU_SEIBETU = 'M' then '男'\r\n\t\twhen KYAKU_SEIBETU is null then '不明'\r\n\t\telse '入力エラー'\r\n\tend as SEIBETU,\r\n\tcase\r\n\t\twhen KYAKU_NENREI = 1 then '10代'\r\n\t\twhen KYAKU_NENREI = 2 then '20代'\r\n\t\twhen KYAKU_NENREI = 3 then '30～40代'\r\n\t\twhen KYAKU_NENREI = 4 then '50～60代'\r\n\t...\r\nfrom\r\n\tURIAGE_MEISAI, SHOHIN, URIAGE_HEADER, TENPO, TANTOSHA ...\r\nwhere\r\n\tURIAGE_MEISAI.DENPYO_BANGO = URIAGE_HEADER.DENPYO_BANGO\r\n\tand\r\n\tURIAGE_MEISAI.SHOHIN_CODE = SHOHIN.SHOHIN_CODE\r\n\tand\r\n\t...\r\n```\r\n\r\nSQLキーワードを大文字にした場合(それ以外小文字)\r\n```\r\nSELECT\r\n\turiage_header.denpyo_bango,\r\n\tshohin.shohin_code,\r\n\tshokei,\r\n\tshohin_mei,\r\n\tshohin_ryakusho,\r\n\ttanka,\r\n\turiage_bi,\r\n\turiage_jikan,\r\n\tsuryo,\r\n\tgokei,\r\n\tzei,\r\n\tsekyu_gaku,\r\n\ttantosha_id,\r\n\ttantosha_mei,\r\n\ttenpo_id,\r\n\ttenpo_mei,\r\n\ttenpo_jusho,\r\n\tCASE\r\n\t\tWHEN kyaku_seibetu = 'F' THEN '女'\r\n\t\tWHEN kyaku_seibetu = 'M' THEN '男'\r\n\t\tWHEN kyaku_seibetu IS NULL THEN '不明'\r\n\t\tELSE '入力エラー'\r\n\tEND AS seibetu,\r\n\tCASE\r\n\t\tWHEN kyaku_nenrei = 1 THEN '10代'\r\n\t\tWHEN kyaku_nenrei = 2 THEN '20代'\r\n\t\tWHEN kyaku_nenrei = 3 THEN '30～40代'\r\n\t\tWHEN kyaku_nenrei = 4 THEN '50～60代'\r\n\t....\r\nFROM\r\n\turiage_meisai, shohin, uriage_header, tenpo, tantosha\r\nWHERE\r\n\turiage_meisai.denpyo_bango = uriage_header.denpyo_bango\r\n\tAND\r\n\turiage_meisai.shohin_code = shohin.shohin_code\r\n\tAND\r\n\t.....\r\n```\r\n\r\n比べてみると、SQLキーワードを大文字にした方が、\r\nSQL文の区切りが分かりやすく見えると思います。\r\n\r\n@aa3232f04d55748a813cefeac5bcb0ea4d04a15f"},"comment":{"url":"https://api.github.com/repos/swatanabe214/study_marking_201503/issues/comments/112426126","html_url":"https://github.com/swatanabe214/study_marking_201503/issues/1#issuecomment-112426126","issue_url":"https://api.github.com/repos/swatanabe214/study_marking_201503/issues/1","id":112426126,"user":{"login":"TakamasaSugiura","id":7827645,"avatar_url":"https://avatars.githubusercontent.com/u/7827645?v=3","gravatar_id":"","url":"https://api.github.com/users/TakamasaSugiura","html_url":"https://github.com/TakamasaSugiura","followers_url":"https://api.github.com/users/TakamasaSugiura/followers","following_url":"https://api.github.com/users/TakamasaSugiura/following{/other_user}","gists_url":"https://api.github.com/users/TakamasaSugiura/gists{/gist_id}","starred_url":"https://api.github.com/users/TakamasaSugiura/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/TakamasaSugiura/subscriptions","organizations_url":"https://api.github.com/users/TakamasaSugiura/orgs","repos_url":"https://api.github.com/users/TakamasaSugiura/repos","events_url":"https://api.github.com/users/TakamasaSugiura/events{/privacy}","received_events_url":"https://api.github.com/users/TakamasaSugiura/received_events","type":"User","site_admin":false},"created_at":"2015-06-16T13:22:50Z","updated_at":"2015-06-16T13:22:50Z","body":"追記：出力結果は大文字のものが求められていますので、大文字にしてください。"}}