[SQL] 6. 데이터베이스 객체 작성과 객체

1. 데이터베이스 객체

1) 데이터베이스 객체

-데이터베이스 객체란, 테이블이나 뷰, 인덱스 등 데이터베이스 내에 정의하는 실체를 가지는 모든 것을 의미함.

-객체에 따라 데이터베이스에 저장되는 내용이 달라짐.

-SELECT나 INSERT 등은 클라이언트에서 객체를 조작하는 SQL 명령어이지, 객체가 아님. 

-객체에는 이름을 붙일 수 있고, 이름을 붙일 때 다음의 제약사항을 따름.

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

2) 스키마

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

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

-데이터베이스에 테이블을 작성해서 구축해나가는 작업을 '스키마 설계'라고 함. 

-테이블과 스키마는 무엇인가를 담는 그릇 역할을 한다는 점과 각각의 그릇 안에서는 중복하지 않도록 이름을 정한다는 점이 유사함. 

  →  이처럼 이름이 충돌하지 않도록 기능하는 그릇을 '네임스페이스'라고 부름.

2. 테이블 작성/삭제/변경

1) 테이블 작성

-SELECT, INSERT, DELETE, UPDATE는 SQL 명령 중에서도 DML(데이터 조작)로 분류되고, 스키마 내의 객체를 관리하는 명령은 DDL임.

-테이블을 작성할 때 CREATE TABLE 명령을 사용함.

  →  SYNTAX

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

 

 →  열 정의 SYNTAX

열명 자료형 [DEFAULT 기본값] [NULL|NOT NULL]

 

CREATE TABLE sample62(
 no INTEGER NOT NULL,
 a VARCHAR(30),
 b DATE);

 

2) 테이블 삭제

-테이블을 삭제할 떄에는 DROP TABLE 명령을 사용함.

-SYNTAX: DROP TABLE 테이블명

-많은 데이터베이스가 SQL 명령을 실행할 때 확인을 요구하지 않으므로 실수로 삭제하지 않도록 주의해야 함.

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

  →  이때, WHERE 조건을 지정하지 않으면 모든 행을 삭제할 수 있음.

-DELETE 명령은 행 단위로 여러가지 내부처리가 일어나므로 삭제할 행이 많으면 처리속도가 느림.

  →   TRUNCATE TABLE 명령을 사용하면 모든 행을 삭제해아 할 때 빠른 속도로 삭제 가능함.(DDL 명령, 삭제할 행/WHERE 구 지정 불가)

  →  SYNTAX: TRUNCATE TABLE 테이블명

 

3) 테이블 변경

-테이블을 작성한 뒤 열 구성을 변경하고 싶다면 ALTER TABLE 명령을 사용함.

-ALTER TABLE로 할 수 있는 일은 크게 두가지로 분류함.

• 열 추가/삭제/변경
• 제약 추가/삭제

-열 추가: ALTER TABLE에서 열을 추가할 떄는 ADD 하부명령을 통해 실행할 수 있음.

  →  SYNTAX: ALTER TABLE 테이블명 ADD 열 정의

ALTER TABLE sample62 ADD newcol INTEGER;

 

-열 속성 변경: MODIFY 하부명령을 실행. 열 이름은 변경할 수 없고, 자료형/기본값 등의 속성은 변경 가능함.

  →  SYNTAX: ALTER TABLE 테이블명 MODIFY 열 정의

ALTER TABLE sample62 MODIFY newcol VARCHAR(20);

 

-열 이름 변경: CHANGE 하부 명령. CHANGE는 열 이름뿐만 아니라 열 속성도 변경할 수 있음.

  → SYNTAX: ALTER TABLE 테이블명 CHANGE [기존 열 이름] [신규 열 정의] 

ALTER TABLE sample62 CHANGE newcol c VARCHAR(20);

 

-열 삭제: DROP 하부 명령.

  → SYNTAX: ALTER TABLE 테이블명 DROP 열명

ALTER TABLE sample62 DROP c;

 

4) ALTER TABLE 로 테이블 관리

-최대길이 연장: 문자열의 최대길이 연장

ALTER TABLE sample MODIFY col VARCHAR(30)

 

-열 추가: 테이블에 열을 추가

ALTER TABLE sample ADD new_col INTEGER

3. 제약

1) 테이블 작성시 제약 정의

-CREATE TABLE로 테이블을 작성할 떄 제약을 같이 정의함.

-열에 대해 정의하는 제약을 '열 제약'이라고 부르고, 한 개의 제약으로 복수의 열에 제약을 설명하는 경우를 '테이블 제약'이라고 부름.

CREATE TABLE sample631(
 a INTEGER NOT NULL,
 b INTEGER NOT NULL UNIQUE,
 c VARCHAR(30)
 );

 

-제약에 이름을 붙이면 나중에 관리하기가 쉬워지므로 CONTSTRAINT 키워드를 사용해서 이름을 지정해야 함.

CREATE TABLE sample632(
 a INTEGER NOT NULL,
 b INTEGER NOT NULL UNIQUE,
 c VARCHAR(30)
 CONSTRAINT pkey_sample PRIMARY KEY (no, sub_no)
 );

 

2) 제약 추가

-열 제약 추가: ALTER TABLE로 열 정의를 변경할 수 있음. 기존 테이블을 변경할 경우에는 제약을 위반하는 데이터가 있는지 먼저 검사함.

ALTER TABLE sample631 MODIFY c VARCHAR (30) NOT NULL;

 

  → 만약 c 열에 NULL 값이 존재한다면 에러가 발생함.

 

-테이블 제약 추가: ALTER TABLE의 ADD 하부 명령으로 추가할 수 있음. 열 제약을 추가할 때와 마찬가지로 기존의 행을 검사해 추가할 제약을 위반하는 데이터가 있으면 에러 발생함.

ALTER TABLE sample631 ADD CONSTRAINT pkey_sample631 PRIMARY KEY(a);

 

3) 제약 삭제

-열 제약 삭제하기: MODIFY 명령어 이용하여 열 정의를 변경함.

ALTER TABLE sample631 MODIFY c VARCHAR(30);

 

-테이블 제약 삭제하기: DROP 하부 명령으로 삭제 가능

ALTER TABLE sample631 DROP CONSTRAINT pkey_sample631;

-단, 기본키는 테이블당 하나만 설정할 수 있기 때문에 위처럼 굳이 제약명을 지정하지 않고도 삭제 가능함.

 

4) 기본키

-기본키 제약은 열을 기본키로 지정해 유일한 값을 가지도록 하는 구조임.

-기본키로 지정할 열은 NOT NULL 제약이 설정되어 있어야 하고, INSERT/UPDATE 명령을 실행할 때 중복되는 값을 추가/갱신하려 한다면 기본키 제약에 위반되므로 에러가 발생함.

CREATE TABLE sample634(
 p INTEGER NOT NULL,
 a VARCHAR NOT NULL,
 CONSTRAINT pkey_sample634 PRIMARY KEY(p)
 );

 

-기본키를 구성하는 열은 복수라도 상관없음. 복수의 열을 기본키로 지정했을 경우, 키를 구성하는 모든 열을 사용해서 중복하는 값이 있는지 없는지를 검사함.

4. 인덱스 구조

1) 인덱스

-인덱스는 테이블에 붙여진 색인이라고 할 수 있음.

-인덱스의 역할은 SELECT 명령에 WHERE 구로 조건을 지정하고 그에 일치하는 행을 찾는 검색 과정에서의 속도 향상임.

-인덱스는 테이블과는 별개의 독립된 데이터베이스 객체이지만, 테이블에 의존하는 객체라고 할 수 있음.

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

 

2) 검색에 사용하는 알고리즘

-풀 테이블 스캔: 인덱스가 지정되지 않은 테이블을 검색할 때 사용하는 방법. 테이블에 저장된 모든 값을 처음부터 차례로 조사해나가는 것.

-이진 탐색: 차례로 나열된 집합에 대해 처음부터 순서대로 조사하는 것이 아니고 집합을 반으로 나누어 조사하는 방법.

   → 대량의 데이터를 검색할 떄는 이진탐색이 훨씬 빠름.

-테이블에 인덱스를 작성하면 테이블 데이터와 별개로 인덱스용 데이터가 저장장치에 만들어지는데, 이때 이진트리라는 데이터 구조로 작성됨.

 

3) 유일성

-이진 트리는 중복된 값을 가지지 않는 구조임.

-기본키 제약은 이진 트리로 인덱스를 작성하는 데이터베이스가 많음.

5. 인덱스 작성과 삭제 

1) 인덱스 작성

-CREATE INTDEX 명령으로 인덱스를 작성함.

-인덱스가 데이터베이스 객체가 될지 테이블의 열처럼 취급될지는 데이터베이스 제품에 따라 다름.

  → Oracle, DB2: 스키마 객체 / SQL server, MySQL: 테이블 내의 객체

-SYNTAX: CREATE INDEX 인덱스명 ON 테이블명 (열명1, 열명2, ...)

CREATE INDEX isample65 ON sample62(no);

 

   → sample62 테이블의 no열에 isample65라는 인덱스를 지정함.

 

2) 인덱스 삭제

-인덱스는 DROP INDEX 명령으로 삭제함.

  → SYNTAX1(인덱스가 스키마 객체인 경우) : DROP INDEX 인덱스명

  → SYNTAX1(인덱스가 테이블 내 객체인 경우) : DROP INDEX 인덱스명 ON 테이블명

-인덱스는 테이블에 의존하는 객체이므로 테이블을 삭제하면 테이블에 작성된 인덱스도 자동으로 삭제됨. 인덱스만 삭제하는 경우에는 DROP INDEX 명령으로 삭제함.

CREATE INDEX isample65 ON sample62(a);

 

3) EXPLAIN

-EXPLAIN 명령은 SQL 명령이 어떤 상태로 실행되는지 설명해주는 명령어임.

  → SYNTAX: EXPLAIN SQL 명령

-다만 이 SQL 명령은 실제로는 실행되지 않고 어떻게 실행되는지를 설명해줄 뿐임.

-EXPLAIN은 표준 SQL 에는 존재하지 않는 데이터베이스 의존형 명령임.

EXPLAIN SELECT * FROM sample62 WHERE a = 'a';

 

id	select_type	table	type	possible_keys	key
1	simple	sample62	ref	isample65	ismple65

 

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

 

4) 최적화

-SELECT 명령을 실행할 떄 인덱스의 사용 여부를 선택하는 것은 데이터베이스 내부의 최적화에 의해 처리되는 부분임.

-내부처리에서는 명령을 실행하기 앞서 실행계획을 세우는데, 이 실행계획을 확인하는 명령이 EXPLAIN 명령임.

-실행 계획에서는 인덱스의 유무, 인덱스를 사용할 것인지 여부에 대해 데이터베이스 내부 최적화 처리를 통해 판단. 이때 판단 기준으로 인덱스의 품질도 고려함.

6. 뷰 작성과 삭제

1) 뷰

-뷰란 본래 데이터베이스 객체로 등록할 수 없는 SELECT 명령을 객체로서 이름을 붙여 관리할 수 있도록 한 것.

-뷰를 참조하면 그에 정의된 SELECT 명령의 실행결과를 테이블처럼 사용가능함.

-뷰를 작성하는 것으로 복잡한 SELECT 명령을 간략하게 표현할 수 있음.

-뷰는 테이블처럼 취급하지만 '실체가 존재하지 않는다'는 의미에서 '가상 테이블'이라고 불리기도 함.

  → 테이블처럼 데이터를 쓰고 지울 수 있는 저장공간을 가지지 않기 때문에 SELECT 명령에서만 사용하는 것을 권장함.

 

2) 뷰 작성과 삭제

-뷰의 작성: CREATE VIEW로 뷰의 이름과 SELECT 명령을 지정함.

  → SYNTAX(뷰 작성): CREATE VIEW 뷰명 AS SELECT 명령

CREATE VIEW sample_view_67 AS SELECT * FROM sample54;

SELECT * FROM sample_view_67

 

  → SYNTAX(열 지정하기): CREATE VIEW 뷰명 (열병1, 열명2, ...) AS SELECT 명령

-뷰의 열 지정을 생략한 경우에는 SELECT 명령의 SELECT 구에서 지정하는 열 정보가 수집되어 자동적으로 뷰의 열로 지정됨.

-뷰 삭제: DROP VIEW 이용

  → SYNTAX: DROP VIEW 뷰명

 

3) 뷰의 약점

-뷰는 대량의 저장공간을 필요로 하지 않는 대신, 계산능력을 필요로 하기 때문에 CPU 자원을 사용함.

-뷰를 참조하면 뷰에 등록되어 있는 SELECT 명령이 실행되고, 실행결과는 일시적으로 보존되며 뷰를 참조할 때마다 SELECT 명령이 실행됨.

-뷰의 약점

• 테이블에 보관하는 데이터 양이 많은 경우, 집계처리를 할 때 뷰가 사용되거나 뷰를 중첩해서 사용하는 경우 처리 속도가 저하됨.

-> 뷰에 지정된 테이블의 데이터가 자주 변경되지 않는 경우라면, 머티리얼라이즈드 뷰로 약점 보완 가능. (머티리얼라이즈드 뷰: 처음 참조되었을 때 데이터를 저장해두고 다시 참조할 떄 이전에 저장해 두었던 데이터를 사용하는 뷰)

• 부모 쿼리와 어떤 식으로든 연관된 서브쿼리의 경우, 뷰의 SELECT 명령으로 사용할 수 없음.(뷰를 구성하는 SELECT 명령은 단독으로도 실행할 수 있어야 하므로)

-> 함수테이블을 사용하여 보완가능함. (함수 테이블: 테이블을 결괏값으로 반환해주는 사용자 정의 함수. 함수는 인수를 지정할 수 있기 때문에 인수의 값에 따라 WHERE 조건을 붙여 결괏값을 바꿀 수 있음.)