[SQL] MySQL 설치 및 기초 기능 활용해보기

데이터 분석가에게 SQL이란?

아직 데이터 분석가는 아니지만 데이터 분석에 있어 SQL 이 얼마나 중요한지는 알고 있다. 데이터 분석을 위해서는 데이터가 있어야 한다. 이러한 데이터는 그냥 주어지는 것이 아니고 데이터 수집에서부터 정제/가공 단계가 이루어져야 분석을 위해 사용할 수 있는 데이터가 된다.

이때 데이터를 정제/가공하는 부분을 데이터 분석가가 진행한다. 물론 데이터 수집부터 진행할 수도 있으며, 정확한 업무의 범위는 회사마다 상황별로 다르기 때문에 딱 잘라서 말하기는 어렵다.

데이터를 정제/가공하는 부분은 SQL로 진행할 수 밖에 없다. 데이터 분석을 위해서는 회사의 DB에서 분석 목적에 맞는 raw data를 꺼내서 데이터를 정제하고 가공하여 적절한 데이터 형태로 만드는 것이 필요하다. 물론 엑셀로도 진행할 수 있겠지만, 엑셀은 대용량 데이터를 다루기 적합하지 않다. 대용량 데이터를 다루기 위해서는 SQL이라는 도구를 활용해야 한다.

SQL 역량

데이터 분석가 채용공고를 보면 SQL 역량은 필수적으로 요구한다. 데이터 분석가로 취업하기 위해서는 SQL 역량을 갖춰야 한다. 1월 중순부터 SQL 관련 강의를 듣긴 하지만 미리 예습할 겸 SQL 공부를 시작했다.

어떤 SQL이 좋을까?

SQL 공부를 하기 위해서는 SQL을 먼저 설치해야한다. 그런데 SQL의 종류가 아주 많다. 정확히말하면 SQL을 이용한 DB 관리 시스템이 많은 것이다.

다양한 DB 관리 시스템

그럼 어떤 SQL을 선택해야 할까? 몇 가지 기준을 세워봤다.

1. 관계형 데이터베이스여야 한다. 정형 데이터를 다룰 거니 당연히 관계형이여야 한다.
2. 오픈 소스여야 한다.
3. 사람들이 많이 사용. 그래야 질문과 답변이 많아서 참고하기 좋다.

검색을 해보던 중 SQL 랭킹 사이트가 있어서 확인해보았다.

출처: https://db-engines.com/

상위 10위 SQL이다. 오픈소스 관계형 데이터베이스 중 MySQL과 PostgreSQL이 있다. 여기서 매기는 Score는 아래의 사진과 같은 기준으로 매겨진다고 하는데 얼마나 많이 사용되고 채용시장에서 얼마나 언급되는지 등의 기준을 통해 점수를 매긴다. 따라서, 점수가 더 높은 MySQL이 적합하다고 생각했다.

출처: https://db-engines.com/

또한, AWS에서 MySQL과 PostgreSQL의 차이를 비교하는 문서를 확인했다. 해당 문서에서 선택 기준을 제시해줬다.

출처 :https://aws.amazon.com/ko/compare/the-difference-between-mysql-vs-postgresql/

MySQL이 초보자에게 더 적합하다고 하여 확실히 MySQL로 선택할 수 있었다.

MySQL 설치하기 (Mac Version)

수정 (24.03.11)

버전 선택하는 법 -이 글에서 다운 받는 MySQL Server의 버전은 MySQL Workbench와 호환되지 않아 오류가 발생합니다. MySQL Workbench를 사용하실 분은 아래 버전으로 다운받으시면 됩니다.
MySQL Server : 8.0.31
MySQL Workbench : 8.0.36

더 많은 내용은 아래 문서를 참고하시기 바랍니다. 
[SQL] MySQL 워크벤치 Incompatible server version 해결

구글에 MySQL Mac Download를 검색하면 아래와 같이 화면이 뜬다.
MySQL Community Version을 다운로드 받으면 된다. 8.2.0 Innovation 버전으로 다운로드 받았다.

MySQL Community version 다운로드 화면

아래 사진과 같은 화면에서 아래 빨간 네모로 표시해둔 "No thanks,~~"를 클릭하면 다운로드가 시작된다.

MySQL Community version 다운로드 화면

다운로드가 완료되면, MySQL 설치를 진행하면 된다. 이 때 중간에 password를 설정하라는 화면이 나온다. root유저로 접속할 때 사용하는 패스워드다.

MySQL Community version 패스워드 설정

설치가 완료되면 터미널에 아래 명령어를 입력한다.

/usr/local/mysql/bin/mysql -uroot -p

해당 명령어를 입력하면, 아래 사진과 같이 패스워드를 입력창이 뜨고, 패스워드를 입력하면 MySQL이 실행된다.

터미널에서 mysql 실행

MySQL 구조와 기초 기능 활용

생활코딩 유튜브의 MySQL 강의와 구글링을 참고해서 MySQL 기초기능을 활용해보았다.

SQL에는 CRUD라는 기본기능 4가지가 있다. CRUD는 Create, Read, Update, Delete의 약자이다. 데이터를 입력하고 읽고 수정하고 삭제하는 가장 기본적인 기능을 의미한다. 데이터 분석가는 이 네가지 중 Read를 가장 많이 사용한다고 한다. 데이터베이스에서 분석에 필요한 데이터를 읽어오기 위해서이다. 하지만, 이것도 일단 DB가 있어야 가능한 것이니 간단하게나마 Create 기능을 활용하여 DB를 생성해보았다.

MySQL의 구조

데이터베이스 서버 구조[참고: 생활코딩 MySQL 강의]

MySQL은 Table, DB, DB Server로 이루어져있다.

Table은 하나의 엑셀 시트를 생각하면 좋다. 행과 열로 이루어진 데이터 구조이다. R이나 판다스에서 사용하는 데이터프레임을 생각해도 좋다. 정형데이터를 저장하기 위한 구조이다.

여러 개의 Table을 모아둔 것을 Database이다. Schema(스키마)라고도 부른다. join등의 함수를 이용하여 Table을 연결하여 사용한다.

Database를 모아둔 것을 Database Server이라고 한다.

Database 생성

CREATE Database

# name이라는 이름의 Database 생성
CREATE Database name;

USE

테이블을 생성하기 전에 사용할 데이터베이스를 선택해야 한다.

# name Database 선택
USE Database_name;

SHOW

# Database Server 내 모든 데이터 베이스 조회
SHOW Databases; 
SHOW Schemas;

SELECT

# 현재 Database 확인, 사용하고 있는 Database가 없는 경우 Null을 Return함.
# MySQL의 Built-in function을 사용.
SELECT DATABASE();

Table 생성하기

CREATE TABLE

# table_name이라는 테이블 생성
CREATE TABLE table_name (
    column1 data_type1
    column2 data_type2
    ...
)

Data Type

Table을 생성할 때, 각각의 column에 data type을 지정해야 한다. data type은 SQL마다 다르기 때문에 관련 문서를 참고해서 사용하면 된다.

참고: MySQL 데이터 타입 문서
https://dev.mysql.com/doc/refman/8.0/en/data-types.html

Data 저장 및 확인

Insert

# 데이터 저장
INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...)

SELECT

# Table 내 모든 데이터 확인
SELECT * FROM table_name

Python을 이용하여 csv데이터 SQL에 저장하기

아래 사진의 데이터를 저장했다. 매일 나의 취침-기상시각을 기록한 데이터이다.

취침-기상시간 데이터

1. 데이터 타입 지정

컬럼명	영문명	Type
ID	ID	Date (YYYY-MM-DD)
날짜	Date	TIME (HH:mm:ss)
취침시각	SleepTime	TIME (HH:mm:ss)
기상시각	WakeTime	TIME (HH:mm:ss)
목표기상시각	TargetWakeTime	TIME (HH:mm:ss)
알람기상여부	WakeAlarm	BOOL
재취침횟수	CountResleep	TINYINT
전날 활동	PrevDayActivity	VARCHAR(255)
하루 컨디션(전날)	FeelDay	TINYINT
기상 컨디션	FeelWake	TINYINT
침대 누운시각	BedTime	TIME (HH:mm:ss)

2. Table 생성

CREATE TABLE Sleep ( 
    ID int, 
    Date Date, 
    SleepTime Time, 
    WakeTime Time, 
    TargetWakeTime Time, 
    WakeAlarm Bool, 
    CountResleep Tinyint, 
    PrevDayActivity Varchar(255), 
    FeelDay Tinyint, 
    FeelWake Tinyint, 
    Bedtime Time
);

Varchar타입은 Size 지정이 필수적이다. 지정하지 않으면 에러가 발생한다.

3. 라이브러리 설치

아래 링크의 라이브러리를 설치한다.

https://pypi.org/project/mysql-connector-python/

또는 pip을 이용하여 설치한다.

pip install mysql-connector-python

4. 데이터 불러오기 및 전처리

# "-"으로 표시 된 결측치 None으로 처리 -> SQL 값에 결측치로 처리하기 위해서
for c in data.columns:
    data.loc[(data[c] == "-") | data[c].isnull() , c] = None

# 알람기상여부 type -> Boolean으로 바꾸기
data.loc[data["알람 기상 여부"] == "O", "알람 기상 여부"] = True
data.loc[data["알람 기상 여부"] == "X", "알람 기상 여부"] = False

# 날짜 포맷 변경 (YYYY-MM-DD)
data["날짜"] = data["날짜"].apply(lambda x : "-".join([v[:-1].zfill(2) for v in x.split(" ")[:-1]]))

# 시간 포맷 변경 (HH:mm:ss)
time_columns = ["취침", "기상", "목표기상(알람시각)","침대 누운 시각"]
for c in time_columns:
    data[c] = data[c].apply(lambda x : ":".join([v[:-1].zfill(2) for v in x.split(" ")]) if x is not None else x)

5. 서버 연결 및 데이터 저장

# mysql-python connector 라이브러리 
import mysql.connector

# 데이터베이스 서버 내 데이터베이스에 연결
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  password="your_password",
  database = "your_database"
)

mycursor = mydb.cursor()

# SQL 쿼리 작성
sql = "Insert Into sleep (Date, SleepTime, WakeTime, TargetWakeTime, WakeAlarm, CountResleep, PrevDayActivity, FeelDay, FeelWake, Bedtime) Values (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
val = values.tolist()

# 저장
mycursor.executemany(sql, val)

mydb.commit()

6. 확인

SELECT 함수를 이용해서 확인한다. 터미널이나 파이썬을 이용하여 확인한다.

데이터 확인

참고문헌

[이고잉] https://www.youtube.com/playlist?list=PLuHgQVnccGMCgrP_9HL3dAcvdt8qOZxjW
[W3Schools] https://www.w3schools.com/sql/
[Stackoverflow] https://stackoverflow.com/questions/6741042/mysql-alter-the-columns-of-my-primary-key