4주차. 내용 정리

1. or

select * from orders o where email like '%gmail.com' or email like '%naver.com' or email like '%daum.net';

 

2. Is null, Is not null

select * from users u left join point_users pu on pu.user_id = u.user_id where pu.point_user_id is NULL;

 

3. 시간 데이터

select enrolled_detail_id, seen_date, done_date, DATEDIFF(done_date , seen_date) 
from enrolleds_detail ed 
where done = true and seen = true and DATEDIFF(done_date , seen_date) > 0;

select enrolled_detail_id, seen_date, done_date, TIMESTAMPDIFF(MINUTE , seen_date, done_date) 
from enrolleds_detail ed 
where done = true and seen = true and TIMESTAMPDIFF(MINUTE , seen_date, done_date) > 0;

 

4. 시간 차이

select enrolled_detail_id , CURDATE(), done_date, datediff(CURDATE(), done_date) 
from enrolleds_detail ed 
where done = TRUE and seen = TRUE 
order by datediff(CURDATE(), done_date) desc;

select enrolled_detail_id , ADDDATE(CURDATE(), 100), done_date, datediff(CURDATE(), done_date) 
from enrolleds_detail ed
where done = TRUE and seen = TRUE ;

 

5. Group by, order by

select ed.enrolled_id, ed.week, count(*) 
from enrolleds_detail ed 
group by ed.enrolled_id, ed.week ;

select * from enrolleds_detail ed 
order by enrolled_id, week, current_order ;

 

6. SUBSTRING

select user_id, email, SUBSTRING_INDEX(email, '@', 1) from users ;

select c.checkin_id, c.comment, SUBSTRING(c.comment, 3, 5) from checkins c

 

7. CASE

select order_no, payment_method, 
case payment_method when 'kakaopay' then '카카오페이' 
					when 'CARD' then '카드' 
                    else '기타' 
                    END as '결제수단' 
from orders;

select pu.point_user_id, pu.point, 
	case when pu.point > 10000 then '잘했어요!!' 
   		 else '분발하세요!' END as '구분' 
from point_users pu;