미니 쇼핑몰 ddl, 상품 주문까지 sql

 

 

-- 회원 
insert into member (name) values ("길동"),("재석"),("호동");
select * from member;

-- 아이템 등록 
insert into Item (name, price) values ("쌀밥", 1500),("육개장", 8000),("계란후라이", 800);
select * from Item;

-- 길동이 장바구니에 담음
insert into cart (memberId, itemId, count) values (1, 1, 2), (1, 2, 1), (1,3,3);
select * from cart;

---- 주문하기
-- 입력한 배송정보
insert into delivery (status, address) values ("준비", "신대방역");
select * from delivery;

-- 주문 생성
insert into orders (memberId, deliveryId, status) values (1, 1, "주문");
select * from orders;

-- 주문된 아이템 등록
insert into orderItem (orderId, itemId, count) 
select 1, itemId, count from cart where memberId = 1;

select * from orderItem;

-- 장바구니 비우기
delete from cart where memberId=1;

-- 길동이 주문한거 보기
select * from orders join orderItem on orders.id = orderItem.orderId where orders.memberId = 1;

select * from orders where memberId=1;
select * from orders join delivery on orders.deliveryId = delivery.id where memberId=1;

-- 배송 시작, 완료
update delivery set status="배송시작" where id = 1;
select * from delivery;
update delivery set status="완료" where id = 1;

 

 

CREATE TABLE member (
    id bigint NOT NULL AUTO_INCREMENT primary key,
	name varchar(16) NOT NULL,
    createdAt timestamp DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE cart (
    id bigint NOT NULL AUTO_INCREMENT primary key,
    memberId bigint NOT NULL,
	itemId bigint NOT NULL,
    count int NOT NULL,
    createdAt timestamp DEFAULT CURRENT_TIMESTAMP,
    index idx_member (memberId),
    index idx_item (itemId)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE Item (
    id bigint NOT NULL AUTO_INCREMENT primary key,
    name varchar(16) Not Null,
    price int NOT NULL,
    createdAt timestamp DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE orders (
    id bigint NOT NULL AUTO_INCREMENT primary key,
    memberId bigint NOT NULL,
	deliveryId bigint NOT NULL,
    status varchar(16) NOT NULL,
    createdAt timestamp DEFAULT CURRENT_TIMESTAMP,
    index idx_member (memberId),
    index idx_delivery (deliveryId)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE delivery (
    id bigint NOT NULL AUTO_INCREMENT primary key,
	status varchar(16) NOT NULL,
	address varchar(16) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE orderItem (
    id bigint NOT NULL AUTO_INCREMENT primary key,
    orderId bigint NOT NULL,
    itemId bigint NOT NULL,
    count int NOT NULL,
	index idx_order (orderId),
    index idx_item (itemId)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;