[Oracle] 오라클 기본정리(1)

<기본 SQL>

SELECT 칼럼명 , GROUP 함수

FROM 테이블명

WHERE 조건식

GROUP BY 칼럼명

HAVING 그룹 조건식

ORDER BY 컬럼 또는 표현식 [ASC / DESC]

 

- DISTINCT : 중복행을 제거함.

항상 SELECT 바로 다음에서 기술

DISTINCT뒤에 나타나는 컬럼들은 모두 DISTINCT의 영향를 받음

기본적으로 오름차순 정렬됨

 

 

- ALIAS : 나타날때 컬럼에 대하여 다른 이름을 부여함

ALIAS 생략 가능

* SQL> Select sysdate Today From dual

* SQL> Select sysdate Alias Today From dual

 

 

- GROUP BY : 전체 데이터를 소그룹으로 나눌 칼럼을 명시한다.

* SQL> Select A, Sum(B) From Test Group By A

Test 테이블의 A데이터를 기준으로 B데이터의 Sum값을 출력한다

 

 

- HAVING : GROUP에 대한 조건을 기술한다.

* SQL> Select A, Sum(B)

From Test

Group By A

Having Sum(B) < 100

=> Test 테이블의 A데이터를 기준으로 B데이터의 Sum값을 출력한다.

Having 절에 의해서 Sum(B)의 값이 100 미만인 값만 출력한다

 

 

- WHERE절에 사용되는 연산자의 3가지 부류

* AND : 여러조건을 동시에 만족한다.

* OR : 여러조건들 중 어느 하나라도 만족한다.

* NOT : 조건에 대한 반대를 돌려준다.

논리 비교 연산자

* = : 같다.

* > : 보다 크다.

* >= : 보다 크거나 같다.

* < : 보다 작다.

* <= : 보다 작거나 같다.

SQL 비교 연산자

* BETWEEN a AND b : a와 b사이에 있다.(a,b값 포함)

* IN (list) : list의 값 중 어느 하나와 일치한다.

* IS NULL : NULL값을 가졌다.

* LIKE '%문자_문자%‘ : % 는 0개서부터 여러개까지의 문자열을 나타내는 와일드카드이고

_ 는 단 하나의 문자를 나타내는 와일드카드입니다.

와일드 카드를 일반문자처럼 쓰고 싶은 경우에는 ESCAPE 옵션을 사용

WHERE name LIKE '%X_Y%' ESCAPE ''

 

[출처] [기초강좌] Oracle 개발자 입장에서 사용하기...(1) (파워빌더 커뮤니티(PBExpertClub)) |작성자 또치

 

SQL*PLUS

 

- 버퍼에 있는 명령어 편집하기

* A(PPEND) text : line의 끝에 text를 추가함

* C(HANGE)/old/new : old를 new로 바꿈

* DEL n : n line을 지움

* I(NPUT) text : 다음 line에 text를 추가함

* L(IST) : 전체 문장을 보여줌

* n text : n line전체를 text로 바꿈

* R(UN) : buffer에 있는 문장을 실행함(/ 와 같음)

* EDIT : buffer에 있는 문장을 파일로 부름(afiedt.buf)

 

- 파일에 있는 명령어 편집하기

* SAVE a : buffer에 있는 내용을 a.sql 파일로 저장

* GET a : 파일 a에 있는 내용을 buffer로 부름

* START a (=@a) : 파일 a를 실행함

* ! : UNIX Shell로 처리

#!/bin/ksh

COUNT = 1

Select sysdate From dual;

* !vi a.sql : 파일 a.sql을 vi편집기로 부름

 

- 환경설정 : SET 환경변수 값

* COLSEP (text) : 칼럼이 표시될때 칼럼간의 구별문자 기본값은 공백

* FEEDBACK (off|on) : 선택된 행이 몇행인지를 표시함 기본값은 6행 이상인 경우에 on

* HEADING (off|on) : 칼럼에 대한 HEADING를 표시함 기본 값은 on

* LINESIZE (n) : 한 라인당 표시되는 문자의 수 기본값은 80

* PAGES (n) : 한 페이지당 표시되는 라인수 기본값은 24

* PAUSE (off|on|text) : 「ENTER」키를 누를 때마다 화면이 지나감 기본값은 off

* TIMING (off|on) : SQL문장이 처리되는데 걸리는 시간을 표시 기본값은 off

* SHOW : SET 환경이 어떻게 설정되어 있는지 보는 명령어

* SHOW ALL : 전체가 어떻게 설정되어 있는지 보고 싶은 경우

* 자신이 쓰는 환경을 항상 맞추고 싶으면 login.sql 파일에 SET환경을 설정하면 됩니다.

 

- 표시형식

COL(UMN) 칼럼이나 ALIAS 옵션

- 옵션의 종류 - CLE(AR) : 칼럼에 지정된 형식을 지움

- FOR(MAT) : 형식 칼럼에 대한 표시 형식과 폭을 바꿈

- HEADING text : 칼럼에 대한 HEADING를 지정함

* CLEAR COLUMNS : 지정된 형식을 다 지우고 싶은 경우

SQL> COL name HEADING ‘사원이름’ FORMAT A10

SQL> COL salary FORMAT $9,999,999

SQL> COL salary CLEAR

 

 

 

- 변수사용

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

사용시에는 &기호를 이용

&& 기호를 사용하면 한번 입력받은 변수값을 다시 적용할 수 있음

* &기호를 이용하여 조건, 칼럼명, 테이블명, 전체 SELECT구문도 받을 수 있음

SQL> SELECT a , b, c

FROM test

WHERE a = &A값;

Enter value for A값: 112

 

ACCEPT를 사용한 변수

사용자가 SELECT구문을 사용하기 전에 미리 변수를 설정해 놓는 경우에 사용할 수 있습니다.

* ACCEPT 변수명 데이터타입 FORMAT PROMPT text HIDE

* 데이터타입 : NUMBER,CHAR,DATE 중에서 지정

* FORMAT : A10, 9,999등의 표시 형식지정

* PROMPT text : 사용자가 데이터를 입력할 때 보여주는 문장

* HIDE : password등을 지정시 화면에 보이지 않도록 함

ACCEPT p_test NUMBER PROMPT ‘테스트 값 :’

ACCEPT pswd CHAR PROMPT 'Password : ' HIDE <?xml:namespace prefix = o />

 

l 단일행 함수

 

- 문자형 함수

* UPPER : 모든 문자를 대문자로 전환

* LOWER : 모든 문자를 소문자로 전환

* INITCAP : 문자를 단어별로 앞머리는 대문자 나머지는 소문자로 전환

* CONCAT : 두 문자열을 합성. ||연산자와 같은 용도로 사용

* SUBSTR : 특정문자열의 부분을 선택

* LENGTH : 문자열의 길이를 구함

* LPAD : 왼쪽 문자 자리를 채움

Lpad(‘Test’ , 10 , ‘*’) à ******Test

* RPAD : 오른쪽 문자 자리를 채움

* LTRIM : 왼쪽 문자를 지움

LTrim(‘*****Test’, ‘*’) à Test

LTrim(‘ Test’) à Test

* RTRIM : 오른쪽 문자를 지움

* TRANSLATE : 특정 문자열을 대체

 

Translate(‘AABBCA’,’A’, ‘1’) à 11BBC1

* REPLACE : 특정 문자열을 치환

* INSTR : 특정 문자의 위치를 반환

 

응용) SQL> Select test, substr(test, Instr(test, '.')+1) www제외,

substr(test, Instr(test, '.', 1, Length(test)-Length(replace(test, '.')))+1)

From (Select 'WWW.TEST.CO.KR' test From Dual)

à WWW.TEST.CO.KR TEST.CO.KR KR

 

 

 

- 숫자형 함수

* ROUND : 숫자를 반올림

* TRUNC : 숫자를 절사

* MOD : 나누기 연산에서 나머지 구함

* POWER : 거듭제곱

POWER(5, 2) à 25

* SQRT : 제곱근

SQRT(25) à 5

* SIGN : 양수인지 음수인지 0인지를 구벌함

Sign(Num) à 양수 = 1, 0 = 1, 음수 = -1

* CHR : ASCII 값에 해당하는 문자를 구함

 

 

 

 

 

- 날짜형 함수

* SYSDATE : 현재 시스템의 날짜 및 시간을 구함

* LAST_DAY : 지정한 날짜의 해당 월의 마지막 날짜를 구함

* MONTHS_BETWEEN : 두 날짜 사이의 개월 수를 구함

MONTHS_BETWEEN(날짜값1, 날짜값2)

* ADD_MONTHS : 지정한 날짜로부터 몇 개월 후의 날짜를 구함

* ROUND : 날짜에 대한 반올림

* TRUNC : 날짜에 대한 버림

 

- 날짜에 대한 산술연산

* 날짜 + 숫자 : 날짜 특정한 날로부터 몇일 후의 날짜 계산

* 날짜 - 숫자 : 날짜 특정한 날로부터 몇일 전의 날짜 계산

* 날짜 - 날짜 : 숫자 두 날짜 사이의 차이를 숫자로 계산

 

- 변환형 함수

* TO_CHAR : 숫자나 날짜를 문자열로 변환

* TO_NUMBER : 문자를 숫자로 변환

* TO_DATE : 문자를 날짜로 변환

 

TO_CHAR(문자값,‘형식’)

* TO_CHAR에서 숫자를 문자로 변환시에 형식에 사용되는 요소

9 : 일반적인 숫자를 나타냄

0 : 앞의 빈자리를 0으로 채움

$ : dollar를 표시함

L : 지역 통화 단위(ex )

. : 소숫점을 표시함

, : 천단위를 표시함

* TO_CHAR에서 날짜를 문자로 변환시에 형식에 사용되는 요소

SCC : 세기를 표시 S는 기원전(BC)

YEAR : 연도를 알파벳으로 spelling

YYYY : 4자리 연도로 표시

YY : 끝의 2자리 연도로 표시

MONTH : 월을 알파벳으로 spelling

MON : 월의 알파벳 약어

MM : 월을 2자리 숫자로 표시

DAY : 일에 해당하는 요일

DY : 일에 해당하는 요일의 약어

DDD,DD,D : 연도,월,일 중의 날짜를 숫자로 표시

HH , HH24 : (1-12) , (0-23)중의 시간을 표시

MI : 분을 표시

SS : 초를 표시

AM(A.M.),PM(P.M.) : 오전인지 오후인지를 표시

 

TO_DATE(문자값, ‘형식’)

TO_NUMBER(문자값)

 

- DECODE 함수

* DECODE 함수는 값을 비교하여 해당하는 값을 돌려주는 함수

DECODE (형식,비교값1,결과치1,비교값2,결과치2,...기본치 )

형식 : 컬럼이나 값

비교값1 : 형식이 비교값1에 맞는지를 비교

결과값1 : 형식이 비교값1에 맞을 때 갖는 값

기본치 : 형식이 비교값1,2,...에 맞지 않을 때 가지는 값(생략되면 NULL)

 

SQL> SELECT DECODE(SUBSTR(test,-2,2), '부장',salary*1.1, '과장',salary*1.07) 보너스

FROM Test

ORDER BY 3 DESC ;

 

[출처] [기초강좌] Oracle 개발자 입장에서 사용하기...(2) (파워빌더 커뮤니티(PBExpertClub)) |작성자 또치

 

 

l 다중행 함수

 

 

 

- 그룹함수

COUNT( a ) : a의 행의 개수를 구함

AVG( a ) : a의 평균을 구함

SUM( a ) : a의 합계를 구함

MIN( a ) : a의 최소값을 구함

MAX( a ) : a의 최대값을 구함

STDDEV( a ) : a의 표준 편차를 구함

VARIANCE( a ) : a의 분산을 구함

 

* COUNT(*)를 제외한 모든 그룹함수는 NULL값을 고려하지 않습니다.

* 중복값을 제거하고 싶은 경우는 a의 앞에 DISTINCT를 기술합니다.

* MAX , MIN , COUNT를 제외한 그룹함수는 숫자타입의 데이터에만 가능합니다

 

l JOIN

 

- EQUIJOIN

컬럼에 있는 값들이 정확하게 일치하는 경우에 =연산자를 사용하여 JOIN하는 방법을 말합니다.

WHERE 절에서 JOIN조건을 기술하도록 합니다.

 

SELECT 테이블명.컬럼명, 테이블명.컬럼명, ...

FROM 테이블1, 테이블2

WHERE 테이블1.컬럼1 = 테이블2.컬럼2 ;

 

- NON-EQUIJOIN

Non-Eqijoin이란 한 칼럼의 값이 다른 칼럼의 값과 정확히 일치하지 않는 경우에

= 연산자 외의 다른 연산자를 사용하여 JOIN하는 방법을 말합니다.

 

SELECT 테이블명.컬럼명, 테이블명.컬럼명, ...

FROM 테이블1, 테이블2

WHERE 테이블1.컬럼1 BetWeen 테이블2.컬럼2 And 테이블2.컬럼3 ;

 

- OUTER JOIN

Outer join이란 JOIN조건을 만족하지 않는 경우에도

모든 행들을 다 보고자 하는 경우에 JOIN하는 방법

 

SELECT 테이블명.컬럼명, 테이블명.컬럼명, ...

FROM 테이블1, 테이블2

WHERE 테이블1.컬럼1 = 테이블2.컬럼2(+);

 

- SELF JOIN

Self join이란 한 테이블의 행을 같은 테이블에 있는 행과 연결하는 방법으로 같은

테이블을 마치 두개의 테이블인 것처럼 사용하여 JOIN하는 방법을

 

SELECT 테이블명.컬럼명, 테이블명.컬럼명, ...

FROM 테이블1, 테이블1

WHERE 테이블1.컬럼1 = 테이블1.컬럼2;

 

 

 

l SET 연산자 (집합 연산자)

 

 

 

* UNION : 각 QUERY결과의 합집합

* UNION ALL : 각 QUERY결과의 합집합에 공통부분을 더함

* INTERSECT : 각 QUERY결과의 교집합

* MINUS : 첫번째 QUERY결과와 두번째 QUERY결과의 차집합

 

 

 

l SUBQUERY

 

 

 

[SUBQUERY의 문형]

SELECT 검색할 컬럼들

FROM 테이블명

WHERE 형식 연산자 (SELECT 검색할 컬럼들

FROM 테이블명 . . . )

 

* SUBQUERY는 괄호로 묶여 있어야 합니다.

* SUBQUERY구문에서는 ORDER BY절을 포함할 수 없습니다.

* SUBQUERY는 연산자의 오른쪽에 나타나야 합니다.

* SUBQUERY에서 사용할 수 있는 연산자의 종류에는

- 단일행 연산자(=, >, >=, <, <=, <>)

- 복수행 연산자 (IN, NOT IN)가 있습니다.

- SUBQUERY를 사용할 수 있는 절의 종류

(WHERE 절 , HAVING절 , UPDATE절,INSERT구문의 INTO절, UPDATE구문의 SET절

SELECT나 DELETE의 FROM절)

 

 

 

- SINGLE ROW SUBQUERY

SUBQUERY에서 Main Query로 전달되는 행이 단 하나인 경우 단일 행 연산자를 사용합니다

SQL> Select A.col1, (select B.col3 From test1 B Where B.col3 = A.col2)

From Test

 

 

 

- MULTI ROW SUBQUERY

SUBQUERY에서 Main Query로 전달되는 행이 여러 개인 경우를 말합니다.

이런 경우는 다중 행 연산자를 사용합니다.

SUBQUERY에서 넘어오는 행이 여러 개이므로, IN 연산자가 사용된다.

SQL> Select col1, col2

From Test

Where col1 in (Select col3 From test1 where addr = ‘서울’)

 

- MULTI COLUMN SUBQUERY

SUBQUERY구문을 작성할 때 WHERE절에서 비교하는 컬럼이 하나가 아니라

여러개의 컬럼을 동시에 비교하는 경우를 말하며, 이런 경우를 Pair-wise되었다고 합니다.

 

* Non-Pairwise SUBQUERY

SQL> SELECT col1, col2

FROM Test

WHERE col3 IN (SELECT MIN(col3)

FROM Test1

GROUP BY col1 );

 

* Pairwise SUBQUERY

SQL> SELECT col1, col2

FROM Test

WHERE (col1, col3) IN

(SELECT col1, MIN(col3)

FROM test1

GROUP BY col1 );

 

 

 

- FROM절에서의 SUBQUERY

한 테이블에 데이터 양이 많은 경우에는 FROM절에 테이블 전체를 기술하여 사용하면 효율이 떨어질 수

있으므로 이런 경우에는 필요한 행과 열만을 선택하여 FROM절에 SUBQUERY로 기술함으로써 효율적인

데이터 검색을 할 수 있습니다.

SQL> SELECT a.col1, a.col2, B.col1

FROM (SELECT col1, col2

FROM test1

WHERE col3 = 3 ) A , test2 B

WHERE A.col1 = B.col1

 

 

 

- HAVING절에서의 SUBQUERY

일반적인 조건은 WHERE절에서 기술하지만, GROUP에 대한 조건은 HAVING절에서 기술합니다.

이 때 HAVING의 조건에 기술할 값이 주어져 있지 않은 경우에 모르는 값에 대한 데이터를

검색하기 위하여 SUBQUERY를 사용할 수 있습니다

SQL> SELECT col1, AVG(col3)

ROM test1

Group By col1

HAVING AVG(col3) > (SELECT AVG(col3) From Test2 Where col1 = ‘TEST’)

 

 

 

- CORRELATED SUBQUERY

Outer Query의 candidate row가 더 이상 남지 않을때까지 반복됩니다.

CORRELATED SUBQUERY인 경우는 Outer Query의 각 행에 대해서 Inner Query가 매번 수행됩니다.

SELECT col1, col2, col3

FROM test1 A

WHERE col3 < (SELECT AVG(col3) FROM test1 WHERE col1 = A.col1)

* 바로 LINE의 A라는 테이블을 WHERE절에서 사용하게 되면

내부적으로 CORRELATE SUBQUERY의 진행방식을 따르게 됩니다.

[출처] [기초강좌] Oracle 개발자 입장에서 사용하기...(3) (파워빌더 커뮤니티(PBExpertClub)) |작성자 또치