SQL문에서 쓰이는 함수들과 특정 컬럼의 값을 그룹으로 나타낼 수 있는 GROUP BY,
특정 컬럼의 값을 내림차순 혹은 오름 차순으로 정렬해 주는 ORDER BY와 앞 회에서 배운 조인(Join)만큼이나 중요한
서브쿼리(SubQuery)에 대해서 살펴본다.
SQL문에서 쓰이는 함수들
SQL문에서 쓰이는 함수들은 생각 이상으로 많다. 그렇지만 실지로 쓰이는 것은 비교적 적으며 우리는 자주 쓰이는
함수를 중심으로 알아 볼 것이다. 만약 더 많은 함수들을 알고 싶다면 각각의 데이터 베이스 사이트를 방문해 보길 바란다.
참고로 이 함수들은 mysql에서 사용하는 함수들이며 각각의 데이터 베이스별로 기능은 같지만 함수 이름은 다를 수 있다.
1) 숫자 함수
ABS(X) : X 에 해당하는 절대 값을 돌려준다.
SIGN(X) : X의 값의 부호 값을 돌려 준다.(-1 : 음수, 0 : 0, 1 : 양수)
MOD(N,M) : N을 M으로 나눈 값의 나머지를 돌려 준다.
FLOOR(X) : 실수 X 값의 소수점 이하의 값은 버림을 한 정수 값을 돌려 준다.
CEILING(X) : 실수 X 값의 소수점 이하의 값을 올림을 한 정수 값을 돌려 준다.
ROUND(X,D) : 실수 X 값에서 소수점에서 D + 1번째에 해당하는 값을 올림한 값을 돌려 준다.
POWER(X,Y) : X의 값을 Y번 곱한 값을 돌려 준다.
RAND() : 0에서 1 사이의 숫자를 랜덤하게 발생시겨 돌려준다.
SELECT abs(-2),abs(2),sign(-45),sign(0),sign(34),mod(23,7);
SELECT floor(1,234),ceiling(1,234),round(2,49),round(2,51);
SELECT power(3,2),power(3,3),rand();
문자열함수
2) 문자열 함수
CHAR(N,...) : N(1-256의 정소)값의 아스키코드를 해당 문자로 출력한다.
CONCAT(str1,str2,...) : 각각의 str1,str2,...등을 하나의 스트링으로 돌려 준다. 단, NULL 이 있으면 NULL 을 출력한다.
CONCAT_WS(separator, str1, str2,...) : 각각의 str1,str2,...등을 separator을 구분자로 사용하여 하나의 스트링으로 출력한다.
LENGTH(str) : str의 문자의 길이를 출력한다.
SELECT char(48),char(76),concat(('My','S','ql'),concat('My',NULL,'Sq;');
SELECT concat_ws(',','My',NULL,'Sql',' ','is',' ','Good');
SELECT length('mysql'),locate('bar','foobarbar');
[그림2] 문자 함수를 실행한 결과
LOCATE(substr,str) : str에서 substr이 위치한 첫번째 위치를 정수 값으로 출력한다.
LEFT(str,len) : str에서 지정해준 자릿수 len만큼 왼쪽에서부터 문자열을 출력한다.
RIGHT(str,len) : str에서 지정해준 자릿수 len만큼 오른쪽에서부터 문자열을 출력한다.
SUBSTRING(str,pos,len) : 문자열 str에서 특정 시작위치 pos에서 지정된 길이 len만큼 문자를 출력한다.
LTRIM(str),RTRIM(str),TRIM(str) : str의 왼쪽,오른쪽,혹은 양쪽 모두의 공백 문자를 제거해 준다.
SELECT left('mysql is good',3),right('mysql is good',3);
SELECT substring('mysql is good',1,5);
SELECT ltrim(' mysql'),rtrim(mysql '),trim(' mysql ');
[그림3] 문자 함수를 실행한 결과
SPACE(N) : N개 만큼의 공백 문자를 만들어 출력한다.
REPLACE(str,from_str,to_str) : 문자열 str에서 특정문자 from_str을 특정 문자 to_str로 바꾸어 출력한다.
REPEAT(str,count) : 문자열 str을 count 갯수만큼 반복하여 반복한 값을 출력한다.
REVERSE(str) : 문자열 str을 역으로 출력 한다.
INSERT(str,pos,len,newstr) : 문자열 str에서 특정 위치 pos에서 특정 길이 len 만큼을 newstr문자열로 바꾸어 출력한다.
SELECT concat('a',space(6),'a');
SELECT replace('mysql is good','is','very'),repeat('a',3);
SELECT reverse('mysql'),insert('Quadratic',3,4,'What');
[그림4] 문자 함수를 실행한 결과
LCASE(str) : 문자열 str을 소문자로 바꾸어 출력한다.
UCASE(str) : 문자열 str을 대문자로 바꾸어 출력한다.
SELECT lcase('MYSQL'),ucase('mysql);
날짜시간함수
3) 날짜, 시간 함수
DAYOFWEEK(date) : 해당날짜의 요일값을 출력한다.(1=일요일,2=월요일,3=화요일,...,7=토요일)
WEEKDAY(date) : DayOfWeek() 함수와 같지만 요일값이 다르다.(0=월요일,1=화요일,...,6=일요일)
DAYOFMONTH(date) : 해당날짜의 날(1-31) 값을 출력한다.
DAYOFYEAR(date) : 일년중 몇번째 날(1-366)인지를 출력한다.
MONTH(date) : 해당 날짜의 달(1-12) 값을 출력한다.
DAYNAME(date) : 해당 날짜의 요일 값을 스트링으로 출력한다.
SELECT dayofweek('2003-02-22'),weekday('2003-02-23');
SELECT dayofmonth('2003-02-22'),dayofyear('2003-02-23');
SELECT month('2003-02-22),dayname('2003-02-23');
[그림6] 날짜, 시간 함수를 실행한 결과
MONTHNAME(date) : 해당 날짜의 달 값을 스트링으로 출력한다.
QUARTER(date) : 총 4분기중 해당 날짜의 분기 수 값을 출력한다.
WEEK(date,first) : 해당날짜가 1년중 몇번째 주인지를 출력한다.
YEAR(date) : 해당 날짜의 년도 값을 출력한다.
YEARWEEK(date,first) : 해당 날짜의 년도와 1년중 몇번째 주인지를 출력한다.
HOUR(time) : 해당 시간의 시 값을 출력한다.
SELECT monthname('2003-02-22'),quarter('2003-02-23');
SELECT week('2003-02-22'),year('2003-02-23');
SELECT yearweek('2003-02-22'),hour('10:22:32');
[그림7] 날짜, 시간 함수를 실행한 결과
MINUTE(time) : 해당 시간의 분 값을 출력한다.
SECOND(time) : 해당 시간의 초 값을 출력한다.
PERIOD_ADD(P,N) : P(YYMM or YYYYMM)값에서 N 달을 뺀 값을 출력한다.
PERIOD_DIFF(P1,P2) : P1(YYMM or YYYYMM)과 P2(YYMM or YYYYMM)의 달 차이를 출력한다.
TO_DAYS(date) : 0년 부터 date까지의 날 수를 출력한다.
FROM_DAYS(N) : N(날)에 해당하는 날짜를 출력한다.
SELECT minute('10:22:32'),second('10:22:32');
SELECT period_add(200302,3),period_diff(0302,200209);
SELECT to_days(20030222),from_days(731633);
[그림8] 날짜, 시간 함수를 실행한 결과
DATE_FORMAT(date,format) : date의 날짜 값을 원하는 format 값으로 바꾸어 출력한다.
TIME_FORMAT(time,format) : time의 시간 값을 원하는 format 값으로 바꾸어 출력한다.
CURDATE() : 현재의 날짜 값을 출력한다.
CURTIME() : 현재 시간을 출력한다.
NOW() : 현재 시스템의 날짜와 시간을 출력한다.
SELECT date_format('2003-02-22 22:23:00','%D %y %a %d %m %b %j');
SELECT time_format('22:23:00','%H %k %h %i %I %r %p %s %S');
SELECT curdate(),curtime(),now();
제어함수
4) 제어 함수
1) ~ 3)까지의 함수들은 이름만 들어도 대강은 어떤 함수일 거란 것을 예상 할 것이다. 그럼 제어 함수란 무엇인가.
이들 함수는 일반적인 프로그래밍 언어에서의 조건문과 같은 역할을 하는 함수들이다.
종류로는 IsNULL,NULLIF,IF,CASE등이 있으며 이들 함수로 인해 SQL문 안에서 값들을 제어 할 수가 있다.
IFNULL(expr1,expr2) : expr1 값이 널이면 expr2값이 출력이 되는 함수
NULLIF(expr1,expr2) : expr1 값과 expr2값이 같으면 NULL이 출력이 되고 다르면 expr1 값이 출력되는 함수
IF(expr1,expr2,expr3) : expr1 값이 참이면 expr2 값이 출력되고 거짓이면 expr3값이 출력되는 함수
CASE value WHEN [compare-value] THEN result [WHEN [compare-value] THEN result ...] [ELSE result] END :
value 값이 WHEN 다음의 compare-value값과 같으면 THEN 다음의 result 값을 출력하는 함수
SELECT IFNULL(NULL,2),IFNULL(1,2),IF(1 > 0,1,0),IF(1 < 0,1,0);
SELECT CASE 1 WHEN 1 THEN "one"
WHEN 2 THEN "two" ELSE "more" END;
[그림10] 제어 함수를 실행한 결과
지금까지 SQL문에서 많은 쓰이는 함수들에 대해서 공부해 보았다. 이들 함수 말고도 많은 다른 함수들이 존재하니
꼭 한번쯤은 봐 두길 바란다. 다음은 Gruop By 절과 GROUP BY 절과 같이 쓰이는 그룹함수에 대해서 알아보자.
GROUP BY
GROUP BY에 대해서
GROUP BY 문은 SELECT 문으로 데이터베이스로부터 얻어오는 내용 중에 최대값, 최소값, 합계, 평균 혹은 분산등
여러 열의 컬럼을 포함하는 집합 연산(aggregate function)이 적용된 경우에 주로 사용된다. 이런 연산에 사용하는
함수는 좀더 후에 배우고 먼저 GROUP BY문에 대해서 알아 보겠다.
GROUP BY절은 특정 컬럼의 값들을 하나로 묶는 역할을 한다. 이 말은 특정 컬럼의 값이 같으면 그 열 값은 하나의
열 값으로 취급한다는 말이다. 백문이 불여일견이라고 먼저 직접 테스트를 해보자. 테스트를 하기 위해서 다음과
같이 테이블을 하나 만든다.
CREATE TABLE group_test(
id varchar(10) not null,
number int not null,
string varchar(10) not null
}
위와 같이 만들었다면 테이블에 값을 다음 그림과 같이 넣어 보자.
SELECT * FROM group_test;
[그림11] Group_test 테이블 열(row)리스트
이제 말로만 떠들지 말고 직접 테스트를 해보자. 과연 어떤 결과가 나올지. string 컬럼을 그룹화하여 보자.
SELECT * FROM group_test GROUP BY string;
[그림12] String을 그룹화한 결과
위의 그림처럼 많은 변화가 생겼다. 이렇듯 특정 컬럼을 그룹화하면 그 컬럼의 동일한 값은 하나로 묶여 출력이 된다.
이제는 이 Group By 절을 사용하여 그룹함수를 사용해 보자.
그룹함수(aggregate function)에 대해서
그룹함수(aggregate function)에 대해서
그룹 함수도 여러개가 존재 하지만 여기서는 자주 사용하는 함수 몇 개에 대해서 설명을 하겠다.
그 전에 그룹함수라 하여 꼭 GROUP BY절과 같이 사용해야 하는 것은 아니며 단지 일반적으로
SELECT 컬럼1,그룹함수 FROM test1
와 같은 비슷한 형식일 경우에는 GROUP BY절을 같이 사용하여야 한다. 그렇지 않고 그룹함수만 쓸 경우에는 GROUP BY 절을
꼭 같이 써야 할 필요는 없다.
AVG() : 이 함수는 ()안의 컬럼들의 평균을 출력하는 함수이다.
MAX() : 이 함수는 ()안의 컬럼들 중 가장 큰 값을 출력하는 함수이다.
MIN() : 이 함수는 ()안의 컬럼들 중 가장 작은 값을 출력하는 함수이다.
SUM() : 이 함수는 ()안의 컬럼들의 합계을 출력하는 함수이다.
COUNT() : 이 함수는 레크드의 수를 출력하는 함수이다.
SELECT count(*),avg(number),max(string),min(id),sum(number) FROM group_test;
[그림13] 그룹 함수를 실행한 결과
이제 이들을 활용해 보자. 어떤 경우에 이들을 효율적으로 사용할 수 있을까?
예를 들어 어떤 사람이 'id가 'kim'이라는 사람의 number의 값의 평균과 총점을 구하고 싶다'라고 주문을 해왔다.
그러면 여러분들은 어떻게 하겠는가? 지금까지 배운데로 먼저 이들의 값을 구해보자.
SELECT avg(number),sum(number) FROM group_test WHERE id = 'kim';
[그림14] id가 'kim'인 사람의 평균과 총점 결과
여기까지는 문제가 없다. 그렇다면 이제는 id별로 그 사람들의 number의 값의 평균과 총점을 구하고 싶다고 한다.
그러면 여러분들은 어떻게 하면 되겠는가? 그렇다. 이럴때 유용하게 쓰이는 것이 GROUP BY 절이다.
이를 사용하여 id를 그룹화하면 이들 각각의 평균과 총점을 구할 수 있다.
SELECT id,avg(number),sum(number) FROM group_test GROUP BY id;
[그림15] 각각의 사람의 평균과 총점 결과
ORDER BY 절에 대해서 이제는 결과 값을 정렬하는 방법에 대해서 알아보자. 정렬을 하기 위해서 사용하는 것이 ORDER BY 절이다.
이 절은 특정 컬럼을 중심으로 내림차순 정렬 혹은 오름차순 정렬을 할 수가 있다. 그럼 먼저 형식을 보자.
ORDER BY 특정 컬럼1 [Asc|Desc],특정 컬럼2 [Asc|Desc],...
이며 제일 먼저 특정 컬럼1이 정렬이 되고 그 정렬 속에서 특정 컬럼2가 정렬이 된다. 그럼 직접 실습을 해보자.
SELECT * FROM group_test ORDER BY id ASC,number DESC, string ASC;
[그림16] ORDER BY 절을 사용한 결과
이렇게 ORDER BY 절을 이용하면 원하는 정렬이 가능하다.
여기서 한가지 주의할 점이 있는데 ORDER BY 절을 먼저 쓰고 GROUP BY절을 나중에 쓰면 오류가 발생하므로
반드시 GROUP BY와 ORDER BY 절을 같이 쓸 경우에는 GROUP BY절을 먼저 쓰고 뒤에 order by절을 쓰길 바란다.
일반적인 SQL문 순서
SELECT 컬럼1,컬럼2,... FROM 테이블1,테이브2,.. WHERE 조건문 GROUP BY 그룹화 할 컬럼1,컬럼2,...
ORDER BY 정렬할 컬럼1 [ASC|DESC],컬럼2,[ASC|DESC],...
서브쿼리(SubQuery?)에 대해서
서브쿼리(SubQuery)에 대해서
드디여 여기까지 왔다. 이 부분은 조인(Join)처럼 매우 중요하므로 꼭 마스터 하길 바란다.
이젠 조인(Join)에 대해서 어느 정도 이해가 가는가? 그렇다면 조인(join)만큼이나 중요하고
복잡한 서브쿼리(SubQuery)에 대해서 알아보자. 먼저 알아 보기 전에 다음과 같이 테이블을 만든다.
참고로 MySQL에서는 서브쿼리(SubQuery)를 지원하지 않는다. mysql.com 에 따르면 4.1버전부터 지원을 한다고
하며 우리는 일단 PostgreSQL를 사용하여 테스트를 할 것이다.
CREATE TABLE Student(
ID varchar(50),
Name varchar(50),
Class int
);
CREATE TABLE Score(
ID varchar(50),
Score int
);
위와 같이 테이블을 만들었다면 [그림17]과 [그림18]처럼 열를 추가한다.
SELECT * FROM Student;
[그림17] Student 테이블 속 열
SELECT * FROM Score;
[그림16] Score 테이블 속 열
추가 하였다면 이제 본격적으로 서브쿼리(SubQuery)에 대하여 공부하여 보자.
서브쿼리(SubQuery)는 SELECT, INSERT, UPDATE, DELETE 문이나 다른 서브쿼리(SubQuery) 내부에 중첩된 SELECT 쿼리이다.
서브쿼리(SubQuery)는 식이 허용되는 모든 위치에서 사용할 수 있고 ()로 묶어서 사용된다.
다음 예제에서 서브쿼리(SubQuery)는 SELECT 문에서 Score 라는 컬럼으로 사용된다.
SELECT a.Name, a.Class,
(SELECT Score FROM Math AS b
WHERE b.ID = a.ID) AS Score
FROM Student AS a
이것은 학생들의 이름과 반, 점수을 뽑아내는 서브쿼리(SubQuery)문이다.
대부분의 서브쿼리(SubQuery)문은 조인(join)으로 나타낼 수 가 있다.
또한 서브쿼리(SubQuery)가 포함된 문장이나 의미상 동일한 문장에서는 서브쿼리(SubQuery)나 조인(Join)이나 성능면에서는 차이가 없다.
이제 위의 문장을 테스트 해보자.
[그림19] 위의 서브쿼리(SubQuery)문 실행 결과
위에서 하위 쿼리(SubQuery)는 대부분이 조인(Join)으로 출력할 수 있다고 했다. 그럼 이젠 앞 강에서 배운 조인(Join)으로 출력해보자.
SELECT a.Name,a.Class,b.Score FROM Student a INNER JOIN Score b ON b.ID = a.ID;
[그림20] 위의 서브쿼리(SubQuery)문을 조인(join)문으로 변경하여 실행한 결과
내용을 보면 똑같은 결과를 출력하였다. 서브쿼리(SubQuery)에 대해서 위에서 설명할 때 식이 허용하는한 어디에서든
사용할 수 있다고 했다. 그럼 이제는 서브쿼리(SubQuery)문을 현재의 위치가 아닌 다른 위치에서 사용하여 보자.
기본적인 형식은 다음과 같다.
SELECT 컬럼1,컬럼2,.....
FROM 테이블1 where 비교할 컬럼 [IN|NOT IN]
(select 비교할 컬럼 From 테이블2)
여기서 주목할 곳은 IN과 NOT IN이다. IN은 =와 같은 뜻이며 NOT IN은 !=과 같은 뜻으로 사용된다. 그럼 간단한 실습을 해보자.
SELECT ID,Name,Class FROM Student WHERE ID IN (SELECT ID FROM Score);
SELECT ID,Name,Class FROM Student WHERE ID NOT IN (SELECT ID FROM Score);
[그림21] 위의 서브쿼리(SubQuery)문에서 IN과 NOT IN의 차이
이상과 같이 서브쿼리(SubQuery)에 대해서 살펴보았다. 앞으로 데이터 베이스를 사용하면서 서브쿼리(SubQuery)문과
조인(join)문은 성능향상을 위해서도 꼭 필요한 것들이므로 반드시 마스터하길 바란다. 참고로 MSSQL과 오라클등
상업적인 데이터 베이스는 IN,NOT IN뿐만이 아니라 =,!=,<,>등을 서브쿼리(SubQuery)에서도 지원하고 있다.
이건 오픈 소스 데이터 베이스의 한계로 앞으로 이들 오픈 소스 데이터 베이스도 역시 지원을 할 것이다.
Copyright ⓒ hompy.info. All rights reserved.
