[DB]서브쿼리, 정규화

서브쿼리의 종류로는

중첩서브쿼리(단일 행, 복수행, 다중컬럼...where문에 작성),

인라인 뷰(from문에 작성),

스칼라 서브쿼리(select문제 작성)가 있다.

사실 그 외에 걍 어디에서도 다 사용 가능하다.

 

MySql5.5까지는 속도 이슈가 있었다. 그래서 정말 join으로 안되는것들만 서브쿼리로 하는 것이 옳았다. 그러나 5.6부터는 서브쿼리 최적화가 잘 되어서 성능이 많이 좋아져서 이젠 크게 상관 없다. 

그래도 왠만하면 최대한 join을 이용할 것이 추천된다.(https://jojoldu.tistory.com/520)

 

단일행

서브쿼리의 결과가 단일행을 리턴하는 것임.

ex) 부서가 Seattle에 있는 부서의 정보

select department_name
from departments
where department_id = (select department_id
			from employees
			where employee_id = 100);

다중행

- in 사용

ex)근무 도시가 ‘seattle’(대소문자 구분X)인 사원의 사번, 이름

select department_id, department_name
from departments
where department_id in (select department_id
                    from departments
                    where location_id = (
                                        select location_id
                                        from locations
                                        where city = 'seattle'
                                        )
                    );

 

- any 사용

ex)모든 사원 중 적어도(최소급여자보다) 30번 부서에서 근무하는 사원의 급여보다 많이 받는 사원의 정보.

select employee_id, first_name, department_id
from employees
where salary > any(select salary
                from employees
                where department_id = 30);

 

- all 사용

ex)30번 부서에서 근무하는 모든(최대급여자보다) 사원들보다 급여를 많이 받는 사원 정보

select employee_id, first_name, department_id
from employees
where salary > all(select salary
                from employees
                where department_id = 30);

 

다중열(!=다중행)

다중 칼럼을 리턴하는 것임

select employee_id, first_name
from employees
where (salary, department_id) in (
                            select salary, department_id
                            from employees
                            where commission_pct is not null
                            and manager_id = 148
                            );

 

 

인라인 뷰

from절 다음에 테이블이 아닌 select절이 나온다

 

ex)모든 사원의 평균급여보다 적게 받는 사원들과 같은 부서에서 근무하는 사원의 정보

select e.employee_id, e.last_name, e.salary, d.department_id
from (select distinct department_id
	  from employees 
	  where salary < (select avg(salary) from employees)) d
join employees e on(d.department_id = e.department_id);

 

Top N질의

한페이지당 5명씩 출력될 때 현재 3페이지 정보를 출력

set @pageno=3; -- 변수 설정

select b.rn, b.employee_id, b.first_name, b.salary
from(select @rownum := @rownum + 1 as rn, a.*
	 from(select employee_id, first_name, salary
		  from employees
		  order by salary desc
		  ) a, (select @rownum := 0) tmp
	) b
where b.rn > (@pageno * 5 - 5) and b.rn <= (@pageno * 5);

 

근데 mysql은 걍 아래처럼 하면 편하다.

select employee_id, first_name, salary
from employees
order by salary desc limit 10, 5;

 

스칼라쿼리

select절에 있는 서브쿼리. 한개의 행만 반환함

 

ex)직급 아이디가 IT_PROG인 사원의 사번, 이름, 업무ID, 부서이름

select e.employee_id, e.first_name, job_id,
	  (select department_name from departments d 
	   where e.department_id = d.department_id) as department_name
from employees e
where job_id = 'IT_PROG';

 

ex)60번 부서에 근무하는 사원의 사번, 이름, 급여,부서번호, 60번부서의 평균급여

select e.employee_id, e.first_name, salary, department_id,
	(select avg(salary) from employees where department_id = 60) as avg60
from employees e
where department_id = 60;

 

ex)부서번호가 50인 부서의 총급여, 60인 부서의 평균급여, 90인 부서의 최고급여, 90인 부서의 최저급여

select
	(select sum(salary) from employees where department_id = 50) sum50,
	(select avg(salary) from employees where department_id = 60) avg60,
	(select max(salary) from employees where department_id = 90) max90,
	(select min(salary) from employees where department_id = 90) min90
from dual;

 

정규화

아 이거 설명하기 귀찮.

아래 링크에 잘 설명되어 있으니 헷갈릴땐 이거 보자.ㅋㅋ

https://mangkyu.tistory.com/110

짧게 설명하면, 

1정규화는 한 칼럼에 여러 정보가 들어가면 안되는 것임. 예를 들면 "과목"칼럼에 "수학, 영어, 국어" 이렇게 들어가면 안됨

2정규화는 어떤 테이블의 기본키가 복합키로 이루어져있을 때, 복합키중 하나의 칼럼이 다른 칼럼을 구별할 수 있는 관계가 되면 안된다는 것임. "이름, 주민번호"가 복합키로 기본키라면 이미 주민번호만으로 모든 사람을 다 구별가능하니까 2정규화가 지켜지지 않은 것이다.

3정규화는 기본키외에 걍 일반 칼럼끼리 서로 종속적인 관계를 갖고 있는 것임. 두 칼럼중 하나가 "수강과목", 또하나는 "수강료"라면 수강료는 수강과목이 달라질때마다 계속 달라져야 함. 3정규화가 안지켜졌다면 한 테이블에 다 있는거니까 만약 내가 듣는 수강과목이 달라졌다면 수강료도 고쳐야 과거의 수강료를 내지 않게 된다. 반면 3정규화를 지켜서 테이블을 나누면 걍 내가 듣는 과목명만 바꾸면 알아서 달라진 수강료를 내게 된다.