정렬

SELECT * 
FROM ANIMAL_INS 
ORDER BY ANIMAL_ID ASC
 
ORDER BY ANIMAL_ID DESC
 
ORDER BY NAME ASC, DATETIME DESC

조건

SELECT ANIMAL_ID, NAME 
FROM ANIMAL_INS
WHERE INTAKE_CONDITION="Sick"
 
WHERE INTAKE_CONDITION<>"Aged"
 
WHERE (A.SEX_UPON_INTAKE<>"Spayed Female" AND A.SEX_UPON_INTAKE<>"Neutered Male")AND (B.SEX_UPON_OUTCOME = "Spayed Female" OR B.SEX_UPON_OUTCOME = "Neutered Male")

제한

ORDER BY DATETIME ASC
LIMIT 1

SUM, MAX, MIN

SELECT MAX(DATETIME)
FROM ANIMAL_INS;
 
SELECT MIN(DATETIME)
FROM ANIMAL_INS;
 
SELECT COUNT(*)
FROM ANIMAL_INS;
 
SELECT COUNT(DISTINCT NAME)
FROM ANIMAL_INS;

그룹화

SELECT ANIMAL_TYPE, COUNT(*)
FROM ANIMAL_INS
GROUP BY ANIMAL_TYPE
ORDER BY ANIMAL_TYPE ASC
 
 
SELECT NAME, COUNT(NAME)
FROM ANIMAL_INS
GROUP BY NAME
HAVING COUNT(NAME)>=2
ORDER BY NAME ASC
 
SELECT HOUR(DATETIME) AS HOUR, COUNT(*)
FROM ANIMAL_OUTS
GROUP BY HOUR(DATETIME)
HAVING HOUR>=9 AND HOUR<20
ORDER BY HOUR ASC

IS NULL

SELECT ANIMAL_ID
FROM ANIMAL_INS
WHERE NAME IS NULL
 
WHERE NAME IS NOT NULL
 
SELECT ANIMAL_TYPE, IF(NAME IS NULL, "No name", NAME) AS NAME, SEX_UPON_INTAKE
FROM ANIMAL_INS

JOIN

OUTER-JOIN_더알아보기
  • SELECT * FROM A a [JOIN 문] B b ON a.key=b.key 는 동일
  • 어디를 남기느냐에 따라 LEFT JOIN / FULL OUTER JOIN / RIGHT OUTER JOIN
  • 겹치는 걸 안남기면, 안남는 부분의 키가 NULL
    • FULL 일 경우 둘 중 하나가 NULL

String, Date

WHERE A.NAME IN ("Lucy", "Ella", "Pickle", "Rogan", "Sabrina", "Mitty")
 
SELECT ANIMAL_ID, NAME, DATE_FORMAT(DATETIME, "%Y-%m-%d") as 날짜
 
SELECT DATE('2021-08-21 12:34:56') as 날짜, 
       MONTH('2021-08-21 12:34:56') as 월, 
       DAY('2021-08-21 12:34:56') as 일, 
       HOUR('2021-08-21 12:34:56') as 시간, 
       MINUTE('2021-08-21 12:34:56') as 분, 
       SECOND('2021-08-21 12:34:56') as 초;

LIKE

--A로 시작하는 문자를 찾기--
SELECT 컬럼명 FROM 테이블 WHERE 컬럼명 LIKE 'A%'
 
--A로 끝나는 문자 찾기--
SELECT 컬럼명 FROM 테이블 WHERE 컬럼명 LIKE '%A'
 
--A를 포함하는 문자 찾기--
SELECT 컬럼명 FROM 테이블 WHERE 컬럼명 LIKE '%A%'
 
--A로 시작하는 두글자 문자 찾기--
SELECT 컬럼명 FROM 테이블 WHERE 컬럼명 LIKE 'A_'
 
--첫번째 문자가 'A''가 아닌 모든 문자열 찾기--
SELECT 컬럼명 FROM 테이블 WHERE 컬럼명 LIKE '[^A]'
 
--첫번째 문자가 'A'또는'B'또는'C'인 문자열 찾기--
SELECT 컬럼명 FROM 테이블 WHERE 컬럼명 LIKE '[ABC]'
SELECT 컬럼명 FROM 테이블 WHERE 컬럼명 LIKE '[A-C]'