정보처리 기능사 실기 [SQL의 개념]
2023-03-20 10:38:09

SQL의 개요

  • 1974년 IBM연구소에서 개발한 SEQUEL에서 유래한다.
  • IBM외에도 많은 회사에서 관계형 데이터베이스(RDB)를 지원하는 언어로 채택하고 있다.
  • 관계대수 와 관계해석 을 기초로 한 혼합 데이터 언어이다.
  • 질의어 지만. 질의 기능만 있는 것이 아니라 데이터 구조의 정의, 데이터 조작. 데이터 제어 기능을 모두 갖추고 있다.

DDL, DML, DCL (정의어, 조작어, 제어어)

DDL

  • DDL(Data Define Language, 데이터 정의어)
  • DDL은 SCHEMA, DOMAIN, TABLE, VIEW, INDEX를 정의하거나 변경 또는 삭제할 떄 사용하는 언어이다.
  • 데이터베이스 관리자나 데이터베이스 설계자가 사용한다.
명령어 기능
CREATE SCHEMA, DOMAIN, TABLE, VIEW, INDEX를 정의한다.
ALTER TABLE에 정의를 변경하는 데 사용한다.
DROP SCHEMA, DOMAIN, TABLE, VIEW, INDEX를 삭제한다.

DML

  • DML(Data Manipulation Language, 데이터 조작어)
  • DML은 데이터베이스 사용자가 응용 프로그램이나 질의어를 통하여 저장된 데이터를 실질적으로 처리하는 데 사용되는 언어이다.
  • 데이터베이스 사용자와 데이터베이스 관리 시스템 간의 인터페이스를 제공한다.
  • 데이터 조작어(DML)의 4가지 유형
명령어 기능
SELECT 테이블에서 조건에 맞는 튜플을 검색한다.
INSERT TABLE에 정의를 변경하는 데 사용한다.
DELETE 테이블에서 조건에 맞는 튜플을 삭제한다.
UPDATE 테이블에서 조건에 맞는 튜플의 내용을 변경한다.

DCL

  • DCL(Data Control Language, 데이터 제어어)
  • DCL은 데이터의 보안, 무결성, 회복, 병행 수행 제어 등을 정의하는 데 사용되는 언어이다.
  • 데이터베이스 관리자가 데이터 관리를 목적으로 사용한다.
  • 데이터 제어어(DCL)의 종류
명령어 기능
COMMIT 명령에 의해 수행된 결과를 실제 물리적 디스크로 저장하고, 데이터베이스 조작 작업이 정삭적으로 완료되었음을 관리자에게 알려준다.
GRANT 데이터베이스 사용자에게 사용 권한을 부여한다.
REVOKE 데이터베이스 사용자의 사용 권한을 취소한다

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

  • DDL는 DB 구조, 데이터 형식, 접근 방식 등 DB를 구축하거나 수정할 목적으로 사용하는 언어이다.
    • DDL 은 번역한 결과가 데이터 사전(Data Dictionary)이라는 특별한 파일에 여러 개의 테이블로서 저장된다.
    • DDL은 번역한 결과가 데이터 사전(Data Dictionary)이라는 특별한 파일에 여러 개의 테이블로서 저장된다.
    • DDL에는 CREATE SCHEMA, CREATE DOMAIN, CREATE TABLE, CREATE VIEW, CREATE INDEX, ALTER TABLE, DROP 등이 있다.

CREATE SCHEMA

  • CREATE SCHEMA는 스키마를 정의하는 명령문이다.
    • 스키마의 식별을 위해 스키마 이름과 소유권자나 허가권자를 정의한다.
    • 표기형식
      1
      CREATE SCHEMA 스키마명 AUTHORIZATUON 사용자\_id;

CREATE DOMAIN

  • CREATE DOMAIN은 도메인을 정의하는 명령문이다.

    • 임의의 속성에서 취할 수 있는 값의 범위가 SQL에서 지원하는 전체 데이터 타입의 값이 아니고 일부분일 때, 사용자는 그 값의 범위를 도메인으로 정의 할 수 있다.

    • 정의된 도메인명은 일반적인 데이터 타입처럼 사용한다.

    • 표기 형식

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

    • 데이터 타입 : SQL에서 지원하는 데이터 타입

    • 기본값 : 데이터를 입력하지 않았을 때 자동으로 입력되는 값

    CREATE TABLE

  • CREATE TABLE은 테이블을 정의하는 명령문이다.

    • 표기 형식
      1
      2
      3
      4
      5
      6
      7
      CREATE TABLE 테이블명
        (속성명 데이터_타입 [DEFAULT 기본값] [NOT NULL],...)
        [, PRIMARY KEY[기본키_속성명,...]]
        [, UNIQUE(대체키_속성명,...)]
        [, FOREIGN KEY(외래키_속성명,...)]
        EFERENCES 참조테이블(기본키_속성명,...)
        [, CONSTRAINT 제약조건명] [CHECK (조건식)];
    • 기본 테이블에 포함될 모든 속성에 대하여 속성명과 그 속서으이 데이터 타입, 기본값, NOT NULL 여부를 지정한다.
    • PRIMARY KEY : 기본키로 사용할 속성 또는 속성의 집합을 지정한다.
    • UNIQUE : 대체키로 사용할 속성 또는 속성의 집합을 지정하는 것으로 UNIQUE로 지정한 속성은 중복된 값을 가질 수 없다.
    • FOREIGN KEY ~ REFERENCES ~
      • 참조할 다른 테이블과 그 테이블을 참조할 때 사용할 외래키 속성을 지정 한다.
      • 외래키가 지정되면 참조 무결성의 CASCADE 법칙이 적용된다.
      • CONSTRAINT : 제약 조건의 이름을 지정한다. 이름을 지정할 필요가 없으면 CHECK절만 사용하여 속성 값에 대한 제약 조건을 명시한다.
      • CHECK : 속성 값에 대한 제약 조건을 정의한다.

CREATE VIEW

  • CREATE VIEW는 뷰(View)를 정의하는 명령문이다. -표기 형식
    1
    2
    CREATE VIEW 뷰명[(속성명[,속성명, ...])]
    AS SELECT문;
    • SELECT문은 서브 쿼리로 사용하여 SELECT문의 결과로서 뷰를 생성한다.
    • 서브 쿼리인 SELECT문에는 UNION이나 ORDER BY절을 사용할 수 없다.
    • 속성명을 기술하지 않으면 SELECT문의 속성명이 자동으로 사용된다.

CREATE INDEX

  • CREATE INDEX는 인덱스를 정의하는 명령문이다.
    • 표기형식
      1
      2
      3
      CREATE [UNIQUE] INDEX 인덱스명
      ON 테이블명(속성명 [ASC | DESC] [,속성명 [ASC | DESC]])
      [CLUSTER];
    • UNIQUE
      • 사용된 경우 : 중복 값이 없는 고유한 특성을 갖는 인덱스를 생성한다.
      • 생략된 경우 : 중복 값을 허용하는 인덱스를 생성한다.
    • 정렬 여부 지정
      • ASC : 오름차순 정렬
      • DESC : 내림차순 정렬
      • 생략된 경우 : 오름차순(ASC)으로 정렬됨
        -CLUSTER : 사용하면 인덱스가 클러스터드 인덱스로 설정됨

ALTER TABLE

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

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

DROP

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

  • 표기형식
1
2
3
4
5
6
DROP SCHEMA 스키마명 [CASCADE | RESTRICT];
DROP DOMAIN 도메인명 [CASCADE | RESTRICT];
DROP TABLE 테이블명 [CASCADE | RESTRICT];
DROP VIEW 뷰명 [CASCADE | RESTRICT];
DROP INDEX 인덱스명 [CASCADE | RESTRICT];
DROP CONSTRAINT 제약조건명 [CASCADE | RESTRICT];
  • DROP SCHEMA : 스키마를 제거한다.
  • DROP DOMAIN : 도메인을 제거한다.
  • DROP TABLE : 테이블을 제거한다.
  • DROP VIE : 뷰를 제거한다.
  • DROP INDEX : 인덱스를 제거한다.
  • DROP CONSTRAINT : 제약 조건을 제거한다.
  • CASCADE : 제거할 요소를 참조하는 다른 모든 개체를 함께 제거한다. 즉
    주 테이블의 데이터 제거 시 각 외래키와 관계를 맺고 있는 모든 데이터를 제거하는 참조 무결성 제약 조건을 설정하기 위해 사용된다.
  • RESTRICT : 다른 개체가 제거할 요소를 참조중일 때는 제거를 취소 한다.

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

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

  • DML은 데이터베이스 사용자와 데이터베이스 관리 시스템 간의 인터페이스를 제공한다.
  • DML의 유형
명령문 기능
SELECT 테이블에서 튜플을 검색한다
INSERT 테이블에서 새로운 튜플을 삽입한다
DELETE 테이블에서 튜플을 삭제한다
UPDATE 테이블에서 튜플의 내용을 갱신한다

삽입문(INSERT INTO~)

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

  • 일반 형식
1
2
INSERT INTO 테이블명([속성명1, 속성명2,...]);
VALUES (데이터1, 데이터2,...);
  • 대응하는 속성과 데이터는 개수와 데이터 유형이 일치해야 한다.
  • 기본 테이블의 모든 속성을 사용할 때는 속성명을 생략할 수 있다.
  • SELECT문을 사용하여 다른 테이블의 검색 결과를 삽입할 수 있다.
이름 부서 생일 주소 기본급
홍길동 기획 04/05/06 망원동 120
임꺽정 인터넷 01/09/69 성산동 80
황진이 편집 07/21/75 연희동 100
김선달 편집 10/22/23 망원동 90
선춘향 기획 02/20/64 망원동 100
장길산 편집 03/11/67 상암동 120
일지매 기획 04/29/78 합정동 110
강호동 인터넷 12/11/80 90

삭제문(DELETE FROM~)

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

  • 일반 형식
1
2
3
DELETE
FROM 테이블명
[WHEER 조건];
  • 모든 레코드를 삭제할 때는 WHERE절을 생략한다.
  • 모든 레코드를 삭제하더라도 테이블 구조는 남아 있기 때문에 디스크에서 테이블을 완전히 제거하는 DROP과는 다르다.

갱신문(UPDATE~ SET~)

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

  • 일반 형식
1
2
3
UPDATE 테이블명
SET 속성명 = 데이터[, 속성명=데이터, ...]
[WHERE 조건];

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

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

  • DCL은 데이터베이스 관리자(DBA)가 데이터를 관리를 목적으로 사용한다.
  • DCL에는 GRANT, REVOKE, COMMIT, ROLLBACK, SAVEPOINT 등이 있다.

GRANT / REVOKE

데이터베이스 관리자가 데이터 베이이스 사용자에게 권한을 부여하거나 취소하기 위한 명령어이다.

  • GRANT : 권한 부여를 위한 명령어
  • REVOKE : 권한 취소를 위한 명령어
  • 사용자등급 지정 및 해제
1
2
GRANT 사용자등급 TO 사용자*ID*리스트 [IDENTIFIED BY 암호];
REVOKE 사용자등급 FROM 사용자*ID*리스트;
  • 테이블 및 속성에 대한 권한 부여 및 취소
1
2
GRANT 권한\_리스트 ON 개체 TO 사용자 [WITH GRANT OPTION];
ROVOKE [GRANT OPION FOR] 권한\_리스트 ON 개체 FROM 사용자 [CASCADE];
  • 권한 종류 : ALL, SELECT, INSERT, DELETE, UPDATE, ALTER 등
  • WITH GRANT OPTION : 부여받은 권한을 다른 사용자에게 다시 부여할 수 있는 권한을 부여함
  • GRANT OPTION FOR : 다른 사용자에게 권한을 부여할 수 있는 권한을 취소함
  • CASCADE : 권한 취소 시 권한을 부여받았던 사용자가 다른 사용자에게 부여한 권한도 연쇠적으로 취소함

COMMIT

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

  • COMMIT 명령을 실행하지 않아도 DML문이 성공적으로 완료되면 자동으로 커밋되고, DML 이 실패하면 자동으로 ROLLBACK이 되도록 AOTO COMMIT 기능을 설정할 수 있다.

ROLLBACK

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

  • 트랜잭션 전체가 성공적으로 끝나지 못하면 일부 변경된 내용만 데이터베이스에 반영되는 비일관성(Inconsistency)인 상태를 가질 수 있기 때문에 일부분만 완료된 트랜잭션은 롤백(ROLLBACK)되어야 한다.

SVAEPOINT

SAVEPOINT는 트랜잭션 내에 롤백 할 위치인 저장점을 지정하는 명령어이다.

  • 저장점을 지정할 때는 이름을 부여하며, 롤백 시 지정된 저장점까지 의 트랜잭션 처리 내용이 취소된다.
사원번호 이름 부서
10 김기획 기획부
20 박인사 인사부
30 최재무 재무부
30 오영업 영업부
2023-03-20 10:38:09
Next