리닝's reframe

트랜잭션(Transaction)이란? 

-여러작업들을 하나로 묶은 단위

-한 덩어리의 작업들은 모두 실행되거나, 실행되지 않습니다. (all-or-nothing)

ACID란? 

-데이터의 유효성을 보장하기 위한, 트랜젝션의 특징들의 앞글자를 딴 단어

- Atomicity(원자성)

    중간 단계까지 실행되는 것이 아니라 처음부터 끝까지 완전하게 실행되며 중간에서 실패하는 일이 없도록 합니다.

    모든 작업이 반영되거나 모두 롤백되는 특성입니다.

- Consistency(일관성) 

    트랜잭션이 완료되면 언제나 일관된 DB 상태를 유지하는 것을 의미합니다.

    데이터는 미리 정의된 규칙에서만 수정이 가능하도록 합니다. 숫자 컬럼에 문자열값이 저장되는 것을 방지해줍니다.

- Isolation(고립성, 격리성) 

    다른 트랜잭션의 연산 작업이 끼어들지 못하도록 보장하는 것을 의미합니다.

    다시말해 다른 트랜잭션의 연산이 중간 단계의 데이터를 볼 수 없음을 의미합니다.

- Durability(영구성, 지속성)

    성공적인 트랜젝션은 영원히 반영(커밋, commit) 되어야 함을 의미합니다.

트랜잭션의 격리성(Transaction Isolation) 이란? 

- 실행중인 트랜잭션의 중간결과를 다른 트랜잭션이 접근할 수 없다! 

<격리성으로 인해 나타날 수 있는 문제점>

Dirty Read

- 다른 트랜잭션에 의해 수정되었지만 아직 커밋되지 않은 데이터를 읽는 것 

Non-Repeatable Read

- 한 트랜잭션 내에서 같은 Key를 가진 행을 두번 읽었는데 그 사이에 값이 변경되거나 삭제되어 결과가 다르게 나타나는 현상

Phantom Read

- 한 트랜잭션 내에서 같은 쿼리를 두번 수행했는데, 첫 번째 쿼리에서 없던 유령(Phantom) 레코드가 두번쨰 쿼리에서 나타나는 현상

<격리성 수준(Transaction Isolation Level)>

병려 트랜잭션 시 특정 트랜잭션이 다른 트랜잭션에서 변경하는 데이터를 언제부터 볼 수 있는지 허용하는 수준을 의미한다. 

Read Uncommitted

- 모든 트랜잭션은 다른 트랜잭션의 커밋 여부와 상관없이 변경되는 데이터를 확인 가능하다. 

- 데이터 정합성이 좋지 않은 격리 수준이므로 사용하지 않는다. 

- Diry Read, Non-Repeatable Read, Phantom Read 현상이 발생한다. 

Read Committed

- 각 트랜잭션은 다른 트랜잭션의 커밋 완료된 데이터만 확인 가능하다. 

- 주로 사용되는 격리 수준이다. 

- Non-Repeatable Read, Phantonm Read 현상이 발생한다. 

Repeatable Read

- 한 트랜잭션이 시작되면 다른 트랜잭션에서 커밋 데이터가 있더라도 확인되지 않는다. 

- 트랜잭션 별로 식별자를 주고 Undo 영역의 데이터를 백업해둠으로써 트랜잭션의 일관성을 보장한다. 

- Phantom Read 현상이 발생한다. 

Serializabl Read

- 가장 높은 수준의 격리수준이다. 

- 한 트랜잭션에서 사용하는 데이터는 다른 트랜잭션에서 사용 불가능하다.

- 격리 수준에 따른 문제는 발생하지 않지만, 동시성이 저하되는 문제가 발생한다. 

- 레코드 조회 : Shared Lock 획득시에만 조회 가능

- 레코드 수정 : Exclusive Lock 획득시에만 조회 가능 

DDL(Data Definition Language : 데이터 정의어) 

- 스키마, 도메인, 테이블, 뷰, 인덱스를 정의하거나 변경 또는 삭제할때 사용하는 언어

- 논리적 데이터 구조와 물리적 데이터 구조의 사상을 정의 

- 데이터 베이스 관리자나 데이터 베이스 설계자가 사용함 

*DDL 설명 : https://reframeurmind.tistory.com/4

DML(Data Manipulation Language : 데이터 조작어)

- 데이터베이스 사용자가 응용 프로그램이나 질의어를 통하여 저장된 데이터를 실질적으로 처리하는데 사용하는 언어

- 데이터베이스 사용자와 데이터베이스 관리 시스템 간의 인터페이스를 제공

<SELECT>

* SELECT 문 실행 순서 

FROM - WHERE - GROUP BY - HAVING -SELECT - ORDER BY

<INSERT>

<UPDATE>

<DELETE>

DELETE (DML)와  TRUNCATE(DDL)

-DELETE 는 롤백(ROLLBACK)이 가능

-TRUNCATE 는 롤백이 불가능, 속도가 빠름 

*DML 설명 : https://reframeurmind.tistory.com/5

DCL(Data Control Language : 데이터 제어어) 

- 데이터의 보안, 무결성, 회복, 병행 수행 제어 등을 정의하는데 사용되는 언어 

- 데이터베이스 관리자가 데이터관리를 목적으로 사용 

*DCL/TCL 설명 : https://reframeurmind.tistory.com/6

TCL(Transaction Control Language : 트랜잭션 제어어) 

- DCL 에서 트랜잭션을 제어하는 명령어

DUAL 테이블(Dual Table)

- DUAL 테이블은 데이터 딕셔너리와 함께 오라클에 의해 자동 생성되는 테이블

- DUAL 테이블은 사용자가 SYS의 스키마에 있지만 모든 사용자는 DUAL이라는 이름으로 엑세스 할 수 있다 

- DUAL 테이블 VARCHAR2(1)으로 정의된 DUMMY라는 하나의 열이 있으며 값을 가지는 하나의 행도 포함되어 있다. 

DUAL 테이블의 특성

- 사용자 SYS가 소유하며 모든 사용자가 엑세스 가능한 테이블 

- SELECT ~ FROM ~의 혀식을 갖추기 위한 일종의 DUMMY 테이블이다. 

- DUMMY 라는 문자열 유형의 칼럼에는 'X'라는 값이 들어있는 행을 1건 포함하고 있다. 

*함수나 현재 시간의 값 등 특정 테이블에서의 데이터가 아닌 사실데이터를 추출하기 위해서는 SELECT ~ FROM ~절에서 

FROM DUAL 의 형태로 구문을 작성해 사용한다. 

온리인에서 SQL 문을 시험해볼 수 있는 사이트 

: http://sqlfiddle.com/#!4/75ac2b

<연산자> 

연산자는 주로 WHERE 절에 사용되며 연산자의 종류에 따라 우선순위가 발생한다. 

-산술 연산자 (+, -, * , /) : 0으로 나눌 수 없으며 '정수/정수 = 실수' 이다. 

- 비교 연산자

- 논리 연산자 : NOT, AND, OR

- LIKE 연산자 : 유사 문자열 검색

<예시>

LIKE 'A%' -> A로 시작하는 모든 문자열

LIKE '%A' ->A로 끝나는 모든 문자열 

LIKE'%A%' ->A가 포함된 모든 문자열

LIKE '___A%' -> 3번째 글자가 A인 모든 문자열 ->결과 : BLAKE, CLARK, ADAMS

LIKE '__O__' -> 총 5글자이며 그 중 3번째 글자가 O인 문자열 ->결과 : SCOTT

- 연산자 우선순위 

* 산술 > 관계 > 논리 연산자 순으로 연산자 우선순위가 정해짐

+ 플러스 !

-IN : 포함된 데이터를 가지고 올때 

WHERE DEPTNO = 10 OR DEPTNO =20 OR DEPTNO =30; 

아래와 위의 값은 같다. 

WHERE DEPTNO IN (10, 20, 30)

OR 가 여러개일때 IN 을 사용하면 유용하다. 

- BETWEEN ~ AND : 범위, 기간

SELECT * FROM EMP WHERE EMPNO BETWEEN 7000 AND 8000; 

사원 번호(EMPNO가 7000에서 8000사이에 있는 직원들의 정보를 전부 조회 

상기 두개의 연산자는 'NOT'과도 같이 쓰인다. 

- Null의 연산 

Null값을 가진 필드의 연산은 무조건 Null 값이 된다. 

예) SELECT CAL1 + CAL2 FROM TABLE1;  

이라면은 값은 50, null, null 이 된다. 첫번째 행은 숫자끼리의 연산이기에 그대로 더하기를 해주면 되지만, 나머지 행의 경우 Null값이 포함되어있기에 결과값은 null이다. 

다만, SUM()같은 집계함수에서는 Null은 값이 없는 것으로 처리되어서 영향을 주지 않는다. 

내장함수(Stored Function) 란? 

* SQL 함수라고도 한다. 

장함수란 DBMS에서 제공하는 함수로 특정 값이나 열의 값을 입력 받아서 계산하여 결과값을 보여준다. 

- 단일 행 함수(Single Row Function): 문자형, 숫자형, 날짜형, 변환형

- 다중 행 함수(Multi Row Function): 그룹 함수

- 사용될 수 있는 위치 : SELECT 절, WHERE 절, START WITH 절, HAVING 절, INSERT문의 INTO절, UPDATE문의 SET 절

- 1:M 의  조인이라 하더라도 M쪽에서 출력된 행이 하나의 단일행 함수의 입력값으로 사용 가능

- 다중행 함수도 단일행 함수와 동일하게 단일 값만 반환함

*여러 함수를 겹쳐서 사용 가능

<그룹함수(GROUP FUNCTION)> 

하나 이상의 행을 그룹으로 묶어 연산하여 총합, 평균 등 하나의 결과로 나타낸다. 

NULL을 제외하고 계산한다. 

GROUP BY 예시! 
SELECT DEPTNO(부서번호), MAX(SAL), MIN(SAL)

FROM EMP(테이블명)

GROUP BY DEPTNO; 

->부서별 최대/최소 월급을 구하기 위해서는 위와 같이 GROUP BY 를 통해 그룹별로 묶어서 사용 할 수 있다. 

HAVING 예시!

SELECT DEPTNO(부서번호), MAX(SAL), MIN(SAL)

FROM EMP(테이블명)

GROUP BY DEPTNO;

HAVING MAX(SAL) > 2000; 

->부서별 월급의 최소값과 최대값을 구하는데 최대 월급이 2000보다 클 경우에만 표시한다. 

<윈도우함수(WINDOW FUNCTION)>

분석함수 또는 순위 함수로도 알려져있으며 GROUP BY 절을 사용하지 않고 속성의 값을 집계할 수 있는 함수를 말한다. 

다른 함수들 처럼 중첩해 사용할 수 없지만 서브쿼리에서 사용이 가능하다. 

출처 : https://for-my-wealthy-life.tistory.com/48

 DCL(데이터 제어어 : Data Control Language) 란? 

- 데이터베이스에 권한을 부여하는 언어 

* 데이터베이스 테이블에 직접적인 영향을 미치기 때문에 DCL 을 입력하는 순간 명령어에 해당하는 작업이 즉시 (AUTO COMMIT)된다. 

<예시>

GRANT 

GRANT 권한 종류 ON 대상 TO 계정명 IDENTIFIED BY 암호 [WITH GRANT OPTION];

-- 모든 권한을 가진 계정 생성

GRANT ALL ON *.* to root2 IDENTIFIED by 1234; 

--특정 데이터베이스에 조회권한을 가진 계정 생성

GRANT SELECT ON test.*(권한 종류) TO root3 IDENTIFIED BY 12345; 

-- GRANT로 계정생성 후 아래의 명령어를 실행해야 적용된다. 

FLUSH privileges;

REVOKE

-- 권한 해제 명령어

REVOKE insert, update, create ON [DB명.테이블명] TO [계정명];

--전체 권한 해제 명렁어

REVOKE ALL ON [DB명.테이블명] TO [계정명];

TCL(트랜젝션 제어어 : Transaction Control Language)란? 

- 데이터의 보안, 무결성, 회복, 병행, 수행제어 등을 정의하는데 사용 

DML( 데이터 조작어 : Data Manipulation Language) 란?

-  SQL에서 기본적으로 테이블의 입력(INSERT), 수정(UPDATE), 삭제(DELETE), 조회(SELECT)

- 데이터베이스의 데이터를 관리하는데 사용, 자동으로 커밋(저장, Commit)되지 않는다. 

- 즉, DML명령에 의한 변경은 데이터 베이스에 영구적이지 않으로 롤백(RollBack)할 수 있다. 

(* 다만, SQL Server의 경우 DML 도 Auto Commit(자동 저장) 되기에 따로 Commit(저장)을 할 필요가 없다.) 

DDL( 데이터 정의어 : Data Definition Language) 이란? 

- 물리적 DB 객체의 구조를 정의하는데 사용

- 가장 대표적으로 테이블을 생성하는 일 

제약조건(Constraint)이란?

- 테이블에 입력되는 데이터가 사용자가 원하는 조건에 만족하는 데이터만 입력 될 수 있도록 하는 것

- 칼럼마다 설정한다. 

CREATE TABLE : 신규 테이블을 생성하는 SQL 문

<예시>

CREATE TABLE STUDY(  - 테이블의 이름을 명시

ID INT NOT NULL,   - 칼럼명과 데이터 속성, 

NAME VARCHAR(45)

);

<주의사항>

- 테이블명은 다른 테이블과 중복되어서는 안된다. 

- 한 테이블에 같은 컬럼명이 존재하면 안된다. 

- 데이터의 속성(데이터형)은 꼭 지정해주어야 한다. 

- 모든 이름(칼럼, 테이블)은 반드시 분자로 시작

- 테이블명에 사용하는 문자는 영문(대소문), 숫자, _(언더바), $(달러), #(샾) 문자만 해당된다. 

ALTER TABLE : 칼럼 및 제약조건을 추가/수정/제거

<예시>

칼럼 추가 : ALTER TABLE STUDY ADD (CLASS VARCHAR(45));

(해당 테이블에 칼럼을 추가한다.)

칼럼 제거 : ALTER TABLE STUDY DROP COLUMN CLASS;

(해당 테이블에서 원하는 컬럼을 제거한다.)

칼럼 변경 : ALTER TABLE STUDY MODIFY (CLASS VARCHAR(30)
                                                                         DEFAULT 0 NOT NULL

                                                                         NOVALIDATE );

SUBJECT 칼럼의 속성을 변경하며 제약조건을 추가,

기본값(DEFAULT)으로 0 을 주었고 NULL이 허용되지 않는다. (NOT NULL)

NOVALIDATE 는 이미 해당 칼럼에 NULL로 데이타가 있어도 무시하고 이 다음부터 새로 추가되는 데이터만

NULL 을 불허한다. 

칼럼명 변경 : ALTER TABLE STUDY RENAME CLASS TO SUBJECT;

외래키 생성 : 

(다른 테이블은 STUDENT로 가정하고 기본키는 STUDENT_NUMBER로 가정)

ALTER TABLE STUDY ADD CONSTRAINT FK_SUBJECT - STUDY 테이블에 제약조건(Constraint)를 추가

FOREIGN KEY (ID) -STUDY 테이블의 기본키인 ID 를 외래키로 지정

REFERENCES STUDENT (STUDENT_NUMBER) -STUDENT 테이블의 STUDENT_NUMBER  칼럼을 참조

외래키 제거 :

ALTER TABLE STUDY DROP CONSTRAINT FK_SUBJECT;

DROP TABLE : 테이블 제거

DROP TABLE SCHOOL;

DROP TABLE STUDENT;

RENAME : 테이블명 변경

RENAME STUDY TO SCHOOL;

TRUNCATE : 테이블 내의 데이타 삭제 

TRUNCATE TABLE SCHOOL;