SQL
DISTINCT 필드명
SELECT COUNT(DISTINCT NAME)
FROM ANIMAL_INS
LIMIT 개수
SELECT NAME
FROM ANIMAL_INS
ORDER BY DATETIME
LIMIT 1
GROUP BY 필드명
SELECT ANIMAL_TYPE, COUNT(*)
FROM ANIMAL_INS
WHERE ANIMAL_TYPE IN("Cat", "Dog")
GROUP BY ANIMAL_TYPE
ORDER BY ANIMAL_TYPE
HOUR(DATETIME필드명)
SELECT HOUR(DATETIME) AS HOUR, COUNT(*)
FROM ANIMAL_OUTS
GROUP BY HOUR
HAVING HOUR >= 9 AND HOUR <= 19
ORDER BY HOUR
SET @변수명 = 값;
퀴리문 밖에서의 대입 연산자 =
쿼리문 안에서의 대입 연산자 :=
입양 시각 구하기(2)
-- 변수 선언
SET @HOUR_LIST = -1;
SELECT (@HOUR_LIST := @HOUR_LIST + 1) AS HOUR, (SELECT COUNT(*) FROM ANIMAL_OUTS WHERE HOUR(DATETIME) = @HOUR_LIST) AS COUNT
FROM ANIMAL_OUTS
WHERE @HOUR_LIST < 23
NULL값 조회
WHERE 필드 IS NULL WHERE 필드 IS NOT NULL
SELECT ANIMAL_ID
FROM ANIMAL_INS
WHERE NAME IS NULL
NULL 처리
IFNULL(필드명, '처리값')
SELECT ANIMAL_TYPE, IFNULL(NAME, 'No name'), SEX_UPON_INTAKE
FROM ANIMAL_INS
LEFT OUTER JOIN
FROM 테이블명1 AS 별칭1
LEFT JOIN 테이블명2 AS 별칭2
ON 별칭1.외래키 = 별칭2.외래키
SELECT O.ANIMAL_ID, O.NAME
FROM ANIMAL_OUTS AS O
LEFT JOIN ANIMAL_INS AS I
ON O.ANIMAL_ID = I.ANIMAL_ID
WHERE I.ANIMAL_ID IS NULL
ORDER BY O.ANIMAL_ID
OUTER JOIN
INNER JOIN
FROM 테이블명1 AS 별칭1, 테이블명2 AS 별칭2
WHERE 별칭1.외래키 = 별칭2.외래키 (AND 조건...)
SELECT O.ANIMAL_ID, O.NAME
FROM ANIMAL_OUTS AS O, ANIMAL_INS AS I
WHERE O.ANIMAL_ID = I.ANIMAL_ID AND O.DATETIME < I.DATETIME
ORDER BY I.DATETIME
%: 여러 문자
_: 한개의 문자
SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
WHERE NAME LIKE "%EL%" AND ANIMAL_TYPE = "Dog"
ORDER BY NAME
if(필드 조건1 OR 필드 조건2, 조건1결과값, 조건2결과값)
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
DATE_FORMAT(필드, '%Y-%m-%d')
SELECT ANIMAL_ID, NAME, DATE_FORMAT(DATETIME, '%Y-%m-%d') AS '날짜'
FROM ANIMAL_INS
ORDER BY ANIMAL_ID
반올림 ROUND()
내림 FLOOR()
올림 CEILING()
SELECT CO.CONTINENT, FLOOR(AVG(CI.POPULATION))
FROM COUNTRY AS CO, CITY AS CI
WHERE CO.CODE = CI.COUNTRYCODE
GROUP BY CO.CONTINENT;
SET @i = 21;
SELECT REPEAT("* ", @i:=@i-1)
FROM INFORMATION_SCHEMA.TABLES;
SUBSTR(필드명, 시작인덱스, 길이)
SELECT DISTINCT(CITY)
FROM STATION
WHERE SUBSTR(CITY, 1, 1) NOT IN('a', 'e', 'i', 'o', 'u');
LEFT(필드명, 인덱스)
RIGHT(필드명, 인덱스)
SELECT DISTINCT(CITY)
FROM STATION
WHERE LEFT(CITY, 1) NOT IN('a', 'e', 'i', 'o', 'u') OR
RIGHT(CITY, 1) NOT IN('a', 'e', 'i', 'o', 'u');