📝SQL 기본 다지기(Select, Group By)

📝8일차 데이터베이스 실습

1. SQL(DDL,DML,DCL)

DDL(Data Definition Language) - 데이터 정의어

데이터베이스를 정의하는 언어이며, 데이터리를 생성, 수정, 삭제하는 등의 데이터의 전체의 골격을 결정하는 역할을 하는 언어이다.

종류	역할
CREATE	데이터베이스, 테이블등을 생성하는 역할을 합니다.
ALTER	테이블을 수정하는 역할을 합니다.
DROP	데이터베이스, 테이블을 삭제하는 역할을 합니다.
TRUNCATE	테이블을 초기화 시키는 역할을 합니다.

• SCHEMA, DOMAIN, TABLE, VIEW, INDEX를 정의하거나 변경 또는 삭제할 때 사용하는 언어입니다.
• 데이터 베이스 관리자나 데이터베이스 설계자가 사용한다.

DML(Data Manipulation Language) - 데이터 조작어

정의된 데이터베이스에 입력된 레코드를 조회하거나 수정하거나 삭제하는 등의 역할을 하는 언어를 말한다.

종류	역할
SELECT	데이터 조회 기능
INSERT	데이터 추가 기능
UPDATE	데이터 수정 기능
DELETE	데이터 삭제 기능

• 데이터베이스 사용자가 응용 프로그램이나 질의어를 통하여 저장된 데이터를 실질적으로 처리하는데 사용하는 언어
• 데이터베이스 사용자와 데이터베이스 관리 시스템 간의 인터페이스를 제공한다.

DCL(Data Control Language) - 데이터 제어어

데이터베이스에 접근하거나 객체에 권한을 주는등의 역할을 하는 언어를 말한다.

종류	역할
GRANT	특정 데이터베이스 사용자에게 특정 작업에 대한 수행권한 부여 한다.
REVOKE	특정 데이터베이스 사용자에게 특정 작업에 대한 수행 권한을 박탈, 회수 한다.
COMMIT	트랜잭션의 작업 DB에 반영하는 역할을 한다.
ROLLBACK	트랜잭션의 작업을 취소 및 원래래 복구하는 역할을 한다.

• 데이터를 제어하는 언어이다.
• 데이터의 보안, 무결성, 회복, 병행 수행제어 등을 정의하는데 사용한다.

2. DB 설계

예제) PROD 데이터베이스

Session 테이블

컬럼	타입	속성
id	int	NOT NULL AUTO_INCREMENT PRIMARY KEY
user_id	int	NOT NULL
created	timestamp	NOT NULL DEFAULT CURRENT_TIMESTAMP
channel_id	int	NOT NULL FOREIGN KEY

Channel 테이블

컬럼	타입	속성
id	int	NOT NULL AUTO_INCREMENT PRIMARY KEY
channel	varchar(32)	NOT NULL

---

Select 문을 돌리기 전에

SHOW DATABASE; -- 현재 데이터베이스의 목록을 보여준다.
USE PROD; -- 해당 데이터 베이스를 사용한다.
SHOW TABLES; -- 해당 데이터베이스의 테이블 목록을 보여준다.   

3. Select

테이블에서 레코드들(혹은 레코드수)을 읽어오는데 사용한다.

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

select(1) - 기본 구조

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

select(2) - from

select * -- *는 모든 필드를 지칭하는 표현
from prod.session; -- 앞서 USE prod;를 수행했다면 FROM session도 사용 가능

• prod.session의 테이블에 해당하는 모든 레코드를 출력한다.

select(3) - from

SELECT id, user_id, channel_id -- 3개의 컬럼값들을 가져온다. 
FROM prod.session;

select(3) - from, LIMIT[갯 수 제한]

SELECT *
FROM prod.session
LIMIT 10; -- Limit 를 쓰게되면 N개 만큼만 출력한다.

select(4) - from, DISTINCT[중복제거]

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

select(5) - from, COUNT()[필드명 Count하기 위한 함수]

• COUNT(필드 이름 or 필드 index) 입력이 가능하다.

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

SELECT * -- channel 테이블의 모든 레코드들을 표시
FROM prod.channel;

-- 채널이 1, 5인 채널만을 묶어서 출력
select channel_id, COUNT(1) 
from prod.session
where channel_id = 5 or channel_id = 1
group by channel_id;

CASE WHEN

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

-- 채널 ID별로 갯수와 type을 정의하고 묶은다음 오름차순으로 출력
select channel_id, COUNT(1),
	CASE
		when channel_id in (1,5,6) then 'Social-Media'
        when channel_id in (2,4) then 'Search-Engine'
        else 'Something-Engine'
	END channel_type
from prod.session
group by channel_id
order by count(1); -- 1은 여기서 channel_id를 의미한다. + 오름차순

NULL

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

• 필드 지정시 값이 없는 경우 NULL로 지정 가능
  • 테이블 정의시 디폴트 값으로도 지정 가능
• 어떤 필드의 값이 NULL인지 아닌지 비교는 특수한 문법을 필요로 함
  • field1 is NULL 혹은 field1 is not NULL
• NULL이 사칙연산에 사용되면 그 결과는?
  • SELECT 0 + NULL, 0 - NULL, 0 * NULL, 0/NULL

COUNT 함수

헷갈리는 count 함수 예시

그룹(aggregate) 함수에 해당한다.

• 예시 테이블

value
NULL
1
1
0
0
4
3

SELECT COUNT(1) FROM prod.count_test;
-- 정답 : 7개 (null 값을 포함한다.)

SELECT COUNT(0) FROM prod.count_test;
-- 정답 : 7개 (null 값을 포함한다.)

SELECT COUNT(NULL) FROM prod.count_test;
-- 정답 : 0개 (null이면 count를 하지 않는다.)

SELECT COUNT(value) FROM prod.count_test;
-- 정답 : 6개 (필드값을 넣으면 null값을 제외하고 count 한다.)

SELECT COUNT(DISTINCT value) FROM prod.count_test;
-- 정답 : 4개 (중복 값 제외 + null값 제외)

WHERE

• IN
  • WHERE channel_id in (3, 4)
    • WHERE channel_id = 3 OR channel_id = 4
  • NOT IN
• LIKE
  • LIKE: 대소문자 구별 없이 문자열 매칭 기능을 제공해줌
  • WHERE channel LIKE 'G%' -> 'G*'
  • WHERE channel LIKE '%o%' -> 'o'
  • NOT LIKE
• BETWEEN
  • 날짜 범위에 사용 가능

-- where 조건
select count(1)
from prod.channel
where channel like 'G%'; -- G로 시작하는 channel만

select channel
from prod.channel
where channel not like '%o%'; -- o라는 문자가 포함하지 않는 channel

STRING 함수

• LEFT(str, N)
• REPLACE(str, exp1, exp2)
• UPPER(str)
• LOWER(str)
• LENGTH(str)
• LPAD, RPAD
• SUBSTRING
• CONCAT

-- String 함수를 써보자
select 
	length(channel), -- 길이
    upper(channel),  -- 대문자 변환
    lower(channel),  -- 소문자 변환
    left(channel,4), -- 왼쪽부터 4개만 출력
    rpad(channel,15,'-') --15자리 중 빈칸은 오른쪽에 - 로 채움
from prod.channel
order by length(channel); -- 길이로 오름차순   

DATE 함수

• 예시 1

-- Date 함수 써보기
select
	created, convert_tz(created, 'GMT','Asia/Seoul') seoul_time,
	year(created) y, quarter(created) q, month(created) m, monthname(created) mnn,
    date(created) d, hour(created) h, minute(created), second(created) s
from prod.session
limit 10;

• 예시 2

select created,
	datediff(now(), created) gap_in_days,
    date_add(created, interval 10 day) ten_days_after_created
from prod.session
limit 10;

• 예시 3

select str_to_date('11,8,2021', '%d,%m,%y');
-- 문자열 형 변환

Type Casting

• cast 함수를 사용
  • cast(category as float)
  • convert(expression, float)

SELECT cast('100.0' as float), convert('100.0', float);
-- 100, 100 을 출력한다. 

3. Group by - 그룹(aggregate) 함수

GROUP BY 절은 선택된 레코드의 집합을 필드의 값이나 표현식에 의해 그룹화한 결과 집합을 반환합니다.

즉, GROUP BY 절은 하나의 그룹을 하나의 레코드로 반환하므로, 결과 집합의 크기를 줄여주는 역할을 합니다.

• GROUP BY 절은 SELECT 문에서만 사용할 수 있다.
• ORDER BY와 GROUP BY
  • 포지션 번호 vs. 필드 이름
  • GROUP BY 1 == GROUP BY mon == GROUP BY LEFT(s.created, 7)

-- 월별 세션 수를 계산
select 
	left(created,7) mon,
    count(1) as session_count
from prod.session
group by 1 -- mon으로 group
order by 1; -- mon으로 ASC 


-- 가장 많이 사용된 채널은 무엇인가?
select 
	channel_id,
    count(1) as session_count,
    count(distinct user_id) as user_count
from prod.session
group by 1
order by 2 desc;

-- 가장 많은 세션을 만들어낸 사용자 ID는 무엇인가?
select
	user_id,
    count(1) as user_count
from prod.session
group by 1
order by 2 desc
limit 1;

-- 월별 유니크한 사용자 수
select
	left(created, 7) as mon,
    count(distinct user_id) as user_count
from prod.session
group by 1
order by 1;

-- session 아이디별, 사용자별 통계
select s.id, s.user_id, s.created, s.channel_id, c.channel
from prod.session s
join prod.channel c on c.id = s.channel_id;
GROUP BY 1, 2
ORDER BY 1 DESC, 2;

-- 월별, 채널별 유니크한 사용자 수
select
	left(s.created, 7) as mon,
    c.channel,
    count(distinct s.user_id) as mau
from prod.session s
join prod.channel c on c.id = s.channel_id
group by 1,2
order by 1 desc, 2;