실습 환경
- 실습할 데이터베이스로 이동 : AWS RDS (database-1) zerobase 에 접속
% mysql -h <엔드포인트> -P <port> -u <username> -p <databasename>- 실습할 데이터 확인 1 : police_station 데이터 확인 (31 rows)
SELECT * FROM police_station;
- 실습할 데이터 확인 2 : crime_status 데이터 확인 (310 rows)
SELECT * FROM crime_status;
Aggregate Functions (집계함수)
- 집계함수 : 여러 칼럼 혹은 테이블 전체 칼럼으로부터 하나의 결과값을 반환하는 함수
COUNT
-
COUNT : 총 갯수를 계산해 주는 함수
-
문법
SELECT COUNT(column)
FROM tablename
WHERE condition;- 예제 1 : police_station 테이블에서 데이터는 모두 몇 개?
SELECT COUNT(*)
FROM police_station;
- 예제 2 : crime_status 테이블에서 경찰서는 총 몇군데?
SELECT COUNT(DISTINCT police_station)
FROM crime_status;
- 예제 3 : crime_type 은 총 몇 가지?
SELECT COUNT(DISTINCT crime_type)
FROM crime_status;
SUM
-
SUM : 숫자 칼럼의 합계를 계산해주는 함수
-
문법
SELECT SUM(column)
FROM tablename
WHERE condition;- 예제 1 : 범죄 총 발생건수는?
SELECT SUM(case_number)
FROM crime_status
WHERE status_type='발생';
- 예제 2 : 살인의 총 발생건수는?
SELECT SUM(case_number)
FROM crime_status
WHERE status_type='발생' AND crime_type LIKE '살인';
- 예제 3 : 중부 경찰서에서 검거된 총 범죄 건수는?
SELECT SUM(case_number)
FROM crime_status
WHERE status_type='검거' AND police_station='중부';
AVG
-
AVG : 숫자 칼럼의 평균을 계산해주는 함수
-
문법
SELECT AVG(column)
FROM tablename
WHERE condition;- 예제 1-1 : 평균 폭력 검거 건수는?
SELECT AVG(case_number)
FROM crime_status
WHERE crime_type LIKE '폭력' AND status_type='검거';
- 예제 1-2 : 확인
SELECT police_station, crime_type, status_type, case_number
FROM crime_status
WHERE crime_type LIKE '폭력' AND status_type='검거';
- 예제 2-1 : 중부경찰서 범죄 평균 발생 건수는?
SELECT AVG(case_number)
FROM crime_status
WHERE police_station LIKE '중부' AND status_type LIKE '발생';
- 예제 2-2 : 확인
SELECT police_station, crime_type, status_type, case_number
FROM crime_status
WHERE police_station LIKE '중부' AND status_type LIKE '발생';
MIN
-
MIN : 숫자 칼럼 중 가장 작은 값을 찾아주는 함수
-
문법
SELECT MIN(column)
FROM tablename
WHERE condition;- 예제 1-1 : 강도 발생 건수가 가장 적은 경우 몇 건?
SELECT MIN(case_number)
FROM crime_status
WHERE crime_type LIKE '강도' AND status_type='발생';
- 예제 1-2 : 확인
SELECT police_station, crime_type, status_type, case_number
FROM crime_status
WHERE crime_type LIKE '강도' AND status_type='발생';
- 예제 2-1 : 중부경찰서에서 가장 낮은 검거 건수는?
SELECT MIN(case_number)
FROM crime_status
WHERE police_station LIKE '중부' AND status_type LIKE '검거';
- 예제 2-2 : 확인
SELECT police_station, crime_type, status_type, case_number
FROM crime_status
WHERE police_station LIKE '중부' AND status_type LIKE '검거';
MAX
-
MAX : 숫자 칼럼 중 가장 큰 값을 찾아주는 함수
-
문법
SELECT MAX(column)
FROM tablename
WHERE condition;- 예제 1-1 : 살인이 가장 많이 검거된 건수는?
SELECT MAX(case_number)
FROM crime_status
WHERE crime_type LIKE '살인' AND status_type LIKE '검거';
- 예제 1-2 : 확인
SELECT police_station, crime_type, status_type, case_number
FROM crime_status
WHERE crime_type LIKE '살인' AND status_type LIKE '검거';
- 예제 2-1 : 강남 경찰서에서 가장 많이 발생한 범죄 건수는?
SELECT MAX(case_number)
FROM crime_status
WHERE police_station LIKE '강남' AND status_type LIKE '발생';
- 예제 2-2 : 확인
SELECT police_station, crime_type, status_type, case_number
FROM crime_status
WHERE police_station LIKE '강남' AND status_type LIKE '발생';
GROUP BY
-
GROUP BY : 그룹화하여 데이터를 조회
-
문법
SELECT column1, column2, ...
FROM tablename
WHERE condition
GROUP BY column1, column2, ...
ORDER BY column1, column2, ...- 예제 1-1 : crime_status 에서 경찰서별로 그룹화 하여 경찰서 이름을 조회
SELECT police_station
FROM crime_status
GROUP BY police_station
ORDER BY police_station
LIMIT 5; 
- 예제 1-2 : 경찰서 종류를 검색 - DISTINCT 사용 시 ORDER BY는 사용할 수 없음
SELECT DISTINCT police_station
FROM crime_status
LIMIT 5;
- 예제 2 : 경찰서 별로 총 발생 범죄 건수를 검색
SELECT police_station, sum(case_number) 발생건수
FROM crime_status
WHERE status_type LIKE '발생'
GROUP BY police_station
ORDER BY 발생건수 DESC
LIMIT 5; 
- 예제 3 : 경찰서 별로 평균 범죄 검거 건수를 검색
SELECT police_station, avg(case_number) 평균검거건수
FROM crime_status
WHERE status_type LIKE '검거'
GROUP BY police_station
ORDER BY 평균검거건수 DESC
LIMIT 5; 
- 예제 4 : 경찰서 별 평균 범죄 발생건수와 평균 범죄 검거 건수를 검색
SELECT police_station, status_type, avg(case_number)
FROM crime_status
GROUP BY police_station, status_type
LIMIT 6; 
HAVING
-
HAVING : 조건에 집계함수가 포함되는 경우 WHERE 대신 HAVING 사용
-
문법
SELECT column1, column2, ...
FROM tablename
WHERE condition
GROUP BY column1, column2, ...
HAVING condition (Aggregate Functions)
ORDER BY column1, column2, ...- 예제 1-1 : 경찰서 별로 발생한 범죄 건수의 합이 4000 건보다 보다 큰 경우를 검색
SELECT police_station, sum(case_number) count
FROM crime_status
WHERE status_type LIKE '발생'
GROUP BY police_station
HAVING count > 4000;
- 예제 1-2 : 확인
SELECT sum(case_number)
FROM crime_status
WHERE status_type LIKE '발생' AND police_station LIKE '영등포';
- 예제 2-1 : 경찰서 별로 발생한 폭력과 절도의 범죄 건수 평균이 2000 이상인 경우를 검색
SELECT police_station, avg(case_number)
FROM crime_status
WHERE (crime_type LIKE '폭력' OR crime_type LIKE '절도')
AND status_type LIKE '발생'
GROUP BY police_station
HAVING avg(case_number) >= 2000;
- 예제 2-2 : 확인1
SELECT avg(case_number)
FROM crime_status
WHERE (crime_type LIKE '폭력' OR crime_type LIKE '절도')
AND status_type LIKE '발생' AND police_station LIKE '영등포';
- 예제 2-3 : 확인2
SELECT *
FROM crime_status
WHERE (crime_type LIKE '폭력' OR crime_type LIKE '절도')
AND status_type LIKE '발생' AND police_station LIKE '영등포';
