SQL Basic Cheat -2- ( 집계함수, 데이터 정렬하기, SQL 문자열 자르기)

SQL Basic Cheat Sheet 스터디 기록

 

 

3. 집계함수

COUNT, SUM, AVG, MAX/MIN

GROUP BY, HAVING

 

4. 정렬하기

ORDER BY

LIMIT

 

5. 문자열 함수

LEFT, RIGHT, SUBSTR(SUBSTRING)

 

6. SQL 소수점 처리

 

1) COUNT

SELECT COUNT(*) FROM sample;

>5

 

SELECT COUNT(Name)

FROM sample;

>4

 

SELECT COUNT(DISTINCT Name)

FROM sample;

>3

 

2) SUM

SELECT SUM(Visits)

FROM sample;

 

3) AVG

SELECT AVG(Visits)

FROM sample;

> (1+2+3+5)/4 = 2.75

 

SELECT SUM(Visits)/COUNT(*)

FROM sample;

> (1+2+3+5)/ 5 = 2.2

 

4) MAX/MIN

SELECT MAX(Visits)

FROM sample;

>5

SELECT MIN(Visits)

FROM sample;

>1

 

5) GROUP BY

SELECT CategoryID, SUM(Price)

FROM Products

GROUP BY CategoryID;

(그룹화의 기준이 되는 컬럼은 SELECT 구문에 반드시 적어주기)

 

6) HAVING

SELECT CategoryID, COUNT(*)

FROM Products

GROUP BY CategoryID

HAVING COUNT(*) <= 10

 

 

4. 데이터 순서 정렬하기

ORDER BY

오름차순 ASC ascending 

내림차순 DESC descending

 

SELECT * FROM Products

WHERE Price >= 20

ORDER BY price DESC;

(Price가 20이상인 값들 중 비싼 순으로 정렬)

 

LIMIT

SELECT * FROM Products

ORDER BY price DESC LIMIT 1;

(가장 비싼 물건 1개 출력)

 

 

5. SQL 문자열 자르기

SELECT LEFT(“20140323”, 4)

FROM sample;

>2014

 

SELECT RIGHT(“20140323”, 4)

FROM sample;

>0323

 

SELECT SUBSTR(“20140323”, 1, 4)

FROM sample;

>2014

 

SELECT SUBSTR(“20140323”, 3)

FROM sample;

>140323

 

6. SQL 소수점 처리

SELECT CEIL(5.5)

>6

 

SELECT FLOOR(5.5)

>5

 

SELECT ROUND(5.5569, 2)

>5.56