IT 국비 교육

쌍용교육센터 - 20일

개발자가 되고 싶어요 ㅣ 2024. 3. 15. 19:09

2024.03.15

DML

 

INSERT문 : 테이블에 행을 삽입한다.

INSERT INTO 테이블명 (컬럼명1,컬럼명2,ㆍㆍㆍ)
             VALUES (컬럼값1,컬럼값2,ㆍㆍㆍ)

단, 전체 데이터를 삽입할 때는 컬럼명을 생략 가능하다.

INSERT INTO 테이블명
VALUES (컬럼값1,컬럼값2,ㆍㆍㆍ

 

UPDATE문 : 행 단위로 데이터를 갱신한다.

UPDATE 테이블명 SET 컬럼명1=컬럼값1, 컬럼명2=컬럼값2 ㆍㆍㆍ
WHERE 컬럼명 = 컬럼값

단, WHERE절을 명시하지 않으면 전체 행의 데이터를 수정한다.

 

DELETE문 : 행을 삭제한다.

DELETE FROM 테이블명 WHERE 컬럼명 = 컬럼값

단, WHERE절을 명시하지 않으면 전체 행의 데이터를 삭제한다.

 

트랜잭션

트랜잭션은 데이터 처리의 한 단위이다. 오라클 서버에서 발생하는 SQL문들을 하나의 논리적인 작업 단위로써 성공하거나 실패하는 일련의 SQL문을 트랜잭션이라고 할 수 있다. 트랜잭션은 데이터를 일관되게 변경하는 DML 문장으로 구성된다.

트랜잭션의 시작시점은 실행 가능한 SQL문장이 제일 처음 실행될 때이다.

트랜잭션의 종료시점은 COMMIT이나 ROLLBACK을 하거나, DDL이나 DCL문장의 실행(자동 COMMIT)할 때, 기계 장애 또는 시스템 충돌(crash)나 deadlock이 발생하거나 사용자가 정상 종료했을 때이다.

 

COMMIT(변경사항 저장) & ROLLBACK(변경사항 취소)

  • COMMIT과 ROLLBACK의 장점
    • 데이터의 일관성을 제공한다.
    • 데이터를 영구적으로 변경하기 전에 데이터 변경을 확인하게 강제한다.
    • 관련된 작업을 논리적으로 그룹화 한다.
    • COMMIT, ROLLBACK 문장으로 트랜잭션의 논리를 제어한다.
  • COMMIT이나 ROLLBACK 이전의 데이터 상태
    • 데이터 이전의 상태로 복구 가능하다.
    • 현재 사용자는 SELECT문장으로 DML작업의 결과를 확인한다.
    • 다른 사용자는 SELECT문장으로 현재 사용자가 사용한 DML문장의 결과를 확인할 수 없다.
    • 변경된 행은 LOCK이 설정되어서 다른 사용자가 변경할 수 없다.
  • COMMIT이후의 데이터 상태
    • 데이터베이스에 데이터를 영구적으로 변경한다.
    • 데이터의 이전 상태는 완전히 상실한다.
    • 모든 사용자가 결과를 볼 수 있다.
    • 변경된 행의 LOCK이 해제되고 다른 사용자가 변경할 수 있다.

 

데이터베이스 객체

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

 

테이블이란?

  • 테이블은 기본적인 데이터 저장 단위이다.
  • 레코드와 컬럼으로 구성되어 있다.
    • 레코드(record, row): 테이블의 데이터는 행에 저장된다.
    • 컬럼(column): 테이블의 각 컬럼은 데이터를 구별할 수 있는 속성을 표현한다.
  • 이름 지정 규칙
    • 문자로 시작해야 한다.
    • 1자부터 30자까지 가능하다.
    • a-z, A-Z, 0-9, _, $, #만 포함해야 한다.
    • 동일한 사용자가 소유한 다른 객체의 이름과 중복되지 않아야 한다.
    • Oracle server의 예약어가 아니어야 한다.
  • 오라클 데이터베이스의 테이블
    • 사용자 테이블
      • 사용자가 생성 및 유지 관리하는 테이블의 collection사용자 정보를 포함한다.
    • 데이터 딕셔너리
      • Oracle Server가 생성 및 유지 관리하는 테이블의 collection 데이터베이스 정보를 포함한다.

    접두어 설명
    USER_ 이 뷰는 사용자가 소유하는 객체에 관한 정보를 포함
    ALL_ 이 뷰는 사용자가 엑세스할 수 있는 모든 테이블(객체 테이블 및 관계형 테이블)에 관한 정보를 포함
    DBA_ 이 뷰는 제한된 뷰로서 DBA 롤을 할당 받은 사용자만 엑세스할 수 있음
    V$ 이들 뷰는 데이터베이스 서버 성능, 메모리 및 잠금에 대한 동적 성능 뷰
  • 테이블의 생성
    • 테이블 이름: 만들어질 테이블의 이름
    • 열 이름: 테이블 내에 만들어질 열의 이름. 열의 이름은 같은 테이블 내에서는 유일해야 하지만, 서로 다른 테이블 간에는 같은 이름을 사용할 수 있다.
    • 데이터 타입: 각각의 열은 자신의 데이터 타입을 가진다. 열의 데이터 타입이 결정되면 어떤 데이터 타입이 지정되는지에 따라 데이터의 길이나 정확도와 스케일이 지정되어야 한다.
    • default<표현식>: 각각의 열에는 insert 구문에 열의 값이 지정되지 않은 경우에 이용될 디폴트 값을 지정해준다.<표현식>부분에는 정적인 값이나 대부분의 SQL함수를 지정 가능하다.
    • 제약조건: 만들어질 각 열에 선택적으로 제약조건을 정의할 수 있다. 제약조건은 각 열의 값이 올바른 것이 되기 위해 지켜져야 할 규칙이다.

제약조건이란 테이블에 부적절한 자료가 입력되는 것을 방지하기 위해서 여러 가지 규칙을 적용해 놓는 것을 말한다.

제약조건 설명
primary key(PK) 유일하게 테이블의 각 행을 식별(not null과 unique 조건을 만족)
foreign key(FK) 열과 참조된 열 사이의 외래키 관계를 적용하고 설정
unique key(UK) 테이블의 모든 행을 유일하게 하는 값(null 허용)
not null(NN) 열은 null값을 포함할 수 없음
check(ck) 해당 컬럼에 저장 가능한 데이터 값의 범위나 사용자 조건을 지정

 

테이블의 관리

 

ADD 연산자: 테이블에 새로운 컬럼을 추가한다.

ALTER TABLE 테이블명 ADD (컬럼명1 컬럼타입)

 

제약조건 추가

ALTER TABLE 테이블명 ADD CONSTRAINT 컬럼명 제약조건(컬럼명)

 

MODIFY 연산자: 테이블의 컬럼을 수정하거나 NOT NULL컬럼으로 변경할 수 있다.

ALTER TABLE 테이블명 MODIFY (컬럼명 컬럼타입 제약조건)

 

컬럼명 변경

ALTER TABLE 테이블명 RENAME COLUMN 기존컬럼명 TO 새컬럼명

 

테이블명 변경

RENAME 기존테이블명 TO 새테이블명

 

테이블의 삭제

DROP TABLE 테이블명

단, 삭제하게 되면 복구할 수 없다.

 

부모 테이블의 컬럼을 삭제하면서 자식 테이블의 자식 데이터를 모두 삭제

ON DELETE CASCADE

 

뷰(VIEW)

논리적으로 하나 이상의 테이블에 있는 데이터의 부분 집합

  • 데이터 엑세스를 제한하기 위해
  • 복잡한 질의를 쉽게 작성하기 위해
  • 데이터 독립성을 제공하기 위해
  • 동일한 데이터로부터 다양한 결과를 얻기 위해

뷰는 가상으로 만들어진 컬럼(Virtual Column)을 제외하면 수정이 가능하고 삭제도 가능하다. 수정,삭제하면 원래 테이블에 반영되고, 삽입은 여러 제약 조건과 Virtual Column 사용으로 제약이 많다.

VIEW 생성
CREATE OR REPLACE VIEW emp10_view
AS SELECT empno id_number, ename name,
            sal*12 ann_salary
    FROM EMP
    WHERE DEPTNO=10;

 

조인한 결과를 뷰로 생성하여 사하면 잦은 조인으로 인한 성능저하를 막을 수 있다.

CREATE OR REPLACE VIEW emp_info_view
AS SELECT e.empno, e.ename, d.deptno, d.loc, d.dname
FROM emp e, dept d
WHERE e.deptno=d.deptno;

 

VIEW를 통한 데이터 변경하기 일반적으로 VIEW는 조회용으로 많이 사용되지만 아래와 같이 데이터를 변경할 수 있다.

(주의) VIEW의 데이터를 변경하게 되면 해당 VIEW가 참조하는 TABLE의 데이터 또한 변경된다.

UPDATE EMP10_VIEW SET NAME='SCOTT'
WHERE ID_NUMBER=7839;

 

WITH READ ONLY : 읽기 전용 뷰를 생성하는 옵션

CREATE OR REPLACE VIEW EMP20_VIEW
AS SELECT EMPNO ID_NUMBER, ENAME NAME, SAL*12 ANN_SALARY
    FROM EMP
    WHERE DEPTNO=20
WITH READ ONLY;

UPDATE EMP20_VIEW SET NAME='DAVID' WHERE ID_NUMBER=7902; -- 읽기 전용 뷰에서는 DML 작업을 수행할 수 없습니다.

 

VIEW 수정

VIEW의 수정
CREATE OR REPLACE VIEW EMP10_VIEW
(id_number,name,sal,department_id)
AS SELECT EMPNO, ENAME, SAL, DEPTNO
    FROM EMP
    WHERE DEPTNO=10;

 

VIEW 삭제

DROP VIEW EMP10_VIEW;

 

SEQUENCE(시퀀스)

유일한 값을 생성해주는 오라클 객체 (숫자만 가능) 시퀀스를 생성하면 기본키와 같이 순차적으로 증가하는 컬럼을 자동적으로 생성할 수 있다. 보통 PRIMARY KEY 값을 생성하기 위해 사용한다.

시퀀스 생성

CREATE SEQUENCE test_seq
START WITH 1
INCREMENT BY 1
MAXVALUE 1000000;

시작 값이 1이고 1씩 증가하고 최대값이 1000000이 되는 시퀀스 생성

 

CURRVAL : 현재 값을 반환

NEXTVAL : 현재 시퀀스 값의 다음 값 반환

SELECT TEST_SEQ.NEXTVAL FROM DUAL;
SELECT TEST_SEQ.CURRVAL FROM DUAL;

 

sboard 테이블에 데이터를 삽입할 때 시퀀스를 활용

INSERT INTO SBOARD (NUM,ID,CONTENT)
VALUES (TEST_SEQ.NEXTVAL,'SKY','여기서는 강남');

 

 

시퀀스 수정 (START WITH는 수정할 수 없음)

ALTER SEQUENCE TEST_SEQ
INCREMENT BY 5;

 

시퀀스 삭제

DROP SEQUENCE TEST_SEQ;

 

 

 

Q&A

Q. 실수로 롤백한 경우에 취소가 가능한지?
A. 롤백을 취소하는건 불가능하기 때문에 SAVEPOINT를 사용하는것을 권장한다.
 
Q.데이터를 일부만 수정할 때 INSERT와 UPDATE의 차이점
A. INSERT는 데이터 수정시에 데이터 무결성을 헤칠수 있기 때문에 수정시에 사용하는 것은 권장되지 않는다.

Q. 서브쿼리 OR 조인 둘 중 뭐가 더 성능이 좋은가?
A.  보통 조인을 사용한 쿼리가 서브쿼리를 사용한 쿼리보다 성능면에서 우수하다.

저작자표시

'IT 국비 교육' 카테고리의 다른 글

쌍용교육센터 - 22일  (0) 2024.03.19
쌍용교육센터 - 21일  (3) 2024.03.18
쌍용교육센터 - 19일  (0) 2024.03.14
쌍용교육센터 - 18일  (0) 2024.03.13
쌍용교육센터 - 17일  (1) 2024.03.12
IT 국비 교육 다른글

티스토리툴바