[필기정리] Day62 - primary key, unique key, index, view 등

CREATE TABLE로 테이블을 정의할 경우, 열 이외에 제약도 정의할 수 있다.
테이블에 제약을 설정함으로써 저장될 데이터를 제한할 수 있다. 

 

ex)

- NOT NULL

- 기본키(Primary Key) 제약 

- 외부참조제약 : 복수의 테이블 사이에서 정합성을 유지하기 위해 설정하는 것

 

테이블 간에는 부모 자식과 같은 관계를 가지며 정합성을 유지한다.

- 자식 테이블 : 외부키(FOREIGN KEY)

- 부모 테이블 : 유일성(UNIQUE KEY, PRIMARY KEY)

//Tip - 데이터 정합성 : 어떤 데이터들이 서로 모순 없이 일관되게 일치해야 함
          ≒ 무결성 : 데이터가 정확하고 완전해야 함 

# 테이블 작성 시 제약 정의

CREATE TABLE로 테이블을 작성할 때 제약을 같이 정의한다. 

물론 ALTER TABLE로 제약을 지정하거나 변경할 수 있다.

- 열 제약 : 하나의 열에 대해 설정하는 제약
ex)

CREATE TABLE sample631( 
a INTEGER NOT NULL, 
b INTEGER NOT NULL, # b INTEGER NOT NULL UNIQUE = 속성이 PRIMARY KEY가 된다. 
c VARCHAR(30) 
);
desc sample631;

a 열 : NOT NULL 제약 

b 열 : NOT NULL 제약, UNIQUE 제약

 

- 테이블 제약 : 테이블 단위로 설정하는 제약

ⓐ PRIMARY KEY
★ 특징
① 값이 중복되지 않는다.
② 반드시 값을 입력해야 한다.
③ 테이블 데이터의 고유 인식번호(ID)

ⓑ UNIQUE KEY(고유 키)
★ 특징
① 값이 중복되지 않는다.
② 값을 입력하지 않아도 된다.  // 기본 키와 가장 다른 차이점, 기본 키는 null 허용 안함
③ 중복되면 안되는 테이터 ex) 주민등록번호, 군번 등 

- 테이블에 '테이블 제약' 정의하기

ex)

CREATE TABLE sample632( 
no INTEGER NOT NULL, 
sub_no INTEGER NOT NULL, # 열 제약
name VARCHAR(30), 
PRIMARY KEY(no, sub_no) # 테이블 제약
); 

CREATE TABLE sample633( 
no INTEGER NOT NULL, 
sub_no INTEGER NOT NULL, 
name VARCHAR(30), 
PRIMARY KEY(no, sub_no) 
); 

# 제약 추가 : 기존 테이블에 제약을 추가하는 방법

 

- 열 제약 추가
열 제약을 추가할 경우 먼저 해당 열에 제약 위반 데이터의 여부를 검사한다.

제약 위반 데이터가 존재할 경우 에러가 발생한다. 

ex) c열에 NOT NULL 제약 걸기

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

- 테이블 제약 추가 

테이블 제약 추가 시 동일한 제약이 존재하는 경우 에러가 발생한다.

열 제약처럼 제약 조건 위반 데이터의 경우 마찬가지로 에러가 발생한다.

 

ex)

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

 

# 테이블의 제약 삭제하기

- 열 제약 삭제하기
ex) c열의 NOT NULL 제약 없애기

ALTER TABLE sample631 MODIFY c VARCHAR(30); 
DESC sample631;

 

- 테이블 제약 삭제하기 

① 제약키 조회

SELECT constraint_name, constraint_type FROM information_schema.table_constraints WHERE table_name = 'sample631'; 

② 제약 조건 삭제 (DROP)

ALTER TABLE sample631 DROP PRIMARY KEY; 

 

# 기본키 제약 조건 ( = 유일성 제약 )

  기본키로 설정된 열이 중복하는 데이터 값을 가지면 제약 위반이다.

ex)

CREATE TABLE sample634( 
p INTEGER NOT NULL,  
a VARCHAR(30), 
PRIMARY KEY(p)  # 기본키
); 

INSERT INTO sample634 VALUES(1, '첫째줄'); 
INSERT INTO sample634 VALUES(2, '둘째줄'); 
INSERT INTO sample634 VALUES(3, '셋째줄'); 

 

ex) sample634에 중복되는 행 추가하기

INSERT INTO sample634 VALUES(2, '넷째행'); # ERROR 

ex) sample634를 중복된 값으로 갱신하기

UPDATE sample634 SET p=2 WHERE p=3; # ERROR 

- 복수의 열로 기본키 지정하기

ex)

CREATE TABLE sample635( 
a int, 
b int, 
CONSTRAINT PRIMARY KEY(a, b)
); 
INSERT INTO sample635 values(1, 1); 
INSERT INTO sample635 values(1, 2); 
INSERT INTO sample635 values(1, 3); 
INSERT INTO sample635 values(2, 1); 
INSERT INTO sample635 values(2, 2); 

기본 키를 구성하는 열은 복수라도 상관없다. 

복수의 열을 기본키로 지정했을 경우, 

키를 구성하는 모든 열을 사용해서 중복하는 값이 있는지 없는지를 검사한다.
a와 b열 모두 중복되어야 중복 에러가 발생한다.

 

# 인덱스 구조  
- 인덱스 : 테이블에 붙여진 색인

   ㄴ 역할 : 검색속도의 향상

               검색 시에 쓰이는 키워드와 대응하는 데이터 행의 장소가 저장되어 있다.
        특징 : 독립된 객체이나 테이블에 의존한다.

                 테이블을 삭제하면 인덱스도 삭제된다.

- 검색에 사용하는 알고리즘
① 이진탐색 

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

    집합을 반으로 나누어 조사한다.

    데이터가 미리 정렬되어 있어야 한다

② 해시
③ 풀 테이블 스캔(full table scan)  // 순차검색
   인덱스가 지정되지 않은 테이블을 검색할 때 사용

   테이블에 저장된 모든 값을 처음부터 차례로 조사해 나가는 방법

// Tip : 이진 트리(binary tree) 

          인덱스 생성 시 테이블 데이터의 구조 중 하나

          집합 내 중복 값을 가질 수 없으므로 이진 탐색에 활용된다.

 

# 인덱스 작성과 삭제

CREATE INDEX 
DROP INDEX 

ex)

CREATE TABLE sample62(no int, a varchar(10)); 
INSERT INTO sample62 VALUES(2, 'ABC'); 
INSERT INTO sample62 VALUES(1, 'DEF'); 
INSERT INTO sample62 VALUES(100, 'GHI'); 
INSERT INTO sample62 VALUES(3, 'JKL'); 
INSERT INTO sample62 VALUES(101, 'MNO');

 

// Tip : DB별 관리 방법 

① Oracle, DB2 등 : 스키마 객체, 스키마 내에 이름이 중복되지 않도록 지정해 관리 

② SQL Server, MySQL : 테이블 내 객체, 테이블 내에 이름이 중복되지 않도록 지정해 관리 

 

- 인덱스 작성

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

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

이 때 열은 복수로도 지정할 수 있다.

 

테이블 크기에 따라 인덱스 작성시간도 달라지는데,

행이 대량으로 존재하면 시간도 많이 걸리고 저장공간도 많이 소비한다. 

 

// Tip : INSERT의 경우에는 인덱스를 최신상태로 갱신하는 처리가 늘어나 속도가 떨어진다. 

 

ex) sample62 테이블의 no 열에 isammple65라는 인덱스를 지정

CREATE INDEX isample62 ON sample62(no);

ex) 인덱스 보기

SHOW INDEX FROM sample62; # SHOW INDEX FROM sample62\G;

 

- 인덱스 삭제

DROP INDEX 인덱스명 ON 테이블명 # 테이블 내 객체의 경우

DROP TABLE로 테이블을 삭제하면 테이블에 작성된 인덱스도 자동으로 삭제된다. 

인덱스만 삭제하는 경우에는 DROP INDEX를 사용한다.

ex) 

DROP INDEX isample62 ON sample62;

ex) a열에 대한 조건식 지정

CREATE INDEX isample62 ON sample62(a);

SELECT * FROM sample62 WHERE a = 'a';

# EXPLAIN : SQL 성능 확인

EXPLAIN SQL 명령

- possible_keys : 사용될 수 있는 인덱스

- key : 사용된 인덱스

 

데이터베이스의 내용이 아닌 성능 정보만 제공한다.

EXPLAIN은 표준 SQL에는 존재하지 않는, 데이터베이스 제품 의존형 명령이다. 

하지만 어떤 데이터베이스 제품이라도 이와 비슷한 명령을 지원한다.

 

ex) EXPLAIN으로 인덱스 사용 확인하기

EXPLAIN SELECT * FROM sample62 WHERE a = 'a'; # 인덱스 사용

 

EXPLAIN SELECT * FROM sample62 WHERE no > 10; # 인덱스 미사용 - possible_keys와 key가 NULL 

// Tip - 최적화 : SQL의 성능을 비교하여 속도, 퍼포먼스가 뛰어난 SQL을 사용하는 것

 

# VIEW : 논리적 테이블

            SELECT 명령을 기록하는 데이터베이스 객체 

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

 

 

ex)

CREATE VIEW sample_view_54 AS SELECT * FROM sample54; 

SELECT * FROM sample_view_54;

 

// Tip
뷰는 저장공간을 가지지 않는 가상 테이블이다.

INSERT나 UPDATE, DELETE 명령에서도 조건이 맞으면 가능하지만 사용에 주의할 필요가 있다.


- 뷰의 작성

CREATE VIEW 뷰명 AS SELECT 명령

ex)

CREATE VIEW sample_view_54 AS SELECT * FROM sample54; 
SELECT * FROM sample_view_54; 

 

- CREATE VIEW에서 열 지정하기

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

뷰의 열 지정을 생략한 경우에는 자동적으로 뷰의 열로 지정된다. 

반대로 열을 지정한 경우에는 SELECT 명령의 SELECT구에 지정한 열보다 우선된다. 

열 이외에는 정의할 수 없으며, 테이블의 열을 정의할 때처럼 자료형이나 제약도 지정할 수 없다.

ex) 열을 지정해 VIEW 작성하기

CREATE VIEW sample_view_541(n, v, v2) AS 
SELECT no, a, a*2 FROM sample54; 
SELECT * FROM sample_view_541 WHERE n = 1;

CREATE VIEW sample_view_542 AS SELECT no, a FROM sample54; 
SELECT * FROM sample_view_542;

- VIEW 삭제

DROP VIEW 뷰명

ex)

DROP VIEW sample_view_54;

- VIEW의 단점 : 저장공간을 소비하지 않는 대신 CPU 자원을 사용한다.

 

//Tip - Materialized View (Oracle, DB2에서만 사용) // VIEW의 보완책

         VIEW이나 실제로 데이터를 저장하여 CPU 자원을 사용하지 않아 처리 속도에 영향을 주지 않는 방법

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

         함수에는 인수를 지정할 수 있기 때문에 인수의 값에 따라

         WHERE 조건을 붙여 결과값을 바꿀 수 있다.

 

- VIEW 조회

SHOW FULL TABLES IN haksa_database WHERE TABLE_TYPE LIKE 'VIEW';

# 연습문제
Q1. 테이블을 작성할 때 사용하는 명령은 무엇인가?
① ALTER TABLE
② CREATE TABLE
③ CREATE VIEW
A. ② CREATE TABLE : 테이블을 작성할 때는 CREATE TABLE 명령을 사용한다.

Q2. 저장장치의 저장공간을 거의 사용하지 않는 객체는 무엇인가?
① 테이블
② 인덱스
③ 뷰
A. ③ 뷰 : 뷰는 SELECT 명령을 따로 기입해 관리할 수 있게 한 것으로, 

            테이블과 달리 데이터를 일시적으로 지정해 사용하는 방식을 취한다.

Q3. 인덱스를 사용하면 어떤 효과를 얻을 수 있나?
① 쿼리의 성능 향상
② 데이터의 정합성
③ 데이터베이스의 용량 절약
A. ① 쿼리의 성능 향상 : 인덱스를 사용해 WHERE 구의 조건식을 효율적으로 처리할 수 있다. 

                                이로 인해 쿼리의 성능향상을 기대할 수 있다.