02. SQL

서울대학교 데이터사이언스대학원 정형수 교수님의 "데이터사이언스 응용을 위한 빅데이터 및 지식 관리 시스템" 강의를 필기한 내용입니다.

• 목차

속기록

• 날것의 필기록이라 좀 설명이 부실합니다.

SQL §

• System: 누군과와 소통하는 interconnected software components
• DBMS: db 와 user 간의 연결다리
• 이때의 interface 로서 SQL 을 정의
• SQL 은 high-level lang 이고, optimization 도 잘 돼있다고 한다.
• 보통 대부분의 DBMS 는 SQL 표준과는 상이하다.
  • 되는것도 있고 안되는 것도 있다.
• 알다시피 end user 는 인지하지 못하지만 parallelism optimizing 이 되어 있다.
  • 그래서 누군가는 가장 성공적인 parallel programming language 이라고 하기도 한다.
• Data Definition Language (DDL) DB 의 schema 정의 (schema 를 다루는 언어)
  • 얘는 system table (= system catalog) 에 저장된다
    • 이놈은 memory 상에 올려져 있으며
    • query parsing 이 끝나면 여기에 저장되어 있는 schema 에 따라서 constraint 등이 맞나 체크한다.
  • Excel 로 비유하면 header row 가 schema 다.
    • Named relation 이라고도 한다.
• Data Manipulation Language (DML): 그 schema 에 따라 데이터를 넣고 변경하고 하는 것들
  • 당연히 schema 가 DDL 에 의해 먼저 정해져 있어야 한다.
• Table
  • Multiset: 순서없는, 중복가능 (list 와 set 의 합성 - bag)
• Attribute = column = field
  • Number of attribute = Arity
• Row = tuple = record
  • Number of tuple = Cardinality
  • Cardinality estimation: predicate 에 대한 cardinality 를 기존의 statistics 를 이용해 추정하는 것
    • 아마 어떤 query plan 에 대한 효과를 대략적으로 측정해서 제일 좋은 query plan 을 짜기 위함인듯
• TABLE_NAME(ATTR_NAME: TYPE ...) 로 schema 정의
  • 어떤 attribute 가 어떤 type 을 가지는지 정의하고 이걸 system catalog 에 넣어서 insert 등의 작업에 참고하여 판단한다.
• Key 는 value 가 unique 하는 특징이다.
  • schema 에서는 밑줄로 표현
  • Uniqueness 외에는 constraint 가 없다
  • Uniqueness 를 보장하는 minimum attribute subset 를 key 로 지정해야 하지만
    • 이것을 DBMS 가 알 수는 없으므로 minimum constraint 를 지키지 않아도 DBMS 는 뭐라 안한다.
• NULL: 모르는 값
• 임의적인 constraint 를 거는 것도 가능하다.
  • 를 위한 DDL 이 또 마련되어 있겠지
• 근데 constraint 가 많아지면 성능이 안좋다: 검사할게 많아지므로
  • 따라서 꼭 필요한 것만 넣어야 한다.
• FK, reference constraint (Referential Integrity Constraint)
  • Relation 에서 대상이 실제로 있냐를 검사하도록 강제하는 constraint
  • 물론 안걸어도 되긴 하지만, 당연히 거는게 좋다.
    • 안걸면 없는 놈을 참조하는 등의 문제가 생기니깐은
  • 그리고 당연히 얘네들이 많이 걸리면 느려진다.
    • Reference 를 찾아가서 실제로 있는지 lookup 을 해야 하기 때문
    • 근데 어쩔 수 있냐 꼭 필요하면 써야지.
  • 참고로 FK 와 PK 는 반드시 이름이 같을 필요는 없다.
• ALTER TABLE 로 constraint 를 나중에 넣을 수도 있다.

Single Table Query §

• 가장 기본적으로는
  • SELECT attr FROM relation WHERE predicate: SFW query 라고 한다.
  • DBMS 에서 FE 는 이 SFW 를 처리하는 것이 핵심 관심사다.
    • 나머지 UPDATE, INSERT, DELETE 는 FE 보다는 데이터를 빨리 변경하는 것이 핵심이기 떄문에 BE 가 관심을 가진다.
  • SFW 가 많은 놈이 analytics workload 이고, UPDATE, INSERT,DELETE 가 많으면 transactional workload
• FROM 뒤에 있는 relation 은 여러개를 적으면 full product (cartesian product, concate) 된다 (JOIN)
  • 이렇게 해서 나온 길다란 tuple 에 WHERE 를 적용해 tuple 을 골라내고
  • 골라진 tuple 에서 attr 를 지정하는 것이 SELECT 절
  • 물론 optimizer 에 의해 변경되어 이렇게 실행되지는 않는다.

Nested Query §

• SELECT 절로 attribute 를 지정하기 때문에 이것 또한 하나의 relation 의 schema 라고 할 수 있고
  • 즉, FROM 은 input schema
  • SELECT 는 output schema 인 셈
• 따라서 FROM에 넣을 수 있다.
• 이런 nesting 이 SQL 이 가진 큰 장점이다.
  • Nesting 이 가능한 것은 SFW 의 input 와 output 이 모두 relation 이기 때문이다.
  • Input, output schema 가 잘 맞는지 확인하는 기능도 DBMS 에 있다.
• SELECT * 로 하면 input schema 와 output schema 는 같다.
• 이렇지 않을 경우 sub-attribute 이 output schema 가 되고 그것이 Projection 이다.
• JOIN을 사용하면 input schema 가 product of relation 이라고 할 수 있다.
• Output schema 를 임시 table 로 저장하는 기능이 VIEW 이다.

etc… §

• SQL command 는 알다시피 case insensitive 이다
• LIKE : % 는 any substring, _ 는 any character
• ORDER BY : 정렬
• GROUP BY : 같은애들끼리 묶어서

Multi-Table Query §

FK Constraint §

• 뭔지는 위에서 설명했으니까
• Violation 시에:
  • INSERT: 거부 (reject) 한다.
  • DELETE: 거부 (reject, RESTRICT) 혹은 연관된 놈들을 다지움 (CASCADE) 혹은 NULL 로 만듦 (SET NULL) 세가지 정책이 있다.
    • 위 세가지 정책은 ON DELETE 로 지정할 수 있다.
    • 물론 세가지만 있는건 아니다 (SET DEFAULT, NO ACTION).
• DBMS 가 자동으로 걸어줄 수는 없다: 이건 DBA 가 할일이다.

JOIN §

• Table A 의 column B 의 값 과 table C 의 column D 의 값이 같은 두 table 의 두 tuple 을 concat 하는 것
• 당연히 비싼 연산이고 이것과 denormalization 간의 tradeoff 를 고려해서 schema 를 짜야 한다.
• JOIN ON 으로 하거나 ON 대신 WHERE 로도 된다.
  • INNER JOIN: predicate 에 맞는 애들만 결과에 포함된다.
  • OUTER JOIN: 한쪽이 NULL 인 상황도 결과에 포함된다.

etc… §

• Ambiguity: 다른 table 의 column 이름이 같은 경우 variable (alias) 을 지정할 수 있다.
  • SQL 은 declarative, relational algebra 의 순서로 바꾸는 것을 query planner 가 한다.