Sql implements guide

SQL 활용 완벽 가이드

출제 빈도: 상
빈출 태그: CASCADE, RESTRICT, 삭제/조회/권한 부여 문법

데이터 정의어 (DDL)

CREATE

• 객체(DB, TABLE, INDEX, VIEW 등) 생성
• 메타언어 표현: CREATE <객체유형> <객체명> <옵션>;
  • < >: 입력요소 (생략가능)
  • [ ]: 선택요소
  • [, ...]: 반복가능
  • |: 선택가능

테이블 생성 문법

CREATE TABLE <테이블명> (
    <컬럼명> <데이터유형> [<제약조건>] [, ...],
    [테이블 제약조건]
);

데이터 유형과 제약조건

• 데이터 유형:
  • INT, DOUBLE
  • CHAR - 고정크기 문자열
  • VARCHAR(N) - 가변크기 문자열
  • DATE
  • UNIQUE - 유일키 식별자
  • NOT NULL - 공백 불허
  • CHECK - 조건식 만족값만 허용
• 제약조건:
  • PRIMARY KEY (<컬럼명>[, ...]) - 기본키
  • FOREIGN KEY (<컬럼명>[, ...]) REFERENCES (<컬럼명>[, ...])
  • ON UPDATE <처리옵션>
  • ON DELETE <처리옵션>

⭐처리옵션⭐

• NO ACTION: 아무 작업 안함
• SET DEFAULT: 기본값 지정
• SET NULL: 공백 처리
• CASCADE: 관련 튜플 모두 처리
• RESTRICT: 관련 튜플 없는 경우만 처리
• NULLIFY: NULL 값으로 비움

테이블 구조 복사

CREATE TABLE <테이블명> AS <SELECT문>;

예시

CREATE TABLE 학생(
    학번 INT PRIMARY KEY,
    이름 CHAR(10) NOT NULL,
    연락처 CHAR(15) DEFAULT '비공개'
);

CREATE TABLE 도서대여(
    대여일 DATE NOT NULL,
    학번 INT,
    도서명 CHAR(20) NOT NULL,
    FOREIGN KEY(학번) REFERENCES 학생(학번)
        ON UPDATE CASCADE
        ON DELETE RESTRICT
);

ALTER

• 컬럼 변경:

  ALTER TABLE <테이블명> ADD <필드명> <데이터타입> [<위치옵션>];
  ALTER TABLE <테이블명> MODIFY <컬럼명> <데이터유형>;
  ALTER TABLE <테이블명> RENAME COLUMN <원본컬럼명> TO <변경컬럼명>;
  

• 컬럼 삭제:

  ALTER TABLE <테이블명> DROP <컬럼명>;
  

• 제약조건 변경:

  ALTER TABLE <테이블명> ADD CONSTRAINT <제약조건명> <제약조건>;
  ALTER TABLE <테이블명> ENABLE|DISABLE|DROP CONSTRAINT <제약조건명>;
  

DROP

DROP <객체유형> <객체명> [<삭제옵션>];
TRUNCATE TABLE <테이블명>;

데이터 조작어 (DML)

INSERT

-- 모든 값 삽입
INSERT INTO <테이블명> VALUES (<값>[, ...]);

-- 특정 컬럼 값 삽입
INSERT INTO <테이블명> (<컬럼명>[, ...]) VALUES (<값>[, ...]);

-- 다른 테이블에서 복사
INSERT INTO <테이블명> (<필드>[, ...]) <SELECT문>

UPDATE

UPDATE <테이블명> SET <컬럼명>=<값>[, ...] WHERE <조건식>;

DELETE

DELETE FROM <테이블명> WHERE <조건식>;
DELETE * FROM <테이블명> WHERE <조건식>;

SELECT

SELECT [ALL|DISTINCT] <컬럼명>[,...] FROM <테이블명>
    [WHERE <조건식>
    [GROUP BY <컬럼명> [HAVING <조건식>]]
    [ORDER BY <컬럼명> [ASC | DESC]]];

SELECT 활용

AS (별칭)

• 집계함수:
  • COUNT(): 레코드 개수
  • SUM()/AVG(): 합계/평균
  • MAX()/MIN(): 최대값/최소값
  • STDDEV(): 표준편차
  • VARIAN(): 분산

SELECT SUM(점수) AS 합계 FROM 학생정보;
SELECT 이름 AS 학생명, 점수-5 AS 성적 FROM 학생정보;

⭐윈도우 함수⭐

• 순위함수:
  • RANK: 동일 순위만큼 다음 순위 건너뜀
  • DENSE_RANK: 동일 순위를 하나로 처리
  • ROW_NUMBER: 순차적 순위 부여
• 행순서 함수:
  • FIRST_VALUE: 최소값
  • LAST_VALUE: 최대값
  • LAG: 이전 N번째 행의 값
  • LEAD: 이후 N번째 행의 값

조건식

AND

SELECT * FROM 성적 WHERE 국어>=80 AND 영어>=80;
-- BETWEEN 활용
SELECT * FROM 성적 WHERE 수학 BETWEEN 80 AND 89;

OR

SELECT * FROM 성적 WHERE 반="1반" OR 반="3반" OR 반="5반";
-- IN 활용
SELECT * FROM 성적 WHERE 반 IN("1반", "3반", "5반");

IS NULL

SELECT * FROM 성적 WHERE 벌점 IS NULL;

LIKE

SELECT * FROM 성적 WHERE 이름 LIKE <패턴>;

• LIKE %동: 동으로 끝나는 문자열
• LIKE 홍%: 홍으로 시작하는 문자열
• LIKE %길%: 길을 포함하는 문자열
• LIKE 강_: 강으로 시작하는 2글자
• LIKE _강_: 강이 가운데인 3글자
• LIKE 강__: 강으로 시작하는 3글자

하위 질의 (Sub Query)

서브 쿼리 기본

• 메인 쿼리 이전에 한번만 실행
• 결과값은 메인쿼리의 내부 요소

유의사항:

• 비교연산자 오른쪽에 기술
• 소괄호()로 감싸기
• 메인쿼리가 기대하는 행/컬럼 수와 일치
• ORDER BY 사용 불가

단일 행 서브쿼리

• 하나의 행으로 결과 반환
• 사용 연산자: =, <>, >, >=, <, <=

SELECT * FROM 성적 
WHERE 학과=(SELECT 학과 FROM 성적 WHERE 이름="권영석");

다중 행 서브쿼리

• 여러 행으로 결과 반환
• 사용 연산자: IN, ANY, SOME, ALL, EXISTS

-- IN 연산자 예시
SELECT 성명, 학년 FROM 데이터베이스 
WHERE 학번 IN (SELECT 인공지능.학번 FROM 인공지능);

-- EXISTS 연산자 예시
SELECT 성명, 학년 FROM 데이터베이스 
WHERE EXISTS (SELECT * FROM 인공지능 
              WHERE 데이터베이스.학번 = 인공지능.학번);

정렬과 그룹화

정렬 (ORDER BY)

-- 단일 정렬
SELECT * FROM 성적 ORDER BY 점수 DESC;

-- 다중 정렬
SELECT * FROM 성적 ORDER BY 성별 ASC, 성적 DESC;

그룹화 (GROUP BY)

-- 기본 그룹화
SELECT 성별, COUNT(*) FROM 성적 GROUP BY 성별;

-- HAVING 절 사용
SELECT 성별, COUNT(*) FROM 성적 
GROUP BY 성별 HAVING COUNT(*)<3;

⭐그룹 함수⭐

• ROLLUP(): 첫 칼럼의 각 그룹 합계와 전체 합계
• CUBE(): 모든 칼럼의 각 그룹 합계와 전체 합계
• GROUPING SETS(): 각 그룹별 총 합계만 표시
• GROUPING(): 집계 함수 지원

조인 (JOIN)

INNER JOIN

SELECT * FROM <왼쪽 테이블> 
JOIN <오른쪽 테이블> 
ON <왼쪽 테이블>.<컬럼명> = <오른쪽 테이블>.<컬럼명>;

OUTER JOIN

SELECT * FROM <왼쪽 테이블> 
OUTER JOIN <오른쪽 테이블> 
ON <왼쪽 테이블>.<컬럼명> = <오른쪽 테이블>.<컬럼명>;

LEFT/RIGHT JOIN

-- LEFT JOIN
SELECT * FROM <왼쪽 테이블> 
LEFT JOIN <오른쪽 테이블> 
ON <왼쪽 테이블>.<컬럼명> = <오른쪽 테이블>.<컬럼명>;

-- RIGHT JOIN
SELECT * FROM <왼쪽 테이블> 
RIGHT JOIN <오른쪽 테이블> 
ON <왼쪽 테이블>.<컬럼명> = <오른쪽 테이블>.<컬럼명>;

데이터 제어어 (DCL)

권한 제어

GRANT

GRANT <권한 유형> TO <대상>
    [WITH GRANT OPTION | WITH ADMIN OPTION];

• WITH GRANT OPTION: 권한 부여/회수 가능
• WITH ADMIN OPTION: 권한 부여만 가능

REVOKE

REVOKE <권한 유형> FROM <대상>;

ROLE

• 사용자 그룹 관리
• RBAC(Role Based Access Control) 방식 사용

트랜잭션 제어

트랜잭션 특징

• 논리적 연산들(DML)의 최소 단위
• 특징: 원자성, 일관성, 고립성, 지속성

트랜잭션 상태

• 활동(Active)
• 완료(Committed)
• 실패(Failed)
• 철회(Aborted)
• 실행 취소(Undo)
• 다시 실행(Redo)

제어 명령어

• COMMIT: 변경사항 최종 반영
• ROLLBACK: 이전 상황으로 복구
• SAVEPOINT: ROLLBACK 지점 지정

절차형 SQL

기본 구성요소

• DECLARE: 정의 영역
• BEGIN~END: 실제 구현 영역
• OR REPLACE: 기존 코드 덮어쓰기

프로시저 (Procedure)

• 호출을 통해 실행
• 반환값 없음
• 구성: CONTROL, EXCEPTION, SQL, TRANSACTION

사용자 정의 함수

• 호출을 통해 실행
• 반환값 존재
• 구성: CONTROL, EXCEPTION, SQL, RETURN

⭐트리거 (Trigger)⭐

• 이벤트 발생 시 자동 호출
• 입출력/반환값 없음

구성요소

• EVENT: 실행 조건 (필수)
• CONTROL: 순차/분기/반복 처리
• EXCEPTION: 예외 처리
• SQL: 주로 DML 사용

수행 시점

• AFTER: 테이블 변경 후 실행
• BEFORE: 테이블 변경 전 실행
• FOR EACH NOW: 레코드마다 트리거 수행
• NEW: 새로운 데이터
• OLD: 기존 데이터

인덱스와 뷰

인덱스

• 빠른 검색을 위한 자료구조
• 기본키 설정 시 자동 생성
• 수정이 잦을수록 비효율적

구현

-- 생성
CREATE [UNIQUE] INDEX <인덱스명> 
ON <테이블명>(<컬럼명>[, ...]);

-- 변경
ALTER [UNIQUE] INDEX <인덱스명> 
ON <테이블명>(<컬럼명>[, ...]);

-- 조회
SHOW INDEX FROM <테이블명>;

뷰 (View)

• 논리적 가상 테이블
• 시스템 카탈로그에 저장
• 종속 테이블 제거 시 함께 제거

장점

• 논리적 독립성 유지
• 접근 방법 단순화
• 데이터 보안 유지

단점

• 인덱스 사용 불가
• 수정/변경 불가

구현

-- 생성
CREATE VIEW <뷰 이름>(<컬럼 목록>) 
AS SELECT문 [옵션];

-- 삭제
DROP VIEW <뷰 이름>;

-- 조회
SELECT * FROM <뷰 이름>;

주요 옵션

• REPLACE: 재생성
• FORCE: 무조건 생성
• NOFORCE: 원본 테이블 존재 시에만 생성
• WITH CHECK OPTION: 조건 컬럼 수정 불가
• WITH READ ONLY: 전체 수정 불가

시스템 카탈로그

특징

• DBMS가 자동 생성
• 조회만 가능 (직접 변경 불가)
• DDL 실행 시 자동 갱신

SQL 지원도구

• PL/SQL: 확장 SQL도구
• SQL*Plus: Oracle 제공 도구
• APM: 성능 모니터링
• TKPROF: SQL 추적/분석
• EXPLAIN PLAN: SQL 실행경로 분석

병행제어

문제점

• 분실된 갱신
• 모순성
• 연쇄 복귀
• 비완료 의존성

로킹 (Locking)

• 다른 트랜잭션 접근 제한
• 로크 단위가 클수록:
  • 병행성 낮음
  • 오버헤드 감소
• 로크 단위가 작을수록:
  • 병행성 높음
  • 오버헤드 증가

로킹 기법

• 타임 스탬프: 직렬 처리
• 낙관적 병행 제어: 종료 시 일괄 검사
• 다중 버전 병행 제어: 타임 스탬프 기반 버전 선택

회복

• 장애 발생 이전 상태로 복원
• 연산자: Undo, Redo

회복 기법

• 로그 이용:
  • 즉시 갱신: 즉시 반영 + Undo
  • 지연 갱신: 완료 시 반영 + Redo
• 검사 시점:
  • CheckPoint 이전: Redo
  • CheckPoint 이후: Undo
• 그림자 페이징: 복사본 기반 회복