8 min read

SQL 첫걸음 3장

0001-01-01

9강: 정렬-ORDER BY

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

WHERE 구 뒤에 ORDER BY 구를 지정하는 경우:
SELECT 열명 FROM 테이블명 WHERE 조건식 ORDER BY 열명;

FROM 구 뒤에 ORDER BY 구를 지정하는 경우:
SELECT 열명 FROM 테이블명 ORDER BY 열명;

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

내림차순으로 정렬:
SELECT 열명 FROM 테이블명 ORDER BY 열명 DESC;

오름차순으로 정렬:
SELECT 열명 FROM 테이블명 ORDER BY 열명 ASC;

3. 대소관계

수치형, 날짜시간형 데이터의 경우 숫자 크기로 판별한다. 문자형의 경우 알파벳이나 한글 자모음 배열순서로 판별하게 된다.(사전식 순서)

  • 사전식 순서에서 주의할 점 수치형과 무자열형 데이터는 대소관계의 계산방법이 다르다.

4. ORDER BY는 테이블에 영향을 주지 않는다 이 함수를 이용해서 데이터의 행 순서를 바꿀 수 있다. 하지만 서버에서 클라이언트로 행 순서를 바꾸어 결과를 반환하는 것뿐, 저장장치에 저장된 데이터의 행 순서를 변경하는 것은 아니다. SELECT 명령은 데이터를 검색하는 명령어다. 데이터 테이블 참조만 할 뿐 변경은 하지 않는다.

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

SELECT 열명 FROM 테이블명 WHERE 조건식 ORDER BY 열명 [ASC|DESC]

1. 복수 열로 정렬 지정

SELECT * FROM SAMPLE ORDER BY A;

위의 코드처럼 한 열만 선택해서 열을 정렬할 수 있다 기본적으로 오름차순으로 정렬된다.

  • ORDER BY로 복수 열 지정하기
SELECT 열명 FROM 테이블명 ORDER BY 열명1, 열명2 ....

EX)
SELECT * FROM SAMPLE ORDER BY A,B;

열을 여러개 지정해서 정렬을 시도하면 a열이 먼저 정렬되고 그 후 b열이 정렬된다. 정렬 순서도 중요하기 때문에 주의깊게 확인할 필요가 있다.

2. 정렬방법 지정하기

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

구문에서 []는 생략할 수 있는 옵션이다. 둘 중 하나를 선택해주면 된다.

EX)
SELECT * FROM SAMPLE ORDER BY A ASC, B DESC;

이렇게 해주면 a열은 오름차순, b열은 내림차순으로 정렬된다.

3. NULL값의 정렬순서

NULL값은 데이터베이스 제품에 따라 다르게 정렬되는데, MySQL에서는 가장 작은 값으로 취급되서 오름차순으로 하면 가장 먼저, 내림차순은 가장 나중에 표시한다.

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

SELECT 열명 FROM 테이블명 LIMIT 행수 [OFFSET 시작행]

1. 행수 제한

LIMIT은 표준 SQL은 아니다. MySQL과 PostgreSQL에서만 사용가능하다.

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

EX)
SELECT * FROM SAMPLE LIMIT 3;

위의 예제대로 코드를 실행하면 상위 3개의 행만 보여준다. 만약 테이블의 행수보다 많이 지정하면 테이블 행수만큼만 뽑히게 된다.

  • 정렬한 후 제한하기

WHERE 구에서도 LIMIT과 같은 효과를 나타낼 수 있다. 위의 예제대로 뽑히기 위해선 WHERE 열명 <= 3 같은 조건을 붙여주면 된다. 둘의 내부처리 순서가 다르다. LIMIT은 반환할 행수를 제한하는 기능이고, WHERE은 검색한 후 ORDER BY로 정렬된 뒤 최종적으로 처리된다.

SELECT * FROM SAMPLE ORDER BY NO DESC LIMIT 3;

이 코드를 실행시키면 order by가 먼저 실행된 후 limit 구문이 실행되는 결과를 볼 수 있다.

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

LIMIT은 표준 SQL 언어가 아니기 때문에 특정 데이터베이스 제품 외에는 사용할 수 없다. 대신에 TOP이라는 구문을 통해 LIMIT와 같은 결과값을 나타낼 수 있다.

SELECT TOP 3 * FROM SAMPLE;

Oracle에는 limit랑 top이 없다. 대신에 ROWNUM이라는 열을 사용해 where 구로 조건을 지정하여 행을 제한할 수 있다.

SELECT * FROM SAMPLE WHERE ROWNUM <=3,

ROWNUM은 클라이언트에게 결과가 반활될 때 각 행에 할당되는 행 번호다. 단 이걸로 행을 제한할 때 WHERE 구로 지정하므로 정렬하기 전에 처리되어 LIMIT으로 행을 제한하는 경우와 결과값이 다르다.

2. 오프셋 지정

오프셋 지정을 통해서 제한할 행을 어디서부터 시작할 지 정할 수 있다.

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

OFFSET의 기본값은 0이다.

12강: 수치 연산

1. SELCET 구로 연산하기

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

EX)
SELECT *, PRICE * QUANTITY FROM SAMPLE;

위의 코드를 실행시키면 PRICE * QUANTITY라는 새로운 열이 생기고 PRICE와 QUANTITY의 곱이 속성값이 된다.

3. 열의 별명

위의 예제대로 실행시키면 PRICE * QUANTITY라는 열명으로 열이 생긴다. 그러나 열명이 마음에 들지 않을 경우 바꿔줄 필요가 있다.

SELECT *, PRICE * QUANTITY AS AMOUNT FROM SAMPLE;

select는 콤마로 구분해 복수의 식을 지정할 수 있고 각각의 별명을 지정할 수 있다. MySQL에서는 별명을 중복해서 지정해도 에러가 발생하진 않지만, 나중에 결과값을 처리할 때 문제가 생길 수 있으니 중복되지 않도록 지정해주는게 중요하다. AS는 생략해줄 수 있다. 별명은 영어, 한국어, 숫자 등으로 나타낼 수 있다. 다만 한국어로 별명을 설정하게 되면 오류가 생길 가능성이 있기 때문에 ““로 처리해주는게 좋다. ASCII 문자 이외의 것을 포함할 경우에는 ““로 둘러싸서 지정해줘야 한다.

”“로 둘러싸면 명령구문을 분석할 때 데이터베이스 객체의 이름이라고 간주하고, “로 둘러싸면 문자열 상수로 간주한다.

4. WHERE 구에서 연산하기

EX)
SELECT *, PRICE * QUANTITY AS AMOUNT FROM SAMPLE WHERE PRICE * QUANTITY>=2000;

위의 코드를 실행시키면 select에서 계산한 값을 기준으로 2000보다 큰 행만 뽑아준다. where 구문에서는 별명이 아닌 원래 열명으로 설정해줘야 한다.

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

where 구에서의 행 선택, select 구에서 열 선택은 데이터베이스 서버 내부에서 where구->select구 순서로 처리된다. 표준 SQL에서는 내부처리 순서가 정해진건 아니지만, 보통 where->select 순으로 내부처리가 진행되는 데이터베이스 제품들이 많다. 따라서 select에서 지정한 별명은 where에서 사용할 수 없다.

5. NULL 값의 연산

null값의 경우 어떠한 연산을 하더라도 결과값은 Null이 나온다.

6. ORDER BY 구에서 연산하기

EX)
SELECT *, PRICE * QUANTITY AS AMOUNT FROM SAMPLE ORDER BY PRICE * QUANTITY DESC;

order by는 서버에서 내부적으로 가장 나중에 처리된다. select보다 더 늦게 처리되기 때문에 select에서 지정한 별명을 사용할 수 있다. where->select->order by순서로 진행된다.

7. 함수

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

연산자를 사용할 수도 있지만, 함수명을 사용해서 계산할 수 있다. 둘의 값은 표기만 다르고 값은 같게 나온다.

8. ROUND 함수

ROUND는 반올림 해주는 함수다.

EX)
SELECT AMOUNT, ROUND(AMOUNT) FROM SAMPLE;

이렇게 해주면 소수점 이하는 반올림 된다. 소수점을 지정할 수 있는 자료형을 DECIMAL이라고 부른다.

  • 반올림 자릿수 지정

ROUND 함수는 기본적으로 소수점 첫째 자리에서 반올림해준다.

EX)
SELECT AMOUNT, ROUND(AMOUNT,1) FROM SAMPLE;

위의 예제처럼 코드를 실행시키면 소수점 2번째에서 반올림 해준다. 음수로도 반올림할 수 있다. -1로 지정하면 1단위, -2로 지정하면 10단위를 반올림할 수 있다. 반버림 함수는 TRUNCATE 함수를 써주면 된다.

13강: 문자열 연산

  1. 문자열 결합

문자열을 결합하는 연산자는 데이터베이스 제품마다 방언이 있다. SQL Server는 +, Oracle,DB2,PostgreSQL은 “||”, MySQL에서는 CONCAT을 사용한다.

EX)
SELECT CONCAT(QUANTITY,UNIT) FROM SAMPLE;

quantity는 숫자형, unit은 문자형 데이터다. 이 두개를 합치게 되면 문자열로 처리된다.

2.SUBSTRING 함수

substring은 문자열의 일부분을 계산해서 반환해주는 함수다. 데이터베이스 제품에 따라 다르지만 SUBSTR인 경우도 있다.

EX)
SUBSTRING('20140125001',1,4)-> '2014'
SUBSTRING('20140125001',5,2)->'01'

3. TRIM 함수

문자열 앞뒤로 여분의 스페이스가 있을 경우 이를 제거해주는 함수다. 문자열 중간에 있는 스페이스는 제거되지 않는다.

TRIM('ABC    ')->'ABC'

4. CHARACTER_LENGTH 함수

문자열의 길이를 계산해 돌려주는 함수다. 수치로 반환해준다. OCTET_LENGTH 함수는 문자열의 길이를 바이트 단위로 계산해서 돌려주는 함수다.

  • 문자세트(Character set)

인코드 방식은 데이터베이스나 테이블을 정의할 때 변경할 수 있다. 이를 RDBMS에서는 ‘문자세트’라고 부른다. ‘한 문자가 몇 바이트인지는 쓰이는 문자세트에 따라 다르다’가 핵심이다.

14강: 날짜 연산

  1. SQL에서의 날짜

날짜나 시간 데이터는 수치 데이터와 같이 사칙 연산을 할 수 있다. 날짜시간 데이터를 연산하면 결괏값으로 동일한 날짜시간 유형의 데이터를 반환하는 경우랑 기간의 차를 나타내는 기간형 데이터를 반환하는 경우가 있다.

  • 시스템 날짜

표준 SQL에서는 ‘CURRENT_TIMESTAMP’라는 함수로 실행했을 때를 기준으로 시간을 표시한다.

SELECT CURRENT_TIMESTAMP;

CURRENT_TIMESTAMP는 FROM 구를 생략하고 SELECT 구만 사용해도 된다. 그러나 ORACLE 같은 전통적 데이터베이스는 FROM 구를 생략할 수 없다.

  • 날짜 서식

CURRENT_TIMESTAMP로 현재 날짜를 저장할 수 있지만, 특정 날짜를 저장하고 싶으면 직접 날짜를 지정해야한다. 날짜 표기하는 방식은 나라마다 다르다. 따라서 TO_DATE라는 코드를 사용하면 문자열 데이터를 날짜형 데이터로 변환할 수 있고, 서식 또한 별도로 저장할 수 있다.

TO_DATE('2015/05/09','YYYY/MM/DD')

‘YYYY/MM/DD’는 서식 부분이다. 반대로 날짜형 데이터를 서식에 맞춰 변환해 문자열 데이터로 출력하는 함수도 존재한다. ORACLE에서는 TO_CHAR 함수가 그에 해당된다.

2. 날짜의 덧셈과 뺄셈

SELECT CURRENT_DATE+INTERVAL 1 DAY;

CURRENT_DATE는 시스템 날짜의 날짜만 확인하는 함수다. INTERVAL 1 DAY 는 ‘1일 후’라는 의미의 기간형 상수다. 기간형 상수의 기술방법은 데이터베이스마다 다르다.

  • 날짜형 간의 뺄셈

날짜시간형 데이터 간에 뺄셈을 할 수 있다. MySQL에서는 DATEDIFF(‘2014-02-28’, ‘2014-01-01’)로 계산할 수 있다.

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

1. CASE 문

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

WHEN 절에서는 참과 거짓을 반환하는 조건식을 기술한다. 해당조건이 참이 되는 경우는 THEN 절에 기술한 식이 처리된다. 이때 WHEN과 THEN을 조합해서 지정할 수 있다. WHEN 절의 조건식을 차례로 평가해 나가다가 가장 먼저 조건을 만족한 WHEN 절과 대응하는 THEN 절 식의 처리결과를 CASE 문의 결과값으로 반환한다. 그 어떤 조건식을 맞족하지 못한 경우에는 ELSE 절에 기술한 식이 채택된다. ELSE는 생략이 되고 생략될 경우 ELSE NULL로 간주된다.

EX) NULL값을 0으로 반환하기
SELECT A, CASE WHEN A IS NULL THEN 0 ELSE A END "A(NULL=0)" FROM SAMPLE;
  • COALESCE

NULL값을 반환하는 경우에는 COALESCE를 사용하는 것이 더 쉽다.

SELECT A, COALESCE(A,0) FROM SAMPLE;

이 함수에는 여러 개의 인수를 저장할 수 있다. 주어진 인수 가운데 NULL값이 아닌 값에 대해서는 가장 먼저 지정된 인수의 값을 반환한다.

2. 또 하나의 CASE 문

문자화하는 것을 ‘디코드’라고 부르고 숫자화하는 것을 ‘인코드’라고 부른다.

WHEN A=1 THEN 'MAN'
WHEN A=2 THEN 'WOMAN'

CASE문에는 ‘검색’과 ‘단순’ CASE문 두가지로 나눌 수 있따. 검색 CASE는 앞에서 말한것처럼 ‘CASE WHEN 조건식 THEN 식…‘형태를 가지고 있고, 단순 CASE는 ‘CASE 식 WHEN 식 THEN 식…‘으로 이루어져 있다.

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

식1의 값이 when 식2 값과 동일한지 비교하고, 값이 같다면 식3의 값이 case문 전체의 결괏값이 된다. 값이 같지 않으면 그 뒤에 기술한 when과 비교하는 식으로 진행된다. 즉 식1과 식4의 값이 같은지를 비교하고 같다면 식5의 값이 case문의 결괏값이 되는 것이다. 비교 결과가 일치하는 when절이 하나도 없으면 else값이 나온다.

EX) 성별 코드 변환하기(검색 CASE)

SELECT A AS "코드",
CASE
  WHEN A=1 THEN 'MAN'
  WHEN A=2 THEN 'WOMAN'
  ELSE '미지정'
END AS "SEX" FROM SAMPLE;

EX) 성별 코드 변환하기(단순 CASE)

SELECT A AS "코드",
CASE A
  WHEN 1 THEN 'MAN'
  WHEN 2 THEN 'WOMAN'
  ELSE '미지정'
END AS "SEX" FROM SAMPLE;

이 두 가지 CASE 구문의 결과는 같게 나올 것이다.

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

CASE문은 SELECT 구 뿐만 아니라 다른 구에서도 사용가능하다.

  • ELSE 생략

CASE 문에서 ELSE는 생략할 수 있지만, 생략하지 말고 사용하는 것을 추천한다.

  • WHEN에 NULL 지정하기

위에 예제처럼 NULL을 지정하면 ‘WHEN NULL THEN’이 된다. 문법적으로는 아무런 문제가 없지만 정상적으로 작동되진 않는다.

CASE A
  WHEN 1 THEN 'MAN'
  WHEN 2 THEN 'WOMAN'
  WHEN NULL THEN 'NO DATA'
  ELSE '미지정'
END AS "SEX" FROM SAMPLE;

비교 연산자 =로는 NULL값을 비교할 수 없다. 따라서 A가 NULL이더라도 참이 되지 않는다. 따라서 비교 연산자 대신에 IS NULL을 사용해서 나타내주면 된다.

CASE A
  WHEN 1 THEN 'MAN'
  WHEN 2 THEN 'WOMAN'
  WHEN IS NULL THEN 'NO DATA'
  ELSE '미지정'
END AS "SEX" FROM SAMPLE;
  • DECODE NVL

Oracle에는 이 같은 디코드를 수행하는 함수가 내장되어 있다. 디코드 함수는 CASE문과 같은 용도로 사용가능하다. 이 함수는 Oracle에서만 지원해주기 때문에 다른 데이터베이스 제품에서는 사용할 수 없다. CASE는 표준 SQL이기 때문에 많은 데이터베이스 제품에서 지원한다.

NULL값을 변환해주는 함수도 있다. Oracle에서는 NVL 함수, SQL Server에서는 ISNULL 함수다. 그러나 이러한 함수들은 표준 SQL 언어가 아니기 때문에 표준 SQL 언어인 COALESCE를 사용해주는게 좋다.