MySQL 명령어 : DML(INSERT, UPDATE, DELETE, REPLACE)

MySQL 의 DML(INSERT, UPDATE, DELETE, REPLACE) 에 대해 알아보고 간단한 실습까지 진행해보려고 한다.

실습 진행을 위해 아래의 menu db를 사용할 것이다. ERD로 나타내면 아래와 같고 실습 전에 그림 밑에 MySQL에서 사용할 db의 script를 넣고 진행하면 된다.

 

MENU DB

• ERD (Entity Relationship Diagram)
  • 논리모델

• 물리모델

 

 

 

use menudb;

-- 테이블 삭제
DROP TABLE IF EXISTS tbl_payment_order CASCADE;
DROP TABLE IF EXISTS tbl_payment CASCADE;
DROP TABLE IF EXISTS tbl_order_menu CASCADE;
DROP TABLE IF EXISTS tbl_order CASCADE;
DROP TABLE IF EXISTS tbl_menu CASCADE;
DROP TABLE IF EXISTS tbl_category CASCADE;

-- 테이블 생성
-- category 테이블 생성
CREATE TABLE IF NOT EXISTS tbl_category
(
    category_code    INT AUTO_INCREMENT COMMENT '카테고리코드',
    category_name    VARCHAR(30) NOT NULL COMMENT '카테고리명',
    ref_category_code    INT COMMENT '상위카테고리코드',
    CONSTRAINT pk_category_code PRIMARY KEY (category_code),
    CONSTRAINT fk_ref_category_code FOREIGN KEY (ref_category_code) REFERENCES tbl_category (category_code)
) ENGINE=INNODB COMMENT '카테고리';

CREATE TABLE IF NOT EXISTS tbl_menu
(
    menu_code    INT AUTO_INCREMENT COMMENT '메뉴코드',
    menu_name    VARCHAR(30) NOT NULL COMMENT '메뉴명',
    menu_price    INT NOT NULL COMMENT '메뉴가격',
    category_code    INT NOT NULL COMMENT '카테고리코드',
    orderable_status    CHAR(1) NOT NULL COMMENT '주문가능상태',
    CONSTRAINT pk_menu_code PRIMARY KEY (menu_code),
    CONSTRAINT fk_category_code FOREIGN KEY (category_code) REFERENCES tbl_category (category_code)
) ENGINE=INNODB COMMENT '메뉴';


CREATE TABLE IF NOT EXISTS tbl_order
(
    order_code    INT AUTO_INCREMENT COMMENT '주문코드',
    order_date    VARCHAR(8) NOT NULL COMMENT '주문일자',
    order_time    VARCHAR(8) NOT NULL COMMENT '주문시간',
    total_order_price    INT NOT NULL COMMENT '총주문금액',
    CONSTRAINT pk_order_code PRIMARY KEY (order_code)
) ENGINE=INNODB COMMENT '주문';


CREATE TABLE IF NOT EXISTS tbl_order_menu
(
    order_code INT NOT NULL COMMENT '주문코드',
    menu_code    INT NOT NULL COMMENT '메뉴코드',
    order_amount    INT NOT NULL COMMENT '주문수량',
    CONSTRAINT pk_comp_order_menu_code PRIMARY KEY (order_code, menu_code),
    CONSTRAINT fk_order_menu_order_code FOREIGN KEY (order_code) REFERENCES tbl_order (order_code),
    CONSTRAINT fk_order_menu_menu_code FOREIGN KEY (menu_code) REFERENCES tbl_menu (menu_code)
) ENGINE=INNODB COMMENT '주문별메뉴';


CREATE TABLE IF NOT EXISTS tbl_payment
(
    payment_code    INT AUTO_INCREMENT COMMENT '결제코드',
    payment_date    VARCHAR(8) NOT NULL COMMENT '결제일',
    payment_time    VARCHAR(8) NOT NULL COMMENT '결제시간',
    payment_price    INT NOT NULL COMMENT '결제금액',
    payment_type    VARCHAR(6) NOT NULL COMMENT '결제구분',
    CONSTRAINT pk_payment_code PRIMARY KEY (payment_code)
) ENGINE=INNODB COMMENT '결제';


CREATE TABLE IF NOT EXISTS tbl_payment_order
(
    order_code    INT NOT NULL COMMENT '주문코드',
    payment_code    INT NOT NULL COMMENT '결제코드',
    CONSTRAINT pk_comp_payment_order_code PRIMARY KEY (payment_code, order_code),
    CONSTRAINT fk_payment_order_order_code FOREIGN KEY (order_code) REFERENCES tbl_order (order_code),
    CONSTRAINT fk_payment_order_payment_code FOREIGN KEY (order_code) REFERENCES tbl_payment (payment_code)
) ENGINE=INNODB COMMENT '결제별주문';

-- 데이터 삽입
INSERT INTO tbl_category VALUES (null, '식사', null);
INSERT INTO tbl_category VALUES (null, '음료', null);
INSERT INTO tbl_category VALUES (null, '디저트', null);
INSERT INTO tbl_category VALUES (null, '한식', 1);
INSERT INTO tbl_category VALUES (null, '중식', 1);

INSERT INTO tbl_category VALUES (null, '일식', 1);
INSERT INTO tbl_category VALUES (null, '퓨전', 1);
INSERT INTO tbl_category VALUES (null, '커피', 2);
INSERT INTO tbl_category VALUES (null, '쥬스', 2);
INSERT INTO tbl_category VALUES (null, '기타', 2);

INSERT INTO tbl_category VALUES (null, '동양', 3);
INSERT INTO tbl_category VALUES (null, '서양', 3);

INSERT INTO tbl_menu VALUES (null, '열무김치라떼', 4500, 8, 'Y');
INSERT INTO tbl_menu VALUES (null, '우럭스무디', 5000, 10, 'Y');
INSERT INTO tbl_menu VALUES (null, '생갈치쉐이크', 6000, 10, 'Y');
INSERT INTO tbl_menu VALUES (null, '갈릭미역파르페', 7000, 10, 'Y');
INSERT INTO tbl_menu VALUES (null, '앙버터김치찜', 13000, 4, 'N');

INSERT INTO tbl_menu VALUES (null, '생마늘샐러드', 12000, 4, 'Y');
INSERT INTO tbl_menu VALUES (null, '민트미역국', 15000, 4, 'Y');
INSERT INTO tbl_menu VALUES (null, '한우딸기국밥', 20000, 4, 'Y');
INSERT INTO tbl_menu VALUES (null, '홍어마카롱', 9000, 12, 'Y');
INSERT INTO tbl_menu VALUES (null, '코다리마늘빵', 7000, 12, 'N');

INSERT INTO tbl_menu VALUES (null, '정어리빙수', 10000, 10, 'Y');
INSERT INTO tbl_menu VALUES (null, '날치알스크류바', 2000, 10, 'Y');
INSERT INTO tbl_menu VALUES (null, '직화구이젤라또', 8000, 12, 'Y');
INSERT INTO tbl_menu VALUES (null, '과메기커틀릿', 13000, 6, 'Y');
INSERT INTO tbl_menu VALUES (null, '죽방멸치튀김우동', 11000, 6, 'N');

INSERT INTO tbl_menu VALUES (null, '흑마늘아메리카노', 9000, 8, 'Y');
INSERT INTO tbl_menu VALUES (null, '아이스가리비관자육수', 6000, 10, 'Y');
INSERT INTO tbl_menu VALUES (null, '붕어빵초밥', 35000, 6, 'Y');
INSERT INTO tbl_menu VALUES (null, '까나리코코넛쥬스', 9000, 9, 'Y');
INSERT INTO tbl_menu VALUES (null, '마라깐쇼한라봉', 22000, 5, 'N');

INSERT INTO tbl_menu VALUES (null, '돌미나리백설기', 5000, 11, 'Y');

COMMIT;

 

 

 

# DML 

SELECT menu_code, menu_name, menu_price, category_code, orderable_status
FROM tbl_menu;

# INSERT
-- INSERT INTO 테이블명 VALUES (컬럼순으로, 삽입할, 데이터, 나열, ...)
INSERT INTO tbl_menu VALUES (null, '곰탕', 9500, 6, 'Y'); #null로 만들었지만 menu_code는 AUTO_INCREMENT이기 때문에 22로 들어감 

-- INSERT INTO 테이블명 (컬럼명1, 컬럼명2, 컬럼명3, ...)
-- VALUES (데이터1, 데이터2, 데이터3, ...)
INSERT INTO tbl_menu(menu_code, menu_name, menu_price, orderable_status, category_code)
VALUES (null, '차돌짬뽕',15000,'Y',6);


INSERT INTO tbl_menu(menu_name, menu_price, category_code, orderable_status) # menu_code 는 AUTO_INCREMENT로 생성했기 때문에 제외하고 사용가능
VALUES ('만잔라떼',4500,'7','Y');

-- MULTI INSERT
INSERT INTO tbl_menu
VALUES
(null, '유자민트티', 6900, 12, 'Y'),
(null, '프렌치프라이', 7500, 7, 'Y'),
(null, '훈제오리샐러드', 9500, 7, 'Y');

-- INSERT INTO tbl_menu
-- VALUES (1, '1번 음식', 1, 10, 'Y');  #에러발생. 1번에 대해서 중복됨 ( menu_code는 PK 이기 때문에 중복 불가능 ) 

-- INSERT INTO tbl_menu
-- VALUES (100, '1번 음식', 100, 10, 'Y');   #정상 동작

-- INSERT INTO tbl_menu
-- VALUES (null, '1번 음식', 100, 10, 'Y');   # menu_code 의 중간을 건너뛰고 101부터 시작

# UPDATE
-- UPDATE 테이블명
-- 	SET 컬럼명1 = 수정할 데이터,
--  	컬럼명2 = 수정할 데이터,
-- [ WHERE 수정 대상 데이터 조건 ] ;

UPDATE tbl_menu 
SET              #where과 함께 써주어야함.
    menu_name = '100번이었던 음식',
    menu_price = 19000
WHERE
    menu_code = 100;  -- SAFE UPDATE MODE가 설정되어 있으면 WHERE절 없이 수정 불가



# DELETE
-- DELETE FROM 테이블명 [ WHERE 삭제 조건 ];

DELETE 
FROM tbl_menu
WHERE menu_code = 101;


DELETE 
FROM tbl_menu
ORDER BY menu_code DESC 
LIMIT 3;

DELETE          #where 안쓰면 에러발생.
FROM tbl_menu
WHERE menu_code = 26;



#REPLACE 
-- REPLACE는 기존에 있는 값에 대해서는 덮어씌우고, 기존에 값이 없을 경우에는 INSERT 역할을 한다.
-- INTO 키워드는 생략 가능
REPLACE INTO tbl_menu VALUES (100, '100번 음식 REPLACE!!', 100, 10, 'Y');

REPLACE tbl_menu VALUES (120, '없는 메뉴', 11111, 8, 'Y');