23일차 수업 정리(트랜잭션, 제약조건, 뷰, 시퀀스)
Tip!
인덱스 개념(매우 중요), 프로시저(왜쓸까? 실무에서 많이 사용)
**SQL의 분류
1. Database 이론
1) DDL(정의) - 데이터베이스 객체를 생성, 구조를 변경 및 제거
2) DML(조작) - 데이터를 삽입, 갱신, 삭제 및 조회
3) DCL(제어) - 데이터베이스의 무결성, 회복, 병행제어 등을 처리
=> DDL과 DCL은 DBA의 언어이고, DML은 user의 언어
=> 트랜잭션 제어 명령어를 DCL에 포함
2. 실무에서의 분류
1) DDL - 데이터베이스 객체 생성, 구조변경 및 제거(CREATE, ALTER, DROP)
2) DQL - 데이터베이스 조회(SELECT)
3) DML - 데이터 삽입, 갱신, 삭제(INSERT, UPDATE, DELETE)
4) TCL - 트랜젝션 제어어(COMMIT, ROLLBACK, SAVEPOINT)
5) DCL - 권한, 회복, 병행제어, 무결성에 관련된 명령어(GRANT, REVOKE, IMPORT, EXPORT)
=> DQL, DML, TCL은 USER의 언어, DDL, DCL은 DBA의 언어로 간주
3. DATABASE 에서 이야기하는 USER는 Application Programmer
**Database 구동 원리
=> 관계형 Database는 원본에 바로 작업하는 경우가 거의 없음
=> 복사본에 작업을 수행하고, 작업 완료시 원본에 다시 복사하는 형태로 동작
=> TCL : 지금까지 수행한 작업을 원본에 반영할 지 여부를 결정하는 명령어
**Transaction
=> 한번에 이루어져야 하는 작업의 논리적 단위
1. 기본 성질(ACID)
1) Atomicity(원자성) : All or Nothing - 전부 수행되거나 전부 수행되지 않아야 함
2) Consistency(일관성) : 한번 완료된 트랜잭션은 일관성을 유지해야 함
3) Isolation(독립성) : 트랜잭션은 다른 트랜잭션에 독립적이어야 함
4) Durablity(영속성) : 한번 완료된 트랜잭션은 계속 되어야 함
2. 트랜잭션 명령어
1) COMMIT : 작업을 원본에 반영
2) SAVEPOINT : ROLLBACK 할 지점을 만드는 명령어
3) ROLLBACK : 작업을 취소
3. AUTO COMMIT : 자동으로 COMMIT 되는 상황
=> DDL이나 DCL이 성공적으로 수행된 경우
=> 접속 프로그램을 정상적으로 종료한 경우
4. AUTO ROLLBACK 되는 경우
=> DB 사용중에 정전, 컴퓨터 고장으로 인한 ShutDown의 경우 ROLLBACK 됨
5. 프로그래밍 언어에서 DB 사용시, 그 언어나 프레임 워크의 기본 트랜잭션 동작에 대하여 알아보고 프로그래밍 해야함
=> Java에서 데이터베이스를 사용할 때는 기본적으로 AUTO COMMIT
=> Java에서도 Hibernate와 같은 데이터베이스 연동 프로그램을 사용할 때는 Manual Commit(수동 커밋)
6. Transaction 적용 모드
1) Auto Commit : SQL 명령어가 실행되면 무조건 Commit을 수행(Java, DBeaver의 기본모드)
2) Manual Commit : DML, DQL의 경우는 직접 Commit이나 Rollback을 해야만 적용되는 방식
=> 타 프로그래밍 언어나 대다수의 접속 프로그램의 기본모드
=> 프로그램 적용시 대부분 이 방식 사용
** CONSTRAINT(제약조건)
=> 데이터베이스에 잘못된 데이터가 삽입되지 않도록 데이터베이스의 열들에 설정하는 여러가지 규칙
1. 종류
1) NOT NULL : 필수입력
2) UNIQUE : 유일성 - 중복될 수 없음(NULL은 가능)
3) PRIMARY KEY : NOT NULL이고, UNIQUE
4) CHECK : 값의 범위나 조건을 지정
5) FOREIGN KEY : 외래키 - 다른 테이블에서 데이터를 조회하기 위한 열
2. 제약조건 확인
=> 제약조건 : USER_CONSTRAINTS 테이블에서 확인 가능
=> CONSTRAINT TYPE이 종류를 의미
P : PRIMARY KEY
R : FOREIGN KEY
U : UNIQUE
C : CHECK, NOT NULL
3. 제약조건 설정
1) 행 레벨 제약조건 설정 : CREATE TABLE 구문에서 행을 만들때 기재
2) 테이블 레벨 제약조건 설정 : 행을 다 만들고 제약조건을 설정
4. NOT NULL
=> 필수 입력을 하고자 하는 경우 사용
=> 행 레벨에서만 설정 가능
=> NOT NULL은 실제로는 하나의 바이트를 이용해서 설정
(NOT NULL 지정시, NULL을 표현하기 위한 BYTE가 만들어지지 않기에 행을 만들때 결정해야함)
5. UNIQUE
=> 유일성
=> NULL은 제외 - NULL은 여러개일수 있음
6. PRIMARY KEY
=> NOT NULL이고, UNIQUE
=> 1개 이상의 열로 만듬(2개 이상의 열도 가능)
=> 테이블에서 1개만 생성 가능
=> 테이블에서 행을 구분하기 위한 유일무이한 속성 또는 속성의 집합
7. CHECK
=> 값의 범위나 조건을 설정
=> 컬럼명 조건의 형태로 입력
=> GENDER는 M 또는 F의 값 만을 가져야 한다
- GENDER CHAR(1) GENDER IN ('M', 'F')
=> SCORE는 1~100 사이의 값이어야 한다
- SCORE NUMBER(3) SCORE BETWEEN 0 AND 100
ex) 회원정보를 저장하는 테이블
EMAIL(변하지 않는 문자열 50자- 한글X) - PRIMARY KEY
NAME(변하지 않는 문자열 10자(한글) - 필수입력(NOT NULL)
PHONE(변하는 문자열이지만 글자수는 항상 11자(한글X) - UNIQUE
GENDER(영문 한글자로 M, F만 가능)
ADDRESS(변하는 문자열로 한글 100자 이내)
DESCRIPTION(변하는 문자열로 한글 1000자이내) - 긴 문자열
CREATE TABLE MEMBER(
EMAIL VARCHAR2(50) PRIMARY KEY,
NAME VARCHAR2(30) NOT NULL,
PHONE VARCHAR2(11) UNIQUE,
GENDER VARCHAR2(1) CHECK(GENDER IN('M', 'F')),
ADDRESS CHAR(300),
DESCRIPTION CLOB); 8. FOREIGN KEY
=> 다른 테이블의 데이터를 참조하기 위한 열
=> 자신의 테이블에서는 어떤 열이던 상관없지만 타 테이블에서는 UNIQUE하거나 PRIMARY KEY 여야 함
=> REFERENCES 테이블명(열이름) 옵션
=> 옵션은 생략 가능하고, ON DELETE CASCADE, ON DELETE SET NULL, ON UPDATE CASCADE등이 있음
=> 외래키로 지정된 열을 소유중인 테이블을 부모테이블, 외래키를 소유한 테이블을 자식 테이블이라고 함
=> 별다른 옵션없이 제작시 자식 테이블에 존재하는 데이터는 부모 테이블에서 삭제 불가
- ON DELETE CASCADE는 부모테이블에서 지워 질 때, 자식 테이블에서도 연쇄적으로 지워짐
- ON DELETE SET NULL은 부모 테이블에서 지워 질 때, 자식 테이블에서는 외래키의 값이 NULL 로 설정
- ON UPDATE CASCADE는 부모테이블에서 갱신 발생시, 자식 테이블에서도 같이 갱신 수행
=> 외래키 만드는 방법은 테이블간의 관계를 확인해봐야 함
- 1:1관계시 양쪽 테이블의 기본키를 상대방 테이블의 외래키로 추가
- 1:N 관계시 1인쪽 테이블의 기본키를 상대방 테이블의 외래키로 추가
- N:N 관계시 양쪽 테이블의 기본키를 외래키로 갖는 별도의 테이블을 생성
ex) 회원테이블과 게시판 테이블이 존재하는 경우
- 회원 1명이 여러개의 게시글 작성 가능
- 게시글 1개는 1명이 작성해야 함
=> 회원과 게시판의 관계는 1:N
=> 회원 테이블의 기본키를 게시판 테이블의 외래키로 추가(회원테이블 = 부모테이블, 게시판테이블 = 자식테이블)
ex) 회원테이블과 상품 테이블이 존재하는 경우
- 회원 1명이 여러개의 상품을 구매 가능
- 동일한 상품을 여러 회원이 구매 가능
=> 회원 테이블의 기본키와 상품테이블의 기본키를 외래키로 갖는 별도의 테이블 생성(N:N관계)
ex) 회원 테이블을 참조하는 (EMAIL을 외래키) 게시판 테이블을 생성
=>글번호(정수 10자리- 기본키), 제목(한글 100자, 변경O), 내용(긴문자열), 작성일(날짜), 조회수(정수 10자리)로 구성
CREATE TABLE BOARD(
NUM NUMBER(10) PRIMARY KEY,
TITLE CHAR(300),
CONTENT CLOB,
WRITEDATE DATE,
READCNT NUMBER(10),
EMAIL VARCHAR2(50) REFERENCES MEMBER(EMAIL));
=> 외래키가 설정되면 부모 테이블의 데이터를 삭제, 테이블 제거 시도시 에러 발생 8. DEFAULT
=> 데이터를 입력하지 않았을 때, 대입하는 기본값
=> DEFAULT 기본값 형식으로 추가
=> 작성일의 경우 직접 입력받는 것보다는 SYSDATE가 자동으로 대입되도록 해주는 것이 좋음
CREATE TABLE BOARD(
NUM NUMBER(10) PRIMARY KEY,
TITLE CHAR(300) DEFAULT '무제',
CONTENT CLOB DEFAULT '냉무',
WRITEDATE DATE DEFAULT SYSDATE,
READCNT NUMBER(10) DEFAULT 0,
EMAIL VARCHAR2(50) REFERENCES MEMBER(EMAIL));
9. INTEGRITY(무결성)
1) ENTITY INTEGRITY(개체 무결성) : 기본키는 NULL이거나 중복될 수 없음
2) REFERENCE INTEGRITY(참조 무결성) : 외래키는 NULL이거나 참조 할 수 있는 값 만을 가져야 함
=> 데이터베이스와 관련된 시험을 치를 때 반드시 기억(E-R Diagram, Transaction Acid, Integrity)
10. 제약 조건 명
=> 제약 조건 이름을 설정하지 않고, 제약조건 생성시 오라클은 SYS로 시작하는 번호를 이용해 제약조건명 설정
=> 제약 조건은 USER_CONSTRAINTS 테이블에 존재
=> 제약 조건을 만들때 이름을 부여할 수 있는데, 이때는 제약조건 앞에 CONSTRATINTS 제약조건명을 추가
=> 관습 - 기본키 : PK, 외래키 FK, 유일성 : UK, NOT NULL : NN, 체크 : CK를 붙임
11. 테이블 레벨 제약조건
=> 행을 만들 때 제약조건을 설정하지 않고 행을 전부 선언하고 마지막에서 제약조건을 설정하는 것
=> [CONSTRAINT 제약조건명] 제약조건 종류 (컬럼명)
=> NOT NULL은 테이블 레벨 제약 조건으로 사용 할 수 없음
=> 제약조건 종류 : 기본키- PRIMARY KEY, 유일성- UNIQUE, CHECK는 동일,
외래키- FOREIGN KEY(열이름) REFERENCES 테이블(참조할 열 이름)으로 설정
=> 기본키를 2개 이상의 열로 만들 때는 반드시 테이블 레벨 제약 조건을 만들어야 함
=> NCS LEVEL 4시험에서는 기본키로 2개 컬럼 설정하는 것이 문제로 출제
12. 제약조건 갱신
1) 제약조건 추가
ALTER TABLE 테이블명
ADD [CONSTRAINT 제약조건명] 제약조건 종류(컬럼명);
2) 제약조건 변경
ALTER TABLE 테이블명 - NOT NULL은 제약조건 추가가 아니라 변경
MODIFY 열 이름 [CONSTRAINT 제약조건명] 제약조건 종류;
3) 제약조건 삭제
ALTER TABLE 테이블명
DROP CONSTRAINT 제약조건명;
13. 제약조건 활성화&비활성화
1) 제약 조건을 일시적으로 비활성화 : 샘플링 입력이나 테스트 할 때 주로 이용
ALTER TABLE 테이블명
DISABLE CONSTRAINT 제약조건명;
2) 제약조건을 활성화
ALTER TABLE 테이블명
ENABLE CONSTRAINT 제약조건명;
14. 제약조건의 사용
=> 개발자들은 제약조건을 설정하지 않으려고 하는 경향이 있으나 잘못된 생각
**오라클의 데이터베이스 객체
1. 종류
1) Table
2) View
3) Sequence
4) Index
5) Synonym
6) Procudere
7) Trigger
2. View
=> 논리적인 가상의 테이블
=> 실제 존재하지는 않지만 마치 테이블처럼 사용
=> 테이블이나 다른 뷰로부터 생성
=> 자주 사용하는 SELECT 구문을 하나의 이름으로 묶어두고 사용하는 것
1) 사용 목적
=> 속도 : View나 Procedure는 한번 호출시 메인 메모리에 남아 다음 호출시 속도가 빠름
=> 보안 : User에게 User가 필요한 데이터만 골라줄수 있어 보안기능이 강화될수 있음
2) 생성
CREATE [OR REPLACE] VIEW 뷰이름
AS
SELECT 구문
[WITH CHECK OPTION]
[WITH READ ONLY]
=> VIEW는 구조변경이 안되서 ALTER VIEW가 없음
=> 구조 변경시 OR REPLACE를 이용
=> VIEW는 가상의 테이블이지만 DML 작업이 가능
- VIEW에 데이터 삽입시, 원본 테이블에 데이터가 삽입됨
=> DML(INSERT, UPDATE, DELETE)작업을 못하도록 할 때, WITH READ ONLY를 추가
=> WITH CHECK OPTION은 VIEW에 존재하는 데이터만 DML 작업을 할 수 있도록 하기 위한 옵션
3) 삭제
DROP VIEW 뷰이름;
4) 뷰 생성과 삭제
ex) DEPT 테이블의 데이터를 이용하여 DEPTVIEW를 생성
CREATE OR REPLACE VIEW DEPTVIEW
AS
SELECT *
FROM DEPT;
3. SEQUENCE
=> 오라클에서 일련번호를 만들어주기 위해 제공되는 객체
1) 생성
- CREATE SEQUENCE 시퀀스 명
- START WITH 시작숫자
- INCREMENT BY 증가숫자
- MAXVALUE 최대값
- MINVALUE 최소값
- CYCLE | NOCYCLE
- CACHE | NOCACHE
=> START WITH는 시작 위치로 기본값은 계정에 따라 다름
=> INCREMENT BY는 증가값으로 기본값은 1
=> MAXVALUE는 최대값으로 기본값은 10의 27승
=> MINVALUE는 최소값으로 기본값은 1
=> NOCYCLE은 최대값으로 간 경우 최소값으로 돌아오지 않음(기본키 만들때 해당 옵션 사용)
=> CACHE는 기억할 값의 갯수로 기본값은 20
2) 사용
=> 시퀀스명.NEXTVAL : 다음 시퀀스 값 가져오기
=> 시퀀스명.CURRVAL : 현재 시퀀스 값 가져오기
- 생성후 1번은 NEXTVAL을 호출 해야 CURRVAL 사용 가능
3) 변경
=> 형식 : ALTER SEQUENCE 시퀀스 명
=> 옵션 설정
- START WITH는 수정할 수 없음
- START WITH를 변경할 때는 삭제하고 다시 생성
4) 삭제
=> 형식 : DROP SEQUENCE 시퀀스 명;
4. INDEX
=> 데이터를 빠르게 찾을 수 있도록 설정한 포인터
1) 장점
=> 검색속도가 향상
2) 단점
=> 추가적인 공간이 필요
=> 삽입, 삭제가 빈번하게 발생시, 속도가 느려짐
3) 오라클의 경우는 B* 트리 이용(B : Balance 좌우 균형이 맞는지)
4) PRIMARY KEY와 UNIQUE에는 별도의 지정이 없어도 인덱스를 생성