쇼핑몰 DB 문제 1

문제

• 조건 1. 아래 5개의 테이블을 구현해주세요(각 테이블에 데이터를 최소 3개 이상 넣어주세요)
  • member(회원)
  • cateItem(카테고리)
  • product(상품)
  • productReal(상품실물)
  • cartItem(장바구니 품목)
• 조건 2. 회원만 구매가 가능
• 조건 3. 상품 중에 1개는 아래와 같은 정보로 구성
  • 카테고리 : 1차 : 일반, 2차 : 여성의류, 3차 : 상의, 4차 : 원피스
  • 상품명 : 단가라 쉬폰 원피스
  • 가격 : 39,800원
  • 옵션1 : 색상(레드,블루)
  • 옵션2 : 사이즈(44, 55, 66)
• 조건 4. 상품의 재고정보
  • 레드, 44 : 10개
  • 레드, 55 : 20개
  • 레드, 66 : 0개
  • 블루, 44 : 12개
  • 블루, 55 : 14개
  • 블루, 66 : 16개

1. 회원테이블 생성

# 데이터베이스 생성
DROP DATABASE IF EXISTS shop1;
CREATE DATABASE shop1;
USE shop1;

# 회원 테이블
CREATE TABLE `member` (
    id INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    regDate DATETIME NOT NULL,
    updateDate DATETIME NOT NULL,
    loginId CHAR(50) NOT NULL,
    loginPw CHAR(50) NOT NULL,
    `name` CHAR(30) NOT NULL,
    email CHAR(50) NOT NULL,
    cellphoneNo CHAR(20) NOT NULL
);

# 회원 1명 추가
INSERT INTO `member`
SET regDate = NOW(),
updateDate = NOW(),
loginId = 'user1',
loginPw = '1234',
`name` = '홍길동',
email = 'test@test.com',
cellphoneNo = '01012341234';

# 회원 데이터 대량 생성, 100만명 될 때 까지 반복실행
INSERT INTO `member`
(
    regDate,
    updateDate,
    loginId,
    loginPw,
    `name`,
    email,
    cellphoneNo
)
SELECT NOW(),
NOW(),
CONCAT('user', RAND()),
'1234',
'홍길동',
'test@test.com',
'01012341234'
FROM `member`;

# 전체 회원 수 확인
SELECT COUNT(*)
FROM `member`;

# 굉장히 빠름
# 이유 : 인덱스가 있어서
SELECT SQL_NO_CACHE *
FROM `member`
WHERE id = 1;

# 굉장히 느림
# 이유 : 풀스캔
SELECT SQL_NO_CACHE *
FROM `member`
WHERE loginId = 'user1';

# loginId 에 대해서 색인 생성
ALTER TABLE `member` ADD INDEX (`loginId`); 

# 굉장히 빠름
# 이유 : 인덱스 사용
SELECT SQL_NO_CACHE *
FROM `member`
WHERE loginId = 'user1';

2. 회원테이블의 loginID 칼럼에 unique 인덱스 추가

# 인덱스 제거
ALTER TABLE `member` DROP INDEX `loginId`; 

# 회원테이블의 모든 데이터 제거
TRUNCATE `member`;

# 일반 인덱스가 아닌 유니크 인덱스 추가
ALTER TABLE `member` ADD UNIQUE INDEX (`loginId`);

# 회원 1명 다시 추가
INSERT INTO `member`
SET regDate = NOW(),
updateDate = NOW(),
loginId = 'user1',
loginPw = '1234',
`name` = '홍길동',
email = 'test@test.com',
cellphoneNo = '01012341234';

3. 카테고리 테이블 만들기

# cateItem 테이블 만들기
DROP TABLE IF EXISTS cateItem;

CREATE TABLE cateItem (
    id INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    regDate DATETIME NOT NULL,
    updateDate DATETIME NOT NULL,
    depth1Name CHAR(30) NOT NULL,
    depth1Code CHAR(30) NOT NULL,
    depth2Name CHAR(30) NOT NULL,
    depth2Code CHAR(30) NOT NULL,
    depth3Name CHAR(30) NOT NULL,
    depth3Code CHAR(30) NOT NULL,
    depth4Name CHAR(30) NOT NULL,
    depth4Code CHAR(30) NOT NULL
);

INSERT INTO cateItem
SET regDate = NOW(),
updateDate = NOW(),
depth1Name = '일반',
depth1Code = 'common',
depth2Name = '여성의류',
depth2Code = 'woman',
depth3Name = '원피스',
depth3Code = 'one-piece',
depth4Name = '원피스',
depth4Code = 'one-piece';

INSERT INTO cateItem
SET regDate = NOW(),
updateDate = NOW(),
depth1Name = '일반',
depth1Code = 'common',
depth2Name = '여성의류',
depth2Code = 'woman',
depth3Name = '상의',
depth3Code = 'top',
depth4Name = '티셔츠',
depth4Code = 't-shirts';

4. 상품관련 테이블 만들기

# product 테이블 만들기
DROP TABLE IF EXISTS product;

CREATE TABLE product (
    id INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    regDate DATETIME NOT NULL,
    updateDate DATETIME NOT NULL,
    `name` CHAR(50) NOT NULL,
    price INT(10) UNSIGNED NOT NULL,
    salePrice INT(10) UNSIGNED NOT NULL,
    cateItemId INT(10) UNSIGNED NOT NULL
);

INSERT INTO product
SET regDate = NOW(),
updateDate = NOW(),
`name` = '단가라 쉬폰 원피스',
price = 39800,
salePrice = 39800,
cateItemId = 1;

# productReal 테이블 만들기
DROP TABLE IF EXISTS productReal;

CREATE TABLE productReal (
    id INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    regDate DATETIME NOT NULL,
    updateDate DATETIME NOT NULL,
    productId INT(10) UNSIGNED NOT NULL,
    `quantity` SMALLINT(2) UNSIGNED NOT NULL,
    opt1Name CHAR(50) NOT NULL,
    opt1Code CHAR(50) NOT NULL,
    opt2Name CHAR(50) NOT NULL,
    opt2Code CHAR(50) NOT NULL,
    opt3Name CHAR(50) NOT NULL,
    opt3Code CHAR(50) NOT NULL
);

# RED, 44, 10개
INSERT INTO productReal
SET regDate = NOW(),
updateDate = NOW(),
productId = 1,
opt1Name = 'RED',
opt1Code = 'RED',
opt2Name = '44',
opt2Code = '44',
opt3Name = '',
opt3Code = '',
quantity = 10;

# RED, 55, 20개
INSERT INTO productReal
SET regDate = NOW(),
updateDate = NOW(),
productId = 1,
opt1Name = 'RED',
opt1Code = 'RED',
opt2Name = '55',
opt2Code = '55',
opt3Name = '',
opt3Code = '',
quantity = 20;

# RED, 66, 0개
INSERT INTO productReal
SET regDate = NOW(),
updateDate = NOW(),
productId = 1,
opt1Name = 'RED',
opt1Code = 'RED',
opt2Name = '66',
opt2Code = '66',
opt3Name = '',
opt3Code = '',
quantity = 0;

# BLUE, 44, 12개
INSERT INTO productReal
SET regDate = NOW(),
updateDate = NOW(),
productId = 1,
opt1Name = 'BLUE',
opt1Code = 'BLUE',
opt2Name = '44',
opt2Code = '44',
opt3Name = '',
opt3Code = '',
quantity = 12;

# BLUE, 55, 14개
INSERT INTO productReal
SET regDate = NOW(),
updateDate = NOW(),
productId = 1,
opt1Name = 'BLUE',
opt1Code = 'BLUE',
opt2Name = '55',
opt2Code = '55',
opt3Name = '',
opt3Code = '',
quantity = 14;

# BLUE, 66, 16개
INSERT INTO productReal
SET regDate = NOW(),
updateDate = NOW(),
productId = 1,
opt1Name = 'BLUE',
opt1Code = 'BLUE',
opt2Name = '66',
opt2Code = '66',
opt3Name = '',
opt3Code = '',
quantity = 16;