[2주차 TIL] KnockOn Bootcamp - Mysql

 

 

💡 이번에 공부할 내용은 다음과 같습니다.

1. MySQL의 개념과 특징
2. 데이터베이스 및 테이블 생성 방법
3. 기본적인 SQL 쿼리문 작성 방법
4. 데이터 조작 및 관리 

 

 

MySQL 데이터베이스

 

MySQL은 관계형 데이터베이스 관리 시스템(RDBMS)으로, 데이터를 효율적으로 저장, 관리 및 검색을 위해 SQL(Structured Query Language)을 사용합니다. 오픈 소스이며, 다양한 운영 체제에서 사용할 수 있습니다.

구조화된 쿼리 언어(SQL)를 사용해 데이터를 쿼리하거나 수정할 수 있습니다.

 

• 특징:
  • 오픈 소스: 누구나 무료로 사용하고 수정할 수 있습니다.
  • 빠른 성능: 읽기/쓰기 성능이 매우 빠르고, 대규모 데이터베이스에도 유리합니다.
  • 다양한 플랫폼 지원: Windows, Linux, macOS 등 여러 운영 체제에서 사용 가능합니다.
  • ACID 지원: 트랜잭션을 지원하여 데이터의 무결성을 보장합니다.
  • 확장성: 수많은 데이터를 처리할 수 있는 능력.

---

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

• 데이터베이스 생성:
  • 예: CREATE DATABASE test_db;
• CREATE DATABASE database_name;
• 테이블 생성:

  • CREATE TABLE users (
        id INT AUTO_INCREMENT PRIMARY KEY,
        username VARCHAR(50),
        email VARCHAR(100),
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    );
    

• CREATE TABLE table_name ( column1 datatype, column2 datatype, ... );

---

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

• SELECT: 데이터 조회

SELECT username, email FROM users;

 

• INSERT: 데이터 삽입
  • 예: INSERT INTO users (username, email) VALUES ('john_doe', 'john@example.com');

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

• UPDATE: 데이터 수정
  • 예: UPDATE users SET email = 'john_new@example.com' WHERE username = 'john_doe';

UPDATE table_name SET column1 = value1 WHERE condition;

• DELETE: 데이터 삭제
  • 예: DELETE FROM users WHERE username = 'john_doe';

DELETE FROM table_name WHERE condition;

---

4. 데이터 조작 및 관리

• 데이터 조회 조건:
  • WHERE: 조건을 주어 데이터를 필터링. 
  • SELECT * FROM users WHERE username = 'john_doe';
• 정렬:
  • ORDER BY: 데이터를 특정 컬럼을 기준으로 정렬. 

  • SELECT * FROM users ORDER BY created_at DESC;
    

• 그룹화 및 집계:
  • GROUP BY: 동일한 값을 가지는 행들을 그룹화.
  • HAVING: 그룹화된 데이터에 조건을 추가.

    SELECT COUNT(*), username FROM users GROUP BY username HAVING COUNT(*) > 1;
    

• 조인 (JOIN):
  • 여러 테이블에서 데이터를 결합하여 조회할 때 사용.

  SELECT users.username, orders.amount 
  FROM users 
  JOIN orders ON users.id = orders.user_id;
  

---

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
);

​
데이터 조작 및 관리를 위한 기본적인 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;

​
데이터베이스 관리
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​

​

 

 

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

1. Mysql 로컬 환경에서 설치 후 위의 내용 실습하기
2. JOIN을 사용하여 여러 테이블 간 관계 설정하기
3. 데이터베이스 접근 제한, 사용자 권한 설정하기

 

1. MySQL 로컬 환경 설치

먼저 MySQL을 로컬에 설치해야 합니다. 이 작업은 운영 체제에 따라 다르므로 아래 절차를 참고하세요.

 

MySQL을 설치합니다:

apt update
apt install mysql-server

 

설치 후 MySQL을 시작합니다:

service mysql start
systemctl status mysql

 

 

MySQL에 접속:

mysql -u root -p

 

 

 

2. 데이터베이스와 테이블 생성

로컬에서 MySQL에 접속한 후, 위에서 제공한 SQL 쿼리를 실행하여 데이터베이스와 테이블을 생성합니다.

CREATE DATABASE example;

USE example;

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

 

 

3. 기본 데이터 조작

3.1 데이터 삽입

INSERT INTO users (username, email) VALUES ('user1', 'user1@example.com');

 

3.2 데이터 조회

SELECT * FROM users;

3.3 데이터 수정

 

UPDATE users SET email = 'newemail@example.com' WHERE id = 1;

3.4 데이터 삭제

DELETE FROM users WHERE id = 1;

4. 데이터베이스 관리

4.1 새로운 사용자 생성

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

4.2 권한 부여

새로 생성한 사용자에게 example 데이터베이스에 대한 모든 권한을 부여합니다.

 

GRANT ALL PRIVILEGES ON example.* TO 'newuser'@'localhost';

4.3 권한 적용

FLUSH PRIVILEGES;

 

5. 백업 및 복원

5.1 데이터베이스 백업

터미널에서 mysqldump 명령어를 사용하여 데이터베이스를 백업합니다.

 

mysqldump -u username -p example > example_backup.sql

5.2 데이터베이스 복원

백업한 SQL 파일을 사용하여 데이터베이스를 복원합니다.

mysql -u username -p example < example_backup.sql

---

6. 추가 실습: JOIN을 사용한 여러 테이블 간 관계 설정

 

이제 JOIN을 사용하여 두 개 이상의 테이블을 결합하는 방법을 실습해보겠습니다. 먼저, 두 개의 테이블을 생성하고, JOIN을 사용해 데이터를 결합하는 예제를 만들어 봅니다.

6.1 orders 테이블 생성

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    order_date DATE,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

6.2 orders 테이블에 데이터 삽입

INSERT INTO orders (user_id, order_date) VALUES (1, '2024-12-01');

6.3 JOIN을 사용하여 데이터 조회

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

위 쿼리는 users 테이블과 orders 테이블을 user_id 필드를 기준으로 결합하고, 사용자 이름과 주문 날짜를 반환합니다.

 

 

JOIN을 사용하여 데이터 조회를 할 때, 그 결과값은 두 개 이상의 테이블에서 지정한 조건에 맞는 데이터를 결합한 형태로 반환됩니다.

우리는 예시로 users 테이블과 orders 테이블을 결합하여 조회하는 쿼리를 사용했습니다:

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

가정

1. users 테이블에 사용자가 존재하고,
2. orders 테이블에 그 사용자에 대한 주문이 존재한다고 가정하겠습니다.

예를 들어, users 테이블에 아래와 같은 데이터가 있다고 가정해봅시다:

 

idusernameemail

1	user1	user1@example.com
2	user2	user2@example.com
3	user3	user3@example.com

그리고, orders 테이블에 아래와 같은 데이터가 있다고 가정합니다:

 

iduser_idorder_date

1	1	2024-12-01
2	1	2024-12-02
3	2	2024-12-03

JOIN 결과

JOIN 쿼리에서 users 테이블과 orders 테이블을 결합할 때, users.id = orders.user_id 조건을 기준으로 데이터를 매칭합니다. 그 결과는 다음과 같습니다:

 

usernameorder_date

user1	2024-12-01
user1	2024-12-02
user2	2024-12-03

설명

• user1은 두 번의 주문(2024-12-01, 2024-12-02)을 했기 때문에 두 개의 결과가 반환됩니다.
• user2는 하나의 주문(2024-12-03)을 했기 때문에 한 개의 결과가 반환됩니다.
• user3는 주문이 없기 때문에 결과에 포함되지 않습니다.

 

 

---

7. 데이터베이스 접근 제한 및 사용자 권한 설정

MySQL에서 특정 사용자에게 데이터베이스 접근을 제한하려면, 사용자 권한을 세부적으로 설정할 수 있습니다. 예를 들어, newuser가 example 데이터베이스에 대해 읽기 전용 권한만 가지도록 설정할 수 있습니다.

REVOKE ALL PRIVILEGES ON example.* FROM 'newuser'@'localhost';
GRANT SELECT ON example.* TO 'newuser'@'localhost';

 

위 명령어는 newuser에게 SELECT 권한만 부여하여, 데이터베이스에서 데이터를 읽기만 할 수 있게 설정합니다.

---

실습 정리

• MySQL 환경을 로컬에 설치하고 데이터베이스 및 테이블을 생성하는 방법을 익혔습니다.
• JOIN을 사용하여 여러 테이블을 연결하는 방법을 배웠습니다.
• 사용자 권한을 설정하여 특정 사용자에게 데이터베이스 접근을 제한하는 방법을 실습했습니다.