데이터 변경: INSERT, UPDATE, DELETE

 이번에는 DB 내에서 데이터 값을 변경하는 구문들을 살펴볼 것이다. 처음으로 다루는 INSERT는 말 그대로 테이블에 새로운 데이터를 삽입하는 쿼리이다. 주의해야 할 점으로는 테이블에 데이터를 입력하는 과정이므로 열과 데이터 타입 같은 형식을 잘 고려해서 요청을 해야 한다는 점이 있다. 예시로 테스트 테이블을 만들고 데이터를 입력하는 과정을 보자.

 

INSERT

USE sqldb ;
CREATE TABLE testTBL (id int, userName char(3), age int) ;
INSERT INTO testTBL VALUES (1, '홍길동', 25) ;
INSERT INTO testTBL(id, userName) VALUES (2, '설현') ; -- 채우고 싶지 않은 열은 이런식으로 넘길 수 있다. (공백은 NULL)
INSERT INTO testTBL(userName, age, id) VALUES ('하니', 26, 3) ; -- 순서도 변경 가능

SELECT * FROM testTBL ;

 CREATE TABLE... 이후에 설정한 열의 특성들(데이터 타입, PK 여부 등)을 고려해서 INSERT INTO... 에서 데이터 값을 적어야 한다는 것이다. 그렇게 어렵지 않은 개념이다. 이와 함께 자주 쓰이는 기능인 AUTO_INCREMENT의 사용법을 알아보자.

 

-- 자동으로 증가하는 AUTO_INCREMENT

USE sqldb ;
CREATE TABLE testTBL2
(id int AUTO_INCREMENT PRIMARY KEY,
userName char(3), age int) ;
INSERT INTO testTBL2 VALUES (NULL, '지민', 25);
INSERT INTO testTBL2 VALUES (NULL, '유나', 22);
INSERT INTO testTBL2 VALUES (NULL, '유경', 21);
SELECT * FROM testTBL2 ;

 주석에서도 설명했듯이 AUTO_INCREMENT는 자동으로 증가하는 숫자를 넣기 위해서 사용된다. 고유번호와 같이 자동으로 다른 숫자를 배정해주면 좋은 경우에 자주 쓰인다. 이를 그저 숫자가 1부터 하나씩 증가하는 설정 이외에도 다른 설정으로 사용할 수 있다.

 

SELECT last_insert_id() ; -- AUTO_INCREMENT를 통해서 마지막으로 입력된 값이 몇인지 확인 가능함

ALTER TABLE testTBL2 AUTO_INCREMENT=100 ; -- 이후부터 증가하는 값의 시작점을 정할 수 있다
INSERT INTO testTBL2 VALUES (NULL, '찬미', 23) ;
SELECT * FROM testTBL2 ;

USE sqldb ;
CREATE TABLE testTBL3
(id int AUTO_INCREMENT PRIMARY KEY,
userName char(3), age int) ;
ALTER TABLE testTBL3 AUTO_INCREMENT=1000;
SET @@auto_increment_increment=3 ; -- 자동으로 증가하는 값을 설정하는 것이다
INSERT INTO testTBL3 VALUES (NULL, '나연', 20) ;
INSERT INTO testTBL3 VALUES (NULL, '정연', 18) ;
INSERT INTO testTBL3 VALUES (NULL, '모모', 19) ;
SELECT * FROM testTBL3 ;

 

 지난 별첨에서 소개했던 테이블 복사 방법과 비슷하게 대량의 샘플 데이터를 생성할 때 INSERT INTO... SELECT..를 사용할 수도 있다. CREATE TABLE... SELECT...와 다른점은 테이블 정의를 할 수 있다는 점이 있으니 참고하자.

USE sqldb ;
CREATE TABLE testTBL4 (id int, Fname varchar(50), Lname varchar(50)) ;
INSERT INTO testTBL4
	SELECT emp_no, first_name, last_name
		FROM employees.employees ; 

USE sqldb ;
CREATE TABLE testTBL5
	(SELECT emp_no, first_name, last_name FROM employees.employees) ; -- 테이블 속성 과정도 스킵할 수 있다

 

 INSERT 쿼리문을 실행하는 중간에 오류가 발생하면 나머지 쿼리 또한 입력이 되지 않는 것을 알 수 있다. 이러한 작업 중지를 피하고 조건을 입력해 모든 데이터가 정상적으로 입력될 수 있도록 하는 방법을 실습으로 알아보자.

-- 데이터 입력시 오류가 발생해도 나머지는 입력하도록 하는법
USE sqldb ;
CREATE TABLE memberTBL (SELECT userID, name, addr FROM usertbl LIMIT 3) ; -- 3개의 데이터만 가져옴
ALTER TABLE memberTBL
	ADD CONSTRAINT pk_memberTBL PRIMARY KEY (userID) ; -- PK를 지정
SELECT * FROM memberTBL ;

INSERT INTO memberTBL VALUES('BBK' , '비비코' , '미국') ; -- PK에 중복이 있기 때문에 이 쿼리문에서 오류가 남
INSERT INTO memberTBL VALUES('SJH' , '서장훈' , '서울') ; 
INSERT INTO memberTBL VALUES('HJY' , '현주엽' , '경기') ;
SELECT * FROM memberTBL ; -- 에러로 쿼리문 시행 이전과 그대로 3개의 데이터만 있음

INSERT IGNORE INTO memberTBL VALUES('BBK' , '비비코' , '미국') ;
INSERT IGNORE INTO memberTBL VALUES('SJH' , '서장훈' , '서울') ; 
INSERT IGNORE INTO memberTBL VALUES('HJY' , '현주엽' , '경기') ;
SELECT * FROM memberTBL ; -- 비비코씨의 아이디(PK)가 겹쳐 에러가 난 것을 제외한 나머지 것들은 추가가 됨

INSERT INTO memberTBL VALUES('BBK' , '비비코' , '미국')
	ON DUPLICATE KEY UPDATE name='비비코', addr='미국' ;
INSERT INTO memberTBL VALUES('DJM' , '동짜몽' , '일본')
	ON DUPLICATE KEY UPDATE name='동짜몽', addr='일본' ;
SELECT * FROM memberTBL ; -- PK가 중복이 있는 경우에 데이터 변경이 됐고, 기본적으로는 데이터가 추가됨
-- 따라서 ON DUPLICATE UPDATE 문을 통해서 PK가 중복되면 UPDATE, 중복이 아니라면 INSERT가 된 것이다

 

UPDATE

-- UPDATE 데이터 수정
/*
<기본 구조>
UPDATE 테이블이름
	SET 열1 = 값1, 열2 = 값2, ... 
    WHERE 조건 ;
*/

USE sqldb ;
UPDATE buytbl SET price = price * 1.5 ;
SELECT * FROM buytbl ;

 UPDATE문도 간단하지만 기억해야할 것이 있다. 위의 기본 구조에서 WHERE 부분을 생략하면 모든 행이 변경된다는 것을 주의해야 한다. 특정 조건의 행만 지정해서 바꿔야한다는 것이다. 실수로 모든 행이나 불필요한 수정을 진행할 경우에는 원상태로 복구하기 힘들고, 아예 복구하지 못할 수도 있으니 유의하자.

 

DELETE

-- DELETE 데이터 삭제
/*
<기본 구조>
DELETE FROM 테이블 이름 WHERE 조건 ;
*/

-- 대용량 데이터를 삭제할 때 효율성을 높이는 방법 (트랜잭션의 유무)
USE sqldb ;
CREATE TABLE bigTBL1 (SELECT * FROM employees.employees) ;
CREATE TABLE bigTBL2 (SELECT * FROM employees.employees) ;
CREATE TABLE bigTBL3 (SELECT * FROM employees.employees) ; -- 실습을 위해 샘플 데이터를 담은 테이블 생성

DELETE FROM bigTBL1 ;
DROP TABLE bigTBL2 ;
TRUNCATE TABLE bigTBL3 ; -- DELETE문은 트랜잭션을 남기는 과정 때문에 시간이 가장 오래 소요된다

 DELETE도 UPDATE문과 크게 다를 것이 없지만, 데이터 삭제시 효율성을 높이는 다른 명령어들이 존재한다. 최소 처리단위(트랜잭션) 로그를 남기는 과정의 유무에 따라 작업시간이 차이가 나는 것이다. DELETE는 트랜잭션 로그를 남기는 과정으로 가장 오래 걸리고, DROP과 TRUNCATE는 이를 남기지 않고 아예 지우는 기능이다. DROP과 TRUNCATE의 차이점이 있다면 DROP은 테이블 자체를 날리는 것이고, TRUNCATE는 테이블의 형태는 남기되 데이터만 지우는 것이다.