Database D+5

database D+5

 

## DML

 

- 데이터 조작어

- 생성되어 있는 테이블 자료를 입력, 수정, 삭제

- CRUD : 추가, 조회, 변경, 삭제

- 로그가 남는다 : 작업내용 로그를 기반으로 취소할 수 있다.

 

ST_DEPT ( DEPT_NO, DEPT_NAME)

ST( S_NO, NAME, BIRTH, DEPT_NO )

 

CREATE TABLE ST_DEPT (

DEPT_NO NUMBER(2) PRIMARY KEY,

DEPT_NAME VARCHAR2(50) NOT NULL

);

 

CREATE TABLE ST (

S_NO CHAR(8) PRIMARY KEY,

NAME VARCHAR2(30) NOT NULL,

BIRTH DATE,

DEPT_NO NUMBER(2) REFERENCES ST_DEPT (DEPT_NO )

);

 

## 자료 입력

-- ST_DEPT

-- INSERT

-- 컬럼리스트의 수와 값리스트의 수 일치

-- 컬럼의 데이터타입과 값의 테이터 타입 일치

-- INSERT INTO 테이블명 ( 컬럼명, ....) VALUES (값1, .... ) ;

 

INSERT INTO ST_DEPT ( DEPT_NO, DEPT_NAME ) VALUES ( 10, '국문과' ) ;

INSERT INTO ST_DEPT VALUES ( 20, '영문과' ) ;

INSERT INTO ST_DEPT VALUES ( 30, '수학과' ) ;

INSERT INTO ST_DEPT VALUES ( 40, '멀티과' ) ;

 

INSERT INTO ST (S_NO, NAME, BIRTH, DEPT_NO)

VALUES ( '20201234', '연문숙','2020-10-10', 10 ) ;

 

INSERT INTO ST VALUES ( '20201111', '송중기','1900-10-10', 20 ) ;

 

학번, 이름, 학과번호 입력

-- 생일 컬럼 생략하고 입력

-- 생략된 컬럼이 NOT NULL 제약조건이 아니면 가능

 

INSERT INTO ST (S_NO, NAME, DEPT_NO)

VALUES ( '20202222', '아이유', 30 ) ;

 

INSERT INTO ST VALUES ( '20203333', '수지',NULL, 20 ) ;

 

## 다른 테이블에서 자료를 읽어서 왕창 입력하는 방법

 

INSERT INTO ST

SELECT EMPLOYEE_ID, LAST_NAME, HIRE_DATE, DEPARTMENT_ID

FROM EMP

WHERE DEPARTMENT_ID IN ( 10,20,30,40 ) ;

 

================================================

 

## UPDATE

## row 의 특정 컬럼의 값을 변경

 

1. 지정한 컬럼에 원하는 값을 지정 => 모든 row 값이 변경

2. 조건에 맞는 row 에 대해서 지정한 컬럼에 원하는 값을 지정 => 일반적 사용법

3. WHERE 절 조건 컬럼은 유니크 컬럼으로 하는 것이 좋다

 

UPDATE ST

SET DEPT_NO = 30 ;

=> 모든 row 값이 변경

 

UPDATE ST

SET DEPT_NO = 30

WHERE NAME ='수지' ;

=> 수지의 부서번호를 30으로 변경

 

 

UPDATE ST

SET DEPT_NO = 30 , BIRTH = '2000-10-10'

WHERE NAME ='수지';

=> 수지의 부서번호를 30으로 변경

 

--수지의 학과번호를 송중기와 같게 변경하시오.

 

UPDATE ST

SET DEPT_NO = ( SELECT DEPT_NO FROM ST WHERE NAME='송중기')

WHERE NAME ='수지' ;

 

===================================================

 

## ROW 삭제

## DELETE

 

1. 모든 ROW 삭제

2. 조건에 맞는 ROW만 삭제 => ***

 

DELETE ST ;

DELETE FROM ST ;

=> 테이블에 INSERT 한 모든 자료가 삭제 !! 주의!!

 

DELETE ST

WHERE BIRTH < '2005-01-01' ;

 

==================================================

 

## DML 작업이 안되는 경우

 

## INSERT

ST_DEPT 테이블에 INSERT 작업 => OK

ST 테이블에 INSERT 작업 => 항상 되는 것은 아니다.

 

INSERT INTO ST VALUES ( '20203333', '수지','2000-10-10', 77 ) ;

=> violated - parent key not found 오류 발생!!

 

## UPDATE

ST_DEPT 테이블에 UPDATE 작업 => 항상 되는 것은 아니다

 

UPDATE ST_DEPT

SET DEPT_NO = 35

WHERE DEPT_NAME = '수학과' ;

 

=> violated - child record found 오류발생 !!

=> 자식테이블 ( ST ) DEPT_NO 컬럼에서 30 없앤다. -> null

-> ST_DEPT DEPT_NO 30-> 35 수정

-> ST_DEPT DEPT_NO NULL -> 35

 

UPDATE ST

SET DEPT_NO = null

WHERE DEPT_NO = 30 ;

 

UPDATE ST_DEPT

SET DEPT_NO = 35

WHERE DEPT_NO = 30 ;

 

UPDATE ST

SET DEPT_NO = 35

WHERE DEPT_NO IS NULL ;

 

## DELETE

ST_DEPT 테이블에 DELETE 작업 => 항상 되는 것은 아니다

 

DELETE ST_DEPT

WHERE DEPT_NO=35 ;

 

=> constraint (AD40.SYS_C00198137) violated - child record found 오류발생!!

 

 

 

## VIEW

 

-- 가상의 테이블

-- 목적 :

보안 ( 컬럼에 대한 접근 제한 ),

편리성 ( 쿼리문이 길고 복잡 => VIEW 생성 )

-- 쿼리문을 저장하는 객체

 

##뷰 생성

 

CREATE VIEW EMP60_VU

AS

SELECT LAST_NAME, SALARY

FROM EMP

WHERE DEPARTMENT_ID = 60;

 

CRATE OR REPLACE VIEW 뷰명

AS

SELECT 문;

 

## 뷰 확인

 

--USER_VIEWS

 

SELECT * FROM USER_VIEWS;

 

## 읽기전용 뷰

--뷰를 통해서 DML작업을 할 수 없다

 

CREATE VIEW EMP60_VU

AS

SELECT LAST_NAME, SALARY

FROM EMP

WHERE DEPARTMENT_ID = 60;

WITH READ ONLY;

 

##뷰를 통한 DML 연산

CREATE TABLE TT5 ( A,B,C )

AS

SELECT LAST_NAME, SALARY, DEPARTMENT_ID

FROM EMP

WHERE DEPARTMENT_ID = 60 ;

 

CREATE VIEW TT5_VU

AS

SELECT A, B

FROM TT5;

 

SELECT * FROM TT5_VU;

 

 

INSERT INTO TT5_VU( A, B) VALUSE ( 'AAA', 5555) ;

 

CREATE VIEW TT55_VU

AS

SELECT LAST_NAME, SALARY, DEPARTMENT_ID

FROM EMP;

 

CREATE VIEW TT55_VU

AS

SELECT C, SUM(B) AS B_SUM

FROM TT55

GROUP BY C

ORDER BY C ;

 

INSERT INTO TT55_VU VAULES (999, 1000) ;

=> 오류 !!

 

 

##시퀀스

--일런번호 발생기 객체

--PK 용 주로 사용

--시퀀스 번호가 입력될 경우 일관성을 보장하지 않는다.

 

 

시퀀스의 특징

-자동적으로 유일 번호를 생성

-공유 가능한 객체

-주로 기본키 값을 생성하기 위해 사용

 

##시퀀스 생성

 

create sequence test_pk_seq ;

 

-- 1부터 1씩 무한대로 증가하는 시퀀스 생성

-- 옵션

 

create sequence test_pk_seq

start with 1

increment by 1

maxvalue 99999

 

 

##시퀀스 수정

--start with 제외하고 수정가능

 

alter sequence test_pk_seq

maxvalue 88888

increment by 10;

 

## 시퀀스 확인

select * from user_sequences ;

 

##시퀀스 사용

--시퀀스명, nextval ->주로사용!

 

--시퀀스명. currval

 

create table test (

a number(5), primary key,

b varchar2(20)

c varchar2(20)

);

 

 

create sequence test_pk_seq

start with 1

increment by 1

maxvalue 99999

nocycle;

 

insert into test (a, b, c)

values (test_pk_seq.nextval, 'aa', 'bb') ;

select * from test ;

 

주문번호 : 20210503_00001

 

 

create table test1 (

a char(14), primary key,

b varchar2(20)

c varchar2(20)

);

 

CREATE SEQUENCE TEST1_PK_SEQ ;

 

 

INSERT INTO TEST1

VALUES (TO_ CHAR(SYSDATE, 'YYYYMMDD') || '_'||

LPAD (TEST1_PK_SEQ.NEXTVAL, 5, 0 ), 'AA', 'BB' );

 

## 시퀀스 안쓰고 일련번호 만들기

 

insert into test (a, b, c)

values (test_pk_seq.nextval, 'aa', 'bb') ;

 

-------------------------------

insert into test (a, b, c)

values ( max(a) + 10, 'aa', 'bb') ; ===> 오류발생

 

insert into test (a, b, c)

values ( (select max(a) + 10 from test), 'aa', 'bb') ;

 

 

=+=+=+=+=+=+=+=+=+

 

## INDEX

 

인덱스 생성 지침

 

1)언제 인덱스를 생성하면 좋지 않은가?

 

작은 테이블

질의의 조건으로 자주 사용되지 않는 컬럼

대부분의 질의 결과 전체 행의 2~4% 이상을 검색

자주 갱신되는 테이블

 

인덱스를 사용하는 이유

--검색 속도 향상

--기본키와 유일키는 자동으로 인덱스 생성이 된다

## INDEX 생성

 

CREATE TABLE EMP

AS

SELECT *FROM HR.EMP;

 

CREATE TABLE EMP1

AS

SELECT *FROM HR.EMP;

 

CREATE INDEX EMP_ID_IDX ON EMP (EMPLOYEE_ID) ;

 

 

SELECT *

FROM EMP

WHERE EMPLOYEE_ID = 105;

 

==> 0.007

 

SELECT *

FROM EMP1

WHERE EMPLOYEE_ID = 105;

 

 

==> 0.0015

 

 

## INDEX 삭제

 

DROP INDEX 인덱스명 ;

 

## INDEX 확인

 

SELECT * FROM USER_IND_COLUMNS ;

 

 

## INDEX 재건

 

ALTER INDEX EMP_ID_IDX REBUILD;

 

##INDEX 종류 : 성격에 따른 분류

 

고유인덱스: 기본키, 유일키, 부여되는 인덱스 ->

오라클이 자동 생성

비유고유인덱스 : 사용자 생성한 인덱스 ->

검색조건에 자주 사용하는 컬럼 주로 생성

 

##INDEX 종류 : 형태에 따른 분류

 

--단일 인덱스 : 컬럼 하나

 

--결합인덱스 : 컬럼 두개이상

 

 

 

-- 함수기반인덱스 : 검색조건 왼쪽 부분이 계산식 또는 함수 썻거나...

 

 

 

 

SELECT *

FROM EMP

WHERE SALAY * 12 >= 4000; => 함수기반 인덱스를 만 들어줘야한다

 

CREATE INDEX EMPSAL12_IDX N EMP(SALARY * 120 ;)

SELECT *

FROM EMP

WHERE UPPER(EMAIL) = 'ABC' ;

==> EMAIL 컬럼에 인덱스가 있어도 변형되었으므로 인덱스로 사용하지 않는다

==> 함수기반인덱스 생성해야 한다.

 

CREATE INDEX EMPUP_IDX ON EMP( UPPER(EMAIL) );

 

 

 

==================================================

 

## 시노임

- 긴 객체명을 편하게 사용하기 위한 수단

 

## 시노임 생성

CREATE STNONYM 시노임 이름 FOR 원래객체명 ;

 

 

 

CREATE STNONYM E FOR EMP;

 

CREATE[PUBLIC / PRIVATE] SYNONYM 시노임이름 FOR 원래객체명;

 

PUBLIC: 모든 사용자 접근 가능

PRIVATE: 특정 사용자(오너) 접근 가능 => 기본값

 

 

## 시노임 확인

 

SELECT * FROM USER_SYNONYMS;

 

 

 

##SET 연산자

CREATE TABLE JOB_HISTORY

AS

SELECT * FROM HR.JOB_HISTORY ;

 

SELECT EMPLOYEE_ID, JOB_ID

FROM EMP

UNION

SELECT EMPLOYEE_ID, JOB_ID

FROM JOB_HISTORY ;

==>

 

CREATE TABLE TT7

AS

SELECT EMPLOYEE_ID, JOB_ID

FROM EMP

UNION

SELECT EMPLOYEE_ID, JOB_ID

FROM JOB_HISTORY ;

 

 

CREATE TABLE TT7_VU

AS

SELECT EMPLOYEE_ID, JOB_ID

FROM EMP

UNION

SELECT EMPLOYEE_ID, JOB_ID

FROM JOB_HISTORY ;

 

 

 

SELECT EMPLOYEE_ID, JOB_ID

FROM EMP

UNION ALL

SELECT EMPLOYEE_ID, JOB_ID

FROM JOB_HISTORY ;

 

UNION ALL -> 정렬이 안된다

 

===================================

## 현재 종사중인 직종에 과거에더 종사한 경험이 있는 직원 검색

 

 

SELECT EMPLOYEE_ID, JOB_ID

FROM EMP

UNION ALL

SELECT EMPLOYEE_ID, JOB_ID

FROM JOB_HISTORY ;

 

## 직종을 한번도 변경하지 않은 직원 검색

 

SELECT EMPLOYEE_ID, JOB_ID

FROM EMP

INTERSECT

SELECT EMPLOYEE_ID, JOB_ID

FROM JOB_HISTORY ;

 

==> 현재 직종 - 과거직종

==> 현재 직종 - 없음 => 직종 변경하지 않았다

 

 

## 컬럼의 갯수와 타입을 맞추는 방법

 

SELECT DEPARTMENT_ID, TO_NUMBER (NULL) AS LOCATION, HIRE_DATE

FROM EMP

UNINON

SELECT DEPARTMENT_ID LOCATION_ID, TO_DATE (NULL)

FROM DEPT ;

 

SELECT DEPARTMENT_ID, ?? , HIRE_DATE

FROM EMP

UNINON

SELECT DEPARTMENT_ID LOCATION_ID, ??

FROM DEPT ;