8/11 [JAVA]- 예제 모음 [DataBase]-데이터 조작(DML)

[ java ]

 

새 게시물에 반복문 풀이를 정리했습니다

https://yuhyewon.tistory.com/21

반복문 예제 모음

[DataBase]

<<8.데이터 조작>>

DML -> DB운영 (무결성 보장) , 현재 시점 데이터를 유지해야 한다

 

트랜잭션 ->작업의 모음(단위), 일괄처리의 기준

commit -> 트랜잭션이 정상적으로 수행이 되었을 시 트랜잭션의 수행내용 전체를 한번에 데이터베이스에 물리적으로 영구히 저장

rollback -> 비정상적인 트랜잭션이 수행이 되었을시에 트랜잭션 이전 시점으로 취소

               -> 자동 수행 : 시스템이 비정상 종료

               -> 수동 수행: rollback 명령어 입력

 

[데이터 조작어]

 

* 다음 경우에 DML 문이 실행됩니다.

- 테이블에 새 행 추가

- 테이블의 기존 행 수정

- 테이블에서 기존 행 삭제

* 트랜잭션은 논리 작업 단위를 형성하는 DML 문의 모음으로 구성됩니다.

 

*DML(데이터 조작어)은 SQL의 핵심 요소로서 데이터베이스에 데이를 추가, 갱신 또는 삭제 할때 실행합니다. 논리 작업 단위를 형성하는 DML 문의 모음을 트랜잭션이라고 합니다.

예를들어, 은행 업무 데이터베이스에서 은행 고객이 보통 예금에서 당좌 예금으로 금액을 이체하는 경우 이 트랜잭션은 보통 예금 잔액 감소, 당좌 예금 잔액 증가 및 트랜잭션 저널(journal)에 트랜잭션 기록이라는 세 가지 작업으로 구성됩니다. orcale server는 이 세 가지 sql 문이 모두 수행되어 해당 계정에 정확한 잔액이 유지되도록 합니다. 트랜잭션 명령문 중 하나라도 실행되지 못하면 나머지 명령문의 실행도 취소되어야 합니다.

 

테이블에 새 행 추가 ->insert

테이블의 기존 행 수정 ->update

테이블에서 기존 행 삭제 -> delete

insert,delete -> 행 단위

update ->열 단위

 

[ INSERT문 구문]

* INSERT 문을 사용하여 테이블에 새 행을 추가합니다.

inser into table [(column [, column...])]
values (value [,value...]);

* 이 구문으로는 한 번에 한 행만 추가됩니다.

 

[테이블에 새 행 추가(계속)]

insert 문을 실행하여 테이블에 새 행을 추가할 수 있습니다.

구문 설명: table 테이블 이름입니다.

                 column 데이터를 삽입할 테이블의 열 이름입니다.

                 value 열 값입니다.

- 각 열의 값을 포함하는 새 형을 삽입할 수 있으므로 INSERT 절에 열 목록이 필요없지만 열 목록을 사용하지 않는 경우에는 값을 테이블의 기본 열 순서에 따라 나열해야 하며 모든 열에 대해 각각 값을 제공해야 합니다.

 

[새 행 삽입]

- 각 열에 대한 값을 포함하는 새 행을 삽입합니다.

- 테이블 열의 기본 순서대로 값을 나열합니다.

- insert 절에 열을 나열할 수 있습니다(선택사항)

insert into departments(department_id, department_name, manager_id, location_id)
values (70,'Public Relations', 100, 1700);
1 row created.

- 문자 및 날짜 값은 작은 따옴표로 묶습니다.

 

[널 값을 갖는 행 삽입]

- 암시적(implicit)방법: 열 목록에서 해당 열을 생략합니다.

- 명시적 방법: values 절에서 null 키워드를 지정합니다./ 문자열 및 날짜에 대해 values 목록에서 공백 문자열 (' ')을 지정 합니다.

 

[특정 값 삽입]

sysdate 함수는 현재 날짜 및 시간을 기록합니다.

insert into employees (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (113, 'Louis','Popp','LPOPP', '515.124.4567', sysdate, 'ac_account', 6900, null ,205,100);
1 row created.

위 코드는 employees 테이블에 사원 popp에 대한 정보를 기록합니다. 이 때 hire_date 열에 현재 날짜 및 시간을 제공하기 위해 sysdate 함수를 사용합니다.

 

[스크립트 작성]

- SQL 문에 & 치환을 사용하여 사용자에게 값을 요구합니다.

- &는 변수 값에 대한 위치 표시자 입니다.

insert into departments
            (department_id, department_name, location_id)
values      (&department_id, '&department name' ,&location);

 

[다른 테이블에서 행 복사]

-서브 쿼리를 포함하는 insert 문을 작성합니다.

insert into sales_reps(id, name, salary, commission_pct)
       select employee_id, last_name, salary, commission_pct
       from employees
       where job_id like '%REP%';
4 rows created.

- values 절은 사용하지 않습니다.

- insert 절의 열 수와 서브 쿼리의 열 수를 일치시킵니다.

 

[update문 구문]

- update 문을 사용하여 기존 행을 수정합니다.

update table
set column = value [,column = value, ...]
[where condition];

- 필요한 경우 한 번에 여러 행을 갱신합니다.

 

[테이블의 행 갱신]

- where 절을 지정하여 특정 행을 수정합니다.

update employees
set department_id=70
where employee_id = 113;
1 row updated.

- where 절을 생략하면 테이블의 모든 행이 수정됩니다.

update copy_emp
set department_id = 110;
22 rows updated

 

 

insert into table_name -> all column data 삽입 values(삽입할 data value)

insert into table_name(column_name) -> 명시된 col에 data 삽입

valuew(삽입할 data value)

1. into 절에 명시된 컬럼의 순서, 개수, 타입, 길이에 맞게 values절의 data가 반드시 1:1 매칭

 

insert는 column 무결성 검증한다

제약조건 "cpk,.fk"

 

into(col 생략) ->자동

values (null값 수동 명시)

 

오늘날짜 출력

SQL> select sysdate
  2  from dual;

SYSDATE
--------
22/08/11

 

 

[delete 문]

delete 문을 사용하여 테이블에서 기존 행을 제거할 수 있습니다.

delete(from) table
[where     condition];

 

 

[행 삭제: 무결성 제약 조건 오류]

다른 테이블에서 외래 키로 사용되는 기본 키를 포함하는 행은 삭제할 수 없습니다.

 

[commit 및 rollback 문의 장점]

commit 및 rollback 문으로 다음을 수행할 수 있습니다.

- 데이터 일관성을 보장합니다.

- 데이터 변경 내용을 영구히 저장하기 전에 미리볼 수 있습니다.

- 논리적으로 관련된 작업을 묶습니다.

 

 

* 참조값이 있으면 pk는 변경 불가능

 

* 병행제어 -> lock

 

*성능을 무결성을 할려면 테이블을 늘려야 한다

조인할 확률 높아짐

 

[트랜잭션 제어]

명령문	설명
commit	보류 중인 모든 데이터 변경 내용을 영구히 저장하고 현재 트랜잭션을 종료합니다.
savepoint name	현재 트랜잭션 내에 저장점을 표시합니다.
rollback	rollback은 보류 중인 모든 데이터 변경 내용을 버리고 현재 트랜잭션을 종료합니다.
rollback to savepoint name	rollback ro savepoint는 현재 트랜잭션을 지정된 저장점으로 롤백하여 롤백하는 저장점 이후에 생성된 모든 변경 내용 및 저장점을 버립니다. to savepoint 절을 생략할 경우 rollback 문은 전체 트랜잭션을 롤백합니다. 저장점은 논리적인 것이므로 작성한 저장점을 나열할 수는 없습니다.

 

[표시자까지 변경 내용 롤백]

- savepoint 문을 사용하여 현재 트랜잭션에 표시자를 생성합니다.

- rollback to saverpoint 문을 사용하여 해당 표시자 까지 롤백합니다.

 

[암시적(implicit) 트랙잭션 처리]

- 자동 커밋은 다음 상황에서 발생합니다.

   - DDL 문이 실행되는 경우

   - DCL 문이 실행되는 경우

   - commit 또는 rollback 문이 명시적(explicit)으로 실행되지 않은 채 isql*plus이 정상적으로 종료되는 경우

- 자동 롤백은 isql*plus 가 비정상적으로 종료되거나 시스템에 장애가 있을 때 발생합니다.

 

[commit 또는 rollback 실행 이전의 데이터 상태]

- 데이터를 이전 상태로 복구할 수 있습니다.

- 현재 사용자는 select 문을 사용하여 dml 작업 결과를 검토할 수 있습니다.

- 현재 사용자가 사용 중인 dml 문의 결과를 다른 사용자는 볼 수 없습니다.

- 관련 행이 잠기므로 다른 사용자는 관련 행의 데이터를 변경할 수 없습니다.

 

[commit 실행 이후의 데이터 상태]

- 데이터 변경 내용이 데이터베이스에 영구히 저장됩니다.

- 데이터의 이전 상태는 완전히 없어집니다.

- 모든 사용자가 결과를 볼 수 있습니다,

- 관련 행 잠금이 해제되어 다른 사용자가 행을 조작할 수 있습니다

- 모든 저장점이 지워집니다.

 

[rollback 실행 이후의 데이터 상태]

rollback 문을 사용하여 보류 중인 변경 내용을 모두 버립니다.

- 데이터 변경이 취소됩니다.

- 데이터가 이전 상태로 복구됩니다.

- 관련 행에 대한 잠금이 해제됩니다.

 

[명령문 레벨 롤백]

- 트랙잭션 실행 중 단일 DML 문 실행에 실패하면 해당 명령문만 롤백됩니다.

- Oracle server는 암시적(implicit)저장점을 구현합니다.

- 다른 모든 변경 내용은 유지됩니다.

- 사용자는 commit 또는 rollback 문을 실행하여 트랜잭션을 명시적(explicit)으로 종료해야 합니다.

 

[읽기 일관성]

- 읽기 일관성을 통해 항상 일관성 있는 데이터 뷰를 볼 수 있습니다.

- 한 사용자가 변경한 내용이 다른 사용자가 변경한 내용과 충돌하지 않습니다.

- 읽기 일관성은 동일한 데이터에 대해 다음을 보장합니다.

   - 데이터를 읽는 중에는 쓸 수 없습니다. / 데이터를 쓰는 중에는 읽을 수 없습니다.

 

[잠금]

오라클 데이터베이스에서 잠금은 다음과 같은 기능을 가집니다.

- 동시에 수행되는 트랜잭션 간에 파괴적인 상호 작용을 방지합니다.

- 사용자 작업이 필요 없습니다.

- 자동으로 최대한 낮은 레벨의 제한을 사용합니다.

- 트랜잭션 실행 기간 동안 유지됩니다.

- 명시적(explicit)잠금과 암시적(implicit)잠금이라는 두 가지 유형이 있습니다.

 

[암시적(implicit)잠금]

- 두 가지 잠금 모드

   - 배타적(exclusive):다른 사용자가 액세스하지 못하도록 잠금

   - 공유(share):다른 사용자의 액세스를 허용

- 높은 레벨의 데이터 동시성

   - dml: 테이블은 공유 잠금, 행은 배타적 잠금

   - 질의: 잠금 필요 없음

   - ddl: 객체 정의 보호

- 잠금은 커밋 또는 롤백이 수행될 때까지 유효합니다.

 

[요약]

명령문	설명
insert	테이블에 새 행 추가
update	테이블의 기존 행 수정
delete	테이블에서 기존 행 제거
marge	조건에 따라 데이터를 테이블에 삽입 또는 갱신
commit	보류 중인 모든 변경 내용을 영구히 저장
savepoint	저장점 표시자까지 롤백하는데 사용
rollback	보류 중인 데이터 변경 내용을 모두 버림

<<9.테이블 생성 및 관리>>

[데이터베이스 객체]

객체	설명
테이블	기본 저장 단위며 행과 열로 구성 됩니다.
뷰	논리적으로 하나 이상의 테이블에 있는 데이터의 부분 집합을 나타냅니다.
시퀀스	숫자 값 생성기입니다.
인덱스	질의의 성능을 향상시킵니다.
동의어	객체에 다른 이름을 제공합니다.

DDL -> 구조를 가지고 있는 애들 ->객체-> 구조정리 생성,수정,삭제

 

[이름 지정 규칙]

테이블 이름 및 열 이름:

- 문자로 시작해야 합니다. 작은따옴표를 붙여야 문자로 인식한다

- 1자부터 30자까지 가능합니다.

- A-Z,a-z,0-9,_,$,#만 포함해야 합니다.

- 동일한 사용자가 소유한 다른 객체의 이름과 중복되지 않아야 합니다.

- oracle server의 예약어가 아니어야 합니다.

 

[creat table문]

* 다음 사항이 필요합니다.

  - create table 권한

  - 저장 영역

create table [schema.]table
             (column datatype [DEFAULT expr][, ...]);

* 다음을 지정합니다.

   - 테이블이름, 열의 이름, 데이터 유형 및 크기

 

[다른 사용자의 테이블 참조]

* 다른 사용자에게 속한 테이블은 해당 사용자의 스키마에 없습니다.

* 다른 사용자의 테이블을 사용할 때는 소유자 이름을 접두어로 붙어야 합니다.

 

[default 옵션]

- 삽입을 수행할 때 사용할 열의 기본값을 지정합니다.

-리터럴 값, 표현식 또는 sql 함수는 유효한 값입니다

- 다른 열의 이름이나 의사 열은 잘못된 값입니다.

- 기본 데이터 유형은 해당 열의 데이터 유형과 일치해야 합니다.

 

[오라클 데이터베이스의 테이블]

- 사용자 테이블

   - 사용자가 생성 및 유지 관리하는 테이블의 collection 입니다.

   - 사용자 정보를 포함합니다.

- 데이터 딕셔너리

   - oracle server가 생성 및 유지 관리하는 테이블의 collection 입니다.

   - 데이터베이스 정보를 포함합니다.

 

[데이터 딕셔너리 질의]

- 사용자가 소유한 테이블의 이름을 봅니다.

select table_name
from user_tables;

- 사용자가 소유한 개별 객체 유형을 봅니다.

select distinct object_type
from user_objects;

- 사용자가 소유한 테이블, 뷰, 동의어 및 시퀀스를 봅니다.

select*
from user_catalog;

 

[데이터 유형]

데이터 유형	설명
varchr2(size)	가변 길이 문자 데이터
char(size)	고정 길이 문자 데이터
number(p,s)	가변 길이 숫자 데이터
date	날짜 및 시간 값
long	최대2GB의 가변 길이 문자 데이터
clob	최대4GB의 문자 데이터
RAN 및 LONG RAN	원 시 이진 데이터
blog	최대4GB의 이진 데이터, 이미지에 많이 사용
bfile	외부 파일에 저장된 이진 데이터(최대 4gb)
rowid	테이블에서 행의 고유 조소를 나타내는 64wlstn

[데이터 구현 방벙]

1. table 생성 create

2.data 삽입 insert

3. wjwkd commit

 

 

텍스트 -문자, 날짜, 숫자

멀티미디어 - 음성, 이미지, 영상

 

char(5) ->20% (확장성)

고정

성능 높음. 공간활용도 낮음

varchar2(5)

가변길이

성능 낮음, 공간활용도 높음

 

number  (-,0,+)

numder(p)-> 정수만 사용한다

 

date->년월일 나타냄

timestamp -> 년월일 시간 분 초 나타냄

 

[data time 데이터 유형]

oracle9i에서의 datetime 기능 향상

- 새로운 datetime 데이터 유형이 도입되었습니다.

- 새로운 데이터 유형 저장이 가능합니다

- 시간대 및 지역 시간대에 관한 기능이 향상되었습니다.

데이터 유형	설명
timestamp	소수점 이하 초까지 포함하는 날짜
interval year to month	연 수 및 개월 수를 기간을 저장
interval day to second	날짜 수, 시간 수, 분 수, 초 수로 기간을 저장

- timestamp 데이터 유형은 date 데이터 유형을 확장한 것입니다.

-date 데이터 유형의 연도, 월,일 뿐 아니라 시, 분, 초 및 소수점 이하 초 값까지 저장합니다.

- timestamp 데이터 유형은 다음과 같이 지정합니다.

timestamp[(fractional_seconds_precision)]

 

Q. 90번 부서에 근무하는 사원의 이름과 급여를 저장하는 dept90테이블을 생성

1.create table dept90

   (last_name char(10), 

    salary number(9));

2.select last_name, salary from employees where d_id=90;

3.insert ito dept90 values();

4.commit;

 

[alter table 문]

새 열 추가, 기존 열 수정, 새 열의 기본값 정의, 열 삭제 할때 alter table 문을 사용합니다

 

[set unuserd 옵션]

- set unused 옵션을 사용하여 하나 이상의 열을 unused로 표시합니다.

- drop unused columns 옵션을 사용하여 unused로 표시된 열을 제거합니다.