정보처리기사 실기 8장 - SQL 응용

102. SQL - DDL (A)

1. DDL (Data Define Language, 데이터 정의어)

• DB를 구축하거나 수정
• CREATE, ALTER, DROP

2. CREATE SCHEMA

표기 형식

CREATE SHCEMA 스키마명 AUTHORIZATION 사용자_ID;

 

소유권자의 ID가 '홍길동'인 스키마 '대학교'를 정의

CREATE SCHEMA 대학교 AUTHORIZATION 홍길동;

 

3. CREATE DOMAIN

표기 형식

 

CREATE DOMAIN 도메인명 [AS] 데이터_타입
    [DEFAULT 기본값]
    [CONSTRAINT 제약조건명 CHECK (범위값)];

 

'성별'을 '남', '여'와 같이 정해진 1개의 문자로 표현되는 도메인 SEX를 정의

CREATE DOMAIN SEX CHAR(1)
    DEFAULT '남'
    CONSTRAINT VALID-SEX CHECK(VALUE IN ('남', '여'));

 

4. CREATE TABLE

표기 형식

CREATE TABLE 테이블명
    (속성명 데이터_타입 [DEFAULT 기본값] [NOT NULL] ...
    [, PRIMARY KEY(기본키_속성명, ...)]
    [, UNIQUE(대체키_속성명, ...)]
    [, FOREIGN KEY(외래키_속성명, ....)]
        REFERENCES 참조테이블(기본키_속성명, ...)]
        [ON DELETE 옵션]
        [ON UPDATE 옵션]
    [, CONSTRAINT 제약조건명] [CHECK (조건식)];

 

FOREIGN KEY ~ REFERENCES ~ : 외래키로 사용할 속성 지정

 - ON DELETE 옵션 : 참조 테이블의 튜플이 삭제되었을 때 기본 테이블이 취해야 할 사항 지정

 - ON UPDATE 옵션 : 참조 테이블의 참조 속성 값이 변경되었을 때 기본 테이블이 취해야할 사항 지정

 

5. CREATE VIEW

표기 형식

CREATE VIEW 뷰명[(속성명[, 속성명,, ...])]
AS SELECT 문;

 

<고객> 테이블에서 '주소'가 '안산시'인 고객들의 '성명'과 '전화번호'를 '안산고객'이라는 뷰로 정의

CREATE VIEW 안산고객(성명, 전화번호)
AS SELECT 성명, 전화번호
FROM 고객
WHERE 주소 = '안산시';

 

 

6. CREATE INDEX

표기 형식

CREATE [UNIQUE] INDEX 인덱스명
ON 테이블명(속성명 [ASC | DESC] [, 속성명 [ASC | DESC]])
[CLUSTER];

 

UNIQUE

 - 사용된 경우 : 중복 값이 없는 속성으로 인덱스 생성

 - 생략된 경우 : 중복 값을 허용하는 속성으로 인덱스 생성

 

정렬 여부 지정

 - ASC : 오름차순 (생략시 기본)

 - DESC : 내림차순

 

CLUSTER : 사용하면 인덱스가 클러스터드 인덱스로 설정됨

 

<고객> 테이블에서 UNIQUE한 특성을 갖는 '고객번호' 속성에 대해 내림차순으로 정렬하여 '고객번호_idx' 라는 이름으로 인덱스를 정의

CREATE UNIQUE INDEX 고객번호_idx
ON 고객(고객번호 DESC);

 

7. ALTER TABLE

표기 형식

ALTER TABLE 테이블명 ADD 속성명 데이터_타입 [DEFAULT '기본값'];
ALTER TABLE 테이블명 ALTER 속성명 [SET DEFAULT '기본값'];
ALTER TABLE 테이블명 DROP COLUMN 속성명 [CASCADE];

 

8. DROP

표기 형식

DROP SCHEMA 스키마명 [CASCASE | RESTRICT];
DROP DOMAIN 도메인명 [CASCASE | RESTRICT];
DROP TABLE 테이블명 [CASCASE | RESTRICT];
DROP VIEW 뷰명 [CASCASE | RESTRICT];
DROP INDEX 인덱스명 [CASCASE | RESTRICT];
DROP CONSTRAINT 제약조건명;

 

CASCADE : 제거할 요소를 참조하는 모든 개체를 함께 제거

RESTRICT : 다른 개체가 제거할 요소를 참조중일 때는 제거를 취소

---

103. SQL - DCL (A)

1. DCL(Data Control Language, 데이터 제어어)

• 데이터의 보안, 무결성, 회복, 병행 제어 등을 정의하는데 사용
• COMMIT, ROLLBACK, GRANT, REVOKE

2. GRANT / REVOKE

• GRANT : 권한 부여
• REVOKE : 권한 취소

 

사용자 등급 지정 및 해제

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

 

사용자 등급

• DBA : 관리자
• RESOURCE : DB 및 테이블 생성가능자
• CONNECT : 단순 사용자

 

테이블 및 속성 권한 부여 및 취소

 GRANT 권한_리스트 ON 개체 TO 사용자 [WITH GRANT OPTION];
REVOKE [GRANT OPTION FOR] 권한_리스트 ON 개체 FROM 사용자 [CASCADE];

 

권한 종류 : ALL, SELECT, INSERT, DELETE, UPDATE 등

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

GRANT OPTION FOR : 다른 사용자에게 권한을 부여할 수 잇는 권한을 취소

CASCADE : 권한 취소시 권한을 부여받았던 사용자가 다른 사용자에게 부여한 권한도 연쇄적으로 취소

 

3. COMMIT

• 트랜잭션이 수행한 내용을 DB에 반영하는 명령
• Auto Commit 가능

4. ROLLBACK

• 변경되었으나 아직 COMMIT되지 않은 모든 내용들을 취소하고 이전 상태로 돌림

5. SAVEPOINT

• ROLLBACK할 위치인 저장점을 지정
• 저장점을 지정할 때는 이름을 부여

SAVEPOINT S1;
SAVEPOINT S2;
ROLLBACK TO S2;
ROLLBACK TO S1;

위와 같은 방식으로 사용

---

104. SQL - DML(A)

1. DML (Data Manipulation Language, 데이터 조작어)

• 저장된 데이터를 실질적으로 관리하는데 사용되는 언더
• SELECT, INSERT, DELETE, UPDATE

2. 삽입문 (INSERT INTO ~)

일반 형식

INSERT INTO 테이블명 ([속성명1, 속성명2, ...])
VALUES (데이터1, 데이터2, ...);

 

3. 삭제문 (DELETE FROM ~)

일반 형식

DELETE
FROM 테이블명
[WHERE 조건];

 

4. 갱신문 (UPDATE ~ SET~)

일반 형식

UPDATE 테이블명
SET 속성명 = 데이터[, 속성명=데이터, ...]
[WHERE 조건];

---

105. DML - SELECT-1 (A)

SELECT절

 - PREDICATE : 검색할 튜플 수를 제한하는 명령어

 > DISTINCT : 중복된 튜플이 있으면 그 중 첫번째 한 개만 표시

 

LIKE 연산자

% - 모든 문자

_ - 문자 하나

# - 숫자 하나

 

* SELECT * FROM 사원 WHERE 생일 BETWEEN #01/01/69# AND #12/31/73#

> <사원>테이블에서 '생일'이 '01/01/69' 에서 '12/31/73' 사이인 튜플

 

SELECT 이름, 기본급, 주소 FROM 사원 WHERE 기본급 < ALL (SELECT 기본급 FROM 사원 WHERE 주소 = '망원동');

> "망원동"에 거주하는 사원들의 '기본급'보다 적은 '기본급'을 받는 사원의 정보

 

ALL() : 하위 질의로 검색된 범위를 기본 질의의 조건으로 사용

즉 <사원> 테이블에서 "망원동"인 사원의기본급을 모두 추출한 후 추출된 기본급들을 기본 질의의 조건으로 사용

---

106. DML - SELECT-2 (A)

• 그룹함수 : GROUP BY 절에 지정된 그룹별로 속성의 값을 집계할 함수
• WINDOW 함수 : GROUP BY절을 이용하지않고 속성의 값을 집계할 함수
  • PARTITION BY : WINDOW함수의 적용 범위가 될 속성
  • ORDER BY : PARTITION 안에서 정렬 기준으로 사용할 속성
• GROUP BY : 특정 속성을 기준으로 그룹화 하여 검색
• HAVING절 : GROUP BY와 함께 사용,  그룹에대한 조건 지정

2. 그룹 함수

• COUNT(속성명) : 그룹별 튜플 수
• SUM(속성명) : 그룹별 합계
• AVG(속성명) : 그룹별 평균
• MAX(속성명) : 그룹별 최대값
• MIN(속성명)
• STDDEV(속성명) : 그룹별 표준편차
• VARIANCE(속성명) : 그룹별 분산
• ROLLUP(속성명, 속성명, ...) : 인수로 주어진 속성을 대상으로 그룹별 소계를 구함, 속성의 개수가 n개면, n+1레벨까지, 하위레벨에서 상위레벨순으로 데이터 집계
• CUBE(속성명, 속성명, ...) : ROLLUP과 유사하지만, CUBE는 인수로 주어진 속성을 대상으로 모든 조합의 그룹별 소계를구함, 속성의 개수가 n개이면 2ⁿ 레벨까지, 상위레벨에서 서하위레벨 순으로 데이터 집계

3. WINDOW 함수

• GROUP BY절을 사용하지 않고 함수의 인수로 지정한 속성의 값 집계
• ROW_NUMBER() : 윈도우별로 각 레코드에 대한 일련번호를 반환
• RANK() : 윈도우별로 순위를 반환, 공동 순위 반영함
• DENSE_RANK() : 윈도우별 순위 반환, 공동 순위 무시하고 순위 부여

4. 집합 연산자

SELECT * FROM 테이블명
UNION | UNION ALL | INTERSECT | EXCEPT
SELECT * FROM 테이블명

 

• UNION
  • 두 SELECT 문의 조회 결과를 통합하여 출력
  • 중복된 행은 한 번만 출력
  • 합집합
• UNION ALL
  • UNION에서 중복된 행도 그대로 출력
  • 합집합
• INTERSECT
  • 두 SELECT문의 공통행만 출력
  • 교집합
• EXCEPT
  • 첫번째 SELECT문의 조회 결과에서 두번째SELECT문의 조회결과를 제외한 행 출력
  • 차집합

---

107. DML - JOIN (B)

2. INNER JOIN

INNER JOIN - EQUI / NON-EQUI JOIN 구분

EQUI JOIN : =으로 비교

NON-EQUI JOIN : =을 제외한 연산자, (>, <, >=, <=, <>)

 

NATURAL JOIN  - 중복 속성을 제거하여 같은 속성을 한 번만 표기 

 - 조인할 속성을 지정하지 않음, 두 테이블에는 반드시 같은 속성(컬럼)이 있어야함

 

FROM 테이블1 JOIN 테이블2 USING(속성명)

 - 해당 속성명으로 동일한 값을 찾음

 

INNER JOIN = CROSS JOIN

 - 테이블의 행의 수 = 두 테이블의 행의 수의 곱

 3. OUTER JOIN

LEFT OUTER JOIN : 좌측 릴레이션 기준, 좌측은 모두 표시 (우측항과 맞지 않는 튜플은 NULL), 우측은 관련이 있는 것만

RIGHT OUTER JOIN : 우측 릴레이션 기준, 우측은 모두 표시 (좌측항과 맞지 않는 튜플은 NULL) , 좌측은 관련이 있는 것만

 

FULL OUTER JOIN : LEFT와 RIGHT를 합친 것. 좌측항의 튜플들에 대해 우측 항의 튜플과 맞지 않는 튜플들에 NULL을 붙여서 INNER JOIN의 결과에 추가. 우측도 좌측과 맞지 않는 튜플에 NULL을 붙여 INNER JOIN에 추가.

 

SELECT * FROM 테이블1 LEFT OUTER JOIN 테이블2
ON 테이블1.속성명 = 테이블2.속성명

SELECT * FROM 테이블1, 테이블2
WHERE 테이블1.속성명 = 테이블2.속성명(+)

위의 두개는 같은 결과를 출력한다.

 

on : join 전에 조건을 필터링
where : join 후에 조건을 필터링