SQL 함수, 서브 쿼리 연습

 

직전 포스팅(SQL 기본 문법)에 이어서 SQL 함수와 서브 쿼리 연습 후 정리한 내용임. 문자, 숫자, 날짜, 그리고 중복 제거와 관련한 함수를 연습해보고, 서브 쿼리에 대한 내용도 정리해서 올려봅니다.

 

문자 관련 함수

# 오라클 함수를 실행하기 위해서는 SELECT 문 내에 있어야 함

 

 

▶ UPPER, LOWER, INITCAP

UPPER : 대문자로 바꿈
LOWER : 소문자로 바꿈
INITCAP : 맨 앞 글자는 대문자, 나머지는 소문자로 바꿈 

select ename, upper(ename), lower(ename), initcap(ename) from emp;
select * from emp where upper(ename) = upper('smith');
select * from emp where upper(ename) like upper('%smith%');

 

 

▶ LENGTH, SUBSTR

LENGTH : 문자열의 길이를 구함
select ename, length(ename) from emp;

SUBSTR : 문자열의 일부를 추출
SUBSTR(문자열, 시작위치, 추출 길이)
select job, substr(job, 1, 2), substr(job, 3, 2) from emp;

 

 

▶ LPAD, RPAD

* PAD는 padding 의미

LPAD : 문자 왼쪽을 채움
RPAD : 문자 오른쪽을 채움
select 'oracle', LPAD('oracle', 10, '*'), rpad('oracle', 7, '*') from dual;

 

 

▶ CONCAT, DUAL

- 오라클에 dual이라는 가상의 테이블이 있음  

CONCAT : 두 문자를 합치기
select concat('oracle ', 'database') from dual;


DUAL : 오라클 데이터베이스에서 객체를 실행시켜 눈으로 확인 가능하게 만들어주는 가상의 테이블
SELECT [객체] FROM dual;

 

 

 

 

숫자 관련 함수

 

# ceil, floor는 정수를 만들어 줌

• trunc([숫자], [버림 위치])
• ceil[숫자])
• floor([숫자])
• mod([숫자], [나눌 숫자])

select
round(3.141592, 2),
trunc(3.141592, 3),
mod(5, 2),
ceil(3.141592),
floor(3.141592)
from dual;

 

 

날짜 관련 함수

• SYSDATE : 오라클 서버의 현재 날짜와 시간을 보여줌 (날짜 데이터는 '+', '-' 가능)
• TO_DATE : 문자를 날짜 자료형으로 변환하는 함수이며, 함수 사용은 to_date([문자(날짜 형태)], [인식될 날짜 형태])
• TO_CHAR : 날짜나 숫자를 문자로 변환하는 함수이며, 함수 사용은 to_char([날짜 또는 숫자)]

select sysdate,
sysdate + 1 as tomorrow,
sysdate - 1 as yesterday,
to_date('2023-03-20', 'YYYY-MM-DD'),
to_char(sysdate)
from dual;

 

 

※ 오라클에서 시간 표시

select to_date('12-08-23', 'MM-DD-YY')from dual;

 

select to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS ')from dual;

 

select to_char(sysdate, 'YY-mm-DD AM HH:MM:SS ')from dual;

 

 

※ 중복 제거 : DICTINCT

• 중복되는 값이 없으면 표현하지 않음
• 2개 이상의 컬럼을 선택하면 2개 컬럼이 and 조건으로 묶어서 하나의 값으로 비교하여 중복된 경우에만 제거됨 

select distinct job from emp;

select distinct job, deptno from emp;

 

 

서브 쿼리(Sub Query)

서브 쿼리란, SQL 문장 내(메인 쿼리)에 있는 다른 SQL 문장(SELECT 문)을 의미하며,

 

이 SELECT 문장(서브 쿼리)에서 데이터를  추출하여 메인 쿼리의 SQL 문장(주로 SELECT, INSERT, UPDATE, DELETE 등)의 조건이나 결과로 사용됩니다.

 

서브쿼리를 사용하면 더 복잡한 데이터 추출이 가능하고, 결과를 조작하거나 데이터를 필터링하는데 아주 유용합니다.

select ename, sal
from emp
where sal > (select sal from emp where ename = 'JONES');

 

 

▶ 단일행 서브쿼리

• 실행 결과가 하나의 값으로 나오는 서브쿼리
• 단일행 연산자(>, <, = 등)을 사용함

select *
from emp
where hiredate < (select hiredate from emp where ename = 'JAMES');

 

 

▶ 다중행 서브쿼리

• 실행 결과가 하나 이상의 여러 값으로 나오는 서브쿼리
• 단일행 연산자는 사용이 불가하고, IN, EXISTS 같은 연산자를 사용함

select *
from emp
where sal in (select max(sal) from emp group by deptno);

select *
from emp
where deptno in (select deptno from dept where loc = 'CHICAGO');

 

※ exists는 뒤에 오는 서브쿼리에 값이 있으면 true, 없으면 false 의미

select *
from emp
where exists (select dname from dept where deptno = 10);

 

 

 

트랜잭션(Transaction)

트랜잭션이란?

 

데이터베이스에서 한 번에 하나 이상의 작업을 묶어서(쿼리를 묶어서 하나의 작업 단위로) 처리하는 개념으로 데이터베이스에서 수행되는 작업의 논리적 단위

 

# 트랜잭션의 ACID 원칙

• 원자성(Atomicity) : 모든 작업은 성공 또는 실패로 완전히 수행되어야 함
• 일관성(Consistency) : 실행 전과 후에 데이터베이스는 항상 일관된 상태 유지
• 고립성(Isolation) : 여러 트랜잭션이 동시에 실행 중일 때 각 트랜잭션은 다른 트랜잭션의 영향을 받지 않아야 함
• 지속성(Durability): 트랜잭션이 완료된 후에는 그 결과가 영구적으로 유지되어야 함

 

# 트랜잭션의 예시)

A가 B에게 C은행을 사용하여 100만원을 송금한다

 

1. A가 Kb은행의 100만원을 이체하기 위해 보내는 작업을 진행한다.

A의 잔고 : -100만원

B의 잔고 : +0원

Kb은행 : +100만원

 

2. Kb은행에서 B의 계좌로 100만원을 입금한다.

A의 잔고 : -100만원

B의 잔고 : +100만원

Kb 은행 : +0원

 

 

위 상황에서 1과 2를 실행하는 과정에서 전산 오류가 발생하였다면,

A는 100만원이 인출되어 있어 -100만원인 상태이고

B는 100만원이 입금이 되지 않아 +0원인 상태일 것이다.

 

1과 2의 행동을 나눠서 실행되면 안되고 한번에 처리가 되고 저장이 되어야한다.

 

이런 상황을 막기위해 트랜잭션을 사용하여 1과 2를 하나의 처리하는 방법을 사용하여 중간에 문제가 발생하지 않도록 하기 위함이다.

 

▶ 트랜잭션을 취소하고 싶을 때는 ROLLBACK

--첫번째 조회
select * from emp;

--테이블 모든 데이터 삭제
delete from emp;

--두번째 조회
(아무 값이 없음)
select * from emp;

--트랜잭션 취소
rollback;

--세번째 조회
(값이 다시 보임)
select * from emp;

 

 

▶ 트랜잭션을 적용하고 싶을 때는 COMMIT

--첫번째 조회
select * from emp;

--테이블 모든 데이터 삭제
delete from emp;

update emp set ename = 'SCOTT' where ename = 'SMITH';
--두번째 조회(SMITH가 SCOTT으로 바뀜)
select * from emp;

--트랜잭션 적용
commit;

--세번째 조회(여전히 값이 바껴있음)
select * from emp;

 

 

 

오라클 11g, SQL Developer 설치해보기

 

 

DB, DBMS, 그리고 SQL 기본 문법 알아보기