[SQL] 15. DML, 트랜잭션, TCL

DML(Data Manipulation Language / 데이터조작어)

• 테이블의 데이터를 다루는 목적의 문법들
• INSERT : 새로운 데이터를 테이블에 추가
• UPDATE : 기존 데이터를 갱신
• DELETE : 기존 데이터를 삭제

 

 

INSERT - 데이터 추가

 

 

INSERT INTO절

• 데이터를 추가할 테이블과 컬럼의 목록을 작성하는 절.

VALUES절

• 테이블에 입력할 값들의 목록을 작성.
• INSERT INTO 테이블명 [(컬럼 목록[, …])]
  VALUES (값1 [, 값2, …]);
• 기본적으로 새로운 데이터를 입력하는 경우 한번에 하나의 행만 입력 가능하다.
• MySQL의 경우 여러 행의 데이터를 한번에 입력받을 수 있다.
• …
  VALUES (값, …), (값2, …), …;
• INSERT INTO 절에서 테이블명은 필수적으로 작성되어야 하며, 컬럼리스트의 경우 필수가 아닌 옵션이며 값의 입력을 원하는 특정한 컬럼이 있는 경우 원하는 컬럼의 목록을 작성한다.
  이 때 VALUES절에서도 컬럼 목록에 명시된 컬럼에 대해서만 입력값을 작성하면 된다.
• 컬럼 리스트를 생략하는 경우 테이블의 모든 행에 대한 입력값을 작성해야 한다.
  VALUES절에는 컬럼의 수만큼 입력값이 기본 컬럼의 순서대로 작성된다.

DESC departments;

SELECT * FROM departments;

 

 

 

• 입력값이 생략된 컬럼의 경우 테이블의 옵션에 따라서 기본값이 있다면 NULL대신 DEFAULT값이 입력된다.
• DEFAULT가 없는 컬럼의 입력값이 생략된 경우 NULL값이 입력된다.
• NULL값을 허용하지 않는 컬럼에 DEFAULT값과 입력값이 없다면 에러가 발생한다.

INSERT INTO departments (department_id, department_name) VALUES (320, 'GoHome');

department_id|department_name     |manager_id|location_id|

-------------+--------------------+----------+-----------+

           10|Administration      |       200|       1700|

           20|Marketing           |       201|       1800|

           30|Purchasing          |       114|       1700|

…

          280|Storm               |       100|       1700|

          290|HOME                |          |           |

          300|Coffe               |       100|       1700|

          310|Nap                 |       100|       1700|

          320|GoHome              |          |           |

 

 

 

• id 컬럼에 default값으로 10의 숫자를 가진 테이블을 생성한다.

CREATE TABLE t_insert ( id INT DEFAULT 10 );

DESC t_insert;

Field|Type|Null|Key|Default|Extra|

-----+----+----+---+-------+-----+

id   |int |YES |   |10     |     |

 

• id 컬럼에 대해서 명시적으로 NULL값을 입력한다.

INSERT INTO t_insert (id) VALUES (NULL);

 

 

• 위와 같이 명시적으로 NULL값을 입력하는 경우 컬럼에 DEFAULT 설정이 적용되어 있더라도 무시되고 NULL값이 입력되는 것을 볼 수 있다.

SELECT * FROM t_insert;

id|

--+

  |

 

서브쿼리를 사용한 데이터 입력

• employees 테이블의 구조를 복사하여 생성

CREATE TABLE sales_reps AS ( SELECT * FROM employees WHERE 1 = 2 );

DESC sales_reps;

Field         |Type        |Null|Key|Default|Extra|

--------------+------------+----+---+-------+-----+

id            |int unsigned|NO  |   |       |     |

name          |varchar(25) |NO  |   |       |     |

salary        |decimal(8,2)|NO  |   |       |     |

commission_pct|decimal(2,2)|YES |   |       |     |

• 생성된 sales_reps테이블의 데이터는 복사되지 않았음.

SELECT * FROM sales_reps;

id|name|salary|commission_pct|

--+----+------+--------------+

 

• values 절을 대신하여 서브쿼리의 출력 결과를 메인 쿼리의 입력값으로 입력하는 구문작성방법이다.

INSERT INTO sales_reps (id, name, salary, commission_pct) SELECT employee_id, last_name, salary, commission_pct FROM employees WHERE job_id LIKE '%REP';

SELECT * FROM sales_reps;

id |name      |salary  |commission_pct|

---+----------+--------+--------------+

150|Tucker    |11000.00|          0.30|

151|Bernstein |10450.00|          0.25|

152|Hall      | 9900.00|          0.25|

153|Olsen     | 8800.00|          0.20|

154|Cambrault | 8250.00|          0.20|

155|Tuvault   | 7700.00|          0.15|

156|King      |11000.00|          0.35|

157|Sully     |10450.00|          0.35|

…

 

 

UPDATE - 데이터 갱신

• 기존 데이터를 갱신 할 때 사용하는 문법
• UPDATE절 : 갱신 테이블을 명시한다.
  SET절 : 갱신 컬럼과 값을 작성한다. 여러 컬럼을 한번에 갱신하는 것도 가능하다.
  WHERE절(옵션) : 갱신 대상 행의 조건식을 작성. 생략하는 경우 테이블의 모든 행이 갱신된다.

 

• 다음 구문은 where절에 항상 거짓이 되는 조건을 넣었으므로 갱신되는 데이터는 없다.

UPDATE departments SET department_id = department_id, department_name = 'name' WHERE 1=2;

 

 

 

서브쿼리를 활용한 데이터 갱신

 

• 113, 205 사원의 정보를 조회한다. 기존 값은 서로 다른 값을 가지고 있음.

SELECT employee_id, job_id, salary FROM copy_emp WHERE employee_id IN (113, 205);

 

employee_id|job_id    |salary  |

-----------+----------+--------+

        113|FI_ACCOUNT| 7590.00|

        205|AC_MGR    |13200.00|

 

• 113 사번의 직원에 대하여 job_id, salary의 값을 205번 사원의 job_id, salary값으로 갱신한다.

UPDATE copy_emp SET job_id = ( SELECT job_id FROM employees WHERE employee_id = 205), salary = ( SELECT salary FROM employees WHERE employee_id = 205) WHERE employee_id = 113;

 

• 갱신 이후 113, 205 사원의 데이터 확인

SELECT employee_id, job_id, salary FROM copy_emp WHERE employee_id IN (113, 205);

employee_id|job_id|salary  |

-----------+------+--------+

        113|AC_MGR|13200.00|

        205|AC_MGR|13200.00|

• 1800위치 값에 근무하는 부서의 정보

SELECT department_id FROM departments WHERE location_id = 1800;

 

department_id|

-------------+

           20|

 

• 1800위치 코드에 존재하는 부서의 사원 정보를 출력

SELECT department_id, salary FROM copy_emp WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 1800);

department_id|salary  |

-------------+--------+

           20|14300.00|

           20| 6600.00|

 

• 1800 위치에 사무실이 있는 부서에 근무하는 직원의 급여를 10%만큼 인상

UPDATE copy_emp SET salary = salary * 1.1 WHERE department_id IN ( SELECT department_id FROM departments WHERE location_id = 1800);

 

• 갱신 작업 후 값의 변화

SELECT department_id, salary FROM copy_emp WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 1800);

department_id|salary  |

-------------+--------+

           20|15730.00|

           20| 7260.00|

 

 

 

DELETE - 데이터 삭제

• 기존 테이블의 데이터를 삭제할 수 있는 구문
• DELETE절 : 삭제 될 테이블명을 명시
  WHERE절(옵션) : 삭제 될 행에 대한 조건식 작성.
  옵션으로 생략이 가능하지만, 생략하는 경우 모든 행이 삭제 된다.

 

서브쿼리를 활용한 데이터 삭제

• 1800번 위치에서 근무하는 부서의 직원들을 삭제

DELETE FROM copy_emp WHERE department_id = (SELECT department_id FROM departments WHERE location_id = 1800);

 

SELECT department_id, salary FROM copy_emp WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 1800);

 

department_id|salary|

-------------+------+

 

 

트랜잭션

• 데이터베이스의 일관성, 무결성을 위해 사용하는 단위이며, 물리적인 작업 단위가 DML이라 할 때, 진행하는 작업과 관련 있는 DML들을 묶어서 논리적인 작업 단위인 트랜잭션을 구성한다.
• 기본적으로 데이터베이스의 DML들은 즉각적으로 데이터베이스에 기록되지 않는다.
  (MySQL은 기본이 데이터베이스에 기록이다.)
• 현재 진행 중인 DML과 연결되어 작업하고 있는 DML들이 다 입력 될 때까지 데이터베이스에 기록하고 있지 않다가 작업 완료 후 검토하고 작업 내용을 데이터베이스의 반영 여부를 결정하고 트랜잭션을 마무리짓게 된다.
• SELECT 구문은 굳이 트랜잭션으로 묶지 않아도 상관 없음.

 

트랜잭션의 특성 - ACID

• (Atomicity) 원자성 : 트랜잭션의 작업은 모두 한번에 수행되거나 아예 수행되지 않아야 한다.
• (Consistency) 일관성 : 트랜잭션의 수행이 데이터베이스를 일관된 상태에서 일관된 상태로 변경해야 한다.
• (Isolation) 독립성 : 동시에 여러 트랜잭션이 실행되더라도 서로에게 영향을 주면 안 된다.
• (Durability) 지속성 : 트랜잭션이 성공적으로 완료되면 그 결과는 영구히 반영되어야 한다.

 

 

트랜잭션이 사용해야하는 경우

• 여러 쿼리의 실행 결과가 서로에게 영향을 미치는 경우
• 데이터의 일관성과 무결성이 중요한 데이터인 경우

 

트랜잭션을 사용하지 않아도 되는 경우

• 단일 쿼리로 작업이 진행되는 경우
• 데이터의 일관성이 크게 중요하지 않은 단순 조회 작업

 

 

트랜잭션 제어어(Transaction Control Language / TCL)

• 트랜잭션의 작업을 검토 후 끝내거나 관리할 때 사용하는 명령어
• MySQL의 기본값은 auto-commit이므로 작업 시 주의할 것.
• 데이터베이스 개발 도구에도 기본값이 별도로 있는 경우가 있으므로 미리 확인해둔다.
• 데이터베이스의 계정이 동일하더라도 접속 클라이언트가 다르다면 별도의 세션으로 접속하게 된다.
  트랜잭션은 세션 별로 진행되기 때문에 동일 계정에서 트랜잭션은 공유되지 않는다.

 

BEGIN TRANSACTION : 트랜잭션의 시작하는 명령어.

COMMIT : 트랜잭션의 작업 단위를 데이터베이스에 기록한다.

ROLLBACK : 트랜잭션의 작업 내용을 일괄 취소한다.

SAVEPOINT : 트랜잭션 진행 중 되돌릴 수 있는 지점을 생성한다. 작업 중인 트랜잭션을 저장하지는 않는다.

 

DBeaver에서 커밋모드 수동으로 전환하기

• DBeaver에서는 auto-commit 모드가 기본값이므로 필요한 경우 수동이나 기타 설정을 적용하여 사용한다.

 

• 100, 101의 사원에게 급여를 90%로 설정하는 작업을 한다 했을 때 작업 중 문제가 발생했다면 ROLLBACK으로 트랜잭션의 작업 내역을 취소할 수 있다.

UPDATE employees SET salary = salary * 0.9 WHERE employee_id = 100;

 

UPDATE employees SET salary = salary * 0.8 WHERE employee_id = 101;

ROLLBACK;

 

 

 

SAVEPOINT 사용하기

• savepoint는 트랜잭션 중 부분적으로 rollback을 수행할 수 있는 지점을 만들 수 있다.

 

UPDATE employees SET salary = salary * 0.9 WHERE employee_id = 100;

SAVEPOINT update_100;

• 필요한 경우 savepoint를 설정하여 작업의 중간 지점을 생성할 수 있다. 이름은 고유하게 작성해야 한다.

UPDATE employees SET salary = salary * 0.8 WHERE employee_id = 101;

 

• 101 사원의 급여를 0.9가 아닌 0.8을 곱하여 rollback이 필요함.

SELECT employee_id, salary FROM employees WHERE employee_id IN (100, 101);

employee_id|salary  |

-----------+--------+

        100|26244.90|

        101|16456.00|

 

• 이 때 100번 사원에 대한 급여의 갱신은 유지하고 싶다면 중간 지점에 생성한 savepoint까지 rollback을 하여 전체 작업을 취소하지 않아도 된다.

ROLLBACK TO SAVEPOINT update_100;

 

• 101번 사원의 급여값만 이전 상태로 돌아간 것을 볼 수있다.

SELECT employee_id, salary FROM employees WHERE employee_id IN (100, 101);

employee_id|salary  |

-----------+--------+

        100|26244.90|

        101|20570.00|

 

 

연습문제 풀이

CREATE TABLE my_employee ( id INT PRIMARY KEY, last_name VARCHAR(25), first_name VARCHAR(25), userid VARCHAR(8), salary INT );

DESC my_employee;

Field     |Type       |Null|Key|Default|Extra|

----------+-----------+----+---+-------+-----+

id        |int        |NO  |PRI|       |     |

last_name |varchar(25)|YES |   |       |     |

first_name|varchar(25)|YES |   |       |     |

userid    |varchar(8) |YES |   |       |     |

salary    |int        |YES |   |       |     |

 

INSERT INTO my_employee VALUES (1, 'Patel', 'Ralph', 'rpatel', 895), (2, 'Dancs', 'Betty', 'bdancs', 860), (3, 'Biri', 'Ben', 'bbiri', 1100), (4, 'Newman', 'Chad', 'cnewman', 750);

 

SELECT * FROM my_employee;

id|last_name|first_name|userid |salary|

--+---------+----------+-------+------+

 1|Patel    |Ralph     |rpatel |   895|

 2|Dancs    |Betty     |bdancs |   860|

 3|Biri     |Ben       |bbiri  |  1100|

 4|Newman   |Chad      |cnewman|   750|

COMMIT;

 

UPDATE my_employee SET last_name = 'Drexler' WHERE id = 3;

 

UPDATE my_employee SET salary = 1000 WHERE salary < 900;

 

SELECT * FROM my_employee;

id|last_name|first_name|userid |salary|

--+---------+----------+-------+------+

 1|Patel    |Ralph     |rpatel |  1000|

 2|Dancs    |Betty     |bdancs |  1000|

 3|Drexler  |Ben       |bbiri  |  1100|

 4|Newman   |Chad      |cnewman|  1000|

 

DELETE FROM my_employee WHERE first_name = 'Betty' AND last_name = 'Dancs';

SELECT * FROM my_employee;

id|last_name|first_name|userid |salary|

--+---------+----------+-------+------+

 1|Patel    |Ralph     |rpatel |  1000|

 3|Drexler  |Ben       |bbiri  |  1100|

 4|Newman   |Chad      |cnewman|  1000|

COMMIT;

INSERT INTO my_employee VALUES (5, 'Ropeburn', 'Audrey', 'aropebur', 1550);

 

SELECT * FROM my_employee;

id|last_name|first_name|userid  |salary|

--+---------+----------+--------+------+

 1|Patel    |Ralph     |rpatel  |  1000|

 3|Drexler  |Ben       |bbiri   |  1100|

 4|Newman   |Chad      |cnewman |  1000|

 5|Ropeburn |Audrey    |aropebur|  1550|

 

SAVEPOINT before_insert;

SELECT * FROM my_employee;

 

DELETE FROM my_employee;

 

SELECT * FROM my_employee;

id|last_name|first_name|userid|salary|

--+---------+----------+------+------+

 

SELECT * FROM my_employee;

id|last_name|first_name|userid  |salary|

--+---------+----------+--------+------+

 1|Patel    |Ralph     |rpatel  |  1000|

 3|Drexler  |Ben       |bbiri   |  1100|

 4|Newman   |Chad      |cnewman |  1000|

 5|Ropeburn |Audrey    |aropebur|  1550|

 

COMMIT;