Oracle DB 1일차

oracle을 기반으로 데이터베이스를 공부하면서 정리한 내용들을 공유하고자 한다

테이블 간의 관계를 이 글 내에서 모두 설명하기는 힘들기 때문에 문법적인 부분 위주로 

글을 적어보고자 한다

---

-- between 구문

select employee_id,first_name,department_id  from employees  where salary between 5000 and 10000;

 

5000과 10000 사이에 대한 조건을 제시하고자 할 때 사용한다

이 때 not을 붙여서 "not between"을 사용하여 반대를 나타낼 수도 있다

 

-- in 구문

select employee_id,first_name,salary,salary+salary*commission_pct
from employees
where department_id in (50,60,80,90);

 

in을 이용하여 괄호() 안에 해당하는 숫자에 포함되는 것들만 출력하여 나타낼 수도 있다

 

-- 이름에 'e'로 들어가는(포함) 사원의 사번,이름

select employee_id,first_name  from employees  where first_name like '%e__%';

이와 같이 %를 앞,뒤로 붙이게 되면 앞,뒤에 어떠한 문자나 숫자가 와도 상관없다는 뜻이다

또한 __를 통하여 글자 수를 지정해줄 수도 있다 위와 같이 __를 뒤에 넣음으로써 4글자 이상의 조건이 된다

 

-- power 지수승,sign 부호,mod 나머지 

select power(2,3),power(4,2),sign(100),sign(0),sign(-100) ,              mod(17,3),mod(19,5)  from dual;

power : 지수승을 출력

sign: 부호를 의미하며 양수이면 1, 0이면 0 음수이면 -1이 출력

mod: 나머지를 출력

 

-- 소수점 이후를 버림 

select 1529.612734, floor(1529.612734), ceil(1529.112734), round(1529.112734), round(1529.612734)  from dual;

 

floor: 내림

ceil: 올림

round: 반올림

select '사번이' || employee_id || '인 사원의 이름은 ' || first_name || ' ' || last_name || ' 입니다.' "사원정보"  from employees;

 

select employee_id, concat(first_name,last_name)  from employees;

||: 합치는 것을 의미(공백있음)

concat도 마찬가지로 문자열 결합(공백없이)

 

-- 문자열 결합, 중첩함수 utf8=3byte씩

select employee_id, concat(first_name,concat(' ',last_name)),  length(concat(first_name,concat(' ',last_name))),length('oracle'),length('오라클'),lengthb('oracle')  ,lengthb('오라클')  from employees;

문자열 결합의 예제이며, utf8이면 3byte를 차지한다

 

-- 소문자

select employee_id,first_name,lower(first_name),upper(first_name),salary  from employees  where lower(first_name)=lower('steVen');

lower 함수: 소문자로 만들어준다

이를 통해 혹시나 모를 대문자를 소문자로 변경하여 검색하는 데 의의가 있다

 

-- substr 인수를 뒤에 하나만 주게 되면 그 다음부터 쭉 나오게 되고, 인수를 두 개 주면 첫 번째 인수부터 두 번째 인수까지가 나오게 된다
-- instr 인수를 하나 string 값으로 주게 되면 해당 string에 대한 순서가 반환되고, 두 개 주게 되면 첫 번째 인수 뒤에서부터 해당 string을 찾게 된다
select 'hello oracle !!!', substr('hello oracle !!!',7), substr('hello oracle !!!',7,6),
    instr('hello oracle !!!','o'),instr('hello oracle !!!','o',6)
from dual;

 

substr('hello oracle !!!',7) 이 경우 해당 문자열의 7번째부터 출력된다

substr('hello oracle !!!',7,6)의 경우 해당 문자열의 7번째부터 6개가 출력된다

 instr('hello oracle !!!','o')를 사용하게 될 경우 o의 인덱스가 출력된다

instr('hello oracle !!!','o',6)를 사용하게 될 경우 6번째 인덱스부터 'o'의 위치를 찾고 해당 인덱스를 출력한다

 

-- '123-456'

select '1234-56' zipcode, substr('123-456',1,instr('123-456','-')-1) zip1,  substr('123-456',instr('123-456','-')+1) zip2  from dual;

"-"를 기준으로 둘로 나눌 수 있도록 쿼리문을 설계한 것이다

substr과 instr을 이용하여 instr함수를 통해 "-"의 인덱스를 찾고 해당 인덱스 전,후로 나누어 출력해주면 된다

 

-- trim 앞 뒤 공백 제거

select '   ora  c l e   ', trim(' ora    c   l e ')  ,length(trim(' ora    c   l e ')),  replace('jaba','b','v')  from dual;

trim: 공백 제거

replace(문자열,a,b): a를 b로 바꿔준다

 

select ascii('0'),ascii('A'),ascii('a'),  chr(48),chr(65),chr(97),chr(47)  from dual;

 

ascii : 아스키코드 값으로 변환

chr: 문자로 변환

참고 아스키코드값

"0": 48

"a": 65

"A": 97

 

--- 날짜함수

select sysdate, sysdate+3 "3일 후", add_months(sysdate,1) "한달 후", add_months(sysdate,2) "2달 후",      sysdate,sysdate+500  from dual;

sysdate: 날짜함수로 현재 날짜를 반환해줌

add_months(날짜,a) : 날짜를 a만큼 증가시킨다

 

select employee_id, round(months_between(hire_date,sysdate)) "입사 개월 수"  from employees;

 

months_between(날짜a,날짜b): 날짜b에서 날짜a를 뺀 값을 반환해준다

이를 통해 "현재일-입사일"과 같은 쿼리 조회문을 할 때 얼마동안 일을 했는지를 알 수 있다

 

select sysdate, last_day(sysdate),next_day(sysdate,3)  from dual; last_day: 전 날값을 반환 next_date(날짜,숫자) 날짜에서 숫자만큼의 날 수를 더해서 반환해준다

 

-- sysdate에 round를 시키게 되면 반올림 기준이 오전,오후이다

select sysdate, round(sysdate), round(sysdate,'dd'),round(sysdate,'mm'),round(sysdate,'yy'), to_char(round(sysdate,'hh'),yyyy.mm.dd am hh:mi:ss')

to_char를 통해 문자열로 변환함으로써 반환 형태를 지정해줄 수 있다.

 

-- trunc를 사용하여 시간을 버릴 수도 있다

select sysdate, trunc(sysdate), trunc(sysdate,'dd'),trunc(sysdate,'mm'),trunc(sysdate,'yy'),  to_char(trunc(sysdate,'hh'),'yyyy.mm.dd am hh:mi:ss'),

이와 같이 기준값을 지정해서 버림할 수도 있다

 

-- 형변환 함수
-- 1345682456 >> 문자열. 0 또는 9로 자릿수를 지정할 수 있다. "."은 소숫점을 나타낸다 ","는 콤마를 결과값에 나타낸다

-- L은 각국의 통화표시를 해준다

select 1345682345, to_char(1345682345,'0,000,000,000,000'),          to_char(1345682345,'L9,999,999,999,999'),to_char(1345682345,'L9,999,999,999.99')  from dual;

to_char(1345682345,'0,000,000,000,000'),를 하게 되면 자릿 수를 지정할 수 있다.

자릿수가 이외의 부분은 0으로 채워지게 되며 0이 더 많을 경우 앞에 채워진다

따라서  0,001,345,682,345와 같은 결과가 나온다

 

 to_char(1345682345,'L9,999,999,999,999'): ￦1,345,682,345

to_char(1345682345,'L9,999,999,999.99') :        ￦1,345,682,345.00 

이는 .이 뒤에 있으므로 소수점으로 나오게 된다

 

select to_number('123,456','999,999')-to_number('23,567','99,999')  from dual; to_number()는 문자열을 숫자로 만들어주는 기능을 한다.

 

select sysdate, to_char(sysdate,'yyyy.mm.dd month yy mon w ww day dy dd')   ,to_char(sysdate,'hh24:mi:ss am hh hh12') from dual;

시간 형식을 지정할 때  위와 같이 사용한다

 

select sysdate,to_number(to_char(sysdate,'yyyymmdd'),'00000000')  from dual;

날짜를 숫자형식으로 바꾸어주었다

이 떄 바로 날짜 -> 숫자가 되지 않으므로 유의해서

날짜 -> 문자열 -> 숫자 이런 식으로 변경해야 한다

 

-- 자동 현변환이 일어난다

select sysdate,to_date(20201230,yyyy.mm.dd')  from dual;

 

-- 사번, 이름, 부서번호
-- 단, 부서가 없는 경우(null) '발령대기중'

select employee_id,first_name,department_id,nvl(department_id,0)  from employees;

nvl 함수를 사용하여 null값을 0으로 처리해준다

 

 

-- 부서번호가 90인 경우 임원
--           60인 경우 개발자
--          나머지 평사원
-- 사번,이름,부서번호,사원종류
-- decode(if,else if, else) 같다 비교만 가능하다

select employee_id,first_name,department_id,          decode(department_id,          90,'임원',          60, '개발자',          '평사원') 사원등급  from employees;

decode()를 통해 해당 값과 동일할 경우 어떤 값을 가진다로 표현할 수 있다

또한 이는 범위 지정이 아니다

 

-- 급여가 10000이상이면 고액연봉
--       5000이상      평균연봉
-- 사원, 이름, 급여, 급여등급

select employee_id,first_name,department_id,          (case when salary>=10000 then '고액연봉'          when salary>=5000 then '평균연봉'           else '저액 연봉'          end) 급여등급  from employees;

 

범위 지정을 위해서는 위와 같은 case when 구문을 사용하여 나타내 주어야 한다

 

-- 입사년도가 93년 미만(~~92) : 고참
--             98년 미만(~~97) : 선배
--                나머지 신입

select employee_id,first_name,hire_date,          (case when to_char(hire_date,'yyyy')<1993 then '고참'          when to_char(hire_date,'yyyy')<1998 then '선배'          else '신입' end) 입사년도  from employees;

 

입사년도를 표기하기 위하여 to_char를 이용해 날짜를 나타내어주었고 이에 대한 범위를

조건으로 주기 위하여 case when 구문을 사용해주었다.

 

-- 'seattle'에서 근무하는 사원의 사번, 이름, 부서이름

select e.employee_id,e.first_name,d.department_name  from employees e, departments d, locations l  where e.department_id=d.department_id  and d.location_id=l.location_id  and lower(l.city)=lower('seattle');

이전에 언급했던 lower() 함수를 이용하여 예외사항까지 포함하여 적용해준다

조인 조건절을 사용한 것으로 테이블에 대한 설명을 하지 않았기 때문에 

자세한 설명은 생략하고 넘어간다

 

 

select e.employee_id,e.first_name,d.department_name,job_title,start_date,end_date  from job_history h,jobs j,departments d,employees e  where j.job_id=h.job_id  and h.department_id=d.department_id  and h.employee_id=e.employee_id  and h.employee_id=101

위 구문은 101번 사원의 직급, 시작일 끝 일에 대한 쿼리 조회문이다

그런데 여기서 주의할 점은 여기서 해당 사원의 history가 저장된 테이블과의 조인은 수행하였지만,

현재 어디서 근무하고 있는지에 대한 내용이 없기 때문에

이를 추가해주기 위하여, 

select e.employee_id,e.first_name,d.department_name,          j.job_title,  to_date('97.03.16',yy.mm.dd'),sysdate from employees e, departments d,job_history h,jobs j  where e.department_id=d.department_id  and e.employee_id=101  and j.job_id=e.job_id;

 

위 구문을 추가하여 현재 어디서 근무하고 있는지에 대한 정보도 포함시킨다

 

-- 모든 사원의 사번, 이름, 매니저 사번, 매니저 이름

select e1.employee_id,e1.first_name,e2.manager_id,e2.first_name  from employees e1, employees e2  where e1.manager_id=e2.employee_id;

매니저, 사원에 대한 정보가 있어야 하므로 테이블을 두 개의 alias를 통하여 설정해주고

각각을 매니저, 사원 테이블이라고 alias를 한 뒤 쿼리문을 설계하면 된다

 

-- 모든 사원의 부서이름,사번, 이름, 매니저 사번, 매니저 이름
-- 두 개가 왜 다를까?
-- 부장에 해당하는 사람
-- 하위에 있는 과장이나 대리까지도 뽑아낼 수 있다.
-- 부장사원 밑에 있는 사람까지 추가시킨다

select e1.employee_id,e1.first_name,e2.manager_id,e2.first_name  from employees e1, employees e2, departments d  where e1.manager_id=e2.employee_id  and e1.employee_id=d.manager_id  and e1.department_id=60  union all  select e1.employee_id,e1.first_name,e2.manager_id,e2.first_name  from employees e1, employees e2, departments d  where e1.manager_id=e2.employee_id  and e1.department_id=d.department_id  and e1.department_id=60;

첫 번째 쿼리문의 경우 상사에 대한 데이터를 뽑아낼 때 모든 직급을 포함시킬 수 있다

두 번째 쿼리문의 경우 상사에 대한 데이터를 뽑아낼 떄 부서장이 상사인 직급을 출력해준다

 

-- non-equi 조인

select e.employee_id,e.first_name,e.salary,j.grade_level,department_name  from employees e,job_grades j,departments d  where e.salary>=j.lowest_sal   and e.salary<=j.highest_sal  and e.department_id=d.department_id  order by j.grade_level;

non-equi 조인은 "="와 같이 어떠한 column에 대하여 같은 것을 맞추는 것이 아닌

다른 범위와 같은 것으로 조인되는 것을 의미한다

 

-- 모든 사원의 사번, 이름, 급여, 부서번호, 부서이름(+: outer 조인)

select e.employee_id,e.first_name,e.salary,e.department_id, nvl(d.department_name,'대기발령')  from employees e, departments d  where e.department_id=d.department_id(+);

oracle에서만 +를 통하여 outer join이 가능하다

 

-- ansi join(join on을 이용한 것)
-- inner join

select e.employee_id,e.first_name,e.salary,e.department_id,department_name  from employees e inner join departments d   on e.department_id=d.department_id  order by e.employee_id;

ansi join으로 join ~ on 구문을 이용하여 조인연산이 가능하다

 

-- department_id가 공통되므로 이를 alias로 사용해선 안된다

select e.employee_id,e.first_name,e.salary,department_id,department_name  from employees e inner join departments d   using (department_id)  order by e.employee_id;

department_id가 공통되므로 이를 alias로 사용해선 안된다 

주의할 사항이다. 이와 같은 조인 연산을 수행할 시에 alias를 조심하자

 

natural join, full outer join 등등