3일동안 배운 내용을 올립니다.
[ Part1 - 오라클 설치 및 접근법 ]
1. 오라클 설치 (10g)
(1) 디렉토리 이름(경로)이 한글이면X
1) oracle 설치파일이 존재하는 경로에 한글X
2) oracle 설치경로에 한글X
(2) 디렉토리에 공백이 있으면 X
1) oracle 설치파일이 존재하는 경로에 공백X
2) oracle 설치경로에 공백X
(3) 설치시에 에러 해결 방법
1) OS부터 밀고 다시 설치
2) oracle를 다시 설치
<1> 제어판 -> 프로그램 추가제거 -> 관련 프로그램 제거
<2> 관리도구 -> 서비스 -> 오라클 관련 서비스 정지
<3> oracle설치 디렉토리를 삭제
( 만약, 삭제가 안되면 '안전모드'에서 삭제 )
<4> 시작 -> 모든프로그램 -> oracle 관련 메뉴 삭제
<5> 실행 -> regedit 에서 oracle 관련 파일을 모두 제거
<6> 다시 설치
2. 오라클 접근법
(1) sqlplus 이용법 ( local관리 )
1)oracle Application 이용법
( oracle -> 응용프로그램개발 -> SQL Plus )
2)도스창 이용법
( 실행 -> sqlplus scott/tiger )
(2) isqlplus 이용법 ( WEB-remote관리)
1) oracle의 Apache Web Server 작동
2) 접근 : http://ip:5560/isqlplus
ex) http://192.168.0.45:5560/isqlplus
(3) utility 이용법 ( local / remote 관리 )
1) sqlgate
2) toad
3) orange
......
cf1) scott 계정 unlock 방법
SQL> conn system/pwd;
SQL> alter user scott identified by tiger account unlock;
cf2) 오라클 관리자 모드 ( SYSTEM/SYS )
Enterprise Manager Console ( EM 법 )
1) http://khs-com:1158/em
2) http://192.168.0.45:1158/em
[ Part2 - Oracle의 개요 ]
1. DBMS (DataBase Management System)의 개념
(1) DB(DataBase)
- 지속적으로 유지관리해야 할 데이터의 집합
(2) DBMS
- DB를 편리하게 관리하고, 효율적으로 저장하고
검색할 수 있는 환경을 제공해주는 시스템 소프트웨어
를 의미 ( ex: oracle, ms-sql, mysql, db2, ... )
2. DBMS 역사
(1) 1960년 : File System
(2) 1970년 : Network-DBMS
(3) 1980년 : 관계형-DBMS
(4) 1990년 : 관계형-DBMS, 객체관계형-DBMS
(5) 2000년 : 관계형/객체관계형/객체지향-DBMS
ex) Oracle 6.X, 7.X -> 관계형(RDBMS)
Oracle 8.X, 9.X, 10.X -> 객체관계형(OO-RDBMS)
cf) OODB
3. 데이터베이스 설계 단계
실세계(업무분석) -> 개념적설계 -> 논리적설계 -> 물리적설계
( ERD ) (DBMS결정)
4. SQL( Structured Query Language )
데이터를 Access 하기 위해 DBMS와 통신하는 언어
5. 기본 사용자 계정
(1) SYS
오라클 super사용자 ID이며, 데이터베이스에서 발생하는
모든 문제를 처리할 수 있는 권한
(2) SYSTEM
SYS과 같은데, 차이는 데이터베이스를 생성할 수 있는
권한이 없음
(3) SCOTT
처음 오라클을 사용하는 user들을 위한 SAMPLE계정이며,
일반적인 프로그램을 작성할 때 사용되는 계정
(for Developer )
(4) HR ( 인적관리계정 )
SAMPLE계정
6. 주요 용어
(1) TABLE
관계형 DBMS에서 기본 데이터저장구조로써 Entity(실체)의
집합 저장소 (ex: DETP, EMP, SALGRADE, .... )
(2) ROW
테이블의 행( 하나의 유효 데이터 )
(ex: 20 RESEARCH DALLAS )
(3) COLUMN
테이블의 열명
(ex: DEPTNO DNAME LOC )
(4) PRIMARY-KEY
테이블에서 각 ROW를 유일하게 구분하는
COLUMN에 부여하는 제약조건
(5) FOREIGN-KEY
다른 테이블의 COLUMN값을 참조하는
테이블에 부여하는 제약조건
(6) FIELD
테이블의 ROW와 COLUMN이 교차하는 데이터
(ex: dept테이블의 'RESEARCH'이란 값 )
(7) NULL
데이터가 존재하지 않는 FIELD값
cf) DETP : 부서테이블 (부모 테이블 )
EMP : 자식 테이블 (자식 테이블 )
7. SQL의 구분
(1) DQL ( Data Query Language )
-> 테이블의 데이터를 조회할 때 사용
(ex: select ~ )
(2) DML ( Data Manipulation Language )
-> 테이블의 데이터를 입력, 수정, 삭제할 때 사용
(ex: insert~, update~, delete~ )
(3) DDL ( Data Definition Language )
-> 테이블등의 객체를 생성, 변경, 삭제할 때 사용
(ex: create~, alter~, drop~ )
(4) TCL ( Transaction Control Language )
-> 테이블내의 DML문을 DB에 저장 or 취소할 때 사용
(ex: commit~, rollback~, savepoint )
(5) DCL ( Data Control Language )
-> DB사용자에게 권한을 부여 or 취소할 때 사용
(ex: grant~, revoke~ )
8. PL-SQL
제어문(조건문, 반복문)이 들어있는 SQL로써, 오라클 DBMS에서
지원하는 확장된 SQL
(1) Stored Procedure
-> 일련의 작업처리순서를 정의해 놓은 것으로
미리 DBMS에 컴파일되어져있어 프로그램 or 사용자로부터
호출되면 실행되는 일종의 작업처리순서 로직(기능)
(2) Anonymous Procedure
-> Stored Procedure 와 차이점은 DBMS에 자동으로 포함되어
있는 이름이 없는 Procedure
(3) Stored Function
-> 저장된 기능덩어리로써 Procedure와 차이점은 수행되고
나서 return 값이 존재함
(4) Trigger
-> 일련의 작업처리순서를 정의해 놓은 것으로 미리 DBMS에
컴파일되어져있어, 어떤 조건에 만족되어지는 상황(DML수행)
이 발생되면 자동으로 호출되어 작동되는 로직(기능)
(5) Package
-> Procedure/Function/Trigger 등의 기능별 묶음
[ Part 3 - SQL ]
1. DQL
(1) 기본 구조
SQL> select * from TAB;
SQL> select * from DETP;
SQL> select * from EMP;
(2) DISTINCT / ALL
SQL> select DISTINCT JOB from EMP;
SQL> select ALL JOB from EMP;
( SQL> select JOB from EMP; )
(3) ORDER BY
SQL> select EMPNO, SAL from EMP;
SQL> select EMPNO, SAL from EMP order by SAL;
SQL> select EMPNO, SAL from EMP order by SAL asc;
SQL> select EMPNO, SAL from EMP order by SAL desc;
//JOB desc 정렬하고, SAL desc 정렬하라
//JOB을 알파벳Z부터 정렬하고, 그 정렬결과를
다시 봉급이 많을 순서대로 출력하라
SQL> select JOB,SAL from EMP order by JOB desc, SAL desc;
(4) ALIAS
SQL> select JOB "직업", SAL "봉급" from EMP;
SQL> select JOB 직업, SAL "봉급" from EMP;
SQL> select JOB 직업 , SAL "봉급 !@#$%^" from EMP;
SQL> select JOB as 직업 , SAL as "봉급 !@#$%^" from EMP;
SQL> select JOB as 직업, SAL as "1234" from EMP;
SQL> select JOB as 1234, SAL as "봉급" from EMP;(X)
SQL> select JOB as !@#$^%, SAL as "1234" from EMP;(X)
cf) 숫자 or 특수문자 ALIAS는 반드시 ""안에 넣어줘야한다.
(5) WHERE
SQL> select EMPNO, DEPTNO from EMP where DEPTNO=20;
SQL> select EMPNO, SAL from EMP where DEPTNO=30;
//30번 부서 사람의 사번과 봉급을 봉급이 많은 순서대로 출력
SQL> select EMPNO, SAL from EMP where DEPTNO=30 order by SAL desc;
//직업이 'SALESMAN'인 사람의 직업과 이름을 출력(이름의 알파펫 순서)
SQL> select JOB, ENAME from EMP where JOB='SALESMAN' order by ENAME;
//입사일 '81/12/03'사원의 사번과 이름을 사번의 내림차순으로 출력
SQL> select EMPNO, ENAME from EMP where HIREDATE='81/12/03'
order by EMPNO desc;
//입사일이 '81/12/03'이후인 사원의 사번과 이름을 사번의
내림차순으로 출력
SQL> select EMPNO, ENAME, HIREDATE from EMP
where HIREDATE>='81/12/03' order by EMPNO desc;
//사번이 7900 인 사원의 이름과 사번을 출력
SQL> select EMPNO, ENAME from EMP where EMPNO=7900;
(6) 연산자 ( Operator )
1) 산술 연산자 ( + , -, * , / )
//부서번호가 30번 부서사람들의 봉급을 10%인상 이름/봉급 출력
SQL> select ENAME, SAL*1.1 from EMP where DEPTNO=30;
//30번 부서의 모든 사원의 '연말보너스'를 높은 순으로 출력
( 단, 연말보너스는 급여의 2배와 comm 의 1/2의 합이다. )
SQL> select EMPNO, SAL/12*2+NVL(COMM,0)/2 "연말보너스"
from EMP where DEPTNO=30 order by "연말보너스" desc;
2) 비교 연산자 ( =, !=, >, >=, <, <= )
SQL> select ENAME, SAL from EMP where SAL=950;
//연봉이 3000 이상인 사원의 이름과 연봉을 출력
SQL> select ENAME, SAL from EMP where SAL>=3000;
//부서번호가 30번이 아닌 사원의 이름과 연봉을 출력
SQL> select ENAME, SAL from EMP where DEPTNO != 30;
3) 논리 연산자 ( AND, OR, NOT )
//20번 부서이고 연봉인 3000 이상인 사람을 부서번호/이름/연봉 출력
SQL> select DEPTNO, ENAME, SAL from EMP where DEPTNO=20 AND SAL>=3000;
//직업이 'SALESMAN'이고 부서번호가 30인 사원의 이름/직업/부서번호 출력
SQL> select ENAME, JOB, DEPTNO from EMP where DEPTNO=30 and JOB='SALESMAN';
//연봉이 1000을 미만 또는 4000이상의 사원의 이름과 연봉을 출력
SQL> select ENAME, SAL from EMP where SAL<1000 or SAL>=4000;
//연봉이 1000이상 4000미만의 사원의 이름과 연봉을 출력
SQL> select ENAME, SAL from EMP where NOT (SAL<1000 or SAL>=4000);
4) SQL 연산자
( IN, ANY, ALL, BETWEEN, LIKE, IS NULL, IS NOT NULL, EXISTS,..)
//부서번호가 10이나 20부서의 사원 이름과 부서번호를 출력
SQL> select ENAME, DEPTNO from EMP where DEPTNO IN(10, 20, 100);
SQL> select ENAME, DEPTNO from EMP where DEPTNO=ANY(10, 20, 100);
//30번부서 사원중 가장높은 연봉을 받는 사람보다 더 많은
연봉의 사원이름, 연봉 출력
SQL> select ENAME, SAL from EMP
where SAL>ALL(select SAL from EMP where DEPTNO=30);
//연봉이 1000 이상 3000 미만인 사원이름과 연봉 출력
SQL> select ENAME, SAL from EMP where SAL BETWEEN 1000 AND 3000;
//'FORD'와 'SCOTT' 사이의 이름을 알파벳순서로 출력
SQL> select ENAME from EMP where ENAME BETWEEN 'FORD' AND 'SCOTT'
order by ENAME;
//이름이 J로 시작되는 사원이름을 출력
SQL> select ENAME from EMP where ENAME LIKE 'J%';
//이름에 T가 들어있는 사원이름을 출력
SQL> select ENAME from EMP where ENAME LIKE '%T%';
//세번째 글자가 A인 사원이름을 출력
SQL> select ENAME from EMP where ENAME LIKE '__A%';
//연봉에 8자가 들어있는 사원의 이름과 연봉을 출력
SQL> select ENAME, SAL from EMP where SAL LIKE '%8%';
//연봉의 십의자리가 5인 사원의 이름과 연봉을 출력
SQL> select ENAME, SAL from EMP where SAL LIKE '%5_';
//커미션이 NULL인 사원의 이름과 커미션을 출력
SQL> select ENAME, COMM from EMP where COMM IS NULL;
//커미션이 NULL이 아닌 사원의 이름과 커미션을 출력
SQL> select ENAME, COMM from EMP where COMM IS NOT NULL;
//이름이 'FORD'라는 사원이 있으면 사원 이름과 커미션 출력
SQL> select ENAME, COMM from EMP
where EXISTS(select ENAME from EMP where ENAME='FORD');
//이름이 'AAA'라는 사원이 있으면 사원 이름과 커미션 출력
SQL> select ENAME, COMM from EMP
where EXISTS(select ENAME from EMP where ENAME='AAA');
5) 결합 연산자 ( || )
//XXX의 연봉은 YYY입니다.
SQL> select ENAME||'의 연봉은'||SAL||'입니다' from EMP;
SQL> select ENAME||11111||SAL||222222 from EMP;
6) 집합 연산자 ( UNION(합), UNION ALL, INTERSECT(교), MINUS(차) )
<1> UNION
SQL> select EMPNO, ENAME from EMP
UNION
select DEPTNO, DNAME from DEPT;
<2> UNION ALL
SQL> select EMPNO, ENAME from EMP
2 UNION
3 select EMPNO, ENAME from EMP;
SQL> select EMPNO, ENAME from EMP
2 UNION ALL
3 select EMPNO, ENAME from EMP;
<3> INTERSECT
SQL> select EMPNO, ENAME from EMP
2 INTERSECT
3 select DEPTNO, DNAME from DEPT;
SQL> select EMPNO, ENAME from EMP
2 INTERSECT
3 select EMPNO, ENAME from EMP where DEPTNO=20;
<4> MINUS
SQL> select EMPNO, ENAME from EMP
2 MINUS
3 select EMPNO, ENAME from EMP where DEPTNO=20;
(7) 연산자 우선순위
1) 1순위 : 비교연산자, SQL연산자, 산술연산자
2) 2순위 : NOT
3) 3순위 : AND
4) 4순위 : OR
5) 5순위 : 결합연산자, 집합연산자
SQL> select ENAME, SAL from EMP
2 where NOT SAL>1000 AND SAL<3000;
SQL> select ENAME, SAL from EMP
2 where NOT (SAL>1000 AND SAL<3000);
(8) SQL문장의 실행방법
1) 문장의 끝에 ;을 붙여주고 enter!
2) 문장의 다음 라인에 /를 붙여주고 enter!
SQL> select ENAME, SAL from EMP
2 /
3) 가장 최근(직전)의 버퍼에 담겨있는 SQL문장을 실행
SQL> run
4) 버퍼의 SQL 보기 / 편집
SQL> edit
<1> 메모리 버퍼에 메모장에 SQL보기
<2> 편집
<3> 메모장을 저장(메모리버퍼에 쓴다)하고 종료
<4> run
/////////////////// 문제 1 ////////////////////////
2. SQLPlus
(1) SQL*Plus 란 ?
오라클에서만 지원되는 SQL의 추가 명령어
(2) SQL과 SQL*Plus 차이
1) SQL
<1> DB내의 테이블에 데이터를 입력/수정/삭제/조회
<2> 버퍼에 저장됨(edit)
<3> 여러 라인에 걸쳐서 작성 가능
<4> 실행시 ; 또는 / 로 실행됨
<5> 명령어의 요약할 수 없음
2) SQL*Plus
<1> SQL명령어를 DBMS에 전송
<2> oracle 에서만 지원
<3> DB의 데이터에 대한 직접적인 조작이 불가능
<4> 버퍼에 저장되지 않음
<5> 반드시 하나의 라인만 사용해야 함
<6> 실행시 ; 이 필요없음
<7> 명령어를 요약해서 작성할 수 있음
ex) SQL> describe EMP
SQL> desc EMP
(3) SQL*Plus 종류
1) 파일명령어
-> 실행된 SQL을 편집/저장/실행할 때 사용하는 명령어
<1> SAVE
예)
SQL> save E:\SOO\oracle\temp\splplus\scott_emp.sql
SQL> save E:\SOO\oracle\temp\splplus\scott_emp2.sql
<2> GET
예)
SQL> get E:\SOO\oracle\temp\splplus\scott_emp.sql
<3> SPOOL ~ SPOOL OFF
SQL> SPOOL E:\SOO\oracle\temp\splplus\scott_spool.txt
SQL> select * from DEPT;
SQL> select ENAME from EMP where SAL>=3000;
SQL> SPOOL OFF
2) (버퍼) 편집 명령어
-> SQL문을 수정/삭제할 때 사용하는 명령어
<1> i
SQL> select * from DEPT;
SQL> i
2 where DEPTNO=30;
<2> L
SQL> L
1 select * from DEPT
2* where DEPTNO=30
<3> N
SQL> 2
2* where DEPTNO=30
<4> C
SQL> C/30/20
2* where DEPTNO=20
SQL> /
<5> DEL N( 임의의 숫자 - line번호 )
SQL> DEL 2
SQL> L
1* select * from DEPT
SQL> /
<6> CLEAR BUFFER
SQL> clear buffer
buffer 소거되었습니다.
SQL> L
SP2-0223: SQL 버퍼에 줄이 없습니다.
SQL> /
SP2-0103: SQL 버퍼에 실행할 사항이 없습니다.
3) 실행 명령어( ***** )
-> SQL문을 실행할 때 사용하는 명령어
<1> START
SQL> start E:\SOO\oracle\temp\splplus\scott_emp.sql
<2> @
SQL> @E:\SOO\oracle\temp\splplus\scott_emp2.sql
<3> RUN
SQL> run
<4> /
SQL> /
cf) run과 /는 특정 SQL파일을 수행하는 기능이 없고,
오로지 버퍼내용만을 수행한다.
4) 환경 명령어
-> SQL문의 결과를 출력시, 출력되는 내용을 다르게 할 때 사용
<1> SET FEEDBACK ON 과 SET FEEDBACK OFF
SQL> SET FEEDBACK ON
SQL> select * from DEPT;
SQL> SET FEEDBACK OFF
SQL> select * from DEPT;
<2> SET HEADING ON 과 SET HEADING OFF
SQL> SET HEADING ON
SQL> select * from DEPT;
SQL> SET HEADING OFF
SQL> select * from DEPT;
5) 형식 명령어
-> SQL문의 결과를 보고서 형태로 출력할 때 사용
3. 함수 ( FUNCTION )
(1) 함수(function)란? ( 처리/반환 )
어떠한 일을 수행하는 기능으로써 주어진 인수(argument)를 재료로
처리를 하여 그 결과를 반환하는 일을 수행
(2) 함수 기능의 구체적 표현
1) Data에 대한 계산
2) Data를 다른 형태로 변환
3) Data를 결과를 출력
(3) 함수의 종류
1) 단일행 함수
-> 하나의 행(ROW)당, 하나의 결과값을 반환하는 함수
2) 복수행 함수
-> 여러개의 행당, 하나의 결과값을 반환하는 함수
(4) 단일행 함수
1) 문자함수
<1> CHR(아스키코드)
//65와 매핑되어있는 문자를 출력
SQL> select CHR(65) from DUAL;
<2> CONCAT(컬럼명, '붙일문자열')
SQL> select ENAME||' is a '|| JOB from EMP;
SQL> select CONCAT(ENAME, ' is a '), JOB from EMP;
<3> INITCAP('문자열')
SQL> select 'the lion' from dual;
SQL> select INITCAP('the lion') from dual;
<4> LOWER('문자열')
SQL> select LOWER('MY NAME IS KHS') from DUAL;
<5> LPAD('문자열1', 자리수, '문자열2')
SQL> select LPAD('khs', 13, '*#') from DUAL;
SQL> select LPAD('khs', 13, ' ') from DUAL;
<6> RPAD('문자열1', 자리수, '문자열2')
SQL> select RPAD('khs', 13, '*#') from DUAL;
<7> LTRIM('문자열1', '문자열2')
SQL> select LTRIM('xyxXxyLAST WORDxy', 'xy') from DUAL;
SQL> select LTRIM('xyyxXxyLAST WORDxy', 'xy') from DUAL;
SQL> select LTRIM('xyyxXxyLAST WORDxy', 'xy') from DUAL;
SQL> select LTRIM(' LAST WORDxy', ' ') from DUAL;
SQL> select LTRIM(' LAST WORDxy') from DUAL;
<8> RTRIM('문자열1', '문자열2')
SQL> select RTRIM('xyxXxyLAST WORDxy', 'xy') from DUAL;
SQL> select RTRIM('xyxXxyLAST WORDxyx', 'xy') from DUAL;
SQL> select RTRIM('WORD ', ' ') from DUAL;
SQL> select RTRIM('WORD ') from DUAL;
SQL> select length('WORD ') from DUAL;
SQL> select length(RTRIM('WORD ')) from DUAL;
<9> REPLACE('문자열1', '문자열2', '문자열3')
SQL> select REPLACE('JACK and JUE', 'J', 'BL') from DUAL;
SQL> select REPLACE(DNAME, 'A', 'i') from DEPT;
<10> SUBSTR('문자열', 자리수, 갯수)
SQL> select SUBSTR('ABCDEFG', 3, 2) from DUAL;
//ename의 두번째 자리가 'A'인 사원의 이름 출력
SQL> select ENAME from EMP where ENAME LIKE '_A%';
SQL> select ENAME from EMP where SUBSTR(ENAME, '2', '1')='A';
<11> ASCII('문자')
SQL> select ASCII('A') from DUAL;
SQL> select ASCII('"') from DUAL;
SQL> select ASCII('#') from DUAL;
SQL> select ASCII('3') from DUAL;
<12> LENGTH('문자')
SQL> select LENGTH('진달래 꽃') from DUAL;
SQL> select LENGTH('abC D') from DUAL;
<13> GREATEST('문자열1', '문자열2', '문자열3')
SQL> select GREATEST('CC', 'ABCDE', 'CA') from DUAL;
SQL> select GREATEST('12', '132', '119') from DUAL;
SQL> select GREATEST('가나', '가다', '가자') from DUAL;
<14> LEAST('문자열1', '문자열2', '문자열3')
SQL> select LEAST('CC', 'ABCDE', 'CA') from DUAL;
SQL> select LEAST('12', '132', '119') from DUAL;
SQL> select LEAST('가나', '가다', '가자') from DUAL;
<15> INSTR('문자열1', '문자열2', 자리수1, 자리수2)
SQL> select INSTR('CORPORATE FLOOR', 'OR', 3, 2) from DUAL;
SQL> select INSTR('CORPORATE FLOOR', 'OR', 6, 1) from DUAL;
cf) '자리수1'부터 '자리수2'번째의 '문자열2'를 찾음
<16> NVL(컬럼명, 숫자)
SQL> select ENAME, NVL(COMM, 100) from EMP;
SQL> select ENAME, NVL(COMM, '100') from EMP;
SQL> select ENAME, NVL(COMM, 0) from EMP;
2) 숫자함수
<1> ABS(숫자)
SQL> select ABS(-15) from DUAL;
<2> CEIL(숫자)
SQL> select CEIL(11.012) from DUAL;
SQL> select CEIL(-11.012) from DUAL;
<3> FLOOR(숫자)
SQL> select FLOOR(11.012) from DUAL;
SQL> select FLOOR(-11.012) from DUAL;
<4> ROUND(숫자)
SQL> select ROUND(11.012) from DUAL;
SQL> select ROUND(-11.48754321, 4) from DUAL;
SQL> select ROUND(-11.48756321, 4) from DUAL;
<5> COS(숫자[rad])
SQL> select COS(90 * 3.14/180) from DUAL;
<6> SIN(숫자[rad])
SQL> select SIN(90 * 3.14/180) from DUAL;
<7> TAN(수자[rad])
SQL> select TAN(45 * 3.14/180) from DUAL;
<8> EXP(숫자)
SQL> select EXP(4) from DUAL;
<9> LOG(숫자1, 숫자2)
SQL> select LOG(10, 100) from DUAL;
<10> MOD(숫자1, 숫자2)
SQL> select MOD(11, 4) from DUAL;
<11> POWER(숫자1, 숫자2)
SQL> select POWER(3, 2) from DUAL;
SQL> select POWER(3, 3) from DUAL;
<12> TRUNC(숫자1, 숫자2)
SQL> select TRUNC(15.789, 2) from DUAL;
SQL> select TRUNC(15.789, 0) from DUAL;
SQL> select TRUNC(-15.789, 0) from DUAL;
3) 날짜함수(*****)
<1> SYSDATE
SQL> select SYSDATE from DUAL;
<2> ADD_MONTHS(날짜컬럼 or 날짜데이터, 숫자)
SQL> select HIREDATE, ADD_MONTHS(hiredate, 3) from EMP
2 where EMPNO=7782;
SQL> select ADD_MONTHS('90/11/05', 2) from DUAL;
<3> LAST_DAY(날짜컬럼 or 날짜데이터)
SQL> select LAST_DAY('10/12/01') from DUAL;
SQL> select HIREDATE, LAST_DAY(hiredate) from EMP;
<4> NEW_TIME(날짜컬럼 or 날짜데이터, 'GMT', 'PDT')
SQL> select NEW_TIME('08/12/05', 'GMT', 'PDT') from DUAL;
SQL> select NEW_TIME(hiredate, 'GMT', 'PDT') from EMP;
<5> MONTHS_BETWEEN(날짜컬럼or날짜데이터1, 날짜컬럼or날짜데이터2)
SQL> select MONTHS_BETWEEN('80/02/03', '82/02/27') from DUAL;
//empno 가 7782인 사원의 현재까지의 근무월수
SQL> select MONTHS_BETWEEN(SYSDATE, hiredate) from EMP
2 where EMPNO=7782;//결과의 단위(월)
<6> NEXT_DAY(날짜컬럼or날짜데이터, 숫자)
SQL> select SYSDATE, NEXT_DAY(SYSDATE, 4) from DUAL;
//4는 수요일
-> 기준날짜 초과의 해당 요일(숫자)의 날짜를 반환
4) 문자변환함수(*****)
-> TO_CHAR(날짜컬럼or날짜데이터, '변환포멧')
<1> 'D'
SQL> select SYSDATE, TO_CHAR(SYSDATE, 'D') from DUAL;
<2> 'DAY'
SQL> select SYSDATE, TO_CHAR(SYSDATE, 'DAY') from DUAL;
<3> 'DY'
SQL> select SYSDATE, TO_CHAR(SYSDATE, 'DY') from DUAL;
<4> 'DD'
SQL> select SYSDATE, TO_CHAR(SYSDATE, 'DD') from DUAL;
<5> 'MM'
SQL> select SYSDATE, TO_CHAR(SYSDATE, 'MM') from DUAL;
<6> 'MONTH'
SQL> select SYSDATE, TO_CHAR(SYSDATE, 'MONTH') from DUAL;
<7> 'YY'
SQL> select SYSDATE, TO_CHAR(SYSDATE, 'YY') from DUAL;
<8> 'YYYY'
SQL> select SYSDATE, TO_CHAR(SYSDATE, 'YYYY') from DUAL;
<9> 'DD-MM-YY'(원하는 순서로 조합 가능)
SQL> select SYSDATE, TO_CHAR(SYSDATE, 'DD-MM-YY') from DUAL;
SQL> select SYSDATE, TO_CHAR(hiredate, 'DD-MM-YY') from EMP;
<10> 'fmMM-DD-YY'(원하는 순서로 조합 가능)
SQL> select SYSDATE, TO_CHAR(SYSDATE, 'fmDD-MM-YY') from DUAL;
<11> 'HH' or 'HH12'
SQL> select SYSDATE, TO_CHAR(SYSDATE, 'HH') from DUAL;
<12> 'HH24'
SQL> select SYSDATE, TO_CHAR(SYSDATE, 'HH24') from DUAL;
<13> 'MI'
SQL> select SYSDATE, TO_CHAR(SYSDATE, 'MI') from DUAL;
<14> 'SS'
SQL> select SYSDATE, TO_CHAR(SYSDATE, 'SS') from DUAL;
<15> 'AM' or 'PM'
SQL> select SYSDATE, TO_CHAR(SYSDATE, 'AM HH:MI:SS') from DUAL;
SQL> select SYSDATE, TO_CHAR(SYSDATE, 'PM HH:MI:SS') from DUAL;
<16> YYYY-MM-DD PM HH:MI:SS (DY)
//2010-12-01 오후 02:20:15 (수)
SQL> select TO_CHAR(SYSDATE, 'YYYY-MM-DD PM HH:MI:SS (DY)')
from DUAL;
SQL> select TO_CHAR(SYSDATE, 'fmYYYY-MM-DD PM HH:MI:SS (DY)')
from DUAL;
cf) Tip
- 출력에 임의의 문자열 삽입
SQL> select TO_CHAR(SYSDATE, 'DD "of" MONTH') from DUAL;
SQL> select TO_CHAR(SYSDATE, 'DD "임의의 문자열" MONTH') from DUAL;
- one, two, three,...
SQL> select TO_CHAR(SYSDATE, 'DDSP') from DUAL;
- simple 서수 ( 1st, 2nd, .. )
SQL> select TO_CHAR(SYSDATE, 'DDTH') from DUAL;
SQL> select TO_CHAR(SYSDATE, 'SSTH') from DUAL;
- 서수 ( first, second, .. )
SQL> select TO_CHAR(SYSDATE, 'DDSPTH') from DUAL;
SQL> select TO_CHAR(SYSDATE, 'SSSPTH') from DUAL;
5) 숫자변환함수 (무시!!)
-> TO_NUMBER('숫자')
SQL> select '100' from DUAL; //100
SQL> select '100'+12 from DUAL; //112
SQL> select TO_NUMBER('100') from DUAL; //100
SQL> select TO_NUMBER('100')+12 from DUAL; //112
SQL> select 'A' from DUAL;
SQL> select 'A'+1 from DUAL;//에러
SQL> select TO_NUMBER('A') from DUAL;//에러
6) 날짜변환함수
-> TO_DATE('날짜 형태의 문자열')
'날짜변환포멧(앞 문자열을 해석하는 순서)'
SQL> select TO_DATE('01-05-99', 'DD/MM/YYYY') from DUAL;
SQL> select TO_DATE('12-30-99', 'MM/DD/YYYY') from DUAL;
7) 시스템함수
-> USER ( field의 데이터 사용자를 리턴 )
SQL> select USER from DUAL;
SQL> select USER from EMP;
8) 최신추가함수
<1> NVL2(컬럼명, 표현식1, 표현식2)
SQL> select COMM, NVL2(comm, comm*1.1, 0) from EMP;
<2> NULLIF(비교값1, 비교값2)
SQL> select ENAME, COMM, NULLIF(comm, 0) from EMP;
<3> COALESCE(표현식1, 표현식2, ... , 표현식n)
SQL> select comm, sal, coalesce(comm, sal) from EMP;
<4> TRIM(LEADING 문자 FROM 대상컬럼) //왼쪽부터..
SQL> select TRIM(LEADING 'A' FROM ENAME) from EMP;
SQL> select TRIM(LEADING 'x' FROM 'xxyxXxyLAST') from DUAL;
<5> TRIM(TRAILING 문자 FROM 대상컬럼) //오른쪽부터..
SQL> select TRIM(TRAILING 'S' FROM ENAME) from EMP;
<5> TRIM(BOTH 문자 FROM 대상컬럼) //양쪽에서..
SQL> insert into EMP(EMPNO, ENAME) values(1000, 'TOMCAT');
SQL> select TRIM(BOTH 'T' FROM ENAME) from EMP;
SQL> rollback;//위의 insert문을 DB에 적용하지 않음
(5) 복수행(그룹) 함수( ***** )
1) COUNT(컬럼명)
//사원들의 이름갯수
SQL> select COUNT(ename) from EMP;
SQL> select COUNT(comm) from EMP;
SQL> select count(empno) from emp; //전체행의 개수
SQL> select count(*) from emp; //전체행의 개수
2) SUM(컬럼명)
SQL> select sum(comm) from emp;
SQL> select sum(sal) from emp;
3) AVG(컬럼명)
SQL> select AVG(sal) from EMP;
SQL> select AVG(comm) from EMP; //NULL갯수 제외
SQL> select AVG(NVL(comm,0)) from EMP; //NULL갯수 포함
4) MAX(컬럼명) 와 MIN(컬럼명)
SQL> select MAX(sal) from EMP;
SQL> select MIN(sal) from EMP;
SQL> select MAX(sal)-MIN(sal) from EMP;
//최고 연봉자의 사원이름을 구하세요.
SQL> select ENAME from EMP where SAL=MAX(sal);
ORA-00934: 그룹 함수는 허가되지 않습니다
SQL> select ENAME from EMP
where SAL=(select MAX(sal) from EMP);
5) STDDEV(컬럼명)
SQL> select STDDEV(comm) from EMP;
6) VARIANCE(컬럼명)
SQL> select VARIANCE(comm) from EMP;
<1> Tip
SQL> select AVG(SAL), DEPTNO from EMP;
ORA-00937: 단일 그룹의 그룹 함수가 아닙니다
//부서별 평균 연봉 출력
SQL> select AVG(SAL), DEPTNO from EMP group by DEPTNO;
SQL> select ROUND(AVG(SAL)), DEPTNO from EMP group by DEPTNO;
<2> 문제
- Q1:연봉 2000 이상인 사원들의 부서별 평균연봉의 반올림값
SQL> select ROUND(AVG(SAL)), DEPTNO from EMP
where SAL>=2000 group by DEPTNO;
- Q2:연봉 2000 이상인 사원들의 부서별 평균연봉의 반올림값
을 부서번호의 오름차순으로 정렬하라
SQL> select ROUND(AVG(SAL)), DEPTNO from EMP where SAL>=2000 group by DEPTNO
order by DEPTNO;
- Q3:연봉 2000 이상인 사원들의 부서별 평균연봉의 반올림값
을 평균연봉의 반올림값으로 오름차순 정렬하라
SQL> select ROUND(AVG(SAL)) aa, DEPTNO
2 from EMP
3 where SAL>=2000
4 group by DEPTNO
5 order by aa;
SQL> select ROUND(AVG(SAL)), DEPTNO
2 from EMP
3 where SAL>=2000
4 group by DEPTNO
5 order by 1;
- Q4:연봉 1000이상인 사원들의 부서별 평균연봉의 반올림값
을 부서번호로 내림차순 정렬하라
(단, 부서별 평균연봉이 2000 이상인 값만 출력)
SQL> select ROUND(AVG(SAL)), DEPTNO
2 from EMP
3 where SAL>=1000
4 group by DEPTNO
5 having ROUND(AVG(SAL))>=2000
6 order by DEPTNO desc;
#주의 : having 절에서는 alias를 쓸 수 없음
SQL> select ROUND(AVG(SAL)) aa, DEPTNO
2 from EMP
3 where SAL>=1000
4 group by DEPTNO
5 having aa>=2000
6 order by DEPTNO desc;//에러!!
- Q5:연봉 1000이상인 사원들의 부서별 평균연봉의 반올림값
을 부서번호로 내림차순 정렬하라
(단, 부서번호가 20 이상인 값만 출력! )
SQL> select ROUND(AVG(SAL)), DEPTNO
2 from EMP
3 where SAL>=1000
4 group by DEPTNO
5 having DEPTNO>=20
6 order by DEPTNO desc;
- Q6:각 부서별 같은 업무를 하는 사원의 인원수를 구하여
부서번호, 업무명, 인원수를 출력하라
(단, 부서번호와 업무명으로 각각 내림차순 정렬! )
SQL> select DEPTNO, JOB, COUNT(EMPNO) from EMP
2 group by DEPTNO, JOB
3 order by DEPTNO desc, JOB desc;
< 일반화 규칙 1 >
[1] S-> F -> W -> G -> H -> O
[2] where 절에는 그룹함수를 쓸 수 없음
[3] having 절에서는 alias를 쓸 수 없음
4. 조인(JOIN) & 서브쿼리(SubQuery)
(1) 조인(JOIN)
1) 설명
하나의 테이블로는 원하는 컬럼정보를 참조할 수 없는 경우
관련된 테이블을 논리적으로 결합하여 원하는 컬럼정보를
참조하는 방법을 조인이라 한다.
2) 조건
논리적으로 결합되는 2개 이상의 테이블에는 반드시 '공통컬럼'
이 존재해야하며 이 '공통컬럼'은 동일한 데이터 타입과
공통된 데이터를 포함해야 한다.
3) 예
//(부서번호가 같은) 사원번호와 부서이름 출력!
SQL> select EMP.EMPNO, DEPT.DNAME from EMP, DEPT
2 where EMP.DEPTNO=DEPT.DEPTNO;
SQL> select e.EMPNO, d.DNAME from EMP e, DEPT d
2 where e.DEPTNO=d.DEPTNO;
SQL> select e.EMPNO, d.DNAME from EMP e join DEPT d
2 on e.DEPTNO=d.DEPTNO;
4) 종류
<1> CROSS 조인 ( CARTESIAN 조인 )
2개 이상의 테이블이 조인될 때 where절에 의해
공통되는 컬럼에 의한 결합이 발생하지 않는 경우
즉, 테이블 전체행의 전체컬럼이 조인에 사용되는
조인을 말한다.
따라서, 모든 데이터가 검색된다.
SQL> select e.EMPNO, e.ENAME, d.DNAME
from EMP e, DEPT d;
SQL> select e.EMPNO, e.ENAME, s.GRADE
from EMP e, SALGRADE s;
<2> NATURAL 조인 ( EQUI 조인 ) - 가장 일반적
where 절이 사용된 '공통된 컬럼'들이 동등 연산자
( equals, = )에 의해 비교되는 조인을 말한다.
1> 형태1(*****)
SQL> select e.EMPNO, d.DNAME from EMP e, DEPT d
2 where e.DEPTNO=d.DEPTNO; //14개
//(부서번호가 같은) 사원번호와 부서이름 출력!(단, 30부서만)
SQL> select e.EMPNO, d.DNAME from EMP e, DEPT d
2 where e.DEPTNO=d.DEPTNO and d.DEPTNO=30;
2> 형태2
SQL> select EMPNO, ENAME, DNAME from EMP
2 natural join DEPT;
//사원번호와 사원이름과 부서이름 출력!(단, 20부서만)
SQL> select EMPNO, ENAME, DNAME from EMP
2 natural join DEPT
3 where DEPTNO=20;
3> 형태3 ( oracle 9i )
SQL> select e.EMPNO, e.ENAME, d.DNAME
2 from EMP e join DEPT d using(DEPTNO);
//사원번호와 사원이름과 부서이름 출력!(단, 10부서만)
SQL> select e.EMPNO, e.ENAME, d.DNAME
2 from EMP e join DEPT d using(DEPTNO)
3 where DEPTNO=10;
4> 형태4
SQL> select e.EMPNO, d.DNAME from EMP e join DEPT d
2 on e.DEPTNO=d.DEPTNO;
//(부서번호가 같은) 사원번호와 부서이름 출력!(단, 30부서만)
SQL> select e.EMPNO, d.DNAME from EMP e join DEPT d
2 on e.DEPTNO=d.DEPTNO
3 where d.DEPTNO=30;
cf) INNER조인 ( 형태4와 비교 )
SQL> select e.EMPNO, d.DNAME from EMP e inner join DEPT d
2 on e.DEPTNO=d.DEPTNO
3 where d.DEPTNO=30;
<3> SELF 조인
참조해야 할 컬럼이 자신의 테이블에 있는 다른 컬럼인
경우 사용하는 조인
1> 형태1
//사원번호가 같은 사원번호, 사원이름을 출력
SQL> select e1.EMPNO, e2.ENAME from EMP e1, EMP e2
2 where e1.EMPNO=e2.EMPNO;
2> 형태2
SQL> select e1.EMPNO, e2.ENAME from EMP e1 join EMP e2
2 on e1.EMPNO=e2.EMPNO;
여기서.. sqlForSelfJoin.sql을 작성해서 해당 검색을 하자
//'취업반'이면서 '국어 and 미술'을 듣는 학생이름을 출력!
답1> select NAME from HACKSA where DEPT='취업반'
2 INTERSECT
3 select NAME from HACKSA where SUBJECT='국어'
4 INTERSECT
5 select NAME from HACKSA where SUBJECT='미술';
답2> select NAME from HACKSA where DEPT='취업반' and SUBJECT='국어'
2 INTERSECT
3 select NAME from HACKSA where DEPT='취업반' and SUBJECT='미술';
답3> select a.NAME from HACKSA a, HACKSA b
2 where a.NAME=b.NAME
3 and a.DEPT='취업반'
4 and a.SUBJECT='국어'
5 and b.SUBJECT='미술';
답4> select a.NAME from HACKSA a join HACKSA b
2 on a.NAME=b.NAME
3 and a.DEPT='취업반'
4 and a.SUBJECT='국어'
5 and b.SUBJECT='미술';
<4> OUTER 조인
한쪽 테이블에는 해당하는 데이터가 존재하는 데
( ex: DEPT의 deptno=40 - 존재 O)
다른쪽 테이블에는 데이터가 존재하지 않을 경우
( ex: EMP의 deptno=40 - 존재 X)
모든 데이터를 출력하게 하는 조인
1>형태1
//사원번호, 사원이름, 부서이름 출력!
SQL> select e.EMPNO, e.ENAME, d.DNAME from EMP e, DEPT d
2 where e.DEPTNO (+)= d.DEPTNO; //15개
//dept deptno컬럼이 기준
비교> select e.EMPNO, e.ENAME, d.DNAME
from EMP e, DEPT d
where d.DEPTNO (+)= e.DEPTNO; //14개
//emp deptno컬럼이 기준
즉, right outer join 과 유사
(주의 : (+)= 으로는 full outer join은 안됨 )
2>형태2
SQL> select e.EMPNO, e.ENAME, d.DNAME
2 from EMP e right outer join DEPT d
3 on e.DEPTNO=d.DEPTNO; //15개
SQL> select e.EMPNO, e.ENAME, d.DNAME
2 from EMP e left outer join DEPT d
3 on e.DEPTNO=d.DEPTNO; //14개
여기서.. sqlForOuterJoin.sql을 작성해서 해당 검색을 하자
SQL> select T1.NO, T1.NAME, T2.NO, T2.NAME
2 from T1 left outer join T2
3 on T1.NO=T2.NO; //T1기준
SQL> select T1.NO, T1.NAME, T2.NO, T2.NAME
2 from T1 right outer join T2
3 on T1.NO=T2.NO; //T2기준
SQL> select T1.NO, T1.NAME, T2.NO, T2.NAME
2 from T1 full outer join T2
3 on T1.NO=T2.NO; //T1과 T2 기준
< 일반화 규칙 2 >
OUTER JOIN의 옵션
- right : 오른쪽 테이블의 해당컬럼을 기준
- left : 왼쪽 테이블의 해당컬럼을 기준
- full : 두 테이블 모두의 해당컬럼을 기준
< 일반화 규칙 3>
DQL문의 구문배치순서 및 실행순서(NUMBER)
- select XX -----> 6
- from XX (XX join) -----> 1
- on XX -----> 2
- where XX -----> 3
- group by XX -----> 4
- having XX -----> 5
- order by XX -----> 7
(2) 서브쿼리(SubQuery)
1) 설명
하나의 DQL문장절에 부속된(포함된) 또 다른 SELECT문장
따라서, 두 번 이상 질의를 해야 얻을 수 있는 결과를
한번의 질의로 해결이 가능케하는 쿼리
2) 용어
<1> Main-Query or Outer-Query
<2> Sub-Query or Inner-Query
3) 특징
<1> 괄호로 묶어야 한다.
<2> 실행순서는 대부분 Sub-Query가 먼저 수행되고,
Main-Query 수행된다.
<3> Sub-Query는 Main-Query의 다음 부분에 위치된다.
- select/delete/update문장의 from절/where절
- insert문장의 into절
- update문장의 set절
<4> Sub-Query는 order by절을 사용할 수 없다.
( 예외: select/delete/update문장의 from절 )
4) 종류
<1> 단일행 Sub-Query
-> Sub-Query의 결과가 하나의 컬럼 and
하나의 행만을 리턴해 주는 경우의 쿼리
즉, 하나의 데이터를 리턴해 주는 쿼리
//사원번호가 7900 인 사원의 부서이름
SUB> select DEPTNO from EMP where EMPNO=7900
MAIN> select DNAME from DEPT where DEPTNO=30
SQL> select DNAME from DEPT
where DEPTNO=(select DEPTNO from EMP where EMPNO=7900);
cf)SQL> select d.DNAME from DEPT d, EMP e
2 where d.DEPTNO=e.DEPTNO and e.EMPNO=7900;
//부서번호가 10번인 사원연봉과 연봉이 같은
사원의 이름과 커미션을 구하라.
SUB> select SAL from EMP where DEPTNO=10
error> select ENAME, COMM from EMP
where SAL=(select SAL from EMP where DEPTNO=10);
-> 행(ROW)이 여러개 return 되었기 때문(복수행)
SUB> select SAL, COMM from EMP where EMPNO=7369;
error> select ENAME, COMM from EMP
where SAL=(select SAL, COMM from EMP where EMPNO=7369);
-> 컬럼이 여러개 return 되었기 때문(복수컬럼)
//평균급여보다 많은 급여의 사원의 이름과 급여 출력!
SQL> select ENAME, SAL from EMP
where SAL>(select AVG(SAL) from EMP);
//생각해 보자
SQL> select DNAME,
2 (select SAL from EMP where EMPNO=7900)
3 from DEPT where DEPTNO=10;
출력은 되지만, MAIN과 SUB쿼리의 연관관계가 없기에
의미없는 결과이다.
<2> 복수행 Sub-Query
-> Sub-Query의 실행결과가 여러개의 행을 return
하는 경우의 쿼리
(연산자 IN[=ANY], ANY, EXISTS,
ALL[>:가장큰값,<:가장작은값,=:값이한개일때])
1> IN
SUB> select SAL from EMP where DEPTNO=10;
SQL> select ENAME, SAL from EMP
2 where SAL IN(select SAL from EMP where DEPTNO=10);
SQL> select ENAME, SAL from EMP
where SAL=2450 OR SAL=5000 OR SAL=1300;
2> ALL
//급여가 가장 많은 'MANAGER'보다 급여가
같거나, 많은 사원의 JOB, SAL 을 출력!
SUB> select SAL from EMP where JOB='MANAGER';
SQL> select JOB, SAL from EMP where SAL>=
ALL(select SAL from EMP where JOB='MANAGER');
효과> select JOB, SAL from EMP where
SAL>=2975 AND SAL>=2850 AND SAL>=2450;
//급여가 가장 적은 'MANAGER'보다 급여가
적은 사원의 JOB, SAL 을 출력!
SUB> select SAL from EMP where JOB='MANAGER';
SQL> select JOB, SAL from EMP where SAL<
ALL(select SAL from EMP where JOB='MANAGER');
효과> select JOB, SAL from EMP where
SAL<2975 AND SAL<2850 AND SAL<2450;
//급여가 가장 적은 'MANAGER'와 급여가
같은 사원의 JOB, SAL 을 출력!
SUB> select SAL from EMP where JOB='MANAGER';
SQL> select JOB, SAL from EMP where SAL=
ALL(select SAL from EMP where JOB='MANAGER');
효과> select JOB, SAL from EMP where
SAL=2975 AND SAL=2850 AND SAL=2450;
3> ANY
//급여가 가장 큰 'SALESMAN'보다 급여가 작은 사원의
JOB, SAL을 출력!
SUB> select SAL from EMP where JOB='SALESMAN';
SQL> select JOB, SAL from EMP
where SAL<ANY(select SAL from EMP where JOB='SALESMAN');
효과> select JOB, SAL from EMP where
SAL<1600 OR SAL<1250 OR SAL<1500;
4> EXISTS
//'부서번호'가 10인 사원이 존재하면
모든 부서의 이름을 출력!
SUB> select * from DEPT where DEPTNO=10;
SQL> select DNAME from DEPT
2 where EXISTS(select * from DEPT where DEPTNO=10);//true
SQL> select DNAME from DEPT
2 where EXISTS(select * from DEPT where DEPTNO=50);//false
<3> 복수컬럼 Sub-Query
-> Sub-Query의 실행결과가 여러개의 컬럼 값(
AND 여러개의 행)을 리턴해 주는 쿼리
//'부서번호'가 30인 사원의 SAL과 COMM이
같은 사원들의 '이름'과 '부서번호'를 출력!
SUB> select SAL, COMM from EMP where DEPTNO=30;
SQL> select ENAME, DEPTNO from EMP
2 where (SAL, COMM)
3 IN(select SAL, COMM from EMP where DEPTNO=30);
비교> select ENAME, DEPTNO from EMP
where (SAL, NVL(COMM, -1))
IN(select SAL, NVL(COMM, -1) from EMP where DEPTNO=30);
<4> 상호관련 Sub-Query
-> Main-Query절에 사용된 테이블이 Sub-Query절에 다시
재사용되는 경우의 서브쿼리
SUB> select AVG(e1.sal) from EMP e2, EMP e1
2 where e2.DEPTNO=e1.DEPTNO; //독립적 수행 쿼리
SUB-error> select AVG(sal) from EMP e2
2 where e2.DEPTNO=e1.DEPTNO; //독립적 수행 안되는 쿼리
SQL> select e1.EMPNO, e1.SAL from EMP e1
2 where SAL>(select AVG(sal) from EMP e2
where e2.DEPTNO=e1.DEPTNO); //수행
///////////////////////// 문제 3 ///////////////////////////
5. DML ( Data Manipulation Language )
(1) 설명
테이블내의 데이터를 입력,수정,삭제할 수 있는 SQL언어
(2) 종류
1) INSERT 문
-> 테이블의 데이터를 저장(입력)할 때 사용
SQL> insert into EMP values(8888,
'이현록', 'ANALYST', 7788, SYSDATE, 2400, 100, 30);
SQL> insert into EMP values(9000, '이효준', SYSDATE);
error> insert into EMP(ENAME) values('홍길동');
2) UPDATE 문
-> 테이블의 데이터를 변경(수정)할 때 사용
SQL> select * from EMP where EMPNO=8888 or EMPNO=9000;
SQL> update EMP set ENAME='서은경', JOB='학생' where EMPNO=8888;
SQL> update EMP set EMPNO=8999 where EMPNO=8888;//pk field도 변경가능
error> update DEPT set DEPTNO=50 where DEPTNO=30;
//불가 - 30번부서를 참조하는 자식 ROW가 있기에
SQL> update DEPT set DEPTNO=50 where DEPTNO=40;
//가능 - 40번 부서의 사원이 없기에
3) DELETE 문
-> 테이블의 데이터를 삭제할 때 사용
SQL> delete from EMP where ENAME='서은경';
SQL> delete from EMP where EMPNO=9000;
상동> delete from EMP
where EMPNO=(select EMPNO from EMP where ENAME='이효준');
SQL> insert into EMP(EMPNO, ENAME) values(1111, '이효준');
SQL> insert into EMP(EMPNO, ENAME) values(2222, '이효준');
SQL> delete from EMP where
2 EMPNO=(select EMPNO from EMP where ENAME='이효준');//불가
//여기서.. 실습을 해보자
<1> TABLE 생성
SQL> create table DMLTEST(
2 NO number not null,
3 NAME varchar2(20),
4 ADDR varchar2(40));
<2> TABLE 삭제
SQL> drop table DMLTEST;
<3> INSERT 테스트
SQL> insert into DMLTEST values(10, '허인정', '경기도');
SQL> insert into DMLTEST values(20, '박민형', '서울시');
SQL> insert into DMLTEST values(30, '김태우', '대전시');
<4> UPDATE 테스트
SQL> update DMLTEST set NAME='정상화' where NO=10;
<5> DELETE 테스트
SQL> delete from DMLTEST where NO=20;
cf1) drop table DMLTEST;
purge recyclebin; //백업파일삭제
cf2) SQL> @E:\SOO\oracle\temp\dml\dmltest.sql
(3) INSERT 문
1) 입력시 제약사항
<1> 한번에 하나의 행만 입력할 수 있다.
<2> insert절에 명시된 컬럼갯수와
values 절의 갯수는 같아야 한다.
cf) insert절에 명시되지 않은 경우에는
테이블의 모든 컬럼갯수가 적용
<3> 입력될 값의 데이터타입과 컬럼타입은 같아야 한다.
error> insert into DMLTEST(NO, ADDR) values('one', '서울');
cf) 자동 converting
- NUMBER 타입의 '숫자형태의 문자열'입려시
자동으로 컨버팅(문자열 DATA -> NUMBER DATA) 기능
- 문자열타입(VARCHAR2)에 '숫자'를 입력시
자동으로 컨버팅(NUMBER DATA -> 문자열 DATA) 기능
<4> 입력될 값의 크기는 컬럼의 크기보다 크지 않아야 한다.
SQL> create table DMLTEST(
NO number(3) not null,
NAME varchar2(20),
ADDR varchar2(40));
SQL> insert into DMLTEST values(999, '다라', '인천');
SQL> insert into DMLTEST values(-999, '가나', '서울');
error> insert into DMLTEST values(1000, '가나', '서울');//불가
SQL> insert into DMLTEST values(10.89, '다라', '인천');//11
SQL> insert into DMLTEST values(10.12, '다라', '인천');//10
SQL> insert into DMLTEST values(-10.89, '다라', '인천');//-11
cf) VARCHAR2 : 4000 byte 저장가능
NUMBER : +, - 38 자리수의 정수형 저장 가능
( 소수 입력시에는 반올림해서 정수형으로 저장)
SQL> create DMLTEST(NO number, NAME varchar2);
SQL> create table DMLTEST(NO number(5,2), NAME varchar2(20));
SQL> insert into DMLTEST values(100, '길동');//가능
error> insert into DMLTEST values(1000, '길동');//불가
SQL> insert into DMLTEST values(12.34567, '길동');//12.35
cf) NO number(5,2)는 총 5자리인데, 그 중에
소수 2자리까지만 입력됨(소수 3째 자리에서 반올림)
<5> NULL에 주의해야 한다.
SQL> create table DMLTEST(NO number(3), NAME varchar2(20));
SQL> insert into DMLTEST values(null, null);//가능
SQL> create table DMLTEST(NO number(3) not null , NAME varchar2(20));
SQL> insert into DMLTEST values(null, null);//불가
SQL> create table DMLTEST(NO number(3) primary key, NAME varchar2(20));
SQL> insert into DMLTEST values(null, 'aa'); //불가
cf)create table DMLTEST(NO number(3) unique UK_TEST, NAME varchar2(20));
<6> 문자와 날짜타입의 컬럼에는 ''을 사용하는 것이 원칙
SQL> create table DMLTEST(NO number, NAME varchar2(20), d date);
SQL> insert into DMLTEST values(10, '홍길동', SYSDATE);
SQL> insert into DMLTEST values(20, '이순신', '99/11/28');
SQL> select TO_CHAR(d, 'YYYY-MM-DD AM HH:MI:SS DAY') from DMLTEST;
SQL> select * from DMLTEST where d>'10/05/11';
SQL> select * from DMLTEST where d<'10/05/11';
<7> 참조무결성을 고려해야 한다.
SQL> insert into EMP(EMPNO, DEPTNO, ENAME)
values(1111, 40, '이선희');//가능
SQL> insert into EMP(EMPNO, DEPTNO, ENAME)
values(2222, 90, '이선희');//불가
//DEPT과 EMP은 부모간의 참조관계(FK)에 있기 때문
2) 치환변수를 이용한 데이터 입력
<1> 설명
치환변수는 사용자로부터 입력받은 값을 SQL문장간에
전달하기 위한 변수
<2> 사용위치
WHERE절, SELECT절, ORDER BY절, 테이블 이름, VALUES절
<3> 종류
1> & : SQL문장이 실행될 때마다 새로운 값을 요구
(즉, 유효범위 => SQL문 )
[ex1]
SQL> select EMPNO, JOB from EMP where DEPTNO=&dno;
dno의 값을 입력하십시오: 10
[ex2]
SQL> select EMPNO, JOB from EMP where JOB=&j;
j의 값을 입력하십시오: 'MANAGER'
SQL> select EMPNO, JOB from EMP where JOB=&j;
j의 값을 입력하십시오: 'manager'
[ex3]
SQL> insert into DEPT values(&no, &dn, &lo);
no의 값을 입력하십시오: 60
dn의 값을 입력하십시오: '영업부'
lo의 값을 입력하십시오: '서울'
2> && : 기실행된 치환변수값이 다음 실행시에도 유효
(즉, 유효범위 => 하나 세션 )
[ex]
SQL> select EMPNO, JOB from EMP where JOB=&&j;
j의 값을 입력하십시오: 'SALESMAN'
SQL> insert into DMLTEST values(10, &&j, SYSDATE);
//이미 j가 셋팅
3> ACCEPT : 메세지와 함께 치환변수를 입력
( 유효범위 => 하나 세션 )
SQL> accept depart prompt '부서번호를 입력하세요'
부서번호를 입력하세요60
SQL> accept depart prompt '부서번호가 뭐니?'
부서번호가 뭐니?50
SQL> insert into DEPT values(&depart, '총무부', '대전');//가능
SQL> select * from DEPT where DEPTNO=&depart; //가능
3) SUB-QUERY를 이용한 데이터 입력(*****)
//DEPT테이블을 복사해서 DEPT2 테이블을 만드세요.
SQL> create table DEPT2(DEPTNO number(2) not null,
DNAME varchar2(14), LOC varchar2(13));
SQL> insert into DEPT2 select * from DEPT;//복사
SQL> delete from DEPT2;
SQL> insert into DEPT2(DEPTNO, LOC)
select DEPTNO, LOC from DEPT; //DEPTNO, LOC에만 데이터복사
error> insert into DEPT2(DEPTNO, LOC)
select DNAME, LOC from DEPT; //컬럼매칭이 틀림
//EMP테이블을 EMP2을 만들어 복사하세요.
(4) UPDATE 문
1) 수정시 제약사항
<1> where문을 항상 고려해야 한다.
<2> where를 생략하면 테이블의 모든 데이터가 변경된다.
SQL> update DEPT set DNAME='마케팅부';
<3> 참조무결성을 고려해야 한다.
SQL> create table DMLTEST(
NO number(3) constraint UK_TEST unique, NAME varchar2(20));
SQL> select CONSTRAINT_NAME, CONSTRAINT_TYPE
from user_constraints;
확인> FK_DEPTNO (R)
error> update DEPT set DEPTNO=90 where DEPTNO=30;//불가
//EMP사원중에서 deptno=30인 사원이 존재하므로
SQL> update DEPT set DEPTNO=90 where DEPTNO=40;//가능
//EMP사원중에서 deptno=40인 사원이 없으므로
SQL> update DEPT set DNAME='회계부' where DEPTNO=30;//가능
//deptno=30인 사원이 존재하더라도 다른 컬럼은 변경가능
cf) 자식이 참조하고 있더라도 부모가 수정가능한 방법
1> FK 삭제 ( 부자 인연 끊기 )
2> 참조 자식 모두 삭제
3> 참조 자식 다른 부모를 셋팅
SQL> update EMP set DEPTNO=10 where DEPTNO=30;
2) 데이터 수정의 2가지 방법
<1> 일반적인 update문 사용법
<2> SUB-QUERY를 이용한 UPDATE문 사용법
SUB> select JOB, DEPTNO from EMP where EMPNO=7369;
SQL> update EMP set(JOB, DEPTNO)
=(select JOB, DEPTNO from EMP where EMPNO=7369);
cf) DML을 DB에 적용하기 위해서는 TCL를 써줘야 함
만약, TCL을 쓰지 않고 sqlplus를 종료(세션을 종료)
하면 commit이 될 수도 있고, 안될 수도 있다.
- commit이 되고 종료 : 안전종료 즉, exit;
- commit이 안되고 종료 : 불안전종료 즉, 세션을 왼쪽상단X
(5) DELETE 문
1) 삭제시 주의사항
<1> where 절을 항상 고려해줘야 한다.
<2> 참조무결성을 고려해야 한다.
error> delete from DEPT where DEPTNO=10; //불가 - 자식유
SQL> delete from DEPT where DEPTNO=40; //가능 - 자식무
참고)SQLPlus창을 청소 : SQL> $cls
6. TCL ( Transaction Control Language )
(1) 설명
DML문의 결과를 영구적으로 DBMS에 반영할지 여부를 결정하는 SQL
즉, INSERT, UPDATE, DELETE 문의 실행결과를 DBMS저장하거나,
되돌리기 위해서 실행해야 하는 SQL문
(2) Transaction 설명
1) 정의
분리되어서는 안 될 (논리적)작업단위
2) 시작
<1> DBMS에 처음 접속했을 때
<2> 하나 or 여러개의 DML문을 실행한 후 commit 또는
rollback을 수행한 직후
3) 끝
<1> commit 이나 rollback을 실행하는 순간
예외) rollback to a;처럼 savepoint 까지의 rollback은
Transaction의 끝이 아님[진행중](즉, 영구저장X)
<2> DDL문이나 DCL(Data Control Language)문이 실행되는 순간
<세션1>
SQL> insert into EMP(EMPNO, ENAME) values(4444, 'SSS');
SQL> insert into EMP(EMPNO, ENAME) values(5555, 'DDD');
SQL> create table AAA(NO number);
<세션2>
SQL> select * from EMP;//세션1의 insert data확인
<3> DBMS가 (정상/비정상) 종료될 때
<세션1> 세션 정상종료( commit됨 )
SQL> insert into DEPT2 values(70, '국방부', '부산');
SQL> quit; 또는 exit;
<세션2>
SQL> select * from DEPT2; //확인
<세션1> 세션 비정상종료 ( rollback 됨 )
SQL> insert into DEPT2 values(80, '산자부', '부산');
SQL> quit; 또는 exit;
<세션2>
SQL> select * from DEPT2; //존재하지 않음
<세션1>
SQL> insert into DEPT2 values (50, '정통부', '대구');
<세션2> DBMS 종료 ( rollback 됨 )
sqlplus "/as sysdba"
SQL> shutdown immediate; //DBMS process 종료
SQL> startup //DBMS process 시작
<세션2>
SQL> select * from DEPT2; //존재하지 않음
<4> 작업 Connection(세션)을 끊을 때
1> 다른 세션으로 로그인 할 때 ( commit 됨 )
<세션1>
SQL> insert into DEPT2 values(80, '여성부', '인천');
SQL> conn system/java6 as sysdba;
<세션2>
SQL> select * from DEPT2; //존재함
2> sqlplus상에서 exit; 또는 quit; 를 쳐서 끊을 때
( 위와 중복 )
(3) TCL 의 종류
1) commit;
DML문의 결과를 영구적으로 DBMS에 반영하는 SQL문
2) rollback;
DML문의 결과를(이전 Transaction 끝까지) 취소하는 SQL
3) savepoint
트랜젝션의 한 지점에 표시하는 임의 저장점
(4) TCL의 관련 특성
1) READ CONSISTENCY (읽기 일관성 - 해당 row에 대해서 )
어떤 사용자가 변경 중인 행을 다른 사용자가 변경
할 수 없게 하는 기술로써 변경중인 사용자에 의해
commit 이나 rollback이 실행된 후에 변경가능한 특성
<세션1>
SQL> update DEPT2 set DNAME='남성부' where DEPTNO=80;
SQL> commit; 또는 rollback;
SQL> 1행 갱신
<세션2>
SQL> update DEPT2 set DNAME='중성부' where DEPTNO=80;//실행보류
SQL> 1행 갱신
cf) 다른 행(row)에서는 READ CONSISTENCY 적용 X
2) LOCK ( 잠금성 )
다른 사용자에 의해 DML문이 실행되어져 동일한 데이터를
insert, update, delete 할 때 한 쪽 사용자는 LOCK걸리는 특성
<1> ROW-LEVEL LOCK // 1)의 예와 동일
<2> TABLE-LEVEL LOCK
<세션1>
SQL> delete from DEPT2; //테이블 전체 row에 lock걸림
<세션2>
SQL> update DEPT2 set DNAME='AAA' where DEPTNO=80;//보류
SQL> update DEPT2 set DNAME='AAA' where DEPTNO=70;//보류
cf) LOCK을 해제시키는 방법
-> commit; 또는 rollback;
/////////////// 숙제 /////////////////////////////////////
1. 게시판 테이블을 만들어서 오늘 배운부분예 대한 예제를
적용해 오세요 ( create, insert, update, delete, drop )
2. CASCADE 옵션에 대한 예제를 만들어 오세요.
(1) DELETE
(2) DROP
3. EMP2와 DEPT2 테이블을 생성하고, 데이터를 복사해서
CASCADE 옵션을 테스팅해 오세요..
///////////////////////////////////////////////////////////
