[실기] 제 8과목 - SQL 응용 요점 정리

SQL - Structured Query Language, 비절차적 언어(원하는 것 명시 O, 처리방법 명시 X), 대화식 SQL, 내포된 SQL

 

DQL - Data Query Language, 검색하는데 사용하는 질의어 SELECT

 

DML - Data Manipulation Language, 저장된 데이터를 수정, 삭제, 추가하는 명령어, INSERT, UPDATE, DELETE

 

DDL - Data Definition Language, 객체를 생성, 수정, 삭제하는 명령어, 스키마 정의, 스키마에 대한 명세는 시스템 카탈로그에 저장, CREATE, ALTER, DROP

 

DCL - Data Control Language, 규정이나 기법 정의, 제어하는 언어, 사용자 권한 부여, 취소, 트랜잭션 제어, GRANT, REVOKE, COMMIT, ROLLBACK

 

SELECT - SELECT [DISTINCT] 컬럼명 FROM 테이블명 WHERE 조건 GROUP BY 컬럼명 HAVING 조건 ORDER BY 컬럼명 [ASC | DESC];

   - * : 모든 데이터 검색

   - DISTINCT : 중복 제거

   - like % : 없거나 하나 이상의 어떤 문자

   - _ : 하나의 어떤 문자

   - AND, OR, NOT

   - BETWEEN ~ AND ~ : ~사이 범위 검색

   - A IN (B) : A 컬럼 값이 B에 포함된 경우 출력

   - IS NULL : NULL 값인 컬럼 검색

   - IS NOT NULL : 컬럼 값이 NULL이 아닌 것 검색

   - ASC : 오름차순, 기본값, 생략 가능

   - DESC : 내림차순

 

INSERT - INSERT INTO 테이블명 (컬럼명..) VALUES (값..);

                INSERT INTO 테이블명 (컬럼명..) SELECT 절; -> 새 레코드를 한번에 여러 개 입력할 때, SELECT 절의 컬럼 개수, 타입 = INSERT INTO 컬럼 개수 타입

 

DELETE - DELETE FROM 테이블명 WHERE 조건; (WHERE 조건 생략 시 모든 튜플 삭제)

 

UPDATE - UPDATE 테이블명 SET 컬럼명=값/식 WHERE 조건; (WHERE 조건 생략 시 모든 튜플 수정)

 

CREATE - CREATE TABLE 테이블명(  ); (IDENTIFIED BY - 비밀번호 부여)

 

ALTER - ALTER TABLE 테이블명 (   );

   - ADD 컬럼명 데이터타입

   - RENAME COLUMN 이전 이름 TO 새 이름

   - MODIFY (컬럼명 데이터타입)

   - UNUSED COLUMN : 컬럼 사용 안함

   - CONSTRAINT : 제약 조건

 

DROP - DROP TABLE 테이블명 [RESTRICT | CASCADE];

   - RESTRICT : 해당 테이블을 참조하는 뷰나 제약조건이 있으면 삭제 안함

   - CASCADE : 해당 테이블을 참조하는 뷰나 제약조건이 있어도 테이블이 삭제되고 종속된 제약조건을 삭제함

 

TRUNCATE - TRUNCATE TABLE 테이블명; (모든 데이터 삭제, ROLLBACK 불가능)

 

MERGE - MERGE INTO 테이블명A USING 테이블명B ON (조인 조건) WHEN MATCHED THEN ~ WHEN NOT MATCHED THEN ~ ; (대상 테이블을 조인해 입력, 수정, 삭제를 한번에 할 수 있음)

 

GRANT - GRANT 권한 [컬럼리스트] ON 객체 TO {사용자, 역할, PUBLIC} [WITH GRANT OPTION];

   - 권한 : SELECT, INSERT, DELETE, UPDATE, REGERENCES(참조권한)

   - WITH GRANT OPTION : 부여받은 권한 다른 사용자에게 허가 가능

 

REVOKE - REVOKE {권한리스트, ALL} ON 객체 FROM {사용자, 역할, PUBLIC};

   - 권한을 허가했던 사람이 그 권한 취소 가능

   - 권한 취소 시, 취소당한 사용자가 WITH GRANT OPTION을 통해 다른 사용자에게 허가했던 권한도 연쇄적으로 취소

 

역할 - Role, 여러 사용자의 권한 관리 단순화를 위해 사용, 그룹, 여러 역할에 속하고, 여러 사용자가 동일한 역할을 가질 수 있음

   - TABLE, INDEX

   - PUBLIC (모든 사용자)

   - WITH ADMIN OPTION : 시스템 권한 허가, 다른 사용자에게 허가 가능, 연쇄적 취소 X

 

COMMIT - INSERT, UPDATE, DELETE 문장 사용 후 변경 작업이 완료되었음을 DB에 알려주기 위해 사용

 

ROLLBACK - INSERT, UPDATE, DELETE 문장을 사용한 데이터에 대해 COMMIT 이전에 변경 사항을 취소할 경우 사용

 

SAVEPOINT - 회복 시 재수행할 트랜잭션의 수를 줄이기 위해 주기적으로 체크포인트 수행, 현재 지정된 SAVEPOINT 이후 갱신된 내용만 되돌림

   - SAVEPOINT A;

   - ROLLBACK TO SAVEPOINT A;

 

트랜잭션 - 한번에 수행되어야 하는 데이터베이스 연산들의 논리적인 모임, 데이터베이스 동시성 제어와 회복을 위한 기본 개념

   - 트랜잭션 완료 (Commit) : 트랜잭션이 성공적으로 종료됨을 의미, 트랜잭션에서 변경하려는 내용이 DB에 완전히 반영되어야 함

   - 트랜잭션 철회 (Abort) : 트랜잭션이 비성공적으로 종료됨을 의미, 변경하려는 내용이 일부만 반영될 경우, 원자성을 보장하기 위해 수행 전 상태로 되돌림

   - 동시성 제어 (Concurrency Contorl) : 다수의 사용자가 DB에 동시 접근할 수 있도록 허용하며 일관성을 유지하는 DBMS 기능

   - 회복 (Recovery) : DB를 갱신하는 도중, 시스템이 고장나도 DB의 일관성을 유지하는 DBMS 기능, 로그를 이용한 회복, 로그 먼저 쓰기

 

집계 함수 - SELECT [DISTINCT] 그룹핑컬럼, 집계함수 FROM 테이블명 [WHERE 조건] GROUP BY 그룹핑컬럼 [HAVING 그룹제한조건] [ORDER BY 정렬대상컬럼 [ASC | DESC]];

   - COUNT() 개수, SUM() 합, AVG() 평균, MAX() 최대, MIN() 최소, STDDEV() 표준편차, VARIANCE() 분산

   - COUNT(*)  제외 다른 함수 : NULL 값 제거 후 계산

   - COUNT(*) : 모든 행 개수

 

그룹 함수 - SELECT [DISTINCT] 그룹핑컬럼, 집계함수 FROM 테이블명 [WHERE 조건] GROUP BY 그룹핑컬럼 그룹함수 (소계계산컬럼) [HAVING 그룹제한조건] [ORDER BY 정렬대상컬럼 [ASC | DESC]];

   - ROLLUP() 중간 집계값 산출, CUBE() 다차원 집계 생성, GROUPING SET() 집계 대상 컬럼의 개별 집계 구하기, 순서 무관

 

윈도우 함수 - SELECT 윈도우함수(ARGUMENTS) OVER ([PARTITION BY 컬럼] [ORDER BY 절] [WINDOWING 절]) FROM 테이블명;

   - 행 간의 관계를 쉽게 정의하기 위한 함수, 분석, 순위 함수

   - PARTITION BY 컬럼 : 소그룹으로 나누기 위한 기준 컬럼

   - WINDOWING 절 : 함수의 대상이 되는 행 기준의 범위를 ROWS, RANGE로 지정

   - 순위 관련 함수 : RANK() 순위, 동일 값은 동일 순위, DENSE_RANK() 동일 순위는 하나로 취급, ROW_NUMBER() 동일 값도 고유 순위 부여

   - 행 순서 관련 함수 : FIRST_VALUES() 파티션별 윈도우의 첫 값=MIN(), LAST_VALUES 파티션별 윈도우의 마지막 값=MAX(), LAG() 이전 몇 번째 행, LEAD() 이후 몇 번째 행

   - 비율 관련 함수 : PERCENT_RANK() 파티션별 윈도우 첫 값 = 0, 마지막 값 = 1, 그 사이 백분율, CUME_DIST() 전체 건수의 현재 행보다 작거나 같은 건수의 누적 백분율, NTILE() 전체 건수를 ARGUMENT 값으로 N등분 한 결과, RATIO_TO_REPORT() 전체 SUM(컬럼)값에 대한 행별 컬럼 값의 백분율을 소수점으로 표현

 

절차형 SQL - SQL문의 연속적인 실행, 조건에 따른 분기, 반복 등의 제어를 활용해 다양한 기능을 수행하는 DB 저장 모듈

   - DECLARE : 프로시저, 함수 정의

   - BEGIN : 프로시저, 함수가 실행되는 시작점

   - END : 프로시저, 함수가 실해되는 종료점

   - SELECT INTO, INSERT INTO, UPDATE SET, DELETE FROM

   - 조건 제어 : IF-THEN, ELSIF-THEN, ELSE, END IF;

   - 반복 제어 : LOOP-EXIT, END LOOP; For index in [REVERSE] 시작값..끝값 LOOP, END LOOP; WHILE condition LOOP, END LOOP;

   - 순차 제어 : GOTO (제어가 건너뛰는 곳을 지정하는 레이블과 사용), NULL (실행하지 않음을 의미)

 

커서 - SQL 처리 결과가 저장된 작업 영역에 이름을 지정하고 저장된 정보를 접근할 수 있게 하는 용도, SQL 명령을 실행시키면 서버는 명령을 parse, 메모리 영역을 open, 이 영역을 cursor라고 함

   - 명시적 커서 (Explict), 묵시적 커서 (Implict)

   - DECLARE, OPEN, FETCH, EMPTY, CLOSE

 

프로시저 - 매개 변수를 받을 수 있고 반복해서 사용할 수 있거나, 구현이 복잡한 트랜잭션을 수행하는 SQL 블록을 DB에 저장하기 위해 생성

   - DECLARE, BEGIN/END, CONTROL, SQL, EXCEPTION, TRANSACTION

   - CREATE OR REPLACE, PARAMETER, MODE(IN, OUT, INOUT)

 

트리거 - DB에 특정한 변경이 가해졌을 때, DBMS가 이에 대응해 자동적으로 호출하는 일종의 프로시저

   - EVENT (트리거를 가동하는 사건), CONDITION (수행 여부를 결정하는 검사 조건), ACTION (가동될 때 수행하는 작업)

   - CREATE OR REPLCAE TRIGGER 이름, BEFORE/AFTER 트리거 이벤트 내용(INSERT, UPDATE, DELETE)