이제 본격적으로 통계 영역으로 한걸음 나아갈 때입니다. 데이터베이스로 데이터를 관리하는 여러 이유 중에, 저는 수많은 데이터를 엮어 의미 있는 지표를 만들어 내고, 또 그것을 시계열로 차트를 그려내어 데이터의 흐름을 판단하기 위한 것을 가장 중요하게 생각합니다. 그러기 위해서는 관점별로 수치 지표에 대한 roll-up 을 다룰 줄 알아야 합니다.
예를 들면서 roll-up에 대한 설명을 드리겠습니다. 아래 city 테이블에는 도시별로 인구수가 있습니다.
country 테이블에는 국가코드와 국가명이 있다고 했을 때 한국의 총인구수는 얼마나 되는 지 구해보겠습니다.
먼저 한국의 도시별 인구를 구합니다. 이게 되면 한국전체 인구를 구합니다.
한국 전체 인구수를 구하는 SQL입니다. 한국 기준이 때문에 우선 city 테이블의 컬럼은 select 항목에서 빠져야 합니다.
a.Code와 a.Name 은 국가코드와 국가명입니다. a가 가르키는 것이 country 테이블이죠. 그다음에 sum() 이라는 함수를 사용했습니다. 엑셀처럼 합계를 하는 함수입니다. 인자는 city 테이블의 Population 컬럼입니다. 따라서 국가코드, 국가명 명 기준으로 인구수를 합계해라라는 명령이 됩니다. 그런데 이 sum() 함수는 아래 group by 와 같이 써 줘야 합니다. 대부분은 select 에 컬럼과 동일하게 적어주면 됩니다. 결과를 보면, 3천9백만 명으로 나옵니다. 그냥 예제 데이 터니까 그러려니 하시길 바랍니다.
자, 그런데 사실은 country 테이블에도 Population 컬럼이 있습니다. 한번 확인해볼까요?
country 테이블의 Popluation 컬럼은 max() 함수로 구했습니다. max값은 데이터 중에 최댓값을 구한다는 뜻인데요, 만약에 그냥 sum()을 사용하면 인구수가 4천7백만 명 x 도시수만큼 나오게 됩니다.
왜 sum(a.Population) 을 쓰면 안 되는지 설명드리겠습니다. 물론 문법적으로는 문제가 없습니다. 데이터 값이 틀리게 되는 거죠. sum과 group by 부분을 빼고 실행하면 아래와 같이 결과가 나옵니다. country 테이블의 population 은 같은 데이터가 연속으로 나열된 것이 보이죠? sum()을 하게 되면 이 중복으로 나온 값을 다 합산을 하기 때문에 sum()을 쓰면 안 된다는 이야기입니다. max()는 이 중에서 가장 큰 값을 구하는데, 모두 동일한 값이 기 때문에 어떤 값이 나와와도 괜찮겠죠?
국가 전체 인구와 같은 경우 max()를 하지 않고 어떤 경우에는 관점으로 정의할 수 도 있습니다. select 항목 중에 alias 는group by에서는 빼줘야 합니다. 즉 as ~ 는 쓰지 않습니다.
자주 사용하는 통계 합수를 간단히 정리해드리겠습니다.
sum() : 합계
max() : 최대값
min() : 최소값
avg() : 평균값
count() : 건수
std(), stddev(), stddev_pop() : 표준편차. stddev() 가 오라클에도 사용되기 때문에 가장 많이 쓰입니다.
전 시간까지 각 데이타 유형별로 해당하는 function 사용법을 주제로 다뤘는데, 이번에는 데이터 유형을 변환하는 function에 대해 내용을 다뤄보겠습니다. 데이터 유형변환이라면 문자에서 숫자, 숫자에서 문자로 변화하는 것이 가장 일반적이고, 숫자 유형도 여러가지 있는데 그 유형별로 변화하는 방법이 있습니다. 날짜도 변환이라면 변환이겠지만 이전 주제의 날짜 function 에 이미 다 소개되어서 날짜 데이터는 #7을 참조하시길 바랍니다. 날짜는 딴게 없어요, 문자를 날짜로 혹은 날짜를 문자로, 그게 거의 대부분입니다.
▶ CAST()
데이터 타입을 바꿔주는 대표적인 function 입니다. 이자는 하나인듯 2개입니다. 보통은 인자들이 콤마(,)로 분리되기 때문에 몇개인지 명확히 말할 수 있는데 이 cast()는 데이터와 변환할 유형을 지정하는데 콤마(,)가 아닌 'AS' 가 중간에 들어갑니다. 사용방법을 간단한 예제를 보면서 익혀봅시다.
'2' 는 분명히 문자형 데이터입니다. 이것을 unsigned 형태로 바꾸게 되는데, 이 unsigned는 숫자형 데이터 유형 중, 부호를 없는 데이터만 쓸때 사용하는 형태입니다. 결과는 3이 나오는 것을 보니까 문자 '2'가 숫자로 변형이 된 것을 확인 할 수 있습니다.
그런데, 음수값을 unsigned로 바꾸면 이상한 값이 나오게 됩니다.
이 때는 signed 라는 부호를 관리하는 데이터로 사용해야 합니다.
같은 숫자 데이터 유형이라도 unsigned 는 부호를 관리하지 않기 때문에 표시할 수 있는 데이터가 signed의 2배입니다. 예를 들어, int는 -21억 ~ 21억 정도의 숫자를 표현할 수 있습니다. 이것을 unsigned로 표현한다면 2배인 42억까지 표시가 됩니다. 그런데 이렇게 아슬아슬하게 만들어 놓으면 문제가 생깁니다. 아니 문제가 안생기더라도 약간이나마 걱정이 생기게 됩니다. 그래서 현실에서 사용되는 정수는 bigint 하나로 끝입니다. 그럼 숫자의 범위는 잊으셔도 됩니다. 관리 가능 범위가 -9223372036854775808 ~ 9223372036854775807 인데 아주 특수목적이 아닌 이상 문제없습니다. 그런데 소숫점이하를 관리하고 싶다면 #4에서는 decimal을 써라고 말씀 드렸습니다. 이 부분을 한번더 짚고 가죠.
소수점 관리 데이터 유형은 부동소수점과 고정소수점 형태 2가지로 구분을 합니다. 제가 써라는 decimal은 고정소수점형태입니다. 관리할 데이터의 정수부, 소수점부를 정확하게 지정하고 그 범위만 관리하는 겁니다. 부동소수점은 아래와 같이 범위 데이터가 제한 적입니다. Float과 real 이라는 형태를 보면 정수, 소수점 모두 포함해서 표현되는 자리수가 제한 된다는 점을 알 수 있습니다. float은 소수점을 제외하고 총 6자리를 표현할 수가 있고 real은 그것보다 많습니다. 근데 오버하게 되면 공학용 숫자로 표시되는 것을 볼 수 있습니다.
위에서 봤듯이 단순한 용도 예를 들어 100점 만점 과목들의 평균을 구한다거나 할 때라면 float을 사용해도 무방하다고 볼 수 있지만 부동소수점 데이터는 엄밀히 말하면 정확한 값이 아닌 근사값입니다. 실제 좀 복잢한 계산을 하게되면 소수점 끝자리가 계산할 때마다 다르게 나오는 경우가 있습니다. 그래서 저는 부동소수점을 거의 사용하지 않습니다. 고정소수점 형식은 데이터를 정확하게 관리합니다. 부동소수점 분야가 필요한 곳이 있겠지만 저는 정확한 데이터만 원한는 범위까지 관리하는 목적이기 때문에 소수점 데이터는 decimal 하나만 사용하게 된 것입니다.
음 그런데, 굳이 cast()를 안써도 연산이 될 수 도 있다는 것을 알 수 있습니다. MySQL 버젼이 높아지면서 되는 것인지 잘 모르겠는데, 되더라도 잠시 확인하는 용도로는 상관 없겠지만 프로그램에 이렇게 사용하는 것은 피해주세요.
이제 날짜로 변환하기위해 cast()를 사용하는 방법을 보시지요. 날짜, 날짜시간, 시간 형식으로 변환할 수 있습니다. 참고로 as timestamp는 안되는군요. 그리고, 변환할 문자형식의 포맷을 지정하고 싶다면 str_to_date 함수를 지정할 수 있습니다. 이렇게 되면 cast() 보다 더 다양한 방식으로 날짜 데이터 유형으로 변환 할 수 있습니다. 다양한 방식이란 첫번째 인자 날짜형식을 마음데로 뒤죽박죽 넣어도 어떤 형식으로 받아들이면 되는지 format 값을 넣으면 알아서 인식한다는 것입니다.
Cast()를 이용한 데이터 변환 방법도 알아봤습니다. 마무리 하기 전에 간단한 내용을 하나만 더 추가하겠습니다. 데이터 타입을 변경하는 것이 아닌 간단한 데이터 자체의 변경도 있을 수 있습니다. 이 내용만 간단하게 소개하고 교육을 마치겠습니다.
▶ COALESCE()
NULL 값을 다른 값으로 대체. 혹은 여러개 항목 중에 왼쪽부터 시작해서 첫번째가 NULL값이 아닌 값을 구할 때 사용합니다. 처음 코딩 교육을 배우시는 입장에서는 이 NULL 의 개념이 있으실지 잘 모르겠군요. NULL 은 데이터가 없다라고 하기도 하고, 데이터 타입을 소개하면서 Oracle DB 를 언급하면 잠시 말씀 드렸던 varchar2 타입과 마찬가지로 길이가 0인 문자열을 의미하기도 합니다. 이런 NULL 값은 데이터를 다룰 때 꽤 성가십니다. 그래서 NULL 인 경우에는 다른 값으로 치환하는 것이 보통입니다. 그 때 사용되는 것이 이 coalesce() 입니다.
이것으로 데이터 변환 function에 대한 설명을 마치도록 하겠습니다. 분명 말씀 드리지만 데이터 변환방법은 이거보다 훨씬더 많이 존재할 것입니다. 자신에게 잘 맞는 것을 쓴 것도 중요하겠지만 코드를 봤을 때 누구나 쉽게 알 수 있는 몇가지로 개발을 쉽게 할 수 있다면 저는 그것이 최고라고 생각합니다. 수고하셨습니다.
이번에는 날짜와 시간에 관련된 function 사용법을 설명해 드리겠습니다. 개인적으로 느낀 점은 MySQL의 날짜/시간 function 사용법은 Oracle 보다는 DB2와 비슷하다는 느낌이 듭니다.
§ 날짜/시간 function 들
▶ 현재일과 시간 구하기 - now(), curdate(), curtime(), 그리고 currend_date(),current_time(), current_timestamp()
현재일과 시간을 구하는 구하는 함수들입니다. 현재일자만 구할 때는 curdate()를 사용하고 시간까지 구하고 싶을 때는 current_time()이나 now()를 사용합니다. 그리고 특이한 것이 curdate() +숫자를 적용할 수 있습니다. 이거 실수를 꽤하는 걸로 아는데, 날짜계산이 아닙니다. currdate() + 숫자는 yyyy-MM-dd 형태의 날자를 숫자형태로 yyyyMMdd 를 구합니다. 그냥 숫자에요 거기다가 + 숫자연산을 하는 겁니다. + 뿐만아니라 사칙연산이 다 가능합니다. 급하게 개발하다보면 연말이나 연초가 아닌 이상 값이 같아서 실수를 하게 되는데 날짜계산도 function을 사용합니다. Oracle 예기를 알할 수 없는데 Oracle은 현재일시를 sysdate 로 쓰는데 sysdate + 1 하면 이건 날짜 계산이 되서 내일이 됩니다.
▶ 날짜 시간 계산 - date_add(), data_sub(), adddate(), addtime()
날짜에 하루를 더한다던지 해외시간을 구하기위해 시차를 가감하기위한 날짜/시간 계산 function 들에 대해 설명을 드립니다. 우선 위에서 날자에 단순 산술로 숫자를 대입해서는 날짜계산이 안된다는 설명을 꼭 기억해 두시길 바랍니다. 일단위위를 가산하기 위해서는 date_add()를 쓰면 됩니다. 첫번째 인자는 대상일자가 되고 두번 째 인자는 가산할 일수를 넣습니다. 일수를 차감하기위해서는 data_add()를 쓰되 두번재 인자를 음수로 대입하거나 data_sub() 를 사용하면 됩니다. adddate() 함수도 있는데 이건 동의어라 date_add()와 같은 기능을 제공합니다. 일단위 뿐아니라 초단위, 그리고 아래 예제에는 없지만 밀리 초단위도 계산이 가능한 addtime() 이 있습니다. Addtime()을 쓸 때 가감할 시간을 넣을 때는 아래 예제에 나왔듯이 형식을 지켜주셔야 합니다.
날짜 / 시간 계산은 위에서 설명한 것다 좀더 복작하게 적용할 수 있습니다. 좀 복잡하게 느껴질 수도 있는데, 사실 자세히 보면 패턴이 같습니다. 일단위와 시분초는 interval 뒤에 문자 형으로 더할 값을 제시하고 위에 DAY_{HOUR, MINUTE, SECOND} 로 포맷에 맞게 지정하면 됩니다. 시작은 DAY로 하고 끝은 더할 시간의 끝 단위를 기술하는 거죠. MONTH, YEAR, WEEK 단위로도 적는 이 때는 시간단위는 못쓰는 걸로 알고 있습니다. 예제를 몇개 해봤는데 역시 안되더군요.
▶ 시간/일 차이 - timediff(), datediff(), 그리고 time_to_sec(), sec_to_time()
경과시간을 구하는 경우에 유용한 시간 혹은 일 차이를 구하는 function입니다. 주의하셔야할 것이 있는데 datediff()는 일단위로 계산합니다. 아래 차이3 예제 처럼 시분초를 넣어도 오류없이 시분초는 절사하고 계산해 버리기 때문에 개발실수를 모르고 넘어갈 수 있습니다. 시간까지 할려면 timediff를 쓰십시오. 그리고, 차이가 몇초인지 구하는 경우가 많을 겁니다. 차이4를 나오는 예제를 환산1은 초단위로 계산한 것입니다. 또 반대로 환산2는 초단위를 차이4번 예제 처럼 시분초 단위로 바꾸는 편리한 기능도 있습니다. 이걸 코딩에서 작업할려면 몇줄 코딩이 들어가야 하기때문에 SQL에서 하면 굉장히 개발에 소요되는 시간을 아낄 수 있을 것입니다.
▶ 날짜 포맷 출력 -date_format()
날짜를 원하는 형식으로 출력하기위해서는 date_format()을 사용합니다. 2번째 인자가 날짜를 어떻게 출력해 줄지를 결정하는 것인데 %알파벳으로 쓰는데, a~z 까지 다 있는 거 같아요. 공부삼아서 한번 해보는 것도 재밌겠는데, 구글링해서 date_format() 검색하면 자세히 나와 있으니까 필요한 것만 몇개 정리했다가 쓰시는 것도 괜찮겠습니다. 요일같은 경우에는 영문으로 나오는데 한글로 나오게 하기 위해서는 별도의 조치가 필요합니다. 이 별도의 조치라는 것이 MySQL 자체에서는 안되는 것으로 알고 있습니다. 제가 생각해둔 과정이 있으니 그 때 다시 설명하기로 하고 지금은 출력 가능한 형식이 어떤 게 있는지 보는 정도로 봐주시길 바랍니다.
아래는 자주 쓰이는 날짜 출력형식입니다. 물론 한국에서요. 외국은 출력을 좀 다르게 하는 거로 알고 있습니다. 미국만 봐도 날짜서수 월, 년도로 표현하잖아요. 따옴표 안에 %알파벳 외에 글자는 그대로 출력되는 것을 보실 수 있습니다. 이걸이용해서 원하는 출력형식을 마음대로 편집할 수 있습니다. 현실적으로 가장 많이 쓰이는 형식을 년도4자리-월-일과 시각까지 붙인다면 24시:분:초 형식이라고 봅니다. 24시:분:초 형식은 %T 로 간단하게 지정할 수 있습니다. 오전/오후 형식은 %r로 합니다.
날짜 형식을 다루는 여러가지 function을 다루어 봤습니다. 이것 외에도 사실 많습니다만 몇번 언급했다시피 자주 쓰는 편인 것만 정리했습니다. 혹시 싶은 것이 있으면 그때 구글링 하면 쉽게 정보를 구할 수 있습니다. 그만큼 MySQL은 참조할 곳이 무궁무진합니다. 이제 숫자, 문자, 날짜를 다루는 function을 한번 쭈~욱 훑어 봤습니다. 이제 다음 시간에는 조금더 상위과정으로 가보겠습니다. 수고하십시오.
지난 교육에 이어 이번에는 숫자 데이터에 사용될 수 있는 function에 대해 교육을 이어 가겠습니다.
§ 숫자 function 들
▶ 나머지 구하기 - %, MOD, MOD()
MySQL 에서 나머지 값을 구할 수 있는 구문이 3가지가 있습니다. 보통 편한대로 쓰면 되겠지만, 저는 가급적 다른 DB에서도 호환되는 SQL을 쓰라고 권하고 싶습니다. 그리고 몫도 구할 수 있는데 그건 나누기를 하면 되는데 0으로 딱 떨어지는 경우에는 문제가 없겠지만 소수점이 나와버리는 경우도 있으니까 소수점이하를 절사해줄 수 있는 function을 써야 합니다.
▶ 절사 함수 - truncate()
위에서 몫을 구하기 위해 언급했던 truncate() 입니다. 아래는 4가지를 경우로 들어 설명을 드립니다. 가장 많이 쓰는 게 소수점 이하 절사입니다. 반올림이 아니라 버림입니다. 1은 소숫점 첫번째 자리수의 절사, 2는 소숫점 두번째 자리의 절사, 3은 세번째 자리의 절사를 의미합니다. 음수도 쓸 수 있는데 -1 은 1의 자리수를 절사하고 -2는 10의 자리수를 절사합니다. 그런데 여기서 아주 주의해야 할 것이 있습니다. Truncate는 다른 쓰임새도 있습니다. 바로 truncate 테이블명 으로 테이블에 있는 데이터를 싹 지워버리는 기능으로 사용할 수 있습니다. 데이터의 절사 겠네요. 게다가 이렇게 지우면 rollback 이라 해서 복구를 할 수 없기 때문에 주의를 기울여야 합니다만, select 와 같이 쓰는 경우에 그럴 일이 없으니 안심하세요.
▶ 반올림 함수 - round()
절사가 있으면 반올림도 있겠지요. 바로 round() 인데 두번째 인자가 truncate()와 동일합니다.
▶ 천정값 - ceil(), ceiling()
소수점 아래 평등한 값, 바로 소수점이 1일이라도 있으면 정수값을 올릴 때 ceiling()을 씁니다. ceil()은 synonym 으로 동일한 기능을 수행합니다.
▶ 바닥값 - floor()
천정값이 있으면 바닥값도 있습니다. floor()을 씁니다. 그런데 앞서 truncate() 와 자칫 중복될 수도 있는데 유의하셔야 합니다. 음수일 때 바로 차이가 납니다.
▶ 제곱 - pow(), power()
제곱을 구하기위해서는 pow()와 power()를 사용합니다. power()는 pow()의 synonym 입니다. 두번째 인자가 제곱횟수 가 됩니다.
음수제곱도 쓸 수 있습니다. 왠만한 현실에서 쓸일이 있을지 모르겠군요. 수학을 놓으신지 오래되신 저와 같은 분들이 있을지 몰라서요, 음수제곱의 계산하는 방법은 다음과 같습니다.
▶ 제곱근 - sqrt()
반대로 제곱근을 구할 대는 sqrt()를 상용하면 됩니다. 루트 2는 1.414, 루트 3은 1.732 이건 수포자인 저도 압니다.
▶ 절대값 - abs()
음수부호를 뗀 값을 구합니다.
▶ 천단위 구분 - format()
천단위 콤마 있는 서식으로 출력하기 위해서는 format() 을 사용합니다. 고정 소수점 포맷을 만들 때는 엑셀처럼 적용이 안되니 주의하셔야 합니다.
MySQL 숫자 function에서 제가 그나마 쓸 만한 것들만 추려봤는데요, 사실 실무에서 사용하는 Oracle에서는 쓴 유용한 것이 MySQL에서는 없는 것인지 못찾겠습니다. 그리고 삼각함수, Log, 자연상수 등 좀더 복잡한 것도 있는데 그건 어느 정도 MySQL에 익숙해 지시면 구글링으로 직접 찾아 보시길 권해 드립니다. 제가 아는 걸 설명해 드리는 취지에 어긋나서 적는 것을 포기했습니다.
Data type도 어느 정도 설명이 끝났으니 이제 함수 사용법을 익힐 시간이 도래한 것 같습니다. 여러분은 data를 한글로 표기힐 때 '데이터' 라고 표기하나요 아니면 '데이타' 라고 표기하나요. '데이타'라고 발음하면서 굳이 '데이터'라고 쓰는 게 너무 거슬려서 저는 '데이타'라고 쓰는 편인데 이게 맞춤법 검사에서는 '데이터' 로 표시해야 하기 때문에 그냥 data라고 영어로 씁니다. 앞으로 짧은 영어는 그냥 영어로 표시하겠습니다. 함수도 그냥 이하 function으로 표기하겠습니다.
Function은 인자값을 넣거나 혹은 인자값 없이도 결과값을 받을 수 있는 기능을 일컬어 말합니다. 수학시간에 배우신 f(x) = ... 여기에 f가 function 이고 x 가 인자입니다. 아래는 f(x) = x+5 를 그림으로 표현해 봤습니다. 이 function 이름을 plus5() 라고 한다면 SQL 에서는 select plus5(1) from 테이블 이라고 실행하면 결과가 6 이 나오게 되는 겁니다.
DB 마다 function 종류가 꽤 많을 거라고 생각합니다. 개발자도 주력으로 사용하는 것은 몇가지 안될 것입니다. 그래도 어떤 게 필요하다고 생각되면 쉽게 구글링해서 검색해서 찾아냅니다. 그만큼 오픈되어 있고 참고할 곳도 많기 때문에 굳이 다 외울 필요는 없다고 생각합니다. 그래서 제가 많이 쓰는 것 기준으로 설명해 보겠는데, DB마다 같은 기능이지마 function 명과 사용법이 다릅니다. MySQL과 Oracle 문법으로 작성해 보겠습니다.
§ 문자열 function 들
▶ 문자열 연결 - concat(문자열1, 문자열2, ....)
아래 SQL은 문자열 연결에 사용되는 concat() 의 사용법을 예제로 보여 드리고 있습니다. 연결할 문자열 수에 상관없이 콤마로 계속 붙여 쓸 수 있습니다. 그리고 테스트3을 봤을 때 문자와 숫자도 연결해서 문자열로 만들 수 있는 것을 알 수 있습니다. SQL 컬럼에 as 컬럼명으로 해서 제가 지정한 컬럼명으로 보여줄 수 있습니다. 이것을 alias 라고 합니다. Alias는 컬럼 뿐만아니라 테이블명에도 사용할 수 있습니다. 다만 테이블에는 as 를 쓰지 않고 테이블명 다음에 공백을 주고 alias 문자를 씁니다. 이건 SQL 예제 난이도가 높아지면서 자연적으로 알 수 있을 것입니다.
Oracle 에서는 문자열 연결을 || 로 연결해서 사용합니다. 이걸 파이프(pipe) 라고 말하기도 하고 바바(bar bar) 라고 하기도 하더군요.
※ Oacle 문자열 연결 문법
▶ 문자 자르기 - substr(), substring()
문자열 자르기 위해서 substr()을 사용합니다. MySQL에서 정식은 substring() 이지만 동의어로 substr() 을 사용할 수 있습니다. Oracle에서 워낙 substr()을 많이 써서 synonym을 만들어 둔게 아닐까 싶습니다. 뇌피셜입니다. 쓰는 법은 substr(작업대상 문자열, 시작위치, 몇글자) 입니다. 끝에 몇글자를 생략하면 시작위치부터 끝까지를 결과로 반환합니다. 그리고 시작위치를 음수도 넣을 수 있는데 음수를 넣으면 문자열 끝에서부터 자를 수도 있습니다. 아마 SQL을 사용하게 되면 무궁무진하게 응용하시게 될 겁니다.
▶ 문자 위치 - instr()
특정 문자열의 위치를 찾을 때는 instr(작업대상 문자열, 찾을 문자) 를 사용합니다. 아래 예제를 보면 4가 나옵니다. A문자열의 위치를 1로 보고 위치를 구한 거네요. 코딩을 배우게 되면 첫번째 위치를 1이 아닌 0으로 생각해야 할 경우가 많다보니 instr() 같이 문자열 위치를 찾을 때는 몇번 테스트 후에 개발하게 됩니다. 그리고 아래 예제에 보면 from 테이블이 없죠? MySQL은 dummy 한 건을 보이는 용도로 from 테이블을 생략할 수 있습니다. Oracle 에서는 from dual 이라고 써야 합니다.
※ Oracle에는 from dual로 씁니다. MySQL도 가능합니다.
응용해서 이런 것도 해볼 수 있습니다. 실제 많이 쓰는 건데요, 알파벳 'D' 이후 문자열을 출력하고 싶을 때 substr() 과 combo로 사용해서 해결할 수 있습니다.
추가로 'D' 부터 'F' 까지 출력도 가능하겠죠.
▶ 문자열 공백제거 - trim(), Ltrim(), rtrim(), replace()
문자열에 공백이 있을 때 공백을 제거해 줘야 할 경우 있습니다. Trim()은 문자열의 시작과 끝에 존재하는 공백을 다 제거합니다. Ltrim()은 완쪽 즉, 문자열 앞 부분에 존재하는 공백만 제거합니다. 뒷편은 공백이 남게됩니다. Rtrim()은 반대로, 오른쪽 공백만 제거합니다. 그리고 글자 중간에 문자열이 있을 때는 trim() 으로 제거 할 수 없기 때문에 문자열을 바꾸는 함수인 replace()를 사용합니다. Replace(작업할 문자열, 바꿀 대상문자, 변경할 문자) 로 사용하는 데 적고 보니 전달이 잘 안될 수도 있겠다는 생각이 듭니다. 작업할 문자열에서 바꿀 대상문자를 찾아서 변경할 문자로 바꾼다는 의미입니다. 아래에 보면 ' '를 찾아서 '' 로 바꾼다는 의미로 결구 공백을 모두 제거한다는 뜻입니다. 이 함수들은 Oracle과 문법이 같습니다.
▶문자열 채우기 - LPAD(), RPAD()
문자열을 왼쪽이든 오른쪽이든 채우는 기능입니다. 방향만 Left, Right에 따라 LPAD(), RPAD()로 씁니다. LPAD(작업대상 문자열, 총자리수, 채울 문자) 로 사용합니다. 총자리수보다 작업대상 문자열 수가 작을 때 fuction 명에 따라 왼쪽이든 오른 쪽이든 채울 문자열로 덧붙이는 기능입니다. 채울문자는 단 문자도 되고 2개이상 문자열도 가능합니다.
이제 데이터의 유형에 대한 설명을 드리겠습니다. 데이터는 특수한 것을 제외하고는 문자, 숫자, 날짜 이 3가지 입니다. 특수한 것으로 참/거짓을 관리하는 boolean, 이미지 등이 있는데요, 대부분 문자, 숫자, 날짜만 잘 다루면 일하는 데 큰 불편함이 없을 것입니다. 데이터 변환 함수는 결구 문자와 숫자간의 데이터 형태 변환 혹은 문자와 날짜의 변환을 하는 부분, 문자간 연결, 문자열 자르기 등이 있습니다.
MySQL을 실행합니다. Tray영역에서 MySQL80 서비스를 실행 합니다.
그리고 MySQL Workbench를 실행 합니다. 아래 tray 메뉴에서 SQL Editor... 를 클릭해서도 실행 됩니다.
우선 기존 테이블에서 데이터 유형을 확인해 보겠습니다. 이미 우리는 데이터 유형을 많이 봐왔습니다. world DB를 사용하겠습니다. city 테이블 컬럼을 확인 합니다. 데이터 유형은 컬럼단위로 정해 집니다.
컬럼 정보보기 명령어는 desc 테이블명 입니다. Desc는 describe 의 약어입니다.
Type 열이 데이터 유형을 의미합니다. ID와 Population 컬럼은 int 이고, 나머지 컬럼은 char(숫자) 형태입니다. int란 정수형이라는 의미입니다. 정수는 0을 포함한 음수, 양수가 모두 포함되지만 소수점이하가 없는 숫자형 입니다. Name 컬럼은 char(35) 로 되어 있는데 char 이 문자열을 의미하고 괄호 안에 숫자는 문자열 최대 허용길이를 의미합니다. 그러니까 최대 35 바이트까지 관리될 수 있습니다. 영어 알파벳이나 숫자 마침표, 쉼표등의 기호는 1 바이트이 므로 35개 문자열이 저장됩니다. 그러나 한글은 멀티바이트입니다. 그래서 35자리만큼 못쓰는데 저는 보통 계산할 때 4바이트로 환산해서 길이를 산정합니다.
데이터 타입을 좀더 자세하게 기술해 볼까 하다가 어차피 다른 웹사이트 내용 내용을 베낄꺼면 쓰지 않는 편이 좋겠다 싶어서 제 기준으로 정말 자주 쓰는 것만 요약하겠습니다. 인테넷에서 mysql 데이터 타입 혹은 mysql data type 라고 검색하시면 훨씬 더 전문적인 내용이 나오니 참조하시길 바랍니다.
문자 데이터타입에는 char 이란 것 외에 varchar 라는 것이 있습니다. 문자열은 varchar와 char 만으로도 99% 이상 해결됩니다. 실제 작업하다 막히면 저도 인터넷에서 자료형을 검색해서 문제를 해결해 갑니다. 머리속에 DB별로 자리수의 최대 허용범위를 다 외우질 못하거든요. 예를 들어 문자열이 4000자 이내까지는 varchar로 해결하고 그 이상은 text, long text 가 있다는 것을 검색해서 썼을 겁니다. Char와 varchar 에는 차이가 있습니다. Char(35) 로 선언되면 그 컬럼에 데이터가 있던 없던간에 35 바이트를 기록하는 데 사용합니다. 반면에 Varchar(35)로 되어 있으면 데이터가 없으면 사용하지 않습니다. 물론 저도 자세한 메커니즘을 모르기 때문에 단 1바이트도 안쓰는지는 모릅니다만 이해하기 쉽게 기록할 만큼만 사용한다고 알고 있습니다. 4000 자리 처럼 넉넉하게 만들 거면 char를 써서 데이터 공간을 낭비하지말고 입력한 만큼만 디스크 공간을 사용하도록 varchar를 사용하는 게 좋겠죠? 극단적으로 제가 일하는 곳에서는 문자는 varchar만 사용합니다. Oracle 에서는 문자열을 대부분 varchar2 를 사용합니다.
참고로 Oracle에서 varchar와 varchar2 의 차이점을 기술하고 있는데, 인터넷에 있는 글을 발췌했기 때문에 정확하게 맞다 아니다라고 말씀을 못드리겠습니다만 내용을 보면 NULL과 empty 를 동일취급히고, varchar말고 varchar2를 써라고 하는 군요. 실제 Oracle DB로 구축된 사이트에서 일반 문자열 컬럼은 varchar2 만 사용했습니다.
※ 출처 www.thecrazyprogrammer.com
숫자 타입은 숫자의 크기와 부호, 소수점 등에 따라 종류가 많습니다. 저는 여기에 일일이 기술하고 정리하고 싶지 않습니다. 현실적으로 DB를 설계할 때 대략적으로 어떤 수치가 들어올지 혹은 단위가 뭔지는 알지만 앞으로 어느 정도까지의 데이터가 관리될 지 장담할 수 있는 경우는 드뭅니다. 그리고 숫자 타입 컬럼 별로 데이터 유형을 다르게 해두면 컬럼간의 사칙연산이나 계산에 지장을 주게 됩니다. 그래서 앞서 문자를 varchar로 거의 통일해서 쓰는 것 처럼 숫자도 거의 통일 해서 쓰는 편입니다. 나중에 데이터 타입 변경하는 리스크를 안고 가는 것 보다는 디스크 용량 좀더 잡아 먹는 게 훨씬 더 유리힙니다. 저는 숫자 타입에서 bigint, decimal 2가지만 사용합니다. 소수점이 없는 정수는 bigint, 소스점이 필요한 경우 정수와 부동소수점 자리를 지정할 수 있는 decimal을 사용합니다. 나머지는 좀더 익숙해 지면 저절로 아시게 될 거라고 생각합니다. 이해를 돕기 위해 언급한 2개의 숫자 타입을 잠시만 설명 드릴 께요. 직접 test 라는 테이블을 만들었고, 컬럼은 a, b 2개입니다. a는 bigint, decimal 은 (5,2)로 생성했습니다. Bigint 자리수 20은 저절로 잡히는 거고, decimal의 5,2는 직접 정해줘야 합니다.
제가 a 컬럼에는 10.1 을, b 컬럼에는 55555.222 를 입력해 보았습니다. 일단 오류 없이 입력은 되었습니다. 입력된 결과를 조회해 보면 a 컬럼에 소수점 0.1 은 사라지고, b 컬럼은 999.99 로 입력되어 있습니다. a 컬럼은 이해 되실 겁니다. bigint 는 정수만 관리하기 때문에 소숫점을 빼고 정수만 입력되었습니다. 문제는 b컬럼인데, 선언을 decimal(5,2) 로 선언했는데 이 뜻은 앞에 5는 정수부와 소숫점부 자리수 모두 합친 자리수를 의미하고 끝에 2는 소수점부를 의미합니다. 따라서 정수부는 5-2=3 자리만 관리가능합니다. 입력한 값이 관리되는 최대값 999.99를 넘어서서 값이 저렇게 들어가는 겁니다.
값을 다시 입력했는데 a 컬럼에는 10000, b에는 123.45를 입력했습니다. 결과를 조회해보니 입력한 값대로 정상적으로 출력되는 것을 확인할 수 있습니다. 다시 말씀드리는데, 숫자 타입의 데이터 유형이 많이 있지만 각 유형별 제한사항을 외우기 번거롭고 또 서로 다른 유형의 숫자끼지 사칙연산을 사용하면 의도치 않은 결과가 나올 위험성이 있기 때문에 decimal 로 통일해서 씁니다.
이제 날짜 타입의 데이터 유형에 대해 알아보겠습니다. MySQL에서 제가 주로 쓰는 날짜 함수는 딱 3개입니다. 첫번째 date 는 년-월-일로 1000-01-01 ~ 9999-12-31 까지 관리가능합니다. 두번재 datetime 은 덧붙여 시분초까지 관리됩니다. 그리고 세번째 timestamp 가 있습니다. 이것도 시분초까지 관리됩니다. 그럼 datetime과 timestamp가 같은 건가 봤을 때 쓰는 용도에 따라 같을 수도 있습니다. 그러나 timestamp는 1970년 ~ 2038년까지의 데이터를 관리합니다. 엄밀히 말하자면 timestamp는 1970년 1월 1일 부터 걸린 시간을 숫자로 관리합니다. 그런데 Timestamp는 아주 유용한 기능이 있습니다. 데이터를 입력하거나 자동으로 입력된 시점의 시간을 남길 필요가 있습니다. 즉, 자동으로 시간을 입력해 주는 기능이 있습니다. 아래 테이블 컬럼의 예제를 보면 입력시간 컬럼이 timestamp 타입이고, default 에 보면 CURRENT_TIMESTAMP 로 되어 있는 것을 볼 수 있습니다. 이 말은 데이터를 입력할 때, 입력시간 컬럼 값은 자동으로 현재 시간이 들어간다는 뜻입니다. 그리고 여기 현재시간에는 또 다른 중요한 것이 포함되어 있는데 바로 UTC (세계협정시) 로 관리 된다는 의미입니다. 한국 시간으로 시차가 적용된 것은 time zone 이란 것이 보통 기본적으로 system으로 설정되어 있어 OS 에 설정된 시간대로 시차를 적용해서 보여주고 있기 때문에 마치 한국시간이 입력되어 있다는 착각이 들기도 합니다.
※ 기본으로 현재 시간을 입력하는 timetamp 컬럼이 적용된 사례
위 설명만으로 timetamp에 대한 이해가 되셨다고 생각이 되진 않습니다. 그래서 재밌는 실험을 하나 보여드리도록 하겠습니다. 실험은 current_timestamp가 기본 입력된 test 테이블을 만들고 조회해서 몇시에 데이터가 입력되었는지 확인 한다음, 윈도우 시간설정을 런던으로 봐꾼 후 다시 데이터를 조회해서 시간이 어떻게 조회 되는지 보는 것입니다.
이제 시간대를 UTC 런던으로 바꿉니다.
MySQL을 Restart 합니다. 변경된 시간대를 적용할려면 DB를 재가동 시켜야 합니다.
다시 test 테이블을 조회합니다. 재가동 되면서 인증을 다시 해야 합니다. 좀전에 23시 였는데 지금은 14시로 보입니다. 9시간 차이가 나는데, 분명 데이터를 변경하지 않아도 이렇게 보인 다는 겁니다. 즉, 실제 데이터는 세계 협정시 기준으로 저장되지만 시스템의 시간대 즉, time zone에 따라 시차를 적용해서 결과가 보인다는 겁니다. 이게 timestamp의 가장 큰 특징입니다. 향후, 다국적 시스템을 만들 때 이 부분이 유용하게 사용될 수도 있습니다.
※ 세계 협정시로 바꾸고 다시 조회
사실 DB 4번째 교육만으로 이런 부분이 이해가실지는 잘 모르겠습니다. 그래도 너무 완벽하게 이해할려고 애쓰시기 보다 대충 감을 잡고 따라오시는 것을 권고 드립니다. 솔직히 말씀드리자면 UTC가 모르는 개발자도 수두룩합니다. 제가 넌즈시 같이 일하는 분들한테 물어봤는데 아무도 모르더군요. 경험으로 봤을 때 실적일자나 실적발생일시같은 경우는 date나 datetime을 씁니다. Audit 항목이라 해서 언제 입력되었는지 혹은 수정되었는지는 감시하기 위한 목적으로는 timestamp를 썼습니다. 그리고 날짜 타입을 쓰지 않고 varchar(8) 로 yyyymmdd 타입을 쓰거나 varchar(14) 로 yyyymmddHHmiss 형태을 문자열로 관리하는 경우도 있었습니다.
이번 시간도 수고하셨습니다. 저도 전체 데이터 타입을 모두 숙지하는 것은 아닙니다. 제가 잘알고 있는 몇가지로 대부분 해결 가능하고 실제 업무에서도 사용가능한 데이터 타입을 최소화 시켜서 단순화 시킵니다. 복잡하면 다른 DB로 마이크레이션 하기가 어렵거든요. 다음 시간에 뵙겠습니다.
이번 시간에는 SQL로 데이터를 조회할 때, 두 테이블간의 join 하는 유형에 대해 교육을 해 보겠습니다. 테이블별로 존재 데이터를 서로의 공통점을 이용하여 나란히 데이터를 출력하게 하기위헤서 join을 사용합니다. 왜만한 데이터 추출용 SQL에는 거의 대부분이 join을 이용해야 하기 때문에 반드시 알아두어야 합니다. Join을 할 대상 테이블에 데이터가 어떤 식으로 존재하는 지 사전에 숙지를 해야 합니다. 잘못된 join은 문법오류가 나면 차라리 다행이지만 문법이 맞기 때문에 수행이 되어 버리면 DB 성능이 굉장히 저하되는 물리적 현상과 더불어 잘못된 데이터가 산출되기 때문에 데이터 신뢰성에 문제가 발생하게 됩니다.
먼저 join 할 대상이 되는 테이블에 대해 알아보기로 합시다. 우선 DB 서비스를 기동시킵니다. 이전에는 windows service 관리자를 실행시켜서 시동했는데, Task바 오른쪽 시계나오는 부분에 나오는 Tray 영역을 통해서도 서비스를 기동시킬 수 있습니다. 메뉴 전체를 캡쳐가 안되서 아이콘만 캡쳐했는데 빨간색 돌고래 아이콘에 잘보면 동그라밍 아래 빨간색 상자 즉, 정지 모습이 보입니다 .이 아이콘을 오른 쪽 클릭하면 MySQL80 Stopped 오른쪽 확장메뉴를 누르면 Start 메뉴가 보이는데 그걸 클릭하면 빨간색이던 아이콘이 흰색으로 보이고 작은 동그라미 안에도 초록색 삼각형으로 바뀝니다. 글로는 뭐 복잡한데 아래 이미지를 보고 한번 해보시면 어려운 것이 전혀 아닙니다.
MySQL Workbench 를 실행합니다. 저는 Windows 8을 사용하고 있기 때문에 아래와 같이 검색해서 찾아 실행합니다.
아래 Local instance MySQL80 을 클릭합니다.
Join은 4가지를 사용합니다. 이외에도 더 있지만 현실적으로 이 4가지외에는 사용한 적이 없습니다. 4가지는 inner join, left outer join, right outer join, full outer join 입니다. 대소문자는 신경 안쓰셔도 됩니다. 사실 inner join 이냐 아니냐로 구별합니다. 첫번째, inner join 입니다. Inner join은 join 하는 테이블 간에 연켤 컬럼에 동일한 데이터가 존재하는 경우만 데이터가 추출 됩니다. Inner join은 Inner를 빼고 join만 써도 됩니다. SQL을 작성하다보면 줄맞추기가 중요합니다. 줄을 잘 맞춰야 해석하기가 용이하거든요. 이 줄 맞추기 때문에 저는 inner를 잘 안쓰는 편입니다.
두번째 Left outer join 은 위의 예제로 보면 city 에 데이터가 없더라도 빈값으로 데이터를 출력할 때 필요합니다. 아래 SQL을 보면 country 와 city를 join하는데, city는 KOR 을 제외하고 join 합니다. 그리고 최종조건에는 country테이블의 Code가 KOR 인 것만 추출합니다. 이렇게 inner join을 하는 경우에는 데이터가 추출이 되지 않습니다. Inner join할 때는 2개 테이블간에 on 구문으로 join 한 컬럼에 동일한 값이 존재 해야만 조회조건이 달성됩니다.
이 join을 left outer join으로 바꿔보면 아래와 같이 결과가 나옵니다. Join하는 테이블인 city 의 왼쪽에 기술된 테이블인 country 기준으로 비록 city 에 해당되는 값이 없더라도 NULL 값으로 출력이 됩니다. DB 조회 툴마다 차이는 있는데 어떤 툴은 NULL 표시 자체없이 빈공간으로 나오기도 합니다. NULL 이란 아무 값도 없다는 뜻입니다. 결과에서 왼쪽 Code, Name 컬럼은 country 테이블에서 읽은 것이라 데이터가 있지만, city 테이블에서 읽는 Name과 Population은 애초에 join할 때 KOR을 제외하고 읽은 거라 출력할 수 있는 게 없습니다.
세번째 right outer join은 left outer join과 순서가 반대입니다. 아래 예제는 좀 어렵게 느껴지실 수도 있습니다. 자, Right outer join 이기 때문에 데이터 출력은 오른쪽에 기술된 city 테이블 기준으로 확장해서 출력됩니다. Join 조건에 country 테이블의 code <> 'KOR' 조건으로 한국은 빼고 country 테이블 데이터를 읽을 겁니다. Where 조건에서 city 테이블에서 city.Countrycode = 'KOR' and city.Name = 'Seoul' 인 조건으로 서울데이터만 읽어오게 읽어옵니다. 결과적으로 country 테이블에서 일거오는 Code와 Name은 NULL 값 즉, 빈 값으로 출력되고 city 테이블 값만 출력됩니다.
예제 SQL이 좀 꼬아놓은 거라 이해가 잘 안가실 수도 있습니다. 저도 SQL을 작성할 때 왼쪽에서 오른쪽으로 습관으로 인해 right outer join은 잘 안씁니다. 그리고 한 SQL 내에서 left otuer join과 right outer join을 섞어 쓰지 않습니다. SQL을 머리속으로 해석하며 데이터 처리과정을 상상할 때 상당히 어려워지고 결국 실수로 이어질 겁니다. 그래서 실제론 inner join과 left outer join 만으로도 충분히 문제를 해결할 수 있습니다.
4번째로 full outer join 이 있습니다. 이건 join 한 두 테이블간에 서로 없는 컬럼까지 포함해서 출력하는 것입니다. 안타깝게도 MySQL 에서는 full outer join 문법을 지원하지 않습니다. 아래 그림으로 우선 개념만 이해하시길 바랍니다.
예를 들어 아래와 같이 타짜 1편과 2편 출연진 테이블이 있다고 봅시다.
< 예상 결과 >
왼쪽은 타짜1편 출연진이고 오른쪽은 2편 출연진입니다. 타짜와 타짜2 모두 출연한 배우는 나란히 옆에 출력되지만, 어느 한 쪽만 출연한 배우는 옆에 NULL (빈 값)이 출력됩니다. 위에서 봤던 left outer join과 right outer join 이 모두 반영된 결과 입니다.
마지막으로 self join과 cross join 이란 것도 있습니다. 제 생각에는 엄밀히 말하자면 self join 이란 것은 위에서 분류하는 join의 분류 기준과는 다릅니다. 동일한 테이블을 별명(alias)만 다르게 해서 join하는 것이지만 실제 문법은 inner join 이나 left outer join등을 씁니다. 이 부분은 다음 교육 과정에서 설명할 테니 너무 신경쓰시지 않으셔도 됩니다. cross join은 의도적으로 cartesian 을 일으키는 것인데 결국 경우의 수를 모두 만드는 것입니다. Join할 때 on 절에서 join 컬럼을 지정하는데 cross join 에서는 on 절을 안씁니다. 그럼 중학 수학에서 배웠던 행렬의 곱처럼 경우의 수를 모두 구하는 것과 비슷합니다. Cross join을 지금 시점에서 의도적으로 배울 필요는 없다고 생각합니다. SQL을 작성하다보면 실수로 의도치 않게 cross join을 일으키게 됩니다. 따라서 join 은 위에서 말한 inner join과 left outer join, right outer join, full outer join 만 보시고 나머지는 이번 글을 읽고 넘어가시길 권고드립니다.
이번 시간도 수고하셨습니다. MySQL80을 시작했을 때와 반대로 Stop 하시길 바랍니다.
지난 교육의 내용을 아래의 이미지 한장으로 표현할 수 있겠습니다. 저작권 때문에 직접 그린 것입니다.
이전 교육과정에서 MySQL DB를 설치하였습니다. 다시 말씀드리지만 SI 개발자에게 DB 활용능력은 절대적으로 필요합니다. 제가 SI 에 대한 설명을 드리지 않았습니다. 네이버에 "SI 개발" 로 검색해보면 좀 비참한 현실을 그린 내용이 나옵니다. System integration 의 약자로 시스템 통합 개발을 의미하겠죠? 1990년대 말부터 각 단위 기능별로 만들어져 있는 시스템을 통합개발해서 업무효율을 높이기위해 개발 프로젝트를 정말 많이 했더랍니다. 그 시절에는 MSG 조금 뿌려서 이력서에 활용가능한 언어로 "J(ava)" 라고 J만 써도 바로 반강제로 취업되던 시절이었습니다. 수시로 PM, PL 들이 와서 친구들 없냐고, 연락되는 사람 대우 좋으니까 빨리 오라고, 혹시 다른 데 취업해 있더라도 이쪽으로 오게 할 수 없냐고 지겹도록 들었습니다. 그만큼 고생도 많았습니다. 주말이 뭐에요? 아~ 아침 10시까지 출근할 수 있는 날이요? 라고 쉬는 날이란게 거의 없었죠. 저는 그나마 대기업 하청업체에서 일했기 때문에 일요일에는 쉬었습니다. 퇴근은 오늘 안에 할 수가 없었습니다. 일찍 갈 수 있는 날에는 또 술마시러 끌려가야 했거든요. 고생도 많았지만 정말 재미있었습니다. 아쉬운 것은 그 때 제가 초짜여서 급여를 많이 못받았습니다. 그리고 빨때 꽂은 사람들이 많아서 좀 많이 뜯겼었죠. 그 바닦이 좀 그랬어요. 첨부터 좋은 회사에 갔으면 그럴 일 없었겠지만 뭐 능력이 안되서 어쩔 수 없는 노릇이었죠. 그런데 요즘은 이 SI 라는 의미가 그냥 개발자 중에서도 막노동 급 단계로 취부하는 경향이 있습니다. 어감은 좀 그렇지만 다른 시각으로 보면 개발방법이 그 만큼 표준화 되어 있어서 어느 수준의 개발능력을 가진 사람들이라면 어디 가서든 일을 맡아서 할 수 있다는 의미로도 해석되니 쉽게 말해 돈 벌 수 있는 채널이 무궁무진하다고 좋게 생각할 수도 있습니다.
SI 개발자로 취업활동을 하려면 어느 정도의 소양이 되어야 하는가가 궁금하실 수도 있을 겁니다. 과연 내가 어는 정도까지 알아야 개발업무에 투입될 수 있을까? 저는 그 시작을 DB 활용능력이라고 봅니다. DB 활용능력을 다시 말하면 얼마나 SQL 구현을 자유자재로 할 수 있냐, DB 구조를 보고 데이터가 어떻게 관리되고 활용하려면 어떤식으로 사용해야 겠구나라고 인지할 수 있으면 이미 상당한 수준이 되신 겁니다. 그런데 이게 막상 어렵거나 그런게 아닙니다. 지금 부터 그 과정을 무리가 가지 않는 수준에서 설명을 드리겠습니다.
SQL은 DB 에 데이터를 조회하거나 저장 혹은 구조를 변경하는 언어입니다. SQL도 코딩의 일부인 것입니다. 일반 적인 SQL은 DB 별도 크게 다르지 않습니다. 그래서 SQL을 익혀두시면 처음 다뤄보는 DB 데이터도 쉽게 추출해 보실 수 있습니다. 물론 함수나 사용법이 DB마다 차이가 없는 것은 아닙니다. 하지만 그런 것을은 구글링해서 언제든지 바로 찾아볼 수 있기 때문에 걱정하실 필요 없습니다. SQL은 Structured Query Language 입니다. Standard가 아닌 Structured 입니다. 구조화 혹은 체계화된 질의 언어라는 뜻입니다. 그래서 SQL을 배우기는 참 쉽습니다.
SQL로 할 수 있는 일은 데이터 조회, 입력, 삭제, 수정을 할 수 있습니다. 이런 작업을 수행하는 SQL을 DML 이라고 합니다. 데이터를 넣을 테이블 구조를 만들거나 변경, 삭제도 할 수 있습니다. 이것을 DDL 이라고 합니다. 그 밖에도 사용자 계정을 만들거나 권한을 주거나 파티션을 만들거나 여러가지를 할 수 있는데 이 영역은 SI 개발자의 영역이 아닙니다. 이 부분은 DB adminiatrator 가 하는 역활이니 천천히 배우셔도 됩니다. 이번에는 SQL DML 중 데이터 조회하는 과정에 대해 설명을 드리도록 하겠습니다.
MySQL 서비스를 실행합니다. Window Service 관리화면을 실행합니다.
MySQL80 을 선택하고 시작을 누릅니다.
MySQL Workbench 를 실행합니다. 저는 Windows 8을 사용하고 있기 때문에 아래와 같이 검색해서 찾아 실행합니다.
아래 Local instance MySQL80 을 클릭합니다.
암호를 입력하고 SQL 창에서 실습을 하면서 설명을 하겠습니다. 그러기에 앞서 주석에 대해 설명을 드립니다.
주석은 쉽게 말해 Coding 내용을 이해하기 쉽도록 설명을 적는 곳이라고 생각하면 됩니다. SQL도 Coding 의 한 종류이다 보니 주석을 생각보다 많이 적게됩니다. 주석은 아래와 같이 /* ... */ 로 시작과 끝을 감싸서 구간 전체를 주석처리할 수도 있고 -- 뒤로 한 줄 전체가 주석되는 방법도 있습니다.
MySQL은 DB를 선택하는 과정이 있습니다. 목록을 보기위해서는 show 명령어를 사용합니다.
show databases; <ctrl + enter>
명령어 끝에 세미콜론(;)이 있는데, 이게 명령의 끝이라는 것의 명시합니다. 커서가 위치한 부분의 SQL을 (;) 있는 부분까지 실행됩니다. 명령어는 특수한 경우를 제외하고는 대부분 대소문자를 가리지 않습니다. 대소문자를 가려야 하는 경우는 교육과정을 따라오다보면 자연스럽게 알 수 있을 거라고 봅니다.
DB 사용은 use 명령어로 전환이 가능합니다. 그리고 DB 안에 포함된 테이블 목록을 볼 때도 show 명령어를 사용합니다. 실제 데이터는 테이블안에 일종의 엑셀의 표와 같은 형태로 존재합니다. 이해가 쉽도록 설명하자면 DB는 폴더고 테이블은 그 안에 든 엑셀 파일입니다. 엑셀파일 안에는 데이타가 있는 거죠.
world 연습 DB는 구조가 간단해서 설명하거나 이해하기가 쉬울 겁니다. sakila DB는 처음 DB를 접하시는 분은 좀 복잡하게 보일 것 같습니다. world 에 대해 과정을 마친 후, sakila 과정을 준비해보겠습니다.
world DB 안에는 city, country, countrylanguage 3개의 테이블이 존재 합니다. 그리고 이 테이블들은 아래의 구조로 되어 있습니다. 여기서 city 테이블은 어떤 컬럼이 있는 지 구조를 확인 해볼 수 있습니다. 컬럼 목록을 볼 때도 show 목록을 사용할 수 있습니다.
show columns from city; <ctrl + enter>
city 테이블은 엑셀로 친다면 아래과 같은 컬럼으로 데이터를 관리합니다. Field 가 컬럼명, Type은 데이터 유형, Null 은 Null 값허용 여부를 의미합니다. Key는 키값인데 PRI 로 표신된 컬럼이 Primary key를 의미하며 중복을 허용하지 않으며 Null 값 또한 허용하지 않습니다. 전 체 데이터 중에서 한 행을 구별할 수 있는 기준이 됩니다. Default는 기본값을 의미합니다. 데이터를 입력할 때 컬럼에 어떠한 값도 입력하지 않으면 자동으로 Default 에 지정한 값으로 입력됩니다. Extra 에 auto_incremnet 는 자동 증감을 의미하는데 이런 경우 데이터를 신규로 입력하면 ID컬럼의 숫자값이 자동 입력됩니다. 일단 구조만 이해하시고 자세한 언급한 내용은 앞으로도 계속 보게 될테니 이해가 안가더라도 넘어가시길 바랍니다.
여기 까지해서 DB - 테이블 - 컬럼 순으로 연결된다는 것을 이해하셨을 겁니다. 그럼 나머지 country, countrylanguage 도 어떤 컬럼이 전체 테이블들이 어떻게 연결되는지 보겠습니다.
중심은 country 이고 countrylanguage와 city는 각각 CountryCode 로 연결이 됩니다. 영어로 되어 있지만 대충 국가와 도시, 그리고 사용하는 언어 등으로 짐작해 볼 수 있겠습니다. country 테이블 데이터를 조회 해보겠습니다. 이제 부터 본격 적으로 SQL이 시작됩니다. 이전에 사용했던 show 명령어나 use 명령어는 MySQL 만의 언어이니 다른 DB에서 사용하지 않습니다만, 데이터를 조회하는 SQL은 다른 DB에서도 다 통합니다.
데이터를 조회하는 SQL은 SELECT 로 시작합니다. 사용 문법은 select {조회할 컬럼명} from {테이블명} [where 조건] 입니다. 여기서 컬럼을 모를 때는 * 로 전체 컬럼을 조회해 볼 수 있습니다. 당연히 select id, name, country 등 출력할 컬럼을 명시해서 사용하 수도 있습니다. 대충 보니 도시명과 구역, 국가코드, 인구수 등이 관리되나 보군요.
데이터 조회에 조건을 넣어 보겠습니다. 인구수가 1천만명 이상인 도시가 있는지 조회해봅니다.
몸바이가 있군요. 호텔 뭄바이의 그 뭄바이겠죠. 혹시 서울도 데이터가 존재하는지 확인 해볼까요? 문자열 값은 '문자' 형태로 따옴표로 감싸서 사용합니다. 서울은 1천만이 조금 안되네요. 데이터가 맞는지 여부는 일단 넘어 가겠습니다.
이와같이 데이터를 조회하는 조건에서는 등식 혹은 부등식을 통해서 조건을 줄 수 있다는 것을 확인 했습니다. 그런데 문자열 같은 경우 한가지 더 짚고 넘아거야 할 것이 있습니다. 문자열 값을 찾을 때 대소문자를 가립니다. 그런데 이 Workbench 툴은 대문자로 조건을 줬는데도 검색이 되는 군요. 이것은 일반 프로그래밍 코딩에서 통하지 않을 것입니다. 사실 문자열은 대소문자가 섞여 있을 가능성이 있을 때는 별도의 조치를 취해 조회를 하는 방법이 있습니다.
※ 여기서는 조회가 되지만 일반적으로 조회가 안되므로 대소문자 주의를 기울여야 합니다.
대소문자가 섞여 있는 컬럼은 upper(컬럼)으로 모두 대문자로 바꿔서 비교하는 방법이 일반적입니다. upper는 소문자를 모두 대문자로 바꿔주는 함수입니다. 문자열 컬럼에서만 사용해야 합니다. 숫자 컬럼에서 사용하면 안되고 사용할 필요도 없겠지요.
단, 위와 같이 사용할 때 주의해야 할 점이 있습니다 조건에 컬럼에 함수를 쓰면 성능이 느려집니다. 교육을 하는 과정에서는 와닿을지 모르겠지만 실전에서는 이런 부분 때문에 어려움이 많습니다. 그래서 성능까지 고려한 SQL 을 써야 합니다. 이번에는 조건을 2개 줘보겠습니다. 조건을 처음 줄 때는 where 를 쓰지만 2번 째 부터는 and 나 or 같은 관계연산자를 사용합니다. And 는 전 / 후 조건이 모두 만족하는 대상이고 or 는 둘 중 하나만 만족해도 됩니다. 경험으로 or는 사용할 때 주의를 해야 합니다. 조회 조건에 데이터 누출현상이 발생하는 원인이 될 때가 많습니다. 실제 코딩에서는 조건이 많이 들어가는 편입니다. 그렇다보니 or가 들어가면 착각을 하기 쉽습니다.
데이터 조회에 대해서는 한번 쭉 훑어 봤습니다. 이제 테이블 간 연결을 통해 데이터를 출력하는 과정을 한번 더 살펴보겠습니다. city 테이블과 country 테이블을 연결해 보겠습니다. 연결하는 방법은 2가지가 존재합니다.
첫번째 where 조건을 통한 join 방법으로 아래와 같이 볼 수 있습니다.
두번째로 ANSI 방법으로 join 하는 방법이 있습니다. 개인적으로 이 방법을 선호하는데 희한하게 국내 개발자들 중에는 이런 문법을 모르시는 분들이 많이 봤습니다. join 될 대상 테이블에 대해 join 방식과 조건을 명시적으로 표현하는데, 이렇게 하면 SQL 이 복잡해도 분석하기 좋고 어떤 테이블이 사용되는지 분석하기가 쉽습니다. 나중에 다시 교육하겠지만 두 테이블 간에 한 쪽에 데이터가 없는 경우를 대비해서 left outer join 이나 right outer join 할 때도 편하고 무엇보다도 ANSI SQL은 어떤 DB에서건 통한다는 겁니다. 개발하는 시스템의 DB가 오라클에서 DB2로 변경할 때 ANSI 형태로 SQL을 개발했다면 수정할 내용이 적어집니다. 참고로 ANSI 는 미국표준입니다.
이번 교육은 테이블 join 에 대해 맛만 보고 마무리를 하겠습니다. 다음 교육 때 join 을 응용한 좀더 어려운 예제를 보면서 SQL 심화 과정을 진행하겠습니다. 수고하셨습니다. 학습일 끝내실 때 MySQL 서비스를 종료하세요.
