Database
무결성 제약조건
- 테이블에 유효하지 않은 데이터가 입력되는 것을 방지한다.
- 해당 테이블에 데이터가 추가, 삭제, 변경될 때마다 무결성 제약조건을 검사한다.
- 종류
- NOT NULL 제약조건
- 해당 컬럼에 NULL값이 입력되지 못하도록 지정한다.
- 고객테이블의 이름컬럼, 도서테이블의 제목컬럼, 상품테이블의 상품명컬럼
- UNIQUE 제약조건(고유키 제약조건)
- 해당 컬럼의 값은 테이블전체에서 고유한 값을 가져야한다.
- NULL값의 입력은 가능하다.
- 고객테이블의 핸드폰번호, 사원테이블의 주민번호컬럼, 학생테이블의 이메일컬럼
- PRIMARY KEY 제약조건(기본키 제약조건)
- 테이블의 각 행을 고유하게 식별할 수 있는 값을 가져야한다.
- 테이블전체에서 고유한 값을 가져야 하고, NULL값을 허용하지 않는다.
- 학생테이블의 학번, 사원테이블의 사원번호, 과목테이블 과목코드, 택배테이블의 송장번호
- FOREIGN KEY 제약조건(외래키 제약조건, 참조키 제약조건)
- 해당 컬럼의 값은 다른 테이블 혹은 같은 테이블의 특정 컬럼의 값과 관련된 값만 가져야 한다.
- 종속 테이블(자식 테이블) : FOREING KEY로 설정된 컬럼은 부모테이블의 기존 값과 일치하거나 NULL이어야 한다.
- 참조 테이블(부모 테이블) : 자식테이블이 참조하는 부모테이블의 컬럼은 PRIMARY KEY 혹은 UNIQUE 제약조건이 정의된 컬럼이어야 한다.
- 사원테이블(종속테이블)의 부서아이디는 부서테이블(참조테이블)의 부서 아이디를 참조한다.
- 부서테이블(자식테이블)의 관리자 아이디는 사원테이블(부모테이블)의 사원번호를 참조한다.
- 사원테이블(자식테이블)의 직종아이디는 직종테이블(부모테이블)의 직종아이디를 참조한다.
- 자식테이블에서 참조하고 있는 부모테이블의 행은 삭제할 수 없다.
- CHECK 제약조건
- 제시된 조건을 만족하는 값을 가져야한다.
- 사원테이블의 급여는 0보다 큰 값을 가져야한다.
- 학생테이블의 학년은 1,2,3,4 중의 하나여야 한다.
- 고객테이블의 성별은 M, F 중의 하나여야 한다.
- 학생테이블의 성적은 0.0 ~ 4.5 사이의 값이어야 한다.
- NOT NULL 제약조건
제약조건의 정의
- 컬럼 레벨 제약조건 정의
CREATE TABLE 테이블명 (
컬럼명 데이터타입 [CONSTRAINT 제약조건별칭] 제약조건,
컬럼명 데이터타입 [CONSTRAINT 제약조건별칭] 제약조건,
...
);- 테이블 레벨 제약조건 정의
CREATE TABLE 테이블명 (
컬럼명 데이터타입,
컬럼명 데이터타입,
...,
[CONSTRAINT 제약조건별칭] 제약조건 (컬럼명),
[CONSTRAINT 제약조건별칭] 제약조건 (컬럼명, 컬럼명, ...)
);
-- 하나 이상의 컬럼을 조합하여 제약조건을 정의할 수 있다.
-- NOT NULL 제약조건을 제외한 모든 제약조건을 정의할 수 있다.-
제약조건 정의하기
- NOT NULL 제약조건 정의
CREATE TABLE users ( user_name varchar2(30) NOT NULL, user_tel varchar2(20) CONSTRAINT user_tel_nn NOT NULL, ... ); -- NOT NULL 제약조건이 정의되지 않은 컬럼은 기본적으로 NULL 값을 가질 수 있다.
-
UNIQUE 제약조건 정의
CREATE TABLLE users ( user_name varchar2(30), user_tel varchar2(20) CONSTRAINT user_tel_nn NOT NULL, user_email varchar2(200) CONSTRAINT user_email_uk UNIQUE, ..., CONSTRAINT user_tel_uk UNIQUE(user_tel) ); -- user_tel 컬럼은 NOT NULL, UNIQUE 제약조건이 정의되어 있음 -- user_tel의 NOT NULL제약조건은 컬럼 레벨 제약조건 방식으로 정의 UNIQUE제약조건은 테이블 레벨 제약조건 방식으로 정의했음 -- user_email 컬럼은 UNIQUE 제약조건이 정의되어 있음, NULL값이 허용됨 -
PRIMARY KEY 제약조건 정의
CREATE TABLE users ( user_no number(10) CONSTRAINT user_no_pk PRIMAY KEY, ... ); CREATE TABLE users ( user_no number(10), ..., CONSTRAINT user_no_pk PRIMARY KEY(user_no) ); CREATE TABLE course_registerations ( stud_no number(10), course_no number(5), ..., CONSTRAINT course_regist_pk PRIMARY KEY(stud_no, course_no) ); -- 하나 이상의 열을 조합해서 기본키 제약조건 정의하기 -
FOREIGN KEY 제약조건
CREATE TABLE emps( ... dept_id number(10) CONSTRAINT emp_dept_id_fk REFERENCES depts(dept_id), ... ); -- emps 테이블의 dept_id 컬럼의 값은 depts테이블의 dept_id 값을 참조한다. CREATE TABLE emps( ... dept_id number(10), ..., CONSTRAINT emp_dept_id_fk FOREIGN KEY (dept_id) REFERENCES depts(dept_id), ); -- REFERENCES 키워드와 참조대상테이블(컬럼명)을 지정한다. -- 참조대상 컬럼은 PRIMARY KEY 혹은 UNIQUE 제약조건이 정의된 컬럼만 가능하다. -
CHECK 제약 조건
CREATE TABLE products ( ... pro_price number(10) CONSTRAINT pro_price_ck CHECK (pro_price > 0), pro_sell varchar2(20) CONSTRAINT pro_sell_ck CHECK (pro_sell in ('판매중', '재고부족', '절판')), pro_discount number(4, 3) CONSTRAINT pro_discount_ck CHECK (pro_discount >= 0.0 and pro_discount <= 0.5), );
-- 제약조건 정의하기
-- 컬럼레벨 제약조건 정의방식 사용
-- 별칭은 30자를 넘으면 안된다.
CREATE TABLE sample_todos (
todo_no NUMBER(8)
CONSTRAINT sampletodos_no_pk PRIMARY KEY,
todo_title VARCHAR2(250) NOT NULL,
todo_writer VARCHAR2(20)
CONSTRAINT sampletodos_writer_fk REFERENCES sample_users (user_id),
todo_day DATE NOT NULL,
todo_status VARCHAR2(10) DEFAULT '등록'
CONSTRAINT sampletodos_status_ck CHECK (todo_status in ('등록', '삭제', '완료')),
todo_text VARCHAR2(1000)
CONSTRAINT sampletodos_text_nn NOT NULL,
todo_created_Date DATE DEFAULT SYSDATE,
todo_completed_date DATE,
todo_deleted_date DATE
);
-- 생성된 테이블 삭제하기
DROP TABLE sample_todos;
commit;
-- 테이블 레벨 제약조건 정의방식
CREATE TABLE sample_todos (
todo_no NUMBER(8),
todo_title VARCHAR2(250) NOT NULL,
todo_writer VARCHAR2(20) NOT NULL,
todo_day DATE NOT NULL,
todo_status VARCHAR2(10) DEFAULT '등록',
todo_text VARCHAR2(1000) NOT NULL,
todo_created_Date DATE DEFAULT SYSDATE,
todo_completed_date DATE,
todo_deleted_date DATE,
CONSTRAINT sampletodos_no_pk PRIMARY KEY (todo_no),
CONSTRAINT sampletodos_writer_fk FOREIGN KEY (todo_writer) REFERENCES sample_users (user_id),
CONSTRAINT sampletodos_status_ck CHECK (todo_status in ('등록', '삭제', '완료'))
);
-- 상품을 여러 개 담을 수 있는 장바구니 테이블 정의하기
CREATE TABLE sample_cart_items (
item_no NUMBER(8) CONSTRAINT cartitems_no_pk PRIMARY KEY,
user_id VARCHAR2(20) NOT NULL,
product_no NUMBER(8) NOT NULL,
item_amount NUMBER(4) DEFAULT 1,
item_created_date DATE DEFAULT SYSDATE,
CONSTRAINT cartitems_userid_fk FOREIGN KEY (user_id) REFERENCES sample_users (user_id),
CONSTRAINT cartitems_productno_fk FOREIGN KEY (product_no) REFERENCES sample_products (product_no),
CONSTRAINT cartitems_uk UNIQUE (user_id, product_no),
CONSTRAINT cartitems_amount_ck CHECK (item_amount >= 1)
);
CREATE SEQUENCE cartitem_seq NOCACHE;
CREATE SEQUENCE todo_seq NOCACHE;
-- sample_cart_items 테이블에 장바구니 상품정보 추가하기
INSERT INTO sample_cart_items (item_no, user_id, product_no, item_amount, item_created_date)
VALUES (cartitem_seq.nextval, 'hong', 20, 1, sysdate);
DELETE FROM sample_cart_items;
commit;
-- sample_products 테이블에서 20번 상품 삭제하기
DELETE FROM sample_products
WHERE product_no = 20;
-- 오류 발생 ORA-02292: integrity constraint (HR.CARTITEMS_PRODUCTNO_FK) violated - child record found
-- sample_products테이블의 20번 상품을 참조하는 행이 sample_cart_items 테이블에
-- 존재하고 있기 때문에 20번 상품은 삭제할 수 없다.
-- sample_cart_items의 상품수량을 변경하기
UPDATE sample_cart_items
SET
item_amount = 0
WHERE item_no = 5;
-- 오류 발생 ORA-02290: check constraint (HR.CARTITEMS_AMOUNT_CK) violated
-- item_amount에 정의되어 있는 CHECK 제약조건 위배.
데이터 정의어(DDL)
- 오라클 데이터베이스 객체 생성, 변경, 삭제에 사용되는 명령어
- CREATE, ALTER, DROP, TRUNCATE
인덱스(색인)
- 데이터 행의 검색 속도를 향상시키기 위해서 사용되는 데이터베이스 객체다.
- 데이터의 위치를 빠르게 찾는 신속한 경로 엑세스 방법을 사용하여 디스크 I/O를 줄여 준다.
- 인덱스는 테이블과 독립적으로 존재한다.
- 한 번 생성된 인덱스는 Oracle이 자동으로 유지 관리한다.
- 테이블이 삭제되면 그 테이블의 데이터를 색인화하고 있는 인덱스도 같이 삭제된다.
- 인덱스 생성
- 자동 생성
- 테이블 정의할 때 primary key 제약조건, unique 제약조건이 정의된 컬럼의 값들은 자동으로 인덱스가 생성된다.
- 수동 생성
- 사용자가 행에 대한 엑세스 시간을 줄이기 위해서 특정 열을 대상으로 인덱스를 생성할 수 있다.
- 자동 생성
- 인덱스 생성하기
CREATE INDEX 인덱스명
ON 테이블명 (컬럼명, ....)- 인덱스 삭제하기
DROP INDEX 인덱스명- 함수 기반 인덱스 생성하기
CREATE INDEX 인덱스명
ON 테이블명 (오라클함수)- 인덱스를 생성이 필요한 경우
- where절이나 조인조건에 자주 사용되는 경우
- 컬럼이 매우 다양한 값을 포함하고 있는 경우
- 데이터가 아주 많은 테이블을 대상으로 조회작업을 했을 때 대부분의 조회작업에서 검색되는 행이 전체 데이터의 2%~4%미만인 경우
- 인덱스 생성이 필요하지 않는 경우
- 테이블이 작은 경우
- 테이블이 자주 갱신되는 경우
- 인덱스화된 열이 표현식의 일부로 사용되는 경우
- where절의 조회 조건으로 자주 사용되지 않는 경우
- 대부분의 조회작업에서 전체 데이터의 2%~4%이상 검색되는 경우
-- 인덱스 생성하기
-- sample_users 테이블의 user_phone컬럼의 값으로 사용자를 조회하는 일이 빈번하게 있다
-- 전화번호로 사용자를 빠르게 검색할 수 있도록 user_phone컬럼의 값으로 인덱스(색인)을 생성하기
-- sample_user_phone_idx의 내용
-- user_phone rowid
------------------------------------------
-- 010-0001-0001 AAAAAA AAE AAAACT AAA
-- 010-0001-0002 AAAAAA AAE AAAACT AAE
-- 010-0001-0011 AAAAAA AAE AAAACT ABA
-- 010-0001-0291 AAAAAA AAE AAAACT AAZ
-- 010-0001-1234 AAAAAA AAE AAAACT AZA
-- 010-0001-1256 AAAAAA AAE AAAACT AZZ
CREATE INDEX sample_user_phone_idx
ON sample_users (user_phone);
DROP INDEX sample_user_phone_idx;
-- sample_users 테이블에서 user_phone 컬럼의 값이 where절의 조회조건으로 사용되면
-- 자동으로 sample_user_phone_idx 인덱스(색인)에서 해당전화번호의 ROWID를 사용해서
-- 사용자정보(데이터행)를 빠르게 검색한다
SELECT *
FROM sample_users
WHERE user_phone = '010-5678-1234';
-- Top-N 분석과 분석함수 사용하기
-- Top-N 분석하기 : rownum을 사용한다.
-- 월급에 대한 내림차순으로 정렬했을 때, 급여를 가장 많이 받은 사람 3명의 아이디, 이름, 급여 조회
SELECT employee_id, first_name, salary
FROM (SELECT employee_id, first_name, salary
FROM employees
ORDER BY salary desc)
WHERE rownum <= 3;
-- 분석함수
-- rank(), dense_rank(), row_number()
-- SELECT 분석함수() OVER ([PARTITION BY 컬럼] [ORDER BY 컬럼]), 컬럼, 컬럼, 컬럼
-- FROM 테이블명
-- rank(), dense_rank(), row_number()으로 랭킹, 순번 부여하기
SELECT RANK() OVER (ORDER BY salary DESC) ranking,
DENSE_RANK() OVER (ORDER BY salary DESC) dense_ranking,
ROW_NUMBER() OVER (ORDER BY salary DESC) row_number,
employee_id, first_name, salary
FROM employees;
-- 각 부서에서 급여를 가장 많이 받는 사람 조회하기
SELECT department_id, employee_id, first_name, salary
FROM (SELECT RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) ranking,
employee_id, first_name, department_id, salary
FROM employees
ORDER BY department_id, salary DESC)
WHERE ranking = 1
AND department_id IS NOT NULL;
-- 입사년도별 급여를 가장 많이 받는 사람 조회하기
SELECT hire_year, employee_id, first_name, salary
FROM (SELECT RANK() OVER(partition by to_char(hire_date, 'yyyy') ORDER BY salary DESC) ranking,
TO_CHAR(hire_date, 'yyyy') hire_year, employee_id, first_name, salary
FROM employees)
WHERE ranking = 1;
-- row_number() 분석함수를 사용해서 순번을 부여하고, 특정 범위의 데이터를 조회하기
-- 데이터에 대한 페이징처리시 사용되는 쿼리문
SELECT row_number, employee_id, first_name
FROM (SELECT ROW_NUMBER() OVER (ORDER BY employee_id) row_number, employee_id, first_name
FROM employees)
WHERE row_number >= (? - 1) * 10 + 1 and row_number <= ? * 10;
트랜잭션
- 논리적인 작업 단위를 구성하는 데이터 조작 명령문의 모음
- 트랜잭션 처리 대상
- 데이터 조작어(DML)
- 데이터베이스 테이블에 데이터를 추가, 갱신 또는 삭제할 때 사용하는 명령어
- INSERT, UPDATE, DELETE 명령
- 데이터 조작어(DML)
- 사용예제
- 이체하기
- 내 계좌의 잔액 감소 - UPDATE
- 상대방 계좌의 잔액 증가 - UPDATE
- 내 계좌에 이체 발송 내용 추가 - INSERT
- 상대방 계좌에 이체 수신 내용 추가 - INSERT
- 이체하기는 4번의 DML 작업으로 구성된다. <---- 논리적인 작업단위
- 구매하기
- 구매 정보 저장 - INSERT
- 배송 정보 저장 - INSERT
- 결재 정보 저장 - INSERT
- 상품 재고 감소 - UPDATE
- 상품 판매지수 증가 - UPDATE
- 고객의 포인트 증가 - UPDATE
- 구매하기는 6번의 DML 작업으로 구성된다. <---- 논리적인 작업단위
- 이체하기
트랜잭션의 사용 목적
- 트랜잭션내의 명령문 중 하나라도 실행되지 못하면 나머지 명령문도 모두 데이터베이스 반영이 취소되어야 한다.
- 트랜잭션내의 명령문이 모두 성공적으로 실행되면 데이터베이스에 영구적으로 실행결과를 영구적으로 반영시킨다.
- 데이터의 일관성이 깨지지 않도록 하는 것이 목적이다.
트랜잭션 처리 명령어
- commit
- 트랜잭션 내의 DML 실행결과를 데이터베이스에 반영한다.
- rollback
- 트랜잭션 내의 DML 실행결과의 데이터베이스 반영을 전부 취소한다.
트랜잭션의 시작과 종료
- 트랜잭션의 시작
- 첫번째 DML 명령이 실행될때 새 트랜잭션 자동으로 시작된다.
- 트랜잭션의 종료
- commit 또는 rollback 명령이 실행될 때 기존 트랜잭션이 종료된다.
- 트랜잭션이 종료되면 새로운 트랜잭션이 자동으로 시작된다.
- SQL 명령어 편집툴을 종료할때 기존 트랜잭션이 종료된다.
- 시스템에 장애가 발생할 때 기존 트랜잭션이 종료된다.
commit과 rollback의 이해
- commit/rollback 실행 이전
- 현재 사용자는 select문을 사용해서 DML작업결과를 검토할 수 있다.
- 현재 사용자의 DML작업결과를 다른 사용자는 볼 수 없다.
- 현재 사용자가 작업하는 관련행은 행잠금 때문에 다른 사용자가 관련행의 데이터를 변경할 수 없다.
- 데이터를 이전상태로 되돌릴 수 있다.
- commit 실행 후
- DML 작업 결과가 데이터베이스에 영구적으로 저장된다.
- 모든 사용자가 작업 결과를 볼 수 있다.
- 관련행의 행잠금이 해제되어 다른 사용자가 행을 조작할 수 있다.
- rollback 실행 후
- DML 작업 결과의 반영이 취소된다.
- 데이터가 이전상태로 복구된다.
- 관련행의 행잠금이 해제된다.
트랜잭션의 성질
- 원자성(Atomicity)
- 트랜잭션과 관련된 작업들은 부분적인 성공을 허용하지 않는다.
- 일관성(Consistency)
- 트랜잭션이 종료되면 언제나 데이터는 일관성을 유지하는 상태가 된다.
- 고립성(Isolation)
- 트랜잭션 수행 시 다른 트랜잭션이 끼어들지 못한다.
- 지속성(Durability)
- 성공적으로 수행된 트랜잭션은 데이터베이스에 영구적으로 반영된다.
