[MYSQL] SELECT문(조건절, GROUP BY, ORDER BY 등)

MYSQL

MYSQL의 CRUD는 INSERT, SELECT, UPDATE, DELETE가 있습니다.

그중 가장 많이 사용되는 SELECT문에 대해 알아보겠습니다!

 

---

 

기본 select 형식

SELECT (값, 컬럼명, 함수, sub query)
FROM (테이블명, sub query)

-- 기본 select
select * from employees;

select employee_id, last_name, first_name
from employees;


-- 임의 컬럼
select '이름: ', 20, first_name
from employees;


-- alias == 별명
select first_name as "이름", last_name as "성"
from employees;

select first_name 이름, last_name 성 -- alias 생략 가능
from employees;

select first_name, last_name, salary*12 as 연봉
from employees;


-- concat()
select concat('이름 : ', last_name, first_name) as '전체이름'
from employees;

 

---

 

조건절

(1) 대소 비교, 판정

같다	다르다	크다	작다	크거나 같다	작거나 같다
=	!=, <>	>	<	>=	<=

select first_name, salary
from employees
where first_name='john';

select first_name, salary
from employees
where first_name != 'john';

select * 
from employees
where salary >= 9000;

select *
from employees
where first_name >= 'john'; -- a~i까지는 안 나옴, j 이후 알파벳이 해당됨

select hire_date
from employees
where hire_date < '1991-01-01'; -- 1991 이전 날짜, date 타입이지만 문자열로 비교가능!!
-- where hire_date < date('1991-01-01'); -- 이렇게 안하고 위 처럼 해도 됨

 

(2) NULL
IS NULL, IS NOT NULL

 

(3) 논리 연산

&&	||
AND	OR

select first_name, last_name
from employees
where manager_id is null;

select *
from employees
where manager_id is not null and salary >= 10000;

select *
from employees
where manager_id is not null and not salary >= 10000;

select first_name, last_name, salary
from employees
where (first_name='john' or first_name='den') 
and salary > 6000;

 

---

 

ORDER BY, GROUP BY

(1) ORDER BY

정렬할 때 사용하는 것으로, ASC(오름차순), DESC(내림차순)이 있습니다.

ASC, DESC를 생략한다면 ASC(오름차순)이 적용됩니다. 

select first_name, salary
from employees
order by salary desc;

select employee_id, first_name, manager_id
from employees
order by manager_id is null asc; -- null은 나중에 출력
-- order by manager_id is null desc; -- null이 먼저 출력

select first_name, commission_pct, salary
from employees
order by commission_pct is null desc, salary desc;

 

(2) GROUP BY

칼럼 별로 그룹화를 할 수 있으며 집계 함수와 함께 사용될 때가 많습니다.

집계 함수는 아래 표와 같이 5개가 있습니다.

개수	합계	평균	최대값	최소값
COUNT	SUM	AVG	MAX	MIN

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

select count(employee_id), count(*), sum(salary), avg(salary), max(salary), min(salary)
from employees
where job_id = 'it_prog';

 

또한 HAVING 절을 통해 GROUP BY의 조건을 추가할 수 있습니다.

select job_id, sum(salary)
from employees
group by job_id
having sum(salary) >= 100000;

 

---

 

그 외

(1) ALL, ANY, IN

select first_name, last_name, salary
from employees
where salary in (6000, 8000, 10000);

select first_name, last_name, salary
from employees
where salary = all (select salary from employees where job_id = 'it_prog'); -- all은 모두 해당되야 하니깐 안나옴

select first_name, last_name, salary
from employees
where salary = any (select salary from employees where job_id = 'it_prog');

 

(2) EXIST

select first_name, salary, job_id
from employees a
where exists (select 1 from dual where a.job_id = 'it_prog');

 

(3) BETWEEN

select first_name, salary
from employees
where salary between 3200 and 9000;

select first_name, salary
from employees
where salary not between 3200 and 9000; -- salary < 3200 or salary > 9000

 

(4) LIKE

문자열을 비교하는 것으로, '_'은 한 글자 '%'은 여러 글자를 의미합니다.

select first_name
from employees
where first_name like 'G_ra_d'; -- _은 한글자

select first_name
from employees
where first_name like 'k%y'; -- %은 여러글자

 

---

지금까지 MYSQL의 SELECT문에 대해 알아보았습니다!

다음에는 INSERT, UPDATE, DELETE문에 대해 알아보겠습니다.