오라클 수업정리 4일차

1교시

클라이언트 SQL DB

QUEARY : SELECT

종합문제

 SELECT RPAD(ename, 8, ' ') || lpad(' ',ROUND(SAL/100,0)+1, '*')
           || '(' || SAL || ')' "사원별 급여 현황"
 FROM emp
- 영업사원 판매현황시 적용

 

DML (Data Manipulation Language) : 데이터 조작 언어

종류 : INSERT, UPDATE, DELETE

INSET : 테이블 ROW 추가

INSERT INTO 

VALUES (   )

 

ㅇ 암시적 삽입법

INSER INTO DEPT(DEPTNO, DNAME)

VALUES ( 60, 'C');

지정되지 않는 컬럼은 NULL값 대입

 

ㅇ 명시적 삽입법

INSERT INTO DEPT

VALUES (70, NULL, 'D')

주의 'NULL' 문자가 됨

 

UPDATE : 테이블 ROW 변경

update dept

set dname = 'A'

where deptno = 70;

 

DELETE : 테이블 삭제

delete dept

where dept in(50, 60 ,70);

 

3교시

TCL (Transaction Control Language) : 데이터 제어 언어

종류 : COMMIT, ROLLBACK

- 트랜잭션 : 일련의 작업처리를 위한 연관된 DML의 모음

  ㅇ 성격 : ALL or NOTHING ex) A은행 => B은행 입출금

- commit 명령어를 쓰기전까지 DB에 반영 안 됨.

  ㅇ DB는 여러 사람, 어플리케이션이 동시에 작업(무결성 유지)

- TCL을 만나면 현재 트랜잭션을 종료

  ㅇ 다음번 첫 SQL문을 만나면 새로운 트랜잭션이 시작

- 비정상 종료 AUTO ROLLBACK / 정상 종료 AUTO COMMIT / CREATE

- 굉장히 긴 트랜잭션일 경우

  ㅇ SAVE POINT를 통해 부분 ROLLBACK 가능 ex)입력값 오류 / ANSI 표준 X

SAVEPOINT 1

 

SAVEPOINT 2

SAVEPOINT 3

SAVEPOINT 4

결과

SAVEPOIN로 ROLLBACK

- 한 번 뒤로 가면 다시 돌아 올 수 없다.

DDL(DATE DEFINITION LANGUAGE) : 데이터 정의어

종류 : CREATE, ALTER, DROP, RENAME, TRUNCATE, COMMENT

- DDL 사용시 AUTO COMMIT 발생 ROLLBACK 불가

 

CREATE

주의사항

1. 시작은 반드시 영문자이여야 한다.
2. 예약어(SELECT 등)는 사용할수 없다.
3. 유일한 단어이어야 한다. (중복이 되면 안된다)
4. _, $, #은 사용가능하다.

 

ㅇ TABLE 생성

1. EMPS 테이블 생성

2. 컬럼 EMPNO, ENAME, SAL, HIREDATE

3. 숫자 4자리 컬럼

4. 문자 가변형 문자 20자

  - CHAR 고정문자 ex) CHAR(8) ABC입력 남은공간 유지

    ㅇ 장점 : 읽는 속도가 빠름(길이 고정)

    ㅇ 단점 : 공간낭비가 있음.

  - VARCHAR2 가변문자 ex) VARCHAR2(8) ABC 입력 남은 공간 유지

    ㅇ 장점 : 공간효율면에서 우수함

    ㅇ 단점 : 읽는 속도가 느림.

ex) 회원가입을 통한 VARCHAR2 와 CHAR의 사용 예

5. number(7, 2) 7자리까지 표현하는데 5자리는 정수 2자리는 소수점 표현

 

ㅇ 전체복사(서브쿼리 활용)

CREATE(생성할 때) TABLE COPY_EMP2 (COPY_EMP2 테이블을)

AS (처럼 하여라)

SELECT * FROM EMP; (EMP의 모든 컬럼처럼)

 

ㅇ 테이블 구조 복사 ROW값 비우기 (3가지 방법)

1. WHERE EMPNO = 9999;

2. WHERE EMPNO = NULL

3. WHERE 0 = 1

 

ㅇ 테이블 구조 전체 입력값 넣기(INSERT문에 서브쿼리 활용)

 

ㅇ 부분복사(서브쿼리 + WHERE문 추가)

CREATE(생성할 때) TABLE COPY_EMP2 (COPY_EMP2 테이블을)

AS (처럼 하여라)

SELECT * FROM EMP; (EMP의 모든 컬럼처럼)

WHERE DEPTNO = 10;

 

ALTER ( 변경 )

- 컬럼을 추가(ADD), 수정(MODIFY), 삭제(DROP) 

 

ㅇ ADD : 테이블 COLUMN 추가

ALTER TABLE EMP

ADD (컬럼명 데이터타입(  )); 

 

ㅇ RENAME : 테이블 COLUMN명 변경

ALTER TABLE EMP

RENAME COLUMN 컬럼명 TO 바꿀 컬럼명;

 

ㅇ MODYFY : 테이블 COLUMN 데이터타입 변경

ALTER TABLE EMP

MODIFY (컬럼명 데이터타입(  ));

- 자료를 줄이는 건 불가

- 숫자형(NUMBER) => 문자형(CHAR) 속성 변경시

   컬럼 안에 데이터 있을 시 변경 불가(CHAR형과 VARCHAR2형 사이는 가능)

 

ㅇ DROP : 테이블 COLUMN 삭제

ALTER TABLE EMP

DROP COLUMN 컬럼명;

 

ㅇ 부하가 많이 걸리고, 명령 종료전까지 테이블 사용 불가

- 논리적 데이터 삭제(물리적 데이터 유지)

ALTER TABLE EMP
SET UNUSED(컬럼명);

 

- 물리적 데이터 삭제

ALTER TABLE test2
DROP UNUSED columns;

 

DROP( 테이블 삭제 )

- DDL은 AUTO COMMIT ROLLBACK이 안 됨

- 존재 자체를 지움

테이블 삭제 

DROP TABLE COPY_EMP;

테이블 완전 삭제

DROP TABLE COPY_EMP PURGE;

 

ㅇ FLASHBACK ( 테이블 삭제 복구 )

- 해당 테이블을 드랍하기 직전으로 돌려줌

- DB마다 조금씩 다름

FLASHBACK TABLE COPY_EMP

TO BEBORE DROP;

 

ㅇ SHOW RECYCLEBIN(휴지통) 

ㅇ PURGE RYCYCLEBIN(휴지통 비우기)

 

RENAME ( 테이블명 변경)

RENAME COPY_EMP2 TO CEMP;

 

TRUNCATE ( 모든 RAW를 삭제 )

TRUNCATE TABLE CEMP;

- TRUNCATE 모두 삭제, 일부 삭제 x DDL  조건 부여 불가, 백업 x 속도 빠름

  ㅇ 차이점 DML DELETE CEMP; : ROLLBACK 가능, 조건 부여 가능, 

 

COMMENT ( 주석 생성 )

- 4000자까지 가능

COMMENT ON TABLE EMP 
IS 'EMPLOYEE TABLE';

 

COMMENT 주석 보기

DESC USER_TABLE_COMMENTS

SELECT * 
FROM USER_TAB_COMMENTS;

 

 

 

용어

condition : 조건

 

TIP.

1. 폭 조절 ex) COL "근속 기간" FROMAT A15 SQLPLUS 명령어

2. 순차대로 진행하면서 규칙을 찾자

3. 서브쿼리 조건 개수 확인

4. sqlplus 로그인부터 종료까지 하나의 트랜잭션

5. 현재 접속자로 표기 

5. ACCESS(MDB) 통상 DB라 하지 않는다.

6. MS-DOS WIN3.1 ( WIN NT > WIN 2000 ) WIN95 SE WIN ME

   WIN XP(보안패치 단종, NTFS(서버급 보안기능) VISTA 7 8 10

7. DB는 10의 9승 1초 단위로 히스토리가 남는다.

 

과제 1

1. 사원번호 7499 번사원의 월급을 5000으로 바꿔라 ?

2. 20번 부서의 사원들의 월급을 4000 으로 바꿔라 ?

3. 부서 테이블에 부서번호 50, 부서위치 Boston , 부서명 reserch 로 해서 data 를 입력하라 ?

4. 사원번호 7698 번 사원의 부서 번호를 7499번 사원의 부서번호로 바꿔라 ?

5. 10 번 부서의 사원들의 커미션을 smith 의 커미션으로 바꿔라 ?

6. 사원 테이블에 data 를 집어 넣는데 사원번호를 9901 이라고 하고 이름을 jackson , 직업을 salesman ,

   부서번호를 10 번 이라고 해서 data 를 집어 넣어라 ?

7. job 이 salesman 인 사원의 사원번호와 이름 과 직업을 사원테이블에 insert 하여라 ?

8. 7369번 사원과 같은 직업을 가진 사원들의 월급을 7698 번 사원의 월급으로 수정하여라 ?

9. jackson 사원의 data 만 삭제 하여라 ?

10. data 를 집어 넣는데 사원 번호를 9902, 이름을 동수 , 그리고 입사일을 현재 날짜로 집어 넣어라 ?

 

과제2