MySQL

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

Song hyun 2024. 6. 12. 09: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
반응형