[CS] 정보처리기능사 실기_05.SQL

in Develop on CS

본 문서는 정보처리기능사 실기 시험 대비용 필기입니다

SQL

SQL의 개념

SQL(Structed Query Language)의 개요

관계대수, 관계해석을 기초로 한 혼합 데이터 언어
단순하게 질의만을 수행하는 것이 아니라 데이터베이스의 모든 작업을 통제하는 비절차적(Non-procedural) 언어

  • 관계대수 : 관계형 데이터베이스(RDB)에서 원하는 정보와 그 정보를 어떻게 유도하는 가를 기술하는 절차적인 언어
  • 관계해석 : 원하는 정보가 무엇이라는 것만 정의하는 비절차적 특성을 지님

SQL의 분류

  • DDL(Data Define Language, 데이터 정의어)
    • SCHEMA, DOMAIN, TABLE, VIEW, INDEX를 정의하거나 변경 또는 삭제할 때 사용하는 언어
    • DBA나 데이터베이스 설계자가 사용
    • DLL의 3가지 유형
      • CREATE : SCHEMA, DOMAIN, TABLE, VIEW, INDEX를 정의
      • ALTER : TABLE에 대한 정의를 변경하는 데 사용
      • DROP : SCHEMA, DOMAIN, TABLE, VIEW, INDEX를 삭제
  • DML(Data Manipulation Language, 데이터 조작어)
    • 데이터베이스 사용자가 응용프로그램이나 질의어를 통하여 저장된 데이터를 실질적으로 처리하는 데 사용되는 언어
    • 데이터베이스 사용자와 데이터베이스 관리 시스템 간의 인터페이스를 제공
    • DML의 4가지 유형
      • SELECT : 테이블에서 조건에 맞는 튜플을 검색
      • INSERT : 테이블에 새로운 튜플을 삽입
      • DELETE : 테이블에서 조건에 맞는 튜플을 삭제
      • UPDATE : 테이블에서 조건에 맞는 튜플의 내용을 변경
  • DCL(Data Control Language, 데이터 제어어)
    • 데이터의 보안, 무결성, 회복, 병행 수행 제어 등을 정의하는 데 사용
    • DBA가 데이터 관리를 목적으로 사용
    • DCL의 종류
      • COMMIT : 명령에 의해 수행된 결과를 실제 물리적 디스크로 저장하고, 데이터베이스 조작 작업이 정상적으로 완료되었음을 관리자에게 알려 줌
      • ROLLBACK : 데이터베이스 조작 작업이 비정상적으로 종료되었을 때 원래의 상태로 복구
      • GRANT : 데이터베이스 사용자에게 사용 권한을 부여
      • REVOKE : 데이터베이스 사용자의 사용 권한을 취소

DDL

DDL의 개념

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

  • DDL은 번역한 결과가 데이터 사전(Data Dictionary)이라는 특별한 파일에 여러 개의 테이블로서 저장

CREATE SCHEMA

스키마(DB의 구조와 제약 조건에 관한 전반적인 명세를 기술한 것)를 정의

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

CREATE DOMAIN

도메인(하나의 속성이 취할 수 있는 동일한 유형의 원자값들의 집합)을 정의

  • 임의의 속성에서 취할 수 있는 값의 범위가 SQL에서 지원하는 전체 데이터 타입의 값이 아니고 일부분일 때, 사용자는 그 값의 범위를 도메인으로 정의 가능
  • 정의된 도메인명은 일반적인 데이터 타입처럼 사용함
CREATE DOMAIN 도메인명 [AS] 데이터_타입
       [DEFAULT 기본값]
       [CONSTRAINT 제약조건 CHECK(범위값)];

CREATE TABLE

테이블을 정의하는 명령문

  • 기본 테이블에 포함될 모든 속성에 대하여 속성명과 그 속성의 데이터 타입, 기본값, NOT NULL(NULL 적용 불가) 여부를 지정
  • PRIMARY KEY : 기본키로 사용할 속성 또는 속성의 집합을 지정
  • UNIQUE : 대체키로 사용할 속성 또는 속성의 집합을 지정, UNIQUE로 지정한 속성은 중복된 값을 가질 수 없다
  • FOREIGN KEY ~ REFERENCES ~
    • 참조할 다른 테이블과 그 테이블을 참조할 때 사용할 외래키 속성을 지정
    • 외래키가 지정되면 참조 무결성의 CASCADE 법칙이 적용됨
      • 참조 무결성 제약이 설정된 기본 테이블의 어떤 데이터를 삭제할 경우 그 데이터와 밀접하게 연관되어 있는 다른 테이블의 데이터들도 도미노처럼 자동으로 삭제
  • CONSTRAINT : 제약 조건의 이름 지정
  • CHECK : 속성 값에 대한 제약 조건 정의
CREATE TABLE 테이블명
        (속성명 데이터_타입 [DEFAULT 기본값][NOT NULL], ...)
        [, PRIMARY KEY(기본키_속성명, ...)]
        [, UNIQUE(대체키_속성명, ...)]
        [, FOREIGN KEY(외래키_속성명, ...)]
                  REFERENCES 참조테이블(기본키_속성명, ...)
        [, CONSTRAINT 제약조건명][CHECK(조건식)];

CREATE TABLE 학생                 // 학생 테이블을 생성
    (이름 VARCHAR(15) NOT NULL,   // '이름' 속성은 최대 문자 15자로 NULL값을 갖지 않음 
    학번 CHAR(8)                  // '학번' 속성은 문자 8자
    전공 CHAR(5)                  // '전공' 속성은 문자 5자
    성별 SEX,                     // '성별' 속성은 'SEX' 도메인을 자료형으로 사용
    생년월일 DATE,                // '생년원일' 속성은 DATE 자료형을 갖는다
    PRIMARY KEY(학번),            // '학번'을 기본키로 정의
    FOREIGN KEY(전공)             
        REFERENCES 학과(학과코드)  // '전공' 속성은 <학과> 테이블의 '학과코드' 속성을 참조하는 외래키
    CHECK(성별='남'));             // '성별' 속성의 값으로는 '남'만 지정할 수 있다

CREATE VIEW

뷰를 정의하는 명령문

  • View
    • 뷰는 물리적으로 구현되지 않음
    • 뷰 정의가 시스템 내에 저장되었다가 SQL 내에서 뷰 이름을 사용하면 실행 시간에 뷰 정의가 대체되어 수행됨
  • SELECT문을 서브 쿼리로 사용하여 SELECT문의 결과로서 뷰를 생성
    • 서브 쿼리 : 조건 절에 주어진 질의, 상위 질의에 앞서 실행되며 그 검색 결과는 상위 질의의 조건절의 피연산자로 사용됨
  • 서브 쿼리인 SELECT문에는 UNION이나 ORDER BY절 사용 불가
  • 속성명을 기숧라지 않으면 SELECT문의 속성명이 자동으로 사용됨
CREATE VIEW 뷰명[(속성명[, 속성명, ...])]
AS SELECT문;

CREATE INDEX

인덱스를 정의하는 명령문

  • UNIQUE
    • 사용된 경우 : 중복 값이 없는 고유한 특성을 갖는 인덱스 생성
    • 생략된 경우 : 중복 값을 허용하는 인덱스를 생성
  • 정렬 여부 지정
    • ASC : 오름차순 정렬 (기본 값)
    • DESC : 내림차순 정렬
  • CLUSTER : 인덱스 키의 순서에 따라 데이터가 정렬되어 저장되는 방식
CREATE [UNIQUE] INDEX 인덱스명
ON 테이블명(속성명 [ASC | DESC] [, 속성명 [ASC | DESC]])
[CLUSTER];

ALTER TABLE

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

  • ADD : 새로운 속성(열)을 추가할 때 사용
  • ALTER : 특정 속성의 데이터 타입이나 크기를 변경할 때 사용
  • DROP COLUMN : 특정 속성을 삭제할 때 사용
ALTER TABLE 테이블명 ADD 속성명 데이터_타입;
ALTER TABLE 테이블명 ALTER 속성명 데이터_타입;
ALTER TABLE 테이블명 DROP COLUMN 속성명 [CASCADE];

DROP

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

  • CASCADE : 개체를 참조하고 있는 모든 개체들이 변경/삭제
  • RESTRICT : 다른 개체가 제거할 요소를 참조중일 때는 제거를 취소
DROP SCHEMA 스키마명 [CASCADE | RESTRICT];
DROP DOMAIN 도메인명 [CASCADE | RESTRICT];
DROP TABLE 테이블명 [CASCADE | RESTRICT];
DROP VIEW 뷰명 [CASCADE | RESTRICT];
DROP INDEX 인덱스명 [CASCADE | RESTRICT];
DROP CONSTRAINT 제약조건명;

DCL

DCL(Data Control Language, 데이터 제어어)의 개념

데이터의 보안, 무결성, 회복, 병행 제어 등을 정의하는 데 사용하는 언어
DBA가 데이터 관리를 목적으로 사용

GRANT / REVOKE

DBA가 DB User에게 권한을 부여하거나 취소하기 위한 명령어

  • GRANT : 권한 부여를 위한 명령어
  • REVOKE : 권한 취소를 위한 명령어
  • 사용자 등급
    • DBA : DB 관리자
    • RESOURCE : 데이터베이스 및 테이블 생성 가능자
    • CONNECT : 단순 사용자
  • 사용자등급 지정 및 해제
GRANT 사용자등급 TO 사용자_ID_FLTMXM [IDENTIFIED BY 암호];
REVOKE 사용자등급 FROM 사용자_ID_리스트;
  • 테이블 및 속성에 대한 권한 부여 및 취소
    • 권한 종류 : ALL, SELECT, INSERT, DELETE, UPDATE, ALTER 등
    • WITH GRANT OPTION : 부여받은 권한을 다른 사용자에게 다시 부여할 수 있는 권한을 부여
    • GRANT OPTION FOR : 다른 사용자에게 권한을 부여할 수 있는 권한을 취소
    • CASCADE 권한 취소 시 권한을 부여받았던 사용자가 다른 사용자에게 부여한 권한도 연쇄적으로 취소
GRANT 권한_리스트 ON 개체 TO 사용자 [WITH GRANT OPTION];
REVOKE [GRANT OPTION FOR] 권한_리스트 ON 개체 FROM 사용자 [CASCADE];

COMMIT

트랜잭션이 성공적으로 긑나면 데이터베이스가 새로운 일관성 상태를 가지기 위해 변경된 모든 내용을 데이터베이스에 반영하여야 하는데, 이때 사용하는 명령이 COMMIT

  • 트랜잭션 : 데이터베이스에서 하나의 논리적 기능을 수행하기 위한 일련의 연산 집합으로서 작업의 단위가 됨
  • 하나의 트랜잭션은 COMMIT 되거나 ROLLBACK 되어야 함ㄴ
  • COMMIT 명령을 실행하지 않아도 DML문이 성공적으로 완료되면 자동으로 COMMIT 되고, DML이 실패하면 자동으로 ROLLBACK 되도록 Auto Commit 기능을 설정할 수 있다

ROLLBACK

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

  • 일부분만 완료된 트랜잭션은 롤백되어야 함 (일관성 상태를 유지하기 위함)

SAVEPOINT

트랜잭션 내에 ROLLBACK 할 위치인 저장점을 지정하는 명령어

  • 저장점을 지정할 때는 이름을 부여하며, ROLLBACK 시 지정된 저장점까지의 트개잭션 처리 내용이 취소됨

DML

DML(Data Manipulation Language, 데이터 조작어)의 개념

DB User가 응용 프로그램이나 질의어를 통해 저장된 데이터를 실질적으로 관리하는데 사용되는 언어

  • DB User와 DBMS 간의 인터페이스를 제공
  • 유형 : SELECT, INSERT, DELETE, UPDATE

INSERT TO

삽입문은 기본 테이블에 새로운 튜플을 삽입할 때 사용

INSERT INTO 테이블명([속성명1, 속성명2, ...])
VALUES (데이터1, 데이터2, ...);
  • 대응하는 속성과 데이터는 개수와 데이터 유형이 일치해야 함
  • 기본 테이블의 모든 속성을 사용할 때는 속성명 생략 가능
  • SELECT문을 사용하여 다른 테이블의 검색 결과를 삽입할 수 있다

DELETE FROM ~

삭제문은 기본 테이블에 있는 튜플들 중에서 특정 튜플(행)을 삭제할 때 사용

DELETE
FROM 테이블명
[WHERE 조건];
  • 모든 레코드를 삭제할 때는 WHERE절을 생략
  • 모든 레코드를 삭제하더라도 테이블 구조는 남아 있기 떄문에 디스크에서 테이블을 완전히 제거하는 DROP과는 다름

UPDATE ~ SET ~

갱신문은 기본 테이블에 있는 튜플들 중에서 특정 튜플의 내용을 변경할 때 사용

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

DML - SELECT

SELECT문은 테이블을 구성하는 튜플(행)들 중에서 전체 또는 조건을 만족하는 튜플을 검색하여 주기억장치 상에 임시 테이블로 구성하는 명령문

일반형식

SELECT PREDICATE [테이블명.]속성명1, [테이블명2.]속성명2, ...
FROM 테이블명1, 테이블명2, ...
[WHERE 조건]
[GROUP BY 속성명1, 속성명2, ...]
[HAVING 조건]
[ORDER BY 속성명 [ASC | DESC]];
  • SELECT절
    • 속성명 : 검색하여 불러올 속성(열) 또는 속성을 이용한 수식을 지정
      • 기본 테이블을 구성하는 모든 속성을 지정할 때는 ‘*‘을 기술
      • 두 개 이상의 테이블을 대상으로 검색할 때는 ‘테이블명.속성명’으로 표현
    • PREDICATE : 불러올 튜플 수를 제한할 명령어를 기술
  • FROM절 : 질의에 의해 검색될 데이터들을 포함하는 테이블명을 기술
  • WHERE절 : 검색할 조건 기술
  • GROUP BY절 : 특정 속성을 기준으로 그룹화하여 검색할 때 그룹화할 속성을 지정
    • 그룹합수(릴레이션의 한 열을 요약하거나 집계하는 함수)와 함께 사용
    • COUNT, MAX, MIN, SUM, AVG
  • HAVING절 : GROUP BY와 함께 사용되며, 그룹에 대한 조건을 지정
  • ORDER BY절 : 특정 속성을 기준으로 정렬하여 검색할 때 사용

기본 검색

SELECT절에 원하는 속성을 지정하여 검색

  • DISTINCT : 중복 제거
SELECT DISTINCT 속성명
FROM 테이블명;

조건 지정 검색

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

  • LIKE 연산자 : 만능문자(%, _ 등)를 이용해 필드의 값 중 일부만 일치하는 자료를 검색할 수 있음
SELECT *
FROM 테이블명
WHERE 속성 LIKE 만능문자% OR 만능문자_';
  • BETWEEN 연산자 : 지정한 사이의 값
SELECT *
FROM 테이블명
WHERE 속성 BETWEEN 데이터 AND 데이터;
  • CASE 문 : sql의 조건문

예시) <학생> 테이블을 대상으로 '학과코드'의 값이 "ELE"인 경우 "전자공학과"라는 별칭으로 개수를 1씩 증가시키고, '학과코드'의 값이 "COM"인 경우 "컴퓨터공학과"라는 별칭으로 개수를 1씩 증가시킨 후 출력

SELECT COUNT (CASE WHEN 학과코드 = "ELE" THEN 1 END) AS "전자공학과",
      COUNT(CASE WHEN 학과코드 = "COM" THEN 1 END) AS "컴퓨터공학과"
FROM 학생; //

정렬 검색

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

SELECT TOP 2 *
FROM 테이블명
ORDER BY 속성명 DESC; // 해당 속성 내림차순 정렬 후 상위 2개 튜플만 검색

그룹 지정 검색

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

SELECT 부서, AVG(기본급) AS 평균
FROM 사원
GROUP BY 부서;

하위 질의

하위 질의가 있는 질의문은 조건절에 주어진 하위 질의를 먼저 수행하여 그 검색 결과를 조건절의 피연산자로 사용함

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

통합(UNION) 질의

두 SELECT문의 조회 결과를 통합하여 검색

  • 집합 연산자의 종류
    • UNION (합집합_중복제외)
    • UNION ALL (합집합_중복허용)
    • INTERSECT (교집합)
    • EXCEPT (차집합)
SELECT *
FROM 테이블명1
UNION
SELECT *
FROM 테이블명2;

© 2023 Jinsoo Lee. All rights reserved.

Powered by Hydejack v9.1.6