SQL 실습코드

show
use T

select ###1,###2,###3							조회할 필드 선택
from T									
limit #;								조회할 데이터의 숫자를 제한

select distinct ###							중복 데이터를 제거할 수 있다.

select ###, count(###)							count로 개수를 조회할 수 있음
from T
group by ###;								ㅎ

select count(*)
from session
where ### = 조건;

select distinct ###,						 	필드 값의 변환을 위해
	case
		when ### in (1,2,3) then 'A표현'				if else 처럼 사용할 수 있다.
		when ### in (4,5,6) then 'B표현'
		Else 'C표현'
		end 표현할 필드네임,
	count(*)
from T
group by ###;

- NULL 표현
TwithNull = null이 포함된 테이블
select count(1) from TwithNull;					NULL 포함됌
select count(0) from TwithNull;					NULL 포함됌
select count(null) from TwithNull;				0
select count(value) from TwithNull;				NULL 포함 안됌.
select count(distinct value) from TwithNull;			NULL 포함 안됌.


select count(###)
from T
where (###) in (1,2,3);

select count(###)
from T
where ### like '%포함될_데이터%';

select distinct (###)
from T
where (###) not like '%포함안될_데이터%';

select
	length((###)),
	upper((###)),
	lower((###)),
	left((###),4),
	rpad((###),15,'-'),
	lpad((###),15,'-')
from T;

select ###
from T
order by ### desc(asc);

select
	#time#, convert_tz(#time#, 'GMT','Asia/Seoul') seoul_time,
	year(#time#) y, quarter(#time#) q, month(#time#) m , monthname(#time#) mnn,
    	date(#time#) d, hour(#time#) h, minute(##time#) m, second(#time#) s
from T;

select #time#, now(),
	datediff(now(), #time#) gap_in_days,
	date_add(#time#, interval 10 day) ten_days_after_created
from T;

select str_to_date('01,5,2013','%d,%m,%y');

select cast('100.0' as float), convert('100.0',float);


select
	left(#time#, 7) as mon,
	count(mon) as session_count
from T
group by mon
order by mon;

select
	###1,
	count(###1) as session_Count,
	count(distinct ###2) as user_count
from T
group by ###1
order by session_Count desc;


select
	###1, count(###1) as session_Count
from T
group by ###1
order by session_Count desc
limit 1;

select
	left(#time#, 7) as mon,
	count(distinct ###) as user_count
from T
group by mon
order by mon;

select s.id, s.user_id, s.created, s.channel_id, c.channel
from T1 s
join T2 c on c.id = s.channel_id;

select
	left(#time#, 7) as mon,
	c.channel,
	count(distinct ###) as mau
from T1 s
join T3 c on c.id = s.channel_id
group by 1,2
order by 1 desc, 2;