☁️Dorong9's Blog

2과목 - SQL 기본 및 활용

32 min read

[!info]

2과목 - SQL 기본 및 활용에 대한 내용을 정리한 페이지입니다.

1장 SQL 기본

데이터베이스와 DBMS

  • 데이터베이스 : 데이터의 집합
  • DBMS : 효율적 데이터 관리 및 데이터 손상 예방, 복구를 위한 소프트웨어

관계형 데이터베이스 구성 요소

  • 계정 : 데이터 접근 제한 위해 업무별/시스템별 구분
  • 테이블 : DBMS의 DB 안의 데이터 저장 형식
  • 스키마 : 테이블의 기본적 구조 정의

테이블

DB 기본 단위. 데이터 저장 객체

  • 가로 = 행 = 로우 = 튜플 = 인스턴스
  • 세로 = 열 = 컬럼 = 속성

테이블의 특징

  1. 하나의 테이블은 반드시 하나의 계정 소유
  2. 테이블 간 관계는 1:1, 1:N, M:N의 관계
  3. 테이블명 중복 X (소유자가 다른 경우는 O)
  4. 행 단위로 데이터 입력, 삭제 (수정은 값 단위)

관계형 데이터베이스 특징

  1. 데이터의 분류, 정렬, 탐색 속도 빠름
  2. 신뢰성이 높고 데이터의 무결성 보장
  3. 기존의 작성된 스키마를 수정하기 어려움
  4. 데이터베이스의 부하를 분석하는 것 어려움

데이터 무결성

데이터의 정확성과 일관성을 유지하고, 데이터에 결손과 부정합 없음을 보증

  1. 개체 무결성 : 기본키 NULL X, 중복값 X
  2. 참조 무결성 : 외래키 NULL 또는 참조테이블 기본키
  3. 도메인 무결성 : 속성 값이 도메인에 속한 값
  4. NULL 무결성 : 특정 속성에 대해 NULL 허용 X
  5. 고유 무결성 : 특정 속성에 대해 값 중복 X
  6. 키 무결성 : 하나의 관계에는 하나의 키가 존재해야 함

SQL 종류

  • DDL (정의) : CREATE, ALTER, DROP, TRUNCATE
  • DML (조작) : INSERT, DELETE, UPDATE, MERGE, SELECT(DQL)
  • DCL (제어) : GRANT, REVOKE
  • TCL (트랜잭션 제어) : COMMIT, ROLLBACK

SELECT문 구조

6개 절로 구성. 각 절의 순서대로 작성해야 함

실행 순서 (파싱 순서) FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

SELECT * | 컬럼명 | 표현식
FROM 테이블명 또는 뷰명
WHERE 조회 조건
GROUP BY 그룹핑컬럼명
HAVING 그룹핑 필터링 조건
ORDER BY 정렬컬럼명

옵티마이저가 SYNTAX, SEMANTIC 에러를 점검하는 순서이기도 함

컬럼 Alias (별칭)

컬럼명 대신 출력할 임시 이름 지정

  • 컬럼명 뒤에 AS와 함께 별칭 전달 (AS 생략 O)
  • 주의사항
    1. ORDER BY 절에서만 별칭 사용 가능
    2. 이미 존재하는 예약어는 별칭으로 사용 X
    3. 공백, 특수문자 포함 및 별칭 그대로 : ' ' 사용

FROM 절

  • 데이터를 불러올 테이블명 또는 뷰명 전달
  • 테이블 여러 개 전달 가능 (콤마로 구분)
  • 테이블 별칭 선언 가능 (오라클 AS 사용 X)
  • 오라클 : FROM절 생략 X (DUAL 테이블 사용)
  • SQL Server : 생략 O

WHERE 절

조건에 맞는 데이터만 조회

  • NULL 조회 시 IS NULL / IS NOT NULL 연산자
  • 문자, 날짜 상수 시 반드시 홑따옴표 ' ' 사용
  • 오라클 : 대소문자 구분 O / MSSQL : 구분 X
연산자설명
!=, <>, ^=같지 않은 조건
IN(a, b, c)a or b or c
LIKE특정 패턴 가진 조건
NOT AA가 아닌 모든 조건
  • LIKE + % : 자리수 제한 X
  • LIKE + _ : _ 개수만큼 자리수 제한 O

GROUP BY 절

각 행을 특정 조건에 따라 그룹으로 분리하여 계산

  • 그룹에 대한 조건은 WHERE 절 사용 X
  • 요약 전 데이터와 함께 출력 X
  • Alias 사용 X

HAVING 절

그룹 함수 결과를 조건으로 사용

  • SELECT 절에서 선언된 Alias 사용 X
  • SQL Server에서는 HAVING 절이 GROUP BY 절 앞에 나올 시 문법오류 발생

ORDER BY 절

특정 컬럼을 기준으로 정렬

  • 유일하게 SELECT 절 Alias명 사용 가능
  • 컬럼 순서를 나타내는 정수도 사용 가능
  • ASC (오름차순, 생략O) / DESC (내림차순)
  • 오라클 : NULL을 가장 큰 값으로 취급
  • SQL Server : 가장 작은 값으로 취급
  • 여러 컬럼을 기준으로 정렬 가능 (복합정렬)

산술연산자

NUMBER와 DATE 자료형에 대해 적용. 수학의 사칙연산과 동일

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

합성연산자

문자를 연결하는 CONCATENATION 연산자

  • 오라클 : || / SQL Server : +
  • CONCAT 함수

함수

input value-output value 관계 정의의 객체

1. 입력값 수에 따른 분류

  • 단일행 : input과 output의 관계가 1:1
  • 복수행 : 여러 건의 데이터를 동시에 입력 받아 하나의 요약값을 리턴 (집계함수, 그룹함수)

2. 문자형 함수

함수설명
LOWER(대상)소문자 변환
UPPER(대상)대문자 변환
ASCII(대상)ASCII 코드 번호로 변환
CHR/CHAR(ASCII)코드 번호를 문자로 변환
CONCAT(대상1, 대상2)연결
SUBSTR(대상, M[, N])M 위치에서 N개 문자 반환
LENGTH(대상)문자 길이 반환
L/RTRIM(대상[, 삭제문자열])왼/오른쪽에서 특정 문자 삭제
TRIM(대상)양쪽 특정 문자 삭제
L/RPAD(대상, n, 문자열)왼/오른쪽에 문자열 추가하여 총 n 길이 반환
REPLACE(대상, 찾을문자열, 바꿀문자열)치환
TRANSLATE(대상, 찾을문자열, 바꿀문자열)글자를 1:1로 치환

3. 숫자형 함수

함수설명
ABS(숫자)절대값
ROUND(숫자, 자리수)반올림
TRUNC(숫자, 자리수)버림
SIGN(숫자)양수 1, 음수 -1, 0이면 0 반환
FLOOR(숫자)작거나 같은 최대 정수
CEIL(숫자)크거나 같은 최소 정수
MOD(피제수, 제수)나머지
POWER(m, n)m의 n제곱
SQRT(숫자)루트값

4. 날짜형 함수

함수설명
SYSDATE / GETDATE()현재 날짜, 시각
EXTRACT(‘YEAR’|‘MONTH’|‘DAY’ from d) / DATEPART(‘YEAR’|‘MONTH’|‘DAY’, d)날짜에서 연월일 출력
TO_NUMBER(TO_CHAR(d, ‘YYYY’)) / YEAR(d)날짜에서 연도 출력

5. 변환함수

값의 데이터 타입 변환

함수설명
TO_NUMBER(문자)숫자로 변환
TO_CHAR(대상, 포맷)문자타입으로 변환
TO_DATE(문자, 포맷)날짜로 변환
FORMAT(날짜, 포맷)날짜의 포맷 변경
CAST(대상 AS 데이터타입)데이터타입 변환

6. 그룹함수 (집계함수)

여러 값이 input으로 들어가서 하나의 요약된 값으로 리턴. 다중행함수

  • COUNT / SUM / AVG / MIN / MAX / VARIANCE(분산) / STDDEV(표준편차)
  • NULL 무시하고 연산

7. 일반함수

함수설명
DECODE(대상, 값1, 리턴1, ~, 그 외 리턴)대상이 값1이면 리턴1, … 그 외 리턴값 (생략 시 NULL)
NVL(대상, 치환값)대상이 NULL이면 치환값
NVL2(대상, 치환값1, 치환값2)NULL이면 치환값1, NULL 아니면 치환값2
COALESCE(대상1, 대상2, ~)첫 번째로 NULL 아닌 값 반환. 모두 NULL이면 그 외 리턴값
ISNULL(대상, 치환값)대상 NULL이면 치환값 (SQL Server)
NULLIF(대상1, 대상2)두 값이 같으면 NULL, 다르면 대상1 반환
CASE문조건별 치환 및 연산 수행

2장 SQL 활용

조인 (JOIN)

두 개 이상의 테이블을 연결해 데이터 출력

  • FROM 절에 조인할 테이블 나열
  • 동일한 열 이름이 여러 테이블에 존재할 경우 테이블 이름이나 Alias 붙임
  • N개의 테이블을 조인 : 최소 N-1개의 조인 필요
  • 오라클 : 순서 상관 X, WHERE 절에 기술
  • ANSI 표준 : 외부 조인 시 순서 중요, ON 절

조인의 종류

1. 조건의 형태에 따라

  • EQUI 조인 : 조인 조건이 동등 조건인 경우
  • NON EQUI 조인 : 동등 조건이 아닌 경우 (BETWEEN 등)

2. 조인 결과에 따라

  • INNER JOIN : 조인 조건에 성립하는 데이터만 출력
  • OUTER JOIN : 조건에 성립하지 않는 데이터도 출력 (LEFT, RIGHT, FULL OUTER JOIN)
  • NATURAL JOIN : 조인 조건 생략 시, 두 테이블에 같은 이름으로 자연 연결
  • CROSS JOIN : 조인 조건 생략 시, 두 테이블의 가능한 모든 행을 출력 → 카티시안 곱
  • SELF JOIN : 하나의 테이블을 두 번 이상 참조

표준조인 (ANSI)

INNER JOIN (오라클 조인 기본)

  • ANSI 표준의 경우 FROM절에 명시, USING이나 ON 조건절 필수
  • INNER 생략해도 가능

NATURAL JOIN

  • 동일한 이름을 갖는 모든 컬럼들 EQUI 조인 수행
  • USING, ON, WHERE 절에서 조인조건 정의 불가
  • SQL Server 지원 X
  • 조인에 사용된 컬럼은 같은 데이터 유형이어야 함
  • Alias나 테이블명 같은 접두사 쓸 수 없음

USING 절

  • 조인할 컬럼명이 같을 경우 사용. SQL Server 지원 X
  • Alias, 테이블명 붙일 수 없음. 괄호 필수

ON 절

  • 컬럼명이 달라도 사용 가능
  • ON 조건의 괄호는 옵션 (생략가능)
  • 조인 서술부(ON), 비조인 서술부(WHERE) 분리
  • WHERE 절과 혼용 O / ON 조건절에 JOIN 외의 검색 조건 추가 O

CROSS JOIN

  • 카테시안 곱, CROSS PRODUCT와 같은 표현
  • 결과는 양쪽 집합의 M*N건의 데이터 조합

OUTER JOIN

  • USING이나 ON 절 반드시 사용해야 함
  • SQL식에서 (+) 붙지 않은 쪽으로 조인
  • 조건을 만족하는 값이 없을 땐 NULL로 채움

서브쿼리

하나의 SQL문 안에 포함되어 있는 또 다른 SQL문 (쿼리 안에 있는 쿼리)

  • 서브쿼리는 메인쿼리의 컬럼을 모두 사용할 수 있지만, 메인쿼리는 서브쿼리 컬럼 사용 불가
  • GROUP BY절에서 사용 불가
  • 다른 행, 테이블과의 비교 및 연산을 위해 사용

1. 형태에 따른 분류

① 단일행 서브쿼리 : 실행결과 1개 이하

  • 단 하나의 상수와 비교하는 비교연산자 사용 O

② 다중행 서브쿼리 : 실행결과 2개 이상

  • 단순비교연산자(=, >, < 등) 사용 불가
  • IN, ANY, ALL 등의 연산자 사용 O

③ 다중컬럼 서브쿼리 : 실행결과 컬럼 여러 개

  • 메인쿼리 여러 컬럼과 서브쿼리 여러 컬럼이 일치하는 경우의 행 추출 시 사용
  • 메인쿼리의 여러 컬럼을 괄호로 묶어서 비교

2. 위치에 따른 분류

① 스칼라 서브쿼리 : SELECT절에 사용

  • 서브쿼리 결과를 메인쿼리의 컬럼처럼 표현
  • 각 행별로 단 하나의 결과만 허용 (단일형)

② 인라인뷰 : FROM절에 사용

  • 서브쿼리 결과를 가상 테이블처럼 사용
  • 메인쿼리 테이블과 조인 가능 (테이블 별칭 명시)

③ 일반 서브쿼리 : WHERE절에 사용

  • 비교 상수 자리에 값 전달 위해 사용

3. 메인쿼리와의 관계에 따른 분류

① 비연관 : 서브쿼리 내 메인쿼리 컬럼 존재 X

  • 메인쿼리에 서브쿼리 실행 결과값 제공하기 위해 사용

② 연관 : 서브쿼리 내 메인쿼리 컬럼 존재 O

  • 메인쿼리가 먼저 수행된 후, 서브쿼리에서 조건이 맞는지 확인하는 목적으로 사용

집합연산자

두 개 이상의 테이블에서 조인을 사용하지 않고 연관된 데이터를 조회할 때 사용

  • SELECT문 결과를 하나의 집합으로 간주하고 합집합, 교집합, 차집합 연산
  • 두 집합 컬럼 동일하게 구성 (데이터 타입, 순서)
  • 전체 집합의 데이터 타입과 컬럼명은 첫 번째 집합에 의해 결정
연산자설명
UNION합집합, 중복 제거 (내부적으로 정렬 수행)
UNION ALL합집합, 중복 포함 전체 출력
INTERSECT교집합
EXCEPT / MINUS (오라클)차집합 (순서 중요: A-B ≠ B-A)

사용 시 주의사항

  1. 두 집합의 컬럼 수가 일치해야 함
  2. 두 집합의 컬럼 순서가 일치해야 함
  3. 두 집합의 각 컬럼의 데이터 타입이 일치해야 함
  4. 각 컬럼의 사이즈(Volume)은 달라도 됨

그룹함수

데이터를 다차원적으로 집계. GROUP BY 절과 사용. 반드시 한 컬럼만 전달. NULL 무시

함수설명
ROLLUP소계, 총계 산출. A별, (A,B)별, 전체그룹연산결과 출력 (순서 상관O)
CUBE가능한 모든 조합 집계. A별, B별, (A,B)별, 전체 출력 (순서 X)
GROUPING SETS특정 그룹의 집계. A별, B별 그룹 연산 결과 출력 (순서 X)
GROUPING특정 컬럼 집계 사용 여부 반환. 사용O → 0, 사용X → 1

윈도우 함수

서로 다른 행의 비교, 연산 함수

  • GROUP BY 쓰지 않고 연산 가능
  • 행이 그대로 유지되면서 값이 추가됨
윈도우함수(인자) OVER (
  [PARTITION BY 컬럼]
  [ORDER BY 컬럼]
  [ROWS | RANGE BETWEEN 시작점 AND 끝점]
)
  • PARTITION BY : 계산을 적용할 행 그룹 지정
  • ORDER BY : 파티션 내에서 행 순서 지정
  • ROWS | RANGE : 연산 범위 설정

윈도우 함수 종류

1. 순위 관련 함수

함수설명
ROW_NUMBER()파티션 내에서 순차적 번호
RANK()동일값 동일순위, 다음 순위 건너뜀
DENSE_RANK()동일 순위 다음 순차적 값 (건너뜀 없음)

2. 집계 관련 함수

함수설명
SUM() OVER파티션별 합계
AVG() OVER파티션별 평균
MAX/MIN() OVER파티션별 최댓값/최솟값

3. 행 이동 관련 함수

함수설명
LAG()이전 행 값 가져오기
LEAD()다음 행의 값 가져오기

4. 누적 관련 함수

함수설명
CUME_DIST()누적 분포 값 (누적 비율) 계산
PERCENT_RANK()백분위 순위 계산

TOP N 쿼리

전체 결과에서 특정 N개 추출. 페이징 처리를 효과적으로 수행하기 위해 사용

TOP N 행 추출 방법

  1. ROWNUM : 출력된 데이터 기준 행 번호 부여. 가상의 번호 → 특정 행 지정 X, 연산 X
  2. RANK
  3. FETCH : 출력된 행의 수 제한. ORDER BY 절 뒤에 사용
SELECT 컬럼
FROM 테이블
ORDER BY 차순
OFFSET N { ROW | ROWS }
FETCH { FIRST | NEXT } N { ROW | ROWS } ONLY;
  • FIRST : OFFSET X → 처음부터 N행 출력
  • NEXT : OFFSET O → 제외한 행 다음부터 N행
  • ROW | ROWS : 행의 수에 따라 단수형/복수형

셀프 조인

하나의 테이블을 두 개인 것처럼 조인

  • 주로 계층적 관계나 순차적 관계 조회 시 사용
  • 식별 위해 반드시 테이블 별칭 Alias 사용

계층형 질의

계층형 데이터 : 동일 테이블에 계층적으로 상위와 하위 데이터가 포함된 데이터

SELECT 컬럼
FROM 테이블
START WITH 시작조건
CONNECT BY PRIOR 연결조건;
  • START WITH : 데이터 출력 시작 조건 지정
  • CONNECT BY PRIOR : 행을 이어나갈 조건
  • PRIOR 위치에 따라 연결하는 데이터 달라짐

PRIOR 방향

  • PRIOR 자식 = 부모 : 부모데이터 → 자식데이터
  • PRIOR 부모 = 자식 : 자식데이터 → 부모데이터

계층적 질의 가상 컬럼

컬럼설명
LEVEL계층의 깊이 (루트데이터 1, 하위 2, 리프데이터까지 1씩 증가)
CONNECT_BY_ISLEAF해당 데이터가 리프데이터면 1, 아니면 0
CONNECT_BY_ISCYCLE해당 데이터가 조상으로 존재하면 1, 아니면 0

계층적 질의 가상 함수

함수설명
CONNECT_BY_ROOT 컬럼명루트 노드의 해당 컬럼명 출력
SYS_CONNECT_BY_PATH(컬럼, 구분자)루트부터 현재 노드까지 경로를 문자열로 반환
ORDER SIBLINGS BY 컬럼같은 LEVEL일 경우 (형제 노드들끼리) 정렬 수행

PIVOT과 UNPIVOT

데이터의 구조를 회전

  • PIVOT : 행 → 열
  • UNPIVOT : 열 → 행
SELECT
FROM 테이블
(UN)PIVOT (
  집계함수(집계대상컬럼)
  FOR 피벗컬럼 IN (피벗값1, 피벗값2, ...)
);

정규표현식

문자열의 공통된 규칙을 일반화하여 표현. 문자열 내에서 특정 패턴을 검색, 추출, 변환에 사용 (문자함수와 함께 사용)

메타 문자의미예
\메타 문자를 리터럴 문자로 표시하거나 리터럴 문자와 결합하여 정해진 메타 문자를 표시ww : w, wn : 줄바꿈(개행) 문자
^개행으로 나뉜 문자열의 시작 지정^The : The로 시작하는 문자열
$개행으로 나뉜 문자열의 끝 지정ing$ : ing로 끝나는 문자열
.임의의 한 문자 (개행 문자 제외)a.b : acb, a-b, a1b, …
?선행 문자 0 또는 1개no? : n, no
*선행 문자 0개 이상no* : n, no, noo, nooo…
+선행 문자 1개 이상no+ : no, noo, nooo…
|선택적 일치a|b : a, b
[]대괄호 안의 문자들 중 하나와 일치[abc] : a, b, c
[-]연속 문자의 범위를 지정[a-z] : a부터 z까지 소문자 알파벳 문자
[^]대괄호 안의 문자들을 제외한 나머지 중 하나와 일치[^abc] : d, e, z, …(a, b, c를 제외한 나머지 문자)
()소괄호로 묶인 표현식을 한 단위로 취급(ab) : ab
  • SQL Server : % (0개 이상의 문자), _ (임의의 한 문자)

3장 관리 구문

DML (데이터 조작어)

데이터베이스의 테이블에 데이터를 추가, 수정, 삭제하는 명령어

  • INSERT, UPDATE, DELETE, MERGE

INSERT

새로운 데이터(행) 추가

INSERT INTO 테이블명 (컬럼1, 컬럼2, ...)
VALUES (값1, 값2, ...);
  • 하나의 컬럼에 한 값만
  • 컬럼별 데이터 타입과 사이즈에 맞게 삽입
  • INTO절에 컬럼명을 명시하여 일부 컬럼만 입력 가능
  • 전체 컬럼 데이터 입력 시 컬럼명 생략 가능

UPDATE

데이터를 수정할 때 컬럼 단위 사용

UPDATE 테이블명
SET 수정할컬럼명 = 수정값
WHERE 조건;
  • 다중 컬럼 수정 가능 (서브쿼리 사용)
  • 서브쿼리 결과가 각 컬럼마다 한 값으로 정의되지 않으면 오류 발생

DELETE

데이터를 삭제할 때 행 단위 사용

DELETE [FROM] 테이블명
[WHERE 조건];

MERGE

데이터 병합. 참조 테이블과 동일하게 맞추는 작업. INSERT, UPDATE, DELETE 작업과 동시에 수행

MERGE INTO 테이블명
USING 참조테이블
  ON (연결조건)
WHEN MATCHED THEN
  UPDATE SET 수정할내용
WHEN NOT MATCHED THEN
  INSERT VALUES (삽입할내용)
  • 수정할 테이블명 → MERGE INTO 절
  • 참조 테이블 → USING 절
  • 두 테이블의 데이터를 참조할 조건 → ON 절
  • UPDATE문에서는 테이블명 명시 X
  • INSERT 문에는 INTO 절 없이 VALUES로 컬럼명 전달

TCL (트랜잭션 제어어)

트랜잭션의 속성 (ACID)

  1. 원자성 : 묶인 연산은 모두 실행 OR 전혀 실행 X
  2. 일관성 : 트랜잭션 결과는 DB의 정합성 깨지 X
  3. 고립성 : 트랜잭션은 독립적으로 수행 (간섭 X)
  4. 지속성 : 결과는 DB에 영구적으로 저장, 유지

COMMIT

COMMIT을 실행하면 트랜잭션이 종료되고, 해당 트랜잭션의 변경사항이 DB에 영구적 저장

  • COMMIT 이후 해당 트랜잭션 ROLLBACK 불가

트랜잭션을 시작하면 일종의 쓰기 잠금(LOCK)이 발생하여 다른 사용자가 DB의 데이터를 수정, 반영하는 것을 막음

ROLLBACK

트랜잭션의 모든 변경사항 (혹은 SAVEPOINT 이후)을 취소하고, 트랜잭션 시작 시점으로 되돌림

  • DDL 명령어는 자동으로 커밋되어 롤백 불가, DML은 가능

SAVEPOINT

특정 지점 지정 후 그 곳까지 롤백

  • 오라클 : 트랜잭션 시작 지정 X
  • SQL Server : BEGIN TRANSACTION으로 지정

DDL (데이터 정의어)

데이터 구조를 정의 (객체 생성, 삭제, 변경)

  • CREATE, ALTER, DROP, TRUNCATE
  • AUTO COMMIT (명령어를 수행하면 즉시 저장하고, 원상복구 불가)

CREATE

테이블이나 인덱스 같은 객체를 생성

CREATE TABLE 테이블명 (
  컬럼1 데이터타입 [제약조건],
  컬럼2 데이터타입 [제약조건], ...
);
  • 테이블명, 컬럼명, 컬럼 순서, 크기, 데이터타입 정의
  • 각 컬럼의 제약조건, 기본값 생략 가능
  • 테이블 생성 시 소유자 명시 가능

ALTER

테이블 구조 변경. 컬럼명, 데이터타입, 사이즈, 기본값, 삭제, 추가, 제약 조건 변경

  • 컬럼 순서 변경 불가 (→ 테이블 재생성으로 해결)
  • 컬럼 추가 : 맨 뒤로 추가. 데이터타입 명시 필수. 여러 컬럼 동시 추가 가능
  • 컬럼 변경 : 사이즈, 데이터타입, 기본값 변경 가능. 여러 컬럼 동시 변경 가능
  • 컬럼명 변경 : 항상 가능. 여러 컬럼 동시에 불가
  • 컬럼 삭제 : RECYCLEBIN에 남지 않아, FLASHBACK으로 복구 불가. 동시 삭제 불가

DROP

객체(테이블, 인덱스 등) 삭제. DROP 후에는 조회 불가

TRUNCATE

구조(테이블명, 컬럼 등)만 남기고 데이터 즉시 삭제. 데이터 일부 삭제 불가

DELETE / DROP / TRUNCATE 차이

구분DELETEDROPTRUNCATE
삭제 범위데이터 일부 또는 전체데이터 + 구조 모두데이터만 전체 (인덱스, 트리거, 제약조건 유지)
롤백가능불가불가
공간유지반환초기화

제약조건

데이터 무결성을 위해 각 컬럼에 생성하는 데이터의 제약 장치

  • 테이블 생성 / 컬럼 추가 시 정의 가능
  • 이미 생성된 컬럼에 제약조건만 추가 가능
ALTER TABLE 테이블명 ADD [DEFAULT 기본값] [제약조건];
  • PK / UNIQUE / NOT NULL / FK / CHECK (직접적으로 데이터의 값 제한)

기타 오브젝트

VIEW

저장공간을 가지지 않지만, 테이블처럼 조회 및 수정 가능

  • 기존테이블 삭제시, 그 테이블을 참조한 뷰도 삭제됨
  • 논리적 독립성, 보안 유지, 데이터 관리 단순화
  • 뷰의 정의 변경 X, 삽입·삭제·갱신 연산에 제한, 인덱스 구성 X

SEQUENCE

자동으로 연속적인 숫자를 부여

CREATE SEQUENCE 시퀀스명
  INCREMENT BY   -- 증가값 (DEFAULT: 1)
  START WITH     -- 시작값 (DEFAULT: 1)
  MAXVALUE       -- 최댓값 (증가시퀀스), 재사용 시 시작값 (감소시퀀스)
  MINVALUE       -- 최솟값 (감소시퀀스), 마지막값 (감소시퀀스)
  CYCLE | NOCYCLE  -- 시퀀스 번호 재사용 (DEFAULT: NOCYCLE)
  CACHE N;       -- 캐시값 (DEFAULT: 20)

SYNONYM (시노님)

테이블 별칭 생성

CREATE [OR REPLACE] [PUBLIC] SYNONYM 별칭 FOR 테이블명;
  • OR REPLACE : 기존에 같은 이름으로 시노님이 생성되어 있는 경우 대체
  • PUBLIC : PRIVATE SYNONYM의 반대 → 누구나 사용가능
  • PUBLIC으로 생성한 시노님은 반드시 PUBLIC으로 삭제

DCL (데이터 제어어)

DB 접근 권한을 관리

  • 일반적으로 본인 소유가 아닌 테이블 조회 불가
  • 오브젝트 권한 : 특정 테이블(객체)에 대한 권한
  • 시스템 권한 : 데이터베이스 전체에 대한 권한. 관리자 권한만 권한 부여, 회수 가능

GRANT

사용자에게 권한 부여

GRANT 권한1, 권한2, ... ON 객체명 TO 사용자명 [WITH GRANT OPTION];
  • WITH GRANT OPTION : 부여받은 오브젝트 권한을 다른 사용자에게 부여. 중간관리자 권한 회수 시 제3자 권한도 함께 회수됨
  • WITH ADMIN OPTION : 부여받은 시스템 권한/롤 권한을 다른 사용자에게 부여. 중간관리자 회수 시 제3자 권한 유지됨
  • 동시에 여러 객체 권한 부여 불가능

REVOKE

사용자에게 부여한 권한 회수

REVOKE 권한1, 권한2, ... ON 객체명 FROM 사용자명;

ROLE을 이용한 권한 관리

ROLE : 여러 권한을 묶어 그룹화한 것. 효율적 권한 관리 가능

  • SYSTEM 계정에서 ROLE 생성 가능
  • 여러 사용자에게 동일한 권한을 부여하고 관리하는 작업 간소화
  • ROLE을 통해 부여한 권한은 ROLE로만 회수
-- ROLE 생성
CREATE ROLE hr_role;
 
-- ROLE에 권한 부여
GRANT SELECT, INSERT, UPDATE ON EMP TO hr_role;
GRANT SELECT ON DEPT TO hr_role;
 
-- 사용자에게 ROLE 부여
GRANT hr_role TO user1, user2;
 
-- ROLE 삭제
DROP ROLE hr_role;

그래프 뷰