MySQL 제약 조건과 JOIN

1. 제약조건이란?

· 데이터의 무결성을 지키기 위해 입력받은 데이터에 제한된 조건을 두는 것!
· 제약조건의 종류_아래 제약 조건 설정 시
  1) NOT NULL: 해당 필드는 NULL 값을 저장할 수 없음
  2) UNIQUE: 해당 필드는 중복된 값을 저장할 수 없음
  3) PRIMARY KEY(PK): 데이터 각 행을 유일하게 식별해 주는 역할, NOT NULL과 UNIQUE 제약 조건의 특징을 가짐
  4) FOREIGN KEY(FK): 논리적 연관성이 있는 테이블을 연결해 주는 역할, 참조 무결성
  5) DEFAULT: 데이터 값을 전달하지 않으면, 자동으로 기본 값 저장

 

2. 제약조건 실습을 위한 테이블 생성 

· table name: girl_group

 

· table name: song

 

· table name: entertainment

 

· table name: employee

 

3. girl_group table 제약조건 추가

· entertainment table의 PK인 enter_id를 FK 설정

  alter table girl_group
  add ( 
   constraint fk_enter_id
   foreign key(enter_id)
   references entertainment (e_id) 
  );

 

· song table의 PK인 s_id를 FK 설정
  alter table girl_group
  add ( 
   constraint fk_hit_song_id
   foreign key(hit_song_id)
   references song (s_id)
  );

 

4. girl_group table 제약조건 삭제 

 ※ 완벽히 삭제하기 위해서는 두 번의 과정 필요
· FK enter_id 제약조건 삭제
 → alter table girl_group drop foreign key fk_enter_id;
 → alter table girl_group drop key fk_enter_id;

 

5. 외래키 옵션 변경: ON DELETE / ON UPDATE

1) CASCADE: 수정/삭제 시 참조하는 테이블의 데이터도 자동 변경
2) SET NULL: 수정/삭제 시 데이터 NULL로 변경
3) NO ACTION: 수정/삭제해도 데이터 변경 X
4) SET DEFAULT: 수정/삭제 시 데이터 기본값으로 변경
5) RESTRICT: 참조하는 테이블의 데이터를 우선 삭제해야 부모 테이블 변경 가능

 ※ 아래와 같이 사용: song 데이터 삭제 시, s_id를 참조하고 있는 자식 테이블의 자료도 삭제
alter table girl_group
add ( 
 constraint fk_hit_song_id
 foreign key(hit_song_id)
 references song (s_id) on delete cascade
);

 

6. JOIN이란?

· 두 개 이상의 테이블을 연결하여 데이터 추출
 ※ JOIN의 종류
1) INNER JOIN: 조건에서 동일한 값이 있는 자료만 반환 (교집합), inner 키워드는 생략 가능

· select * from girl_group inner join song where girl_group.hit_song_id = song.s_id; 
· select * from girl_group as gg inner join song as s where gg.hit_song_id = s.s_id; : 별칭을 사용하여 join 하는 방식

 

· 부가적인 조건이 필요한 경우, on을 사용하여 조인하고 where 절 구문 사용

   select gg.g_id, s.title from girl_group as gg inner join song as s on gg.hit_song_id = s.s_id where 조건...

 

2) LEFT OUTER JOIN: 첫 번째 테이블을 기준으로 두 번째 테이블을 JOIN, 두 번째 테이블 부분만 제외

 

 

 

 

· select gg.g_id, gg.name, s.title from girl_group as gg left join song as s on gg.hit_song_id = s.s_id;

· song table의 title이 null인 경우의 자료를 조회
  select gg.g_id, gg.name, s.title from girl_group as gg left join song as s on gg.hit_song_id = s.s_id where s.title is null;

 

3) RIGHT OUTER JOIN: 두 번째 테이블을 기준으로 첫 번째 테이블을 JOIN, 첫 번째 테이블 부분만 제외

 

· select gg.g_id, gg.name, s.title from girl_group as gg right join song as s on gg.hit_song_id = s.s_id;

 

4) SELF JOIN: 자신의 테이블을 참조

· select em1.id, em1.name, em2.name as '상사' from employee as em1 inner join

  employee as em2 on em1.superior_id = em2.id;

 

 ※ Quiz

Q1. 걸그룹명, 노래 제목, 가사, 데뷔일자를 출력하고, 데뷔년도가 2011년 자료만 조회하시오
A. select gg.name, s.title, s.lyrics, gg.debut from girl_group as gg join song as s on gg.hit_song_id = s.s_id
    where gg.debut like '2011%';

A. select gg.name, s.title, s.lyrics, gg.debut from girl_group as gg join song as s on gg.hit_song_id = s.s_id
     where year(gg.debut) = 2011;

 

Q2. 가사 중에 'me'를 포함하는 자료의 id, 걸그룹명, 가사를 출력하시오
A. select gg.g_id, gg.name, s.lyrics from girl_group as gg join song as s on gg.hit_song_id = s.s_id
    where s.lyrics like '%me%';

 

Q3. 데뷔해를 기준으로 데뷔한 걸그룹 수를 출력하시오 (단, 데뷔해를 기준으로 오름차순 정렬하고 year 함수 사용할 것)
A. select count(g_id), year(debut) as debut_year from girl_group
    group by debut_year 
    order by debut_year asc;

 

Q4. 걸그룹 id, 걸그룹명, 소속사명을 출력하시오
A. select gg.g_id, gg.name, e.name from girl_group as gg join entertainment as e on gg.enter_id = e.e_id;

 

Q5. 소속사명, 걸그룹명, 데뷔일자, 노래 제목을 출력하시오 (단, 데뷔일을 기준으로 내림차순 정렬)
A. select e.name as '엔터', gg.name as '걸그룹', gg.debut, s.title from girl_group as gg join entertainment as e
    on gg.enter_id = e.e_id
     join song as s
     on gg.hit_song_id = s.s_id
     order by gg.debut desc;

 

Q6. 소속사 그룹 수를 내림차순으로 정렬하시오 (단, 히트곡이 있는 그룹만 조회할 것)
A. select e.name as '엔터', count(gg.g_id) as '그룹수' from girl_group as gg join entertainment as e
     on gg.enter_id = e.e_id
     join song as s
     on gg.hit_song_id = s.s_id
     group by e.e_id
     order by 그룹수 desc;

 

Q7. 소속사 그룹 수를 내림차순으로 정렬하시오 (단, 모든 그룹 포함하여 조회할 것)
A. select count(g_id) as '그룹수', e.name as '엔터' from girl_group as gg join entertainment as e
     on gg.enter_id = e.e_id
     group by e.e_id
     order by 그룹수 desc;