MySQL 서브 쿼리와 트랜잭션

1. 서브 쿼리란?

· 쿼리문 안에 포함되는 다른 쿼리문
· 서브 쿼리는 세미콜론 생략이 가능하며, 메인 쿼리 내부에 ()를 사용하여 작성
· 데이터를 수정/삭제 시 두 번의 쿼리를 작성해야 하지만, 서브 쿼리 이용 시 한 번에 처리 가능

 

※ Quiz

Q1. song table에서 가사 중에 Give가 포함된 girl_group의 모든 자료를 출력하시오
리턴 값이 1개인 경우 아래와 같이 사용해도 됨
A. select * from girl_group
    where hit_song_id = (
        select s_id from song 
        where lyrics like '%Give%'
    );

 

Q2. song table에서 가사 중에 me가 포함된 girl_group의 모든 자료를 출력하시오

※ 서브쿼리의 조회 자료가 2개 이상인 경우 비교연산자 사용 시 오류 발생하므로 in 사용
A. select * from girl_group
     where hit_song_id in (
         select s_id from song 
         where lyrics like '%me%'
     );

 

Q3. song table에서 제목이 존재하고, 가사에 e가 포함되는 모든 자료를 출력하시오
A. select gg.name, gg.debut, 
      ( select title from song
        where title is not null 
        and lyrics like '%e%' and gg.hit_song_id = s_id
     ) as hitsong
     from girl_group as gg;

 

Q4. 가사에 e가 포함된 걸그룹을 삭제하시오
A. delete from girl_group
     where hit_song_id in (
       select s_id from song where lyrics like '%e%'
     );

 

Q5. 노래 제목에 '미스터'를 포함된 걸그룹의 enter_id를 4로 변경하시오
A. update girl_group 
     set enter_id=4 
        where hit_song_id = (
        select s_id from song where title like '%미스터%'
      );

 

Q6. JYP에 소속된 그룹 이름을 출력해주세요
A. select name, enter_id from girl_group 
      where enter_id in (
      select e_id from entertainment where name='JYP'
    );

 

Q7. 시크릿의 소속사를 2NE1의 소속사로 변경하시오.
A. update girl_group set enter_id = (
       select enter_id from girl_group
       where name='2NE1'
   )
   where name='시크릿';

→ You can't specify target table 'girl_group' for update in FROM clause 오류 발생
    데이터 추가, 갱신할 경우 oracle과 postgreSQL은 동일한 테이블로 서브 쿼리 사용 가능하지만 MySQL은 사용 불가!! 
    결론! from 구는 임시 테이블로 다룰 수 있으므로 아래와 같이 작성하면 실행 가능

 

최종A. update girl_group 
            set enter_id = (

                select enter_id from 
                   (select enter_id from girl_group where name='2NE1') as tt
                )
                where name='시크릿';

 

Q8. SM 소속의 그룹을 추출하여 새로운 테이블로 생성하시오 (테이블명: SM, 필드: 그룹명, 데뷔일, 노래제목) 

A. create table sm as 
    select gg.name, gg.debut, s.title
    from girl_group gg
    left join song s
     on gg.hit_song_id = s.s_id
     where enter_id = (
         select e_id from entertainment where name='sm'
     );

 

2. 트랜잭션

· 데이터베이스의 상태를 바꾸는 일종의 작업 단위 (transaction~rollback / transaction~commit)
· MySQL은 auto commit 되므로 쿼리 입력과 동시에 처리되는 구조
 ※ 트랜잭션 특징
1) 원장성(Atomicity): 트랜잭션이 모두 반영되거나, 혹은 전혀 반영되지 않아야 함
2) 일관성(Consistency): 작업 처리 결과가 항상 일관성이 있어야 함
3) 독립성(Isolation): 트랜잭션이 동시에 실행되는 경우, 다른 트랜잭션의 연산에 끼어들 수 없음
4) 영구성(Durability): 성공적으로 완료된 경우, 결과는 영구적으로 반영되어야 함

 

 ※ 트랜잭션의 현 상태 확인 명령어

· show variables like '%commit%'; 

 

 ※ 트랜잭션 실습1_ROLLBACK

· select * from entertainment;
· start transaction; → 해당 시점부터 트랜잭션 시작
· insert into entertainment value(5, '스타쉽');
· select * from entertainment;
· rollback;
· select * from entertainment;

 

 ※ 트랜잭션 실습2_COMMIT

· start transaction;
· insert into entertainment values(5, 'FNC');
· select * from entertainment;
· commit;
· select * from entertainment;

 

 ※ 트랜잭션 실습3_ROLLBACK

· start transaction;
· delete from entertainment where e_id=3;
· update entertainment set name='소속사없음' where e_id=1;
· select * from entertainment;
· rollback;
· select * from entertainment;