[오라클]SQL 기본 핸들링

* 데이터 검색

SELECT 칼럼명 , GROUP 함수

FROM 테이블명

WHERE 조건식

GROUP BY 칼럼명

HAVING 조건식

ORDER BY 칼럼이나 표현식 ;

 

 

- DISTINCT : 중복행을 제거함. 항상 SELECT 바로 다음에서 기술

DISTINCT뒤에 나타나는 컬럼들은 모두 DISTINCT의 영향를 받음 기본적으로 오름차순 정렬됨

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

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

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

 

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

1. 논리 연산자

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

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

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

2. 논리 비교 연산자 - = : 같다.

- > : 보다 크다.

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

- < : 보다 작다.

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

 

SQL 비교 연산자 - BETWEEN a AND b : a와 b사이에 있다.(a,b값 포함)

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

- IS NULL : NULL값을 가졌다.

LIKE '%문자_문자%' : % 는 0개서부터 여러개까지의 문자열을 나타내는

와일드카드 (wildcard)이고 '_' 는 단 하나의 문자를 나타내는 와일드카드입니다.

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

WHERE name LIKE '%X_Y%' ESCAPE ''

 

EX)

SQL> SELECT name,salary*18 AS 연봉

2 FROM s_emp;

SQL> SELECT name||' '||title

2 FROM s_emp ;

 

SQL> SELECT name, salary*18 연봉 , title

2 FROM s_emp

3 ORDER BY salary*18 DESC ;

 

SQL> SELECT name , salary

2 FROM s_emp

3 WHERE salary BETWEEN 1000 AND 1500;

 

SQL> SELECT name, title , dept_id

2 FROM s_emp

3 WHERE dept_id IN (110,113) ;

 

SQL> SELECT name, start_date , title

2 WHERE start_date BETWEEN '01-JAN-91'

3 AND '31-DEC-91' ;

 

SQL> SELECT dept_id , count(*) 인원수

2 FROM s_emp

3 WHERE dept_id = 110

4 GROUP BY dept_id ;

 

SQL> SELECT dept_id , AVG(salary)

2 FROM s_emp

3 GROUP BY dept_id ;

 

SQL> SELECT dept_id , AVG(salary)

2 FROM s_emp

3 WHERE title = '사원'

4 GROUP BY dept_id ;

 

SQL> SELECT dept_id , title , COUNT(*)

2 FROM s_emp

3 GROUP BY dept_id , title ;

 

SQL> SELECT title , AVG(salary) , COUNT(*)

2 FROM s_emp

3 GROUP BY title

4 HAVING COUNT(*) > 2 ;

 

SQL> SELECT title , SUM(salary) 급여총합

2 FROM s_emp

3 WHERE title NOT LIKE '%부장'

5 HAVING SUM(salary) > 10000

6 ORDER BY SUM(salary) ;

 

* 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로 나들이

!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를 지정함

ex)

SQL> COL name HEADING '사원이름' FORMAT A10

SQL> COL salary FORMAT $9,999,999

SQL> COL salary CLEAR

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

 

- 변수사용

& 변수 사용 :

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

사용시에는 &기호를 이용

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

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

EX)

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

 

 

- ACCEPT를 사용한 변수

사용자가 SELECT구문을 사용하기 전에 미리 변수를 설정해 놓는 경우에 ACCEPT를

사용할 수 있습니다.

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

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

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

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

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

EX)

SQL> ACCEPT p_salary NUMBER PROMPT '급여 입력 :'

SQL> ACCEPT pswd CHAR PROMPT 'Password : ' HIDE

 

 

 

* 단일행 함수

- 문자형 함수

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

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

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

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

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

LENGTH : 문자열의 길이를 구함

LPAD : 왼쪽 문자 자리를 채움

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

LTRIM : 왼쪽 문자를 지움

RTRIM : 오른쪽 문자를 지움

TRANSLATE : 특정 문자열을 대체

REPLACE : 특정 문자열을 대신

 

UPPER(문자값) : UPPER('Oracle Server')

→ ORACLE SERVER

LOWER(문자값) : LOWER('Oracle Server')

→ oracle server

INITCAP(문자값) : INITCAP('Oracle Server')

→ Oracle Server

CONCAT(문자값1, 문자값2) : CONCAT('Oracle',' Server')

→ Oracle Server

SUBSTR(문자값, a, b)

 

a 선택할 문자열의 시작위치. 음수면 끝에서부터 시작

b 선택할 문자열의 개수. 이 인자는 생략할 수 있으며, 생략할 경우 문자열의 끝까지 선택

 

: SUBSTR('강남구 역삼동',5,2)

→ 역삼

LENGTH(문자값1, a, 문자값2) : LENGTH('홍길동')

→ 3

LPAD(문자값1, a, 문자값2)

RPAD(문자값1, a, 문자값2)

 

a : 전체 채울 자리수

문자값2 : 채울 문자

생략할 수 있으며, 생략되면 공백값임

 

: LPAD('홍길동',10 '*')

→ ****홍길동

LTRIM(문자값1, 문자값2)

RTRIM(문자값1, 문자값2)

문자값1에서 왼쪽(오른쪽)에서부터

더이상 문자값2를 만나지 않을 때까지 지움

 

: LTRIM('XXAXBA','X')

→ AXBA

TRANSLATE(문자값, a, b)

a 대체하고 싶은 문자(from)

b 대체할 결과의 문자(to)

 

: TRANSLATE('AABBA','B','C')

→ AACCA

REPLACE (문자값, a, b)

a 바꾸고 싶은 문자(from)

b 바꿀 결과의 문자(to)

 

: REPLACE ('JACK and JUE','J','BL')

→ BLACK and BLUE

 

- 숫자형 함수

ROUND : 숫자를 반올림

TRUNC : 숫자를 절사

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

POWER : 거듭제곱

SQRT : 제곱근

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

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

ROUND(숫자값, a), TRUNC(숫자값, a) a 숫자값을 반올림(버림)하여 a자리까지 돌려줌.

a가 양수이면 소수이하자리를, 음수이면 정수 부분 자리임 생략할 수 있으며, 생략하면 0

 

: ROUND(35.735,2)→35.74

MOD(숫자값, a )

a 숫자값을 나누기 할 수 있음

 

: MOD(7,2)→1

POWER(숫자값1, 숫자값2) : POWER(3,2)→9

SQRT (숫자값) : SQRT(25)→5

SIGN(숫자값) : SIGN(-15)→-1

CHR(숫자값) : CHR(65)→A

 

- 날짜형 함수

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

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

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

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

ROUND : 날짜에 대한 반올림

TRUNC : 날짜에 대한 버림

 

SYSDATE :

SYSDATE → 10-MAY-99

 

LAST_DAY(날짜값) :

LAST_DAY('17-FEB-98') → 28-FEB-98

 

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

MONTHS_BETWEEN('26-APR-97','22-JUL-95') → 21.1290323

 

ADD_MONTHS(날짜값, 숫자값) :

ADD_MONTHS('22-JUL-95',21) → 22-APR-97

 

ROUND(날짜값, 자리수) :

현재 날짜가 1999년 5월 10일이라고 가정하자.

 

ROUND(SYSDATE,'MONTH') → 01-MAY-99

 

TRUNC(날짜값, 자리수) :

현재 날짜가 1999년 5월 10일이라고 가정하자.

 

TRUNC(SYSDATE,'YEAR') → 01-JAN-99

 

- 날짜에 대한 산술연산

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

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

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

 

- 변환형 함수

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

TO_NUMBER : 문자를 숫자로 변환

TO_DATE : 문자를 날짜로 변환

 

- 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_CHAR(문자값,'형식')

숫자를 문자로 변환

: TO_CHAR(350000,'$999,999')→ $350,000

 

숫자를 날짜로 변환

: TO_CHAR(SYSDATE,'YY/MM/DD')→ 95/05/25

TO_DATE(문자값, '형식') : TO_DATE('10 SEPTEMBER 1992','DD MONTH YYYY')→10-SEP-92

TO_NUMBER(문자값) : TO_NUMBER('1234')→ 1234

 

- DECODE 함수

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

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

형식 : 컬럼이나 값

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

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

기본치 : 형식이 비교값1,2,...에 맞지 않을 때 가지는 값

생략될 수 있으며, 생략되면 NULL이다.

 

EX)

SQL> SELECT name,title,DECODE(SUBSTR(title,-2,2),

2 '부장',salary*1.1,

3 '과장',salary*1.07,

4 '사원',salary*1.05,

5 salary) 이번달급여

6 FROM s_emp

7 ORDER BY 3 DESC ;

 

* 다중행 함수

- 그룹함수

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를 제외한 그룹함수는 숫자타입의 데이터에만 가능합니다

 

* JOIN

- EQUIJOIN

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

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

 

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

FROM 테이블1, 테이블2

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

 

EX)

SQL> SELECT s_emp.name, s_emp.dept_id , s_dept.name

2 FROM s_emp , s_dept

3 WHERE s_emp.dept_id = s_dept.id ;

SQL> SELECT e.name 사원명, e.dept_id , d.name 부서명

2 FROM s_emp e, s_dept d

3 WHERE e.dept_id = d.id ;

SQL> SELECT e.name 사원명, d.name 부서명

2 FROM s_emp e, s_dept d , s_region r

3 WHERE e.dept_id = d.id

4 AND d.region_id = r.id

5 AND r.name LIKE '%서울%' ;

 

- NON-EQUIJOIN

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

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

 

SQL> SELECT e.name, e.salary , g.grade 급여등급

2 FROM s_emp e, salgrade g

3 WHERE e.salary BETWEEN g.losal AND g.hisal ;

 

- OUTER JOIN

Outer join이란 JOIN조건을 만족하지 않는 경우에도 모든 행들을 다 보고자하는 경우에 JOIN하는 방법

SQL> SELECT e.name 사원명 ,e.id,c.name 고객명

2 FROM s_emp e, s_customer c

3 WHERE e.id (+) = c.sales_rep_id

4 ORDER BY 2;

 

- SELF JOIN

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

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

SQL> SELECT w.id 사번, w.name 사원명 ,

2 m.id 부서장사번, m.name 부서장명

3 FROM s_emp w, s_emp m

4 WHERE w.manager_id = m.id ;

 

- SET 연산자의 활용

UNION : 각 QUERY결과의 합집합

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

INTERSECT : 각 QUERY결과의 교집합

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

 

SELECT 칼럼1, 칼럼2 . . .

FROM 테이블1 . . .

SET 연산자

SELECT 칼럼1`, 칼럼2` . . .

FROM 테이블2 . . .

ORDER BY ;

 

SQL> SELECT name , dept_id , title

2 FROM s_emp

3 WHERE dept_id = 110

4 UNION

5 SELECT name , dept_id , title

6 FROM s_emp

7 WHERE dept_id = 113

8 ORDER BY 1 ;

 

* 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 name, title , dept_id

2 FROM s_emp

3 WHERE dept_id = 김정미가 근무하는 부서;

SQL> SELECT dept_id

2 FROM s_emp

3 WHERE name = '김정미';

SQL> SELECT name, title , dept_id

2 FROM s_emp

3 WHERE dept_id = (SELECT dept_id

4 FROM s_emp

5 WHERE name = '김정미') ;

SQL> SELECT name, salary ,title

2 FROM s_emp

3 WHERE title = (SELECT title

4 FROM s_emp

5 WHERE name = '최정선')

6 ORDER BY salary ;

 

- MULTI ROW SUBQUERY

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

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

SUBQUERY에서 넘어오는 행이 여러 개이므로, 이때 사용되는 연산자는 IN임을 주의하세요.

SQL> SELECT name, dept_id

2 FROM s_emp

3 WHERE dept_id IN (SELECT id

4 FROM s_dept

5 WHERE region_id =3 ) ;

SQL> SELECT name , dept_id

2 FROM s_emp

3 WHERE dept_id IN

4 (SELECT id

5 FROM s_dept

6 WHERE region_id =

7 (SELECT id

8 FROM s_region

9 WHERE name = '서울특별시') ) ;

 

- MULTI COLUMN SUBQUERY

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

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

 

- Non-Pairwise SUBQUERY

SQL> SELECT name, dept_id, salary

2 FROM s_emp

3 WHERE salary IN (SELECT MIN(salary)

4 FROM s_emp

5 GROUP BY dept_id );

 

Pairwise SUBQUERY

SQL> SELECT name, dept_id, salary

2 FROM s_emp

3 WHERE (salary,dept_id) IN

4 (SELECT MIN(salary),dept_id

5 FROM s_emp

6 GROUP BY dept_id );

 

- FROM절에서의 SUBQUERY

한 테이블에 데이터 양이 많은 경우에는 FROM절에 테이블 전체를 기술하여

사용하면 효율이 떨어질 수 있으므로 이런 경우에는 필요한 행과 열만을

선택하여 FROM절에 SUBQUERY로 기술함으로써 효율적인 데이터 검색을 할 수 있습니다.

SQL> SELECT e.name , e.title , d.name

2 FROM (SELECT name ,title , dept_id

3 FROM s_emp

4 WHERE title = '사원') e , s_dept d

5 WHERE e.dept_id = d.id ;

 

- HAVING절에서의 SUBQUERY

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

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

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

SQL> SELECT dept_id , AVG(salary)

2 FROM s_emp

3 GROUP BY dept_id

4 HAVING AVG(salary) > (SELECT AVG(salary)

5 FROM s_emp

6 WHERE dept_id = 113) ;

 

- CORRELATED SUBQUERY

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

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

 

SQL> SELECT name, salary ,dept_id

2 FROM s_emp outer

3 where salary < (SELECT AVG(salary)

4 FROM s_emp

5 WHERE dept_id = outer.dept_id) ;

 

바로 2번째 LINE의 OUTER라는 테이블 ALLAS를 5번 LINE에서 사용하게 되면

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