Oracle 정리(2)

단일행 함수 : 하나의 행에 대해서 함수가 적용되는것

그룹 함수 : 여러 행에 걸쳐서 함수가 적용되는것

sum(합), avg(평균), count(객수), max(최대값), min(최소값), stddev(표준편차), variance(분산)

그룹함수를 사용할 때에는 여러 행에 적용되지 않는 단순 컬럼을 함께 사용하면 오류가 뜬다.

그룹함수를 사용할 때 특정 컬럼을 중심으로 구분한다면 group by를 사용하고, group by에서 조건은 having을 사용한다.

 

select ename, MAX(SAL) from emp;

그룹함수를 사용할때에는 ename 같이 여러행에 적용되지 않는 단순컬럼을 함께 사용하면 오류가 뜬다 subquery를 사용하면 이러한 문제를 해결할 수 있다.

 

select deptno, round(sal,3) from emp;

select sum(sal) from emp;

평균을 구하는 avg 를 사용할때 소수점 몇번째 까지 보여라 하는건 round 함수를 사용하면 된다.

select round(avg(sal),2) from emp; 

 

분석함수 : 기존 select 로 해결하기 어려운 쿼리를 수행해주는데 순위, 누적합계, 이동 평균등을 구하는데 필요한 함수이다. 일반 그룹함수는 여러행에 함수가 적용되어서 하나의 결과만 보이지만, 분석함수는 여러행에 여러가지를 적용해서 여러 결과가 나올수 있다. 보통 order by 전에 사용 된다.

분석함수는 select 와 order by와 함께 사용되는데 over ( ~ order by ~) 의 구문을 같는다

분석함수들은 : Rank(), first_value(), last_value(), row_number() 등이 있다.

 

정규 표현식 : 기존의 함수를 확장한 개념으로써 매우 정밀하게 쿼리를 수행해준다.(sed 편집기)

 

UNION: 두개의 테이블에서 모든 컬럼 항목이 일치하는 경우에 두 테이블을 합칠때 사용한다

(예를 들어 각 나라의 면적 인구 국기 등등 으로 묶을때 항목이 나라마다 같아서 사용할수 있다.)

 

select employee_id, department_id, salary, round(avg(salary) over(partition by department_id),0) "AVGDEPT" from employees where department_id in(10,20,30);

select rank(3000) within group(order by salary DESC) "sal_rank" from employees;

select salary, rank() over(order by salary DESC) from employees;

rank 값을 넣지 않으면 총 인원의 rank가 나온다

정규표현식

select product_name from product_information

where regexp_like (product_name, 'SS[^P');

'SS[^P]' ss로 시작되는데 p가 들어가 있지 않은 것을 출력해라

ALTER TABLE customers ADD constraint cust_email_addr check(regexp_list(cust_email, '@')) novaildate;

커스토머 테이블을 변경한다. 이메일 주소를 추가할때 @가 들어가게 해라

 

문장 합치기

select ename || ' is a ' || job from emp;

 

Distinct(중복값 제거)

select DISTINCT deptno from emp;

 

oracle 팁

oracle 은 한번 쿼리문 작성을 하면 버퍼메모리에 저장되어 있어서 명령어로 불러낼수 있다.

LIST명령(L) 마지막으로 사용한 쿼리문을 불러낼수 있다('/' 명령을 사용하면 저장되어 있는 실행할수 있다,)

RUN명령(R) 마지막으로 사용한 쿼리문의 내용을 보이고 실행시킬수 있다.

EDIT 명령(ED) : 방금 작성한 쿼리문에 대해 메모장으로 편집이 가능하다.

SAVE명령 : 작성한 쿼리문을 컴퓨터에 저장 *.sql 로 저장되고 @*.sql해서 재실행 할수있다.

replace 를 뒤에 추가하면 덮어 씌울수 있다.

Append 를 뒤에 추가하면 내용을 추가시킬수 있다.

@ 명령어로 확장자가 sql로 끝나는 문장을 실행시킬수 있다.

프로그래밍한 SQL, 쿼리 문장이 매우긴 SQL을 실행할때 사용

LINEsize : 결과를 볼때 보기가 불편하다면 LINEsize, pagesize 를 통해서 볼수 있다.

SET Linesize 200(좌우)

set pagesize 50(상하)