DML(Data Manipulation Language)

💡 데이터 조작언어, 테이블에 값을 삽입하거나 수정하거나 삭제하는(데이터베이스 내의 데이터를 조작하는데 사용하는) SQL의 한 부분이다.

INSERT

• 새로운 행을 추가하는 구문이다.

• 테이블의 행의 수가 증가한다.

  INSERT 
    INTO tbl_menu
  VALUES 
  (
    NULL, '바나나해장국'
  , 8500, 4
  , 'Y'
  );

  실행결과

  

• NULL 허용 가능한(NULLABLE) 컬럼이나 AUTO_INCREMENT가 있는 컬럼을 제외하고 INSERT하고 싶은 데이터 컬럼을 지정해서 INSERT 가능하다.(default 속성이 있다면 default값이 들어감)

  실행결과

  INSERT 
    INTO tbl_menu
  (
    menu_name, menu_price
  , category_code, orderable_status
  )
  VALUES 
  (
    '초콜릿죽', 6500
  , 7, 'Y'
  );

  

• 컬럼을 명시하면 INSERT 시 데이터의 순서를 바꾸는 것도 가능하다.

  INSERT 
    INTO tbl_menu
  (orderable_status, menu_price, menu_name, category_code)
  VALUES 
  ('Y', 5500, '파인애플탕', 4);

  실행결과

  

SELECT * FROM tbl_menu;

실행결과

• MULTI INSERT

  INSERT 
    INTO tbl_menu 
  VALUES 
  (null, '참치맛아이스크림', 1700, 12, 'Y'),
  (null, '멸치맛아이스크림', 1500, 11, 'Y'),
  (null, '소시지맛커피', 2500, 8, 'Y');

  실행결과

  

UPDATE

• 테이블에 기록된 컬럼의 값을 수정하는 구문이다.
• 테이블의 전체 행 갯수는 변화가 없다.

  SELECT
         menu_code
       , category_code
    FROM tbl_menu
   WHERE menu_name = '파인애플탕';

  실행결과

  

UPDATE tbl_menu
   SET category_code = 7
     , menu_name = '딸기맛붕어빵'
 WHERE menu_code = 24;

실행결과

• SUBQUERY를 활용할 수도 있다.
• 다만 MySQL은 Oracle과 달리 update나 delete 시 자기 자신 테이블의 데이터를 사용 시 1093 에러가 발생한다.

  UPDATE tbl_menu
     SET category_code = 6
   WHERE menu_code = (SELECT menu_code
                        FROM tbl_menu 
                       WHERE menu_name = '파인애플탕');

  실행결과

  

DELETE

• 테이블의 행을 삭제하는 구문이다.

• 테이블의 행의 갯수가 줄어든다.

• LIMIT을 활용한 행 삭제(offset 지정은 안됨)

  DELETE FROM tbl_menu
  ORDER BY menu_price
  LIMIT 2;

  실행결과

  

• WHERE절을 활용한 단일 행 삭제

  DELETE
    FROM tbl_menu
   WHERE menu_code = 24;

  실행결과

  

• 해당 테이블 전체 행 삭제

  DELETE FROM tbl_menu;
  
  -- (경고문구를 안 띄우고 싶다면)
  DELETE FROM tbl_menu WHERE 1 = 1;

  실행결과

  

REPLACE

• INSERT 시 PRIMARY KEY 또는 UNIQUE KEY가 충돌이 발생할 수 있다면
• REPLACE를 통해 중복 된 데이터를 덮어 쓸 수 있다.

  -- INSERT INTO tbl_menu VALUES (17, '참기름소주', 5000, 10, 'Y'); -- 에러 발생
  REPLACE INTO tbl_menu VALUES (17, '참기름소주', 5000, 10, 'Y');

  실행결과

  

  

• INTO는 생략 가능 하다.

  REPLACE tbl_menu VALUES (17, '참기름소주', 6500, 10, 'Y');

  실행결과

  

• UPDATE 시 WHERE 구문 없이 UPDATE가 가능하다.

  REPLACE tbl_menu
      SET menu_code = 2
        , menu_name = '우럭쥬스'
        , menu_price = 2000
        , category_code = 9
        , orderable_status = 'N';

  실행결과

  

• DML 작업이 끝나면 db_script를 다시 전체 실행해서 테이블 및 데이터 정보를 초기화 한다.

TRANSACTION

💡 TRANSACTION은 데이터 베이스에서 한 번에 수행되는 작업의 단위이다.

시작, 진행, 종료 단계를 가지며 만약 중간에 오류가 발생하면 롤백(시작 이전 단계로 되돌리는 작업)을 수행하고 데이터 베이스에 제대로 반영하기 위해서는 커밋(이후 롤백이 되지 않음)을 진행한다.

MySQL은 기본적으로 자동 커밋 설정이 되어 있어(롤백이 안됨). 롤백을 하기 위해서는 자동 커밋 설정을 해제해 주어야 한다.

Transaction 활용

• MySQL은 기본적으로 commit이 자동으로 되므로 수동으로 조절하고 싶다면 autocommit 설정을 바꿔 주어야 한다.

• autocommit 활성화

  SET autocommit = 1; 
  -- 또는 
  SET autocommit = ON;

  실행결과

  

• autocommit 비활성화

  SET autocommit = 0; 
  -- 또는 
  SET autocommit = OFF;

  실행결과

  

• START TRANSACTION 구문을 작성하고 DML 작업 수행 후 COMMIT 또는 ROLLBACK을 하면 된다.

• COMMIT 이후에는 ROLLBACK을 해도 ROLLBACK이 적용되지 않는다.

  START TRANSACTION;
  
  SELECT * FROM tbl_menu;
  INSERT INTO tbl_menu VALUES (null, '바나나해장국', 8500, 4, 'Y');
  UPDATE tbl_menu SET menu_name = '수정된 메뉴' WHERE menu_code = 5;
  DELETE FROM tbl_menu WHERE menu_code = 7;
  
  -- COMMIT; 
  ROLLBACK;

  실행결과