정보처리기사 - 제3과목 데이터베이스 구축 / SQL 응용, 활용

 제3과목 SQL 응용 

▷ SQL 응용 A, B

> SQL의 개념

- SQL (Structured Query Language)의 개요 : 1974, IBM에서 제작, 관계형 데이터베이스 언어, 관계대수+관계해석

- SQL의 분류

  DDL(Data Define Language, 데이터 정의어) : DB관리자, DB설계자가 주로 사용, 스키마, 도메인, 테이블, 뷰, 인덱스, 정의(CREATE), 변경(ALTER), 삭제(DROP)

출처) 주간컴공

  DML(Data Manipulation Language, 데이터 조작어) : 검색(SELECT FROM), 삽입(INSERT INTO), 삭제(DELETE FROM), 수정(UPDATE SET) + WHERE 조건

  DML-JOIN : INNER JOIN, OUTER JOIN, SELF JOIN

  DCL (Data Control Language, 데이터 제어어) 데이터보안(COMMIT-정상, ROLLBACK-복구, GRANT-TO 권한부여(DBA, RESOURCE, CONNECT) + WITH GRANT OPTION, REVOKE-FROM 권한취소 + GRANT OPTION FOR), 회복, SAVEPOINT(복귀지점 설정)

 

▷ SQL 응용 A, B

절차형 SQL : 분기, 반복이 가능한 모듈화(재사용가능)된 프로그램, DBMS에서 직접 실행, 프로시저, 트리거, 사용자정의함수 등

> 프로시저(Stored Procedure)

- 프로시저의 개요 : SQL활용, 특정기능 수행을 위한 호출을 하기위해 미리저장된 SQL작업, 주로 DML을 사용하여 주기적으로 진행해야하는 작업 진행(ex. 마감작업, 일괄작업 등)

주간컴공

  DECLARE : 명칭, 변수, 인수, 데이터 타입 정의

  BEGIN : CONTROL(조건, 반복), SQL(DML, DCL 삽입), EXCEPTION(예외발상), TRANSACTION(적용, 취소 결정)

  END 

- 프로시저 생성

  CREAT (OR REPLACE : 동일한, 기존 프로시저 대체) PROCEDURE 이름(파라미터)

     파라미터 : IN(값전달), OUT(값반환), INOUT(전달,반환), 매개변수명, 자료형

  BEGIN

    프로시저 BODY;

  END

- 프로시저 실행

   EXECUTE(EXEC, CALL) 프로시저 명;

- 프로시저 제거

   DROP PROCEDURE 이름

 

> 트리거(Trigger)

- 트리거(Trigger)의 개요 : 이벤트가 발생되면 해당된 작업이 자동으로 수행되는 SQL, DB저장, DCL 사용x, (ex. 무결성 유지, 로그작성시 사용), 자동으로 실행되므로 리터값, 매개변수가 없고 COMMIT, ROLLBACK이 없음

- 트리거의 구성

주간컴공

  DECLARE

  EVENT : 선언

  BEGIN : 실행되는 조건

    CONTROL : 조건문, 반복문

    SQL : DML

    EXCEPTION : 예외발생

  END

- 트리거의 생성

  CREATE [OR REPLACE] TRIGGER 트리거명 [동작시기 옵션: AFTER, BEFORE], [동작옵션: INSERT, DELETE, UPDATE] ON

  REFERENCING [NEW, OLD] AS

  FOR EACH ROW

  [WHEN 조건]

  BEGIN

    트리거 BODY;

  END

- 트리거의 제거

  DROP TRIGGER 트리거명;

 

> 사용자 정의함수

- 사용자 정의함수의 개요 : 프로시저와 유사, SQL과 유사, 호출에 의해 실행, DML문에서 실행, RETURN반환, 종료시 단일값 반환(리턴)

- 사용자 정의함수의 구성 : 조작x, SELECT조회, 프로시저 호출X, DML에서 반환값 활용 

주간컴공

  DECLARE : 명칭

  BEGIN

    CONTROL : 조건문, 반복문

    SQL : SELECT

    EXCEPTION : 예외상황

    RETURN

  END

- 사용자 정의함수 생성

  CREATE [OR REPLACE] FUNCTION 사용자 정의함수명(파라미터 : IN, 매개변수명, 자료형)

  BEGIN

    사용자 정의함수 BODY;

    TETURN 반환값;

  END;

- 사용자 정의함수 실행 : SELECT 함수명 FROM 테이블 / INSERT INTO 테이블(속성명) VALUES 사용자 정의함수 명 / DELETE FROM 테이블 WHERE 속성명(사용자 정의함수) / UPDATE 테이블명 SET 속성명(사용자 정의함수 명)

- 사용자 정의함수 제거

  DROP FUNCTION 사용자 정의함수명

 

> DBMS 접속 기술

- DBMS 접속의 개요 : 데이터를 사용하기 위해 사용자가 DBMS에 접근하는 것

- DBMS 접속 기술 : JDBS(Java DB Connect, DB접속 SQL 수행 표준 API), 1997 MS 출시, JDBS(java, sql), ODBC 1992, 개발언어와 무관, MyBatis(SQL 매핑기반)

- 동적 SQL(Dynamic SQL)

 

> SQL 테스트

- SQL 테스트의 개요 : SQL의작성의도에 맞게 작성되었는지 검증, 구문오류, 참조오류 확인, 디버깅 로직 검증, 결과 확인

- 단문 SQL 테스트 : DDL, DML,DCL포함, SQL, TCL 테스트(COMMIT, ROLLBACK, SAVEPOINT), DESCRIBE(DDL로 작성된 테이블, 뷰 확인가능)

- 절차형 SQL 테스트 : SHOW로 오류 확인

 

> ORM(Object-Relational Mapping)

- ORM(Object-Relational Mapping)의 개요 : 객체지향 프로그램의 객체와 관계형 DB의 데이터를 연결, 가상의 객체지향 데이터베이스-> 프로그래밍의 코드와 데이터를 연결, 독립적, 재사용가능, 유지보수 용이, 데이터 조작용이(선언문 및 할당 코드 생략)

- ORM 프레임워크 : JAVA(Hibernate, EclipseLink), C++(ODB), Python(Django), IOS(DatabaseObjects), .NET(Nhibernate), php(Doctrine)

- ORM의 한계 : SQL 자동작성되므로 확인필요, DB가 아닌 경우 크고 복잡할수록 어려움, 변환시 시간과 노력 소요

 

> 쿼리 성능 최적화

- 쿼리 성능 최적화의 개요 : APM(성능측정도구, 접속자, 자원현황 등 제공도구)로 최적화할 쿼리 선정하여 옵티마이저가 수립한 실행계획 검토

- 실행계획(Execution Plan) : EXPLAIN 명령어, 연산순서

- 쿼리 성능 최적화 : SQL 코드, 인덱스 재구성