[혼공S] CH3-(3). 데이터 변경을 위한 SQL 문

 SELECT문은 이미 만들어져 있는 테이블에서 데이터를 추출하는 구문이다. 이 절에서는 데이터의 입력, 수정, 삭제를 하는 방법을 알아보려 한다. 

 

새로운 데이터를 입력할 때에는 INSERT, 데이터를 수정할 때에는 UPDATE, 데이터를 삭제할 때에는 DELETE를 사용한다. 

 

데이터 입력: INSERT

INSERT는 데이터를 삽입하는 명령이다. 기본적인 형식은 아래와 같다.

INSERT INTO 테이블 [(열1, 열2, ...)] VALUES (값1, 값2, ...) ;

위의 구문에서 테이블 다음에 오는 열의 리스트는 생략 가능하다. 하지만 VALUES 다음에 오는 값은 테이블의 열 개수와 동일해야 한다. 

 

hongong1 이라는 테이블을 만들고 데이터를 입력해보자. 아래와 같이 쿼리문을 작성하면 테이블이 만들어진 것을 확인할 수 있다. 

CREATE TABLE hongong1 (toy_id INT, toy_name CHAR(4), age INT) ; -- TABLE 만들기 
INSERT INTO hongong1 VALUES (1, '우디', 25) ; -- 데이터 입력하기
SELECT * FROM hongong1 ;

열 이름을 입력하고 해당 데이터를 작성할 수 있느데, 열의 이름을 생략한 경우에 대해서는 NULL 값이 들어가게 된다. 

INSERT INTO hongong1 (toy_id, toy_name) VALUES (2, '버즈') ; -- 열 이름 입력, 
SELECT * FROM hongong1 ;

 

자동으로 증가하는 AUTO_INCREMENT 

AUTO_INCREMENT는 앞 절에서도 봤듯이, 1부터 자동으로 증가하는 값을 입력해준다. 여기서 주의할 점은, AUTO_INCREMENT를 사용하는 열은 PRIMARY KEY로 지정해주어야 한다는 것이다. 

-- hongong2 테이블 만들기
CREATE TABLE hongong2
  (toy_id INT AUTO_INCREMENT PRIMARY KEY, 
   toy_name CHAR(4), 
   age INT) ; 
-- 테이블에 데이터 입력하기
INSERT INTO hongong2 VALUES (NULL, '보핍', 25) ;
INSERT INTO hongong2 VALUES (NULL, '슬링키', 22) ; 
INSERT INTO hongong2 VALUES (NULL, '렉스', 21) ; 
SELECT * FROM hongong2 ;

(내가 중복해서 여러 개 만들고 지웠더니 toy_id의 번호가 7번부터 시작하는데,, 이건 어떻게 수정해야 할까,, 나중에 차차 알게 되겠지..?ㅎ)

AUTO_INCREMENT를 이용해서 계속 입력하다 보면, 어느 숫자까지 증가되었는지를 확인이 필요한데, 이는 다음과 같이 볼 수 있다.

SELECT last_insert_id() ;

AUTO_INCREMENT를 사용할 때, 시작을 100부터 하고싶다면 다음과 같이 설정할 수 있다. 

-- AUTO_INCREMENT 100부터 시작
ALTER TABLE hongong2 AUTO_INCREMENT = 100 ; 
INSERT INTO hongong2 VALUES (NULL, '재남', 35) ; 
SELECT * FROM hongong2 ;

 

이번에는 AUTO_INCREMENT를 이용해서 번호가 1000부터 시작하는데, 3씩 증가하도록 만들어보자.

CREATE TABLE hongong3 
  (toy_id INT AUTO_INCREMENT PRIMARY KEY, 
   toy_name CHAR(4), 
   age INT) ; 
ALTER TABLE hongong3 AUTO_INCREMENT = 1000 ; 
SET @@AUTO_INCREMENT_INCREMENT = 3 ; -- 증가값은 3으로 지정

INSERT INTO hongong3 VALUES (NULL, '토마스', 20) ; 
INSERT INTO hongong3 VALUES (NULL, '제임스', 23) ; 
INSERT INTO hongong3 VALUES (NULL, '고든', 25) ; 
SELECT * FROM hongong3 ;

여기서 시스템 변수라는 개념을 알아볼 필요가 있다. 시스템 변수란, MySQL에서 자체적으로 가지고 있는 설정값이 저장된 변수를 의미한다. 주로 MySQL의 환경과 관련된 내용이 저장되어 있다. 시스템 변수는 앞에 @@이 붙는 것이 특징이며, 시스템 변수의 값을 확인하려면 SELECT @@시스템변수를 실행하면 된다. 또한 전체 시스템 변수의 종류를 알고 싶다면 SHOW GLOBAL VARIABLES를 실행하면 된다. 

SELECT @@AUTO_INCREMENT_INCREMENT ; 
SHOW GLOBAL VARIABLES ;

 

다른 테이블의 데이터를 한 번에 입력하는 INSERT INTO ~ SELECT 

만약 다른 테이블에 이미 데이터가 입력되어 있다면 INSERT INTO ~ SELECT 구문을 사용하여 해당 테이블의 데이터를 가져와서 한 번에 입력할 수 있다. 형식은 아래와 같다. 

INSERT INTO 테이블_이름 (열_이름1, 열_이름2, ...)
  SELECT 문 ;

여기서 주의할 점은, SELECT 문의 열 개수가 INSERT할 테이블의 열 개수와 동일해야 한다는 점이다. world 데이터베이스의 city 테이블을 이용해보도록 하자. 

-- city 테이블의 행 개수 확인
SELECT COUNT(*) FROM world.city ; 
-- city 테이블의 description 확인
DESC world.city ; 
-- 맨 처음 다섯 개의 데이터 확인
SELECT * FROM world.city LIMIT 5 ;

 

city 테이블에서 도시이름(Name)과 인구(Population)를 가져오는 걸로 해보자. 먼저 하나의 테이블을 만들고, INSERT INTO 해당테이블 SELECT ~ 문을 이용하여 city테이블에서 데이터를 가져올 것이다. 

-- 테이블 만들기 
CREATE TABLE city_popul 
  (city_name CHAR(35), 
   population INT) ; 
SELECT * FROM city_popul ; 
-- city테이블에서 데이터 불러오기 
INSERT INTO city_popul
  SELECT Name, Population FROM world.city ; 
SELECT * FROM city_popul ;

데이터 수정: UPDATE 

데이터를 수정해야 한다면 UPDATE를 이용하여 할 수 있다. 기본 형식은 아래와 같다. 

UPDATE 테이블_이름 
  SET 열1 = 값1, 열2 = 값2, ..
  WHERE 조건 ;

※ UPDATE문 또는 DELETE문을 쓰기 전에 체크해야할 것이 있다. Edit > Preference > SQL Editor > Safe Updates (rejects UPDATEs and DELETEs with no restrictions) 의 체크 해제. 

 

city_popul의 테이블에서 city_name 중 'Seoul'을 '서울'로 변경해보고자 한다. 

-- UPDATE
USE market_db ; 
SELECT * FROM city_popul ; 
UPDATE city_popul
  SET city_name = '서울' 
  WHERE city_name = 'Seoul'; 
SELECT * FROM city_popul 
  WHERE city_name = '서울' ;

한꺼번에 여러 값을 변경할 수도 있다. New York을 '뉴욕'과 뉴욕의 인구수를 9으로 변경해보고자 한다. 

-- 한꺼번에 여러 데이터 변경
UPDATE city_popul 
  SET city_name = '뉴욕', population = 0 
  WHERE city_name = 'New York' ; 
SELECT * FROM city_popul WHERE city_name = '뉴욕' ;

 

WHERE가 없는 UPDATE 문

UPDATE문에서 WHERE 부분은 생략이 가능하지만, WHERE를 생략하게 된다면 모든 행의 데이터 값이 변경된다. 

UPDATE city_popul
  SET city_name = '서울' ; -- 실행하지 않음

전체 행의 데이터 값을 변경하는 경우는 아래의 경우에 효과적일 수 있다. 인구 수의 값이 크기 때문에, 만 명 단위로 변경하고자 한다. 

UPDATE city_popul 
  SET population = population / 10000 ; 
SELECT * FROM city_popul LIMIT 5 ;

 

 

데이터 삭제: DELETE

데이터의 행을 삭제해야 하는 경우에는 DELETE를 쓸 수 있다. 형식은 아래와 같다. 

DELETE FROM 테이블이름 WHERE 조건 ;

 

city_popul 테이블에서 New로 시작하는 데이터를 도시를 삭제하고자 한다. 아래의 쿼리문을 시행하니 11개 정도 되었던 데이터가 삭제된 것을 확인할 수 있었다. 

-- New로 시작하는 도시 출력
SELECT * FROM city_popul WHERE city_name LIKE "New%" ; 
-- New로 시작하는 도시 삭제
DELETE FROM city_popul WHERE city_name LIKE "New%" ; 
-- New로 시작하는 도시 재출력
SELECT * FROM city_popul WHERE city_name LIKE "New%" ;

만약 New로 시작하는 모든 데이터를 삭제하는 것이 아닌, 상위 다섯 개만 삭제하고 싶다면 아래처럼 LIMIT을 이용할 수 있다.

DELETE FROM city_popul WHERE city_name LIKE "New%" LIMIT 5 ;

 

대용량 테이블 삭제하기

sakila 데이터 베이스에 있는 country 테이블과 world 데이터 베이스에 있는 city 테이블을 복사해서, 삭제를 해보고자 한다. 

-- sakila DB의 country 테이블 description
DESC sakila.country ; 
SELECT * FROM sakila.country LIMIT 5 ;
SELECT count(*) FROM sakila.country ; 
-- world DB의 city 테이블 description 
DESC world.city ; 
SELECT * FROM world.city LIMIT 5 ; 
SELECT count(*) FROM world.city ; 
-- create big table
CREATE TABLE big_table1 (SELECT * FROM world.city, sakila.country) ; -- 어떻게 44만개가 만들어졌는지 모르겠음.. JOINING시켜서 그런건가 
SELECT * FROM big_table1 LIMIT 5 ; 
CREATE TABLE big_table2 (SELECT * FROM world.city, sakila.country) ;
CREATE TABLE big_table3 (SELECT * FROM world.city, sakila.country) ; 
-- 대용량 테이블 삭제하는 법
DELETE FROM big_table1 ; -- 시간이 오래 걸림
DROP TABLE big_table2 ; -- 테이블 자체를 삭제함. 속도 빠름. 
TRUNCATE TABLE big_table3 ;  -- 테이블의 구조는 남겨놓음. 속도 빠름.

 

 

Reference

혼자 공부하는 SQL, 우재남 지음