[SQL] 데이터 변경을 위한 SQL 문

목차

• INSERT 문
• UPDATE 문
• DELETE 문

 

데이터 변경을 위한 SQL 문

 

데이터베이스와 테이블을 만든 후에는 데이터를 변경하는 입력/수정/삭제 기능이 필요하다.

예를 들어, 새로 가입한 회원을 테이블에 입력할 때는 INSERT 문을,

회원의 주소나 연락처가 변경되어 정보를 수정할 때는 UPDATE 문을 사용한다.

또, 회원이 탈퇴해서 회원을 삭제할 때는 DELETE 문을 사용한다.

 

 

INSERT 문

테이블에 행 데이터를 입력하는 기본적인 SQL 문이다.

INSERT는 테이블에 데이터를 삽입하는 명령어 이다.

 

기본적인 형식

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

 

INSERT 문은 별로 어려울 것이 없으나, 주의할 점 몇 가지가 있다.

• 테이블 이름 다음에 나오는 열은 생략이 가능하다.
  • 열이름을 생략할 경우에 VALUES 다음에 나오는 값들의 순서 및 개수는
  • 테이블을 정의할 때의 열 순서 및 개수와 동일해야 한다.

CREATE TABLE hongong1 (toy_id INT, toy_name CHAR(4), age INT);
INSERT INTO hongong1 VALUE (1, '우디', 25);

 

만약 아이디(toy_id)와 이름(toy_name)만 입력하고 나이(age)는 입력하고 싶지 않다면

다음과 같이 테이블 이름 뒤에 입력할 열의 이름을 써줘야 한다.

생략한 나이(age) 열에는 NULL 값이 들어간다.

INSERT INTO hongong1 (toy_id, toy_name) VALUE (2, '버즈');

 

열의 순서를 바꿔서 입력하고 싶을 때는 열 이름과 값을 원하는 순서에 맞춰 써주면 된다.

INSERT INTO hongong1 (toy_name, age, toy_id) VALUE ('제시', 20, 3);

 

 

AUTO_INCREMENT

AUTO_INCREMENT는 열을 정의할 때 1부터 증가하는 값을 입력해줘야 한다.

INSERT에서는 해당 열이 없다고 생각하고 입력하면 된다.

 

주의할 점

• AUTO_INCREMENT로 지정하는 열은 꼭 PRIMARY KEY로 지정해줘야 한다.

 

새로운 테이블을 만들어보겠다. 아이디(toy_id) 열을 자동 증가로 설정했다.

CREATE TABLE hongong2 (
    toy_id INT AUTO_INCREMENT PRIMARY KEY,
    toy_name CHAR(4),
    age INT);

 

이제 테이블에 데이터를 입력해보자. 자동 증가하는 부분은 NULL으로 채워 놓으면 된다.

결과를 보면 아이디(toy_id)에 1부터 차례대로 채워진 것을 확인할 수 있다.

INSERT INTO hongong2 VALUE (NULL, '보핍', 25);
INSERT INTO hongong2 VALUE (NULL, '슬링키', 22);
INSERT INTO hongong2 VALUE (NULL, '렉스', 21);

SELECT * FROM hongong2;

 

계속 입력하다 보면 현재 어느 숫자까지 증가되었는지 확인이 필요하다.

자동 증가로 3까지 입력되었다는 의미이다.

SELECT LAST_INSERT_ID();

 

만약 AUTO_INSERT로 입력되는 다음 값을 100부터 시작하도록 변경하고 싶다면 이렇게 실행해보자

ALTER TABLE 뒤에는 테이블 이름을 입력하고, 자동 증가를 100부터 시작하기 위해

AUTO_INCREMENT를 100으로 지정했다.

ALTER TABLE hongong2 AUTO_INCREMENT = 100;
INSERT INTO hongong2 VALUE(NULL, '승우', 25);
SELECT * FROM hongong2;

 

이번에는 처음부터 입력되는 값을 1000으로 지정하고

다음 값은 1000, 1003, 1006, .. 으로 3씩 증가하도록 설정하는 방법이다.

 

이런 경우에는 시스템 변수인 @@auto_increment_increment를 변경시켜야 한다.

테이블을 새로 만들고 자동 증가의 시작값은 1000으로 설정해봤다.

그리고 증가값은 3으로 하기 위해 @@auto_increment_increment를 3으로 지정했다.

CREATE TABLE hongong3 (
    toy_id INT AUTO_INCREMENT PRIMARY KEY,
    toy_name CHAR(4),
    age INT
);

ALTER TABLE hongong3 AUTO_INCREMENT = 1000; -- 시작값은 1000으로 설정
SET @@auto_increment_increment = 3; -- 증가값은 3으로 지정

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

 

데이터를 추가해 처음 시작되는 값과 증가값을 확인해보자

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

SELECT * FROM hongong3;

 

참고로 데이터를 추가하기 위해 3줄을 입력한 쿼리문은 1줄로 바꿔쓸 수도 있다.

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

 

 

INSERT INTO ~ SELECT

많음 양의 데이터를 지금까지 했던 방식으로 직접 타이핑해서 입력한다면 오랜 시간이 걸릴 것이다.

다른 테이블에 이미 데이터가 입력되어 있다면 INSERT INTO ~ SELECT 구문을 사용해

해당 테이블의 데이터를 가져와서 한 번에 입력할 수 있다.

 

주의할 점

• SELECT 문의 열 개수는 INSERT할 테이블의 열 개수와 같아야 한다.
• 즉 SELECT의 열이 3개라면 INSERT 될 테이블의 열도 3개여야 한다.

MySQL을 설치할 때 함께 생성된 world 데이터베이스의 city 테이블의 개수를 조회해보겠다.

COUNT(*)를 사용하자.

SELECT COUNT(*) FROM world.city;

 

wolrd.city 테이블의 구조도 살펴볼 수 있다.

DESC 명령으로 테이블 구조를 확인할 수 있다.

DESC는 Describe의 약자로 테이블의 구조를 출력해주는 기능을 한다.

즉, CREATE TABLE을 어떻게 했는지 예상할 수 있다.

DESC world.city;

 

이번에는 LIMIT을 이용해 5건의 데이터를 살펴보자.

SELECT * FROM wolrd.city LIMIT 5;

 

이 중에서 도시 이름(Name)과 인구(Population)을 가져와보자.

먼저 테이블도 만들어주겠다.

CREATE TABLE city_popul (
    city_name CHAR(35),
    population INT
);

 

이제는 INSERT INTO ~ SELECT 을 사용해 world.city 테이블의 내용을 city_popul 테이블에 입력해보겠다. 

INSERT INTO city_popul
    SELECT Name, Population FROM world.city;

 

 

 

UPDATE 문

회원의 주소가 변경되는 경우처럼 행 데이터를 수정해야 하는 경우도 빈번하게 발생된다.

이럴 때 UPDATE를 사용해서 내용을 수정한다.

UPDATE는 기존에 입력되어 있는 값을 수정하는 명령이다.

 

기본적인 형식

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

 

UPDATE를 사용하기 전에 MySQL Workbench 설정을 변경해야 한다.

MySQL 워크벤치에서는 기본적으로 UPDATE 및 DELETE 를 허용하지 않기 때문이다.

 

 

생성한 city_popul 테이블의 도시 이름(city_name) 중에서 'Seoul'을 '서울'로 변경해보겠다.

UPDATE city_popul 
    SET city_name = '서울'
    WHERE city_name = 'Seoul';

SELECT * FROM city_popul WHERE city_name = '서울';

 

필요하다면 한꺼번에 여러 열의 값을 변경할 수도 있다.

콤마(,)로 분리해서 여러 개의 열을 변경해보자.

UPDATE city_popul
    SET city_name = '뉴욕', population = 0
    WHERE city_name = 'New York';
    
SELECT * FROM city_popul WHERE city_name = '뉴욕';

스펠링이 잘못된 것을 지금 확인 하였다..

 

 

WHERE가 없는 UPDATE 문

UPDATE는 사용법이 간단하지만 주의할 사항이 있다.

UPDATE를 WHERE 절 없이 사용하면 모든 행이 변경된다.

 

주의할 점

• UPDATE 문에서 WHERE 절은 문법상 생략이 가능하지만
• WHERE 절을 생략하면 테이블의 모든 행의 값이 변경된다.
• 일반적으로 전체 행의 값을 변경하는 경우는 별로 없으므로 주의해야 한다.

실행하지는 말자!!

UPDATE city_popul 
    SET city_name = '서울';

 

만약 이 SQL을 실행했다면 4096개의 도시 이름(city_name)이 '서울'로 바뀌었을 것이다.

 

그렇다면 전체 테이블의 내용은 어떤 경우에 변경할까?

city_popul 테이블의 인구(population) 열은 1명 단위로 데이터가 저장되어 있다.

이 단위를 10,000명 단위로 변경하면 좀 더 읽기 쉬울 것이다.

 

다음 SQL을 이용해서 모든 인구 열(population)을 한꺼번에 10,000으로 나눌 수 있다.

UPDATE city_popul
    SET population = population / 10000;

 

 

 

 

DELETE 문

테이블의 행 데이터를 삭제해야 하는 경우도 발생할 것이다.

예를 들어 회원이 탈퇴한 경우에 해당 회원의 정보를 삭제해야 한다.

이럴 때 DELETE를 사용해서 행 데이터를 삭제한다.

DELETE는 행 단위로 삭제하며, 형식은 다음과 같다.

 

기본적인 형식

DELETE FROM 테이블_이름 WHERE 조건;

 

city_popul 테이블에서 'New'로 시작하는 도시를 삭제해보자.

DELETE FROM city_popul
    WHERE city_name LIKE 'New%';

 

만약 'New' 글자로 시작하는 11건의 도시를 모두 지우는 것이 아니라, 'New' 글자로 시작하는

도시 중 상위 몇 건만 삭제하려면 LIMIT 구문과 함께 사용할 수 있다.

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

 

 

대용량 테이블 삭제

만약 몇억 건의 데이터가 있는 대용량의 테이블이 더 이상 필요 없다면 어떻게 삭제하는 것이 좋을까?

다음 SQL을 실행하면 각각 몇십만 건의 데이터를 가진 테이블이 3개 생성된다.

CREATE TABLE big_table1 (SELECT * FROM world.city, sakila.country); -- cross join
CREATE TABLE big_table2 (SELECT * FROM world.city, sakila.country);
CREATE TABLE big_table3 (SELECT * FROM world.city, sakila.country);

SELECT COUNT(*) FROM big_table1;

 

이제 동일한 내용의 대용량 테이블 3개를 DELETE, DROP, TRUNCATE 각각 다른 방법으로 삭제해보겠다.

 

우선 DELETE 문은 삭제가 오래 걸린다.

DROP 문은 테이블 자체를 삭제한다.

TRUNCATE 문도 DELETE와 동일한 효과를 내지만 속도가 무척 빠르다.

• DROP은 테이블 자체가 없어진다.
• DELETE 와 TRUNCATE는 빈 테이블을 남긴다.

DELETE FROM big_table1;
DROP TABLE big_table2;
TRUNCATE TABLE big_table3;

• 결론적으로 대용량 테이블의 전체 내용을 삭제할 때 테이블 자체가 필요 없을 경우에는 DROP으로 삭제
• 테이블의 구조는 남겨놓고 싶다면 TRUNCATE로 삭제하는 것이 효율적이다.