codeahn

서브 쿼리 개요

–하나의 SQL 명령문의 결과를 다른 SQL 명령문에 전달하기 위해 두 개 이 상의 SQL 명령문을 하나의 SQL명령문으로 연결하여 처리하는 방법

단일행 서브 쿼리

– 서브 쿼리에서 단 하나의 행만을 검색하여 메인 쿼리에 반환하는 질의문

– 메인 쿼리의 WHERE 절에서 서브 쿼리의 결과와 비교할 경우에는 반드시 단일행 비교 연산자 중 하나만 사용해야 함

– 서브 쿼리의 결과로 하나의 행만이 출력되어야 함

다중 행 서브 쿼리

–서브 쿼리에서 반환되는 결과행 이하나 이상일 때 사용하는 서브 쿼리

–메인 쿼리의 WHERE 절에서 서브 쿼리의 결과와 비교할 경우에는 다중행 비교 연산자를 사용하여 비교
다중행 비교 연산자: IN, ANY, SOM, ALL, EXISTS
다중행 비교 연산자는 단일행 비교 연산자와 결합하여 사용 가능

IN 연산자를 이용한 다중행 서브 쿼리

- 메인 쿼리의 비교조건에서 서브 쿼리의 출력 결과와 하나라도 일치하면 메인 쿼리 조건절이 참이 되는 연산자

- '='연산자를 OR로 연결한 것과 같은 의미

ANY 연산자를 이용한 다중행 서브 쿼리

- 메인 쿼리의 비교조건에서 서브 쿼리의 출력 결과와 하나라도 일치하면 메인 쿼리 조건절이 참이 되는 연산자

– '>, <'등과 같은 범위 비교도 가능

- ANY 연산자를 이용한 서브 쿼리의 예

ALL 연산자를 이용한 다중 행 서브 쿼리

-메인 쿼리의 비교조건에서 서브 쿼리의 검색 결과와 모두 일치하면 메인 쿼리 조건절이 참이 되는 연산자

–ANY 연산자와 차이점

'> ANY': 서브 쿼리 결과 중에서 최솟값보다 크면 메인 쿼리의 비교조건이 참

'> ALL': 서브 쿼리의 결과 중에서 최댓값보다 크면 메인 쿼리의 비교조건이 참

EXISTS 연산자를 이용한 다중행 서브 쿼리

– 서브 쿼리에서 검색된 결과가 하나라도 존재하면 메인 쿼리 조건절이 참이 되는 연산자
– 서브 쿼리에서 검색된 결과가 존재하지 않으면 메인 쿼리의 조건절은 거짓

NOT EXISTS

- EXISTS와 상반되는 연산자
- 서브 쿼리에서 검색된 결과가 하나도 존재하지 않으면 메인 쿼리의 조건절이 참이 되는 연산자

다중 칼럼 서브 쿼리

– 서브 쿼리에서 여러 개의 칼럼 값을 검색하여 메인 쿼리의 조건절과 비교하는 서브 쿼리

– 메인 쿼리의 조건절에서도 서브 쿼리의 칼럼 수만큼 지정해야 함

 PAIRWISE : 칼럼을 쌍으로 묶어서 동시에 비교하는 방식
 UNPAIRWISE : 칼럼 별로 나누어서 비교한 후, AND 연산을 하는 방식

PAIRWISE 다중 칼럼 서브 쿼리

– 메인 쿼리와 서브 쿼리의 비교대상 칼럼을 쌍으로 묶어서 행별로 비교하는 방법
– 메인 쿼리와 서브 쿼리에서 비교하는 칼럼의 수는 반드시 동일해야 함

SELECT column_list
FROM table1
WHERE (column1, column2, ...) IN(SELECT column1, colun2, ...
				FROM table2
				WHERE condition);

UNPAIRWISE 다중 칼럼 서브 쿼리

– 메인 쿼리와 서브 쿼리의 비교대상 칼럼을 분리하여 개별적으로 비교한 후 AND 연산에 의해 최종 결과를 출력
– 각 칼럼이 동시에 만족하지 않더라도 개별적으로 만족하는 경우에는 비교조건이 참이 되어 결과를 출력 가능

SELECT column_list
FROM table1
WHERE column1 IN(SELECT column1
			FROM table2
			WHERE condition)
AND 	column2 IN(SELECT column2
			FROM table2
			WHERE condition);

상호 연관 서브 쿼리

–메인 쿼리 절과 서브 쿼리 간에 검색 결과를 교환하는 서브 쿼리
–메인 쿼리와 서브 쿼리 간의 결과를 교환하기 위하여 서브 쿼리의 WHERE 조건절에서 메인 쿼리의 테이블과 연결

SELECT column_list
FROM table1
WHERE [column | expression] operator
		(SELECT [column | expression]
		FROM table2
		WHERE table2.column operator table1.column);

서브 쿼리 사용 시 주의사항

– 복수행 값을 반환하는 서브 쿼리와 단일행 비교 연산자 함께 사용하는 경우
– 반환되는 칼럼의 수와 메인 쿼리에서 비교되는 칼럼수가 일치하지 않는 경우
– 복수행을 출력하는 서브 쿼리와'='단일행 연산자로 비교하는 경우

ORDER BY절 사용

– 서브쿼리 내에서ORDER BY 절 사용하면 오류 발생

- 서브쿼리의 결과가 NULL인경우

Scalar Subquery

참조 : https://itwiki.kr/w/%EB%8D%B0%EC%9D%B4%ED%84%B0%EB%B2%A0%EC%9D%B4%EC%8A%A4_%EC%84%9C%EB%B8%8C_%EC%BF%BC%EB%A6%AC

조인의 개념

–하나의 SQL 명령문에 의해 여러 테이블에 저장된 데이터를 '한 번에' 조회할 수 있는 기능

–관계형 데이터베이스 분야의 표준

–두 개 이상의 테이블을'결합'한다는 의미

조인의 필요성

– 조인을 사용하지 않는 일반적인 예

•학생 주소록을 출력하기 위해 학생들의 학번, 이름, 소속 학과이름을 검색
•학생에 대한 정보 검색하는 단계 필요
•학생정보에서 소속 학과 번호정보를 추출하여 소속 학과이름을 검색하는 단계 필요

조인을 사용하지 않을 때

SELECT studno, name, deptno
FROM student;

조인을 사용할 때

SELECT tudno, name,
       student.deptno, department.dname
FROM student, department
WHERE student.deptno = department.deptno;

칼럼 이름의 모호성

– 서로 다른 테이블에 있는 동일한 칼럼 이름을 연결한 경우 칼럼 이름 앞에 테이블 이름을 접두사로 사용 테이블 이름과 칼럼 이름은 점(.)으로 구분

- SQL 명령문에 대한 구문 분석시간(parsing time) 줄임

테이블 별명

테이블 이름이 너무 긴 경우 사용한다.

- FROM절에서 테이블 이름 다음에 공백을 두고 별명 정의

- 하나의 SQL 명령문에서 테이블 이름과 별명을 혼용할 수 없다

- 테이블의 별명은 해당 SQL 명령문 내에서만 유효

카티션 곱

- 두 개 이 상의 테이블에 대해 연결 가능한 행을 모두 결합

- WHERE 절에서 조인 조건절을 생략하거나 잘못 설정한 경우

– 대용량 테이블에서 발생할 경우 SQL명령문의 처리속도 저하

–개발자가 시뮬레이션을 위한 대용량의 실험용 데이터를 생성하기 위해 의도적으로 사용 가능

–오라클 9i 이후 버전에서 FROM절에 CROSS JOIN 키워드 사용

SELECT table.column, table2.column
FROM [table, table2 | table1 CROSS JOIN table2];

EQUI JOIN

- 조인 대상 테이블에서 공통 칼럼을'='(equal) 비교를 통해 같은 값을 가지는 행을 연결하여 결과를 생성하는 조인 방법

–SQL 명령문에서 가장 많이 사용하는 조인 방법

– 조인 애트리뷰트(join attribute(속성))

WHERE 절을 이용한 EQUI JOIN 사용법 

– FROM : 조인 대상 테이블을 기술한다. 테이블은 콤마(,)로구분

– WHERE : 조인 애트리뷰트와'='연산자를 사용하여 조인 조건을 기술

SELECT table.column, table2.column
FROM table, table2
WHERE table1.column1 = table2.column2;

EQUI JOIN - NATURAL JOIN(자연 조인을 이용한 )

–오라클 9i 버전부터 EQUI JOIN을 자연 조인이라 명명

–WHERE 절을 사용하지 않고 NATURAL JOIN 키워드 사용

–오라클에서 자동적으로 테이블의 모든 칼럼을 대상으로 공통 칼럼을 조사 후, 내부적으로 조인문 생성

SELECT table.column, table2.column
FROM table1
	 NATURAL JOIN table2;

JOIN ~ USING 절을 이용한 EQUI JOIN(EQUI JOIN –JOIN ~ USING)

–USING절에 조인 대상 칼럼을 지정

–칼럼 이름은 조인 대상 테이블에서 동일한 이름으로 정의되어있어야 함

SELECT table.column, table2.column
	   table1 JOIN table2
       USING (column);

NON-EQUI JOIN

–'<', BETWEEN a AND b 와같이'='조건이 아닌 연산자 사용

OUTER JOIN

– EQUI JOIN의 조인 조건에서 양측 칼럼 값 중, 어느 하나라도 NULL 이면'='비교결과가거짒이되어 NULL 값을 가진 행은 조인 결과로 출력 불가

– 일반적인 EQUI JOIN 의예: 학생 테이블 의학과 번호 칼럼과 부서 테이블의 부서 번호 칼럼에 대한 EQUI JOIN ( student.deptno = department.deptno ) 한 경우

– EQUI JOIN에서 양측 칼럼 값 중의 하나가 NULL이지만 조인 결과로 출력할 필요가 있는 경우 OUTER JOIN 사용

OUTER JOIN의 제약사항

– OUTER JOIN 연산자(+)는 NULL이 존재하는 칼럼 쪽에 표시
–OUTER JOIN에서는 IN 연산자를 사용불가
–다른 조건과 OR 연산자로 결합 불가

사용법

SELECT table.column, table2.column
FROM table table[RIGHT | LEFT | FULL] OUTER JOIN table2
	 ON table1.colum = table.column;

LEFT OUTER JOIN

– FROM 절의 왼쪽에 위치한 테이블이 NULL을 가질 경우에 사용
– WHERE절의 오른편에'(+)'기호를 추가한 것과동일
–예: 지도교수가 배정되지 않은 학생 명단을 출력하여라

RIGHT OUTER JOIN
– FROM 절의 오른쪽에 위치한 테이블이 NULL 일가 질 경우, 사용
– WHERE 절의 왼편'(+)'기호를 추가한 것과동일

FULL OUTER JOIN
– LEFT OUTER JOIN과 RIGHT OUTER JOIN을 동시에 실행할 결과를 출력

SELF JOIN

– 하나의 테이블 내에 있는 칼럼끼리 연결하는 조인이 필요할 경우 사용
– 조인 대상 테이블이 자신 하나라는 것 외에는 EQUI JOIN과 동일

SELECT table.column, table2.column
FROM table1 JOIN table2
	 ON table1.column = table2.column;

그룹 함수의 개념

- 테이블의 전체 행을 하나 이상의 칼럼을 기준으로 그룹화하여 그룹별로 결과를 출력하는 함수

- 그룹 함수는 통계적인 결과를 출력하는데 자주 사용

SELECT column, group_function(column)
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[HAVING group_condition]

– GROUP BY : 전체행을 group_by_expression을 기준으로 그룹화

– HAVING : GROUP BY 절에 의해 생성된 그룹별 로조건 부여

- '*'은 NULL을 포함한 모든 행의 개수

- DISTINCT는 중복되는 값을 제외한 행의 개수

- ALL은 중복되는 값을 포함한 행의 개수, 기본값은 ALL

- expr 인수에서 사용 가능한 데이터 타입은 CHAR, VARCHAR2, NUMBER, DATE타입

데이터 그룹 생성

– 특정 칼럼 값을 기준으로 테이블의 전체 행을 그룹별로 나누기 위한 절

– 예를 들어, 교수 테이블에서 소속 학과별이나 직급별로 평균 급여를 구하는 경우

– GROUP BY 절에 명시되지 않은 칼럼은 그룹 함수와 함께 사용할 수 없음

GROUP BY 절 사용할 때 적용되는 규칙

– 그룹핑 전에 WHERE 절을 사용하여 그룹 대상 집합을 먼저 선택

– GROUP BY 절에는 반드시 칼럼 이름을 포함해야 하며 칼럼 별명은 사용할 수 없음

- 그룹별 출력 순서는 오름차순으로 정렬

- SELECT절에서 나열된 칼럼 이름이나 표현식은 GROUP BY 절에서 반드시 명시

- GROUP BY절에서 명시한 칼럼 이름은 SELECT절에서 명시하지 않아도 된다.

다중 칼럼을 이용한 그룹핑

– 하나이상의 칼럼을 사용하 여그룹을 나누고, 그룹 별로 다시 서브그룹을 나눔

- 전체 교수를 학과 별로 먼저 그룹핑 한 다음, 학과별 교수를 직급 별로 다시 그룹핑하는 경우

ROLLUP 연산자

– GROUP BY 절의 그룹 조건에 따라 전체행을 그룹화하고 각 그룹에 대해 부분합을 구하는 연산자

– GROUP BY 절에 칼럼의 수가 n개이면 ROLLUP 그룹핑 조합은 n+1개

SELECT deptno, grade, COUNT(*), ROUND(AVG(weight))
FROM student
GROUP BY deptno, grade;

SELECT에서 조회 FROM에서 테이블명 입력 GROUPBY에서 deptno와 grade순으로 정렬

CUBE 연산자

– ROLLUP에 의한 그룹 결과와 GROUP BY 절에 기술된 조건에 따라 그룹 조합을 만드는 연산자

– GROUP BY 절에 칼럼의 수가 n개이면 CUBE 그룹핑 조합은 2n개

SELECT column, group_function(column)
FROM table
[WHERE condition]
[GROUP BY [ROLLUP | CUBE] group_by_expression]
[HAVING group_condition]

GROUPING 함수

– 인수로 지정된 칼럼이 ROLLUP이나 CUBE 연산자로 생성된 그룹 조합에서 사용되었는지 여부를 1 또는 0으로 반환

– 사용하면 0, 아니면 1

SELECT column, group_function(column), GROUPING(column)
FROM table
[GROUP BY [ROLLUP | CUBE] group_by_expression]
		  [GROUPING SET(column, column...), ...]
[HAVING group_condition]

GROUPING SETS : 괄호를 사용하여 복수 개의 그룹 조건 지정

HAVING 절

– GROUP BY 절에 의해 생성된 그룹을 대상으로 조건을 적용

HAVING 절의 실행 과정

–테이블에서 WHERE 절에 의해조건을만족하는 행 집합을 선택

–행 집합을 GROUP BY 절에의해 그룹핑

- HAVING절에 의해 조건을 만족하는 그룹을 선택

SELECT column, group_function(column), GROUPING(column)
FROM table
[GROUP BY [ROLLUP | CUBE] group_by_expression]
[HAVING group_condition]
[ORDER BY column]

HAVING과 WHERE의 차이

–WHERE 절의검색조건을먼저실행하는방법이효율적
•그룹화하는 행 집합을 줄여서 내부 정렬 시간을 단축
•SQL 처리성능향상

SQL 함수

- 칼럼의 값이나 데이터 타입의 변경하는 경우

- 숫자 또는 날짜 데이터의 출력 형식 변경하는 경우

- 하나 이상의 행에 대한 집계(aggregation)를 하는 경우

SQL 함수의 유형

- 단일 행 함수 : 테이블에 저장되어 있는 개별 행을 대상으로 함수를 적용하여 하나의 결과를 반환하는 함수

- 복수 행 함수 : 조건에 따라 여러 행을 그룹화하여 그룹별로 결과를 하나씩 반환하는 함수

단일 행 함수

- 데이터 값을 조작하는데 주로 사용

- 행별로 함수를 적용하여 하나의 결과를 반환하는 함수

단일 행 함수의 사용법

function_name (column | expression, [arg1, arg2, . . .])

– function_name : 단일행 함수 이름

– column : 칼럼 이름

– expression : 문자열 또는 표현식

– arg1, arg2 : 함수의 인수(상수, 변수, 칼럼 이름, 표현식)

문자 함수

– 문자 데이터를 입력하여 문자나 숫자를 결과로 반환하는 함수

문자 함수의 종류

– 대소문자변환 함수

– 문자조작 함수

– 문자열 길이 반환 함수

문자열 길이 반환 함수

문자 조작 함수의 종류와 사용 예

문자 조작 함수의 종류와 사용 예

숫자 함수

- 숫자 데이터를 처리하기 위한 함수

날짜 함수(sql의 특징! 날짜 계산이 가능하다!)

날짜 함수의 종류

날짜 연산 함수(날짜 함수)

데이터 타입의 변환

– 숫자나 날짜 타입을 문자와 함께 결합하거나 보고서 양식에 맞추기 위해 주로 사용

묵시적인 데이터 타입 변환

- 묵시적인 데이터 타입 변환은 정확한 연산을 위하여 오라클에서 데이터 타입을 내부적으로 변환하는 경우

WHERE A=B 일 때일 때(A는 칼럼, B는 상수)

– 문자 타입의 숫자 타입으로 변환은 문자열이 숫자로 구성된 경우에만 가능

명시적인 데이터 타입 변환

- 사용자가 데이터 타입 변환 함수를 이용하여 명시적으로 데이터 타입을 변환

SQL> SELECT name, date
FROM table
WHERE date = TO_DATE('june 01, 01', 'MONTH DD', 'YY');

NAME   DATE
전은지 01/06/01

시간 표현 형식의 종류

기타 날짜 표현 형식의 종류

TO_CHAR 함수를 이용한 숫자 출력 형식 변환

중첩 함수

사용법

- F1함수의 결과 값은 F2의 인수로, F2의 결과 값은 F3의 인수로 사용된다.

F3(F2(F1(column, arg1), arg2), arg3)

사용 예

주민등록번호에서 생년월일을 추출하여 'YY/MM/DD'형태로 출력하여라

SQL> SELECT TO_CHAR(TO_DATE(SUBSTR(idnum, 1, 6), 'YYMMDD'), 'YY/MM/DD') BIRTHDATE
FROM table;

주민등록번호(idnum)를 1~6번째 자리 생년월일 추출 후 DATE형식'YYMMDD'으로 바꾼 뒤 CHAR형식'YY/MM/DD'로 변환한다.

NVL 변환 함수: NVL

- NVL 함수는 NULL을 0 또는 다른 값으로 변환하기 위한 함수

사용법

NVL(expression1, expression2)

– expression1 : NULL을 포함하는 칼럼 또는 표현식

– expression2 : NULL을 대체하는 값

주의: expression1과 expression2는 반드시 동일한 데이터 타입 이어야 한다.

NVL2(NVL 확장 함수)

– NVL2 함수는 첫 번째 인수 값이 NULL이 아니면 두 번째 인수값을 출력하고, 첫 번째 인수값이 NULL이면 세 번째 인수값을 출력하는 함수

NVL2(expression1, expression2, expresion3)

– expression1 : NULL을 포함하는 칼럼 또는 표현식

– expression2 : expression1이 NULL이 아닐 때 반환되는 값

– expression3 : expression1이 NULL일 때 대체되는 값

NULLIF(NVL 확장 함수)

– NULLIF 함수는 두 개의 표현식을 비교하여 값이 동일하면 NULL을 반환하고, 일치하지 않으면 첫 번째 표현식의 값을 반환

NULLIF(expression1, expression2)

– expression1 : expression2와 비교하는 값

COALESCE 함수

– COALESCE 함수는 인수중에서 NULL이 아닌 첫 번째 인수를 반환하는 함수

COALESCE(expression-1, expression-2, … ,expression-n)

– expression-1 : expression-1이 NULL이 아니면 expression-1을 반환
– expression-2 : expression-1이 NULL이고 expression-2가 NULL이 아니면 expression-2를 반환
– expression-n : expression-1부터 expression-n-1까지의 값이 NULL이고 expression-n이 NULL이 아니면 expression-n을 반환

DECODE 함수

–DECODE 함수는 기존 프로그래밍 언어에서 IF문이나 CASE 문으로 표현되는 복잡한 알고리즘을 하나의 SQL 명령문으로 간단하게 표현할 수 있는 유용한 기능

–DECODE 함수에서 비교 연산자는'='만 가능

DECODE(expression | column, SEARCH1,
    RESULT1[, SEARCH2, RESULT2, …, ]
    [, DEFAULT])

–표현식 또는 칼럼의 값이 SEARCH1 값과 일치하면 RESULT1 값을 반환, SEARCH2 값과 일치하면 RESULT2 값 반환
–일치하는 값이 없거나 NULL인 경우에는 기본값 반환
–기본값이 없는 경우에는 NULL 반환

주의사항

ELSE없는 IF를 사용하라
가능한 그룹함수내에서 NVL을 사용하지 마라
가능한 반복해서 DECODE를 사용하지 마라(CASE문으로 대체하는 등)
펼칠 컬럼이 너무 많을때는 먼저 GROUP BY한 후 처리하라

CASE 함수

– CASE 함수는 DECODE 함수의 기능을 확장한 함수
– DECODE 함수는 표현식 또는 칼럼 값이'='비교를 통해 조건 과일 치하는 경우에만 다른 값으로 대치할 수 있지만, CASE 함수에서는 산술 연산, 관계 연산, 논리 연산과 같은 다양한 비교가 가능
– 또한 WHEN 절에서 표현식을 다양하게 정의
– 8.1.7에서부터 지원되었으며, 9i에서 SQL, PL/SQL에서 완벽히 지원
– DECODE 함수에 비해 직관적 인문 법체계와 다양한 비교 표현식 사용

WHERE절 

- 테이블에 저장된 데이터중에서 원하는 데이터만 선택적으로 검색하는 기능

- WHERE 절의 조건문은 칼럼 이름, 연산자, 상수, 산술 표현식을 결합하여 다양한 형태로 표현 가능

- WHERE 절에서 사용하는 데이터 타입은 문자, 숫자, 날짜 타입 사용 가능

- 문자와 날짜 타입의 상수 값은 작은 따옴표('')로 묶어서 표현하 고 숫자는 그대로 사용

- 상수 값에서 영문자는 대소문자를 구별

비교연산자

- WHERE절에서 숫자, 문자, 날짜의 크기나 순서를 비교하는 연산자

SQL연산자

- SQL 연산자는 SQL 언어에만 제공

- SQL 연산자는 모든 데이터 타입에 대해 사용 가능

NULL 개념

- NULL은 미확인 값이나 아직 적용되지 않은 값을 의미

- 0도 아니며 스페이스도 아닌 정의되지 않은 값

- NULL은 단독으로 인덱스를 사용할 수 없으므로 가능한 정의가 능한 값(특정값)으로 정한다면 ( '0','99' 등 ) 인덱스를 이용할 수 있다. 

- 그렇다고 생각없이 NULL을 특정 값으로 변환하는 것은 향후 큰 문제를 일으킬 수 있으므로 주의 깊게 사용해야 함.

NULL의 정의

- 컬럼에 아무런 값이 없을 경우, 컬럼은 null 상태라고 이야기를 함 또는 null을 포함했다고 정의함

- null 은 어떠한 not null 또는 Primary key 제약조건을 포함하지 않는 경우 모든 컬럼의 모든 데이터 타입에서도 나타날 수 있음

- null은 값을 알 수 없거나 아무런 의미가 없을 경우 사용됨

- null 값을 0(zero)을 나타내는데 사용하지는 말것(null과 0은 동일한 값이 아님)

집합 연산자

- 테이블을 구성하는 행집합에 대해 테이블의 부분 집합을 결과로 반환하는 연산자

- 합병 가능 : 집합 연산의 대상이 되는 두 테이블의 칼럼수가 같고, 대응되는 칼럼끼리 데이터 타입이 동일

정렬

정렬(sorting)

- SQL 명령문에서 검색된 결과는 테이블에 데이터가 입력된 순서 대로 출력

- 하지만, 데이터의 출력 순서를 특정 컬럼을 기준으로 오름차순(ASC) 또는 내림차순(DESC)으로 정렬하는 경우가 자주 발생

- 여러 개의 칼럼에 대해 정렬 순서를 정하는 경우도 발생

ORDER BY 절

정렬 방법

- ORDER BY : 칼럼이나 표현식을 기준으로 출력 결과를 정렬할 때 사용

- ASC : 오른차순으로 정렬, 기본 값

- DESC : 내림차순으로 정렬하는 경우에 사용, 생략 불가능

- 문자 값은 알파벳순으로 출력됨

- 날짜 타입은 가장 빠른 값 부터 출력됨 
- 한글은 가나다순으로 출력됨

- Null값은 오름차순에서는 제일 나중에 그리고 내림차순에서는 제일 먼저 출력됨

- SELECT 절에 포함되지 않는 컬럼도 정렬을 하기 위한 ORDER BY 절에 올수 있음.

데이터베이스에 저장되는 데이터는 문자, 숫자, 날짜, 이미지 등과 같은 다양한 형식으로 표현

실무에서 칼럼에 대한 데이터 타입이나 길이의 결정은 데이타베이스 설계 단계에서 신중하게 고려해야하는 중요한 사항

데이터베이스 설계 단계에서 잘못 선정된 데이터 타입은 응용프로그램 개발을 어렵게 하거나 성능을 저하시키는 요인이 됨

CHAR 데이터 타입

개요

- 고정 길이의 문자열을 저장하며 최대 2,000바이트까지 저장 가능

- 지정된 길이보다 짧은 데이터가 입력되는 경우, 나머지 공간은 공백으로 채워짐

특징

- 데이터 입력시 사용자가 데이터를 입력하지 않으면 NULL 이 입력

- 지정된 길이보다 긴 데이터가 입력되면 오류 발생

- 주소 데이터와 같은 편차가 심한 데이터를 입력할 때 사용하면 저장 공간이 낭비될 수 있음

- 주민등록번호와 같이 길이가 일정하거나 비슷핚 경우에 사용하는 것이 좋음

VARCHAR2 데이터 타입

개요

- 가변 길이의 문자열을 저장하기 위해 사용하는 데이터 타입

- 최대 4,000 바이트 저장 가능

- 지정된 길이보다 짧은 문자열이 입력되면 뒷부분은 NULL로 처리되어 저장공간을 낭비하지 않음

특징

- 데이터 입력시 사용자가 값을 입력하지 않으면 NULL 이 입력

- 지정된 길이보다 긴 데이터가 입력되면 데이터가 입력되지 않고 오류 발생

- 입력될 데이터의 편차가 심하거나 NULL 이 많이 입력되는 경우에 사용하는 것이 효율적

- 실무에서는 CHAR 데이터 타입보다 VARCHAR2 를 많이 사용

문자형 데이터 타입과 문자 상수 비교 방법

비교 방법

- 문자 상수는 문자 데이터 타입으로 내부 변환되어 문자 타입의 비교 방식에 의해 처리

주의

- 동일한 문자 상수를 비교하는 경우에도 비교 대상 칼럼의 데이터 타입에 따라 비교 결과가 달라질 수 있음

문자 상수와 CHAR 데이터 타입 비교

- 문자 상수를 CHAR 타입으로 변환한 후, CHAR와 CHAR 비교 방식 으로 비교

문자 상수와 VARCHAR2 데이터 타입 비교

- 문자 상수를 VARCHAR2 타입으로 변환한 후 VARCHAR2와 VARCHAR2 비교 방식에 의해 비교

NUMBER 데이터 타입

개요

숫자를 저장하며, 가변 길이 데이터 타입으로 최대 38자리까지 저장 가능

저장 가능한 숫자의 범위 : 1.0X10^-130 ~ 96.9...X10^125

소수는 NUMBER(precision, scale) 형식으로 입력

- 지정된 자리 이하에서 반올림되어 저장

- precision : 소수점을 포함하는 전체 자리 수

- scale : 소수점이하 자리 수

- precision을 지정하지 않고 숫자를 입력하는 경우

 입력되는 숫자 값의 크기만큼 저장공간이 할당됨

- scale을 지정하지 않고 소수점을 입력하는 경우

 소수점 이하는 반올림되어 정수 값만 저장됨

DATE 데이터 타입

개요

- 세기, 년도, 월, 일, 시간, 분, 초의 날짜와 시간 정보를 저장하기 위한 데이터 타입

- 7비트의 고정길이 필드에 저장

- 날짜나 시간에 대한 연산이나 이력 정보를 관리하기 위해 사용

- 퍼스널 오라클의 기본 날짜 형식 : YY/MM/DD 형식

기능

- TO_DATE함수 사용 가능

    문자 형태로 저장된 데이터를 날짜 형식으로 변환

- SYSDATE함수 사용 가능

    시스템의 현재 날짜와 시간을 반환하는 함수

주의 

- 날짜 데이터 입력시 월, 일을 지정하지 않으면 입력되는 시점을 기 준으로 해당 월과 1일이 입력, 시간을 지정하지 않으면 12:00:00 로 입력

- 시간 정보가 입력된 경우에는 '=' 비교시 시간 정보도 함께 비교

LOB데이터 타입

개요

- 텍스트, 그래픽, 이미지, 동영상, 사운드 등과 같이 구조화되지 않은 대용량의 텍스트나 멀티미디어 데이터를 저장하기 위한 데이터 타입

- 최대 4GB까지 저장

종류

- BLOB

 그래픽 이미지, 동영상, 사운드와 같이 구조화되지 않은 바이너리 데이터를 저장하기 위해 사용

- CLOB

 e-Book, html 과 같은 대용량의 텍스트 데이터 저장

- BFILE

 바이너리 데이터를 파일 형태로 저장, 읽기 전용이므로 내용 변경 불가능

ROWID 데이터 타입

개요

테이블에서 행의 위치를 지정하는 논리적인 주소 값

데이타베이스 전체에서 중복되지 않는 유일한 값

테이블에 새로운 행이 삽입되면 테이블 내부에서 의사 칼럼 (pseudo column) 형태로 자동적으로 생성

특정 레코드를 랜덤 액세스하기 위한 가장 빠른 방법

ROWID 는 데이터베이스에 저장되지 않음

SELECT 명령문으로 조회는 가능하나, INSERT, UPDATE와 같은 DML 문으로 임의적으로 변경 불가능

형식

데이터 객체 번호(data object number)

- 데이터 객체 생성 시 할당

상대적 파일 번호(relative file number)

- 데이터가 저장되는 물리적인 데이터 파일 번호(10bit)

블록 번호( block number)

- 데이터 파일내에서 행을 포함하는 블록 위치(22bit)

행 번호(row number)

- 블록 헤더내에서 행 디렉토리 슬롯의 위치(16bit)

참조변수

Pseudo-column

참조만 될 뿐 데이터베이스에 따로 저장되지 않음

Oracle의 Pseudo-column은 테이블의 실제적인 컬럼은 아니므로 테이블의 describe을 통해서는 보이지 않지만 매우 유용하게 사용

SELECT ROWID, STUDNO FROM STUDENT;

TIMESTAMP 데이터 타입

개요

- DATE 타입의 확장된 형태로서 백만분의 일초 단위까지 표현 가 능

- milli second 의 기본 값은 6자리, 최대 9자리까지 사용 가능

종류

- TIMESTAMP WITH TIME ZONE

    TIMESTAMP 데이터 타입에 지역 시간대(time zone)를 함께 저장

    지역 시간대는 세계 표준시간대(universal time)의 표준 시간을 기준 으로 현 지역 시간대를 환산한 것

- TIMESTAMP WITH LOCAL TIME ZONE

    사용자 데이터베이스의 지역 시간대를 따름

    • 지역 시간대를 다른 곳으로 옮겨도 TIME ZONE 를 변경할 필요는 없음

CREATE TABLE EX_TIME
(ID NUMBER(2),
BASICTIME TIMESTAMP,
STANDARDTIME TIMESTAMP WITH TIME ZONE,
LOCALTIME TIMESTAMP WITH LOCAL TIME ZONE);

DUAL 테이블

DUAL테이블은 데이터 딕셔너리와 함께 Oracle에 의해 자동으로 생성되는 테이블 입니다.

    DUAL테이블은 사용자 SYS의 스키마에 있지만 모든 사용자는 DUAL이라는 이름으로 엑세스 할 수 있습니다.

    DUAL테이블은 VARCHAR2(1)으로 정의된 DUMMY라는 하나의 열이 있으며 값을 가지는 하나의 행도 포함되어 있습니다. 

    DUAL테이블은 사용자가 계산이나 사용자 함수등을 실행하고자 할 경우에 유용 합니다. 

데이터 타입의 결정법

1. 응용 프로그램 개발 시 오류 감소

2. SQL 명령문의 검색 성능에 영향(index)

3. 문자형 데이터

    데이터의 길이가 가변인지, 고정인지 고려

4. 데이터가 숫자인 경우

    숫자 데이터 타입 또는 문자형 데이터 타입으로 지정할지 고려

5. 데이터가 날짜 타입인 경우

    문자 데이터 타입으로 지정하는 편이 효율적

SQL과 명령어 비교

SQL*PLUS 명령어의 유형별 기능과 자주 사용하는 명령어

SQL*PLUS 사용 환경 명령어

SQL 명령문의 실행 결과를 사용자가 효과적으로 확인할 수 있는 여러 가지 시스템 환경을 제어하기 위한 명령어

명령문은 세션 종료나 환경 설정을 변경, 삭제할 때까지 혹은 세션 사용자가 SQL*PLUS툴로 로그인해서 로그 아웃(EXIT)할 때까지 유지된다.

SET 명령어

현재 작업중인 세션의 환경을 제어하기 위한 시스템 변수 설정

사용법

SET SYSTEM_VARIABLE VALUE

system_variable:SQL 명령문의 실행 결과를 제어하기 위한 시스템 변수

value : 시스템 변수별로 지정하는 옵션

SHOW 명령어

SQL > SHOW ALL

SQL > SHOW system_variable

All : 모든 시스템 변수 설정 값 확인

system_variable : 특정 시스템 변수의 설정 값 확인

시스템 변수

SQL 명령문의 실행 결과를 제어하기 위한 함수

AUTOCOMMIT : 삽입, 수정, 삭제와 같은 DML 명령묷의 처리 결과를 디스크에 영구적으 로 저장하기 위해 반드시 COMMIT 명령문의 실행 필요

SQL> INSERT INTO department values(301,‟제어계측학과‟,200,‟5호관‟);

FEEDBACK : SELECT 명령묷의 실행 결과를 표시하기 위하여 춗력 행의 수를 지정하는 변수

FEED[BACK] {6 | n | off | on}

HEADING : SELECT 명령묷의 실행 결과를 출력핛 때, 칼럼 제목의 출력 여부 를 제어하는 변수

HEADING {off | on}

LINESIZE : 한 화면에서 표시되는 SQL 명령문의 출력 결과에 대한 행의 크기를 설정하는 변수

LIN[LINESIZE] {80 | n}

기본 값은 80이며, 최대값은 시스템에 따라 차이가 남

PAGESIZE : 한 화면에서 표시되는 SQL 명령문의 실행 결과에 대한 페이지의 크기를 설정하는 변수

PAGES[SIZE] {14 | n}

기본 값은 14

PAUSE : SQL 명령문의 실행 결과를 한 화면에서 보기 힘든 경우, 한 페이 지씩 나누어 출력하기 위핚 변수

PAUSE {ON | OFF}

TERMOUT : SQL 명령문 실행 결과를 화면에 출력할 지 여부를 지정하기 위한 변수

TERM[OUT] {OFF | ON}

TIME : SQL 프롬프트 앞에 시스템의 현재 시갂을 함께 표시하도록 설정 하는 변수

TI[TIME] {OFF | ON}

TIMING : SQL 명령문을 실행하는데 소요된 시간을 출력하기 위한 변수

TIMING {OFF | ON}

UNDERLINE : 칼럼 제목과 데이터간의 구분 기호를 설정하기 위한 변수

UND[UNDERLINE] {- | C | OFF | ON}

SQL*Plus 형식 명령어의 개요

- SQL 명령문의 실행 결과에 대핚 칼럼 제목이나 데이터의 출력 형식을 변경하기 위한 명령어

COLUMN 명령어

- SQL 명령문의 실행 결과로 출력되는 칼럼 제목이나 칼럼 데이터 에 대한 출력 형식을 다양하게 지정하기 위한 명령어

COLUMN {COLUMN | ALIAS} [OPTION][FORMAT]

COLUMN 명령어 option

칼럼 데이터에 대한 출력 형식 종류

칼럼에 대한 설정 내용 확인 및 해제하기 위한 옵션

SQL*Plus 편집 명령어

- SQL*Plus 에서 최근에 실행된 SQL 명령문은 임시 기억공간인 SQL 버퍼에 저장

- SQL 버퍼에 저장된 명령문을 SQL*Plus 편집 명령으로 편집 가능

SQL*Plus 편집 명령어

LIST 명령어

- 버퍼에 저장된 SQL 명령문을 출력하기 위해 사용하는 명령어

- 특정 라인 또는 특정 범위, 전체 범위의 라인에 포함된 명령문을 확인 또는 편집시 특정 라인으로 이동하기 위해 사용

L[LIST] { n | m n }

DEL명령어

- SQL 버퍼에 저장된 SQL 명령문중에서 일부 내용을 삭제하기 위한 명령어

DEL {n | m n}

DEL : 현재 행을 삭제합니다

DEL n : n번째 행을 삭제

DEL m n : m ~ n 번째까지 삭제

INPUT 명령어

- SQL 버퍼에 저장된 SQL 명령문의 현재 라인 다음에 새로운 라인 을 추가하는 경우에 사용

I[INPUT] [text] 또는 0 text

A[APPEND] text

-Text를 현재 행의 끝에 추가합니다.

CHANGE 명령어

- 현재 활성화된 라인에서 특정 텍스트를 다른 텍스트로 변경하기 위해 사용하는 명령

C[HANGE] /old /new

CL[CLEAR] BUFF[BUFFER]

cl buff

R[RUN]

버퍼의 현재 SQL 문을 표시하고 실행

/

버퍼의 현재 SQL문을 표시하지 않고 실행

SQL*Plus 파일조작 명령어

- SQL 버퍼에 저장된 SQL 명령문을 파일에 저장하거나 파일에 저 장된 SQL 명령문을 읽어올 수 있도록 하는 명령어

SAV[SAVE] filename [REP[REPLANCE] APP[APPEND]]

- SQL 버퍼의 현재 내용을 파일에 저장

- APPEND : 기존 파일에 추가

- REPLACE : 기존 파일에 겹쳐씀

- 파일 이름의 기본 확장자는 .sql임

 - GET filename : 이전에 저장한 파일 내용을 SQL버퍼에 쓴다

SPO[SPOOL] [filename | OFF | OUT]

- 질의 결과를 파일에 저장

- OFF : 스풀 파일을 닫음

- OUT : 스풀 파일을 닫은 후 파일 결과를 시스템 프린터로 전송

- 확장명은 .LST

실습용 데이터

테이블 이름 확인

- 현재 접속한 데이터베이스 계정에 생성된 모든 테이블 이름을 확인

SELECT * 
FROM TAB;

테이블 구조 확인

DESC TBL_NAME;

DESC STUDENT;

SQL 명령문의 표준형식

SELECT [DISTINCT] *
FROM TABLE
[WHERE CONDITION]
[GROUP BY GROUP_BY_EXPRESSION]
[HAVING GROUP_CONDITION]
[ORDER BY COLUMN];

DISTINCT는 중복 제거

WHERE은 조건

GROUP BY는 그룹으로 묶음(행이줄어듦)

HAVING GROUP BY절에 의해 생성된 그룹을 대상으로 조건을 적용

ORDER BY 칼럼이나 표현식을 기준으로 출력 결과를 정렬할 때 사용

칼럼에 대한 별명 부여

- 칼럼 이름과 별명 사이에 공백을 추가

- 칼럼 이름과 별명 사이에 AS 키워드를 추가

- 큰 따옴표를 사용

    1.칼럼 이름과 별명 사이에 공백을 추가하는 경우

    2. 특수문자를 추가하거나 대소문자를 구분하는 경우

SELECT DNAME DEPT_NAME, DEPTNO AS DN
FROM DEPARTMENT;

합성 연산자(||) 사용

- 하나의 칼럼과 달느 칼럼, 산술 표현식 또는 상수 값과 연결하여 하나의 칼럼처럼 출력할 경우에 사용

산술 연산자(+,-,*,/)

- 칼럼 값에 산술 연산자를 적용하여 계산된 결과를 출력할 수 있는 기능을 제공

- 숫자 또는 날짜 타입(데이터 베이스 특징)에만 사용가능

- 수학에서 산술 연산자의 우선순위와 동일, 괄호를 사용하여 우선순위 변경가능