SQL QUERY 정리

sql 쿼리 지문 정리

GROUP_BY

SELECT COUNT(column) FROM table_name GROUP_BY column

특정 시간대별 정렬

SELECT date_format(DATETIME, '%H') AS `DATE`, COUNT(date_format(DATETIME, '%H')) FROM ANIMAL_OUTS
 WHERE date_format(DATETIME, '%H') BETWEEN '09' AND '19'
 GROUP BY date_format(DATETIME, '%H')
 ORDER BY date_format(DATETIME, '%H') ASC

 SELECT HOUR(datetime) AS HOUR, COUNT(HOUR(datetime)) AS COUNT
 FROM animal_outs
 GROUP BY HOUR(datetime)
 HAVING HOUR >= 9 AND HOUR <= 19
 ORDER BY HOUR

등록되는 시간인 7~19시를 제외한 시간 또한 표시하여 정렬

set @hour := -1;
SELECT (@hour := @hour + 1) as 'HOUR',
       (SELECT COUNT(*) FROM ANIMAL_OUTS WHERE HOUR(DATETIME) = @hour) AS 'COUNT'
FROM ANIMAL_OUTS
WHERE @hour < 23;

NULL CHECK

SELECT ANIMAL_ID FROM ANIMAL_INS WHERE NAME IS NOT NULL

IFNULL()

SELECT ANIMAL_TYPE, IFNULL(NAME, 'No name'), SEX_UPON_INTAKE FROM ANIMAL_INS

INNER JOIN

SELECT
테이블별칭.조회할칼럼,
테이블별칭.조회할칼럼
FROM 기준테이블 별칭
INNER JOIN 조인테이블 별칭 ON 기준테이블별칭.기준키 = 조인테이블별칭.기준키....

한쪽 집합에 값이 없는 것만 출력

SELECT O.ANIMAL_ID, O.NAME
FROM ANIMAL_INS I
RIGHT JOIN ANIMAL_OUTS O ON I.ANIMAL_ID = O.ANIMAL_ID
WHERE I.ANIMAL_ID IS NULL

SELECT I.NAME, I.DATETIME
FROM ANIMAL_INS I
LEFT JOIN ANIMAL_OUTS O ON I.ANIMAL_ID = O.ANIMAL_ID
WHERE O.DATETIME IS NULL
ORDER BY I.DATETIME ASC
LIMIT 3;

Refernce

• https://www.w3schools.com/sql/
• https://programmers.co.kr/learn/challenges?tab=sql_practice_kit