TIL - 6

SELECT 살펴보기

SELECT 사용하기 전에

• SHOW DATABASES;

• USE prod; – 데이터베이스 이름

• SHOW TABLES;

SELECT (1)

• 테이블에서 레코드들을 읽어오는데 사용

• WHERE를 사용해 조건을 만족하는 레코드

SELECT 필드이름1, 필드이름2, ...,
FROM 테이블이름
WHERE 선택조건
GROUP BY 필드이름1, 필드이름2
ORDER BY 필드이름 [ASC|DESC] -- 필드이름 대신에 숫자 사용 가능
LIMIT N;

SELECT (2)

SELECT * -- *은 모든 필드를 지칭
FROM prod.session; -- 앞서 USE prod; 를 수행했다면 session도 사용 가능

SELECT (3)

SELECT id, user_id, channel_id
FROM session;

SELECT *
FROM session
LIMIT 10;

SELECT (4)

SELECT DISTINCT channel_id -- 유일한 채널 ID를 알고 싶은 경우
FROM session;

SELECT channel_id, COUNT(1) -- 채널 ID별 카운트를 하려면 그룹 바이 / 카운트 함수
FROM session
GROUP BY 1;

SELECT (5)

SELECT COUNT(1) -- 테이블의 모든 레코드 수 카운트 COUNT(*) 하나의 레코드
FROM prod.session;

SELECT *  -- 채널 테이블의 모든 레코드 표시
FROM channel

select count(1)
from channel
where channel_id = 5; -- channel이 facebook 인 경우

CASE WHEN

• 필드 값의 변환을 위해 사용 가능
• CASE WHEN 조건 THEN 참일때 값 ELSE 거짓일때 값 END 필드이름

• 여러 조건을 사용하여 변환하는 것도 가능하다.

CASE
    WHEN 조건1 THEN 값1
    WHEN 조건2 THEN 값2
    ELSE 값3
END 필드이름

SELECT channel_id, CASE
    WHEN channel_id in(1,5,6) THEN 'Social-Media'
    WHEN channel_id in(2,4) THEN 'Search-Engine'
    ELSE 'Something-Else'
END channel_type
FROM session

NULL 이란?

• 값이 존재하지 않음을 나타내는 상수 0혹은 ""과는 다름

• 필드 지정시 값이 없는 경우 NULL로 지정 가능
• 테이블 정의시 디폴트 값으로도 지정 가능

• 어떤 필드의 값이 NULL인지 아닌지 비교는 특수한 문법을 필요로함
• field 1 is NULL 혹은 field 1 is not null

• NULL이 사칙연산에 사용되면 그 결과는?
• select 0 + null, 0 - null, 0 * null, 0/null

Where

• IN
• where channel_id id (3,4)
• where channel_id = r or channel_id =4
• not in과 함께 쓰임

• LIKE
• LIKE : 대소문자 구별 없이 문자열 매칭 기능을 제공해줌
• where channel LIKE ‘G%’ -> ’G*’
• where channel LIKE ‘%o%’ -> ‘o’
• NOT LIKE와 함께 쓰임

• BETWEEN
• 날짜 범위에 사용 가능

• 위의 오퍼레이터들은 CASE WHEN사이에서도 사용이 가능하다.

SELECT  COUNT(1)
FROM prod.session
WHERE channel_id IN (4,5);

SELECT DISTINCT channel
FROM prod.channel
WHERE channel LIKE '%o%';


SELECT DISTINCT channel
FROM prod.channel
WHERE channel NOT LIKE '%o%';

String 함수

• LEFT(str,N)

• REPLACE(str, exp1, exp2)

• UPPER(str)

• LOWER(str)

• LENGTH(str)

• LPAD,RPAD

• SUBSTRING

• CONCAT

ORDER BY

• 디폴트 순서는 오름차순(작은 값이 먼저 나옴)
• ORDER BY 1 ASC

• 내림차순을 원하면 DESC
• ORDER BY 1 DESC

• 여러개의 필드를 사용해서 정렬하려면
• ORDER BY 1 DESC, 2, 3

• NULL 값 순서는?
• 오름차순일 경우 처음에 위치
• 내림차순일 경우 마지막에 위치

select value from prod.count_test
order by value desc;

select value from prod.count_test
order by value asc;

타입 변환 (1)

• DATE Conversion
• NOW
• 타임존 관련 변환
• CONVERT_TZ(now(),‘GMT’,‘Asia/Seoul’)
• DATE, WEEK, MONTH, ,YEAR, HOUR, MINUTE, SECOND, QUARTER, MONTHNAME
• DATEDIFF
• DATE_ADD
• …

• STR_TO_DATE, DATE_FORMAT

타입 캐스팅

• 1/2 결과는?
• 0이된다. 정수간의 연산은 정수가 되어야 하기 때문
• 분자나 분모중의 하나를 float으로 캐스팅해야 0.5가 나온다.
• 이는 프로그래밍 언어에서도 일반적으로 동일하게 동작함
• case 함수를 사용
• cast(category as float)
• convert(expression, float)

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

GROUP BY

• select 실행 시 결과를 특정 그룹별로 묶는 방법

GROUP BY & Aggregate 함수 (1)

• 테이블의 레코드를 그룹핑하여 그룹별로 다양한 정보를 계산

• 두단계로 이루어진다.
• 먼저 그룹핑을 할 필드를 결정(하나 이상의 필드가 될 수 있다.)
• GROUP BY로 지정(필드 이름을 사용하거나 필드 일련번호 사용)
• 다음 그룹별로 계산할 내용을 결정
• 여기서 Aggregate함수를 사용
• COUNT, SUM, AVG, MIN, MAX, GROUP _CONCAT, …
• 보통 필드 이름을 지정하는 것이 일반적(alias)

GROUP BY & Aggregate 함수 (2)

• 월별 세션수를 계산하는 SQL
• prod.session 사용 (id, create 필드)

select
    LEFT(create, 7) AS mon,
    COUNT(1) AS session_count
from prod.session
GROUP BY 1 -- GROUP BY mon, GROUP BY LEFT(create, 7)
ORDER BY 1;

학습예제

SHOW DATABASES;
USE prod;
SHOW TABLES;

SELECT *
FROM prod.session;


SELECT id, user_id, channel_id
from prod.session;

select *
from prod.session
limit 10;

-- 유일한 채널 ID를 알고 싶은 경우
select distinct channel_id
from prod.session;

-- 채널 id별 카운트를 하려면?
select channel_id, count(1)
from prod.session
group by 1;

-- 테이블의 모든 레코드 수 카운트
select count(1)
from prod.session;

-- 채널 테이블의 모든 레코드 수카운트
select *
from prod.channel;

-- 채널이 페이스북인 경우만 레코드 수 카운트
select count(1)
from prod.session
where channel_id=5; -- where channel_id in (5)

-- CASE WHEN
select channel_id,
    CASE
        WHEN channel_id in (1,5,6) THEN 'social-media'
        WHEN channel_id in (2,4) THEN 'search-engine'
        ELSE 'somthing-else'
    END channel_type
from prod.session;


 select distinct channel_id,
    CASE
        WHEN channel_id in (1,5,6) THEN 'social-media'
        WHEN channel_id in (2,4) THEN 'search-engine'
        ELSE 'somthing-else'
    END channel_type
from prod.session;

-- count 제대로 이해하기
select * from prod.count_test;
select count(1) from prod.count_test;
select count(0) from prod.count_test;
select count(null) from prod.count_test;
select count(value) from prod.count_test;
select count(distinct value) from prod.count_test;

-- where 조건
select count(1)
from prod.session
where channel_id in (4,5);

select distinctchannel
from prod.channel
where channel LIKE '%G%';