[DB] 6. Basic SQL (Structural Query Language)

SUbbb·2021년 10월 17일
0

DataBase

목록 보기
7/15
post-thumbnail

Background

  • SQL (Structural Query Language)
    • tuple calculus로부터 유래
    • 옛 이름은 SEQUEL(Structed English QUEry Language)
    • IBM에서 실사용
    • DBMS마다 SQL 지원 문법이 조금씩 다르다.

SQL DATA DEFINITION AND DATA TYPES

Schema and Catalog Concepts in SQL (Data Definition Language: DDL)

SQL schema

  • 몇 시스템에서는 database라고 한다.
  • 같은 DB에 있어야 하는 table + relationship을 의미
  • schema name으로 식별되고, 누가 schema를 소유하고 있는지를 나타내는 (i) authorization identifier와 schema에 있는 각 element에 대한 (ii) descriptors를 포함

Schema elements

  • Tables(formal하게는 relations), Constraints, Views, Domains(column의 data type) and other construct를 포함

CREATE SCHEMA

  • schema를 생성하는 구문
  • 모든 schema elements' definitions을 포함가능
  • Example: CREATE SCHEMA COMPANY AUTHORIZATION 'Joshua'

하지만 모든 사용자가 schemas & schema elements를 생성할 수 있는 권한이 있는 것은 아니다.

  • DBA가 권한을 부여

Catalog

: 명명된 schemas의 collection, 모든 schema의 정보 저장

  • Integrity constraintsrelations가 동일한 catalog 내에 존재하는 경우에만 정의될 수 있다.
  • 동일한 catalog 내에서, schemas는 특정 elements(type/domain definitions)를 공유할 수 있다.
  • 다른 schema 참조 시에 catalog 사용

The CREATE TABLE Command in SQL

새로운 relation R을 정의

  • R에 이름 부여
  • R의 attributes, types(domains), and intial constraints(NOT NULL or UNIQUE)를 정의

Atfer attribute declaration

  • key, entity integrity, and referential integrity constraints는 attribute declaration이후에 정의될 수 있다.
    • 특정 referential의 경우, 참조할 다른 table의 선언 이후 선언해야 한다. 따라서 ALTER TABLE을 사용하여 이후에 제약조건 추가 (혹은 선언 순서를 바꿔주기)

Base tables(base relations)

  • CREATE TABLE
  • tables(relations)와 table의 rows(tuples)는 physically, actually하게 생성되고, DBMS에 의해 '파일'로서 저장된다.

Virtual relations(views)

  • CREATE VIEW
  • 어떠한 물리적 파일과도 일치하지 할 수도, 하지 않을 수도 있다.
    • view에 속한 관련 tuples는 물리적 구체화가 되지 않는 한 view가 참조될 때만 계산된다.
  • Why needed?
    • 디스크 낭비를 막기 위함 (하지만 계산 시간이 오래 걸리면 table화 하는 게 이득일 수 있음)
    • 권한을 분리하기 위함

Recall the COMPANY Relational Schema

One Possible Database State

SQL CREATE TABLE Data Definition Statements for Defining a COMPANY Schema

  • 몇 foreign keys는 에러를 발생시킬 수 있다.

    • Circular references (순환 참조): e.g. EMPLOYEE table의 Super_ssn
    • 또는 참조할 table이 생성되지 않아 오류 발생
      E.g, EMPLOYEE table의 Dno는 DEPARTMENT table을 참조하는데, 아직 DEPARTMENT가 만들어지지 않은 경우
  • ALTER TABLE 구문으로 이를 해결

    • 위의 구문을 이용해 primary key는 후에 정의될 수 있다.

Attribute Data Types and Domains in SQL (1/7)

Basic data types - Numeric data types

  • Integer numbers of various sizes: INTEGER, INT and SMALLINT
    • Oracle에서는 NUMBER 사용(DECIMAL을 대체가능)
  • Floating-point (real) numbers: FLOAT, or REAL, and DOUBLE PRECISION
  • DECIMAL(i,j) (or DEC(i,j) or NUMBERIC(i,j))로 정형화된 숫자 표현 가능
    • Ex) DEC(4,2) = 12.34

Attribute Data Types and Domains in SQL (2/7)

Basic data types - Character-string data types

  • 고정 길이: CHAR(n), CHARACTER(n)
    • n: 최대 문자 길이
    • 왼쪽부터 문자를 채우고, 빈 공간은 공백
  • 가변 길이: VARCHAR(n), CHAR VARYING(n), CHARACTER VARYING(n)
    • n은 4K로 한정
    • CLOB(CHARACTER LARGE OBJECT): "document"와 같이 매우 긴 문자열 값을 위함
      • size: K, M, G
      • Ex) CLOB(20M): 20MB까지

Attribute Data Types and Domains in SQL (3/7)

Basic data types - Bit-string data types (이진 코드)

  • 고정 길이: BIT(n)
    • n: 최대 bits 수, 4K로 한정
    • 값 지정을 위해서, B`10101`와 같은 형식을 취해야 함
      \rarr 일반 문자열과 구분짓기 위해 B로 bit임을 알려주고, ``안에 있는 값을 사용
  • 가변 길이: BIT VARYING(n)
    • BLOB(BINARY LARGE OBJECT): 실행가능한 코드나 java 객체와 같은 매우 큰 binary 값을 위함
      • size: K, M, G
      • Ex) BLOB(20M): 20MB까지

Attribute Data Types and Domains in SQL (4/7)

Basic data types - Boolean data type

  • TRUE or FALSE or NULL 값

Basic data types - Date data type

  • 10자리
    • Components: YEAR, MONTH, and DAY -> YYYY-MM-DD
    • E.g., DATE '2014-09-27': Oracle에서는 to_date() 함수를 이용해서 전달받은 인자를 날짜형으로 변환
      • Ex) TO_DATE('2021-10-17', 'yyyy-mm-dd')

Basic data types - Time data type

  • 적어도 8자리
    • Components: HOUR, MINUTE, and SECOND -> HH:MM:SS
    • E.g., TIME '09:12:47'

Date & TIme data types에서, 유효한 days 와 times만 수용될 수 있다.

  • '>', '<'로 date/time 비교 연산 가능

Attribute Data Types and Domains in SQL (5/7)

Basic data types - TimeStamp data type

  • DATE & TIME fields를 포함
    • seconds의 소수점 이하 자릿수에 대해 최소 6자리 추가
    • WITH TIME ZONE option
    • E.g., TIMESTAMP '2014-09-27 09:12:47.648302'

Basic data types - INTERVAL data type

  • Date, time, or timestamp의 절댓값을 증가 또는 감소시키는데 사용되는 상대값을 지정
  • time의 기간을 저장하기 위해 사용:
    e.g., INTERVAL '40' MONTH = INTERVAL '3-4' YEAR TO MONTH
    • YEAR TO MONTH: 연과 월의 기간을 저장하는 type
    • DAY TO SECOND: 일과 시간, 분, 초를 저장하는 type

Date, Time, Timestamp, INTERVAL data types는 비교 연산을 위해 string formats으로 cast or convert될 수 있다.

Attribute Data Types and Domains in SQL (6/7)

Domain:

  • 공통의 Domain을 지정 (Data type을 만드는 것과 유사?)
  • column마다 data type을 지정하는 대신 선언될 수 있다.
  • Name은 attribute specification과 함께 사용될 수 있다.
  • 이점
    1) domain에 대한 data type을 변경하는 것이 쉬워진다.
    2) schema 가독성 향상
  • Ex)
    • CREATE DOMAIN SSN_TYPE AS CHAR(9);
      • CHAR, NUMBER와 같은 basic data types 대신 사용
    • EMPLOYEE의 Super_ssn, DEPARTMENT의 Mgr_ssn, WORKS_ON의 Essn, DEPENDENT의 Essn과 같이 동일한 data type을 가지는 attribute에 사용할 수 있다.

Attribute Data Types and Domains in SQL (7/7)

Type

  • 객체를 저장하는 용도
  • UDTs(User Defined Types)를 생성하는데 사용
  • 객체 기반 applications 지원
  • CREATE TYPE 구문으로 생성

SPECIFYING CONSTRAINTS IN SQL

Specifying Constraints in SQL

Basic constraints

Key constraints
: primary key의 값은 중복될 수 없다.

Entity integrity constraints
: primary key의 값은 NULL일 수 없다.

Referential integrity constraints
: foreign key는 참조하는 table에 존재하는 primary key의 값을 가져야 하고, 혹은 NULL 값을 가질 수도 있다.

  • NULL값이 가능한 경우? EMPLOYEE의 Super_ssn이 NULL인 경우=boss

SQL에서 이를 기술하는 방법?

  • PRIMARY KEY(Key constraints, +UNIQUE), NOT NULL(Entity integrity constraints), FOREIGN KEY(Referential integrity constraints) 키워드 사용

Specifying "Attribute" Constraints

Attribute의 기본값 설정

  • DEFAULT <value> 구문
  • NULL
    • 특정 attribute에 대해서는 적용되지 않는다. (primary key -> NOT NULL 사용)
  • CHECK 구문
    • Dnumber INT NOT NULL CHECK (Dnumber > 0 and Dnumber < 10)
    • Age INT NOT NULL CHECK (Age >= 15 and Age < 65)
    • DBMS가 제약조건에 대한 충족여부를 확인 -> 충족해야만 data insert 가능
    • schema-based constraint로 인해 지원

Specifying "Key" Constraints

PRIMARY KEY 구문

  • relation의 primary key를 구성하는 하나 이상의 attributes를 지정
  • Dnumber INT PRIMARY KEY
  • PRIMARY KEY(State, Number)

UNIQUE 구문

  • relational model에서, candidate keys로 불리는 alternate(secondary) keys를 지정 (중복되면 안되는 attribute)
  • Dnumber VARCHAR(15) UNIQUE
  • UNIQUE(State, Number)

Specifying "Referential Integrity" Constraints

FOREIGN KEY 구문

  • Default operation: violation을 일으키는 update를 "reject"
  • referential triggered action 구문 추가 (참조 트리거 액션 절)
    • Options: SET NULL | CASCADE | SET DEFAULT
      • SET DEFAULT는 oracle에서 지원하지 않는다.
    • SET NULL or SET DEFAULT을 처리하는 DBMS의 과정
      ON DELETE & ON UPDATE에서 동일하다.
    • CASCADE는 참조하는 tuples를 모두 삭제한다.
      • 일관성을 유지하는 것이 중요한 relationship relations에 적절

Giving Names to Constraints

CONSTRAINT 키워드

  • constraint에 이름 부여: 후에 altering시 유용
    • Ex1) CONSTRAINT reg_no_u UNIQUE(State, Number)
    • Ex2) dno CONSTRAINT fk_deptno REFERENCE Department(Dnumber)

Specifying Constraints on Tuples Using CHECK

  • relation에 있는 개별 tuples에 대한 추가적인 constraint는 CHECK를 사용하여 지정 가능하다.

CREATE TABLE 문 끝에 CHECK

  • 각 tuple에 개별적으로 적용; row-based constraints
    • tuple이 삽입 또는 수정될 때마다 check

Example of Specifying Constraints

BASIC RETRIEVAL QUERIES IN SQL

Basic SQL Query Block

condition -> column 이름 operation value
ORDER BY -> DESC: 내림차순, ASC: 오름차순 (default)

Basic Retrieval Queries in SQL

SQL은 how가 아닌 what을 요구하는 non-procedural, declarative language (비절차적, 선언적 언어)

SELECT statement

: database로부터 정보 검색 시 사용하는 basic statement
(relational algebra(procedual language)의 "selection" operation과 동일하지 않음)

Practical model(or, SQL)과 Relational model(or, relational calculus/algebra) 사이의 중요한 구별

  • SQL은 query 결과로 table이 모든 attribute 값에서 동일한 두 개 이상의 tuple을 가지는 것을 허용한다. (결국 중복을 허용한다는 의미)
    • tuple들의 multiset (a.k.a resultset) (bag이라고도 부른다)
    • query 결과에서 unique하게 row를 식별하기 위해 row ID(artificial identifier)가 사용될 수 있다.

The SELECT-FROM-WHERE Structure of Basic SQL Queries

SELECT statement의 기본 형태

SELECT <attribute list>
FROM <table list>
[WHERE <condition>]
  • SELECT 절:

    • <attribute list><attribute \space list>: query를 통해 탐색하고자 하는 values를 가지고 있는 attribute names의 list
  • FROM 절:

    • <table list><table \space list>: query를 처리하는데 필요한 table(relation) names의 list
  • WHERE 절:

    • condition을 충족한 tuple들만 result에 추가
    • <condition>\lt condition\gt: query를 통해 탐색하려는 rows(tuples)를 식별하는 조건식
  • Logical comparison operators: =,<,,>,,<>=, \lt, \le, \gt, \ge, \lt\gt

  • Projection attributes

    • 탐색된 값들을 가지고 있는 attributes
  • Selection condition -> 'WHERE'

    • 어떠한 탐색된 tuple은 항상 TRUE여야 하는 Boolean 조건문
    • 여러 relations이 포함될 때는 "join" condition을 포함

Basic Retrieval Quries - Simple (No Join)

  • Q0. Retrieve the birth date and address of the EMPLOYEE(s) whose name is ‘John B. Smith’

    • Q0은 WHERE절의 조건을 만족하는 FROM절의 EMPLOYEE의 개별 tuples을 선택하고, SELECT절에 있는 Bdate, Address attributes를 project한다.
    • Result:

Basic Retrieval Queries - 2-way Join

2개의 table이 join에 참여

  • Q1. Retrieve the name and address of all EMPLOYEEs who work for the ‘Research’ DEPARTMENT.

    • selection condition: 어떤 table을 선택할 것인지 명시
    • join condition: 2개 이상의 table을 join
      위 예시에서는 Dnumber(DEPARTMENT의 PK), Dno(EMPLOYEE의 FK)
    • Select-Project-Join(SPJ) query
    • Result:

Basic Retrieval Queries - 3-way Join

  • Q2. For every project located in ‘Stafford’, list
    • the project number,
    • the controlling department number, and
    • the department manager’s last name, address, and birth date.

  • Dnum(PROJECT's FK), Dnumber(DEPARTMENT's PK) => 첫 번째 join
  • Mgr_ssn(DEPARTMENT's FK), Ssn(EMPLOYEE's PK) => 두 번째 join
    • Result:

Ambiguous Attribute Names

  • 2개 또는 그 이상의 attribute에 대해, 동일한 이름이 사용될 수도 있다.
    • 서로 다른 tables에 있는 경우에도 마찬가지
    • 어떠한 문제가 발생할 수 있는가? Join하여 query 작성 시 어떤 attribute인지 구별이 어려움
  • 이러한 모호성을 없애기 위해, table name과 함께 fully-qualified attribute name을 사용해야 한다.
  • 아래의 경우를 생각해보자.
    • In EMPLOYEE: Dno -> Dnumber | Lname -> Name
    • In DEPARTMENT: Dname -> Name
      => Join 시에 구분이 어려움
    • 모호성 제거를 위해 아래와 같이 기술

    • Table name을 붙임으로써 모호성을 제거되었지만, Query를 작성하는 성능의 저하를 초래 => "Aliasing" 필요

Aliasing, and Renaming

모호성 제거를 위해 별칭을 사용

  • attribute name의 모호성은 동일한 relation에 두 번 참조하는 query의 경우에도 발생 (EMPLOYEE.Ssn, EMPLOYEE.Super_ssn의 경우?)
    • 이를 해결하기 위해, "alternative table names"를 선언한다.
      EMPLOYEE table을 query 안에서 두 번 참조하는 경우, aliases 또는 tupe variable인 E & S를 선언
  • Query: For each employee, retrieve the employee’s first & last name and the first & last name of his/her supervisor.

    • Self-join
    • Result:
  • Attribute name은 아래의 위치에서도 rename될 수 있다.
    • FROM 절
    • SELECT 절
      • E_Fn, E_Ln, S_Fn, S_Ln은 result set에 뜨는 name

Unspecified WHERE clause in a Query

WHERE 절이 없는 경우

  • tuple selection에서 아무 조건 없이 적용함을 의미
  • FROM 절에서 참조된 table의 Cartesian product의 결과
    • Outcome: 모든 가능한 tuple combinations

Use of the Asterisk(*)

  • “Retrieve all the attribute values of the selected tuples.”
    • * 는 relation name이나 alias의 접두사로 사용할 수 있다.
      • e.g., EMPLOYEE.* (E.*)

Table as Sets(a Multiset) in SQL (1/4)

SQL은 자동적으로 query results에 있는 중복 tuples를 제거하지 않는다.

  • 중복제거의 비용이 크기 때문
  • Formal한 SQL에서는 중복을 제거

Table as Sets(a Multiset) in SQL (2/4)

  • 왜 중복된 tuple을 제거하지 않는가 ?
    1) 중복 제거는 비용이 크다. tuple들을 먼저 정렬하고, 그 이후에 중복을 제거
    \rarr tuple의 양이 많은 경우, 정렬 + 제거의 비용이 증가
    2) query의 결과에 있는 중복된 tuples을 사용자가 원하는 경우가 존재할 수 있다.
    3) aggregate function(집계 함수: sum, avg, max, distinct, ...)이 tuples에 적용될 때, 대부분의 경우에 중복 제거를 원치 않는다.
  • 중복 제거를 원하는 경우, DISTINCT 키워드를 사용

Table as Sets(a Multiset) in SQL (3/4)

Set operations

: UNION(set union), EXCEPT(set difference), INTERSECT(set intersection)

  • multiset operations(중복 다중 집합): UNION ALL, EXCEPT ALL(or MINUS), and INTERSECT ALL
  • 이러한 set operations가 유효하기 위해서, resultset tables가 서로 Type-compatible해야 한다.
    1) 두 relations가 동일한 attributes(names)를 가지고 있어야 하고, (AS 키워드로 rename이 필요할 수도 있다.)
    2) 두 relations에서 attributes가 같은 순서로 나타나야 한다.

Table as Sets(a Multiset) in SQL (4/4): A Query Example

  • “Make a list of all project numbers for projects that involve an employee whose last name is ‘Smith’, either as a worker or as a manager of the department that controls the project."

Several More Features of SQL: Substring Pattern Matching

부분 문자열 패턴 매칭

  • LIKE 비교 연산자: 문자열 패턴 매칭을 위해 사용
    • 부분 문자열은 2개의 문자로 명시될 수 있다:
      1) '%'는 0개 이상의 문자를 대체
      E.g., “Retrieve all employees whose address is in Houston, Texas."

      2) '_'는 1개의 문자를 대체
      E.g., “Retrieve all male employees that were born during 1990s.”
  • attribute value가 '%'나 '_'를 포함하고 있는 경우 '\'문자 사용

Several More Features of SQL: Arithmetic Operations

산술 연산

  • 기본 arithmetic operators: +, -, *, /
    • “Show the resulting salaries if every employee working on the ‘ProductX’ project is given a 10 percent raise.”
  • BETWEEN 비교 연산자:
    • 이상, 이하를 의미

Ordering of Query Results

질의 결과 정렬

  • ORDER BY 구문 사용
    • DESC: 내림차순, ASC: 오름차순
    • 개별적 정렬방식 지정 가능

Summary: Basic SQL Query Block

INSERT, DELETE, AND UPDATE STATEMENTS IN SQL

Commands for Database Modification

INSERT

relation에 tuple을 insert

UPDATE

조건을 만족하는 relation에 있는 tuples를 update (DELETE+INSERT로 구현 가능)

DELETE

조건을 만족하는 relation에 있는 tuples를 update하는 것과 유사

The INSERT Command

  • CREATE TABLE command로 생성한 attributes의 순서와 동일하게 insert해야 함
  • data types에 대한 제약조건은 자동적으로 관찰되어, 위반하는 경우 insertion을 reject
  • NULL값을 포함한 모든 값들이 attributes 순서에 맞게 제공되어야 한다.
  • 혹은 아래와 같이, 원하는 attributes에 대한 값만을 insert할 수도 있다.
    • 명시되지 않은 attributes에 대해서는 DEFAULT or NULL로 set
  • SELECT 의 결과를 아래와 같이 INSERT 할 수도 있다.
  • table이 없는 경우 아래와 같이 생성하고 SELECT 의 결과를 적재할 수도 있다.
  • 많은 tuples를 "bulk-loading"(대용량 적재)하는 경우에도 사용될 수 있다.
  • 새로운 table(say, D5EMPS)는 이미 존재하는 table(say, EMPLOYEE)과 동일한 attributes를 가진다. 이때, LIKE & WITH DATA 를 이용해서 생성할 수 있다.
    • Table 생성과 적재를 동시에 수행
    • EMPLOYEE table을 그대로 이용하고, query의 결과를 data로 사용하여 INSERT

The DELETE Command

relation에서 tuples 삭제

  • 삭제할 tuples를 선택하기 위해 WHERE 절 포함
    • WHERE 절을 만족하는 tuple의 수에 따라 삭제되는 tuple 수가 결정됨
  • tuples는 한 번에 한 table에서만 삭제가 가능
  • Referential integrity만 위반될 가능성이 존재
    • 만약 CASCADE가 referential integrity constraint에 의해 명시되었다면, 참조하는 모든 tuple들 또한 삭제된다.
  • WHERE 절이 없는 경우
    • table의 모든 tuples가 삭제되고 텅 빈 table만 남게 된다.
    • table을 제거하는 방법은 ?
  • Ex)

The UPDATE Command

  • 하나 이상의 선택된 tuples의 attribute value를 수정하기 위해 사용
  • 각 command는 동일한 relation에 있는 tuples를 수정
  • WHERE 절로 수정할 tuples를 선택
  • 추가적으로 붙는 SET-절은 UPDATE할 값을 지정하는 역할
    • referential triggered action이 DDL의 referential integrity constraint에서 지정된 경우, PK 값을 update하는 것은 다른 tables에 있는 tuples의 FK 값에도 전파될 수 있다.
    • data의 일관성을 유지하기 위함
  • Ex) Change the location and controlling department number of project number 10 to ‘Bellaire’ and 5, respectively.
    • P.Dnum = 5 -> 참조하는 다른 tuple의 attribute 값도 변경해야 함.
profile
배우고 정리하고 공유하기

0개의 댓글