MySQL 학습 02 - INNER JOIN, LEFT OUTER JOIN , RIGHT OUTER JOIN, 기본 함수들

1.  INNER JOIN

일반적으로 사용하는 JOIN

 

 

2. LEFT OUTER JOIN , RIGHT OUTER JOIN 

표시 되지 않더라도 보이고 싶을 때는 하나의 테이블 기준으로 합치는 조인을 사용할 수 있다.

 

 

3. COUNT(), SUM(), MAX(), MIN(), AVG()

 SELECT 에서 사용하는 기본 함수들

 

 

4. GROUP_CONCAT(emp.name) : 그룹화한 그룹의 name들을 합쳐서 보여줌

 

 

# a6 DB 삭제/생성/선택
DROP DATABASE IF EXISTS `a6`;
CREATE DATABASE `a6`;
USE `a6`;
# 부서(홍보, 기획)
CREATE TABLE dept (
    id INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    regDate DATETIME NOT NULL,
    `name` CHAR(100) NOT NULL
);

INSERT INTO dept
SET regDate = NOW(),
`name` = '홍보';

INSERT INTO dept
SET regDate = NOW(),
`name` = '기획';

# 사원(홍길동/홍보/5000만원, 홍길순/홍보/6000만원, 임꺽정/기획/4000만원)

CREATE TABLE emp (
    id INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    regDate DATETIME NOT NULL,
    `name` CHAR(100) NOT NULL,
    deptId INT(10) UNSIGNED NOT NULL,
    salary INT(10) UNSIGNED NOT NULL
);

INSERT INTO emp
SET regDate = NOW(),
`name` = '홍길동',
deptId = 1,
salary = 5000;

INSERT INTO emp
SET regDate = NOW(),
`name` = '홍길순',
deptId = 1,
salary = 6000;

INSERT INTO emp
SET regDate = NOW(),
`name` = '임꺽정',
deptId = 2,
salary = 4000;

# 사원 수 출력
SELECT COUNT(*) FROM emp;
# 가장 큰 사원 번호 출력
SELECT MAX(id) FROM emp;

# 가장 고액 연봉
SELECT MAX(salary) FROM emp;
# 가장 저액 연봉
SELECT MIN(salary) FROM emp;

# 회사에서 1년 고정 지출(인건비)
SELECT SUM(salary) FROM emp;

# 부서별, 1년 고정 지출(인건비)
SELECT deptid, SUM(salary) FROM emp GROUP BY deptid;
# 부서별, 최고연봉
SELECT dept.id, dept.name, MAX(salary) FROM emp JOIN dept ON dept.id = emp.deptId GROUP BY deptId;
# 부서별, 최저연봉
SELECT dept.id, dept.name, MIN(salary) FROM emp JOIN dept ON dept.id = emp.deptId GROUP BY deptId;

# 부서별, 평균연봉
SELECT dept.id, dept.name, AVG(salary) FROM emp JOIN dept ON dept.id = emp.deptId GROUP BY deptId;

# 부서별, 사원리스트, 평균연봉, 최고연봉, 최소연봉, 사원수 
## V1(조인 안한 버전)
SELECT emp.deptId AS '부서번호', GROUP_CONCAT(emp.name) AS '사원리스트', AVG(salary) AS '평균연봉',
MAX(salary) AS '최고연봉', MIN(salary) AS '최소연봉', COUNT(*) AS '사원수' FROM emp GROUP BY emp.deptId;

## V2(조인해서 부서명까지 나오는 버전)
SELECT emp.deptId AS '부서번호', dept.name AS '부서이름' ,GROUP_CONCAT(emp.name) AS '사원리스트', AVG(salary) AS '평균연봉',
MAX(salary) AS '최고연봉', MIN(salary) AS '최소연봉', COUNT(*) AS '사원수' 
FROM emp JOIN dept ON emp.deptId = dept.id GROUP BY emp.deptId;

## V3(V2에서 평균연봉이 5000이상인 부서로 추리기)

SELECT emp.deptId AS '부서번호', dept.name AS '부서이름' ,GROUP_CONCAT(emp.name) AS '사원리스트', AVG(salary) AS '평균연봉',
MAX(salary) AS '최고연봉', MIN(salary) AS '최소연봉', COUNT(*) AS '사원수' 
FROM emp JOIN dept ON emp.deptId = dept.id GROUP BY emp.deptId HAVING AVG(salary) >= 5000;

## V4(V3에서 HAVING 없이 서브쿼리로 수행)
SELECT A.deptId AS '부서번호', dept.name AS '부서이름' ,GROUP_CONCAT(A.name) AS '사원리스트', AVG(A.salary) AS '평균연봉',
MAX(A.salary) AS '최고연봉', MIN(A.salary) AS '최소연봉', COUNT(*) AS '사원수' 
FROM (SELECT E.* FROM (SELECT *, AVG(salary) AS 'avgSalary' FROM emp GROUP BY deptid) E  WHERE E.avgSalary >= 5000) A JOIN dept ON A.deptId = dept.id GROUP BY A.deptId ;

SELECT *, AVG(salary) AS 'avgSalary' FROM emp GROUP BY deptid;
SELECT E.* FROM (SELECT *, AVG(salary) AS 'avgSalary' FROM emp GROUP BY deptid) E  WHERE E.avgSalary >= 5000;
# IT부서 추가, IT부서는 사원이 없음
INSERT INTO dept SET NAME = 'it', regDate = NOW();
SELECT * FROM dept;

INSERT INTO emp
SET regDate = NOW(),
`name` = '임꺽정',
deptId = 3,
salary = 4000;

# 부서별 사원수

SELECT emp.deptId, dept.name, COUNT(*) FROM emp JOIN dept ON dept.id = emp.deptId GROUP BY emp.deptId ;

# 부서별 사원수(LEFT JOIN, 부서명 노출, IT부서 노출)
SELECT dept.Id, dept.name, COUNT(emp.deptId) FROM emp RIGHT OUTER JOIN dept ON dept.id = emp.deptId GROUP BY emp.deptId ;
SELECT * FROM emp;
# 부서별, 최고연봉(LEFT JOIN, 부서명 노출, IT부서 노출)
SELECT dept.Id, dept.name, COUNT(emp.deptId), MAX(salary) FROM emp RIGHT OUTER JOIN dept ON dept.id = emp.deptId GROUP BY emp.deptId ;

# 부서별, 최저연봉(LEFT JOIN, 부서명 노출, IT부서 노출)
SELECT dept.Id, dept.name, COUNT(emp.deptId), MIN(salary) FROM emp RIGHT OUTER JOIN dept ON dept.id = emp.deptId GROUP BY emp.deptId ;

# 부서별, 평균연봉(LEFT JOIN, 부서명 노출, IT부서 노출)
SELECT dept.Id, dept.name, COUNT(emp.deptId), AVG(salary) FROM emp RIGHT OUTER JOIN dept ON dept.id = emp.deptId GROUP BY emp.deptId ;