[MySQL] Code Snippets

최댓값 구하기 2가지 방식

SELECT DATETIME AS 시간 FROM ANIMAL_INS
ORDER BY DATETIME DESC
LIMIT 1

SELECT MAX(DATETIME) AS '시간'
FROM ANIMAL_INS

 

최솟값 구하기

SELECT DATETIME AS 시간 FROM ANIMAL_INS
ORDER BY DATETIME ASC
LIMIT 1

 

COUNT

SELECT COUNT(*) FROM ANIMAL_INS

# COUNT(*)으로 행의 개수 구하기

 

DUPLICATE (DISTINCT 또는 GROUPBY로 사용 가능)

SELECT COUNT(*) FROM ANIMAL_INS

# COUNT(*)으로 행의 개수 구하기

 

GROUP BY ORDER BY

SELECT ANIMAL_TYPE, COUNT(ANIMAL_TYPE) AS count 
FROM ANIMAL_INS
GROUP BY ANIMAL_TYPE
ORDER BY ANIMAL_TYPE

SELECT NAME, COUNT(NAME) AS COUNT
FROM ANIMAL_INS
WHERE NAME IS NOT NULL
GROUP BY NAME
HAVING COUNT(NAME) > 1
ORDER BY NAME ASC;

 

GROUP BY WHERE

SELECT HOUR(DATETIME) AS HOUR, COUNT(DATETIME) AS COUNT
FROM ANIMAL_OUTS
WHERE HOUR(DATETIME) >= 9 AND HOUR(DATETIME) <= 19
GROUP BY HOUR(DATETIME)
ORDER BY HOUR(DATETIME)

 

WITH (으로 가상 테이블 만들기) link

WITH TBL AS
(
	SELECT '철수' AS NAME, 20 AS AGE
	UNION ALL
	SELECT NAME, AGE
	  FROM TB1
)

SELECT NAME, AGE FROM TBL;

 

SET

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

 

WITH RECURSIVE (재귀로 가상의 table 생성하기) link

WITH RECURSIVE CTE AS(
SELECT 1 AS h
UNION ALL
SELECT h+1 FROM CTE WHERE h<5)

SELECT * FROM CTE

# output
# h
# 1
# 2
# 3
# 4
# 5

 

NULL 값 처리하기

SELECT ANIMAL_TYPE, IFNULL(NAME, "No name") AS NAME, SEX_UPON_INTAKE
FROM ANIMAL_INS
ORDER BY ANIMAL_ID

 

JOIN

# ANIMAL OUT 기준으로 LEFT JOIN ON ANIMAL_ID
SELECT A.ANIMAL_ID, A.NAME
FROM ANIMAL_OUTS A LEFT JOIN ANIMAL_INS B ON A.ANIMAL_ID = B.ANIMAL_ID
WHERE B.ANIMAL_ID IS NULL # A에만 있는 데이터만
ORDER BY A.ANIMAL_ID

SELECT A.ANIMAL_ID, A.NAME
FROM ANIMAL_OUTS A JOIN ANIMAL_INS B ON A.ANIMAL_ID = B.ANIMAL_ID
WHERE A.DATETIME < B.DATETIME
ORDER BY B.DATETIME

SELECT B.NAME, B.DATETIME
FROM ANIMAL_OUTS A RIGHT JOIN ANIMAL_INS B ON A.ANIMAL_ID = B.ANIMAL_ID
WHERE A.ANIMAL_ID IS NULL
ORDER BY B.DATETIME
LIMIT 3

SELECT NAME, DATETIME FROM ANIMAL_INS
WHERE ANIMAL_ID NOT IN (SELECT ANIMAL_ID FROM ANIMAL_OUTS)
ORDER BY DATETIME ASC LIMIT 3;

SELECT a.ANIMAL_ID, a.ANIMAL_TYPE, a.NAME
FROM ANIMAL_INS a, ANIMAL_OUTS b
WHERE a.ANIMAL_ID = b.ANIMAL_ID -- 같은동물
AND a.SEX_UPON_INTAKE != b.SEX_UPON_OUTCOME -- 전후가 다름
ORDER BY ANIMAL_ID

 

Condition (OR)

SELECT ANIMAL_ID ,NAME ,SEX_UPON_INTAKE
FROM ANIMAL_INS
WHERE NAME="Lucy" OR NAME="Ella" OR NAME="Pickle" OR NAME="Rogan" OR NAME="Sabrina" OR NAME="Mitty"
ORDER BY ANIMAL_ID

 

문자열 일치 (like: 특정문자포함 검색)

SELECT ANIMAL_ID,NAME
FROM ANIMAL_INS
WHERE NAME LIKE "%el%" AND ANIMAL_TYPE="Dog"
ORDER BY NAME

https://lollolzkk.tistory.com/44

 

CASE + WHEN (다중조건문)

SELECT ANIMAL_ID, NAME, 
    CASE 
	WHEN SEX_UPON_INTAKE LIKE "%Neutered%" 
	    OR SEX_UPON_INTAKE LIKE "%Spayed%" 
	THEN "O" 
	ELSE 'X' 
    END AS "중성화" 
FROM ANIMAL_INS

output:

 

DATE COLUMN DIFF 계산하기 문제링크

SELECT INS.ANIMAL_ID, INS.NAME
FROM ANIMAL_INS INS, ANIMAL_OUTS OUTS
WHERE INS.ANIMAL_ID = OUTS.ANIMAL_ID
ORDER BY OUTS.DATETIME-INS.DATETIME DESC
LIMIT 2

 

DATETIME TO DATE

SELECT ANIMAL_ID, NAME, DATE_FORMAT(DATETIME, '%Y-%m-%d') AS 날짜 
FROM ANIMAL_INS

SELECT NAME
FROM ANIMAL_INS
ORDER BY DATETIME
LIMIT 1