DAY 14 - MysQL

join

: 두 개 이상의 테이블의 특정 조건에 따라 조합하여 결과를 조회하고자 할떄 사용하는 명령어 

 

INNER JOIN

: 두 테이블에서 조건이 일치하는 레코드만 반환 

select
E.employee_number as employee_number,
E.name as employee_name, 
    D.code as department_code,
    D.name as department_name,
    D.tel_number as department_tel_number
from employee as E inner join department as D
on E.department_code = D.code;

LEFT OUTER JOIN (LEFT JOIN)

: 첫 번째 테이블의 모든 레코드와 두 번째 테이블의 조건이 일치하는 레코드를 반환, 첫 번쨰 테이블의 레코드에서 조건이 일치하는 값이 없으면 null로 표현 

select
E.employee_number as employee_number,
E.name as employee_name, 
    D.code as department_code,
    D.name as department_name,
    D.tel_number as department_tel_number
from employee as E left join department as D
on E.department_code = D.code;

RIGHT OUTER JOIN (RIGHT JOIN)

: 두 번째 테이블의 모든 레코드와 첫 번째 테이블의 조건이 일치하는 레코드를 반환, 두 번쨰 테이블의 레코드에서 조건이 일치하는 값이 없다면 null로 반환

select
E.employee_number as employee_number,
E.name as employee_name, 
    D.code as department_code,
    D.name as department_name,
    D.tel_number as department_tel_number
from employee as E right join department as D
on E.department_code = D.code;

서브쿼리

: 쿼리 내부에 위치하는 또 다른 쿼리, 쿼리 결과를 조건이나 테이블로 사용할 수 있도록 함 

where 절에서 서브쿼리

: 쿼리 결과를 조건으로 사용하여 조건을 동적으로 지정 
: where 절에서 비교 연산으로 사용될 때 조회하는 컬럼의 개수 및 레코드 개수 주의 

-- 에러
select * from employee 
where department_code = ( 
select code from department 
);

 

* 서브쿼리에서 1개의 컬럼에는 1개의 값이 나왐

- 이 상태에서는 3개의 값(department의 값)이 나오기 때문에 결과 값을 찾을 수 없어서 에러가 남 

select * from employee 
where department_code = ( 
select code from department where name = '인사부'
);

* 에러가 나지 않을려면 where절 사용 

 

from 절에서의 서브쿼리 : 쿼리 결과 테이블을 다시 from절에 사용하여 복합적인 테이블 조회

select * 

from (
	select name, department_code
    from employee
	where department_code is null) as SQ # is not null일 때 에러남 is null로 변경함 
# (as로 SQ이름을 임의로 지정, 지정하지 않을 시 에러남)

where name = '홍길동';

* 서브쿼리를 사용할려면 테이블의 이름이 있어야 함

select * 
from (
select name as employee_name, department_code -- 에러 (as employee_name으로 변경상태에서 where절에 그대로 name을 사용했기 때문)
    from employee
    where department_code is null
) as SQ 
where employee_name = '홍길동'; # select에서 지정한 name으로 바꿔야지 사용가능
# select name as employee_name을 지정해줬기 때문에 where절에서 name이 없기 때문이다

부서 이름이 '개발부'인 사원이름, 부서코드, 부서명을 조회
:  하나의 테이블로는 찾을 수 없음 - 서브쿼리로 찾기 불가능 # 2개 이상의 테이블로 찾는 경우 join을 사용 

 

부서 이름이 '개발부'인 사원이름, 부서코드를 조회 
: 하나의 테이블로 찾을 수 있음 - 서브쿼리로 찾기 가능 # 1개의 테이블로 찾는 경우 서브쿼리 가능 
: join도 가능하지만 서브쿼리가 join보다 처리속도가 빠르기 때문에 서브쿼리를 사용함 

select name, department_code
from employee
where department_code = (
select code -- 부서코드를 조회
from department
where name = '개발부'
);

 

집계 함수

: 레코드를 '그룹화'하여 하나의 결과를 반환하는 함수 

count() : 특정 조건에 맞는 행의 개수를 반환 

select count(*) from jeju;
select count(*) from jeju where speed_80m > 3;

sum() : 특정 컬럼의 값을 모두 합한 결과를 반환 

select sum(speed_80m) from jeju;
select sum(speed_80m), sum(speed_70m) from jeju where observe_date like '2023-08-__';
select sum(speed_80m)/ count(speed_80m) from jeju;

avg() : 특정 컬럼의 값의 평균을 반환 

select avg(speed_80m) from jeju;

min(), max() : 특정 컬럼의 최소값과 최대값을 반환 

select min(speed_80m), max(speed_80m) from jeju;

 

그룹화(group by) : 레코드를 하나 이상의 컬럼으로 그룹하여 결과를 도출하고자 할때 사용 
일반적으로 집계함수와 함께 사용됨 

# select * from jeju2 group by observe_date; -- 에러 
select observe_date from jeju2 group by observe_date; # select위치에서 표현을 해줘야 실행됨  
# select observe_date, speed_80m from jeju2 group by observe_date; -- 에러  
select observe_date, speed_80m from jeju2 group by observe_date, speed_80m; # select위치에서 표현을 해둔것은 group by절에도 표현을 해줘야 함
select observe_date, avg(speed_80m) from jeju2 group by observe_date;

  
필터링(HAVING) : 그룹화된 결과에 필터링을 적용하는 명령어 
주의! where절과 사용방법이 같지만 where절은 select 이전에 실행되고 having절은 select 이후에 실행 

select observe_date, avg(speed_80m) as avg 
from jeju2 
group by observe_date
having avg > 5.5;

select observe_date, avg(speed_80m) as avg 
from jeju2 
group by observe_date
having speed_80m > 5.5; -- 에러 #speed_80m는 이 테이블에 대해서는 존재하지 않음

select observe_date, avg(speed_80m) as avg 
from jeju2 
where speed_80m > 5.5 # 사용하기 위해서는 where절에서 사용해야 함 
group by observe_date;