DB 생성과 데이터 추가

김소정·2022년 7월 19일

자료형

문자 데이터

고정 길이: 열에 저장할 모든 문자열이 약어처럼 길이가 동일할 때
가변 길이: 열에 저장할 문자열의 길이가 서로 다를 때

char(20) /* fixed-length */
varchar(20) /* variable-length */

char 열의 최대 길이는 현재 255바이트인 반면, varchar 열은 최대 65,535바이트까지 사용할 수 있다.

캐릭터셋

영어와 같이 라틴 알파벳을 사용하는 언어는 각 문자를 저장하는 데 단 1바이트만 필요한 반면, 한국어나 일본어를 비롯한 다른 언어들은 많은 수의 문자를 포함하므로 각 문자마다 여러 바이트의 저장 공간이 필요하다. 이러한 문자 집합을 멀티바이트캐릭터셋이라고 한다.

열을 정의할 때 기본값(mysql 서버 기준 utf8mb4)이 아닌 캐릭터셋을 선택하려면 다음과 같이 자료형 정의 뒤에 지원되는 캐릭터셋 중 하나를 지정해주면 된다.

varchar(20) character set latin1

또한 MySQL을 사용하면 전체 데이터베이스(DB)에 대한 기본 캐릭터셋 설정도 가능하다.

create database european_sales character set latin1;

텍스트 데이터

varchar 열에 64KB 제한을 초과하는 데이터를 저장하려면 텍스트 자료형 중 하나를 사용해야 한다.

자료형	최대 바이트 크기
tinytext	255
text	65,535
mediumtext	16,777,215
longtext	4,294,967,295

MySQL 텍스트 자료형

텍스트 자료형을 선택할 때는 다음의 사항을 고려해야 한다.

텍스트 열에 로드되는 데이터가 해당 유형의 최대 크기를 초과하면 데이터가 잘린다.
데이터를 열에 로드되면 후행 공백이 제거되지 않는다.
MySQL은 이제 varchar 열에 쵀대 65,535바이트를 허용하므로 tinytext나 text 자료형을 사용할 필요가 없다.

숫자 데이터

몇가지 예시를 통해 숫자 자료형이 사용되는 데이터 유형을 살펴보자.

고객 주문의 배송 여부를 나타내는 열
불리언(boolean)이라는 이 자료형의 열에는 false를 나타내는 0과 true를 나타내는 1이 포함된다.
트랜잭션 테이블의 시스템 생성 기본 키
이 데이터는 보통 1에서 시작하여 잠재적으로 매우 큰 수까지 1씩 증가한다.
고객의 온라인 장바구니 품목 번호
이러한 유형의 열에 대한 값은 1과 200 사이의 양의 정수이다.
회로 기판 드릴 기계의 위치 데이터
고정밀 과학 또는 제조 데이터는 종종 소수점 8자리까지 정확도를 요구한다.

자료형	부호 있는 정수 저장값의 범위	부호 없는 정수 저장값의 범위
tinyint	-128부터 127	0부터 255
smallint	-32,768부터 32,767	0부터 65,535
mediumint	-8,388,608부터 8,388,607	0부터 16,777,215
int	-2,147,483,848부터 2,147,483,847	0부터 4,294,967,295
bigint	$-2^{63}$ 부터 $2^{63}-1$	0부터 $2^{64}-1$

MySQL 정수 자료형

불필요하게 스토리지 공간을 낭비하지 않으면서 열에 저장할 수 있는 가장 큰 수를 저장할 수 있을 만큼 충분히 큰 자료형을 선택해야 한다.

이 외에도 부동소수점 자료형이 있다.

정밀도: 소수점 왼쪽과 오른쪽 모두에 허용되는 자릿수
척도: 소수점 오른쪽의 허용 자릿수

부동소수점 자료형은 위 두가지를 지정할 수 있지만 필수는 아니다.

시간 데이터

사용자가 테이블에서 특정 행을 수정한 날짜 및 시간
: timestamp 자료형
자동차 조립 라인에서 배선 장치를 완성하는 데 걸리는 시간
: time 자료형
직원의 생년월일
: date 자료형
데이터 웨어하우스의 years_sales 팩트 테이블 행의 연도
: year 자료형

자료형	기본 형식
date	YYYY-MM-DD
datetime	YYYY-MM-DD HH:MI:SS
timestamp	YYYY-MM-DD HH:MI:SS
year	YYYY
time	HHH:MI:SS

MySQL 시간 자료형

datetime, timestamp, time 자료형에서는 소수점 이하 6자리까지 사용할 수 있다. 또한 위 표에서 HHH는 -838부터 838까지의 시간(경과)을 의미한다.

테이블 생성

설계(design)
브레인 스토밍을 통해 어떤 종류의 정보를 포함해야 도움이 될지 알아본 뒤, 열 이름과 자료형을 지정한다. 추가적으로 허용값까지 제한할 수 있다.
정제(refinement)
DB 설계에 중복(외부 키 제외) 또는 복합 열이 없음을 확인하는 절차인 정규화를 거쳐 정규화된 테이블을 만든다.

SQL 스키마 문 생성
테이블을 정의할 때는 기본 키로 사용할 열을 데이터베이스 서버에 알려줘야 하므로 테이블에 제약조건을 만들어 이 작업을 수행한다.

-- 체크 제약 조건
eye_color CHAR(2) CHECK (eye_color IN ('BR','BL','GR')),

-- 기본 키 제약 조건
CONSTRAINT pk_person PRIMARY KEY (person_id);

desc table; 명령어를 이용해 우리가 만든 테이블 정의를 볼 수 있다. 또한 NULL 값은 해당 사항 없음, 알 수 없음, 비어있는 셋 등 값을 제공할 수 없는 다양한 경우에 사용된다.

-- 테이블 생성 예시

CREATE TABLE favorite_food
(person_id SMALLINT UNSIGNED,
food VARCHAR(20),
CONSTRAINT pk_favorite_food PRIMARY KEY (person_id, food),
CONSTRAINT fk_fav_food_person_id FOREIGN KEY (person_id)
REFERENCES person (person_id)
);

테이블 수정

숫자 키 데이터 생성

-- 자동 증가 기능 ON
ALTER TABLE person MODIFY person_id SMALLINT UNSIGNED AUTO_INCREMENT;

person 테이블에 데이터를 삽입할 때 person_id 열에 null 값을 제공하기만 하면 MySQL은 사용 가능한 다음 숫자로 열을 채운다.

데이터 삽입

데이터를 삽입할 때는 INSERT를 이용해주면 되는데, 해당 구성요소는 다음과 같다.

데이터를 추가할 테이블 이름
데이터를 채울 테이블의 열 이름
열을 채울 값

INSERT INTO person
	(person_id, fname, lname, eye_color, birth_date)
VALUES (null, 'William', 'Turner', 'BR', '1972-05-27');

데이터 수정

UPDATE를 이용하여 person_id가 1인 사람의 정보를 수정한다.

UPDATE person
SET street = '1225 Tremont St.',
	city = 'Boston',
    state = 'MA',
    country = 'USA',
    postal_code = '02138'
WHERE person_id = 1;

데이터 삭제

DELETE를 이용하여 person_id가 2인 사람의 정보를 삭제한다.

DELETE FROM person
WHERE person_id = 2;

좋은 구문을 망치는 경우

고유하지 않은 기본 키
테이블 정의에는 기본 키 제약조건 생성이 포함되므로 중복 키 값을 테이블에 삽입해서는 안 된다.
존재하지 않는 외래 키
상위 테이블에 먼저 관련 행을 작성해야만, 하위 테이블에 데이터를 입력할 수 있다.
열 값 위반
열에 제약조건이 있을 때, 이를 위반하면 에러 메세지가 나타난다.
잘못된 날짜 변환
date 열을 채울 문자열을 구성할 때, 해당 문자열이 예상 형식과 일치하지 않으면 또 다른 오류가 발생한다. 따라서 기본 형식에 의존하지 않고 형식 문자열을 명시적으로 지정하는 것이 좋다.
```
-- str_to_date 함수 이용
UPDATE person
SET birth_date = str_to_date('DEC-21-1980', '%b-%d-%Y')
WHERE person_id = 1;
```