SQLD 요약 정리 (3)

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

- 1970년대 E.F. Codd박사의 논문에서 처음 소개

- Oracle, MS-SQL, MySQL, Sybase -> 관계형 데이터베이스 지원

 

데이터베이스 종류

-계층형 : 1:N 관계 트리 형태 자료구조 

-네트워크형 : Owner과 Member 형테로 데이터 저장

-관계형 : 릴레이션에 데이터를 저장하고 관리, 릴레이션을 사용해서 집합 연산과 관계연산 가능

 

데이터베이스 관리 시스템 (DBMS) : 데이터베이스를 관리하기위한 소프트웨어

 

집합연산

Union(합집합): 두개의 릴레이션을 하나로 합하는것, 중복된 행(튜플)은 한번만 조회

Difference(차집합): 본래 릴레이션에서 존재하고 다른 릴레이션에 존재하지 않는것을 조회

Intersection(교집합): 두개의 릴레이션간에 공통된 것을 조회

Cartesian Product(곱집합): 각 릴레이션에 존재하는 모든데이터를 조합, 중복허용

 

관계연산

Selection(선택연산)

Projection(투영 연산)

Join(결합연산)

Division(나누기 연산)

 

기본키(primary key) -유일성,최소성(not null),대표성

외래키(Foreign key) -다른테이블의 기본키를 참조하는 칼럼

 

SQL(Structured Query Language)

-데이터의 구조를 정의,조작,제어 할수있는 절차형 언어

-ANSI/ISO 표준 준수하기 때문에 DBMS가 변경되어도 그대로 사용가능

 

SQL의 종류

DDL(Data Definition Language) : create, alter, drop, rename

DML(Data Manipulation Language) : insert, delete, select, update

DCL(Data Control Language) : grant, revoke

TCL(Transaction Control Language): commit, rollback   

※transaction: 트랜젝션은 데이터베이스의 작업을 처리하는 단위

 

트랜잭션의 특성

원자성(Atomicity) : 트랜잭션은 데이터베이스 연산의 전부 또는 아무실행을 안함 all or nothing  트랜젝셩 처리가 완전히 끝나지 않았을 경우 전혀 이루어지지 않는 것과 같다.

일관성(Consistency) : 트랜잭션 실행 후에도 일관성이 유지

고립성(Isolation) : 연산의 중간결과는 다른트랜젝션이 접근할수없음

연속성(Durability) : 완료된 결과는 영구성 보장

 

SQL 실행순서

파싱(parsing) : 구문 분석 -> library cache 에 저장 

실행(execution) : 옵티마이저가 수립한 실행 계획에 따라 sql실행

인출(fetch): 데이터를 읽어서 전송

 

테이블 생성

create table dept(

  deptno varchar2(4) primary key,

  deptname varchar2(20)

); --참조될 마스터 테이블 먼저 생성

 

create table emp(

  empno number(10),

  ename varchar2(20),

  sal number(10,2) default 0,  --소수점 둘쨰자리 까지 저장

  deptno varchar2(4) not null,

  createdate date default sysdate,

  constraint emppk primary key(empno),

  constraint deptfk foreign key(deptno) references dept (deptno) ON DELETE CASCADE 

);

 

desc emp;  --생성된 테이블 구조 보고 싶을때

 

CASCADE 옵션 : 참조관계가 있을 경우 참조되는 데이터도 삭제 , 참조 무결성 준수

 

테이블명 변경 

ALTER TABLE emp RENAME TO new_emp;

 

테이블 변경

ALTER TABLE

 

칼럼추가

ALTER TABLE emp ADD (age number(2) default 1);

 

칼럼 변경

ALTER TABLE emp MODIFY (ename varchar2(40) not null);

 

칼럼삭제

ALTER TABLE emp DROP COLUMN age;

 

칼럼명 변경

ALTER TABLE emp RENAME COLUMN ename TO new_ename;

 

테이블 삭제

DROP TABLE emp CASCADE CONSTRAINT; --제약조건 모두 삭제

-테이블 구조와 데이터 모두 삭제

 

뷰 VIEW : 테이블로부터 유도된 가상 테이블, 실제 데이터를 가지지않고 테이블을 참조하여 원하는 칼럼만 조회. DATA DICTIONARY에 저장하고 실행시 참조

 

뷰의 특징

-참조한 테이블이 변경되면 뷰도 변경

-뷰에 대한 입력,수정,삭제에는 제약잇음

-보안성 향상

-한번생성된 뷰는 변경할수없고 재생성해야됨.

 

뷰 생성

CREATE VIEW T_EMP AS SELECT * FROM EMP;

 

뷰삭제

DROP VIEW T_EMP; -뷰가 삭제됬다고 해도 참조했던 테이블은 삭제되지 않음

 

뷰 장단점

장점

-보안성

-데이터 관리 간단

-조회문 간단

-하나의 테이블에 여러 개의 뷰 생성가능

 

단점

-뷰는 독자적 인덱스 생성 불가

-삽입,수정,삭제 제약

-데이터 구조는 변경 불가

 

DML(Data Manipulation Language) - insert delete update select

 

INSERT 

INSERT INTO emp( ) VALUES(  );

-이것으로 파일에 저장되는것은 아님. 반드시 COMMIT 실행해야됨 OR (SET AUTO COMMIT ON)

 

INSERT INTO dept_test SELECT * FROM dept; -- 조회한것을 바로 삽입 가능

 

Nologging

-데이터베이스에 데이터를 입력하면 로그파일에 그정보를 기록한다.

-check point 라는 이벤트가 발생하면 로그파일의 데이터를 데이터파일에 저장

-Nologging 옵션은 로그파일의 기록을 최소화 시켜서 입력시 성능을 향상시키는 방법, Buffer cache라는 메모리 영역을 생략하고 기록

 

UPDATE

UPDATE emp SET ename ='조조' WHERE empno=100;

 

DELETE

DELETE FROM emp WHERE empno=100; --delete문으로 데이터를 삭제한다고해서 테이블용량이 초기화 되지않음

 

TRUNCATE TABLE emp; --테이블의 데이터와 용량 초기화

 

ORDER BY -정렬로인한 메모리 많이 사용,성능저하 발생  (oracle 은 정렬을 위해서 메모리 내부에 할당된 SORT_AREA SIZE 를 사용함, 이것이작으면 성능저하 발생)

 

SELECT * FROM emp ORDER BY ename, sal DESC; 

DESC: 내림차순

ASC : 오름차순 (default)

 

DISTINCT

중복된 데이터를 한번만 조회

 

SELECT DISTINCT deptno FROM emp ORDER BY deptno;

 

ALIAS -별칭 부여

 

SELECT ename AS "이름" FROM emp a WHERE a.empno=1000;

 

 

WHERE

<> 같지않은것 조회 ( !=  ^=)

 

LIKE

% :포함한 모든문자

_ :단일문자

 

BETWEEN -지정된 범위에 있는 값 조회

 

IN 

job IN ('clerk','manager')

 

NULL

-모르는 값

-값의 부재

-비교는 알수없음으로 반환

-숫자 날짜 더하면 NULL

-비교연산자 하면 False 나옴

 

NULL 함수

NVL : NULL이면 다른값으로 바꿈 NVL(MGR,0)  - NULL이면 0으로

NVL2 : NVL2(MGR,1,0) - MGR이 NULL이 아니면 1, 맞으면 0 반환

NULLIF : NULLIF(exp1,exp2)  exp1 == exp2 면 null, 같지않으면 exp1 반환

COALESCE : COALESCE(MGR, 1) - NULL이 아니면 1반환

 

GROUP BY

 

HAVING - GROUP BY에 조건절 사용할때 사용. WHERE 절에 조건문을 사용하게되면 GROUP BY 대상에서 제외

 

COUNT() 함수 - COUNT(칼럼) 하면 NULL값을 제외한 행의 수 계산

 

 

 

SELECT문 실행 순서

 

SELECT ename                  --  5

FROM emp                      --  1

WHERE empno=10            --  2

GROUP BY ename             --  3

HAVING count(*) >= 1       --  4

ORDER BY ename;             --  6

 

 

형변환 - 두개의 데이터의 데이터타입이 일치하도록 변환하는것

 

명시적 형변환(Explicit) - 형변환 함수 사용

( TO_CHAR(숫자 OR 날짜 , FORMAT) TO_NUMBER(문자열) TO_DATE(문자열,FORMAT) 

 

암시적 형변환(Implicit) - DBMS에서 자동으로 형변환 , ※인덱스 칼럼에 암시적 형변환을 수행하면 인덱스를 사용 못함

 

 

내장형 함수

ACSII

CHAR(ACSII 값)

SUBSTR(문자열,M,N)

CONCAT( ) - MS-SQL : '+'  ORACLE : '||'

LOWER( )

UPPER( )

LEN( ) OR LENGTH( )

LTRIM(문자열,지정문자) -왼쪽 지정된 문자 생략하면 공백 삭제 

RTRIM( )

TRIM( ) 

 

숫자형함수

ABS( ) -절대값

SIGN( ) - 양 음 0 구별

MOD(1, 2) - 1을 2로 나누어 나머지 계산 % 사용가능

CEIL( ) - 숫자보다 크거나 같은 최소정수

FLOOR() - 숫자보다 작거나 같은 최대 정수

ROUND(숫자, M) -M의 자리에서 반올림 , DEFAULT = 0

TRUNC(숫자, M)  - M의 자리에서 절삭

 

DECODE

SELECT DECODE(empno, 1000, 'TRUE', 'FALSE') FROM emp;

 

CASE

 

SELECT CASE 

               WHEN empno = 1000 THEN 'a'

               WHEN empno = 1001 THEN 'b'

               ELSE 'c'

             END

FROM emp;

 

ROWNUM - 한개의 행을 가지고올수있음. 조회문의 결과에서 논리적인 일련번호 부여, 조회되는 행의수 제한

 

SELECT * FROM emp WHERE ROWNUM >2; 

 

SELECT * FROM (SELECT ROWNUM list, ename FROM emp) WHERE list <=5;  --별칭을 사용해야 됨

 

ROWID -데이터가 어떤 데이터 파일, 어떤블록에 저장되어 있는지 알수있음. 오라클 데이터베이스 내에서 데이터 구분할수있는 유일한 값

 

DCL(Data Control Language)

GRANT - 사용자에게 권한 부여

GRANT SELECT, INSERT, DELETE, UPDATE ON object TO user WITH GRANT OPTION; 

 

WITH GRANT OPTION - 권한을 부여할수있는 권한 부여, REVOKE 하면 모든 권한 회수

WITH ADMIN OPTION - 테이블의 모든 권한 부여, REVOKE 하면 그사용자만 권한 취소

 

REVOKE

REVOKE privileges ON object FROM user;

 

COMMIT 

-INSERT, DELETE, UPDATE 로 변경한 데이터를 데이터베이스에 반영

-변경전 이전 데이터 잃어버림

-데이터베이스 변경으로 인한 LOCK이 해제됨

-COMMIT 하면 하나의 트랜잭션 과정을 종료함

 

ROLLBACK

-이전에 COMMIT한 곳까지만 복구

-데이터 변경사용을 모두 취소하고 트랜잭션을 종료함

 

 

SAVEPOINT

-사용하면 지정된 위치까지만 트랜잭션 ROLLBACK 가능

-ROLLBACK 하면 SAVEPOINT와 상관없이 변경된 모든 데이터 취소

 

ROLLBACK TO [SAVEPOINT];