[DB] #03. SQL I

💡 SQL 언어 중 중첩 SQL 기능을 제외한 기본적인 SQL 언어 살펴보기

3.1 Database Languages

Database Languages

• DBMS : DB 언어 제공 ← for 사용자와의 의사소통 SQL → 사용자의 query → DBMS : relational algebra로 변환
  
• 기능적 관점에서의 구분
  • DDL : Data Definition Language
  • DML : Data Manipulation Language
  • DCL : Data Control Language
• 표현방식 관점에서의 구분
  • Procedural 절차적인 언어
  • Declarative 비절차적인(명시적인) 언어

Data Definition Language (DDL)

• DB Schema 조작 : Schema 생성, 삭제, 변경
• Data Dictionary에 반영
• 도메인, 데이터 제약 조건 등을 표현할 수 있는 기능 제공

Data Manipulaion Language (DML)

• DB Instance 조작 : Instance 생성, 조회, 삭제, 변경
• 질의어 (query language) 사용자 → DML : 질의 생성 → DBS 전달/처리
  

Data Control Language (DCL)

• schema, instance 제외 다른 객체 조작
• ex. 트랜잭션 시작/종료/분리도 지정, 세션 시작/종료, 회복/복구, 데이터 권한 부여/취소, 사용자 계좌 관리

Procedural vs. Declarative

1. 절차적 언어 how to do
   • 처리 방법 및 절차 명시
   • ex. relational algebra, C, C++, Java 등
2. 비절차적 언어 what to do
   • 방법 및 절차 언급 X, 원하는 데이터만 명시
   • ex. SQL, Prolog, Lisp 등
   • 절차적 언어보다 진보된 언어
   • 컴퓨터 시스템 관점 : 어렵고 복잡 사용자 관점 : 단순하고 배우기 쉬움
     

Relational Database Languages

1. Pure language

   • Relation algebra 관계 대수(Ch#2)
   • Tuple relational calculus 터플 관계 해석
   • Domain relational calculus 도메인 관계 해석

   → 질의어 표현력 동일, 상호 변환 가능

   → 이론적 개발, 상용 시스템에서 구현X

2. real system language

   • SQL ← 가장 많이 사용
   • QUEL
   • Query by Example
   • LDL (Logic Database Language)

SQL

• Structured Query Language
  • 관계형 데이터베이스 모델을 위한 데이터베이스 언어
  • DDL, DML, DCL 모두 포함
• 실질적이고 공식적인 데이터베이스 언어 표준
  1. 실질적인(de facto) 표준
     • 산업체에서 실질적으로 인식되는 표준
     • 시장에 영향을 미치는 주요 회사 연합에서 제정한 표준
  2. 공식적인(de jure) 표준
     • 표준 공식 기관에서 제정한 표준
• 역사
  • 1970s IBM San Jose Research Laboratory에서 실험적 개발한 System R의 일부 : Sequel(시퀄) 이름으로 구현 → SQL 로 명명
  • ISO : 표준 SQL 제정 대부분 SQL-92 원칙 + 특정 시스템 고유의 SQL 기능 제공
    

2023.09.25

• SQL 명칭
  • 대소문자 구분 X (인용부호 내에서만 구분)
  • 세미콜론(;) : 문장의 끝 표기

3.2 DDL SQL

DDL SQL CREATE/ALTER/DROP

• 관계 스키마
• 각 속성과 연관된 값의 도메인
• 무결성 제약
• 관계에 연관된 인덱스
• 관계 저장을 위한 디스크 상의 물리적 구조
• 관계에 연관된 보안 및 권한 부여/취소 (→ DCL로 분류하기도 함)

Domain Types

• char(n) : 고정 길이 character string
• varchar(n) : 가변 길이 character string, 최대 길이 n
• int, smallint : 크기 범위는 OS에 의존적
• numeric(p,d)
  • 소수점 이하 자릿수 지정하여 데이터 처리 시 유용
  • p : 유효 숫자 개수
  • d : 소수점 이후에 나오는 숫자 개수
  • ex. numeric(5,2) : xxx.xx
• real, double precision : 실수
• float(n) : 소수점 이후의 숫자 개수가 적어도 n개의 정확성을 갖는 Floating point number

→ 자체적으로 새로운 데이터 타입명을 정의하여 사용하기도 함

Create Table

• 새로운 테이블 정의하여 생성

  • Create table 테이블명 {(속성명, 도메인명) 데이터무결성제약}

    Create table professor (
    		pID          char(5),
    		name         varchar(20) not null,
    		deptName     varchar(20),
    		salary       numeric(8,2));

  • not null : 무결성 제약

• 저장 : Insert

  • 문자열은 작은따옴표

  • 관계대수는 큰따옴표

    Insert into professor values('10', 'Lee', 'CS', 7500);

Integrity Constraints

1. not null : null값 가지면 안 된다.
2. primary key : not null
3. foreign key : 참조키(외래키)
   • R 테이블에 반드시 존재해야함
   • 테이블 내에 없을 수도, 여러개 존재할 수도 있음
   • 참조하는 속성 명시적 언급 필요 X ← 참조되는 테이블의 주 키만 참조해서

Create table professor (
		...,
		primary key (pID),
		foreign key (deptName) references department(deptName));

• 무결성 제약 이름 명시 가능 → 후에 해당 제약 삭제/변경 용이

  Create table professor (
  		...,
  		constraint myForeignKey foreign key(deptName) references department;

• Schema에 관한 정보 : meta data, Data dictionary에 포함 실제 DB에 관한 정보 : Database에 포함
  

University Database Creation

Create table student (
		sID                   char(5) primary key,
		name                  varchar(20) not null,
		gender                char(1)
		deptName              varchar(20)
		foreign key (deptName) references department),
		check (gender in ('F', 'M')));

• primary key가 두 개 이상의 속성 조합일 경우 → primary key(a1, a2, …, an)
  

Drop/Alter Table

• Drop 삭제
  • DB에 있는 student 테이블 제거

    Drop table student;

  • Data Dictionary에 있는 student schema 제거 → Schema 변경은 조심스러워야한다.
    
• Alter 변경
  • 새로운 속성 추가

    Alter table r add A D;
    //새로운 속성명(A) 및 속성 타입(D) 명시 필요
    //null 값으로 채워짐

  • 기존 속성 제거 → 많은 상용DBS에서 지원 X

    Alter table r drop A;
    //relation r에서 속성 A 제거

  • 테이블 제약 사항 추가 및 제거 가능

    NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK

DROP vs. DELETE

• Drop table student
  • table 요소(관계, tuple), schema 둘 다 삭제
  • DDL 기능
• Delete from student
  • 모든 table의 요소(tuple)만 삭제 → schema는 존재
  • DML 기능

3.3 DML SQL

DML SQL

• select : 가장 복잡, 기능 다양

Insertion

• DB tuple을 입력하는 연산

  Insert into course values ('437', 'Database', 'CS', 4);
  Insert into course (cID, title, deptName, credit) values ('437', 'Database', 'CS', 4);

• null은 예약어

  Insert into course values ('777', 'no', 'CS', null);

• select-from-where 표현 사용 가능 → 해당 표현이 평가되기 전까지 tuple 입력 X

  Insert into professor
  select * from professor;
  //professor 테이블은 tuple 개수가 2배가 된다. 무한루프 X

Deletion

• DB tuple 전체를 삭제하는 연산 cf. 특정 속성값만 삭제(변경) : update

  Delete from professor;
  //professor내 모든 tuple 삭제, schema는 존재

• where : 조건 명시

  Delete from professor where deptName='EE';

• 중첩질의 형태

  Delete from professor
  where deptName in (select deptName
  									from department
  									where building = 'Vision Hall');

  Delete from professor
  where salary < (select avg(salary) from professor);

  • 집계함수는 where절에 직접 사용 X → 중첩 질의 사용
  • tuple 삭제에 따른 평균값 변경 X → 초기에 평균값 계산 후 삭제할 tuple 구한 다음 평균값 재계산없이 삭제
    

Updates

• DB tuple을 갱신하는 연산

  Update professor
  	set salary = salary * 1.03
  	where salary > 7000;
  Update professor
  	set salary = salary * 1.05
  	where salary <= 7000;
  //순서 중요

• case 문장 지원

  Update professor
  	set salary = case
  										when salary <= 7000 then salary*1.05
  										else salary*1.03
  								end;

• 모든 학생의 totalCredit값 update

  Update student S
  	set S.totalCredit=
  						(select sum(credit)        //scalar subquery
  						from takes natural join course
  						where S.sID = takes.sID and grade <> 'F' and grade is not null);

  • from 절 : course, takes 자연 조인
  • where 절 : sID 사용하여 자연조인 결과 테이블과 student 테이블 조인 grade 속성이 ‘F’가 아니고 또는 널값이 아닌 과목의 합
    

3.4 Select SQL Statements

select Statements

• 주어진 조건에 적합한 데이터 검색 → 반환
• 6개의 절(clause) : 순서 중요

  select <atrribute list>          //필수
  from <relation list>             //필수
  where <selection predicate>
  group by <grouping attributes>
  having <conditions>              //group by절 필수
  order by <ordering attributes>;

• 결과 : relation → 중첩 허용

“select” Clause

• 질의 결과에서 사용자가 보고 싶은 속성 리스트 (관계대수의 project)

  Select *         //모든 속성 의미
  from professor;

• DBS에서 제공하는 각종 함수, 수식표현 가능 ex. log함수, square root 함수 등

  Select pID, name, salary/12
  from pofessor;

• 입력/결과 테이블에 tuple 중복 허용
  • Multi-set(bag) 기반
  • 중복 허용 X : distinct 키워드 사용

    Select distinct deptName       //기본값은 all
    from professor;

“where” Clause

• 결과 tuple이 만족해야하는 조건 명시 (관계대수의 select predicate)

  Select name
  from professor
  where deptName = 'CS' and salary > 8000;   //논리연산자 가능

“from” Clause

• 질의에 관련있는 테이블 나열 (관계대수의 cartesian product)

  Select *
  from professor, teaches;

• 카티시안곱 연산은 그 자체로 유용성 X → where 절과 함께 사용시 유용
  

SQL Execution Model

• from 절에 명시된 각 테이블에서 한 개의 tuple 추출
  • 모든 tuple 조합에 대해 where 조건 적용
  • 테이블 하나에서 반드시 한 개 tuple만 추출
• where 절에 명시된 조건에 적용
  • where 조건 우선 적용 후 having 조건 적용
• true 판정 → group by 절로 전송
  • group by 절 처리 전에, true인 tuple 조합 구하기
  • 모든 tuple 조합에 대해 where 조건 적용 마친 후 group by 적용
• group by 절에 명시된 속성을 이용 → 중간 결과를 서브그룹으로 분리
• 서브그룹에 대해 order by 적용 → 그 결과를 결과 테이블로
  

Joins

• where절에 명시

  Select name, cID
  from professor, teaches
  where professor.pID = teaches.pID;    //equi-join

Natural Joins

• 두 테이블에서 동일한 속성명 간에 조인 연산 적용 → 중복 제거

  Select *
  from professor natural join teaches; //결과 테이블 속성 8개

• where 절에서 하는 join 연산의 경우엔 중복 제거 X
• 동일한 속성명으로 인해 원치않는 조인 연산 발생하지 않도록 주의 ! → using : 명시된 속성만을 조인 속성으로 사용

  Select name, title
  from (professor natural join teaches) join course using(cID);

  Select name, title
  from professor natural join teaches, course
  where teaches.cID = course.cID;

  Select name, title
  from teaches, course, professor
  where teaches.cID=course.cID and teaches.pID=professor.pID;

Rename Operations

• 테이블, 속성 재명명

  professor as T
  professor T      //as 생략 가능

  Select distinct T.name
  from professor as T, professor as S      //테이블 재명명
  where T.salary > S.salary and S.deptName = 'CS';

• as 생략 시 comma 위치 주의

  Select sID, name myName, deptName     //속성 재명명
  from student;

String Operations

• String 비교 연산 제공 : like

  Select name
  from professor
  where name like '%j_';
  //_ : 길이가 1인 임의 스트링(한 문자)
  //% : 길이에 무관한 임의 스트링

• 탈출 기호

  where title like '100\%' escape '\';

• 합성 concatenation

  ||

+) 대소문자 변환, 스트링 길이, 일부 스트링 추출 등의 기능 제공

Tuple Ordering

• order by : 결과 테이블의 tuple 정렬 → 다른 결과 테이블 생성 X, 기존의 결과 테이블 순서 변경

  Select distinct name
  from professor
  order by name;

• 한 개 이상의 속성 기준으로 정렬 가능 → order by A, B : A 기준 정렬 후 B 기준 정렬
  
• 기본 값은 오름차순 → 내림차순 : desc 키워드 추가

  order by deptName desc, name;

“where” Clause Predicates

• where 절에 사용할 수 있는 연산자 : between 값 구간 (경계값 포함)

  Select name
  from professor
  where salary between 5000 and 6000;

• tuple간 비교 가능 : 괄호 사용

  Select name, cID
  from professor, teaches
  where (professor.pID, deptName) = (teaches.pID, 'CS');
  //where p.pID=t.pID and deptName='CS';

2023.09.28

Duplicates

• relational algebra 연산에 대한 multi-set 버전 적용

  

  multi-set 버전으로 처리

  • select
  • project
  • cartesian product

• 중복에 상관없이 모든 tuple에 대해 연산

  → 결과 tuple이 중복에 상관없이 결과 테이블에 포함

  

Set Operations

• union, intersect, except (:= set difference) → 중복 제거

  (select ... from ... where  ...)
  union
  (select ... from ... where ...)

• union all, intersect all, except all → 결과 테이블에 중복 허용
  

3.5 Null Values

1. not-exist : 존재하지않는다
2. unknown : 존재했지만 지금은 모르겠다. ←

Null Values

• DBS를 복잡하게 만든다.
• arithmetic expression : null 연산에 대한 약속 필요 ex. 21 + null = null
  
• is null : null 이면 참이다.

  where salary is null;

Three-valued Logic

• 비교식에서의 null : unknown 리턴 ex. 21 < null, null <> null, null = null
  
• OR
  • unknown or true = true
  • unknown or false = unknown
  • unknown or unknown = unknown
• AND
  • true and unknown = unknown
  • false and unknown = false
  • unknown and unknown = unknown
• NOT
  • not unknown = unknown
• “where” 절에서의 처리 : false 로 !
• Three-valued logic operators
  • true : 1
  • false : 0
  • unknown : 0.5