[Mysql] DML - INSERT, UPDATE, DELETE

(해당 포스트는 Inflearn - [백문이불여일타] 데이터 분석을 위한 고급 SQL 강의록입니다.)

DML (Data Manipulation Language)

Insert

insert into '테이블명' values (value_list);
이 경우 컬럼 순서대로 입력 (모든 컬럼에 들어감)

insert into '테이블명' (특정 컬럼 리스트') values (values_list);
특정 컬럼에만 데이터 추가하기
insert가 지정되지 않은 컬럼들에는 null이 들어감 (Not null일 경우 불가)

Update

update '테이블명' set 컬럼 = 값;
이 경우 내가 지정한 컬럼의 모든 값에 적용됨
'update salary set Salary = Salary+100;' : 모든 월급의 100이 올라서 저장됨 (좋겠다..ㅎㅎ)

update '테이블명' set 컬럼 where 조건식
특정 컬럼 내 특정 행만 바꿔줄 때 사용

Delete

delete from '테이블명';
이 경우 전체 테이블의 데이터 삭제

delete vs. truncate (vs. drop)
먼저 delete는 DML(데이터 조작 언어), truncate, drop은 DDL(데이터 정의 언어)이라는 차이가 있다.
drop의 경우 테이블 자체의 흔적을 지워버리기 때문에(때문에 정말 위험하다) 테이블의 구조를 남기는 truncate와의 차이가 명확하지만, delete문은 사용 방법에 따라 사실상 truncate문과 동일하게 사용할 수도 있다.
delete문과 truncate문 둘의 차이는 무엇일까?

1. 로그 공간

• delete문: 행을 한번에 하나씩 제거 --> 삭제된 각 행에 대해 트랜잭션 로그를 기록
• truncate문: 테이블의 데이터를 저장하는데 사용되는 데이터 페이지의 할당을 취소해버리는 방식으로 데이터를 제거 --> 페이지 할당 취소만을 트랜잭션 로그에 기록

2. 속도, 삭제 행수 반환 여부

• delete문: 한번에 하나씩 제거하는 특성으로 인해 속도가 느리고, 삭제 행수를 반환
• truncate문: 속도가 빠르고, 삭제 행수를 반환하지 않음

3. 완전 삭제

• delete문: 데이터만 삭제되며, 테이블 용량이 줄어들지 않음
• truncate문: 테이블 용량이 줄어들며 인덱스 등도 모두 삭제

4. 롤백 여부

• delete문: 잘못 삭제한 것을 되돌릴 수 있음 (commit 이전에는 rollback이 가능)
• truncate문: 불가능

5. 전체 삭제 vs. 일부 삭제

• delete문: 조건을 걸어 일부 또는 전체 삭제가 가능
• truncate문: 무조건 전체 삭제만 가능

delete from '테이블명' where 조건식;
조건에 해당하는 행만 테이블에서 삭제

실전 문제 1

LeetCode 627. Swap Salary

요약하자면 주어진 연봉테이블에서 여성 남성의 성별을 바꾸어보라는 이야기

정답 쿼리

update salary 
set sex = case 
 	 	when sex='m' then 'f'
     		when sex='f' then 'm'
	  end;

update문은 일하면서 많이 쓰는 구문중 하나라 자신이 있었는데, 실제 update문에 case문을 사용하는 것이 쉽지는 않았다. 구글링으로 알아내어 성공적으로 submit!

실전 문제 2

LeetCode 196. Delete Duplicate Emails

요약하자면 중복되는 이메일을 제거하라는 것. 단, 이 때 상대적으로 낮은 id의 이메일을 남기고 상대적으로 높은 id의 이메일을 지우는 형식이어야 함.

정답 쿼리를 위한 과정

서브쿼리를 이용해야한다는 것을 알고 있었음에도 불구하고 접근하기 쉽지 않았다. 아무래도 id값이 작은 것만 남겨야한다는 조건에 대한 쿼리 작성이 어려웠다.
서브쿼리를 살짝만 맛보면 아래와 같다.

where절 서브쿼리

select *
from crimes
where date = (select min(date)
		from crimes);

crimes 테이블에서 최초 범죄 발생일의 모든 정보를 보는 쿼리는 위와 같다. 그런데 이렇게만 쿼리를 작성할 경우, 오로지 최초 범죄 발생일 단 하루 동안에만 국한된 정보밖에 볼 수 없다는 단점이 있다. 만일 최초 발생일 하루가 아니라 그로부터 5일 간의 범죄를 보고 싶은 경우 어려워진다.

select * 
from crimes
where date in (select date
		from crimes
        	order by date
            	desc limit 5);

위의 쿼리처럼 where절에 서브쿼리와 in을 적절히 사용하여 작성하게 되면 최초 발생일로부터 5일동안 발생한 범죄를 모두 볼 수 있다. 이 때는 order by절에 desc를 사용했으므로 최근 일어난 일이 가장 위로 올라가게 된다.

정답 쿼리 (서브쿼리 활용)

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)

부족했던 점

• in이 아니라 not in을 사용하는 생각
• 서브쿼리 안의 서브쿼리 사용 (where절 서브쿼리 안에 from절 서브쿼리)

정답 쿼리 2 (self-join 활용)

사실 이 문제를 풀기 위해 가장 먼저 생각했던 것은 self join이었다. 하지만 생각처럼 쿼리가 작성되지 않았다.

우선 self join을 이용한 select문을 작성한다고 하면,

select *
from Person as p1
inner join Person as p2 
on p1.Email = p2.Email

이 쿼리로 인한 결과는 아래처럼 나올 것이다.

p1.Id     p1.Email          p2.Id     p2.Email
1         john@example.com  1         john@example.com
1	  john@example.com  3         john@example.com
2         bob@example.com   2         bob@example.com
3         john@example.com  1         john@example.com
3         john@example.com  3         john@example.com

그럼 이제 keep할 것, 지워야할 것을 따져서 delete문 쿼리를 짤 수 있다. 또한 이 때에는 기존의 delete from으로 delete문이 시작되지 않는다는 점이 특이했다. delete 뒤에 지우고 싶은 테이블을 직접 적는 것이다. 이런 방식으로도 delete문 작성이 가능하다는 점은 꼭 알아두어야겠다. (🍎)

delete p1 # p2로 쓰면 안됨!
from Person as p1
inner join Person as p2
on p1.Email = p2.Email
where p1.Id > p2.Id

references:
https://pointnet.tistory.com/334
https://goddaehee.tistory.com/55