[MySQL] 챕터 8. 테이블과 뷰

8.1 테이블

테이블은 데이터를 저장하는 기본적인 구조로, 행(row)과 열(column)의 집합으로 이루어져 있다. 테이블을 사용하면 정보를 구조화하여 데이터베이스에 저장할 수 있다.

 

8.1.1 테이블 만들기

SQL로 테이블 만들기

• 테이블 생성 (CREATE TABLE)

테이블 생성 예시

Employees라는 테이블을 만든다.

EmployeeID라는 정수 데이터 타입이면서 기본키인 열을 만든다

FirstName이라는 가변 문자열 데이터타입을 만든다

LastName이라는 가변 문자열 데이터타입을 만든다

Department라는 가변 문자열 데이터타입을 만든다.

 

• 데이터 삽입

데이터 삽입 예시

Employee 테이블에 데이터를 삽입하는 예시이다. 기본키 열은 Auto_Increment 속성이 정의되어 있으니 생략할 수 있다.

 

• 데이터 삭제

데이터 삭제 예시

`Employee` 테이블에서 특정 조건을 충족하는 데이터를 삭제하는 예시다. 여기서는 `Department`가 `Finance`인 직원을 삭제한다.

 

8.1.2 제약 조건

제약 조건(Constraints)은 데이터베이스 테이블에 저장되는 데이터의 무결성을 보장하기 위해 사용되는 규칙이나 제한사항이다. 즉, 데이터를 무조건적으로 입력시키는 것이 아닌, 특정 조건을 만족해야 입력될 수 있도록 제어하는 것이다. 동일한 아이디로 회원 가입이 되면 안되는 예시가 있을 수 있다.

 

• 기본 키 제약 조건

MySQL에서 기본키(Primary key) 제약조건은 테이블 내의 각 행을 고유하게 식별하는 데 사용된다. 기본키로 지정된 컬럼은 다음과 같은 특성을 가진다.

1. 고유성: 기본키로 지정된 컬럼의 값은 테이블 내에서 유일해야 한다.
2. NotNull 제약조건: 기본키 컬럼은 Null 값을 허용하지 않는다. 모든 행에 대해 해당 컬럼에는 유효한 값이 있어야 한다.
3. 자동 색인 생성: 기본키로 지정되면 MySQL은 해당 컬럼을 자동으로 색인(index)한다. 이는 해당 컬럼을 사용한 조회 작업의 성능을 향상시킨다.

기본키 설정 예시

`PRIMARY KEY (student_id)` 구문을 사용하여 `student_id` 컬럼을 기본키로 지정했다. 이는 `student_id`의 값이 각 행마다 고유해야 하며, `NULL` 값을 가질 수 없음을 의미한다.

 

• 복합 기본키

두 개 이상의 열을 조합하여 기본키를 만드는 것을 복합 기본키(Composite Primary Key)라고 한다. 복합 기본키는 각각의 컬럼 값이 유니크할 필요는 없지만, 컬럼들의 조합은 테이블 내에서 고유해야한다. 이를 통해 두 개 이상의 컬럼을 사용하여 각 행을 유일하게 식별할 수 있다.

복합 기본키 생성 예제

위 예시에서 `PRIMARY KEY (student_id, course_id)` 구문은 `student_id` 와 `course_id`의 조합을 기본키로 지정한다. 즉, 동일한 `student_id`와 `course_id`의 조합을 가진 두 행이 테이블 내에 존재할 수 없다.

 

• ADD CONSTRAINT를 사용하여 기본키 추가

테이블 생성 이후에 기본키를 추가하거나 기존 테이블에 복합 기본키를 설정하고 싶을 때는 `ALTER TABLE`문과 `ADD CONSTRAINT` 구문을 사용할 수 있다.

ADD CONSTRAINT 사용 예시

이 명령은 `course_enrollments` 테이블에 `student_id`와 `course_id` 컬럼의 조합으로 구성된 복합 기본키를 추가한다. 여기서 `pk_enrollment`는 이 기본키 제약 조건의 이름으로, 데이터베이스 내에서 해당 제약 조건을 참조할 때 사용된다.

• 기본 키 사용 이유
  1. 고유성 보장: 기본키로 설정된 필드의 값은 테이블 내에서 유일해야 한다. 이는 중복된 레코드의 삽입을 방지하여, 데이터의 정확성을 보장한다.
  2. 데이터 무결성 유지: 기본키는 테이블 데이터 무결성을 유지하는 데 중요한 역할을 한다. 테이블 내에서 각 행이 고유하게 식별되므로 데이터의 정확한 참조와 조작이 가능해진다
  3. 효율적인 데이터 접근: 기본키를 사용하면 데이터베이스 관리 시스템이 더 빠르고 효율적으로 검색할 수 있다. 이는 기본키가 인덱스로 사용되기 때문이다.
  4. 외래키와의 관계 설정: 기본키는 다른 테이블의 외래키와 관계를 맺을 때 사용된다. 이를 통해 데이터베이스 내의 테이블 간 관계를 정의하고, 참조 무결성을 유지할 수 있다.

 

• 외래 키 제약 조건

외래키 제약 조건은 데이터베이스의 두 테이블 간에 무결성을 유지하기 위해 사용된다. 외래키는 한 테이블의 칼럼(필드)이며, 그 값이 참조하는 테이블의 기본 키(Primary Key) 값과 일치해야 한다. 이를 통해 관계형 데이터베이스 간에 관계를 정의하고, 참조 무결성을 보장한다.

외래 키 사용 예시

 위 예시는 `Orders` 테이블의 `CustomerID` 필드를 `Customers` 테이블의 `CustomerID` 필드에 대한 외래 키로 설정한다. 이를 통해 `Order` 테이블에 삽입되는 각 주문이 유효한 고객 ID를 참조하게 된다.

 

• 외래 키 사용 이유
  1. 참조 무결성(Referential Integrity) 유지: 외래키는 참조하는 테이블의 기본 키와 일치해야 한다. 이를 통해 잘못된 데이터가 입력되는 것을 방지하고, 테이블 간의 올바른 관계를 유지할 수 있다.
  2. 데이터 일관성(Data Consistency) 보장: 외래키 제약 조건을 통해 데이터의 추가, 수정, 삭제 시 일관성을 유지할 수 있다. 예를 들어, 어떤 테이블에서 참조하고 있는 데이터가 삭제되려고 할 때, 외래키 제약 조건이 있으면 해당 데이터를 참조하는 다른 데이터가 존재하는 경우 삭제가 제한되거나 같이 삭제가 될 수 있다.
  3. 데이터베이스 내의 관계 명확화: 외래키를 사용하면 데이터베이스 설계 시 테이블 간의 관계를 명확하게 표현할 수 있다. 이는 데이터 모델의 가독성을 높이고, 데이터 간의 연관성을 이해하기 쉽게 만든다.
• ON DELETE CASCADE

`ON DELETE CASCADE` 옵션은 부모 테이블에서 레코드가 삭제될 때, 해당 레코드를 참조하는 자식 테이블의 모든 레코드도 함께 자동으로 삭제된다. 이는 데이터베이스에서 고나련 데이터를 유지 관리할 때 데이터 일관성을 보장하는 데 유용하다.

• ON UPDATE CASCADE

`ON UPDATE CASCADE` 옵션은 부모 테이블의 레코드가 업데이트될 때 해당 레코드를 참조하는 자식 테이블의 레코드도 자동으로 함께 업데이트된다. 이는 참조하는 테이블의 외래키 값이 부모 테이블의 기본 키 값에 연동되어 자동으로 갱신되도록 보장한다. 이로 인해 데이터의 일관성과 무결성이 유지된다.

 

사용 예시

• UNIQUE 제약 조건

`UNIQUE` 제약 조건은 지정된 컬럼의 모든 값이 유일하게 유지되어야 함을 보장한다. 이는 기본 키 제약 조건과 유사하지만, `UNIQUE` 제약 조건은 `NULL` 값을 가질 수 있다는 차이점이 있다.

UNIQUE 사용 예시

위 예시에서는 `Users` 테이블에 `Email`과 `Username` 컬럼이 `UNIQUE` 제약 조건을 갖는다. 이는 각 사용자의 이메일 주소와 사용자 이름이 테이블 내에서 고유해야 함을 의미한다. 즉, 두 사용자가 동일한 이메일 주소나 사용자 이름을 가질 수 없다.

• `UNIQUE` 사용 이유
  1. 중복 방지: `UNIQUE` 제약 조건은 데이터 중복을 방지한다. 예를 들어, 사용자 이메일 주소나 전화 번호와 같이 고유해야 하는 정보에 `UNIQUE` 제약 조건을 적용하여 테이블 내에서 같은 값이 두 번 이상 나타나지 않도록 할 수 있다. 이는 데이터의 신뢰성을 높인다.
  2. 데이터 무결성 유지: 데이터 무결성은 데이터베이스 시스템에서 데이터의 정확성, 일관성, 그리고 신뢰성을 유지하는 것을 의미한다. `UNIQUE` 제약 조건은 특정 데이터가 테이블 내에서 오직 한 번만 등장함으로써, 데이터 무결성을 강화한다.

• CHECK 제약 조건

`CHECK` 제약 조건은 데이터베이스 내의 테이블에 적용되며, 특정 컬럼에 입력되는 데이터가 지정된 조건을 만족해야 함을 보장한다. 이 제약 조건은 데이터의 무결성을 유지하고, 비즈니스 규칙을 데이터베이스 수준에서 직접 강제하는 데 유용하다.

`CHECK` 제약조건 사용 예시

위 예시는 Age 컬럼에 입력되는 모든 값이 18이상이어야 한다는 조건을 지정한다. 만약 위 조건을 만족하지 않는 값이 입력되거나 수정되려고 할 경우, 데이터베이스 시스템은 그 연산을 거부하고 오류 메시지를 반환한다.

 

• 사용 이유
  1. 데이터 무결성 유지: 테이블에 삽입되거나 업데이트되는 데이터가 사전에 정의된 규칙을 준수하도록 보장한다. 이를 통해 데이터가 즉정 조건을 만족하지 않는 경우 입력을 거부함으로써, 데이터베이스의 무결성을 유지한다
  2. 비즈니스 규칙의 데이터베이스 수준에서의 구현: 애플리케이션 레벨에서만이 아니라 데이터베이스 수준에서도 비즈니스 규칙을 강제할 수 있다. 이는 애플리케이션의 변경이나 다양한 데이터 소스로부터의 데이터 삽입 시에도 규칙이 일관되게 적용되도록 한다.

• DEFAULT 정의

`DEFAULT` 제약 조건은 데이터베이스 테이블의 컬럼에 대해 값을 명시적으로 제공하지 않을 때 자동으로 할당되는 기본값을 지정한다. `DEFAULT` 값은 레코드를 삽입할 때 해당 컬럼에 대한 값을 제공하지 않는 경우에 사용된다.

DEFAULT 사용 예시

`StartDate` 컬럼은 `DEFAULT` 제약 조건을 사용하여 새 직원 레코드가 생성될 때 날짜 값을 제공하지 않으면 현재 날짜(`CURRENT_DATE`)를 기본값으로 사용한다.

 

• 사용 이유
  1. 데이터 입력의 편의성: 모든 컬럼 데이터를 사용자가 명시적으로 제공하지 않아도 되므로, 데이터 입력 과정이 더욱 간편해진다.
  2. 데이터 무결성 보장: `DEFAULT` 값은 컬럼에 대해 유효한 값이 항상 존재하도록 보장한다. 이는 특히, 어떠한 값도 명시되지 않았을 때 NULL이 허용되지 않는 컬럼에 유용하다.
  3. 비즈니스 규칙과 로직의 구현: `DEFAULT` 제약 조건을 사용하면, 애플리케이션 또는 시스템의 특정 비즈니스 규칙을 데이터베이스 수준에서 구현할 수 있다. 예를 들어, 주문 상태가 기본적으로 `처리 중`으로 설정되거나, 새로운 글의 기본 카테고리가 `일반`으로 설정되는 등의 기본값을 통해 비즈니스 로직을 반영할 수 있다.

• NULL 값 허용

데이터베이스에서 `NULL` 값은 해당 필드에 대해 아무런 정보도 제공하지 않아도 된다는 것을 의미한다. `NULL` 값은 데이터가 불완전하거나 해당 정보를 모르는 경우 유용하게 사용될 수 있다.

NULL 값 허용 예시

위 예시에서 `Birthday` 필드는 `NULL` 값을 허용하므로, 고객이 생일 정보를 제공하지 않을 경우에도 고객 레코드를 생성할 수 있다. 즉, 유연하게 데이터 입력이 가능하다.

 

8.1.3 테이블 압축

테이블 압축 기능은 저장 공간을 절약하기 위해 사용한다. 테이블 데이터와 인덱스 페이지가 압축되어, 디스크 사용량을 줄이고, 디스크 I/O를 감소시킬 수 있지만, CPU 자원을 추가로 사용한다.

테이블 압축 사용 예시

위 예시에서 `my_table`은 `COMPRESSED` 로우 포맷을 사용하여 생성된다. `COMPRESSED` 포맷은 테이블의 데이터를 압축하여 저장 공간을 절약하고, 디스크 I/O를 줄이는 데 도움이 된다.

 

8.1.4 임시 테이블

특정 작업을 수행하는 동안 일시적으로 데이터를 저장하기 위해 사용된다. 임시 테이블은 세션 동안만 존재한다. 임시 테이블이 삭제되는 시점은 다음과 같다.

• 임시 테이블 삭제 시점
  1. 사용자가 DROP TABLE로 직접 삭제하는 경우
  2. mysql 서비스를 재시작 혹은 종료하는 경우

임시 테이블 사용 예시

위와 같은 방식으로 임시 테이블을 생성할 수 있다. 기존 테이블 생성 방식에서 `TEMPORARY`를 붙었다는 차이가 있다.

임시 테이블 삭제 예시

다음과 같은 방식으로 임시 테이블을 삭제할 수 있다. 세션이 종료될 때 자동으로 삭제되기 때문에 명시적으로 삭제하고 싶을 때 선택적으로 사용하면 된다.

• 임시 테이블 사용 이유
  1. 복잡한 쿼리 분할: 임시 테이블을 사용하면 복잡한 쿼리를 여러 단계로 나누어 각 단계의 결과를 임시 테이블에 저장할 수 있다. 이를 통해 쿼리의 가독성을 향상시키고, 오류 발생 시 디버깅을 용이하게 한다.
  2. 중복 연산 감소: 데이터 처리 과정에서 동일한 계산이 여러 번 반복되는 경우, 계산 결과를 임시 테이블에 저장함으로써 중복 연산을 줄일 수 있다. 이는 쿼리 실행 시간을 단축시키는 데 도움이 된다.
  3. 서브쿼리 대체: 복잡한 서브쿼리를 임시 테이블을 사용하는 방식으로 대체함으로써, 쿼리의 성능을 향상시키고 구조를 단순화할 수 있다

주로 데이터 처리 과정에서 중간 계산 결과를 일시적으로 저장하고, 복잡한 쿼리를 단순화하며, 성능을 향상시키기 위해서 사용한다.

 

8.1.5 테이블 삭제

테이블 삭제 예시

위와 같은 형식으로 테이블을 삭제할 수 있다. 하지만 외래키 제약조건에 의해 테이블 삭제가 제한되는 경우가 있다. 다음과 같은 방법으로 삭제를 할 수 있다.

• 외래 키 제약 조건 일시적으로 비활성화: 외래 키 제약 조건을 일시적으로 비활성화하고 테이블을 삭제한 다음, 제약 조건을 다시 활성화할 수 있다.

외래 키 제약 조건 비활성화

• 외래 키 제약 조건을 가진 테이블 삭제: 외래 키 테이블을 삭제해야 한다. 다음과 같은 방식으로 여러 테이블을 같이 삭제할 수 있다.

여러 테이블 삭제 예시

• 외래 키 제약 조건 삭제: 해당 테이블에서 외래 키 제약 조건을 직접 삭제할 수 있다. 이렇게 하면 테이블 간의 관계가 제거되어 테이블 삭제가 가능하다.

외래 키 제약 조건 직접 삭제 예시

• CASCADE 옵션 사용: `ON DELETE CASCADE` 옵션을 사용하면, 참조하는 테이블의 행이 삭제될 때 자동으로 해당 외래 키를 가진 행도 삭제된다

8.1.6 테이블 수정

• 열 추가

열 추가 예시

위와 같은 형식으로 열 추가가 가능하다.

 

• 열 삭제

열 삭제 예시

위와 같은 형식으로 열 삭제가 가능하다.

 

• 열의 이름 변경

열 이름 변경 예시

 

• 열의 데이터 형식 변경

열 데이터 형식 변경 예시

위와 같은 형식으로 데이터 타입을 수정할 수 있다.

 

• 열의 제약 조건 추가

NOT NULL 제약 조건 추가

기본키 제약 조건 추가

위의 형식으로 제약 조건을 추가할 수 있다

 

• 열의 제약 조건 삭제

기본 키 제약 조건 삭제 예시

외래 키 제약 조건 삭제 예시

위의 형식으로 제약 조건을 삭제할 수 있다.

 

`ALTER TABLE`명령어를 사용하여 테이블 구조를 관리할 수 있다.

 

8.2 뷰

8.2.1 뷰의 개념

뷰(View)는 데이터베이스에서 특정 SQL 쿼리의 결과를 저장하는 가상 테이블이다. 실제 데이터를 저장하지 않고, 데이터베이스 내에 존재하는 하나 이상의 테이블에서 데이터를 추출하는 SQL 쿼리를 기반으로 한다.

 

8.2.2 뷰의 장점

• 데이터 보안 강화

회사의 인사 관리 시스템에서 모든 직원의 상세 정보를 담고 있는 `employee`라는 테이블이 있다고 가정하자. 이 테이블에는 이름, 주소, 전화번호, 급여 등의 개인 정보가 포함되어 있다. HR 부서는 모든 정보에 접근할 수 있어야 하지만, 다른 부서의 직원은 이름과 전화번호만 볼 수 있어야 한다. 이러한 요구 사항을 충족시키기 위해, 이름과 전화번호만 포함하는 뷰를 생성할 수 있다.

뷰 보안 강화 예시

이 뷰를 통해 다른 부서의 직원들에게는 이름과 전화번호 정보만 제공하여 데이터 보안을 강화할 수 있다.

 

• 쿼리 복잡성 감소

매출 분석을 위해 여러 테이블을 조인하고, 복잡한 계산을 포함하는 쿼리를 사용하곤 한다. 이러한 복잡한 쿼리를 매번 작성하는 것은 시간이 많이 걸리고 오류가 발생하기 쉽다. 복잡한 쿼리를 뷰로 만들어 두면, 사용자는 단순히 뷰를 조회하기만 하면 되므로 쿼리의 복잡성을 크게 줄일 수 있다.

뷰 쿼리 복잡성 감소 예시

이제 사용자는 `SELECT * FROM sales_summary;`를 실행하기만 하면, 각 제품의 총 판매량을 쉽게 확인할 수 있다.

 

8.3 테이블스페이스

8.3.1 테이블스페이스의 개념

 테이블스페이스(Tablespace)는 데이터베이스에서 데이터를 저장하는 물리적인 공간의 단위다. 데이터베이스 관리 시스템(DBMS)에서는 테이블스페이스를 사용하여 데이터의 물리적 저장 위치를 관리한다. 주로 대용량 데이터를 다루는 경우 많이 사용한다.

 다음과 같은 이유로 사용한다.

• 관리의 용이성: 서로 다른 종류의 데이터(예: 애플리케이션 데이터, 로그 파일, 임시 데이터)를 분리하여 관리할 수 있다. 이는 데이터 관리와 백업, 복구 전략을 보다 유연하게 만들어 준다.
• 성능 최적화: 쿼리 처리를 위한 임시 데이터를 별도의 테이블 스페이스에 저장함으로써, 주요 운영 데이터의 입출력 성능에 영향을 주지 않게 할 수 있다.
• 데이터 보안 강화: 중요한 데이터를 별도의 테이블스페이스에 저장하고, 해당 테이블스페이스에 대한 접근 권한을 제한함으로써 데이터의 보안을 강화할 수 있다.
• 확장성 및 유연성: 데이터베이스가 성장함에 따라, 테이블스페이스를 추가 스토리지에 쉽게 확장할 수 있다. 이는 데이터베이스의 용량 확장 요구에 유연하게 대응할 수 있게 해준다.

8.3.2 성능 향상을 위한 테이블스페이스 추가

기본 설정은 모든 테이블이 시스템 테이블스페이스에 저장된다. 하지만 대용량 데이터를 다루는 테이블을 여러 개 사용하는 경우, 별도의 스페이스에 저장하는 것이 성능에 이점이 있다.

• 테이블스페이스 생성

테이블스페이스 생성 예시

위 쿼리는 `ts_a`라는 테이블스페이스를 생성한다. 생성된 테이블스페이스에 `ts_a.ibd`를 추가한다.

 

• 테이블스페이스에 테이블 생성

테이블스페이스 지정해서 테이블 생성

`TABLESPACE ts_a`를 통해 테이블스페이스를 지정하여 테이블을 저장할 수 있다.