3장 -3 데이터 변경을 위한 SQL문
데이터베이스를 만든 후 입력/수정/삭제하는 기능
입력할 때는 INSERT
수정은 UPDATE
삭제는 DELETE 문을 사용
1. 데이터 입력: INSERT
INSERT 문의 기본 문법
INSERT INTO 테이블 [(열1, 열2, ...)] VALUES (값 1, 값 2, ...)
테이블 이름 다음에 나오는 열은 생략이 가능. 열 이름을 생략할 경우 values 다음에 나오는 값들의 순서 및 개수는 테이블을 정의할 때의 열 순서 및 개수와 동일해야 한다. 간단한 테이블을 만들어보자.
위 예제에서는 아이디와 이름만 입력하고 나이는 입력하고 싶지 않다면 아래와 같이 테이블 이름 뒤에 입력할 열의 이름을 써줘야 한다. 이 경우 생략한 나이 열에는 아무것도 없다는 의미의 NULL 값이 들어간다.
INSERT INTO hongong1 (toy_id, toy_name) VALUES (2, '버즈');hongong1hongong1
열의 순서를 바꿔서 입력하고 싶을 때는 열 이름과 값을 원하는 순서에 맞춰 써주면 된다.
INSERT INTO hongong1 (toy_name,age, toy_id) VALUES ('제시', '20', 3);
2. 자동으로 증가하는 AUTO_INCREMENT
AUTO_INCREMENT는 열을 정의할 때 1부터 증가하는 값을 입력해준다. INSERT에서는 해당 열이 없다고 생각하고 입력하면 된다. 주의할 점은 AUTO_INCREMENT로 지정하는 열은 꼭 PRIMARY KEY로 지정해줘야 한다.
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;
계속 입려하다보면 현재 어느 숫자까지 증가되었는지 확인이 필요하다. 그를 확인하는 SQL은 다음과 같다.
SELECT LAST_INSERT_ID();
AUTO_INCREMENT로 입력되는 값을 100부터 시작하도록 변경하고 싶다면 다음과 같이 실행하면 된다.
ALTER TABLE hongong2 AUTO_INCREMENT=100;
INSERT INTO hongong2 VALUES (NULL, '재남', 35);
SELET * FROM hongong2;
ALTER TABLE은 테이블을 변경하라는 의미이다. 테이블의 열 이름 변경, 새로운 열 정의, 열 삭제 등의 작업을 한다. 5장에서 자세하게 배우게 된다.
시작되는 값을 1000이라 하고 3씩 증가하도록 설정하는 방법은 다음과 같다.
시스템 변수인 @@auto_increment_increment를 변경시켜야 한다.
SQL은 다음과 같다.
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;
*시스템 변수란?
1. MySQL에서 자체적으로 가지고 있는 설정값이 저장된 변수를 뜻함. 주로 MySQL의 환경과 관련된 내용이 저장되어 있으며, 그 개수는 500개 이상이다.
2. 시스템 변수는 앞에 @@가 붙는 것이 특징이며, 시스템 변수의 값을 확인하려면 SELECT @@시스템변수를 실행하면 된다. 전체 시스템 변수의 종류를 알고 싶다면 SHOW GLOBAL VARIABLES를 실행하면 된다.
아래의 SQL을 실행해 처음 시작되는 값과 증가값을 확인하면 다음과 같다.
INSERT INTO hongong3 VALUES (NULL, '토미스', 20);
INSERT INTO hongong3 VALUES (NULL, '미스', 21);
INSERT INTO hongong3 VALUES (NULL, '제스', 23);
SELECT * FROM hongong3;
3. 다른 테이블의 데이터를 한 번에 입력하는 INSERT INTO ~ SELECT
많은 양의 데이터를 지금까지 했던 방식으로 하면 불편하다.
다른 테이블에 이미 데이터가 입력되어 있다면 INSERT INTO ~SELECT 구문을 사용해 해당 테이블의 데이터를 가져와서 한 번에 입력할 수 있다.
기본 SQL문의 형식은 다음과 같다.
INSERT INTO 테이블_이름 (열_이름1, 열_이름2, ...)
SELECT 문;
주의할 점은 SELECT문의 열 개수는 INSERT할 테이블의 열 개수와 같아야 한다.
즉, SELECT의 열이 3개라면 INSERT될 테이블의 열도 3개여야 한다.
먼저 MySQL에 설칠할 때 함께 생성된 WORLD 데이터베이스의 ciry 테이블의 개수를 조회해보자
COUNT(*)을 사용하면 된다.
SELECT COUNT(*) FROM world.city;
이번에는 world.city의 테이블의 구조를 살펴보자
DES 명령으로 테이블 구조를 확인할 수 있다. DESC는 Describe의 약자로 테이블의 구조를 출력해주는 기능을 한다.
즉 CREATE TABLE을 어떻게 했는지 예상할 수 있다.
아래 SQL은 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 테이블에 입력하는 SQL은 다음과 같다.
INSERT INTO city_popul SELECT Name, Population FROM world.city;
4. UPDATE 문의 기본 문법
UPDATE: 수정
기본적인 형식은 다음과 같다
UPDATE 테이블_이름
SET 열1 = 값1, 열2=값2,...
WHERE 조건
MySQL 워크밴치에서는 기본적으로 UPDATE및 DELETE를 허용하지 않는다 그러니 설정을 변경해야 한다.
[Edit] - [Preferences] 메뉴를 실행하고 Workbench Preferences 창의 [SQL Editor]에서 'Safe Updates (rejects UPDATEs and DELETEs with no restrictions)'를 체크 해제하면 된다.
위에서 생성한 city_popul 테이블의 도시 이름 중에서 Seoul 을 서울로 변경해보자.
SQL은 다음과 같다.
USE market_db;
UPDATE city_popul
SET city_name = '서울'
WHERE city_name = 'Seoul';
SELECT * FROM city_popul WHERE city_name = '서울';
여러 열의 값을 한번에 변경할 수도 있다.
콤마(,)로 분리해서 여러 개의 열을 변경 하면 된다.
다음 SQL은 도시 이름인 'New York'을 뉴옥으로 바꾸면서 동시에 인구는 0으로 설정하는 내용이다.
UPDATE city_popul
SET city_name = '뉴옥', population = 0
WHERE city_name = 'New York';
SELECT * FROM city_popul WHERE city_name = '뉴옥';
UPDATE city_popul
SET city_name = '서울';
만약 위 SQL을 실행하면 모든 도시 이름이 서울로 바뀌게 될 것이다.
WHERE 절이 없기 때문에 모든 값을 서울로 바꿔버린다.
그렇다면 전체 테이블은 어떤 경우에 수정할까? city_popul 테이블의 인구열은 1명 단위로 데이터가 저장되어 있다.
이 것을 10,000명 단위로 변경하면 좀 더 읽기 편할 것이다.
아래 SQL을 실행하면 한꺼번에 10,000으로 인구 데이터를 나눌 수 있다.
5. DELETE 문의 기본 문법
DELETE: 삭제
형식:
DELETE FROM 테이블이름 WHERE 조건;
New가 들어가는 도시 데이터를 삭제하는 SQL은 다음과 같다.
DELETE FROM city_popul WHERE city_name LIKE 'New%';
*UPDATE와 마찬가지로 WHERE 절이 생략되면 전체 행 데이터를 삭제하므로 주의해야 한다.
만약 New 글자로 시작하는 11건의 도시를 지우는 것이 아니라 New 글자로 시작하는 도시 중 상위 몇 건만 삭제하려면 LIMIT 구문과 함께 사용해야 한다.
다음과 같이 실행하면 'New' 글자로 시작하는 도시 중 상위 5건만 삭제된다.
DELETE FROM city_popul
WHERE city_name LIKE 'New%'
LIMIT 5;
*여러 종류의 삭제
DELETE 삭제가 오래 거린다. WHERE 사용 가능
DROP 테이블 자체를 삭제한다 시간이 빠름.
TRUNCATE DELETE와 동일한 효과를 내지만 속도가 무척 빠름. WHERE 사용 부가능 그러므로 조건 없이 전체 행을 삭제할 때만 사용함.
DELETE FROM 테이블 이름;
DROP TABLE 테이블 이름;
TRUNCCATE TABLE 테이블 이름;
정리) 대용량 테이블의 전첸 ㅐ용을 삭제할 때 테이블 자체가 필요 없으면 DROP, 테이블의 구조는 남겨놓고 싶다면 TRUNCATE로 삭제하는 것이 효율적이다.
정리)
1. INSERT 문은 테이블에 데이터를 입력하는 명령이다.
2. AUTO_INCREMENT는 1부터 증가하는 값을 자동으로 입력해준다. 해당 열은 RPIMARY KEY로 지정해야 한다.
3. INSERT INTO ~ SELECT는 다른 테이블의 데이터를 가져와서 한 번에 대량으로 입력한다.
4. UPDATE는 기존에 입력되어 있는 값을 수정하여 주로 WHERㄷ와 함께 사용한다.
5. DELTE는 행 단위로 삭제하면 WHERE가 없으면 전체 행이 삭제된다.
NULL: 아무 것도 없는 값, AUTO_INCREMENT 영레 값을 입력할 때는 NULL로 지정함.
PRIMARY KEY: 기본 키, AUTO_INCREMENT 열은 기본 키로 지정해야 한다.
ALTER TABLE : 테이블의 구조를 변형하는 SQL
시스템 변수: MySQL에서 자체적으로 가지고 있는 설정값이 저장된 변수
@@auto_increment_increment: AUTO_INCREMENT의 증가값을 지정하는 시스템 변수
DESCRIBE(DESC): 테이블의 구조를 확인하는 SQL
TRUNCATE: DELETE와 비슷한 기능이지만 전체 행을 삭제할 때 사용