[2주차 TIL] KnockOn Bootcamp Mysql

푸림님 2025. 4. 12. 23:34

MySQL의 개념과 특징

개념: MySQL은 오픈소스 관계형 데이터베이스 관리 시스템(RDBMS)으로, 데이터를 테이블 형태로 저장하고 SQL(Structured Query Language)을 사용해 관리합니다. 다양한 애플리케이션에서 데이터 저장 및 조회를 위해 널리 사용됩니다.
특징
- 오픈소스: 무료로 사용 가능하며, 커뮤니티와 상용 버전 제공
- 고성능: 빠른 쿼리 처리와 대규모 데이터 처리에 적합
- 확장성: 작은 애플리케이션부터 대규모 시스템까지 지원
- 호환성: 다양한 운영체제(Windows, Linux, macOS) 및 프로그래밍 언어(PHP, Python, Java 등)와 호환
- 보안: 사용자 인증, 암호화, 접근 제어 등 강력한 보안 기능 제공

데이터베이스 및 테이블 생성 방법

데이터베이스 생성

CREATE DATABASE example;

USE example;

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

기본적인 SQL 쿼리문 작성 방법

1. 데이터 조회(SELECT)

SELECT column1, column2 FROM table_name WHERE condition;

- ex) 모든 사용자 조회

SELECT * FROM users;

- ex) 이름이 "Hong"인 사용자 조회

SELECT name, email FROM users WHERE name = 'Hong';

2. 데이터 정렬(ORDER BY)

- ASC: 오름차순

- DESC: 내림차순

SELECT * FROM users ORDER BY created_at DESC;

3. 데이터 그룹화(GROUP BY)

- ex) 이름별 사용자 수 집계

SELECT name, COUNT(*) FROM users GROUP BY name;

4. 테이블 조인(JOIN)

- INNER JOIN: 두 테이블의 공통 데이터만 반환

- LEFT JOIN: 왼쪽 테이블의 모든 데이터와 오른쪽 테이블의 일치 데이터 반환

- RIGHT JOIN: 오른쪽 테이블의 모든 데이터와 왼쪽 테이블의 일치 데이터 반환

SELECT users.name, orders.order_date
FROM users
INNER JOIN orders ON users.id = orders.user_id;

5. 집계 함수

- COUNT(), SUM(), AVG(), MAX(), MIN()

SELECT AVG(age) FROM users;

데이터 조작 및 관리

1. 데이터 삽입(INSERT)

INSERT INTO table_name (column1, column2) VALUES (value1, value2);

- ex) 사용자 추가

INSERT INTO users (name, email) VALUES ('HonGiDong', 'HonGiDong@example.com');

- 여러 행 삽입

INSERT INTO users (name, email) VALUES 
    ('Bob', 'bob@example.com'),
    ('Charlie', 'charlie@example.com');

2. 데이터 수정(UPDATE)

UPDATE table_name SET column1 = value1 WHERE condition;

- ex) 이메일 업데이트

UPDATE users SET email = 'new_HongGiDong@example.com' WHERE name = 'HongGiDong';

3. 데이터 삭제(DELETE)

DELETE FROM table_name WHERE condition;

- ex) 특정 사용자 삭제

DELETE FROM users WHERE id = 1;

- 전체 데이터 삭제

DELETE FROM users;

4. 테이블 관리

- 테이블 구조 변경

ALTER TABLE users ADD age INT;

- 테이블 삭제

DROP TABLE users;

5. 트랜잭션 관리

- 트랜잭션 시작

START TRANSACTION;

- 변경 사항 저장

COMMIT;

- 변경 사항 취소

ROLLBACK;

- ex) 사용 예시

이 코드는 users 테이블에 'PuRin'라는 사용자를 추가한 뒤, 그의 이메일을 즉시 업데이트하는 작업을 트랜잭션으로 안전하게 처리하며, 만약 중간에 오류가 발생하면 COMMIT이 실행되지 않고, 변경 사항은 적용되지 않을 수 있습니다.(롤백 가능)

START TRANSACTION;
INSERT INTO users (name, email) VALUES ('PuRin', 'PuRin@example.com');
UPDATE users SET email = 'PuRin_new@example.com' WHERE name = 'PuRin';
COMMIT;

6. 인덱스 생성

- 조회 성능 향상

CREATE INDEX idx_name ON users(name);

7. 백업 및 복구

- 데이터베이스 백업(mysqldump 사용)

mysqldump -u username -p database_name > backup.sql

- 복구

mysql -u username -p database_name < backup.sql

다음과 같이 내용에 도전해봅시다.

1. Mysql 로컬 환경에서 설치 후 실습 하기

로컬환경으로 사용되는 WSL로 실습 진행

1-1. Mysql 설치하기

sudo apt update && sudo apt upgrade -y
sudo apt-get install mysql-server

1-2. MySQL 서비스 시작

sudo systemctl start mysql

1-3. 설치 확인

- 초기 비밀번호는 아무 입력 없이 엔터 치면 mysql 실행 가능 합니다.

mysql -u root -p

1-4. MySQL의 기본 구조와 데이터베이스 생성 실습하기

CREATE DATABASE example;

USE example;

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL
);

1-5. 데이터 조작 및 관리를 위한 기본적인 SQL 쿼리문

INSERT INTO users (username, email) VALUES ('user1', 'user1@example.com');
SELECT * FROM users;
UPDATE users SET email = 'newemail@example.com' WHERE id = 1;
DELETE FROM users WHERE id = 1;

1-6. 데이터베이스 관리

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON example_db.* TO 'newuser'@'localhost';

mysqldump -u username -p example_db > example_db_backup.sql
mysql -u username -p example_db < example_db_backup.sql

2.JOIN을 사용하여 여러 테이블 간 관계 설정하기

테스트 전 DB 세팅

테이블 생성

- customers: 고객 정보 저장
- orders: 고객별 주문 정보 저장, customer_id로 customers와 연결
- order_details: 주문별 상품 상세 정보, order_id로 orders와 연결
- FOREIGN KEY: 테이블 간 관계를 명시적으로 정의해 데이터 무결성 보장

CREATE DATABASE join_test_db;
USE join_test_db;

CREATE TABLE customers (
    customer_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE
);

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    total_amount DECIMAL(10, 2),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

CREATE TABLE order_details (
    detail_id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT,
    product_name VARCHAR(50),
    quantity INT,
    unit_price DECIMAL(10, 2),
    FOREIGN KEY (order_id) REFERENCES orders(order_id)
);

데이터 삽입

- customers: 3명 추가 (Alice, Bob, Charlie)
- orders: Alice(2개), Bob(1개) 주문 추가. Charlie는 주문 없음
- order_details: 각 주문에 대한 상품 상세 추가

INSERT INTO customers (name, email) VALUES
    ('Alice', 'alice@example.com'),
    ('Bob', 'bob@example.com'),
    ('Charlie', 'charlie@example.com');

INSERT INTO orders (customer_id, order_date, total_amount) VALUES
    (1, '2025-04-01', 150.50),  -- Alice의 주문
    (1, '2025-04-02', 300.00),  -- Alice의 두 번째 주문
    (2, '2025-04-03', 99.99);   -- Bob의 주문
    -- Charlie는 주문 없음

INSERT INTO order_details (order_id, product_name, quantity, unit_price) VALUES
    (1, 'Laptop', 1, 100.00),
    (1, 'Mouse', 2, 25.25),
    (2, 'Phone', 1, 300.00),
    (3, 'Headphones', 1, 99.99);

데이터 확인

SELECT * FROM customers;
SELECT * FROM orders;
SELECT * FROM order_details;

2-1. INNER JOIN

설명: 두 테이블에서 조건에 맞는 데이터만 반환

SELECT c.name, c.email, o.order_id, o.order_date, o.total_amount
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id;

결과: Alice와 Bob의 주문만 표시 (Charlie는 주문이 없으므로 제외)

2-2. LEFT JOUN

설명: 왼쪽 테이블(customers)의 모든 데이터와 오른쪽 테이블(orders)의 일치하는 데이터 반환. 일치하지 않으면 NULL

SELECT c.name, c.email, o.order_id, o.order_date
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;

결과: Charlie도 포함되며, 주문 정보는 NULL

2-3. RIGHT JOIN

설명: 오른쪽 테이블(orders)의 모든 데이터와 왼쪽 테이블(customers)의 일치하는 데이터 반환

SELECT c.name, c.email, o.order_id, o.order_date
FROM customers c
RIGHT JOIN orders o ON c.customer_id = o.customer_id;

결과 : 모든 주문과 관련 고객 정보 표시. Charlie는 주문이 없으므로 제외

2-4.다중 테이블 JOIN

설명: customers, orders, order_details를 모두 연결

SELECT c.name, o.order_id, o.order_date, od.product_name, od.quantity, od.unit_price
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
INNER JOIN order_details od ON o.order_id = od.order_id;

결과: 고객, 주문, 주문 상세 정보를 함께 표시

2-5. LEFT JOIN으로 상세 정보 포함

설명: 주문이 없는 고객도 포함하여, 주문 상세까지 조회

SELECT c.name, o.order_id, o.order_date, od.product_name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
LEFT JOIN order_details od ON o.order_id = od.order_id;

결과: Charlie는 주문이 없으므로 order_id, product_name 등이 NULL

3. 데이터베이스 접근 제한, 사용자 권한 설정하기

테스트 전 DB 세팅

테이블 생성

CREATE DATABASE permission_test_db;
USE permission_test_db;

-- 부서 테이블
CREATE TABLE departments (
    dept_id INT AUTO_INCREMENT PRIMARY KEY,
    dept_name VARCHAR(50) NOT NULL
);

-- 직원 테이블
CREATE TABLE employees (
    emp_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    dept_id INT,
    salary DECIMAL(10, 2),
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

테스트 데이터 삽입

INSERT INTO departments (dept_name) VALUES
    ('HR'),
    ('IT'),
    ('Sales');

INSERT INTO employees (name, dept_id, salary) VALUES
    ('Alice', 1, 50000.00),
    ('Bob', 2, 60000.00),
    ('Charlie', 3, 55000.00);

데이터 확인

SELECT * FROM departments;
SELECT * FROM employees;

3-1. 사용자 생성

- 'localhost' : 로컬 접속 제한

- '%': 모든 호스트에서 접속 허용

- 설명

read_only_user: 읽기 전용 권한 테스트
write_user: 쓰기 및 수정 권한 테스트
admin_user: 모든 권한 테스트

CREATE USER 'read_only_user'@'localhost' IDENTIFIED BY 'readpass123';
CREATE USER 'write_user'@'localhost' IDENTIFIED BY 'writepass123';
CREATE USER 'admin_user'@'localhost' IDENTIFIED BY 'adminpass123';

3-2. 권한 부여

읽기 전용 사용자

- employees와 departments에 SELECT 권한 부여

GRANT SELECT ON permission_test_db.employees TO 'read_only_user'@'localhost';
GRANT SELECT ON permission_test_db.departments TO 'read_only_user'@'localhost';

쓰기 사용자

- employees에 SELECT, INSERT, UPDATE 권한 부여

GRANT SELECT, INSERT, UPDATE ON permission_test_db.employees TO 'write_user'@'localhost';

- departments에는 SELECT만 부여

GRANT SELECT ON permission_test_db.departments TO 'write_user'@'localhost';

관리자 사용자

- permission_test_db의 모든 권한 부여

GRANT ALL PRIVILEGES ON permission_test_db.* TO 'admin_user'@'localhost';

3-3. 권한 적용

- 변경사항 즉시 적용

FLUSH PRIVILEGES;

3-4. 권한 확인

- 각 사용자의 권한 확인

SHOW GRANTS FOR 'read_only_user'@'localhost';
SHOW GRANTS FOR 'write_user'@'localhost';
SHOW GRANTS FOR 'admin_user'@'localhost';

3-5. 권한 테스트

3-5-1. 읽기 전용 사용자 테스트(read_only_user)

접속

mysql -u read_only_user -p

USE permission_test_db;

쿼리 테스트

- 데이터 조회(성공)

SELECT * FROM employees;
SELECT * FROM departments;

- 데이터 삽입(실패)

INSERT INTO employees (name, dept_id, salary) VALUES ('Dave', 1, 52000.00);

- 데이터 수정(실패)

UPDATE employees SET salary = 60000.00 WHERE emp_id = 1;

3-5-2. 쓰기 사용자 테스트(write_user)

mysql -u write_user -p

USE permission_test_db;

- employees 조회(성공)

SELECT * FROM employees;

- employees 데이터 삽입 (성공)

INSERT INTO employees (name, dept_id, salary) VALUES ('Dave', 2, 62000.00);
SELECT * FROM employees; -- Dave 추가 확인

- departments 삽입(실패)

INSERT INTO departments (dept_name) VALUES ('Marketing');

- employees 삭제(실패)

DELETE FROM employees WHERE emp_id = 1;

3-5-3. 관리자 사용자 테스트(admin_user)

mysql -u admin_user -p

USE permission_test_db;

- employees 조회(성공)

SELECT * FROM employees;

- departments 삽입 (성공)

INSERT INTO departments (dept_name) VALUES ('Marketing');

- employees 업데이트 (성공)

UPDATE employees SET salary = 70000.00 WHERE emp_id = 1;

- employees 삭제 (성공)

DELETE FROM employees WHERE name = 'Charlie';

저작자표시 비영리 변경금지 (새창열림)