SQL 데이터베이스 입문_ 5. 다양한 자료형 활용하기

olongun·2025년 4월 18일

SQL 데이터베이스 입문

목록 보기

5/10

5.1 자료형이란

5.1.1 자료형의 개념

자료형: 데이터의 형태 (INTEGER, VARCHAR, DECIMAL 등)
자료형을 잘 선택해야 데이터 저장의 효율성, 데이터 사용의 정확성을 높일 수 있다.

데이터베이스에서 사용하는 자료형은 크게 숫자형, 문자형, 날짜 및 시간형으로 나뉜다.

5.1.2 숫자형

정수형: 소수점이 없는 숫자를 저장
정수형을 선언할 때는 UNSIGNED 제약 조건을 붙일 수 있다.
UNSIGNED는 해당 칼럼에 음수 값을 허용하지 않고 0 이상의 정수만 저장하도록 제한하는 기능을 함

-- data_type DB 생성 및 진입
CREATE DATABASE data_type;
USE data_type;

-- 유효성 보장: 나이는 0~255의 유효한 값만 저장
CREATE TABLE users (
	age TINYINT UNSIGNED
);

-- 안전성 보장: 재고는 음수가 될 수 없음
CREATE TABLE products (
	stock INTEGER UNSIGNED
);

실수형
- 부동 소수점 방식: 실수를 저장하되, 소수점의 위치가 변하는 특징(FLOAT, DOUBLE)
- 고정 소수점 방식: 소수점 이하 자릿수가 고정된 실수를 저장(DECIMAL(P, S))
실습: 숫자형 사용하기

-- student_records 테이블 생성
CREATE TABLE student_records (
	id INTEGER,					-- 아이디(표준 정수)
    grade TINYINT UNSIGNED,		-- 학년(부호가 없는 매우 작은 점수)
    average_score FLOAT,		-- 평균 점수(부동 소수점 방식의 실수)
    tuition_fee DECIMAL(10,2),	-- 수업료(고정 소수점 방식의 실수)
    PRIMARY KEY (id)			-- 기본키 지정: id
);

-- student_records 데이터 삽입
INSERT INTO student_records (id, grade, average_score, tuition_fee)
VALUES
	(1, 3, 88.75, 50000.00),
    (2, 6, 92.5, 100000.00);
    
-- 데이터 조회
SELECT *
FROM student_records;

5.1.3 문자형

CHAR와 VARCHAR
CHAR는 고정 길이의 문자를 저장하는 자료형으로 지정된 길이만큼의 메모리 공간을 차지
VARCHAR는 가변 길이의 문자를 저장하는 자료형으로 실제 입력한 길이만큼만 메모리 공간을 차지

-- CHAR와 VARCHAR 자료형의 사용 예
CREATE TABLE addresses (
	postal_code CHAR(5),			-- 우편번호(고정 길이 문자: 5자)
	street_address VARCHAR(100)		-- 거리 주소(가변 길이 문자: 최대 100자)
);

TEXT
TEXT는 긴 문자열을 저장하기 위한 자료형

-- TEXT 자료형의 사용 예
CREATE TABLE articles (
	title VARCHAR(200),			-- 제목(가변 길이 문자: 최대 200자)
    short_description TINYTEXT, -- 짧은 설명(최대 255Byte)
    comments TEXT,				-- 댓글(최대 64KB)
    content MEDIUMTEXT,			-- 본문(최대 16MB)
    additional_info LONGTEXT	-- 추가 정보(최대 4GB)
);

BLOB
BLOB은 크기가 큰 파일을 저장하기 위한 자료형으로 이미지, 오디오, 비디오 파일 등을 저장할 때 사용

-- BLOB 자료형의 사용 예
CREATE TABLE files (
	file_name VARCHAR(200),		-- 파일명(가변 길이 문자: 최대 200자)
    small_thumbnail TINYBLOB,	-- 작은 이미지 파일(최대 255Byte)
    document BLOB,				-- 일반 문서 파일(최대 64KB)
    video MEDIUMBLOB,			-- 비디오 파일(최대 16MB)
    large_data LONGBLOB			-- 대용량 파일(최대 4GB)
);

ENUM
ENUM은 주어진 목록 중 하나만 선택해 입력할 수 있는 자료형

-- ENUM 자료형의 사용 예
CREATE TABLE memberships (
	name VARCHAR(100),						-- 회원명(가변 길이 문자: 최대 100자)
    level ENUM('bronze', 'silver', 'gold')	-- 회원 레벨(선택 목록 중 택 1)
);

실습: 문자형 사용하기

-- user_profiles 테이블 생성
CREATE TABLE user_profiles (
    id INTEGER, 				-- 아이디(표준 정수)
    email VARCHAR(255), 		-- 이메일(최대 255자)
    phone_number CHAR(13), 		-- 전화번호(고정 길이 13자)
    self_introduction TEXT, 	-- 자기소개(최대 64KB)
    profile_picture MEDIUMBLOB, -- 프로필 사진(최대 16MB)
    gender ENUM('남', '여'), 	-- 성별
    PRIMARY KEY (id) 			-- 기본키 설정
);

-- user_profiles 데이터 삽입
INSERT INTO user_profiles (id, email, phone_number, self_introduction, profile_picture, gender)
VALUES
	(1, 'hongpark@example.com', '012-3456-7890', '안녕하십니까!', NULL, '남'),
	(2, 'hongsoon@example.com', '987-6543-2109', '반갑습니다!', NULL, '여');

-- 데이터 조회
SELECT *
FROM user_profiles;

5.1.4 날짜 및 시간형

DATE: 날짜를 저장 (YYYY-MM-DD)
TIME: 시간을 저장 (hh:mm:ss)
DATETIME: 날짜와 시간을 함께 저장 (YYYY-MM-DD hh:mm:ss)
YEAR: 4자리 연도를 저장 (YYYY)
실습: 날짜 및 시간형 사용하기

-- events 테이블 생성
CREATE TABLE events (
    id INTEGER,                     -- 아이디(표준 정수)
    event_name VARCHAR(100),       -- 이벤트명(가변 길이 문자: 최대 100자)
    event_date DATE,               -- 이벤트 일자(YYYY-MM-DD)
    start_time TIME,               -- 이벤트 시간(hh:mm:ss)
    created_at DATETIME,           -- 이벤트 등록 일시(YYYY-MM-DD hh:mm:ss)
    event_year YEAR,               -- 이벤트 연도(YYYY)
    PRIMARY KEY (id)               -- 기본키 지정: id
);

-- events 데이터 삽입
INSERT INTO events (id, event_name, event_date, start_time, created_at, event_year)
VALUES
    (111, 'Music Festival', '2024-10-04', '17:55:00', '2024-09-04 10:25:30', '2024'),
    (222, 'Art Exhibition', '2024-11-15', '12:00:00', '2024-09-05 11:30:00', '2024');

-- 데이터 조회
SELECT *
FROM events;

5.2 자료형에 따른 필터링 실습: 상점 DB

5.2.1 데이터셋 만들기

-- store DB 생성 및 진입
CREATE DATABASE store;
USE store;

-- orders 테이블 생성
CREATE TABLE orders (
	id INTEGER,
    name VARCHAR(255),
    price DECIMAL(10, 2),
    quantity INTEGER,
    created_at DATETIME,
    PRIMARY KEY (id)
);

-- orders 데이터 삽입
INSERT INTO orders (id, name, price, quantity, created_at)
VALUES
	(1, '생돌김 50매', 5387.75, 1, '2024-10-24 01:19:44'),
	(2, '그릭 요거트 400g, 2개', 7182.25, 2, '2024-10-24 01:19:44'),
	(3, '냉장 닭다리살 500g', 6174.50, 1, '2024-10-24 01:19:44'),
	(4, '냉장 고추장 제육 1kg', 9765.00, 1, '2024-10-24 01:19:44'),
	(5, '결명자차 8g * 18티백', 4092.25, 1, '2024-10-24 01:19:44'),
	(6, '올리브 오일 1l', 17990.00, 1, '2024-11-06 22:52:33'),
	(7, '두유 950ml, 20개', 35900.12, 1, '2024-11-06 22:52:33'),
	(8, '카카오 닙스 1kg', 12674.50, 1, '2024-11-06 22:52:33'),
	(9, '손질 삼치살 600g', 9324.75, 1, '2024-11-16 14:55:23'),
	(10, '자숙 바지락 260g', 6282.00, 1, '2024-11-16 14:55:23'),
	(11, '크리스피 핫도그 400g', 7787.50, 2, '2024-11-16 14:55:23'),
	(12, '우유 900ml', 4360.00, 2, '2024-11-16 14:55:23'),
	(13, '모둠 해물 800g', 4770.15, 1, '2024-11-28 11:12:09'),
	(14, '토마토 케첩 800g', 3120.33, 3, '2024-11-28 11:12:09'),
	(15, '계란 30구', 8490.00, 2, '2024-12-11 12:34:56'),
	(16, '해물 모듬 5팩 묶음 400g', 9800.50, 4, '2024-12-11 12:34:56'),
	(17, '칵테일 새우 900g', 22240.20, 1, '2024-12-11 12:34:56'),
	(18, '토마토 케첩 1.43kg', 7680.25, 1, '2024-12-11 12:34:56'),
	(19, '국내산 양파 3kg', 5192.00, 1, '2024-12-11 12:34:56'),
	(20, '국내산 깐마늘 1kg', 9520.25, 1, '2024-12-11 12:34:56');

5.2.2 문자열 필터링하기

LIKE 연산자: 칼럼 값이 특정 패턴과 완전히 일치하거나 특정 패턴을 포함하는지 확인할 때 사용
SELECT *
FROM 테이블명
WHERE 칼럼명 LIKE '찾는_패턴';

-- name이 '케첩'인 주문 조회
SELECT *
FROM orders
WHERE name LIKE '케첩'; -- 빈 테이블 출력

'케첩'이 포함된 주문을 조회하기 위해서는 LIKE 연산자와 함께 와일드 카드를 사용한다.

와일드 카드: 특정 패턴을 찾는데 사용하는 다음의 기호

% : 0개 또는 그 이상의 임의의 문자를 의미

_ : 정확히 1개의 임의의 문자를 의미

사용 예	설명	결과
LIKE '케첩%'	‘케첩’으로 시작하는 임의의 문자	케첩 소스, 케첩병
LIKE '%케첩'	‘케첩’으로 끝나는 임의의 문자	토마토 케첩, 꿀케첩
LIKE '%케첩%'	가운데에 ‘케첩'을 포함하는 임의의 문자	케첩 소스, 토마토 케첩, 토마토 케첩 소스, 케첩병, 꿀케첩
LIKE '케첩_'	'케첩'으로 시작하는 3글자	케첩병
LIKE '_케첩'	'케첩'으로 끝나는 3글자	꿀케첩

-- name에 '케첩'이 포함된 주문 조회
SELECT *
FROM orders
WHERE name LIKE '%케첩%';

5.2.3 날짜 필터링하기

날짜 함수
YEAR(날짜), MONTH(날짜), DAY(날짜), EXTRACT(필드 FROM 날짜)

-- 11월에 주문받은 상품 개수의 합계
SELECT SUM(quantity)
FROM orders
WHERE MONTH(created_at) = 11;

5.2.4 시간 필터링하기

시간 함수
HOUR(시간), MINUTE(시간), SECOND(시간), TIME_TO_SEC(시간)

-- 오전에 주문받은 매출의 합계
SELECT sum(price * quantity)
FROM orders
WHERE HOUR(created_at) < 12;

5.2.5 특정 범위 필터링하기

BETWEEN 연산자: 두 값 사이에 속하는지 확인할 때 사용하는 연산자
SELECT *
FROM 테이블명
WHERE 칼럼명 BETWEEN 시작_값 AND 마지막_값;

-- pirce가 10000~20000 사이의 주문 조회
SELECT *
FROM orders
WHERE price BETWEEN 10000 AND 20000;

-- 2024-11-15 ~ 2024-12-15 사이의 주문 개수의 합계
SELECT COUNT(*)
FROM orders
WHERE created_at BETWEEN '2024-11-15' AND '2024-12-15';

-- 상품명의 첫 글자가 'ㄱ'으로 시작하는 주문 조회
SELECT *
FROM orders
WHERE name BETWEEN 'ㄱ' AND '깋';

5.3 셀프체크

5.2절에서 만든 orders 테이블을 이용해 다음 1~5를 수행하는 쿼리를 작성하세요.

실습 코드

-- store DB 진입
USE store;

-- 1. 상품명이 '국내산'으로 시작하는 주문의 개수를 구하세요.
SELECT COUNT(*)
FROM orders
WHERE name LIKE '국내산%';

-- 2. 주문 수량이 2~4개인 상품의 평균 가격을 구하세요.
SELECT AVG(price)
FROM orders
WHERE quantity BETWEEN 2 AND 4;

-- 3. 11월 주문 중 11월 20일 이후에 들어온 주문의 개수를 구하세요.
SELECT COUNT(*)
FROM orders
WHERE MONTH(created_at) = 11 AND DAY(created_at) > 20;

-- 4. 상품명에 부피 단위인 'ml' 또는 'l'가 포함된 주문을 모두 조회하세요.
SELECT *
FROM orders
WHERE name LIKE '%ml%' OR name LIKE '%l%';

-- 5. 10월과 12월에 들어온 주문의 개수를 구하세요(주의: 11월은 포함하지 않습니다).
SELECT COUNT(*)
FROM orders
WHERE MONTH(created_at) = 10 OR MONTH(created_at) = 12;
-- WHERE MONTH(created_at) IN (10, 12);

자료형에 따른 필터링 연습 문제

다음 이벤트(events) 테이블을 보고 문제에 답하세요.

-- events 테이블 생성
CREATE TABLE events (
	id INTEGER, 		  -- ID
	title VARCHAR(100),   -- 이벤트 제목
	event_date DATE, 	  -- 이벤트 날짜
	start_time TIME, 	  -- 시작 시간
	location VARCHAR(50), -- 장소
	attendees INT, 		  -- 참석자 수
	PRIMARY KEY (id)
);

-- events 데이터 삽입
INSERT INTO events (id, title, event_date, start_time, location, attendees)
VALUES
	(1, '코딩 부트캠프', '2023-10-01', '09:00:00', '서울', 50),
	(2, 'AI 세미나', '2023-10-15', '14:00:00', '부산', 100),
	(3, '데이터 분석 워크숍', '2023-11-05', '10:30:00', '서울', 30),
	(4, '스타트업 데모데이', '2023-12-10', '13:00:00', '대전', 200),
	(5, '클라우드 컨퍼런스', '2024-01-20', '11:15:00', '인천', 150),
	(6, '해커톤', '2024-02-05', '08:00:00', '서울', 300),
	(7, 'UX/UI 디자인 워크숍', '2023-09-25', '09:30:00', '광주', 25),
	(8, '기술 트렌드 토크', '2023-11-20', '15:00:00', '서울', 80),
	(9, '프로그래밍 대회', '2023-12-01', '10:00:00', '부산', 120),
	(10, '오픈소스 컨트리뷰션 데이', '2023-10-25', '16:00:00', '서울', 60);

실습 코드

-- 연습 문제 1. 이벤트 제목에 '워크숍'이 포함된 이벤트의 제목과 장소 조회
SELECT title, location
FROM events
WHERE title LIKE '%워크숍%';

-- 연습 문제 2. 이벤트 제목이 '데이터'로 시작하는 이벤트의 제목과 참석자 수 조회
SELECT title, attendees
FROM events
WHERE title LIKE '데이터%';

-- 연습 문제 3. 이벤트 날짜가 2023년인 이벤트의 제목과 날짜 조회
SELECT title, event_date
FROM events
WHERE YEAR(event_date) = 2023;

-- 연습 문제 4. 이벤트 시작 시간이 오전 9시 이전인 이벤트의 제목과 시작 시간을 조회
SELECT title, start_time
FROM events
WHERE HOUR(start_time) < 9;

-- 연습 문제 5. 참석자가 50명 이상 150명 이하인 이벤트의 제목과 참석자 수를 조회
SELECT title, attendees
FROM events
WHERE attendees BETWEEN 50 AND 150;

-- 연습 문제 6. 이벤트 날짜가 2023-10-01부터 2023-12-31 사이인 이벤트의 제목과 날짜를 조회
SELECT title, event_date
FROM events
WHERE event_date BETWEEN '2023-10-01' AND '2023-12-31';

-- 연습 문제 7. 이벤트 제목에 '컨퍼런스' 또는 '컨트리뷰션'이 포함되고, 시작 시간이 오전 11시 이후인 이벤트의 제목과 시작 시간을 조회
SELECT title, start_time
FROM events
WHERE (title LIKE '%컨퍼런스%' OR title LIKE '%컨트리뷰션%') AND HOUR(start_time) >= 11;

-- 연습 문제 8. 이벤트 날짜가 2023-11-1부터 2023-12-31 사이고, 시작 시간이 오후 2시 이후인 이벤트의 제목과 날짜, 시작 시간을 조회
SELECT title, event_date, start_time
FROM events
WHERE (event_date BETWEEN '2023-11-1' AND '2023-12-31') AND HOUR(start_time) >= 14;