정보처리기사DB - 3.SQL

 

1. DDL

; Schema, Domain, Table, View, Index의 정의/변경/제거 하는 언어

; 정의 내용은 Metadata가 되며, System Catalog에 저장된다.

 

1.1 CREATE ; Schema, Domain, Table, View, Index를 정의한다.

1.1.1 CREATE SCHEMA

=> CREATE SCHEMA 스키마명 AUTORIZATION 사용자_ID;

EX)CREATE SCHEMA 대학교 AUTORIZATION 홍길동;

 

1.1.2 CREATE DOMAIN

; 정의된 도메인 명은 일반 데이터 타입처럼 사용.

=> CREATE DOMAIN 도메인명 데이터타입

[DEFAULT 기본값]

[CONSTRAINT 제약조건명 CHECK (범위값)];

 

EX) CREATE DOMAIN SEX CHAR(1)

DEFAULT '남'

CONSTRAINT VALID-SEX CHECK (VALUE IN('남', '여'));

 

1.1.3 CREATE TABLE

=> CREATE TABLE 테이블명

(속성명 데이터-타입[NOT NULL], .....

[,PRIMARY KEY(기본키_속성명,....)]

[,UNIQUE(대체키_속성명,....)]

[,FOREIGN KEY(외래키_속성명,....) REFERNECES 참조테이블(기본키_속성명)]

[ON DELETE 옵션]

[ON UPDATE 옵션]

[,CONSTRAINT 제약조건명][CHECK (조건식)]);

 

EX)학생테이블 만들기

CREATE TABLE 학생

(이름 VARCHAR(15) NOT NULL,

학번 CHAR(8),

전공 CHAR(5),

성별 SEX,

생년월일 DATE,

PRIMARY KEY(학번),

FOREIGN KEY(전공) REFERENCES 학과(학과코드)

ON DELETE SET NULL

ON UPDATE CASCADE,

CONSTRAINT 생년월일제약 CHECK(생년월일 >='1980-01-01'));

 

 

 

1.1.4 CREATE VIEW

; VIEW = VIRTUAL TABLE

=> CREATE VIEW 뷰명(속성명[,속성명,...])]

AS SELECT문;

-> SELECT문을 서브쿼리로 사용하여 SELECT의 결과로 뷰 생성.

-> 서브쿼리에는 UNION, ORDER BY 사용 불가.

-> 속성명이 없으면 SELECT의 속성이 자동 적용

 

EX)

CREATE VIEW 안산고객(성명,전화번호)

AS SELECT 성명, 전화번호

FROM 고객

WHERE 주소 = '안산시';

 

 

 

1.1.5 CREATE INDEX ;빠른 검색을 위해 만든 보조 데이터구조

=> CREATE [UNIQUE] INDEX <인덱스명>

ON 테이블 명({속성명 [ASC | DESC] [,속성명 [ASC | DESC]]})

[CLUSTER];

-> UNIQUE ; 중복 없는 속성으로 인덱스 생성

-> CLUSTER ; 지정 키에 따라, 튜플을 그룹으로 지정.

 

EX)CREATE UNIQUE INDEX 고객번호_idx

ON 고객(고객번호 DESC);

 

1.1.6 CREATE TRIGGER

; DB에 이벤트 발생 마다 자동적으로 수행하는 사용자 정의 프로시저.

-> 무결성 제약조건 구현, 관련테이블 데이터 일치를 위해 사용.

 

=> CREATE TRIGGER 트리거명 [동작시기 옵션][동작옵션] ON 테이블명

REFERENCING [NEW | OLD] TABLE AS 테이블명

FOR EACH ROW

WHEN 조건식

트리거 BODY

 

- 동작시기 옵션 : 트리거 실행 타이밍 지정

AFTER : 테이블 변경 후 실행

BEFORE: 테이블 변경 전 실행

 

- 동작 옵션 : 트리거 실행 작업 지정

INSERT : 테이블에 새로운 레코트 삽입시 실행

DELETE : 테이블의 레코드가 삭제될 때 실행

UPDATE : 테이블의 레코드가 수정될 때 실행

 

- 테이블 선택 옵션 : 트리거 적용 테이블 종류 지정

NEW : (새로 추가/ 변경에 참여할) 튜플들의 집합에 적용.

OLD : (변경된) 튜플들의 집합에 적용.

 

- WHEN : 트리거 자체에 걸리는 조건

- BODY : BEGIN~END 구문, 1개 이상의 SQL 포함 필수. 변수 값 치환시 예약어'SET' 사용.

 

EX) 삽입시, 학년정보가 누락되면, 신입생을 치환하는 트리거 정의.

 

CREATE TRIGGER 학년정보_tri BEFORE INSERT ON 학생

REFERENCING NEW TABLE AS new_table

FOR EACH ROW

WHEN new_table.학년 = ' '

BEGIN

SET new_table.학년 = '신입생';

END;

 

 

1.2 ALTER ; 테이블 정의를 변경한다.

- ALTER TABLE

- ADD : 새로운 속성 추가

- ALTER : 속성의 기본값 변경

- DROP COLUMN : 속성 제거

ALTER TABLE 테이블명 ADD 속성명 데이터타입 [DEFAULT '기본값']

ALTER TABLE 테이블명 ALTER 속성명 [SET DEFAULT '기본값']

ALTER TABLE 테이블명 DROP COLUMN 속성명 [CASCADE];

 

EX) ALTER TABLE 학생 ADD 학년 VARCHAR(3)

1.3 DROP ; Schema, Domain, Table, View, Index,TRIGGER 제거

- DROP [CASCADE | RESTRICT];

- CASCADE : 참조하는 모든 다른 개체도 같이 제거

- RESTRICT : 다른 개체가 참조중이면 제거를 취소.

EX) DROP TABLE 학생 CASCADE;

 

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 

2. SELECT

SELECT [PREDICATE] [테이블명.]속성명[AS 별칭][테이블명.]속성명,...]

FROM 테이블명[, 테이블명,...]

[WHERE 조건]

[GROUP BY 속성명[,속성명, ...]]

[HAVING 조건]

[ORDER BY 속성명 [ASC | DESC][, 속성명 [ASC | DESC], ...]];

2.1 PREDICATE ; 검색 튜플 제한

- ALL : 기본값, 모든 튜플 검색

- DISTINCT : 중복된 튜플 제거(선택 속성에서)

- DISTINCTROW : 중복 튜플 제거(튜플 전체 대상)

2.2 FROM : 검색 대상 테이블명 입력

2.3 WHERE : 검색 조건 입력

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

- 그룹함수

COUNT(속성명) ; 그룹별 튜플 수를 구함

MAX(속성명) ; 그룹별 최대값을 구함

MIN(속성명) ; 그룹별 최소값을 구함

SUM(속성명) ; 그룹별 합계를 구함

AVG(속성명) ; 그룹별 평균을 구함

 

2.5 HAVING : 그룹에 대한 조건 기술

2.6 ORDER BY : 특정 속성 기준으로 정렬 설정 [ASC | DESC ]

 

2.7 기타

TRIM : 공백제거 -> LTrim(왼쪽공백) RTrim(오른쪽 공백)

LIKE : 지정 속성 값이 문자 패턴과 일치하는지 체크

* OR % : 모든 문자

? OR _ : 문자 하나

# : 숫자 하나.

EX) LIKE '김%' : 첫번째 글자가 '김'

 

2.8 SUBQUERY

- 조건절에 QUERY가 있는 것.

- 조건절 QUERY를 선수행 -> 조건에 대입.

 

2.9 복수 테이블 검색

EX) SELECT 사원.이름, 사원.부서, 여가활동.취미, 여가활동.경력

FROM 사원, 여가활동

 

2.10 UNION

EX) 임원과 직원 테이블을 통합하는 SQL(중복은 제거된다)

SELECT * FROM 임원 UNION SELECT * FROM 직원

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 

3. JOIN ; 일반적으로 FROM에 기술. 연결고리로 연결하는 것.

 

3.1 INNER JOIN

-> EQUI JOIN ; JOIN조건에 '='사용, 같은 값을 가지는 행 연결.

=> NATURAL JOIN : 중복 속성을 제거하여 한번만 표기하는 방법

-> NON - EQUI JOIN ; JOIN조건에 '='를 제외한 나머지 비교연산자 사용.

 

 

3.2 OUTER JOIN ; JOIN조건을 만족하지 않는 튜플도 출력.

-> LEFT OUTER JOIN ;

INNER JOIN 후

우측 릴레이션에 안맞는 좌측 릴레이션 튜플에

NULL을 붙여서 결과에 출력.

 

-> RIGHT OUTER JOIN

INNER JOIN 후

좌측 릴레이션에 안맞는 우측 릴레이션 튜플에

NULL을 붙여서 결과에 출력.

 

-> FULL OUTER JOIN : LEFT + OUTER

INNER JOIN 후

LEFT 실행하고 RIGHT 실행한다.

 

3.3 SELF JOIN ; 같은 테이블에서 2개의 속성을 연결하여 '=' JOIN

=> 같은 테이블을 2개 그려서 생각하면 된다.

EX) SELECT A.학번, A.이름, B.이름 AS 선배

FROM 학생 A ,학생 B

WHERE A.선배 = B.학번;

 

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 

4. DML ; 저장된 데이터를 관리하는 데 사용하는 언어.

4.1 INSERT ; 테이블에 새로운 튜플 삽입

=> INSERT INTO 테이블명 [(속성명1, 속성명2, ....)]

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

=> 모든속성 삽입시, 속성명 생략 가능.

EX)

INSERT INTO 편집부원(이름,생일,주소,기본급)

SELECT 이름,생일,주소,기본급 FROM 사원 WHERE 부서 = '편집';

 

4.2 DELETE ; 특정 튜플 삭제

=> DELETE FROM 테이블명 WHERE 조건;

-> 모든 튜플 삭제시 WHERE절 생략.

 

4.3 UPDATE ; 특정 튜플 내용 갱신.

=> UPDATE 테이블명

SET 속성명 = 데이터[, 속성명 = 데이터, ...]

WHERE 조건;

EX)

UPDATE 사원 SET 주소 = '퇴계동' WHERE 이름 ='홍길동';

 

UPDATE 사원 SET 부서 ='기획', 기본급 = 기본급+5

WHERE 이름 ='황진이';

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 

5. DCL; DBA가 보안, 무결성, 회복, 병행제어 정의에 사용

 

5.1 COMMIT ; DB조작작업을 영구적으로 반영, 완료

- 트랜잭션의 모든 변경사항을 DB에 반영.

- 트랜잭션이 성공적으로 끝나면 일관성(consistency)을 위해 모든 변경 을 DB에 commit 해야함

5.2 ROLLBACK ; DB조작 작업이 바정상 종료시 원래상태로 복구

- 모든 변경사항을 취소하고 DB를 이전 상태로 복구

- 트랜잭션 일부가 실패하면 비일관성(Inconsistency)를 가질 수 있으므 로 Rollback 해야함.

===> Q) 이전상태가 뭐지? commit 단위 인가?

===> 회복, 병행제어시 처리되는 작업의 논리적인 단위가 트랜잭션.

===> 하나의 트랜잭션은 commit or rollback의 상태를 가진다.

 

5.3 GRANT; 사용자에게 권한 부여

=> GRANT 사용자등급 TO 사용자_ID_리스트[IDENTIFIED BY 암호];

 

5.4 REVOKE; 사용자의 권한 취소

=> REVOKE 사용자등급 FROM 사용자_ID_리스트;

 

사용자 등급 : RESOURCE(DB및 테이블 생성 가능)

DBA(데이터베이스 관리자)

CONNECT(단순 사용자)

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------

6. VIEW

; 필요한 데이터만 접근할 수 있도록 하여, 이외의 데이터를 안전하게 하는 가상 테이블.

- 뷰를 통해 테이블에 접근한다.

- 테이블 --------> 뷰 ----------> (검색) ---------->정보

- 관련 제약성이 있는데 시험에 나올지 모르겠다.

 

6.1 CREATE VIEW(DCL에서도 함)

6.2 DROP VIEW(DCL에서도 함)

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 

7. 내장 SQL

;호스트 프로그램 언어에 삽입한 SQL. 응용프로그램 실행시 함께 실행 될 수 있다.

7.1 특징

- 호스트 언어에 실행문 어느곳이나 사용 가능.

- 결과 튜플이 여러개여도 맨처음 튜플만 반환.

- 선행처리기에 의해 호스트언어와 분리되어 컴파일 됨.

- 호스트 변수와 데이터필드 이름 같아도 됨.

- 실행되면 SQL상태변수에 실행상태가 전달.

7.2 호스트 언어와 실행문 구별

명령문 구분

- C/C++ : 'EXEC SQL' ~ ';' 사이에 기술

- JAVA : #SQL{ <내장 SQL문> };

변수 구분.

내장 SQL의 호스트 변수는 앞에 ':'를 붙인다.

 

7.3 CURSOR : 튜플에 대한 포인터.

- 내장SQL결과에서 여러개의 튜플에 접근 할 수 있게 만든 것.

- DECLARE : 커서 정의, 커서 관련 선언

- OPEN : 첫번째 튜플 가르키기

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

- CLOSE : 질의 결과 처리 종료시에, 커서 닫기.

 

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 

8. 저장프로시저(Stored Procedure)

- SQL문을 그룹화 하여 특정 작업을 수행하도록 만든것.

- 프로그램의 축소판.

=> SQL문을 모아 SQL서버에 미리 컴파일하여 저장해 놓음.

=> 호출문만 보내면 사용 가능하다.

 

8.1 구성

- 선언부 : 타입, 커서, 상수, 변수, 서브 프로그램 선언

- 실행부 : 코드 실행 제어, 데이터 조작

- 예외처리부 : 실행중 발생한 예외 처리

 

8.2 장점

- 모듈별 프로그래밍

- 빠른 SQL 실행시간 => 만들어질 때 최적화 됨. 메모리 캐시 됨.

- 보안성 향상 : 데이터 접근에 단계가 추가되기 때문 생김.

- 통신량 감소 : 명령문만 네트웤으로 보내면 됨.

 

- CREATE PROCEDURE

=> CREATE [OR REPLACE] PROCEDURE 프로시저명(파라미터)

[지역변수 선언]

프로시저 BODY;

 

- OR REPLACE : 동일 프로시저 이름 존재시, 기존 프로시저 대체 가능.

- 파라미터

-> IN : 호출 프로시저에 값을 전달

-> OUT : 호출 프로그램에게 반환값 전달

-> INOUT : 값을 전달하고 실행후 값 반환

- BODY

BEGIN ~ END문. 최소 1개의 SQL문 필요, 변수 값 치환시 "SET" 사용.

 

- DROP PROCEDURE : 제거시 사용, 제거하면 되돌릴 수 없다.

- EXECUTE PROCEDURE

- EXEC PROCEDURE

 

 

출처 : 시나공 2018 정보처리기사 실기(산업기사 포함), 한기준 외 3인, 길벗. 2018