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

📌 목차

• 25강 데이터베이스 객체
• 26강 테이블 작성ㆍ삭제ㆍ변경
• 27강 제약
• 28강 인덱스 구조
• 29강 인덱스 작성과 삭제
• 30강 뷰

KEYWORD : 데이터베이스 객체 작성, '인덱스'구조

25강 - 데이터베이스 객체

데이터베이스 객체 : 테이블, 뷰, 인덱스 등 데이터베이스 내에 정의하는 모든 것

= 데이터베이스 내에 실체를 가지는 어떤 것

 

데이터베이스 내에서 객체를 작성할 때에는 이름을 겹치게 작성 불가능하다. 

왜? 어떤 데이터가 저장되어 있는지 파악하는 기준이 되는 경우가 多

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

이름은 객체의 종류와 관계없다.(테이블 명이 foo? => 테이블, 뷰 등 다른 객체에서 foo라는 이름 아예 사용불가)

 

스키마

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

객체의 이름이 같더라도 스키마가 다르면 무관하다. so called 스키마 객체

스키마설계 : 데이터베이스에 테이블작성해서 구축하는 작업 

스키마는 SQL명령의 DDL을 이용하여 정의한다. 

 

데이터베이스 제품에 따라 어떤 것이 스키마가 되는지 달라진다.

• MySQL : CREATE DATABASE 명령으로 작성한 데이터베이스가 스키마가 된다.
• Oracle : 데이터베이스와 데이터베이스 사용자가 계층적 스키마가 된다.

이름이 충돌하지 않도록 기능하는 그릇을 '네임스페이스(namespace) 라고 부른다.

→ 스키마나 테이블은 네임스페이스이기도 하다.

(C++ using namespace std도 기능적으론 같고, 표준 라이브러리의 이름을 생략 하기위한 목적으로 사용)

26강 - 테이블 작성·삭제·변경

SYNTAX

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

순서대로 작성, 삭제,변경

5장... DML 명령(데이터조작) -  SELECT, INSERT, DELETE, UPDATE 

 

 DDL : 데이터 정의 명령 WHEN USE? 스키나 내의 객체를 관리할 때

 

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

자료형 지정 시, CHAR, VARCHAR와 같은 문자열형으로 지정 시 , 최대 길이를 괄호로 묶어준다.

기본값 설정 시, DEFAULTE로 지정하되 자료형에 맞는 리터럴로 기술. (생략 가능)

NULL허용 할 것인지 지정. if 생략시, NULL 허용으로 간주. (생략 가능)

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

 

mysql>  CREATE TABLE sample62(
    -> no INTEGER NOT NULL,
    -> a VARCHAR(30),
    -> b DATE);
Query OK, 0 rows affected (0.04 sec)

mysql> DESC sample62;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| no    | int         | NO   |     | NULL    |       |
| a     | varchar(30) | YES  |     | NULL    |       |
| b     | date        | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

DESC 테이블명 : Describe의 약자로 생성된 테이블에 있는 컬럼이 어떤 구조로 되어있는지 확인 할 수 있는 명령어이다.

 

2. 테이블 삭제 -  DROP TABLE 테이블명

SQL명령을 실행 할 때, 정말 삭제하시겠습니까?라는 이중확인 없이 바로 삭제되므로 신중하게 진행한다.테이블 정의는 그대로 둔 채, 데이터만 삭제할 때에는 DELETE 명령 사용. WHERE 조건 미지정 시 모든 행 삭제

 

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

→ DDL (데이터 정의 명령)인 TRUNCATE TABLE 테이블명 사용 

    모든 행을 삭제해야할 때 사용

 

3. 테이블 변경 - ALTER TABLE 테이블명 변경명령

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

3.1 열추가

   CREATE TABLE의 경우와 동일하다. 즉, 열 이름과 자료형 지정 후 필요에 따라 기본값, NOT NULL 제약 지정. 열이름 중복 불가에 유의

ALTER TABLE 테이블명 ADD 열 정의

mysql> ALTER TABLE sample62 ADD newcol INTEGER;

mysql> DESC sample62;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| no     | int         | NO   |     | NULL    |       |
| a      | varchar(30) | YES  |     | NULL    |       |
| b      | date        | YES  |     | NULL    |       |
| newcol | int         | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+

열을 추가 할 때, 기존 데이터행이 존재하면 추가한 열의 값이 모두 NULL이 된다. 기본값이 지정되어 있으면 기본값으로 데이터가 저장된다. 기본값이 지정되어 있으면 기본값으로 데이터가 저장된다. NOT NULL 제약 추가하고싶다면, 먼저 제약 걸고 이후에  NULL이외의 값으로 기본값을 지정해야한다. (?? 먼말몰...)

 

3.2 열 속성 변경

ALTER TABLE 테이블명 MODIFY 열 정의

MODIFY로 자료형, 기본값, NOT NULL 제약 등의 속성 변경 가능. ( 열 이름 변경 불가  )

mysql> ALTER TABLE sample62 MODIFY newcol VARCHAR(20);

mysql> DESC sample62;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| no     | int         | NO   |     | NULL    |       |
| a      | varchar(30) | YES  |     | NULL    |       |
| b      | date        | YES  |     | NULL    |       |
| newcol | varchar(20) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+

기존의 데이터 행이 존재 시, 속성 변경에 따라 데이터 역시 반환된다. 자료형이 변경될 시, 테이블 속 데이터의 자료형 역시 변경된다. 처리과정에서 에러 발생 시 ALTER TABLE 명령은 실행되지 않음

 

3.3 열 이름 변경 

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

열 이름, 열 속성 두가지를 변경 가능하다. (Oracle에서는 RENAME TO 사용)

mysql> ALTER TABLE sample62 CHANGE newcol c VARCHAR(20);

mysql> DESC sample62;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| no    | int         | NO   |     | NULL    |       |
| a     | varchar(30) | YES  |     | NULL    |       |
| b     | date        | YES  |     | NULL    |       |
| c     | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

 

3.4 열 삭제

ALTER TABLE 테이블명 DROP 열명

mysql> ALTER TABLE sample62 DROP c;

mysql> DESC sample62;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| no    | int         | NO   |     | NULL    |       |
| a     | varchar(30) | YES  |     | NULL    |       |
| b     | date        | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

 

4. ALTER TABLE로 테이블 관리

- 최대길이 연장

대규모 데이터베이스에서는 데이터 크기가 매우 커지는 경향이 있다. 행 개수가 많은 테이블에서는 데이터 하나의 크기를 최적화 하는것으로 저장공간 절약 가능. 한 자리로 충분했던 용량이 시간이 지나면서 부족해지는 경우가 있는데 열의 자료형을 변경해 대응할 수 있다. 문자열형의 경우 최대길이를 늘릴 수 있다.

↔ 저장공간을 늘리기위해, 최대 길이를 줄이고 싶은 경우도 있다. 기존의 행에 존재하는 데이터의 길이보다 작게 지정할 수 없으며, 최대 길이를 줄인다고 해서 일제 저장공간이 늘어나는 경우도 적다. 

→일반적으로 최대길이를 늘리는 경우는 많으나 줄이는 경우는 없음.

 

- 열 추가

열을 추가하는 것은 테이블의 정의가 바뀌어 버리는 일이라, 꽤 영향이 크다.

변경한 테이블에 행을 추가하는 INSERT명령은 꼭 확인해야함. 열을 추가하면 해당 열에 대해 데이터 값을 지정해야되기 때문. 기존 시스템에서 추가한 열에 대해서는 별다른 처리를 하지 않아도 문제 없다해도, 확인 꼭 하기. 만일, 기존시스템의 INSERT명령에서 열 지정이 생략되어있다면, 열 추가후 그대로 실행했을 때 열의 개수가 맞지 않아 에러가 발생한다.

(=관련예시는머가잇으까)

 

mysql> INSERT INTO sample62 (no,a,new_col)  VALUES(1,'시은',31);

mysql> SELECT * FROM sample62;
+----+------+------+---------+
| no | a    | b    | new_col |
+----+------+------+---------+
|  1 | 시은 | NULL |      31 |
+----+------+------+---------+
1 row in set (0.00 sec)

 

27강 -  제약

NOT NULL , 기본키(Primary key) , 외부참조(정합) 등

 

테이블 작성 시, 제약을 정의한다

(NOT NULL, UNIQUE 제약 사용 예시)

열 제약 : 열에 정의하는 제약 

 

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

mysql> DESC sample631;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| a     | int         | NO   |     | NULL    |       |
| b     | int         | NO   | PRI | NULL    |       |
| c     | varchar(30) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

테이블 제약 : 한 개의 제약으로 복수의 열에 제약을 설명하는 경우

mysql> CREATE TABLE sample632(
    -> no INTEGER NOT NULL,
    -> sub_no INTEGER NOT NULL,
    -> name VARCHAR(30),
    -> PRIMARY KEY(no, sub_no) //이게바로 테이블제약
    -> );


mysql> DESC sample632;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| no     | int         | NO   | PRI | NULL    |       |
| sub_no | int         | NO   | PRI | NULL    |       |
| name   | varchar(30) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+

 

제약 이름 붙이기 : CONSTRAINT

mysql> CREATE TABLE sample632(
    ->  no INTEGER NOT NULL,
    -> sub_no INTEGER NOT NULL,
    -> name VARCHAR(30),
    -> CONSTRAINT pkey_sample PRIMARY KEY(no, sub_no) // 이름붙이기
    -> );

 

제약 추가

기존 테이블에 나중에 제약 추가하기. 열 제약과 테이블 제약은 다른 방법

- 열 제약 추가

ALTER TABLE로 열 정의 변경

1) 변경 전, 제약 위반 데이터 유무 검사

2) 만일, 해당 열에 NULL 값 존재 시, ALTER TABLE명령 에러 발생. 

ALTER TABLE sample631 MODIFY c VARCHAR(30) NOTNULL;

- 테이블 제약 추가

ALTER TABLE 테이블명 ADD

기본키는 테이블당 하나만 설정 가능하다. 기존 행 검사하며, 추가할 제약 위반 데이터 있으면 에러발생.

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

 

제약 삭제

열 제약은 추가할 때와 동일하게 열 정의를 변경하여 삭제 가능.

ALTER TABLE sample631 MODIFY c VARCHAR(30) ; // NOT NULL제약삭제하기

테이블 제약은 제약명 지정하여 삭제 가능. 

ALTER TABLE 테이블명 DROP CONSTRAINT 이름;

ALTER TABLE sample631 DROP PRIMARY KEY;
//테이블제약삭제, 기본키는 테이블당 하나만 설정이므로 이렇게

 

기본키

PRIMARY KEY(열명) 

테이블 행 한 개를 특정할 수 있는 검색키. 지정할 열은 NOT NULL 제약 설정되어있어야 한다.

기본키 제약이 설정된 열은 중복데이터 존재 불가능하다는 제약이다. so called 유일성 제약

 

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

기본키를 구성하는 열은 복수여도 가능하다.

a	b
1	1
1	2
1	3

a,b열을 기본키 구성 열로 지정했을 때 해당 상태는 기본키 제약에 위반되지 않는다.

 

28강 - 인덱스 구조

인덱스 called 색인 은 데이터베이스 객체 중 하나 (테이블과 독립된, but 테이블에 의존하는)

 

검색에 사용하는 알고리즘

데이터 탐색, 검색 알고리즘으로 대량의 데이터를 효율적으로 검색한다. (검색알고리즘 예) 이진 트리(binary tree), 해시

 

이진 탐색(binary search) 에서 검색하기 쉬운 구조로 한 것이 이진 트리이다. (탐색이라기보단, 데이터 구조에 가까움)

- 풀 테이블 스캔(full table scan)

인덱스가 지정되지 않은 테이블을 검색할 때

테이블 내 저장된 모든 값을 처음부터 차례로 조사해나감. 

- 이진 탐색(binary search) / 대량의 데이터를 검색할 때에는 이진탐색이 빠르다!

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

처음부터 조사하는것이아닌, 집합ㅂ을 반으로 나누어 조사해나감 (=Merge sort랑 유사)

- 이진 트리(binary tree)

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

트리는 노드(node)라는 요소로 구성된다. 노드는 두개의 가지로 나뉜다. 노드의 왼쪽 가지는 작은 값, 오른쪽 가지는 큰 값으로 나뉜다. 두개의 가지로 분기하는 구조라 이진 트리라고 불린다. 트리 가장 위를 루트 노드라고 부른다. 노드의 가지는 같은 값을 가질 수 없어, 작거나(왼) 큰 (오른) 가지만 가진다. 같다면 제 3의 가지가 필요...

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

**이진 트리에는 중복하는 값을 등록할 수 없다.

 

29강 - 인덱스 작성과 삭제

테이블에서 인덱스를 작성하는 방법

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

DDL을 사용하여 작성/삭제한다.  표준SQL명령에는 CREATE INDEX명령은 없으나, 인덱스 자체가 데이터베이스제품에 의존하는 항목으로취급됨.

INDEX는,,,

Oracle ,DB2 →스키마 객체가 된다 | 스키마 내에 이름 중복되지 않도록 지정

SQL Server, MySQL →테이블 내의 객체가 된다 | 테이블 내에 이름 중복되지 않도록 지정

 

인덱스 열 복수 지정가능. 인덱스의 네임스페이스가 데이터베이스제품마다 다르자는점 주의!

 

 

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

인덱스를 작성할 때, 저장장치에 색인용 데이터가 만들어진다. 테이블 크기에 따라 시간, 저장공간 많이 소비

mysql> CREATE INDEX issample65 ON sample62(no);

 

INDEX 삭제

DROP INDEX  인덱스명 ON 테이블명 //  열은 지정할 필요 없음.

DROP INDEX issample65 ON sample62;

mysql> DROP INDEX issample65 ON sample62;

 

작성한 인덱스의 열을 WHERE 구로 지정하여 SELECT 명령으로 검색하면 처리속도 향상 가능

mysql> CREATE INDEX issample65 ON sample62(a);

SELECT * sample62 WHERE a = 'a';

WHERE 구에 a 얄에대한 조건식 지정한 경우, SELECT 멸영은 인덱스 사용해 빠르게 검색 가능. 

(만일, WHERE구의 조건식에 a열이 전혀 사용되지 않으면 SELECT명령은 issample62라는 인덱스 사용 불가.)

 

EXPLAIN

EXPLAIN SQL명령

EXPLAIN 뒤에 SELECT명령 등의  SQL 명령을 넣어주면 됨. 그러나 이때  SQL 명령은실제로 실행되지 않거, 어떤 상태로실행되는지를 데이터베이스가 설명해준다. (표준SQL X, 데이터베이스제품 의존)

 

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

 

WHERE 조건을 변경 시(a열을 사용하지않도록) ... 인덱스 사용 불가. 실제로 NULL 표기된것확인가능

 

최적화

인덱스의 품질을 고려해실행 계획 세워진다.

 

30강 - 뷰 작성과 삭제

뷰 : 테이블과 같은 데이터베이스 객체 중 하나

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

서브쿼리는 FROM구에서도 기술이 가능했다. 이  FROM구에 기술된 서브쿼리에 이름을 붙이고, 데이터베이스 객체화하여 쓰기 쉽게한 것을 뷰라고 한다.

 

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

뷰를 참조 시, SELECT명령의 실행결과를 테이블처럼 사용 가능

 

왜 사용? 복잡한 WHERE구로조건지정, GROUBBY집계 등의 명령. => 서브쿼리 부분을 뷰로 대체해 SELECT명령 간략화.

자주 사용하거나 복잡한SELECT명령은 뷰로 만들어 사용

 

-가상 테이블

뷰는 테이블 처럼 취급할 수 있으나, 실체가 존재하지 않는다 라는 의미로 가상 테이블라고 불림. 뷰는 저장공간을 가지지 않는다.( 테이블처럼 데이터 쓰거나 지울 수 있는) 테이블처럼 취급가능하다해도, SELECT명령에서만 사용을 권장함.

 

뷰 작성과 삭제

DDL로 작성 및 삭제한다. 작성 시 CREATE VIEW, 삭제 시 DROP VIEW 적용

생성 시, AS키워드는 생략 불가능하다. (별명과 기능이 다름) AS키워드 뒤에 SELECT명령 지정 필수

 

mysql> CREATE VIEW sample_view_67 AS SELECT * FROM sample54;

mysql> SELECT * FROM sample_view_67;
+------+------+
| no   | a    |
+------+------+
|    1 |  100 |
|    2 |  900 |
|    4 |   80 |
+------+------+

필요에 따라, 뷰는 열 지정도 가능.

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

열 지정 생략 시, SELECT명령의 SELECT 구에서 지정하는 열 정보가 수집되어 자동적으로 뷰의 열로 지정함. 

열 이외에는 지정할 수 없으며, 제약도 지정 불가.

mysql> CREATE VIEW sample_view_672(n,v,v2) AS SELECT no,a,a*2 FROM sample54;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM sample_view_672 WHERE n=1;
+------+------+------+
| n    | v    | v2   |
+------+------+------+
|    1 |  100 |  200 |
+------+------+------+

 

뷰 삭제

DROP VIEW sample_view_67;

 

 

뷰의 약점

뷰는 저장공간을 사용하지 않는 대신, CPU 자원을 사용한다. SELECT의 여러가지 명령은 계산 능력을 요하기에, 컴퓨터의 CPU를 사용. 뷰를 참조 시 뷰에 등록된 SELECT명령이 실행된다.

이 실행결과는 일시적으로 보존되며, 참조할 때마다 SELECT명령 실행된다.

 

- 머티미얼라이즈드 뷰(Materialized View)

테이블에 보관 데이터양이 많은 경우, 집계처리할 때도 뷰가 사용되거나 중첩 뷰 사용 시, 처리속도가 많이 떨어짐.

이를 회피하기 위해 머티미얼라이즈드 뷰(Materialized View) 사용

뷰	머티미얼라이즈드 뷰(Materialized View)
데이터 일시적 저장	테이블에 저장
쿼리 실행 종료 후, 삭제

다만, 뷰에 지정된 테이블의 데이터가 변경된 경우에는 SELECT 명령을 재실행해 데이터를 다시 저장해야한다. 

변경유무확인해 재실행 하는건 RDBMS가 자동으로 실행한다.

MySQL에서는 사용불가 Oracle, DB2만 가능

- 함수 테이블

뷰를 구성하는 SELECT명령은 단독으로도 실행 가능해야함.

부모쿼리와 연관된 서브쿼리의 경우, 뷰의 SELECT명령으로 사용 불가능 (link 상관 서브쿼리)

=> 이를 회피하기 위해 함수테이블사용!

함수테이블: 테이블을 결괏값으로 반환해주는 사용자정의 함수

함수에는 인수 지정 가능 -> 인수 값 따라 WHERE조건 붙여 결괏값 변경 가능 (상관서브쿼리처럼 동작)