Learning SQL 2장 요약 - 데이터베이스 생성과 데이터 추가

2장 데이터베이스 생성과 데이터 추가

 

데이터베이스를 작성하고 앞으로 사용할 예제 테이블 및 데이트를 만든다.

다양한 자료형을 살펴보고 테이블을 생성해본다.

 

MySQL 자료형

 

문자 데이터

자료형	최대 바이트 크기
char - 고정길이	255
varchar - 가변길이	65,535

오라클 데이터베이스에선 varchar2을 사용할 것

 

 

텍스트 데이터

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

 

숫자 데이터

정수형

자료형	signed형 범위	unsigned형 범위
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,648 ~ 2,147,483,647	0 ~ 4,294,967,295
bigint	-2^63 ~ 2^63 - 1	0 ~ 2^64 - 1

 

부동 소수점형

자료형	숫자 범위
float(p, s)	-3.402823466E+38 ~ -1.175494351E-38 1.175494351E-38 ~ 3.402823466E+38
double(p, s)	-1.7976931348623157E+308 ~ -2.2250738585072014E-308 2.2250738585072014E-308 ~ 1.7976931348623157E+308

p는 precision(정밀도, 소수점 왼쪽과 오른쪽에 허용되는 자릿 수)

s는 scale(척도, 소수점 오른쪽에 허용되는 자릿 수)

 

예) float(4,2)로 정의된 열은

총 4자리(소수점 왼쪽 2자리, 오른쪽 2자리)로 저장

17.8675는 17.87로 반올림됨

178.375는 오류 발생

 

시간 데이터

자료형	기본 형식	허용값
date	YYYY-MM-DD	1000-01-01 ~ 9999-12-31
datetime	YYYY-MM-DD HH:MI:SS	1000-01-01 00:00:00.000000 ~ 9999-12-31 23:59:59.999999
timestamp	YYYY-MM-DD HH:MI:SS	1970-01-01 00:00:00.000000 ~ 2038-01-18 22:14:07.999999
year	YYYY	1901 ~ 2155
time	HHH:MI:SS	-838:59:59.000000 ~ 838:59:59.000000

datetime, timestamp, time 자료형은 소수점 이하 자릿수를 정의할 수 있다.

예) datetime(2)

 

날짜 형식

요소	정의	범위
YYYY	연도, 세기 포함	1000 ~ 9999
MM	월	01 ~ 12
DD	일	01 ~ 31
HH	시간	00 ~ 23
HHH	시간(경과)	-838 ~ 838
MI	분	00 ~ 59
SS	초	00 ~ 59

 

테이블 생성

스키마 문 생성

예) person 테이블과 그를 참조하는 하위 테이블인 favorite_food 테이블을 생성

CREATE TABLE person(
    person_id SMALLINT UNSIGNED,
    fname VARCHAR(20),
    lname VARCHAR(20),
    eye_color ENUM('BR', 'BL', 'GR'),		// enum 자료형으로 허용값 정의
    birth_date DATE,
    street VARCHAR(30),
    city VARCHAR(20),
    state VARCHAR(20),
    country VARCHAR(20),
    postal_code VARCHAR(20),
    CONSTRAINT pk_person PRIMARY KEY (person_id)   // 기본 키 제약조건
);

 

CREATE TABLE favorite_food(
    person_id SMALLINT UNSIGNED,
    food VARCHAR(20),
    // 기본 키 제약조건 (기본 키는 2개)
    CONSTRAINT pk_favorite_food PRIMARY KEY (person_id, food),
    // 외래 키 제약조건
    CONSTRAINT fk_fav_food_person_id FOREIGN KEY (person_id)
    REFERENCES person (person_id)
);

desc (테이블명)으로 테이블 생성과 정의를 확인할 수 있다.

 

MySQL은 체크 제약조건 말고도 enum 자료형을 제공하기도 한다.

체크 제약조건을 사용하면 다음과 같다.

예) eye_color CHAR(2) CHECK (eye_color IN ('BR', 'BL', 'GR'))

 

외래 키 만드는 걸 잊어버렸다면, 나중에 alter table 문으로 추가할 수 있다.

 

테이블 수정 - ALTER TABLE (테이블명) 문

숫자 키 데이터 생성

오라클에선 시퀀스라는 별도의 스키마 개체가 사용됨,

MySQL에선 auto-increment 기능만 켜면 됨.

ALTER TABLE person MODIFY person_id SMALLINT UNSIGNED AUTO_INCREMENT;

 

(현재 favorite_food 테이블에 외래 키가 걸려 있으므로 오류가 발생한다.

따라서 다음과 같은 구문을 먼처 처리하자.)

set foreign_key_checks=0;
ALTER TABLE person MODIFY person_id SMALLINT UNSIGNED AUTO_INCREMENT;
set foreign_key_checks=1;

 

데이터 추가 - INSERT INTO (테이블명) 문

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

 

데이터 수정 - UPDATE (테이블명) SET 문

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

 

만약 다음과 같이 변경 했다면,

WHERE person_id < 10

person_id의 값이 10 미만인 행 모두가 수정된다.

 

데이터 삭제 - DELELTE FROM (테이블명) 문

DELETE FROM person
WHERE person_id = 2;

만약 WHERE 절을 생략하면 모든 행은 삭제된다.

(Empty Set이 되어서 빈 테이블이 되는 것이다.)

 

테이블 삭제 - DROP TABLE (테이블명) 문

DROP TABLE favorite_food; // 하위 테이블부터 삭제
DROP TABLE person;

show tables로 확인해보자.