๐ ์์ํ๊ธฐ์ , group by์ having ์
select [distinct] [์ปฌ๋ผ1,์ปฌ๋ผ2,.....][as ๋ณ๋ช ][ || ์ฐ์ฐ์][*] --- 6 from ํ ์ด๋ธ๋ช --- 1 [where ์กฐ๊ฑด์ ] --- 2 [group by์ปฌ๋ผ๋ช ] --- 3 [having ์กฐ๊ฑด์ ] --- 4 [order by ์ปฌ๋ผ๋ช asc|desc ] --- 5
group by
: ๊ทธ๋ฃนํจ์(max,min,sum,avg,count..)์ ๊ฐ์ด ์ฌ์ฉhaving
: ๋ฌถ์ด๋์ ๊ทธ๋ฃน์ ์กฐ๊ฑด์
๐ก ์ฐธ๊ณ
- ์ ๋ฌดID : job id
- ๋ถ์ID : department_id
- ์ฌ์๋ช : last_name
โ๏ธ ํ๊ท ๊ตฌํ๊ธฐ
์ฌ์๊ธ์ฌํ๊ท
select format(avg(salary),0) as "์ฌ์๊ธ์ฌํ๊ท " from employees;
โ๏ธ ๋ถ์๋ณ๋ก ๊ธ์ฌ ํ๊ท ์ ๊ตฌํด์ ๋ถ์ ID, ๊ธ์ฌ ํ๊ท ์ ์ถ๋ ฅํ์์ค.
select department_id as "๋ถ์ID", format(avg(salary),0)as "๊ธ์ฌํ๊ท "
from employees
group by department_id
order by department_id desc;
โ๏ธ ์ ๋ฌดID๋ณ ๊ธ์ฌ์ ํฉ๊ณ๋ฅผ ๊ตฌํด์ ์ ๋ฌดID, ๊ธ์ฌํฉ๊ณ๋ฅผ ์ถ๋ ฅ
select job_id as "์
๋ฌดID", format(sum(salary),0) as "๊ธ์ฌํฉ๊ณ"
from employees
group by job_id
order by job_id;
โ๏ธ ๋ถ์๋ณ ๊ธ์ฌํ๊ท ์ ๊ตฌํด์ ํ๊ท ๊ธ์ฌ๊ฐ 6000์ด์์ธ ๋ถ์๋ง ์ถ๋ ฅ (8๊ฑด)
-- ๋ถ์ID ํ๊ท ๊ธ์ฌ
-- -----------------------
-- NULL 7000
-- 20 9500
select department_id as "๋ถ์ID", floor(avg(salary)) as "ํ๊ท ๊ธ์ฌ"
from employees
group by department_id
having avg(salary) >= 6000;
โ๏ธ ๋ถ์๋ณ ๊ธ์ฌํ๊ท ์ ๊ตฌํ์์ค (9๊ฑด)
-- NULL ๏ฟฆ7,000
-- 20 ๏ฟฆ9,500
select department_id as "๋ถ์์ฝ๋", concat('โฉ',format(round(avg(salary),0),0)) as "ํ๊ท ๊ธ์ฌ"
from employees
group by department_id
having avg(salary) >= 5000
order by department_id;
โ๏ธ 10๊ณผ 20 ๋ถ์์์ ์ต๋๊ธ์ฌ๋ฅผ ๋ฐ๋์ฌ๋์ ๊ธ์ฌ๋ฅผ ๊ตฌํ์์ค, ๋ถ์๋ณ๋ก ์ค๋ฆ์ฐจ์ ์ ๋ ฌํ์์ค
-- department_id max_salary
-----------------------------
-- 10 4400
-- 20 13000
select department_id, round(max(salary),0) as "max_salary"
from employees
where department_id in (10,20)
group by department_id
order by 2;
having์ (where + group by + having)
-- department_id max_salary
-- -----------------------------------
-- 20 13000
select department_id, round(max(salary), 0)
from employees
where department_id in (10, 20)
group by department_id
having max(salary) >= 5000
order by department_id;
๐ ์์ํ๊ธฐ์ , join์
- natural join(์์ฐ์กฐ์ธ) : ๊ฐ์์ปฌ๋ผ์ด ์ฌ๋ฌ๊ฐ ์์๋ ๊ฐ์ ์ปฌ๋ผ ๋ชจ๋๋ฅผ and์ฐ์ฐํด์ ์ฐ๊ฒฐ
- Inner join(๋ด๋ถ์กฐ์ธ) : ๊ฐ์๊ฒ ๋ผ๋ฆฌ๋ง ์ฐ๊ฒฐ
- Outer join(์ธ๋ถ์กฐ์ธ) : ํ์ชฝ์ ๊ธฐ์ค(๋ชจ๋ํฌํจ)ํด์ ์ฐ๊ฒฐ
- left join : ์ผ์ชฝ์ปฌ๋ผ ๋ชจ๋ํฌํจ
- right join : ์ค๋ฅธ์ชฝ์ปฌ๋ผ ๋ชจ๋ํฌํจ
- self join : ์๊ธฐ์์ ํ ์ด๋ธ๊ณผ ์ฐ๊ฒฐ
- cross join : ๋ชจ๋ ๊ฒฝ์ฐ์ ์๋ก ์ฐ๊ฒฐ
- non equijoin : ๋ฒ์์ ์ํ๋์ง ์ฌ๋ถ๋ฅผ ํ์ธ
- n๊ฐ ํ ์ด๋ธ ์กฐ์ธ : ์ฌ๋ฌ๊ฐ์ ํ ์ด๋ธ ์กฐ์ธ
๐ก ์ฐธ๊ณ
1. MySQL๊ตฌ๋ฌธ
2. Ansiํ์ค๊ตฌ๋ฌธ
โ๏ธ ์ฌ์ํ ์ด๋ธ๊ณผ ๋ถ์ํ ์ด๋ธ์์ ๋ถ์๊ฐ ๊ฐ์๊ฒฝ์ฐ ์ฌ์๋ฒํธ,๋ถ์๋ฒํธ,๋ถ์์ด๋ฆ์ ์ถ๋ ฅ
mysql ๊ตฌ๋ฌธ
select employee_id, e.department_id, department_name
from employees e, departments d
where e.department_id = d.department_id;
โ๏ธ ๋ถ์ํ ์ด๋ธ๊ณผ ์์นํ ์ด๋ธ์ ์ฐ๊ฒฐํ์ฌ ๋ถ์๊ฐ ์์นํ ๋์๋ฅผ ์์๋ด์์ค(inner join)
select d.department_id, l.city
from departments d, locations l
where d.location_id = l.location_id;
โ๏ธ ์ฌ์ํ ์ด๋ธ๊ณผ ๋ถ์ํ ์ด๋ธ์์ ๋ถ์๋ฒํธ๊ฐ ๊ฐ์ ์ฌ์์ ์กฐ์ธํ์์ค ==> 107๊ฑด
select last_name, department_id, department_name
from employees
left join departments using(department_id);
โ๏ธ ์ฌ์ํ ์ด๋ธ๊ณผ ๋ถ์ํ ์ด๋ธ์์ ๋ถ์๋ฒํธ๊ฐ ๊ฐ์ ์ฌ์์ ์กฐ์ธํ์์ค
select last_name, department_id, department_name
from employees
right join departments using(department_id);
โ๏ธ departments ์ locations ์์ฐ์กฐ์ธ์ ๋น๊ต(๊ฐ์์ปฌ๋ผ : location_id), ๋๊ฐ์ ํ ์ด๋ธ์ ์ฐ๊ฒฐํด์ ๋ถ์์์น(location_id), ๋์(city), ๋ถ์์ด๋ฆ(department_name)์ ์ถ๋ ฅํ์์ค
natural join
select location_id, city, department_name
from departments
natural join locations;
inner join
select location_id, city, department_name
from departments
inner join locations using(location_id);
โ๏ธ ๋ถ์ID์ ๋งค๋์ ID๊ฐ ๊ฐ์ ์ฌ์์ ์ฐ๊ฒฐํ์์ค
select last_name, department_id, manager_id
from employees e
inner join departments d using(department_id, manager_id);
select last_name, department_id, manager_id
from employees
natural join departments;
โ๏ธ ์ฌ์๊ณผ ๊ด๋ฆฌ์๋ฅผ ์ฐ๊ฒฐํ์์ค. ๋ชจ๋ ์ฌ์์ ํ์
select e.employee_id as "์ฌ์๋ฒํธ", e.last_name as "์ฌ์์ด๋ฆ", m.last_name as "๊ด๋ฆฌ์"
from employees e, employees m
where e.manager_id = m.manager_id