[MySQL] 250519 수업예제

SELECT 
    ename, sal, job
FROM
    emp
WHERE
    ename LIKE '_A%'
ORDER BY ename;

SELECT 
    ename, sal, job
FROM
    emp
WHERE
    ename LIKE '%A%'
ORDER BY job, sal desc;

-- emp 사원중에서 S로 시작하는 이름을 가진 사원을 검색
select ename, job
from emp
where ename like 'S%';

-- emp사원중에서 80년도에 입사한 사원을 검색
select ename, hiredate
from emp
where hiredate BETWEEN '1980-01-01' and '1980-12-31';
-- where hiredate like '%80%';

/* IN 여러개 중에서 어느 하나와 일치하는지 사원번호가 7369 7521 7782번인 사원*/
select * from emp where empno = 7369;
select * from emp where empno = 7521;
select * from emp where empno = 7782;

-- step 2
select * from emp
where empno = 7521 or empno=7369 or empno = 7782;

-- step 3
select * from emp
where empno in(7369, 7521, 77820);

-- 사원번호가 7369번 아니고, 7782번 아닌 사원을 검색
select * from emp
where empno != 7369 and empno != 7521 and empno!=7782;

-- step 5
select * from emp
where empno not in(7369, 7521, 7782);

SELECT 
    empno, ename, job, sal
FROM
    emp
WHERE
    sal >= 2000
        AND job NOT IN ('salesman' , 'president')
order by 3 desc, 4 desc;

-- EMP테이블에서 부하직원을 거느리고 있는 사원을 검색
select empno, ename, job from emp
where empno not in (select ifnull(mgr, 0) from emp);

select empno, ename, job, LOWER(job), upper(job)
from emp
where deptno = 10;

select empno, ename, job, CONCAT(empno, ename)ename, 
CONCAT(ename,empno) e_empno, CONCAT(ename,job)e_job
from emp
where deptno = 10;

select concat('Good', 'Morning') from dual; -- Oracle 방식
select concat('Good', ' ' , 'Morning') Message; -- MySQL은 dual 필요없다

-- concat을 사용해서 
-- Smith is a CLERK 

select concat((select ename from emp), ' is a ', (select job from emp)) "who`s jop";
SELECT 
    ename, job, CONCAT(ename, 'is a', job) Message
FROM
    emp;
    
select ename, substr(ename, 1) from emp where job = 'president';
select substr('HelloWorld', 6);
select substr('HelloWorld', 6, 3);
select substr('HelloWorld', -4, 2);

-- emp테이블에서 사원들의 업무중에서 salesman인 업무를 하는 사원중에서
-- 사원이름이 'R'로 끝나는 사원의 정보를 출력( 2가지 방법 )
-- 1번째
SELECT 
    empno, ename, job
FROM
    emp
WHERE
    job = 'SALESMAN' AND ename LIKE '%R';

-- 2번째
SELECT 
    ename, job
FROM
    emp
WHERE
    job = 'SALESMAN'
        AND SUBSTR(ename, - 1, 1) = 'R';

-- emp테이블에서 salesman 업무가 sales로만 출력되도록
SELECT 
    ename, job, SUBSTR(job, 1, 5) job2
FROM
    emp
WHERE
    job = 'salesman';

select hiredate from emp where ename = 'ALLEN';

select substr(hiredate, 1, 4) 년도, substr(hiredate, 6, 2) 월, substr(hiredate, -2, 2) 일
from emp
where ename = 'ALLEN';

-- pad
select lpad('abc', 6, '*');
select rpad('abc', 6, '*');
select rpad('abc', 6);      -- MySQL에서는 적용안됨.
select empno, deptno, LPAD(deptno, 6, ' ') 부서번호 from emp;

-- trim
select trim('               James Gosling is Good              ') message;
select trim(replace('             James Gos     ling is Good             ', ' ', '')) message;

-- round
-- 소숫점이하 2자리까지 표시!!!!
-- 소숫점이하 3자리에서 반올림이 진행!!
select round(45.923, 2);
select round(45.923, 0);     -- 디폴트
select round(45.923);

-- truncate
-- 소숫점이하 첫번째 자리까지 표시
select truncate(19.412, 1);
select truncate(19.412, 2);
select truncate(19.412, 0);
select truncate(19.412);    -- 반드시 자릿수를 지저해야 한다. 디폴트가 없다.

-- ceiling 
-- 무조건 올림, 정수만 반환
select ceil(12.11) a, ceil(12.59) b, ceil(19.0) c;

-- FLOOR
select floor(12.11) a, floor(19.0) b, floor(19.9) c;

select greatest(1,2,3) 큰수;
select least(1,2,3) 작은수;

SELECT 
    ename, sal, if(SIGN(sal - 2975) >=0, "고액연봉", "평균급여") 연봉에관해서
FROM
    emp
WHERE
    deptno = 20;

-- 년 월 일(오늘날짜)
select curdate();
select current_date();
select date('2025-05-19');
-- 년 월 일 시분초
select sysdate();
select now();

select substr(curdate(), 1, 4) Year;
select year(curdate()) 연도;
select month(curdate()) 월;
select day(curdate()) 일;

-- 오늘 요일을 출력해보세요
select dayname(curdate()) 일;
select weekday(curdate()) 일;
-- 월요일(0), 화요일(1), 수요일(2),,,,, 일요일(6) 
SELECT HOUR(now()) 시;
SELECT MINUTE(sysdate()) 분;
SELECT SECOND(sysdate()) 초;

-- 내일 (curdate() + 1)
SELECT CURDATE() + 1 Tomorrow;   -- 날짜 + 숫자 = 날짜
SELECT CURDATE() - 1 Yesterday;   -- 날짜 - 숫자 = 날짜

-- 소유나님이 지금까지 살아온 생애
select curdate() - date('2002-09-14');    -- 날짜 - 날짜 = 숫자
select datediff(curdate(), '2002-09-14') 유나의일생;
select datediff(curdate(), '1993-02-16');

-- step 1.
select curdate()- '2002-09-14' 기간;   -- 1

-- step 2.
select curdate() - str_to_date('2025-05-18', '%Y, %m, %d') 기간;

-- step 3.
select datediff(curdate(), '2025-05-18') 기간;

-- emp 테이블에서 MANAGER 업무에 관해서 입사한 날부터
-- 오늘 현재까지 근무한 일수를 출력
-- 알리야스는 근무일수로
SELECT 
    ename,
    job,
    DATEDIFF(CURDATE(), hiredate) 근무일수,
    ROUND(DATEDIFF(CURDATE(), hiredate) / 7) Weekends
FROM
    emp
WHERE
    job = 'MANAGER';

SELECT DATE_ADD(CURDATE(), INTERVAL 100 DAY) 100일후;
SELECT DATE_SUB(CURDATE(), INTERVAL 100 DAY) 100일;

-- 지금시간으로 부터 10시간 후
SELECT DATE_ADD(NOW(), INTERVAL 10 HOUR) 10시간후;

-- emp테이블에서 사원들이 입사한 월을 검색... 입사월로 지정(2가지)
-- MONTH(), SUBSTR()사용
select ename, month(hiredate) 입사월
from emp;

select ename, substr(hiredate, 6,2) 입사월
from emp;

select hiredate, substr(hiredate, -5, 2) 입사월 from emp;

-- emp테이블에서 5월에 입사한 사원의 이름과 입사년도, 입사월, 업무를 검색
-- 입사년도순으로 정렬, 월로 다시 정렬
SELECT 
    ename, YEAR(hiredate) 입사년도, MONTH(hiredate) 입사월, job
FROM
    emp
WHERE
    MONTH(hiredate) = 5
ORDER BY 2 , 3;

-- step 2.
SELECT 
    ename 이름, YEAR(hiredate) 입사년도, MONTH(hiredate) 입사월, job 업무
FROM
    emp
WHERE
    입사월 = 5
ORDER BY 2 , 3;

/* 변환함수*/
-- 1. DATE_FORMAT
select date_format(curdate(), '%Y %m %d') TODAY; -- 2025 05 20
select date_format(curdate(), '%Y.%m.%d') TODAY; -- 2025.05.20

-- 2.
SELECT CURDATE() - '2025-05-18' 기간; -- 잘못된 값이 출력
SELECT CURDATE() - STR_TO_DATE('2025-05-18', '%Y-%m-%d') 기간; -- 올바른 값 출력
SELECT curdate() - convert('2025-05-18', date) 기간;
SELECT curdate() - cast('2025-05-18' AS date) 기간;

-- emp 사원 월급, 연봉 출력, 숫자 3자리수마다 , 찍어서 출력
SELECT 
    ename, FORMAT(sal, 0) 연봉, FORMAT(sal * 12 + ifnull(comm, 0), 0) 급여
FROM
    emp;
    
-- 사원의 연봉을 구함 (모든 연봉에 해당하는 값을 INTEGER로 변환)
-- SAL *12 + IFNULL(COMM, 33.2)

-- step 1
Select ename, sal, sal*12+ifnull(comm, 33.2) 연봉
from emp;

-- step 2
Select ename, sal, CONVERT(sal*12+ifnull(comm, 33.2), SIGNED INTEGER) 연봉
from emp;

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

/* IFNULL() */
SELECT 
    ename, mgr
FROM
    emp
where ename = 'KING';

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

-- 예제8.
SELECT empno, ename, sal, job,
CASE job when 'ANAYLYST' THEN sal*1.1
when 'CLERK' THEN sal*1.2
        when 'MANAGER' THEN sal*1.3
        when 'PRESIDENT' THEN sal*1.4
        when 'SALESMAN' THEN sal*1.5
        ELSE sal
END AS 인상된급여
FROM emp;
    
    -- 모든 직원의 이름, 업무, 급여, comm, 보너스 를 검색
-- 보너스는 업무가 MANAGER인 직원에 적용 급여의 15%를 책정
    -- 그 외 업무의 직원은 급여의 13%, CASE~ELSE사용
    select ename, job sal, comm,
    case job when 'MANAGER' THEN sal*0.15
ELSE sal*0.13
            end as 보너스
    from emp;
    
      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 보너스
    from emp;