본문 바로가기

Database/Oracle

오라클 Basic


* 데이터 검색


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의 진행방식을 따르게 됩니다.