2024.06.12 MySQL 활용 쇼핑몰과 카테고리 2단계

class_29.sql

class_30.sql

class_29.sql

0.00MB

class_30.sql

0.00MB

tb_categories 테이블

Field Name	Data Type	Description	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);

select *
from tb_categories; 

-- 여성복 팬츠에 하위 카테고리
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	category_name	category_id	price	size	color
101	남성 셔츠	4
102	여성 셔츠	6
103	남성 면바지	7
104	남성 슬랙스	8
105	여성 면바지	9
106	여성 슬랙스	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)
);

select *
from tb_products;

select *
from tb_categories; 

-- 남성복 상품 입력
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 -- join 보다 on 이 연산순서가 먼저이다.
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 -- join 보다 on 이 연산순서가 먼저이다.
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 -- join 보다 on 이 연산순서가 먼저이다.
where c.category_name = '면바지' 
						and c.parent_id =
									(select * from tb_categories where category_name = '팬츠' );


select *, parent_id from tb_categories where category_name = '팬츠';

select parent_id from tb_categories where category_name = '팬츠';

select category_id, parent_id from tb_categories 
where category_name = '팬츠' and parent_id = 2;

select category_id from tb_categories 
where category_name = '팬츠' and parent_id = 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 -- join 보다 on 이 연산순서가 먼저이다.
where c.category_name = '면바지' 
						and c.parent_id =
									(select category_id from tb_categories 
									where category_name = '팬츠' and parent_id = 2);
                                    
-- 문제 1 남성복 셔츠만 출력 하시오.
-- 테이블 확인
select * from tb_products;
select * from tb_categories;

select *
from tb_products as p
join tb_categories as c
on p.category_id = c.category_id;

-- 문제 1 남성복 셔츠만 출력 하시오.
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 p.product_name like '%셔츠' and c.parent_id = 1;


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.category_id = (select category_id
						from tb_categories where category_name = '셔츠' and category_id = 4);

select category_id
from tb_categories
where category_name = '셔츠' and category_id = 4;


-- 문제 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_id =
		(select category_id from tb_categories where category_name = '슬랙스' and parent_id = 5);

-- 특정 색상의 상품이 있는 모든 카테고리 조회 (서브쿼리 사용할 필요 없음)
-- 예를 들어, '흰색' 상품이 있는 모든 카테고리를 조회하는 쿼리입니다.

-- 테이블 확인
select * from tb_products;
select * from tb_categories;

select *
from tb_products as p
join tb_categories as c
on p.category_id = c.category_id;

select p.product_name, p.price, p.color, c.category_name, c.parent_id
from tb_products as p
join tb_categories as c
on p.category_id = c.category_id
where p.color = '퍼블';

-- 가장 비싼 상품을 가진 카테고리 찾기 (서브쿼리 사용할 필요 없음)
-- 각 카테고리 중 가장 비싼 상품을 가지고 있는 카테고리와 그 상품의 정보를 조회하는 쿼리입니다

-- 테이블 확인
select * from tb_products;
select * from tb_categories;

select *
from tb_products as p
join tb_categories as c
on p.category_id = c.category_id;

select p.product_name, max(p.price), p.color, c.category_id, c.category_name
from tb_products as p
join tb_categories as c
on p.category_id = c.category_id;

 

특정 색상의 상품이 있는 모든 카테고리 조회 (서브쿼리 사용할 필요 없음)

예를 들어, '흰색' 상품이 있는 모든 카테고리를 조회하는 쿼리입니다.

-- 1단계  - 검색정 
-- 결과집합에서 중복 제거할때 distinct 
select distinct c.category_name
from tb_products as p 
join tb_categories as c on p.category_id = c.category_id
where p.color = '샤인';

 

가장 비싼 상품을 가진 카테고리 찾기 (서브쿼리 사용할 필요 없음)

각 카테고리 중 가장 비싼 상품을 가지고 있는 카테고리와 그 상품의 정보를 조회하는 쿼리입니다.

use demo3;

-- 특정 색상의 상품이 있는 모든 카테고리 조회
select * from tb_products;
select * from tb_categories;

-- 특정 색상의 상품이 있는 모든 카테고리 조회
select c.category_name, 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 p.color = '샤인';

-- 1단계  - 검색정 
-- 결과집합에서 중복 제거할때 distinct 
select distinct c.category_name
from tb_products as p 
join tb_categories as c on p.category_id = c.category_id
where p.color = '샤인';


select c.category_name, 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
order by price desc
limit 0,2;

-- 가장 비싼 상품을 가진 카테고리 찾기 (서브쿼리 사용할 필요 없음
select *,  MAX(p.price) as max_price
from tb_products p 
join tb_categories c on  p.category_id = c.category_id
group by c.category_name
order by max_price desc
limit 2