[MySQL] 41. 쇼핑몰 서비스 DB 구축 (2)

2024년 06월 12일

Song hyun
작성자
2024.06.12.:44

728x90

[MySQL] 41. 쇼핑몰 서비스 DB 구축 (2)

1. 쇼핑몰 카테고리 2단계

2. 예제 코드

3. 전체 코드

1. 쇼핑몰 카테고리 2단계

(1) 카테고리 테이블 설계

-자신보다 상위의 개념이 있을 수 있다.

ex: 남성복/여성복, 셔츠, 면바지라는 카테고리들이 있다면, 남성복/여성복이라는 상위 카테고리를 정하고, 그 아래에 셔츠와 면바지라는 하위 카테고리를 넣을 수 있을 것이다.

category_id	parents_id	category_name
1	null	남성복 (최상위 카테고리)
2	null	여성복 (최상위 카테고리)
3	1	팬츠 (남성복->팬츠)
4	2	팬츠 (여성복->팬츠)
5	1	셔츠 (남성복->셔츠)
6	2	셔츠 (여성복->셔츠)
7	3	면바지 (남성복->팬츠->면바지)
8	4	면바지 ( 여성복->팬츠->면바지 )
9	3	슬랙스 (남성복->팬츠-> 슬랙스)
10	4	슬랙스 (여성복->팬츠-> 슬랙스)

*만약 '팬츠'의 부모 id에 1,2를 동시에 넣으면, 데이터의 무결성에 위배된다.

=> '팬츠'라는 레코드를 두 개 만들어, 하나는 여성복 카테고리, 하나는 남성복 카테고리를 부모 카테고리로 지정한다.

=> '면바지' 역시, 레코드를 3개를 만들어 각각 여성복-팬츠/남성복-팬츠를 상위 카테고리로 지정한다.

(2) 프로덕트 테이블 설계

PRODUCT_ID	PRODUCT_ NAME	CATEGORY_ ID	PRICE	SIZE	COLOR
102	남성 셔츠	4 (남성복-셔츠)	49,000	'L'	'퍼플'
104	여성 셔츠	6 (여성복- 셔츠)	49,000	'L'	'퍼플'
105	남성 면바지	7 (남성복-바지-면바지)	55,000	'M'	'핑크'
106	남성 슬랙스	8	105,000	'XL'	'샤인'
107	여성 면바지	9	55,000	'M'	'핑크'
108	여성 슬랙스	10	105,000	'XL'	'샤인'

* 남성 셔츠, 여성 셔츠 같은 프로덕트를 CATEGORY_ID와 JOIN할 때는, 최상위 카테고리가 아닌 바로 위의 카테고리를 JOIN해야 한다.

2. 예제 코드

(1) 카테고리 테이블 설계

create database demo3;

use demo3;

-- 카테고리 테이블 (상위, 하위 개념 추가)
create table tb_categories(
	CATEGORY_ID INT AUTO_INCREMENT PRIMARY KEY,
    PARENT_ID INT NULL,
    CATEGORY_NAME VARCHAR(100),	
    FOREIGN KEY(PARENT_ID) REFERENCES TB_CATEGORIES(CATEGORY_ID)
);

(2) 카테고리 테이블 - 데이터 삽입

-- 남성복의 하위 카테고리
INSERT INTO TB_CATEGORIES(CATEGORY_NAME,PARENT_ID) 
	VALUES('팬츠',1);    
INSERT INTO TB_CATEGORIES(CATEGORY_NAME,PARENT_ID) 
	VALUES('셔츠',1);

-- 여성복의 하위 카테고리
INSERT INTO TB_CATEGORIES(CATEGORY_NAME,PARENT_ID) 
	VALUES('팬츠',2);    
INSERT INTO TB_CATEGORIES(CATEGORY_NAME,PARENT_ID) 
	VALUES('셔츠',2);
    
-- 남성복 팬츠의 하위 카테고리
INSERT INTO TB_CATEGORIES(CATEGORY_NAME,PARENT_ID) 
	VALUES('면바지',3);
INSERT INTO TB_CATEGORIES(CATEGORY_NAME,PARENT_ID) 
	VALUES('슬랙스',3);
    
-- 남성복 팬츠의 하위 카테고리
INSERT INTO TB_CATEGORIES(CATEGORY_NAME,PARENT_ID) 
	VALUES('면바지',5);
INSERT INTO TB_CATEGORIES(CATEGORY_NAME,PARENT_ID) 
	VALUES('슬랙스',5);

(3) 프로덕트 테이블 설계

CREATE TABLE TB_PRODUCTS(
	PRODUCT_ID INT AUTO_INCREMENT PRIMARY KEY,
    PRODUCT_NAME VARCHAR(255),
    CATEGORY_ID INT,
    PRICE DECIMAL(10,2),
    SIZE VARCHAR(10),
    COLOR VARCHAR(50),
    FOREIGN KEY (CATEGORY_ID) REFERENCES TB_CATEGORIES(CATEGORY_ID)
);

(4) 프로덕트 테이블에 데이터 삽입

-- 남성복 상품 입력
INSERT INTO TB_PRODUCTS(PRODUCT_NAME,CATEGORY_ID,PRICE,SIZE,COLOR)
VALUES ('남성셔츠',4,49000,'L','퍼플'),
('남성면바지',7,55000,'M','핑크'),
('남성슬랙스',8,105000,'XL','샤인');

-- 여성복 상품 입력
INSERT INTO TB_PRODUCTS(PRODUCT_NAME,CATEGORY_ID,PRICE,SIZE,COLOR)
VALUES ('여성셔츠',6,49000,'L','퍼플'),
('여성면바지',9,55000,'M','핑크'),
('여성슬랙스',10,105000,'XL','퍼플');

(5) 응용 문제

-- 특정 카테고리에 속하는 모든 상품 조회
-- 1단계, 남성, 여성 팬츠에 속하는 상품을 모두 조회

-- 특정 카테고리에 속하는 모든 상품 조회
-- 1단계, 남성, 여성 팬츠에 속하는 상품을 모두 조회
SELECT P.PRODUCT_NAME,P.PRICE,P.SIZE,P.COLOR
FROM TB_PRODUCTS AS P
JOIN TB_CATEGORIES AS C ON P.CATEGORY_ID=C.CATEGORY_ID
WHERE C.CATEGORY_NAME='슬랙스';

-- 2단계, 만약 여성 면바지만 출력해야 한다면?
-- 서브쿼리를 이용해보자.
-- 서브쿼리: 쿼리 속의 쿼리

-- 2단계, 만약 여성 면바지만 출력해야 한다면?
-- 서브쿼리를 이용해보자.
-- 서브쿼리: 쿼리 속의 쿼리
SELECT P.PRODUCT_NAME,P.PRICE,P.SIZE,P.COLOR,C.PARENT_ID
FROM TB_PRODUCTS AS P
JOIN TB_CATEGORIES AS C ON P.CATEGORY_ID=C.CATEGORY_ID
WHERE C.CATEGORY_NAME='면바지' 
						AND c.parent_id=
                        (SELECT category_ID FROM TB_CATEGORIES 
                        WHERE category_name='팬츠' and parent_id=2);

3. 전체 코드

(1) 카테고리 테이블

create database demo3;

use demo3;

-- 카테고리 테이블 (상위, 하위 개념 추가)
create table tb_categories(
	CATEGORY_ID INT AUTO_INCREMENT PRIMARY KEY,
    PARENT_ID INT NULL,
    CATEGORY_NAME VARCHAR(100),	
    FOREIGN KEY(PARENT_ID) REFERENCES TB_CATEGORIES(CATEGORY_ID)
);

INSERT INTO TB_CATEGORIES(CATEGORY_NAME) VALUES('남성복');
INSERT INTO TB_CATEGORIES(CATEGORY_NAME) VALUES('여성복');

-- 남성복의 하위 카테고리
INSERT INTO TB_CATEGORIES(CATEGORY_NAME,PARENT_ID) 
	VALUES('팬츠',1);    
INSERT INTO TB_CATEGORIES(CATEGORY_NAME,PARENT_ID) 
	VALUES('셔츠',1);

-- 여성복의 하위 카테고리
INSERT INTO TB_CATEGORIES(CATEGORY_NAME,PARENT_ID) 
	VALUES('팬츠',2);    
INSERT INTO TB_CATEGORIES(CATEGORY_NAME,PARENT_ID) 
	VALUES('셔츠',2);
    
-- 남성복 팬츠의 하위 카테고리
INSERT INTO TB_CATEGORIES(CATEGORY_NAME,PARENT_ID) 
	VALUES('면바지',3);
INSERT INTO TB_CATEGORIES(CATEGORY_NAME,PARENT_ID) 
	VALUES('슬랙스',3);
    
-- 남성복 팬츠의 하위 카테고리
INSERT INTO TB_CATEGORIES(CATEGORY_NAME,PARENT_ID) 
	VALUES('면바지',5);
INSERT INTO TB_CATEGORIES(CATEGORY_NAME,PARENT_ID) 
	VALUES('슬랙스',5);
    
SELECT*FROM tb_categorieS;

(2) 프로덕트 테이블 및 연습문제

CREATE TABLE TB_PRODUCTS(
	PRODUCT_ID INT AUTO_INCREMENT PRIMARY KEY,
    PRODUCT_NAME VARCHAR(255),
    CATEGORY_ID INT,
    PRICE DECIMAL(10,2),
    SIZE VARCHAR(10),
    COLOR VARCHAR(50),
    FOREIGN KEY (CATEGORY_ID) REFERENCES TB_CATEGORIES(CATEGORY_ID)
);

-- 남성복 상품 입력
INSERT INTO TB_PRODUCTS(PRODUCT_NAME,CATEGORY_ID,PRICE,SIZE,COLOR)
VALUES ('남성셔츠',4,49000,'L','퍼플'),
('남성면바지',7,55000,'M','핑크'),
('남성슬랙스',8,105000,'XL','샤인');

-- 여성복 상품 입력
INSERT INTO TB_PRODUCTS(PRODUCT_NAME,CATEGORY_ID,PRICE,SIZE,COLOR)
VALUES ('여성셔츠',6,49000,'L','퍼플'),
('여성면바지',9,55000,'M','핑크'),
('여성슬랙스',10,105000,'XL','퍼플');

SELECT*FROM TB_PRODUCTS;

-- 특정 카테고리에 속하는 모든 상품 조회
-- 1단계, 남성, 여성 팬츠에 속하는 상품을 모두 조회
SELECT P.PRODUCT_NAME,P.PRICE,P.SIZE,P.COLOR
FROM TB_PRODUCTS AS P
JOIN TB_CATEGORIES AS C ON P.CATEGORY_ID=C.CATEGORY_ID
WHERE C.CATEGORY_NAME='슬랙스';

-- 2단계, 만약 여성 면바지만 출력해야 한다면?
-- 서브쿼리를 이용해보자.
-- 서브쿼리: 쿼리 속의 쿼리
SELECT P.PRODUCT_NAME,P.PRICE,P.SIZE,P.COLOR,C.PARENT_ID
FROM TB_PRODUCTS AS P
JOIN TB_CATEGORIES AS C ON P.CATEGORY_ID=C.CATEGORY_ID
WHERE C.CATEGORY_NAME='면바지' 
						AND c.parent_id=
                        (SELECT category_ID FROM TB_CATEGORIES 
                        WHERE category_name='팬츠' and parent_id=2);

SELECT PARENT_ID FROM TB_CATEGORIES WHERE category_name='팬츠';
SELECT PARENT_ID FROM TB_CATEGORIES 
	WHERE category_name='팬츠' and parent_id=2;
    
    
-- 문제 1. 남성복 셔츠만 출력하시오.
SELECT P.PRODUCT_NAME,P.PRICE,P.SIZE,P.COLOR
FROM TB_PRODUCTS AS P
JOIN TB_CATEGORIES AS C ON P.CATEGORY_ID=C.CATEGORY_ID
where c.category_name='셔츠' 
							and c.parent_id=
							(SELECT parent_id FROM TB_CATEGORIES 
                        WHERE category_name='셔츠' and parent_id=1);

-- 문제 2. 여성복 슬랙스만 출력하시오.
select P.PRODUCT_NAME,P.PRICE,P.SIZE,P.COLOR
from TB_PRODUCTS as p
join TB_CATEGORIES as c on p.CATEGORY_ID=c.CATEGORY_ID
where c.category_name='슬랙스' 
	and c.parent_id=
		(select category_id from TB_CATEGORIES
        where category_name='팬츠' and parent_id=2);

728x90

'MySQL' 카테고리의 다른 글

[MySQL] 43. 연습 문제: 서브 쿼리 및 기본 개념 다지기 (0)	2024.06.12
[MySQL] 42. 서브 쿼리 (0)	2024.06.12
[MySQL] 40. 쇼핑몰 서비스 DB 구축 (1) (0)	2024.06.12
[MySQL] 1일 1쿼리 (5) : Self join 및 기존 개념 복습 (1)	2024.06.11
[MySQL] ３９. JDBC 설치 및 설정 (0)	2024.06.11

다음 글이 없습니다.

이전 글이 없습니다.

[MySQL] 41. 쇼핑몰 서비스 DB 구축 (2)

'MySQL' 카테고리의 다른 글

티스토리툴바