[Tibero교육] Tibero SQL Developers

SQL

• SQL 문장의 구성요소

• 데이터 조회

• 함수 사용

• 데이터 조회 활용

• DML 활용

 

1. SQL 문장의 구성요소

구분	설명
데이터 타입	Tibero에서는 SQL 표준에 기반한 여러 가지 데이터 타입을 제공한다.
리터럴	상수 값을 의미한다.
형식 문자열	NUMBER 타입과 날짜형 타입의 값을 문자열로 변환하기 위한 형식을 정의한 것이다.
의사 컬럼	명시적으로 선언하지 않아도 시스템이 자동으로 모든 테이블에 포함시키는 컬럼
NULL	한 로우에서 어떤 컬럼에 값이 없을 때 그 컬럼을 NULL이라고 한다.
주석	책이나 문서에서 주석이 낱말이나 문장의 뜻을 쉽게 풀이하는 역할을 하듯 SQL 문장에도 주석을 활용하여 해당 문장의 부연 설명을 삽입할 수 있다.
힌트	SQL 문장에 힌트를 추가하여 Tibero의 질의 최적화기(Optimizer)에 특정 행동을 지시하거나 질의 최적화기의 실행 계획을 변경한다.
스키마 객체	한 사용자가 하나의 스키마만을 정의할 수 있고, 스키마의 이름은 항상 사용자의 이름과 동일하다. 이러한 스키마에 포함된 객체를 스키마 객체라 한다.

Data Type

 Tibero에서는 SQL 표준에 기반한 여러 가지 데이터 타입을 제공한다.

구분	Data Type	설명
문자형	CHAR, VARCHAR, NCHAR, NVARCHAR, RAW, LONG, LONG RAW	문자열을 저장하는 데이터 타입이다.
숫자형	NUMBER	정수나 실수의 숫자를 저장하는 데이터 타입이다.
날짜형	DATE, TIME, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE	시간이나 날짜를 저장하는 데이터 타입이다.
간격형	INTERVAL YEAR TO MONTH, INTERVAL DAY TO SECOND	시간 간격을 표현하는 데이터 타입이다.
대용량 객체형	CLOB, BLOB, XML TYPE	대용량 객체를 저장하기 위해 제공하는 가장 큰 데이터 타입이다.
내재형	ROWID	사용자가 명시적으로 선언하지 않아도 Tibero RDBMS가 자동으로 삽입되는 로우마다 포함하는 컬럼의 타입이다.

문자형 개요

 

 문자형

 

• 문자열을 표현하는 데이터 타입이다. 문자형에는 CHAR 타입, VARCHAR 타입, NCHAR 타입, NVARCHAR 타입, RAW 타입, LONG 타입, LONG RAW 타입이 있다.

구분	Data Type	설명
문자형	CHAR, VARCHAR, NCHAR, NVARCHAR, RAW, LONG, LONG RAW	문자열을 저장하는 데이터 타입이다.

 CHAR

• 문자열을 저장하는 데이터 타입이다. 항상 고정된 문자열 길이를 갖는다.

VARCHAR

• 문자열을 저장하는 데이터 타입으로, 가변 길이를 갖는다.

 NCHAR

• 유니코드 문자열을 저장하기 위한 타입으로, 고정된 문자열 길이를 갖는다.

 NVARCHAR

• 유니코드 문자열을 저장하기 위한 타입으로, 가변 길이를 갖는다.

 LONG

• VARCHAR 타입을 확장한 데이터 타입이다. 가변 길이 문자열이 저장된다.

 RAW

• 바이너리 데이터를 저장하는 데이터 타입으로, 가변 길이를 갖는다.

• CHAR, VARCHAR 와 차이점

- RAW 타입은 데이터 중간에 NULL 문자('\0')가 올 수 있지만 CHAR, VARCHAR

타입은 그렇지 않다. 따라서 RAW 타입은 NULL 문자로 데이터의 끝을 나타낼 수 없으므로 항상 길이 정보를 같이 저장한다.

 LONG RAW

• RAW 타입을 확장한 타입으로 바이너리 데이터가 저장된다.

숫자형 개요

 

 숫자형

• 정수나 실수의 숫자를 저장하는 데이터 타입이다. 숫자형에는 NUMBER 타입, INTEGER 타입, FLOAT 타입이 있다.

 

• Tibero에서는 ANSI에서 제정한 SQL 표준의 숫자 타입의 선언을 지원한다. 즉, INTEGER 타입 또는 FLOAT 타입으로 컬럼을 선언하더라도 내부적으로 적절한 정밀도와 스케일을 설정하여 NUMBER 타입으로 변환해 준다.

구분	Data Type	설명
숫자형	NUMBER, INTEGER , FLOART	정수나 실수의 숫자를 저장하는 데이터 타입이다.

 

작성중.....

 

 

 

의사 컬럼

 

의사 컬럼 이란?

• 사용자가 명시적으로 선언하지 않아도 시스템이 자동으로 모든 테이블에 포함시키는 컬럼을 말한다.

• 종류

  - CONNECT_BY_IS_LEAF

  - ROWID

  - ROWNUM

  - LEVEL

CONNECT_BY_IS_LEAF 의사 컬럼

• 현재 로우가 CONNECT BY 조건에 의해 정의된 트리(Tree)의 리프(Leaf)이면 1을 반환하고 그렇지 않을 경우에는 0을 반환한다. 이 정보는 해당 로우가 계층 구조(Hierarchy)를 보여주기 위해 확장될 수 있는지 없는지를 나타낸다.

SELECT ENAME, CONNECT_BY_ISLEAF, LEVEL, SYS_CONNECT_BY_PATH(ENAME,'-') "PATH"
FROM EMP2
START WITH ENAME = 'Clark'
CONNECT BY PRIOR EMPNO = MGRNO
ORDER BY ENAME;

ROWID 의사 컬럼

• 전체 데이터베이스 내의 하나의 로우를 유일하게 참조하는 식별자이다. ROWID는 그 로우의 디스크의 물리적인 위치를 가리키고 있으며, 그 로우가 삭제될 때까지 변화되지 않는다.

• ROWID 값을 표현하기 위한 포맷으로는 BASE64 인코딩을 이용한다. BASE64 인코딩은 6bit에 포함된 숫자를 8bit 문자로 나타내는 방식으로, 0 ~ 63까지의 숫자를 A ~ Z, a ~ z, 0 ~ 9, +, /로 대치한다

 

• ROWID를 BASE64 인코딩으로 변환하면 세그먼트#, 데이터 파일#, 데이터 블록#, 로우#가 각각 6, 3, 6, 3byte로 되고, 'SSSSSSFFFBBBBBBRRR'의 형태를 갖는다. 예를 들어, 세그먼트# = 100, 데이터 파일# = 20, 데이터 블록# = 250, 로우# = 0인 ROWID는 'AAAABkAAUAAAAD6AAA'로 나타낸다.

 

ROWNUM 의사 컬럼

• SELECT 문장의 실행 결과로 나타나는 로우에 대하여 순서대로 번호를 부여한다.

• ROWNUM이 할당되는 순서

①질의를 수행한다.

②질의 결과로 로우가 생성된다.

③로우를 반환하기 직전에 그 로우에 ROWNUM이 할당된다. Tibero는 내부적으로 ROWNUM 카운터를 가지고 있으며, 카운터 값을 질의 결과의 로우에 할당한다.

④ROWNUM을 할당 받은 로우에 ROWNUM에 대한 조건식을 적용한다.

⑤조건식을 만족하면 할당된 ROWNUM이 확정되고, 내부의 ROWNUM 카운터의 값이 1로 증가한다.

⑥조건식을 만족하지 않으면 그 로우는 버려지고, 내부의 ROWNUM 카운터의 값은 증가하지 않는다.

ROWNUM 의사 컬럼

• 주의 사항

- WHRER 절을 포함하는 모든 부질의를 처리한 다음에 ORDER BY 절을 처리한다. 따라서 ORDER BY 절을 이용해서 항상 같은 결과를 얻을 수는 없다.

- 예를 들어, 다음의 질의는 실행할 때마다 다른 결과를 얻는다.

SELECT * 
FROM EMP WHERE ROWNUM <= 10 ORDER BY EMPNO;

• 다음과 같이 변환하면 ORDER BY 절을 먼저 처리하게 되므로 항상 같은 결과를 얻을 수 있다.

SELECT * 
FROM (SELECT * FROM EMP ORDER BY EMPNO)
WHERE ROWNUM <= 10;

• 다음과 같은 SELECT 문장은 하나의 로우도 반환하지 않는다.

- ROWNUM 값이 확정되기 전에 ROWNUM에 대한 조건식이 수행되기 때문에 첫 번째 로우가 ROWNUM = 1 으로 조건식을 만족하지 않고, 두 번째 결과 로우도 ROWNUM = 1이므로 반환되지 않는다.

SELECT * FROM EMP WHERE ROWNUM > 1;

LEVEL 의사 컬럼

• 계층 질의(Hierarchical Query) 를 실행한 결과에 각 로우의 트리 내 계층을 출력하기 위한 컬럼 타입이다. 최상위 로우의 LEVEL 값은 1이며, 하위 로우로 갈수록 1씩 증가한다.

NULL

 

NULL

• 어떤 컬럼에 값이 없을 때 그 컬럼을 NULL이라고 하거나 NULL 값을 가진다고 한다.

• NULL은 NOT NULL 제약과 PRIMARY KEY 제약이 걸리지 않은 모든 데이터 타입의 칼럼에 포함될 수 있다.

• 실제 값을 모르거나 아무런 의미 없는 값이 필요할 때 사용할 수 있다. NULL과 0은 다르기 때문에 NULL을 0으로 나타내면 안 된다. 다만 문자 타입의 컬럼에 빈 문자열('')이 들어가면 NULL로 처리된다.

• 다음 연산의 결과는 NULL이다.

NULL에 대한 비교조건

• NULL을 검사할 수 있는 비교조건은 IS NULL과 IS NOT NULL만 가능하다. NULL은 데이터가 없다는 것을 의미한다. 때문에 NULL과 NULL, NULL과 NULL이 아닌 다른 값을 서로 비교할 수 없다.

 

• 다만 DECODE 함수에서는 두 개의 NULL을 비교할 수 있다.

SELECT DECODE(NULL, NULL, 1) FROM DUAL;

사실 NVL 함수 쓰는게 더 깔끔...

 SELECT ENAME, SAL, COMM, SAL+NVL(COMM,0) AS "급여"
 FROM SCOTT.EMP;

• 만일 NULL에 다른 비교조건을 사용하면, 결과는 UNKOWN으로 나타난다. UNKNOWN으로 판별되는 조건은 거의 대부분 FALSE처럼 처리된다.

• 그 예로 SELECT 문에서 WHERE 절에 UNKNOWN으로 판별되는 조건이 있을 경우 반환되는 로우가 없다. 하지만 UNKNOWN이 FALSE와 다른 점은 UNKNOWN 조건에 또 다른 연산자가 더해져도 결과는 UNKNOWN이라는 점이다.

 

주석

 

주석

• SQL문장과 데이터베이스 오브젝트에 대한 주석을 작성할 수 있다.

• HINT를 제외한 주석은 SQL 문장의 실행에 영향을 주지 않는다.

• 주석시작 /*, 주석 끝 */ 으로 표기하고, 따로 공백이나 줄 바꿈으로 내용과 구분할 필요는 없다.

• '--'로 주석의 시작을 나타내고 바로 뒤에 주석의 내용을 적는다.

• 스키마 객체에도 주석을 삽입할 수 있다. 즉 COMMENT 명령을 사용하여 스키마 객체인 테이블, 뷰, 컬럼에 주석을 삽입할 수 있다. 스키마 객체에 삽입된 주석은 데이터 사전에 저장된다.

 

힌트

 

힌트(HINT)

• SQL문에 주석을 추가하여 Optimizer의 특정 행동을 지시 할 수 있다.

• 힌트는 반드시 SELECT, INSERT, UPDATE, DELETE 키워드 뒤에만 사용할 수 있다.

• 문법에 맞지 않는 힌트는 주석으로 취급한다.

• 힌트를 사용할 때 주의할 점

  - 힌트는 반드시 DELETE, INSERT, SELECT, UPDATE 절 뒤에만 올 수 있다.

  - '+' 기호는 반드시 주석 구분자('/*' 또는 '--') 바로 뒤에 공백 없이 붙여 써야 한다.

  - 힌트와 '+'기호 사이에 공백은 있어도 되고, 없어도 된다.

  - 문법에 맞지 않는 힌트는 주석으로 취급되며, 에러는 발생하지 않는다.

 

힌트(HINT) 의 종류

구분	힌트
질의 변형	NO_QUERY_TRANSFORMATION, NO_MERGE, UNNEST, NO_UNNEST, NO_JOIN_ELIMINATION
최적화 방법	ALL_ROWS, FIRST_ROWS
접근 방법	FULL, INDEX, NO_INDEX, INDEX_ASC, INDEX_DESC, INDEX_FFS, NO_INDEX_FFS, INDEX_RS, NO_INDEX_RS, INDEX_SS, NO_INDEX_SS
조인 순서	LEADING, ORDERED
조인 방법	USE_NL, NO_USE_NL, USE_NL_WITH_INDEX, USE_MERGE, NO_USE_MERGE, USE_HASH, NO_USE_HASH, HASH_SJ, HASH_AJ, MERGE_SJ, MERGE_AJ, NL_SJ, NL_AJ
병렬 처리	PARALLEL, NO_PARALLEL, PQ_DISTRIBUTE
실체화 뷰	REWRITE, NO_REWRITE
기타	APPEND, APPEND_VALUES, NOAPPEND, IGNORE_ROW_ON_DUPKEY_INDEX, CARD, MONITOR, NO_MONITOR, USE_CONCAT, NO_EXPAND

힌트(HINT) - 접근 방법

• 접근 방법이 적용된 힌트는 질의 최적화기가 특정 접근 방법의 사용이 가능한 경우, 그 방법을 사용하도록 명시한다. 만일 힌트에서 명시한 방법을 사용할 수 없는 경우에는 질의 최적화기는 그 힌트를 무시한다.

• 힌트에 명시하는 테이블명은 SQL 문에서 사용하는 이름과 동일해야 한다. 즉, 테이블 이름에 대한 별칭을 사용하였다면, 테이블 이름 대신에 별칭을 사용하여야 한다.

• SQL 문에서 테이블 이름에 스키마 이름을 포함하여 명시하였더라도 힌트에서는 테이블 이름만을 명시하여야 한다.

 

힌트(HINT) - 접근 방법 종류

구분	힌트
FULL	전체 테이블을 스캔하도록 지시하는 힌트이다. WHERE 절에 명시된 조건식에 맞는 인덱스 가 있더라도 전체 테이블 스캔을 사용한다.
INDEX	명시한 인덱스를 사용하여 인덱스 스캔을 하도록 지시하는 힌트이다.
NO_INDEX	명시한 인덱스를 사용하는 인덱스 스캔을 하지 않도록 지시하는 힌트이다. 만일 NO_INDEX 힌트와 INDEX 또는 INDEX_ASC, INDEX_DESC 힌트가 동일한 인덱스를 명시한 다면 질의 최적화기는 이 두 힌트를 모두 무시한다.
INDEX_ASC	명시한 인덱스를 사용하여 인덱스 스캔을 하도록 지시하는 힌트이다. 만일 인덱스 범위 스캔을 사용하는 경우에는 인덱스를 오름차순으로 스캔하도록 한다. 현재 Tibero의 인덱스 스캔의 기본 동작이 오름차순이기 때문에 INDEX_ASC는 INDEX와 동일한 작업을 수행한다. 분할된 인덱스의 경우 분할된 각 영역 내에서 오름차순으로 스캔한다
INDEX_DESC	명시한 인덱스를 사용하여 인덱스 스캔을 하도록 지시하는 힌트이다. 만일 인덱스 범위 스 캔을 사용하는 경우에는 인덱스를 내림차순으로 스캔하도록 한다. 분할된 인덱스의 경우 분할된 각 영역 내에서 내림차순으로 스캔한다
INDEX_FFS	명시한 인덱스를 사용하는 빠른 전체 인덱스 스캔을 사용하지 않도록 지시하는 힌트이다.

 

2. 데이터 조회

 

SQL 정의

SQL (Structure Query Language) : 구조 질의 언어

데이터베이스와 소통을 위해 필요한 언어

SQL 문장 분류

SELECT 를 이용한 데이터 조회

WHERE절 이용

AND

OR

BETWEEN AND

IN

IS [NOT] NULL 

LIKE

연산자

ORDER BY

GROUP BY

GROUP BY 절과 집합 함수를 이용하여 다양한 데이터 조회 가능

GROUP BY 절 이후의 컬럼 리스트 별로 GROUP을 묶어 조회한다

HAVING

GROUP BY 절과 결과로 부터 특정 조건을 만족하는 값을 얻고자 할 경우 HAVING절을 사용

SQL 작성시 유의사항

 

3. 함수 사용

 

함수 정의 / 종류

주어진 인수를 처리하여 결과값을 반환한다.

SELECT문을 간결하게 만들어 Data 조작을 쉽고 간결하게 만든다.

함수 (function)
단일행 함수 (Single-Row Functions)	집합 함수 (Aggregate Functions)	분석 함수 (Analytical Functions)

숫자 함수

숫자 데이터 타입의 값을 조작하여 변환된 숫자 값을 반환한다.

문자 함수

문자 데이터 타입의 값을 조작하여 변환된 문자 값을 반환한다.

날짜 함수

DATE로 지정된 데이터 타입의 값에 적용한다.

변환 함수

데이터 타입을 변환시켜 표현한다.

기타 함수 (1/4)

기타 함수 (2/4)

기타 함수 (3/4)

기타 함수 (4/4)

집합 함수 (1/4)

집합 함수 (2/4)

집합 함수 (3/4)

집합 함수 (4/4)

분석 함수 (1/6)

분석 함수 (2/6)

분석 함수 (3/6)

분석 함수 (4/6)

분석 함수 (5/6)

분석 함수 (6/6)

 

4. 데이터 조회 활용

 

조인의 정의

 

조인(JOIN)이란?

둘 이상의 테이블을 연결하여 데이터를 검색하는 방법

둘 이상의 행들의 공통된 값을 사용하여 조인한다.

 

조인유형 (ANSI)

American National Standard Institute에서 정의한 JOIN 표준안

• CROSS JOIN

• NATURAL JOIN

• JOIN ~ USING

• JOIN ~ ON

• OUTER JOIN

 

SELECT FROM을 이용한 조인

JOIN절을 이용한 조인

3개 이상의 테이블 조인

여러 형태의 조인 (1/8)

여러 형태의 조인 (2/8)

여러 형태의 조인 (3/8)

여러 형태의 조인 (4/8)

여러 형태의 조인 (5/8)

여러 형태의 조인 (6/8)

여러 형태의 조인 (7/8)

여러 형태의 조인 (8/8)

SUBQUERY(하위질의)

스칼라 SUBQUERY

INLINE VIEW

TOP-N QUERY

ROLLUP

CUBE

GROUPING SETS

계층질의

 

5. DML 활용

 

DML 정의

Data Manipulation Language

테이블의 데이터를 추가, 삭제, 변경 작업을 수행하는 명령어이다.

 

• SELECT - 검색(질의)

• INSERT  - 삽입(등록)

• UPDATE - 업데이트(수정)

• DELETE - 삭제

• MERGE - 삽입 또는 업데이트

 

INSERT 문

UPDATE 문

DELETE 문

MERGE 문

TRIGGER (1/2)

TRIGGER (2/2)