3장 SQL 기본

관계형 데이터 베이스 (Relational Database)

- 정규화 이론에 근거한 합리적인 데이터 모델링을 통해 데이터 이상현상 및 불필요한 데이터 중복 현상을 피할 수 있다

 

동시성 관리 및 병행 제어 -> 많은 사용자들이 동시에 데이터를 공유 및 조작할 수 있는 기능 제공

메타 데이터 총괄 관리 -> 데이터의 성격, 속성, 표현방법 체계화 / 데이터 표준화를 통한 데이터 품질을 확보 

데이터 무결성을 보장 -> 중요 데이터 삭제 방지

데이터 회복 / 복구 기능 제공

 

SQL (Structured Query Language)

- 관계형 데이터베이스에서 데이터 정의, 데이터 조작, 데이터 제어를 하기 위해 사용하는 언어

 

데이터 조작어 DML

- SELECT : 데이터베이스 내 데이터를 조회하거나 검색

- INSERT : 데이터 삽입

- UPDATE : 데이터 수정

- DELETE : 데이터 삭제

 

데이터 정의어 DDL

- CREATE : 테이블이나 인덱스, 뷰등을 생성

- ALTER : 테이블이나 인덱스, 뷰등의 구조 변경

- DROP : 테이블이나 인덱스, 뷰 등의 구조 삭제

- RENAME : 테이블이나 인덱스, 뷰등의 이름 변경

- TRUNCATE : 테이블이나 인덱스,뷰 내 값들을 모두 지움 -> 자체가 사라지지는 않음

 

데이터 제어어 DCL

- GRANT : 권한 부여

- REVOKE : 권한 회수

 

트랜잭션 제어어 TCL

- COMMIT : 트랜잭션 완료

- ROLLBACK : 트랜잭션 취소

 

 

테이블 : 칼럼과 행의 2차원 구조를 가진 데이터 저장소

칼럼 : 테이블에서 세로방향의 하나하나 속성

행 : 테이블에서 가로방향으로 이루어진 데이터

정규화 : 테이블을 분할하여 데이터의 정합성을 확보하고, 불필요한 중복을 최소화

기본키 : 테이블에 존재하는 각 행을 한가지 의미로 특정할 수 있는 1개 이상의 칼럼

외래키 : 다른 테이블의 기본키를 참조하는 칼럼

 

ERD : ENTITY  RELATIONSHIP  DIAGRAM

ENTITY : 개체(엔터티)

RELATIONSHIP : 관계

ATTRIBUTE : 속성

 

(개념적 데이터 모델링)

 

주요 데이터 형

 

1) CHAR(L) : 고정길이 문자열 , 저장된 값이 L보다 작을 경우 그만큼 공백으로 채워짐

 

2) VARCHAR2(L) : 가변길이 문자열, L만큼 최대길이, L보다 작을 경우 입력 값만큼만 차지 , 공백도 문자로 취급

 

3) NUMBER(L,D) : 정수 및 실수를 저장 L : 전체 자릿수, D: 소수점 자릿수

 

4) DATE :  날짜 시각정보 저장 , 년월일시분초

 

제약 사항

1) 기본키 : 테이블에 저장된 행들 중 특정 행을 고유하게 식별 하는 키 / 한 테이블에는 하나의 기본키만 / 유일 인덱스 자동 생성 / NULL 입력 불가 / UNIQUE + NOT NULL

 

2) 고유키 : 테이블에 저장된 행들 중 특정 행을 고유하게 식별 하는 키 / NULL 입력 가능 / UNIQUE + NULL

 

3) NOT NULL : NULL 값 허용 안함 / 필수 값 칼럼

 

4) CHECK : 입력할 수 있는 값의 종류 혹은 범위 제한

 

5) 외래키 : 다른 테이블의 기본키를 참조하는 칼럼 / 참조 무결성 제약조건

 

6) 기본값, 디폴트 값 : 해당 칼럼에 아무 값도 안넣었을때 나오는 값

 

 

 

CREATE TABLE

- 테이블을 생성

 

주의) 테이블명은 단수형 / 테이블명은 특정 사용자가 사용하는 다른 테이블명과 겹치면 안됨 / 한 테이블 내 동일한 칼럼명 존재 불가 / 생성문 끝은 ;으로 끝남 / 칼럼의 데이터 형은 반드시 지정 / 테이블명과 칼럼명은 반드시 문자로 시작 / A-Z a-z 0-9 _ $ #만 사용 가능

 

CREATE TABLE TT

(

     TEST VARCHAR2(10) NOT NULL                   // NOT NULL -> NULL값 허용 안함

     TESTCASE VARCHAR2(20) NOT NULL

);

 

 

ALTER TABLE

- 테이블 구조 변경

 

1) 칼럼 추가

ALTER TABLE TT ADD (TESTTEST CHAR(2)); // TT 테이블에 TESTTEST라는 칼럼 추가

 

2) 칼럼 제거

ALTER TABLE TT DROP COLUMN TESTTEST; // TT테이블에서 TESTTEST 칼럼 제거

 

3) 데이터형 및 제약조건 변경

ALTER TABLE TT ADD(TESTTEST CHAR(2) NULL); // TT테이블에 TESTTEST라는 CHAR형 칼럼 생성, NULL값 허용

ALTER TABLE TT MODIFY( TESTTEST NUMBER(1) DEFAULT 0 NOT NULL NOVALIDATE);

// TESTTEST 칼럼을 NUMBER 자료형으로 변경, 디폴트 값 0 , NULL값 허용 안함, NOVALIDATE => MODIFY하려는 칼럼의 값 중에 만약 NULL값이 있어도 NOT NULL을 줄 수 있다.

 

4) 칼럼명 변경

ALTER TABLE TT RENAME COLUMN TESTTEST TO TEST; // TT테이블 안에 TESTTEST라는 칼럼을 TEST로 이름 변경

 

5) 외래키 생성

ALTER TABLE TT ADD CONSTRAINT FK_TEST // TT 테이블에 참조 무결성 제약 조건 생성

FOREIGN KEY (TESTTEST) REFRENCES NN (NESTNEST); // TT테이블의 TESTTEST는 NN테이블의 NESTNEST 칼럼을 참조

 

6) 외래키 제거

ALTER TABLE TT DROP CONSTRAINT FK_TEST; //TT 테이블에 외래키 제거

 

DROP TABLE

- 테이블 삭제

 

DROP TT; //TT테이블 삭제

 

 

RENAME TABLE

- 테이블 이름 변경

RENAME TT TO NN; //TT테이블을 NN으로 이름 변경

 

TRUNCATE TABLE

- 테이블 내 데이터 삭제

 

TRUNCATE TT; // TT테이블 내 데이터 삭제

 

 

 

 

 

INSERT 문

- 테이블에 데이터를 신규로 입력

 

INSERT INTO TT A(A.a,A.b,A.c) VALUES ('1','2','3')  

COMMIT; // DB에 최종적으로 적용

 

 

UPDATE문 

- 테이블 내 행의 칼럼 값 수정

 

UPDATE TT A SET A.a = '100', A.b = '20' WHERE A.c = '3' ; 

COMMIT;

 

 

DELETE문

- 테이블 내 행의 칼럼 값 제거

 

DELETE FROM TT A WHERE A.a = '100' ;

COMMIT;

 

 

SELECT문

- 테이블에서 데이터를 조회

 

SELECT A.a , A.b , A.c FROM TT A WHERE A.a = '100';

 

 

 DISTINCT : 중복된 행이 제거되어 유일한 값을 가진 행만 출력

           

 SELECT DISTINCT A.a FROM TT A;

 

 

ALIAS (앨리어스) : 별칭을 지정

 

SELECT A.create AS C

FROM TT A

WHERE A.a = '100';

 

합성 연산자 ( | | )

- 여러개의 칼럼을 합쳐서 하나의 칼럼 값으로 출력

 

SELECT A.a || A.B || A.C  AS TOTAL

FROM TT A;

 

 

DUAL 테이블

- 오라클 DB 기본 테이블

- 단 1건의 데이터만 저장이 되어있음

- 칼럼은 DUMMY 단 1개

- 저장되어있는 칼럼값은 X

 

SELECT ( (1+1) * 3) / 6  AS "연산결과 값"

FROM DUAL;

 

연산결과 1이 DUAL의 DUMMY 칼럼을 통해서 나온다.

 

 

 

 

트랜잭션 특징 ACID

1) 원자성(Atomicity) :  트랜잭션의 연산들은 모두 성공하거나 모두 실패해야한다. (All or Nothing)

2) 일관성(Consistency) : 트랜잭션이 실행되기 전 DB의 내용이 잘못되지 않았으면, 실행 후에도 잘못되지 않아야 한다.

3) 고립성(Isloation) : 트랜잭션이 실행되는 도중 다른 트랜잭션의 영향을 받아선 안된다.

4) 지속성(Durability) : 트랜잭션이 성공적으로 수행되면 그 트랜잭션이 갱신한 데이터베이스의 내용은 영구적으로 저장된다.

 

 

COMMIT : 커밋 -> DB에 최종적으로 저장하며 트랜잭션을 완료

 

ROLLBACK : 롤백 -> 트랜잭션을 취소

 

 

커밋/ 롤백 전

- 최종 적용 상태가 아니고, 메모리에만 적용된 상태이므로 변경 이전으로 복구 가능

- 사용자 외에 다른 사용자는 입력 수정 삭제 결과확인 x

- 락이 형성되어 다른 사용자가 변경 못함

 

커밋/롤백 후

- DB에 완전 반영 -> 이전 데이터는 유실

- 모든 사용자가 행들을 조작

- 모든 사용자가 입력 수정 삭제 결과를 확인 가능

 

 

SAVEPOINT

= 롤백 시 되돌아가는 지점을 정해주는 것

 

ROLLBACK TO SVPT3;

 

 

트랜잭션의 요점

- 커밋 : 변경된 데이터를 테이블에 영구 반영하라

- 롤백 : 변경 전으로 복구하라

- SAVEPOINT : 데이터 변경 사항의 취소를 지정한 지점까지만 취소하라

 

 

커밋/롤백과는 상관없이 트랜잭션이 적용 처리되는 상황

1) DDL 실행 시, 실행전과 실행 후 시점에 자동 COMMIT

2) DML 실행 후, COMMIT을 하지 않고 DDL을 실행 시 -> DDL 실행 전에 DML 변경사항이 COMMIT

3) DB에 접속 종료 시, 이전 DML 변경 내용을 COMMIT

4) 애플리케이션 동작 오류 -> 접속 단절 시 데이터의 변경 사항이 자동으로 ROLLBACK

 

CF) DML문은 COMMIT을 써줘야 반영이 된다.

 

 

 

SELECT-WHERE절

: SELECT문의 조건을 거는 부분

 

연산자의 종류

1) 비교 연산자

 =  같다 

 >  ~보다 크다   

 >=   ~보다 크거나 같다   

 <   ~보다 작다 

 <=  ~보다 작거나 같다

 

2) SQL 연산자

BETWEEN A AND B  : A와 B 사이에 있으면 된다

IN (리스트) : 리스트에 있는 값 중 하나라도 있으면 된다

LIKE '비교문자열' : 비교문자열의 형태와 일치하면 된다 (와일드 카드)

IS NULL : NULL이면 된다

IS NOT NULL :  NULL이 아니면 된다

 

와일드 카드

% : 0개 이상의 어떤 문자

_ : 1개의 단일 문자

 

 

3) 논리 연산자

AND :  앞 조건, 뒤 조건 모두 참

OR :  앞 조건, 뒤조건 중 하나라도 참

NOT : 조건이 거짓이어야 참

 

 

4) 부정 비교 연산자

!=  같지 않다

<> 같지 않다

^= 같지 않다

NOT 칼럼명 = ~칼럼 값이 ~와 같지 않다

NOT 칼럼명 > ~칼럼 값이 ~보다 크지 않다

NOT 칼럼명 < ~칼럼 값이 ~보다 작지 않다

 

 

5) 부정 SQL 연산자

NOT BETWEEN A AND B : A와 B 값 사이에 있지 않다

NOT IN(LIST) : 같지 않다

IS NOT NULL : NULL이 아니다