SELECT [컬럼명 | 집계함수(컬럼명) | DISTINCT(컬럼명)]
FROM [테이블명]
[LEFT | RIGHT | FULL OUTER] JOIN [조인할 테이블명]
ON [조인할 기준 컬럼]
WHERE [조건절(그룹화 전)]
GROUP BY [그룹화할 컬럼명]
HAVING [조건절(그룹화 후)]
ORDER BY [정렬컬럼 ASC | DESC]
LIMIT [개수]
-- DISTINCT(컬럼명)은 해당 컬럼에서 중복 값 제거
-- AS는 별칭 정하기
-- 집계함수: SUM, AVG, MIN, MAX, COUNT 등
-- 조건절 관련
-- NULL 여부는 IS NULL 혹은 IS NOT NULL
- NAME IS NOT NULL
-- 집합 포함 여부는 IN (...)
- NAME IN ("Lucy", "Mitty")
-- 패턴 포함 여부는 LIKE
- NAME LIKE "%el%" → 앞뒤 글자 상관없이 중간에 el이 끼어있는 문자
- NAME LIKE "_el%" → 2, 3번째 문자 el 이후 상관 X (1번째는 꼭 다른 문자)
-- IF문 (6-3 참고)
IF(표현식, 참일때 리턴값, 거짓일때 리턴값)
-- CASE문 (6-3 참고)
CASE WHEN 표현식1 THEN 결과1
WHEN 표현식2 THEN 결과2
ELSE 결과 3
END
SELECT ANIMAL_TYPE, COUNT(*) AS count
FROM ANIMAL_INS
GROUP BY ANIMAL_TYPE
ORDER BY ANIMAL_TYPE
2. 동명 동물 수 찾기
SELECT NAME, COUNT(*) AS COUNT
FROM ANIMAL_INS
WHERE NAME IS NOT NULL
GROUP BY NAME
HAVING COUNT > 1
ORDER BY NAME ASC
3. 입양 시각 구하기 (1)
SELECT HOUR(DATETIME) AS HOUR, COUNT(*) AS COUNT
FROM ANIMAL_OUTS
GROUP BY HOUR
HAVING HOUR >= 9 AND HOUR < 20
ORDER BY HOUR ASC
4. 입양 시각 구하기 (2) → 프로그래머스 답 참고
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;
SELECT OUTS.ANIMAL_ID, OUTS.NAME
FROM ANIMAL_INS AS INS
RIGHT JOIN ANIMAL_OUTS AS OUTS
ON INS.ANIMAL_ID = OUTS.ANIMAL_ID
WHERE INS.ANIMAL_ID IS NULL
ORDER BY OUTS.ANIMAL_ID ASC
2. 있었는데요 없었습니다
SELECT INS.ANIMAL_ID, INS.NAME
FROM ANIMAL_INS AS INS
JOIN ANIMAL_OUTS AS OUTS
ON INS.ANIMAL_ID = OUTS.ANIMAL_ID
WHERE INS.DATETIME > OUTS.DATETIME
ORDER BY INS.DATETIME ASC
3. 오랜 기간 보호한 동물 (1)
SELECT INS.NAME, INS.DATETIME
FROM ANIMAL_INS AS INS
LEFT JOIN ANIMAL_OUTS AS OUTS
ON INS.ANIMAL_ID = OUTS.ANIMAL_ID
WHERE OUTS.DATETIME IS NULL
ORDER BY INS.DATETIME ASC
LIMIT 3
4. 보호소에서 중성화한 동물
SELECT INS.ANIMAL_ID, INS.ANIMAL_TYPE, INS.NAME
FROM ANIMAL_INS AS INS
JOIN ANIMAL_OUTS AS OUTS
ON INS.ANIMAL_ID = OUTS.ANIMAL_ID
WHERE INS.SEX_UPON_INTAKE LIKE "Intact%"
AND (
OUTS.SEX_UPON_OUTCOME LIKE "Spayed%"
OR OUTS.SEX_UPON_OUTCOME LIKE "Neutered%"
)
ORDER BY INS.ANIMAL_ID ASC
SELECT ANIMAL_ID, NAME, SEX_UPON_INTAKE
FROM ANIMAL_INS
WHERE NAME IN ("Lucy", "Ella", "Pickle", "Rogan", "Sabrina", "Mitty")
ORDER BY ANIMAL_ID ASC
2. 이름에 el이 들어가는 동물 찾기
SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
WHERE ANIMAL_TYPE = "Dog" AND NAME LIKE "%el%"
ORDER BY NAME ASC
3. 중성화 여부 파악하기
-- if 문: IF(표현식, 참일때 리턴값, 거짓일때 리턴값)
SELECT ANIMAL_ID, NAME,
IF (
SEX_UPON_INTAKE LIKE "Neutered%" OR SEX_UPON_INTAKE LIKE "Spayed%",
"O", "X"
) AS "중성화"
FROM ANIMAL_INS
ORDER BY ANIMAL_ID ASC
-- case문: CASE WHEN 표현식1 THEN 결과
-- WHEN 표현식2 THEN 결과
-- ELSE 결과 3
-- END
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
ORDER BY ANIMAL_ID ASC
4. 오랜 기간 보호한 동물
SELECT INS.ANIMAL_ID, INS.NAME
FROM ANIMAL_INS AS INS
JOIN ANIMAL_OUTS AS OUTS
ON INS.ANIMAL_ID = OUTS.ANIMAL_ID
ORDER BY (OUTS.DATETIME - INS.DATETIME) DESC
LIMIT 2
5. DATETIME 에서 DATE로 형변환
SELECT ANIMAL_ID, NAME, DATE_FORMAT(DATETIME, '%Y-%m-%d') AS "날짜"
FROM ANIMAL_INS
ORDER BY ANIMAL_ID ASC