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

정규표현식

* 메타 문자

@C:\oraclexe\labs\regexp_tab.sql;

SELECT * FROM t1;

q1.sql >

SELECT fname, lname

FROM t1

WHERE REGEXP_LIKE (fname, '^Ste(v|ph)en$');

q2.sql >

SELECT fname, phone

FROM t1

WHERE REGEXP_LIKE (phone, '...\...\.....\.......');

- . : 정규표현식에서는 문자 하나를 대신함.

- \ : 예외 처리

q3.sql >

SELECT fname, phone

FROM t1

WHERE REGEXP_LIKE (phone, '[0-9]{3}\.[0-9]{2}\.[0-9]{4}\.[0-9]{6}');

- [] : 문자(숫자)의 범위

- {} : 자리수

q4.sql >

SELECT fname, phone, REGEXP_REPLACE ( phone , '\.' , '-' ) new_format

FROM t1;

- REGEXP_REPLACE : 다른 문자로 대체

q5.sql >

SELECT fname, phone,

REGEXP_REPLACE (phone, '(\d{3})\.(\d{3})\.(\d{4})','(\1)-\2-\3') new_phone

FROM t1;

- \d : 숫자(digit). [0-9]와 같은 의미.

- 뒤의 \1, 2, 3 : 그룹을 지정, '\'는 앞의 표현식과 일치하기 위해 사용.

q6.sql >

SELECT fname, addr,

REGEXP_INSTR ( addr, '[[:alpha:]]' ) pos ,

phone,

REGEXP_INSTR ( phone, '[[:alpha:]]') pos

FROM t1;

- [:alpha:] : 범주를 지정(alpha: 알파벳, digit: 숫자)해서 해당되는 부분이 나타나는 자리수를 찍어줌.

              해당되는 것이 없으면 0.

q7.sql >

SELECT fname, addr,

REGEXP_SUBSTR ( addr, ' [^ ]+ ') road

FROM t1;

- REGEXP_SUBSTR : 첫 번째 나오는 문자를 검색한다.

- [^#] : #가 아닌, 부정의 의미.

- + : 한 개 이상.

- ' [^ ]+ ' : 공백과 공백 사이를 추출하라.

q8.sql >

SELECT fname, phone,

REPLACE(REGEXP_SUBSTR(phone,'\.(\d{3})\.'),'.') code

FROM t1;

- 정규표현식도 중첩이 가능하다.

q9.sql >

SELECT fname, addr,

REGEXP_COUNT(addr,'a') cnt

FROM t1;

- REGEXP_COUNT : 표현된 횟수

q11.sql >

SELECT REGEXP_SUBSTR ('0123456789',

'(123)(4(56)(78))', 1, 1, 'i', 1 ) "Exp1" ,

REGEXP_SUBSTR ('0123456789',

'(123)(4(56)(78))', 1, 1, 'i', 2 ) "Exp2" ,

REGEXP_SUBSTR ('0123456789',

'(123)(4(56)(78))', 1, 1, 'i', 3 ) "Exp3" ,

REGEXP_SUBSTR ('0123456789',

'(123)(4(56)(78))', 1, 1, 'i', 4 ) "Exp4"

FROM dual;

- 순서 : 123 → 45678 → 56 → 78

q10.sql >

SELECT REGEXP_INSTR ('0123456789',

'(123)(4(56)(78))', 1, 1, 0, 'i', 2 ) "Position"

FROM dual;

- 자리값

q12.sql >

SELECT

REGEXP_INSTR('ccacctttccctccactcctcacgttctcacctgtaaagcgtccctc

cctcatccccatgcccccttaccctgcagggtagagtaggctagaaaccagagagctccaagc

tccatctgtggagaggtgccatccttgggctgcagagagaggagaatttgccccaaagctgcc

tgcagagcttcaccacccttagtctcacaaagccttgagttcatagcatttcttgagttttca

ccctgcccagcaggacactgcagcacccaaagggcttcccaggagtagggttgccctcaagag

gctcttgggtctgatggccacatcctggaattgttttcaagttgatggtcacagccctgaggc

atgtaggggcgtggggatgcgctctgctctgctctcctctcctgaacccctgaaccctctggc

taccccagagcacttagagccag',

'(gtc(tcac)(aaag))',

1, 1, 0, 'i',

1) "Position"

FROM dual;

- 쿼리문은 DNA 유전자 배열

- 특별한 배열을 찾을 때 사용

q14.sql >

SELECT REGEXP_COUNT(

'ccacctttccctccactcctcacgttctcacctgtaaagcgtccctccctcatccccatgcccccttaccctgcag

ggtagagtaggctagaaaccagagagctccaagctccatctgtggagaggtgccatccttgggctgcagagagaggag

aatttgccccaaagctgcctgcagagcttcaccacccttagtctcacaaagccttgagttcatagcatttcttgagtt

ttcaccctgcccagcaggacactgcagcacccaaagggcttcccaggagtagggttgccctcaagaggctcttgggtc

tgatggccacatcctggaattgttttcaagttgatggtcacagccctgaggcatgtaggggcgtggggatgcgctctg

ctctgctctcctctcctgaacccctgaaccctctggctaccccagagcacttagagccag',

'gtc') AS Count

FROM dual;

- 'gtc'가 몇 번 나오는지 count

* 기타 메타 문자들

- + : 1번 이상

- ? : 0번 또는 1번

- * : 0번 이상

- {m, } : m 이상

- {m, n} : m 이상 ~ n 이하

- ( ... ) : 표현식을 묶어줄 때 사용

- $ : 끝

- ^ : 시작

- \ : escape

- \n : 선행하는 표현식의 위치

- \d : 숫자

- [:class:] : class 종류를 지정

- [^:class:] : class가 아닌 것

SQL 끝. DB Admin 시작!

플래시백

conn / as sysdba

SELECT * FROM v$option;

WHERE parameter like 'Flashback%';  ← 플래시백 유료 옵션 확인하기

* 플래시백 실습

conn hr/hr

UPDATE employees

SET salary = salary*1.5

WHERE employee_id = 141;

commit;

SELECT salary FROM employees

WHERE employee_id = 141;

=> 롤백은 안 되지만, undo 파일에 남아있을 것임.

* 플래시백 쿼리

SELECT employee_id, salary FROM employees

as of timestamp (systimestamp-5(24*60))

WHERE employee_id = 141;

* 플래시백 버전 쿼리

col versions_starttime for a25

col versions_endtime for a25

set line 120

SELECT versions_starttime, versions_endtime, salary

FROM employees

versions between timestamp minavalue and maxvalue

WHERE employee_id = 141;

Time Zones

* 시간대

표준 시 : 그리니치(Greenwich) 천문대 시간

우리나라 : 표준 시 + 9

- sysdate : date type을 반환

- systimestamp : timestamp(date/time) type을 반환

SELECT sysdate, systimestamp FROM dual;

-> 서버 시간

SELECT current_date, current_timestamp, localtimestamp FROM dual;

-> 내 시간

ALTER SESSION set time_zone = '-07:00';

-> 나의 세션 설정 바꾸기(미국 시간)

SELECT current_date, current_timestamp, localtimestamp FROM dual;

-> 다시 확인해보면 날짜가 바뀌어 있다.

※ 글로벌 환경에서는 시간대 조정이 필요할 수 있다.

* 시간 데이터 타입 확인하기

실습)

CREATE TABLE timetest1

(a date,

 b timestamp,

 c timestamp with time zone,

 d timestamp with local time zone);

INSERT INTO timetest1

VALUES(sysdate, sysdate, sysdate, sysdate);

SELECT * FROM timetest1;

>> local time zone : 클라이언트의 시간대로 바꿔준다.

>> 시간 정보에서 초(sec)의 정밀도는 기본(default)값이 6, 최대 9까지 지정 가능(Max 11byte)

SELECT * FROM tab;

※ DB에 있는 테이블 보기

* 데이터 타입 변경하기

CREATE TABLE dept50

AS

SELECT employee_id, last_name, hire_date, job_id

FROM employees

WHERE department_id = 50;

SELECT * FROM dept50;

ALTER TABLE dept50

MODIFY hire_date TIMESTAMP(2);  ← 바로 변경 가능

ALTER TABLE dept50

MODIFY hire_date TIMESTAMP(2) with time zone;  ← with time zone 속성은 테이블에 값이 들어있으면 불가

* Interval : 간격

CREATE TABLE timetest2

(a interval year to month,

 b interval day to second);

INSERT INTO timetest2

VALUES(interval '5-10' year to month,  ← 5년 10개월

       interval '90 12:30:30' day to second);  ← 90일 12시간 30분 30초

SELECT systimestamp+a, systimestamp+b

FROM dual, timetest2;

* extract : 날짜 함수 (TO_CHAR 등으로 변환하지 않고 날짜 그대로 추출)

* TO_YMINTERVAL, TO_DSINTERVAL

* Daylight Saving Time (DST, 일광시간절약제, 서머타임)

* 고급 서브쿼리

>> Multiple-column Subquery

SELECT employee_id, manager_id, department_id

FROM empl_demo

WHERE (manager_id, department_id) IN (SELECT manager_id, department_id

                                      FROM empl_demo

                                      WHERE first_name = 'John')

                                      AND first_name <> 'John';

이 문장과 아래의 문장은 다르다.

SELECT employee_id, manager_id, department_id

FROM empl_demo

WHERE manager_id IN

                 (SELECT manager_id

                  FROM empl_demo

                  WHERE first_name = 'John')

AND department_id IN

                 (SELECT department_id

                  FROM empl_demo

                  WHERE first_name = 'John')

AND first_name <> 'John';

※ IN이 AND보다 우선순위가 높다.

* Scalar Subqueries: Examples

SELECT employee_id, last_name,

       (CASE

        WHEN department_id = (SELECT department_id FROM departments

                              WHERE location_id = 1800)

        THEN 'Canada' ELSE 'USA' END) location

FROM employees;

SELECT employee_id, last_name

FROM employees e

ORDER BY (SELECT department_name

FROM departments d

WHERE e.department_id = d.department_id);

* 상호 관련(Correlated) 서브쿼리

- 메인 쿼리의 행이 다 떨어질 때까지 서브쿼리를 실행하여 비교하는 방법으로 수행됨.

- 구조가 좋지 않은 대표적인 SQL 중 하나.

ex)

SELECT last_name, salary, department_id

FROM employees outer_table

WHERE salary > (SELECT AVG(salary)

                FROM employees inner_table

                WHERE inner_table.department_id = outer_table.department_id);

-> 서브쿼리가 여러 번 실행된다.

※ 성능을 좋게 하는 SQL 문장 튜닝의 예 >

SELECT e.employee_id, e.last_name, e.salary, b.avg_sal, e.department_id

FROM employees e JOIN (SELECT department_id, AVG(salary) avg_sal

                       FROM employees

                       GROUP BY department_id) b

ON (e.department_id = b.department_id)

WHERE e.salary > b.avg_sal;

-> 조건을 FROM절에 작성.

* EXISTS 연산자 : 상호 관련 서브쿼리

- 조건에 맞는 것이 발견되면 추가로 더 이상 찾지 않는다.

- 성능을 개선할 수 있음.

ex)

SELECT employee_id, last_name, job_id, department_id

FROM employees outer

WHERE EXISTS (SELECT 'X'

              FROM employees

              WHERE manager_id = outer.employee_id);

* Correlated UPDATE : 상호 관련 업데이트

실습)

SELECT * FROM dept50;

ALTER TABLE dept50

ADD salary NUMBER(10);  ← 행을 추가한 후,

UPDATE dept50 d

SET salary = (SELECT salary FROM employees e

              WHERE e.employee_id = d.employee_id);

← 데이터를 기존에 있던 테이블과 똑같이 넣고 싶을 때 사용.

* Correlated DELETE : 상호 관련 삭제

* WITH문 : 인라인뷰를 확장한 기능.

ex)

WITH

dept_costs AS (SELECT d.department_name, SUM(e.salary) AS dept_total

               FROM employees e JOIN departments d

               ON e.department_id = d.department_id

               GROUP BY d.department_name),

avg_cost AS (SELECT SUM(dept_total)/COUNT(*) AS dept_avg

             FROM dept_costs)

SELECT * FROM dept_costs

WHERE dept_total > (SELECT dept_avg

                    FROM avg_cost)

ORDER BY department_name;

* Constraint

SELECT table_name, constraint_name, constraint_type, status

FROM user_constraints

WHERE table_name IN('EMP', 'DEPT');

ALTER TABLE dept

DROP primary key;  <- 삭제가 되지 않는다.

ALTER TABLE dept

DROP primary key CASCADE;  <- 옵션을 추가해 준다.

SELECT table_name, constraint_name, constraint_type, status

FROM user_constraints

WHERE table_name IN('EMP', 'DEPT');  <- 다시 확인해 본다.

ALTER TABLE dept

ADD primary key(department_id);

ALTER TABLE emp

ADD CONSTRAINT emp_deptid_fk foreign key(department_id)

REFERENCES departments(department_id);  <- 제약 조건을 다시 만들고,

SELECT table_name, constraint_name, constraint_type, status

FROM user_constraints

WHERE table_name IN('EMP', 'DEPT');  <- 다시 확인해 보면, enabled 상태.

INSERT INTO dept

VALUES(10, 'CS', null, null);

UPDATE emp

SET department_id = 300

WHERE employee_id = 176;  <- 두 문장 모두 오류 발생

ALTER TABLE dept

DISABLE primary key;

ALTER TABLE emp

DISABLE CONSTRAINT emp_deptid_fk;  <- 제약 조건을 끌 수 있다.

SELECT table_name, constraint_name, constraint_type, status

FROM user_constraints

WHERE table_name IN('EMP', 'DEPT');  <- 확인해 보면, 두 제약 조건은 disable 상태.

제약 조건을 disable하고, 테이블에 새로운 데이터를 입력 후 enable할 때 문제가 있으면 enable되지 않는다.

문제가 있는 해당 데이터(중복 등)를 삭제 후 enable 해주어야 한다.

DELETE FROM dept

WHERE department_name = 'CS';

UPDATE emp

SET department_id = 30

WHERE employee_id = 176;  <- 데이터 갱신 후

ALTER TABLE dept

ENABLE primary key;

ALTER TABLE emp

ENABLE CONSTRAINT emp_deptid_fk;  <- 제약 조건 enable

* Cascading Constraints

연쇄 제약 조건

@c:\oraclexe\labs\cre_test1.sql  <- TEST1 테이블 생성

DESC test1;

SELECT table_name, constraint_name, constraint_type, status

FROM user_constraints

WHERE table_name = 'TEST1';

ALTER TABLE test1

DROP COLUMN col2;  <- 삭제가 된다.

ALTER TABLE test1

DROP COLUMN col1;  <- 삭제가 안 된다.

ALTER TABLE test1

DROP COLUMN col1

CASCADE CONSTRAINTS;  <- 옵션 추가하여 삭제.

DROP TABLE test1;  <- 테스트 끝났으니 테이블 삭제.

* DROP TABLE ... PURGE : 영구 삭제

DROP TABLE emp;

SELECT * FROM tab;

SHOW RECYCLEBIN;  <- 휴지통 보기

FLASHBACK TABLE emp TO BEFORE DROP;  <- 복원

DESC emp;

DROP TABLE emp PURGE;  <- 완전 삭제, FLASHBACK 불가.

PURGE RECYCLEBIN;  <- 휴지통 비우기

※ 오라클의 휴지통 특징

- 파일 이동이 없다. 따라서, 속도가 빠르다.

* Temporary Tables : 임시 테이블

- 장바구니 개념

CREATE GLOBAL TEMPORARY TABLE emp_temp1

ON COMMIT DELETE ROWS

AS

SELECT employee_id, salary, department_id

FROM employees;

CREATE GLOBAL TEMPORARY TABLE emp_temp2

ON COMMIT PRESERVE ROWS

AS

SELECT employee_id, salary, department_id

FROM employees;

< 재접속 >

SELECT * FROM emp_temp1;

SELECT * FROM emp_temp2;  <- 테이블에 아무것도 없다.

INSERT INTO emp_temp1

SELECT employee_id, salary, department_id

FROM employees

WHERE department_id = 50;

INSERT INTO emp_temp2

SELECT employee_id, salary, department_id

FROM employees

WHERE department_id = 60;  <- 데이터 삽입

SELECT * FROM emp_temp1;

SELECT * FROM emp_temp2;

COMMIT;

SELECT * FROM emp_temp1;  <- 테이블이 비워진다.

SELECT * FROM emp_temp2;  <- 테이블의 내용이 유지된다.

※ 임시 테이블은 세션 범위를 벗어나지 못한다.

* External Tables : 외부 테이블

- 자주 사용하지는 않지만, 버리지는 못하는 자료를 빼두는 용도로 사용

  ex) 은행 거래내역 등

- Oracle 9i부터 사용

- DML 불가

- 부득이하게 수정이 필요하면, 파일을 직접 수정

실습)

• SQL*Plus에서 권한 주기

> conn / as sysdba

Connected.

SYS> CREATE DIRECTORY dir_1

AS 'c:\oraclexe\test';

Directory created.

SYS> GRANT read, write ON DIRECTORY dir_1 TO hr;

Grant succeeded.

• SQL문 실행

@C:\oraclexe\labs\cre_empxt.sql;

DESC empxt;

SELECT * FROM empxt;

Manipulating Large Data Sets

생략

★ Oracle의 Flashback 기술에 대하여

 - 빠르게 취소하는 기능(commit이 된 것)

 - 과거에는 못 했는데 가능하도록 추가된 기능.

• Flashback Query - 9i ver.~             ┐
• Flashback Versions Query               │→ Undo data 읽기 방식(영구적이지 않음)
• Flashback Transaction Query - Undo SQL ┘
• Flashback Table ($)
• Flashback Data Archive ($) - 11g ver.~
• Flashback Undrop - Recyclebin
• Flashback Database ($)

※ ($) 표시는 유료 옵션

* TRUNCATE : WHERE절 없는 DELETE 명령처럼 모든 행을 삭제

  - 차이점은 DDL이기 때문에 자동 COMMIT.

SELECT * FROM sales_reps;

DELETE FROM sales_reps;

ROLLBACK;

※ DELETE는 ROLLBACK이 가능하다.

TRUNCATE TABLE sales_reps;

ROLLBACK;

※ TRUNCATE는 ROLLBACK이 불가능하다.

=> Undo를 사용하지 않기 때문에, 속도도 매우 빠르다.

* 자동 커밋/롤백

* SAVEPOINT는 ROLLBACK 시 사용, COMMIT하면 SAVEPOINT는 날아감.

* C:\oraclexe\app\oracle\oradata\XE 내의 파일 정보

* 데이터베이스는 아파트(또는 다세대 주택)에 비유될 수 있음.

* CREATE TABLE : 테이블 생성하기

실습)

CREATE TABLE member

(id        NUMBER(4),

 name      VARCHAR2(20),

 phone_no  VARCHAR2(11),

 email     VARCHAR2(40),

 join_date DATE);

DESC member;

INSERT INTO member

VALUES(1001, 'Jisang', '01012345678', 'wona23@gmail.com', sysdate);

SELECT * FROM member;

* 오라클에서 사용 가능한 데이터 타입

※ 이진 데이터(RAW, LONGRAW)는 용량 때문에 사용 시 주의

※ Bfile은 응용 프로그램에서 많이 쓰이는 형태

※ LOB 타입 이전에 사용된 LONG 타입(LONG, LONGRAW)의 데이터는 용량이 커서 테이블을 따로 저장해서 사용했었음.

- 테이블을 따로 만들고, 필요할 때 Join해서 사용.

※ LOB 타입은 링크 정보만 저장, 실제 데이터는 별도의 저장소에 저장하는 방식.

- Indexing 기법

* ROWID

ex)

SELECT employee_id, last_name, rowid

FROM employees;

>> ROWID 의미

-  6        3          6           3

  객체 | File No | Block 번호  | 행 번호

- ROWID는 일종의 Pseudo Column

- Primary Key보다 더 정확하게 중복 데이터를 식별할 수 있음

* DEFAULT Option : 기본 옵션

ex)

CREATE TABLE dept

       (deptno      NUMBER(2),

        dname       VARCHAR2(14),

        loc         VARCHAR2(13),

        create_date DATE DEFAULT sysdate);

INSERT INTO dept(deptno, dname)

VALUES(10, '관리부');

SELECT * FROM dept;

* Including Constraints : 제약 조건

- NOT NULL

- UNIQUE

>> NULL도 가능(NULL은 각각 UNIQUE하므로)

- PRIMARY KEY(PK)

>> 비교적 짧고, 숫자만으로 된 것이 가장 좋음. (주민등록번호보다는 학번)

- FOREIGN KEY(FK)

- CHECK

>> Foreign Key와 Check는 남발하면 그 자체로 제약 조건이 될 수 있어서, 최근에는 응용 프로그램 코드로 대체되는 경우가 많음. (리스트에서 선택하는 방식)

※ Bold는 많이 사용

실습)

CREATE TABLE member

(id NUMBER(4) Primary Key,   ← Column Level 제약 조건

name VARCHAR2(20) not null,  ← Column Level 제약 조건

phone_no VARCHAR2(11) CONSTRAINT member_phone_uk UNIQUE,  ← Column Level 제약 조건

email VARCHAR2(40),

join_date DATE,

CONSTRAINT member_email_uk UNIQUE(email)  ← Table Level 제약 조건

);

DESC member;

=> 제약 조건이 나타나지 않는다.

※ 제약 조건 확인 방법 >>

실습)

SELECT table_name, constraint_name, constraint_type

FROM user_constraints

WHERE table_name = 'MEMBER';  ← table_name은 반드시 대문자로 작성해야 함.

서브쿼리 (계속)

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;