mysql 함수 사용하여 선택 조회

null 함수 / trim 함수

eng_title컬럼이 없는 경우 조회

select * 
from movie m
where eng_title is null 
or trim(eng_title) = ' ' 
or length(trim(eng_title))=0
limit 10
;

count 함수

select count(*) 
from movie 
;

like 함수

select * 
from movie m
where country ="한국" 
	and genre like "%액션%" 
	and pub_year  = 2001

in 함수

select * from  actor 
where domain = "감독"
 and name in (
 	select director
 	from movie m
	where production like "%싸이더스%"
	and pub_year = 2020
 	)

중복제거

distinct

select DISTINCT domain
from actor
where domain is not null and trim(domain != ' ')

group by

select DOMAIN 
from actor
where domain is not null and trim(domain!=' ')
group by domain

join, order by desc

select m.title, m.director , m.pub_year , m.genre , m.open_flag  
from movie m
join  actor a on ( m.director = a.name and a.domain = "감독")
where m.pub_year  > 2020
and a.country  = '독일'
order by m.pub_year DESC

case when

select f.code 
,f.title
, case 
	when ifnull(f.eng_title,'') = '' then f.title 
	else f.eng_title 
end
,f.continent 
,f.country 
,f.city 
,
case 
	when ifnull(f.gerne,'')= ''then "기타"
	else f.gerne
end
,f.important_flag 
,case
	when ifnull(f.homepage,'') = ''then "홈페이지 없음"
	else f.homepage 
end
,f.first_open_date 
from festival f 
where country ="미국"
and city = "시카고"

max 함수

select
ROW_NUMBER () over (order by max_seat_count DESC )
,max(seat_count) as max_seat_count
, biz_name 
from screen s
where biz_name is not null and trim(biz_name) !=' ' 
group by biz_name 
order by max_seat_count DESC