Transcript PowerPoint 프레젠테이션

Oracle 9i SQL 정리 자료집1
2003. 05
Oracle 9i SQL 정리 자료집 1
SQL 문장의 종류와 기능
종
류
문
법
설
명
쿼리
SELECT
-. DATA를 검색하는 명령어
DML
(Data
Manipulation
Language)
INSERT
UPDATE
DELETE
-. 데이터에 행을 삽입, 변경, 제거하는 작업을 수행하는 명령어.
DDL
(Data
Definition
Language)
CREATE
ALTER
DROP
RENAME
TRUNCATE
COMMENT
-. 데이터의 구조를 생성,변경,제거 등의 작업을 수행하는 명령어.
TCL
(Transaction
Control
Language)
COMMIT
ROLLBACK
SAVEPOINT
-. DML에 의해 변경된 일련의 TRANSACTION을 관리하는 명령어.
DCL
(Data
Control
Language)
GRANT
REVOKE
-. 사용자에게 구조에 대한 접근 권한을 부여하고 제거하는 명령어.
2
Oracle 9i SQL 정리 자료집 1
JOIN
종
류
문
법
설
명
CROSS JOIN
SELECT 테이블1.컬럼, 테이블2.컬럼
FROM 테이블1
CROSS JOIN 테이블2
-. 기존의 CARTESIAN JOIN 기법의 명칭.
-. 2개 이상의 모든 데이터를 참조해야 하는 경우에 사용.
-. 거의 사용하지 않는다.
NATURAL JOIN
SELECT 테이블1.컬럼, 테이블2.컬럼
FROM 테이블1
NATURAL JOIN 태이블2
-. 기존 EQUI JOIN 기법의 명칭
-. 두 개 이상의 테이블이 공통되는 컬럼에 의해 논리적으로 연결되는 조인 기법.
-. SELECT 절에서 공통되는 컬럼에 테이블 명을 생략해야 한다.(테이블2.컬럼->컬럼)
USING JOIN
SELECT 테이블1.컬럼, 테이블2.컬럼
FROM 테이블1
JOIN 테이블2
USING (컬럼)
-. 기존 EQUI JOIN 기법의 명칭
-. USING 구에 정의된 컬럼을 기준으로 NATURAL JOIN이 발생됨.
-. SELECT 절에서 공통되는 컬럼에 테이블 명을 생략해야 한다.(테이블2.컬럼->컬럼)
JOIN ~ ON
SELECT a.컬럼, b.켤럼
FROM 테이블1 a
JOIN 테이블2 b
ON (a.컬럼 = b.켤럼)
-. 기존 SELF JOIN 기법의 명칭
-. 하나의 테이블이 2번 이상 반복적으로 사용되고 참조할 컬럼이 자신의 테이블에 존재
할 때 사용.
-. 테이블1과 테이블2가 같은 테이블인 경우에 사용.
RIGHT OUTER
JOIN
SELECT 테이블1.컬럼, 테이블2.컬럼
FROM 테이블1
RIGHT OUTER JOIN 테이블2
ON (테이블1.컬럼 = 테이블2.컬럼)
-. 기존 데이터가 존재하지 않는 쪽 테이블에 (+)의 기법의 명칭.
-. 데이터가 존재하지 않는 쪽에 (+) 기호를 둔 것과 같은 결과를 만든다.
-. 문법에서 테이블2(+) 와 같은 의미
SELECT 테이블1.컬럼, 테이블2.컬럼
FROM 테이블1
LEFT OUTER JOIN 테이블2
ON (테이블1.컬럼 = 테이블2.컬럼)
-. 기존 데이터가 존재하지 않는 쪽 테이블에 (+)의 기법의 명칭.
-. 데이터가 존재하는 쪽에 (+) 기호를 둔 것과 같은 결과를 만든다.
-. 문법에서 테이블1(+)와 같은 의미
SELECT 테이블1.컬럼, 테이블2.컬럼
FROM 테이블1
FULL OUTER JOIN 테이블2
ON (테이블1.컬럼 = 테이블2.컬럼)
-. 기존 양쪽 테이블에 (+)의 기법의 명칭.
-. 양쪽 테이블 모두 서로 데이터가 존재하지 않는 것이 존재 할 때 사용.
-. 문법에서 테이블1(+), 테이블2(+)와 같은 의미.
LEFT OUTER
JOIN
FULL OUTER
JOIN
3
Oracle 9i SQL 정리 자료집 1
단일 행 FUNCTION
종
ROUND
류
문
법
ROUND(컬럼 명|표현식|상수 값, n)
설
명
-. 인자로 받은 수를 명시된 (자리 수-1)에서 반올림 하는 함수.
-. n이 양수 : 소수점의 오른쪽 (자리 수 – 1)에서 반올림.
-. n이 음수 : 소수점의 왼쪽 자리 수에서 반올림.
-. n이 생략 : 소수점 첫 번째 자리에서 반올림.
-. 인자로 받은 수를 명시된 (자리 수 – 1) 에서 버린 후의 값을 RETURN 하는 함수.
-. n이 양수 : 소수점의 오른쪽 (자리 수 – 1)에서 버림.
TRUNC
TRUNC(컬럼 명|표현식|상수 값, n)
CEIL
CEIL(컬럼 명|표현식|상수 값)
-. 인자의 값에서 첫번째 소수점의 값을 올린 후 값을 RETURN하는 함수.
-. 항상 결과는 정수 값이다.
FLOOR
FLOOR(컬럼 명|표현식|상수 값)
-. 인자의 값에서 첫번째 소수점의 값을 버린 후 값을 RETURN하는 함수.
-. 항상 결과는 정수 값이다.
MOD
MOD(m, n)
-. m을 n으로 나눈 후 나머지 값을 RETURN하는 함수.
-. 항상 결과는 정수 값이다.
ABS
ABS(m)
-. 절대값을 RETURN하는 함수.
-. 항상 결과는 양의 값이다.
SIGN(m)
-.
-.
-.
-.
SIGN
-. n이 음수 : 소수점의 왼쪽 자리 수에서 버림.
-. n이 생략 : 소수점 첫 번째 자리에서 버림.
부호를 숫자 값으로 RETURN하는 함수.
m이 양수인 경우 RETURN 값 : 1
m이 음수인 경우 RETURN 값 : -1
m이 0인 경우 RETURN 값
:0
4
Oracle 9i SQL 정리 자료집 1
복수 행(문자열) FUNCTION
종
류
문
법
설
명
UPPER
UPPER(문자열)
-. 문자열을 대문자로 바꾸어 RETURN하는 함수.
LOWER
LOWER(문자열)
-. 문자열을 소문자로 바꾸어 RETURN하는 함수.
INITCAP
INITCAP(문자열)
-. 문자열에서 띄어쓰기를 구분하여 첫 문자만 대문자로 바꾸어 RETURN하는 함수.
CONCAT
CONCAT(문자열1,문자열2)
-. 문자열1과 문자열2를 연결하여 값을 RETURN하는 함수.
LENGTH
LENGTH(문자열)
-. 문자열의 길이 값을 RETURN하는 함수
SUBSTR
SUBSTR(문자열, m, n)
-.
-.
-.
-.
-.
-.
INSTR
INSTR(문자열1, 문자열2)
-. 문자열2의 문자열을 문자열1에서 찾아 그 위치의 정수 값을 RETURN하는 함수.
-. 문자열2의 문자열이 문자열1에서 찾지 못했을 경우 0을 RETURN한다.
LPAD(문자열1, n, 문자열2)
-. 문자열1이 n자리 보다 작은 경우 왼쪽에서부터 문자열2의 패턴으로 n자리 수를
맞춰 RETURN하는 함수.
-. n의 값이 문자열1의 길이보다 작은 경우 문자열1의 왼쪽에서부터 n수 만큼 RETURN 된다.
RPAD
RPAD(문자열1, n, 문자열2)
-. 문자열1이 n자리 보다 작은 경우 오른쪽에서부터 문자열2의 패턴으로 n자리 수를
맞춰 RETURN하는 함수.
-. n의 값이 문자열1의 길이보다 작은 경우 문자열1의 왼쪽에서부터 n수 만큼 RETURN 된다.
LTRIM
LTRIM(문자열, 문자)
-. 특정문자와 일치하는 문자를 문자열의 왼쪽으로부터 제거한 값을
RETURN하는 함수.
RTRIM
RTRIM(문자열, 문자)
-. 특정문자와 일치하는 문자를 문자열의 오른쪽으로부터 제거한 값을
RETURN하는 함수.
TRIM
TRIM(문자 FROM 문자열)
-. 특정문자와 일치하는 문자를 문자열의 왼쪽이나 오른쪽으로부터 제거한 값을
RETURN하는 함수.
LPAD
문자열의 m번째 위치부터 n개수 만큼의 문자를 잘라 RETURN하는 함수.
LIKE문장은 ORACLE 내부에서 SUBSTR를 호출하여 사용한다.
m이 1인 경우
: 문자열의 첫 번째 문자를 가리킨다.
m이 음수인 경우 : 문자열의 뒤쪽에서부터 m번째 문자를 가리킨다.
N의 의미
: m의 위치부터 문자의 개수를 의미한다.
n이 생략된 경우 : m의 위치부터 문자열의 끝까지의 문자를 처리한다.
5
Oracle 9i SQL 정리 자료집 1
복수 행(날짜) FUNCTION
종
류
문
법
설
명
SYSDATE
SYSDATE
-. 현재의 날짜를 RETURN하는 함수.
ADD_MONTHS
ADD_MONTHS(날짜,n)
-. 인자로 받은 날짜에 명시된 정수 n개월 수만큼 더한 결과를 RETURN하는 함수.
INTERVAL
INTERVAL ‘더할 값’ {DAY TO
SECOND | YEAR TO MONTH}
-. 날짜와 시간 연산을 좀 더 편리하게 할 때 사용한다.
-. DAY TO SECOND : (?)
-. YEAR TO MONTH : ‘더할 값’에 ‘1-2’로 주면 1년 2개월을 의미함.
LAST_DAY
LAST_DAY(날짜)
-. 인자로 받은 날짜의 해당 월의 마지막 일자를 RETURN하는 함수.
NEXT_DAY
NEXT_DAY(날짜, 요일)
-. 명시된 날짜에서 가장 빠른 요일의 날짜를 RETURN하는 함수.
MONTHS_BETWEEN(날짜1, 날
짜2)
-. 날짜1 인자에서 날짜2 인자 값을 뺀 숫자 값을 RETURN하는 함수.
-. RETURN값이 날짜가 아니라 소수점 숫자로 RETURN 된다.
-. SELECT MONTHS_BETWEEN(sysdate+40,sysdate) FROM DUAL;
결과 : 1.32258064516129
-. SELECT MONTHS_BETWEEN(sysdate+31,sysdate) FROM DUAL;
결과 : 1
MONTHS_BETWE
EN
ROUND
ROUND(날짜, 형식기준)
-. 인자로 받은 날짜를 명시된 기준에 의해 반올림한 값을 RETURN하는 함수.
-. 월의 기준 : 16일을 기준으로 반올림.
년의 기준 : 7월을 기준으로 반올림.
-. SELECT TO_CHAR(SYSDATE, YYYY-MM-DD), ROUND(SYSDATE,'MONTH'), ROUND(SYSDATE,'YEAR')
FROM DUAL;
결과 : 2003-05-23, 2003-06-01, 2003-01-01
TRUNC
TRUNC(날짜, 형식기준)
-. 인자로 받은 날짜를 명시된 기준에 의해 버림 한 값을 RETURN하는 함수.
-. SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD') ,TRUNC(SYSDATE,'MONTH'), TRUNC(SYSDATE,'YEAR')
FROM DUAL;
결과 : 2003-05-23, 2003-05-01, 2003-01-01
6
Oracle 9i SQL 정리 자료집 1
복수 행(변환) FUNCTION
종
류
TO_CHAR
문
법
TO_CHAR(날짜|숫자, 변환형식)
설
명
-. 인자로 받은 날짜 또는 숫자를 명시된 형식대로 변환한 값을 RETURN하는 함수.
-. 변환 형식(년, 월, 일) 설명
YYYY : 년도를 네 자리 숫자로 표시한다.
YY
: 년도를 頭 자리 숫자로 표시한다.
MONTH : 달을 문자로 표시한다. (예, NOVEMBER)
MON
: 달의 앞 문자 세 개만 표시한다. (예, NOV)
MM
: 달을 두 자리 숫자로 표시한다.
DD
: 일을 두 잘 숫자로 표시한다.
DAY
: 일을 요일로 표시한다. (예, FIRDAY)
DY
: 일의 앞 문자 세 개만 표시한다. (예, FIR)
-. 변환 형식(시, 분, 초) 설명
AM/A.M. : 오전을 표시한다.
PM/P.M. : 오후를 표시한다.
HH/HH12 : 시간을 12시간제로 표시한다.
HH24
: 시간을 24시간제로 표시한다.
MI
: 분을 표시한다.
SS
: 초를 표시한다.
-. 변환 형식(정수,금액) 설명
9 : 자릿수를 표시한다. 빈 자리는 표시하지 않는다.
0 : 자릿수를 표시한다.
$ : ‘$’를 표시한다.
L : 각 지역별 통화기호를 표시한다.
. : 소수점을 표시한다.
, : 천의 자리를 구분하는 기호를 표시한다.
TO_DATE
TO_DATE(날짜, 변환형식)
-. 인자로 받은 날짜 형태의 문자열과 명시된 날짜 형식을 이용해 날짜 값을 RETURN하는 함수.
TO_NUMBER
TO_NUMBER(문자열, 변환형식)
-. 인자로 받은 문자열을 변환 형식을 이용해 숫자 값을 RETURN하는 함수.
7
Oracle 9i SQL 정리 자료집 1
복수 행(그룹1) FUNCTION
종
류
COUNT
문
법
COUNT(컬럼 | 표현식 | *)
설
명
-. 인자로 받은 컬럼의 행 수를 RETURN한느 함수.
-. ‘*’를 인자로 사용할 경우엔 NULL값도 카운트하여 결과를 RETURN한다.
-. 인자에 컬럼 명을 사용할 경우 컬럼이 NULL인 경우에는 카운트 되지 않는다.
AVG
AVG([DISTINCT] 컬럼 명 | 표현식)
-. 인자로 받은 컬럼 값의 평균을 RETURN하는 함수.
-. 인자의 컬럼이 NULL인 경우에는 카운트 되지 않으며, 그 NULL 컬럼은 무시된다.
SUM
SUM([DISTINCT] 컬럼 명 | 표현식)
-. 인자로 받은 컬럼 값의 합을 RETURN하는 함수.
MIN
MIN([DISTINCT] 컬럼 명 | 표현식)
-. 인자로 받은 컬럼 값에서 최소값을 RETURN하는 함수.
MAX
MAX([DISTINCT] 컬럼 명 | 표현식)
-. 인자로 받은 컬럼 값에서 최대값을 RETURN하는 함수.
RANK(상수 값) WITH GROUP
(ORDER BY 켤럼 명 [DESC | ASC]
[NULLS {FIRST | LAST}])
-.
-.
-.
-.
RANK() OVER (ORDER BY 컬럼 명
[DESC | ASC] [NULLS {FIRST |
LAST}])
-. 분석 함수의 기능을 한다.
-. 전체 행을 대상으로 각각의 행에 대한 순위를 계산하여 결과를 RETURN 한다.
SELECT 컬럼 명, 그룹함수(컬럼 명)
FROM 테이블 명
[WHERE 조건]
GROUP BY 그룹 하고자 하는 컬럼 명
[ORDER BY {컬럼 명|SELECT 구에
열거된 컬럼의 순서|컬럼의 별칭}]
-. GROUP BY절은 행을 GROUP화 한다.
-. GROUP BY절에서는 SELECT절에 열거된 컬럼의 순서나 컬럼의 별칭을 사용할 수 없음.
-. Default로 행은 오름차순으로 정렬이 되며, 그룹 함수가 아닌 어떠한 컬럼 이나 표현식도
GROUP BY절에 나타나야 한다.
-. 컬럼에 NULL은 무시된다.
RANK
GROUP BY절
값의 집합에서 순위를 계산하여 RETURN하는 함수.
그룹 함수의 기능을 한다.
인자를 이용해 전체 행으로부터의 순서를 계산한다.
인자는 반드시 상수가 되어야 하며, ORDER BY절에서 명시한 컬럼과 같은 데이터
타입 이어야 한다.
8
Oracle 9i SQL 정리 자료집 1
복수 행(그룹2) FUNCTION
종
류
HAVING 절
ROLLUP
문
법
SELECT 컬럼 명, 그룹함수(컬럼 명)
FROM 테이블 명
[WHERE 조건]
GROUP BY 그룹 하고자 하는 컬럼 명
HAVING 그룹 조건
[ORDER BY {컬럼 명|SELECT 구에
열거된 컬럼의 순서|컬럼의 별칭}]
SELECT 컬럼 명, 그룹 함수
FROM 테이블 명
[WHERE 조건]
GROUP BY ROLLUP(그룹 하고자 하
는 컬럼 명, …)
HAVING 그룹 조건
[ORDER BY {컬럼 명|SELECT 구에
열거된 컬럼의 순서|컬럼의 별칭}]
CUBE
GROUPING
SETS
GROUP BY CUBE(그룹 하고자 하는
컬럼 명, …)
GROUP BY GROUPING SETS(그룹
하고자 하는 컬럼 명, …)
설
-.
-.
-.
-.
명
그룹화 된 결과에 제약을 가하는 방법
GROUP BY절 다음에 기술한다.
HAVING절에는 그룹함수나 GROUP BY절에 사용한 것만이 올 수 있다.
SELECT dept_no, AVG(pay)
FROM
emp
GROUP BY dept_no
HAVING AVG(pay) >= 5000;
-. 주로 통계 데이터를 나타내는 합산형 형태의 보고서를 작성 할 때 사용한다.
-. 일반적으로 GROUPING 함수와 함께 사용되어 선행 그룹에 대한 출력이 바뀔 때마다 그
선행 그룹별 집계를 출력하고 최종적으로 전체 집계를 출력하는 형태의 보고서에 사용.
-. SELECT dept_no,GROUPING(dept_no), job_no,GROUPING(job_no),COUNT(*),
AVG(pay)*12
FROM emp
GROUP BY ROLLUP(dept_no,job_no);
-. ROLLUP 함수와 마찬가지로 주로 통계 데이터를 나타내는 합산형 형태의 보고서에 사용.
-. 일반적 GROUPING 함수와 같이 사용되어 먼저 선행 그룹에 대한 출력이 바뀔 때마다 그
선행 그룹별 집계를 출력하며 이 후 후행 그룹별 집계를 출력하는 형의 보고서에 사용.
-. SELECT dept_no,GROUPING(dept_no), job_no,GROUPING(job_no),COUNT(*),
AVG(pay)*12
FROM emp
GROUP BY CUBE(dept_no,job_no)
ORDER BY dept_no;
-. GROUP BY 절과 UNION ALL 연산자의 결합 기능을 가지고 있다.
-. UNION ALL 기능을 쉽게 사용할 수 있도록 제공한 것이 GROUPING SETS이다.
-. SELECT dept_no, job_no, manager_no, AVG(pay)
FROM
emp
GROUP BY GROUPING SETS((dept_no, job_no), (job_no,manager_no));
-. 복잡한 UNION 구문이 간단해지며 성능 또한 빠른 장점을 가지고 있다.
9
Oracle 9i SQL 정리 자료집 1
복수 행(기타) FUNCTION
종
류
문
법
설
명
NVL
NVL(컬럼 명|표현식, 대체할 값)
-. 첫번째 인자(컬럼 명 또는 표현식)가 NULL인 경우 두 번째 인자(대체할 값)로
바꾸어
RETURN하는 함수.
-. NULL은 어떠한 값과 연산을 해도 그 결과는 NULL이 됨을 유의한다.
-. 대체할 값은 해당 컬럼의 데이터 타입과 동일해야 한다.
NVL2
NVL2(컬럼 명|표현식, NULL 값이 아
닐 때의 대체할 값, NULL값일 때의 대
체할 값)
-. 첫번째 인자(컬럼 명 또는 표현식)가 NULL이 아닌 경우 두 번째 인자의 값으로
대체하고,
NULL인 경우에는 세 번째 인자의 값으로 대체한다.
-. 대체할 값들은 해당 컬럼의 데이터 타입과 동일해야 한다.
NULLIF(컬럼 명|표현식, 컬럼 명|표현
식)
-. 명시된 두 인자의 값을 비교하여 같으면 NULL값을 RETURN하고, 다를 경우엔 첫
번째 인자
값을 RETURN하는 함수이다.
-. SELECT e.emp_name, m.emp_name,
NULLIF (SUBSTR(e.emp_name,1,1),
SUBSTR(m.emp_name,1,1))
FROM emp e
JOIN emp m ON (e.manager_no = m.emp_no);
DECODE(컬럼 명|표현식, 조건1, 결
과1, 조건2, 결과2, …, 디폴트 값)
-. 데이터를 원하는 다른 값으로 출력해주는 함수이다.
-. 인자로 컬럼 또는 표현식으로 받은 후 값이 조건1에 해당하면 결과1이 출력되고 값이
조건2에 해당하면 결과2가 출력되는 식으로 사용되는 함수이다.
-. 만일 어느 조건에도 해당하지 않으면 디폴트 값을 사용한다.
NULLIF
DECODE
CASE
SELECT CASE
WHEN 조건1 THEN 결과1
ELSE 결과2
END [AS 컬럼 별칭]
-. 데이터를 원하는 다른 값으로 출력해주는 DECODE와 동일한 기능을 하는 함수이다.
-. SELECT
COUNT(CASE WHEN TO_CHAR(startdate,'MM') = '01' THEN
COUNT(*) END) AS "1월",
COUNT(CASE WHEN TO_CHAR(startdate,'MM') = '02' THEN
COUNT(*) END) AS "2월",
COUNT(CASE WHEN TO_CHAR(startdate,'MM') = '03' THEN
COUNT(*) END) AS "3월",
COUNT(CASE WHEN TO_CHAR(startdate,'MM') = '04' THEN
COUNT(*) END)
10 AS "4월",
Oracle 9i SQL 정리 자료집 1
단일 행 SUB QUERY
종
류
문
법
기본적인 서브
쿼리 문
SELECT 컬럼 명
FROM 테이블 명
WHERE 조건 비교 연산자 (
SELECT 컬럼 명
FROM 테이블 명
WHERE 조건);
서브 쿼리의 그
룹 함수 적용
SELECT 컬럼 명
FROM 테이블 명
WHERE 조건 비교 연산자 (
SELECT 그룹 함수(컬럼 명)
FROM 테이블 명
WHERE 조건);
HAVING절에서
의 서브 쿼리
SELECT 컬럼 명, 그룹함수(컬럼 명)
FROM 테이블 명
GROUP BY 컬럼 명
HAVING 그룹함수(컬럼 명) 비교 연
산자 ( SELECT 컬럼 명
FROM 테이블 명
WHERE 조건);
설
명
-. 서브 쿼리가 하나의 행을 RETURN하는 경우에 비교 연산자를 이용한다.
-. 여러 개의 행을 RETURN하는 경우에는 서브 쿼리에 대한 에러 메시지가 RETURN된
다.
-. 비교 연산자 설명
=
: 같다
<> : 같지 않다.
>
: 크다
>= : 크거나 같다
<
: 작다
<= : 작거나 같다
-. 서브 쿼리에서는 그룹 함수를 메인 쿼리처럼 사용할 수 있기 때문에 평균 값이나
합 등의
값을 구해 WHERE의 조건과 비교하는 쿼리를 가능하게 한다.
-. WHERE절에서는 그룹 함수를 쓸 수 없기 때문에 서브 쿼리를 이용하면 그 제약을
해결할
수 있다.
-. 상용법은 기본적인 서브 쿼리문과 비슷함.
11
Oracle 9i SQL 정리 자료집 1
복수 행 SUB QUERY
종
류
문
법
설
명
IN 연산자
SELECT 컬럼 명
FROM 테이블 명
WHERE 조건 IN (
SELECT 컬럼 명
FROM 테이블 명
WHERE 조건);
-. 복수 행의 서브 쿼리의 결과는 IN 연산자에 열거된 결과로 사용되어 비교된다.
ANY 연산자
SELECT 컬럼 명
FROM 테이블 명
WHERE 조건 ANY (
SELECT 컬럼 명
FROM 테이블 명
WHERE 조건);
-. 복수 행의 서브 쿼리의 각각의 결과는 조건과 비교되어 어느 하나라도 만족하는
범위의
것을 취한다.
-. ANY 연산자는 어떤 특정 값이 아닌 범위를 통해 비교 연산 처리를 한다.
-. 서브 쿼리에서 RETURN되는 값들 각각에 대해 수행된다.
-. 조건 > ANY(3000,7000,8000)이 되면, 조건은 3000,7000,8000의 값에서 어떠
한 한 값만을 만족 하면 되므로 조건의 값은 3000보다 크면 된다.
SELECT 컬럼 명
FROM 테이블 명
WHERE 조건 ALL (
SELECT 컬럼 명
FROM 테이블 명
WHERE 조건);
-. 복수 행의 서브 쿼리의 결과는 그 결과 중 조건을 모두 만족하는 범위의 것을 취
한다.
-. ALL 연산자는 어떤 특정 값이 아닌 범위를 통해 비교 연산 처리를 한다.
-. ANY 와는 달리 서브 쿼리에서 RETURN되는 값들 중 모두 만족하는 범위의 값만을
선택해 수행한다.
-. 조건 > ALL(3000,7000,8000)이 되면, 조건은 3000,7000,8000의 값보다 무조
건 커야 하므로
조건의 값은 8000보다 커야 한다.
ALL 연산자
12
Oracle 9i SQL 정리 자료집 1
복수 컬럼 SUB QUERY
종
류
RAIRWISE(쌍)
서브 쿼리
문
법
SELECT 컬럼 명
FROM 테이블 명
WHERE (컬럼 명1, 컬럼 명2, …) IN
(SELECT 컬럼 명1, 컬럼 명2, …
설
명
-. WHERE 조건의 서브 쿼리가 RETURN하는 컬럼을 서로 쌍으로 묶어 비교한다.
FROM 테이블 명
WHERE 조건);
NONRAIRWISE 서브
쿼리
IN-LINE VIEW
SELECT 컬럼 명
FROM 테이블 명
WHERE 컬럼 명1 IN
(SELECT 컬럼 명1
FROM 테이블 명
WHERE 조건)
AND 컬럼 명2 IN
(SELECT 컬럼 명2
FROM 테이블 명
WHERE 조건);
SELECT a.컬럼 명, b.컬럼 명
FROM 테이블 a,
(SELECT 컬럼 명
FROM 테이블 명
WHERE 조건) b
WHERE 조건;
-. 결과가 RAIRWISE의 것과 다를 수 있다.
이유: WHERE절에 IN 연산자를 두 개 사용할 경우 두 개의 서브 쿼리의 RETURN값의
순서가 다를 수 있기 때문에 RAIRWISE의 것과 다를 수 있다.
-. FROM절에 기술하는 서브 쿼리로 다른 물리적인 테이블과 함께 사용할 수 있다.
13
Oracle 9i SQL 정리 자료집 1
기타 SUB QUERY
종
류
상호 연관 서브
쿼리
WITH 절
문
법
설
명
SELECT 컬럼 명,컬럼 명
FROM 테이블 명 a
WHERE 조건 비교 연산자 (
SELECT 그룹함수(컬럼 명)
FROM 테이블 명
WHERE 컬럼 명 = a.컬럼 명);
-. 한 개의 행을 처리할 때마다 메인 쿼리로 RETURN하는 방식을 사용
-. 내부적으로 성능이 저하된다는 단점을 안고 있다.
-. 메인 쿼리의 컬럼이 서브 쿼리의 WHERE절에 비교되는 값으로 사용된다.
WITH
인 라인 뷰 명1 AS (SELECT 컬럼 명
FROM 테이블 명
WHERE 조건),
인 라인 뷰 명2 AS (SELECT 컬럼 명
FROM 테이블 명
WHERE 조건)
(SELECT 컬럼 명
FROM 인 라인 뷰 명1
WHERE 조건 비교 연산자
(SELECT 컬럼 명
FROM 인 라인 뷰 명2
WHERE 조건);
-. 여러 개의 서브 쿼리가 메인 쿼리에서 사용될 때 생기는 복잡성을 보다 간결하게
정의해 사용함으로써 가독성이 높고아울러 성능 저하 현상을 최소화할 수 있다.
-. WITH 절을 이용한 쿼리 작성 정리
1. 먼저 메인 쿼리에 정의될 서브 쿼리를 WITH절과 함께 선언한다.
2. 각각의 서브 쿼리를 대신할 인 라인 뷰의 이름을 정의 합니다.
3. 만일 어떤 서브 쿼리가 다른 서브 쿼리를 참조할 경우 순서가 매우 중요하다.
반드시 참조되는 인 라인 뷰가 먼저 선언되어 있어야 한다.
4. 서브 쿼리 선언 후 메인 쿼리를 작성 한다.
5. 메인 쿼리에서 WITH절에 미리 선언해 놓은 인 라인 뷰의 이름을 사용하여 서브
쿼리를 작성하여 사용한다.
-. WITH
dept_cost AS(SELECT dept_name,SUM(pay) AS dept_total
FROM emp
NATURAL JOIN dept
GROUP BY dept_name),
avg_cost AS(SELECT SUM(dept_total) / COUNT(*) AS dept_avg
FROM dept_cost)
SELECT *
FROM
dept_cost
WHERE dept_total >= (SELECT dept_avg
FROM
avg_cost)
ORDER BY dept_name;
14
Oracle 9i SQL 정리 자료집 1
DML 문장 종류
종
류
DML문장의정의
INSERT 명령어
문
법
DELETE명렬어
MERGE명령어
명
INSERT, UPDATE, DELETE
-. DML문장이 수행된 후 TCL문장이 수행 되어야
다.
INSERT INTO 테이블 명[(컬럼
명,…)]
VALUES(각 컬럼에 해당하는 값,…);
-. 테이블에 새로운 데이터를 삽입하기 위한 문장이다.
-. 문장에서 INSERT INTO절에 컬럼을 생략할 수 있으며, 컬럼을 생략할 때에는 반
드시
VALUES절에 테이블 상의 컬럼 순서와 동일하게 삽입할 값들을 열거해야 한다.
-. 문자와 날짜로 구성되는 값들은 반드시 ‘’로 묶어주어야 한다.
INSERT INTO 테이블 명 [(컬럼
명,…)]
SELECT 컬럼 명, …
FROM 테이블 명
[WHERE 조건]
UPDATE명령어
설
Transaction의 처리가 반영 된
-. 어느 한 테이블에 대한 서브 쿼리 결과를 다른 테이블의 행으로 삽입할 수 있다.
-. 서브 쿼리를 이용한 행의 추가는 VALUES절 대신 SELECT문장이 오게 된다.
-. INSERT절에 명시된 컬럼과 SELECT절에 열거된 컬럼 들은 그 수와 타입이 일치
해야
하며, 서브 쿼리가 RETURN행의 개수만큼 새로운 행이 추가된다.
UPDATE 테이블 명
SET 컬럼 명 = 변경할 값
[WHERE 조건];
-. 기존의 테이블에 존재하는 데이터를 갱신을 한다.
-. 한번에 한 컬럼씩 값을 변경할 수 있기 때문에 SET절에는 한 컬럼만 올 수 있다.
-. 여러 컬럼을 동시에 갱신하거나 다른 테이블의 값을 참조해서 갱신해야 한다면
서브
쿼리를 이용한 방식을 사용해야만 한다.
DELETE [FROM] 테이블 명
[WHERE 조건];
-. 기존의 테이블에 존재하는 데이터를 삭제하는 역할을 한다.
-. WHERE절에 삭제하고자 하는 행의 선택을 위한 조건을 기술한다.
-. 서브 쿼리를 이용하여 다른 테이블의 값을 참조하여 해당 케이블의 행을 선택하
여
삭제할 수 있다.
MERGE INTO 테이블 명 [별명]
USING {대상 테이블|뷰|} [별칭]
ON 조인 조건
WHEN MATCHED THEN
UPDATE SET 컬럼명 = 값,
…………….
-. 추가하는 데이터가 테이블에 존재하지 않을 때는 INSERT가 수행되고, 이미 데이
터가
존재할 경우에는 UPDATE가 수행된다.
-. MERGE문장 작성 순서
1. 새로운 행이 추가되거나 갱신이 될 테이블을 지정한다.
2. 다른 테이블을 참조하여 데이터를 비교한다면 대상 테이블 명을 별칭과 함께
정의한다.
3. 새로운 행을 추가하려고 하는 테이블과 대상 테이블 간에 조인이 필요하면
15
ON절을
Oracle 9i SQL 정리 자료집 1
다중 INSERT 문
종
류
문
법
INSERT {ALL|FIRST}
WHEN 조건1 THEN
INTO 테이블1 VALUES (컬럼, …)
다중 INSERT문
설명
WHEN 조건2 THEN
INTO 테이블2 VALUES (컬럼, …)
ELSE
INTO 테이블3 VALUES (컬럼, …)
설
명
-. 하나의 INSERT문에서 여러 테이블에 동시에 하나의 행을 입력할 때 사용.
-. ALL의 서브 쿼리에 의해 RETURN된 행들의 컬럼을 INSERT문의 수행 시 모두
참조하여 입력할 때 사용.
-. 만족하는 조건에 따라 수행되는 입력 데이터가 달라지며 조건 모두 만족하지
않았을 때는 ELSE 구문이 수행된다.
SELECT 서브 쿼리 문;
UNCONDITION
AL-INSERT문
INSERT ALL
INTO 테이블1 VALUES (컬럼, …)
INTO 테이블2 VALUES (컬럼, …)
-. 다중 INSERT문 실행 시 조건이 없는 경우를 의미한다.
SELECT 서브 쿼리 문;
CONDITIONAL
-INSERT문
CONDITIONAL
-FIRSTINSERT문
PIVOTINGINSERT문
INSERT ALL
…. 이하 다중 INSERT문법과 동일함.
-. 다중 INSERT문 실행 시 해당 조건을 만족하는 행만을 추가하는 경우에 사용한
다.
INSERT FIRST
…. 이하 다중 INSERT문법과 동일함.
-. 다중 INSERT문을 실행 시 서브 쿼리에 의해 RETURN된 데이터가 다중 INSERT
문에
정의된 첫 번째 조건을 만족하면 그 조건에 의해 데이터를 입력하고 그렇지 않
으면
다음에 정의된 조건에 의해 데이터를 입력하는데 사용한다.
-. CONDITIONAL-FIRST-INSERT문은 UNCONDITIONAL-INSERT문이나
CONDITIONAL-INSERT문과는 다르게 한 가지 조건을 만족하면 다른 조건에
대해 중복된 수행을 하지 않는다.
INSERT ALL
INTO 테이블1 VALUES (컬럼, …)
INTO 테이블1 VALUES (컬럼, …)
INTO 테이블1 VALUES (컬럼, …)
SELECT 서브 쿼리 문;
-. 비관계형 데이터베이스의 컬럼 들을 읽어서 관계형 데이터베이스 구조의 테이블
에
데이터를 일괄 입력할 때 사용한다.
-. 관계형 데이터베이스에서는 중복되는 컬럼을 하나의 공통 컬럼으로 만들게 되므
로
PIVOTING-INSERT문을 사용해 비관계형 데이터베이스 구조의 데이터를 관계형
16
타입으로 바꿔 사용할 수 있다.
Oracle 9i SQL 정리 자료집 1
TRANSACTION(TCL) 종류
종
류
문
법
설
명
TRANSACTION.
의 설명
SAVEPOINT 인수
ROLLBACK 또는 ROLLBACK TO 인수
COMMIT
-. TRANSACTION이란 각 사용자들의 행위를 하나의 단위로 구성한 것으로 사용자
들이
사용한 DML 문장을 뜻한다.
-. 데이터 조작은 COMMIT명령에 의해 영구적으로 반영되며, ROLLBACK명령에 의
해
TRANSACTION의 일부 또는 전체가 취소 될 수 있다.
-. DDL(Data Definition Language)이나 DCL(Data Control Language)는
명령 하나가 그
자체로 TRANSACTION이며 자동으로 COMMIT이 된다.
COMMIT
COMMIT;
-. TRANSACTION이 시작된 이후의 데이터베이스에 대한 변경 작업을 확정하여 영
구적인
변경을 가하는 기능을 수행.
ROLLBACK
ROLLBACK;
-. 사용자가 행했던 데이터 조작을 원래 상태로 돌리는 취소 작업이다.
SAVEPOINT 인수1
…..
SAVEPOINT
SAVEPOINT 인수2
…..
ROLLBACK TO 인수1;
…..
-. 여러
내에
-. 주로
취소
데이터 변경 작업이 일어난 TRANSACTION을 관리하기 위해 TRANSACTION
특정한 시점들을 알리기 위한 표지 기능을 수행한다.
ROLLBACK TO SAVEPOINT와 같은 명령을 이용해 전체 작업에 대한 변경
대신 특정 시점까지만 취소하는 용도로 사용된다.
COMMIT;
TRANSACTION
의 시작과 종료
-. TRANSACTION의 종료 시점
1. COMMIT명령이나 ROLLBACK명령을 만났을 때
2. DDL이나 DCL 중 한 문장이 실행되었을 때(AUTO COMMIT 됨)
3. SQL*PLUS가 정상 종료를 했을 때(AUTO COMMIT 됨)
4. SQL*PLUS가 비정상 종료를 했을 때(AUTO ROLLBACK 됨)
5. 시스템이 다운 되었을 때(AUTO ROLLBACK 됨)
-. 위에서 PL*SQL의 정상 종료는 exit로 접속을 종료 했을 때를 의미하며, 비
정상 종료는
17
Oracle 9i SQL 정리 자료집 1
DDL 문장 종류
종
류
문
법
CREATE TABLE [스키마 명.]테이블 명
(컬럼 명 데이터 타입 [DEFAULT 디
폴트 값], …);
CREATE
TABLE 명령어
CREATE TABLE [스키마 명.]테이블 명
[(컬럼 명, …)]
AS
서브 쿼리;
ALTER TABLE 테이블 명
ADD(컬럼 명 데이터 타입 [DEFAULT
디폴트 값], … );
또는
ALTER TABLE 테이블 명
ADD(컬럼 명 데이터 타입
CONSTRAINT 제약 조건 명 제약 조건
의 유효 값, …);
ALTER
TABLE 명령어
설
명
-. 테이블 명, 컬럼 명,데이터 타입, 크기 등을 기술한다.
-. 테이블 생성 시 테이블 명, 하나 이상의 컬럼 명, 데이터 타입은 반드시 기술
해야 한다.
-.기존 테이블에 존재하는 특정 컬럼과 행을 이용해 테이블을 생성할 경우에 사용
한다.
-. 서브 쿼리에 의해 만들어진 결과를 통해 테이블이 생성된다.
-. 컬럼 명을 명시할 경우 서브 쿼리에 열거된 컬럼의 수와 일치해야 한다.
-. 서브 쿼리에서 함수를 사용할 경우 컬럼 명을 기술 하거나, 서브 쿼리에
alias를 사용한다.
-. 테이블에 컬럼을 추가하거나 제약 조건의 추가 시 사용한다.
-. 컬럼 추가 시 테이블에 이미 행이 존재한다면 존재하는 행들의 추가된 컬럼 값
들은
모두 NULL값으로 처리된다.
-. 서브 쿼리를 이용해서 테이블 생성 시 NOT NULL 제약 조건을 제외한 나머지
제약
조건들은 계승되지 않는다.
-. ALTER TABLE manager_test
ADD(gender VARCHAR(1) CONSTRAINT pk_test_mgr_no CHECK(gender IN
('M','F')));
ALTER TABLE 테이블 명
MODIFY (컬럼 명 데이터 타입
[DEFAULT 디폴트 값], …);
-. 테이블의 기존 컬럼의 속성 변경 시 사용된다.
-. 데이터 타입, 크기, 디폴트 값 등을 변경한다.
-. Size를 MODIFY할 경우에는 테이블에 데이터가 없거나 NULL값만이 존재할 때
만
그 크기를 줄일 수 있다.
-. 컬럼의 이름을 변경할 수 없다.
-. 컬럼의 디폴트 값의 변경은 기존 데이터에 대해서 소급(적용)되지 않는다.
ALTER TABLE 테이블 명
DROP CONSTRAINT 제약 조건 명;
-. 테이블을 삭제하는 것이 아니라 테이블의 기존 컬럼을 삭제할 때 사용한다.
-. 삭제 하고자 하는 컬럼에 데이터가 있어도 삭제가 가능하다.
-. 마지막으로 남은 컬럼은 삭제 할 수 없다.
-. 테이블을 삭제하는 것이 아니라 테이블의 기존 제약 조건을 삭제할 때 사용한다.
18 제약 조건 명 없이도 삭제 가능하다.
-. 기본 키는
Oracle 9i SQL 정리 자료집 1
DDL 문장 종류(계속)
종
류
문
법
설
명
테이블, 데이터 타입, 인덱스 등의 데이터베이스 객체를 삭제할 때 쓰는 명령
이다.
테이블의 소유주나 DROP ANY TABLE 권한이 부여되어 있어야 테이블을 삭제할
있다.
DROP TABLE 테이블 명;
-.
어
-.
수
TRUNCATE
TABLE 명령어
TRUNCATE TABLE 테이블 명;
-. 테이블 구조는 남고 데이터만 제거하는 명령어이다.
-. DELETE명령과는 달리 자동으로 COMMIT이 수행된다.
-. 테이블 소유자나 DELETE TABLE 권한이 있는 사용자만이 사용할 수 있다.
RENAME
명령어
RENAME 기존의 객체 명 TO 새로운 객
체 명;
-. 데이터베이스 객체의 이름을 변경하고자 할 때 사용하는 명령어 이다.
-. 객체의 소유자만이 사용할 수 있다.
COMMENT ON {TABLE|COLUMN}
{테이블 명|테이블 명.객체명} IS ‘주석
내용’;
-. 테이블이나 컬럼에 대한 주석을 추가할 수 있다.
-. 자료 사전을 통해 조회할 수 있다.
-. COMMENT관련 VIEW 설명
1. ALL_COL_COMMENTS : 모든 접근 가능한 컬럼에 대한 주석문을 볼 수 있다.
2. ALL_TAB_COMMENTS : 모든 접근 가능한 테이블에 대한 주석문을 볼 수 있
다.
3. USER_COL_COMMENTS : 사용자 소유의 컬럼에 대한 주석문을 볼 수 있다.
4. USER_TAB_COMMENTS : 사용자 소유의 테이블에 대한 주석문을 볼 수 있다.
DROP
TABLE 명령어
COMMEMT
명렬어
19