Chapter2. MySQL

2.1 MySQL이란?2.2 MySQL 기법 2.2.1 CREATE문 2.2.2 ALTER문 2.2.3 DROP문 2.2.4 INSERT문 2.2.5 UPDATE문 2.2.6 DELETE문 2.2.7 SELECT문 2.3 MySQL과 Jupyter Notebook 연동 2.3.1 pymysql 라이브러리 설치 및 연결 2.3.2 SQL문 실행

2.1 MySQL이란?

MySQL은 가장 많이 사용되고 있는 관계형 데이터베이스 관리 시스템(RDBMS: Relational DBMS)입니다. 오픈형 소스이기에 무료로 사용 가능하지만 상업적으로 사용할 때는 상업용 라이센스를 구입해야만 합니다.

표준 SQL 형식을 사용하며, 다중 사용자 및 다중 스레드를 지원하기에 큰 크기의 데이터 집합도 빠르고 효과적으로 처리할 수 있습니다. 또한, MySQL 응용 프로그램을 사용자의 용도에 맞게 수정하여 사용할 수 있습니다.

윈도우, 리눅스 등 다양한 운영체제에서 사용할 수 있으며, 특히 PHP와 함께 웹 개발에 자주 사용됩니다. C++, C언어, PHP, JAVA 등 많은 프로그래밍 언어를 위한 여러 API를 제공하고 있습니다.

2.2 MySQL 기법

2.2.1 CREATE문

CREATE 문을 사용하여 데이터베이스와 테이블을 만들 수 있습니다.

데이터베이스 생성

➤ CREATE DATABASE 데이터베이스명;


# SCHOOL이라는 데이터베이스를 생성합니다.
CREATE DATABASE SCHOOL;

테이블 생성

➤ CREATE TABLE 테이블(칼럼1 칼럼타입 ,칼럼2 칼럼타입 , …);


# STUDENT라는 테이블을 생성합니다.
# NAME, STUDENT_NUM, ADDRESS, BIRTH라는 칼럼을 가진 테이블을 생성합니다.
# VARCHAR()는 가변 길이 문자열, INT는 숫자형, DATE는 날짜형을 의미합니다.
CREATE TABLE STUDENT(NAME VARCHAR(20)
				             ,STUDENT_NUM INT
										 ,ADDRESS VARCHAR(50)
										 ,BIRTH DATE)

NAME

STUDENT_NUM

ADDRESS

BIRTH

2.2.2 ALTER문

ALTER 문을 사용하여 데이터베이스와 테이블의 내용을 수정할 수 있습니다.

데이터베이스 문자집합 변경

➤ ALTER DATABASE 데이터베이스명 CHARACTER SET = 문자집합이름;


# SCHOOL이라는 데이터베이스의 문자집합을 UTF8로 변경합니다.
ALTER DATABASE SCHOOL CAHRACTER SET = utf8

테이블 내 칼럼 타입 변경

➤ ALTER TABLE 테이블명 ADD 칼럼명 칼럼타입;


# STUDENT 테이블에 INT타입의 GRADE 칼럼을 추가합니다.
ALTER TABLE STUDENT 
		        ADD GRADE INT;

NAME

STUDENT_NUM

ADDRESS

BIRTH

GRADE

테이블 내 칼럼 삭제

➤ ALTER TABLE 테이블명 DROP 칼럼명;


# STUDENT 테이블의 STUDENT_NUM 칼럼을 삭제합니다.
ALTER TABLE STUDENT 
		        DROP STUDENT_NUM;

NAME

ADDRESS

BIRTH

GRADE

테이블 내 칼럼 타입 변경

➤ ALTER TABLE 테이블명 MODIFY COLUMN 칼럼명 칼럼타입;


# STUDENT 테이블의 NAME칼럼의 타입을 VARCHAR(20)에서 VARCHAR(10)으로 변경합니다.
ALTER TABLE SUTDENT 
            MODIFY COLUMN NAME VARCHAR(10);

2.2.3 DROP문

DROP 문을 사용하여 데이터베이스와 테이블을 삭제할 수 있습니다.

데이터베이스 삭제

➤ DROP DATABASE 데이터베이스명;


# SCHOOL 데이터베이스를 삭제합니다
DROP DATABASE SCHOOL;

테이블 삭제

➤ DROP TABLE 테이블명;


# STUDENT라는 테이블을 삭제합니다
DROP TABLE STUDENT;

2.2.4 INSERT문

INSERT 문을 사용하여 테이블에 새로운 레코드를 추가할 수 있습니다.

테이블에 값 추가(원하는 칼럼만 입력가능)

➤ INSERT INTO 테이블명 (칼러명1, 칼럼명2, 칼럼명3, …) VALUES (값1, 값2, 값3, …);


# STUDENT 테이블 칼럼 NAME, ADDRESS에 '성춘향','부산'을 추가합니다.
INSERT INTO STUDENT (NAME, ADDRESS)
VALUES ('성춘향', '부산');

NAME	ADDRESS	BIRTH	GRADE
성춘향	부산	ㅤ	ㅤ

테이블에 값 추가(모든 칼럼에 값 추가)

➤ INSERT INTO 테이블명 VALUES (값1, 값2, 값3, …);


'''
STUDENT 테이블에 '성춘향','부산','1999.11.12',2를 추가합니다.
테이블명 뒤에 칼럼명을 작성하지 않은 경우에는 모든 칼럼 값들을 작성해줘야 합니다.
테이블명 뒤에 칼럼명을 작성하지 않은 경우에는 칼럼 순서를 맞춰서 작성해줘야 합니다.
'''
INSERT INTO STUDENT
VALUES ('홍길동', '광주', '1999.11.12', 2);

NAME	ADDRESS	BIRTH	GRADE
성춘향	부산	ㅤ	ㅤ
홍길동	광주	1999.11.12	2

2.2.5 UPDATE문

UPDATE 문을 사용하여 레코드의 내용을 수정할 수 있습니다.

테이블내 값 변경

➤ UPDATE 테이블명 SET 칼러명1 = 값1, 칼럼명2 = 값2, … WHERE 칼럼명 = 조건값;


'''
STUDENT 테이블에서 NAME이 성춘향인 값을 찾아
ADDRESS, BIRTH, GRADE를 대전, 1999.11.12, 3으로 변경합니다.
'''
UPDATE STUDENT
       SET ADDRESS = '대전',
           BIRTH = '1999.11.12',
           GRADE = 3
       WHERE NAME = '성춘향';

NAME	ADDRESS	BIRTH	GRADE
성춘향	부산	ㅤ	ㅤ
홍길동	광주	1999.11.12	2

⇓

NAME	ADDRESS	BIRTH	GRADE
성춘향	대전	1995.10.28	3
홍길동	광주	1999.11.12	2

2.2.6 DELETE문

DELETE 문을 사용하여 테이블의 레코드를 삭제할 수 있습니다.

테이블 내 원하는 값 삭제

➤ DELETE FROM 테이블명 WHERE 칼럼명 = 조건값;


# STUDENT 테이블에서 NAME이 홍길동인 레코드를 삭제합니다.
DELETE FROM STUDENT
            WHERE NAME = '홍길동';

NAME	ADDRESS	BIRTH	GRADE
성춘향	대전	1995.10.28	3
홍길동	광주	1999.11.12	2

⇓

NAME	ADDRESS	BIRTH	GRADE
성춘향	대전	1995.10.28	3

2.2.7 SELECT문

SELECT 문을 사용하여 테이블의 레코드를 선택할 수 있습니다.

테이블 내 모든 값 선택

➤ SELECT * FROM 테이블명;


# STUDENT 테이블에 포함된 모든 값들을 가져옵니다.
SELECT *
       FROM STUDENT;

NAME	STUDENT_NUM	ADDRESS	BIRTH
홍길동	1	서울	1999.11.14
김영희	2	수원	2000.02.14
김철수	3	인천	1998.04.05

테이블 내 원하는 칼럼 값 선택

➤ SELECT 칼럼1, 칼럼2… FROM 테이블명;


# STUDENT 테이블에서 NAME, ADDRESS 칼럼 값들을 가져옵니다.
SELECT NAME, ADDRESS
       FROM STUDENT;

NAME	ADDRESS
홍길동	서울
김영희	수원
김철수	인천

테이블 내 원하는 값 선택

➤ SELECT * FROM 테이블명 WHERE 칼럼명 = 조건값;


# STUDENT 테이블에서 NAME칼럼의 값이 '홍길동'인 모든 칼럼의 값을 가져옵니다.
SELECT *
       FROM STUDENT
       WHERE NAME = '홍길동';

NAME	STUDENT_NUM	ADDRESS	BIRTH
홍길동	1	서울	1999.11.14

2.3 MySQL과 Jupyter Notebook 연동

2.3.1 pymysql 라이브러리 설치 및 연결

pymysql 라이브러리 설치 및 import

주피터 노트북에서 새 파일을 열고, 아래의 코드를 실행하여 pymysql 라이브러리를 설치합니다.


# pymysql 라이브러리 설치
!pip install pymysql

pymysql 라이브러리를 import합니다.


import pymysql

Connection 객체 생성

연결에 필요한 호스트명, 포트번호, 유저명, 비밀번호를 변수에 저장합니다.


# 본인의 username, password를 변수에 저장합니다.
host = '127.0.0.1'  # 로컬 호스트
port = 3307
username = 'root'
password = '0000'

pymysql 라이브러리의 Connection 객체는 파라미터로 받아 가는 값들을 통해 파이썬과 SQL을 연결시켜 줍니다. 따라서 아래와 같이 Connection 객체를 생성하여 파이썬과 MySQL을 연결합니다.


# MySQL Connection
conn = pymysql.connect(host = host,
                       port = port,
                       user = username,
                       password = password,
                       charset='utf8')

Connection 객체를 가지고 있는 conn 변수를 출력해 봄으로 Connection 객체가 정상적으로 생성된 것을 확인할 수 있습니다.


conn

➡️

결과


<pymysql.connections.Connection at 0x21895704e80>

Cusor 객체 생성

SQL 문을 실행하기 위해서는 DB에서 SQL 문을 실행하고 실행된 결과를 돌려받는 통로의 역할을 하는 Cusor 객체가 필요합니다. 앞에서 연결한 Connection 객체를 사용하여 커서 객체를 생성하였습니다.


cur = conn.cursor()

Cursor 객체를 가지고 있는 cur 변수를 출력해 봄으로 Cursor 객체가 정상적으로 생성된 것을 확인할 수 있습니다.

cur

➡️

결과


<pymysql.cursors.Cursor at 0x218957178e0>

2.3.2 SQL문 실행

앞에서 Cusor 객체까지 정상적으로 생성된 것을 확인하였으니, 실제로 SQL 문을 실행해 보겠습니다.

DB 생성

데이터를 저장할 DB를 먼저 생성해 보겠습니다. DB 이름은 ‘test_db’로 명명하였습니다.


# SQL문 실행 - DB 생성
cur.execute('CREATE DATABASE test_db')
conn.commit()

아래의 SQL 문을 실행하여 ‘test_db’ DB가 정상적으로 생성된 것을 확인할 수 있습니다. Connection 객체와 커서 객체를 close를 사용하여 종료합니다.


# SQL문 실행 - DB 리스트 확인
cur.execute('SHOW DATABASES')
for data in cur:
	print(data)

# Connection, 커서 객체 종료
cur.close()
conn.close()

➡️

결과


('information_schema',)
('mysql',)
('performance_schema',)
('sakila',)
('sys',)
('test_db',)
('world',)

테이블 생성

‘test_db’ DB 내에 테이블을 생성해 보겠습니다. 먼저 MySQL DB에 root 계정으로 접속하는 Connection 객체를 생성합니다.


# MySQL DB Connection
conn = pymysql.connect(host = host,
                       port = port,
                       user = username,
                       password = password,
                       charset='utf8',
                       database='test_db')

아래는 ‘student’ 테이블을 생성하는 SQL 문입니다. 이름(name), 주소(address), 생일(birth), 성적 등급(grade), 총 4개의 Column을 가진 테이블을 생성하겠습니다.


# 테이블 생성 SQL문
sql = '''create table student (
            name varchar(5) primary key,
            address varchar(5),
            birth varchar(11),
            grade int)'''

Connection 객체의 커서(cusor) 객체를 생성하고, ‘test_db’ DB에 ‘student’ 테이블을 생성하는 SQL 문을 실행합니다. 실행한 SQL 문을 실제 DB에 적용하기 위해 commit을 실행합니다. with 절을 이용하였기에 커서는 코드를 모두 실행한 후에 자동으로 close 됩니다.


# SQL문 실행 - 테이블 생성
with conn.cursor() as cur:
    cur.execute(sql)
    conn.commit()

위의 방식과 같이 ‘test_db’ DB 내의 테이블 리스트를 출력하였습니다. ‘student’ 테이블이 정상적으로 생성된 것을 확인할 수 있습니다.


# SQL문 실행 - 테이블 리스트 확인
with conn.cursor() as cur:
    cur.execute('SHOW TABLES')
    conn.commit()

    for data in cur:
        print(data)

➡️

결과


('student',)

데이터 저장

생성된 ‘student’ 테이블에 데이터를 저장해 보겠습니다.

INSERT 문을 사용하여 데이터를 삽입하는 SQL 문을 작성하였습니다. 커서 객체를 생성하고 SQL 문을 실행시켜줍니다.


# 데이터 저장 SQL문
sql = '''insert into student
         values ('성춘향', '대전', '1995.10.28', 3)'''

# SQL문 실행 - 데이터 저장
with conn.cursor() as cur:
    cur.execute(sql)
    conn.commit()

위와 같은 방식으로 데이터를 하나 더 저장해 보았습니다.


# 데이터 저장 SQL문
sql = '''insert into student
         values ('홍길동', '광주', '1999.11.12', 2)'''

# SQL문 실행 - 데이터 저장
with conn.cursor() as cur:
    cur.execute(sql)
    conn.commit()

저장된 데이터 확인

위에서 저장한 데이터를 확인해 보겠습니다.

커서 객체를 생성하여 SELECT 문을 실행시키고, 받아온 데이터를 출력해 줍니다. 데이터들이 정상적으로 저장된 것을 확인할 수 있습니다.


# query
sql = '''select * from student'''

# SQL문 실행 - 데이터 확인
with conn.cursor() as cur:
    cur.execute(sql)
    conn.commit()
    
    for data in cur:
        print(data)

➡️

결과


('성춘향', '대전', '1995.10.28', 3)
('홍길동', '광주', '1999.11.12', 2)

모든 작업을 마쳤으니 ‘test_db’ DB에 연결시켜둔 Connection 객체를 close 합니다.


# MySQL DB Connection 종료
conn.close()

📢

지금까지 활용 편에서 자주 사용할 SQL 문을 중점으로 실행 방법을 살펴보았습니다. 사용하지 않은 ALTER 문, DROP 문, UPDATE 문, DELETE 문 또한 pymysql 라이브러리와 execute 메서드를 사용하여 동일한 방식으로 실행할 수 있습니다.