[MySQL] select문(2) - where절, 정렬(sorting), 그룹화(grouping)

💖 where절

조건절
대소비교, 판정

🔥 형식

select 필드이름
from 테이블이름
where 조건식;

🔥 예시

비교연산자

select employee_id, first_name, salary
from employees
where first_name = 'John';	-- first_name == "John"

select employee_id, first_name, salary
from employees
-- where first_name != 'John';	-- first_name != "John"
where first_name <> 'John';

select first_name, salary
from employees
where salary >= 10000;	-- salary가 10000 이상

select first_name
from employees
where first_name >= 'John';	-- J이후부터 시작하는 이름이 출력됨

select employee_id, first_name, hire_date
from employees
-- where hire_date < '1990-01-01';		-- year + "-"+ month + "-" + day
where hire_date < date('1990-01-01');	-- date != string, date자료형으로 비교

all / any / in / exists / between

/*
all any in exists between
abd or  or        >= <=
*/

select first_name, salary
from employees
where salary = all( select salary 
					from employees 
                    where first_name = 'John');	-- sub Query
-- where salary = 8200 and salary = 2700 and salary = 14000		-- all 조건                    

select first_name, salary
from employees
where salary = any( select salary 
					from employees 
                    where first_name = 'John');
-- where salary = 8200 or salary = 2700 or salary = 14000	

select first_name, salary
from employees
where salary in(8200, 2700, 14000, 10000);	-- 여러 개의 값을 비교할 수 있다

select first_name, job_id
from employees
where job_id in('AD_VP', 'IT_PROG');

select first_name, salary, job_id
from employees a
where exists(	select 1 from dual
				where a.job_id = 'IT_PROG' );	-- 있는지 없는지를 가리는 것. 사용하기 까다로움 

select first_name, salary
from employees
where salary >= 6000 
	and salary <= 10000;
    
select first_name, salary
from employees
where salary between 6000 and 10000;    

select first_name, salary
from employees
where salary < 6000 
	or salary > 10000;

select first_name, salary
from employees
where salary not between 6000 and 10000; 

like

/*
	**like : 포함하는 문자열
*/

select first_name
from employees
where first_name like 'G_ra_d'; 	-- _ 한글자, 
									-- G_ra_d가 포함된 5개의 문자로 이루어진 문자열 출력

select first_name
from employees
where first_name like 'K%y';	 	-- % 글자의 갯수와 상관없다, 
									-- 첫번째 문자가 K 마지막 문자가 y인 문자열 출력

select first_name
from employees
where first_name like 'M%';

select first_name
from employees
where first_name like '%y';

select first_name
from employees
where first_name like '%b%'; 		-- b가 포함된 것들을 출력. 대소문자를 가리지 않는다. oracle에서는 안됨

select first_name, hire_date
from employees
where hire_date like '2000-04%';

💖 정렬(sorting)

🔥 형식

order by 정렬하려는 테이블명 asc;	-- 오름. 기본값
order by 정렬하려는 테이블명 desc;	-- 내림

🔥 예시

select first_name, salary
from employees
order by salary asc;		-- salary순으로 정렬(오름)

select first_name, salary
from employees
order by salary desc;		-- salary순으로 정렬(내림)

select employee_id, first_name
from employees
order by employee_id;		-- 사원번호순으로 정렬(오름)

select first_name, hire_date
from employees
order by hire_date asc;		-- 입사시기순으로 정렬

select job_id, first_name, salary
from employees
where job_id = 'IT_PROG'
order by salary desc;		-- 업무가 'IT_PROG'인 사원들 중 salary순으로 정렬(내림)

select first_name, manager_id
from employees
order by manager_id asc;  	-- 매니저넘버순으로 정렬. null 인 사원이 먼저 출력된다

select first_name, commission_pct
from employees
order by commission_pct asc;	-- 보너스순으로 정렬

select first_name, job_id, salary
from employees
order by job_id asc, salary desc; 	-- 업무순으로 먼저 정렬되고(오름) 거기에서 급여순으로 정렬(내림) 

select first_name, salary * 12 as 연봉
from employees
order by 연봉 desc;		-- 연봉순으로 정렬

💖 그룹화(grouping)

🔥 형식

group by 그룹으로 묶으려는 테이블명
having 조건식 							-- 그룹 조건식

🔥 예시

-- 그룹으로 묶는 기능
select distinct department_id
from employees
order by department_id asc;

-- group by 절
select department_id
from employees
group by department_id;

통계 : count / sum / avg / max / min

select count(employee_id), count(*), sum(salary), avg(salary), max(salary), min(salary),
	sum(salary) / count(*)
from employees
where job_id = 'IT_PROG';	-- 업무가 'IT_PROG'인 사원들만 통계

-- truncate : 버림
select department_id, sum(salary), max(salary), truncate(avg(salary), 0)
from employees
-- where department_id is not null
group by department_id;

select department_id, job_id
from employees
group by department_id, job_id
order by department_id asc;

-- having : 조건
select job_id, sum(salary), count(*)
from employees
group by job_id
having sum(salary) > 15000
order by sum(salary) desc;