- 데이터베이스를 구축하고
- 데이터의 구조 및 이름을 변경
-- csv자료를 불러와서 자료를 세팅하는 과정
-- cctv범죄검거율.csv 자료(서울지역만임)를 cctv 데이터베이스에
-- Population_in_Seoul.csv 자료를 불러와서 population 데이터베이스에 할당
# 데이터 베이스 생성 및 세팅
-- [실행] 생성되어 있는 데이터 베이스 목록을 살펴봅니다.
SHOW DATABASES;
-- [실행] exam 데이터베이스를 생성하고 데이터 베이스로 이동
-- 아래 작업 실행후 왼쪽 목록 리스트에서 f5키를 눌러 새로고침합니다.
CREATE DATABASE exam ;
USE exam;
SHOW DATABASES;
-- [실행] exam 데이터베이스의 데이터 테이블 확인
-- 빈자료임.
SHOW TABLES;
-- [실행] 범죄cctv 검거율을 테이블을 제작하고,
-- 테이블을 확인
CREATE TABLE cctv
(기관명 VARCHAR(5) PRIMARY KEY,
소계 INT,
2013년도 INT,
2014년도 INT,
2015년도 INT,
2016년도 int
);
SHOW TABLES; -- exam의 테이블 목록 보기
DESCRIBE cctv; -- 테이블 구조 보기
SELECT * FROM cctv;
-- [실행] cctv범죄검거율.csv 를 불러와서 cctv 테이블에 넣기
LOAD DATA INFILE 'c:/data/CCTV범죄검거율.csv'
INTO TABLE CCTV
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;
SELECT * FROM cctv LIMIT 2;
SELECT COUNT(*) 건수 FROM cctv; -- 25건 나오면 됨
-- 필드에 널값이 있는 자료 모두 찾기
SELECT *
FROM cctv
WHERE 기관명 IS NULL
OR 소계 IS NULL
OR 2013년도 IS NULL
OR 2014년도 IS NULL
OR 2015년도 IS NULL
OR 2016년도 IS NULL;
-- 테이블 생성
CREATE TABLE population (
gu VARCHAR(50) PRIMARY KEY,
poptot INT,
Koreans INT,
foreigners INT,
elderly_people INT
);
DESCRIBE population;
DESCRIBE cctv;
INSERT population INTO
-- [실행] 데이터셋 제작 (구이름, 총인구수, 한국인, 외국인, 고령자)
INSERT INTO population (gu, poptot, Koreans, foreigners, elderly_people)
VALUES
('종로구', 162820, 153589, 9231, 25425),
('중구', 133240, 124312, 8928, 20764),
('성동구', 311244, 303380, 7864, 39997),
('광진구', 372164, 357211, 14953, 42214),
('동대문구', 369496, 354079, 15417, 54173),
('중랑구', 414503, 409882, 4621, 56774),
('성북구', 461260, 449773, 11487, 64692),
('강북구', 330192, 326686, 3506, 54813),
('노원구', 569384, 565565, 3819, 71941),
('은평구', 494388, 489943, 4445, 72334),
('서대문구', 327163, 314982, 12181, 48161),
('양천구', 479978, 475949, 4029, 52975),
('강서구', 603772, 597248, 6524, 72548),
('금천구', 255082, 236353, 18729, 32970),
('영등포구', 402985, 368072, 34913, 52413),
('관악구', 525515, 507203, 18312, 68082),
('서초구', 450310, 445994, 4316, 51733),
('강남구', 570500, 565550, 4950, 63167),
('송파구', 667483, 660584, 6899, 72506),
('강동구', 453233, 449019, 4214, 54622);
SELECT * FROM population;
SELECT COUNT(*) 건수 FROM population; -- 20건 나오면 됨# 만든 데이터 베이스 만져보기 1
USE exam; -- 사용할 db 명시
-------------------------------------------------------------------------------------------
-- ●[미션1] cctv에서 소계가 가장 작은 하위 3개의 지역만 출력,
-------------------------------------------------------------------------------------------
SELECT * FROM cctv
order by 소계 ASC
LIMIT 3;
-- ASC는 오름차순임! 즉 밑으로 내려갈 수록 작은 숫자가 나옴!
-- 그래서 위에서부터 출력하면 작은 숫자가 나옴!
# 2
-------------------------------------------------------------------------------------------
-- ●[미션2] 최근 3년간 CCTV가 이전에 대비해서 많이 증가한 상위 top3 데이터는?
-- 계산데이터 생성 : 2014년이후==[year2014]+[year2015]+[year2016]
-- 증가=([year2014]+[year2015년]+[year2016년])-[year2013]
-- 증감율,백분율 계산은 과제2에서 시행합니다.
-------------------------------------------------------------------------------------------
SELECT 기관명, 소계, (2014년도 + 2015년도 + 2016년도) AS total_2014_2016,
(2014년도 + 2015년도 + 2016년도) - 2013년도 AS increase
FROM cctv
ORDER BY increase DESC
LIMIT 3;
# 3
-- ------------------------------------------------------------------------
-- [미션2-1] 에서 %로 top순위를 볼수 있음 - 모든 문제 풀고 해보아도 됨
-- 코드 어려움, gpt에게 'mysql에서 limit에 percent' 로 10% 하였을때 같은 결과 나옴
-- -----------------------------------------------------------------------
# 3-1
-- ------------------------------------------------------------------------
-- [미션3-1] 구별, 전체인구수가 200000 보다크면(초과) 이면 '대도시' 그외에는 '중소도시'
-- -----------------------------------------------------------------------
SELECT gu, poptot, Koreans, foreigners, elderly_people,
CASE
WHEN poptot > 200000 THEN '대도시'
ELSE '중소도시'
END AS increase_level
FROM population
# 3-2
-- ------------------------------------------------------------------------
-- [미션3-2] 3-1의 결과물을 이용하여서 도시규모별 '인구집계'(총 인구수) 출력
-- -----------------------------------------------------------------------
ALTER TABLE population
ADD COLUMN increase_level VARCHAR(50) -- 대도시, 중소도시 넣을 필드값 생성
ALTER TABLE population
DROP COLUMN increase_level; -- 필드값 삭제하기
-- 대도시, 중소도시 넣어주기
UPDATE population
SET increase_level = CASE
WHEN poptot > 200000 THEN '대도시'
ELSE '중소도시' -- 원하는 기본값을 설정할 수 있습니다. NULL은 예시로 사용한 것입니다.
END;
-- 정답
SELECT
CASE
WHEN poptot > 200000 THEN '대도시'
ELSE '중소도시'
END AS 도시규모,
SUM(poptot) AS 인구집계
FROM population
GROUP BY
CASE
WHEN poptot > 200000 THEN '대도시'
ELSE '중소도시'
END;
# 3-3
-- ------------------------------------------------------------------------
-- [미션3-3] 3-2의 결과물을 이용하여서 '인구집계' 의 큰값순으로 순위 출력
-- -----------------------------------------------------------------------
SELECT
CASE
WHEN poptot > 200000 THEN '대도시'
ELSE '중소도시'
END AS 도시규모,
SUM(poptot) AS 인구집계,
ROW_NUMBER() OVER (ORDER BY 인구집계 DESC) AS 'RANK' -- 추가한 부분
FROM population
GROUP BY
CASE
WHEN poptot > 200000 THEN '대도시'
ELSE '중소도시'
END;
# 4
--------------------------------------------------------------------------------------------
-- [미션4] ccctv 데이터셋에서 gu 데이터의 중복여부를 확인
-- group by 명령으로, 중복된자료(즉 count)수가 >1 크면 출력
-- 아무자료도 나오지 않아야함
--------------------------------------------------------------------------------------------
SELECT gu, COUNT(*) AS 'cnt'
FROM population
GROUP BY gu -- 중복을 확인할 열
HAVING COUNT(*) > 1;
# 5
--------------------------------------------------------------------------------------------
-- ●[미션5] 'Cctv의 범죄자검거 데이터에 population 별 자료를 출력
--------------------------------------------------------------------------------------------
SELECT *
FROM cctv AS C
INNER JOIN population AS P
ON C.`기관명`=P.gu
# 6
-------------------------------------------------------------------------------------------
-- ●[미션6] cctv정보에 각 구별 인구수를 넣어서 db를 완성하려 한다.
-- 인구수정보를 제공하지 않는 gu 출력
--------------------------------------------------------------------------------------------
SELECT C.*, P.poptot
FROM cctv AS C
RIGHT JOIN population AS P -- 일치하지 않는 행만 선택
ON C.`기관명`=P.gu
SELECT c.기관명, p.*
FROM cctv c
left JOIN population p ON c.기관명 = p.gu
WHERE p.gu IS NULL;
-- 정리: 테이블을 합칠 때 c에는 있지만 p에는 없는 데이터들이 있음
-- 따라서 기관명은 c에만 있으니까 c로 나타내주고, 아무것도 없는 부분은 p니까 p로 나타내주기!
USE exam;
SELECT * FROM cctv
SELECT * FROM population -- 확인용으로 자주 사용한 코드들