MySQL 기초 복습 - 12

복습할 DB 데이터 (shop)

-- 쇼핑몰 
drop database if exists shop;
create database shop;
use shop;

-- 회원 테이블  일반, VIP
create table member(
	id bigint primary key auto_increment,
    email varchar(100) not null, 
    name varchar(50) not null, 
    phone varchar(20),
    grade varchar(10) default '일반', 
    created_at datetime  default current_timestamp
);
select * from member;

-- 상품 카테고리 테이블 
create table category(
	id int primary key auto_increment, 
    name varchar(50) not null
);

select * from category;

-- 상품 테이블 
create table product(
	id int primary key auto_increment, 
    category_id int not null, 
    name varchar(100) not null, 
    price decimal(10, 2) not null, 
    stock int default 0, 
    description text, 
    created_at datetime default current_timestamp,
    foreign key(category_id) references category(id)
);

-- 주문 테이블  : 테이블 명에 예약어 사용하려면 백틱(`) 으로 감싸줘야 한다. 
create table orders (
	id int primary key auto_increment, 
    member_id bigint not null, 
    total_price decimal(10, 2) not null, 
    status varchar(20) default '결제대기',
    created_at datetime default current_timestamp, 
    foreign key (member_id) references member(id)
);

select * from orders; 

-- 주문 상세 테이블 (주문 1건에 여러 상품)
create table order_item(
	id int primary key auto_increment, 
    order_id int not null, 
    product_id int not null, 
    quantity int not null, 
    price decimal(10, 2) not null, 
    foreign key (order_id) references orders(id), 
    foreign key (product_id) references product(id)
);

show tables;

 

USE shop;

-- 카테고리 데이터
INSERT INTO category (name) VALUES
('전자제품'), ('의류'), ('식품'), ('도서'), ('스포츠');

-- 회원 데이터
INSERT INTO member (email, name, phone, grade) VALUES
('hong@test.com',  '홍길동', '010-1111-2222', 'VIP'),
('kim@test.com',   '김영희', '010-2222-3333', '일반'),
('lee@test.com',   '이철수', '010-3333-4444', 'VIP'),
('park@test.com',  '박민지', '010-4444-5555', '일반'),
('choi@test.com',  '최재영', '010-5555-6666', 'GOLD');

-- 상품 데이터
INSERT INTO product (category_id, name, price, stock, description) VALUES
(1, '삼성 갤럭시 S24',  1200000.00,  50, '최신 안드로이드 스마트폰'),
(1, '애플 아이폰 15',   1500000.00,  30, '애플의 플래그십 스마트폰'),
(1, '삼성 노트북',      1300000.00,  20, '가성비 좋은 업무용 노트북'),
(2, '나이키 운동화',      150000.00, 100, '에어 쿠션 운동화'),
(2, '아디다스 티셔츠',     59000.00, 200, '드라이핏 기능성 티셔츠'),
(3, '곰표 밀가루 1kg',      2500.00, 500, '국산 밀 100% 밀가루'),
(4, '자바 완전정복',       35000.00,  80, '자바 기초부터 심화까지'),
(4, '스프링 부트 실전',     42000.00,  60, '스프링 부트 실전 가이드'),
(1, 'LG 모니터 27인치',   450000.00,  40, '4K UHD 모니터'),
(5, '요가 매트',           45000.00, 150, '친환경 TPE 요가 매트');

-- 주문 데이터
INSERT INTO orders (member_id, total_price, status) VALUES
(1, 1200000.00, '배송완료'),
(1,   35000.00, '배송중'),
(2, 1500000.00, '배송완료'),
(3,  150000.00, '결제완료'),
(3,   59000.00, '배송중'),
(4,   42000.00, '결제대기'),
(5,  450000.00, '배송완료');

-- 주문 상세 데이터
INSERT INTO order_item (order_id, product_id, quantity, price) VALUES
(1, 1, 1, 1200000.00),
(2, 7, 1, 35000.00),
(3, 2, 1, 1500000.00),
(4, 4, 1, 150000.00),
(5, 5, 1, 59000.00),
(6, 8, 1, 42000.00),
(7, 9, 1, 450000.00);

-- 전체 확인
SELECT * FROM member;
SELECT * FROM product;
SELECT * FROM orders;

 

---

DDL 연습

-- 1. product 테이블에 is_deleted 컬럼 추가 (소프트 삭제용)
ALTER TABLE product ADD COLUMN is_deleted BOOLEAN DEFAULT FALSE;

-- 2. orders 테이블에 주문일 기준 인덱스 추가
CREATE INDEX idx_created_at ON orders (created_at);

-- 3. 인덱스 확인
SHOW INDEX FROM orders;

 

---

DML 연습

SELECT

-- VIP 회원만 조회
SELECT * FROM member WHERE grade = 'VIP';

-- 가격 50만원 이상 상품 조회 (이름, 가격만)
SELECT name, price FROM product WHERE price >= 500000;

-- 배송 중인 주문 조회
SELECT * FROM orders WHERE status = '배송중';

-- 재고가 50개 미만인 상품
SELECT name, stock FROM product WHERE stock < 50;

-- 도서 카테고리 상품 (category_id = 4)
SELECT * FROM product WHERE category_id = 4;

 

UPDATE

-- 수정 전 확인
SELECT * FROM member WHERE email = 'kim@test.com';

-- 김영희 등급을 GOLD 로 업그레이드
UPDATE member
SET grade = 'GOLD'
WHERE email = 'kim@test.com';

-- 주문 상태 변경 (결제대기 → 결제완료)
UPDATE orders
SET status = '결제완료'
WHERE id = 6;

-- 상품 재고 감소 (구매 처리)
UPDATE product
SET stock = stock - 1
WHERE id = 1;

 

DELETE (소프트 삭제)

-- 실제 DELETE 대신 is_deleted = TRUE 로 표시
UPDATE product
SET is_deleted = TRUE
WHERE id = 6;

-- 삭제되지 않은 상품만 조회
SELECT * FROM product WHERE is_deleted = FALSE;

 

 

제약 조건 확인 연습

-- 유니크 키 추가 , 안되는 이유 확인, 처리 
alter table member add constraint uk_email unique (email);
-- 또는 짧은 구문으로도 생성 가능 
alter table member add unique (email);

-- UNIQUE 위반 테스트
INSERT INTO member (email, name) VALUES ('hong@test.com', '홍길동2');
-- → 오류: Duplicate entry 'hong@test.com' for key 'email'

-- FK 위반 테스트
INSERT INTO orders (member_id, total_price) VALUES (999, 50000.00);
-- → 오류: foreign key constraint fails

-- NOT NULL 위반 테스트
INSERT INTO product (category_id, price, stock) VALUES (1, 50000.00, 10);
-- → 오류: name cannot be null