728x90

SQL 개념

SQL의 개요

- 국제 표준 데이터베이스 언어이며 많은 관계형 데이터베이스(RDB)를 지원하는 언어로 채택

 

SQL의 분류

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

    -> 스키마, 도메인, 테이블, 뷰, 인덱스를 정의, 변경, 삭제할 때 사용하는 언어

    -> CREATE : 스키마, 도메인, 테이블, 뷰, 인덱스를 정의

    -> ALTER : 테이블에 대한 정의를 변경

    -> DROP : 스키마, 도메인, 테이블, 뷰, 인덱스를 삭제

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

    -> 사용자가 저장된 데이터를 실질적으로 처리하는 데 사용

    -> SELECT : 테이블에서 조건에 맞는 튜플 검색

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

    -> DELETE : 테이블에서 조건에 맞는 튜플 삭제

    -> UPDATE : 테이블에서 조건에 맞는 튜플의 내용 변경

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

    -> 데이터의 보안, 무결성, 회복, 병행 수행 제어 등을 정의하는 데 사용하는 언어

    -> COMMIT : 명령에 의해 수행된 결과를 실제 물리적 디스크로 저장하고 데이터베이스 조작 작업이 정상적으로 완료되었음을 알려줌

    -> ROLLBACK : 데이터베이스 조작 작업이 비정상적으로 종료되었을 때 원래의 상태로 복구

    -> GRANT : 데이터베이스 사용자에게 사용 권한을 부여

 

DDL

DDL의 개념

- DDL(데이터 정의어)는 DB 구조, 데이터 형식, 접근 방식 등 DB를 구축하거나 수정할 목적으로 사용하는 언어

- DDL의 종류에는 CREATE, ALTER, DROP이 있음

 

CREATE

- CREATE SCHEM

    -> 스키마(데이터베이스 구조와 제약 조건에 관한 전반적인 명세를 기술한 것)를 정의

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

     -> 소유권자의 사용자 ID가 '김이박'이고 스키마 '성적'을 정의하는 SQL문

CREATE SCHEMA 성적 AUTHORIZATION 김이박;

- CREATE DOMAIN

     -> 도메인을 정의

     -> 대괄호는 생략 가능하다는 의미

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

     -> 성별을 '남' 또는 '여'와 같이 정해진 1개 문자로 표현되는 도메인 GENDER를 정의하는 SQL문

CREATE DOMAIN [AS] CHAR(1)
	DEFAULT '남'
    CONSTRAINT VALID-GENDER CHECK(VALUE IN('남','여');

- CREATE TABLE

     -> 테이블을 정의

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

     -> '이름', '학번', '전공', '성별', '생년월일'로 구성된 학생 테이블을 정의하는 SQL문

     -> 제약조건

         * '이름'은 NULL 일 수 없음

         * '학번'은 기본키

         * '전공'은 학과 테이블의 '학과 코드'를 참조하는 외래키로 사용

         * 학과 테이블에서 삭제가 일어나면 관련된 튜플들의 전공 값을 NULL로 만듦

         * 학과 테이블에서 '학과 코드'가 변경되면 전공 값도 같은 값으로 변경

         * '생년월일'은 1980-01-01 이후의 데이터

         * 제약 조건의 이름은 '생년월일 제약'

         * 각 속성의 데이터 타입은 적당하게 지정

         * '성별'은 도메인 GENDER 사용

CREATE TABLE 학생
    (이름 VARCHAR(15) NOT NULL,
    학번 CHAR(8)
    전공 CHAR(5)
    성별 GENDER
    생년월일 DATE
    PRIMARY KEY(학번)
    FOREIGN KEY(전공) REFERENCES 학과(학과코드)
    	ON DELETE SET NULL
        ON UPDATE CASCADE
    CONSTRAINT 생년월일제약 CHECK(생년월일>='1980-01-01'));

- CREATE VIEW

     -> 뷰(하나 이상의 기본 테이블로부터 유도되는 이름을 갖는 가상 테이블)를 정의

     -> SELECT문의 결과로써 뷰를 생성

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

     -> 고객 테이블에서 '주소'가 '포천시'인 고객들의 '이름'과 '전화번호'를 '포천 고객'이라는 뷰로 정의하는 SQL문

CREATE VIEW 포천고객(이름, 전화번호)
AS SELECT 이름,전화번호
FROM 고객
WHERE 주소 = '포천시';

- CREATE INDEX

     -> 인덱스(검색 시간을 단축시키기 위해 만든 보조적인 데이터 구조)를 정의

     -> ASC : 오름차순 정렬, 생략 시 기본 값 / DESC : 내림차순 정렬

     -> CLUSTER : 사용 시 인덱스를 클러스터드 인덱스로 지정

     -> 클러스터드 인덱스 : 인덱스 키의 순서에 따라 데이터가 정렬되어 저장되는 방식

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

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

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

ALTER

- ALTER TABLE

     -> 테이블에 대한 정의를 변경

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

     -> 학생 테이블에 최대 3 문자로 구성되는 '학년' 속성을 추가하는 SQL문

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

     -> 학생 테이블의 학년 필드의 데이터 타입과 크기를 최대 10글자로 하고 NULL값이 입력되지 않게 하는 SQL문

ALTER TABLE 학생 ALTER 학년 VARCHAR(10) NOT NULL;

DROP

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

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

- CASCADE : 제거할 때 참조 관계에 있는 테이블의 데이터도 연쇄 삭제

- RESTRICTED : 제거할 때 참조하고 있는 테이블이 있다면 삭제를 취소 

 

DCL

DCL의 개념

- DCL(데이터 제어어)는 데이터의 보안, 무결성, 회복, 병행 제어 등을 정의하는 데 사용하는 언어

 

GRANT

- 데이터베이스 관리자가 사용자에게 권한 부여

 

REVOKE

- 데이터베이스 관리자가 사용자에게 권한 취소

 

COMMIT

- 트랜잭션이 성공적으로 끝난 후 변경된 내용을 데이터베이스에 반영

 

ROLLBACK

- COMMIT 되지 않은 변경된 내용을 취소하고 데이터베이스를 이전 상태로 되돌리는 명령

 

SAVEPOINT

- 트랜잭션 내에 ROLLBACK 할 위치인 저장점을 저장

 

DML

DML의 개념

- DML(데이터 조작어)는 데이터베이스 사용자가 응용 프로그램이나 질의어를 통해 저장된 데이터를 관리하는 데 사용하는 언어

 

INSERT

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

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

- 사원 테이블에 (이름 - 김이박, 부서 - 개발)을 삽입하는 SQL문

INSERT INTO 사원(이름, 부서) VALUE(김이박, 개발);

- 사원 테이블에 있는 개발의 모든 튜플을 개발 부원(이름, 생일) 테이블에 삽입하는 SQL문

INSERT INTO 개발부원(이름, 주소)
SELECT 이름, 주소
FROM 사원
WHERE 부서 = "개발"

 

DELETE

- 테이블의 튜플 중 특정 튜플을 삭제

DELETE
FROM 테이블명
[WHERE 조건]

- 사원 테이블에서 부서가 개발인 튜플을 삭제하는 SQL문

DELETE FROM 사원 WHERE 부서 = "개발";

 

UPDATE

- 테이블의 튜플 중 특정 튜플의 내용을 변경

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

- 사원 테이블에서 김이박의 부서를 IT로 변경하는 SQL문

UPDATE 사원
SET 부서 = "IT"
WHERE 이름 = "김이박";

 

SELECT

- 테이블에서 튜플을 검색

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

- ORDER BY : 특정 속성을 기준으로 정렬하여 검색

    -> ASC : 오름차순

    -> DESC : 내림차순

- WINDOW 함수 : GROUP BY 절을 이용하지 않고 속성의 값을 집계할 함수를 기술

    -> PARTITION BY : WINDOW 함수가 적용될 범위로 사용할 속성을 지정

    -> ORDER BY : PARTITION 안에서 정렬 기준으로 사용할 속성을 지정

    -> ROW_NUMBER() : 각 레코드에 대한 일련번호 반환

    -> RANK() : 순위를 반환하되 공동 순위를 반영

    -> DENSE_RANK() : 순위를 반환하되 공동 순위를 반영하지 않음

- GROUP BY : 특정 속성을 기준으로 그룹화하여 검색할 때 사용. 그룹 함수와 같이 사용

    -> COUNT(속성명) : 그룹별 튜플 수를 구하는 함수

    -> SUM(속성명) : 그룹별 합계를 구하는 함수

    -> AVG(속성명) : 그룹별 평균을 구하는 함수

    -> MAX(속성명) : 그룹별 최대값을 구하는 함수

    -> MIN(속성명) : 그룹별 최소값을 구하는 함수

    -> ROLLUP(속성명, 속성명, ...) : 인수로 주어진 속성을 대상으로 그룹별 소계를 구하는 함수

    -> CUBE(속성명, 속성명, ...) : 인수로 주어진 속성을 대상으로 모든 조합의 그룹별 소계를 구하는 함수

- HAVING : GROUP BY와 함께 사용하여 그룹에 조건을 지정

 

SELECT 예제

예제

- 기본 검색

    -> 사원 테이블에 있는 모든 튜플을 검색하는 SQL문

SELETE * FROM 사원;

-> 사원 테이블에서 부서를 중복없이 검색하는 SQL문

SELECT DISTINCT 부서 FROM 사원;

- 조건 지정 검색

    -> 사원 테이블에서 부서가 개발인 튜플을 검색하는 SQL문

SELECT * FROM 사원 WHERE 부서 = "개발";

    -> 사원 테이블에서 부서가 개발 혹은 IT인 튜플을 검색하는 SQL문

SELECT * FROM 사원 WHERE 부서 = "개발" OR 부서 = "IT";
SELECT * FROM 사원 WHERE 부서 IN("개발","IT");

    -> 사원 테이블에서 성이 김인 튜플을 검색하는 SQL문

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

    -> 사원 테이블에서 부서가 NULL인 튜플을 검색하는 SQL문

SELECT * FROM 사원 WHERE 부서 IS NULL;

- 정렬 검색 : ORDER BY를 이용한 검색

    -> 사원 테이블에서 이름을 오름차순으로 정렬하여 튜플을 검색하는 SQL문

SELECT * FROM 사원 ORDER BY 이름 ASC;

- 하위 질의 : 조건절에 다시 SELECT문을 넣어 그 결과를 조건으로 검색

    -> 취미 테이블에서 취미활동이 축구인 사원 이름의 튜플을 검색하는 SQL문

SELECT 이름 FROM 사원 WHERE 이름 = (SELECT 이름 FROM 취미 WHERE 취미활동 = "축구");

- 복수 테이블 : 여러 테이블을 대상으로 검색

    -> 경력이 10년 이상인 사원의 이름, 부서, 취미활동을 검색하는 SQL문

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

SELECT 예제 2

예제 2

- WINDOW 함수 이용

    -> 사원 테이블에서 부서 별로 경력에 대한 일련번호를 구하여 짬순이라는 이름을 붙이는 SQL문 

SELECT 부서, 경력
    ROW_NUMBER() OVER (PARTITION BY 부서 ORDER BY 경력 DESC) AS 짬순
FROM 사원;

- GROUP BY : 그룹 지정 검색

    -> 사원 테이블에서 부서별 경력의 평균을 구하는 SQL문

SELECT 부서, AVG(경력) AS 평균
FROM 사원
GROUP BY 부서;

- 집합 연산자를 이용한 통합 질의

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

- UNION : 두 SELECT 문의 결과를 통합하고 중복된 행은 한 번만 출력

- UNION ALL : 두 SELECT 문의 결과를 통합하고 중복된 행도 그대로 출력

- INTERSECT : 두 SELECT 문의 결과 중 공통된 행만 출력

- EXCEPT : 첫번째 SELECT 문의 결과에서 두 번째 SELECT 문의 결과를 제외한 행을 출력

실기 정리

 

2021 정보처리기사 실기 정리

본 정리 글은 시나공 정보처리기사 실기책과 2020년 기출문제 등을 참고하여 작성하였습니다. -> 책 정보 확인하기 시나공 정보처리기사 실기 수험생들의 궁금증을 100% 반영시험에 나올만한 내

1d1cblog.tistory.com

 

728x90

+ Recent posts