레도널스의 개발자 되어가기

서브쿼리 (계속)

SELECT employee_id, last_name, salary

FROM employees

WHERE salary > (SELECT AVG(salary) FROM employees);

=> 실행 가능

SELECT employee_id, last_name, salary

FROM employees

WHERE salary > (SELECT AVG(salary) FROM employees

                GROUP BY department_id);

=> 실행 불가

SELECT employee_id, last_name, salary

FROM employees

WHERE salary IN (SELECT AVG(salary) FROM employees

                 GROUP BY department_id);

=> 실행 가능

* 서브쿼리의 결과값이 null일 때, 비교연산자를 사용하면 결과를 반환하지 않는다.

SELECT employee_id, last_name, manager_id, salary

FROM employees

WHERE manager_id = (SELECT manager_id FROM employees

                    WHERE last_name = 'Adam');

※ Adam이라는 사람이 존재하지 않을 때(null)

* Multiple-Row Subqueries : 여러 행 서브쿼리

>> ANY : 아무거나(어느 것 중 하나라도)

ex)

SELECT employee_id, last_name, salary

FROM employees

WHERE salary > ANY (SELECT AVG(salary) FROM employees

                    GROUP BY department_id);

※ = ANY와 IN은 같다.

ex)

SELECT employee_id, last_name, salary

FROM employees

WHERE salary IN (SELECT AVG(salary) FROM employees

                 GROUP BY department_id);

=

SELECT employee_id, last_name, salary

FROM employees

WHERE salary = ANY (SELECT AVG(salary) FROM employees

                    GROUP BY department_id);

>> ALL : 모두

ex)

SELECT employee_id, last_name, salary

FROM employees

WHERE salary > ALL (SELECT AVG(salary) FROM employees

                    GROUP BY department_id);

>> NOT IN : 같지 않다.

※ NOT IN으로 검색할 때, 서브쿼리 값에 NULL이 포함되어 있으면 결과를 반환하지 않는다.

   왜냐하면, IN은 OR 연산인데 앞에 NOT이 붙으면 부정이 되어 AND 연산으로 바뀌기 때문.

   따라서, WHERE절에 IS NOT NULL(등 NOT NULL 조건)을 습관적으로 써 주는 것이 규칙.

SELECT emp.last_name

FROM employees emp

WHERE emp.employee_id NOT IN (SELECT mgr.manager_id

                              FROM employees mgr

                              WHERE mgr.manager_id IS NOT NULL);

* Set Operators : 집합 연산자

>> 집합 연산은 기본적으로 결과값의 중복을 제거하고, 자동으로 행을 기준으로(행 단위로) 정렬해서 보여준다.

>> MINUS : 교환 법칙이 성립하지 않는다.

>> 데이터 타입이 서로 다른 테이블끼리의 합집합 방법

ex)

SELECT employee_id,   hire_date,     TO_CHAR(null) AS department_name

FROM employees

UNION

SELECT department_id, TO_DATE(null), department_name

FROM departments;

>> 집합을 정렬하는 방법(편법)

1) 순서를 정하기 위해 행을 추가한다. (한글, 영문, 숫자 등)

ex)

SELECT employee_id, hire_date, TO_CHAR(null) AS department_name, 'B'

FROM employees

UNION

SELECT department_id, TO_DATE(null), department_name, 'A'

FROM departments

ORDER BY 4;

2) 행에 별칭을 준다. (아무거나)

ex)

SELECT employee_id, hire_date, TO_CHAR(null) AS department_name, 'B' A_D

FROM employees

UNION

SELECT department_id, TO_DATE(null), department_name, 'A'

FROM departments

ORDER BY 4;

3) 추가한(의미없는) 행을 출력하지 않는 방법

ex)

COLUMN A_D NOPRINT

SELECT employee_id, hire_date, TO_CHAR(null) AS department_name, 'B' A_D

FROM employees

UNION

SELECT department_id, TO_DATE(null), department_name, 'A'

FROM departments

ORDER BY 4;

Manipulating Data

* Data Manipulation Language : DML (데이터 조작어)

>> SELECT / INSERT / UPDATE / DELETE

- Transaction(트랜잭션) 단위로 동작.

※ Transaction(트랜잭션)의 개념

>> INSERT문

ex)

INSERT INTO departments

VALUES(70,'Public Relations', 100, 1700);

- DB는 엑셀과 다르게, 행의 순서를 DBMS가 알아서 정한다(제어하지 못함). 논리적 구조.

- Session 단위로 작업하므로, Commit 하지 않으면 공유하지 않는다.

- NULL값 입력 방법

1) 암시적(Implicit) 방법

INSERT INTO departments(department_id, department_name)

VALUES (30, 'Purchasing');

2) 명시적(Explicit) 방법

INSERT INTO departments

VALUES (100, 'Finance', NULL, NULL);

- 치환 변수를 이용한 Insert

INSERT INTO departments(department_id, department_name, location_id)

VALUES (&deptno, '&dept_name', &loc_id);

>> Session 접속 중 (같은 유저로) 다시 접속하는 경우, 자동으로 commit.

>> 테이블 만들기(DDL 명령어)

ex)

CREATE TABLE sales_reps

AS 

SELECT employee_id id, last_name name, salary, commission_pct

FROM employees

WHERE 1=2;  =>  WHERE절을 일부러 틀리게 적으면, 데이터는 제외하고 테이블 구조만 가져와서 생성

※ CREATE와 같은 DDL 명령어는 자동 커밋.

>> UPDATE문

- WHERE절을 잘못 작성하면 아무것도 적용되지 않음.

- WHERE절을 작성하지 않으면 테이블의 모든 데이터가 업데이트됨. 주의!

* 트랜잭션의 특징

- 서로 다른 유저가 같은 테이블의 서로 다른 행을 수정하는 것은 가능

- 같은 테이블의 같은 행을 수정할 경우, 먼저 접속한 유저가 우선권을 가짐.

※ 자동 커밋되는 경우가 엄청 많이 존재한다. 툴에 따라 다르고 등등.

>> DELETE문

- UPDATE문과 비슷한 특징을 가짐.

- WHERE절을 잘못 작성하면 아무것도 적용되지 않음.

- WHERE절을 작성하지 않으면 테이블의 모든 데이터가 삭제됨. 주의!

* Conditional Expressions : 조건문

>> IF-THEN-ELSE logic

- CASE expression : 주로 사용

- DECODE function : 오라클 전용. 해석만 할 줄 알면 됨.

>> 예문

SELECT last_name, job_id, salary,

       CASE job_id WHEN 'IT_PROG'  THEN 1.10*salary

                   WHEN 'ST_CLERK' THEN 1.15*salary

                   WHEN 'SA_REP'   THEN 1.20*salary

                   ELSE salary

       END AS "Revised_salary"

FROM employees;

※ WHEN절 앞에 컬럼명을 쓰게 되면 동등 비교밖에 못 한다. (BETWEEN절을 쓸 수 없다.)

SELECT last_name, job_id, salary,

       CASE WHEN salary BETWEEN 2500  AND 5000  THEN 'A'

            WHEN salary BETWEEN 5001  AND 10000 THEN 'B'

            WHEN salary BETWEEN 10001 AND 20000 THEN 'C'

            ELSE 'D'

       END AS salary_grade

FROM employees

ORDER BY 4;

>> NVL2문을 CASE문으로 바꿔보기

SELECT employee_id, last_name, salary, commission_pct,

       NVL2(commission_pct, 'SAL+COMM', 'SAL') income

FROM employees

WHERE department_id IN (50, 80);

=>

SELECT employee_id, last_name, salary, commission_pct,

       CASE WHEN commission_pct IS NOT NULL THEN 'SAL+COMM'

            WHEN commission_pct IS NULL     THEN 'SAL'

       END income

FROM employees

WHERE department_id IN (50, 80);

* Group Functions : 그룹 함수

-> 한 그룹에서 결과가 하나만 나오는 함수

 - AVG

 - COUNT

 - MAX

 - MIN

 - STDDEV

 - SUM

 - VARIANCE

※ Bold체는 모든 데이터 타입이 사용 가능한 함수

>> Group Functions Syntax 그룹 함수 문법

SELECT    group_function(column), ...

FROM      table

[WHERE    condition]

[ORDER BY column];

ex)

SELECT MAX(last_name), MIN(last_name)

FROM employees;

SELECT MAX(hire_date), MIN(hire_date)

FROM employees;

SELECT AVG(salary), SUM(salary)

FROM employees

WHERE department_id = 50;

>> 그룹 함수는 null값을 빼고 계산한다.

ex)

SELECT AVG(commission_pct), AVG(NVL(commission_pct, 0))

FROM employees;

>> COUNT 함수의 사용법

SELECT COUNT(department_id), COUNT(DISTINCT department_id), COUNT(*)

FROM employees;

>> 일반적으로 그룹 함수는 그룹 함수를 사용하지 않은 컬럼을 함께 쓸 수 없다.

SELECT department_id, SUM(salary)

FROM employees;

- 쓰려면 GROUP BY절이 필요.

SELECT department_id, SUM(salary)

FROM employees

GROUP BY department_id;

- GROUP BY에 컬럼을 더 넣으면 그룹을 더 세분화할 수 있다.

  ORDER BY절은 항상 마지막 줄에.

SELECT department_id, job_id, SUM(salary)

FROM employees

GROUP BY department_id, job_id

ORDER BY 1, 2;

- GROUP BY절의 조건은 WHERE절 대신 HAVING절을 사용.

  (HAVING절 사용 시 WHERE절은 생략 가능, 추가 조건이 필요할 때 사용)

  그리고, 아래 문장은 그룹 함수의 전체 syntax

SELECT department_id, job_id, SUM(salary)

FROM employees

WHERE employee_id > 120

GROUP BY department_id, job_id

HAVING SUM(salary) > 10000

ORDER BY 1, 2;

- 그룹 함수의 중첩은 일반 컬럼이 함께 쓰일 수 없다. 행 수가 다르므로.

SELECT ROUND(AVG(salary))

FROM employees

GROUP BY department_id;

SELECT department_id, MAX(ROUND(AVG(salary)))

FROM employees

GROUP BY department_id;

* Join

>> Natural Join : 컬럼명이 같아야 조인 가능

SELECT department_id, department_name, location_id, city

FROM departments NATURAL JOIN locations;

>> USING절

SELECT employee_id, last_name, department_id, department_name

FROM employees JOIN departments

USING (department_id);

>> ON절 -> 가장 많이 사용, 응용이 가능.

SELECT employee_id, last_name, employees.department_id, department_name

FROM employees JOIN departments

ON (employees.department_id = departments.department_id);

>> 별칭 사용 가능

   별칭을 사용하게 되면 그 문장에는 원래 테이블명 대신 별칭만 존재

SELECT e.employee_id, e.last_name, e.department_id, d.department_name

FROM employees e JOIN departments d

ON (e.department_id = d.department_id);

* Equi Join, Non-Equi Join

>> Non-Equi Join : 동등하지 않은 조건의 테이블끼리 조인

ex)

SELECT e.employee_id, e.last_name, e.salary, s.grade_level

FROM employees e JOIN job_grades s

ON (e.salary BETWEEN s.lowest_sal AND s.highest_sal);

>> Natural Join, Using절은 무조건 Equi Join,

   On절에서 Equal을 사용하면 Equi Join, 아니면 Non-Equi Join

* OUTER joins : Join ~ On에서 파생된 것.

- LEFT OUTER JOIN

SELECT e.employee_id, e.last_name, e.department_id, d.department_name

FROM employees e LEFT OUTER JOIN departments d

ON (e.department_id = d.department_id);

- RIGHT OUTER JOIN

SELECT e.employee_id, e.last_name, e.department_id, d.department_name

FROM employees e RIGHT OUTER JOIN departments d

ON (e.department_id = d.department_id);

- FULL OUTER JOIN

SELECT e.employee_id, e.last_name, e.department_id, d.department_name

FROM employees e FULL OUTER JOIN departments d

ON (e.department_id = d.department_id);

* 기본 조인은 테이블 2개이며, 테이블 3개, 1개 조인 방법도 있음.

1) 테이블이 하나일 때(같은 테이블일 때) -> 별칭을 이용하여 다른 테이블인 것처럼 만듦(셀프 조인)

ex)

SELECT e.employee_id, e.last_name, e.manager_id, m.last_name

FROM employees e JOIN employees m

                 ON (e.manager_id = m.employee_id);

2) 테이블이 3개일 때 -> 3-Way 조인

ex)

SELECT e.last_name, d.department_name, l.city

FROM employees e JOIN departments d

                 ON (e.department_id = d.department_id)

                 JOIN locations l

                 ON (d.location_id = l.location_id);

* CROSS joins : 경우의 수를 찾음. 확률 계산 시 이용.

* Join 정리. 구분.

>> 문법(syntax)

- Cross join

- Natural join

- Join ~ Using

- Join ~ On -> Outer join

>> 연산자

- Equi join

- Non-Equi join

>> Join에 만족하는 행 여부

- Inner join

- Outer join

>> Table 수

- Self join

- 3-way join

* 보너스 : Oracle 전용 Join 문법

>> Join을 사용하지 않음.

SELECT e.employee_id, e.last_name, e.department_id, d.department_name

FROM employees e, departments d

WHERE e.department_id = d.department_id;

- Outer Join : 기호를 사용하여 행을 추가.

SELECT e.employee_id, e.last_name, e.department_id, d.department_name

FROM employees e, departments d

WHERE e.department_id(+) = d.department_id;

- 3-Way Join

SELECT e.employee_id, e.last_name, e.department_id, d.department_name

FROM employees e, departments d

WHERE e.department_id(+) = d.department_id

AND e.department_id > 50;

SQL Functions

SQL 함수

1. Single-row Functions 단일행 함수

* 함수는 중첩 사용 가능

* Character 함수

SELECT employee_id, last_name, LOWER(last_name)

FROM   employees

WHERE  department_id = 50;

*

SELECT UPPER('oracle database'), LOWER('Oracle Database'), INITCAP('oracle database')

FROM dual;

* 계산식(dual 테이블 이용)

SELECT 23542*254 AS Result FROM dual;

*

SELECT employee_id, last_name, department_id

FROM   employees

WHERE  LOWER(last_name) = 'king';

* 연결 함수 CONCAT

SELECT employee_id, CONCAT(first_name, last_name) AS Fullname  <-  AS는 생략 가능

FROM   employees;

SELECT employee_id, CONCAT(CONCAT(first_name,' '), last_name) Fullname

FROM   employees;

* SUBSTR (섭스타) : SUBSTR('text', 검색 시작 위치, 글자 수)

SELECT SUBSTR('HelloWorld', 6, 5) FROM dual;

SELECT SUBSTR('HelloWorld', -5, 5) FROM dual;

두 식은 같다.

검색 시작 위치에 음수를 쓰면 맨 뒤부터 찾음.

*

SELECT * FROM employees

WHERE SUBSTR(last_name, -1, 1) = 'n';

= WHERE last_name LIKE '%n';

last_name이 n으로 끝나는 사원을 찾음.

* INSTR (인스타)

SELECT INSTR('HelloWorld', 'l') FROM dual;  ->  'l'의 위치

SELECT INSTR('HelloWorld', 'l', 1, 2) FROM dual;  ->  1(1번째)부터 검색해서 'l'이 2번째 반환되는 위치

해당 사항이 없으면 0을 반환

*

SELECT * FROM employees

WHERE INSTR(last_name, 'A') <> 0;

* 패딩 함수 LPAD, RPAD (글자 수 포함, 나머지 문자는 지정한 문자로 채움)

- LPAD : 왼쪽부터 패딩을 준다.

- RPAD : 오른쪽부터 패딩을 준다.

ex)

SELECT employee_id, last_name, LPAD(salary,10,'*') AS Salary

FROM   employees

WHERE  department_id = 60;

SELECT employee_id, last_name, RPAD(salary,10,'*') AS Salary

FROM   employees

WHERE  department_id = 60;

SELECT RPAD(last_name, 15, '*') AS last_name,

       LPAD(salary, 10, '*') AS salary

FROM employees;

* TRIM 함수 : 잘라내기

ex)

SELECT TRIM('w' FROM 'window') FROM dual;  ->  모든 'w'

SELECT TRIM(LEADING 'w' FROM 'window'),  ->  선행하는(앞의) 'w'

       TRIM(TRAILING 'w' FROM 'window')  ->  따르는(뒤의) 'w'

FROM dual;

* Number Functions 숫자 함수

- 올림

SELECT ROUND(45.923,2), ROUND(45.923,0), ROUND(45.923,-1)

FROM dual;

- 버림

SELECT TRUNC(45.923,2), TRUNC(45.923,0), TRUNC(45.923,-1)

FROM dual;

- 나머지

SELECT last_name, salary, MOD(salary, 5000)

FROM employees

WHERE job_id = 'SA_REP';

* 날짜 연산하기

SELECT last_name, hire_date

FROM employees

WHERE hire_date < '1988-12-17';

* sysdate 함수

Database Server의 날짜.

서버와 클라이언트가 떨어져 있는 경우(서로 다른 타임존에 있는 경우) 시차가 발생할 수 있음.

SELECT sysdate FROM dual;

* sysdate의 표시 형식 변경하기

ex)

ALTER SESSION SET nls_date_format = 'yyyy/mm/dd hh24:mi:ss';

: Session이 유지 중일 때만 날짜 형식 변경

* sysdate는 계산이 가능

ex)

SELECT sysdate +10, sysdate -10

FROM dual;  ->  날짜 계산

SELECT sysdate, sysdate +10/24, sysdate -10/24

FROM dual;  ->  시간 계산

SELECT sysdate, sysdate +10/(24*60), sysdate -10/(24*60)

FROM dual;  ->  분 계산

*

SELECT employee_id, hire_date, sysdate-hire_date

FROM employees;

date에 시간이 함께 나온다.

-

DESC employees;

해 보면, date 데이터 타입은 길이가 없음을 알 수 있다.

그렇지만 저장할 때는 시간도 같이 저장한다.

이것을 응용하면,

-

SELECT employee_id, hire_date, TRUNC(sysdate-hire_date)

FROM employees;

* 날짜 함수

1) MONTHS_BETWEEN : 두 날짜의 차이

ex)

SELECT employee_id, hire_date, MONTHS_BETWEEN(sysdate, hire_date)

FROM employees;

응용)

SELECT employee_id, hire_date, ROUND(MONTHS_BETWEEN(sysdate, hire_date))

FROM employees;

2) ADD_MONTHS : 개월 더하기

SELECT employee_id, hire_date, ADD_MONTHS(hire_date, 4)

FROM employees;

3) NEXT_DAY : 

SELECT sysdate, NEXT_DAY(sysdate, '월요일') FROM dual;

OR

SELECT sysdate, NEXT_DAY(sysdate, '월') FROM dual;

BUT

SELECT sysdate, NEXT_DAY(sysdate, 'Monday') FROM dual; <- 지역에 따라 안 됨

따라서,

일요일 1, 토요일 7

SELECT sysdate, NEXT_DAY(sysdate, 2) FROM dual;

4) LAST_DAY : 생략

5) ROUND : 날짜 초기화(기준에 따라)

ex)

- 'year' : 6월 30일 기준으로 반올림

SELECT sysdate, ROUND(sysdate, 'year') FROM dual;

- 'mm' : 15일 기준(30/2 = 15일)

- 'dd' : 하루 기준(24/2 = 12시간)

- 'd' : 일주일 기준(7/2 = 3.5일)

6) TRUNC : 날짜 초기화(이전)

* Conversion Functions : 변환 함수

1) TO_CHAR : 문자로

ex) SELECT TO_CHAR(sysdate, 'yyyy-mm-dd hh24:mi:ss') FROM dual;

- 날짜 형식 앞에 'fm' 붙이면, 데이터 외에 필요없는 것(자리수 채우는, 0이나, 공백) 삭제

ex) SELECT employee_id, hire_date, TO_CHAR(hire_date, 'yy/mm/dd')

    FROM employees;  ->  18/05/07

    SELECT employee_id, hire_date, TO_CHAR(hire_date, 'fmyy/mm/dd')

    FROM employees;  ->  18/5/7

2) TO_NUMBER : 숫자로

ex)

SELECT employee_id, last_name, TO_CHAR(salary, 'L99,999.99') AS salary

FROM employees

WHERE salary > TO_NUMBER('$8,000', '$9,999');

3) TO_DATE : 날짜로

ex)

SELECT employee_id, last_name, TO_CHAR(hire_date, 'yyyy/month/dd') AS salary

FROM employees

WHERE hire_date > TO_DATE('31-12-99', 'dd-mm-rr');

* yy: 같은 세기에서 찾음(99, yy일 경우 2099년을 찾게 됨)

  rr: 현재 세기를 기준으로(100/2=50년), 뒤쪽의 데이터를 찾는다면 자동으로 앞의 것에서 찾음

* Nesting Functions : 함수 중첩

ex)

SELECT last_name,

UPPER(CONCAT(SUBSTR(last_name, 1, 8), '_US'))

FROM employees

WHERE department_id = 60;

* General Functions : 일반 함수

1) NVL (expr1, expr2) : 인수가 null일 경우, 계산 가능한 값(타입이 같아야 함)으로 변환

ex) SELECT employee_id, last_name, salary + salary*NVL(commission_pct, 0) AS monthly_salary

    FROM employees;

- 계산하지 않는 경우에도 쓸 수 있음

ex) SELECT employee_id, last_name, salary, NVL(commission_pct, -1)

 FROM employees;

2) NVL2 (expr1, expr2, expr3) : expr1이 null이 아니면 expr2, null이면 expr3을 반환

ex)

SELECT employee_id, last_name, salary + salary*NVL(commission_pct, 0) AS monthly_salary,

NVL2(commission_pct, 'COMM', 'NOCOMM') AS bigo

FROM employees;

3) NULLIF (expr1, expr2) : 인수가 같으면 null, 다르면 expr1을 반환

ex)  SELECT NULLIF('a','a') FROM dual;

ex2) SELECT first_name, last_name, NULLIF(LENGTH(first_name), LENGTH(last_name))

  FROM employees;

4) COALESCE (expr1, expr2, ..., exprn) : 처음으로 null이 아닌 인수의 값을 반환

ex) SELECT employee_id, commission_pct, manager_id,

 COALESCE(commission_pct, manager_id, 7777)

 FROM employees;

오라클 SQL 기초

- /(슬래시) : 방금 실행했던 쿼리문 다시 실행

- ed : 파일 편집(편집기를 띄움)

- get : SQL 실행기 내에서 내용을 보여줌

- @ : 파일을 실행

예) @c:\oraclexe\labs\test0201.sql

* 치환 변수

오라클에만 있는 기능이며, 오라클 10 이상부터 지원한다고 함.

ampersand(&)를 붙이고 변수명을 정한다.

예) &col_name

set verify off

set echo on

SELECT employee_id, last_name, &&col_name

FROM employees

ORDER BY &col_name

/

UNDEFINE col_name

set verify on

set echo off

* show : SQL Plus 설정값 보기

예) show linesize

- 작성 방법 : set linesize 숫자

예) set linesize 120

※ show로 볼 수 있는 것은 모두 set으로 변경 가능

* Run SQL Command Line 편집기(SQL Plus) 기본 설정 손을 조금 보자.

C:\oraclexe\app\oracle\product\11.2.0\server\sqlplus\admin

내의 glogin 파일 내용 아래에

SET linesize 120 추가 후 저장, SQL Plus 다시 실행