DB: 오라클 기초 (2)

* 오라클 자체가 설치되었다는 전제상황인 요약정리입니다.

* 사용환경

-> 이클립스 유노(Eclipse Juno R Packages)

-> 오라클 엔터프라이즈 (Oracle Database 10g)

-> easy-quantum-3.0.6 [공식 홈페이지] [이클립스 플러그인 페이지]

-> 오라클 JDBC 드라이버 (ojdbc14.jar)

03. 오라클에서 사용하는 자료형

-> char. 4000바이트. 고정길이 문자데이터

-> varchar. 4000바이트, 가변길이 문자데이터

-> number. 가변길이

-> int. 정수 4바이트

-> ROWID

-> CLOB

-> BLOB

-> BFILE

-> TIMSTAMP

-> INTERVAL YEAR TO MONTH

-> INTERVAL YEAR TO SECOND

04. 문법

01. 기본형태 select 컬럼/표현식 from 테이블/뷰명 where 조건절; --where같은 조건절부터는 자율적  * 오라클에 기본적으로 있는 테이블 기준
select employee_id,            salary+1000 --해당 컬럼에 1000 더해서 출력            salary+commission_pct --컬럼간 연산가능 from employees; --컬럼명에 '*'를 사용시 employees 테이블내 모든 컬럼 검색				select last_name,            nvl(commission_pct, 0)//NVL함수가 null값은 0으로 출력 from employees;
select last_name ||  '(' || first_name || ')' from employees; -- ||기호를 이용해서 검색결과를 합성 가능. -- 참고로 쿼리문에서 공백없는 문자열은 작은 따옴표('')로 묶어서 끼워넣기 가능.

02. Alias -> 테이블 검색결과 출력시 컬럼(필드)명을 재명명해서 보이게 한다.

select employee_id  as 사원번호            --필드명 명명법1, 공백불가           last_name     as "Family Name"  --필드명 명명법2, 큰 따옴표로 공백이 있는 이름 처리가능           hire_date            "사내 입사일자" --필드명 명명법3, 큰 따옴표를 사용시 as생략가능 from employees;

03. 조건절 -> 일반 프로그래밍 언어의 if와 비슷하다. 조건설의 구성은 컬럼/연산자/비교대상값 -> C/C++/JAVA에서의 !=가 여기서는 <> , 그리고 프로그래밍에서의 ==는 = -> like, in, between and -> AND, OR, NOT   [and, or, not으로도 상관 없다] -> order by
--where 사용예시 select employee_id 사원번호,   salary 연봉 from employees where department_id = 90; --부서ID가 90으로 조건절 명시	--in 사용예시 select employee_id 사번,   commission_pct 수당 from employees where commission_pct in(0.3, 0.1, 0.25); --수당 0.3/0.1/0.25을 조건절 명시	--between and 사용예시 select employee_id 사번,   salary 급여 from employees where salary between 2000 and 5000; --급여 2000~5000을 조건절 명시
--and 사용예시 select job_id 직무,   salary 급여,   hire_date 입사 from employees where salary > 3000 and hire_date < '1990-01-01';	--or 사용예시 select first_name 이름,   salary 급여,   department_id 부서번호 from employees where department_id = 50 OR salary = 7000;	--not 사용예시 select department_id 부서코드,   salary 급여,   hire_date 입사 from employees where not (department_id = 90)           (salary <> 7000);
--any 사용예시 select employee_id from employees where employee_id > any(150, 200);	--order by 사용예시 select first_name 이름,  salary 연봉 from employees where salary >= 5000 order by  salary desc;     //올림순                 first_name asc; //내림순
--like 사용예시 select employee_id 사원번호,   first_name 이름1,   last_name 이름2 from employees where last_name like 'K%';--K로 시작하는 문자열 --특정문자로 끝나는거 : '%n' 등등 --특정문자가 끼어 있는거 : '%h%', '_a%'등등

05. 기본함수

01. 듀얼 테이블(가상 테이블) -> 간단한 계산값이나 시간출력에 쓸법

select 10+20 from dual;

02. 숫자함수
--반올림 select round(123.567) 소수 첫째 자리반올림 round(123.567, 1) 소수 둘째에서 반올림 round(123.567, -1) 일의 자리 반올림 from dual;	--버림 select trunc(123.567) "소수자리 버림", trunc(123.567, 1) "소수 둘째까지 버림", trunc(123.567, -1) "일의 자리 버림" from dual;
--천장함수 select ceil(2.1) from dual; --결과는 3	--바닥함수 select floor(2.1) from dual; --결과는 2
--sign함수 select sign(-10) 음수, sign(0) 그냥0, sign(10) 양수 from dual;	--MOD[%], 나눗셈 나머지값 select mod(1,3), mod(2,3) from dual;	--제곱 select power(2,10) from dual; --2^10인 1024가 나옴.

03. 문자함수
--문자열 대문자화 select upper('Welcome to Oracle') from dual; select upper(first_name) from employees;	--문자열 소문자화 select lower('Welcome to Oracle') from dual;	--첫글자만 대문자 select initcap('welcome to oracle') from dual;
--문자열 길이(공백포함) select first_name 이름, length(first_name) "문자열 길이" from employees;		--문자추출 select substr('Welcome to Oracle 10g', 4, 3) from dual;--4칸부터 3글자
--특정문자의 위치 --문자열or 칼럼, 찾는 글자, 시작위치, 몇번째인가 select instr('Welcome to Oracle', 'e', 3, 2) from dual;		--문자변환 select chr(65) from dual;
--출력조절 select lpad('Oracle 10g', 20, '#') "우측정렬+공백채우기" from dual; select rpad('Oracle 10g', 20, '#') "좌측정렬+공백채우기" from dual;		--아스키변환 select ascii('a') from dual;
--특정문자열 삭제 select ltrim('     Oracle 10g     ') from dual; select ltrim('---Oracle 10g---', '---') "좌측문자열 삭제" from dual; select rtrim('---Oracle 10g---', '---') "우측문자열 삭제" from dual; select trim('-' from '---Oracle 10g---') 지정문자열_완전삭제 from dual;

04. 날짜 함수
--SYSDATE select sysdate 오늘, hire_date 입사일, trunc(Sysdate - hire_date) 근무일 from employees;	--날짜함수 여러가지 select sysdate 오늘날짜, next_day(sysdate, '토'), months_between(sysdate, hire_date) 근무개월, add_months(hire_date, 6) 개월추가, last_day(hire_date) "당월_말일" from dual;

05. 표기변환 00 빈자리 99 일의 자리 . 소수점 이하 , 천단위 구분 $ 달러표기 L 국가별 화폐단위 MI 오른쪽 마이너스 부호 EEEE 과학적 표시
select to_char(12345, '000,999') 숫자1, to_char(12345.67, '99,999.99') 숫자2, to_char(12345, '$99,999') 숫자3, to_char(12345, 'L99,999') 숫자4 from dual;	select 10 + to_number('1'), to_date('2012/01/01', 'YYYY-MM-DD HH:MI:SS'), sysdate-to_date('2006/01/01', 'YYYY/MM/DD'), trunc(sysdate-to_date('2006/01/01', 'YYYY/MM/DD')) 일, hire_date 입사일 from employees where hire_date < to_date('1999-01-01', 'YYYY-MM-DD');

06. DECODE decode(A, B, '문자열1', NULL) decode(A, B, '문자열1', '문자열2') decode(A, B, '문자열1')  -> 삼항연산자와 비슷하게 A==B를 따져서 참/거짓에 맞게 실행

select employee_id 사번,  department_id 부서코드,  decode(department_id, 90, '영업부', --department_id가 90이면 영업부   60, '개발부', --department_id가 60이면 개발부   50, '관리부', --department_id가 50이면 관리부   '부서명 없음') 부서명 --(else) 나머지들은 부서명 없음으로 출력 from employees;

07. CASE함수
case 표현식 when 조건1 then 결과1,   when 조건2 then 결과2,   else 결과 in end 컬럼명	select employee_id 사원번호,  department_id 부서코드,  case when department_id = 90 then '영업부'   when department_id = 60 then '개발부'   when department_id = 50 then '관리부'   else '알수없음'  end 부서명 from employees;

08. 그룹함수 -> 하나의 결과만 산출. NULL값은 알아서 처리한다
--SUM함수 select sum(salary) from employees;	--AVG함수 select avg(salary) from employees;	--MAX, MIN함수 select max(salary) 최대연봉,   min(salary) 최저연봉 from employees;
--COUNT함수 select count(employee_id) 사번,  count(commission_pct) 수당 from employees;	--GROUP BY select department_id 부서코드,   count(commission_pct) 개수 from employees group by department_id;	--테이블내 행수 출력 select count(*) from employees;

06. 집합함수

01. 컬럼간의 집합처리 -> 두 집합의 select절에 오는 컬럼의 갯수/데이터형이 일치해야 한다.
--UNION, 합집합 select department_id from employees --집합A where department_id is not null union select department_id from departments --집합B where department_id is not null; select department_id from employees where manager_id is not null union all --중복제거 없이 select manager_id from departments where manager_id is not null	--INTERSECT, 교집합 select department_id from employees intersect select manager_id from departments;	--MINUS, 차집합 select manager_id from employees minus select manager_id from departments;

02. JOIN -> 오라클은 관계형 DB, 각 테이블끼리 서로 특정한 규칙이나 관게가 있도록 설계해야 한다. -> 2개 이상의 테이블을 접점이 있는 컬럼을 사용해서 1개의 테이블처럼 조합해서 사용이 가능. -> Outer Join : 두개 이상의 테이블 조인 시 한쪽 테이블의 행에 다른 쪽 테이블에 일치하는 행이 없더라도 다른쪽 테이블의 내용을 전부 출력.
--EQUI JOIN, 테이블명 붙여서 사용 select e.employee_id 사번,  e.salary,  j.min_salary,  j.max_salary from employees e, jobs j where e.job_id = j.job_id;	--SELF JOIN select e.employee_id,  e.manager_id,  m.last_name from  employees e, employees m where e.manager_id = m.employee_id;	--Outer Join select e.employee_id,  e.manager_id,  m.last_name from  employees e, employees m where e.manager_id = m.employee_id(+);

03. 서브쿼리 -> where에 쿼리문 완전형태(?) 1문장을 통째로 넣는것.

--다중행 서브쿼리 select department_id from employees where department_id=(select department_id from employees where first_name='Bruce');

04. 인라인 뷰

select e.first_name 사원명,  d.department_name from employees e, (select department_id,     department_name     from departments) d where e.department_id = d.department_id and e.first_name='Jack';