-- 각 부서별 인원수, 최대 급여
select min(sal), max(sal), sum(sal)/count(sal) as 평균, round(avg(sal),1), count(sal) from emp;
group by: 칼럼을 그룹으로 묶는다. 여러 칼럼으로 그룹을 묶을 수 있다.
having: group by 이후 조건 따지기
select deptno 부서번호, min(sal) min, max(sal) max, round(avg(sal),2) as 평균, count(sal) '사원 수'
from emp
group by deptno order by deptno;
select*from emp group by deptno; -- 부서번호를 그룹으로 했는데 모든 칼럼을 출력하려고 해서 에러
select에 칼럼을 적으려는 경우, 그룹화 한 칼럼만 적을 수 있다.
그룹화하지 않은 다른 칼럼을 적을 경우 에러가 난다.
문제)
-- 문) 부서별 근무 인원 출력 / 출력 예) 10 3명
select deptno, concat(count(deptno), '명') as 인원 from emp group by deptno order by deptno;
select deptno, concat(count(*), '명') as 인원 from emp group by deptno order by deptno;
count(*): 모든 칼럼을 비교해서 null값이 아닌 행의 수를 측정한다.
예제)
-- 10번 부서를 제외한 부서별 근무 인원 출력
select deptno, concat(count(*), '명') as 인원 from emp
where deptno<>10 group by deptno order by deptno;
-- 10번 부서 제외한 부서, 직급 별 근무 인원, 2명 이상
select deptno, job, concat(count(*), '명') as 인원 from emp
where deptno<>10 group by deptno, job having count(*)>1 order by deptno, job;
문제)
-- 문) 부서 내 직급별 인원수와 최대급여 검색, 부서번호 오름차순 검색
select deptno '부서번호', job '직급', count(*) '인원 수', max(sal) '최대급여'
from emp group by deptno, job order by deptno;
select deptno '부서번호', job '직급', count(*) '인원 수', max(sal) '최대급여'
from emp group by deptno, job with rollup;
with rollup: count, min 등 select에서 사용한 칼럼 그룹 단위로 집계 값을 출력한다.
단, order by와 같이 사용하지 못한다.
-- 부서별 입사일 1981-01-01 이후 입사한 사원이 2명 이상인 경우
select deptno '부서', count(*) '인원' from emp
where hiredate>'1981-01-01' group by deptno having count(*)>1;
hiredate가 날짜이기 때문에 문자열로 '1981-01-01'이라고만 해도 비교가 된다.
부서 별 2명 이상은 그룹을 묶은 후 비교를 할 수 있기 때문에 having으로 조건을 적는다.
-- 급여가 1000이상인 사원 중에서 부서별 중위급여가 2500미만이고
-- 평균 급여가 1200~2000 사이인 최대급여, 최소급여, 중위급여, 평균급여 검색
select deptno '부서', max(sal), min(sal),
round((max(sal)+min(sal))/2,2) '중위급여',
round(avg(sal),2) '평균급여', count(*) '인원'
from emp
where sal>=1000
group by deptno
having (max(sal)+min(sal))/2<2500 and avg(sal) between 1200 and 2000;
-- job이 salesman이 아닌 사원 중에서 job별로 급여 합계가 5000이상인 직급, 급여합계를 검색
-- 단, 급여 합계 내림정렬
select job '직급', sum(sal) '급여합계' from emp
where job<>'salesman' group by job having sum(sal)>=5000 order by '급여합계' desc;
상관(연관) 서브쿼리: 메인쿼리와 서브쿼리가 관계를 맺음, 서브쿼리 내에 메인쿼리의 칼럼 존재
처리 순서: 메인쿼리 -> 서브쿼리 -> 메인쿼리
예제)
-- jones가 근무하는 부서 이름 검색
select dname from dept where deptno=(select deptno from emp where ename='jones');
where에서 서브쿼리로 'jones'의 'deptno'를 검색(선택)하고 그것을 조건으로 사용했다.
문제)
-- 평균급여보다 많이 받는 사원 검색
select ename, sal from emp where sal>(select avg(sal) from emp);
-- jones보다 급여를 많이 받는 사원 검색 / 단, 급여 내림차 순
select ename, sal from emp where sal>(select sal from emp where ename='jones')
order by sal desc;
예제)
-- 10번 부서에서 근무하는 사원의 이름과 10번 부서의 부서명, 부서번호를 출력
select d.deptno 부서번호, d.dname 부서명, emp.ename '사원 명'
from (select deptno, dname from dept where deptno=10) d, emp order by ename;
select*from emp e, (select deptno, dname from dept where deptno=10) d where e.deptno=d.deptno;
-- 10번 부서와 20번 부서 사원 중에서 직급이 같은 사원들의 이름, 부서번호, 직급을 검색
select e1.job, e1.deptno, e1.ename, e2.deptno, e2.ename
from (select ename, deptno, job from emp where deptno=10) e1,
(select ename, deptno, job from emp where deptno=20) e2
where e1.job=e2.job;
-- 직급별로 직급, 최대급여를 출력하되, 최대 급여가 salseman의 최대급여보다 큰 그룹만 출력
select job, max(sal) maxsal from emp group by job
having max(sal)>(select max(sal) from emp where job='salesman');
-- blake와 같은 부서에 있는 사원 이름, 입사일 출력 / 단, 본인 제외
-- blake가 서로 다른 부서에 있을 수 있다.
select ename, hiredate
from emp
where deptno in(select deptno from emp where ename='blake') and ename<>'blake';
'blake'가 동명이인으로 서로 다른 부서에 있을 수 있다는 것을 가정한 조건이 붙었다.
in을 통해 여러 조건 중 하나를 만족하면 조건에 충족하는 것으로 하고 서브쿼리를 통해 'blake'의 'deptno'를 검색/선택해왔다.
예제)
select (select dname from dept d where d.deptno = e.deptno) as dname, empno, ename
from emp e;
~ all: 서브쿼리의 모든 결과에 대해 ~하다.
~ any: 서브쿼리의 하나 이상의 결과에 대해 ~하다.
select ename, sal, deptno from emp
where deptno = any(select deptno from emp where sal>=3000);
where deptno in(select deptno from emp where sal>=3000); < 해당 코드와 두번째 코드가 동일하다.
in도 any도 괄호 안의 코드(값) 중 하나라도 만족하면 조건에 충족한다.
select ename, sal from emp
where sal>all(select sal from emp where deptno=30);
where sal>(select max(sal) from emp where deptno=30); < 해당 코드와 두번째 코드가 동일하다.
inner(내부) join: 양 쪽(테이블) 모두에 값이 있는 행(not null) 반환 / inner 생략 가능
-- 사원 이름, 호봉, 부서명 검색
select d.deptno, d.dname, e.ename, e.sal
from emp e join dept d on d.deptno=e.deptno
join salgrade s on e.sal between s.losal and s.hisal;
on에는 테이블을 합하는 join의 조건을 적는다.
해당 코드에서는 dept를 emp에 합할 때, 동일하게 가지고 있는 열인 deptno를 이용하여 합한다.
전체 피드백
밥 먹고 나서 집중력이 급격히 떨어지는 바람에 활용하는 부분에서 많이 헤맨 것 같다.
초반에는 쉬웠는데 역시 갈수록 많이 어려워지는 것 같다. db랑 자바랑 연결할 때도 크게 헤맬 것 같은 기분이 든다.
-- 급여가 1000~2000 사이가 아닌 사원
select*from emp where !(sal between 1000 and 2000);
-- 직급이 clerk, manager, analyst인 사원 검색
select*from emp where job in('clerk', 'manager', 'analyst');
-- 직급이 clerk, manager, analyst이 아닌 사원 검색
select*from emp where job not in('clerk', 'manager', 'analyst');
-- 급여가 2000이상이고 입사일이 1981-01-01 이후인 사원 검색
select*from emp where sal>=2000 and hiredate>='1981-01-01';
직급은 [select distinct job from emp;] 코드를 통해 이름을 복사해서 작성했다.
select 'HELLO' like 'hel%'; -- 참
select 'HELLO' like '__LLO'; -- 참
select 'HELLO' like 'HELLO_'; -- 거짓
select 'HELLO' like 'HELL_'; -- 참
'HELLO'
문자열의 앞에 'hel'이란 문자가 들어있는가: MySQL은 대소문자를 구분하지 않고, 대문자 'HEL'이 첫부분에 들어있기 때문에 반환 값은 1
문자열의 세번째부터 마지막 글자까지 'LLO'인가: 위치와 문자가 동일하기 때문에 반환 값은 1
문자열의 첫번째부터 다섯번째까지 'HELLO'이고, 마지막에 한 글자가 있는가(총 6글자인식): 'HELLO'는 들어있지만 마지막 문자가 없기 때문에 반환 값은 0
문자열의 첫번째부터 네번째까지 'HELL'이고, 마지막 한 글자가 있는가(총 5글자로 인식): 'HELL'이 위치와 문자가 동일하고 마지막 문자가 있기 때문에 반환 값은 1
예제)
-- 사원 이름이 'J'로 시작하는 사원 검색
select*from emp where ename like 'j%';
-- 사원 이름이 'R'로 끝나는 사원 검색
select*from emp where ename like '%r';
-- 사원 이름이 'R'을 포함하는 사원 검색
select*from emp where ename like '%r%';
-- 사원 이름 중 세번째 글자가 'A'인 사원 검색
select*from emp where ename like '__a%';
select*from emp where empno like '_7%';
select*from emp where hiredate like '1981%';
select*from emp where hiredate not like '1981%';
문제) 1981년도에 입사한 사원 중에서 부서가 10번 또는 20번인 사원 검색
select*from emp where hiredate like '1981%' and (deptno=10 or deptno=20);
select*from emp where hiredate like '1981%' and deptno in(10,20);
두 코드는 동일하지만 처음에 위에 버전으로 풀었다가 어제 배웠던 in이 생각나서 뒤늦게 추가했다.
-- 1981-01-01부터 1981-12-31 사이에 입사한 사람
select*from emp where hiredate between '1981-1-1' and '1981-12-31';
select*from emp where hiredate between date('1981-1-1') and date('1981-12-31');
select*from emp where hiredate like '1981%';
datetime
year(datetime): 주어진 datetime 값의 년도 반환
monthname(datetime): 주어진 datetime 값의 월(영문) 반환
month(datetime): 주어진 datetime 값의 월 반환
weekday(datetime): 주어진 datetime 값의 요일값(0~7) 반환 / 월요일부터 0
dayname(datetime): 주어진 datetime 값의 요일명(영문) 반환
dayofmonth / day(datetime): 주어진 datetime 값의 날짜(일) 반
select hiredate,
year(hiredate) as year,
monthname(hiredate) as monthname,
month(hiredate) as month,
weekday(hiredate) as weekday,
dayname(hiredate) as dayname,
day(hiredate) as day
from emp;
결과값
-- 예시) 1981-12-03 --> 1981/12/3 THU
select hiredate, concat(concat_ws('/', year(hiredate), month(hiredate), day(hiredate)),
' ', upper(left(dayname(hiredate),3))) as hiredate2 from emp;
year, month, day를 통해 값을 추출하고, concat_ws를 이용해 사이마다 '/'를 넣었다.
select hiredate, now(), concat(abs(datediff(hiredate, now())), '일 경과') from emp;
select hiredate, now(), concat(datediff(now(), hiredate), '일 경과') from emp;
위의 두 코드는 적은 날짜에서 큰 날짜를 빼도 절댓값을 구하는 공식으로 마이너스-가 안 나오게 하는 것과 그냥 큰 날짜를 앞에 두어 마이너스-가 안 나오게 하는 것의 차이일 뿐 동일한 코드다.
dateDiff(d, n): d에서 n만큼의 일수차
timeDiff(d, n): d에서 n만큼의 시간차
예제)
-- 입사일이 1981-12-05 기준 앞뒤로 5일 이내 사원 검색
select hiredate, ename, concat(abs(datediff(hiredate,'1981-12-05')), '일 경과') as '경과일'
from emp where abs(datediff('1981-12-05', hiredate))<=5;
select
case
when -1 > 0 then '-1은 양수다.'
when -1 = 0 then '-1은 0이다.'
else '-1은 음수다.'
end;
case 안에 when으로 조건을 주고 조건에 충족한다면 then에 반환할 값을 준다. 모든 when 조건에 충족하지 않으면 else의 값을 반환한다.
해당 코드는 조건에 해당하는 것이 없기 때문에 else로 빠져서 '-1은 음수다'가 출력된다.
예제)
select
sal,
if (sal > 2000, 'Expensive', 'Cheap') as result1,
case
when sal < 1000 then '저급여'
when sal between 1000 and 2000 then '일반급여'
else '고급여'
end as result2
from emp;
create user 계정이름 identified by '비밀번호';
create user 'icia' identified by '1234';
create user 'icia'@'%' identified by '1234'; -- 위의 코드와 동일
create user 'icia2'@'localhost' identified by '1234';
insert into dept(deptno, dname, loc) values(10, 'accounting', 'NEW YORK');
insert into dept values(20, 'research', 'DALLAS');
insert into dept values(30, 'sales', 'CHICAGO');
insert into dept values(40, 'operations', 'BOSTON');
insert into (db(칼럼)) values(값);
여러줄을 실행할 때에는 드래그해서 윗쪽에 번개모양을 누르면 된다.
INSERT INTO emp VALUES(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
INSERT INTO emp VALUES(7499,'ALLEN','SALESMAN',7698,'1981-2-20',1600,300,30);
INSERT INTO emp VALUES(7521,'WARD','SALESMAN',7698,'1981-2-22',1250,500,30);
INSERT INTO emp VALUES(7566,'JONES','MANAGER',7839,'1981-2-4',2975,NULL,20);
INSERT INTO emp VALUES(7654,'MARTIN','SALESMAN',7698,'1981-9-28',1250,1400,30);
INSERT INTO emp VALUES(7698,'BLAKE','MANAGER',7839,'1981-5-1',2850,NULL,30);
INSERT INTO emp VALUES(7782,'CLARK','MANAGER',7839,'1981-9-6',2450,NULL,10);
INSERT INTO emp VALUES(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO emp VALUES(7844,'TURNER','SALESMAN',7698,'1981-9-8',1500,0,30);
INSERT INTO emp VALUES(7900,'JAMES','CLERK',7698,'1981-3-12',950,NULL,30);
INSERT INTO emp VALUES(7902,'FORD','ANALYST',7566,'1981-12-3',3000,NULL,20);
INSERT INTO emp VALUES(7934,'MILLER','CLERK',7782,'1982-1-23',1300,NULL,10);
insert into salgrade values (1,700,1200);
insert into salgrade values (2,1201,1400);
insert into salgrade values (3,1401,2000);
insert into salgrade values (4,2001,3000);
insert into salgrade values (1,3001,9999);
MySQL에서는 %를 쓸 수 있지만 Oracle에서는 사용할 수 없기 때문에 mod를 사용한다.
select 5-2.5 from dual;
MySQL에서는 뒤의 from dual을 생략해도 되지만 Oracle에서는 안 된다.
select 'ABC' + 3; -- 3
select '1' + '002'; -- 3
문자열에 사칙연산을 하면 0으로 인식하지만, 문자열인데 숫자만 있을 경우엔 알아서 숫자로 인식한다.
select true, false; -- 1, 0
select !true, not 1, !false, not 0; -- 0, 0, 1, 1
true: 1
false: 0
select true is true, true is not true; -- true는 true다, true는 false다
select true = true, true != true; -- true는 true다, true는 false다
is연산자: == null 값 비교일 경우 사용한다.
is not 연산자: !=
-- 부서번호가 30이면서 급여가 2000 이상인 사원 검색
select*from emp where deptno = 30 and sal >= 2000;
-- 전체사원중에서 급여가 1000에서 2000인 사원
select*from emp where sal >= 1000 and sal <= 2000;
-- 부서번호가 10 또는 20
select*from emp where deptno = 10 or deptno=20;
논리연산자
&&, and: 논리곱 (모두 해당하는 경우)
||, or: 논리합 (하나라도 해당하는 경우)
select*from emp where sal between 1000 and 2000;
select*from emp where deptno in(10,20);
-- 사원번호가 홀수인 사원의 사원번호, 사원이름, 직급 검색
select empno,job,ename from emp
where mod(empno,2)=1;
-- 커미션이 null인 사원, null이 아닌 사원
select ename, comm from emp
where comm is null;
select ename, comm from emp
where comm is true; -- is not null
-- 부서번호가 20번인 사원
select * from emp where deptno=20;
-- 부서번호가 20번이 아닌 사원
select * from emp
-- where !(deptno = 20);
-- where not(deptno != 20);
-- where deptno != 20;
where deptno <> 20;
질문한 내용이나 어려웠던 점 메모
1. 단축키
자바를 하다가 와서 그런지 한 줄 삭제를 하려고 ctrl+D를 눌러서 선택된 줄(혹은 드래그한것) 복사 단축키를 알게 되었다..자꾸 복사하고 있어서 ctrl+Z를 계속 누르고 있었다.
전체 피드백
오늘 배운 것은 비교적 간단한 것들이라 그렇게 어렵지만은 않았다!
MySQL을 하고 다시 Java로 돌아갈 것이라고 하셨다(Java에서 하던 PhoneBook이라던가 MySQL에 연동하는 식). 그전에 잊지 않도록 짬나는 시간에 복습하는거 잊지 말기!
