MySQL 기초 입문: 설치부터 Python 연동까지

관계형 데이터베이스의 이해

데이터베이스는 전원 차단 후에도 지속적으로 정보를 보존할 수 있는 저장 매체에 데이터를 안정적으로 기록하는 지속성(Persistence)을 제공합니다. 오랜 발전 과정을 거쳐 현재는 관계형 데이터베이스가 가장 널리 사용되고 있으며, 그 이전에는 계층형 및 망형 데이터베이스가 존재했습니다.

1970년 IBM 연구원 E.F. Codd는 논문 *"A Relational Model of Data for Large Shared Data Banks"* 에서 관계 모델을 제안하며 관계형 데이터베이스의 이론적 기반을 마련했습니다. 이후 정규화(Normalization)와 SQL(Structured Query Language)의 기반이 되는 수학적 체계가 확립되었습니다.

주요 특징

  • 이론 기반: 집합론과 관계 대수
  • 자료 표현: 행과 열로 구성된 2차원 테이블
  • 조작 언어: SQL (구조화된 질의 언어)

E-R 다이어그램 요소

  • 엔티티(Entity): 사각형으로 표시
  • 속성(Attribute): 타원으로 표시
  • 관계(Relationship): 마름모로 표시
  • 다중성(Cardinality): 1:1, 1:N, M:N

대표적인 RDBMS 제품군

  • Oracle: 엔터프라이즈급 시장에서 점유율 1위. 클라우드 환경을 위한 멀티테넌시 아키텍처 지원.
  • DB2: IBM이 개발한 Unix/Linux/Windows 기반 RDBMS. 초기 SQL 구현체 중 하나.
  • SQL Server: 마이크로소프트 제품. 중소기업에서 시작해 대규모 시스템까지 확장됨.
  • MySQL: 오픈소스 기반. GPL 라이선스 하에 자유롭게 수정 및 배포 가능. 고성능과 안정성으로 인기.
  • PostgreSQL: BSD 라이선스 기반의 강력한 오픈소스 DBMS. 확장성과 준수한 SQL 표준 준수가 특징.

MySQL 설치 및 초기 설정 (CentOS 기준)

리눅스 환경에서는 MariaDB라는 MySQL 분기 버전이 기본 패키지로 제공되며, 오라클 인수 이후의 폐쇄화 우려로 만들어졌습니다. 필요 시 아래 명령어로 설치 가능:

yum install mariadb mariadb-server

공식 MySQL을 설치하려면 먼저 기존 MariaDB를 제거해야 합니다:

yum list installed | grep mariadb | awk '{print $1}' | xargs yum erase -y

다음으로 MySQL 5.7 버전을 다운로드하고 RPM 패키지를 순차적으로 설치합니다:

wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.26-1.el7.x86_64.rpm-bundle.tar
tar -xvf mysql-5.7.26-1.el7.x86_64.rpm-bundle.tar

rpm -ivh mysql-community-common-5.7.26-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-5.7.26-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-5.7.26-1.el7.x86_64.rpm
rpm -ivh mysql-community-server-5.7.26-1.el7.x86_64.rpm

설치 확인:

rpm -qa | grep mysql

서비스 실행 및 초기 로그인

임시로 인증 없이 접속하기 위해 /etc/my.cnf 파일에 다음 항목 추가:

[mysqld]
skip-grant-tables
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

MySQL 서비스 시작 (systemd 기반 시스템):

systemctl start mysqld

root 계정으로 접속 후 비밀번호 변경:

mysql -u root

USE mysql;
UPDATE user SET authentication_string = PASSWORD('SecurePass!2024') WHERE user = 'root';
FLUSH PRIVILEGES;

설정 파일에서 skip-grant-tables 제거 후 재시작:

systemctl restart mysqld
mysql -u root -p

그래픽 도구 활용

  • MySQL Workbench: 공식 GUI 도구
  • Navicat for MySQL: 직관적인 인터페이스와 풍부한 기능
  • SQLyog: 경량화된 MySQL 관리 도구

기본 SQL 명령어

-- 서버 버전 확인
SELECT VERSION();

-- 모든 데이터베이스 목록 조회
SHOW DATABASES;

-- 특정 DB 선택
USE school;

-- 해당 DB 내 테이블 목록
SHOW TABLES;

-- 도움말 시스템 활용
? contents
? functions
? numeric functions
? round
? data types
? longblob

SQL 세부 문법 실습

DDL (데이터 정의 언어)

DROP DATABASE IF EXISTS school;
CREATE DATABASE school DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_bin;

USE school;

CREATE TABLE department (
    dept_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '학과 번호',
    dept_name VARCHAR(50) NOT NULL COMMENT '학과명',
    head_name VARCHAR(20) NOT NULL COMMENT '학과장',
    website VARCHAR(511) DEFAULT '' COMMENT '웹사이트 주소'
);

CREATE TABLE student (
    student_id INT PRIMARY KEY COMMENT '학번',
    name VARCHAR(20) NOT NULL,
    gender BOOLEAN DEFAULT TRUE COMMENT '남:1, 여:0',
    birth_date DATE NOT NULL,
    hometown VARCHAR(255),
    dept_id INT NOT NULL,
    FOREIGN KEY (dept_id) REFERENCES department(dept_id)
);

CREATE TABLE instructor (
    emp_id INT PRIMARY KEY COMMENT '교직원 번호',
    name VARCHAR(20) NOT NULL,
    position VARCHAR(10) DEFAULT '조교수',
    dept_id INT NOT NULL,
    FOREIGN KEY (dept_id) REFERENCES department(dept_id)
);

CREATE TABLE course (
    course_id INT PRIMARY KEY,
    title VARCHAR(50) NOT NULL,
    credits INT NOT NULL,
    instructor_id INT NOT NULL,
    FOREIGN KEY (instructor_id) REFERENCES instructor(emp_id)
);

CREATE TABLE enrollment (
    record_id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    enroll_time DATETIME,
    grade DECIMAL(4,1),
    FOREIGN KEY (student_id) REFERENCES student(student_id),
    FOREIGN KEY (course_id) REFERENCES course(course_id)
);

-- 학생별 동일 과목 중복 수강 방지 유니크 제약 조건
ALTER TABLE enrollment ADD CONSTRAINT uk_student_course UNIQUE (student_id, course_id);

DML (데이터 조작 언어)

INSERT INTO department (dept_name, head_name, website) VALUES
('컴퓨터공학과', '김철수', 'http://cs.example.edu'),
('외국어학부', '이영희', 'http://fl.example.edu'),
('경영대학', '박민수', 'http://biz.example.edu');

INSERT INTO student VALUES
(1001, '홍길동', 1, '1990-05-12', '서울', 1),
(1002, '이순신', 1, '1991-03-15', '부산', 1),
(1003, '신사임당', 0, '1989-11-20', '대구', 2);

DELETE FROM student WHERE student_id = 1003;

UPDATE student SET name = '홍길동개정', hometown = '인천' WHERE student_id = 1001;

DQL (데이터 조회 언어)

-- 전체 학생 정보
SELECT * FROM student;

-- 과목명과 학점만 추출
SELECT title AS 과목명, credits AS 학점 FROM course;

-- 성별 한글 변환 출력
SELECT name, CASE WHEN gender THEN '남' ELSE '여' END AS 성별 FROM student;

-- 여성 학생 필터링
SELECT name, birth_date FROM student WHERE gender = 0;

-- 1990년대 출생 학생 조회
SELECT name, birth_date FROM student WHERE birth_date BETWEEN '1990-01-01' AND '1999-12-31';

-- 이름에 '길' 포함된 학생
SELECT name FROM student WHERE name LIKE '%길%';

-- 주소 미기입 학생
SELECT name FROM student WHERE hometown IS NULL;

-- 중복 제거된 수강 날짜
SELECT DISTINCT enroll_time FROM enrollment;

-- 남학생 생년월일 기준 나이 정렬 (내림차순)
SELECT name, TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) AS age 
FROM student 
WHERE gender = 1 
ORDER BY age DESC;

-- 최연소 학생 생일
SELECT MAX(birth_date) FROM student;

-- 학과별 인원 수
SELECT dept_id, COUNT(*) AS count FROM student GROUP BY dept_id;

-- 특정 과목 평균 점수
SELECT AVG(grade) FROM enrollment WHERE course_id = 1111;

-- 학생별 평균 점수 (90점 이상만)
SELECT student_id, AVG(grade) AS avg_grade 
FROM enrollment 
GROUP BY student_id 
HAVING avg_grade >= 90;

-- 최고령 학생 이름 조회 (서브쿼리 사용)
SELECT name FROM student WHERE birth_date = (SELECT MIN(birth_date) FROM student);

-- 수강 과목 수가 2개 초과인 학생
SELECT name FROM student WHERE student_id IN (
    SELECT student_id FROM enrollment GROUP BY student_id HAVING COUNT(*) > 2
);

-- 학생 이름, 과목명, 성적 조인 조회
SELECT s.name, c.title, e.grade 
FROM student s
JOIN enrollment e ON s.student_id = e.student_id
JOIN course c ON e.course_id = c.course_id
WHERE e.grade IS NOT NULL
ORDER BY e.grade DESC
LIMIT 5 OFFSET 10;

DCL (데이터 제어 언어)

-- 새 사용자 생성
CREATE USER 'devuser'@'%' IDENTIFIED BY 'P@ssw0rd!';

-- 권한 부여
GRANT SELECT, INSERT ON school.* TO 'devuser'@'%';

-- 권한 회수
REVOKE INSERT ON school.* FROM 'devuser'@'%';

-- 변경 적용
FLUSH PRIVILEGES;

데이터베이스 설계 원칙

정규화(Normalization) 기준

  1. 제1정규형(1NF): 각 속성은 원자값이며 반복 그룹이 없어야 함.
  2. 제2정규형(2NF): 부분 함수 종속을 제거하고 기본 키에 완전히 종속되어야 함.
  3. 제3정규형(3NF): 이행적 종속 제거. 비기본 키 속성 간 독립성 유지.

데이터 무결성

  • 엔티티 무결성: 기본 키 또는 유니크 제약으로 중복 방지.
  • 참조 무결성: 외래 키(Foreign Key)로 관련 테이블 간 일관성 유지.
  • 도메인 무결성: 자료형, NOT NULL, DEFAULT, CHECK 제약으로 유효성 보장.

트랜잭션과 ACID 특성

  • 원자성(Atomicity): 모든 작업이 성공하거나 전체 취소.
  • 일관성(Consistency): 트랜잭션 전후 데이터베이스 상태의 일관성 유지.
  • 격리성(Isolation): 동시 실행 중인 트랜잭션이 서로 영향을 주지 않음.
  • 지속성(Durability): 커밋된 변경 사항은 영구 저장.

Python을 통한 MySQL 연동

PyMySQL은 순수 파이썬으로 작성된 MySQL 클라이언트 라이브러리로, 현재 가장 추천되는 접근 방식입니다.

설치

pip install PyMySQL

예제 스키마 준비

DROP DATABASE IF EXISTS company;
CREATE DATABASE company DEFAULT CHARSET utf8mb4;
USE company;

CREATE TABLE department (
    id INT PRIMARY KEY,
    name VARCHAR(10) NOT NULL,
    location VARCHAR(20) NOT NULL
);

CREATE TABLE employee (
    id INT PRIMARY KEY,
    name VARCHAR(20) NOT NULL,
    role VARCHAR(20) NOT NULL,
    manager_id INT,
    salary INT NOT NULL,
    allowance INT,
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES department(id)
);

파이썬 CRUD 예제

새 부서 추가:

import pymysql

def add_department():
    dept_id = int(input("부서 ID: "))
    name = input("부서명: ")
    loc = input("위치: ")

    conn = None
    try:
        conn = pymysql.connect(
            host='localhost',
            port=3306,
            database='company',
            user='root',
            password='your_password',
            charset='utf8mb4'
        )
        with conn.cursor() as cursor:
            result = cursor.execute(
                "INSERT INTO department VALUES (%s, %s, %s)",
                (dept_id, name, loc)
            )
        if result == 1:
            print("등록 완료")
        conn.commit()
    except Exception as e:
        print(f"오류 발생: {e}")
        conn.rollback()
    finally:
        if conn:
            conn.close()

if __name__ == "__main__":
    add_department()

부서 삭제 (자동 커밋 사용):

def delete_department():
    dept_id = int(input("삭제할 부서 ID: "))
    conn = pymysql.connect(
        host='localhost', port=3306,
        database='company', user='root',
        password='your_password', charset='utf8mb4',
        autocommit=True
    )
    try:
        with conn.cursor() as cursor:
            result = cursor.execute("DELETE FROM department WHERE id = %s", (dept_id,))
        print("삭제 성공" if result else "해당 부서 없음")
    finally:
        conn.close()

모든 부서 조회 (딕셔너리 커서 사용):

def list_departments():
    conn = pymysql.connect(
        host='localhost', port=3306,
        database='company', user='root',
        password='your_password', charset='utf8mb4'
    )
    try:
        with conn.cursor(pymysql.cursors.DictCursor) as cursor:
            cursor.execute("SELECT id AS no, name, location AS loc FROM department")
            rows = cursor.fetchall()
            for row in rows:
                print(f"{row['no']}\t{row['name']}\t{row['loc']}")
    finally:
        conn.close()

직원 정보 페이징 조회:

class Employee:
    def __init__(self, emp_id, name, role, salary):
        self.id = emp_id
        self.name = name
        self.role = role
        self.salary = salary
    
    def __str__(self):
        return f"\nID: {self.id}\n이름: {self.name}\n직책: {self.role}\n연봉: {self.salary:,}원\n"

def paginate_employees():
    page = int(input("페이지: "))
    size = int(input("항목 수: "))
    
    conn = pymysql.connect(
        host='localhost', port=3306,
        database='company', user='root',
        password='your_password', charset='utf8mb4'
    )
    try:
        with conn.cursor() as cursor:
            cursor.execute(
                "SELECT id, name, role, salary FROM employee LIMIT %s, %s",
                ((page - 1) * size, size)
            )
            for data in cursor.fetchall():
                emp = Employee(*data)
                print(emp)
    finally:
        conn.close()

태그: MySQL relational database SQL PyMySQL database design

6월 15일 01:34에 게시됨

인기 태그

©2026 괴물 클럽