정보처리기사 실기 대비_파트8

02 Jul 2023

정보처리기사 실기 대비

8. SQL 응용

Section118 SQL - DDL

DDL(Data Define Language, 데이터 정의어)
1. DB구조, 데이터 형식, 접근 방식 등 DB 구축하거나 수정할 목적으로 사용
2. 번역 결과가 데이터 사전이라는 특별한 파일에 여러 개의 테이블로 저장
3. 3가지 유형
  1. CREATE : SCHEMA, DOMAIN, TABLE, VIEW, INDEX를 정의함
  2. ALTER : TABLE에 대한 정의를 변경하는 데 사용
  3. DROP : SCHEMA, DOMAIN, TABLE, VIEW, INDEX를 삭제함
CREATE SCHEMA
1. 스키마 정의 멍령문

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

CREATE DOMAIN
1. 도메인 정의 명령문

CREATE DOMAIN 도메인명 [AS] 데이터_타입
  [DEFAULT 기본값]
  [CONSTRAINT 제약조건명 CHECK(범위값)];
-- 데이터 타입 : SQL에서 지원하는 데이터 타입
-- 기본값 : 데이터를 입력하지 않았을 때 자동으로 입력되는 값

CREATE TABLE
1. 테이블 정의 명령문

CREATE TABLE 테이블명
  (속성명 데이터_타입 [DEFAULT 기본값] [NOT NULL],
  [, PRIMARY KEY(기본키, 속성명)]
  [, UNIQUE(대체키_속성명, ...)]
  [, FOREIGN KEY(외래키_속성명)]
    REFERENCES 참조테이블(기본키_속성명, ...)
    [ON DELETE 옵션]
    [ON UPDATE 옵션]
  [, CONSTRAINT 제약조건명] [CHECK (조건식)]);
-- PRIMARY KEY : 기본키로 사용할 속성 지정
-- UNIQUE : 대체키로 사용할 속성 지정함, 중복된 값 가질 수 없음
-- FOREIGN KEY ~ REFERENCES : 외래키로 사용할 속성 지정함
  -- ON DELETE : 참조 테이블의 튜플이 삭제되었을 때 기본 테이블에 취해야 할 사항 지정
  -- ON UPDATE : 참조 테이블의 참조 속성값이 변경되었을 때 기본 테이블에 취해야 할 사항을 지정함
-- CONSTRAINT : 제약 조건의 이름을 지정함
-- CHECK : 속성 값에 대한 제약 조건을 정의함

CREATE VIEW
1. 뷰를 정의하는 명령문

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

CREATE INDEX
1. 인덱스를 정의하는 명령문

CREATE [UNIQUE] INDEX 인덱스명
ON 테이블명(속성명 [ASC | DESC] [,속성명 [ASC | DESC]]) --오름차순, 내림차순(생략 시 오름차순)
[CLUSTER]; --사용하면 인덱스가 클러스터드 인덱스로 설정됨

ALTER TABLE
1. 테이블에 대한 정의를 변경하는 명령문

ALTER TABLE 테이블명 ADD 속성명데이터_타입 [DEFAULT '기본값']
ALTER TABLE 테이블명 ALTER 속성명 [SET DEFAULT '기본값'];
ALTER TABLE 테이블명 DROP COLUMN 속성명 [CASCADE];
-- ADD : 새로운 속성(열) 추가할 때 사용
-- ALTER : 특정 속성의 DEFAULT 값 변경할 때 사용
-- DROP COLUMN 특정 속성 삭제할 때 사용

DROP
1. 스키마, 도메인, 기본 테이블, 뷰 테이블, 인덱스, 제약 조건 등을 제거하는 명령문

DROP SCHEMA 스키마명 [CASCADE | RESTRICT]
DROP DOMAIN 도메인명 [CASCADE | RESTRICT]
DROP TABLE 테이블명 [CASCADE | RESTRICT]
DROP VIEW 뷰명 [CASCADE | RESTRICT]
DROP INDEX 인덱스명 [CASCADE | RESTRICT]
--CASCADE : 제거할 요소를 참조하는 다른 모든 개체를 함께 제거
--RESCTRICT : 다른 개체를 제거할 요소를 참조 중일 때는 제거를 취소
DROP CONSTRAINT 제약조건명;

Section119 SQL - DCL

DCL(Data Control Language, 데이터 제어어)
1. 데이터 보안, 무결성, 회복, 병행 제어 등을 정의하는 데 사용
2. 데이터베이스 관리자가 데이터 관리를 목적으로 사용
3. 종류
  1. COMMIT : 명령에 의해 수행된 결과를 실제 물리적 디스크로 저장하고 작업이 정상적으로 완료되었음을 관리자에게 알려줌
  2. ROLLBACK : 데이터베이스 조작 작업이 비정상적으로 종료되었을 때 원래 상태로 복구함
  3. GRANT : 사용자에게 사용 권한 부여
  4. REVOKE : 사용자의 사용 권한 취소
GRANT / REVOKE

사용자 등급 지정 및 해제

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

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

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

권한 종류 : ALL, SELECT, INSERT, DELETE, UPDATE, ALTER
WITH GRANT OPTION : 부여받은 권한을 다른 사용자에게 다시 부여할 수 있는 권한 부여
GRANT OPTION FOR : 다른 사용자에게 권한을 부여할 수 권한을 취소함
CASCADE : 권한 취소 시 권한을 부여받았던 사용자가 다른 사용자에게 부여한 권한도 연쇄적으로 취소함

COMMIT

트랜잭션 처리가 정상적으로 완료된 후 트랜잭션이 수행한 내용을 데이터베이스에 반영하는 명령
COMMIT 명령을 실행하지 않아도 DML문이 성공적으로 완료되면 자동으로 COMMIT되고, DML이 실패하면 자동으로 ROLLBACK이 되도록 AUTO COMMIT 기능 설정 가능

ROLLBACK

변경되었으나 아직 커밋되지 않은 모든 내용들을 취소하고 데이터베이스를 이전 상태로 되돌리는 명령
트랜잭션 전체가 성공적으로 끝나지 못하면 일부 변경된 내용만 데이터베이스에 반영되는 비일관성 상태가 될 수 있기 때문에 일부분만 완료된 트랜잭션은 롤백되어야 함

SAVEPOINT

트랜잭션 내에 ROLLBACK 할 위치인 저장점을 지정하는 명령어
저장점을 지정할 때는 이름을 부여
ROLLBACK 할 때 지정된 저장점까지의 트랜잭션 처리 내용이 모두 취소됨

Section120 SQL - DML

DML(Data Manipulation Language, 데이터 조작어)
1. 데이터베이스 사용자가 저장된 데이터를 실질적으로 관리하는데 사용
2. 데이터베이스 사용자와 데이터베이스 관리 시스템 간의 인터페이스 제공
3. 유형
  1. SELECT : 테이블에서 튜플 검색
  2. INSERT : 테이블에 새로운 튜플 삽입
  3. DELETE : 테이블에서 튜플 삭제
  4. UPDATE : 테이블에서 튜플의 내용 갱신
삽입문
1. 기본 테이블에 새오운 튜플 삽입할 때 사용

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

대응하는 속성과 데이터는 개수와 데이터 유형이 일치해야 함
기본 테이블의 모든 속성 사용할 때는 속성명 생략 가능
SELECT문 사용하여 다른 테이블의 검색 결과 삽입할 수 있음

삭제문(DELETE FROM~)

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

갱신문(UPDATE~ SET~)

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

Section121 DML - SELECT-1

일반 형식

SELECT [PREDICATE] [테이블명,]속성명 [AS 별칭][, [테이블명,]속성명, ...]
[, 그룹함수(속성명) [AS 별칭]]
[, Window함수 OVER (PARTITION BY 속성명1, 속성명2, ...
                  ORDER BY 속성명3, 속성명4, ...)]
FROM 테이블명[, 테이블명, ...]
[WHERE 조건]
[GROUP BY 속성명, 속성명, ...]
[HAVING 조건]
[ORDER BY 속성명 [ASC | DESC]];

SELECT절
- PREDICATE: 검색할 튜플 수를 제한하는 명령어 기술
  - DISTINCT : 중복된 튜플이 있으면 첫 번째 한 개만 표시
- 속성명 : 검색하여 불러올 속성(열) 또는 속성을 이용한 수식을 지정함
- AS : 속성이나 연산의 이름을 다른 이름으로 표시하기 위해 사용함
FROM절 : 검색할 데이터가 들어있는 테이블 이름 기술
WHERE절 : 검색할 조건 기술
ORDER BY절 : 데이터를 정렬하여 검색할 때 사용
- 속성명 : 정렬의 기준이 되는 속성명 기술

조건 연산자
1. 비교 연산자
  1. = 같다
  2. <> 같지 않다
  3. 크다
  4. < 작다
  5. = 크거나 작다
  6. <= 작거나 같다
2. 논리 연산자
  1. NOT
  2. AND
  3. OR
3. LIKE 연산자 : 대표 문자를 이용해 지정된 속성의 값이 문자 패턴과 일치하는 튜플을 검색하기 위해 사용
  1. % 모든 문자를 대표
  2. _ 문자 하나를 대표함
  3. # 숫자 하나를 대표함
기본 검색

SELECT * FROM 사원;
SELECT 사원* FROM 사원;
SELECT DISTINCT 주소
FROM 사원

조건 지정 검색
1. WHERE절에 조건을 지정하여 조건에 만족하는 튜플만 검색

SELECT *
FROM 사원
WHERE 부서='기획';

SELECT *
FROM 사원
WHERE 이름 LIKE "김%";

SELECT *
FROM 사원
WHERE 주소 IS NULL;

정렬 검색
1. ORDER BY절에 특정 속성을 지정하여 지정된 속성으로 자료를 정렬하여 검색

SELECT TOP 2*
FROM 사원
ORDER BY 주소 DESC;

하위 질의
1. 하위 질의는 조건절에 주어진 질의를 먼저 수행하여 그 검색 결과를 조건절의 피연산자로 사용한다

SELECT 이름, 주소
FROM 사원
WHERE 이름 = (SELECT 이름 FROM 여가활동 WHERE 취미 = '나이트댄스');

SELECT *
FROM 사원
WHERE 이름 NOT IN (SELECT 이름 FROM 여가활동);

SELECT 부서
FROM 사원
WHERE EXISTS (SELECT 이름 FROM 여가활동 WHERE 여가활동.이름 = 사원.이름);

복수 테이블 검색
1. 여러 테이블 대상으로 검색 수행

SELECT 사원.이름, 사원.부서, 여가활동.취미, 여가활동.경력
FROM 사원, 여가활동
WHERE 여가활동.경력 >= 10 AND 사원.이름 = 여가활동.이름;

Section122 DML - SELECT-2

일반 형식

SELECT [PREDICATE] [테이블명,]속성명 [AS 별칭][, [테이블명,]속성명, ...]
[, 그룹함수(속성명) [AS 별칭]]
[, WINDOW함수 OVER (PARTITION BY  속성명1, 속성명2, ...
            ORDER BY 속성명3, 속성명4, ...) [AS 별칭]]
FROM 테이블명[, 테이블명, ...]
[WHERE 조건]
[GROUP BY 속성명, 속성명, ...]
[HAVING 조건]
[ORDER BY 속성명 [ASC | DESC]];

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

그룹 함수
1. COUNT(속성명) : 그룹별 튜플 수를 구하는 함수
2. SUM(속성명) : 그룹별 합계를 구하는 함수
3. AVG(속성명) : 그룹별 평균을 구하는 함수
4. MAX(속성명) : 그룹별 최대값을 구하는 함수
5. MIN(속성명) : 그룹별 최소값을 구하는 함수
6. STDDEV(속성명) : 그룹별 표준편차를 구하는 함수
7. VARIANCE(속성명) : 그룹별 분산을 구하는 함수
8. ROLLUP(속성명, 속성명, …) : 인수로 주어진 속성을 대상으로 그룹별 소계를 구하는 함수, 속성의 개수가 N개이면, N+1 레벨까지, 하위 레벨에서 상위 레벨 순으로 데이터가 집계됨
9. CUBE(속성명, 속성명, …) : ROLLUP과 유사한 형태지만 CUBE는 인수로 주어진 속성을 대상으로 모든 조합의 그룹별 소계를 구함, 속성의 개수가 N개이면, 2**N 레벨까지, 상위 레벨에서 하위 레벨 순으로 데이터가 집계됨
WINDOW 함수
1. GROUP BY절을 이용하지 않고 함수의 인수로 지정한 속성의 값을 집계
2. 함수의 인수로 지정한 속성이 집계할 범위가 되는데, 이를 윈도우라고 부른다
3. WINDOW 함수
  1. ROW_NUMBER(): 윈도우별로 각 레코드에 대한 일련번호를 반환한다
  2. RANK(): 윈도우별로 순위를 반환하며, 공동 순위를 반영한다
  3. DENSE_RANK(): 윈도우별로 순위를 반환하며, 공동 순위를 무시하고 순위를 부여
WINDOW 함수 이용 검색
1. GROUP BY절 이용하지 않고 함수의 인수로 지정한 속성을 범위로 하여 속성의 값을 집계

SELECT 상여내역, 상여금,
   ROW_NUMBER() OVER (PARTITION BY 상여내역 ORDER BY 상여금 DESC) AS NO
FROM 상여금;

그룹 지정 검색
1. GROUP BY절에 지정한 속성을 기준으로 자료를 그룹화하여 검색한다

SELECT 부서, AVG(상여금) AS 평균
FROM 상여금
GROUP BY 부서;

SELECT 부서, COUNT(*) AS 사원수
FROM 상여금
GROUP BY 부서;

SELECT 부서, COUNT(*) AS 사원수
FROM 상여금
WHERE 상여금 >= 100
GROUP BY 부서
HAVING COUNT(*) >= 2;

SELECT 부서, 상여내역, SUM(상여금) AS 상여금합계
FROM 상여금
GROUP BY ROLLUP(부서, 상여내역)

집합 연산자를 이용한 통합 질의
1. 집합 연산자를 사용해 2개 이상의 테이블의 데이터를 하나로 통합

SELECT 속성명1, 속성명2, ...
FROM 테이블명
UNION | UNION ALL | INTERSECT | EXCEPT
SELECT 속성명1, 속성명2, ...
FROM 테이블명
[ORDER BY 속성명 [ASC | DESC]];

두 개의 SELECT문에 기술한 속성들은 개수와 데이터 유형이 서로 동일해야 함
집합 연산자의 종류
- UNION : 두 SELECT문의 조회 결과를 통합하여 모두 출력, 중복은 한 번만 출력
- UNION ALL : 두 SELECT문의 조회 결과를 통합하여 모두 출력, 중복된 행도 그대로 출력
- INTERSECT : 두 SELECT문의 조회 결과 중 공통된 행만 출력 - 교집합
- EXCEPT : 첫 번째 SELECT문의 조회 결과에서 두 번째 SELECT문의 조회 결과를 제외한 행을 출력

SELECT *
FROM 사원
UNION
SELECT *
FROM 직원;

Section123 DML - JOIN

JOIN
1. 2개의 릴레이션에서 연관된 튜플들 결합하여, 하나의 새로운 릴레이션 반환
2. 일반적으로 FROM절에 기술하지만, 릴레이션이 사용되는 곳 어디에나 사용할 수 있음
INNER JOIN
1. EQUI JOIN, NON-EQUI JOIN으로 구분
2. 조건이 없는 INNER JOIN 수행하면 CROSS JOIN과 동일한 결과 얻음
3. EQUI JOIN
  1. JOIN 대상 테이블에서 공통 속성을 기준으로 ‘=’(equal) 비교에 의해 같은 값을 가지는 행을 연곃하여 결과를 생성하는 JOIN 방법
  2. EQUI JOIN에서 JOIN 조건이 ‘=’일 때 동일한 속성이 두 번 나타나게 됨. 이 중 중복된 속성을 제거하여 같은 속성을 한 번만 표기하는 방법을 NATURAL JOIN이라고 함
  3. EQUI JOIN에서 연결고리가 되는 공통 속성을 JOIN 속성이라고 함
  4. WHERE절을 이용한 EQUI JOIN의 표기 형식
```
SELECT [테이블명1.]속성명, [테이블명2.]속성명, ...
FROM 테이블명1, 테이블명2
WHERE 테이블명1.속성명 = 테이블명2.속성명;
```
  1. NATURAL JOIN절을 이용한 EQUI JOIN의 표기 형식
```
SELECT [테이블명1.]속성명, [테이블명2.]속성명, ...
FROM 테이블명1 NATURAL JOIN 테이블명2;
```
  1. JOIN ~ USING절을 이용한 EQUI JOIN의 표기 형식
```
SELECT [테이블명1.]속성명, [테이블명2.]속성명, ...
FROM 테이블명1 JOIN 테이블명2;
```
4. NON-EQUI JOIN
  1. JOIN 조건에 = 조건이 아닌 나머지 비교 연산자, 즉 >, <, <>, >=, <= 연산자를 사용하는 JOIN 방법
```
SELECT [테이블명1.]속성명, [테이블명2.]속성명, ...
FROM 테이블명1, 테이블명2, ...
WHERE (NON-EQUI JOIN 조건);
```

OUTER JOIN

릴레이션에서 JOIN 조건에 만족하지 않는 튜플도 결과로 출력하기 위한 JOIN 방법 : LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN

LEFT OUTER JOIN

INNER JOIN의 결과를 구한 후, 우측 항 릴레이션의 어떤 튜플과도 맞지 않는 좌측 항의 릴레이션에 있는 튜플들에 NULL 값을 붙여서 INNER JOIN 결과 추가 2.

SELECT [테이블명1.]속성명, [테이블명2.]속성명
FROM 테이블명1 LEFT OUTER JOIN 테이블명2
ON 테이블명1.속성명 = 테이블명2.속성명;

SELECT [테이블명1.]속성명, [테이블명2.]속성명
FROM 테이블명1, 테이블명2
ON 테이블명1.속성명 = 테이블명2.속성명(+);

RIGHT OUTER JOIN

INNER JOIN의 결과를 구한 후, 좌측 항 릴레이션의 어떤 튜플과도 맞지 않는 우측 항의 릴레이션에 있는 튜플들에 NULL 값을 붙여서 INNER JOIN 결과에 추가 2.

SELECT [테이블명1.]속성명, [테이블명2.]속성명
FROM 테이블명1 RIGHT OUTER JOIN 테이블명2
ON 테이블명1.속성명 = 테이블명2.속성명;

SELECT [테이블명1.]속성명, [테이블명2.]속성명
FROM 테이블명1, 테이블명2
ON 테이블명1.속성명(+) = 테이블명2.속성명;

FULL OUTER JOIN

LEFT OUTER JOIN, RIGHT OUTER JOIN을 합쳐 놓은 것
INNER JOIN 결과 구한 후, 좌측 항의 릴레이션

SELECT [테이블명1.]속성명, [테이블명2.]속성명
FROM 테이블명1 FULL OUTER JOIN 테이블명2
ON 테이블명1.속성명 = 테이블명2.속성명;

rustandbone Developer

정보처리기사 실기 대비_파트8

정보처리기사 실기 대비

8. SQL 응용

Section118 SQL - DDL

Section119 SQL - DCL

Section120 SQL - DML

Section121 DML - SELECT-1

Section122 DML - SELECT-2

Section123 DML - JOIN

Related posts

모던 JS deep dive 2회차 챕터 44 REST API 29 Mar 2024

모던 JS deep dive 2회차 챕터 43 Ajax 28 Mar 2024

모던 JS deep dive 2회차 챕터 42 비동기 프로그래밍 27 Mar 2024