3. 정렬과 연산

09. 정렬

 

ORDER BY

SELECT [원하는 열] FROM [table name] WHERE [조건] ORDER BY [원하는 열];

: 지정한 열의 값에 따라 행의 순서 정렬

* ORDER BY는 행 순서를 변형할 수는 있지만 서버에서 클라이언트로 행 순서를 바꿔 출력하는 것으로 저장장치에 영향을 주지는 않음

 

ASC/DESC

SELECT [원하는 열] FROM [table name] WHERE [조건] ORDER BY [원하는 열] ASC;

: 오름차순으로 정렬 의미, ASC의 경우 생략 가능하며 ORDER BY의 기본 정렬 방법

 

SELECT [원하는 열] FROM [table name] WHERE [조건] ORDER BY [원하는 열] DESC;

: 내림차순으로 정렬

 

대소관계

- 작은 것 부터 정렬: ASC

- 큰 것 부터 정렬: DESC

 

| 수치형: 1 < 5 < ... < 100, 작은 수부터 큰 수로 정렬

 

| 날짜/시간형: 1999년 < 2010년 < ... < 2050년, 과거부터 최근으로 정렬

 

| 문자열형: ㄱ < ㄷ < ... < ㅎ, 사전 순서에 의해 나열

mysql> SELECT * FROM t1 ORDER BY a; // 문자열형 나열
mysql> SELECT * FROM t2 ORDER BY b; // 수치형 나열 (b=1, 2, 3...)

* 쿼리의 형태에 따라 대소관계 형 변형

 

10. 복수의 열을 지정해 정렬

 

복수 열 지정

SELECT [원하는 열] FROM [table name] WHERE [조건] ORDER BY [원하는 열1, 2, ...];

: 복수 지정을 원하는 열을 쉼표(,)로 구분하여 나열

 

정렬방법 지정

SELECT [원하는 열] FROM [table name] WHERE [조건] ORDER BY [원하는 열1 ASC, 원하는 열2 DESC, ....];

: 복수 열 지정 시 각 열에 대해 개별적인 정렬 방법 지정 가능

 

NULL 정렬

ORDER BY 정렬 시 지정한 열에서 NULL 값은 가장 먼저 혹은 가장 나중에 출력

NULL 대소비교는 표준 SQL에 규정되지 않아 DB에 따라 기준이 상이

* MySQL에서는 NULL을 가장 작은 값으로 취급

 

11. 결과 행 제한하기

 

LIMIT

SELECT [원하는 열] FROM [table name] LIMIT [row number] [OFFSET start row number];

: LIMIT 구문으로 결과 반환 행을 제한

* 표준 SQL이 아니므로 MySQL과 PostgreSQL 외 DB에서는 사용 불가

 

| 행 수 제한: SELECT [원하는 열] FROM [table name] WHERE [조건] ORDER BY [원하는 열] LIMIT [row number];

* 이때 row number는 변환할 최대 행수

 

| TOP: SELECT TOP [row number] * FROM [talbe name];

* SQL 서버에서 사용하며 row number를 사용해 최대 행수 지정

 

| ROWNUM: SELECT [원하는 열] FROM [table name] WHERE ROWNUM [<= row number];

* 이때 row number는 각 클라이언트에게 결과가 반환될 때 행에 할당되는 번호로 WHERE 구문으로 지정하므로 정렬하기 전에 LIMIT 행을 제한한 경우와 결과값이 상이

 

오프셋

대량의 데이터를 나타낼 때 주로 사용하는 페이지 나누기(pagination) 기능을 사용

SELECT [원하는 열] FROM [table name] LIMIT [row number] OFFSET [location];

* 이때, location은 행의 시작 위치를 의미하며, 인덱스는 0부터 시작

mysql> SELECT * FROM t1 LIMIT 5 offset 0; // 1부터 상위 5개 행 반환
mysql> SELECT * FROM t2 LIMIT 3 offset 2; // 3부터 상위 3개 행 반환

* offset 0이 기본 설정이므로 생략 가능

 

12. 수치 연산

 

사칙 연산

- 덧셈: +

- 뺄셈: -

- 곱셈: *

- 나눗셈: /

- 나머지: %, 몫이 정수값이 되도록 계산하며 %대신 MOD 함수를 사용하는 DB 제품도 있음

 

연산자 우선순위

*, /, % > +, -

: SQL 명령에서는 SELECT, WHERE 구에서도 산술 연산자 사용 가능

 

SELECT 구문 연산

SELCT [원하는 열1] [연산자] [원하는 열2] FROM [table name];

mysql> SELECT price*quantity FROM t1;

 

AS

원하는 열의 이름을 별명으로 지정해 사용하며 ASCII 문자 이외 것을 포함하는 경우 더블쿼트(")로 둘러싸 지정

단, Oracle을 사용하는 경우 이름을 지정할 때 숫자로 시작하지 않도록 주의

 

더블쿼트(")

명령구문을 분석할 때 데이터베이스 객체의 이름으로 인식하도록 지정

단, 싱클쿼트(')의 경우 문자열 상수로 인식

 

WHERE 구문 연산

WHERE 구문의 조건식에서 수치 연산을 포함한 연산 가능

단, 데이터베이스 서버 내부에 WEHRE에서 SELECT 순서로 구문 처리가 이루어지므로 SELECT에서 붙인 별명은 사용 불가

mysql> SELECT price*quantity AS amount FROM t1 WHERE amount >= 10000;

 

NULL 연산

NULL 연산의 결과는 항상 NULL로 반환

 

ORDER BY 구문 연산

ORDER BY 구문 내에서도 식 형태의 연산 처리가 가능하며 가장 마지막에 처리되므로 SELECT 구문에서 지정한 별명 사용 가능

mysql> SELECT * price*quantity AS amount FROM t1 ORDER BY price*quantity DESC;

 

DB 서버의 내부처리 순서

WEHRE > SELECT > ORDER BY

: SELECT 구문에서 별명을 지정하므로 SELECT 구문에서 별명 적용 가능

 

함수

연산자 외 함수를 이용한 연산 가능

 

| ROUND: 반올림 함수로 첫번째 인자로 반올림할 열을  선정한 후 반올림할 자릿수를 지정

단, 두번째 인자가 없는 경우 기본값 0으로 간주

* 외에 버림은 TRRUNCATE, 삼각함수는 SIN, COS, 외에도 SQRT, LOG 등의 함수 사용 가능

 

13. 문자열 연산

 

문자열 결합

데이터베이스는 제품마다 조금씩 상이한 방언을 사용

이때, 수치 데이터도 결합 가능하지만 결합 결과는 문자열형으로 표현

 

- SQL 서버: +를 사용해 표현

- Oracle, DB2, PostgresSQL: ||을 사용해 표현

- MySQL: CONTACT 사용해 표현

 

SUBSTRING

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

데이터베이스에 따라 함수명이 SUBSTR로 사용하는 경우도 있음

mysql> SELECT SUBSTRING('20241005',1,4), SUBSTRING('20241005',5,2); // 앞 4자리 추출, 5째 자리부터 2자리 추출

 

TRIM

문자열의 앞뒤로 여분의 공백이 있는 경우 공백을 제거하는 함수로 문자열 도중에 존재하는 공백은 제거하지 않음

CHAR형 문자열과 같이 문자열의  길이가 고정되어있을 경우 빈 공간을 채우기 위해 사용딘 공백을 제거하는 데 사용

 

CHARACTER_LENGTH

문자 수를 계산하여 반환하는 함수로 CHARACTER_LENGTHg 함수는 바이트 단위로 길이 계산

 

- 반각문자: 알파벳, 숫자, 기호와 같은 ASCII 문자

- 전각문자: 한글과 같은 문자로 한글은 EUC-KR 혹은 UTF-8 등의 인코드 방식 사용

* 반각문자는 전각문자 폭의 절반으로 저장용량 또한 전각문자의 절반만 차지

 

인코드 방식

데이터베이스나 테이블을 정의할 때 변경가능 하며 RDBMS에서는 문자세트로 명칭

한 문자가 몇 바이트인지 쓰이는 문자세트에 따라 상이

 

- EUC-KR에서 ASCII 문자는 1byte, 한글은 2byte

- UTF-8에서 ASCII 문자는 1byte, 한글은 3byte

 

14. 날짜 연산

 

CURRENT_TIMESTAMP

SQL에서의 시스템 날짜를 확인하는 함수로 실행을 기준으로 시간을 반환하며 타 함수와 달리 인수 지정 불필요

mysql> SELECT CURRENT_TIMESTAMP;

* 표준 SQL로 규정되어있는 함수로 Oracle에서는 SYSDATE 함수, SQL 서버에서는 GETDATE 함수 사용

 

날짜 서식

날짜를 표기하는 방식이 다양하여 대부분의 데이터베이스 제품은 날짜 데이터의 서식을 임의로 지정하고 변환할 수 있는 함수를 지원

SELECT TO_DATE('2024/10/05','YYYY/MM/DD') // Oracle에서 문자열 데이터를 서식에 맞춰 반환
SELECT TO_CHAR(SYSDATE,'YYYYMM') // Oracle에서 날짜형 데이터를 서식에 맞춰 문자열 데이터로 반환

SELECT STR_TO_DATE('2024-10-05','%Y-%M-%d') // MySQL에서 문자열 데이터를 날짜형으로 변환
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %T') // MySQL에서 날짜형 데이터를 서식에 맞춰 반환

 

날짜의 연산

날짜시간형 데이터는 기간형 수치데이터와 연산 가능

SELECT CURRENT_DATE [연산 기호] [연산할 수치데이터];

mysql> SELECT CURRENT_DATE + INTERVAL 1 DAY;

* CURRENT_DATE는 시스템날짜를 확인하는 함수

* INTERVAL 1 DAY는 '1일 후'라는 의미의 기간형 상수

SELECT TO DATE('2024-10-05 18:08:00','YYYY_MM_DD HH24:MI:SS') - TO_DATE('2022-03-01 14:00','YYYY_MM_DD HH24:MI:SS') // Oracle에서 날짜형 간의 뺄셈

DATEDIFF('2024-10-05','2022-03-01') // MySQL에서 날짜형 간의 뺄셈

 

CASE문

NULL 값을 0으로 간주하여 계산하고 싶은 경우 사용자 정의 함수 없이 CASE문으로 처리 가능

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

단, ELSE는 생략하는 경우 ELSE NULL로 간주

 

| WHEN 구문: 참과 거짓을 반환하는 조건식을 기술

 

| THEN 구문: 참이 되는 경우 THEN 구문에 해당하는 식을 처리

* 조건식을 차례로 평가하다 가장 먼저 조건을 만족한 WHEN절과 대응하는 THEN절 식의 결과를 CASE 결과로 반환

mysql> SELECT a, CASE WHEN a IS NULL THEN 0 ELSE a END "a(null=0)" FROM t1;

* CASE문의 경우 SELECT외에 WHERE 구에서 조건식의 일부로 사용되기도 하며 ORDER BY에서도 사용 가능

 

COALESCE

NULL값을 반환하는 경우 간단히 사용 가능한 함수

mysql> SELECT a, COALESCE(a,0) FROM t1; // NULL이 아니면 a, NULL이면 0 출력

 

CASE문 종류

- 검색 CASE: CASE WHEN [조건식] THEN [식];

- 단순 CASE: CASE [식1] / WHEN [식2] THEN [식3] / WHEN [식4] THEN [식5] / ELSE [식6] / END;

* 식1의 값이 WHEN 식 2의 값과 동일한지 비교하고 같으면 식3, 그렇지 않으면 뒤의 WHEN 절과 비교하여 반환

mysql>
SELECT a AS "code",
   CASE
     WHEN a=1 THEN 'M'
     WHEN a=2 THEN 'F'
     ELSE 'Unkown'
   END AS "gender"
FROM t1; // 검색 CASE문으로 성별 코드 변환

mysql>
SELECT a AS "code",
   CASE a
     WHEN 1 THEN 'M'
     WHEN 2 THEN 'F'
     ELSE 'Unkown'
   END AS "gender"
FROM t1; // 단순 CASE문으로 성별 코드 변환

* 단순 CASE 문에서는 NULL 값을 비교할 수 없으며 비교연산자 만으로는 NULL 값과 같은지 판정하기 어려워 IS NULL 사용

 

기타 Oracle 내장 함수

- DECODE NVL: 디코드를 수행하는 함수

- NVL: NULL 값을 변환하는 함수