[SQL][DB] DML 살펴보기
여태까지는 데이터를 조회하는 SELECT 문에 대해서 살펴봤는데, 이번에는 데이터 삽입, 수정, 삭제와 같은 데이터 변경 작업을 위한 쿼리에 대해서도 살펴보자.
INSERT : 새 데이터 삽입
INSERT는 테이블에 새 데이터를 삽입할 때 쓰이는 키워드이다. 구조는 다음과 같다.
-- #1. 기본 구조
INSERT INTO 테이블명 [(필드1, 필드2, ...)] VALUES (값1, 값2, ...);
-- #2. 모든 필드에 하나도 생략하지 않고 데이터를 삽입할 경우,
-- 필드명은 생략할 수 있다.
INSERT INTO 테이블명 VALUES (값1, 값2, ...);
이전에 보았던 site_user.sql을 다시 보면 어떻게 사용하는지 감이 잡힐 것이다.
-- SQL문에서는 '--' 대시 두 문자를 통해 주석을 작성할 수 있다.
-- "sql_practice"라는 데이터베이스가 이미 존재한다면
-- 해당 DB를 삭제한다. 그 후, 똑같은 이름의 새 데이터베이스를 생성한다.
-- 이렇게 하는 이유는, 실습 도중 데이터가 의도치 않게 변형되어
-- 초기화하여 처음부터 다시 사용하고자 하기 위함이다.
-- "sql_practice"라는 이름의 데이터베이스 생성 시도.
DROP DATABASE IF EXISTS sql_practice;
CREATE DATABASE sql_practice;
-- 사용할 데이터베이스 선택.
-- 사용할 데이터베이스를 선택해야 그 안에 테이블 및
-- 새 데이터를 삽입할 수 있다.
-- 여기서는 위에서 생성한 데이터베이스를 선택.
USE sql_practice;
create table site_users (
member_id INT PRIMARY KEY, -- member_id 필드에 기본 키 설정.
sign_up_date DATE,
class_number INT,
mileage INT,
username VARCHAR(50),
aver_purchase INT
);
insert into site_users (member_id, sign_up_date, class_number, mileage, username, aver_purchase) values (1, '2023-10-23', 1, 1549, 'pcopozio0', 616);
insert into site_users (member_id, sign_up_date, class_number, mileage, username, aver_purchase) values (2, '2024-01-09', 4, 2712, 'eszanto1', null);
insert into site_users (member_id, sign_up_date, class_number, mileage, username, aver_purchase) values (3, '2023-08-15', 5, 1129, 'jmatuszkiewicz2', 855);
insert into site_users (member_id, sign_up_date, class_number, mileage, username, aver_purchase) values (4, '2024-06-04', 2, 4238, 'egudgion3', null);
insert into site_users (member_id, sign_up_date, class_number, mileage, username, aver_purchase) values (5, '2023-12-06', 3, 4632, 'gfittes4', 592);
insert into site_users (member_id, sign_up_date, class_number, mileage, username, aver_purchase) values (6, '2023-09-23', 4, 2235, 'dbuttrum5', null);
insert into site_users (member_id, sign_up_date, class_number, mileage, username, aver_purchase) values (7, '2024-07-21', 5, 1891, 'lfree6', 547);
insert into site_users (member_id, sign_up_date, class_number, mileage, username, aver_purchase) values (8, '2023-07-03', 3, 1396, 'bleggis7', null);
insert into site_users (member_id, sign_up_date, class_number, mileage, username, aver_purchase) values (9, '2024-05-04', 2, 4058, 'kmardle8', null);
insert into site_users (member_id, sign_up_date, class_number, mileage, username, aver_purchase) values (10, '2023-05-06', 5, 4042, 'dsummerside9', 249);
insert into site_users (member_id, sign_up_date, class_number, mileage, username, aver_purchase) values (11, '2023-01-08', 2, 1282, 'fwillimonta', null);
insert into site_users (member_id, sign_up_date, class_number, mileage, username, aver_purchase) values (12, '2024-02-21', 1, 1546, 'atailourb', null);
insert into site_users (member_id, sign_up_date, class_number, mileage, username, aver_purchase) values (13, '2024-07-09', 2, 3492, 'dmulgrewc', 807);
insert into site_users (member_id, sign_up_date, class_number, mileage, username, aver_purchase) values (14, '2024-06-14', 4, 4908, 'ksimeolid', null);
insert into site_users (member_id, sign_up_date, class_number, mileage, username, aver_purchase) values (15, '2024-05-16', 1, 4640, 'lscaddinge', 656);
insert into site_users (member_id, sign_up_date, class_number, mileage, username, aver_purchase) values (16, '2023-10-10', 5, 3486, 'srenonf', 322);
insert into site_users (member_id, sign_up_date, class_number, mileage, username, aver_purchase) values (17, '2024-03-27', 4, 2954, 'aledesg', 850);
insert into site_users (member_id, sign_up_date, class_number, mileage, username, aver_purchase) values (18, '2023-01-06', 1, 2632, 'hshickleh', null);
insert into site_users (member_id, sign_up_date, class_number, mileage, username, aver_purchase) values (19, '2022-09-11', 5, 3712, 'lglandersi', null);
insert into site_users (member_id, sign_up_date, class_number, mileage, username, aver_purchase) values (20, '2024-03-13', 1, 2596, 'dionsj', 761);
site_users.sql
위 쿼리문의 VALUES 뒤에 null값이 하나도 안 들어간다면 다음과 같이 축약할 수 있다.
insert into site_users values (1, '2023-10-23', 1, 1549, 'pcopozio0', 616);
예제 1-1
또한, 만약 위와 같이 삽입할 데이터가 많다면, 맨 첫 INSERT문에서만 VALUES를 사용하고, 그 뒤부터는 괄호로 묶인 데이터들을 쉼표를 구분자로 하여 나열하면 된다. 즉, 중복되는 VALUES를 제거하여 사용해도 된다. 다음과 같이 말이다.
insert into site_users (member_id, sign_up_date, class_number, mileage, username, aver_purchase) values (1, '2023-10-23', 1, 1549, 'pcopozio0', 616),
(2, '2024-01-09', 4, 2712, 'eszanto1', null),
(3, '2023-08-15', 5, 1129, 'jmatuszkiewicz2', 855),
(4, '2024-06-04', 2, 4238, 'egudgion3', null),
(5, '2023-12-06', 3, 4632, 'gfittes4', 592),
(6, '2023-09-23', 4, 2235, 'dbuttrum5', null),
(7, '2024-07-21', 5, 1891, 'lfree6', 547),
(8, '2023-07-03', 3, 1396, 'bleggis7', null),
(9, '2024-05-04', 2, 4058, 'kmardle8', null),
(10, '2023-05-06', 5, 4042, 'dsummerside9', 249),
(11, '2023-01-08', 2, 1282, 'fwillimonta', null),
(12, '2024-02-21', 1, 1546, 'atailourb', null),
(13, '2024-07-09', 2, 3492, 'dmulgrewc', 807),
(14, '2024-06-14', 4, 4908, 'ksimeolid', null),
(15, '2024-05-16', 1, 4640, 'lscaddinge', 656),
(16, '2023-10-10', 5, 3486, 'srenonf', 322),
(17, '2024-03-27', 4, 2954, 'aledesg', 850),
(18, '2023-01-06', 1, 2632, 'hshickleh', null),
(19, '2022-09-11', 5, 3712, 'lglandersi', null),
(20, '2024-03-13', 1, 2596, 'dionsj', 761);
예제 1-2
위 코드로 바꿔서 실행해도 이전의 것과 똑같이 실행된다.
INSERT INTO ~ SELECT : 다른 테이블의 데이터를 한꺼번에 가져와 저장하기
INSERT와 SELECT문을 결합하면 기존에 있는 테이블에서 데이터를 가져와 다른 곳에 복사하여 붙여넣을 수 있다. 문법 구조는 다음과 같다.
-- #1
INSERT INTO 테이블명1 [(필드명1, 필드명2, ...)]
SELECT 필드명1, 필드명2, ... FROM 테이블명2;
-- #2
-- 전체 데이터를 가져와 삽입한다.
-- 백업할 때 사용하면 좋다.
INSERT INTO 테이블명1 SELECT * FROM 테이블명2
이 때, 삽입할 데이터의 필드의 개수와 SELECT로부터 가져오는 필드의 개수가 같아야한다. 또는 #2처럼 모든 데이터를 가져올 경우 INSERT INTO 뒤에 올 필드명들을 생략할 수 있다.
다음은 INSERT INTO ~ SELECT문을 이용하여 기존 테이블에 있는 모든 데이터들을 다른 테이블을 만들어 거기에 백업하는 쿼리문이다.
USE sql_practice;
CREATE TABLE IF NOT EXISTS site_users_backup (
member_id INT PRIMARY KEY, -- member_id 필드에 기본 키 설정.
sign_up_date DATE,
class_number INT,
mileage INT,
username VARCHAR(50),
aver_purchase INT
);
INSERT INTO site_users_backup SELECT * FROM site_users;
site_users_backup.sql
위 sql 파일을 콘솔창에서 mariaDB 내에서 실행시켜보았다.
MariaDB [sql_practice]> show tables;
+------------------------+
| Tables_in_sql_practice |
+------------------------+
| site_users |
+------------------------+
1 row in set (0.001 sec)
MariaDB [sql_practice]> source C:\sql_practice\site_users_backup.sql
Database changed
Query OK, 0 rows affected (0.010 sec)
Query OK, 20 rows affected (0.003 sec)
Records: 20 Duplicates: 0 Warnings: 0
MariaDB [sql_practice]> show tables;
+------------------------+
| Tables_in_sql_practice |
+------------------------+
| site_users |
| site_users_backup |
+------------------------+
2 rows in set (0.001 sec)
MariaDB [sql_practice]> SELECT * FROM site_users_backup;
+-----------+--------------+--------------+---------+-----------------+---------------+
| member_id | sign_up_date | class_number | mileage | username | aver_purchase |
+-----------+--------------+--------------+---------+-----------------+---------------+
| 1 | 2023-10-23 | 1 | 1549 | pcopozio0 | 616 |
| 2 | 2024-01-09 | 4 | 2712 | eszanto1 | NULL |
| 3 | 2023-08-15 | 5 | 1129 | jmatuszkiewicz2 | 855 |
| 4 | 2024-06-04 | 2 | 4238 | egudgion3 | NULL |
| 5 | 2023-12-06 | 3 | 4632 | gfittes4 | 592 |
| 6 | 2023-09-23 | 4 | 2235 | dbuttrum5 | NULL |
| 7 | 2024-07-21 | 5 | 1891 | lfree6 | 547 |
| 8 | 2023-07-03 | 3 | 1396 | bleggis7 | NULL |
| 9 | 2024-05-04 | 2 | 4058 | kmardle8 | NULL |
| 10 | 2023-05-06 | 5 | 4042 | dsummerside9 | 249 |
| 11 | 2023-01-08 | 2 | 1282 | fwillimonta | NULL |
| 12 | 2024-02-21 | 1 | 1546 | atailourb | NULL |
| 13 | 2024-07-09 | 2 | 3492 | dmulgrewc | 807 |
| 14 | 2024-06-14 | 4 | 4908 | ksimeolid | NULL |
| 15 | 2024-05-16 | 1 | 4640 | lscaddinge | 656 |
| 16 | 2023-10-10 | 5 | 3486 | srenonf | 322 |
| 17 | 2024-03-27 | 4 | 2954 | aledesg | 850 |
| 18 | 2023-01-06 | 1 | 2632 | hshickleh | NULL |
| 19 | 2022-09-11 | 5 | 3712 | lglandersi | NULL |
| 20 | 2024-03-13 | 1 | 2596 | dionsj | 761 |
+-----------+--------------+--------------+---------+-----------------+---------------+
새 테이블에 기존 테이블에 있던 모든 데이터가 복제된 것을 확인할 수 있다. 이렇게 백업 용도로 사용할 수 있다.
UPDATE : 기존 데이터 수정하기
UPDATE 키워드는 기존의 데이터를 수정하는 키워드이다. 구조는 다음과 같다.
UPDATE 테이블명
SET 필드1=값1, 필드2=값2, ...
WHERE 조건문;
UPDATE 문법 구조
UPDATE 키워드 뒤에 업데이트가 진행될 테이블명을 적는다. 그 뒤로 SET 키워드 뒤에 새로 넣을 값들을 필드명과 함께 적는다. 여러 개일 경우 쉼표(,)를 기준으로 나열한다.
여기서 주의해야할 점이 있다. SET에서 끝나면 해당 테이블 내 모든 데이터가 SET 뒤로 명시한 새 값으로 변경된다. 만약 테이블 내 모든 데이터의 수정을 원한 게 아니라면 반드시 그 뒤에 WHERE 조건문을 명시함으로써 바꾸고자 하는 데이터만 선택하여 바꿔야 한다. 그래서 사실 실제로는 WHERE 절과 함께 쓰일 때가 많을 것이다.
다음은 평균구매액의 데이터가 없는 특정 회원의 평균 구매액 데이터를 넣는 예제이다.
UPDATE site_users
SET aver_purchase = 100
WHERE member_id = 19;
예제 2-1
MariaDB [sql_practice]> SELECT * FROM site_users;
+-----------+--------------+--------------+---------+-----------------+---------------+
| member_id | sign_up_date | class_number | mileage | username | aver_purchase |
+-----------+--------------+--------------+---------+-----------------+---------------+
| 1 | 2023-10-23 | 1 | 1549 | pcopozio0 | 616 |
| 2 | 2024-01-09 | 4 | 2712 | eszanto1 | NULL |
| 3 | 2023-08-15 | 5 | 1129 | jmatuszkiewicz2 | 855 |
| 4 | 2024-06-04 | 2 | 4238 | egudgion3 | NULL |
| 5 | 2023-12-06 | 3 | 4632 | gfittes4 | 592 |
| 6 | 2023-09-23 | 4 | 2235 | dbuttrum5 | NULL |
| 7 | 2024-07-21 | 5 | 1891 | lfree6 | 547 |
| 8 | 2023-07-03 | 3 | 1396 | bleggis7 | NULL |
| 9 | 2024-05-04 | 2 | 4058 | kmardle8 | NULL |
| 10 | 2023-05-06 | 5 | 4042 | dsummerside9 | 249 |
| 11 | 2023-01-08 | 2 | 1282 | fwillimonta | NULL |
| 12 | 2024-02-21 | 1 | 1546 | atailourb | NULL |
| 13 | 2024-07-09 | 2 | 3492 | dmulgrewc | 807 |
| 14 | 2024-06-14 | 4 | 4908 | ksimeolid | NULL |
| 15 | 2024-05-16 | 1 | 4640 | lscaddinge | 656 |
| 16 | 2023-10-10 | 5 | 3486 | srenonf | 322 |
| 17 | 2024-03-27 | 4 | 2954 | aledesg | 850 |
| 18 | 2023-01-06 | 1 | 2632 | hshickleh | NULL |
| 19 | 2022-09-11 | 5 | 3712 | lglandersi | 100 |
| 20 | 2024-03-13 | 1 | 2596 | dionsj | 761 |
+-----------+--------------+--------------+---------+-----------------+---------------+
예제 2-1 실행결과
원래 member_id = 19인 사람의 aver_purchase 필드에는 NULL값이 있었다. 그런데 UPDATE 쿼리문 실행 이후 값이 100으로 바뀐 것을 확인할 수 있다.
DELETE : 데이터 삭제
테이블 내 데이터를 삭제할 수도 있다. 이는 DELETE문을 통해 실행할 수 있다. 구조는 다음과 같다.
DELETE FROM 테이블명 WHERE 조건;
UPDATE와 마찬가지로, DELETE에서도 주의해야할 게 있다. 만약 뒤에 WHERE 조건문을 추가하지 않는다면 테이블내 모든 데이터가 삭제된다. 이를 의도한 게 아니라면 꼭 WHERE 조건문을 추가해야한다. WHERE 조건문에는 삭제하고자 하는 특정 데이터의 조건을 입력하면 해당 데이터가 삭제된다.
다음은 site_users에 임의의 데이터를 삽입하고 이 데이터를 다시 삭제하는 쿼리문이다. 먼저 임의의 데이터를 다음과 같이 삽입하겠다.
INSERT INTO site_users
VALUES (21, NOW(), 6, 3230, 'gooday123', 1000),
(22, NOW(), 6, 2380, 'bowwow222', 2000);
예제 3-1
MariaDB [sql_practice]> SELECT * FROM site_users;
+-----------+--------------+--------------+---------+-----------------+---------------+
| member_id | sign_up_date | class_number | mileage | username | aver_purchase |
+-----------+--------------+--------------+---------+-----------------+---------------+
| 1 | 2023-10-23 | 1 | 1549 | pcopozio0 | 616 |
| 2 | 2024-01-09 | 4 | 2712 | eszanto1 | NULL |
| 3 | 2023-08-15 | 5 | 1129 | jmatuszkiewicz2 | 855 |
| 4 | 2024-06-04 | 2 | 4238 | egudgion3 | NULL |
| 5 | 2023-12-06 | 3 | 4632 | gfittes4 | 592 |
| 6 | 2023-09-23 | 4 | 2235 | dbuttrum5 | NULL |
| 7 | 2024-07-21 | 5 | 1891 | lfree6 | 547 |
| 8 | 2023-07-03 | 3 | 1396 | bleggis7 | NULL |
| 9 | 2024-05-04 | 2 | 4058 | kmardle8 | NULL |
| 10 | 2023-05-06 | 5 | 4042 | dsummerside9 | 249 |
| 11 | 2023-01-08 | 2 | 1282 | fwillimonta | NULL |
| 12 | 2024-02-21 | 1 | 1546 | atailourb | NULL |
| 13 | 2024-07-09 | 2 | 3492 | dmulgrewc | 807 |
| 14 | 2024-06-14 | 4 | 4908 | ksimeolid | NULL |
| 15 | 2024-05-16 | 1 | 4640 | lscaddinge | 656 |
| 16 | 2023-10-10 | 5 | 3486 | srenonf | 322 |
| 17 | 2024-03-27 | 4 | 2954 | aledesg | 850 |
| 18 | 2023-01-06 | 1 | 2632 | hshickleh | NULL |
| 19 | 2022-09-11 | 5 | 3712 | lglandersi | 100 |
| 20 | 2024-03-13 | 1 | 2596 | dionsj | 761 |
| 21 | 2024-08-07 | 6 | 3230 | gooday123 | 1000 |
| 22 | 2024-08-07 | 6 | 2380 | bowwow222 | 2000 |
+-----------+--------------+--------------+---------+-----------------+---------------+
22 rows in set (0.000 sec)
예제 3-1 실행결과 확인
이제 해당 데이터들을 삭제해보겠다. 해당 데이터들은 클래스 넘버가 모두 6이다. 이를 WHERE 조건문에 넣어 해당 데이터들만 삭제할 것이다.
DELETE FROM site_users WHERE class_number = 6;
예제 3-2
MariaDB [sql_practice]> SELECT * FROM site_users;
+-----------+--------------+--------------+---------+-----------------+---------------+
| member_id | sign_up_date | class_number | mileage | username | aver_purchase |
+-----------+--------------+--------------+---------+-----------------+---------------+
| 1 | 2023-10-23 | 1 | 1549 | pcopozio0 | 616 |
| 2 | 2024-01-09 | 4 | 2712 | eszanto1 | NULL |
| 3 | 2023-08-15 | 5 | 1129 | jmatuszkiewicz2 | 855 |
| 4 | 2024-06-04 | 2 | 4238 | egudgion3 | NULL |
| 5 | 2023-12-06 | 3 | 4632 | gfittes4 | 592 |
| 6 | 2023-09-23 | 4 | 2235 | dbuttrum5 | NULL |
| 7 | 2024-07-21 | 5 | 1891 | lfree6 | 547 |
| 8 | 2023-07-03 | 3 | 1396 | bleggis7 | NULL |
| 9 | 2024-05-04 | 2 | 4058 | kmardle8 | NULL |
| 10 | 2023-05-06 | 5 | 4042 | dsummerside9 | 249 |
| 11 | 2023-01-08 | 2 | 1282 | fwillimonta | NULL |
| 12 | 2024-02-21 | 1 | 1546 | atailourb | NULL |
| 13 | 2024-07-09 | 2 | 3492 | dmulgrewc | 807 |
| 14 | 2024-06-14 | 4 | 4908 | ksimeolid | NULL |
| 15 | 2024-05-16 | 1 | 4640 | lscaddinge | 656 |
| 16 | 2023-10-10 | 5 | 3486 | srenonf | 322 |
| 17 | 2024-03-27 | 4 | 2954 | aledesg | 850 |
| 18 | 2023-01-06 | 1 | 2632 | hshickleh | NULL |
| 19 | 2022-09-11 | 5 | 3712 | lglandersi | 100 |
| 20 | 2024-03-13 | 1 | 2596 | dionsj | 761 |
+-----------+--------------+--------------+---------+-----------------+---------------+
20 rows in set (0.000 sec)
예제 3-2 실행결과
앞서 삽입한 두 데이터만 삭제된 것을 확인할 수 있다.
References
[1] 에이콘아카데미(강남) 강의
[2] 우재남, “혼자 공부하는 SQL”, (한빛미디어, 2021)
This content is licensed under
CC BY-NC 4.0
댓글남기기