tb_categories 테이블
Field Name | Data Type | escription | Constraints | Example Values |
category_id | INT | 카테고리의 고유 ID | PRIMARY KEY, AUTO_INCREMENT | 1, 2, 3 |
parent_id | INT | 상위 카테고리의 ID | FOREIGN KEY, NULL 가능 | NULL, 1 |
category_name | VARCHAR(100) | 카테고리 이름 | NOT NULL | "남성복", "팬츠", "셔츠" |
category_id | parent_id | category_name |
1 | NULL | 남성복 |
2 | NULL | 여성복 |
3 | 1 | 팬츠 |
4 | 2 | 팬츠 |
5 | 1 | 셔츠 |
6 | 2 | 셔츠 |
7 | 3 | 면바지 (남성복 →팬츠 → 면바지) |
8 | 4 | 면바지 (여성복 → 팬츠 → 면바지) |
9 | 3 | 슬랙스 (남성복 →팬츠 → 슬랙스 ) |
10 | 4 | 슬랙스(여성복→팬츠 → 슬랙스 ) |
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;
product_id | product_name | category_id | price | size | color |
102 | 남성 셔츠 | 4 | |||
104 | 여성 셔츠 | 6 | |||
105 | 남성 면바지 | 7 | 4 | ||
106 | 남성 슬랙스 | 8 | |||
107 | 여성 면바지 | 9 | |||
108 | 여성 슬랙스 | 10 |
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 = 5;
-- -------------------------
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 = '팬츠' ;
select parent_id from tb_categories
where category_name = '팬츠' and parent_id = 2;
-- 문제 1 남성복 셔츠만 출력 하시오
SELECT p.product_id, p.product_name, p.price, p.size, p.color
FROM tb_products p
JOIN tb_categories c ON p.category_id = c.category_id
WHERE c.category_name = '셔츠' AND c.parent_id = 1;
-- 문제 2 여성복 슬랙스만 출력 하시오
SELECT p.product_id, p.product_name, p.price, p.size, p.color
FROM tb_products p
JOIN tb_categories c ON p.category_id = c.category_id
WHERE c.category_name = '슬랙스' AND c.parent_id = 5;
-- 문제 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);
-- 문제 3 : demo3에서 남성슬랙스, 여성면바지의 상품이름, 가격, 사이즈, 색상을 출력하시오 단, 서브쿼리를 쓰시오.
use demo3;
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 = 1)
OR category_name = '면바지'
AND c.parent_id = (SELECT
category_id
FROM
tb_categories
WHERE
category_name = '팬츠'
AND parent_id = 2);
특정 색상의 상품이 있는 모든 카테고리 조회 (서브쿼리 사용할 필요 없음)
예를 들어, '흰색' 상품이 있는 모든 카테고리를 조회하는 쿼리입니다.
가장 비싼 상품을 가진 카테고리 찾기 (서브쿼리 사용할 필요 없음)
각 카테고리 중 가장 비싼 상품을 가지고 있는 카테고리와 그 상품의 정보를 조회하는 쿼리입니다.
'MySQL > MySQL 활용' 카테고리의 다른 글
쇼핑몰과 카테고리 1단계 - 5 (0) | 2024.06.12 |
---|---|
블로그 서비스의 DB 구축 - 4 (0) | 2024.06.11 |
쇼핑몰 서비스의 DB 구축 - 3 (0) | 2024.06.11 |
SELF JOIN - 2 (1) | 2024.06.11 |
정규화란(1단계) - 1 (0) | 2024.06.11 |