[Lecture] DataBase - Day 6

🖥 DataBase - Day 6

• 2.5 확장된 관계 대수 연산자
  • 기본 제공 연산자를 확장한 연산자들이 제안됨
    • 자연 조인 연산을 확장한 세미 조인과 외부 조인
  • 세미 조인(semi-join)
    • 릴레이션 S의 조인 속성으로만 구성한(프로젝트한) 릴레이션을 릴레이션 R에 자연 조인하는 것
    • 표현법 : $R ⋉ S$
    • 검색에 불필요한 속성을 미리 제거하여 조인 연산의 비용을 줄일 수 있음
    • 교환적 특징이 없음
  • 외부 조인(outer-join)
    • 두 릴레이션에 자연 조인 연산을 수행할 때 조인 속성 값이 같은 튜플이 상대 릴레이션에 존재하지 않아 조인 연산에서 제외된 모든 튜플을 결과 릴레이션에 포함
      • 결과 릴레이션에 속성 값이 없는 경우는 널 값으로 처리
    • 왼쪽 외부 조인, 오른쪽 외부 조인, 완전 외부 조인
      • 모든 튜플을 결과 릴레이션으로 가져오고자 하는 릴레이션이 무엇이냐에 따라 분류
      • 조인 연산에서 제외된 튜플을 포함
    • 왼쪽 외부 조인
      • 왼쪽에 있는 첫 번째 릴레이션에 존재하는 모든 튜플을 결과 릴레이션에 포함

      • 

    • 오른쪽 외부 조인
      • 오른쪽에 있는 두 번째 릴레이션에 존재하는 모든 튜플을 결과 릴레이션에 포함

      • 

    • 완전 외부 조인
      • 연산에 참여하는 두 릴레이션에 있는 모든 튜플을 결과 릴레이션에 포함

      • 

[03] 관계 해석

• 관계 해석(relational calculus)
  • 처리를 원하는 데이터가 무엇인지만 기술하는 비절차 언어
  • 수학의 프레디킷 해석(predicate calculus) 에 기반을 둠
  • 데이터를 처리하는 기능과 처리를 요구하는 표현력에서 관계 대수와 관계 해석은 능력이 모두 동일
  • 튜플 관계 해석(tuple relational calculus) 과 도메인 관계 해석(domain relational calculus) 로 분류

👉🏻 [Chapter 07] 데이터베이스 언어 SQL

[01] SQL의 소개

• SQL(Structured Query Language)
  • 관계 데이터베이스를 위한 표준 질의어
  • 비절차적 데이터 언어
  • SEQUEL(Structured English QUEry Language) 에서 유래됨
    • SEQUEL : 연구용 관계 데이터베이스 관리 시스템인 SYSTEM R 을 위한 언어
  • 미국 표준 연구소인 ANSI 와 국제 표준화 기구인 ISO 에서 SQL 을 관계 데이터베이스의 표준 질의어로 채택하고 표준화 작업 진행
  • 대화식 SQL : 데이터베이스 관리 시스템에 직접 접근하여 대화식으로 질의를 작성하고 실행
  • 삽입 SQL : 프로그래밍 언어로 작성한 응용 프로그램에 삽입하여 실행
• SQL의 분류
  • 데이터 정의어(DDL; Data Definition Language) : 테이블을 생성하고 변경﹒삭제하는 기능을 제공함
  • 데이터 조작어(DML; Data Manipulation Language) : 테이블에 새 데이터를 삽입하거나, 테이블에 저장된 데이터를 수정﹒삭제﹒검색하는 기능을 제공함
  • 데이터 제어어(DCL; Data Control Language) : 보안을 위해 데이터에 대한 접근 및 사용 권한을 사용자별로 부여하거나 취소하는 기능을 하는 언어
    • 데이터베이스 관리자가 주로 사용

[02] SQL을 이용한 데이터 정의

• 2.1 SQL의 데이터 정의 기능
  • 테이블 생성, 생성된 테이블 구조의 변경, 테이블 삭제
• 2.2 테이블의 생성 : CREATE TABLE

  CREATE TABLE 테이블_이름 (
      1. 속성_이름 데이터_타입 [NOT NULL] [DEFAULT 기본_값]
      2. [PRIMARY KEY (속성_리스트)]
      3. [UNIQUE (속성_리스트)]
      4 .[FOREIGN KEY (속성_리스트) REFERENCES 테이블_이름(속성_리스트)] [ON DELETE 옵션] [ON UPDATE 옵션]
      4. [CONSTRAINT 이름] [CHECK(조건)]
  );
  /* 
  1. 테이블을 구성하는 각 속성의 이름과 데이터 타입, 기본적인 제약 사항 정의
  2. 기본키 정의
  3. 대체키 정의
  4. 외래키 정의
  5. 데이터 무결성을 위한 제약조건 정의
  []로 표시한 항목은 옵션이기에 생략 가능
  */

  • 모든 SQL 문은 세미콜론(;) 으로 문장 끝을 표시함
  • SQL 문은 대소문자를 구분하지 않음
  • 속성의 정의
    • 테이블을 구성하는 각 속성의 데이터 타입을 선택한 후에는 속성의 널 값 허용 여부와 기본 값 필요 여부를 결정
    • CREATE TABLE 문으로 생성되는 테이블을 구성하는 속성은 기본적으로 널 값이 허용됨
      • NOT NULL : 속성이 널 값을 허용하지 않음을 의미하는 키워드
        • (EX) 고객아이디 VARCHAR(20) NOT NULL
      • DEFAULT : 속성의 기본 값을 지정하는 키워드
        • (EX) 적립금 INT DEFAULT 0
        • (EX) 담당자 VARCHAR(10) DEFAULT '방경아'
          • 문자열이나 날짜 데이터는 작은따옴표로 묶어서 표현
          • 작은따옴표로 묶인 문자열은 대소문자를 구분함
    • 속성의 데이터 타입

      • 

  • 키의 정의
    • PRIMARY KEY : 기본키 지정
      • (EX) PRIMARY KEY(고객아이디)
      • (EX) PRIMARY KEY(주문고객, 주문제품)
    • UNIQUE : 대체키 지정
      • 대체키로 지정된 속성의 값은 유일성을 가지고 기본키로 지정된 속성과 달리 널 값을 가질 수 있음
      • (EX) UNIQUE(고객이름)
    • FOREIGN KEY : 외래키 지정
      • 외래키가 어떤 테이블의 무슨 속성을 참조하는지 REFERENCES 키워드 다음에 명확히 제시
      • 참조 무결성 제약조건 유지를 위해 참조되는 테이블에서 튜플을 삭제할 때 처리 방법을 지정하는 옵션

        ON DELETE NO ACTION : 튜플을 삭제하지 못하게 함
        ON DELETE CASCADE : 관련 튜플을 함께 삭제
        ON DELETE SET NULL : 관련 튜플의 외래키 값을 NULL로 변경
        ON DELETE SET DEFAULT : 관련 튜플의 외래키 값을 미리 지정한 기본 값으로 변경

      • 참조 무결성 제약조건 유지를 위해 참조되는 테이블에서 튜플을 변경할 때 처리 방법을 지정하는 옵션

        ON UPDATE NO ACTION : 튜플을 변경하지 못하게 함
        ON UPDATE CASCADE : 관련 튜플에서 외래키 값을 함께 변경
        ON UPDATE SET NULL : 관련 튜플의 외래키 값을 NULL로 변경
        ON UPDATE SET DEFAULT : 관련 튜플의 외래키 값을 미리 지정한 기본 값으로 변경

        • (EX) FOREIGN KEY(소속부서) REFERENCES 부서(부서번호) ON DELETE CASCADE ON UPDATE CASCADE
        • (EX) FOREIFGN KEY(소속부서) REFERENCES 부서(부서번호)
  • 데이터 무결성 제약조건의 정의
    • CHECK : 특정 속성에 대한 제약조건 지정
      • 테이블에서 항상 정확하고 유효한 데이터를 유지하기 위해 데이터 무결성을 위한 제약조건을 표현하는 방법
      • CONSTRAINT 키워드와 함께 고유의 이름 부여 가능
• 2.3 테이블의 변경 : ALTER TABLE
  • 새로운 속성 추가, 기존 속성 삭제, 새로운 제약조건 추가, 기존 제약조건 삭제 등이 가능
  • 새로운 속성의 추가

    ALTER TABLE 테이블_이름
        ADD 속성_이름 데이터_타입 [NOT NULL] [DEFAULT 기본_값];

  • 기존 속성의 삭제

    ALTER TABLE 테이블_이름 DROP COLUMN 속성_이름;

    • 만약 삭제할 속성과 관련된 제약조건이 존재하거나 이 속성을 참조하는 다른 속성이 존재하는 경우에는 속성을 삭제할 수 없음
    • 관련된 제약조건이나 참조하는 다른 속성을 먼저 삭제해야 함
  • 새로운 제약조건의 추가

    ALTER TABLE 테이블_이름 ADD CONSTRAINT 제약조건_이름 제약조건_내용;

  • 기존 제약조건의 삭제

    ALTER TABLE 테이블_이름 DROP CONSTRAINT 제약조건_이름;

• 2.4 테이블의 삭제 : DROP TABLE

  DROP TABLE 테이블_이름;

  • 삭제할 테이블을 참조하는 테이블이 있다면 삭제가 수행되지 않음
    • 삭제하고자 하는 테이블을 참조하는 외래키 제약조건을 먼저 삭제해야 함

[03] SQL을 이용한 데이터 조작

• 3.1 SQL의 데이터 조작 기능

  • 데이터 검색, 새로운 데이터 삽입, 데이터 수정, 데이터 삭제

• 3.2 데이터의 검색 : SELECT

  • 기본 검색

    SELECT [ ALL | DISTINCT ] 속성_리스트
    FROM 테이블_리스트;

    • SELECT 키워드와 함께 검색하고 싶은 속성의 이름을 콤마(,) 로 구분하여 차례로 나열
    • FROM 키워드와 함께 검색하고 싶은 속성이 있는 테이블의 이름을 콤마(,) 로 차례로 나열
    • 테이블을 대상으로 하고 수행 결과도 테이블
    • ALL : 결과 테이블이 중복을 허용하도록 지정, 생략 가능
    • DISTINCT : 결과 테이블에서 튜플의 중복을 제거하고 한 번씩만 출력되도록 지정
    • 모든 속성 검색 시 * 사용 가능
    • AS : 결과 테이블에 출력되는 속성의 이름을 다른 이름으로 바꾸어 출력할 수 있음
      • 지정하는 이름에 공백이 포함되어 있으면 오라클에서는 큰따옴표, MS SQL 서버에서는 작은따옴표로 묶어줘야 함
      • 생략 가능

  • 산술식을 이용한 검색

    • SELECT 키워드와 함께 산술식 제시
      • 산술식 : 속성의 이름과 $+, -, *, /$ 등의 산술 연산자, 상수로 구성
    • 결과 테이블에서만 계산한 결과 값을 출력
      • 속성의 값이 실제로 변경되는 것이 아님

  • 조건 검색

    SELECT [ ALL | DISTINCT ] 속성_리스트
    FROM 테이블_리스트
    [ WHERE 조건 ];

    • 조건을 만족하는 데이터만 검색
    • WHERE 키워드와 함께 비교 연산자와 논리 연산자를 이용한 검색 조건을 제시
      • 조건에서 비교 연산자를 이용해 숫자뿐 아니라 문자나 날짜 값을 비교할 수 있음
      • 조건에서 숫자 값은 그대로 작성해도 되지만 문자나 날짜 값은 속성의 이름과 구별할 수 있도록 작은따옴표로 묶어야 함

  • LIKE를 이용한 검색

    • LIKE 키워드를 이용해 부분적으로 일치하는 데이터 검색
      • 검색 조건을 정확히 알면 $=$ 연산자로 표현
    • 문자열을 이용하는 조건에만 사용 가능
    • LIKE 키워드와 함께 사용할 수 있는 기호

      기호	설명
      %	0개 이상의 문자 (문자의 내용과 개수는 상관 없음)
      _	1개의 문자 (문자의 내용은 상관 없음)

    • (EX)

      사용 예	설명
      LIKE '데이터%'	데이터로 시작하는 문자열 (데이터로 시작하기만 하면 길이는 상관 없음)
      LIKE '%데이터	데이터로 끝나는 문자열 (데이터로 끝나기만 하면 길이는 상관 없음)
      LIKE '%데이터%'	데이터가 포함된 문자열
      LIKE '데이터___'	데이터로 시작하는 6자 길이의 문자열
      LIKE '__한%'	세 번째 글자가 '한'인 문자열

  • NULL을 이용한 검색

    • IS NULL : 검색 조건에서 특정 속성의 값이 널 값인지를 비교
    • IS NOT NULL : 검색 조건에서 특정 속성의 값이 널 값이 아닌지를 비교
    • 검색 조건에서 널 값은 다른 값과 크기를 비교하면 결과가 모두 거짓이 됨

  • 정렬 검색

    SELECT [ ALL | DISTINCT ] 속성_리스트
    FROM 테이블_리스트
    [ WHERE 조건 ]
    [ ORDER BY 속성_리스트 [ ASC | DESC ] ];

    • ORDER BY : 결과 테이블의 내용을 사용자가 원하는 순서로 출력
    • ORDER BY 키워드와 함께 정렬 기준이 되는 속성 지정
      • ASC(기본) : 오름차순 / DESC : 내림차순
      • 널 값은 오름차순에서는 맨 마지막에 출력되고 내림차순에서는 맨 먼저 출력됨
      • 여러 기준에 따라 정렬하려면 정렬 기준이 되는 속성을 차례로 제시

  • 집계 함수를 이용한 검색

    • 특정 속성 값을 통계적으로 계산한 결과를 검색하기 위해 집계 함수 이용

    • 집계 함수(aggregate function)

      함수	의미	사용 가능한 속성의 타입
      COUNT	속성 값의 개수	모든 데이터
      MAX	속성 값의 최댓값	모든 데이터
      MIN	속성 값의 최솟값	모든 데이터
      SUM	속성 값의 합계	숫자 데이터
      AVG	속성 값의 평균	숫자 데이터

      • 열 함수(column function) 라고도 함
      • 개수, 합계, 평균, 최댓값, 최솟값의 계산 기능을 제공
      • 주의 사항
        • 집계 함수는 널인 속성 값은 제외하고 계산
        • 집계 함수는 WHERE 절에서는 사용할 수 없고 SELECT 절이나 HAVING 절에서만 사용할 수 있음

  • 그룹별 검색

    SELECT [ ALL | DISTINCT ] 속성_리스트
    FROM 테이블_리스트
    [ WHERE 조건 ]
    [ GROUP BY 속성_리스트 [ HAVING 조건 ] ]
    [ ORDER BY 속성_리스트 [ ASC | DESC ] ];

    • GROUP BY : 특정 속성의 값이 같은 튜플을 모아 그룹을 만들고, 그룹별로 검색
      • 그룹을 나누는 기준이 되는 속성을 지정함
    • 그룹에 대한 조건을 추가하려면 GROUP BY 키워드를 HAVING 키워드와 함께 사용
    • 그룹을 나누는 기준이 되는 속성을 SELECT 절에도 작성하는 것이 좋음

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

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

    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 키워드를 이용
      • 모든 튜플을 검색 대상으로 하는 테이블이 무엇이냐에 따라 왼쪽 외부 조인, 오른쪽 외부 조인, 완전 외부 조인으로 분류