2022.04.26. 화요일 이공계 전문기술 연수사업 22일차

오늘 배운 것

문자함수
chr 정수 아스키코드에 해당하는 문자를 반환
concat 두 개의 문자열을 연결하여 반환
initcap 단어의 첫 글자를 대문자로 변환하여 반환
lower 소문자로 반환
upper 대문자로 반환
lpad(문자열,자리수,채울 글자) 문자열을 자리수만큼 칸을 잡아 출력하고 왼쪽 빈 칸을 채울 글자로 채워줌
rpad(문자열,자리수,채울 글자) 문자열을 자리수만큼 칸을 잡아 출력하고 오른쪽 빈 칸을 채울 글자로 채워줌
ltrim 문자열의 왼쪽 공백을 제거하여 반환
rtirm 문자열의 오른쪽 공백을 제거하여 반환
trim 문자열의 좌,우 공백을 제거하여 반환
length 문자열의 길이를 반환
ascii 문자에 해당하는 아스키 코드값을 반환 select ascii('A') from dual;
instr 문자열에서 특정 문자열의 위치를 반환 select instr('hello korea','k') from dual; //오라클은 문자열길이 1부터 시작
instr(문자열1,문자열2) 문자열1로부터 문자열2가 나오는 위치를 반환
instr(문자열1,문자열2,숫자1) 문자열1로부터 숫자1인덱스 이후에 나오는 문자열2의 위치를 반환
substr 문자열의 일부분을 잘라서 반환하는 함수
substr(문자열,시작위치,길이) 문자열의 시작위치에서 길이만큼 잘라서 반환
replace(문자열,a,b) 문자열 중에서 a를 b로 변경 select replace('hello java','hello','안녕') from dual;

ex)개발팀에 근무하는 모든 직원들의 사원번호,이름,부서번호,부서명,관리자명,사원아이디,관리자아이디 출력
select e.eno, e.ename, e.dno, dname, m.ename, 
substr(e.email,1,instr(e.email,'@')-1) id,
substr(m.email,1,instr(m.email,'@')-1) 관리자id 
from emp e, emp m, dept d 
where e.dno=d.dno and e.mgr=m.eno and dname like '개발%';

ex)서교동에 근무하는 여성직원들의 사원번호,이름,관리자명,이메일,관리자이메일,부서번호,부서명,연봉을 출력
단,부서번호 순으로 출력하고 동일하면 사원이름순으로 출력
select e.eno,e.ename,m.ename,e.email,m.email,e.dno,dname,e.salary*12+e.comm 연봉 
from emp e,emp m,dept d 
where d.dno=e.dno and e.mgr=m.eno 
and substr(e.jumin,8,1) in ('2','4') 
and dloc='서교동' order by dno, e.ename;

ex)모든 직원의 사원번호,이름을 출력하시오 단, 이름은 10칸을 잡아 왼쪽 정렬하여 출력하고 빈칸은 *로 출력
select eno,rpad(substr(ename,1,1),3,'*') from emp;

ex)김한희의 부하직원들의 사원번호,사원명,아이디,주민번호,실수령액을 출력
단,주민번호는 14자리를 잡아 8번째까지만 왼쪽 정렬하여 출력하고 빈 칸은 '*'로 출력
또, 실수령액은 10칸을 잡아 오른쪽 정렬하고 빈 칸은 0으로 출력
실수령액이 높은 순으로 출력하고 동일할 때는 사원명순으로 출력
select e.eno,e.ename,substr(e.email,1,instr(e.email,'@')-1) id,
rpad(substr(e.jumin,1,8),14,'*') 주민번호,
lpad((e.salary+e.comm)*12,10,'0') 실수령액 
from emp e,emp m 
where e.mgr=m.eno and m.ename='김한희' 
order by 실수령액 desc,e.ename;

ex)도서 제목에 '야구'가 포함된 도서명을 '농구'로 변경하여 출력
select replace(bookname,'야구','농구') from book where bookname like '%야구%';

ex)같은 성씨를 가진 고객이 몇 명이나 되는지 파악하도록 성씨별 인원수 출력
select substr(ename,1,1) ,count(substr(ename,1,1)) from emp group by substr(ename,1,1);

----------------------------------------------------------------------------------------------
날짜 관련 함수
to_date(char,datetime) 문자형 데이터를 날짜형으로 변환
select to_date('2022/04/26','yyyy/mm/dd') from dual;
to_char(date,datetime) 날짜형 데이터를 문자형으로 변환 
select to_char(sysdate,'yyyy') from dual;
select to_char(sysdate,'yyyymmdhhmiss') from dual;
yyyy yy 연도
mm 월
d 날
hh 시
mi 분
add_months(date,숫자) 날짜에 숫자 개월 수만큼 더한 값을 반환
last_day(date) 그 날짜가 속해있는 달의 마지막 날을 반환
sysdate select sysdate from dual;
months_between(날짜1,날짜2) 두 날짜 사이의 개월 수를 반환 단, 날짜 1이 더 최근의 날짜여야 함

ex)이번달 생일자인 모든 직원들에게 상품권을 지급하려고 함
대상자의 사원번호,사원명,부서번호,부서명,관리자명,주민번호를 출력
주민번호는 14자리를 잡아서 앞에서 8글자만 출력하고 빈칸은 *로 출력
관리자명 순으로 출력하고 동일할 때는 사원이름 순으로 출력
select e.eno,e.ename,e.dno,dname,m.ename,rpad(substr(e.jumin,1,8),14,'*') 주민번호 
from emp e, emp m, dept d 
where d.dno=e.dno and e.mgr=m.eno 
and substr(e.jumin,3,2)=to_char(sysdate,'mm') 
order by m.ename,e.ename;

select sysdate-1,sysdate,sysdate+1 from dual;

ex)마당서점은 주문일로부터 10일 후 매출을 확정함
각 주문에 대하여 주문번호,고객번호,고객명,도서번호,도서명,출판사명,주문일,확정일을 출력
단, 최근의 주문확정일 순으로 출력함 동일할 때는 주문번호 순으로 출력
select orderid,c.custid,name,b.bookid,bookname,publisher,orderdate,orderdate+10 
from orders o,customer c,book b 
where o.custid=c.custid and b.bookid=o.bookid 
order by orderdate+10 desc , o.orderid;

select to_char(sysdate,'yyyy/mm/dd d') from dual;
요일에 대한 숫자가 나타남
select to_char(sysdate,'yyyy/mm/dd day') from dual;
요일까지 문자로 나타남
select to_char(sysdate,'yyyy/mm/dd dy') from dual;
요일에 대한 약어가 출력됨

ex)2022년 4월 7일에 주문받은 도서의 주문번호,주문일,고객번호,도서번호,도서명을 출력
단, 주문일을 'yyyy-mm-dd 요일' 형태로 출력함
select orderid, to_char(orderdate,'yyyy-mm-dd day') 주문일,custid, b.bookid,bookname 
from orders o, book b 
where o.bookid=b.bookid and orderdate='2022/04/07';

ex)모든 사원의 이름과 근무 개월 수 출력
select ename, months_between(sysdate,hiredate) from emp;

ex)근속년수가 5년 이상인 직원들에게 특별 상여금을 지급하려고 함.
상여금은 연봉의 50프로이고 대상자의 사원번호,부서번호,사원명,관리자명,입사일,근무개월수,주민번호,상여금을 출력
입사일은 연/월/일 요일을 출력하고 근무개월수와 상여금은 반올림하여 정수만 출력
또, 주민번호는 14자리를 잡아서 왼쪽정렬하여 8글자만 출력하고 공백은 '*'로 출력
근무개월수가 높은 순으로 출력하고 동일할 때는 이름 순으로 출력

select e.eno,e.dno,dname,e.ename,m.ename,
to_char(e.hiredate,'yyyy/mm/dd dy') hiredate,
round(months_between(sysdate,e.hiredate),0) months,
rpad(substr(e.jumin,1,8),14,'*') jumin,
round((e.salary+e.comm)*12*0.5,0) bonus
from emp e left outer join emp m
on e.mgr=m.eno
right outer join dept d
on e.dno=d.dno
where months_between(sysdate,e.hiredate)>=60 
order by months_between(sysdate,e.hiredate) desc,e.ename;

outer join
왼쪽 테이블이나 오른쪽 테이블의 레코드는 조건을 만족하지 않더라고 무조건 포함시키기 위한 조인

select 컬럼1,컬럼2,... from 테이블1 left outer join 테이블2 on 조건식

-------------------------------------------------------------------------------------
3개의 테이블에 대한 outer join
select 컬럼1,컬럼2 
from 테이블1 left outer join 테이블2
on 조건식
left outer join 테이블 3
on 조건식

3개의 테이블에 대한 outer join과 다른 조건식
select 컬럼1,컬럼2
from 테이블1 left outer join 테이블2
on 조건식
left outer join 테이블3
where 조건식

------------------------------------------------------------------------------
decode(컬럼,값1,결과1,값2,결과2,결과3)
컬럼의 값이 값1이면 결과 1을 반환 
컬럼의 값이 값2이면 결과 2를 반환
그 나머지면 결과 3을 반환
값과 결과는 쌍을 이루어 얼마든지 나열할 수 있음

ex)사원의 이름과 주민번호,성별을 출력
select ename,jumin,decode(substr(jumin,8,1),'1','남','2','여','3','남','4','여') from emp;

ex)모든 직원들의 사원번호,이름,주민번호,나이를 출력
select eno,ename,jumin,
to_char(sysdate,'yyyy')-
(substr(jumin,1,2)+
(select decode(substr(jumin,8,1),'1','1900','2','1900','4','2000') from emp)) age 
from emp;

SQL> select eno,ename,jumin,
  2  to_char(sysdate,'yyyy')-
  3  (substr(jumin,1,2)+
  4  (select decode(substr(jumin,8,1),'1','1900','2','1900','4','2000') from emp)) age
  5  from emp;
(select decode(substr(jumin,8,1),'1','1900','2','1900','4','2000') from emp)) age
 *
4행에 오류:
ORA-01427: 단일 행 하위 질의에 2개 이상의 행이 리턴되었습니다.
상관서브쿼리!!!!!!!!!!!!!!!!!!!!!!!!1이해됨. 예제 더 풀어보기

select eno,ename,jumin,
to_char(sysdate,'yyyy')-
(substr(jumin,1,2)+
(select decode(substr(jumin,8,1),'1','1900','2','1900','2000') from emp e2 where e2.eno=e1.eno)) age 
from emp e1;

---------------------------------------------------------------------------------------------------------------
null값 처리
아직 지정되지 않은 값이며 0,' ' 와는 다른 특별한 값임
비교 연산자로 비교가 불가능하며 연산을 수행하면 결과 역시 null값으로 반환됨
null+숫자 연산의 결과는 null
집계함수 계산 시에 null이 포함된 행은 집계에서 빠짐

select * from customer where address = null; 이건 성립 안됨!
select * from customer where address is null;

' '=>null과 다르게 취급
'',null =>동일하게 취급

SQL> select avg(salary) from emp;

AVG(SALARY)
-----------
      407.5

SQL> select sum(salary)/count(*) from emp;

SUM(SALARY)/COUNT(*)
--------------------
          388.095238
      
SQL> select sum(salary)/count(salary) from emp;

SUM(SALARY)/COUNT(SALARY)
-------------------------
                    407.5
=>null은 집계함수에 포함되지 않음!


select count(*), sum(price), avg(price) from book where bookid>10;

  COUNT(*) SUM(PRICE) AVG(PRICE)
---------- ---------- ----------
         0
         //count는 0을 반환하고 나머지 집계함수들은 조건을 만족하는 레코드가 없어서 null을 반환함

nvl(속성,대체할 값) 속성값이 null이면 대체할 값을 반환하라
select name,nvl(phone,'전화 없음') from customer;

customer와 똑같은 구조,데이터인 customer100을 만듦
create table customer100 as select * from customer;

ex)customer100에 새로운 회원등록을 위한 회원번호를 검색
SQL> select max(custid)+1 from customer100;

MAX(CUSTID)+1
-------------
           11

SQL> delete customer100;

10 행이 삭제되었습니다.

SQL> select max(custid)+1 from customer100;

MAX(CUSTID)+1
-------------


SQL> select nvl(max(custid),0)+1 from customer100;

NVL(MAX(CUSTID),0)+1
--------------------
                   1


개별 연습문제) 부서팀명을 입력받아 최연장자,최연소자의 나이를 출력하는 jsp파일을 작성해봅시다

select 
max(to_char(sysdate,'yyyy')-
(substr(jumin,1,2)+
(select decode(substr(jumin,8,1),'1','1900','2','1900','2000') from emp e2 where e2.eno=e1.eno))) age_max,
min(to_char(sysdate,'yyyy')-
(substr(jumin,1,2)+
(select decode(substr(jumin,8,1),'1','1900','2','1900','2000') from emp e2 where e2.eno=e1.eno))) age_min  
from emp e1, dept d
where e1.dno=d.dno and dloc='판교';

 

오늘 헷갈린 것

• 최대,최소 구할 때는 무조건 삼항연산자!!!
• instanceof 연산자
• 배열 shuffle 하는 법 !(tmp 사용)
• 이중 outer join ->이거 사실 아직도 잘 이해안감.......
• 2000년생 상관서브쿼리 다시 해보기!!!!
• 내가 만든 개별연습문제 꼭 완성하기

오늘 한 생각

시험기간이라그런가 스카 중고딩들이 시끄럽게해서 짱난다...

고만좀 이동해 이것더라...

백엔드로 갈거면 자바 계속계속 공부해야겠다..

공부에는 끝이 없다 하하하