SELECT ANIMAL_TYPE, COUNT(ANIMAL_TYPE)
FROM ANIMAL_INS
GROUP BY ANIMAL_TYPE
ORDER BY ANIMAL_TYPE;
"이때 고양이를 개보다 먼저 조회해주세요."
SELECT ANIMAL_ID,NAME,SEX_UPON_INTAKE
FROM ANIMAL_INS
WHERE NAME IN ('Lucy', 'Ella', 'Pickle', 'Rogan', 'Sabrina', 'Mitty');
SELECT DATETIME FROM ANIMAL_INS ORDER BY DATETIME LIMIT 1;
SELECT MIN(DATETIME) FROM ANIMAL_INS
SELECT NAME, COUNT(NAME) AS cnt
FROM ANIMAL_INS
GROUP BY NAME HAVING cnt > 1
ORDER BY NAME;
SELECT ANIMAL_ID, NAME FROM ANIMAL_INS WHERE
(NAME LIKE '%el%' OR '%El%' OR '%eL%' OR '%EL%')
AND ANIMAL_TYPE = "Dog"
ORDER BY NAME;
SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
WHERE LOWER(NAME) LIKE "%el%"
AND ANIMAL_TYPE = "Dog"
ORDER BY NAME
SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
WHERE UPPER(NAME) LIKE "%EL%"
AND ANIMAL_TYPE = "Dog"
ORDER BY NAME
기본적으로 MySQL 은 대소문자를 구분하지 않지만, 문자열에서는 대소문자를 구분함.
따라서 첫번째 처럼 일일히 다 해주거나, ( OR 묶을 때 괄호() 해줘야 하나봄 안그럼 안됨)
2,3 번째 처럼 열의 모든 문자를 UPPER 또는 LOWER 해준뒤 검색한다.
SELECT COUNT(*) FROM ANIMAL_INS ;
SELECT HOUR(DATETIME) AS HOUR , COUNT (DATETIME)
FROM ANIMAL_OUTS
GROUP BY HOUR
HAVING HOUR BETWEEN 9 AND 19
ORDER BY HOUR;
HOUR() 함수는 시간만 반환함.
SELECT DATE_FORMAT(DATETIME , '%H') AS HOUR , COUNT (DATETIME)
FROM ANIMAL_OUTS
GROUP BY HOUR
HAVING HOUR BETWEEN 9 AND 19
ORDER BY HOUR;
DATE_FORMAT(시간값, 원하는 포맷);
대소문자를 항상 주의하여 사용
%H 시간 24시간(00 ~ 23)
%h 시간 12시간(00 ~ 12)
%i 분 (00 ~ 59)
%s 초 (00 ~ 59)
SELECT ANIMAL_TYPE, IF(NAME IS NULL, 'No name',NAME),SEX_UPON_INTAKE
FROM ANIMAL_INS ;
SELECT ANIMAL_ID, NAME,
CASE
WHEN SEX_UPON_INTAKE LIKE '%Neutered%' THEN 'O'
WHEN SEX_UPON_INTAKE LIKE '%Spayed%' THEN 'O'
ELSE 'X'
END AS '중성화'
FROM ANIMAL_INS
ORDER BY ANIMAL_ID;
SELECT COUNT(DISTINCT NAME) FROM ANIMAL_INS ;
SELECT ANIMAL_ID, NAME,DATE_FORMAT(DATETIME , '%Y-%m-%d') AS '날짜'
FROM ANIMAL_INS ;
대소문자 주의
'%Y-%M-%D'로 작성하면 2016-January-3rd 이런식으로 뜸 대소문자에따라 표현이다르다.