정보처리산업기사(필기) - 데이터베이스 : 관계 데이터베이스 모델과 언어 2

SQL의 개념

SQL(Structured Query Language)의 개요

• 관계대수와 관계해석의 혼합 데이터 언어
• 질의어 + 데이터 구조 정의, 조작, 제어 기능

SQL의 분류

DDL(데이터 정의어)

• (Data Define Language) 스키마, 도메인, 테이블, 뷰, 인덱스 정의 및 변경, 삭제
• 관리자나 설계자가 사용
• 유형

명령어	기능
CREATE	정의
ALTER	테이블 정의 변경
DROP	삭제

DML(데이터 조작어)

• (Data Manipulation Language) 데이터 실질적 처리에 이용
• 유형

명령어	기능
SELECT	튜플 검색
INSERT	퓨틀 삽입
DELETE	튜플 삭제
UPDATE	튜플 내용 변경

DCL(데이터 제어어)

• (Data Control Language) 데이터 보안, 무결성, 회복, 병행 수행제어 등
• 관리자가 사용
• 종류

명령어	기능
COMMIT	실제 물리적 디스크로 저장 및 작업 완료를 관리자에게 전달
ROLLBACK	비정상적 종료시 복구
GRANT	사용 권한 부여
REVOKE	사용 권한 취소

DDL

• (Data Define Language, 데이터 정의 언어). 스키마, 도메인, 테이블, 뷰, 인덱스를 정의 및 변경 또는 삭제 시 사용.
• 정의된 내용은 Meta-data로 시스템 카탈로그에 저장.

테이블 종류 : 기본 테이블, 뷰 테이블, 임시 테이블

CREATE SCHEMA

• 스키마 정의 명령문
• 식별을 위해 이름과 소유권자, 허가권자 정의
• 표기 형식 :
  CREATE SCHEMA 스키마_이름 AUTHORIZATION 사용자_id;

CREATE DOMAIN

• 도메인 정의 명령문
• 속성이 가질 수 있는 원자값 범위가 SQL의 data_type 전체가 아닌 일부분일 경우 사용자 정의
• 표기 형식 :
  CREATE DOMAIN 도메인_ 이름 data_type
    [DEFAULT 묵시값_정의]
      [CONSTRAINT VALID-도메인_이름 CHECK (범위값)];  (대괄호 생략가능)
• data_type : SQL에서 지원하는 data_type
• 묵시값 : default 값
• 정의된 '도메인_이름'은 일반적 data_type처럼 사용

CREATE TABLE

• 기본 테이블 정의
• 표기 형식 : 
  CREATE TABLE 기본테이블_이름
             (속성명 date_type [NOT NULL] , ...,
              PRIMARY KEY(기본키_속성명) ,
              UNIQUE(대체키_속성명,...) ,
              FOREIGN KEY (외래키_속성명,...)
                           REFERENCES 참조테이블(기본키_속성명) ,
              CONSTRAINT 제약조건명 CHECK(조건식) );
• 속성명 : 테이블의 속성명과 속성의 data_type, NOT NULL 여부
• PRIMARY KEY : 기본키 속성 지정
• UNIQUE : 대체키로 사용할 속성명 지정
• FOREIGN KEY ~ REFERENCES ~
  - 테이블 참조 시 외래키 지정
  - 외래키 지정시 참조 무결성 CASCADE 법칙 적용 (데이터 삭제시 연관된 다른 테이블 데이터도 삭제)
• CHECK : 제약 조건 정의

CREATE INDEX

- 인덱스 정의 명령문

• 표기 형식
  CREATE [UNIQUE] INDEX 인덱스_이름
         ON 기본테이블_이름 ({속성_이름 [ASC | DESC],})
         [CLUSTER];
• UNIQUE 옵션
  - 사용하는 경우 : 중복값이 없는 속성으로 인덱스 생성 시
  - 생략하는 경우 : 중복값이 있는 속성으로 인덱스 생성 시
• 정렬 여부 지정
  - ASC : 오름차순, DESC : 내림차순
  - defualt : ASC
• CLUSTER 옵션 : 동일 인덱스 값을 갖는 튜플을 그룹으로 묶음

ALTER TABLE

• 테이블에 대한 정의 변경
• 표기 형식
  ALTER TABLE 기본테이블_이름ADD 속성_이름 data-type [DEFAULT '기본값'];
  ALTER TABLE 기본테이블_이름ALTER 속성_이름 [SET DEFAULT '기본값'];
  ALTER TABLE 기본테이블_이름 DROP 속성_이름 [CASCADE];
• ADD : 속성 추가
• ALTER : 속성 Default 값 변경
• DROP : 특정 속성 삭제

DROP

• 삭제 명령문
• 표기 형식
  DROP SCHEMA 스키마_이름 [CASCADE | RESTRICTED];
  DROP DOMAIN 도메인_이름 [CASCADE | RESTRICTED];
  DROP TABLE 기본테이블_이름 [CASCADE | RESTRICTED];
  DROP VIEW 뷰_이름 [CASCADE | RESTRICTED];
  DROP INDEX 인덱스_이름;
• CASCADE 옵션 : 삭제 요소를 참조하는 모든 개체 함께 삭제.
• RESTRICTED 옵션 : 삭제할 요소를 다른 개체가 참조중일 경우 삭제 취소

DML

SELECT

• 형식
  SELECT Predicate [테이블명.]속성명1, [테이블명.]속성명2, ...
  FROM 테이블명1, 테이블명2, ...
  [WHERE 조건]
  [GROUP BY 속성명1, 속성명2, ...]
  [HAVING 조건]
  [ORDER BY 속성명 [ASC | DESC]]
• Predicate : 불러올 튜플 수 제한 명령어
  - ALL : 모든 튜플 검색 (Default)
  - DISTINCT : 중복된 튜플 중 한 개 검색
  - DISTINCTROW : 중복된 튜플을 검색하나, 선택된 속성의 값이 아닌, 튜플 전체를 대상
• 속성명 : 검색할 속성 및 수식
• FROM : 검색할 테이블
• WHERE : 검색 조건
• GROUP BY : 특정 속성 기준으로 그룹화. 일반적으로 그룹함수와 사용
  그룹 함수
  - COUNT(속성명) : 그룹별 튜플 수 
  - MAX(속성명) : 그룹별 최대값
  - MIN(속성명) : 그룹별 최소값
  - SUM(속성명 ) : 그룹별 합계
  - AVG(속성명) : 그룹별 평균
• HAVING : GROUP BY와 사용. 그룹에 대한 조건
• ORDER BY : 특정 속성 기준 정렬 (ASC | DESC)

INSERT INTO ~

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

• 속성과 데이터 개수 일치
• 테이블 전체를 사용하면 속성명 생략
• SELECT로 검색 결과 삽입 가능

DELETE FROM ~

DELETE
FROM 테이블명
WHERE 조건;

• WHERE정 생략이 모두삭제
• 모두 삭제해도 테이블구조는 남음 (DROP과의 차이)

UPDATE ~ SET ~

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

내장 SQL

• 응용 프로그램 실행시 함께 실행
• 프로그램의 어디서나 사용가능
• 일반 SQL과 달리 내장 SQL은 하나의 튜플만 반환
• 반환되는 튜플을 일반 변수로 저장가능
• Host Program커마일 시 선행 처리기에 의해 분리 컴파일
• 호스트 변수와 데이터 베이스 필드 이름은 같아도 됨
• 호스트 변수의 데이터 타입은 데이터베이스 필드의 타입과 일치
• 실행 시 SQL 상태변수에 전달

호스트 언어의 실행문과 SQL문을 구분하는 법

• 명령문의 구분 :
  C/C++에서 내장 SQL은 $와 세미콜론 사이 기술, Visual BASIC에서는 앞에 'EXEC SQL'을 기술
• 변수의 구분 : 
  내장 SQL은 변수에 앞에 콜론

뷰(View)

• 자료를 제한적으로 보여주기 위해 기본 테이블에서 유도된 가상 테이블
• 물리적으로 존재하지 않지만, 사용자에게 있는 것으로 간주
• 데이터 보정, 처리과정 시험 등 임시 작업용

뷰(View) 특징

• 기본 테이블과 같은 구조와 조작
• 물리적 구현이 없는 가상 테이블
• 관리가 용이하고 명령문이 간단함
• 조인 사용 최소화로 편의성 최대화
• 뷰에 나타나지 않는 데이터 보호
• 기본키를 포함한 속성 집합으로 뷰를 구성해야 삽입, 삭제, 갱신 가능
• 다른 뷰 정의에 기반이 될 수 있음
• 삭제시 기반으로 정의된 다른 뷰도 삭제

뷰의 장점

• 논리적 데이터 독립성
• 동일 데이터에 대한 여러 요구 지원
• 사용자 데이터 관리 용이
• 접근 제어로 자동 보안

뷰의 단점

• 독립적 인덱스가 없음
• 정의 변경 불가
• 구성 내용에 대한 삭입, 삭제. 갱신 제약

뷰 정의문

CREATE VIEW 뷰이름[(속성이름 [, 속성이름])]
AS SELECT문;

• SELECT문의 결과로 뷰 생성
• 부질의어  SELECT문에는 UNION 및 ORDER BY 사용불가
• 속성 명 기본값이 SELECT의 값

뷰 삭제문

DROP VIEW 뷰이름 {RESTRICT | CASCADE};

• RESTRICT : 뷰를 다른 곳에서 참조하면 삭제 취소
• CASCADE : 뷰를 참조하는 다른 뷰 및 제약조건 모두 삭제

시스템 카탈로그

• 시스템 자체에 관련있는 스키마 및 객체 정보를 포함하는 시스템 데이터베이스
• 데이터베이스에 포함되는 모든 정의나 명세 정보 유지관리
• 기본 테이블, 뷰, 인덱스, 패키지, 접근 권한 등 구조 및 정보 저장
• 생성되면 자료 사전에 저장므로 자료 사전이라고도 함
• 카탈로그에 저장된 정보를 메타 데이터(Meta-Data)라고 함

시스템 카탈로그의 특징

• 시스템 테이블이므로 일반 이용자도 내용 검색 가능
• INSERT, DELETE, UPDATE 허용 않음
• DBMS가 스스로 생성 및 유지
• 사용자가 SQL문 조작시 시스템이 자동 갱신