[LG CNS 6기] 본 과정 20일차 TIL / [DB] - SQL 함수

1. 오늘의 한 줄 요약

SQL 함수 단일행/복수행과 문자열, 숫자, 날짜, 흐름제어 함수 실습!

2. 오늘 배운 것

2.1 함수의 개념과 유형

함수는 하나의 큰 프로그램에서 반복적으로 쓰는 부분을 분리한 작은 서브 프로그램이다.
호출하면 반환값을 받는다.

함수는 크게 두 갈래로 나뉜다.

구분	특징	예시
단일행 함수	행마다 한 번씩 적용, 결과 행 수 유지	문자, 날짜, 숫자, 형변환 함수
복수행 함수(그룹)	여러 행을 입력받아 하나(또는 그 이상)로 축약	COUNT, MIN, MAX, AVG, SUM

2.2 문자열 함수

컬럼 타입은 문자/날짜/숫자로 나뉘고, 문자 타입은 다시 CHAR(고정 길이)와 VARCHAR(가변 길이)로 갈린다.

SELECT LENGTH('홍길동'), CHAR_LENGTH('홍길동');
-- LENGTH: 9 (바이트), CHAR_LENGTH: 3 (글자 수)

한글은 UTF-8에서 한 글자가 3바이트라서, LENGTH()(바이트 기준)와 CHAR_LENGTH()(글자 수 기준)의 결과가 다르게 나온다.

그 외 문자열 함수는 아래처럼 정리했다.

함수	역할
TRIM / LTRIM / RTRIM	양쪽 / 왼쪽 / 오른쪽 공백 제거
LPAD / RPAD	지정한 길이만큼 왼쪽 / 오른쪽을 문자로 채움
SUBSTRING(str, 시작, 길이)	부분 문자열 반환 (인덱스는 1부터)
LEFT / RIGHT	왼쪽 / 오른쪽에서 n글자
INSTR(str, 찾을것)	부분 문자열의 시작 위치 반환, 없으면 0

SELECT LEFT(EMAIL, INSTR(EMAIL, '@') - 1)
FROM employee;

INSTR로 @의 위치를 찾고, LEFT로 그 앞부분만 잘라서 이메일 아이디를 뽑아냈다.
함수는 이렇게 안쪽부터 중첩해서 쓸 수 있다.

2.3 CAST 타입

문자와 숫자를 섞어 쓰면 CAST로 타입을 명시해야 하는 경우가 있다.

SELECT CAST(AVG(AMOUNT) AS SIGNED INTEGER)
FROM buytbl;

AVG(AMOUNT)는 실수로 나오는데, 정수로 강제 변환할 때는 CAST(값 AS 타입) 문법을 쓴다.
INT(...)처럼 타입 이름을 함수처럼 바로 호출하면 에러가 난다.

2.4 숫자 함수

오늘 다룬 숫자 함수는 아래와 같다.

함수	역할
ABS(x)	절댓값
CEILING(x)	올림
FLOOR(x)	내림
ROUND(x, n)	n번째 자리까지 반올림
TRUNCATE(x, n)	n번째 자리까지 자르기 (반올림 없음)
GREATEST(...)	인수 중 최댓값
LEAST(...)	인수 중 최솟값

ROUND와 TRUNCATE는 둘 다 자릿수를 지정하는데, 자릿수가 음수면 소수점이 아니라 정수 자리(십의 자리, 백의 자리...) 기준으로 처리한다.

SELECT ROUND(4153.415354, -2), TRUNCATE(4153.415354, -2);
-- 4200, 4100

2.5 날짜 함수

NOW()와 SYSDATE()는 둘 다 현재 시각을 반환하지만, NOW()는 문장이 시작한 시점으로 고정되고 SYSDATE()는 호출되는 순간마다 값이 바뀔 수 있다.

오늘 다룬 날짜 함수는 아래와 같다.

함수	역할
NOW()	현재 날짜+시간 (문장 시작 시점으로 고정)
SYSDATE()	현재 날짜+시간 (호출 시점마다 값이 바뀔 수 있음)
CURDATE()	현재 날짜만
CURTIME()	현재 시간만
ADDDATE(날짜, INTERVAL n 단위)	날짜 더하기
SUBDATE(날짜, INTERVAL n 단위)	날짜 빼기
YEAR() / MONTH() / DAY()	날짜에서 연/월/일만 추출

날짜에 기간을 더하고 뺄 때는 ADDDATE/SUBDATE + INTERVAL을 쓴다.

SELECT ADDDATE(CURDATE(), INTERVAL 30 YEAR);

요일을 숫자로 반환하는 함수는 두 개인데, 기준이 반대다.

함수	반환 범위	기준
DAYOFWEEK()	1~7	일요일 = 1
WEEKDAY()	0~6	월요일 = 0

같은 날짜를 넣어도 두 함수의 숫자가 다르게 나오기 때문에, 요일 문자열이 필요하면 DAYNAME()을 따로 쓰는 게 더 간단하다.

2.6 DDL/DML

DDL은 Data Definition Language(정의어)이다. 테이블처럼 데이터를 담는 틀 자체를 만들고 바꿀 때 쓴다.
DML은 Data Manipulation Language(조작어)이다. 그 틀 안의 데이터를 넣고 바꾸고 지울 때 쓴다.

CREATE TABLE COUPON_TBL(
  CREATE_AT DATE,
  END_AT DATE
);

INSERT INTO COUPON_TBL(CREATE_AT, END_AT)
VALUES(NOW(), ADDDATE(NOW(), INTERVAL 7 DAY));

CREATE TABLE(DDL)로 테이블 뼈대를 만들고 INSERT INTO ~ VALUES(DML)로 데이터를 채워 넣었다.

2.7 흐름 제어 함수

IF, IFNULL, NULLIF, CASE ~ WHEN ~ THEN ~ END을 배웠다.
CASE는 ANSI SQL 표준이라 대부분의 DBMS에서 쓸 수 있다는 점이 다른 함수와 다르다.

SELECT EMP_NAME, EMP_NO,
CASE
  WHEN SUBSTRING(EMP_NO, 8, 1) IN ('1','3') THEN '남자'
  WHEN SUBSTRING(EMP_NO, 8, 1) IN ('2','4') THEN '여자'
  ELSE '?'
END AS GENDER
FROM employee;

2.8 복수행 함수

COUNT, MIN, MAX, AVG, SUM은 여러 행을 입력받아 하나(또는 그 이상)의 결과로 반환한다.

• WHERE 절에는 복수행 함수를 쓸 수 없다. 어제 정리한 처리 순서(FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY)를 보면, WHERE는 GROUP BY보다 먼저 실행되기 때문에 그 시점엔 아직 그룹(집계값)이 만들어지지 않아서다. 그룹 결과에 조건을 걸고 싶으면 대신 HAVING을 쓴다.
• SELECT 절에서는 쓸 수 있지만, 일반 컬럼과 함께 쓰려면 GROUP BY가 필요하다.

2.9 ORDER BY

ORDER BY [기준컬럼 | 표현식 | 컬럼인덱스 | 컬럼 별칭] ASC | DESC

SELECT에서 만든 별칭을 ORDER BY에서 그대로 쓸 수 있다.
이것도 처리 순서상 SELECT가 ORDER BY보다 먼저 실행되기 때문이다.

3. 실습 / 적용

직접 해본 것:

실습 때 풀었던 문제 일부분이다.

-- Q2) 이름이 3글자가 아닌 교수의 이름/주민번호
SELECT PROFESSOR_NAME, PROFESSOR_SSN
FROM   tb_professor
WHERE  CHAR_LENGTH(PROFESSOR_NAME) != 3;

-- Q3) 남자 교수의 이름/나이, 나이 적은 순 (교수라 2000년대생 없음 전제)
SELECT   PROFESSOR_NAME AS `교수이름`,
         YEAR(CURDATE()) - (1900 + CAST(LEFT(PROFESSOR_SSN,2) AS SIGNED)) AS `나이`
FROM     tb_professor
WHERE    SUBSTRING(PROFESSOR_SSN, 8, 1) = '1'
ORDER BY `나이`;

결과:
Q2, Q3까지 정상적으로 결과가 나오는 걸 확인했다.
Q3는 원래 세기(1900/2000) 구분이 필요한 문제이지만, 2000년생 없음을 전제했기 때문에 별도의 CASE 없이 1900 +로 고정했다.

4. 트러블슈팅

LENGTH()로 한글 바이트 착각
WHERE LENGTH(PROFESSOR_NAME) != 3 조건이 "3글자가 아닌 이름"을 걸러낼 줄 알았는데, 한글 이름은 LENGTH()(바이트 기준)로 재면 3글자면 9바이트가 나오기 때문에 조건이 의도대로 동작하지 않았다.
글자 수 기준으로 세는 CHAR_LENGTH()로 바꾸니 해결됐다.

5. 오늘의 회고

• 느낀 점: 어제까지는 첫날이라 그런가 쉽게만 느껴졌는데, 오늘 내용은 살짝 어렵게 느껴졌다.
  요구사항에 대해서 코드를 작성할 때, 어떻게 구현할지 생각을 먼저 해보고 짜는 연습이 필요할 것 같다.
• 다음에 할 것: 강의 영상 복습

---

#LGCNS #LGCNS6기 #개발자 #LGCNSINSPIRECAMP