[SQL] - RDBMS, DDL

SQLD를 준비하며 공부했던 내용으로, 2020 이기적 SQL 개발자(Developer) 이론서 + 기출문제를 참고했다.

관계형 데이터베이스

• 관계형 데이터베이스는 릴레이션과 릴레이션의 조인 연산을 통해 합집합, 교집합, 차집합 등을 만듦(Oracle, MySQL 등)

• 데이터베이스는 데이터를 어떠한 형태의 자류구조로 사용하느냐에 따라 나누어짐

• 데이터베이스 관리 시스템(DBMS): 계층형, 네트워크, 관계형 데이터베이스 등을 관리하기 위한 SW(Orcle, MysQL 등)

• 계층형 데이터베이스: 트리 형태의 자료구조에 데이터를 저장하고 관리, 1대N 관계를 표현

• 네트워크 데이터베이스: 오너와 멤버 형태로 데이터를 저장, 1대 N과 M대N 표현 가능

• 관계형 데이터베이스: 릴레이션에 데이터를 저장하고 관리, 릴레이션을 사용해 집합 연산과 관계 연산 가능

• 집합 연산

  1) 합집합(Union): 두 개의 릴레이션을 하나로 합하는 것, 중복된 행(튜플)은 한 번만 조회

  2) 차집합(Difference): 본래 릴레이션에는 존재하고 다른 릴레이션에는 존재하지 않는 것 조회

  3) 교집합(Intersection): 두 개의 릴레이션 간에 공통된 것을 조회

  4) 곱집합(Cartesian product): 각 릴레이션에 존재하는 모든 데이터를 조합하여 연산함
  

• 관계 연산

  1) 선택 연산(Selection): 릴레이션에서 조건에 맞는 행(튜플)만을 조회

  2) 투영 연산(Projection): 릴레이션에서 조건에 맞는 속성만을 조회

  3) 결합 연산(Join):여러 릴레이션의 공통된 속성을 사용해서 새로운 릴레이션을 만들어냄

  4) 나누기 연산(Division): 기존 릴레이션에서 나누는 릴레이션이 가지고 있는 속성과 동일한 값을 가지는 행(튜플)을 추출하고 나누는 릴레이션의 속성을 삭제한 후 중복된 행을 제거하는 연산
  

• 기본키(Primary Key): 하나의 테이블에서 유일성, 최소성, Not Null을 만족하며 해당 테이블을 대표하는 것

• 테이블은 행과 칼럼으로 구성됨, 행(튜플)은 하나의 테이블에 저장되는 값, 칼럼(속성)은 어떤 데이터를 저장하기 위한 필드

• 외래키(Foreign Key): 다른 테이블의 기본키를 참조(조인)하는 칼럼, 관계 연산 중 결합 연산(Join)을 하기 위해서 외래키 사용

SQL 종류

• SQL: 관계형 데이터베이스에 대해 데이터 구조 정의, 데이터 조작, 데이터 제어 등을 할 수 있는 절차형 언어

• SQL의 종류

  1) DDL(Data Definition Language): 관계형 데이터베이스의 구조를 정의하는 언어(CREATE, ALTER, DROP, RENAME 등), 데이터베이스 테이블을 생성하거나 변경, 삭제하는 것으로 데이터를 저장할 구조를 정의

  2) DML(Data Manipulation Language): 테이블에서 데이터를 입력, 수정, 삭제, 조회(INSERT, UPDATE, DELETE, SELECT 등), 데이터 구조가 DDL로 정의되면 해당 데이터 구조에 데이터를 입력하거나 수정, 삭제, 조회할 수 있음

  3) DCL(Data Control Language): 데이터베이스 사용자에게 권한을 부여하거나 회수(GRANT, REVOKE, TRUMCATE 등), DDL로 정의된 구조에 어떤 사용자가 접근할 수 있는지 권한을 부여

  4) TCL(Transaction Control Language): 트랜잭션을 제어하는 명령어(COMMIT, ROLLBACK, SAVEPOINT)

  • 작업의 순서: DCL로 데이터베이스의 사용자에게 권한을 부여하고, DDL로 데이터 구조를 정의, DML로 데이터를 입력한 후 개발자 및 사용자가 그 데이터를 조회하는 것

• 트랜잭션: 데이터베이스의 작업을 처리하는 단위

  1) 원자성(Atomicity): 트랜잭션은 데이터베이스 연산의 전부가 실행되거나 전혀 실행되지 않아야 함(ALL OR NOTHING), 트랜잭션의 처리가 완전히 끝나지 않았을 경우는 실행되지 않은 상태와 같아야 함

  2) 일관성(Consistency): 트랜잭션 실행 결과로 데이터베이스의 상태가 모순되지 않아야 함, 트랜잭션 실행 후에도 일관성이 유지되어야 함

  3) 고립성(Isolation): 트랜잭션 실행 중에 생성하는 연산의 중간결과는 다른 트랜잭션이 접근할 수 없음, 부분적인 실행결과를 다른 트랜잭션이 볼 수 없음

  4) 영속성(Durability): 트랜잭션이 그 실행을 성공적으로 완료하면 그 결과는 영구적 보장이 되어야 함
  

• SQL문의 실행 순서

  1) 파싱(Parsing): SQL문의 문법을 확인하고 구문분석함, 구문분석한 SQL문은 Library Cache에 저장

  2) 실행(Execution): 옵티마이저(Optimizer)가 수립한 실행 계획에 따라 SQL을 실행함

  3) 인출(Fetch): 데이터를 읽어서 전송함
  

DDL(Data Definition Language)

• 테이블 관리 SQL문

  1) Create Table: 새로운 테이블 생성, 테이블을 생성할 때 기본키, 외래키, 제약사항 등을 설정할 수 있음

  2) Alter Table: 생성된 테이블 변경, 칼럼을 추가하거나 변경, 삭제함, 기본키 및 외래키 설정 가능

  3) Drop Table: 해당 테이블 삭제, 테이블의 데이터 구조뿐만 아니라 저장된 데이터도 모두 삭제됨
  

• 테이블 생성

  1) 기본적인 테이블 생성

  Create Table EMP
  (
  	empno number(10) primary key,
  	ename varchar2(20),
  	sal number(6)
  );

  • Create Table문의 구조

    (1) Create Table: 'Create Table EMP'는 'EMP' 테이블을 생성하라는 의미, '( )' 사이에 칼럼을 쓰고 마지막은 세미콜론으로 끝남

    (2) 칼럼 정보: 테이블에 생성되는 칼럼 이름과 데이터 타입 입력, 칼럼 이름은 영문 한글 숫자 모두 가능

    (3) 데이터 타입: number는 숫자형 타입, varchar2는 가변 길이 문자열, char는 고정된 크기의 문자, date는 날짜형 타입

    (4) 기본키: 기본키를 지정할 때 칼럼 옆에 primary key 입력
    

  • DESC문: 테이블의 생성된 구조를 확인할 때 사용 (DESC EMP;)

  2) 제약조건 사용

  • 기본키, 외래키, 기본값, not null 등은 테이블을 생성할 때 지정할 수 있음
  • constraint를 사용하여 기본키(empno)와 기본키의 이름(emppk)을 지정할 수 있음 → constraint emppk primary key(empno)
  • 두 개의 기본키를 지정하려면 'constraint emppk primary key(empno, ename)'
  • sal number(10,2)는 소수점 둘째 자리까지 저장함
  • 'sysdate'는 오늘의 날짜 조회, default를 사용해 오늘 날짜를 기본값으로 지정 가능
  • 외래키(foreign key)를 지정하려면 먼저 마스터 테이블이 생성되어야 함
  • constraint를 사용하여 외래키 이름을 입력하여 생성

    constraint deptfk foreign key(deptno)
    						references dept(deptno)

  3) 테이블 생성시 CASCADE 사용

  • CASCADE 옵션은 참조 관계(기본키와 외래키 관계)가 있을 경우 참조되는 데이터를 자동으로 반영할 수 있는 것
  • 먼저 마스터 테이블(DEPT)을 생성하고 데이터 입력
  • 그 다음 EMP 테이블을 생성하고 데이터 입력, EMP 테이블을 생성할 때 'ON DELETE CASCADE' 옵션 사용
  • EMP 테이블을 생성하고 데이터 두 개 입력
  • ON DELETE CASCADE 옵션은 자신이 참조하고 있는 테이블(DEPT)의 데이터가 삭제되면 자동으로 자신(EMP)도 삭제되는 옵션
  • ON DELETE CASCADE 옵션을 사용해 참조 무결성 준수

• 테이블 변경: ALTER TABLE문을 통해 테이블 변경을 할 수 있고, 테이블명 변경, 칼럼 추가, 변경, 삭제 등을 할 수 있음

  1) 테이블 변경: ALTER TABLE ~ RENAME TO문 사용

  ALTER TABLE EMP
  	RENAME TO NEW_EMP;

  2) 칼럼 추가

  • 생성된 EMP 테이블에 ALTER TABLE ~ ADD문을 사용해서 칼럼을 추가함

    ALTER TABLE EMP
    ADD (age number (2) default 1);

  3) 칼럼 변경

  • ALTER TABLE ~ MODIFY문을 사용함, 칼럼 변경을 통해 데이터 타입을 변경하거나 데이터의 길이 변경, 제약조건 설정 가능, 칼럼의 데이터 타입을 변경할 때 기존 데이터가 있는 경우 에러가 발생함

    ALTER TABLE EMP
    	MODIFY (ename varchar2(40) not null);

  4) 칼럼 삭제

  • ALTER TABLE ~ DROP COLUMN문으로 삭제

    ALTER TABLE EMP
    	DROP COLUMN age;

  5) 칼럼명 변경

  • ALTER TABLE ~ RENAME COLUMN ~ TO문으로 칼럼명 변경

    ALTER TABLE EMP
    	RENAME COLUMN ename to new_ename;

• 테이블 삭제: DROP TABLE문을 사용해 테이블의 구조와 데이터를 모두 삭제

  DROP TABLE EMP;

  • DROP TABLE에서 'CASCADE CONSTRAINT'옵션 사용 가능, 해당 테이블의 데이터를 외래키로 참조한 슬레이브 테이블과 관련된 제약사항도 삭제할 때 사용됨

    DROP TABLE EMP CASCADE CONSTRAINT;

• 뷰(View) 생성과 삭제

  • 뷰: 테이블로부터 유도된 가상의 테이블
  • 실제 데이터를 가지고 있지 않고, 테이블을 참조해서 원하는 칼럼만을 조회할 수 있게 함
  • 뷰는 데이터 딕셔너리에 SQL문 형태로 저장하되 실행 시에 참조됨
  • 뷰의 특징 1) 참조한 테이블이 변경되면 뷰도 변경됨 2) 뷰의 검색은 참조한 테이블과 동일하게 할 수 있지만, 뷰에 대한 입력, 수정, 삭제에는 제약이 있음 3) 특정 칼럼만 조회시켜서 보안성을 향상시킴 4) 한번 생성된 뷰는 변경할 수 없고 변경을 원하면 삭제 후 재생성해야 함 5) ALTER문을 사용해 뷰를 변경할 수 없음
    
  • 뷰를 생성할 때 CREATE VIEW문을 사용하며 이때 참조할 테이블은 SELECT문으로 지정함

    CREATE VIEW T_EMP AS
    	SELECT * FROM EMP;

  • 뷰의 조회는 SELECT문을 사용해서 일반 테이블처럼 조회함

    SELECT * FROM T_EMP;

  • 뷰의 삭제는 'DROP VIEW' 사용, 뷰를 삭제해도 참조했던 테이블이 삭제되지는 않음

    DROP VIEW T_EMP;

  • 뷰의 장점: 특정 칼럼만 조회할 수 있기에 보안기능이 있음, 데이터 관리가 간단함, SELECT문이 간단함, 하나의 테이블에 여러 개의 뷰 생성 가능
  • 뷰의 단점: 독자적인 인덱스를 만들 수 없음, 삽입, 수정, 삭제, 연산이 제약됨, 데이터 구조를 변경할 수는 없음