[MySQL] Data Manipulation Language (INSERT, UPDATE, DELETE)

 

1. 데이터 조작어(Data Manipulation Language)

• SQL 구문을 기능에 따라 분류하면 아래와 같다.

구문	역할	예시
DDL (Data Definition Language)	테이블이나 컬럼 등의 구조 정의/수정	CREATE, ALTER, DROP
DML (Data Manipulation Language)	테이블 안의 데이터 추가/수정/삭제/조회	INSERT, UPDATE, DELETE, SELECT
DCL (Data Control Language)	사용자 권한 부여/회수	GRANT, REVOKE
TCL (Transaction Control Language)	트랜잭션을 확정/취소	COMMIT, ROLLBACK, SAVEPOINT

• 이 중에서 DML은 (테이블 구조는 그대로 두고) 테이블 안의 "데이터" 자체를 조작하는 언어로, 가장 기본적으로 많이 쓰는 SELECT도 여기에 포함된다.
• 다만 단순히 '조회'만 했던 SELECT와 달리, 테이블 안의 데이터를 직접적으로 조작하는 함수들을 알아보자!

2. INSERT, UPDATE, DELETE

INSERT INTO ... VALUES

• 테이블에 새로운 데이터(행)를 추가 (⚠️컬럼 순서와 VALUES 순서는 일치해야 함)

-- 기본 문법
INSERT INTO 테이블명 VALUES (값 리스트)
-- 예시
INSERT INTO salary VALUES ('1', 'A', '250', '2020-03-31')

• 특정 컬럼에 새로운 데이터(행)를 추가할 수 있음!
  • 지정하지 않은 컬럼은 기본적으로 NULL이 채워짐

-- 기본 문법
INSERT INTO 테이블명 (컬럼 리스트) VALUES (값 리스트)
-- 예시
INSERT INTO salary (id, salary) VALUES ('2', '550')

 

UPDATE ... SET

• 특정 컬럼의 데이터를 수정(업데이트)
  • ⚠️여기서 =은 대입 연산자로 사용됨 (비교연산자 아님)
  • 예시) salary 컬럼의 값들을 전부 100씩 더하고 싶어

-- 기본 문법
UPDATE 테이블명
SET 컬럼명 = 수정된 값
-- 예시
UPDATE Salary
SET salary = salary+100

• WHERE 사용해서 특정 행의 값만 수정할 수 있음!

-- 기본 문법
UPDATE 테이블명
SET 원래 컬럼 = 수정된 값
WHERE 조건식

-- 예시1
UPDATE Salary
SET salary = salary+100
WHERE id=2
-- 예시2 (여러 개의 컬럼을 같이 수정할 수도 있음)
UPDATE Salary
SET id=NULL, name=NULL, sex=NULL, salary=NULL
WHERE id=2

• ⭐UPDATE ... SET column = CASE WHEN 방식으로, 조건별로 수정하는 경우가 많음!
  • 아래 연습문제 참고

DELETE ... FROM

• 테이블의 전체 데이터를 삭제

-- 기본 문법
DELETE FROM 테이블명
-- 예시
DELETE FROM salary

• WHERE 사용해서 특정 행만 삭제할 수 있음!

-- 기본 문법
DELETE FROM 테이블명
WHERE 조건식
-- 예시
DELETE FROM salary
WHERE id=2

• JOIN과 연계하면 여러 테이블의 데이터 한 번에 삭제 가능! (DELETE 2번 실행할 필요 없음)

-- 따로 삭제하면 이렇게 해야 하는데,
DELETE t1
FROM t1 WHERE t1.id=1;
DELETE t2
FROM t2 WHERE t2.ref=1;

-- JOIN으로 연결하면 한 번에 삭제 가능!
DELETE t1, t2
FROM t1
INNER JOIN t2 ON t1.id = t2.ref
WHERE t1.id = 1 -- t1 조건만 걸어도 t2까지 삭제됨!

-- 👨🏻‍🏫if) 연결했는데 t2의 행만 지우고 싶을 경우, 아래처럼 쓰면 됨!
DELETE t2
FROM t1
INNER JOIN t2 ON t1.id = t2.ref
WHERE t1.id = 1

3. 연습 문제

리트코드 672. Swap salary

Write a solution to swap all 'f' and 'm' values with a single update statement and no intermediate temporary tables. (Do not write any select statement)

-- 풀이 (1)
UPDATE salary
SET sex = CASE
            WHEN sex='m' THEN 'f'
            WHEN sex='f' THEN 'm' -- 그냥 else로 써도 됨
        END
        
-- 풀이 (2) 조건 2개뿐이니까 IF문 써도 됨
UPDATE salary
SET sex = IF(sex = 'm', 'f', 'm');

-- (참고) 이렇게 쿼리 두 번 실행해도 되지만, 비효율적이라서 위처럼 조건문 쓰는 것!
UPDATE salary SET sex = 'f' WHERE sex = 'm';
UPDATE salary SET sex = 'm' WHERE sex = 'f';

 

리트코드 196. Delete Duplicate Emails

• Write a solution to delete all duplicate emails, keeping only one unique email with the smallest id. (Please note that you are supposed to write a DELETE statement and not a SELECT one.)

📌풀이1: 서브쿼리 + 임시테이블 활용

-- 1) 일단 각 이메일의 min(Id)를 출력해보기 (우리가 킵해야하는 데이터의 id)
SELECT
	email,
    MIN(id) AS min_id
FROM person
GROUP BY email;

-- 2) 여기서 id만 필요하니까 서브쿼리로 감싸기 + 임시테이블로 지정
SELECT sub.min_id
FROM(
	SELECT
    	email,
        MIN(id) AS min_id
	FROM person
	GROUP BY email
	) AS sub;

-- 3) 그리고 이걸 DELETE구문의 WHERE 절에 넣어서 삭제 조건을 완성
DELETE FROM person
WHERE id NOT IN(
                SELECT sub.min_id
                FROM(
                    SELECT
                        email,
                        MIN(id) AS min_id
                    FROM person
                    GROUP BY email
                    ) AS sub
                );

-- ⚠️참고로, MySQL에서는 이렇게 바로 서브쿼리 쓰는 건 안 됨⚠️
DELETE FROM person
WHERE id NOT IN(
                SELECT MIN(id)
                FROM person
                GROUP BY email
                )

📌풀이2: JOIN 활용

• 직접 셀프조인 결과를 아래와 같이 그려보면, where 조건을 설정할 수 있음
• (배운 점) DELETE FROM ... JOIN ...에서 JOIN은 삭제 대상 행을 찾는 용도로만 사용됨!!
  • ⭐결국 삭제 대상은 DELETE 바로 뒤에 붙은 테이블!! (=p1)
  • = 조인 결과에서 삭제가 수행되는 게 아니라, DELETE 바로 뒤에 적은 p1에서 삭제를 하는 것이고, JOIN은 그저 특정 행을 필터링하기 위한 과정으로만 사용된 것!! (위 경우, 단순히 p1.id > p2.id를 만족하는 p1 행을 찾아내기 위한 용도로만 사용된 것 = p1에서 `id=3`인 행이 필터링된 것)
  • = DELETE는 출력하는 게 아니라 해당 행을 실제 테이블에서 삭제하는 연산이기 때문!

DELETE p1
FROM person AS p1
INNER JOIN person AS p2
ON p1.email = p2.email
WHERE p1.id > p2.id -- 그냥 중복된 id를 뽑아내는 것에 집중한 풀이인 듯

/* 직접 조인 결과를 아래와 같이 그려봐야 where 조건을 알아낼 수 있음

p1.Id.    p1.Email.             p2.Id.    p2.Email  
1         john@example.com      1         john@example.com   (X)  
1         john@example.com      3.        john@example.com   (O)
2         bob@example.com       2.        bob@example.com    (O)
3.        john@example.com      1         john@example.com   (X)
3         john@example.com      3.        john@example.com   (X)

⚠️위의 조인 결과 테이블에서 where 조건에 맞는 건 4행뿐이니까
결과로 1,2,3,5행이 나와야할 것 같지만, 실제로는 2,3행만 나옴!!
*/