Day28 Database-1

221130

📌 5-3. 단일행함수 – 변환함수, 제어흐름함수, 시스템정보함수

📂 변환함수

DATE_FORMAT(날짜, 형식) : 날짜를 형식에 맞게 출력하는 함수

select date_format(now(), '%Y-%M-%d') as "Now";

select date_format(now(), '%Y/%M/%d %H:%i:%s') as "Now";

select employee_id, date_format(hire_date, '%Y-%M-%d %W') as "입사일" from employees;

CAST(값 AS 데이터타입) : 값을 지정된 데이터타입으로 변환하는 함수

select cast("123" as signed), cast("-123.45" as signed);
select cast("123" as unsigned), cast("123.45" as unsigned);
select cast('2022/03/18' as date) as '날짜';
select cast('2022@03@18' as date) as '날짜';
select cast('2022-01-02 21:24:33.123' as date) as "DATE",
	   cast('2022-01-02 21:24:33.123' as time) as "TIME",
       cast('2022-01-02 21:24:33.123' as datetime) as "DATETIME";

---

📂 제어 흐름 함수

IF(논리식, 참일 때 값, 거짓일 때 값)
: 논리식이 참이면 참일 때 값을 출력하고 거짓이면 거짓일 때 값을 출력하는 함수

select if(100>200, '참이다','거짓이다') as "결과";
select employee_id, salary, if(salary>10000, '1등급','2등급') as "급여등급" 
from employees;

IFNULL(수식1, 수식2)
: 첫번쨰 수식1이 NULL이 아니면 수식1이 반환되고, 수식1이 NULL이면 수식2가 반환되는 함수
  null값을 실제값으로 반환해 주는 함수

select ifnull(null,'널이군요') as "결과1", ifnull(100,'널이군요') as "결과2";

select employee_id, last_name, salary, commission_pct, 
		ifnull(commission_pct,0)  as "커미션"
from employees;

NULLIF(수식1, 수식2) : 수식1과 수식2가 같으면 NULL을 반환하고, 다르면 수식1을 반환하는 함수

select nullif(100,100) as "결과1", nullif(100,200) as "결과2";

select employee_id, first_name, last_name,
		nullif(length(first_name), length(last_name)) as "결과"
from employees;

        (좌변)     =     (우변)
CASE 비교값 WHEN 값1 THEN 결과1
                 WHEN 값2 THEN 결과2
                  ... ...
                 ELSE 기본값
END

select case 10 when 1 then '일'
			   when 5 then '오'
               when 10 then '십'
			   else '모름'
end as "case예제";

select employee_id, last_name, department_id,
		case department_id when 10 then '부서 10'
						   when 50 then '부서 50'
                           when 100 then '부서 100'
                           when 150 then '부서 150'
                           when 200 then '부서 200'
                           else '기타 부서'
		end as "부서 정보"
from employees;

---

📂 시스템 정보 함수

USER( )
CURRENT_USER( )
SESSION_USER( ) : 현재 사용자 정보를 반환하는 함수

select user(), current_user(), session_user();

DATABASE( )
SCHEMA( ) : 현재 데이터베이스 또는 스키마 정보를 반환하는 함수

select database(), schema();

VERSION( ) : 현재 MySQL 버전을 반환하는 함수

select version();

---

✏️ <연습문제>

1. employees 테이블로부터 사원들의 last_name과 commission_pct를 출력하되
   커미션을 받는 사원은 자신의 커미션 비율을 출력하고, 커미션을 받지 않는 사원은 "No Commission"을 출력하는 구문을 작성하시오.

2. employees 테이블로부터 JOB_ID 값을 기반으로 모든 사원의 등급을 표시하는 query를
   작성하시오.

---

📌 6. 그룹함수와 그룹화

그룹함수란? 행 그룹을 조작해서 하나의 결과값을 반환하는 함수

• 그룹함수 종류 : min, max, sum, avg, count
• 그룹함수 특징 : null값은 제외하고 작업함.

---

📂 그룹함수

MIN(행그룹) : 행그룹에서 최소값을 반환해주는 함수 모든 데이터타입에 사용 가능함.
MAX(행그룹) : 행그룹에서 최대값을 반환해주는 함수 모든 데이터타입에 사용 가능함.

select min(salary) as "최소급여", max(salary) as "최대급여"
from employees;

select min(hire_date) as "가장 오래된 일사일",
	   max(hire_date) as "가장 최근 입사일"
from employees;

select min(last_name) as "name1", max(last_name) as "name2"
from employees;

SUM(행그룹) : 행그룹의 합계를 구해주는 함수 숫자 데이터타입에만 사용 가능함.
AVG(행그룹) : 행그룹의 평균을 구해주는 함수 숫자 데이터타입에만 사용 가능함.

select sum(salary) as "급여합계", avg(salary) as "평균급여"
from employees;

select sum(salary) as "급여합계", avg(salary) as "평균급여"
from employees
where job_id like '%REP%';

✍ employees 테이블에서 전체 직원의 커미션 평균을 출력하시오.

select avg(commission_pct) as "comm_avg"
from employees;

❌ [오답] 전직원의 커미션 평균 x. null값은 자동으로 제외됨
-> null값을 0으로 바꿔서 계산!

select avg(ifnull(commission_pct,0)) as comm_avg
from employees;

⭕ [정답] 전체 사원들의 커미션 평균

COUNT(*) : 행그룹에서 행의 개수를 반환해주는 함수
                            모든 컬럼의 조합값을 기준으로 count함 (null값 포함 O, 중복값 포함 O)
COUNT(행그룹) : 행그룹에서 행의 개수를 반환해주는 함수
                                        특정 컬럼을 기준으로 count함 (null값 포함 X, 중복값 포함 O)
COUNT(DISTINCT 행그룹) : 행그룹에서 행의 개수를 반환해주는 함수
                중복값을 제외하고 특정 컬럼을 기준으로 count함 (null값 포함 X, 중복값 포함 X)

select count(*)
from employees;					-- 전 직원 수 / 107
select count(commission_pct)
from employees;					-- 커미션을 받는 직원 수 / 35
select count(*)
from employees
where department_id <=80;		-- 80번 이하 부서에 소속된 직원 수 / 96
select count(commission_pct)
from employees
where department_id <=80;		-- 80번 이하 부서에 소속된 직원 중 커미션을 받은 직원 수 / 34
select count(department_id)
from employees;					-- 부서가 있는 직원의 수 / 106
select count(distinct department_id)
from employees;					-- 직원들이 소속된 부서의 개수 / 11

---

📂 GROUP BY절

• GROUP BY절 : 테이블 안에서 또다시 그룹화를 시킴
                    테이블 안에서 또다시 작은 그룹화를 나눠서 그룹함수를 적용함

[문법] select 컬럼1, 컬럼2, 컬럼3
         from 테이블명
         [where 조건문]
         [group by 컬럼명]
         [order by 컬럼명 [asc | desc]];

💥 [GROUP BY절 사용시 주의사항]
select절의 컬럼리스트 중 그룹함수에 포함된 컬럼과 그룹함수에 포함되지 않은 컬럼이
함께 출력되려면 적어도 그룹함수에 포함되지 않은 컬럼은 빠짐없이 group by절에
포함되어야 문법 오류가 발생되지 않음!!!

select sum(salary)
from employees;		-- 전 직원의 급여 합계	/ 691400

select department_id, sum(salary)
from employees
group by department_id;		-- 부서별 급여 합계

select department_id, job_id, sum(salary)
from employees
group by department_id, job_id
order by department_id;		-- 부서 내 업무별 급여 합계

select department_id, job_id, sum(salary)
from employees
where department_id>40
group by department_id, job_id
order by department_id;

✍ 부서별 사원의 수를 출력하시오

✍ 부서 내 업무별 사원의 수를 출력하시오

✍ 매니저별 부하직원의 수를 출력하시오

---

📂HAVING절

• 조건별 : WHERE절 - 행을 제한하는 조건문을 작성하는 곳
            : HAVING절 - 행그룹을 제한하는 조건문을 작성하는 곳
                               그룹함수가 포함된 조건을 작성하는 곳

[문법] select 컬럼1, 그룹함수(컬럼2)
         from 테이블명
         [where 조건문]
         [group by 컬럼1]
         [having 그룹함수가 포함된 조건문]
         [order by 컬럼명 [asc | desc]];

select job_id, sum(salary) as payroll
from employees
where job_id not like '%REP%'
group by job_id
having sum(salary)>13000
order by sum(salary);

---

✏️ <연습문제>

1. employees 테이블로부터 전체 사원들의 커미션 평균을 출력하는 구문을 작성하시오.
   단, 소수점 둘째자리까지 반올림해서 출력하시오.

2. employees 테이블로부터 업무(job_id)별 최대 급여(Maximum), 최소 급여(Minimum),
   급여의 합계(Sum), 평균 급여(Average)를 출력하시오.

3. employees 테이블로부터 동일 업무(job_id)를 수행하는 직원 수를 출력하는 구문을
   작성하시오.

4. employees 테이블로부터 매니저를 알 수 없는 사원은 제외하고 매니저별로 그룹화하여 매
   니저별 최소 급여를 출력하되 최소 급여가 $6000 이상인 그룹만 출력하시오.
   또한 최소 급여를 기준으로 내림차순으로 정렬하여 출력하시오.

5. employees 테이블에서 최고 급여와 최저 급여의 차이를 출력하는 구문을 작성하시오.

6. employees 테이블로부터 사원의 총 수와 1995년, 1996년, 1997년, 1998년에 채용된
   사원의 수를 표시하는 구문을 작성하시오.

3-p.28 문제6번