SQL

[6장] 데이터베이스 객체 작성과 삭제

오승미 2022. 11. 21. 15:34

25강 데이터베이스 객체

  • 데이터베이스 객체

데이터베이스에는 테이블 외에 다양한 데이터를 저장하거나 관리하는 '어떤 것' 을 만들 수 있다. (테이블, 뷰, 인덱스 .. )

    -> 데이터베이스 내에 정의하는 모든 것

여기서 객체란, 데이터베이스 내에 실체를 가지는 어떤 것을 일컫는다.

    ->SELECT, INSERT 같은 명령은 실체가 없으므로 객체가 아니다.

객체는 이름을 가지고 이름이 겹쳐선 안된다.

    -> 열 또한 이름을 가지고 열에 별명을 붙일 수 있지만 이는 객체가 아니다.

    -> 이름을 붙일 때는 제약 사항(명명규칙)을  따라야 한다.

  1. 기존 이름이나 예약어와 중복하지 않는다.
  2. 숫자로 시작할 수 없다.
  3. 언더스코어(_) 이외의 기호는 사용할 수 없다.
  4. 한글을 사용할 때는 더블쿼트(MySQL에서는 백쿼트)로 둘러싼다.
  5. 시스템이 허용하는 길이를 초과하지 않는다.

 

  • 스키마

데이터베이스 객체는 스키마라는 그릇 안에 만들어진다.

    -> 객체의 이름이 같아도 스키마가 다르면 상관없다.

SQL 명령의 DDL 을 이용하여 정의한다.

MySQL에서는 'CREATE DATABSE' 명령으로 작성한 데이터베이스가 스키마가 된다.

Oracle 등에서는 데이터베이스와 데이터베이스 사용자가 계층적 스키마가 된다.

 

26강 테이블 작성, 삭제, 변경

SELECT, DELETE, UPDATE : DML (데이터 조작어)

CREATE, DROP, ALTER, TRUNCATE : DDL (데이터 정의어)

    -> 스키마 내의 객체를 관리할 때 사용한다.

 

  • 테이블 작성
CREATE TABLE 테이블명 (
    열 정의1,
    열 정의2,
    ...
)

* 열 정의

[] 은 생략 가능하다.

  1. 열명 - 열에 붙이는 이름
  2. 자료형 - INTEGER, VARCHAR 등을 지정 (CHAR, VARCHAR 문자열형은 최대길이를 괄호로 묶는다.)
  3. 기본값 - DEFAULT로 지정하되, 자료형에 맞는 리터럴로 기술한다.
  4. NULL - 열이 NULL을 허용할 것인지 결정한다. 생략했을 경우 NULL 을 허용한다.
열명 자료형 [DEFAULT 기본값] [NULL|NOT NULL]

 

sample62 테이블 작성

 

  • 테이블 삭제
DROP TABLE 테이블명

SQL 명령의 경우 사용자에게 확인을 요구하지 않는다.

 

  • 데이터 행 삭제

테이블 정의는 그대로 둔 채 데이터만 삭제할 때는 DELETE 명령을 사용한다.

     -> 내부처리로 인해 삭제할 행이 많으면 처리속도가 늦어진다. (모든 작업을 로그에 남기기 때문)

이런 경우에는 DDL 로 분류되는 TRUNCATE 명령을 사용한다.

    -> WHERE 구를 지정할 수 없고 삭제할 행을 지정할 수 없지만 모든 행을 삭제할 때 속도가 빠르다.

 

* 참고

  1. DELETE 명령어는 데이터는 지워지지만 테이블 용량은 줄어 들지 않는다. 원하는 데이터만 지울 수 있다. 삭제 후 잘못 삭제한 것을 되돌릴 수 있다.
  2. TRUNCATE 명령어는 용량이 줄어 들고, 인덱스 등도 모두 삭제 된다. 테이블은 삭제하지는 않고, 데이터만 삭제한다. 한꺼번에 다 지워야 한다. 삭제 후 절대 되돌릴 수 없다.

    -> ROLLBACK 을 통해 데이터를 변경하기 이전 상태로 돌릴 수 있다. 이 때 SAVEPOINT 지정이 가능하다.

    -> COMMIT 을 했다면 데이터베이스에 마지막으로 반영되었기 때문에 되돌릴 수 없다.

  1. DROP 명령어는 데이블 전체를 삭제, 공간, 객체를 삭제한다. 삭제 후 절대 되돌릴 수 없다.
TRUNCATE TABLE 테이블명

 

  • 테이블 변경
ALTER TABLE 테이블명 변경명령

열 추가, 삭제, 변경

제약 추가, 삭제

 

  • 열 추가
ALTER TABLE 테이블명 ADD 열 정의

 

열 정의에는 열 이름과 자료형을 지정하고 필요에 따라 기본값과 NOT NULL 제약을 지정하면 된다.

    -> NOT NULL 제약을 붙인 열을 추가하고 싶다면 NOT NULL 제약을 걸고 NULL 이외의 값으로 기본값을 지정해야 한다.

 

sample62 테이블에 newcol 열 추가

 

  • 열 속성 변경
ALTER TABLE 테이블명 MODIFY 열 정의

newcol 열 속성 변경

 

기존의 데이터 행이 존재하는 경우, 속성 변경에 따라 데이터 역시 변환된다.

자료형이 변경되면 테이블에 들어간 데이터의 자료형 역시 바뀌는데, 처리과정에서 에러가 발생하면 ALTER TABLE 명령이 실행되지 않는다.

 

  • 열 이름 변경
ALTER TABLE 테이블명 CHANGE [기존 열 이름] [신규 열 정의]

Oracle 에서는 열 이름 변경 시 RENAME TO 하부명령을 사용한다.

 

newcol 이름을 c 로 변경

 

  • 열 삭제
ALTER TABLE 테이블명 DROP 열명

c 열 삭제

 

  • ALTER TABLE로 테이블 관리
  • 최대길이 연장

ALTER TABLE로 열의 자료형만 변경해 대응할 수 있다.

    -> 특히 문자열형의 경우 최대길이를 지정하는데, 이 최대길이를 ALTER TABLE로 늘릴 수 있다.

 

ALTER TABLE sample MODIFY col VARCHAR(30);

 

최대길이를 줄이는 경우 여러 가지 문제가 발생한다. 데이터 일부를 잘려나가게 할 수 없기 때문이다.

일반적으로 줄이는 경우는 별로 없다.

 

  • 열 추가

ALTER TABLE sample ADD new_col INTEGER

 

기존 시스템에서 INSERT 명령에서 열 지정이 생략되어 있다면,  열을 추가한 후 그대로 실행했을 때 열의 개수가 맞지 않아 에러가 발생한다.

    -> new_col 이 추가되어 sample table 열이 5개라고 한다면 INSERT로 행 추가 시 5개 데이터를 넣어주어야 한다.

 

27강 제약

NOT NULL

기본키 (Primary Key) 제약

외부참조(정합) 제약

    -> 자식 테이블 측에서는 외부키(FOREIGN KEY) 를 지정해 부모 테이블을 참조한다.

    -> 부모 테이블에서 참조될 열은 반드시 유일성 (UNIQUE KEY, PRIMARY KEY) 를 가진다.

 

[학생]

학번(pk) 이름 학년
1 홍길동 3
2 김철수 2

학번은 참조되는 키이자 기본키이다.

 

[수강]

학번(fk) 과목명
1 알고리즘
2 컴퓨터구조

학번은 참조하는 키이자 외래키이다.

 

학번 이름 학년 과목명
1 홍길동 3 알고리즘
2 김철수 2 컴퓨터구조

 

 

  • 테이블 작성시 제약 정의

테이블 열에 제약 정의

 

테이블에 '테이블 제약' 정의

 

복수의 열에 제약을 설명하는 경우 테이블 제약이라 부른다.

제약에는 이름을 붙일 수 있다.

    -> CONSTRAINT 키워드를 사용해서 지정한다.

    -> 제약에 이름을 붙이면 관리가 쉬워진다.

 

'테이블 제약' 에 이름 붙이기

 

  • 제약 추가
  • 열 제약 추가

  • 테이블 제약 추가

기본키 제약 추가

 

  • 제약 삭제

c 열의 NOT NULL 제약 삭제
mysql 에서 테이블 제약 삭제

 

  • 기본키

sample634 테이블 작성

기본키로 지정할 열은 NOT NULL 제약이 설정되어 있어야 한다.

기본키는 테이블의 행 한 개를 특정할 수 있는 검색키이다.

    -> 기본키로 설정된 열이 중복하는 데이터 값을 가지면 제약에 위반된다. (식별이 불가능해지기 때문)

 

p 열의 값이 각각 1, 2, 3 으로 중복하지 않는다.

 

중복하는 행 추가

p 열의 값이 2로 중복되므로 '기본키 제약에 위반된다' 는 내용의 에러가 표시된다.

 

중복된 값으로 갱신

p 열의 값이 3인 행의 p 값을 2로 갱신하려고 하면 p=2 인 열이 두 개가 존재하기 때문에 에러가 발생한다.

 

* 유일한 값을 가지도록 하는 구조가 기본키 제약이다.

 

 

  • 복수의 열로 기본키 구성하기

기본키를 구성하는 열은 복수일 수 있다.

 

a열만 보면 중복하는 값이 있지만, b 열이 다르면 키 전체로는 중복되지 않기 때문에 기본키 제약에 위반되지 않는다.

예를 들어 사원 테이블에서 이름은 동명이인이 있을 수 있지만, 이름+생일로는 사원 식별이 가능하다고 할 때 사원+생일을 기본키로 지정해서 사용할 수 있다.

 

28강 인덱스 구조

인덱스는 데이터베이스 객체 중 하나이다.

 

no a
2 ABC
1 DEF
100 GHI
no
1
2
3
4

 

  • 인덱스

인덱스는 테이블에 붙여진 색인이다.

인덱스의 역할은 검색속도의 향상이다.

    -> '검색' 이란 SELECT 명령에 WHERE 구로 조건을 지정하고 그에 일치하는 행을 찾는 과정이다.

    -> WHERE 조건이 지정된 SELECT 명령의 처리 속도가 향상

인덱스에는 검색 시에 쓰이는 키워드와 대응하는 데이터 행의 장소가 저장되어 있다.

인덱스는 테이블과 별개로 독립된 데이터베이스 객체로 작성된다.

    -> 인덱스만으로는 아무런 의미가 없으므로, 테이블에 의존하는 객체이다.

 

  • 검색에 사용하는 알고리즘
  • 이진 트리

인덱스에 쓰이는 대표적인 검색 알고리즘

탐색 방법으로는 '이진탐색(binary search)'가 된다.

    -> 이진탐색에서 검색하기 쉬운 구조로 한 것이 이진 트리

 

 

    1. 풀 테이블 스캔

 

인덱스가 지정되지 않은 테이블을 검색할 때 사용하는 방법

테이블에 저장된 모든 값을 처음부터 차례로 조사해나간다. (행이 1000건 있다면 최대 1000번 값을 비교)

 

    2. 이진 탐색

 

차례로 나열된 집합에 대해 유효한 검색 방법

처음부터 순서대로 조사하지 않고 집합을 반으로 나누어 조사한다.

 

ex ) 20 을 찾는다.

1 2 3 5 10 11 19 20 23

                                                                                    ->    10<20 이므로 오른쪽에 존재

11 19 20 23

* array 가 짝수개라면 반올림, 올림, 버림을 이용하여 해결할 수 있다.

가운데 index 가 1.5 가 나오기 때문에 반올림이나 올림을 사용하면 20을 기준으로, 버림을 사용하면 19가 기준이 된다.

 

19가 기준이라면                   -> 19<20 이므로 오른쪽에 존재

20이 기준이라면 탐색 종료 (최소 2번 비교해 목표를 찾을 수 있다.)

 

 

    3. 이진 트리

 

이진 탐색은 데이터가 미리 정렬되어 있어야 한다.

테이블에 인덱스를 작성하면 테이블 데이터와 별개로 인덱스용 데이터가 저장장치에 만들어진다.

 

(출처 : 위키피디아)

 

 

트리는 노드(node) 라는 요소로 구성된다.

노드의 왼쪽 가지는 작은 값으로, 오른쪽 가지는 큰 값으로 나뉘어져 있다.

    -> 두 개의 가지로 분기하는 구조

 

ex ) 6을 찾는다.

 

8>6 이므로 왼쪽 가지로 이동 - 3<6 이므로 오른쪽 가지로 이동 - 6 발견, 탐색 종료

 

 

  • 유일성

이진 트리에서는 집합 내에 중복하는 값을 가질 수 없다.

키에 대하여 유일성을 가지도록 한다.

    -> 기본키 제약은 이진 트리로 인덱스를 작성하는 db 가 많다.

 

29강 인덱스 작성과 삭제

 

CREATE INDEX
DROP INDEX

 

인덱스는 데이터베이스 객체의 하나로 DDL 을 사용해서 작성하거나 삭제한다.

 

  • 인덱스 작성
CREATE INDEX 인덱스명 ON 테이블명 (열명1, 열명2, ... )

Oracle, DB2 등에서 인덱스는 스키마 객체가 된다.

    -> 스키마 내에 이름이 중복하지 않도록 관리

SQL Server 나 MySQL 에서 인덱스는 테이블 내의 객체가 된다.

    -> 테이블 내에 이름이 중복하지 않도록 지정해 관리

 

 

인덱스 작성

인덱스를 작성할 대는 해당 인덱스가 어느 테이블의 어느 열에 관한 것인지 지정할 필요가 있다.

    -> sample62 테이블의 no 열에 isample65라는 인덱스를 지정

 

  • 인덱스 삭제
DROP INDEX 인덱스명 (스키마 객체)
DROP INDEX 인덱스명 ON 테이블명 (테이블 내 객체)

인덱스는 테이블에 의존하는 객체이므로 DROP TABLE로 테이블을 삭제하면 테이블에 작성된 인덱스도 자동 삭제된다.

 

인덱스 삭제

* INSERT 명령의 경우 인덱스를 최신 상태로 갱신하는 처리가 늘어나므로 처리 속도가 조금 떨어진다.

 

 

  • EXPLAIN

인덱스 작성으로 쿼리의 성능 향상을 기대할 수 있다.

EXPLAIN SQL 명령

실제로 실행되는 것이 아니라, 어떤 상태로 실행되는지를 설명한다.

 

possible_keys 에는 사용될 수 있는 인덱스가 표시되며, key는 사용된 인덱스가 표시된다.

 

a열을 사용하지 않으므로 possible_keys 와 key가 NULL 이 된다.

 

  • 최적화

내부 처리에서는 SELECT 명령을 실행하기에 앞서 실행계획을 세운다.

    -> 인덱스가 지정된 열이 WHERE 조건으로 지정되어 있으니 인덱스를 사용한다

 

데이터의 종류가 적을수록 인덱스의 효율도 떨어진다. (단순한 리스트와 별 차이가 없어지기 때문)

서로 다른 값으로 여러 종류의 데이터가 존재하면 효율이 좋아진다.

 

30강 뷰 작성과 삭제

CREATE VIEW 뷰명 AS SELECT명령
DROP VIEW 뷰명

 

  • 뷰

데이터베이스 객체 중 하나이다.

객체로 등록할 수 없는 SELECT 명령을, 객체로서 이름을 붙여 관리할 수 있도록 한 것이 뷰이다.

뷰를 참조하면 SELECT 명령의 실행결과를 테이블처럼 사용할 수 있다.

서브쿼리 부분을 뷰로 대체하여 SELECT 명령을 간략하게 표현할 수 있다.

 

  • 가상 테이블

뷰는 테이블처럼 취급할 순 있지만 실체가 존재하지 않으므로 가상 테이블이라 불린다.

저장공간을 가지지 않는다.

    -> SELECT 명령에서만 사용하는 것을 권장한다.

 

  • 뷰 작성과 삭제

DDL로 작성하거나 삭제한다.

 

CREATE VIEW 뷰명 AS SELECT 명령

 

뷰 작성

CREATE VIEW 뷰명 (열명1, 열명2, ...) AS SELECT 명령

SELECT 명령의 모든 열을 사용할 경우에는 열을 지정하지 않는 편이 낫다.

열 이외에는 정의할 수 없고, 자료형이나 제약도 지정할 수 없다.

 

 

DROP VIEW 뷰명

 

  • 뷰의 약점

저장공간을 소비하지는 않지만 CPU 자원을 사용한다.

SELECT 명령 처리를 위해 계산능력을 필요로 하므로 컴퓨터의 CPU를 사용한다.

 

  • 머티리얼라이즈드 뷰(Merterialized View)

데이터를 일시적으로 저장해 사용하는 것이 아니라 테이블처럼 저장장치에 저장해두고 사용한다.

    -> 테이블에 데이터 양이 많은 경우 집계처리를 할 때 뷰가 사용된다면 처리속도가 떨어지는 것을 보완

매번 SELECT 명령을 실행할 필요가 없다.

데이터가 자주 변경되지 않는 경우라면 머티리얼라이즈드 뷰 사용으로 뷰의 약점을 보완할 수 있다.

    -> Oracle 과 DB2 에서만 사용할 수 있다.

 

  • 함수 테이블

뷰를 구성하는 SELECT 명령은 단독으로도 실행할 수 있어야 한다.

부모 쿼리와 연관된 서브쿼리의 경우 뷰의 SELECT 명령으로 사용할 수 없다.

    -> 함수 테이블을 사용하여 뷰의 약점 회피

    -> 함수 테이블은 테이블을 결괏값으로 반환해주기 때문