데이터베이스와 테이블을 만든 후에는 데이터를 변경하는, 즉 입력/수정/삭제하는 기능이 필요하다.
예를 들어, 새로 가입한 회원을 테이블에 입력할 때는 INSERT 문을, 회원의 주소나 연락처가 변경되어 정보를 수정할 때는 UPDATE문을 사용한다. 또 회원이 탈퇴해서 회원을 삭제할 때는 DELETE 문을 사용한다.
데이터 입력: INSERT
테이블에 행 데이터를 입력하는 기본적인 SQL 문은 INSERT.
INSERT 문의 기본 문법
INSERT는 테이블에 데이터를 삽입하는 명령. 기본적인 형식은 다음과 같다.
INSERT INTO 테이블 [(열1, 열2, ...)] VALUES (값1, 값2, ...)INSERT 문은 별로 어려울 것이 없으니, 주의할 점만 몇 가지 확인하자.
우선 테이블 이름 다음에 나오는 열은 생략이 가능하다. 열 이름을 생각할 경우에 VALUES 다음에 나오는 값들의 순서 및 개수는 테이블을 정의할 때의 열 순서 및 개수와 동일해야 한다. 간단한 테이블을 만들어서 연습해보자.
테이블의 열이 3개이므로 입력할 때도 차례에 맞춰서 3개를 입력했습니다.
USE market db;
CREATE TABLE hongong1 (toy id INT, toy name CHAR(4), age INT);
INSERT INTO hongong1 VALUES (1, '우디', 25);이 예제에서 아이디(toy_id)와 이름(toy_name)만 입력하고 나이(age)는 입력하고 싶지 않다면 다음과 같이 테이블 이름 뒤에 입력할 열의 이름을 써줘야 한다. 이 경우 생략한 나이(age) 옆에는 아무것도 없다는 의미의 NULL 값이 들어간다.
INSERT INTO hongong1 (toy id, toy name) VALUES (2, '버즈');열의 순서를 바꿔서 입력하고 싶을 때는 열 이름과 값을 원하는 순서에 맞춰 써주면 됩니다.
INSERT INTO hongong1 (toy name, age, toy_id) VALUES ('제시', 28, 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 hongang2 VALUES (NULL, '보핑', 25);
INSERT INTO hongong2 VALUES (NULL, '슬림키', 22);
INSERT INTO hongong2 VALUES (NULL, '렉스', 21)
SELECT FROM hongong2;AUTO INCREMENT 자장한 열은 1부터 자동으로 입력된다. 데이터를 입력할 때는 NULL로 지정하면 된다.
계속 입력하다 보면 현재 어느 숫자까지 증가되었는지 확인이 필요. 다음 SQL을 입력해보자. 책과 동일하게 실습했다면 3이 나올텐데, 자동 증가로 3까지 입력되었다는 의미.
SELECT LAST INSERT_10();만약 AUTO_INCREMENT로 입력되는 다음 값을 100부터 시작하도록 변경하고 싶다면 다음과 같이 실행한다. ALTER TABLE 뒤에는 테이블 이름을 입력하고, 자동 증가를 100부터 시작하기 위 AUTO INCREMENT를 100으로 지정한다. ``` ALTER TABLE hangong2 AUTO_INCREMENT=100; INSERT INTO hongong? VALUES (NULL, '재남', 35); SELECT * FROM hongong2; ``` _ALTER TABLE은 테이블을 변경하라는 의미. 테이블의 열 이름 변경, 새로운 열 정의, 열 삭제 등의 작업을 한다._
이번에는 처음부터 입력되는 값을 1000으로 지정하고, 다음 값은 1003,1006, 1009,...으로 3씩 증가하도록 설정하는 방법을 살펴보자.
이런 경우에는 시스템 변수인 @@auto_increment_increment를 변경시켜야 한다. 테이블을 새로 만들고 자동 증가의 시작값은 1000으로 설정. 그리고 증가값은 3으로 하기 위해@@auto_increment_increment를 3으로 지정.
CREATE TABLE hongang3 (
toy id INT AUTO INCREMENT PRIMARY KEY,
toy nane CHAR(4),
age INT);
ALTER TABLE hongong3 AUTO INCREMENT 1000; → 시작값은 1000으로 지정
SET @auto_increment_increment=3; → 증가값은 3으로 지정+ 여기서 잠깐 시스템 변수
사스템 변수란 MySQL에서 자체적으로 가지고 있는 실정값이 철된 변수. 주로 MySQL의 환경과 관련된 내용이 처 잘되어 있으며, 그 개수는 500개 이상이다.
시스템 변수는 앞에 @@가 볼는 것이 특징이며, 시스템 변수의 값을 확인하려면 SELECT @@시스템수를 실행하면 된다. 만약, 전체 시스템 변수의 종류를 알고 싶다면 SHOW GLOBAL VARIABLES를 실행하면 된다.
다음 SQL을 실행해 처음 시작되는 값과 증가값을 확인해봅시다.
INSERT INTO hongong3 VALUES (NULL, '토마스', 20);
INSERT INTO hongong) VALUES (NULL, '제임스', 23);
INSERT INTO hongong3 VALUES (NULL, '25', 25);
SELECT * FRON hongong3;+여기서 잠깐 여러 줄을 한 줄로 작성
지금까지 3건을 입력하기 위해서는 다음과 같이 3줄로 입력했다.
INSERT INTO 테이블 이름 VALUES (21, 22, ...);
INSERT INTO 테이블 이름 VALUES (23, 24, ...);
INSERT INTO 레이블 이름 VALUES (225, 26, ...);이는 다음과 같이 출력할 수 있다. 그 이상도 마찬가지.
INSERT INTO 테이블 이름 VALUES (1, 2, ...), (23, 24, ...), (25, 26, ...);다른 테이블의 데이터를 한 번에 입력하는 INSERT INTO ~ SELECT
많은 양의 데이터를 지금까지 했던 방식으로 직접 타이핑해서 입력하려면 오랜 시간이 걸릴 것이다. 다른 테이블에 이미 데이터가 입력되어 있다면 INSERT INTO ~ SELECT 구문을 사용해 해당 테이블의 데이터를 가지와서 한 번에 입력할 수 있다.
INSERT INTO 테이블_이름 (열_이름1, 열_이름2,...)
SELECT 문 ;주의할 점은 SELECT 문의 개수는 INSERT할 테이블의 열 개수와 같아야 한다. 즉 SELECT의 열이 3개라면 INSERT될 테이블의 열도 3개여야 한다.
먼저 MySQL을 설치할 때 함께 생성된 world 테이터베이스의 city 테이블의 개수를 조회해보자. 앞에서 배운 COUNT()를 사용. 4079개가 나왔다. 도시가 4079개 저장되어 있다는 의미.
SELECT COUNT(*) FROM world.city;데이터베이스이름테이블이름으로 할 수 있다.
이번에는 world.city 테이블의 구조를 살펴보자. DESC 명령으로 이를 구조를 확인할 수 있다. DESC는 Describe의 약자로 테이블의 구조를 주는 기능을 한다. 즉, CREATE TABLE을 어떻게 했는지 예상할 수 있다.
DESC world.city;데이터도 몇 건 살펴보는 것이 좋겠다. LIMIT을 사울에서 5건 정도만 살펴보자.
SELECT FROM world.city LIMIT 5;이 중에서 도시 이름(Name)과 인구(Population)를 가지와보자. 먼저 테이블을 만든다. 테이블은 DESC로 확인한 열 이름(Filed)과 데이터 형식(Type)을 사용하면 된다. 필요하다면 열 이름은 바꿔도 상관없음.
CREATE TABLE city_popul (city_name CHAR(35), population INT);이제는 world.city 테이블의 내용을 city_popul 테이블에 입력 해보자. 결과 메시지로는 4079행이 처리된 것으로 나온다. 이렇게 다른 테이블의 데이터를 한 번에 가져오는 방법을 확인해보자.
INSERT INTO city_popul
SELECT Name, Population FROM world.city;INSERT INTO~SELECTE 다른 테이블의 데이터를 한 번에 입력하는 구문이다.
결과 메시지
4879 row(s) affected Records: 4079 Duplicates: 0 Warnings: 0
데이터 수정: UPDATE
회원의 주소가 변경되는 경우처럼 행 데이터를 수정해야 하는 경우도 빈번하게 발생한다. 이럴 때 UPDATE를 사용해서 내용을 수정한다.
UPDATE 문의 기본 문법
UPDATE는 기존에 입력되어 있는 값을 수정하는 명령. 기본적인 형식은 다음과 같다.
UPDATE 테이블 이름
SET 열1=값1, 열2=값2,...
WHERE 조건;+ 여기서 잠깐 MySQL Workbench 설정 변경
MySQL 워크벤치에서는 기본적으로 UPDATE 및 DELETE를 허용하지 않기 때문에 UPDATE를 실행하기 전에 설정을 변경해야 한다. 먼저 기존에 열린 캐리 창을 모두 종료한다. [Edit][Preferences] 메뉴를 실행하고 Workbench Preferences 창의 [SQL, Editor)에서 "Safe Updates (rejects UPDATEs and DELETEs with no restrictions 체크 해제한 후 [OK] 버튼을 클릭.
설정한 내용을 적용하려면 MySQL 워크벤치를 재시작해야함. MySQL 워크벤치를 하고 다시 실행해서 'root/0000 으로 접속한 후 새 쿼리 창을 열어서 작업을 계속 진행한다.
앞에서 생성한 city_popul 테이블의 도시 이름(city_name) 중에서 'Seoul'을 '서울'로 변경해보자. 새 쿼리 창을 열고 다음 SQL을 실행한다. 결과를 보면 한글로 잘 변경되었다.
USE market db;
UPDATE city popul
SET city name '서울'
WHERE city_name = 'Seoul';
SELECT FROM city popul WHERE city_name = '서울';필요하면 한꺼번에 여러 열의 값을 변경할 수도 있다. 콤마(,)로 분리해서 여러 개의 열을 변경 하면 된다. 다음 SQL은 도시 이름(city_name)인 'New York'을 '뉴욕'으로 바꾸면서 동시에 인구(population)는 0으로 설정하는 내용이다.
UPDATE city_popul
SET city_name = "&", population = 0
WHERE city_name = 'New York';
SELECT FROM city popul WHERE city_name = '뉴욕';데이터를 변경하려면 UPDATE~SET~ WHERE 칠을 사용한다.
WHERE가 없는 UPDATE 문
UPDATE는 사용법이 간단하지만 주의할 사항이 있다. UPDATE 문에서 WHERE 절은 문법 상 생략이 가능하지만, WHERE 절을 생략하면 테이블의 모든 행의 값이 변경된다. 일반적으로 전체 행의 값을 변경하는 경우는 별로 없으므로 주의해야 한다. 다음 SQL은 문제가 있으니 실행하지는 말자.
UPDATE city_popul
SET city name '서울';만약 이 SQL을 실행했다면 4000개가 넘는 모든 도시 이름(city_name)이 '서울'로 바뀌었을 것이다. WHERE 절이 없기 때문에 도시 이름(city_name) 열의 모든 값을 '서울'로 바꿔버린다. 그러므로 UPDATE 문에 WHERE가 없다면 꼭 SQL을 상세히 확인해보기 바란다.
그렇다면 전체 테이블의 내용은 어떤 경우에 변경할까? city_popul 테이블의 인구(population) 열은 1명 단위로 데이터가 저장 되어있다. 아프가니스탄의 도시 카불(Kabul)의 경우 인구 (population)가 1,780,000명인데, 이 단위를 10,000명 단위로 변경하면 좀 더 읽기 쉬울 것이다.
다음 SQL을 이용해서 모든 인구 열(population)을 한꺼번에 10,000으로 나눌 수 있다. 5개 행만 조회해보자. 인구 열이 10,000명 단위로 변경되어서 한눈에 보기 편해졌다.
UPDATE city popul
SET population population / 10000;
SELECT FROM city popul LIMIT 5;데이터 삭제: DELETE
테이블의 행 데이터를 삭제해야 하는 경우도 발생한다. 예를 들어 회원이 탈퇴한 경우에 해당 회원 의 정보를 삭제 해야한다. 이럴 때 DELETE를 사용해서 행 데이터를 삭제한다.
DELETES UPDATE와 거의 비슷하게 사용할 수 있다. DELETE는 행 단위로 삭제하며, 형식은 다음과 같다.
DELETE FROM 테이블이름 WHERE 조건;city_popul 테이블에서 'New'로 시작하는 도시를 삭제하기 위해 다음과 같이 실행한다. 도시 이름에 앞에 New가 들어가는 도시는 Newcastle, Newport, New Orleans 등 11개 정도가 있다.
DELETE FROM city_popul WHERE city name LIKE 'Nev";UPDATE와 마찬가지로 WHERE 절이 생략되면 전체 행 데이터를 삭제하므로 주의해야한다.
만약 'New' 글자로 시작하는 11건의 도시를 모두 지우는 것이 아니라, 'New' 글자로 시작하는 도 시 중 상위 및 건만 삭제하려면 LIMIT 구문과 함께 사용하면 된다. 다음과 같이 실행하면 'New' 글자로 시작하는 도시 중에서 상위 5건만 삭제됩니다(이미 앞에서 관련 데이터가 삭제되어서 다음 SQL은 실행해도 0건이 삭제된다).
DELETE FROM city_popul
WHERE city name LIKE 'New'
LIMIT 5;DELETE WHERE없이 사용 하면 모든 대미티가 삭제된다.
좀더 알아보기 : 대용량 테이블의 삭제
만약 몇억 건의 데이터가 있는 대용량의 테이블이 더 이상 필요 없다면 어떻게 삭제하는 것이 좋을까?
우선 대용량 테이블을 3개 준비한다. 다음 SQL을 실행하면 각각 몇 십만 건의 데이터를 가진 big tablel, big table2, big table3이 생성된다. 데이터는 모두 동일. 결과를 확인하면 44만 건 정도가 있을 것.
CREATE TABLE big table1 (SELECT * FROM world.city, sakila.country);
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;이 SQL에서 생성된 데이터는 단지 대량의 테이블을 만들기 위한 것이며 내용에 의미는 X. 이러한 SQL을 상호 조인(aross join)이라고 부르는데 뒤에서 배우니 지금은 그냥 대량의 데이터를 만든다는 정도로만 이해하고 넘어가자.
이제 동일한 내용의 대용량 테이블 3개를 DELETE, DROP, TRUNCATE 각각 다른 방법으로 삭제해보자.
우선 DELETE 문은 삭제가 오래 걸린다. 필자는 3.5초 정도가 걸렸는데 만약 데이터가 수억 전 이상이라면 훨씬 오랫동안 삭제할 수도 있다. DROP 문은 테이블 자체를 삭제한다. 그래서 순식간에 삭제된다. TRUNCATE 문도 DELETE와 동일한 효과를 내지만 속도가 무척 빠르다. DROP은 테이블이 아예 없어지지만, DELETE와 TRUNCATE는 빈 테이블을 남긴다.
DELETE FROM big_tablel;
DROP TABLE big table2;
TRUNCATE TABLE big table3;TRUNCATE는 DELETE와 달리 WHERE 문을 사용할 수 없다. 그러므로 TRUNCATE는 조건 없이 전체 행을 삭제할 때만 사용한다.
결론적으로 대용량 테이블의 전체 내용을 삭제할 때 테이블 자체가 필요 없을 경우에는 DROP으로 삭제하고, 테이블의 구조는 남겨놓고 싶다면 TRUNCATE로 삭제하는 것이 효율적이다.
정리
- INSERT 문은 테이블에 데이터를 입력하는 명령.
- AUTO_INCREMENT는 1부터 증가하는 값을 자동으로 입력해줌. 해당 열은 PRIMARY KEY로 지정해야함.
- INSERT INTO ~SELECT는 다른 테이블의 데이터를 가져와서 한 번에 대량으로 입력한다.
- UPDATE는 기존에 입력되어 있는 값을 수정하며 주로 WHERE와 함께 사용한다.
- DELETE는 행 단위로 삭제하며 WHERE가 없으면 전체 행이 삭제된다.
관련 중요 용어
- NULL : / 아무것도 없는 값. AUTO_INCREMENT 열에 값을 입력할 때는 NULL로 지정.
- PRIMARY KEY : PK / 기본 키. AUTO_INCREMENT 열은 기본 키로 지정해야 함.
- ALTER TABLE : / 테이블의 구조를 변형하는 SQL.
- 시스템 변수 : / MySQL에서 자체적으로 가지고 있는 설정값이 저장된 변수.
- @@auto_increment increment : / AUTO INCREMENT의 증가값을 지정하는 시스템 변수.
- DESCRIBE : DESC / 테이블의 구조를 확인하는 SQL
- TRUNCATE : / DELETE와 비슷한 기능이지만 전체 행을 삭제할 때 사용
