[MySQL/1일차] select, 연산자, 함수, 조인

-- 비교 연산자 --
select * from emp where sal >= 3000;
-- 비교 연산자 (char) --
select * from emp where hiredate <= '1985-01-01 00:00:00';
-- 등가비교 =, !=, <> --
select * from emp where sal <> 3000;
-- 논리 부정 연산자 not -- 
select * from emp where not sal = 3000;
-- in 연산자 --
select * from emp where job in ('MANAGER','CLERK','SALESMAN');
select * from emp where job not in ('MANAGER','CLERK','SALESMAN');
-- between a and b --
select * from emp where sal between 2000 and 3000;
select * from emp where sal not between 2000 and 3000;
-- like --
select * from emp where ename like 'S%'; -- 첫번째 글자가 S --
select * from emp where ename like '_L%'; -- 두번째 글자가 L --
select * from emp where ename like '%AM%';
select * from emp where ename not like '%AM%';
-- is null --
select * from emp where comm is null;

-- 그룹함수 --
-- sum : 중복데이터 제외, 모든 데이터 합산, 모든 데이터 합산 --
select sum(distinct sal), sum(all sal), sum(sal) from emp;
-- count 데이터 개수 --
select count(*) from emp;
select count(*) from emp where deptno=30;
select count(distinct sal), count(all sal), count(sal) from emp;
select count(comm) from emp;
-- max min avg --
select max(sal) as 최대, min(sal) as 최소 from emp where deptno=10;
select max(hiredate) as 최근입사일, min(hiredate) as 오래된입사일 from emp where deptno=20;
select avg(sal) from emp where deptno=30;
-- group by --
select avg(sal), deptno from emp group by deptno;
select deptno, job, avg(sal) from emp group by deptno, job order by deptno, job; 
select deptno, job, avg(sal) from emp group by deptno, job having avg(sal) >= 2000 order by deptno, job;
select deptno, job, avg(sal) from emp where sal <= 3000 group by deptno, job having avg(sal) >= 2000 order by deptno, job;

-- length -- 
select ename, length(ename) from emp where length(ename)>=5;
-- substr --
select job, substr(job, 1, 2), substr(job, 5) from emp;
select job, substr(job, -length(job)), substr(job, -length(job) ,2), substr(job,-3) from emp;
-- instr --
select instr('HELLO, ORACLE!', 'L') AS INSTR_1;
select * from emp where instr(ename, 'L')>0;
-- replace --
select '010-1234-5678' as 'BEFORE',
replace('010-1234-5678','-',' ') as 'AFTER1',
replace('010-1234-5678','-','') as 'AFTER2';
-- concat --
select concat(empno, ename), concat(empno, concat(':',ename))
from emp
where ename='SMITH';

-- round 반올림 --
select 
round(1234.5678) as 'ROUND',
round(1234.5678, 0) as 'ROUND_0',
round(1234.5678, 1) as 'ROUND_1',
round(1234.5678, 2) as 'ROUND_2',
round(1234.5678, -1) as 'ROUND_-1',
round(1234.5678, -2) as 'ROUND_-2';

-- truncate 특정 위치에서 버림 --
select
truncate(1234.5678, 0) as 'TRUNC_0',
truncate(1234.5678, 1) as 'TRUNC_1',
truncate(1234.5678, 2) as 'TRUNC_2',
truncate(1234.5678, -1) as 'TRUNC_-1',
truncate(1234.5678, -2) as 'TRUNC_-2';

-- IF --
select if(100>200, '참이다', '거짓이다');
select ifnull(NULL, 'NULL이군'), ifnull(100,'NULL이군');
select nullif(100,100), nullif(100,200);
select empno, ename, sal, comm, 
ifnull(sal+comm, sal),
ifnull(comm, 0), 
sal+ifnull(comm,0)
from emp;
select empno, ename, comm, if (comm is null, 'X','O')
from emp;

-- case when else end --
select case 10
	when 1 then '일'
    when 5 then '오'
    when 10 then '십'
    else '모름'
end;

-- date --
select empno, ename, str_to_date(hiredate, '%Y-%m-%d') as '입사일'
from emp
where hiredate >= date_format('19810401', '%Y%m%d');

select adddate('2021-01-01', interval 31 day), adddate('2021-01-01', interval 1 month);
select subdate('2021-01-01', interval 31 day), subdate('2021-01-01', interval 1 month);
select addtime('2021-01-01 23:59:59', '1:1:1'), addtime('15:00:00', '2:10:10');

select year(curdate()), month(current_date()), dayofmonth(current_date());
select dayofweek(curdate()), monthname(curdate()), dayofyear(curdate());
select last_day('2020-02-01');
select curdate(), curtime(), now(), sysdate();
  
-- 시스템 정보 함수 --
select current_user(), database();
select * from emp; select found_rows();

-- inner join --
select empno, ename, sal, e.deptno, dname, loc 
from emp e 
inner join dept d 
on e.deptno = d.deptno
where sal >= 3000
order by empno;

-- 비등가 조인 --
select *
from emp e
inner join salgrade s
on e.sal between s.losal and s.hisal;

-- self join --
select e.empno, e.ename, e.mgr, m.empno as 'MGR_NO', m.ename as 'MRG_NAME'
from emp e
inner join emp m
on e.mgr = m.empno;