230414 풀스택 32일차 - DB

dbeaver 사용하면 편한 기능이 많다 커뮤니티버전 치곤 좋다

datagrip erd도 강력하고 전반적으로 강력함. 회사에서

SET SESSION sql_mode = (SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''));
SET sql_mode = (SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''));


DROP DATABASE IF EXISTS a6;

CREATE DATABASE a6;

USE a6;

CREATE TABLE dept (
        id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
        regDate DATETIME NOT NULL,
        `name` CHAR(100) NOT NULL UNIQUE        
);
DESC dept;

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

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

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

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 * FROM emp;

SELECT COUNT(*) FROM emp; ###### 내가 여기서 뽑은 총 로우의 갯수

SELECT MAX(salary) FROM emp; ###### 해당 칼럼의 최댓값

SELECT MIN(salary) FROM emp; ###### 해당 칼럼의 최소값

SELECT SUM(salary) FROM emp; ###### 해당 칼럼의 합

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


SELECT deptId, MAX(salary)
FROM emp
GROUP BY deptId; ###### 부서별 최고연봉

# 부서별, 최저연봉
SELECT deptId, MIN(salary)
FROM emp
GROUP BY deptId;

# 부서별, 평균연봉
SELECT deptId, AVG(salary)
FROM emp
GROUP BY deptId;


# 부서별, 부서명, 사원리스트, 평균연봉, 최고연봉, 최소연봉, 사원수
## V1(조인 안한 버전)

### IF문 버전
SELECT IF(deptId = 1, '홍보', '기획') AS `부서명`,
GROUP_CONCAT(`name` ORDER BY id DESC SEPARATOR ', ') AS `사원리스트`,
CONCAT(TRUNCATE(AVG(salary), 0), '만원') AS `평균연봉`, # 소수점 짤라주는 명령어
CONCAT(MAX(salary), '만원') AS `최고연봉`,
CONCAT(MIN(salary), '만원') AS `최소연봉`,
CONCAT(COUNT(*), '명') AS `사원수`
FROM emp
GROUP BY deptId;


### CASE문을 사용하고, GROUP_CONCAT의 결과에서 중복제거도 처리도 한 버전
SELECT CASE
WHEN deptId = 1 # 1일때
THEN '홍보' # 이렇게 출력하고
WHEN deptId = 2 # 2일때
THEN '기획' # 이렇게 출력하고
ELSE '무소속' # 아무것도 해당되지 않으면 이렇게 출력해라
END AS `부서명`,
GROUP_CONCAT(DISTINCT `name` ORDER BY id DESC SEPARATOR ', ') AS `사원리스트`,
TRUNCATE(AVG(salary), 0) AS `평균연봉`,
MAX(salary) AS `최고연봉`,
MIN(salary) AS `최소연봉`,
COUNT(*) AS `사원수`
FROM emp
GROUP BY deptId;


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


## V3(V2에서 평균연봉이 5000이상인 부서로 추리기)
SELECT D.name AS 부서,
GROUP_CONCAT(E.name) AS 사원리스트,
TRUNCATE(AVG(E.salary), 0) AS 평균연봉,
MAX(E.salary) AS 최고연봉,
MIN(E.salary) AS 최소연봉,
COUNT(*) AS 사원수
FROM emp AS E
INNER JOIN dept AS D
ON E.deptId = D.id
GROUP BY E.deptId
HAVING `평균연봉` >= 5000;


SELECT * # 이중쿼리 (쿼리안에 쿼리로..고급기능)
FROM (
    SELECT D.name AS `부서명`,
    GROUP_CONCAT(E.`name`) AS `사원리스트`,
    TRUNCATE(AVG(E.salary), 0) AS `평균연봉`,
    MAX(E.salary) AS `최고연봉`,
    MIN(E.salary) AS `최소연봉`,
    COUNT(*) AS `사원수`
    FROM emp AS E
    INNER JOIN dept AS D
    ON E.deptId = D.id
    WHERE 1
    GROUP BY E.deptId
) AS D
WHERE D.`평균연봉` >= 5000;

######inner join