[DB] Oracle SQL 완벽 정리

Oracle SQL 완벽 정리: 기초부터 고급 기능까지 총정리

안녕하세요.

이번 포스팅은 Oracle 데이터베이스를 다루는 데 필수적인 SQL의 기초 개념부터 고급 기능까지 체계적으로 정리해보려고 합니다. 개발자와 DBA를 위한 핵심 내용을 쉽게 이해할 수 있도록 정리했으니, 실무에서 바로 활용하실 수 있을 것입니다.

---

목차

1. SQL의 종류와 특징
2. 데이터베이스 테이블의 기본 구조
3. 절차적/비절차적 데이터 조작어
4. Oracle과 SQL Server의 주요 차이점
5. 제약 조건의 종류와 특징
6. 인덱스 생성과 관리
7. 테이블 구조 변경 명령어
8. 참조 무결성과 참조 동작
9. 트랜잭션 제어 언어(TCL)
10. 트랜잭션의 ACID 특성
11. SQL 연산자 총정리
12. 단일행 함수 활용법
13. 문자형 함수 모음
14. 숫자형 함수 모음
15. NULL 처리 함수
16. 다중행 집계 함수

 

#1. SQL의 종류와 특징

SQL(Structured Query Language)은 관계형 데이터베이스 관리 시스템(RDBMS)에서 데이터를 관리하기 위한 표준 언어입니다. SQL은 용도에 따라 다음과 같이 네 가지로 분류됩니다:

1) DML (Data Manipulation Language)

데이터를 조작하는 언어로, 실제 데이터를 다루는 명령어들입니다.

• SELECT: 데이터 조회
• INSERT: 데이터 삽입
• UPDATE: 데이터 수정
• DELETE: 데이터 삭제

2) DDL (Data Definition Language)

데이터베이스 객체를 정의하고 관리하는 언어입니다.

• CREATE: 객체 생성
• DROP: 객체 삭제
• ALTER: 객체 수정
• RENAME: 객체 이름 변경

3) DCL (Data Control Language)

데이터베이스 접근 권한과 관련된 언어입니다.

• GRANT: 권한 부여
• REVOKE: 권한 회수

4) TCL (Transaction Control Language)

트랜잭션을 제어하는 언어입니다.

• COMMIT: 트랜잭션 확정
• ROLLBACK: 트랜잭션 취소
• SAVEPOINT: 중간 저장점 설정

중요: DDL 명령어는 실행 시 자동으로 커밋(AUTO COMMIT)되지만, DML 명령어는 명시적으로 COMMIT을 입력해야 합니다.

 

 

#2. 데이터베이스 테이블의 기본 구조

테이블은 데이터베이스의 기본 단위이며, 데이터를 저장하는 객체입니다.

• 행(Row): 가로로 나열된 데이터 단위로, 튜플(Tuple) 또는 인스턴스(Instance)라고도 합니다.
• 열(Column): 세로로 나열된 데이터 단위로, 특정 속성이나 필드를 나타냅니다.

테이블은 행과 열이 교차하는 셀(Cell)에 실제 데이터 값이 저장되는 2차원 구조를 갖습니다.

 

#3. 절차적/비절차적 데이터 조작어

데이터 조작어는 접근 방식에 따라 두 가지로 나눌 수 있습니다:

비절차적 데이터 조작어 (DML)

• 사용자가 어떤 데이터를 원하는지 명확하게 명세합니다.
• SQL의 SELECT, INSERT, UPDATE, DELETE 등이 이에 해당합니다.
• "무엇을" 원하는지 명세하고, "어떻게" 가져올지는 DBMS가 결정합니다.

절차적 데이터 조작어

• 데이터에 어떻게 접근할지 명확하게 명세합니다.
• PL/SQL(Oracle), T-SQL(SQL Server) 등이 이에 해당합니다.
• 데이터를 가져오는 절차와 방법을 프로그래밍 방식으로 지정합니다.

 

#4. Oracle과 SQL Server의 주요 차이점

Oracle과 SQL Server는 두 가지 주요 RDBMS이지만, 여러 가지 면에서 차이가 있습니다:

No	구분	Oracle	SQL Server
1	여러 컬럼 동시 수정	지원함	지원하지 않음
2	괄호 사용	괄호를 사용함	괄호를 사용하지 않음
3	DDL 자동 커밋	DDL 문장 수행 후 자동으로 Commit 수행	DDL 문장 수행 후 자동으로 Commit 수행하지 않음
4	NULL 값 정렬 순서	NULL 값을 가장 큰 값으로 간주(오름차순 정렬 시 마지막에 위치)	NULL 값을 가장 작은 값으로 간주(오름차순 정렬 시 처음에 위치)
5	빈 문자열 처리	빈 문자열(' ')이 NULL로 저장됨(IS NULL로 검색)	빈 문자열(' ')이 NULL로 저장됨(=' '로 검색)

 

#5. 제약 조건의 종류와 특징

제약 조건(Constraint)은 데이터 무결성을 유지하기 위한 규칙입니다.

1) PRIMARY KEY (기본키)

• 테이블의 각 행을 고유하게 식별
• 특징: UNIQUE + NOT NULL
• 테이블당 하나만 정의 가능

2) UNIQUE KEY (고유키)

• 중복 값을 허용하지 않음
• 특징: UNIQUE, NULL 허용
• 테이블에 여러 UNIQUE 제약조건 정의 가능

3) CHECK

• 특정 컬럼에 입력할 수 있는 값의 범위를 제한
• 데이터 무결성을 확인하기 위한 제약조건

4) FOREIGN KEY (외래키)

• 테이블 간의 관계를 정의
• 다른 테이블의 PRIMARY KEY를 참조
• 특징: NULL 허용, 한 테이블에 여러 FK 가능
• 참조 무결성 제약을 받음(두 테이블 간의 일관성 유지)

 

#6. 인덱스 생성과 관리

인덱스는 데이터베이스 테이블의 검색 속도를 향상시키는 데이터 구조입니다.

인덱스 생성 구문

CREATE INDEX 인덱스_이름 ON 테이블명 (인덱스_생성_컬럼명);

예시

-- 단일 컬럼 인덱스 CREATE INDEX idx_employee_name ON employees (last_name); -- 복합 컬럼 인덱스 CREATE INDEX idx_emp_dept ON employees (department_id, job_id);

 

#7. 테이블 구조 변경 명령어

테이블 구조는 ALTER 명령어를 통해 변경할 수 있습니다.

테이블명 변경

RENAME 테이블명 TO 변경할_테이블명;

테이블 구조 변경 명령어

-- 컬럼 추가 ALTER TABLE PLAYER ADD (ADDRESS VARCHAR2(80)); -- 컬럼 수정 ALTER TABLE  PLAYER MODIFY (ORIG_YYYY VARCHAR(20) DEFAULT '20220901' NOT NULL); -- 컬럼 삭제 ALTER TABLE  PLAYER DROP COLUMN ADDRESS; -- 제약조건 삭제 ALTER TABLE  PLAYER DROP CONSTRAINT 조건명; -- 제약조건 추가 ALTER TABLE  PLAYER ADD CONSTRAINT 조건명 조건 (컬럼명); -- 테이블명 변경 RENAME PLAYER TO PLAYER_NEW_NAME; -- 컬럼명 변경 ALTER TABLE PLAYER RENAME COLUMN TEAM_ID TO T_ID;

테이블 삭제 관련 명령어

-- 테이블 삭제 DROP TABLE PLAYER; -- 테이블 데이터만 전체 삭제 (구조는 유지) TRUNCATE TABLE PLAYER;

 

#8. 참조 무결성과 참조 동작

참조 무결성은 외래키를 통해 참조되는 테이블의 데이터가 일관되게 유지되도록 하는 규칙입니다.

DELETE 동작

• CASCADE: 마스터 레코드 삭제 시 참조하는 자식 레코드도 함께 삭제
• SET NULL: 마스터 레코드 삭제 시 자식 레코드의 외래키 값을 NULL로 설정
• SET DEFAULT: 마스터 레코드 삭제 시 자식 레코드의 외래키 값을 기본값으로 설정
• RESTRICT: 자식 레코드가 참조하지 않는 경우에만 마스터 레코드 삭제 허용
• NO ACTION: 참조 무결성을 위반하는 삭제/수정 동작을 취하지 않음

INSERT 동작

• AUTOMATIC: 마스터 테이블에 PK가 없는 경우 PK 생성 후 자식 레코드 입력
• SET NULL: 마스터 테이블에 PK가 없는 경우 자식 테이블의 외래키를 NULL로 설정
• SET DEFAULT: 마스터 테이블에 PK가 없는 경우 자식 테이블의 외래키를 기본값으로 설정
• DEPENDENT: 마스터 테이블에 PK가 존재하는 경우에만 자식 레코드 입력 허용
• NO ACTION: 참조 무결성을 위반하는 입력 동작을 취하지 않음

 

#9. 트랜잭션 제어 언어(TCL)

트랜잭션(Transaction)은 데이터베이스의 논리적 작업 단위로, 밀접히 관련되어 분리될 수 없는 하나 이상의 데이터베이스 조작을 의미합니다.

TCL 명령어

• COMMIT: 트랜잭션의 변경사항을 데이터베이스에 영구적으로 반영
• COMMIT;
• ROLLBACK: 트랜잭션의 변경사항을 취소하고 이전 상태로 되돌림
• ROLLBACK;
• SAVEPOINT: 트랜잭션 내에 저장점을 설정하여 부분 롤백 가능
• SAVEPOINT 저장점_이름; ROLLBACK TO 저장점_이름;

 

#10. 트랜잭션의 ACID 특성

트랜잭션은 네 가지 중요한 특성을 가지며, 이를 ACID라고 합니다:

1) 원자성(Atomicity)

• 트랜잭션의 연산들은 모두 성공적으로 실행되거나 전혀 실행되지 않아야 함
• 부분적인 실행은 허용되지 않음(All or Nothing)

2) 일관성(Consistency)

• 트랜잭션 실행 전 데이터베이스가 일관된 상태라면, 실행 후에도 일관된 상태여야 함
• 무결성 제약조건이 유지되어야 함

3) 고립성(Isolation)

• 동시에 실행되는 트랜잭션들이 서로 영향을 미치지 않아야 함
• 트랜잭션 실행 중에는 다른 트랜잭션의 연산 결과를 참조할 수 없음

4) 지속성(Durability)

• 성공적으로 완료된 트랜잭션의 결과는 영구적으로 반영되어야 함
• 시스템 장애가 발생하더라도 데이터는 보존되어야 함

 

#11. SQL 연산자 총정리

SQL에서 사용되는 주요 연산자들을 알아보겠습니다:

1) 비교 연산자

• BETWEEN a AND b: a와 b 사이의 값(a, b 포함)
• SELECT * FROM employees WHERE salary BETWEEN 3000 AND 5000;
• IN (list): 목록에 있는 값 중 하나와 일치
• SELECT * FROM employees WHERE department_id IN (10, 20, 30);
• IS NULL: NULL 값인 경우
• SELECT * FROM employees WHERE manager_id IS NULL;

2) NULL 관련 특성

• NULL 값과의 수치 연산(+, -, *, /)은 NULL을 반환
• NULL 값과의 비교 연산은 FALSE를 반환

3) 연산자 우선순위

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

4) 특수 연산자

• ROWNUM: 쿼리 결과의 각 행에 부여되는 일련번호

SELECT * FROM employees WHERE ROWNUM <= 10; -- 상위 10개 행만 조회

 

#12. 단일행 함수 활용법

단일행 함수는 입력값에 대해 하나의 결과를 반환하는 함수입니다.

단일행 함수의 특징

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

-- 예시: 두 함수 중첩 SELECT UPPER(SUBSTR(first_name, 1, 3)) FROM employees;

 

#13. 문자형 함수 모음

문자형 데이터를 처리하는 함수들입니다:

No	함수	설명	예시
1	LOWER(문자열)	문자열을 소문자로 변환	SELECT LOWER('ORACLE') FROM dual;
2	UPPER(문자열)	문자열을 대문자로 변환	SELECT UPPER('oracle') FROM dual;
3	ASCII(문자)	문자의 ASCII 값 반환	SELECT ASCII('A') FROM dual;
4	CHR(숫자)	ASCII 값에 해당하는 문자 반환	SELECT CHR(65) FROM dual;
5	CONCAT(문자열1, 문자열2)	두 문자열 연결	SELECT CONCAT('Hello', 'World') FROM dual;
6	SUBSTR(문자열, m, n)	문자열의 m 위치에서 n개 문자 반환	SELECT SUBSTR('ORACLE', 2, 3) FROM dual;
7	LENGTH(문자열)	문자열의 길이 반환	SELECT LENGTH('ORACLE') FROM dual;

 

#14. 숫자형 함수 모음

숫자형 데이터를 처리하는 함수들입니다:

No	함수	설명	예시
1	SIGN(숫자)	숫자가 양수면 1, 음수면 -1, 0이면 0 반환	SELECT SIGN(-10) FROM dual;
2	MOD(숫자1, 숫자2)	숫자1을 숫자2로 나눈 나머지	SELECT MOD(10, 3) FROM dual;
3	CEIL(숫자)	숫자보다 크거나 같은 최소 정수	SELECT CEIL(10.1) FROM dual;
4	FLOOR(숫자)	숫자보다 작거나 같은 최대 정수	SELECT FLOOR(10.9) FROM dual;
5	ROUND(숫자, m)	숫자를 소수점 m자리까지 반올림	SELECT ROUND(10.567, 2) FROM dual;
6	TRUNC(숫자, m)	숫자를 소수점 m자리까지 truncate	SELECT TRUNC(10.567, 2) FROM dual;

 

#15. NULL 처리 함수

NULL 값을 처리하는 함수들입니다:

No	함수	설명	예시
1	NVL(식1, 식2)	식1이 NULL이면 식2 반환, 아니면 식1 반환	SELECT NVL(commission_pct, 0) FROM employees;
2	NULLIF(식1, 식2)	식1과 식2가 같으면 NULL 반환, 다르면 식1 반환	SELECT NULLIF(1, 1) FROM dual;
3	COALESCE(식1, 식2, ...)	NULL이 아닌 최초의 표현식 반환	SELECT COALESCE(NULL, NULL, 'Hello', 'World') FROM dual;

주의: NVL 함수는 공집합(결과가 없는 경우)을 변환해주지는 않습니다.

 

#16. 다중행 집계 함수

다중행 함수는 여러 행의 데이터를 그룹화하여 하나의 결과를 반환하는 함수입니다.

다중행 함수의 특징

1. 여러 행들의 그룹당 단 하나의 결과를 반환
2. GROUP BY 절로 행들을 소그룹화 가능
3. SELECT, HAVING, ORDER BY 절에서 사용 가능

주요 다중행 함수

No	함수	설명	예시
1	COUNT(*)	NULL 포함 행의 수	SELECT COUNT(*) FROM employees;
2	COUNT(표현식)	NULL 제외 행의 수	SELECT COUNT(commission_pct) FROM employees;
3	SUM(표현식)	NULL 제외 합계	SELECT SUM(salary) FROM employees;
4	AVG(표현식)	NULL 제외 평균	SELECT AVG(salary) FROM employees;
5	MAX(표현식)	최대값	SELECT MAX(salary) FROM employees;
6	MIN(표현식)	최소값	SELECT MIN(salary) FROM employees;
7	STDDEV(표현식)	표준편차	SELECT STDDEV(salary) FROM employees;
8	VARIANCE(표현식)	분산	SELECT VARIANCE(salary) FROM employees;

옵션 사용

• ALL: 기본 옵션, 모든 값 사용 (생략 가능)
• DISTINCT: 중복 값을 제거하고 계산

SELECT COUNT(DISTINCT department_id) FROM employees;

---

결론

이번 포스팅에서는 Oracle SQL의 기초부터 고급 기능까지 총정리해보았습니다. SQL의 종류, 테이블 구조, 제약 조건, 명령어, 함수 등 Oracle 데이터베이스를 효과적으로 다루기 위한 핵심 내용을 다루었습니다.

데이터베이스 개발과 관리에 있어 SQL은 가장 기본적이면서도 강력한 도구입니다. 이 글이 여러분의 데이터베이스 작업에 도움이 되길 바랍니다. 더 많은 데이터베이스 관련 내용은 다음 포스팅에서 계속 이어가겠습니다.

긴 글 읽어주셔서 감사합니다.

끝.

---

참고 자료:

• Oracle 공식 문서
• SQL 표준 가이드
• 데이터베이스 설계 및 구현 지침서