SQLD 자격증 공부 SQL 기본-WHERE 절, 함수 (SQL 전문가 가이드)

*본 게시물은 2013년도 SQL 전문가 가이드 교재(일명 '노랭이')를 참고하여 공부하고 정리한 게시물입니다

 

 

2과목 SQL 기본 및 활용: 제1장 SQL 기본

 

1. WHERE 절

1) WHERE 조건절 개요

• 사용자가 자신이 원하는 자료만 검색
• 두 개 이상의 테이블에 대한 조인 조건을 기술하거나,조회 결과를 제한하기 위한 조건을 기술할 수 있음
• 조회하려는 데이터에 특정 조건을 부여할 목적으로 사용되므로 FROM 절 뒤에 오게 됨

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

2) 조건식 연산자 종류

(1) 비교 연산자(부정 비교 연산자 포함)

연산자	연산자의 의미
=	같다
>	보다 크다
>=	보다 크거나 같다
<	보다 작다
<=	보다 작거나 같다
!=, ^=	[부정 연산자] 같지 않다
<>	[부정 연산자] 같지 않다(ISO 표준, 모든 운영체제에서 사용 가능)
NOT 칼럼명 =	[부정 연산자] ~와 같지 않다
NOT 칼럼명 >	[부정 연산자] ~보다 크지 않다

*문자 유형 비교 방법

구분	비교 방법
비교 연산자의 양쪽이 모두 CHAR	길이가 서로 다르면 작은 쪽에 SPACE를 추가하여, 길이를 같게 한 후에 비교
	서로 다른 문자가 나올 때까지 비교
	달라진 첫 문자의 값에 따라 크기 결정
	BLANK의 수만 다르다면 서로 같은 값으로 결정
비교 연산자의 어느 한 쪽이 VARCHAR (VARCHAR는 NOT NULL까지의 길이)	서로 다른 문자가 나올 때까지 비교
	길이가 다르다면, 짧은 것이 끝날 때까지만 비교하고 길이가 긴 것이 크다고 판단
	길이가 같고 다른 것이 없다면 같다고 판단
상수값과 비교할 경우	상수 쪽을 변수 타입과 동일하게 바꾸고 비교
	변수 쪽이 CHAR이면 위의 경우(비교 연산자의 양쪽이 모두 CHAR)를 적용
	변수 쪽이 VARCHAR이면 위의 경우(비교 연산자의 어느 한 쪽이 VARCHAR) 적용

(2) SQL 연산자(부정 SQL 연산자 포함)

연산자	연산자의 의미
BETWEEN a AND b	a와 b의 값 사이에 있으면 된다(a와 b값이 포함됨)
IN (list)	리스트에 있는 값 중에서 어느 하나라도 일치하면 된다
LIKE '비교문자열'	비교문자열과 형태가 일치하면 된다(%, _ 사용)
IS NULL	NULL 값을 가진다
NOT BETWEEN a AND b	[부정 연산자] a와 b 사이에 있지 않다(a, b값을 포함하지 않음)
NOT IN (list)	[부정 연산자] list 값과 일치하지 않는다
IS NOT NULL	[부정 연산자] NULL 값을 갖지 않는다

*와일드 카드의 종류

• %: 0개 이상의 어떤 문자를 의미
• _: 1개인 단일 문자를 의미

(3) 논리 연산자

연산자	연산자의 의미
AND	앞의 조건과 뒤의 조건이 참(TRUE)이 되면, 결과도 참이 된다 (앞의 조건과 뒤의 조건을 동시에 만족함)
OR	앞의 조건이 참(TRUE)이거나 뒤의 조건이 참이어야 결과도 참이 된다 (앞뒤 조건 중 하나만 참이면 됨)
NOT	뒤에 오는 조건에 반대되는 결과를 되돌려 준다

* 연산자의 우선순위

1. 괄호 ( )
2. NOT 연산자
3. 비교 연산자, SQL 비교 연산자
4. AND
5. OR

3) ROWNUM, TOP 사용

(1) ROWNUM

칼럼과 비슷한 성격의 Pseudo Column(슈도칼럼/가상 칼럼)으로써,
SQL 처리 결과 집합의 각 행에 대해 임시로 부여되는 일련번호
-> 테이블이나 집합에서 원하는 만큼의 행만 가져오고 싶을 때 WHERE 절에서 행의 개수를 제한하려는 목적으로 사용

[한 건의 행 가져올 때]
SELECT PLAYER_NAME FROM PLAYER WHERE ROWNUM = 1;
SELECT PLAYER_NAME FROM PLAYER WHERE ROWNUM <= 1;
SELECT PLAYER_NAME FROM PLAYER WHERE ROWNUM < 2;

[여러 건의 행 가져올 때]
SELECT PLAYER_NAME FROM PLAYER WHERE ROWNUM <= N;
SELECT PLAYER_NAME FROM PLAYER WHERE ROWNUM < N+1;

[테이블 내의 고유한 키나 인덱스 값 생성]
UPDATE MY_TABLE SET COLUMN1 = ROWNUM;

(2) TOP 절

SQL Server에서, 결과 집합으로 출력되는 행의 수를 제한할 때 사용

• Expression: 반환할 행의 수를 지정하는 숫자
• PERSENT: 쿼리 결과 집합에서 처음 Expression%의 행만 반환됨을 나타냄
• WITH TIES: ORDER BY 절이 지정된 경우에만 사용할 수 있으며,
                       TOP N(PERSENT)의 마지막 행과 같은 값이 있는 경우 추가 행이 출력되도록 지정할 수도 있음

[표현식]
TOP (Expression) [PERSENT] [WITH TIES]

[한 건의 행만 가져올 때]
SELECT TOP(1) PLAYER_NAME FROM PLAYER;

[여러 행을 가져올 때]
SELECT TOP(N) PLAYER_NAME FROM PLAYER;

2. 함수-내장 함수

• 함수는 내장 함수(Built-In Function)와 사용자가 정의할 수 있는 함수(User Defined Function)으로 나눌 수 있으나,
  내장 함수만 다룸
• 각 벤더에서 데이터베이스를 설치하면 기본적으로 제공되는 SQL 내장 함수
• SQL을 더욱 강력하게 해주고 데이터 값을 간편하게 조작하는 데 사용 

함수명 (칼럼이나 표현식 [, Arg1, Arg2, ... ])

1) 단일행 함수

Oracle함수/SQL Server함수 표시('/' 없는 것은 공통 함수)

종류	내용	함수의 예
문자형 함수	문자를 입력하면 문자나 숫자 값을 반환	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

*중요한 특징

1. SELECT, WHERE, ORDER BY절에 사용 가능
2. 각 행(Row)들에 대해 개별적으로 작용하여 데이터 값들을 조작하고, 각각의 행에 대한 조작 결과를 리턴
3. 여러 인자(Arguments)를 입력해도 단 하나의 결과만 리턴
4. 함수의 인자(Arguments)로 상수/변수/표현식이 사용 가능하고,
   하나의 인수를 가지는 경우도 있지만 여러 개의 인수를 가질 수도 있음
5. 특별한 경우가 아니면, 함수의 인자(Arguments)로 함수를 사용하는를 함수 중첩 가능

2) 문자형 함수(단일행)

Oracle함수/SQL Server함수 표시('/' 없는 것은 공통 함수)

문자형 함수	함수 설명	사례
LOWER(문자열)	문자열의 알파벳 문자를 소문자로 바꾸어 줌	LOWER('SQL Expert') = 'sql expert'
UPPER(문자열)	문자열의 알파벳 숫자를 대문자로 바꾸어 줌	UPPER('SQL Expert') = 'SQL EXPERT'
ASCII(문자)	문자나 숫자를 ASCII 코드 번호로 바꾸어 줌	ASCII('A') = 65
CHR/CHAR(ASCII 번호)	ASCII 코드 번호를 문자나 숫자로 바꾸어 줌	CHR(65) / CHAR(65) = 'A'
CONCAT(문자열 1, 문자열 2)	Oracle/MySQL에서 유효한 함수이며, 문자열1과 문자열2 연결 합성 연산자 | |(Oracle)나 +(SQL Server)와 동일함	CONCAT('RDBMS', 'SQL') 'RDBMS' || 'SQL'  /  'RDBMS' + 'SQL' = 'RDBMS SQL'
SUBSTR/SUBSTRING (문자열, m[, n])	문자열 중 m 위치에서 n개의 문자 길이에 해당하는 문자 돌려줌 (n이 생략되면 마지막 문자까지)	SUBSTR('SQL Expert', 5, 3) SUBSTRING('SQL Expert', 5, 3) = 'Exp'
LENGTH/LEN(문자열)	문자열의 개수를 숫자값으로 돌려줌	LENGTH('SQL Expert') /  LEN('SQL Expert') = 10
LTRIM(문자열 [, 지정문자])	문자열의 첫 문자부터 확인해서 지정 문자가 나타나면 해당 문자 제거 (지정 문자가 생략되면 공백 값이 디폴트) - SQL Server에서는 LTRIM 함수에   지정문자 사용 불가(공백만 제거)	LTRIM('xxxYYZZxYZ', 'x') = 'YYZZxYZ'
RTRIM(문자열 [, 지정문자 ])	문자열의 마지막 문자부터 확인해서, 지정 문자가 나타나면 해당 문자 제거 (지정 문자가 생략되면 공백 값이 디폴트, 공백 제거할 때, CHAR와 VARCHAR 데이터 유형을 비교할 때 사용)) - SQL Server에서는 RTRIM 함수에   지정문자를 사용 불가(공백만 제거)	RTRIM('XXYYzzYYzz', 'z') = 'XXYYzzYY' RTRIM('XXYYZZXYZ         ') = 'XXYYZZXYZ'
TRIM ( [leading | trailing | both] 지정문자  FROM 문자열)	문자열에서 머리말/꼬리말 또는 양쪽에 있는 지정 문자 제거 (leading | trailing | both가 생략되면 both가 디폴트) SQL Server에서는 TRIM 함수에 지정문자 사용 불가 (공백만 제거)	TRIM('x' FROM 'xxYYZZxYZxx') = 'YYZZxYZ'

*중요한 특징

문자 데이터를 매개 변수로 받아들여, 문자나 숫자 값의 결과를 돌려주는 함수

3) 숫자형 함수

Oracle함수/SQL Server함수 표시('/' 없는 것은 공통 함수)

숫자형 함수	함수 설명	사례
ABS(숫자)	숫자의 절대값 돌려줌	ABS(-15) = 15
SIGN(숫자)	양수/음수/0 구분	SIGN(-20) = -1 SIGN(0) = 0 SIGN(+20) = 1
MOD(숫자1, 숫자2)	숫자1을 숫자2로 나눈 나머지 값 리턴 (% 연산자로 함수 대체 가능)	MOD(7,3) = 1 7 % 3 = 1
CEIL / CEILING(숫자)	숫자보다 크거나 같은 최소 정수 리턴	CEIL(38.123) = 39 CEILING(38.123) = 39 CEILING(-38.123) = -38
FLOOR(숫자)	숫자보다 작거나 같은 최대 정수 리턴	FLOOR(38.123) = 38 FLOOR(-38.123) = -39
ROUND(숫자, m)	숫자를 소수점 m+1 자리에서 반올림하고 리턴 - m이 생략되면 디폴트 값은 0	ROUND(38.5235, 3) = 38.524 ROUND(38.5235, 1) = 38.5 ROUND(38.5235, 0) = 39 ROUND(38.5235) = 39
TRUNC(숫자, m)	숫자를 소수점 m+1 자리에서 버림하고 리턴 - m이 생략되면 디폴트 값은 0이며,   SQL Server에서 제공하지 않음	TRUNC(38.5235, 3) = 38.523 TRUNC(38.5235, 1) = 38.5 TRUNC(38.5235, 0) = 38 TRUNC(38.5235) = 38
SIN, COS, TAN...	숫자의 삼각함수 값 리턴
EXP(), POWER(), SQRT(), LOG(), LN()	숫자의 지수, 거듭 제곱, 제곱근, 자연 로그 값 리턴

*중요한 특징

숫자 데이터를 입력받아 처리하고 숫자를 리턴하는 함수

4) 날짜형 함수

Oracle함수/SQL Server함수 표시('/' 없는 것은 공통 함수)

날짜형 함수	함수 설명
SYSDATE / GETDATE()	현재 날짜와 시각 출력
EXTRACT('YEAR' | 'MONTH' | 'DAY' from d) / DATEPART('YEAR' | 'MONTH' | 'DAY', d)	날짜 데이터에서 년/월/일 데이터 출력(시간/분/초도 가능)
TO_NUMBER(TO_CHAR(d, 'YYYY')) / YEAR(d) TO_NUMBER(TO_CHAR(d, 'MM')) / MONTH(d) TO_NUMBER(TO_CHAR(d, 'DD')) / DAY(d)	날짜 데이터에서 년/월/일 데이터 출력 Oracle EXTRACT(), SQL Server DATEPART()와 같은 기능 TO_NUMBER 함수를 빼면 문자형으로 출력됨

*중요한 특징

• DATE 타입의 값을 연산하는 함수
• Oracle의 TO_NUMBER(TO_CHAR()) 함수의 경우 변환형 함수로 구분할 수도 있으나,
• SQL Server의 YEAR, MONTH, DAY 함수와 매핑하기 위해 날짜형 함수로 설명 

**단일행 날짜형 데이터 연산

연산	결과	설명
날짜 + 숫자	날짜	숫자만큼의 날수를 날짜에 더함
날짜 - 숫자	날짜	숫자만큼의 날수를 날짜에서 뺌
날짜1 - 날짜2	날짜수	다른 하나의 날짜에서 하나의 날짜를 빼면, 일수가 나옴
날짜 - 숫자/24	날짜	시간을 날짜에 더함

5) 변환형 함수

특정 데이터 타입을 다양한 형식으로 출력하고 싶을 경우 사용

(1) 명시적(Explicit) 데이터 유형 변환

데이터 변환형 함수로 데이터 유형을 변환하도록 명시

변환형 함수	설명
Oracle: TO_NUMBER(문자열)	alphanumeric 문자열을 숫자로 변환
Oracle: TO_CHAR(숫자 | 날짜, FORMAT)	숫자나 날짜를, 주어진 FORMAT 형태로 문자열로 변환
Oracle: TO_DATE(문자열, FORMAT)	문자열을 주어진 FORMAT 형태로 날짜 타입으로 변환
SQL Server: CAST(expression AS data_type[(length)])	expression을 목표 데이터 유형으로 변환
SQL Server: CONVERT(data_type [(length)], expression, [style])

(2) 암시적(Implicit) 데이터 유형 변환

데이터베이스가 자동으로 데이터 유형을 변환하여 계산 

-> 성능 저하가 발생할 수 있으며, 자동으로 계산하지 않는 경우가 있어 에러가 발생할 수 있으므로 잘 사용하지 않음 

6) CASE 표현

IF-THEN-ELSE-END 논리와 유사한 표현식 작성

-> SQL의 비교 연산 기능을 보완하고, 함수와 같은 성격을 띰

 

*단일행 CASE 표현의 종류

CASE 표현	함수 설명
CASE     SIMPLE_CASE_EXPRESSION 조건     ELSE 표현절 END	SIMPLE_CASE_EXPRESSION 조건이 맞으면 해당 조건 내의 THEN절 수행, 맞지 않으면 ELSE절 수행
CASE     SEARCHED_CASE_EXPRESSION 조건     ELSE 표현절 END	SEARCHED_CASE_EXPRESSION 조건이 맞으면 해당 조건 내의 THEN절 수행, 맞지 않으면 ELSE절 수행
DECODE(표현식, 기준값1, 값1, 기준값2, 값2, ..., 디폴트값)	[Oracle에서만 쓰이는 함수] - 표현식값이 기준값1이면 값1, 기준값2이면 값2 출력(없으면 디폴트값 출력) - CASE 표현의 SIMPLE_CASE_EXPRESSION 조건과 같음

 

 

7) NULL 관련 함수

(1) NULL 포함 연산의 결과

연산	연산 결과
NULL + 2, 2 + NULL	NULL
NULL - 2, 2 - NULL
NULL * 2, 2 * NULL
NULL / 2, 2 / NULL

(2) 단일행 NULL 관련 함수

함수	설명
NVL(표현식1, 표현식2) / ISNULL(표현식1, 표현식2)	표현식1의 결과값이 NULL이면 표현식2의 값 출력 (표현식1,2의 결과 데이터타입이 같아야 하며, 가장 많이 사용됨)
NULLIF(표현식1, 표현식2)	표현식1,2가 같으면 NULL, 다르면 표현식1 리턴
COALESCE(표현식1, 표현식2, ...)	임의의 개수 표현식에서, NULL이 아닌 최초 표현식을 나타냄 -> 모든 표현식이 NULL이면 NULL 리턴