[정처기 필기] 「3」 | SQL 응용 - (3.1) SQL 개념, DDL

[정처기 필기] 「3」 | SQL 응용 - (3.1) SQL 개념, DDL

「1」 소프트웨어 설계

「2」 소프트웨어 개발

> 「3」 데이터베이스 구축

- 논리 데이터베이스 설계, 물리 데이터베이스 설계, > SQL 응용, SQL 활용, 데이터 전환

「4」  프로그래밍 언어 활용

「5」  정보시스템 구축 관리

 

> 1 SQL 개념

> 2 DDL

3 DCL

4 DML

5 DML - SELECT - 1

6 DML - SELECT - 2

7 DML - JOIN

1. SQL 개념

SQL(Structured Query Language)의 개요

 

- IBM 연구소에서 개발한 SEQUEL에서 유래

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

- 관계대수와 관계해석을 기초로 한 혼합 데이터 언어

- 질의어로 질의 기능뿐 아니라 데이터 구조의 정의, 데이터 조작, 데이터 제어 기능

- 기능에 따라 DDL(데이터 정의어), DML(데이터 조작어), DCL(데이터 제어어)

 

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

 

- SCHEMA, DOMAIN, TABLE, VIEW, INDEX를 정의, 변경, 삭제

- 논리적 데이터 구조, 물리적 데이터 구조의 사상을 정의

- 데이터베이스 관리자, 데이터베이스 설계자가 사용

 

CREATE : SCHEMA, DOMAIN, TABLE, VIEW, INDEX 정의

ALTER : TABLE에 대한 정의를 변경

DROP : SCHEMA, DOMAIN, TABLE, VIEW, INDEX 삭제

 

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

 

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

- 데이터베이스 사용자와 데이터베이스 관리 시스템 간 인터페이스 제공

 

SELECT : 조건이 맞는 튜플 검색

INSERT : 새로운 튜플 삽입

DELETE : 조건에 맞는 튜플 삭제
UPDATE :조건에 맞는 튜플 내용 변경

 

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

 

- 데이터의 보안, 무결성, 회복, 병행 수행 제어 등 정의

- 데이터베이스 관리자가 데이터 관리를 목적

 

COMMIT : 수행된 결과를 실제 물리적 디스크로 저장, 정상적으로 완료되었음을 관리자에게 알림

ROLLBACK : 비정상적으로 종료되었을 때 원래의 상태로 복구

GRANT : 사용자에게 사용 권한 부여

REVOKE : 사용자의 권한 취소

2. DDL

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

 

DB 구조, 데이터 형식, 접근 방식 등 DB 구축, 수정

 

- 번역한 결과가 데이터 사전이라는 특별한 파일에 여러 개의 테이블로 저장

 

CREATE SCHEMA

 

스키마를 정의하는 명령문

 

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

 

- 스키마 이름과 소유권자나 허가권자 정의

 

ex)

CREATE SCHEMA 대학교 AUTHORIZATION 홍길동;

 // 소유권자의 사용자 ID가 '홍길동'인 스키마 '대학교'를 정의하는 SQL문

 

CREATE DOMAIN

 

도메인을 정의하는 명령문

 

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

 

- 값의 범위가 전체 데이터 타입의 값이 아니고 일부분일 때 도메인으로 정의할 수 있음

- 도메인명은 데이터 타입처럼 사용

 

ex)

CREATE DOMAIN SEX CHAR(1)

// 정의된 도메인 이름은 'SEX'이며, 문자형이고 크기는 1자

    DEFAUL '남'

// 도메인 SEX를 지정한 속성의 기본값은 '남'

    CONSTRAINT VALID-SEX CHECK(VALUE IN ('남', '여'));

// SEX를 지정한 속성에는 '남', '여' 중 하나의 값만 지정 가능

 

>SQL에서 지원하는 기본 데이터 타입< 

 

정수(Integer) : INTEGER(48byte 정수), SMALLINT(2byte 정수)

실수(Float) : FLOAT, REAL, DOUBLE PRECISION

형식화된 숫자 : DEC(i, j), i 전체 자릿수, j 소수부 자릿수

고정길이 문자 : CHAR(n), CHARACTER(n)

가변길이 문자 : VARCHAR(n), CHARACTER VARYNG(n)

고정길이 비트열(Bit String) : BIT(n)

가변길이 비트열 : VARBIT(n)

날짜 : DATE

시간 : TIME

 

CREATE TABLE

 

테이블을 정의하는 명령문

 

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

 

-  기본 테이블에 포함될 모든 속성명과 데이터 타입, 기본값, NOT NULL 여부 지정

- PRIMARY KEY : 기본키로 사용할 속성, 속성의 집합

- UNIQUE : 대체키로 사용할 속성, 속성의 집합, 중복된 값을 가질 수 없음

- FOREIGN KEY ~ REFERENCES ~

  : 참조할 다른 테이블과 참조할 때 사용할 외래키 속성을 지정

  : 외래키가 지정되면 참조 무결성의 CASCADE 법칙 적용

  : ON DELETE 옵션 - 참조 테이블의 튜플이 삭제되었을 때, 기본 테이블이 취해야 할 사항 지정

  : ON UPDATE 옵션 - 참조 테이블의 참조 속성 값이 변경되었을 때, 기본 테이블이 취해야 할 사항 지정

    > NO ACTION : 아무런 조치 취하지 않음

    > CASCADE : 참조 테이블의 튜플이 삭제되면 관련 튜플 모두 삭제

                           속성이 변경되면 관련 튜플의 속성 값도 모두 변경

    > SET NULL : 참조 테이블에 변화 있으면, 관련 튜플의 속성값 NULL로 변경

    > SET DEFAULT : 참조 테이블에 변화 있으면, 관련 튜플의 속성값 기본값으로 변경

- CONSTRAINT : 제약 조건의 이름 지정

- CHECK :  속성 값에 대한 제약 조건 정의

 

ex)

CREATE TABLE 학생

// <학생> 테이블 생성

    (이름 VARCHAR(15) NOT NULL,

// '이름' 속성은 최대 문자 15자, NULL 값 갖지 않음

    학번 CHAR(8),

    전공 CHAR(5),

// '학번' 속성은 문자 8자, '전공' 속성은 문자 5자

    성별 SEX,

    생년월일 DATE,

// '성별' 속성은 'SEX' 도메인을 자료형, '생년월일' 속성은 DATE 자료형

    PRIMARY KEY(학번),

    FOREIGN KEY(전공) REFERENCES 학과(학과코드)

// '학번'을 기본키로 정의, '전공' 속성은 <학과> 테이블의 '학과코드' 속성을 참조하는 외래키

        ON DELETE SET NULL

        ON UPDATE CASCADE,

// <학과> 테이블에서 튜플 삭제 시 관련 튜플의 '전공' 속성값을 NULL로 변경

// <학과> 테이블에서 '학과코드'가 변경되면 관련 튜플의 '전공 '속성값도 같은 값으로 변경

    CONSTRAINT 생년월일제약

        CHECK(생년월일>='1980-01-01'));

// '생년월일' 속성에는 1980-01-01 이후의 값만 저장, 제약조건 이름은 ' 생년월일제약' 

 

>다른 테이블을 이용항 테이블 정의<

 

CREATE TABLE 신규테이블명 AS SELECT 속성명[, 속성명, ...] FROM 기존테이블명;

 

- 기존 테이블의 제약 조건은 적용되지 않으므로 ALTER TABLE 명령을 통해 추가해야 함

- 기존 테이블의 일부 속성만 신규 테이블로 생성할 수 있음, 모든 속성을 생성할 경우 속성명 부분에 '*' 입력

 

ex)

CREATE TABLE 재학생 AS SELECT 학번, 이름, 학년 FROM 학생;

// <학생> 테이블의 '학번', '이름', '학년' 속성을 이용하여 <재학생> 테이블을 정의

 

CREATE VIEW

 

뷰를 정의하는 명령문

 

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

 

- SELECT 문을 서브 쿼리로 사용하여, SELECT문의 결과로 뷰를 생성

- 서브 쿼리인 SELECT문에 UNION, ORDER BY 사용 금지

- 속성명을 기술하지 않으면 SELECT문의 속성명이 자동으로 사용

 

ex)

CREATE VIEW 안산고객(성명, 전화번호)

AS SELECT 성명, 전화번호

FROM 고객

WHERE 주소 = '안산시';

// <고객> 테이블에서 '주소'가 '안산시'인 고객들의 '성명'과 '전화번호'를 '안산고객'이라는 뷰로 정의

 

CREATE INDEX

 

인덱스를 정의하는 명령문

 

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

 

 

- UNIQUE : 사용된 경우 중복 값이 없는 속성으로, 생략된 경우 중복 값을 허용하는 속성으로 인덱스 생성

- 정렬 여부 지정

  : ASC - 오름차순 정렬 (기본값)

  : DESC - 내림차순 정렬

- CLUSTER : 클러스터드 인덱스로 설정

 

ex)

CREATE UNIQUE INDEX 고객번호_idx

ON 고객(고객번호 DESC);

// <고객> 테이블에서 UNIQUE 특성을 갖는 '고객번호' 속성을 내림차순 정렬 후 '고객번호_idx'로 인덱스 정의

 

ALTER TABLE

 

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

 

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

 

- ADD : 새로운 속성 추가할 때 사용

- ALTER : 특정 속성의 DEFAULT 값 변경 할 때 사용

- DROP COLUMN : 특정 속성 삭제할 때 사용

 

ex)

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

// <학생> 테이블의 '학번' 필드의 데이터 타입과 크기를 VARCHAR(10), NULL 값이 입력되지 않게 변경

 

DROP

 

제거하는 명령문

 

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

 

- RESTRICT : 다른 개체가 제거할 요소를 참조 중 일 때는 제거를 취소

 

ex)

DROP TABLE 학생 CASCADE;

// <학생> 테이블을 제거하되, 이 테이블을 참조하는 모든 데이터를 함께 제거

 

 

 

 

 

 

 

 

 

 

출처 | <시나공> 정보처리기사 필기 2024 기본서 (길벗알앤디)