3장 정렬과 연산

09강. 정렬 - ORDER BY

ORDER BY 구를 사용하여 검색결과의 행 순서를 바꿀 수 있음 (지정하지 않을 경우 DB 내부에 저장된 순서로 반환)

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

SELECT 열명 FROM 테이블명 WHERE 조건식 ORDER BY 열명 #검색 조건 필요한 경우
SELECT 열명 FROM 테이블명 ORDER BY 열명 #검색 조건 필요 없는 경우

 

ORDER BY 구 로 지정된 열의 값에 따라 오름차순으로(기본 정렬) 행 순서 변경하여 정렬

검색 조건이 필요한 경우, ORDER BY 구는 WHERE 구 뒤에 지정 

검색 조건이 필요없는 경우, ORDER BY 구는 FROM 구 뒤에 지정

 

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

SELECT 열명 FROM 테이블명 ORDER BY 열명 DESC #내림차순 정렬
SELECT 열명 FROM 테이블명 ORDER BY 열명 ASC #오름차순 정렬

DESC (descendant) : 내림차순 정렬  

ASC (ascendant) : 오름차순 정렬

 

ASC나 DESC로 정렬방법을 지저하지 않은 경우에는 ASC(ORDER BY의 기본 정렬방법)로 간주!!

 

3. 대소관계

수치형 데이터와 날짜시간형 데이터는 숫자 크기로 판별

문자열형 데이터의 경우 '사전식 순서'에 의해 결정됨

 

- 사전식 순서에서 주의할 점

수치형과 문자열형의 대소관계 계산 방법이 서로 다름

a	b
1	1
10	2
11	10
2	11

a열 : 문자열형(VARCHAR)

b열 : 수치형(INTEGER)

두 열을 오름차순으로 정렬할 경우 대소관계의 계상 방법이 다르기때문에 결과가 달라짐

 

4. ORDER BY는 테이블에 영향을 주지 않는다

ORDER BY를 이용해 행 순서를 바꾸는 것은 서버에서 클라이언트로 행 순서를 바꾸어 결과를 반환하는 것 뿐,

저장장치에 저장된 데이터의 행 순서를 변경하는 것은 아님!

 

 

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

1. 복수 열로 정렬 지정

같은 값을 가진 행의 순서는 일정하지 않음. DB 서버의 당시 상황에 따라 어떤 순서로 행을 반환할 지 결정

a	b
1	1
2	1
2	2
1	3
1	2

 

a열, b열 모두 INTEGER 형 / b열은 a 열의 하위 번호

열의 상관관계에 맞춰 정렬하려 함

 

SELECT * FROM 테이블명 ORDER BY a;

a	b
1	1
1	3
1	2
2	1
2	2

 

a열은 정렬되었으나, b 열의 정렬 순서가 일정하지 않음

 

SELECT * FROM 테이블명 ORDER BY a, b;

a	b
1	1
1	2
1	3
2	1
2	2

먼저 a열로 정렬하고, 값이 같은 부분은 b열로 정렬함

a와 b의 순서가 바뀌는 경우, b열로 먼저 정렬한 뒤, 값이 같은 부분은 a열로 정렬됨

 

2. 정렬방법 지정하기

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

각 열 뒤에 ASC나 DESC를 붙임

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

[ ]는 생략 가능

정렬 방법을 생략하는 경우에는 기본값은 ASC이지만, DB 제품에 따라 기본값을 변경할 수도 있기에 주의 필요

 

3. NULL 값의 정렬순서

ORDER BY로 지정한 열에서 NULL 값을 가지는 행은 가장 먼저 표시되거나 가장 나중에 표시됨

NULL에 대한 대소비교 방법은 데이터베이스 제품에 따라 기준이 다름

 

 

11강. 결과 행 제한하기 - LIMIT 

LIMIT 구를 사용해 표시할 건(행) 수를 제한할 수 있음

 

1. 행수 제한

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

LIMIT 구는 WHERE 구나 ORDER BY 구의 뒤에 지정

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

 

- 정렬한 후 제한하기

LIMIT 한 것과 동일한 결과를 얻기 위해 WHERE 구에 조건을 지정할 수도 있음

LIMIT와 WHERE은 기능과 내부처리 순서가 전혀 다름

LIMIT는 반환할 행수를 제한하는 기능으로, WHERE 구로 검색한 후 ORDER BY로 정렬된 뒤 최종적으로 처리됨

 

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

TOP(SQL Server), ROWNUM(Oracle)이라는 열을 사용해 WHERE 구로 조건을 지정하여 행을 제한할 수 있음

SELECT TOP 행수 * FROM 테이블명;
SELECT * FROM 테이블명 WHERE ROWNUM <= 행수;

ROWNUM으로 행을 제한할 때는 정렬하기 전에 처리되어 LIMIT로 행을 제한한 경우와 결과값이 다름.

 

2. 오프셋 지정

대량의 데이터를 하나의 페이지에 표시하는 것은 기능적으로도 속도 측면에서도 효율적이지 못하므로 일반적으로 페이지 나누기 기능을 사용

페이지 나누기 기능은 LIMIT를 사용해 구현할 수 있음

LIMIT 구의 OFFSET은 생략 가능, 기본값은 0

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

OFFSET은 LIMIT 뒤에 기술 (OFFSET 위치값은 배열 인덱스처럼 '시작할 행 - 1')

 

 

12강. 수치 연산

1. 사칙 연산

연산자	연산	예
+	덧셈(가산)	1+2 → 3
-	뺄셈(감산)	1-2 →  -1
*	곱셈(승산)	1*2 → 2
/	나눗셈(제산)	1/2 → 0.5
%	나머지	1%2 → 1

%대신 MOD 함수를 사용하는 경우도 있음

 

- 연산자의 우선순위

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

 

2. SELECT 구로 연산하기

SELECT 구는 여러 가지 식(열명, 연산자, 상수로 구성됨)을 기술할 수 있음

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

 

3. 열의 별명

AS를 사용하여 별명 지정

SELECT 구에서는 ,(콤마)로 구분해 복수의 식을 지정할 수 있으며 각각의 식에 별명을 붙일 수 있음

AS는 생략 가능

별명이 ASCII문자 이외의 것을 포함할 경우에는 오류를 방지하기 위해 " "(더블 쿼트)로 둘러싸서 지정 

SELECT 식 AS 별명 FROM 테이블명;

숫자로 시작하거나, 예약어와 같은 이름은 지정이 불가능하지만 더블 쿼트로 둘러싸서 지정하면 사용 가능

이름을 지정하는 경우 숫자로 시작되지 않도록 해야함!

 

4. WHERE 구에서 연산하기

SELECT *, price * quantity AS amount FRom sample34 WHERE price * quantity >= 2000; # 에러 발생x
SELECT *, price * quantity AS amount FRom sample34 WHERE amount >= 2000; # 에러 발생

SELECT 구에서 amount라는 별명을 지었으므로 WHERE 구에서 amount로 지정하면 되지 않을까?

→ amount 열은 존재하지 않는다는 에러 발생 

 

- WHERE 구와 SELECT 구의 내부처리 순서

WHERE 구에서 행 선택, SELECT 구에서 열 선택은 데이터베이스 서버 내부에서 WHERE 구 → SELECT 구 순서로 처리

⇒ 별명은 SELECT 구문을 처리할 때 붙여지므로, WHERE 구에서 사용한 별칭은 아직 지정되지 않은 상태라 에러 발생

 

5. NULL 값의 연산

SQL 에서는 NULL 값이 0으로 처리되지 않음

• NULL+1 → NULL
• 1+NULL → NULL
• 1+2*NULL → NULL
• 1/0 → 에러 
• 1/NULL → NULL

NULL로 연산하면 결과는 NULL

 

6. ORDER BY 구에서 연산하기

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

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

ORDER BY 는 서버에서 내부적으로 가장 나중에 처리됨 → SELECT 구에서 지정한 별명을 ORDER BY에서도 사용 가능

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

WHERE 구 → SELECT 구 → ORDER BY 구 순서로 내부처리

 

7. 함수

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

함수명 (인수1, 인수2...)

인수 : 파라미터(parameter)

10 % 3 → 1
MOD(10,3) → 1

 

8. ROUND 함수

DECIMAL 형 : 소수점을 포함하는 수치를 저장하는 자료형

 

- 반올림 자릿수 지정

ROUND 함수는 기본적으로(자릿수 지정 인수 생략 시) 소수점 첫째 자리(0으로 간주)를 기준으로 반올림한 값을 반환

ROUND 함수의 두번째 인수로 반올림할 자릿수 지정 가능

-1을 지정하면 1단위, -2를 지정하면 10단위를 반올림

버림을 하는 경우 TRUNCATE 함수 사용

 

 

13강. 문자열 연산

1. 문자열 결합

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

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

 

문자열을 결합하는 연산자에는 데이터베이스 제품마다 차이가 있음

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

문자열 결합은 2개의 열 데이터를 모아서 1개의 열로 처리하고 싶은 경우에 자주 사용

 

2. SUBSTRING 함수

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

데이터베이스에 따라서는 함수명이 SUBSTR인 경우도 있음

ex)  앞 4자리(년) 추출 SUBSTRING('20140125001', 1, 4) → '2014' 5째 자리부터 2자리(월) 추출 SUBSTRING('20140125001', 5, 2) → '01'

 

3. TRIM 함수

 

문자열의 앞뒤로 여분의 스페이스가 있을 경우 이를 제거해주는 함수 (문자열 도중에 존재하는 스페이스는 제거되지 않음)

ex) TRIM('ABC     ')  → 'ABC'

 

4. CHARACTER_LENGTH 함수

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

CHAR_LENGTH로 줄여서 사용 가능

 

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

 

인코드 방식에 따라 필요한 저장공간 크기가 달라짐

 

- 문자세트(character set)

(반각) 알파벳, 숫자, 기호 : 'ASCII 문자'

반각문자는 전각문자 폭의 절반, 전각문자의 저장용량 더 큼

한글의 경우 'EUC-KR', 'UTF-8' 등의 인코드 방식(문자 세트) 사용

OCTET_LENGTH 함수의 경우 문자 수가 아닌 바이트 단위로 길이를 계산하므로 주의할 필요가 있음

 

문자열 데이터의 길이는 문자세트에 따라 다름!

 

 

14강. 날짜 연산

1. SQL에서의 날짜

날짜나 시간 데이터는 수치 데이터와 같이 사칙 연산을 할 수 있음

날짜시간 데이터를 연산하면 결과값으로 날짜시간 유형의 데이터를 반환하기도, 기간형(interval) 데이터를 반환하기도 함

 

- 시스템 날짜

SELECT CURRENT_TIMESTAMP;

함수 실행했을 때를 기준으로 시간(하드웨어 상의 시계로부터) 표시

 

- 날짜 서식

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

임의의 날짜를 저장하고 싶을 경우에는 직접 날짜 데이터를 지정해야 함

 

날짜 서식은 국가별로 다름

대부분의 데이터베이스 제품은 날짜 데이터의 서식을 임의로 지정, 변환할 수 있는 함수를 지원

 

2. 날짜의 덧셈과 뺄셈

SELECT CURRENT_DATE + INTERVAL 1 DAY; # 1일 후 계산

 

- 날짜형 간의 뺄셈

두 날짜 사이에 차이가 얼마나 발생하는지 계산할 수 있음

MySQL의 경우: DATEDIFF('2014-02-28', '2014-01-01')로 계산 가능

 

 

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

1. CASE 문

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

WHEN 절에는 참과 거짓을 반환하는 조건식 기술, 해당 조건이 참일 경우 THEN 절에 기술한 식이 처리됨

WHEN 절의 조건식을 차례로 평가해 나가다가 가장 먼저 조건을 만족한 WHEN 절과 대응하는 THEN 절 식의 처리결과를 CASE문의 결과값으로 반환

그 어떤 조건식도 만족하지 못한 경우 ELSE 절에 기술한 식이 채택됨

ELSE 생략 가능, 생략했을 경우 'ELSE NULL'로 간주됨

 

 

ex) NULL 값을 0으로 변환하기

a

1

2

NULL

 

SELECT a, CASE WHEN a IS NULL THEN 0 ELSE a END "a(null=0)" FROM 테이블명;

a	a(null=0)
1	1
2	2
NULL	0

a 열 값이 NULL일 때, WHEN a IS NULL → 참이므로 CASE 문은THEN 절의 '0' 반환

NULL이 아닐 경우 a 열 값 반환

 

- COALESCE

NULL 값을 변환하는 경우 COALESCE 함수를 사용하면 더 쉬움

앞의 경우, 아래와 같이 표현 가능

SELECT a, COALESCE(a, 0) FROM 테이블명;

COALESCE 함수는 여러 개의 인수를 지정할 수 있음

주어진 인수 가운데 NULL이 아닌 값에 대해서는 가장 먼저 지정된 인수의 값 반환

 

2. 또 하나의 CASE 문

<디코드(문자화)>

코드=수치데이터 ⇒ 정보

 

<인코드(수치화)>

코드=수치데이터 ⇐ 정보

 

CASE 문에는 2개의 구문이 있음

'검색 CASE' , '단순 CASE'

검색 CASE : 'CASE WHEN 조건식 THEN 식... '

단순 CASE : 'CASE 식 WHEN 식(조건식이 아닌) THEN 식...'

 

ex) 

<검색 CASE>

SELECT a AS "코드",
CASE
    WHEN a=1 THEN '남자'
    WHEN a=2 THEN '여자'
    ELSE '미지정'
END AS "성별" FROM 테이블명;

 

<단순 CASE>

SELECT a AS "코드",
CASE a
    WHEN 1 THEN '남자'
    WHEN 2 THEN '여자'
    ELSE '미지정'
END AS "성별" FROM 테이블명;

 

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

CASE 문은 SELECT 구, WHERE 구, ORDER BY 구 등 어디에나 사용할 수 있음

 

- ELSE 생략

ELSE를 생략하면 ELSE NULL이 됨

대응하는 WHEN이 하나도 없으면 ELSE 절이 사용됨

ELSE를 생략하면 상정한 것 이외의 데이터가 왔을 때 NULL이 반환되기에 ELSE를 생략하지 않고 지정하는 것이 좋음!

 

- WHEN에 NULL 지정하기

데이터가 NULL인 경우를 고려하여

WHEN NULL THEN '데이터 없음'과 같이 지정해도 문법적으로는 문제가 없으나 정상적으로 처리되지 않음

 

CASE a
    WHEN 1 THEN '남자'
    WHEN 2 THEN '여자'
    WHEN NULL THEN '데이터 없음'
    ELSE '미지정'
END

 이 경우,

① a = 1

② a = 2

③ a = NULL

위와 같이 조건식 처리

 

비교 연산자 = 로는 null 값과 같은지 아닌지 비교할 수 없음

a 의 열 값이 NULL이라고 해도 a = NULL이 참이 되지 않음

 

NULL 값인지 아닌지 판정하기 위해서는 IS NULL 을 사용하여야 함

단순 CASE 문은 특성상 = 연산자로 비교하기에, NULL 값인지를 판정하기 위해서는 검색 CASE 문을 사용해야 함

단순 CASE 문으로는 NULL 값을 비교할 수 없음