SQL 기본 문법 4 - 데이터 변경을 위한 SQL 문

어제 1시 반쯤 취침했는데 알람 없이 5시 반에 일어나 다시 새벽 공부를 시작해 본다. 한 단원만 공부하고 매일 해야 하는 수학 공부를 할 생각이다. 이번주에는 새롭게 아침 7시 혹은 7시 반에 운동하는 습관을 들이고 있다. 처음에는 힘들었으나 요즘은 이 시간이 기다려진다.

이 운동 습관을 들인 후로 지각하는 습관도 고칠 수 있었다. 지각은 아니지만 항상 9시 7분 정도에 도착하곤 했었다. 한번 이이 시간에 오게 된 후로 지난 두 달 동안 더 일찍 등교하려고 해도 잘 되지 않았던 일이었다. 근데 신기하게도 운동 습관을 만든 후로는 20분 정도 일찍 등교하게 되었다. 오늘도 아침 운동을 하려면 서둘러 이 학습을 마쳐야 한다.

 

앞서 배운 SELECT는 이미 만들어 놓은 테이블에서 데이터를 추출하는 구문이다. 이번 절에서는 입력, 수정, 삭제를 통해 행 데이터를 구축하는 방법을 상세하게 알아보겠다.

 

시작하기 전에

데이터베이스와 테이블을 만든 후에는 데이터를 변경하는, 즉 입력/수정/삭제하는 기능이 필요하다. 예를 들어, 새로 가입한 회원을 테이블에 입력할 때는 INSERT 문을, 회원의 주소나 연락처가 변경되어 정보를 수정할 때는 UPDATE 문을 사용한다. 또, 회원이 탈퇴하여 회원을 삭제할 때는 DELETE 문을 사용한다.

 

 

---

 

 

 

 

데이터 입력 : INSERT

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

 

INSERT 문의 기본 문법

INSERT는 테이블에 데이터를 삽입하는 명령이다. 기본 형식은 다음과 같다.

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

 

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

 

 

테이블 열이 3개이므로 입력할 때도 차례에 맞춰서 3개를 입력했다. 이 예제에서 아이디와 이름만 입력하고 나이는 입력하고 싶지 않다면 다음과 같이 테이블 이름 뒤에 열의 이름을 써줘야 한다. 이 경우 생략한 나이 열에는 아무것도 없다는 의미의 NULL이 들어간다.

 

 

 

 

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

---

 

 

 

 

자동으로 증가하는 AUTO_INCREMENT

AUTO_INCREMENT는 열을 정의할 때 1부터 증가하는 값을 입력해준다. INSERT에서는 해당 열이 없다고 생각하고 입력하면 된다. 단, 주의할 점은 AUTO_INCREMENT로 지정하는 열은 꼭 PRIMARY KEY로 지정해줘야 한다.

 

 

1) 자동 증가하는 테이블 만들기

우선 간단한 테이블을 만들어보겠다. 아이디(toy_id) 열을 자동 증가로 설정했다.

 

 

2) 테이블에 데이터 입력하기

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

 

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

 

 

 

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

 

자동 증가로 3까지 증가되었음을 알 수 있다. 

 

 

 

3) 자동 증가 시작값 변경하기

AUTO_INCREMENT로 입력되는 값을 100부터 시작하도록 변경하고 싶다면 다음과 같이 실행한다. ALTER TABLE 뒤에는 테이블 이름을 입력하고, 자동 증가를 100부터 시작하기 위해 AUTO_INCREMENT를 100으로 지정한다

 

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

 

 

 

4) 일정한 규칙으로 3씩 증가하도록 설정하기

이번에는 처음부터 입력되는 값을 1000으로 설정하고, 다음 값은 1003, 1006, 1009, ...으로 3씩 증가하도록 설정해보려고 한다. 이 경우에는 시스템 변수인 @@auto_increment_increment를 변경해야 한다. 테이블을 새로 만들고 자동 증가의 시작값은 100으로 설정했다. 그리고 증가값을 3으로 하기 위해 @@auto_increment_increment를 3으로 지정했다.

 

 

시스템 변수

시스템 변수란 MySQL에서 자체적으로 가지고 있는 설정값이 저장된 변수를 말한다. 주로 MySQL의 환경과 관련된 내용이 저장되어 있으며, 그 개수는 500개 이하이다.

시스템 변수는 앞에 @@가 붙는 것이 특징이며, 시스템 변수의 값을 확인하려면 SELECT @@시스템변수를 실행하면 된다. 만약, 전체 시스템 변수의 종류를 알고 싶다면 SHOW GLOBAL VARIABLES를 실행하면 된다.

---

 

5) 시스템 변수로 변경된 값 확인하기

이제 처음 시작되는 값과 증가값을 확인해보자.

 

이렇게 시작값이 1000이고, 끝값이 1006임을 확인할 수 있다.

 

 

---

 

 

 

 

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

많은 양의 데이터를 지금까지 했던 방식으로 직접 타이핑해서 입력하려면 오랜 시간이 걸릴 것이다. 다른 테이블에 이미 데이터가 있다면 INSERT INTO ~ SELECT 구문을 사용해 해당 테이블의 데이터를 가져와 한번에 입력할 수 있다.

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

 

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

 

 

1) 테이블 개수 조회하기

먼저 MySQL을 설치할 때 함께 생성된 world 데이터베이스의 city 테이블 개수를 조회해 보겠다. 이때 COUNT(*)을 사용한다.

 

도시가 4079개 저장되어 있다.

 

 

2) 테이블 구조 살펴보기

이번에는 world.city 테이블의 구조를 살펴보겠다. DESC 명령으로 테이블 구조를 확인할 수 있다. DESC는 Describe의 약자로 테이블의 구조를 출력해 주는 기능을 한다. 즉, CREATE TABLE을 어떻게 했는지 예상할 수 있다.

 

 

3) 5건의 데이터만 살펴보기

이 중에서 LIMIT를 사용해서 5건만 살펴본다.

 

 

4) 특정 데이터 한 번에 가져오기

이 중에서 도시 이름과 인구를 가져올 것이다. 먼저 테이블을 만들어보겠다. 테이블은 DESC로 확인한 열 이름(Filed)과 데이터 형식(Type)을 사용하면 된다. 필요시 열 이름은 바꿔도 상관없다.

 

 

이제 world.city 테이블의 내용을 city_popul 테이블에 입력해 보겠다.

 

결과 메시지로 4079행이 처리된 것으로 나온다. 이렇게 다른 테이블의 데이터를 한 번에 가져올 수 있다.

---

 

 

 

 

데이터 수정 : UPDATE

회원의 주소가 변경되는 경우처럼 행 데이터를 수정해야 하는 경우도 있다. 이럴 때 UPDATE를 사용해서 내용을 수정한다.

 

UPDATE 문의 기본 문법

UPDATE는 기존에 입력되어 있는 값을 수정하는 명령이다. 기본적인 형식은 다음과 같다.

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

 

MySQL Workbench 설정 변경

MySQL 워크벤치에서는 기본적으로 UPDATE 및 DELETE를 허용하지 않기 때문에 UPDATE를 실행하기 전에 설정을 변경해야 한다. 

 

이것을 설정하지 않으면 이처럼 오류가 발생한다. 먼저 기존에 열린 쿼리 창을 모두 종료하고 다음과 같이 SQL Editor에서 'Safe Updates'를 체크해체한다.

 

그런 다음 MySQL 워크벤치를 다시 시작해야 한다.

 

 

 

이제 서울의 영문 이름이 한글로 잘 변경되었다.

 

 

여러 개의 열을 동시에 변경하기

한꺼번에 여러 열을 변경하려면 콤마로 분리하면 된다. 다음은 도시 이름의 'New York'을 '뉴욕'으로 바꾸면서 동시에 인구는 0으로 설정한 예시이다.

---

 

 

 

WHERE가 없는 UPDATE 문

UPDATE는 사용법이 간단하지만 주의할 사항이 있다. UPDATE 문에서 WHERE 절은 문법상 생략이 가능하지만, WHERE 절을 생략하면 테이블의 모든 행의 값이 변경된다. 일반적으로 전체 행의 값을 변경하는 경우에는 별로 없으므로 주의해야 한다. 다음은 실행하지 않고 예시 코드만 보여줄 것이다.

 

만약 이 SQL을 실행했다면 4000개가 넘는 모든 도시 이름이 서울로 바뀌었을 것이다. 그러므로 UPDATE 문에 WHERE가 없다면 꼭 SQL을 상세히 확인해야 한다.

 

전체 테이블의 내용은 어떤 경우에 변경할까? city_popul 테이블의 인구 열은 1명 단위로 데이터가 저장되어 있다. 이 단위를 1만 명 단위로 변경하면 좀 더 읽기 쉬울 것 같다.

다음 SQL을 이용해서 모든 인구 열을 한꺼번에 10000으로 나눌 수 있다. 이중 5개의 행만 조회해 볼 것이다.

---

 

 

 

 

데이터 삭제 : DELETE

회원이 탈퇴한 경우에 해당 회원의 정보를 삭제해야 한다. 이럴 때 DELETE를 사용해서 행 데이터를 삭제한다. DELETE도 UPDATE와 거의 비슷하게 사용할 수 있다. DELETE는 행 단위로 삭제하며, 형식은 다음과 같다.

DELETE FROM 테이블이름 WHERE 조건 ;

 

city_popul 테이블에서 'New'로 시작하는 도시를 삭제하기 위해 다음과 같이 실행해 본다.

 

 

만약 'New' 글자로 시작하는 11건의 도시를 모두 지우는 것이 아니라, 'New' 글자로 시작하는 도시 중 상위 몇 건만 삭제하려면 LIMIT 구문과 함께 사용하면 된다. 하지만 앞에서 관련 데이터가 삭제되어서 다음 SQL은 실행해도 0건이 삭제된다.

---

 

 

대용량의 테이블 삭제

만약 몇억 건의 데이터가 있는 대용량의 테이블이 더 이상 필요 없다면 어떻게 삭제하는 것이 좋을까? 우선 대용량 테이블을 3개 준비하겠다. 다음 SQL을 실행하면 각각 몇십만 건의 데이터를 가진 big_table1, big_table2, big_table3이 생성된다. 데이터는 모두 동일하다.

 

 

데이터가 많아서 시작이 좀 걸렸다.

 

 

이렇게 44만 개 이상의 데이터가 조회되었다. 이제 동일한 방법으로 이 데이터를 삭제해볼 것이다.

DELETE는 삭제가 오래 걸린다. DROP 문은 테이블 자체를 삭제한다. 그래서 순식간에 삭제된다. TRUNCATE 문도 DELETE와 동일한 효과를 내지만 속도가 무척 빠르다.

 

그래도 5초 만에 모두 삭제되었다. 결론적으로 대룡량 테이블의 전체 내용을 삭제할 때 테이블 자체가 필요 없을 경우에는 DROP으로 삭제하고, 테이블의 구조는 남겨놓고 싶다면 TRUNCATE로 삭제하는 것이 효율적이다.

---

 

 

 

 

단원 마무리하기

 

---

 

 

 

확인 문제 풀기

 

 

 

 

 

 

이번 단원은 3문제나 틀렸다. 3번은 1번이고, 4번은 2번이다. 5번은 4번이었다. 모두 암기해야 하는 부부인데 아직 외우지는 못했다.

 

---

 

 

 

학습을 마치고

오늘 새벽에 2시간 동안 이 단원을 학습했다. 강의를 듣고 실습을 진행하며 학습일지를 쓰는데 생각보다 시간이 많이 걸린다. 그래도 이 정도쯤 공부하면 대충 감이 잡힌다.