2020. 5. 11. 10:29ㆍDB
*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 되는 경우
=>사용 중에 정전이나 컴퓨터의 고장으로 인한 SHUT DOWN 의 경우에는 ROLLBACK 됩니다.
5.프로그래밍 언어에서 데이터베이스를 사용하는 경우 그 언어나 프레임워크의 기본 트랜잭션 동작에 대해서 미리 알아보고 프로그래밍을 해야 함.
JAVA에서 데이터베이스를 사용할 때는 기본적으로 AUTO COMMIT 이고 JAVA에서도 HIBERNATE 와 같은 데이터베이스 연동 프로그램을 사용할 때는 MANUAL COMMIT(수동 커밋)임.
6. TRANSACTIOM 적용 모드
1) AUTO COMMIT: sql명령어가 실행되면 무조건 commit을 수행하는 방식
java나 dbeaver의 기본 모드
2) manual commit: dml이나 dql의 경우는 직접 commit이나 rollback을 해야만 적용되는 방식
-> 다른 프로그래밍언어나 대다수의 접속프로그램의 기본 모드
-> 프로그램에 적용할 때도 대부분 이 방식을 사용
CONSTRAINT(제약 조건)
- 디비에 잘못된 데이터가 삽입되지 않도록 디비의 열들에 설정하는 여러 규칙
제약조건 확인
- 제약조건은 user_constraints 테이블에서 확인 가능
- constraint type이 종류를 의미
P: primary key
R: foreign key
U: unit power를 가져버린 나는 그냥 do it
C: check, not null
제약조건 설정
1) 행 레벨 제약조건 설정: create table구문에서 행을 만들때 기재
2) 테이블 레벨 제약조건 설정: 행을 다 만들고 제약조건을 설정
not null
- 필수 입력을 하고자 하는 경우에 사용
- 행 레벨에서만 설정 가능
- null은
unique
- 유일성
- null은 제외 -> null은 여러개 일 수 있음
primary key
- not null 이고 unique
- 한개 이상의 열로 만듦 => 2개 이상의 열로 만들 수도 있음
- 테이블에서 한개만 생성 가능
- 테이블에서 행을 구분하기 위한 유일무이한 속성 또는 그 집합
check
- 값의 범위나 조건을 설정
- 컬럼이름 조건의 형태로 입력
- gender는 m or f의 값만을 가져야함.
gender char(1) check(gender in ('m','f'))
- score는 0-100까지 사이의 값이어야 한다.
score number(3) score between 0 and 100
ex) 회원정보를 저장하는 테이블
email 변하지 않는 문자열 50자(한글 없음) - primary key
name 변하지 않는 문자열 10자(한글) - 필수 입력
phone 변하는 문자열이지만 글자수는 항상 11자(한글없음)
gender 영문 한글자로 m,f만 가능
address 변하는 문자열로 한글 100자 이내
description 변하는 문자열로 한글 1000자 이내 - 긴 문자열
row migration: varchar를 함부로 쓰면 안되는 이유 => 메모리의 이미 할당중인 다른 영역에서 공간을 빌려오는 경우가 생길 수 있음
기존에 공간에 3글자를 할당중인데 4글자로 변경할 경우.
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 관계일 때는 양쪽 테이블의 기본키를 외래키로 갖는 별도의 테이블을 생성
- 회원 테이블과 게시판 테이블이 존재하는 경우
회원 한명이 여러 개의 게시글을 작성할 수 있음
게시글 한개는 한명이 작성해야 함.
회원과 게시판의 관계는 1:N임.
회원 테이블의 기본키를 게시판 테이블의 외래키로 추가해주어야 하고 회원 테이블이 부모 테이블이 되고
게시판 테이블이 자식 테이블이 됨.
- 회원 테이블과 상품 테이블이 존재하는 경우
회원 한명은 여러 개의 상품을 구매할 수 있음
동일한 상품을 여러 명이 구매할 수 있음.
이런 경우는 N:N
회원 테이블의 기본키와 상품 테이블의 기본키를 외래키로 갖는 별도의 테이블을 생성해야 함.
회원 테이블을 참조하는(email을 외래키로) 게시판 테이블을 생성)
- 글번호(정수 10자리), 제목(가변, 한글 100자까지), 내용(긴 문자), 작성일(날짜), 조회수(정수 10자리)로 구성
create table board(
num number(10),
title varchar2(300),
content clob,
writedate date,
readcnt number(10),
email varchar(20) references userinfo(email));
- 외래키가 설정되면 부모 테이블의 데이터를 삭제하거나 부모 테이블을 제거하려고 하면 에러가 날 수 있음.
*해당 칼럼의 제약 조건을 조회하는게 아니고 테이블 자체를 삭제하거나 테이블 자체의 모든 데이터를 삭제하는것이기 때문
8. Default
- 데이터를 입력하지 않았을 때 대입하는 기본값
- default 기본값 형식으로 추가하면 됨.
- 작성일 같은 경우는 직접 입력받는것보다는 sysdate가 자동으로 대입되도록 해주는 것이 좋음.
- 조회수도 기본값으로 0을 설정하는것이 바람직
create table board(
num number(10),
title varchar2(300) default '무제',
content clob,
writedate date,
readcnt number(10),
email varchar(20) references userinfo(email));
9. Intigrity
1) entity integrity(개체 무결성): 기본키는 null이거나 중복될 수 없다.
2) Reference integrity(참조 무결성): 외래키는 null이거나 참조할 수 있는 값만을 가져야 함.
=> 디비와 관련된 시험을 치를 때 반드시 기억(E-R Diagram, transaction acid, integrity)