4 분 소요

SQL 심화

View 에 대한 개념 및 활용

External level - External Schema (외부 스키마)

링크

  • 사용자가 보는 view level
  • 사용자에게 view의 형태로 필요한 정보만 보여주는 단계
  • 각 사용자들이 개별 요구 사항에 따라 다른 view를 정의해서 데이터를 볼 수 있다.
  • 데이터베이스 하나에 여러 개의 외부 스키마 가능
  • 개념스키마와 논리적 데이터 독립성

View의 목적

  • 어떤 경우에 모든 사용자가 전체 논리적 모델 (데이터베이스에 저장된 모든 실제 테이블)을 보는 건 문제가 될 수 있음
  • 필요한 데이터만 특정 사용자들에게 유출할 필요가 있을 때 사용
    • employ 테이블에서 아이디, 이름, 부서 조회 가능하게 하지만 salary 정보를 숨기고 싶을 때
  • 질의문 작성을 쉽게 만들어 준다.
    • Group by 나 aggregation function 등을 미리 정의
  • 데이터 종속성 제거
    • 응용프로그램은 뷰를 통해 접근함으로써 테이블 스키마 변화에 신경 쓸 필요가 없다.

View 정의

  • CREATE VIEW <view_name> [(<column_name_list>) as <query expression>
  • 다른 view를 이용한 query로 view 생성 가능
  • Column list 가 생략된 경우 query 문의 겨로가 relation의 컬럼 리스트로 view 컬럼이 지정됨
  • 스키마 변화에 신경 쓸 필요가 없다

Example

  • CREATE VIEW public_employee_information AS SELECT ID, name, dept_name FROM employee;
  • CREATE VIEW department_total_salary (dept_name, total_salary) AS SELECT dept_name, sum(salary) FROM employee GROUP BY dept_name;

다른 view를 사용하는 예제

질의 처리에서 view가 query expression 으로 대체되는 로직

View 에 대한 삽입, 수정, 삭제 연산 가능

  • 기본 테이블에 대한 연산으로 변경되어 실행
  • Example
    • CREATE VIEW public_employee_information AS SELECT ID, name, dept_name FROM employee;
    • INSERT INTO public_employee_information values('10101', 'Green','Sales'); -> (‘10101’, ‘Green’, ‘Sales’, null) 이 employee 테이블에 대한 insert 로 변환
  • Updatable View 가 되기 위한 조건
    • 베이스 테이블이 하나인 경우 (JOIN 인 경우 불가능)
    • Select clause에 컬럼 이름만 있는 경우 (Aggregation 함수나 DISTINCT 있는 경우 불가능)
    • Group by 나 having 이 없는 경우
  • Where 절이 있는 경우

Materialized View

  • View 에 대한 expression의 결과가 persistence 테이블 형태로 저장되는 뷰
  • View 에 대한 계산이 복잡한 경우 (여러 테이블들에 대한 조인, aggregation) 쿼리 결과를 테이블에 미리 계산해서 저장해 view에 대한 쿼리 성능을 높임

Materialized view maintenance

  • View에서 사용되는 베이스 테이블들이 업데이트가 있을 때 Materialized view 도 업데이트 해주는 방법
  • 많은 DBMS 들은 on-demand mode/ real-time mode (제한된 경우) 로 refresh 방법을 제공한다.

Drop View

  • syntax : DROP VIEW <view_name>
  • View만 삭제하고 베이스 테이블들은 영향을 받지 않는다.

트랜잭션 SQL

Transaction

  • 논리적인 작업의 단위 : LUW, Logical Units of Work
  • Transaction은 read, write, delete, update 등의 연산들로 구성되나 한 Transaction 단위로 일관성이 보장된다. -> All or Nothing

Atomicity 원자성

  • Transaction 을 구성하는 연사들이 모두 실행이 되거나 또는 하나도 실행되지 않거는다
  • 연산이 실패하면 이미 실행된 연산들로 바뀌었던 부분들이 다시 원 상태로 바뀐다 (Rollback)

Consistency 일관성

  • Transaction 이 실행된 후에도 일관성이 있는 데이터베이스 상태로 유지하는 것을 의미
  • 무결성 제약 조건으로 정의된 일관성 조건이 있다면 transaction의 구성하는 연산 중 조건을 어길 경우 트랜젝션은 실패된다.

Isolation 격리성

  • 트랜잭션 수행 시 다른 트랜잭션의 연산 작업이 끼어들지 못하도록 보장하고 다른 트랜잭션들이 트랜잭션안의 중간 연산을 볼 수 있다는 걸 의미한다.

Durablity 지속성

  • 성공적으로 수행된 트랜젝션은 영원히 반영되어야 함을 의미
  • 시스템이 장애가 발생했더라도 성공적으로 수행된 트랜잭션 결과는 데이터베이스에 반영되어 있음을 보장
  • 전형적으로 트랝잭션은 업데이트에 대한 로그로 적고 로그가 저장된 후에 트랜잭션이 커밋으로 간주
  • 장애 이후 로그 데이터를 가지고 데이터베이스 재구성 가능

트랜잭션 모드

  • Autocommit mode : default
    • statement가 시작할 때 트랜젝션이 내부적으로 시작하고 statement가 끝날 때 마다 commit이 자동으로 실행된다.
    • session level로 mode를 변경 가능
      • SET AUTOCOMMIT = OFF
      • JDBC : connection.setAutoCommit(False)
  • Explicit mode
    • START TRANSACTION; -> Transaction 을 시작
    • COMMIT; -> 트랜젝션의 커밋에서 변경 내용을 데이터베이스에 저장하는 statement
    • ROLLBACK; -> 트랜젝션의 연산들로 변경된 내용을 취소하는 statement
  • Transaction Isolation level
    • SET TRANSACTIOn statement 로 transaction isolation level로 바꿀 수 있다.
      • Isolation level
        • READ UNCOMMITTED
        • READ COMMITTED
        • REPEATABLE READ : MySQL Default
        • SERIALIZABLE
      • example
        • SET TRANSACTION ISOLATION LEVEL SERIALIZABLE:
    • 성능 향상을 위해 DBMS 는 isolation level을 조절하는 기능을 제공한다
    • 레벨이 낮을 수록 데이터 무결성이 유지되지만 비용이 높아지고 동시성이 떨어진다
    • Read Uncommitted
      • 트랜잭션이 처리중에 아직 커밋되지 않은 데이터를 다른 트랜젝션이 읽는 걸 허용
        • -Dirty Read
      • 데이터 일관성을 유지할 수 없다
    • Read Committed
      • 커밋이 이루어진 데이터만 접근해서 읽을 수 있다.
      • 한 트랜잭션안에서 같은 조회가 다른 결과를 리턴 할 수 있다.
        • Non - Repeatable Read
      • Repeatble Read
        • 트랜잭션 안에서의 같은 조회는 항상 동일한 결과가 리턴 하는 걸로 보장한다.
        • Phantom Read 발생
      • Serializable
        • 완벽한 읽기 일관성 모드 제공

무결성 제약 조건

Integrity Constraints (무결성 제약 조건) in CREATE TABLE

  • not null
    • 컬럼 값으로 null 을 허용하지 않을 때 지정
    • name char(10) NOT NULL -> 고객 이름 컬럼으로 사이즈 10 이내, 필수 입력사항
  • primary key
    • 테이블에서 튜플 (row)을 찾는 기본 키
    • unique + not null
    • 만약 같은 값이 이미 테이블에 존재하거나 null 을 입력하려고 하면 insert 실패
  • Unique
    • Primary key 와 같이 튜플에 유일성을 체크하는 대체키 (candidate key) 를 지정한다. 이건 null 허용
  • Check (p) p : predicate

참조 무결성 (Referential Integrity)

  • 관계 데이터베이스 관계 모델에서 2개의 관련 있던 테이블 간의 일관성 (데이터 무결성)
  • 참조 무결성을 정의하기 위해 foreign key (외래키)을 지정하는데 foreign key 에 포함 되는 컬럼은 참조하는 부모 테이블의 primary key 또는 candidate key 이어야 한다
  • Syntax
    • FOREIGN KEY (<column_names>) REFERENCES <parent_table_name> (<column_names>) [ON DELETE reference_option] [ON UPDATE reference_option]
    • 마지막 부분 2개 다 DELETE가 아니라 UPDATE 임

SQL DCL 접근

권한관리

  • Authentication & Authorization
    • Authentication : DBMS 에 보안 유지를 위해 계정을 가진 사용자가 접속할 수 있게 해주는 접근 제어 (Access Control)
    • CREATE USER <user_name> IDENTIFIED BY 'password'
    • Authorization : 접속한 사용자의 사용 범위와 권한을 정의 하는 것
  • 사용권한
    • 데이터베이스의 모든 객체는 해당 객체를 생성한 사용자만 사용 권한을 가진다
  • 권한부여
    • 여러 사용자가 공유해서 사용할 목적으로 다른 상죵자들에게 자신의 객체에 대한 권한을 부여 할 수 있다.
  • Privilege 의 종류
    • SELECT
    • INSERT
    • UPDATE
    • DELETE
    • REFERENCES

REVOKE

Role

댓글남기기

참고