[SQL] 3. 정렬과 연산

1. 정렬 - ORDER BY

1) ORDER BY로 검색 결과 정렬하기

-SELECT 명령의 ORDER BY 구를 사용하여 검색결과의 행 순서를 바꿀 수 있음. 지정한 열의 값에 따라 행의 순서가 정렬됨.

   → 서버에서 클라이언트로 행 순서를 바꾸어 결과를 반환하는 것일뿐, 저장장치에 저장된 데이터의 행 순서를 변경하는 것은 아님.

-SYNTAX: ORDER BY 구는 WHERE 구 뒤에 지정(검색 조건이 필요없는 경우에는, WHERE 구를 생략하고 FROM 구 뒤에 ORDER BY 구 지정) 

SELECT 열명 FROM 테이블명 WHERE 조건식 ORDER BY 열명

 

2) ORDER BY DESC로 내림차순으로 정렬하기

-오름차순으로 정렬(ORDER BY ASC)

• SYNTAX

SELECT 열명 FROM 테이블명 ORDER BY 열명 DESC

• 오름차순은 내림차순과 달리 생략가능함.(즉, 정렬방법을 지정하지 않은 경우에는 ASC로 간주하여 오름차순으로 정렬됨.)

-내림차순으로 정렬(ORDER BY DESC)

• SYNTAX

SELECT 열명 FROM 테이블명 ORDER BY 열명 DESC

 

3) 대소관계

-수치형 데이터와 날짜시간형 데이터는 숫자의 크기로 대소를 판별함.

   → 날짜시간형 데이터의 경우, 최근으로 갈수록 크고 이전으로 갈수록 작다고 판별함.

-문자열형 데이터는 사전식 순서에 의해 대소관계가 결정됨. 

   → 알파벳, 한글 순 / 한글은 자음, 모음순으로 문자열의 순서를 결정함.

-수치형과 문자열형 데이터는 대소관계의 계산방법이 다르므로 문자열형 열에 숫자데이터를 저장할 경우 문자로 인식하여 대소관계의 계산 방법이 달라질 수 있다는 점에 유의.

 

2. 복수의 열을 지정해 정렬하기

1) 복수 열로 정렬 지정

-ORDER BY 구를 생략한 경우 행의 순서는 데이터베이스 서버의 당시 상황에 따라 결정됨. 즉, 순서가 일정하지 않음.

   → 언제나 같은 순서로 결과를 얻고 싶다면 반드시 ORDER BY 구로 순서를 지정해야 함.

-ORDER BY 구를 지정해도 1개의 열만으로는 정확히 순서를 결정할 수 없는 경우, 복수의 열을 지정해 정렬할 수 있음. 

• SYNTAX

SELECT 열명 FROM 테이블명 ORDER BY 열명1, 열명2...

• 이때 정렬 순서는 지정한 열명의 순서를 따름. 값이 같아 순서를 결정할 수 없는 경우에는 다음으로 지정한 열명을 기준으로 정렬하는 식으로 처리됨.

2) 정렬방법 지정하기

-복수 열을 지정한 경우에도 각 열에 대해 개별적으로 정렬방법을 지정할 수 있음. 

-SYNTAX

SELECT 열명 FROM 테이블명 ORDER BY 열명1[ASC|DESC], 열명2[ASC|DESC]...

 

   →복수 열을 지정하는 경우에도 정렬방법을 생략하면 기본값은 ASC가 됨.(단, 데이터베이스 제품에 따라 기본값이 다를 수 있음.)

 

3) NULL 값의 정렬순서

-NULL 값은 대소비교를 할 수 없어 정렬 시에는 별도의 방법으로 취급함. 

   →'특정 값보다 큰 값' 또는 '특정 값보다 작은 값'으로 취급하여 정렬함.

-데이터베이스 제품에 따라 기준이 다른데, MySQL의 경우 NULL 값을 가장 작은 값으로 취급함.

3. 결과 행 제한하기 - LIMIT

1) 행수 제한

-SELECT 명령에서 LIMIT 구를 이용하여 결괏값으로 반환되는 행을 제한할 수 있음.

-LIMIT 구를 이용하여 결과 행 제한하기

• LIMIT 구는 표준 SQL이 아님. → MySQL과 PostgreSQL에서 사용할 수 있는 문법임.
• SYNTAX

SELECT 열명 FROM 테이블명 WHERE 조건식 ORDER BY 열명 LIMIT 행수

 

   → LIMIT 구는 SELECT 명령의 마지막에 지정해야 함.

   → LIMIT 다음에는 최대 행수를 수치로 지정함. 

• LIMIT와 WHERE 은 기능과 내부처리 순서가 전혀 다름. → LIMIT는 반환할 행수를 제한하는 기능으로, WHERE 구로 검색한 후 ORDER BY로 정렬된 뒤 최종적으로 처리됨.

-LIMIT를 사용할 수 없는 데이터베이스의 행 제한

• SQL Server에서는 TOP 뒤에 최대 행수를 지정하여 행수를 제한할 수 있음.

SELECT TOP 3 * FROM sample33;

• Oracle에서는 ROWNUM이라는 열을 사용해 WHERE 구로 조건을 지정하여 행을 제한할 수 있음. → ROWNUM은 클라이언트에게 결과가 반환될 때 각 행에 할당되는 행번호임. ROWNUM으로 행을 제한할 때는 WHERE 구로 지정하므로 정렬하기 전에 처리되어 LIMIT 로 행을 제한한 경우와 결과가 다름. 

SELECT * FROM sample33 WHERE ROWNUM<=3;

 

2) 오프셋 지정

-웹 시스템에서 대량의 데이터를 하나의 페이지에 표시하는 것은 기능적/속도적으로 비효율적 

   → 페이지 나누기(pagenation) 기능을 사용함. 

-페이지 나누기 기능은 LIMIT 구를 사용하여 간단하게 구현 가능함.

   → OFFSET: 결괏값으로부터 데이터를 취득할 위치를 가리키는 것. LIMIT 구에 OFFSET을 지정하여 데이터의 시작 위치를 지정함. 생략 가능하며, 기본값은 0임.('시작할 행 -1'으로 기억하면 편리)

-SYNTAX

SELECT 열명 FROM 테이블명 LIMIT 행수 OFFSET 위치

 

3. 수치 연산

1) 사칙 연산

-산술 연산자

연산자	연산	예
+	덧셈	1+2 → 3
-	뺄셈	1-2 → -1
*	곱셈	1*2 → 2
/	나눗셈	1/2 → 0.5
%	나머지	1%2 → 1

 

-연산자의 우선순위

우선순위	연산자
1	*/%
2	+-

• 계산순서는 기본적으로 왼쪽에서 오른쪽으로 진행함.
• 우선순위가 다른 연산자들이 섞여있는 경우, 우선순위가 높은 쪽이 먼저 계산됨.

2) SELECT구로 연산하기

-SELECT 구에는 여러가지 식을 기술할 수 있음. 

   → 식: 열명, 연산자, 상수로 구성됨.

-SYNTAX

SELECT 식1, 식2...FROM 테이블명

 

3) 열의 별명

-열 이름이 길고 알아보기 어려운 경우 별명을 붙여 열명을 재지정할 수 있음.

ex)

SELECT *, price * quantity AS amount FROM sample34;

   

   → 예약어 AS를 사용해 price*quantity 라는 열에 amount라는 별명을 지정함.

 

-예약어 AS를 사용해 별명을 지정함.(키워드 AS는 생략 가능함.)

-SELECT 구에서는 콤마로 구분해 복수의 식을 지정할 수 있고, 각각의 식에 별명을 붙일 수 있음.

-별명(alias)은 영어, 숫자, 한글 등으로 지정할 수 있으나, ASCII 문자 이외의 것을 포함할 경우에는 더블쿼트로 둘러싸서 지정함. 

-더블쿼트로 둘러싸서 지정하면 예약어와 같은 이름도 지정 가능함.

-별명은 숫자로 시작할 수 없음. 다만 더블쿼트로 묶는다면 숫자로 시작하는 별명도 사용 가능함.

-그러나 숫자만으로 구성되는 객체명은 허용되지 않음.

※이러한 규칙은 데이터베이스 제품에 따라 미묘한 차이가 있으니 주의해야 함.

 

4)WHERE 구에서 연산하기 

ex)

no	price	quantity	amount
1	100	10	1000
2	230	24	5520
3	1980	1	1980

▶sample34

SELECT *, price * quantity AS amount FROM sample34 WHERE price * quantity >= 2000;

 

  → WHERE 구에서 금액을 계산하고 2000원 이상인 행을 검색하는 명령. 다음 명령을 실행한 결과는 아래와 같다.

no	price	quantity	amount
2	230	24	5520

 

-WHERE 구와 SELECT 구의 내부처리 순서: 데이터베이서 서버 내부에서 WHERE 구 -> SELECT 구 순서로 처리됨. 즉, WHERE 구로 행이 조건에 일치하는지 아닌지를 먼저 조사한 후에 SELECT 구에 지정된 열을 선택해 결과로 반환하는 식으로 처리.

  → SELECT 구에서 지정한 별명은 WHERE 구 안에서 사용할 수 없음.

 

5)NULL 값의 연산

-NULL 값의 연산결과는 무조건 NULL이 된다.

ex) NULL + 1 , NULL * 2 + 1 , 1/NULL...의 결과는 모두 NULL.

 

6)ORDER BY 구에서 연산하기 

-ORDER BY 구에서도 연산을 할 수 있고 그 값들을 정렬할 수 있음.

-데이터베이스 서버에서의 내부처리: WHERE 구 -> SELECT 구 -> ORDER BY 구

  →따라서, SELECT 구에서 지정한 별명을 ORDER BY 구에서도 사용할 수 있음.

ex)

SELECT *, price * quantity AS amount FROM sample34 WHERE amount >= 2000;

  

  → WHERE 구에서 별명을 사용하면 amount 라는 열명은 존재하지 않는다는 에러가 발생함.

 

SELECT *, price * quantity AS amount FROM sample34 ORDER BY amount DESC;

 

   → 에러가 발생하지 않음.

 

7) 함수 

-연산자 외에 함수를 사용해 연산할 수 있음

-함수 표기 방법: 함수명(인수1, 인수2,...)

-함수는 함수명에 따라 연산 방법이 결졍됨.

  → ex) MOD 함수; 인수로 나뉘어질 수와 나눌 수의 순으로 2개를 지정하면 나머지값을 반환하는 함수.

-함수의 계산대상은 인수(parameter)이며, 인수의 수나 구분방법은 함수에 따라 다름.

-연산자가 그러하듯, 함수도 결괏값을 반환하는데 이를 '함수의 반환값'이라고 함.

 

8) ROUND 함수

-ROUND 함수란 반올림을 하는 함수임.

-ROUND 함수는 첫번째 인수로 반올림 할 열을 지정하고, 두번째 인수로는 반올림할 자릿수를 지정할 수 있음.

  → 두번째 인수를 생략하는 경우는 0으로 간주하여, 소수점 첫번째 자리를 반올림함. 0을 포함하는 양의 정수로 지정하면 소수부에서 반올림하고, 음수로 지정하면 정수부에서 반올림이 일어남. 

4. 문자열 연산

1) 문자열 결합

-문자열 결합: 문자열 데이터를 결합하는 연산

  → ex) 'ABC' || '1234' -> 'ABC1234' 

-데이터베이스 제품마다 문자열을 결합하는 연산자가 다름.

연산자/함수	연산	데이터베이스
+	문자열 결합	SQL Server
||	문자열 결합	Oracle, DB2, PostgreSQL
CONCAT	문자열 결합	MySQL

 

-ex)

SELECT CONCAT(quantity, unit) FROM sample35;

 

 

   → sample35의 unit 열과 quantity 열을 결합한 CONCAT(quantity, unit) 열을 결과로 반환. 이때 문자열로 결합한 결과는 문자열형이 됨.

 

2)SUBSTRING 함수

-SUBSTRING 함수는 문자열의 일부분을 계산해서 반환해주는 함수임.

-ex) SUBSTRING('20140125001', 1, 4) -> '2014' (1째 자리부터 4자리 추출) / SUBSTRING('20140125001', 5, 2) -> '01' (5째 자리부터 2자리 추출)

 

3)TRIM 함수

-문자열의 앞뒤로 여분의 스페이스가 있을 경우 이를 제거해주는 함수임.

-문자열 도중에 존재하는 스페이스는 제거되지 않음.

-고정길이 문자열형에 대해 많이 사용하는 함수.

-인수를 지정하여 스페이스 이외의 문자를 제거할 수도 있음.

-ex) TRIM('ABC     ') -> 'ABC'

 

4)CHARACTER_LENGTH 함수

-문자열의 길이를 계산해 돌려주는 함수임. 문자열의 길이는 문자를 단위로 계산되어 수치로 반환됨.

-가변 길이를 가지는 VARCHAR 형의 문자열의 길이를 계산할 수 있음. 

-함수명을 CHAR_LENGTH로 줄여서 사용 가능.

-OCTET_LENGTH 함수: 문장의 길이를 바이트 단위로 계산해 돌려주는 함수.  

  →문자세트: RDBMS에서는 인코드 방식을 문자세트라고 부름. 문자세트에 따라 문자열 데이터의 바이트 수가 다름.

  →ex) EUC-KR에서 ASCII 문자는 1바이트, 한글은 2바이트 / UTF-8에서 ASCII 문자는 1바이트, 한글은 3바이트

5. 날짜 연산

1)SQL에서의 날짜

-시스템 날짜: 하드웨어 상의 시계로부터 실시간으로 얻을 수 있는 일시적인 데이터를 말함.

  → 표준 SQL에서는 'CURRENT_TIMESTAMP'라는 함수를 실행했을 때를 기준으로 시간을 표시함. 이때 인수는 지정할 필요 없음.

ex)

SELECT CURRENT_TIMESTAMP;

 

-날짜 데이터를 데이터베이스에 저장할 경우 'CURRENT_TIMESTAMP'를 사용해 시스템 상의 날짜를 저장할 수 있음.

-임의의 날짜를 저장하고 싶다면 직접 날짜 데이터를 지정해야 함. 

  → ORACLE에서는 TO_DATE 함수를 사용해 문자열 데이터를 날짜형 데이터로 변환할 수 있고 서식 또한 별도로 지정할 수 있음.

ex)

TO_DATE('2014/01/25', 'YYYY/MM/DD')

 

2)날짜의 덧셈과 뺄셈

-날짜시간형 데이터는 기간형 수치데이터와 덧셈 및 뺄셈을 할 수 있음. 이때 결과로 날짜시간형 데이터가 반환됨.

  → ex)

SELECT CURRENT_DATE + INTERVAL 1 DAY;

 

→ CURRENT_DATE는 시스템 날짜의 날짜만 확인하는 함수이고, INTERVAL 1 DAY는 '1일 후'라는 의미의 기간형 상수임.

 

-날짜시간형 데이터 간에 덧셈/뺄셈이 가능함.

   → MySQL에서는 DATEDIFF('2014-02-28', '2014-01-01')과 같이 계산할 수 있음.

6. CASE 문으로 데이터 변환하기

1) CASE문

-SYNTAX

CASE WHEN 조건식1 THEN 식1
[WHEN 조건식2 THEN 식2...]
[ELSE 식3]
END

 

   → WHEN 절에는 참과 거짓을 반환하는 조건식을 기술하여, 해당 조건을 만족하여 참이 되는 경우에는 THEN 절에 기술한 식이 처리됨. WHEN 절의 조건식을 차례대로 평가해 나가다가 가장 먼저 조건을 만족한 WHEN 절과 대응하는 THEN 절 식의 처리결과를 결괏값으로 반환함. 그 어떤 조건식도 만족하지 못한 경우에는 ELSE 절에 기술한 식이 채택됨. 

 

-COALESCE: NULL 값을 변환하는 함수. 주어진 인수 가운데 NULL 이 아닌 값에 대해서는 가장 먼저 지정된 인수의 값을 반환함. 

  → ex)

SELECT a, COALESCE(a, 0) FROM sample37;

 

2) 또 하나의 CASE 문

-디코드: 숫자로 이루어진 코드를 알아보기 쉽게 문자열로 변환하는 것/ 인코드: 디코드와 반대로 문자를 수치화하는 것을 말함.

-이와 같은 디코드는 '단순 CASE문'으로 처리할 수 있음.

-CASE 문은 '검색 CASE문'과 '단순 CASE문'의 2개 구문으로 나눌 수 있음.

  → 검색 CASE문의 SYNTAX

CASE WHEN 조건식1 THEN 식1
[WHEN 조건식2 THEN 식2...]
[ELSE 식3]
END

 

  →단순 CASE문의 SYNTAX

CASE 식1
   WHEN 식2 THEN 식3
   [WHEN 식4 THEN 식5...]
   [ELSE 식6]
END

   → 식1의 값이 WHEN의 식2의 값과 동일한지 비교하고 값이 같다면 식3이 전체 CASE문의 결괏값이 됨. 값이 같지 않으면 뒤에 기술한 WHEN 절과 비교하는 식으로 진행함. 비교 결과 일치하는 WHEN 절이 하나도 없는 경우에는 ELSE 절이 적용됨. 

 

3) CASE를 사용할 경우 주의사항 

-CASE 문은 SELECT 구, WHERE 구, ORDER BY 구 어디에서나 사용가능함.

-ELSE를 생략하면 ELSE NULL이 되는 것에 주의해야 함. 따라서 ELSE를 생략하지 않고 지정하는 편이 나음.

-단순 CASE문은 특성상 = 연산자로 비교가 이루어지므로 NULL 값인지를 판정할 수가 없음. 

-CASE문과 COALESCE 함수는 표준 SQL로 지정되어 있으므로 이것을 사용하는 것이 좋음.