[정보처리기사 실기] 데이터베이스 - SQL,View, 내장 SQL, Stored Procedure

[정보처리기사 실기] 데이터베이스

SQL

DDL(Data Definition Language)

- 스키마, 도메인, 테이블, 뷰, 인덱스, 트리거 등을 정의, 변경, 삭제하는 명령어

- 정의 내용은 Metadata가 되고 시스템 카탈로그에 저장됨

1. CREATE SCHEMA

- 스키마 정의

- CREATE SCHEMA schema_name AUTHORIZATION user_ID

2. CREATE DOMAIN

- 도메인 정의

- 정의된 도메인은 일반적인 데이터 타입처럼 사용

- CREATE DOMAIN domain_name data_type

  DEFAULT default

  CONSTRAINT 제약조건 CHECK(범위 값)

- 범위 값 : VALID IN('A','B', ...) , 이 값에 속한 값들만 선택 가능

3. CREATE TABLE

- 테이블 정의

- CREATE TABLE table_name (

  속성 data_type NOT NULL ...,

  PRIMARY KEY(속성) or 속성 data_type PRIMARY KEY,

  UNIQUE 속성

  FOREIGN KEY 속성 REFERENCES 참조테이블(속성)

  ON DELETE 옵션

  ON UPDATE 옵션

  CONSTRAINT 제약 조건 CHECK(조건)

- 옵션

-> NO ACTION : 참조 테이블 변화에도 기본 테이블은 변경되지 않음

-> CASCADE : 참조 테이블 튜플 삭제/변경 시 기본 테이블 관련 튜플 삭제/변경

-> SET NULL : 참조 테이블 변경 시 기본 테이블 관련 튜플 NULL로 변경

-> SET DEFAULT : 참조 테이블 변경 시 기본 테이블 관련 튜플 DEFAULT값으로 변경

4. CREATE VIEW

- 하나 이상의 기본 테이블로부터 만드는 가상 테이블

- CREATE VIEW view_name(속성1, 속성2 ...)

  AS SELECT문

- 속성을 따로 설정하지 않으면 SELECT에서 가져오는 속성들의 이름으로 구성

- SELECT문에서 order by, union 사용 불가

5. CREATE INDEX

- 검색 속도를 향상시키기 위한 구조

- CREATE (UNIQUE) INDEX index_name

  ON table_name(속성 ASC/DESC)

  (CLUESTER)

- UNIQUE 사용 시 중복 값이 없는 속성으로 INDEX 생성

- ASC : 오름차순, DESC : 내림차순, DEFAULT : ASC

- CLUSTER : 지정된 키에 따라 튜플들을 그룹으로 지정

6. CREATE TRIGGER

- 입력, 갱신, 삭제 등의 이벤트 발생 시 자동으로 실행되는 사용자 정의 프로시져

- CREATE TRIGGER trigger_name [AFTER/BEFORE] [INSERT/DELETE/UPDATE]

  REFERENCING [OLD/NEW] TABLE AS table_name

  FOR EACH ROW

  WHEN 조건

  BEGIN

  내용

  END

- NEW : 새로 추가/변경, OLD : 변경

7. ALTER TABLE

- ALTER TABLE table_name ADD 속성 data_type (DEFAULT 'default') : 속성 추가

- ALTER TABLE table_name ALTER 속성 data_type SET DEFAULT 'default' : 속성 default 변경

- ALTER TABLE table_name DROP 속성 data_type (CASCADE) : 속성 삭제

8. DROP

- 제거를 위한 명령문

- DROP [SCHEMA/DOMAIN/TABLE/VIEW/INDEX/TRIGGER] name (CASCADE/RESTRAINT)

- DROP CONSTRAINT name

- CASCADE : 제거할 개체를 참조하는 다른 모든 개체를 함께 삭제(참조 무결성 보장)

- RESTRAINT : 제거할 개체가 참조되어 있으면 삭제 취소

DML(Data Manipulation Language)

- 응용 프로그램이나 질의어를 통해 저장된 데이터를 관리하는 데 사용되는 언어

1. SELECT

- 튜플을 검색해서 임시 테이블로 구성하는 명령어

- SELECT (ALL/DISTINCT/DISTINCTROW) 속성 (AS 별칭)

  FROM 테이블

  WHERE 조건

  GROUP BY 속성

  HAVING 조건

  ORDER BY 속성 (asc,desc)

- ALL : 모든 튜플(default)

- DISTINCT : 중복 제거

- DISTINCTROW : 중복 제거, 선택된 속성의 값이 아닌 튜플의 전체 값을 대상으로 할때 사용

- AS : 다른 이름으로 속성을 표시

- GROUP BY : 특정 속성을 기준으로 그룹화하여 검색

- HAVING : 그룹에 대한 조건

- ORDER BY : 특정 속성을 기준으로 정렬(ASC : 오름차순, DESC : 내림차순, DEFAULT : ASC)

- 그룹 함수

-> COUNT(속성) : 튜플 수

-> MAX(속성) : 그룹 별 최대값

-> MIN(속성) : 그룹 별 최소값

-> SUM(속성) : 그룹 별 합계

-> AVG(속성) : 그룹 별 평균

- UNION : SELECT ~~ UNION SELECT ~~ (합쳐서 출력, 중복 제거)

2. JOIN

- 2개의 테이블에 연관된 튜플들을 결합해 하나의 새로운 릴레이션 생성

1) INNER JOIN

① EQUI-JOIN

- 공통 속성의 값이 같은 행을 연결 -> 중복 제거 : NATURAL JOIN

- SELECT 속성 FROM 테이블1, 테이블2

  WHERE 테이블1.속성 = 테이블2.속성

- SELECT 속성 FROM 테이블1

  NATURAL JOIN 테이블2

- SELECT 속성 FROM 테이블1

  JOIN 테이블2 USING(속성)

② INNER JOIN(NON-EQUI JOIN)

- '='가 아닌 나머지 비교연산을 이용한 JOIN

2) OUTER JOIN

- JOIN 조건에 만족하지 않는 튜플도 결과로 출력

① LEFT OUTER JOIN

- INNER JOIN 후 우측 항 튜플과 맞지 않는 좌측 항의 튜플들에 NULL 값 추가(좌측 모두 출력)

- SELECT 속성 ... 

  FROM 테이블1 LEFT OUTER JOIN 테이블2

  ON 테이블1.속성 = 테이블2.속성

- SELECT 속성 ... 

  FROM 테이블1, 테이블2

  WHERE 테이블1.속성 = 테이블2.속성(+)

② RIGHT OUTER JOIN

- INNER JOIN 후 좌측 항 튜플과 맞지 않는 우측 항의 튜플들에 NULL 값 추가(우측 모두 출력)

- SELECT 속성 ... 

  FROM 테이블1 RIGHT OUTER JOIN 테이블2

  ON 테이블1.속성 = 테이블2.속성

- SELECT 속성 ... 

  FROM 테이블1, 테이블2

  WHERE 테이블1.속성(+) = 테이블2.속성

③ FULL OUTER JOIN

- LEFT, RIGHT OUTER JOIN을 합침

- 좌측 항의 ㅌ플에 우측 항의 튜플과 맞지 않는 값에 NULL 값 추가하고 반대로도 추가

- SELECT 속성 ...

  FROM 테이블1 FULL OUTER JOIN 테이블2

  ON 테이블1.속성 = 테이블2.속성

④ SELF JOIN

- 같은 테이블에서 2개의 속성을 연결해 EQUI JOIN

- SELECT 속성 ...

  FROM 테이블1 AS 별칭1 JOIN 테이블1 AS 별칭2

  ON 별칭1.속성 = 별칭2.속성

3. INSERT

- 테이블에 새로운 튜플 삽입

- INSERT INTO 테이블(속성1, 속성2 ...)

  VALUES (데이터1, 데이터2 ...)

- VALUES 대신 SELECT 문을 이용해 얻어온 결과를 삽입 가능

4. DELETE

- 특정 튜플 삭제

- DELETE FROM 테이블 WHERE 조건

5. UPDATE

- 특정 튜플 갱신

- UPDATE 테이블

  SET 속성 = 데이터

  WHERE 조건

DCL(Data Control Language)

- 보안, 무결성, 회복, 병행 제어 등을 정의

- DBA가 데이터 관리를 목적으로 사용

1. COMMIT

- 트랜잭션의 모든 변경 내용을 영구적으로 DB에 반영

- 일관성을 유지하기 위해 트랜잭션 완료 후 commit

2. ROLLBACK

- 트랜잭션의 모든 변경 내용을 취소하고 이전 상태로 되돌림

- 트랜잭션의 일부가 성공하지 못하면 비일관성이 생길 수 있으므로 rollback

3. GRANT
- 권한 부여를 위해 사용

- GRANT 사용자등급 TO 유저

- GRANT 권한 ON 개체 TO 유저(WITH GRANT OPTION)

- 사용자 등급

-> DBA : 데이터베이스 관리자

-> RESOURCE : 데이터베이스 및 테이블 생성 가능자

-> CONNECT : 일반 사용자

- 권한 : INSERT, ALL, SELECT, DELETE 등

- WITH GRANT OPTION : 부여받은 권한을 다른 사용자에게 다시 부여할 수 있는 권한

4. REVOKE

- 권한 취소를 위해 사용

- REVOKE 사용자등급 FROM 유저

- REVOKE (GRANT OPTION FOR) 권한 ON 개체 FROM 유저(CASCADE)

- GRANT OPTION FOR : 다른 사용자에게 권한을 부여할 수 있는 권한 삭제

- CASCADE : 권한 취소 시 이 사용자가 다른 사용자에게 부여한 권한도 모두 취소

뷰(View)

- 하나 이상의 기본 테이블에서 유도되는 가상 테이블

특징

- 기본 테이블과 같은 형태, 조작도 유사

- 물리적 구현 X

- 뷰에 나타나지 않는 데이터를 안전하게 보호할 수 있음

- 기본키를 포함해야 삽입, 삭제, 갱신 가능

- 정의된 뷰는 다른 뷰 정의에 기초가 될 수 있음

제약조건

- 뷰의 열이 상수, 계산식, 그룹합수로 만들어지면 뷰를 변경할 수 없음

- DISTINCT, GROUP BY, HAVING을 사용해 만들어진 뷰는 뷰를 변경할 수 없음

- 둘 이상 테이블에서 유도된 뷰는 변경할 수 없음

1. CREATE VIEW

- 뷰를 정의하는 명령

- CREATE VIEW 뷰(속성 ...)

  AS SELECT문

  (WITH CHECK OPTION)

- SELECT문에는 ORDER BY, UNION 사용 불가

- WITH CHECK OPTION : 뷰에 대한 갱신, 삽입 연산 시 뷰의 정의 조건을 위배하면 실행 거부

- 속성 명시 X -> SELECT 결과의 속성으로 대체

2. DROP VIEW

- 뷰를 제거하는 명령

- DROP VIEW 뷰 (RESTRICT/CASCADE)

- RESTRICT : 뷰를 다른 곳에서 참조하고 있으면 명령 취소

- CASCADE : 뷰를 참조하는 다른 뷰, 제약조건까지 모두 삭제

내장 SQL

- SQL 문을 응용 프로그램에 내포해 프로그램이 실행될 때 함께 실행되도록 호스트 프로그램 언어에 삽입한 SQL

- C/C++에서는 EXEC SQL과 세미콜론 사이에 기술

- EXEC SQL BEGIN DECLARE SECTION;

      변수 선언

  EXEC SQL END DECLARE SECTION;

- EXEC SQL 쿼리문

커서(Cursor)

- 내장 SQL 실행 결과로 반환된 여러 개의 튜플을 접근하기 위해 사용

- DECLARE : 커서 정의

- OPEN : 질의 결과의 첫번째 튜플을 커서가 가리키게 함

- FETCH : 다음 튜플로 커서 이동

- CLOSE : 커서 종료

- CURRENT OF : 현재 커서를 사용하기 위해 사용

- EXEC SQL DECLARE 커서 CURSOR FOR 쿼리문

- EXEC SQL OPEN 커서

- EXEC SQL FETCH 커서 (INTO 변수) : 변수에 저장하고 다음 튜플로 이동

- EXEC SQL CLOSE 커서

스토어드 프로시져(Stored Procedure)

- SQL 서버에 SQL문을 미리 컴파일 해놓고 저장, 필요할 때마다 가져다 쓸 수 있음

장점

- 모듈별 프로그래밍 허용

- 빠른 SQL 실행 시간

- 보안성 향상

- 네트워크 통신량 감소

- CREATE (OR REPLACE) PROCEDURE 프로시저(IN/OUT/INOUT 파라미터)

  변수 선언

  BEGIN

   내용

  END

- OR REPLACE : 이미 동일한 이름의 프로시져가 있으면 기존의 프로시져 대체

- IN : 스토어드 프로시져에 값을 전달

- OUT : 호출 프로그램에 값을 반환

- INOUT : 값을 전달하고, 값을 반환받음

- DROP PROCEDURE 프로시져 : 제거

- EXEC(EXECUTE) 프로시져 : 실행