[Lecture] DataBase - Day 7

🖥 DataBase - Day 7

• 3.2 데이터의 검색 : SELECT

  • 여러 테이블에 대한 조인 검색

    • 조인 검색 : 여러 개의 테이블을 연결하여 데이터를 검색하는 것
    • 조인 속성 : 조인 검색을 위해 테이블을 연결해주는 속성
      • 테이블을 연결하려면, 조인 속성의 이름은 달라도 되지만 도메인은 반드시 같아야 함
      • 테이블의 관계를 나타내는 외래키를 조인 속성으로 이용
    • FROM 절에 검색에 필요한 모든 테이블을 나열
    • WHERE 절에는 조인 속성의 값이 같아야 함을 의미하는 조인 조건을 제시
    • 이름이 같은 속성이 서로 다른 테이블에 존재할 수도 있기에 속성의 이름 앞에 해당 속성이 소속된 테이블의 이름을 표시해주는 것이 좋음
    • FROM 절에서 테이블의 이름을 대신하는 단순한 별명을 제시하여 질의문을 작성하는 것도 좋음

    SELECT 속성_리스트
    FROM 테이블1 INNER JOIN 테이블2 ON 조인조건
    [ WHERE 검색조건 ]

    • 동등 조인 검색
      • 앞서 설명한 조인 검색은 조인 속성의 값이 같은 튜플에 대해서만 검색을 수행하므로 동등 조인에 해당함
      • 표준 SQL에서는 INNER JOIN 과 ON 키워드를 이용해 작성하는 방법도 제공함

    SELECT 속성_리스트
    FROM 테이블1 LEFT | RIGHT | FULL OUTER JOIN 테이블2 ON 조인 조건
    [ WHERE 검색조건 ];

    • 외부 조인 검색
      • 조인 조건을 만족하지 않는 튜플에 대해서도 검색을 수행
      • OUTER JOIN 과 ON 키워드를 이용
      • 모든 튜플을 검색 대상으로 하는 테이블이 무엇이냐에 따라 왼쪽 외부 조인, 오른쪽 외부 조인, 완전 외부 조인으로 분류

  • 부속 질의문을 이용한 검색

    • SELECT 문 안에 또 다른 SELECT 문을 포함하는 질의
      • 상위 질의문 | 주 질의문 (main query) : 다른 SELECT 문을 포함하는 SELECT 문
      • 부속 질의문 | 서브 질의문 (sub query) : 다른 SELECT 문 안에 들어 있는 SELECT 문
        • 괄호로 묶어 작성
        • ORDER BY 절 사용할 수 없음
        • 단일 행 부속 질의문 : 하나의 행을 결과로 반환
        • 다중 행 부속 질의문 : 하나 이상의 행을 결과로 반환
      • 부속 질의문 먼저 수행 후, 그 결과를 이용해 상위 질의문 수행
      • 부속 질의문과 상위 질의문을 연결하는 연산자 필요
        • 단일 행 부속 질의문 : 일반 비교 연산자 사용 가능
          • $=, <>, >, <, >=, <=$
        • 다중 행 부속 질의문 : 일반 비교 연산자 사용 불가능
          • IN : 부속 질의문의 결과 값 중 일치하는 것이 있으면 검색 조건이 참
          • NOT IN : 부속 질의문의 결과 값 중 일치하는 것이 없으면 검색 조건이 참
          • EXISTS : 부속 질의문의 결과 값이 하나라도 존재하면 검색 조건이 참
          • NOT EXISTS : 부속 질의문의 결과 값이 하나도 존재하지 않으면 검색 조건이 참
          • ALL : 부속 질의문의 결과 값 모두와 비교한 결과가 참이면 검색 조건을 만족(비교 연산자와 함께 사용)
          • ANY or SOME : 부속 질의문의 결과 값 중 하나라도 비교한 결과가 참이면 검색 조건을 만족(비교 연산자와 함께 사용)

• 3.3 데이터의 삽입 : INSERT

  • 데이터 직접 삽입

    INSERT
    INTO 테이블_이름[(속성_리스트)]
    VALUES (속성값_리스트);

    • 테이블에 튜플을 직접 삽입
    • INTO 절의 속성 이름과 VALUES 절의 속성 값은 순서대로 일대일 대응되고 개수도 같아야 함
    • 속성 이름의 리스트는 생략할 수 있는데, 생략한 경우에는 테이블을 정의할 때 지정한 속성의 순서대로 VALUES 절의 속성 값이 삽입됨
  • 부속 질의문을 이용한 데이터 삽입

    INSERT
    INTO 테이블_이름[(속성_리스트)]
    SELECT 문;

    • SELECT 문을 이용해 다른 테이블에서 검색한 데이터를 튜플로 삽입

• 3.4 데이터의 수정 : UPDATE

    UPDATE 테이블_이름
    SET 속성_이름1 = 값1, 속성_이름2 = 값2, ...
    [WHERE 조건];

  • 테이블에 저장된 튜플에서 특정 속성의 값을 수정
  • SET 키워드 다음에 값을 어떻게 수정할 것인지 지정
  • WHERE 절에 제시된 조건을 만족하는 튜플만 속성 값을 수정
    • 생략 시 테이블에 존재하는 모든 튜플을 대상으로 하여 수정
  • 부속 질의문 포함 가능

• 3.5 데이터의 삭제 : DELETE

    DELETE
    FROM 테이블_이름
    [WHERE 조건];

  • 테이블에 저장된 데이터 삭제
  • WHERE 절에 제시한 조건을 만족하는 튜플만 삭제
    • 생략 시 테이블에 존재하는 모든 튜플을 삭제하여 빈 테이블이 됨
      • DROP TABLE 문으로 테이블 자체를 제거하는 것과는 다름
  • 부속 질의문 포함 가능

[04] 뷰

• 4.1 뷰(view) 의 개념
  • 다른 테이블을 기반으로 만들어진 가상 테이블(virtual table)
    • 데이터를 실제로 저장하고 있지 않고 논리적으로 존재함
  • 일반 테이블과 동일한 방법으로 사용
  • 뷰를 통해 기본 테이블의 내용을 쉽게 검색할 수는 있지만 기본 테이블의 내용을 바꾸는 작업은 제한적으로 이루어짐
    • 기본 테이블(base table) : 뷰를 만드는 데 기반이 되는 물리적인 테이블
  • 다른 뷰를 기반으로 새로운 뷰를 만들 수 있음
• 4.2 뷰의 생성

    CREATE VIEW 뷰_이름[(속성_리스트)]
    AS SELECT 문
    [WITH CHECK OPTION];

  • CREATE VIEW 명령어와 함께 새로 생성할 뷰의 이름을 제시한 후, 뷰를 구성하는 속성의 이름을 괄호 안에 나열
    • 속성 리스트 생략 시 SELECT 절에 나열된 속성의 이름을 뷰에서도 그대로 사용함
  • AS 키워드와 함께 기본 테이블에 대한 SELECT 문을 제시
    • SELECT 문은 생성하고자 하는 뷰의 정의를 담고 있는데, ORDER BY 를 사용할 수 없다는 점만 제외하면 일반 SELECT 문과 동일
    • 오라클과 같은 일부 DBMS에서는 ORDER BY 사용을 허가하기도 함
  • WITH CHECK OPTION : 뷰에 삽입이나 수정 연산을 할 때 SELECT 문에서 WHERE 키워드와 함께 제시한 뷰의 정의 조건을 위반하면 수행되지 않도록 하는 제약조건 의미
• 4.3 뷰의 활용
  • SELECT
    • 일반 테이블처럼 원하는 데이터를 검색할 수 있음
      • 뷰에 대한 SELECT 문이 내부적으로는 기본 테이블에 대한 SELECT 문으로 변환되어 수행됨
    • 검색 연산은 모든 뷰에 수행 가능
  • INSERT, UPDATE, DELETE
    • 뷰에 대한 삽입, 수정, 삭제 연산 가능
      • 기본 테이블에 수행되기 때문에 결과적으로 기본 테이블이 변함
    • 뷰에 대한 연산이 제한적임
      • 변경 가능한 뷰 vs 변경 불가능한 뷰
    • 변경 불가능한 뷰의 특징
      • 기본 테이블의 기본키를 구성하는 속성이 포함되어 있지 않은 뷰
      • 기본 테이블에서 NOT NULL 로 지정된 속성이 포함되어 있지 않는 뷰
      • 기본 테이블에 있던 내용이 아니라 집계 함수로 새로 계산된 내용을 포함하고 있는 뷰
      • DISTINCT 키워드를 포함하여 정의한 뷰
      • GROUP BY 절을 포함하여 정의한 뷰
      • 여러 개의 테이블을 조인하여 정의한 뷰
  • 뷰의 장점
    • 질의문을 좀 더 쉽게 작성할 수 있음
      • GROUP BY, 집계 함수, 조인 등을 이용해 미리 뷰를 만들어놓으면, 복잡한 SQL 문을 작성하지 않아도 SELECT 절과 FROM 절만으로 원하는 데이터를 검색할 수 있음
    • 데이터의 보안 유지에 도움이 됨
      • 자신에게 제공된 뷰를 통해서만 데이터에 접근하도록 권한 설정 가능
    • 데이터를 좀 더 편리하게 관리할 수 있음
      • 제공된 뷰에 포함되지 않은 기본 테이블의 다른 부분은 사용자가 신경 쓸 필요 없음
• 4.4 뷰의 삭제

    DROP VIEW 뷰_이름;

  • 뷰를 삭제하더라도 기본 테이블은 영향을 받지 않음
  • 삭제할 뷰를 참조하는 제약조건이 존재
    • 뷰 삭제가 수행되지 않음
    • 관련된 제약조건 먼저 삭제

[05] 삽입 SQL

• 5.1 삽입 SQL의 개념과 특징

  • 삽입 SQL(ESQL; Embedded SQL)
    • 프로그래밍 언어로 작성돈 응용 프로그램 안에 삽입하여 사용하는 SQL 문
  • 주요 특징
    • 삽입 SQL 문은 프로그램 안에서 일반 명령문이 위치할 수 있는 곳이면 어디든 삽입할 수 있음
    • 프로그램 안의 일반 명령문과 구별하기 위해 삽입 SQL 문 앞에 EXEC SQL을 붙임
    • 프로그램에 선언된 일반 변수를 삽입 SQL 문에서 사용 가능
      • 단, SQL 문에서 일반 변수를 사용할 때는 앞에 : 을 붙여 테이블 이름이나 속성의 이름과 구분함
  • 커서(cursor)
    • 수행 결과로 반환된 여러 행을 한 번에 하나씩 가리키는 포인터
    • 여러 개의 행을 반환하는 SELECT 문을 프로그램에서 사용할 때 필요

• 5.2 커서가 필요 없는 삽입 SQL

  • 특별히 결과 테이블을 반환하지 않는 CREATE TABLE 문, INSERT 문, DELETE 문, UPDATE 문, 결과로 행 하나만 반환하는 SELETE 문은 커서가 필요 없음

  int main() {
    exec sql begin declare section;
      char p_no[4], p_name[21];
      int price;
    exec sql end declare section;
  
    printf("제품번호 입력: ");
    scanf("%s", p_no);
  
    exec sql select 제품명, 단가 into :p_name, :price
      from 제품
      where 제품번호 = :p_no;
  
    printf("\n 제품명 = %s", p_name);
    printf("\n 단가 = %d", price);
  
    return 0;
  }

  • 입력된 제품번호에 해당되는 제품명과 단가를 검색하는 프로그램
  • BEGIN DECLARE SECTION 문장과 END DECLARE SECTION 문장 사이에 삽입 SQL 문에서 사용할 변수 미리 선언
  • 삽입 SQL 문은 앞에 EXEC SQL 을 붙여서 일반 명령어와 구분함

• 5.3 커서가 필요한 삽입 SQL

  exec sql declare 커서_이름 cursor for select 문;

  • 커서를 선언하는 삽입 SQL 문
    • 커서를 사용하기 전에 커서의 이름과 커서가 필요한 SELECT 문을 선언

  exec sql open 커서_이름;

  • 커서에 연결된 SELECT 문을 실행하는 삽입 SQL 문

  exec sql fetch 커서_이름 into 변수_리스트;

  • 커서를 이동시키는 삽입 SQL 문
    • 커서를 이동하여 처리할 다음 행을 가리키도록 하고, 커서가 가리키는 행에서 속성 값을 가져와 변수에 저장시킴
    • 결과 테이블에는 여러 행이 존재하므로 FETCH 문은 여러 번 수행해야 함

  exec sql close 커서_이름;

  • 커서의 사용을 종료하는 삽입 SQL 문