Day12_MYSQL_데이터 정규화 + 서브쿼리

1) 데이터 정규화

 

- 데이터 베이스를 설계할 때 중복을 최소화하는 것
- 크고, 조직화되어 있지 않은 테이블과 관계들을 조직화된 테이블과 관계들로 나누는 것

 

사용목적)

- 데이터를 갱신,삽입,삭제 등 테이블을 수정할 때 
  원하지 않게 데이터가 삭제되거나, 가공되는 일이 발생하는데 이를 이상현상이라고 함.

 

- 이상현상 발생하는 경우 데이터 정규화가 반드시 필요

 

종류)

 

1. 1NF(제1 정규화)

- 규칙: 테이블 안에 모든 값들은 단일값

 

 

 

1. 1번 테이블에서 hobby라는 필드를 추가하고 싶다
2. hobby라는 필드안에 중복된 값이 안들어가게 하고 싶다.  (ex) hobby : '운동,'등산','축구')

 

 

2. 2NF(제2 정규화)

- 규칙: 1NF를 만족 + 완전 함수 종속성을 가진 관계들로만 테이블을 생성하는 것

- 종속성들 중 종속 관계에 있는 열들 끼리 테이블을 구분해주는 것 (✔함수 종속성 = X값에 따라 Y값이 결정되는 경우) 

제2 정규화 예시

 

2NF : 비행기정보라는 테이블을 따로만들었을 때 : 승객이 추가되면 승객 테이블에만 추가하면된다.

3. 3NF(제3 정규화)

- 규칙: 2NF를 만족하면서, 기본 키에 대해 이행적 함수 종속이 되지 않는 것을 의미

 

4. 비정규화
- 정규형에 일치하게 되어 있는 테이블을 정규형을 지키지 않는 테이블로 만드는 것 

 Cf) 테이블을 조회하는 용도로 사용하거나, 너무 데이터가 많이 나뉘어 성능이 저하될 상황이 우려되는 경우
 
어떤 작업을 수행하는지, 어떤 데이터를 사용하는지에 따라 적절한 정규화를 하는 것이 좋음

 

---

2) 서브 쿼리

    - 다른 커리 내부에 포함되어 있는 select문을 의미
    - 서브쿼리를 포함하고 있는 쿼리를 외부쿼리라 부르고, 서브쿼리는 내부쿼리라고 부름

    - 서브쿼리는 괄호()로 감싸져서 표현

    - 서브쿼리는 메인쿼리 컬럼 사용이 가능하며, 메인쿼리는 서브쿼리 컬럼 사용x  (파이썬 상속이랑 비슷 ! )
    - select, where, from ,having 절에 대해서 사용할 수 있다.

 

예시)

 

예시용 테이블 2개 생성 (product , product_new) + order 테이블

create table product(
	code varchar(6) not null,
    name varchar(50) not null,
    detail varchar(1000),
    price int default 0,
    regdate datetime default now()
);

create table product_new(
	code varchar(6) not null,
    name varchar(50) not null,
    detail varchar(1000),
    price int default 0,
    regdate datetime default now()
    
    create table orders(
	no int not null,
    userid varchar(20) not null,
    product varchar(100) not null,
    cnt int default 1,
    regdate datetime default now(),
    foreign key(userid) references member(userid)    
);

예시 1

'100001'의 가격보다 큰 price를 가진 상품의 모든 정보
select * from product where price >= (select price from product where code ='100001');


select절 
코드,이름,가격, 전체 데이터의 가격중 가장 큰 값을 출력하는 쿼리
select code,name,price,(select max(price) from product) as max_price from product;

출력 하고 싶은 것  | product 테이블에 전체

조건 : code 가 100001를 가진 데이터의 price보다 큰 데이터

해석: 100001의 price를 1개의 서브쿼리로 작성 후 메인 쿼리 조건식에 대입

---

 

예시2

 

 

시나리오 
상품을 최소 2번 이상 구입한 회원의 아이디와 이름 성별을 출력 / 서브쿼리를 사용

select userid, username, gender from member 
where userid in (select userid from orders group by userid having count(no) >= 2);

 

출력 하고 싶은 것  : member 테이블에 userid,username,gender 데이터

조건 : 상품 구매 횟수를 구할 수 있는건 order 테이블 / 이름,아이디,성별은 member 테이블

해석: order테이블에  상품 구매횟수가 2번 이상한 사람을 in 조건에 작성
         where절 order.userid와 member.userid를 비교 

 

---

예시3

join과 from절에 서브쿼리 사용 2가지 방법

 

상품을 2개 이상 구입한 사용자의 아이디 , 상품 구입횟수, 시도이름을 출력

select userid,substring(address1,1,3) as address from member;
select count(no) as cnt from orders group by userid;
select count(no) as cnt from orders group by userid having cnt >= 2;

조인 사용
select m.userid,count(o.no) as cnt, substring(m.address1,1,3) as address 
from member as m right outer join orders as o on m.userid = o.userid group by userid having cnt >= 2;

from 절 이용
select m.userid,t.ocnt,substring(m.address1,1,3) as address from member as m
right outer join
(select userid, count(no) as ocnt from orders group by userid having count(no) >=2)
as t on m.userid = t.userid;

 

출력 : userid, address1의 처음부터 3번째 자리까지 

해석
join 사용시 - join orders as o on m.userid = o.userid group by userid having cnt >= 2; 

from 절에 서브쿼리 이용 
(select userid, count(no) as ocnt from orders group by userid having count(no) >=2)