[Oracle] Introduction to Oracle 9i : SQL

/*****************************************

Introduction to  Oracle 9i : SQL

작성일  : 2007.02.06

작성자  : 버리 (www.flyburi.com)

*****************************************/

1. SQL구문

select distinct * column alias(빈칸,AS,””)

from table_name

where column, 표현식, value, 비교연산자(비교,select문, 논리연산자)

order by Asc(default), desc

è     Distinct와 order by를 쓰면 내부 그룹화가 발생한다.

è     Distinct는 그룹내 정렬이 이루어진다.

2. SQL함수

함수 : 하나 또는 여러 개의 인수를 받아들여 처리한 다음 결과값을 하나를 얻을수있다.

함수의 종류

1)    인수 하나 : 단일행 함수 – 문자, 숫자, 날짜, 일반, 변환

2)    인수 여러 개 : 다중행 함수(그룹함수)

문자함수

1)    대문자 조작함수 : LOWER, UPPER, INICAP(단어 첫글자만 대문자)

2)    문자 조작함수 : CONCAT, SUBSTR, LENGTH, INSTR, LPAD, RPAD, TRIM

알아두기!)

LOWER, UPPER를 사용하는 때

오라클 where 절에서는 인수를 비교할 때 인수를 ASCII값으로 찾기 때문에 대/소문자 구별한다. (내가 정확한 Format을 모를 때)

날짜함수

  오라클 데이터베이스는 다음과 같은 내부 숫자 형식으로 날짜를 저장합니다. “세기, 연도, 월, 일, 시, 분, 초”

변환함수

1)    암시적(implicit) 데이터 유형 변환

2)    명시적(explicit) 데이터 유형 변환

일반함수

    1) NVL 함수 - 무결성규칙을 따라야 한다.

uk : 쓸수있다.

pk : null값을 가지고 있지 않기 때문에 NVL 함수를 쓸 필요가 없다. 굳이 쓰려면 쓸 수는 있다.

2) NVL2 함수

null에 해당되는지 않되는지만 결과를 리턴해 준다. 어떤 값을 계산해서 리턴하지는 않는다.

3) NULLIF 함수

NULLIF(expr1, expr2) : 두 표현식 비교하여 동일한 경우 null을 반환한다. 동일하지 않을때 첫번째 표현식을 반환한다.

4) COALESCE 함수

첫번째 표현식 null이 아닌 경우 해당 표현식을 반환한다. null인 경우 나머지 표현식에 대해 COALESCE 함수를 적용한다.

3. 조건 표현식

distinct, order by 는 정렬, 그룹화 현상을 일으키기 때문에 퍼포먼스상 매우 좋지 않기 때문에 실무에서 잘 쓰지 않는다.

Cf) 스와핑, 페이징.

DECODE함수는 CASE 표현식과 유사함. 성능이 CASE보다 더 좋음.

함수의 포인트!!! TO_CHAR, NVL, DECODE

4. JOIN

select ename, deptno

from emp, dept;

ename -> 20건, deptno -> 8건

20 * 8 = 160건 : 카티시안 곱 - 조인 조건을 생략한 경우 조인 조건이 부적합하다.

join을 사용하면 퍼포먼스가 떨어진다.

join보다 Sub Query를 사용하는게 퍼포먼스가 좋다.

join조건 쓸때 fk가 설정되어 있는 table이 앞에 나오는 게 빠르다.

1) Equal Join : pk, fk 비교할때, 같은 column이 있을경우 두 테이블에서 비교

column 이름은 36자까지 생성할 수 있다. (스키마에 alias 줄 수 있다. - 인라인뷰)

alias를 사용하면 oracle server는 alias명으로 검색한다.

기본 스키마는 생략가능하다.

alias를 사용하면 메모리 사용이 줄어든다.

alias를 쓸때는 alias를 전부 쓰고, table full name을 쓸 때는 full name을 써야한다.

alias와 table full name을 쓰면 에러난다.

Join table개수는 n-1 (n:join시킬 table수)

Join조건과 where 조건을 쓸 때는 and!! 만 OR!을 쓰면 에러난다.

where 조건과 join 조건 순서는 상관없지만 join조건이 앞에 나오는 것이 퍼포먼스상 좋다.

2) Non Equal Join : Between A and B

동등 연산자가 아닌 연산자를 포함하는 조인 조건, 전혀 다른 Data 값이 존재했을 때 사용한다.

3) Outer Join

Record는 포함하고 있지 않은 쪽에 (+)를 붙여준다.

부족한 쪽에 null 값을 삽입

4) Self Join

하나의 Table을 Alias를 줘서 여러개의 Table인 것처럼 한다.

5. 그룹함수

-> null값을 포함하지 않는다.

count(*)와 count(commission_pct)의 차이를 느낄수 있는 상황

Q) 우리 회사 직원의 commission_pct의 평균을 구해라.

ex1) select avg(commission_pct) from employees;

결과) .2125

ex2) select sum(commission_pct)/20 from employees;

결과) .0425

avg함수를 사용했을때 결과가 ex2)의 결과로 나와야하는데

ex1)에서는 sum(commission_pct)/4로 나눈 결과값이다

암시적으로 avg는 null값을 포함하지 않는다.

=> 해결을 위해,

avg(nvl(commission_pct,0));

SQL문 정리

select distinct * column name alias

from table

where

group by

having

order by

6. SubQuery

SubQuery는 알려지지 않은 값을 찾고 싶을 때 사용하라.

Join문장보다 Subquery를 실행하자!

From 절의 SubQuery에서는 order by 절이 사용가능하지만,

Where, having 절의 SubQuery에서는 order by 절이 사용불가능

SubQuery 결과가 몇 개냐에 따라 단일/다중 연산자를 판단한다.

SubQuery null이 도출될 위험이 있을 때는 사용하지 마라.

SubQuery에서 not in을 피하는게 좋다. Not in은 null값을 찾을 수가 없다. (거의 no rows selected 일 가능성이 높다.)

쓰고싶다면, Is null을 써라.

Ex) select ename

From emp

Where deptno = (select deptno

                     From dept

                     Where dname=’%a%’);

결과 및 해석 ) 다른 테이블을 서로 비교하더라도 가능하다. Subquery의 내용은 결과값을 도출하여 값을 비교하기 때문에 가능.

Ex) select ename

from emp e, dept d

where e.deptno = d.deptno;

이 경우에는 join을 써서 할수 있지만, SubQuery가 퍼포먼스상 좋다~

1) ANY 연산자

<ANY : means less than the maximum. : 최대값보다 작은값(최대값 제외)

>ANY : means more than the minimum : 최소값보다 큰값(최소값 제외)

=ANY : IN연산자와 같은 역할

2) ALL 연산자

>ALL : means more than the maximum : 최대값 이상의 값

<ALL : means less than the minimum : 최소값 이하의 값

Ex) data => 9000, 6000, 4000

연산자          결과

In연산자 =>         9000,6000,4000

<ANY   =>  6000, 4000

>ANY   =>  9000, 6000

>ALL    => 9000이상의 값

<ALL    => 4000이하의 값

7. iSQL*Plus

  * 치환변수 사용

  임시적으로 값을 저장.

문장 실행할 때마다 값이 바뀔 때 사용한다.

반복작업을 수행할 때 편리하다.

  &변수 : 사용자가 원하는 데이터를 사용할 때마다 입력하고 싶은 경우에 치환변수를 사용.

&&변수 : 한번 입력받은 변수값을 다시 적용할 수 있음

SQL> SELECT id,name,dept_id
2 FROM s_emp
3 WHERE dept_id = &부서번호 ;
Enter value for 부서번호: 112
old 3: WHERE dept_id = &부서번호
new 3: WHERE dept_id = 112