SELECTDISTINCTGROUP BYCASE WHENNULLCOUNT 함수 제대로 이해하기STRING FunctionsORDER BY타입 변환DATE ConversionSTR_TO_DATE, DATE_FORMATType CastingINSERT/UPDATE/DELETEINSERTDELETEUPDATEJOINJOIN 문법JOIN시 고려할 점LEFT JOINFULL JOINCROSS JOINSELF JOINDESCRIBE(MySql)exists vs limit 1 vs count(*)
SELECT
SELECT * FROM prod.session; // prod schema의 session테이블을. USE prod를 하면 FROM session; // 으로 가능함
DISTINCT
- 유일한 값들을 보기 위해서 사용하는 명령어
SELECT DISTINCT channel_id FROM prod.session;
GROUP BY
SELECT channel_id, COUNT(1) FROM prod.session GROUP BY 1;- GROUP BY 1은 SELECT 다음에 있는 필드 중 첫 번째 필드로 GROUP BY 하라는 의미
- COUNT(1) 은 하나의 그룹 안에 몇 개가 있는 지를 세는 것. COUNT 안에는 null 만 아니면 갯수 세는 동작 정상적으로 작동
CASE WHEN
- 필드 값의 변환을 위해 사용 가능
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 prod.session;
NULL
- 값이 존재하지 않음을 나타내는 상수. 0 혹은 “” 과 다름
- 필드 지정시 값이 없는 경우 NULL로 지정 가능
- 테이블 정의 시 디폴트 값으로도 지정 가능
- 어떤 필드의 값이 NULL인지 아닌지 비교는 특수한 문법을 필요로 함
- field1 is NULL 혹은 field1 is not NULL
COUNT 함수 제대로 이해하기
- COUNT 안의 parameter가 NULL 이 아닌 경우는 무조건 1씩 count하고, NULL이면 count를 안함
- COUNT(value) 컬럼의 이름으로 명시하면 NULL이 아닌 경우만 count를 함
- 7
- 7
- 0
- 6
- 4
STRING Functions
- LEFT(str, N)
- REPLACE(str, exp1, exp2)
- UPPER(str)
- LOWER(str)
- LENGTH(str)
- LPAD, RPAD - Left padding, Right padding
- SUBSTRING - 특정 String에서 일부분을 뽑는 것
- CONCAT - 몇개 string을 붙여서 새로운 필드를 만듦
ORDER BY
- 디폴트 순서는 오름차순
- NULL 값은 오름차순일 경우 처음에 위치
타입 변환
DATE Conversion
- NOW
- 타임존 관련 변환
- CONVERT_TZ(now(), ‘GMT’, ‘Asia/Seoul’)
- DATE, WEEK, MONTH, YEAR, HOUR, MINUTE, SECOND, QUARTER, MONTHNAME - Timestamp에서 특정 값만 빼오는 함수
- DATEDIFF - Timestamp 주고 그 사이에 몇 일 차이 나는지 확인
- DATE_ADD
SELECT created, DATEDIFF(now(), created) gap_in_days, DATE_ADD(created, INTERVAL 10 DAY) ten_days_after_created FROM session LIMIT 10;
STR_TO_DATE, DATE_FORMAT
SELECT STR_TO_DATE('01,5,2013', '%d,%m,%Y');
Type Casting
- cast 함수를 사용
- cast(category as float)
- convert(expression, float)
SELECT cast('100.0' as float), convert('100.0', float);INSERT/UPDATE/DELETE
INSERT
INSERT INTO <table_name>(<column1>, <column2>, <column3>) values (100, 1, '2020-01-01'); // 테이블 생성 시 컬럼 순서와 같은 순서대로 insert 시에는 column 이름 명시 안해도 됨 // VALUES 만 있으면 모든 칼럼에 대한 값이 다 존재해야 하고, 칼럼 지정해주면 지정한 칼럼에 대한 값만 // 있으면 됨
DELETE
- 조건을 기반으로 테이블에서 레코드 삭제 혹은 모든 레코드 삭제
- DELETE FROM vs. TRUNCATE
- 차이점을 이해하는 것이 중요
- TRUNCATE은 조건 없이 모든 레코드 삭제. 속도가 빠른 대신 트랜잭션 사용 시 롤 백 불가
UPDATE
- 조건을 기반으로 테이블에서 특정 레코드의 필드 값 수정
UPDATE prod.vital SET weight = 92 WHERE vital_id = 4;
JOIN
- 여러 테이블로 흩어진 데이터를 공통 필드를 가지고 통합
- 스타 스키마로 구성된 테이블들로 분산 되어 있던 정보를 통합하는 데 사용
JOIN 문법
SELECT A.*, B.* FROM raw_data.table1 A ___ JOIN raw_data.table2 B ON A.key1 = B.KEY1 and A.key2 = B.key2 WHERE A.ts >= '2019-01-01'; -- Mysql에서는 INNER(디폴트), LEFT, RIGHT, CROSS JOIN 가능함. FULL 조인을 지원하지 않음
- self join은 table 두 개가 똑같은 경우임
JOIN시 고려할 점
- 먼저 중복 레코드가 없고 Primary Key의 uniqueness가 보장됨을 체크
- 조인하는 테이블들 간의 관계를 명확하게 정의
- 어느 테이블을 베이스로 잡을지 (From에 사용할지) 결정해야 함
LEFT JOIN
- 왼쪽 테이블(Base)의 모든 레코드들을 리턴함
- 오른쪽 테이블의 필드는 왼쪽 레코드와 매칭되는 경우에만 채워진 상태로 리턴됨
FULL JOIN
- 왼쪽 테이블과 오른쪽 테이블의 모든 레코드들을 리턴함
- 매칭되는 경우에만 양쪽 테이블들의 모든 필드들이 채워진 상태로 리턴됨
SELECT * FROM vital v LEFT JOIN alert a ON v.vital_id = a.vital_id UNION -- 두개의 select를 합쳐서 새로운 결과를 내는데, 중복되는 record는 없애줌 -- UNION ALL 중복되는 record를 건드리지 않고 다 반환함 SELECT * FROM vital v RIGHT JOIN alert a ON v.vital_id = a.vital_id;
CROSS JOIN
- 왼쪽 테이블과 오른쪽 테이블의 모든 레코드들의 조합을 리턴함
SELF JOIN
- 동일한 테이블을 alias를 달리해서 자기 자신과 조인함
- join condition을 어떻게 쓰냐에 따라서 유용하게 사용할 수 있음
SELECT * FROM vital v1 JOIN vital v2 on v1.vital_id = v2.vital_id;
DESCRIBE(MySql)
- table에 대한 설명
exists vs limit 1 vs count(*)
- postgresql에서는 exists와 limit1이 동일한 방식으로 동작을 함
- 그러나 다른 DBMS에서는 다르게 동작이 가능하다는 점을 알고 있어야 함!
- count 쿼리는 exists와 limit1이 해당하는 조건에 대한 row가 하나만 있으면 바로 결과를 내는 것과 달리, 전체 데이터를 full scan 해야 하기 때문에 조회 속도가 상대적으로 더 느림