MySQL - 문법정리(2) - where, order, group, having...

전체적인 형식만 간단하게 정리해보자. 아래 코드들의 내용을 종합했을 때, 

 

SELECT 컬럼명1, 컬럼명2...

FROM 컬럼명에 속해 있는 테이블명

WHERE 그룹으로 묶기 전, 출력 전에 다는 조건

GROUP BY 그룹으로 묶기

HAVING 그룹으로 묶은 후, 출력 전에 다는 조건

ORDER BY 정렬

정도의 틀이 형성된다. 

 

이후 문제를 포스팅하겠지만,  형식은 직관적이므로 순서를 크게 외울 것은 없으나

WHERE과 HAVING에서 GROUP 전/후의 관계에서 조건을 부여하는 것이기 때문에

내가 부여하는 조건이 그룹을 묶는데 영향을 미치는지 아닌지를 잘 구분하는 것이 중요할 것이다.

use mydb;
 
/*
    where: 조건절
    
    대소비교, 판정
    > < >= <= 
    =(같다) 
    != <>(같지 않다)
 
    is null( == null)
    is not null( != null)
    
    &&(AND) -> and
    ||(OR) -> or
*/
 
-- first_name 컬럼의 값이 John인 다른 컬럼명에 따른 데이터를 출력
select employee_id, first_name, salary
from employees
where first_name = 'John'; -- Java에서는 == "John"
 
-- first_name 컬럼의 값이 John이 아닌 다른 컬럼명에 따른 데이터를 출력
select employee_id, first_name, salary
from employees
where first_name != 'John';
-- where first_name <> 'John'; 와 같다.
 
-- salary 컬럼의 값이 10000이상인 다른 컬럼명에 따른 데이터를 출력 
select first_name, salary
from employees
where salary >= 10000;
 
-- ASCII 코드를 통해 문자도 대소비교가 가능하다 -> 정렬도 같은 논의
select first_name
from employees
where first_name >= 'John';
 
-- hire_date가 1990년 1월 1일보다 이전의 값인 다른 컬럼명에 따른 데이터를 출력
-- 날짜 및 시간 컬럼명 < 어떤 날짜 혹은 시간 값 이면, 정해진 날짜 혹은 시간보다 이전을, 반대로 > 이면 이후를 의미
select employee_id, first_name, hire_date
from employees
-- where hire_date < '1990-01-01'; 
where hire_date < date('1990-01-01'); -- date != String 완전히 다른 데이터 타입이다.
 
-- is null (== null)
-- manager_id의 값이 null인 다른 컬럼명들의 데이터를 출력
select first_name, last_name, manager_id
from employees
-- where manager_id = null;  -- 안된다
where manager_id is null;
 
-- manager_id의 값이 null이 아닌 다른 컬럼명들의 데이터를 출력
select first_name, last_name, manager_id
from employees
where manager_id is not null;
 
select first_name, commission_pct
from employees
where commission_pct is not null;
 
-- 조건1 그리고 조건2
select first_name, commission_pct, salary
from employees
where commission_pct is null
    and salary >= 10000;
 
-- 조건1 또는 조건2
select employee_id, first_name
from employees
where first_name = 'John'
    or first_name = 'Den';
 
-- 전체 문장 중에서 부분만 실행시키고 싶으면 그 부분을 범위 지정하고
-- ctrl + shift + enter
select employee_id, first_name, salary
from employees
where job_id = 'IT_PROG'
    and salary > 8000;
    
-- 조건 사이에 우선순위를 부여하려면 괄호()로 묶는다.
select first_name, salary
from employees
where (first_name = 'John'
    or first_name = 'Den')
    and salary < 6000;
 
/*
    all, any, in, exists, between
    and     or   or          
*/
 
select first_name, salary
from employees
where salary = all(select salary from employees where first_name = 'John');
-- first_name이 John인 사람들의 salary값들은 2700, 8200, 14000인데, 
-- 위와 같이 쓰면 where salary = 8200 and salary 2700 and salary = 14000가 되므로 불가능한 조건이 된다.
 
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);
-- where salary = 8200 or salary 2700 or salary = 14000의 의미
 
-- job_id가 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: 포함하는 문자열 
    
*/
-- _는 한글자이고 _ 부분에 어떤 글자가 와도 상관없다.
select first_name
from employees
where first_name like 'G_ra_d'; 
 
-- %는 글자의 개수와 상관없이, 어떤 글자가 와도 상관 없다.
-- K로 시작하고 y로 끝나기만 하면 된다.
select first_name
from employees
where first_name like 'K%y'; 
 
select first_name
from employees
where first_name like 'M%';
 
select first_name
from employees
where first_name like '%y';
 
-- 중간에 b가 존재하기만 하면 된다.
select first_name
from employees
where first_name like '%b%';
 
-- 날짜에서도 활용가능
-- 2000년대를 나타낼 때, 
select first_name, hire_date
from employees
where hire_date like '2000%';
 
select first_name, hire_date
from employees
where hire_date like '2000-04%';
 
 

 

use mydb;
 
/*
    order by == sorting(정렬) 오름차순/내림차순
 
*/
 
select first_name, salary
from employees
order by salary asc; -- 오름차순
-- order by salary; (생략해도 오름차순이 기본값)
 
select first_name, salary
from employees
order by salary desc; -- 내림차순
 
select job_id, first_name, salary
from employees
where job_id = 'IT_PROG'
order by salary desc;
 
select first_name, manager_id
from employees
order by manager_id asc; -- null값이 오름차순일 때는 제일 먼저 나옴(즉, 제일 작은 값으로 취급)
-- order by manager_id desc; 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; -- 일단 job을 기준으로 오름차순 정렬하고 job의 동일한 값들 내에서 salary기준으로 내림차순
 
select first_name, salary * 12 as "연봉"
from employees
order by "연봉" desc;
 
-- 그룹으로 묶는 기능
select distinct department_id -- distinct를 통해 중복된 값을 안보여줌
from employees
order by department_id asc;
 
-- group by절
select department_id
from employees
group by department_id
order by department_id asc;
 
-- 통계
/*
    그룹함수
    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';
 
-- truncate: 버림 -> truncate(column명, 2): 컬럼명에 해당되는 값들은 소수 둘째자리까지 나타내고 아래는 버림 
select department_id, sum(salary), max(salary), truncate(avg(salary), 0) 
from employees
where department_id is not null
group by department_id
order by department_id asc;
 
select department_id, job_id
from employees
group by department_id, job_id
order by department_id asc, job_id desc; -- 부서별 오름차순 정렬먼저, 그다음에 직업별로 내림차순
 
-- having: group 묶은 다음의 조건. group by와 함께 써야 한다.
select job_id, sum(salary)
from employees
group by job_id
having sum(salary) >= 15000
order by sum(salary) desc;
 
-- 급여가 5000이상 받는 사원으로 합계를 내서 업무로 그룹화하여 급여의 합계가 20000을 초과하는 업무명과 사원수, 합계, 평균을 구하시오
select job_id, count(employee_id), sum(salary), round(avg(salary), 0) -- round는 반올림
from employees
where salary >= 5000
group by job_id
having sum(salary) > 20000;