Oracle 9i SQL 정리 자료집1 2003. 05.

Presentation on theme: "Oracle 9i SQL 정리 자료집1 2003. 05."— Presentation transcript:

1 Oracle 9i SQL 정리 자료집1

2 SQL 문장의 종류와 기능 종 류 문 법 설 명 쿼리 SELECT -. DATA를 검색하는 명령어 DML (Data
종 류 문 법 설 명 쿼리 SELECT -. DATA를 검색하는 명령어 DML (Data Manipulation Language) INSERT UPDATE DELETE -. 데이터에 행을 삽입, 변경, 제거하는 작업을 수행하는 명령어. DDL Definition CREATE ALTER DROP RENAME TRUNCATE COMMENT -. 데이터의 구조를 생성,변경,제거 등의 작업을 수행하는 명령어. TCL (Transaction Control COMMIT ROLLBACK SAVEPOINT -. DML에 의해 변경된 일련의 TRANSACTION을 관리하는 명령어. DCL GRANT REVOKE -. 사용자에게 구조에 대한 접근 권한을 부여하고 제거하는 명령어.

3 JOIN 종 류 문 법 설 명 CROSS JOIN SELECT 테이블1.컬럼, 테이블2.컬럼 FROM 테이블1
종 류 문 법 설 명 CROSS JOIN SELECT 테이블1.컬럼, 테이블2.컬럼 FROM 테이블1 CROSS JOIN 테이블2 -. 기존의 CARTESIAN JOIN 기법의 명칭. -. 2개 이상의 모든 데이터를 참조해야 하는 경우에 사용. -. 거의 사용하지 않는다. NATURAL JOIN NATURAL JOIN 태이블2 -. 기존 EQUI JOIN 기법의 명칭 -. 두 개 이상의 테이블이 공통되는 컬럼에 의해 논리적으로 연결되는 조인 기법. -. SELECT 절에서 공통되는 컬럼에 테이블 명을 생략해야 한다.(테이블2.컬럼->컬럼) USING JOIN JOIN 테이블2 USING (컬럼) -. 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 RIGHT OUTER JOIN 테이블2 ON (테이블1.컬럼 = 테이블2.컬럼) -. 기존 데이터가 존재하지 않는 쪽 테이블에 (+)의 기법의 명칭. -. 데이터가 존재하지 않는 쪽에 (+) 기호를 둔 것과 같은 결과를 만든다. -. 문법에서 테이블2(+) 와 같은 의미 LEFT OUTER JOIN LEFT OUTER JOIN 테이블2 -. 데이터가 존재하는 쪽에 (+) 기호를 둔 것과 같은 결과를 만든다. -. 문법에서 테이블1(+)와 같은 의미 FULL OUTER JOIN FULL OUTER JOIN 테이블2 -. 기존 양쪽 테이블에 (+)의 기법의 명칭. -. 양쪽 테이블 모두 서로 데이터가 존재하지 않는 것이 존재 할 때 사용. -. 문법에서 테이블1(+), 테이블2(+)와 같은 의미.

4 단일 행 FUNCTION 종 류 문 법 설 명 ROUND ROUND(컬럼 명|표현식|상수 값, n)
종 류 문 법 설 명 ROUND ROUND(컬럼 명|표현식|상수 값, n) -. 인자로 받은 수를 명시된 (자리 수-1)에서 반올림 하는 함수. -. n이 양수 : 소수점의 오른쪽 (자리 수 – 1)에서 반올림. -. n이 음수 : 소수점의 왼쪽 자리 수에서 반올림. -. n이 생략 : 소수점 첫 번째 자리에서 반올림. TRUNC TRUNC(컬럼 명|표현식|상수 값, n) -. 인자로 받은 수를 명시된 (자리 수 – 1) 에서 버린 후의 값을 RETURN 하는 함수. -. n이 양수 : 소수점의 오른쪽 (자리 수 – 1)에서 버림. -. n이 음수 : 소수점의 왼쪽 자리 수에서 버림. -. n이 생략 : 소수점 첫 번째 자리에서 버림. CEIL CEIL(컬럼 명|표현식|상수 값) -. 인자의 값에서 첫번째 소수점의 값을 올린 후 값을 RETURN하는 함수. -. 항상 결과는 정수 값이다. FLOOR FLOOR(컬럼 명|표현식|상수 값) -. 인자의 값에서 첫번째 소수점의 값을 버린 후 값을 RETURN하는 함수. MOD MOD(m, n) -. m을 n으로 나눈 후 나머지 값을 RETURN하는 함수. ABS ABS(m) -. 절대값을 RETURN하는 함수. -. 항상 결과는 양의 값이다. SIGN SIGN(m) -. 부호를 숫자 값으로 RETURN하는 함수. -. m이 양수인 경우 RETURN 값 : 1 -. m이 음수인 경우 RETURN 값 : -1 -. m이 0인 경우 RETURN 값 : 0

5 복수 행(문자열) FUNCTION 종 류 문 법 설 명 UPPER UPPER(문자열)
종 류 문 법 설 명 UPPER UPPER(문자열) -. 문자열을 대문자로 바꾸어 RETURN하는 함수. LOWER LOWER(문자열) -. 문자열을 소문자로 바꾸어 RETURN하는 함수. INITCAP INITCAP(문자열) -. 문자열에서 띄어쓰기를 구분하여 첫 문자만 대문자로 바꾸어 RETURN하는 함수. CONCAT CONCAT(문자열1,문자열2) -. 문자열1과 문자열2를 연결하여 값을 RETURN하는 함수. LENGTH LENGTH(문자열) -. 문자열의 길이 값을 RETURN하는 함수 SUBSTR SUBSTR(문자열, m, n) -. 문자열의 m번째 위치부터 n개수 만큼의 문자를 잘라 RETURN하는 함수. -. LIKE문장은 ORACLE 내부에서 SUBSTR를 호출하여 사용한다. -. m이 1인 경우 : 문자열의 첫 번째 문자를 가리킨다. -. m이 음수인 경우 : 문자열의 뒤쪽에서부터 m번째 문자를 가리킨다. -. N의 의미 : m의 위치부터 문자의 개수를 의미한다. -. n이 생략된 경우 : m의 위치부터 문자열의 끝까지의 문자를 처리한다. INSTR INSTR(문자열1, 문자열2) -. 문자열2의 문자열을 문자열1에서 찾아 그 위치의 정수 값을 RETURN하는 함수. -. 문자열2의 문자열이 문자열1에서 찾지 못했을 경우 0을 RETURN한다. LPAD LPAD(문자열1, n, 문자열2) -. 문자열1이 n자리 보다 작은 경우 왼쪽에서부터 문자열2의 패턴으로 n자리 수를 맞춰 RETURN하는 함수. -. n의 값이 문자열1의 길이보다 작은 경우 문자열1의 왼쪽에서부터 n수 만큼 RETURN 된다. RPAD RPAD(문자열1, n, 문자열2) -. 문자열1이 n자리 보다 작은 경우 오른쪽에서부터 문자열2의 패턴으로 n자리 수를 LTRIM LTRIM(문자열, 문자) -. 특정문자와 일치하는 문자를 문자열의 왼쪽으로부터 제거한 값을 RETURN하는 함수. RTRIM RTRIM(문자열, 문자) -. 특정문자와 일치하는 문자를 문자열의 오른쪽으로부터 제거한 값을 TRIM TRIM(문자 FROM 문자열) -. 특정문자와 일치하는 문자를 문자열의 왼쪽이나 오른쪽으로부터 제거한 값을

6 복수 행(날짜) FUNCTION 종 류 문 법 설 명 SYSDATE -. 현재의 날짜를 RETURN하는 함수.
종 류 문 법 설 명 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 MONTHS_BETWEEN(날짜1, 날짜2) -. 날짜1 인자에서 날짜2 인자 값을 뺀 숫자 값을 RETURN하는 함수. -. RETURN값이 날짜가 아니라 소수점 숫자로 RETURN 된다. -. SELECT MONTHS_BETWEEN(sysdate+40,sysdate) FROM DUAL; 결과 : -. SELECT MONTHS_BETWEEN(sysdate+31,sysdate) FROM DUAL; 결과 : 1 ROUND ROUND(날짜, 형식기준) -. 인자로 받은 날짜를 명시된 기준에 의해 반올림한 값을 RETURN하는 함수. -. 월의 기준 : 16일을 기준으로 반올림. 년의 기준 : 7월을 기준으로 반올림. -. SELECT TO_CHAR(SYSDATE, YYYY-MM-DD), ROUND(SYSDATE,'MONTH'), ROUND(SYSDATE,'YEAR') FROM DUAL; 결과 : , , TRUNC TRUNC(날짜, 형식기준) -. 인자로 받은 날짜를 명시된 기준에 의해 버림 한 값을 RETURN하는 함수. -. SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD') ,TRUNC(SYSDATE,'MONTH'), TRUNC(SYSDATE,'YEAR') 결과 : , ,

7 복수 행(변환) FUNCTION 종 류 문 법 설 명 TO_CHAR TO_CHAR(날짜|숫자, 변환형식)
종 류 문 법 설 명 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시간제로 표시한다. HH : 시간을 24시간제로 표시한다. MI : 분을 표시한다. SS : 초를 표시한다. -. 변환 형식(정수,금액) 설명 9 : 자릿수를 표시한다. 빈 자리는 표시하지 않는다. 0 : 자릿수를 표시한다. $ : ‘$’를 표시한다. L : 각 지역별 통화기호를 표시한다. . : 소수점을 표시한다. , : 천의 자리를 구분하는 기호를 표시한다. TO_DATE TO_DATE(날짜, 변환형식) -. 인자로 받은 날짜 형태의 문자열과 명시된 날짜 형식을 이용해 날짜 값을 RETURN하는 함수. TO_NUMBER TO_NUMBER(문자열, 변환형식) -. 인자로 받은 문자열을 변환 형식을 이용해 숫자 값을 RETURN하는 함수.

8 복수 행(그룹1) FUNCTION 종 류 문 법 설 명 COUNT COUNT(컬럼 | 표현식 | *)
종 류 문 법 설 명 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 RANK(상수 값) WITH GROUP (ORDER BY 켤럼 명 [DESC | ASC] [NULLS {FIRST | LAST}]) -. 값의 집합에서 순위를 계산하여 RETURN하는 함수. -. 그룹 함수의 기능을 한다. -. 인자를 이용해 전체 행으로부터의 순서를 계산한다. -. 인자는 반드시 상수가 되어야 하며, ORDER BY절에서 명시한 컬럼과 같은 데이터 타입 이어야 한다. RANK() OVER (ORDER BY 컬럼 명 [DESC | ASC] [NULLS {FIRST | LAST}]) -. 분석 함수의 기능을 한다. -. 전체 행을 대상으로 각각의 행에 대한 순위를 계산하여 결과를 RETURN 한다. GROUP BY절 SELECT 컬럼 명, 그룹함수(컬럼 명) FROM 테이블 명 [WHERE 조건] GROUP BY 그룹 하고자 하는 컬럼 명 [ORDER BY {컬럼 명|SELECT 구에 열거된 컬럼의 순서|컬럼의 별칭}] -. GROUP BY절은 행을 GROUP화 한다. -. GROUP BY절에서는 SELECT절에 열거된 컬럼의 순서나 컬럼의 별칭을 사용할 수 없음. -. Default로 행은 오름차순으로 정렬이 되며, 그룹 함수가 아닌 어떠한 컬럼 이나 표현식도 GROUP BY절에 나타나야 한다. -. 컬럼에 NULL은 무시된다.

9 복수 행(그룹2) FUNCTION 종 류 문 법 설 명 HAVING 절 SELECT 컬럼 명, 그룹함수(컬럼 명)
종 류 문 법 설 명 HAVING 절 SELECT 컬럼 명, 그룹함수(컬럼 명) FROM 테이블 명 [WHERE 조건] GROUP BY 그룹 하고자 하는 컬럼 명 HAVING 그룹 조건 [ORDER BY {컬럼 명|SELECT 구에 열거된 컬럼의 순서|컬럼의 별칭}] -. 그룹화 된 결과에 제약을 가하는 방법 -. GROUP BY절 다음에 기술한다. -. HAVING절에는 그룹함수나 GROUP BY절에 사용한 것만이 올 수 있다. -. SELECT dept_no, AVG(pay) FROM emp GROUP BY dept_no HAVING AVG(pay) >= 5000; ROLLUP SELECT 컬럼 명, 그룹 함수 GROUP BY ROLLUP(그룹 하고자 하는 컬럼 명, …) -. 주로 통계 데이터를 나타내는 합산형 형태의 보고서를 작성 할 때 사용한다. -. 일반적으로 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); CUBE GROUP BY CUBE(그룹 하고자 하는 컬럼 명, …) -. ROLLUP 함수와 마찬가지로 주로 통계 데이터를 나타내는 합산형 형태의 보고서에 사용. -. 일반적 GROUPING 함수와 같이 사용되어 먼저 선행 그룹에 대한 출력이 바뀔 때마다 그 선행 그룹별 집계를 출력하며 이 후 후행 그룹별 집계를 출력하는 형의 보고서에 사용. GROUP BY CUBE(dept_no,job_no) ORDER BY dept_no; GROUPING SETS GROUP BY GROUPING SETS(그룹 하고자 하는 컬럼 명, …) -. 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 구문이 간단해지며 성능 또한 빠른 장점을 가지고 있다.

10 복수 행(기타) FUNCTION 종 류 문 법 설 명 NVL NVL(컬럼 명|표현식, 대체할 값)
종 류 문 법 설 명 NVL NVL(컬럼 명|표현식, 대체할 값) -. 첫번째 인자(컬럼 명 또는 표현식)가 NULL인 경우 두 번째 인자(대체할 값)로 바꾸어 RETURN하는 함수. -. NULL은 어떠한 값과 연산을 해도 그 결과는 NULL이 됨을 유의한다. -. 대체할 값은 해당 컬럼의 데이터 타입과 동일해야 한다. NVL2 NVL2(컬럼 명|표현식, NULL 값이 아닐 때의 대체할 값, NULL값일 때의 대체할 값) -. 첫번째 인자(컬럼 명 또는 표현식)가 NULL이 아닌 경우 두 번째 인자의 값으로 대체하고, NULL인 경우에는 세 번째 인자의 값으로 대체한다. -. 대체할 값들은 해당 컬럼의 데이터 타입과 동일해야 한다. NULLIF 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 DECODE(컬럼 명|표현식, 조건1, 결과1, 조건2, 결과2, …, 디폴트 값) -. 데이터를 원하는 다른 값으로 출력해주는 함수이다. -. 인자로 컬럼 또는 표현식으로 받은 후 값이 조건1에 해당하면 결과1이 출력되고 값이 조건2에 해당하면 결과2가 출력되는 식으로 사용되는 함수이다. -. 만일 어느 조건에도 해당하지 않으면 디폴트 값을 사용한다. CASE SELECT CASE WHEN 조건1 THEN 결과1 ELSE 결과2 END [AS 컬럼 별칭] FROM 테이블 명 -. 데이터를 원하는 다른 값으로 출력해주는 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) AS "4월", COUNT(CASE WHEN TO_CHAR(startdate,'MM') = '05' THEN COUNT(*) END) AS "5월", COUNT(CASE WHEN TO_CHAR(startdate,'MM') = '06' THEN COUNT(*) END) AS "6월" FROM emp GROUP BY startdate;

11 단일 행 SUB QUERY 종 류 문 법 설 명 -. 서브 쿼리가 하나의 행을 RETURN하는 경우에 비교 연산자를 이용한다.
종 류 문 법 설 명 기본적인 서브 쿼리 문 SELECT 컬럼 명 FROM 테이블 명 WHERE 조건 비교 연산자 ( FROM 테이블 명 WHERE 조건); -. 서브 쿼리가 하나의 행을 RETURN하는 경우에 비교 연산자를 이용한다. -. 여러 개의 행을 RETURN하는 경우에는 서브 쿼리에 대한 에러 메시지가 RETURN된다. -. 비교 연산자 설명 = : 같다 <> : 같지 않다. > : 크다 >= : 크거나 같다 < : 작다 <= : 작거나 같다 서브 쿼리의 그룹 함수 적용 SELECT 그룹 함수(컬럼 명) -. 서브 쿼리에서는 그룹 함수를 메인 쿼리처럼 사용할 수 있기 때문에 평균 값이나 합 등의 값을 구해 WHERE의 조건과 비교하는 쿼리를 가능하게 한다. -. WHERE절에서는 그룹 함수를 쓸 수 없기 때문에 서브 쿼리를 이용하면 그 제약을 해결할 수 있다. HAVING절에서의 서브 쿼리 SELECT 컬럼 명, 그룹함수(컬럼 명) GROUP BY 컬럼 명 HAVING 그룹함수(컬럼 명) 비교 연산자 ( SELECT 컬럼 명 -. 상용법은 기본적인 서브 쿼리문과 비슷함.

12 복수 행 SUB QUERY 종 류 문 법 설 명 IN 연산자 SELECT 컬럼 명 FROM 테이블 명 WHERE 조건 IN (
종 류 문 법 설 명 IN 연산자 SELECT 컬럼 명 FROM 테이블 명 WHERE 조건 IN ( FROM 테이블 명 WHERE 조건); -. 복수 행의 서브 쿼리의 결과는 IN 연산자에 열거된 결과로 사용되어 비교된다. ANY 연산자 WHERE 조건 ANY ( -. 복수 행의 서브 쿼리의 각각의 결과는 조건과 비교되어 어느 하나라도 만족하는 범위의 것을 취한다. -. ANY 연산자는 어떤 특정 값이 아닌 범위를 통해 비교 연산 처리를 한다. -. 서브 쿼리에서 RETURN되는 값들 각각에 대해 수행된다. -. 조건 > ANY(3000,7000,8000)이 되면, 조건은 3000,7000,8000의 값에서 어떠한 한 값만을 만족 하면 되므로 조건의 값은 3000보다 크면 된다. ALL 연산자 WHERE 조건 ALL ( -. 복수 행의 서브 쿼리의 결과는 그 결과 중 조건을 모두 만족하는 범위의 것을 취한다. -. ALL 연산자는 어떤 특정 값이 아닌 범위를 통해 비교 연산 처리를 한다. -. ANY 와는 달리 서브 쿼리에서 RETURN되는 값들 중 모두 만족하는 범위의 값만을 선택해 수행한다. -. 조건 > ALL(3000,7000,8000)이 되면, 조건은 3000,7000,8000의 값보다 무조건 커야 하므로 조건의 값은 8000보다 커야 한다.

13 복수 컬럼 SUB QUERY 종 류 문 법 설 명 RAIRWISE(쌍) 서브 쿼리 SELECT 컬럼 명 FROM 테이블 명
종 류 문 법 설 명 RAIRWISE(쌍) 서브 쿼리 SELECT 컬럼 명 FROM 테이블 명 WHERE (컬럼 명1, 컬럼 명2, …) IN (SELECT 컬럼 명1, 컬럼 명2, … FROM 테이블 명 WHERE 조건); -. WHERE 조건의 서브 쿼리가 RETURN하는 컬럼을 서로 쌍으로 묶어 비교한다. NON-RAIRWISE 서브 쿼리 WHERE 컬럼 명1 IN (SELECT 컬럼 명1 WHERE 조건) AND 컬럼 명2 IN (SELECT 컬럼 명2 -. 결과가 RAIRWISE의 것과 다를 수 있다. 이유: WHERE절에 IN 연산자를 두 개 사용할 경우 두 개의 서브 쿼리의 RETURN값의 순서가 다를 수 있기 때문에 RAIRWISE의 것과 다를 수 있다. IN-LINE VIEW SELECT a.컬럼 명, b.컬럼 명 FROM 테이블 a, (SELECT 컬럼 명 FROM 테이블 명 WHERE 조건) b WHERE 조건; -. FROM절에 기술하는 서브 쿼리로 다른 물리적인 테이블과 함께 사용할 수 있다.

14 기타 SUB QUERY 종 류 문 법 설 명 SELECT 컬럼 명,컬럼 명 FROM 테이블 명 a
종 류 문 법 설 명 상호 연관 서브 쿼리 SELECT 컬럼 명,컬럼 명 FROM 테이블 명 a WHERE 조건 비교 연산자 ( SELECT 그룹함수(컬럼 명) FROM 테이블 명 WHERE 컬럼 명 = a.컬럼 명); -. 한 개의 행을 처리할 때마다 메인 쿼리로 RETURN하는 방식을 사용 -. 내부적으로 성능이 저하된다는 단점을 안고 있다. -. 메인 쿼리의 컬럼이 서브 쿼리의 WHERE절에 비교되는 값으로 사용된다. WITH 절 WITH 인 라인 뷰 명1 AS (SELECT 컬럼 명 WHERE 조건), 인 라인 뷰 명2 AS (SELECT 컬럼 명 WHERE 조건) (SELECT 컬럼 명 FROM 인 라인 뷰 명1 WHERE 조건 비교 연산자 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;

15 DML 문장 종류 종 류 문 법 설 명 DML문장의정의 INSERT, UPDATE, DELETE
종 류 문 법 설 명 DML문장의정의 INSERT, UPDATE, DELETE -. DML문장이 수행된 후 TCL문장이 수행 되어야 Transaction의 처리가 반영 된다. INSERT 명령어 INSERT INTO 테이블 명[(컬럼 명,…)] VALUES(각 컬럼에 해당하는 값,…); -. 테이블에 새로운 데이터를 삽입하기 위한 문장이다. -. 문장에서 INSERT INTO절에 컬럼을 생략할 수 있으며, 컬럼을 생략할 때에는 반드시 VALUES절에 테이블 상의 컬럼 순서와 동일하게 삽입할 값들을 열거해야 한다. -. 문자와 날짜로 구성되는 값들은 반드시 ‘’로 묶어주어야 한다. INSERT INTO 테이블 명 [(컬럼 명,…)] SELECT 컬럼 명, … FROM 테이블 명 [WHERE 조건] -. 어느 한 테이블에 대한 서브 쿼리 결과를 다른 테이블의 행으로 삽입할 수 있다. -. 서브 쿼리를 이용한 행의 추가는 VALUES절 대신 SELECT문장이 오게 된다. -. INSERT절에 명시된 컬럼과 SELECT절에 열거된 컬럼 들은 그 수와 타입이 일치해야 하며, 서브 쿼리가 RETURN행의 개수만큼 새로운 행이 추가된다. UPDATE명령어 UPDATE 테이블 명 SET 컬럼 명 = 변경할 값 [WHERE 조건]; -. 기존의 테이블에 존재하는 데이터를 갱신을 한다. -. 한번에 한 컬럼씩 값을 변경할 수 있기 때문에 SET절에는 한 컬럼만 올 수 있다. -. 여러 컬럼을 동시에 갱신하거나 다른 테이블의 값을 참조해서 갱신해야 한다면 서브 쿼리를 이용한 방식을 사용해야만 한다. DELETE명렬어 DELETE [FROM] 테이블 명 -. 기존의 테이블에 존재하는 데이터를 삭제하는 역할을 한다. -. WHERE절에 삭제하고자 하는 행의 선택을 위한 조건을 기술한다. -. 서브 쿼리를 이용하여 다른 테이블의 값을 참조하여 해당 케이블의 행을 선택하여 삭제할 수 있다. MERGE명령어 MERGE INTO 테이블 명 [별명] USING {대상 테이블|뷰|} [별칭] ON 조인 조건 WHEN MATCHED THEN UPDATE SET 컬럼명 = 값, ……………. WHEN NOT MATCHED THEN INSERT [(컬럼 명, …)] VALUES (값, …); -. 추가하는 데이터가 테이블에 존재하지 않을 때는 INSERT가 수행되고, 이미 데이터가 존재할 경우에는 UPDATE가 수행된다. -. MERGE문장 작성 순서 1. 새로운 행이 추가되거나 갱신이 될 테이블을 지정한다. 2. 다른 테이블을 참조하여 데이터를 비교한다면 대상 테이블 명을 별칭과 함께 정의한다. 3. 새로운 행을 추가하려고 하는 테이블과 대상 테이블 간에 조인이 필요하면 ON절을 활용 한다. 4. 조인 조건에 의해 만족하는 데이터가 있을 경우엔 UPDATE가 수행되고 만족하는 데이터가 존재하지 않으면 INSERT문이 수행된다.

16 다중 INSERT 문 종 류 문 법 설 명 다중 INSERT문 설명 INSERT {ALL|FIRST} WHEN 조건1 THEN
종 류 문 법 설 명 다중 INSERT문 설명 INSERT {ALL|FIRST} WHEN 조건1 THEN INTO 테이블1 VALUES (컬럼, …) WHEN 조건2 THEN INTO 테이블2 VALUES (컬럼, …) ELSE INTO 테이블3 VALUES (컬럼, …) SELECT 서브 쿼리 문; -. 하나의 INSERT문에서 여러 테이블에 동시에 하나의 행을 입력할 때 사용. -. ALL의 서브 쿼리에 의해 RETURN된 행들의 컬럼을 INSERT문의 수행 시 모두 참조하여 입력할 때 사용. -. 만족하는 조건에 따라 수행되는 입력 데이터가 달라지며 조건 모두 만족하지 않았을 때는 ELSE 구문이 수행된다. UNCONDITIONAL-INSERT문 INSERT ALL -. 다중 INSERT문 실행 시 조건이 없는 경우를 의미한다. CONDITIONAL-INSERT문 …. 이하 다중 INSERT문법과 동일함. -. 다중 INSERT문 실행 시 해당 조건을 만족하는 행만을 추가하는 경우에 사용한다. CONDITIONAL-FIRST-INSERT문 INSERT FIRST -. 다중 INSERT문을 실행 시 서브 쿼리에 의해 RETURN된 데이터가 다중 INSERT문에 정의된 첫 번째 조건을 만족하면 그 조건에 의해 데이터를 입력하고 그렇지 않으면 다음에 정의된 조건에 의해 데이터를 입력하는데 사용한다. -. CONDITIONAL-FIRST-INSERT문은 UNCONDITIONAL-INSERT문이나 CONDITIONAL-INSERT문과는 다르게 한 가지 조건을 만족하면 다른 조건에 대해 중복된 수행을 하지 않는다. PIVOTING-INSERT문 -. 비관계형 데이터베이스의 컬럼 들을 읽어서 관계형 데이터베이스 구조의 테이블에 데이터를 일괄 입력할 때 사용한다. -. 관계형 데이터베이스에서는 중복되는 컬럼을 하나의 공통 컬럼으로 만들게 되므로 PIVOTING-INSERT문을 사용해 비관계형 데이터베이스 구조의 데이터를 관계형 타입으로 바꿔 사용할 수 있다.

17 TRANSACTION(TCL) 종류 종 류 문 법 설 명 TRANSACTION.의 설명 SAVEPOINT 인수
종 류 문 법 설 명 TRANSACTION.의 설명 SAVEPOINT 인수 ROLLBACK 또는 ROLLBACK TO 인수 COMMIT -. TRANSACTION이란 각 사용자들의 행위를 하나의 단위로 구성한 것으로 사용자들이 사용한 DML 문장을 뜻한다. -. 데이터 조작은 COMMIT명령에 의해 영구적으로 반영되며, ROLLBACK명령에 의해 TRANSACTION의 일부 또는 전체가 취소 될 수 있다. -. DDL(Data Definition Language)이나 DCL(Data Control Language)는 명령 하나가 그 자체로 TRANSACTION이며 자동으로 COMMIT이 된다. COMMIT; -. TRANSACTION이 시작된 이후의 데이터베이스에 대한 변경 작업을 확정하여 영구적인 변경을 가하는 기능을 수행. ROLLBACK ROLLBACK; -. 사용자가 행했던 데이터 조작을 원래 상태로 돌리는 취소 작업이다. SAVEPOINT SAVEPOINT 인수1 ….. SAVEPOINT 인수2 ROLLBACK TO 인수1; -. 여러 데이터 변경 작업이 일어난 TRANSACTION을 관리하기 위해 TRANSACTION 내에 특정한 시점들을 알리기 위한 표지 기능을 수행한다. -. 주로 ROLLBACK TO SAVEPOINT와 같은 명령을 이용해 전체 작업에 대한 변경 취소 대신 특정 시점까지만 취소하는 용도로 사용된다. 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로 접속을 종료 했을 때를 의미하며, 비 정상 종료는 강제 종료를 의미함.

18 DDL 문장 종류 종 류 문 법 설 명 CREATE TABLE 명령어 CREATE TABLE [스키마 명.]테이블 명
종 류 문 법 설 명 CREATE TABLE 명령어 CREATE TABLE [스키마 명.]테이블 명 (컬럼 명 데이터 타입 [DEFAULT 디폴트 값], …); -. 테이블 명, 컬럼 명,데이터 타입, 크기 등을 기술한다. -. 테이블 생성 시 테이블 명, 하나 이상의 컬럼 명, 데이터 타입은 반드시 기술해야 한다. [(컬럼 명, …)] AS 서브 쿼리; -.기존 테이블에 존재하는 특정 컬럼과 행을 이용해 테이블을 생성할 경우에 사용한다. -. 서브 쿼리에 의해 만들어진 결과를 통해 테이블이 생성된다. -. 컬럼 명을 명시할 경우 서브 쿼리에 열거된 컬럼의 수와 일치해야 한다. -. 서브 쿼리에서 함수를 사용할 경우 컬럼 명을 기술 하거나, 서브 쿼리에 alias를 사용한다. ALTER ALTER TABLE 테이블 명 ADD(컬럼 명 데이터 타입 [DEFAULT 디폴트 값], … ); 또는 ADD(컬럼 명 데이터 타입 CONSTRAINT 제약 조건 명 제약 조건의 유효 값, …); -. 테이블에 컬럼을 추가하거나 제약 조건의 추가 시 사용한다. -. 컬럼 추가 시 테이블에 이미 행이 존재한다면 존재하는 행들의 추가된 컬럼 값들은 모두 NULL값으로 처리된다. -. 서브 쿼리를 이용해서 테이블 생성 시 NOT NULL 제약 조건을 제외한 나머지 제약 조건들은 계승되지 않는다. -. ALTER TABLE manager_test ADD(gender VARCHAR(1) CONSTRAINT pk_test_mgr_no CHECK(gender IN ('M','F'))); MODIFY (컬럼 명 데이터 타입 [DEFAULT 디폴트 값], …); -. 테이블의 기존 컬럼의 속성 변경 시 사용된다. -. 데이터 타입, 크기, 디폴트 값 등을 변경한다. -. Size를 MODIFY할 경우에는 테이블에 데이터가 없거나 NULL값만이 존재할 때만 그 크기를 줄일 수 있다. -. 컬럼의 이름을 변경할 수 없다. -. 컬럼의 디폴트 값의 변경은 기존 데이터에 대해서 소급(적용)되지 않는다. DROP CONSTRAINT 제약 조건 명; -. 테이블을 삭제하는 것이 아니라 테이블의 기존 컬럼을 삭제할 때 사용한다. -. 삭제 하고자 하는 컬럼에 데이터가 있어도 삭제가 가능하다. -. 마지막으로 남은 컬럼은 삭제 할 수 없다. DROP COLUMN 컬럼 명; -. 테이블을 삭제하는 것이 아니라 테이블의 기존 제약 조건을 삭제할 때 사용한다. -. 기본 키는 제약 조건 명 없이도 삭제 가능하다. -. DROP PRIMARY KEY CASCADE구문을 사용하면 기본 키를 참조하는 자식 테이블이 존재할 경우에도 기본 키를 삭제할 수 있다.

19 DDL 문장 종류(계속) 종 류 문 법 설 명 DROP TABLE 명령어 DROP TABLE 테이블 명;
종 류 문 법 설 명 DROP TABLE 명령어 DROP TABLE 테이블 명; -. 테이블, 데이터 타입, 인덱스 등의 데이터베이스 객체를 삭제할 때 쓰는 명령어 이다. -. 테이블의 소유주나 DROP ANY TABLE 권한이 부여되어 있어야 테이블을 삭제할 수 있다. TRUNCATE TABLE 명령어 TRUNCATE TABLE 테이블 명; -. 테이블 구조는 남고 데이터만 제거하는 명령어이다. -. DELETE명령과는 달리 자동으로 COMMIT이 수행된다. -. 테이블 소유자나 DELETE TABLE 권한이 있는 사용자만이 사용할 수 있다. RENAME 명령어 RENAME 기존의 객체 명 TO 새로운 객체 명; -. 데이터베이스 객체의 이름을 변경하고자 할 때 사용하는 명령어 이다. -. 객체의 소유자만이 사용할 수 있다. COMMEMT 명렬어 COMMENT ON {TABLE|COLUMN} {테이블 명|테이블 명.객체명} IS ‘주석 내용’; -. 테이블이나 컬럼에 대한 주석을 추가할 수 있다. -. 자료 사전을 통해 조회할 수 있다. -. COMMENT관련 VIEW 설명 1. ALL_COL_COMMENTS : 모든 접근 가능한 컬럼에 대한 주석문을 볼 수 있다. 2. ALL_TAB_COMMENTS : 모든 접근 가능한 테이블에 대한 주석문을 볼 수 있다. 3. USER_COL_COMMENTS : 사용자 소유의 컬럼에 대한 주석문을 볼 수 있다. 4. USER_TAB_COMMENTS : 사용자 소유의 테이블에 대한 주석문을 볼 수 있다.