데이터 베이스 Functions

Functions (concat, ceil, round, truncate, date_format)

CEIL

ceil : 실수 데이터를 올림할 때 사용한다.

1	select ceil (12.345); # 13

ceil은 소수점 몇 째 자리라는 개념이 없다. 만약 ceil을 사용해서 소수 둘째자리 올림을 하고 싶다면 다음과 같이 작성해야 한다.

1 2	# 100을 곱해서 1234.5 라는 숫자를 만들고 올림한후 다시 나누어준다. select ceil(12.345 * 100) / 100

ROUND

round : 실수 데이터를 반올림할 떄 사용한다.

1 2	# 12.345를 소수 둘째자리까지 나타내고 소수 셋째 자리에서 반올림한다. select round(12.345, 2);

ex) 국가별 언어 사용 비율을 소수 첫째 자리에서 반올림해서 정수로 나타냄

1 2	select countrycode, language, percentage, ROUND(percentage, 0) from countrylanguage;

TRUNCATE

truncate : 실수 데이터를 버림할 때 사용한다.

1 2	# 12.345를 소수 둘째 자리까지 나타내고 소수 셋째자리에서 버림한다. select truncate(12.345, 2)

ex) 국가별 언어 사용 비율을 소수 첫번째 자리에서 버림하여 정수로 나타냄

1 2	select countrycode, language, percentage, truncate(percentage, 0) from countrylanguage;

DATE_FORMAT

date_format : 날짜 데이터에 대한 포맷을 바꿔준다.

다음은 date와 관련된 링크이다.

https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html

# sakila 데이터 베이스에서 월별 총 수입
select DATE_FORMAT(payment_date, "%Y-%m") as monthly, sum(amount) as amount
from payment
group by monthly;

만약 데이트 포맷을 정해주지 않는다면 시, 분, 초까지 비교해서 같은 것들끼리 합쳐진다.

년, 월만 같은 것끼리 그룹하고 싶다면 date_format을 정해줘야한다.

Functions 2 (IF, IFNULL, CASE)

SQL에서도 다른 언어에서 처럼 조건문 사용이 가능하다.

IF

if : 조건을 주고 참, 거짓에 따라 다르게 출력해줄 수 있다.

ex) 도시의 인구가 100만이 넘으면 “big city” 그렇지 않으면 “small city”를 출력하는 city_scale 컬럼을 추가

1 2	select name, population, if(population > 1000000, "big city", "small city") as city_scale from city;

IFNULL

ifnull : 데이터 값이 null인 컬럼이 있다면 원하는 값으로 교체할 수 있다.

ex) 독립년도가 없는 데이터는 0으로 출력

1 2	select indepyear, ifnull(indepyear, 0) as indepyear from country;

CASE

case

when (조건1) then (출력1)

when (조건2) then (출력2)

END AS (컬럼명)

ex) 나라별로 인구가 10억 이상, 1억 이상, 1억 이하인 컬럼을 추가하여 출력

select name, population,
    case
        when population > 100000000 then 'upper 1 bilion'
        when population > 10000000 then 'upper 100 milion'
        else 'below 100 milion'
    end as result
from country;

JOIN

join : 여러 개의 테이블에서 데이터를 모아서 보여줄 때 사용된다. join에는 inner join, left join, right join이 있다.

MAKE TEST TABLE & DATA

CREATE TABLE user (
    user_id int(11) unsigned NOT NULL AUTO_INCREMENT,
    name varchar(30) DEFAULT NULL,
    PRIMARY KEY (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE addr (
     id int(11) unsigned NOT NULL AUTO_INCREMENT,
     addr varchar(30) DEFAULT NULL,
    user_id int(11) DEFAULT NULL,
     PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO user(name)
VALUES ("jin"),
 ("po"),
 ("alice"),
 ("petter");
 

INSERT INTO addr(addr, user_id)
VALUES ("seoul", 1),
     ("pusan", 2),
     ("deajeon", 3),
     ("deagu", 5),
     ("seoul", 6);

INNER JOIN

두 테이블 사이에 공통된 값이 없는 row는 출력하지 않는다.

ex) 두 테이블을 합쳐 id, name, addr 출력

select id, user.name, addr.addr
from user
join addr
on user.user_id = addr.user_id;
-- user 테이블을 기준으로 addr 테이블을 합치겠다.

ex) world 데이터 베이스에서 도시이름과 국가이름을 출력

select city.name as city_name, country.name as country_name
from city
join country
on city.countrycode = country.code

위 문제를 where를 사용해서 나타낼 수 도 있다.

1
2
3

select city.name as city_name, country.name as country_name
from city, country
where city.countrycode = country.code

LEFT JOIN

왼쪽 테이블을 기준으로 왼쪽 테이블의 모든 데이터가 출력되고 매핑되는 키값이 없으면 NULL로 출력된다.

ex) 두 테이블을 합쳐 id, name, addr 출력

select id, user.name, addr.addr
from user
left join addr
on user.user_id = addr.user_id;

RIGHT JOIN

오른쪽 테이블을 기준으로 왼쪽 테이블의 모든 데이터가 출력되고 매핑되는 키값이 없으면 NULL로 출력된다.

ex) 두 테이블을 합쳐 id, name, addr 출력

select id, user.name, addr.addr
from user
right join addr
on user.user_id = addr.user_id;

JOIN 연습문제

지역과 대륙별 사용하는 언어 출력

select distinct country.region, country.continent, countrylanguage.language
from country
join countrylanguage
on countrylanguage.countrycode = country.code;

대륙과 지역별 사용하는 언어의 수 출력

select sub1.region, sub1.continent, count(*) as count
from (
    select distinct countrh.region, country.continent, countrylanguage.language
  from country
  join countrylanguage
  on countrylanguage.countrycode = country.code
) as sub1
group by sub1.region, sub1.continent;

국가별, 도시별 언어의 사용율(테이블 세개 조인하기)

select city.name, city.countrycode, country.name, country.code, countrylanguage.language
    , countrylanguage.percentage
from city
join country
on    country.code = city.countrycode
join countrylanguage
on countrylanguage.countrycode = country.code;

UNION

union은 select 문의 결과 데이터를 하나로 합쳐서 출력한다. 단, 컬럼의 개수와 타입, 순서가 같아야 한다.

union은 자동으로 distinct를 하여 중복을 제거해준다. 중복제거를 안하고 컬럼 데이터를 합치고 싶으면 union all을 사용한다.

또한 union을 이용하면 Full Outer Join을 구현할 수 있다.

ex) user 테이블의 name 컬럼과 addr 테이블의 addr 컬럼의 데이터를 하나로 합쳐서 출력

select name
from user
union
select addr
from addr

UNION ALL

ex) 중복데이터를 제거하지 않고 결과 데이터 합쳐서 출력

select name
from user
union all
select addr
from addr;

FULL OUTER JOIN

ex) union을 이용하여 full outer join 구현

select id, user.name, addr.addr
from user
left join addr
on user.user_id = addr.user_id
union
select id, user.name, addr.addr
from user
right join addrr
on user.user_id = addr.user_id

Sub Query

sub query는 query 문 안에 있는 query를 의미한다. select절, from절, where절 등에 사용이 가능하다.

ex) 전체 나라수, 전체 도시수, 전체 언어수를 출력 (select 절에 사용)

select
    (select count(name) from city) as total_city,
    (select count(name) from country) as total_country,
    (select count(distinct(language)) from countrylanguage) as language
from dual;

ex) 800만 이상이 되는 도시의 국가코드, 이름, 도시인구수를 출력 (from 절에 사용)

select *
from (
  select countrycode, name, population
  from city
    where population >= 8000000
)
join (select code, name from country) as country
on city.countrycode = country.code;

ex) 800만 이상 도시의 국가코드, 국가이름, 대통령 이름을 출력 (where 절에 사용)

select code, name, headofstate
from country
where (
    select distinct(countrycode) from city where population > 8000000
)

VIEW

가상 테이블로 데이터만 보고자 할때 사용한다. 실제 데이터를 메모리에 저장하고 있지는 않다. 한마디로 특정 컬럼의 데이터를 보여주는 역할만 한다. 뷰를 사용함으로 쿼리를 더 단순하게 만들 수 있다. 한번 생성된 뷰는 수정이 불가능하며 인덱스 설정이 불가능하다.

Syntax

CREATE VIEW <뷰 이름> AS

(QUERY)

ex) 국가 코드와 국가 이름이 있는 뷰 생성

1
2
3

create view code_name as
select code, name
from country;

ex) city 테이블에 국가 이름 추가

select *
from city
join code_name
on city.countrycode = code_name.code;

INDEX

테이블에서 데이터를 검색할 때 빠르게 찾을 수 있도록 해주는 기능이다.

장점 : 검색속도가 빨라짐
단점
- 저장공간을 10% 정도 더 많이 차지
- INSERT, DELETE, UPDATE 할때 속도가 느려짐
사용법 : SELECT시 WHERE 절에 들어가는 컬럼을 Index로 설정하면 좋다.
내부 작동 원리(B-Tree) : 루프노드와 리프노드의 계층적 구조로 루트노드를 이용하여 리프노드에서의 데이터를 빠르게 찾을 수 있는 자료구조 알고리즘

# employees 데이터 베이스에서 실행
# 실행계획을 확인하여 인덱스를 사용하는지 확인
explain
select *
from salaries
where from_date < "1986-01-01";

explain
select *
from salaries
where to_date < "1986-01-01";

인덱스 확인

1	show index from salaries;

인덱스 생성

create index fdate
on salaries (from_date);

create index tdate
on salaries (to_date);

여러 개의 컬럼을 가지는 인덱스 생성도 가능

1 2	create index ftdate on salaries (from_date, to_date);

인덱스 삭제

drop index fdate
on salaries

drop index tdate
on salaries

drom index ftdate
on salaries

여러 개의 컬럼을 조건으로 where절에 사용하는 경우 인덱스 확인

# 인덱스가 하나의 컬럼에 있을때보다 둘다 있을 때가 더 빠르다.
explian
select *
from salaries
where from_date < "1985-01-01" and to_date < "1986-01-01";

TRIGGER

특정 테이블을 감시하고 있다가 설정한 조건에 감지되면 지정해 놓은 쿼리가 자동으로 실행되도록 하는 방법

Syntax

CREATE TRIGGER trigger_name

{BEFORE | AFTER} {INSERT | UPDATE | DELETE}

on table_name FOR EACH ROW

BEGIN

trigger_body;

END;

ex) 데이터를 삭제하면 다른 테이블에 백업하는 트리거 생성

create database tr;
use tr;

create table chat(
    id VARCHAR(20),
  answer VARCHAR(32) NOT NULL
);

create table chatBackup (
    idBackup VARCHAR(32),
  answerBackup VARCHAR(32) NOT NULL
);

# 트리거 설정
delimiter |
    create trigger backup
  before delete on chat
  for each row begin
          insert into chatBackup(idBackup, answerBackup)
          values(old.id, old.answer);
    end |

# 트리거 확인
show triggers;

# 쿼리 실행
delete from chat
where id = "pigg"
limit 10;

# 트리거가 실행되었는지 확인
select * from chatBackup;