Duritz's Fun Life

---- instr 함수
select instr('oracle server orahome', 'ora', 1, 1)
    -- instr('문장', '찾을문자', x,y) : 찾을문자가 문장의 x번째로 시작하는 문자부터
    --                                           y번째로 나오는 찾을문자를 검색
from dual;

select instr('oracle server orahome', 'ora', 1, 2)
from dual;

select instr('oracle server orahome', 'ora', 2, 1)
from dual;

select instr('oracle server orahome', 'ora', 2) -- 출발지점을 생략하면 1과 같다.
from dual;
select instr('oracle server orahome', 'ora', 1, 2) -- 바로위 문장은 지금 문장과 같다.
from dual;

---- length 함수
select length('oracle'), length('오라클') -- length는 문자수의 길이를 나타낸다.
from dual;

select lengthb('oracle'), lengthb('오라클') -- length는 byte수를 나타낸다.
from dual;

---- substr 함수
select substr('oracle server 9i', 8,6) -- substr('문자',x,y) : 문자에서 x번째부터 y개만큼 글자를 출력한다.
from dual;

select substr('oracle server 9i', 8) -- 갯수 표현을 안하면 원하는 곳부터 끝까지 출력한다.
from dual;

select substr('오라클 서버 9i', 5,2) -- 한글 영문 byte에 관계없이 글자수를 출력한다.
from dual;

select substrb('oracle server 9i', 5,2) -- substrb('문자',x,y)는 번째 바이트부터 y개 byte만큼 출력
from dual;

select substrb('오라클 서버 9i', 5,2) -- 한글은 글자 하나당 2byte이다.
from dual;

------ 예제문장.
select ename, substr(ename,2)
from emp;
------ 예제문제 substr함수를 써서 sawon 테이블의 여자만 검출
select *
from sawon
where substr(minbun,7,1) = 2 or substr(minbun,7,1) = 4;

select *
from sawon
where substr(minbun,7,1) in('2','4') -- in 연산자 사용

---- concat 함수
select concat('oracle', 'server') from dual; -- concat 은 2개밖에 연결할수가 없다.
select 'oracle' || ' server' from dual; -- 권장 2개이상 연결이 가능하다.

select concat(ename ||' ''job is ', job) -- concat 두개 값사이에 || 넣으면 중간에 문자를 넣을수 있다.
from emp;  -- ' ' 사이에 '두개를 넣으면 출력값에는 '하나가 표시된다.

-- ○ Order by 절
select deptno "부서번호", ename "사원명", job "직종", sal "급여"
from emp
order by deptno asc;  -- order by는 행 정렬을 한다. asc는 오름차순을 의미
                      -- column 명대신 별칭을 사용하여도 되고, asc를 빼도 사용가능하다(default가 오름차순)
       -- desc은 내림차순을 의미한다.

select deptno "부서번호", ename "사원명", job "직종", sal "급여"
from emp
order by 1,3,4 desc; -- 1,3번째 column을 오름차순정렬, 4번째 column을 내림차순 정리하게된다.

--- IS NULL 연산자
select ename, sal, comm
from emp
where comm is null;  -- null은 존재하지 않는 값이기 때문에 수식기호를 사용 할 수가 없다.

select ename, sal, comm
from emp
where not comm is null; -- 부정표현 not column is null 또는 column is not null 로 표현할 수 있다.

-- Like 연산자
select ename, job, sal
from EMP
where ename like 'SMITH';  --  Like : ~문자형태와 일치한다.

select ename, job, sal
from EMP
where ename like 'S%'; -- 대문자 S로 시작하는 ename을 출력한다.

 
select ename, job, sal
from EMP
where ename like '%S'; -- 대문자 S로 끝나는 ename을 출력한다.

select ename, job, sal
from EMP
where ename like '%S%'; -- 대문자 S가 포함되는 ename을 출력한다.

select ename, job, sal
from EMP
where ename like '%AM%'; -- 붙어있는 대문자 AM이 포함되는 ename을 출력한다.

select ename, job, sal
from EMP
where ename like '%A%M%'; -- 붙어있건 떨어져있건 대문자 A,M이 포함되는 ename을 출력한다.

select ename, job, sal
from EMP
where ename like '_A%'; -- _는 하나의 문자와 대치된다. 그러므로 두번째 글자가 A인 ename을 출력하게된다.

select ename, job, sal
from EMP                 -- 위와 마찬가지로 풀어봤을때
where ename like '__R%'; -- __R%은 세번째 글자가 R인 ename을 출력하게된다.

select ename, job, sal
from EMP                 -- 위와 마찬가지로 풀어봤을때
where ename like 'M_R%'; -- M_R%은 첫번째 글자가 M이고 세번째 글자가 R인 ename을 출력하게된다.

------ 참조사항(예제문제), 테이블생성
create table sawon            -- sawon 이라는 테이블을 생성한다.
(sano number(4)               -- sano라는 숫자값 column을 만든다.
,name varchar2(10)            -- name라는 문자값 column을 만든다.
,minbun varchar(13)           -- minbun라는 문자값 column을 만든다.
,hiredate date                -- hiredate라는 날짜 column을 만든다.
,constraint sawon_sano_pk primary key(sano) -- P.K를 sano로 지정한다.
) tablespace users;

insert into sawon
values(1001, '홍길동', '7810031234567', '1990-01-01');  -- 값을 입력
insert into sawon
values(1002, '엄정화', '7403012234567', '1999-01-01');
insert into sawon
values(1003, '남자애', '0012253234567', '2002-05-05');
insert into sawon
values(1004, '여자애', '0110104234567', '2003-05-05');

select * from sawon;
commit;
------ 사원테이블에서 여자만 추출하시오.
select *
from sawon
where minbun like '______2%' or
      minbun like '______4%';
     
------ 참조사항(예제문제2), 테이블생성
create table jepum
(jname varchar2(10)
,bigo varchar2(100)
) tablespace users;

insert into jepum
values('금시계', '고객만족도 99.99% 획득제품');
insert into jepum
values('은시계', '정밀도 점수 99.99% 획득제품');
commit;

select * from jepum;
------ 제품테이블에서 99.99% 라는 글자가 있는 것만 추출하시오
select *
from jepum
where bigo like '%99.99%%'; -- 원하는 답이 출력되지 않고 모두 나오게된다.
------ like와 함께 사용되어진 %나 _를 wild 문자라 한다.
select *
from jepum
where bigo like '%99.99\%%' -- escape 로쓰이는 문자는 잘안쓰이는 특수문자를 많이 사용한다.
   escape '\'; -- escape 'x' 쓰게되면 x뒤에 오는 %는 wild 문자기능을 상실하고 단순한 문자로 인식된다.

-- IN (list) 연산자
select ename, job
from EMP
where job = 'SALESMAN' or
   job = 'CLERK' or
   job = 'MANAGER'; -- or 만으로만 구성 (권장사항)
  
select ename, job
from EMP
where job In ('SALESMAN', 'CLERK', 'MANAGER'); -- IN 연산자사용

select ename, job
from EMP
where not job In ('SALESMAN', 'CLERK', 'MANAGER'); -- IN연산자에서 부정사용

select ename, job
from EMP
where job ^= 'SALESMAN' and  -- 같지 않다의 의미인 ^= 사용
   job ^= 'CLERK' and     -- and는 or의 부정표현이다.
   job ^= 'MANAGER';

-- between 연산자
select ename, sal
from EMP
where sal >= 2000 and sal <= 3000; -- sal이 2000이상 3000이하를 나타내라.

select ename, sal
from EMP -- between And B 연산자 : 실행하면 부등호 형식으로 다시 바뀌니 부등호로 하는게 실행이 빠르다.
where sal between 2000 and 3000;  -- between 연산자는 숫자 , 문자 모두 가능하다.

select ename, sal
from EMP
where ename >= 'J' and ename <= 'S'; -- ename의 값이 J에서 S사이값을 나타낸다.
where ename >= 'j' and ename <= 's'; -- 이경우는 값이 출력되지 않는다. 아스키값을 확인하면 이해하기 쉽다.

select ascii('A'), ascii('a') from dual; -- 아스키값 출력

select ename, hiredate
from EMP
where hiredate >= '1982' and hiredate <= '1987'; -- 문자 형식이라 오류가 뜬다. 다음을 참조한다.

select sysdate, to_char(sysdate, 'yyyy') -- sysdate에서 문자형식으로 추출한다.
from dual;

select sysdate, extract(year from sysdate) -- sysdate에서 원하는 부분을 숫자 형식으로 추출한다.
from dual;

select ename, hiredate
from EMP
where to_char(hiredate, 'yyyy') >= '1982' and
   to_char(hiredate, 'yyyy') <= '1987';    -- 날짜값에서 원하는 부분 문자형식으로 
  
select ename, hiredate
from EMP
where extract(year from hiredate) >= '1982' and
   extract(year from hiredate) <= '1987';  -- 날짜값에서 원하는 부분을 숫자형식으로
  
select ename, hiredate
from EMP
where to_char(hiredate, 'yyyy') between 1982 and 1987; -- to_char 와 between 동시사용

select ename, hiredate
from EMP
where extract(year from hiredate) between 1982 and 1987; -- extract 와 between 동시사용