Mir Yolo!

MySQL

LeeMir, 12 March 2021

MySQL

---

모든 정보를 조회 및 정렬

SELECT * FROM ANIMAL_INS ORDER BY ANIMAL_ID ASC

정렬은 여러 기준으로 가능

SELECT ANIMAL_ID, NAME, DATETIME FROM ANIMAL_INS ORDER By NAME ASC, DATETIME DESC;

데이터 필터링 - WHERE 이용

SELECT ANIMAL_ID, NAME FROM ANIMAL_INS WHERE INTAKE_CONDITION = "Sick";

데이터 개수 구하기

SELECT COUNT(ANIMAL_ID) FROM ANIMAL_INS;

중복을 제외한 개수 구하기

SELECT COUNT(DISTINCT NAME) FROM ANIMAL_INS;

NULL이 아닌 데이터 조회

SELECT ANIMAL_ID FROM ANIMAL_INS WHERE NAME is not NULL

NULL인 데이터 처리

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

GROUP BY로 데이터 묶은 후 조건 주기

SELECT NAME, COUNT(NAME) FROM ANIMAL_INS
GROUP By NAME
HAVING COUNT(NAME) >= 2
ORDER By NAME;

GROUP BY로 데이터 묶은 후 시간 형식 처리

select
date_format(DATETIME,'%H') as HOUR, count(DATETIME)
from
ANIMAL_OUTS
group by
HOUR
HAVING HOUR >= 09 and HOUR <=19
order by HOUR asc

WITH RECURSIVE를 이용해 가상 테이블 생성 후 JOIN

WITH RECURSIVE TIME AS(
    SELECT 0 AS h
    UNION ALL
    SELECT h+1 FROM TIME WHERE h<23)
SELECT TIME.h AS HOUR, IFNULL(B.C,0) AS COUNT FROM TIME
LEFT JOIN
(SELECT date_format(DATETIME,'%H') AS HOUR, COUNT(DATETIME) AS C
FROM ANIMAL_OUTS
GROUP By HOUR) AS B ON TIME.h = B.HOUR

서브 쿼리와 JION을 이용해 찾기

SELECT DISTINCT(A.CART_ID) FROM
(SELECT CART_ID FROM CART_PRODUCTS WHERE NAME = 'Milk') AS A
INNER JOIN (SELECT CART_ID FROM CART_PRODUCTS WHERE NAME = 'Yogurt') AS B
ON A.CART_ID = B.CART_ID;