[MySQL] 데이터 변경을 위한 SQL문(INSERT, UPDATE, DELETE)

이 글은 혼자 공부하는 SQL(저자 : 우재남)의 책과 유튜브 영상을 참고하여 개인적으로 정리하는 글임을 알립니다.

---

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

예를 들어, 새로 가입한 회원을 테이블에 입력할 때는 INSERT 문을, 회원의 주소나 연락처가 변경되어 정보를 수정할 때는 UPDATE문을 사용한다. 회원이 탈퇴해서 회원을 삭제할 때는 DELETE문을 사용한다.

 

데이터 입력 : INSERT

INSERT문의 기본 문법

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

INSERT INTO 테이블이름(열1, 열2, 열3) VALUES (값1, 값2, 값3);

테이블 이름 다음에 나오는 열은 생략이 가능하다.

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

USE market_db; //데이터베이스 선택
CREATE TABLE hongong1 (toy_id  INT, toy_name CHAR(4), age INT); //테이블 생성
INSERT INTO hongong1 VALUES (1, '우디', 25); //테이블에 데이터 추가

만약 아이디와 이름만 입력하고 나이는 입력하고 싶지 않다면 아래와 같이 테이블 이름 뒤에 입력할 열의 이름을 써줘야 한다. 이 경우 생략한 나이(age) 열에는 아무것도 없다는 의미의 NULL 값이 들어간다.

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

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

INSERT INTO hongong1(toy_name,age, toy_id) VALUES ('제시', 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);

이후에 3개의 데이터를 추가해 주고, 행과 열을 확인해 보면, 자동으로 아이디가 증가했음을 알 수 있다.

INSERT INTO hongong2 VALUES (NULL, '보핍', 25);
INSERT INTO hongong2 VALUES (NULL, '슬링키', 22);
INSERT INTO hongong2 VALUES (NULL, '렉스', 21);
SELECT * FROM hongong2;

계속 데이터를 추가하다 보면 현재 어느 숫자까지 증가되었는지 확인하려면 아래의 SQL문을 이용하면 된다.

SELECT LAST_INSERT_ID();

만약 AUTO_INCREMENT로 입력되는 다음의 값을 100부터 시작하도록 변경하고 싶다면 아래와 같은 SQL문을 실행하면 된다.(ALTER TABLE 뒤에는 테이블 이름을 입력하고, 자동 증가를 100부터 시작하기 위해 AUTO_INCREMENT를 100으로 지정)

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

ALTER TABLE
테이블을 변경하라는 의미이다. 테이블의 옆 이름 변경, 새로운 열 정의, 열 삭제 등의 작업을 한다.

 

이번에는 처음으로 입력되는 값을 1000으로 지정하고 다음 값은 1003, 1006, 1009,...으로 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;
SET @@auto_increment_increment=3;

이후 데이터를 추가한 후 확인해 보면 아래와 같은 결과가 나온다.

INSERT INTO hongong3 VALUES (NULL, '토마스', 20);
INSERT INTO hongong3 VALUES (NULL, '제임스', 23);
INSERT INTO hongong3 VALUES (NULL, '고든', 25);
#위 세 줄의 코드는 아래의 코드 한 줄과 같은 의미이다.
#INSERT INTO hongong3 VALUES (NULL, '토마스', 20), (NULL, '제임스', 23), (NULL, '고든', 25);
SELECT * FROM hongong3;

 

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

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

다른 테이블에 이미 데이터가 입력되어 있다면 INSERT INTO ~ SELECT 구문을 사용해 해당 테이블의 데이터를 가져와서 한 번에 입력할 수 있다.

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

주의할 점은 SELECT 문의 열 개수는 INSERT 할 테이블의 열 개수와 같아야 한다.

즉, SELECT의 열이 3개라면 INSERT 될 테이블의 열도 3개여야 한다.

 

먼저 MySQL을 설치할 때 함께 생성된 world 데이터베이스의 city 테이블의 개수를 조회해 보면, 4079개가 나온다.

SELECT COUNT(*) FROM world.city;

world.city 테이블의 구조를 확인하려면 DESC 명령으로 확인할 수 있다.

DESC world.city;

 

데이터도 5건 정도 살펴보자

SELECT * FROM world.city LIMIT 5;

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

먼저, 가져오기 전에 새로운 테이블을 먼저 만들어야 한다.(DESC로 확인한 열 이름과 데이터 형식을 사용)

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

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

 

 

데이터 수정 : UPDATE

UPDATE문의 기본 문법

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

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

MySQL Workbench 설정 변경
워크벤치에서는 기본적으로 UPDATE 및 DELETE를 허용하지 않기 때문에 설정을 변경해야 한다.
1. 먼저 기존에 열린 쿼리 창을 모두 종료한다.
2. Edit -> Preferences메뉴를 실행한다.

3. 위 그림처럼 밑 줄 친 곳의 체크 박스를 해제하고 OK를 누른다.
4. 워크벤치를 재시작한다.

 

앞에 생성한 city_popul 테이블의 도시 이름 중에서 Seoul을 서울로 변경해 보자.

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

 

아래는 도시 이름인 NewYork을 뉴욕으로 바꾸면서 인구는 0으로 설정하는 명령이다.

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 절을 생략하면 테이블의 모든 행의 값이 변경된다.
이러한 경우는 드물뿐더러, 끔찍한 결과를 초래할 수 있으니 주의하자

 

 

데이터 삭제 : DELETE

DELETE는 행 단위로 삭제하며 형식은 아래와 같다.

DELETE FROM 테이블 이름 WHERE 조건;

city_popul 테이블에서 New로 시작하는 도시를 삭제하기 위해 아래와 같이 실행한다.

DELETE FROM city_popul 
    WHERE city_name LIKE 'New%';

만약 New로 시작하는 도시 모두를 삭제하는 것이 아니라 상위 몇 건만 삭제하려면 LIMIT 구문과 함께 사용하면 된다.

DELETE FROM city_popul 
    WHERE city_name LIKE 'New%'
    LIMIT 5; #상위 5건만 삭제

DELETE, DROP, TRUNCATE의 차이
DELETE문은 삭제가 오래 걸린다.
TRUNCATE문은 DELETE와 동일한 효과를 내지만 속도가 매우 빠르다. 하지만 WHERE 문을 사용할 수 없다는 것을 주의해야 한다.
DROP문은 테이블 자체를 삭제한다.
반면에 TRUNCATE문과 DELETE는 빈 테이블을 남긴다.
따라서 테이블 자체를 삭제할 때는 DROP를 쓰고,  테이블의 구조는 남겨놓고 싶다면 TRUNCATE로 삭제하는 것이 효율적이다.

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