정렬
1
2
3
4
5
6
7
| SELECT *
FROM ANIMAL_INS
ORDER BY ANIMAL_ID ASC
ORDER BY ANIMAL_ID DESC
ORDER BY NAME ASC, DATETIME DESC
|
조건
1
2
3
4
5
6
7
| 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")
|
제한
1
2
| 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
1
2
3
4
5
6
7
8
| 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
SELECT * FROM A a [JOIN 문] B b ON a.key=b.key 는 동일- 어디를 남기느냐에 따라 LEFT JOIN / FULL OUTER JOIN / RIGHT OUTER JOIN
- 겹치는 걸 안남기면, 안남는 부분의 키가 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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
| --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]'
|
