% mysql -h database-1.dddddd-2.rdddddd -P 3306 -u zero -p zerobase
select * from police_station; select * from police_station limit 3;
select * from crime_status; select * from crime_status limit 3;
총 갯수를 계산해 주는 함수
COUNT 문법
SELECT COUNT(column) FROM tablename WHERE condition;
SELECT COUNT(*) FROM police_station;
SELECT COUNT(DISTINCT police_station) FROM crime_status;
SELECT COUNT(DISTINCT crime_type) FROM crime_status;
# COUNT 없이 해보자 SELECT DISTINCT crime_type FROM crime_status;
숫자 칼럼의 합계를 계산해주는 함수
SUM 문법
SELECT SUM(column) FROM tablename WHERE condition;
SELECT SUM(case_number) FROM crime_status WHERE status_type = '발생';
SELECT SUM(case_number) FROM crime_status WHERE status_type = '발생' AND crime_type LIKE '살인';
SELECT SUM(case_number) FROM crime_status WHERE status_type = '검거' AND police_station = '중부';
SELECT COUNT(DISTINCT name) FROM police_station;
SELECT DISTINCT name FROM police_station;
SELECT COUNT(DISTINCT status_type) FROM crime_status; SELECT COUNT(status_type) FROM crime_status; SELECT DISTINCT status_type FROM crime_status;
# 아래는 모든 범죄 발생건수 SELECT SUM(case_number) FROM crime_status WHERE police_station LIKE '종로' # 이렇게 해도 되고 LIKE OR police_station = '남대문'; # 이렇게 해도 됨 =
# 아래는 강도 범죄만 SELECT SUM(case_number) FROM crime_status WHERE crime_type = '강도' AND (police_station LIKE '종로' # 이렇게 해도 되고 LIKE OR police_station = '남대문'); # 이렇게 해도 됨 =
SELECT SUM(case_number) FROM crime_status WHERE crime_type = '폭력' AND status_type = '검거';
숫자 칼럼의 평균을 계산해주는 함수
AVG 문법
SELECT AVG(column) FROM tablename WHERE condition;
SELECT AVG(case_number) FROM crime_status WHERE crime_type LIKE '폭력' AND status_type = '검거';
SELECT police_station, crime_type, status_type, case_number FROM crime_status WHERE crime_type LIKE '폭력' AND status_type = '검거';
SELECT AVG(case_number) FROM crime_status WHERE police_station LIKE '중부' AND status_type LIKE '발생';
SELECT police_station, crime_type, status_type, case_number FROM crime_status WHERE police_station LIKE '중부' AND status_type LIKE '발생';
SELECT MIN(column) FROM tablename WHERE condition;
SELECT MIN(case_number) FROM crime_status WHERE crime_type LIKE '강도' AND status_type = '발생';
SELECT police_station, crime_type, status_type, case_number FROM crime_status WHERE crime_type LIKE '강도' AND status_type = '발생';
SELECT MIN(case_number) FROM crime_status WHERE police_station LIKE '중부' AND status_type LIKE '검거';
SELECT police_station, crime_type, status_type, case_number FROM crime_status WHERE police_station LIKE '중부' AND status_type LIKE '검거';
숫자 칼럼 중 가장 큰 값을 찾아주는 함수
MAX 문법
SELECT MAX(column) FROM tablename WHERE condition;
SELECT MAX(case_number) FROM crime_status WHERE crime_type LIKE '살인' AND status_type = '검거';
SELECT police_station, crime_type, status_type, case_number FROM crime_status WHERE crime_type LIKE '살인' AND status_type = '검거';
SELECT MAX(case_number) FROM crime_status WHERE police_station LIKE '강남' AND status_type LIKE '발생';
SELECT police_station, crime_type, status_type, case_number FROM crime_status WHERE police_station LIKE '강남' AND status_type LIKE '발생';
SELECT AVG(case_number) FROM crime_status WHERE crime_type LIKE '살인' AND status_type = '발생';
SELECT * FROM crime_status WHERE crime_type LIKE '살인' AND status_type = '발생';
SELECT AVG(case_number) FROM crime_status WHERE police_station LIKE '서초' AND status_type = '검거';
SELECT police_station, crime_type, status_type, case_number FROM crime_status WHERE police_station LIKE '서초' AND status_type = '검거';
SELECT AVG(case_number) FROM crime_status WHERE (police_station LIKE '구로' OR police_station LIKE '도봉') AND crime_type LIKE '살인' AND status_type = '검거';
SELECT * FROM crime_status WHERE (police_station LIKE '구로' OR police_station LIKE '도봉') AND crime_type LIKE '살인' AND status_type = '검거';
SELECT MIN(case_number) FROM crime_status WHERE police_station LIKE '광진' AND status_type = '검거';
SELECT * FROM crime_status WHERE police_station LIKE '광진' AND status_type = '검거';
SELECT MIN(case_number) FROM crime_status WHERE police_station LIKE '성북' AND status_type = '발생';
SELECT * FROM crime_status WHERE police_station LIKE '성북' AND status_type = '발생';
SELECT MAX(case_number) FROM crime_status WHERE police_station LIKE '영등포' AND status_type = '발생';
SELECT * FROM crime_status WHERE police_station LIKE '영등포' AND status_type = '발생';
SELECT MAX(case_number) FROM crime_status WHERE crime_type LIKE '절도' AND status_type = '검거';
SELECT * FROM crime_status WHERE crime_type LIKE '절도' AND status_type = '검거';
그룹화하여 데이터를 조회
GROUP BY 문법
- GROUP BY 와 ORDER BY에 쓰인 컬럼이 SELECT에 무조건 있어야 함.
SELECT column1, column2, ... FROM table WHERE condition GROUP BY column1, column2, ... ORDER BY column1, column2, ...;
SELECT police_station FROM crime_status GROUP BY police_station ORDER BY police_station LIMIT 5;
SELECT DISTINCT police_station FROM crime_status LIMIT 5;
SELECT police_station, sum(case_number) 발생건수 FROM crime_status WHERE status_type LIKE '발생' GROUP BY police_station ORDER BY 발생건수 DESC LIMIT 5;
SELECT police_station, avg(case_number) 평균검거건수 FROM crime_status WHERE status_type LIKE '검거' GROUP BY police_station ORDER BY 평균검거건수 DESC LIMIT 5;
SELECT police_station, status_type, avg(case_number) FROM crime_status GROUP BY police_station, status_type LIMIT 6;
조건에 집계함수가 포함되는 경우 WHERE 대신 HAVING 사용
HAVING 문법
- GROUP BY 와 ORDER BY에 쓰인 컬럼이 SELECT에 무조건 있어야 함.
SELECT column1, column2, ... FROM table WHERE condition GROUP BY column1, column2, ... HAVING condition (Aggregate Functions) ORDER BY column1, column2, ...;
SELECT police_station, sum(case_number) count FROM crime_status WHERE status_type LIKE '발생' GROUP BY police_station HAVING count > 4000;
SELECT sum(case_number) FROM crime_status WHERE status_type LIKE '발생' AND police_station LIKE '영등포';
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;
SELECT AVG(case_number) FROM crime_status WHERE (crime_type LIKE '폭력' OR crime_type LIKE '절도') AND status_type LIKE '발생' AND police_station LIKE '영등포';
SELECT * FROM crime_status WHERE (crime_type LIKE '폭력' OR crime_type LIKE '절도') AND status_type LIKE '발생' AND police_station LIKE '영등포';
SELECT police_station, avg(case_number) FROM crime_status WHERE status_type LIKE '발생' AND crime_type LIKE '절도' GROUP BY police_station ORDER BY avg(case_number) DESC LIMIT 10;
SELECT avg(case_number) FROM crime_status WHERE police_station LIKE '송파' AND status_type LIKE '발생' AND crime_type LIKE '절도';
SELECT police_station, MAX(case_number) FROM crime_status WHERE status_type LIKE '검거' GROUP BY police_station ORDER BY MAX(case_number) ASC LIMIT 5;
SELECT MAX(case_number) FROM crime_status WHERE status_type LIKE '검거' AND police_station LIKE '방배';
SELECT police_station, MIN(case_number) FROM crime_status WHERE status_type LIKE '검거' GROUP BY police_station HAVING MIN(case_number) > 4 ORDER BY MIN(case_number) DESC;
SELECT MIN(case_number) FROM crime_status WHERE status_type LIKE '검거' AND police_station LIKE '중랑';
SELECT police_station, AVG(case_number) FROM crime_status WHERE police_station LIKE '%대문' AND status_type LIKE '발생' GROUP BY police_station HAVING AVG(case_number) >= 500;
SELECT AVG(case_number) FROM crime_status WHERE police_station LIKE '서대문' AND status_type LIKE '발생';
SELECT * FROM crime_status WHERE police_station LIKE '동대문' AND status_type LIKE '발생';
위 글은 제로베이스 데이터 취업 스쿨의 강의자료를 참고하여 작성되었습니다.