01. 함수 - 단일행 함수
스파르타 클럽
- 내장 함수 개요
- 데이터베이스 조작 과정에서 다양한 연산을 지원하기 위해 각 DBMS 회사 별로 제공하는 내장함수가 있습니다. 사용하는 형태가 조금씩 다르지만 핵심적인 기능은 동일합니다.
- 내장함수는 크게 하나의 값이 입력되어 수행되는 단일행 함수와 여러 행의 값이 입력되는 다중행 함수로 분류를 할 수 있습니다. 이러한 함수는 데이터를 변환하거나 계산하기 위해 사용되며, SELECT 문의 SELECT 절이나 WHERE 절, HAVING 절 등에서 사용될 수 있습니다.
(1) LOWER(문자열) / UPPER(문자열) / CONCAT(문자열1, 문자열2)
- LOWER : 모든 문자를 소문자로 변경하여 리턴
- UPPER : 모든 문자를 대문자로 변경하여 리턴
- CONCAT : 문자열과 문자열을 연결하여 리턴
- Oracle과 MySQL에서 사용할 수 있는 함수이며, 합성 연산자 || (Oracle) 과 + (SQL Server) 와 동일한 결과를 출력합니다. SQL Server에서는 지원하지 않는 함수이기 때문에 SQL Server를 사용할 때는 합성 연산자 + 를 사용해야 합니다.
(2) ASCII(문자)
문자나 숫자를 ASCII 코드 번호로 바꿔줍니다.
<aside> 💡 ！참고 - ASCII
미국 표준 코드 정보 교환, American Standard Code for Information Interchange는 문자 인코딩 표준 중 하나로, 컴퓨터와 통신 장치에서 텍스트를 표현하는 데 사용됩니다.
현대의 컴퓨터 시스템에서는 더 넓은 문자 집합을 지원하기 위해 유니코드(Unicode)와 같은 확장된 인코딩 표준을 사용하지만, ASCII는 여전히 기본적인 텍스트 인코딩 표준으로 널리 사용됩니다.
</aside>
(3) Oracle: CHR / SQL Server: CHAR(ASCII 번호)
ASCII 코드 번호를 문자나 숫자로 바꿔줍니다.
(4) Oracle : SUBSTR
SQL Server : SUBSTRING (문자열, m[, n])
문자열 중 m위치에서 n개의 문자 길이에 해당하는 문자를 전달해줍니다. n은 생략이 가능하며 생략한 경우 '마지막 문자'까지를 의미합니다.
(5) Oracle : LENGTH
SQL Server : LEN(문자열)
문자열의 개수를 숫자값으로 돌려줍니다.
(6) LTRIM(문자열[, 지정문자])
문자열의 첫 번째 문자부터 확인해서 지정문자가 나타나면 해당 문자를 문자열에서 제거합니다. 여기서 지정문자의 기본 값은 공백값입니다. 지정 문자 생략 시 공백이 제거됩니다. SQL Server 에서는 LTRIM 함수의 지정문자를 정할 수 없고 오직 공백 제거만 가능합니다.
(7) RTRIM(문자열[, 지정문자])
문자열의 마지막 문자부터 확인해서 지정문자가 나타나면 해당 문자를 문자열에서 제거합니다. LTRIM과 마찬가지로 RTRIM도 SQL Server에서 지정문자를 정할 수 없고 공백 제거만 가능합니다.
(8) TRIM([leading|trailing|both] 지정문자 FROM 문자열)
문자열에서 머리말, 꼬리말, 또는 양쪽에 있는 지정 문자를 제거합니다. Oracle은 LTRIM 처럼 머리말만 잘라낼 때는 leading 옵션을 주고, RTRIM처럼 꼬리말만 잘라내고 싶을 때는 trailing 옵션을 설정하면 됩니다. 기본 설정값은 both 이기 때문에 양쪽 다 잘라내게 됩니다. SQL Server 는 지정문자를 정할 수 있지만 leading, trailing 옵션은 사용할 수 없습니다
(9) LPAD(문자열1,n[,문자열2]) / RPAD(문자열1,n[,문자열2])
- LPAD : 문자열1을 n자리만큼 늘리고, 왼쪽 빈 공간을 문자열2로 채워서 리턴
- (문자열2가 생략되면 기본값은 공백)
- RPAD : 문자열1을 n자리만큼 늘리고, 오른쪽 빈 공간을 문자열2로 채워서 리턴
- (문자열2가 생략되면 기본 값은 공백)
3) 숫자형 함수
숫자형 함수는 숫자 데이터를 입력받아 처리하고 숫자를 돌려주는 함수입니다. 숫자형 함수의 종류는 다음과 같습니다.
(1) ABS(숫자)
숫자의 절댓값을 돌려주는 함수입니다.
예시
SQL
복사
-- Oracle SELECT ABS(-15) FROM DUAL; --> 15
SQL
복사
-- SQL Server SELECT ABS(-15); --> 15
(2) SIGN(숫자)
숫자의 부호를 판단하는 함수입니다. 숫자가 양수이면 1을 리턴하고, 0이면 0을 리턴, 음수면 -1을 리턴합니다. 숫자가 양수인지, 음수인지, 0인지를 구별하는 함수입니다.
예시
SQL
복사
-- Oracle SELECT SIGN(20) FROM DUAL; --> 1 SELECT SIGN(0) FROM DUAL; --> 0 SELECT SIGN(-10) FROM DUAL; --> -1
SQL
복사
-- SQL Server SELECT SIGN(20); --> 1 SELECT SIGN(0); --> 0 SELECT SIGN(-10); --> -1
(3) MOD(숫자1, 숫자2)
숫자1을 숫자2로 나누어 나머지 값을 계산하는 함수입니다. MOD 함수는 % 연산자로도 대체 가능합니다.
예시
SQL
복사
-- Oracle SELECT MOD(7, 3) FROM DUAL; --> 1
SQL
복사
-- SQL Server SELECT MOD(7, 3); --> 1
(4) Oracle : CEIL(숫자) / SQL Server : CEILING(숫자)
숫자보다 크거나 같은 최소 정수를 리턴합니다. 소수를 정수로 올림 연산할 때 사용합니다.
예시
SQL
복사
-- Oracle SELECT CEIL(38.567) FROM DUAL; --> 39 SELECT CEIL(-38.567) FROM DUAL; --> -38
SQL
복사
-- SQL Server SELECT CEILING(38.567); --> 39 SELECT CEILING(-38.567); --> -38
(5) FLOOR(숫자)
숫자보다 작거나 같은 최대 정수를 리턴합니다. 소수를 정수로 내림 연산할 때 사용합니다.
예시
SQL
복사
-- Oracle SELECT FLOOR(38.567) FROM DUAL; --> 38 SELECT FLOOR(-38.567) FROM DUAL; --> -39
SQL
복사
-- SQL Server SELECT FLOOR(38.567); --> 38 SELECT FLOOR(-38.567); --> -39
(6) ROUND(숫자 [, m])
숫자를 소수점 m자리에서 반올림하여 리턴합니다. m이 생략되면 default 값은 0 입니다.
예시
SQL
복사
-- Oracle SELECT ROUND(38.567, 2) FROM DUAL; --> 38.57 SELECT ROUND(38.567) FROM DUAL; --> 39
SQL
복사
--SQL Server SELECT ROUND(38.567, 2); --> 38.57 SELECT ROUND(38.567); --> 39
(7) TRUNC(숫자 [, m])
숫자를 소수 m자리에서 잘라서 버린 값을 리턴합니다. m이 생략되면 default 값은 0입니다. SQL Server는 TRUNC 함수가 제공되지 않습니다.
예시
SQL
복사
-- Oracle SELECT TRUNC(38.567, 2) FROM DUAL; --> 38.56 SELECT TRUNC(38.567) FROM DUAL; --> 38
(8) SIN, COS, TAN(숫자)
숫자의 삼각함수 연산을 하여 결과를 리턴합니다. 숫자는 라디안을 의미합니다.
예시
SQL
복사
-- Oracle SELECT SIN(1.5708) FROM DUAL; --> 0.99999
SQL
복사
-- SQL Server SELECT SIN(1.5708); --> 0.99999
(9) EXP, POWER, SQRT, LOG, LN
숫자의 지수, 거듭 제곱, 제곱근, 로그, 자연 로그의 값을 리턴합니다.
- EXP(n) : e의 n제곱을 반환합니다.
- POWER(m, n) : m의 n제곱을 반환합니다.
- SQRT(n) : n의 제곱근을 반환합니다. n은 음수가 될 수 없습니다.
- LOG(m, n) : 밑을 m으로 한 n의 로그 값을 반환합니다. m은 0 또는 1이외의 정수이며 n은 양수 값으로 설정해야 합니다.
- LN(n) : n의 자연 로그 값을 반환합니다.
- <aside> 📌 시간 형식 참고하기 👀
‘DATE_FORMAT’ 함수
날짜 및 시간을 특정 형식으로 변환하여 표시하기 위해 사용되는 함수로 날짜와 시간을 다양한 형식으로 변환할 수 있도록 도와줍니다. 여러 DBMS에 비슷한 기능이 있으며, 각 시스템마다 사용법이 다소 차이가 있을 수 있습니다.
</aside>
- 변환형 함수
- 변환형 함수는 특정 데이터 타입을 다른 형태로 출력하고 싶을 때 사용되는 함수입니다. 크게 명시적인 변환 방식과 암시적인 변환 방식으로 나눌 수 있습니다.
- (1) 명시적 데이터 유형 변환
- 데이터 변환형 함수로 데이터 유형을 변환하도록 쿼리에 명시를 해주는 경우입니다. 어떤 데이터로 변환되는지 작성이 되어있기 때문에 코드의 가독성이 좋습니다.
- NULL 관련 함수
- (1) NULL의 특징
- NULL은 정의되지 않은 값을 의미합니다. 숫자 0이나 빈 공백 '' 과는 다른 값입니다. 어떠한 값과 NULL과의 연산(+,-,*,/ 등)은 항상 NULL 값이 나옵니다. 따라서 NULL과 관련된 연산이나 값의 확인은 일반 연산자가 아닌 다음에 나올 함수들을 사용해야 합니다.
- (2) Oracle : NVL / SQL Server : ISNULL 함수
- 결괏값을 NULL이 아닌 다른 값을 얻고자 할 때 Oracle은 NVL, SQL Server는 ISNULL 함수를 사용합니다. NULL 관련 함수 중 가장 많이 사용되므로 반드시 사용방법에 대해 알아두어야 합니다.
(3) NULL과 공집합
테이블에서 조건에 맞는 데이터가 한 건도 없는 경우를 공집합이라고 부릅니다. 이때 공집합은 NULL과 다릅니다. 일치되는 데이터가 없는 것이 공집합, 값 자체가 정의되지 않은 것이 NULL 입니다.
공집합의 경우 NVL 함수를 사용해도 공집합이 출력되므로, 그룹함수와 NVL 함수를 같이 사용해서 공집합을 처리해야 합니다.
(4) NULLIF
NULLIF 함수는 특정 값을 NULL로 대체하는 경우에 유용하게 사용할 수 있습니다. NULLIF 함수는 두 조건 EXPR1과 EXPR2를 비교하고 같다면 NULL을 리턴하고, 같지 않으면 첫 번째 EXPR1을 리턴합니다.
(5) 기타 NULL 관련 함수 (COALESCE)
COALESCE 함수는 여러 값 중에서 NULL이 아닌 첫 번째 값을 찾을 때 사용합니다. 만약에 모든 값들이 NULL이라면 NULL을 리턴합니다.
CASE_EXPRESSION
스파르타 코딩클럽
(1) SIMPLE_CASE_EXPRESSION
CASE 다음에 바로 조건에 사용되는 칼럼이나 표현식을 작성하고 바로 WHEN 절에 앞서 작성한 칼럼 혹은 표현식과 같은지 아닌지 판단하는 문장을 써 넣는 형태로 표현식이 같은지 여부를 확인합니다. EQUAL(=) 조건만 사용한다면 간단하게 사용할 수 있습니다.
(3) DECODE 함수
조건을 평가하여 값을 반환하는 함수로, 주로 간단한 조건과 대응하는 결과 값을 처리할 때 사용됩니다. DECODE 함수는 CASE 문과 유사한 역할을 수행하지만, 보다 간단한 형식으로 작성할 수 있습니다.
DECODE 함수의 첫 번째 인자로 표현식을 지정하고 표현식의 값이 기준값1 과 같다면 값1을 출력하고 기준값1과 다르다면 디폴트 값을 출력합니다. 여러 개의 기준값을 설정할 수 있으며 기본값을 설정하지 않으면 NULL 값이 반환됩니다.
02. 서브쿼리
- 서브쿼리 기본
- ☑️ 개념
- 서브쿼리(Subquery)란 하나의 SQL 문안에 포함되어 있는 또 다른 SQL 문을 의미합니다. 서브쿼리를 사용하면 SQL문을 통해 할 수 있는 일이 다양해지기 때문에 다양한 업무 처리가 가능하여 실무에서도 많이 사용합니다. 같이 상세하게 알아보도록 하겠습니다.
- 조인은 조인에 참여하는 모든 테이블에 대등한 관계에 있기 때문에 조인에 참여하는 모든 테이블의 칼럼을 어느 위치에서라도 자유롭게 참조가 가능합니다. 하지만 서브쿼리는 조인과 다르게 자유로운 형태의 참조가 아닌 특정 조건에 맞게 참조를 해야 합니다.
- 서브쿼리는 알려지지 않은 기준을 이용한 검색을 위해 사용되기 때문에 아래 그림과 같이 메인쿼리가 서브쿼리를 포함하는 형태를 띠게 됩니다. 즉, 서브쿼리는 레벨과는 상관없이 항상 메인쿼리 레벨로 결과 집합이 생성됩니다.
서브쿼리 사용 가능한 곳
- SELECT 절
- FROM 절
- WHERE 절
- HAVING 절
- ORDER BY 절
- DML(INSERT, DELETE, UPDATE절)
- GROUP BY 절에서 사용 불가
- FROM : FROM 절에 위치한 서브쿼리를 '인라인뷰 서브쿼리'라고 합니다.
* EMP 테이블과 DEPT 테이블을 조인하여 직원의 정보와 부서 정보를 출력해 보세요
(3) HAVING
스파르타 코딩클럽
(3) HAVING
WHERE 대신 HAVING 절에서도 서브쿼리를 사용할 수 있습니다. HAVING절에서 서브쿼리는 그룹함수와 함께 사용될 때 그룹핑된 결과에 대해 부가적인 조건을 주기 위해서 사용합니다.
****참고 tip
1) 조건과 일치하는 값을 조회하는 방법
|
SELECT * FROM table_name WHERE column_name = "value";
// table_name 테이블에 컬럼명 column_name 데이터 중에 값이 value와 동일한 데이터 조회
|
2) 조건과 일부가 포함한 값을 조회 하는 방법
|
SELECT * FROM table_name WHERE column_name LIKE "%value%";
// table_name 테이블에 컬럼명 column_name 데이터 중에 값이 value를 포함한 데이터 조회
|
3) 데이터 값중 조건값 앞문자에 상관없이 조건을 포함한 값을 조회하는 방법
|
SELECT * FROM table_name WHERE column_name LIKE "%value";
// column_name 데이터 중에 앞문자 상관없이 value를 포함한 데이터 조회
|
4) 데이터 값중 조회값 뒷문자에 상관없이 조건을 포함한 값을 조회하는 방법
|
SELECT * FROM table_name WHERE column_name LIKE "value%";
// 데이터 중에 뒷문자 상관없이 value를 포함한 데이터 조회
|
5) 여러 검색어를 하나의 컬럼에서 비슷한 값을 조회 하는 방법
|
SELECT * FROM table_name WHERE column_name LIKE "%value%check%";
// 데이터 값중에 value와 check 값이 포함되어 있는 데이터만 조회
|
6) 여러 검색어를 하나의 컬럼에서 일치하는 값들을 조회 하는 방법
|
SELECT * FROM table_name WHERE column_name IN ("value","check");
// 데이터 중에 value 값과 일치 거나 check 값이 일치한 데이터만 조회
|
|
-) 나머지 여러 조건을 추가 할 경우에는 AND, OR 구문을 사용해서 조회 합니다.
설명 까지 할 부분이 아닌 것 같아 예시는 생략합니다.
|
7) 검색중 제회할 데이터가 있을 경우 NOT 구문을 이용하여 조회 합니다.
|
SELECT * FROM table_name WHERE column_name NOT IN ("value","check");
// 데이터 중에 value 값과 일치 거나 check 값이 일치한 데이터를 제외한 나머지를 조회
|
8) NOT 구문은 반대 값을 구하라는 뜻이기 때문에 단일 조건 구문 앞에서도 사용됩니다.
|
SELECT * FROM table_name WHERE column_name NOT LIKE "%value%";
// value 값이 포함된 값을 제외한 나머지 값을 조회
|
9) 데이터 값들이 숫자이거나 날짜일 경우에는 부등호를 사용하여 조회합니다.
|
SELECT * FROM table_name WHERE value < 100 AND value > 10;
// 숫자, 날짜 데이터의 경우 부등호의 사용이 가능
|