[자격증 / SQLD] SQL 기본(2)_WHERE 조건절(연산자)과 함수

 

DATA ON-AIR에서 16번 WHERE 절과 17번 함수(FUNCTION)를 요약 정리한 내용이다.

SQLD 준비를 위해 실습을 진행하고자 하신다면, 아래 링크를 참고해서 실습 환경을 세팅하고 시작하면 된다.

 

• SQLD 실습준비(1)_도커(docker)에 오라클(oracle) 설치하기 (ft. 윈도우 환경)
• SQLD 실습준비(2)_디비버(dbeaver) 설치하고, 오라클(oracle)에 연동하기 (ft. 윈도우 환경)

 

 

 

---

 

 

 

WHERE 조건절 개요

자료를 검색할 때 SELECT 절과 FROM 절만을 사용하여 기본적인 SQL 문장을 구성한다면, 테이블에 있는 모든 자료들이 결과로 출력되어 실제로 원하는 자료를 확인하기 어려울 수 있다. 

• 자신이 원하는 자료만을 검색하기 위해서 SQL 문장에 WHERE 절을 이용하여 자료들에 대하여 제한할 수 있다.
• WHERE 절에는 두 개 이상의 테이블에 대한 조인 조건을 기술하거나 결과를 제한하기 위한 조건을 기술할 수도 있다.
• WHERE 절에 조건이 없는 FTS(Full Table Scan) 문장은 SQL 튜닝의 1차적인 검토 대상이 된다.

 

SELECT [DISTINCT/ALL] 칼럼명 [ALIAS명] FROM 테이블명 WHERE 조건식;

WHERE 절은 FROM 절 다음에 위치하며, 조건식은 아래 내용으로 구성된다.

• 칼럼(Column)명 (보통 조건식의 좌측에 위치)
• 비교 연산자
• 문자, 숫자, 표현식 (보통 조건식의 우측에 위치)
• 비교 칼럼명 (JOIN 사용 시)

 

연산자

WHERE 절에 사용되는 연산자는 3가지 종류가 있다.

• 비교 연산자 (부정 비교 연산자 포함)
• SQL 연산자 (부정 SQL 연산자 포함)
• 논리 연산자

 

연산자의 종류

• BETWEEN a AND b : a와 b 사이의 범위 (a와 b 값 포함)
• IN (list) : 리스트에 있는 값 중 어느 하나라도 일치
• IS NULL : NULL 값인 경우 (Oracle은 VARCHAR2 빈 문자열을 NULL로 판단)
• IS NOT NULL : NULL 값이 아닌 경우
• NOT IN (list) : list의 값과 일치하지 않는다.
• LIKE '비교문자열' : 비교문자열과 형태가 일치

 

연산자 우선순위

• ( )  →  NOT →  비교연산자( = , > , >= , < , <= ) / SQL 비교 연산자  →  AND  →  OR

 

부정 비교 연산자

• != / ^= : 같지 않다.
• <> : 같지 않다. (ISO 표준, 모든 운영체제에서 사용 가능)
• NOT 칼럼명 = : ~와 같지 않다.
• NOT 칼럼명 > : ~보다 크지 않다.

 

 

내장함수

• 단일행 함수 : 문자형 함수, 숫자형 함수, 날짜형 함수, 변환형 함수, NULL 관련 함수
• 다중행 함수 : 집계 함수, 그룹 함수, 윈도우 함수

 

단일행 함수

1. SELECT, WHERE, ORDER BY 절에서 사용 가능
2. 행에 개별적 조작
3. 여러 인자가 있어도 결과는 1개만 출력
4. 함수 인자에 상수, 변수, 표현식 사용 가능
5. 함수 중첩 가능

 

단일행 함수의 종류

종류	내용	함수의 예
문자형 함수	문자를 입력하면 문자나 숫자 값을 반환	LOWER, UPPER, SUBSTR/SUBSTRING, LENGTH/LEN, LTRIM, RTRIM, TRIM, ASCII
숫자형 함수	숫자를 입력하면 숫자 값을 반환	ABS, MOD, ROUND, TRUNC, SIGN, CHR/CHAR, CEIL/CEILING, FLOOR, EXP, LOG, LN, POWER, SIN, COS, TAN
날짜형 함수	DATE 타입의 값을 연산	SYSDATE/GETDATE, EXTRACT/DATEPART, TO_NUMBER(TO_CHAR(d, 'YYYY'|'MM'|'DD')) / YEAR|MONTH|DAY
변환형 함수	문자, 숫자, 날짜형 값의 데이터 타입을 변환	TO_NUMBER, TO_CHAR, TO_DATE / CAST, CONVERT
NULL 관련 함수	NULL을 처리하기 위한 함수	NVL/ISNULL, NULLIF, COALESCE

 

 

함수 실습

디비버(DBeaver)에 연결한 오라클(Oracle)로 진행한 실습 내용이다. 직접 실습해보면서 결과값을 확인해 보면 함수를 공부하는데 도움이 많이 된다.

create table country 
(
	country_name varchar(100),
	capital_city varchar(100),
	continent varchar(100)
);

INSERT INTO country (country_name, capital_city, continent) VALUES ('USA', 'Washington', 'America');
INSERT INTO country (country_name, capital_city, continent) VALUES ('England', 'London', 'Europe');
INSERT INTO country (country_name, capital_city, continent) VALUES ('S.Korea', ' Seoul', 'Asia');
INSERT INTO country (country_name, capital_city, continent) VALUES ('Australia', ' Canberra', 'Oceania');
INSERT INTO country (country_name, capital_city, continent) VALUES ('Ghana', 'Accra', 'Africa');
INSERT INTO country (country_name, capital_city, continent) VALUES ('Argentina', 'Buenos aires', 'America');

SELECT *
FROM country;

COMMIT;

 

|  실행결과

문자 함수를 활용해보기 위한 TABLE 완성

 

 

문자 함수

• LOWER(문자열) / UPPER(문자열) : 문자열의 알파벳 문자를 소문자 또는 대문자로 바꾸어준다.

SELECT
	country_name AS 원본,
	lower(country_name) AS 소문자,
	upper(country_name) AS 대문자
FROM
	country;

 

• LENGTH(문자열) : 문자열의 길이를 반환한다.

SELECT
	country_name AS 원본,
	LENGTH(country_name) AS 길이
FROM
	country;

 

• SUBSTR(문자열, m[, n]) : 문자열 중 m위치에서 n개의 문자 길이에 해당하는 문자를 반환한다.

SELECT
	country_name AS 원본,
	substr(country_name, 2, 2) AS substr_,
	substr(country_name, 5, 3) AS substr_
FROM
	country;

 

• INSTR(문자열, [지정문자]) : 지정문자를 찾아서 문자열의 몇 번째에 위치하는지 숫자로 반환한다.

SELECT
	continent AS 원본,
	instr(continent, 'A') AS instr_
FROM
	country;

 

• LPAD(문자열, 총 문자길이, 채움문자) : 문자열의 왼쪽에 특정 문자(또는 공백)를 채워서 문자열의 길이를 맞춰준다.
• RPAD(값, 총 문자길이, 채움문자) : 문자열의 오른쪽에 특정 문자(또는 공백)를 채워서 문자열의 길이를 맞춰준다.

SELECT
	continent AS 원본,
	lpad(continent, 10, 'A') AS lpad_, -- 왼쪽에 'A'를 붙여서 10자리를 채운다.
	rpad(continent, 10, 'A') rpad_,  -- 오른쪽에 'A'를 붙여서 10자리를 채운다.
	rpad(continent, 10, ' ') rpad2_ -- 공백 가능
FROM
	country;

 

• TRIM(지정 문자 from 문자열) : 공백을 제거한다. (양쪽 공백 제거가 디폴트)
• LTRIM(문자열 [, 지정문자])  : 문자열의 첫 문자부터 확인해서 지정 문자가 나타나면 해당 문자를 제거 (지정 문자가 없다면 공백을 제거)
• RTRIM(문자열 [, 지정문자]) : 문자열의 마지막 문자부터 확인해서 지정 문자가 나타나는 동안 해당 문자를 제거 (지정 문자가 없다면 공백을 제거)

SELECT
	capital_city AS 원본,
	trim(capital_city) AS trim_, 
	ltrim(capital_city) AS ltrim_, 
	rtrim(capital_city) AS rtrim_ 
FROM
	country;

Seoul과 Canberra 앞의 공백이 사라진 것을 볼 수 있다.

 

• REPLACE(문자열, 찾을 문자, 치환 문자) : 특정 문자열을 치환하거나 제거한다.

SELECT
	continent AS 원본,
	REPLACE(continent, 'A', '@') AS replace_, -- 'A'를 '@'로 치환하는 함수
	REPLACE(continent, 'A', '') AS replace_ -- 'A'가 제거된다.
FROM
	country;

 

+ Quiz.

PLAYER 테이블에서 PLAYER_NAME의 맨 마지막 문자를 '*'로 대체한 '비식별화' 칼럼을 출력하시오.

SELECT  PLAYER_NAME, CONCAT( SUBSTR (PLAYER_NAME, 1, LENGTH(PLAYER_NAME)-1), '*') AS 비식별화
FROM	PLAYER;

-- CANCAT 대신 || 사용 (결과 동일)
SELECT  PLAYER_NAME,SUBSTR (PLAYER_NAME, 1, LENGTH(PLAYER_NAME)-1) || '*' AS 비식별화
FROM	PLAYER;

 

 

숫자 함수

• SIGN(n) : n이 양수면 1, 음수면 -1, 0 이면 0을 반환한다.

SELECT
	SIGN(10),    -- 1
	SIGN(-10),   -- -1
	SIGN(0)      -- 0
FROM
	DUAL;

 

• MOD(n1, n2) : n1을 n2로 나눈 후의 나머지를 반환한다.

SELECT
	MOD(5, 2),   -- 1
	MOD(4, 2)    -- 0
FROM
	DUAL;

 

• ROUND(n, i) : n의 소수점 기준(i+1)번째에서 반올림한 값을 반환한다.

SELECT
	round(112.3456, 1) r1,   -- 112.3
	round(112.3456, 2) r2,   -- 112.35
	round(112.3456, -1) r3   -- 110 / 첫번째 정수 자리에서 반올림
FROM
	dual;

 

• CEIL(n) : 올림 함수

SELECT
	CEIL(26.3) r1,    -- 27
	CEIL(10.9) r2,    -- 11
	CEIL (-10.9) r3   -- -10
FROM
	dual;

 

• FLOOR(n) : 내림 함수

SELECT
	floor(26.3) r4,   -- 26
	floor(10.9) r5,   -- 10
	floor(-10.9) r6   -- -11
FROM
	dual;

 

• POWER(n2, n1) : 제곱 함수. n2의 n1승을 반환한다.

SELECT
	power(5, 2) AS power_,   -- 25
	power(3, 3) AS power2_   -- 27
FROM
	dual;

 

• TRUNC(n1, n2) : n1의 소수점 기준 n2에서 절삭, n2 생략 시 0

SELECT
	trunc(112.3456, 1) r1,  -- 112.3
	trunc(112.3456, 2) r2,  -- 112.34
    trunc(112.3456) r3      -- 112
FROM
	dual;

 

 

날짜 함수

• SYSDATE / SYSTIMESTAMP : 현재 날짜와 시간을 출력한다.

SELECT
	sysdate, 
	systimestamp   -- sysdate보다 더 자세하게 출력
FROM
	dual;

 

• ADD_MONTHS(date, n) : date의 날짜에 n개월을 더한 날짜를 반환 (n이 음수면 뺀 날짜를 반환)
• NEXT_DAY(date, expr) : date 날짜를 기준으로 expr(요일)에 명시한 날짜를 반환(ex. 월요일 / 1 ~ 7까지의 숫자)
• LAST_DAY(date) : date가 속한 월의 마지막 날짜를 반환

SELECT
	sysdate,
	add_months(sysdate, 6) r1, 
	next_day(sysdate, '일요일') r2, 
	last_day(sysdate) r3 
FROM
	dual;

 

• MONTHS_BETWEEN(date1, date2) : date1과 date2 사이의 개월 수를 반환 (date1이 date2보다 이후 날짜라면 양수, 그 반대라면 음수를 반환)

SELECT
	MONTHS_BETWEEN(SYSDATE, TO_DATE('2021-11-01', 'YYYY-MM-DD')) r1,  -- 양수
	MONTHS_BETWEEN(SYSDATE, TO_DATE('2025-10-01', 'YYYY-MM-DD')) r2   -- 음수
FROM
	dual;

 

 

변환형 함수

변환형 함수는 특정 데이터 타입을 다양한 형식으로 출력하고 싶을 경우에 사용되는 함수이다.

종류	설명
명시적(Explicit) 데이터 유형 변환	데이터 변환형 함수로 데이터 유형을 변환하도록 명시해 주는 경우
암시적(Implicit) 데이터 유형 변환	데이터베이스가 자동으로 데이터 유형을 변환하여 계산하는 경우

• 암시적 데이터 유형 변환의 경우 성능 저하가 발생하거나 데이터베이스가 알아서 계산하지 않는 경우 발생할 수 있으므로 명시적인 데이터 유형 변환 방법을 사용하는 것이 바람직하다.

 

+ 명시적 데이터 유형 변환에 사용되는 대표적인 변환형 함수

변환형 함수 - Oracle	함수 설명
TO_NUMBER(문자열)	alphanumeric 문자열을 숫자로 변환
TO_CHAR(숫자 | 날짜 [, FORMAT])	숫자나 날짜를 주어진 FORMAT 형태로 문자열 타입으로 변환
TO_DATE(문자열 [, FORMAT])	문자열을 주어진 FORMAT 형태로 날짜 타입으로 변환

 

변환형 함수 - SQL Server	함수 설명
CAST (expression AS data_type [(length])	expression을 목표 데이터 유형으로 변환
CONVERT (data_type [(length), expression [,style])	expression을 목표 데이터 유형으로 변환

 

 

• TO_CHAR(날짜 데이터 타입, '지정 형식') : 날짜, 숫자, 문자 값을 문자열로 변환

-- 날짜 포맷 변경
SELECT 
	TO_CHAR(SYSDATE, 'YYYYMMDD'),              --20200723
	TO_CHAR(SYSDATE, 'YYYY/MM/DD'),            --2020/07/23
	TO_CHAR(SYSDATE, 'YYYY-MM-DD'),            --2020-07-23
	TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS')  --2020-07-23 11:10:52
FROM 
	dual;
  
  -- 소수점 변경
 SELECT 
  	TO_CHAR(123.456, 'FM990.999'),  --123.456
 	TO_CHAR(1234.56, 'FM9990.99'),  --1234.56
 	TO_CHAR(0.12345, 'FM9990.99')   --0.12
 FROM 
 	dual;
    
 -- 날짜 계산
 SELECT
	SYSDATE,
	to_char(SYSDATE + 1 / 24 / 60 / 60, 'yyyy/mm/dd hh24:mi:ss') date1, -- 1초 뒤 시간
	to_char(SYSDATE + 1 / 24 / 60, 'yyyy/mm/dd hh24:mi:ss') date2, -- 1분 뒤 시간
	to_char(SYSDATE + 1 / 24, 'yyyy/mm/dd hh24:mi:ss') date3, -- 1시간 뒤 시간
	to_char(SYSDATE + 1 / 24, 'yyyy/mm/dd') date4
FROM
	dual;

날짜 계산 결과값

 

 

NULL 관련 함수

• NULL은 아직 정의되지 않은 값으로 0 또는 공백과 다르다. 0은 숫자이고, 공백은 하나의 문자이다.
• 테이블을 생성할 때 NOT NULL 또는 PRIMARY KEY로 정의되지 않은 모든 데이터 유형은 NULL을 포함할 수 있다.
• NULL 을 포함하는 연산의 경우 결과 값도 NULL이다.
• 결과값을 NULL이 아닌 다른 값을 얻고자 할 때 NVL/ISNULL 함수를 사용한다.

일반형 함수	함수 설명
NVL(표현식1, 표현식2)  - Oracle ISNULL(표현식1, 표현식2) - SQL Server	표현식1의 결과값이 NULL이면 표현식2의 값을 출력한다. 단, 표현식1과 표현식2의 결과 데이터 타입이 같아야 한다. NULL 관련 가장 많이 사용되는 함수이므로 상당히 중요하다.
NULLIF(표현식1, 표현식2)	표현식1이 표현식2와 같으면 NULL을, 같지 않으면 표현식1을 리턴한다.
COALESCE(표현식1, 표현식2, ...)	임의의 개수 표현식에서 NULL이 아닌 최초의 표현식을 나타낸다. 모든 표현식이 NULL이라면 NULL을 리턴한다.

 

+ 아래의 SELECT 결과가 NULL이 아닌 경우는?

1. SELECT COALESCE ('AB', 'BC', 'CD') FROM DUAL;
2. SELECT CASE 'AB' WHEN 'BC' THEN 'CD' END FROM DUAL;
3. SELECT DECODE ('AB', 'CD', 'DE') FROM DUAL;
4. SELECT NULLIF ('AB', 'AB') FROM DUAL;

SELECT
    COALESCE ('AB', 'BC','CD') "1",
    CASE 'AB' WHEN 'BC' THEN 'CD' END "2",
    DECODE ('AB', 'CD', 'DE') "3",
    NULLIF ('AB', 'AB') "4"
FROM
    DUAL;

정답 : 1번

 

 

---

[관련 글 보러가기]

• [자격증 / SQLD] SQL 기본(1)_TABLE과 명령어(DML, DDL, DCL, TCL)
• [자격증 / SQLD] SQL 기본(3)_GROUP BY, ORDER BY, 조인(JOIN)
• [자격증 / SQLD] SQL 활용(1)_표준 조인과 집합 연산자
• [자격증 / SQLD] SQL 활용(2)_계층형 질의, 서브쿼리
• [자격증 / SQLD] SQL 활용(3)_그룹 함수와 윈도우 함수
• [자격증 / SQLD] SQL 활용(4)_절차형 SQL

 

---

 

[참고자료]

 

https://gent.tistory.com/331

[Oracle] 오라클 TO_CHAR 함수 사용법 완벽한 정리 (날짜포맷, 소수점, 천단위 콤마)

https://selgii.tistory.com/39

SQLD 요약 - (4) SQL기본_연산자,함수