sql에서 function 종류

ceil -> 실수 데이터를 올림 할 때 사용합니다.

ex) select ceil(12.345*100)/100;

 

round -> 실수 데이터를 반올림 할 때 사용합니다.

ex) select round(12.345, 2);

 

truncate -> 실수 데이터를 버림 할 때 사용합니다.

ex) select truncate(12.345, 2);

 

date_format -> 날짜 데이터에 대한 포맷을 바꿔줍니다.

정규식 참조 페이지 dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html

ex)

select sum(amount) as income, date_format(payment_date, "%Y-%m") as monthly
from payment
group by monthly;

 

if -> 조건문을 만들 때 사용(조건, 참, 거짓)

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

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

# 만약 조건 결과 부분에 컬럼명을 써주면 해당 컬럼의 데이터가 출력됩니다.

 

ifnull -> null 데이터를 특정 데이터로 바꿔줄 때 사용합니다. (참, 거짓)

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

select indepYear, ifnull(indepYear, 0) as indepYear
from country;

 

case when then end -> 자바스크립트에서 if else와 같습니다.

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

select name, population,
case
when population > 1000000000 then "big"
    when population > 100000000 then "medium"
    else "small"
end as result
from country
order by population desc;

 

distinct -> 중복 제거 함수입니다.

ex) country에서 어떤 대륙이 있는지 출력

select continent
from country
group by continent;

# 위의 쿼리문으로도 중복을 없앨 수 있고 아래 쿼리문도 같은 결과를 출력합니다.

select distinct continent
from country;

 

count -> 데이터의 갯수를 출력

ex)

select count(distinct(continent))
from country;

 

join -> 여러 개의 테이블에서 데이터를 모아서 보여줄 때 사용합니다. join에는 inner join, left join, right join, outer join이 있습니다. (기본적으로 join만 쓰면 inner join이 적용됩니다.)

ex) inner join

select user.user_id, user.name, addr.addr_id, addr_addr_name, addr.user_id

from user

inner join addr

on user.user_id = addr.user_id;

ex) 여러 개 조인할 때는 조인을 계속 쓰면 가독성이 안좋기 때문에 콤마로 줄일 수 있습니다. on은 where로 바꿔줘야 됩니다.

select user.user_id, user.name, addr.addr_id, addr_addr_name, addr.user_id

from user, addr

where user.user_id = addr.user_id;

ex) world 데이터 베이스에서 인구수가 100만이 넘는 도시의 국가코드, 국가이름, 도시이름, 국가인구수, 도시인구수 출력

select city.countryCode, country.name, city.name, country.population, city.population

from country

join city

on city.countryCode = country.code

having city.population > 1000000

order by city.population desc;

# left join과 rightjoin을 할 때 값이 없으면 null이 들어갑니다.

ex) 테이블 3개 join (국가별, 도시별, 언어 사용율을 출력)

select country.code, country.name, city.name, countrylanguage.language, countrylanguage.percentage

from country

join city

on country.code = city.countryCode

join countrylanguage

on country.code = countrylanguage.countryCode;

# 아래 코드는 같은 결과를 출력합니다.

select country.code, country.name, city.name, countrylanguage.language, countrylanguage.percentage

from country, city, countrylanguage

where country.code = city.countryCode and country.code = countrylanguage.countryCode;

ex) 컬럼을 추가해서 도시별로 해당언어 사용인구수 출력 (도시에서 사용하는 언어의 비율은 국가에서 사용하는 언어의 비율을 따릅니다.)

select country.code, country.name, city.name, countrylanguage.language, countrylanguage.percentage, round(city.population * countrylanguage.percentage / 100)

from country, city, countrylanguage

where country.code = city.countryCode and country.code = countrylanguage.countryCode;

 

union -> select 문의 결과 데이터를 하나로 합쳐서 하나로 출력합니다. 컬럼의 갯수와 타입, 순서가 같아야 합니다. union은 자동으로 distinct를 하여 중복을 제거해 줍니다. 중복제거를 안하고 컬럼 데이터를 합치고 싶으면 union all을 사용합니다. 또한 union을 이용하면 full outer join을 구현할 수 있습니다.

ex)

select user_id
from user
union all
select user_id
from addr;

ex) union을 이용한 outer join

select user.user_id, user.name, addr.addr_name

from user

left join addr

on user.user_id = addr.user_id

union

select addr.user_id, user.name, addr.addr_name

from user

right join addr

on user.user_id = addr.user_id;

 

sub query -> sub query는 query 문 안에 있는 query를 의미합니다. select절 from절, where 등에 사용이 가능합니다.

ex) select절에서 sub query 사용 (전체 나라수, 전체 도시수, 전체 언어수를 1개의 row로 출력)

select
    (select count(*) from country) as country_count,
    (select count(*) from city) as city_count,
    (select count(distinct(language)) from countrylanguage) as language_count
from dual;

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

# sub query를 안썼을 때

select country.code, country.name, city.name, city.population

from city

join country

on country.code = city.countrycode

having city.population >= 8000000;

#sub query를 썼을 때 (전체 도시를 조인하지 않아도 됩니다.)

select country.code, country.name, city.name, city.population

from (select countrycode, name, population from city where population >= 8000000) as city

join country

on country.code = city.countrycode;

ex) where절에서 sub query 사용 (900만 이상의 도시를 갖는 국가의 국가코드, 국가이름, 대통령이름을 출력)

select code, name, headOfState
from country
where code in (select countryCode from city where population >= 9000000);

 

view -> 가상 테이블로 쿼리문을 조금 더 간결하게 하기 위해서 사용합니다. 실제 데이터를 저장하고 있지는 않습니다. 한마디로 특정 컬럼의 데이터를 보여주는 역할만 합니다. 뷰를 사용함으로 쿼리를 더 단순하게 만들 수 있습니다. 한 번 생성된 뷰는 수정이 불가능 하며 인덱스 설정이 불가능합니다.

ex) 

create view code_name as
select code, name
from country
where population >= 100000000;

# 사용할 때는 아래와 같이 테이블명 쓰듯 써줍니다.

select country.code, country.name, city.name, city.population

from code_name as country

join city

on city.countryCode = country.code;

 

index -> 테이블에서 데이터를 검색할 때 빠르게 찾을 수 있도록 해주는 기능입니다. 단점도 가지고 있는데, 저장공간을 차지하게 되고 insert, delete, update시 속도가 느려집니다. where절에서 자주 사용하는 컬럼을 index로 설정해주는 것이 좋습니다.

ex) index 생성

create index fdate
on salaries (from_date);

ex) index 삭제

drop index fdate
on salaries;

 

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

ex) chat 테이블에 delete 쿼리가 실행되면 해당 데이터를 chatBackup에 저장

# trigger 생성

delimiter |
    create trigger backup
    before delete
    on chat
    for each row
    begin
        insert into chatBackup(id_bak, msg_bak)
        value (old.id, old.msg);
end |

# trigger 보기

show triggers;

#실습

delete from chat
where msg like "h%" limit 10;

#실행 결과 보기

select * from chatBackup;