데이터 취업스쿨 스터디노트(9주차)

김다희·2024년 5월 29일

데이터취업스쿨 제로베이스

5.28

오늘 수강한 분량: SQL심화 2

SQL File

SQL File 실행1

파일을 실행할 데이터베이스에 접속
- sql_ws mysql -u root -p zerobase(db이름)
sql flie 실행
- source test01.sql

SQL File 실행2(외부에서 바로 실행)

sql_ws mysql -u root -p zerobase(db이름 < test02.sql

database Backup

zerobase batabase backup

mysqldump -u root -p zerobase > zerobase.sql
git

database restore

데이터베이스를 백업한 SQL File 을 실행하여 그 시점으로 복구하거나 이전 할 수 있다.
(SQL File 을 실행하는 방법과 동일함)

AWS RDS (database-1) 서비스가 사용 가능한 상태에서 접속

sql_ws mysql -h "엔드포인트" -P 3306 -u admin -p비밀번호

백업 할 데이터베이스로 이동: use zerobase
AWS RDS의 zerobase Database 복원

source zerobase.sql

Table Backup

Local Database에서 테이블 백업
- mysqldump -u root -p zerobase 테이블이름 > 테이블이름.sql

Table Restore

Table 을 백업한 SQL File 을 실행하여, 해당 테이블을 복구하거나 이전할 수 있다.
(SQL File 을 실행하는 방법과 동일함)
- source 테이블이름.sql
Tablke Schema Backup

5.29

오늘 수강한 분량: SQL 심화 3~5

Python with MySQL

0. 실행 전 준비(mysql 사용할 때 꼭 실행해야함!)

import mysql.connector

1. Create Connection

Local Database 연결

Local MySQL의 zerobase에 연결

AWS RDS(database-1) 연결

AWS RDS(database-1) zerobase에 연결

close Database

local.close()
remote.close()

2. Execute SQL

Query를 실행하기 위한 코드

실행 전 준비

import mysql.connector

Execute SQL 예제

테이블 생성
테이블 삭제

Execute SQL File 예제

test03.sql 생성 및 실행

Execute SQL File 예제2

SQL File 내에 Query가 여러개 존재하는 경우 multi = True 옵션 주기
test04.sql 생성

Fetch All 예제

celeb 테이블 조회(읽어올 양이 많은 경우 buffered=True)

Python with CSV

csv에 있는 데이터를 python으로 insert

police station.csv를 Pandas로 읽어오기

실행 전 준비

import pandas as ad

파일 실행

zerobase(데이터베이스)에 연결

insert문 만들기

데이터 입력

commit()은 database에 적용하기 위한 명령

결과 확인

검색결과를 pandas로 읽기

Python with CSV 예제

crime_station 테이블에 2020_crime.csv 를 입력하는 코드를 작성

AWS RDS에서 접속

2020_crime.csv 데이터(encoding="euc-kr") 읽어오기

insert 쿼리 작성

데이터를 crime_status 테이블에 insert

crime_status 테이블의 데이터 조회

조회한 결과를 pandas로 변환해서 확인

Primary key

• 테이블의 각 레코드를 식별
• 중복되지 않은 고유값을 포함
• NULL 값을 포함할 수 없음
• 테이블 당 하나의 기본키를 가짐

Primary key 생성

primary key (column 값)
ex1 하나의 칼럼을 기본키로 설정
ex2 여러개의 칼럼을 기본키로 설정

Primary key 삭제

alter table 테이블명 drop primary key;
ex

Primary key 추가

alter table 테이블명 add primary key (column값);
ex1
ex2

Foreign key

한 테이블을 다른 테이블과 연결해주는 역할이며,
참조되는 테이블의 항목은 그 테이블의 기본키 (혹은 단일값)

Foreign key 생성

constraint 이름 foreign key column명 references 테이블 명

ex1. constraint 이름 설정
ex2. constraint

이름 설정 안하는 경우(자동생성 됨)

자동 생성된 constraint 확인

show create table 테이블명

ex2. job 테이블 확인

Foreign key 삭제

alter table 테이블명 drop foreign key constraint명

ex1.

Foreign key 추가

alter table 테이블명 add foreign key (column명) references 테이블명(column명);

ex1.

5.31

오늘 수강한 분량: SQL 심화 6~8

Aggregate Functions(집계함수)

count

기본 형태

select count(*) from 테이블명;
select count(distinct column명) from 테이블명;

cf. distinct: 중복 제거해주는 역할

sum

기본 형태

select sum(더할 column명) from 테이블명 where conditions;
예제. 범죄 총 발생건수는?

avg

기본 형태

select avg(수가 들어있는 column명) from 테이블명 where conditions;

min, max

기본 형태

select min/max(column명) from 테이블명 where conditions;

group by

그룹화 하여 조회할 수 있는 명령어

예제

crime_status에서 경찰서별로 그룹화 하여 경찰서 이름 조회
- select police_station from crime_status group by police_station
경찰서별로 총 발생 범죄 건수 검색
경찰서별로 평균 범죄 발생건수와 평균 범죄 검거건수 검색

having

조건을 줄 수 있는 명령어
order by를 사용할 경우 having을 먼저 쓴 후에 order by를 써야 함!

예제

경찰서 별로 발생한 범죄 건수의 합이 4000건보다 큰 경우를 검색

Scalar Functions

UCASE / LCASE

영문을 대문자 / 소문자로 변경

select ucase(string);

MID

문자열 부분을 반환하는 함수

select MID(string, start_position, length);
• string : 원본 문자열
• start : 문자열 반환 시작 위치. (첫글자는 1, 마지막글자는 -1)
• length : 반환할 문자열 길이
-예제

LENGTH

문자열의 길이 반환

SELECT LENGTH(string);
문자가 없는 경우('') 길이는 0, 공백도 문자이므로 공백당 길이 1
NULL의 경우 길이가 없으므로 NULL

ROUND

지정한 자리에서 숫자 반올림

SELECT ROUND(number, decimals_place);
• number : 반올림할 대상
• decimals : 반올림할 소수점 위치 (Option)
반올림할 위치를 지정하지 않을 경우, 소수점 자리(0) 에서 반올림
소수점 첫번째 위치는 0
일 단위 위치는 -1

NOW

현재 날짜 및 시간 반환하는 함수

select now():

FORMAT

숫자를 천단위 콤마가 있는 형식으로 반환하는 함수

select format(number, decimal_place);
• number : 포맷을 적용할 문자 혹은 숫자
• decimals : 표시할 소수점 위치
소수점 표시 않을 경우 0

6.1

오늘 수강한 분량: SQL 과제1, SQL 심화 9~10

SQL Subquery

하나의 SQL 문 안에 포함되어 있는 또 다른 SQL 문을 말한다.
메인쿼리가 서브쿼리를 포함하는 종속적인 관계이다.
• 서브쿼리는 메인쿼리의 칼럼 사용 가능
• 메인쿼리는 서브쿼리의 칼럼 사용 불가

주의

• Subquery 는 괄호로 묶어서 사용
• 단일 행 혹은 복수 행 비교 연산자와 함께 사용 가능
• subquery 에서는 order by 를 사용X

종류

• 스카라 서브쿼리 (Scalar Subquery) - SELECT 절에 사용
• 인라인 뷰 (Inline View) - FROM 절에 사용
• 중첩 서브쿼리 (Nested Subquery) - WHERE 절에 사용

scalar subquery

결과는 하나의 column이어야 함
예제: 서울은평경찰서의 강도 검거 건수 & 서울시 경찰서 전체의 강도 검거 건수 평균 조회(한 표에 같이 나오게!)

Inline View

메인쿼리에서는 인라인 뷰에서 조회한 column만 사용 가능
예제: 경찰서 별로 가장 많이 발생한 범죄 건수와 범죄 유형 조회

Nested Subquery

• Single Row - 하나의 열을 검색하는 서브쿼리
• Multiple Row - 하나 이상의 행을 검색하는 서브쿼리
• Multiple Column - 하나 이상의 열을 검색하는 서브쿼리

single row subquery

서브쿼리가 비교연산자( =, >, >=, <, <=, <>, !=)와 사용되는 경우,
서브쿼리의 검색 결과는 한 개의 결과값을 가져야 한다. (두개 이상인 경우 에러)

multiple row - IN

예제: SNL에 출연한 영화배우를 조회

multiple row = EXISTS

예제: 범죄 검거 혹은 발생 건수가 2000건 보다 큰 경찰서 조회

multiple row = ANY

예제: SNL에 출연한 적이 있는 연예인 이름 조회

multiple row = ALL

서브쿼리 결과를 모두 만족하면(비교 연산자 사용)

multi column subquery

서브쿼리 내에 메인쿼리 컬럼이 같이 사용되는 경우
예제: 강동원과 성별, 소속사가 같은 연예인의 이름, 성별, 소속사를 조회

문제

oil_price 테이블에서 셀프주유의 평균가격과 SK에너지의 가장 비싼 가격을 Scalar Subquery 를 사용하여 조회
oil_price 테이블에서 상표별로 가장 비싼 가격과 상호를 Inline View 를 사용하여 조회

평균가격 보다 높은 주유소 상호와 가격을 Nested Subquery 를 사용하여 조회
3번에서 조회한 주유소에서 주유한 연예인의 이름과 주유소, 주유일을 Nested Subquery 를 사용하여 조회(refueling 테이블)
refueling 테이블과 oil_price 테이블에서 10만원 이상 주유한 연예인 이름, 상호, 상표, 주유 금액, 가격을 Inline View 를 사용하여 조회

6.2

오늘 수강한 분량: git

Git

https://github.com/

git 실행

찾기 -> git bash 오른쪽 마우스 -> 관리자 권한 실행
관리자 권한으로 안하면 프로그램 다운로드 안되는 것들도 있어서 꼭 이렇게 실행하기!
앞으로 터미널을 실행할 때는 git bash 로 실행하기

git bash

복사: ctrl + insert
붙여넣기: shift + insert

Local Repository

• Working Directory (작업공간) - 실제 소스 파일, 생성한 파일들이 존재
• Index (Stage) - Staging area (준비영역) 의 역할, git add 한 파일들이 존재
• HEAD - 최종 확정본, git commit 한 파일들이 존재