3일차에 배운 내용을 정리하였다.
목차는 다음과 같다. :
01. ORDER BY
-01 글자 및 문자의 순서
-02 복합 정렬
-03 NULL의 정렬 순서
02. 함수
-01 함수의 종류
-02 문자 함수
1) 대소문자 치환
2) 길이
3) 문자열 추출
4) 문자열 위치
5) 문자열 삽입
6) 연속 문자열 제거
7) 문자열 치환/삭제
8) 문자열 치환(번역)
9) 조건 치환
03. 날짜 함수
1) 오늘 날짜(현재 시간)
2) 날짜 연산
3) 날짜 파싱
01. ORDER BY
-01 글자 및 문자의 순서
글자의 순서는 다음과 같이 진행된다. :
- 숫자 : 1, 2, 3, 4...
- 문자(한글) : 가, 나, 다, 라...
- 문자(영문) : a, b, c, d...
- 날짜 : 2025/12/01, 2025/12/02, 2025/12/03, 2025/12/04...
문자의 순서는 다음과 같이 진행된다. :
- 왼쪽부터 비교를 시작하며, 비교대상의 값이 같을 경우 다음 대상과 비교를 진행
- 또한 더 많은 글자를 가진 쪽이 더 큰 값으로 인정
- ab < abc
- 가 < 가나
- 사 < 사나 < 서재수
order by에서 오름차순은 asc, 내림차순(역순)은 desc으로 order by 열의 이름 asc/desc 형태로 작성한다.
다음과 같은 테이블이 있을 때, 오름차순으로 정렬하는 쿼리와 결과는 다음과 같다.
select *
from test_name
order by NAME asc;
가, 나, 다...순에 맞추어 김 -> 안 -> 이 -> 홍 순으로 정렬되었으며, 글자가 적은 쪽부터 많은 쪽까지 순차적으로 배열되었음을 확인할 수 있다.
desc를 사용하여 내림차순으로 재정렬해보았다.
select *
from test_name
order by NAME desc;
asc를 이용했을때와 역순으로 재정렬되었음을 확인할 수 있다.
-02 복합 정렬
복합 정렬은 여러 열을 이용하여 정렬을 수행하는 정렬로 1차 정렬 후 2차 정렬 등 지정한 열에 맞추어 정렬을 순서대로 진행한다. 단, 2차 정렬의 의미가 있으려면 1차 정렬값 내에서 반드시 같은 값이 존재해야 한다.
1차 정렬을 하려는 열의 데이터가 모두 다른 값이라면 굳이 2차 정렬을 진행할 이유가 없기 때문이며, 불필요한 정렬은 성능을 저하시킨다.
order by를 이용하여 emp 테이블에서 부서번호(deptno) 기준으로 1차 정렬, 입사일(hiredate) 기준으로 2차 정렬을 수행하였다.
select *
from EMP
order by deptno, hiredate;
부서별로, 입사일별로 정렬되었음을 확인할 수 있다.
또한 order by는 select 이후에 처리되기 때문에 select에서 지정한 컬럼 별칭을 사용 가능하다.
select empno, ename as 이름, deptno as 부서번호
from EMP
order by 부서번호;
select empno, ename as 이름, deptno as 부서번호
from EMP
order by 부서번호, 2 desc;
그리고 order by 부서번호, 이름 desc; 로 표현할 수도 있지만, select에서 작성한 열 순서를 입력하여 열의 별칭, 이름, 순번 등을 동시에 사용 가능하다.
-03 NULL의 정렬 순서
오라클은 오름차순 정렬 시 NULL이 마지막에 배치가 된다. 내림차순 정렬 시 처음에 배치가 된다.
(SQL Server는 반대)
그러나 오름차순 정렬 시 NULL을 처음에 배치하고 싶다면 first, 내림차순 정렬 시 NULL을 마지막에 배치하고 싶다면 last를 사용해야 한다.
오름차순 쿼리와 fist를 추가한 예시를 첨부하였다.
select *
from EMP
order by comm;
NULL이 오름차순으로 정렬이 완료된 행 아래에 위치하고 있다.
select *
from EMP
order by comm nulls first;
first를 추가적으로 작성함으로써 NULL이 먼저 조회되고, 이후로 정렬가능한 행이 하단에 위치함을 확인할 수 있다.
SELECT문에 대한 이론 정리는 이로써 완료되었다.
02. 함수
함수는 input - output의 관계를 표현한다.
-01 함수의 종류
1) input의 수에 따라
- 단일행 함수 (1:1 리턴)
- 다중행 함수 (=복수행 함수, 집계 함수) : ex_ sum, avg, count, min, max...
2) input 데이터 형태에 따라
- 문자함수
- 숫자함수
- 날짜함수
- 변환함수
- 기타함수
-02 문자 함수
문자함수는 문자열에 대한 치환, 삭제, 변환 등을 수행한다.
1) 대소문자 치환
lower, upper, initcap
select ename,
lower(ename), -- 소문자 변환
upper(ename), -- 대문자 변환
initcap(ename) -- camel 표기법 변환
from EMP;
각각 소문자로 변환, 대문자로 변환, 앞글자만 대문자로 표기하는 Camel 표기법으로 변환되었음을 확인할 수 있다.
2) 길이
length, lengthb
select ename, length(ename), -- 글자수 계산(변수)
lengthb(ename) -- 글자길이 계산(byte)
from emp;
length는 글자수를 그대로 출력하고, lengthb는 글자길이를 계산한다.
영문자 혹은 숫자의 경우 1바이트지만 한글의 경우 시스템에 따라 2~4바이트로 계산되며, 오라클의 경우 한글 문자 하나당 2바이트로 처리한다.
select ename, lengthb('aaaa'), lengthb(4444), lengthb('가가가가')
from emp;
영문자인 'AAAA', '숫자인 '4444'의 경우 글자수마다 1바이트이므로 4가 출력되었으며, 한글인 '가가가가'의 경우 글자당 2바이트이므로 8가 출력되었음을 확인할 수 있다.
3) 문자열 추출
substr(대상, 추출위치[, 추출개수])
추출하려는 대상에서 지정한 위치 및 개수에 따른 문자를 리턴한다.
추출개수는 생략 가능하며 생략 시 마지막 글자까지 추출한다.
숫자를 추출해도 substr를 통해 추출된 데이터 타입은 문자 타입으로 처리되며, substr은 from 절을 제외한 모든 절에서 사용 가능하다.
또한 추출위치는 음수의 형태로 전달 가능하다.
| A | B | C | D | E | F |
| 1 | 2 | 3 | 4 | 5 | 6 |
| -6 | -5 | -4 | -3 | -2 | -1 |
'ABCDEF' 라는 글자가 있으면 좌측부터 우측으로 자릿수를 세었을 때 1부터 시작하지만, 우측부터 역순으로 자릿수를 세면 음수의 형태를 취한다.
select substr('abcde', 1, 1),
substr('abcde', 3),
substr('abcde', -2)
from dual;
'abcde' 의 첫번째 위치의 1개의 글자 리턴 -> a
'abcde' 의 세 번째 위치부터 끝까지 리턴(추출개수를 생략하였으므로) -> ced
'abcde' 의 우측에서부터 두 번째 위치부터 끝까지 리턴 -> de
또한 연습문제로 student 테이블에서 남학생 데이터만 출력하되, 이름, 학년, 생년월일 열만 조회하겠다.
select *
from student;
student 테이블에는 성별 열이 존재하지 않는다. 그러나 주민등록번호(JUMIN)열의 7번째 글자가 성별을 나타내는 1, 2임을 참고하였을 때, substr(JUMIN, 7, 1)을 통해 해당 학생이 어떤 성별인지 확인할 수 있다.
select NAME, GRADE, BIRTHDAY
from student
where substr(JUMIN, 7, 1) = '1';
정상적으로 결과가 출력되었다.
또한 '1'이 숫자임에도 불구하고 작은 따옴표를 붙여 문자로 취급한 이유는 substr을 통해 처리된 데이터 타입은 문자이기 때문에, 조건대상과 상수의 데이터 타입을 일치시키는 것이 성능에 유리하다고 한다.
결과물이 어떤 타입으로 처리되는지를 잘 생각해가며 보다 효율적인 쿼리를 작성할 필요가 있어보인다.
4) 문자열 위치
instr(대상, 찾을문자열[, 시작위치][, 발견횟수])
양수의 위치를 리턴하며, 찾고자하는 대상이 없을 경우 0을 반환한다.
시작위치는 생략 가능하며 생략 시 문자의 처음부터 찾게된다. 또한 시작위치가 음의 값일 때에는 탐색 방향이 역순으로 바뀐다. 발견횟수는 생략 가능하며, 생략 시 가장 처음으로 발견되는 문자열의 위치를 리턴한다.
select instr('a#b#c#d#e','#'),
instr('a#b#c#d#e','#',5),
instr('a#b#c#d#e','#',5,2),
instr('a#b#c#d#e','#', -3, 1),
instr('a#b#c#d#e','!') -- 해당 문자는 없는 문자이므로 0이 뜸
from dual;
조건에 따라 찾고자하는 문자의 위치가 조회되었음을 확인할 수 있었으며, '!'의 경우 'a#b#c#d#e'에 없는 문자이므로 0을 반환한다.
5) 문자열 삽입
lpad(대상, 총길이[, 삽입문자열])
rpad(대상, 총길이[, 삽입문자열])
문자열에 원하는 문자열을 삽입하며, 삽입문자열 생략 시 공백이 삽입된다.
select lpad('abcd', 10, '*'),
rpad('abcd', 10, '*'),
lpad('abcd', 10),
rpad('abcd', 10)
from dual;
지정한 길이(10)에 맞추어 원하는 문자열 혹은 공백이 삽입됬엄을 확인할 수 있었다.
연습문제로 emp 테이블에서 각 직원의 이름, 직무, 부서번호, 급여를 출력하되 직무의 출력은 오른쪽으로 정렬하였다.
select max(length(job))
from emp;
length를 통해 job열의 가장 긴 값을 가지고 있는 데이터의 길이를 확인하였다. (9)
select ename, lpad(job, 9), deptno, sal
from emp;
이후 lpad(job,9)를 통해 좌측에 공백을 삽입해 모든 값을 9의 길이에 맞추어주었다. 오른쪽으로 정렬되었음을 확인할 수 있다.
6) 연속 문자열 제거
ltrim(대상[, 제거문자열])
rtrim(대상[, 제거문자열])
trim(대상)
ltrim은 좌측, rtrim은 우측, trim은 양측에서 문자열을 제거한다.
제거문자열 생략 시 공백을 제거할 수 있으며, trim은 공백만 양쪽 방향으로 제거가 가능할 뿐 특정 문자열에 대한 제거는 불가능하다.
연속되는 문자열을 제거하는 것이기에 다른 문자를 만나면 제거 작업이 정지된다.
ex) aaaaba -> ba (O)
aaaaba -> b (X)
select ltrim('aaabcaaa', 'a'),
rtrim('aaabcaaa', 'a')
from dual;
ltrim을 통해 제거하려는 글자 외의 다른 글자(b)에서 제거가 정지되었으며, rtrim의 경우 우측부터 제거하기에 c에서 작업이 정지되었다.
select ltrim(' bca '), length(ltrim(' bca ')),
rtrim(' bca '), length(rtrim(' bca ')),
trim(' bca '), length(trim(' bca '))
from dual;
또한 제거문자열을 지정하지 않아 공백을 제거하되, trim의 경우 양측에서 공백을 제거하기 때문에 길이가 달라졌음을 확인할 수 있다.
7) 문자열 치환/삭제
replce(대상, 찾을문자열[, 바꿀문자열]) *[ ] 안의 바꿀문자열은 생략 가능
바꿀 문자열을 생략할 시 찾을 문자열이 모두 삭제된다.
이름의 가운데글자를 X자로 처리해보겠다.
select replace('홍길동', '길', 'X'),
replace('홍나나', '나', 'X')
from dual;
홍길동의 경우 가운데 글자인 '길'이 X로 치환되었지만 홍나나의 경우 두 번째, 세 번째 글자가 같은 글자로 모두 X로 치환되었다. 위치나 개수에 상관없이 찾을 문자열에 해당되는 모든 글자가 'X'자로 치환되었음을 알 수 있다.
8) 문자열 치환(번역)
translate(대상, 찾을문자열, 바꿀문자열)
찾을 문자열과 바꿀 문자열에 대응되는 문자끼리 1:1 치환이 진행된다.
찾을 문자열의 길이 > 바꿀 문자열의 길이 -> 짝이 맞지 않는 글자가 삭제
찾을 문자열의 길이 < 바꿀 문자열의 길이 -> 짝이 맞지 않는 글자는 무시
select replace('abcba', 'ab', 'AB'),
translate('abcba', 'ab', 'AB')
from dual;
replace와 (단어-단어) 다르게 translate는 문자와 문자가 1:1로 치환되니 a-A, b-B로 치환하면서 ABcBA로 치환된다.
select replace('abcba', 'ab', 'AB'),
translate('abcba', 'ab', 'A')
from dual;
찾을 문자열이 바꿀 문자열보다 길기에 짝이 맞는 a-A만이 치환되었고, b는 삭제되었다.
select replace('abcba', 'ab', 'AB'),
translate('abcba', 'ab', 'ABC')
from dual;
찾을 문자열이 바꿀 문자열보다 짧기에 짝이 맞는 a-A, b-B만이 치환되었으며, C는 무시되었음을 확인할 수 있다.
9) 조건 치환
값 자체가 특정 조건을 만족하였을 때 다른 값으로 치환한다. IF문과 비슷하나 SQL는 IF문이 없다.
- decode(대상, 조건1, 치환1, 조건2, 치환2,....,그 외 리턴) [오라클 전용 함수]
특정값(조건)과 같을 때에만 치환이 발생하며, 여러개의 조건을 동시에 전달 가능하다. '그 외 리턴'은 그 어떤 조건에도 만족하지 않는 값이 발생할 시 치환할 값인데, 이를 지정해주지 않으면 NULL이 리턴된다.
select deptno,
decode(deptno, 10, '인사부'),
decode(deptno, 10, '인사부', '총무부')
from emp;
그 어떤 조건에도 만족하지 않는 값에 대한 치환을 지정하지 않았기에 '인사부'에 해당되는 행을 제외하고 NULL이 리턴되었다.
반면 그 어떤 조건에도 만족하지 않는 값은 '총무부'로 치환하라고 지정하자 NULL로 지정되었던 데이터들이 '총무부'로 변경된 것을 확인할 수 있다.
이를 응용하여 emp 테이블에서 직업이 clerk인 직원에 대해 사번, 이름, 직업, 부서명을 조회할 수 있다.
단, 부서명의 경우 부서번호가 10번이면 인사부, 20번은 총무부, 30번이면 재무부를 출력한다.
select empno, ename, decode(deptno, 10, '인사부', 20, '총무부', 30, '재무부') 부서명
from emp
where job = 'CLERK';
또한 student 테이블을 이용하여 성별, 학생수, 최대몸무게, 최대키를 출력할 수 있다.
select *
from student;
student 테이블은 성별에 대한 열이 따로 지정되어 있지 않다.
따라서 주민등록번호의 7번째 자리가 1인지 2인지를 구분한 후, 각각 '남자', '여자' 로 치환해야 한다.
select decode(substr(jumin, 7, 1), 1, '남자', '여자') 성별,
count(*) 학생수,
max(height) 최대몸무게,
max(weight) 최대키
from student
group by substr(jumin, 7, 1), 1, '남자', '여자';
원하는 결과가 잘 나왔음을 확인할 수 있었다.
03. 날짜 함수
1) 오늘 날짜(현재 시간)
select sysdate, systimestamp
from dual;
0000/00/00 00:00:00의 형태로 날짜를 출력한다.
2) 날짜 연산
select sysdate + 100 "100일 뒤 날짜",
sysdate - 100 "100일 이전 날짜"
from dual;
3) 날짜 파싱
to_date('0000/00/00')를 통해 문자열을 날짜로 지정한다.
select to_date('2026/03/05') + 300
from dual;
'아이티윌_데이터 분석 55기 > 강의내용 필기_SQL' 카테고리의 다른 글
| #6 6일차_ JOIN : equi join, non equi join, 세 개 이상 테이블의 조인 (0) | 2026.03.12 |
|---|---|
| #5 5일차_날짜 파싱 시 유의사항, 변환 함수, NULL 치환 함수 (0) | 2026.03.11 |
| #4 4일차_조건 치환, 숫자 함수, 날짜 함수 (0) | 2026.03.10 |
| #2) 2일차_SELECT문 : WHERE, GROUP BY, HAVING (0) | 2026.03.08 |
| #1) 1일차_오라클과 오렌지 설치하기, SELECT문 (0) | 2026.03.08 |