KOSA fullstack 교육(변환함수, 날짜함수, case문, 그룹함수)

변환함수

 

-- 1. DATE_FORMAT
SELECT date_format(curdate(), '%y-%m-%d') TODAY;
SELECT date_format(curdate(), '%y %m %d') TODAY;
SELECT date_format(curdate(), '%y.%m.%d') TODAY;

-- 2. 
SELECT curdate()-'2025-05-18' 기간; -- 잘못된 값이 출력
SELECT curdate()-str_to_date('25-05-18','%y-%m-%d') 기간; -- 올바른 값이 출력 

-- 3.
SELECT (curdate() - CONVERT('25-05-18', DATE)) TODAY;

-- 사원의 연봉을 구함 (모든 연봉에 해당하는 값을 INTEGER로 변환)
-- SAL * 12 IFNULL(COMM, 33.2)
SELECT ename, sal, CONVERT(sal*12+ifnull(comm,33.2), SIGNED INTEGER) 연봉
FROM emp;

-- 4.
SELECT (curdate() - CAST('25-05-18' AS DATE)) TODAY;

-- FORMAT 함수 
-- emp테이블에서 사원들의 월급과 연봉을 출려기ㅣ 숫자 3자리수마다 ,를 찍어서 출력
SELECT ename 사원이름, FORMAT(sal, 0) 월급, FORMAT(sal*12+ifnull(comm,0),0) 연봉
FROM emp;

SELECT ename, hiredate, date_format(hiredate, '%Y년 %m월 %d일') t_kor
FROM emp
WHERE deptno = 20
ORDER BY hiredate desc;

-- ifnull()
-- 사장은 상사가 없기 때문에 mgr값을 없음 으로 바꾸고, 컬럼명의 알리아스를 CEO로 지정 
SELECT ename 이름, job CEO, ifnull(convert(mgr,char), '없음') MGR
FROM emp
WHERE job = 'PRESIDENT';

-- case문
SELECT 
  empno, ename, sal, job,
  CASE job 
    WHEN 'ANALYST' THEN sal * 1.1
    WHEN 'CLERK' THEN sal * 1.2
    ELSE sal
  END AS salary
FROM emp;

-- 모든 직원의 이름, 업무, 급여, COMM, 보너스를 검색
-- 보너스는 업무가 MGR인 직원에 적용 급여의 15%를 책정
-- 그 외 업무의 직원은 급여의 13%
SELECT ename, job, sal, comm,
CASE job
	WHEN 'MANAGER' THEN CONVERT(sal*0.15, SIGNED INTEGER)
    ELSE CONVERT(sal*0.13, SIGNED INTEGER)
END AS bonus
FROM emp;

 

workshop04

-- ##################  변환 함수 실습 ######################
use kosa;
-- 1) 모든 직원의 이름과 입사일을 ‘1996-5-14’의 형태로 출력 하라.
select ename 이름, date_format(hiredate, '%Y-%m-%d') 입사일
from emp;

-- 2) 모든 직원의 이름과 입사년과 입사월만 '1981.05' 형식으로 출력하라
SELECT ename 이름, date_format(hiredate, '%Y.%m') 입사년월
FROM emp;

-- 3) 모든 직원의 번호, 이름, 급여를 출력하라. 단, 급여앞에 화폐표시($), 그리고 천단위(,)표시, 소수점이하 2자리가 표시되도록 하라.
SELECT empno 번호, ename 이름, concat('$',FORMAT(sal,2))급여
FROM emp;

-- ##################  일반 함수 실습 ######################

-- 1)  모든 직원의 이름, 급여, 커미션을 출력하라. 단, comm이 null이면 '없음'으로 출력하라.
SELECT ename 이름, sal 급여, ifnull(convert(comm, char),'없음') 커미션
FROM emp;

-- 2) 모든 직원의 이름, 직무, 급여, 커미션, 보너스를 출력하라. 
-- 보너스는 직무가  MANAGER이면 급여의 150%, 그외 직원은 급여의 130% 이다.(case ~ else 사용)
SELECT ename 이름, job 직무, sal 급여, ifnull(comm, 0) 커미션, 
	CASE job
		WHEN 'MANAGER' THEN sal*1.5
        ELSE sal*1.3
	END AS bonus
FROM emp;

-- 3) mgr2 컬럼을 하나 더 만들어서
-- mgr이 null이면 상위담당자/ null이면 mgr값을 가지도록 한다.(case ~ else 사용))
SELECT ename 이름, 
CASE
	WHEN mgr is null THEN '상위담당자'
    ELSE mgr
END AS mgr2
FROM emp;

/* 그룹함수 */
SELECT count(*) 직원수 FROM emp;
SELECT * FROM emp; -- 이렇게 했을 때의 행의 수를 반환
-- -1은 데이타의 마지막을 의미, 성능상 더 좋다
SELECT count(-1) 직원수 FROM emp;
-- 그룹함수는 집계시 null 값을 건너뛴다
-- empno 기본키 제약조건
-- not null + unique
SELECT count(empno) FROM emp;

SELECT count(empno) 총인원수, avg(sal) 평균급여, MIN(sal) 최소급여, MAX(sal) 최대급여, SUM(sal) 급여합
FROM emp;

-- emp 테이블에서 입사한 지 가장 오래된 사원과 가장 최근에 입사한 사원을 검색 
SELECT MIN(hiredate), MAX(hiredate) FROM emp;

-- emp 테이블에서 부서번호가 10번이거나 20번인 사원 수를 검색
SELECT count(empno)
FROM emp
WHERE deptno IN(10,20);

-- emp테이블에서 등록된 사원 수c1 comm이 null이 아닌 직원수 c2,
-- 직원들의 comm의 평균값을 가각 구하시오 
-- comm의 평균값을 구하는 것이기에 직원 전체인원수로 나눠야한다.
SELECT count(-1) c1, count(comm) c2, avg(ifnull(comm,0)) c3
FROM emp;

-- emp 테이블에 등록되어져있는 부서의 갯수, 업부의 개수 를 검색 
SELECT count(DISTINCT(deptno)) 부서수,
		count(DISTINCT(job)) 업무수
FROM emp;

-- 부서별 평균 급여를 조회
-- 그룸함수에 포함되지 않는 컬럼이 select 뒤에 명시될 수 없다.
SELECT deptno, round(avg(sal))
FROM emp
GROUP BY deptno;

-- 서버 실행 순서
-- FROM --> WHERE --> GROUP BY -> SELECT
-- 실행 순서를 감안하면 group by 절 뒤에 alias사용 못한다
-- 그러데 mysql은 group by 절 뒤에 별칭붙여도 실행된다. 

SELECT deptno DNumber, round(avg(sal)) AvgSalary
FROM emp
WHERE deptno != 10
GROUP BY DNumber
ORDER BY 1;

-- 입사년도별 사원수를 출력(2가지 방법)
SELECT year(hiredate) 입사년도, count(-1) 사원수
FROM emp
GROUP BY 입사년도
ORDER BY 1;

SELECT date_format(hiredate, '%Y') 입사년도, count(empno) 사원수
FROM emp
GROUP BY 입사년도
ORDER BY 1;