[MySQL] Database Table CRUD 시도해보기

앞서 그렸던 ERD를 바탕으로 데이터베이스를 작성해보고자 한다.

 

터미널에서 SQL로 들어가기

cd /usr/local/mysql/bin/
./mysql -uroot -p
비밀번호

 

데이터베이스 생성

CREATE DATABASE shopping_mall;

 

생성된 데이터베이스 확인

SHOW DATABASES;

 

사용할 데이터베이스 선택

USE shopping_mall;

 

테이블 구축

테이블 생성 (CREATE)

CREATE TABLE customer(
 	id VARCHAR(30) NOT NULL,
    password VARCHAR(255) NOT NULL,
    name VARCHAR(100) NOT NULL ,
    phone VARCHAR(20) NULL,
    zoneCode TEXT,
    regionAddress TEXT,
    detailAddress TEXT,
    gender VARCHAR(1),
    points INT,
    PRIMARY KEY(id)
);

CREATE TABLE product(
 	id INT auto_increment,
    name VARCHAR(255) NOT NULL,
    price INT NOT NULL,
    category VARCHAR(20) NOT NULL,
    size VARCHAR(10),
    color VARCHAR(20),
    avgRate INT,
    imgUrl VARCHAR(200) NOT NULL,
    quantity INT NOT NULL,
    PRIMARY KEY(id)
);

CREATE TABLE `order`(
 	id VARCHAR(255) NOT NULL,
    customerId VARCHAR(30) NOT NULL,
    orderDate DATE NOT NULL,
    totalSalePrice INT,
    totalSaleQty INT,
    orderer VARCHAR(255) NOT NULL,
    ordererPhone VARCHAR(30) NOT NULL,
    shippingAddress VARCHAR(255) NOT NULL,
    repProdName VARCHAR(255),	// 주문 내역 화면에서 보여주기 위한 대표 제품 이름
    repProdImg VARCHAR(200),	// 주문 내역 화면에서 보여주기 위한 대표 제품 이미지
    PRIMARY KEY(id),
    FOREIGN KEY (customerId) REFERENCES customer (id),
);

CREATE TABLE orderItem (
	id INT auto_increment,
    customerId VARCHAR(30) NOT NULL,
    productId INT NOT NULL,
    orderId VARCHAR(255) NOT NULL,
    orderQuantity INT NOT NULL,
    orderPrice INT,
    deliveryStatus VARCHAR(255),
	orderStatus VARCHAR(255),    
    hasReview BOOLEAN DEFAULT false,
    PRIMARY KEY(id),
    FOREIGN KEY (customerId) REFERENCES customer (id),
    FOREIGN KEY (productId) REFERENCES proudct (id),
    FOREIGN KEY (orderId) REFERENCES `order` (id)
);

CREATE TABLE review(
 	id INT AUTO_INCREMENT,
    customerId VARCHAR(30) NOT NULL,
    orderItemId INT NOT NULL,
    title TEXT NOT NULL,
    content LONGTEXT NULL, 
    createdDate DATE NOT NULL,
    PRIMARY KEY(id),
    FOREIGN KEY (customerId) REFERENCES customer (id),
    FOREIGN KEY (orderItemId) REFERENCES orderItem (id)
);

// CREATE TABLE delivery(
// 	id INT AUTO_INCREMENT,
//    status VARCHAR(20) NOT NULL,
//    orderId INT NOT NULL,
//    PRIMARY KEY(id),
//    FOREIGN KEY (orderId) REFERENCES `order` (id)
// );

CREATE TABLE qAndA(
 	id INT AUTO_INCREMENT,
    customerId VARCHAR(30) NOT NULL,
    productId INT NOT NULL,
    rate INT NOT NULL,
    createdDate DATETIME NOT NULL,
    PRIMARY KEY(id),
    FOREIGN KEY (customerId) REFERENCES customer (id),
    FOREIGN KEY (productId) REFERENCES product (id)
);

CREATE TABLE shoppingCart(
 	id INT AUTO_INCREMENT,
    customerId VARCHAR(30) NOT NULL,
    productId INT NOT NULL,
    quantity INT NOT NULL,
    PRIMARY KEY(id),
    FOREIGN KEY (customerId) REFERENCES customer (id),
    FOREIGN KEY (productId) REFERENCES product (id)
);

 

현재 테이블 리스트 보기

SHOW TABLES;

 

테이블 스키마 구조 보기 (DESC)

DESC `테이블명`

 

테이블명 변경

RENAME TABLE 기존테이블명 TO 새로운테이블명;

 

테이블 수정 (ALTER)

ALTER TABLE 테이블이름 
ADD [CONSTRAINT 제약조건이름] 
FOREIGN KEY (필드이름) 
REFERENCES 테이블이름 (필드이름)

(예시1 - 외래키추가)
ALTER TABLE `order`
ADD FOREIGN KEY (customerId)
REFERENCES `customer` (id)

(예시2 - 그냥키추가)
ALTER TABLE `order`
ADD quantity INT

* 이미지 컬럼 추가하려면 타입을 Blob으로 설정해야한다.

 

데이터 생성

INSERT INTO 테이블명(값을 넣을 필드들) values ('값들');

 

테이블 내 데이터 확인

SELECT * FROM 테이블명;

 

테이블 컬럼 삭제

1) 그냥 컬럼 삭제

ALTER TABLE `review` DROP `OrderId`

2) Foreign Key 삭제

ALTER TABLE `review` DROP FOREIGN KEY `OrderId`

3) Primary Key 삭제

ALTER TABLE `review` DROP PRIMARY KEY `OrderId`

 

PRIMARY KEY 변경

primary key 삭제 후, primary key 지정해야한다.

ALTER TABLE `order` DROP PRIMARY KEY `id`;
ALTER TABLE `order` ADD CONSTRAINT PRIMARY KEY (id, productId);

 

FOREIGN KEY 추가

ALTER TABLE review ADD FOREIGN KEY(orderId) references `order`(id);

 

컬럼 속성 변경

ALTER TABLE 테이블명 CHANGE 기존컬럼명 변경할컬럼명 컬럼속성;

(예시1)
ALTER TABLE customer CHANGE gender gender varchar(1);

(예시2) Not Null 변경
ALTER TABLE orderItem MODIFY COLUMN customerId varchar(30) NOT NULL;

 

데이터 수정

UPDATE 테이블명 SET 컬럼명=수정값 WHERE 조건;

(예시)
update product set img=load_file('/Users/jihyun/BeDeveloper/18.ShoppingMall/shop/public/image/top1.jpg') where id=1;

 

데이터 삭제

DELETE FROM 테이블명 WHERE 조건;