stored function이란?

• 사용자가 정의한 함수

• DBMS에 저장되고 사용되는 함수

• SQL의 select, insert, update, delete statement에서 사용할 수 있습니다.

• 에러를 핸들링하거나 에러를 일으키는 다양한 동작을 정의할 수 있습니다.

stored function 사용법

임직원 ID의 맨 앞자리는 1로 고정하며 열자리 정수로 랜덤하게 발급

delimiter $$ -- 구분 문자를 ; -> $$로 변경
create function id_generator()
returns int -- return type 지정
no sql --SQL을 사용하지 않겠다는 의미
begin
    return (1000000000 + floor(rand() * 1000000000));
end
$$
delimiter ; -- 구분 문자를 $$ -> ;로 변경

• 위 코드는 1로 시작하는 10 자리의 랜덤한 숫자를 stored function 입니다.

부서의 ID를 파라미터로 받으면 해당 부서의 평균 연봉을 알려주는 함수

delimiter $$
create function dept_avg_salar(d_id int)
returns int
reads sql data
begin
    declare avg_sal int; -- 변수 선언
    select avg(salary)
    into avg_sal -- 변수를 선언하는 대신에 @avg_sal를 사용할 수 있습니다.
    from employee
    where dept_id = d_id;
    return avg_sal;
end
$$
delimiter ;

토익 800 이상을 충족했는지 알려주는 함수

delimiter $$
create function toeic_pass_fail(toeic_score int)
returns char(4)
NO SQL
begin
    if toeic_score is null
        then set @pass_fail = 'fail';
    elseif toeic_score < 800
        then set @pass_fail = 'fail';
    else
        set @pass_fail = 'pass';
    end if;
    return @pass_fail;
end
$$
delimiter ;

select *, toeic_pass_fail(toeic)
from student;

stored function 삭제하기

drop function stored_function_name

• drop function 을 통해서 stored function을 삭제할 수 있습니다.

stored function 조회하기

-- company db에 stored function들이 있는지 확인
show function status where DB = 'company';

-- id_generator가 어떻게 정의되었는지 확인
show create function id_generator;

SQL에서 join이란?

• 두 개 이상의 table들의 관계를 찾아 이를 기반으로 조합하여 출력할 때 사용하는 문법입니다.

• join에는 inner, outer, natural, cross 등 여러 종류가 있습니다.

implicit join vs explicit join

implicit join

select d.name
from employee as e, department as d
where e.id = 1 and e.dept_id = d.id;

• implicit join은 from 절에서 join할 table들을 나열하고 where 절에서 join condition을 설정합니다. 이러한 방식을 implicit joing이라고 합니다.

• implicit join의 단점은 join condition을 where절에서 select condition과 같이 사용하기 때문에 가독성이 매우 떨어집니다. 이러한 단점을 보완한 것이 추후에 추가된 explicit join입니다.

explicit join

select d.name
from employee as e join department as d on e.dept_id = d.id
where e.id = 1;

• explicit join은 implicit join의 단점이였던 가시성을 보완한 문법입니다. join을 from 절에 명시하고 join condition을 on 뒤에 작성함으로써 join을 사용 여부와 join condition의 가시성을 높였습니다.

inner join vs outer join

inner join

select d.name
from employee as e [ inner ] join department as d on e.dept_id = d.id
where e.id = 1;

• inner join은 위의 explicit join 예시에서 inner를 생략한 join과 동일합니다. 즉, inner 부분은 생략이 가능합니다.

• inner join은 join condition이 true를 만족하는 tuple들로 result table을 만드는 join 입니다.

outer join

from table1 left [outer] join table2 on join_condition

from table1 right [outer] join table2 on join_condition

from table1 full [outer] join table2 on join_condition

• outer join은 두 테이블에서 join condition을 만족하지 않는 tuple들도 result table에 포함되는 join입니다. left outer join, right outer join, ful outer join이 있습니다.

• []안에 있는 outer 부분은 생략 가능합니다.

left outer join

select *
from employee as e left outer join department as d
on e.dept_id = d.id

• left outer join은 left outer join 절의 왼쪽의 테이블을 기준으로 join condition에 맞지 않는 튜플도 출력되며 오른쪽 테이블의 join condition이 맞지 않는 attribute 부분들은 null로 채워져 출력됩니다.

right outer join

select *
from employee as e right outer join department as d
on e.dept_id = d.id;

• right outer join은 right outer join 절의 오른쪽 테이블을 기준으로 join condition에 맞지 않는 튜플도 출력되며 왼쪽 테이블의 join condition이 맞지 않는 attribtue 부분들은 null로 채워져 출력됩니다.

full outer join

pgrsql# select *
from employee as e full outer join department as d on e.dept_id = d.id;

• full outer join은 MySQL에서는 지원되지 않습니다. 그래서 위 쿼리는 PostgreSQL을 기준으로 작성되었습니다.

• full outer join은 left, right outer join이 합쳐진 join이며 양쪽 테이블의 모든 튜플을 출력하는데 join condition이 맞지 않을 경우 null로 채워 출력됩니다.

join과 using

select d.name
from employee as e inner join department as d on e.dept_id = d.dept_id

-- using 사용
select d.name
from employee as e inner join department as d using(dept_id)
where e.id = 1;

• 첫 번째 쿼리문을 사용할 경우 join condition에서 사용된 양측의 dept_id attribute가 두 번 출력됩니다. 이를 한 번만 출력되도록 변경하려면 using을 사용해서 on e.dept_id = d.dept_id을 생략할 수 있습니다.

• using의 조건은 equi join(=) 할 때 join하는 attribute의 이름이 같으면 사용할 수 있습니다.

natural join

from table1 natural [inner] join table2

from table1 natural left [outer] join table2

from table1 natural right [outer] join table2

from table1 natural full [outer] join table2

• 두 테이블에서 같은 이름을 가진 모든 attribute pair에 대해서 equi join을 수행합니다. 그렇기 때문에 이름은 같지만 다른 목적으로 사용되는 attribute가 있는지 유의하여 사용해야 합니다.

• join condition은 따로 명시하지 않습니다.

cross join

implicit cross join : from table1, table2

explicit cross join : from table1 cross join table2

-- 예시
select * from employee cross join department;

• 두 테이블의 tuple마다 모든 조합을 만들어 result table을 반환합니다.
  
  
  
• join condition을 사용하지 않습니다.
  
  
  
• MySQL에서는 cross join = inner join = join으로써, cross join에 on을 같이 쓰면 inner join으로 동작되며,inner join이 on 없이 사용되면 corss join으로 동작합니다.

self join

• table이 자신에게 join 하는 경우를 self join 이라고 합니다.

MySQL과 mongoDB의 기본적 차이

• MySQL은 관계지향형 Database System인 RDBMS이며, mongoDB는 Not Only SQL인 NoSQL입니다.

MySQL의 특징

• 데이터는 정해진 스키마에 따라서 데이터 베이스 테이블에 저장됩니다. 스키마를 지키지 않으며 데이터를 저장할 수 없습니다.

• 2차원 테이블 형식으로 데이터를 저장합니다.

• SQL을 사용해서 데이터 조회, 삽입, 삭제, 업데이트를 수행합니다.

mongoDB 특징

• Schema less 구조로서 정해진 스키마에 따르지 않고 원하는 데이터를 저장할 수 있기 때문에 각각의 document가 서로 다른 field를 가질 수 있습니다.

• 데이터를 저장하는 형식이 자유롭기 때문에 field에 배열이라던지 sub document를 저장할 수 있습니다.

MySQL과 mongoDB 수직, 수평 확장성

• MySQL과 mongoDB의 또 다른 차이점으로는 확장성에 있습니다. MySQL도 샤딩을 통해 수평적 확장이 가능하지만 특정 제한이 있으며 대체적으로 구현하기가 어렵습니다. mongoDB는 자체적으로 수평적 확장을 지원하여 MySQL에 비해 조금 더 수월하게 수평 확장을 구현할 수 있습니다.

MySQL과 mongoDB의 장.단점

MySQL 장점

• 스키마를 정의함으로서 저장할 데이터들의 형태를 미리 정의할 수 있습니다.

• 각각의 정규화된 테이블들의 관계를 통헤 데이터의 중복이나 삽입, 업데이트, 삭제시에 데이터 이상 현상을 방지할 수 있습니다.

• join을 통해서 관련있는 column을 기준으로 행을 합쳐 한 번에 조회할 수 있습니다.

• 데이터 정규화를 통해 불필요한 데이터의 중복을 방지할 수 있습니다.

MySQL 단점

• NoSQL에 비해 상대적으로 유연하지 않으며 데이터를 삽입하기 전에 반드시 스키마를 정의해야 합니다. 그리고 대용량의 데이터가 저장되어 있는 상태에서 column을 추가할 경우 모든 row에 이를 만영해야 되기 때문에 시간이 많이 소요되며 번거롭습니다.

• 테이블간의 관계를 통해서 join을 사용할 수 있지만, join은 상대적으로 복잡하고 DB의 부하를 줄 수 있습니다.

• NoSQL에 비해 상대적으로 수평적 확장이 어렵습니다. MySQL은 정규화로인해 테이블을 나누게 되는데, join에 필요한 테이블들이 서로 다른 shard에 있을 경우 그만큼 오버헤드가 증가합니다.

mongoDB 장점

• 스키마가 없기 때문에, RDBMS보다 상대적으로 유연하게 데이터를 저장할 수 있습니다.

• 기본적으로 수평적 확장을 지원하기 때문에 대용량의 데이터를 저장하기에 적합합니다.

• join문이 없는 대신에 document안에 document를 저장하여 join문을 사용하지 않고 읽으려는 document와 관련된 document를 한 번에 가져올 수 있습니다.
  
  
  
• mongoDB의 트랜잭션은 ACID의 일부를 포기하는 대신 높은 성능을 보여줍니다.

mongoDB 단점

• 유연성 때문에 데이터베이스 구조를 결정을 하지 못하고 미루게될 수 있습니다.

• 각 collection 마다 관계를 가지지 않고 정해진 규격없이 데이터를 저장하기 때문에 데이터의 중복 또는 이상현상이 발생될 수 있습니다. 그렇기 때문에 삽입, 수정, 삭제 시 중복 데이터가 발생되지 않도록 application 레벨에서 관리해주어야 합니다.
  
  
  
• 금융 시스템과 같이 consistency가 중요한 환경에서는 적합하지 않음

MySQL과 mongoDB 사용 시점

MySQL 사용 시점

• 관계를 맺고 있는 데이터가 자주 변경되는 애플리케이션일 경우 NoSQL보다는 MySQL이 유리합니다. ( NoSQL에서라면 여러 컬렉션을 모두 직접 수정해줘야 하기 때문입니다. )

• 스키마가 변경될 여지가 없고, 명확한 스키마가 사용자와 데이터에게 중요한 경우라면 MySQL이 유리합니다.

mongoDB 사용 시점

• 정확한 데이터 구조를 알 수 없거나 변경/확장될 수 있는 경우

• 읽기 처리를 자주하지만 데이터를 자주 변경하지 않는 경우, ( mongoDB는 collection마다 관계를 맺고있지 않기 때문에 수십 개의 문서를 직접 업데이트 해야할 수 있습니다. )

• 대용량의 데이터를 저장해야 하는 경우

insert

-- 1
insert into employee values (1, 'MESSI', '1987-02-01', 'M', 'DEV_BACK', 100000000, null);

-- 2
insert into employee values (2, 'JANE', '1996-05-05', 'F', 'DSGN', 90000000, null);

-- 3
insert into employee (name, birth_date, sex, position, id) values ('JENNY', '2000-10-12', 'F', 'DEV_BACK', 3);

-- 4
insert into employee values
    (4, 'BROWN','1996-03-13','M','CEO',120000000, null),
    (5, 'DINGYO','1990-11-05','M','CTO',120000000, null),
    (6, 'JULIA','1986-12-11','F','CFO',120000000, null),
    (7, 'MINA','1993-06-17','F','DSGN',80000000, null),
    (8, 'JOHN','1999-10-22','M','DEV_FRONT',65000000, null),
    (9, 'HENRY','1982-05-20','M','HR',820000000,null),
    (10, 'NICOLE','1991-03-26','F','DEV_FRONT',90000000, null),
    (11, 'SUZANNE','1993-03-23','F','PO',75000000, null),
    (12, 'CURRY','1998-01-15','M','PLN',85000000, null),
    (13, 'JISUNG','1989-07-07','M','PO',90000000, null),
    (14, 'SAM','1992-08-04','M','DEV_INFRA',70000000, null)

• insert시 attribute 이름을 생략할 경우 반드시 attribute가 정의된 순서대로 모두 insert할 데이터를 셋팅해주어야 합니다.

• 세 번째 방법처럼 attribute 이름을 명시하고 쿼리문을 작성할 경우 순서를 변경해서 insert할 데이터를 셋팅할 수 있으며, insert 하고싶은 데이터만 셋팅할 수 있습니다.

• 네 번째 방법은 여러 튜플을 한 번에 추가할 수 있습니다.

sql constraint로 인한 에러가 발생되었을 때 디버깅 방법

show create table employee;

• sql constraint로 인한 에러가 발생되었을 때 에러 문구를 확인하고 위 sql문을 실행하여 constraint 이름이 어떻게 정의되었는지 비교하여 디버깅하면 어떤 constraint에 의한 에러인지 찾기가 수월합니다.

update

update employee set dept_id = 1003 where id = 1;

update employee set salary = salary + 10000000 where dept_id = 1003;

update employee, works_on
set salary = salary * 2
where employee.id = works_on.empl_id and works_on.proj_id = 2003;

• update 문은 반드시 where 절을 신경써서 작성해주어야 합니다.

delete

delete from employee where id = 8;

delete from works_on where empl_id = 5 and proj_id != 2001;

-- 모든 project를 삭제
delete from project;

• update 문과 같이 where 절을 꼭 신경써서 작성해야 됩니다.

SQL(Structured Query Language)

• RDBMS에서 데이터 컨트롤에 쓰이는 표준 언어이지만 실제 구현에 강제가 없기 때문에 RDBMS마다 제공하는 SQL의 스펙이 조금씩 다릅니다.

SQL 주요 용어

• relation data model에서 사용하는 용어와 SQL에서 사용하는 용어는 위와 같이 맵핑됩니다.

MySQL에서의 database vs schema

• MySQL에서는 database와 schema는 같은 의미를 같습니다. 그렇기 때문에 CREATE DATABASE company == CREATE SCHEMA company가 같은 의미를 같습니다. 하지만, 다른 RDBMS에서는 다른 의미를 가질 수 있습니다.

MySQL 데이터 타입

숫자형

문자형

• 핸드폰 번호와 같이 고정된 길이를 저장할 떄는 가변 길이인 VARCHAR 보다는 CHAR를 사용하는 편이 성능에 조금 더 이점이 있습니다.

날짜와 시간

• TIMESTAMP 같은 경우는 UTC 값을 저장하여 타임존에 따라 변환하여 사용할 수 있습니다.

이진 데이터 타입

데이터 베이스 생성 및 삭제 관련 SQL

-- 데이터 베이스 생성
create database company;

-- 선택된 데이터 베이스 확인
select database();

-- 사용할 데이터 베이스 선택
use company;

-- company 데이터베이스 삭제
drop database company;

테이블 생성 SQL

create table department(
    id int primary key,
    name varchar(20) not null unique,
    leader_id int
);

foreign key를 사용하는 방법

• foreign key를 설정할 때 참조하는 값이 삭제되거나 업데이트될 때 foreign key value를 어떻게 할 것인지를 옵션을 설정할 수 있습니다.
  
  
  
• MySQL에서 RESTRICT와 NO ACTION은 완전히 동일합니다.
  
  
  
• on delete는 참조하는 foreign key가 삭제되었을 때 이벤트를 지정할 수 있으며, on update는 foreign key가 업데이트 되었을 때 이벤트를 지정할 수 있습니다.

foreign key를 적용하여 테이블 생성

create table employee(
id int primary key,
   name varchar(30) not null,
   birth_date date,
   sex char(1) check(sex in('M','F')),
   position varchar(10),
   salary int default 50000000,
   dept_id int,
   foreign key (dept_id) references department(id)
    on delete set null
       on update cascade,
check (salary >= 50000000)
);

create table project(
id int primary key,
   name varchar(20) not null unique,
   leader_id int,
   start_date date,
   end_date date,
foreign key (leader_id) references employee(id)
    on delete set null
       on update cascade,
check(start_date < end_date)
);

create table works_on(
empl_id int,
   proj_id int,
   primary key(empl_id, proj_id),
   foreign key(empl_id) references employee(id)
    on delete cascade
       on update cascade,
foreign key(proj_id) references project(id)
    on delete cascade
       on update cascade
);

-- department 테이블은 다른 테이블이 만들어진 후 만들어졌기 떄문에 alter table로 foreign key 지정
alter table department add foreign key(leader_id)
references employee(id)
   on delete set null
   on update cascade;

constraint 이름 명시하기

create table test(
    age int constraint age_over_20 check(age > 20)
)

• 위와 같이 constraint를 지정할 경우 제약 조건을 위반하였을 때 에러 메시지에 constraint의 이름도 같이 담기기 때문에 에러가 발생되었을 때 조금 더 찾기 수월해집니다.

데이터베이스(Database)란?

• 전자적으로(electronically) 저장되고 사용되는 관련있는(related) 데이터들의 조직화된 집합(organized collection)을 의미합니다.

DBMS(Database management system)

• DB를 정의하여 만들고 관리하는 기능을 제공하는 소프트웨어 시스템을 의미합니다. 대표적인 DBMS로는 PostgreSQL, MySQL, ORACLE, MSSQL 등이 있습니다.

• DB를 정의하다 보면 부가적인 데이터가 발생합니다. 이를 metadata라고 부릅니다. metadata 또한 DBMS를 통해 저장/관리됩니다.

metadata

• data에 대한 부가적인 정보를 기록한 데이터입니다. metadata는 catalog라고도 불립니다.

• e.g.) 데이터 유형, 구조, 제약 조건, 보안, 저장, 인덱스, 사용자 그룹 등등

Database system

• database + DBMS + 연관된 appication을 database system 이라고 합니다.

• 줄여서 Database라고도 부릅니다.

Database system 동작 방식

1. 클라이언트가 서비스 사용을 위한 request

2. 서버에서 이를 수신받은 후 서비스 처리를 위한 SQL을 DBMS에 request

3. 서버에서 request한 SQL을 수신받은 후 이를 분석

4. DBMS는 요청된 쿼리에 관련한 데이터의 정의를 파악한 뒤 관련된 Data가 저장된 DB에서 이를 처리하여 서버에 응답합니다.

data models

• data model은 DB의 구조(structure)와 읽고, 쓰기 위한 기본적인 동작들(operations)를 기술하는데 사용될 수 있는 개념들이 모인 집합입니다. 즉, data model은 DB 구조(데이터 유형, 데이터 관계, 제약 사항 등등)을 추상화해서 표현할 수 있는 수단을 제공합니다.

• data model은 여러 종류가 있으며 추상화 수준과 DB 구조화 방식이 조금씩 다릅니다. data model의 분류로는 conceptual(or high-level) data models, logical(or representaional) data models, physical(or low-level) data models 등이 있습니다.

conceptual(or high-level) data models

• 비개발자도 쉽게 이해할 수 있는 개념들도 이루어진 모델이며, 추상화 수준이 가장 높습니다. 주로 비즈니스 요구 사항을 추상화하여 표현할 때 사용합니다.

• conceptual data model 종류 중 하나로 entity-relationship model이 있습니다.

logical(or representational) data models

• 이해하기 어렵지 않으면서도 디테일하게 DB를 구조화할 수 있는 개념들을 제공합니다. 특정 DBMS나 storage에 종속되지 않는 수준에서 데이터가 컴퓨터에 저장될 때 구조와 크게 다르지 않게 DB 구조화를 가능하게 합니다.

• logical data model 종류로는 relational data model, object data model, object-relational data model 등이 있습니다. 오라클, MySQL, MSSQL 등은 relational data model을 기반한 DBMS입니다.

physical(or low-level) data models

• 컴퓨터에 데이터가 즉, data format, data orderings, access path(index) 등이 어떻게 파일 형태로 저장되는지를 기술할 수 있는 수단을 제공합니다.

database schema

• data model을 바탕으로 database의 구조를 기술(description) 및 표현한 것을 말합니다. 예를들어 data model 중 하나인 relational data model을 기반으로 하여 데이터 베이스 구조를 기술하는 것을 스키마라고 합니다.

database state

• 특정 시점에 database에 있는 데이터들을 database state 혹은 snapshot이라고 합니다.

• 혹은 database에 있는 현재 instance들의 집합이라고도 합니다

three-schema architecture

• database system을 구축하는 architecture 중 하나입니다.

• external(or user view) schema, conceptual schema, internal schema 라고 불리는 세 가지 level의 schema가 존재합니다.

사용 목적

• user application으로 부터 물리적인(physical) databse를 분리시키기 위해 사용됩니다. 즉, 물리적인 데이터베이스 구조가 바뀌더라도 해당 데이터베이스를 사용하는 user application에는 영향이 가지 않도록 하기 위해 사용됩니다. 즉, 어느 레벨에서의 변화가 다른 레벨에 영향을 주지 않음으로서 안정적인 운영을 목표로 합니다.

• 각 schema에서 변화가 생길 때 다른 레벨에 변화가 가지 않도록 구현하는 것은 상당히 어렵습니다. 최근 대부분의 DBMS들은 three-level architecture를 완벽하게 혹은 명시적으로 나누지는 않습니다.

internal schema

• 물리적으로 데이터가 어떻게 저장되는지 physical data model을 통해 표현하며 물리적인 저장 장치에 가장 가깝게 있는 schema입니다.

• data storage, data structure, access path 등등 실체가 있는 내용을 기술합니다.

external schema

• 실제 사용자가 바라보는 스키마로서 external view, user view 라고도 불리며, logical data model을 통해 표현하며 특정 유저들이 필요로 하는 데이터만 정의하여 표현할 수 있습니다.

conceptual schema

• DBMS 초창기 아키텍쳐는 internal, external schema만 존재했습니다. 하지만, 각각의 유저마다 필요로 하는 데이터들이 달라지다보니 여러 개의 internal schema가 생성되었으며 이로인해 데이터가 중복되는 현상이 발생되었습니다. 이를 해결하기 위해 중간 단계인 conceptual schema를 도입하였습니다.

• 전체 database에 대한 구조를 기술하며, 데이터의 entities, data types, relationships와 데이터들을 활용할 수 있는 user operations와 제약 조건에 집중해서 데이터 베이스 구조를 logical data model을 이용해 표현한 것이 conceptual schema 입니다.

database language

DDL(data definition language)

• conceptual schema를 정의하기 위해 사용되는 언어입니다. internal schema까지 정의할 수 있는 경우도 있습니다.

SDL(Storage definition language)

• internal schema를 정의하는 용도로 사용되는 언어입니다. 최근들어서는 특히 RDBMS에서는 SDL로 정의하는 것보다는 파라미터 등의 설정으로 등으로 정의합니다.

VDL(View definition language)

• external schema를 정의하기 위해 사용되는 언어입니다. 대부분의 DBMS에서는 DDL이 VDL 역할까지 수행합니다.

DML(Data manipulation language)

• database에 있는 data를 활용하기 위한 언어로서 data 추가, 삭제, 수정, 검색 등등의 기능을 제공합니다.