어제에 이어 마저 MySQL에 대해서 공부했습니다.
MYSQL 문법
- 그룹별 집계
집계 기준열을 지정해 그룹별 집계(SUM, AVG 등등)하는 것입니다.
부서별, 남녀별, 지역별, 연도별 등등 '별'을 기준으로 그룹화 해줍니다.
SELECT dept_id, SUM(IFNULL(salary, 0)) AS tot_salary
FROM employee
WHERE retire_date IS NULL
GROUP BY dept_id;해당 코드를 보게 되면 집계는 NULL을 무시하기 때문에 IFNULL을 통해서 NULL이 있다면 0으로 처리를 해주는 쿼리를 작성하고 근무중인 사원중에 부서별로 묶어서 월급의 총 합을 구해주는 쿼리입니다.
GROUP BY를 사용할 때 WHERE 절과 같이 조건을 걸어주는 HAVING 절을 사용할 수 있습니다.
WHERE 절 조건: GROUP BY 하기 전 조건을 위해 사용합니다
HAVING 절 조건: GROUP BY 한 후 조건을 위해 사용합니다.
HAVING 없는 GROUP BY는 존재할 수 있지만, GROUP BY 없는 HAVING은 존재할 수 없습니다.
SELECT dept_id, COUNT(*) AS emp_count
FROM employee
WHERE retire_date IS NULL
GROUP BY dept_id
HAVING COUNT(*) >= 3
ORDER BY emp_count DESC;
위 코드를 보게 되면 그룹화 뒤에 HAVING을 사용하여 COUNT(*) >= 3 라는 조건을 주었습니다. 해당 코드를 해석하자면 부서별 근무중이 사원이 3명 이상인 부서를 조회해서 보여줍니다. 부서컬럼명과 갯수를 보여줍니다.
HAVING 절에서는 열 별칭을 사용 가능하지만 권고하지는 않습니다.
- MySQL에는 YEAR, MONTH, DAY 함수가 있습니다.
날짜 데이터에 각 각 함수를 사용하면 년, 월, 일을 출력할 수 있습니다. 물론 구분자가 한국어로 년, 월, 일이라고 쓰여있으면 안됩니다.
- 순위를 작성해주는 RANK 함수
RANK를 사용하면 순위를 작성해줍니다.
SELECT emp_id, emp_name, dept_id, gender, phone, salary,
RANK() OVER(ORDER BY salary DESC) AS rnk
FROM employee
WHERE retire_date IS NULL;
해당 쿼리를 실행하면 근무중인 사람중에 salary을 기준으로 rnk 컬럼을 임시로 만들어 조회해줍니다.
SELECT * FROM (SELECT emp_id, emp_name, dept_id, gender, phone, salary,
RANK() OVER(PARTITION BY gender ORDER BY salary DESC) AS rnk
FROM employee
WHERE retire_date IS NULL) AS T
WHERE rnk = 1;
원래는 RNK라는 컬럼이 없지만 괄호로 묶고 AS 를 통해 T라는 테이블로 가상 생성해주고 다시 SELECT * FROM T를 통해 해당 테이블을 가져와서 사용하는 것입니다. 쿼리 안의 쿼리 형태를 하위쿼리라고 합니다.
- NTILE
NTILE를 사용하면 순위를 나눠서 표기할 수 있습니다.
SELECT emp_id, emp_name, dept_id, gender, phone, salary,
ELT(NTILE(3) OVER(PARTITION BY gender ORDER BY salary DESC), '상','중','하') AS grp
FROM employee
WHERE retire_date IS NULL;ELT를 사용하면 순위를 상중하로 출력할 수 있게 해줍니다.
전체 순위를 3으로 나누어 1등은 상으로 2등은 중으로 3등은 하로 나누어 표기해줍니다.
- 쿼리 작성 요령 INSERT
모든 열이 원래 순서대로 나열되는 경우 열 이름을 생략하기도 하지만 가독성 향상을 위해 열 이름을 지정하기를 권고합니다.
INSERT INTO department(dept_id, dept_name, unit_id, start_date)
VALUES('PRD', '상품', 'A', '2018-10-01');
해당 쿼리는 열 이름(컬럼명)을 작성해서 데이터를 추가하는 것입니다.
INSERT INTO department
VALUES('DBA', 'DB관리', 'A', '2018-10-01');
해당 코드는 컬럼명을 생략하고 데이터를 추가하는 쿼리입니다.
INSERT INTO department
VALUES('DBA', 'DB관리', 'A', '2018-10-01')
,('DBA', 'DB관리', 'A', '2018-10-01');
이렇게 ,를 사용해서 INSERT 한번에 여러행(데이터를) 추가할 수 있습니다.
- UPDATE
UPDATE employee
SET phone = '010-1239-1239'
WHERE emp_id = 'S0001';
위 양식을 통해서 데이터의 정보를 수정할 수 있습니다. 위 쿼리문은 emp_id의 컬럼 값이 S0001인 데이터의 phone 컬럼 값을 010-1239-1239로 수정해주는 쿼리입니다.
- DELETE
-- 특정 조건에 맞는 행 지우기
DELETE FROM vacation
WHERE end_date <= '2013-12-31'; -- 모든 행 지우기
DELETE FROM vacation; -- 모든 행 지우기
TRUNCATE TABLE vacation;이게 TRUNCATE가 DELETE 보다 더 빠르게 지웁니다.
- 조인
조인은 여러 테이블을 묶어서 하나의 테이블인 것처럼 사용하여 데이터를 조회하는 방식입니다.
SELECT emp_id, emp_name, employee.dept_id, department.dept_name, phone, email
FROM employee
JOIN department ON employee.dept_id = department.dept_id
WHERE retire_date IS NULL;위 쿼리는 기본적인 조인 방법으로 employee테이블과 department테이블을 조인해서 데이터를 조회하는 방식입니다. ON뒤에는 각 테이블에 서로 연결할 수 있는 컬럼명을 작성해줍니다.(기준이 되는 컬럼명)
SELECT emp_id, emp_name, e.dept_id, d.dept_name, phone, email
FROM employee AS e
JOIN department AS d ON e.dept_id = d.dept_id
WHERE retire_date IS NULL;별칭을 주어 각 테이블에 컬럼을 가져옵니다.
- INNER JOIN
가장 일반적인 JOIN 문 형태입니다.
양쪽 테이블에서 비교되는 값이 일치하는 행만 가져옵니다. 따라서 NULL값이 없습니다.
일반적으로 PK와 FK가 ON 절에서 서로 비교됩니다.
SELECT d.dept_id, d.dept_name, d.unit_id, u.unit_name, d.start_date
FROM department AS d
INNER JOIN unit AS u ON d.unit_id = u.unit_id;- OUTER JOIN
OUTER에는 종류가 3개가 있습니다. LEFT OUTER와 RIGHT OUTER 그리고 FULL OUTER가 있는데 MySQL은 FULL OUTER가 없습니다.
SELECT d.dept_id, d.dept_name, d.unit_id, u.unit_name
FROM department AS d
LEFT OUTER JOIN unit AS u ON d.unit_id = u.unit_id;
- CROSS JOIN
일반적인 비즈니스 응용프로그램에서 사용되지 않습니다.
ON 절이 없어 모든 경우의 수만큼 결과 행을 얻습니다.
대량의 테스트 데이터를 만드는 목적으로 많이 사용됩니다.
SELECT emp_name, dept_name
FROM employee AS e
CROSS JOIN department AS d;직원과 부서간 CROSS JOIN하는 예제 쿼리입니다.
- 하위 쿼리
괄호 안에 또다른 쿼리문이 있는 쿼리문의 형태를 하위쿼리라고 합니다.
대부분 JOIN 문으로 작성해서 같은 결과를 얻을 수 있으며, JOIN 문보다 작성하기가 쉽습니다.
SELECT emp_id, emp_name, dept_id, phone, email, salary
FROM employee
WHERE hire_date = (SELECT MIN(hire_date) FROM employee);
위와 같은 형식으로 사용됩니다.
- 상관 하위 쿼리
내부 쿼리(괄호 안에 있는 쿼리)가 독립적으로 수행되지 못하고 외부 쿼리에서 넘겨진 값을 가지고 내부 쿼리가 수행됩니다.
SELECT emp_id, emp_name, dept_id,
(SELECT dept_name FROM department WHERE dept_id = e.dept_id) AS dept_name,
phone, email, salary
FROM employee AS e
WHERE retire_date IS NULL;
부서 이름을 포함한 근무중인 직원 정보 조회하는 쿼리입니다.
SELECT emp_id, emp_name, dept_id, phone, email, salary
FROM employee AS e
WHERE EXISTS (
SELECT *
FROM vacation
WHERE emp_id = e.emp_id
) AND retire_date IS NULL;EXISTS는 존재여부만 확인해주는 함수입니다.
오늘은 MySQL에 대해 마저 배웠습니다. 내일은 이제 이론의 마지막 장고를 배우는 날로 기대가 됩니다!
※공부하고 있어 다소 틀린점이 있을 수 있습니다. 언제든지 말해주시면 수정하도록 하겠습니다.
※용어에 대해 조금 공부 더 해서 수정하겠습니다.
