8.1 테이블
8.1.1 테이블 만들기
SQL로 테이블 생성
테이블은 만드는 방법이 중요한 것이 아니라 테이블을 어떻게 모델링 했느냐가 훨씬 중요하다.
구매자 테이블과, 판매 상품 테이블이 있다고 가정한다.
user테이블의 경우 id로 구분할 수 있기 때문에 PRIMARY KEY(기본 키)를 설정하였다.
buy 테이블의 경우 num으로 구분하기 때문에 PRIMARY KEY로 설정하되, 기본적으로 하나씩 추가되게끔 AUTO_INCREMENT를 설정하였다.
AUTO_INCREMENT로 설정한 열은 PRIMARY KEY나 UNION로 반드시 지정해야 된다.
FOREIGN KEY의 경우 user테이블의 id와 외래 키 관계를 맺어라는 뜻이다.
테이블의 구조가 만들어진다.
8.1.2 제약 조건
제약 조건이란 데이터의 무결성을 지키기 위한 제한된 조건을 의미한다.
즉, 특정 데이터를 입력할 때 무조건적으로 입력되는 것이 아닌, 어떠한 조건을 만족했을 때 입력되도록 제약할 수 있다.
MySQL은 데이터의 무결성을 위해 6가지의 제약 조건을 제공한다.
1. PRIMARY KEY 제약 조건
2. FOREIGN KEY 제약 조건
3. UNIQUE 제약 조건
4. CHECH 제약 조건
5. DEFAULT 정의
6. NULL 값 허용
기본 키 제약 조건
테이블에 존재하는 많은 행의 데이터를 구분할 수 있는 식별자를 '기본 키'라고 부른다.
ex) 회원 아이디, 학번 등
기본 키에 입력되는 값은 중복될 수 없으며 NULL 값이 될 수 없다.
또한 하나의 열 뿐만 아니라 하나 이상의 열을 합쳐 기본 키로 설정할 수도 있다.
기본 키를 생성하는 방법은 PRIMARY KEY라는 예약어를 넣어준다.
이렇게 설정한 id는 userTest테이블의 기본 키가 되었으며 중복될 수 없고, NULL이 있을 수도 없다.
PRIMARY KEY 이름 지정하기
맨 뒤에 CONSTRAINT를 통해 PK_userTest_id라는 기본 키의 이름을 지어준다.
테이블에 지정된 키를 보려면 해당 구문을 사용한다.
왜래 키 제약 조건
외래 키 제약 조건은 두 테이블 사이의 관계를 선언함으로써 데이터의 무결성을 보장해주는 역할을 한다.
외래 키 관계를 설정하면 하나의 테이블이 다른 테이블에 의존하게 된다.
왜래 키 테이블에 데이터를 입력할 때는 기준 테이블을 참조해서 입력하므로 기준 테이블에 이미 데이터가 존재해야 된다.
또한 외래 키 테이블이 참조하는 기준 테이블의 열은 반드시 Primary Key이거나 Unique 제약 조건이 설정되어 있어야 한다.
왜래 키를 생성하는 방법은 FOREIGN KEY 키워드로 설정하는 방법이다.
현재 userTest의 id는 기본 키로 설정되어 있으므로 id에 외래 키 연결을 한다.
외래 키 이름 지정하기
기본 키와 마찬가지로 CONSTRAINT를 통해 키의 이름을 지어준다.
외래 키 옵션 중에 ON DELETE CASCADE 또는 ON UPDATE CASCADE가 있는데
기준 테이블의 데이터가 변경되었을 때 외래 키 테이블도 자동으로 적용되도록 설정해준다.
만약 userTest 테이블의 id가 변경되었다면? buyTest에도 자동으로 변경되도록한다.
UNIQUE 제약 조건
UNIQUE 제약 조건은 중복되지 않은 유일한 값을 입력해야 하는 조건이다.
PRIMARY KEY와의 차이점은 NULL 값을 허용한다는 점이다.
email은 각 사용자마다 고유한 값을 가진다.
하지만 모든 사용자가 email을 가지고 있는 것은 아니기 때문에 NULL 값이 들어갈 수 있다.
CHECK 제약 조건
입력되는 데이터를 점건하는 기능을 한다.
ex) 키에 마이너스 값이 들어올 수 없게 한다. 출생년도가 현재 시점 이전이어야 한다. 등등
출생년도가 1900보다 크고 현재 시점 이전인 제약 조건을 지정한다.
CHECK 제약 조건을 통해 1900보다 크고 2025보다 작은 범위를 year에 지정한다.
데이터를 INSERT할 때 year의 값에 1900보다 작은 1899의 값을 넣었더니 CHECK 제약 조건에 위배된다는 오류가 뜨는 것을 알 수 있다.
DEFAULT 정의
DEFAULT는 값을 입력하지 않았을 때, 자동으로 입력되는 기본 값을 정의하는 방법이다.
name이 없을 때는 '이름 없음', age가 없을 때는 0이 입력되는 경우이다.
name, age 데이터 형식 뒤에 DEFAULT를 붙여 값을 지정하였다.
name과 age에 default 값을 주었을 때 각각 이름 없음과 0이 입력된 것을 확인할 수 있다.
NULL 값 허용
NULL 값을 허용하려면 NULL, 허용하지 않으려면 NOT NULL을 사용하면 된다.
PRIMARY KEY가 설정된 열에는 NULL 값이 있을 수 없기 때문에 자동으로 NOT NULL로 인식된다.
8.1.3 테이블 압축
테이블 압축 기능은 대용량 테이블의 공간을 절약하는 효과를 갖는다.
쿼리가 실행되는데 걸린 시간을 확인하면 압축된 테이블이 데이터를 입력할 때 시간이 더 오래 걸린다.
즉, 압축된 테이블은 공간을 절약할 수 있지만 속도적인 측면에서 효율적인 방법은 아니다.
백업 및 복구를 할 때 많은 디스크 공간을 필요로 한다. 이때 데이터 압축하여 백업 파일의 크기를 줄이면 더욱 효율적으로 관리할 수 있다.
8.1.4 임시 테이블
임시 테이블은 임시처럼 잠깐 사용되는 테이블이다.
TABLE 위치에 TEMPORARY라고 써주는 것 외에는 테이블과 정의하는 것이 동일하다.
임시 테이블은 정의하는 구문만 약간 다를 뿐 사용법은 일반 테이블과 동일하게 사용할 수 있다.
임시 테이블은 세션 내에서만 존재하며 세션이 닫히면 자동으로 삭제된다.
또한 임시 테이블은 생성한 클라이언트에서만 접근이 가능하며, 다른 클라이언트는 접근할 수 없다.
데이터베이스 내의 다른 테이블과 이름은 동일하게 만들 수 있다. 하지만 기존 테이블은 임시 테이블이 있는 동안에 업근이 불가능하다.
임시 테이블이 삭제되는 시점
사용자가 DROP TABLE로 직접 삭제
Workbench를 종료하거나 mysql 클라이언트를 종료하면 삭제됨
MySQL 서비스가 재시작되면 삭제됨
현재 users라는 테이블이 있지만 무시하고 users라는 임시 테이블이 생성되었다.
조회 또한 기존 테이블이 아닌 임시 테이블이 조회된다.
해당 테이블은 세션이 끝나야지만 기존 테이블을 조회할 수 있게 된다.
임시 테이블은 언제 사용될까?
복잡한 쿼리를 처리할 때 임시 테이블을 사용하여 중간 결과를 저장하고 다음 단계를 진행한다.
대량의 데이터를 처리할 때 임시 데이터를 통해 일시적으로 데이터를 저장할 수 있게 한다.
8.1.5 테이블 삭제
단, 외래 키 제약 조건의 기준 테이블은 삭제할 수가 없다.
먼저 외래 키 테이블을 삭제해야 그 다음 기준 테이블을 삭제할 수 있다.
또, 여러 개 테이블을 동시에 삭제하려면 DROP TABLE 테이블1, 테이블2, 테이블3 .... 쭉 나열하면 된다.
8.1.6 테이블 수정
이미 생성된 테이블에 무엇인가를 추가/변경/수정/삭제하는 것은 모두 ALTER TABLE을 사용한다.
열의 추가
열을 추가하면 기본적으로 가장 뒤에 추가된다.
만약 열의 순서를 지정하려면 제일 뒤에 'FIRST', 'AFTER 열 이름'을 지정한다.
FIRST는 제일 앞에 열이 추가되며, AFTER 열 이름은 열 이름 다음에 추가된다.
기존 user 테이블의 컬럼은 id, name, age, email, amount가 있다.
ALTER TABLE을 통해 Date를 추가하였다.
조회를 한 결과를 보면 맨 뒤에 Date가 들어간 것을 확인할 수 있다.
열 삭제
제약 조건이 있을 경우 제약 조건을 먼저 삭제한 후 열을 삭제해야 한다.
Date가 컬럼에서 삭제된 것을 확인할 수 있다.
열 이름 및 데이터 형식 변경
만약 age란 이름을 uage로 변경하고 데이터 형식도 변경하고 싶다면?
ALTER TABLE 테이블 명 뒤에 CHANGE COLUMN을 통해 바꾸고 싶은 컬럼과 바꾸고 난 뒤 컬럼을 작성하여 원하는 데이터 형식과 이름으로 변경할 수 있다.
열의 제약 조건 추가 및 삭제
기본 키 제약 조건을 삭제할 때 만약 외래 키 제약 조건이 있다면 먼저 외래 키 제약 조건을 삭제한 뒤 기본 키 제약 조건을 삭제해야 된다.
8.2 뷰
뷰는 하나 이상의 테이블에서 가져온 데이터의 가상 테이블이다.
실제 데이터를 저장하지 않고, 기존 테이블에서 필요한 데이터를 동적으로 조회할 수 있도록 한다.
뷰는 일반 사용자 입장에서는 테이블과 동일하게 사용하는 개체이다.
8.2.1 뷰의 개념
뷰는 실제로 데이터를 저장하지 않고 단순희 정의만 한다.
뷰는 CREATE 'TABLE' 대신 VIEW가 들어간다.
AS를 통해 가져올 컬럼을 SELECT로 가져온다.
v_users 라는 뷰에 users 테이블에 있는 id, name, age 컬럼의 값들을 가져왔다.
해당 뷰를 조회해보면 컬럼의 값들이 잘 나오는 것을 확인할 수 있다.
8.2.2 뷰의 장점
1. 보안에 도움이 된다.
뷰 테이블에는 사용자의 이름과 주소만 넣어 중요한 개인정보 등의 정보를 넣지 않아 보안의 안정성을 높일 수 있다.
2. 복잡한 쿼리를 단순화 시켜줄 수 있다.
매번 복잡한 쿼리를 입력해야 할 때 뷰를 생성해놓고 해당 뷰에만 접근하여 쿼리를 생성하면 단순화시킬 수 있다.
8.3 데이블스페이스
소용량의 데이터를 다룰 때는 테이블이 저장되는 물리적 공간인 테이블스페이스에 대해 신경쓰지 않아도 되지만,
대용량의 데이터를 다룰 때는 성능 향상을 위해 테이블스페이스에 대한 설정을 하는 것이 좋다.
8.3.1 테이블스페이스의 개념
데이터베이스가 테이블이 저장되는 논리적 공간이라면, 테이블스페이스는 테이블이 실제로 저장되는 물리적인 공간을 말한다.
지금까지 테이블을 생성할 때 별도의 테이블스페이스를 지정하지 않았기 때문에 시스템 테이블스페이스에 저장되었다.
값의 정보는 파일명:파일크기:최대파일크기를 의미한다.
기본적으로 파일은 ibdata1이고, 파일크키는 12MB, 최대 파일 크키는 허용하는 최대값까지 자동으로 증가한다.
테이블 스페이스 파일은 기본적으로 C:\PromgramData\MySQL\MySQL Server 8.0\Data 폴더에 저장되어 있다.
8.3.2 성능 향상을 위한 테이블스페이스 추가
기본 설정은 모든 테이블이 시스템 테이블스페이스에 저장되지만, 대용량 테이블을 여러 개 사용하는 상황이라면 테이블마다 별도의 테이블스페이스에 저장하는 것이 성능에 효과적이다.
'Database > MySQL' 카테고리의 다른 글
| 이것이 MySQL이다. [Chapter 7 - SQL 고급(조인, SQL 프로그래밍)] (0) | 2024.03.09 |
|---|---|
| 이것이 MySQL이다. [Chapter 7 - SQL 고급(데이터 형식, 변수, 내장함수)] (0) | 2024.03.09 |
| 이것이 MySQL이다. [Chapter 6 - SQL 기본(SELECT문, SQL문, CTE)] (0) | 2024.03.03 |
| 이것이 MySQL이다. [Chapter 5 - MySQL 유틸리티 사용법(외부 Mysql 서버 관리, 사용자 관리)] (0) | 2024.03.03 |
| 이것이 MySQL이다. [Chapter 4 - 데이터베이스 모델링] (0) | 2024.03.03 |
