MariaDB에서 여러 테이블 다루기 (JOIN,UNION) 및 고급 SQL (집계 함수와 그룹화)
1. 여러 테이블 다루기 - JOIN
워드 설명(FOREIGN KEY, REFERENCES, ON DELETE CASCADE, DISTINCT)
| 용어 | 설명 | 예제 활용 |
| FOREIGN KEY | 한 테이블의 특정 컬럼이 다른 테이블의 기본 키를 참조하는 제약 조건 |
orders.customer_id → customers.customer_id 참조 |
| REFERENCES | FOREIGN KEY 정의 시 참조할 테이블과 컬럼을 지정 |
FOREIGN KEY (author_id) REFERENCES authors(author_id) |
| ON DELETE CASCADE |
부모 테이블의 데이터가 삭제될 때, 참조하는 자식 테이블의 데이터도 자동 삭제 | FOREIGN KEY (author_id) REFERENCES authors(author_id) ON DELETE CASCADE |
| DISTINCT | SELECT 조회 시 중복된 데이터를 제거하여 고유한 값만 출력 |
SELECT DISTINCT product_name FROM sales; |
1) JOIN 활용 예제
| JOIN 유형 | 포함되는 데이터 | 설명 |
| INNER JOIN | 두 테이블에서 일치하는 데이터만 반환 | 교집합을 반환하며, 일치하지 않는 데이터는 제외 |
| LEFT JOIN | 왼쪽 테이블의 모든 데이터 + 오른쪽 테이블에서 일치하는 데이터 | 왼쪽 테이블을 기준으로, 일치하는 데이터가 없으면 NULL 반환 |
| RIGHT JOIN | 오른쪽 테이블의 모든 데이터 + 왼쪽 테이블에서 일치하는 데이터 | 오른쪽 테이블을 기준으로, 일치하는 데이터가 없으면 NULL 반환 |
| FULL OUTER JOIN | 두 테이블의 모든 데이터 포함 | 합집합을 반환하며, 일치하지 않는 데이터는 NULL로 표시 (MySQL 미지원) |
- 요약
- INNER JOIN → 공통된 데이터만 표시 (교집합)
- LEFT JOIN → 왼쪽 테이블의 모든 데이터 표시 + 오른쪽 테이블에서 일치하는 데이터
- RIGHT JOIN → 오른쪽 테이블의 모든 데이터 표시 + 왼쪽 테이블에서 일치하는 데이터
- FULL OUTER JOIN → 모든 데이터 표시 + 일치하지 않는 데이터는 NULL (MySQL 미지원, UNION으로 대체 가능)
(1) 테이블 생성
- customers (고객 정보 테이블)
CREATE TABLE customers (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
phone VARCHAR(15) UNIQUE NOT NULL
);
- orders (주문 정보 테이블)
CREATE TABLE orders (
id INT AUTO_INCREMENT PRIMARY KEY,
customer_id INT NOT NULL,
product VARCHAR(100) NOT NULL,
price DECIMAL(10,2) NOT NULL,
order_date DATE NOT NULL,
FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
);
(2) 데이터 삽입
INSERT INTO customers (name, email, phone) VALUES
('김철수', 'chulsoo@example.com', '010-1234-5678'),
('이영희', 'younghee@example.com', '010-2233-4455'),
('박민수', 'minsoo@example.com', '010-3344-5566'),
('김현우', 'hyunwoo@example.com', '010-4455-6677'), -- 주문 없음
('정수진', 'soojin@example.com', '010-5566-7788'); -- 주문 없음
INSERT INTO orders (customer_id, product, price, order_date) VALUES
(1, '노트북', 1500000, '2024-01-10'),
(2, '스마트폰', 800000, '2024-02-15'),
(1, '무선 이어폰', 200000, '2024-03-01'),
(3, '태블릿', 600000, '2024-03-10'),
(2, '스마트워치', 350000, '2024-04-05');
(3) 고객과 주문 정보를 함께 조회 (INNER JOIN)
SELECT
customers.name, -- 고객 이름 (customers 테이블에서 가져옴)
customers.email, -- 고객 이메일 (customers 테이블에서 가져옴)
orders.product, -- 주문한 제품명 (orders 테이블에서 가져옴)
orders.price, -- 주문한 제품 가격 (orders 테이블에서 가져옴)
orders.order_date -- 주문 날짜 (orders 테이블에서 가져옴)
FROM customers -- 고객 정보를 포함하는 테이블
INNER JOIN orders -- 고객과 주문 데이터를 결합
ON customers.id = orders.customer_id; -- 고객 ID를 기준으로 두 테이블을 연결 (조인 조건)
- 왼쪽 테이블(Left Table): FROM 절에 등장하는 테이블 → customers
- 오른쪽 테이블(Right Table): JOIN 절에서 합쳐지는 테이블 → orders
- 조인 조건: ON customers.id = orders.customer_id
- customers.id(왼쪽 테이블)과 orders.customer_id(오른쪽 테이블)의 값이 일치하는 행만 반환된다.
- ON 키워드는 SQL에서 JOIN을 수행할 때 두 테이블을 결합하는 기준(조인 조건)을 지정하는 역할을 한다. 즉, 테이블 간 관계를 설정하여 원하는 데이터를 효율적으로 조인하는 것이 목적이다.
(4) 고객 정보와 주문 내역을 모두 가져오기 (LEFT JOIN)
SELECT customers.name, customers.email, orders.product, orders.price, orders.order_date
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id;
- 왼쪽 테이블의 모든 데이터 표시 + 오른쪽 테이블에서 일치하는 데이터
- 주문이 없는 고객도 조회된다.
(5) 주문내역이 있는 고객만 조회 (RIGHT JOIN)
SELECT customers.name, customers.email, orders.product, orders.price, orders.order_date
FROM customers
RIGHT JOIN orders ON customers.id = orders.customer_id;
- RIGHT JOIN → 오른쪽 테이블의 모든 데이터 표시 + 왼쪽 테이블에서 일치하는 데이터
- 주문을 한 고객만 출력된다.
(6) 모든 고객과 주문 정보 조회 (FULL OUTER JOIN 대체)
- MariaDB는 FULL OUTER JOIN을 지원하지 않기 때문에, UNION을 사용한다.
SELECT customers.name, customers.email, orders.product, orders.price, orders.order_date
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id
UNION
SELECT customers.name, customers.email, orders.product, orders.price, orders.order_date
FROM customers
RIGHT JOIN orders ON customers.id = orders.customer_id;
2. 고급 SQL - 집계 함수와 그룹화
- 주요 집계 함수
| 함수 | 설명 |
| COUNT(*) | 행의 개수 계산 |
| SUM(컬럼명) | 해당 컬럼 값의 합계 |
| AVG(컬럼명) | 평균 값 계산 |
| MAX(컬럼명) | 최대 값 |
| MIN(컬럼명) | 최소 값 |
1) 집계 함수 및 그룹화 활용 예제
(1) 총 주문 개수 조회
SELECT COUNT(*) AS total_orders FROM orders;
- 결과 예시: total_orders 5
(2) 고객별 총 주문 개수 조회 (GROUP BY)
SELECT customers.name, COUNT(orders.id) AS order_count
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id
GROUP BY customers.name;
- 결과 예시: name order_count
| 김철수 | 2 |
| 이영희 | 2 |
| 박민수 | 1 |
(3) 고객별 총 주문 금액 조회
SELECT customers.name, SUM(orders.price) AS total_spent
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id
GROUP BY customers.name;
- 결과 예시: name total_spent
| 김철수 | 1700000 |
| 이영희 | 1150000 |
| 박민수 | 600000 |
(4) 가장 비싼 주문 찾기
SELECT product, MAX(price) AS highest_price FROM orders;
- 결과 예시: product highest_price
| 노트북 | 1500000 |
(5) 주문이 가장 많은 고객 찾기
SELECT customers.name, COUNT(orders.id) AS order_count
FROM customers
JOIN orders ON customers.id = orders.customer_id
GROUP BY customers.name
ORDER BY order_count DESC
LIMIT 1;
(6) 특정 기간(예: 2024년 3월~4월) 동안의 주문 금액 총합
SELECT SUM(price) AS total_sales
FROM orders
WHERE order_date BETWEEN '2024-03-01' AND '2024-04-30';
7) 월별 총 주문 수 분석
SELECT MONTH(order_date) AS order_month, COUNT(*) AS order_count
FROM orders
GROUP BY order_month
ORDER BY order_month;
3. UNION
- 두 개 이상의 테이블에서 데이터를 가져와 동일한 컬럼 구조로 하나의 결과 집합으로 결합하는 SQL 연산자이다.
- 여러 데이터 소스를 하나의 테이블처럼 합쳐 분석할 때 유용하다.
1) UNION의 특징
- 컬럼 개수와 데이터 타입이 동일해야 한다.
- 결합하려는 모든 테이블의 컬럼 개수와 데이터 타입이 동일해야 정상적으로 동작한다.
- 중복 데이터를 자동 제거한다.
- UNION 연산자는 기본적으로 중복된 데이터를 제거하여 반환한다.
- 중복 데이터를 포함하려면 UNION ALL을 사용한다.
- UNION ALL은 중복 제거 없이 모든 데이터를 결합한다.
2) 테이블 생성
-- 국가별 마케팅 성과 테이블
CREATE TABLE marketing_korea (
region VARCHAR(50),
campaign_name VARCHAR(100),
revenue DECIMAL(10,2)
);
CREATE TABLE marketing_usa (
region VARCHAR(50),
campaign_name VARCHAR(100),
revenue DECIMAL(10,2)
);
CREATE TABLE marketing_europe (
region VARCHAR(50),
campaign_name VARCHAR(100),
revenue DECIMAL(10,2)
);
-- 이메일 및 SNS 캠페인 반응 데이터 테이블
CREATE TABLE email_campaign (
customer_id INT,
campaign_name VARCHAR(100),
clicks INT
);
CREATE TABLE sns_campaign (
customer_id INT,
campaign_name VARCHAR(100),
clicks INT
);
3) 샘플 데이터 삽입
아래는 테스트를 위해 UNION을 적용할 충분한 데이터를 삽입하는 SQL 코드이다.
-- 1) 한국 마케팅 캠페인 성과 데이터 삽입
INSERT INTO marketing_korea VALUES
('Korea', '겨울 할인 이벤트', 500000),
('Korea', '신규 회원 웰컴 이벤트', 300000),
('Korea', '특가 프로모션', 700000),
('Korea', '봄맞이 할인', 400000),
('Korea', '회원 전용 세일', 550000);
-- 2) 미국 마케팅 캠페인 성과 데이터 삽입
INSERT INTO marketing_usa VALUES
('USA', 'Winter Sale', 600000),
('USA', 'New Member Promo', 350000),
('USA', 'Exclusive Deal', 800000),
('USA', 'Spring Discount', 450000),
('USA', 'VIP Members Only', 600000);
-- 3) 유럽 마케팅 캠페인 성과 데이터 삽입
INSERT INTO marketing_europe VALUES
('Europe', 'Winter Discount', 550000),
('Europe', 'Signup Bonus', 320000),
('Europe', 'Limited Offer', 750000),
('Europe', 'Easter Sale', 430000),
('Europe', 'Members-Only Discount', 570000);
-- 4) 이메일 캠페인 데이터 삽입
INSERT INTO email_campaign VALUES
(1, '겨울 할인 이벤트', 8),
(2, '신규 회원 웰컴 이벤트', 5),
(3, '특가 프로모션', 12),
(4, '봄맞이 할인', 3),
(5, '회원 전용 세일', 7),
(6, '여름 한정 할인', 2),
(7, '연말 특별 세일', 10),
(8, 'VIP 고객 특별 혜택', 15),
(9, '신상품 출시 기념', 6),
(10, '한정판 할인', 9);
-- 5) SNS 캠페인 데이터 삽입
INSERT INTO sns_campaign VALUES
(1, '인스타그램 광고', 20),
(2, '페이스북 광고', 15),
(3, '틱톡 바이럴', 25),
(4, '유튜브 리뷰', 10),
(5, '트위터 프로모션', 18),
(6, '카카오톡 친구 추가 이벤트', 5),
(7, '네이버 블로그 후기', 8),
(8, '네이버 쇼핑 라이브', 30),
(9, '유튜브 숏츠 광고', 22),
(10, '페이스북 리타겟팅 광고', 12);
4) UNION 예시
(1) 여러 국가에서의 마케팅 성과 통합
SELECT region, campaign_name, revenue FROM marketing_korea
UNION
SELECT region, campaign_name, revenue FROM marketing_usa
UNION
SELECT region, campaign_name, revenue FROM marketing_europe;
- 한국, 미국, 유럽의 마케팅 성과 데이터를 하나로 합친다.
- UNION을 사용하면 중복된 캠페인 이름이 제거된다.
(2) 이메일과 SNS 캠페인 반응 데이터 통합
SELECT customer_id, campaign_name, clicks, 'Email' AS channel
FROM email_campaign
UNION
SELECT customer_id, campaign_name, clicks, 'SNS' AS channel
FROM sns_campaign;
- 이메일과 SNS 캠페인의 클릭 데이터를 통합하여 분석한다.
- 고객별로 어떤 채널에서 더 많은 반응을 보였는지 비교할 수 있다.
(3) UNION ALL을 사용하여 중복 포함
SELECT region, campaign_name, revenue FROM marketing_korea
UNION ALL
SELECT region, campaign_name, revenue FROM marketing_usa
UNION ALL
SELECT region, campaign_name, revenue FROM marketing_europe;
- UNION ALL을 사용하면 중복된 데이터도 포함하여 전체 데이터를 유지할 수 있다.
(4) 여러 마케팅 채널별 성과 통합
SELECT region, campaign_name, revenue FROM marketing_korea
UNION
SELECT region, campaign_name, revenue FROM marketing_usa
UNION
SELECT region, campaign_name, revenue FROM marketing_europe
ORDER BY revenue DESC;
- 각 국가별 마케팅 캠페인 데이터를 하나로 합치고, 매출 순으로 정렬한다.
(5) A/B 테스트 그룹 데이터 통합
SELECT customer_id, campaign_name, clicks, 'A' AS test_group
FROM email_campaign
UNION
SELECT customer_id, campaign_name, clicks, 'B' AS test_group
FROM sns_campaign;
- 이메일 캠페인을 A그룹, SNS 캠페인을 B그룹으로 분류하여 A/B 테스트 데이터를 통합한다.
- UNION은 여러 데이터 테이블을 하나로 통합할 때 사용된다.
- UNION은 중복 데이터를 제거하고, UNION ALL은 중복을 포함한다.
- 마케팅 데이터 분석에서 UNION을 활용하면 국가별 캠페인 성과 비교, A/B 테스트 통합 분석, 다양한 마케팅 채널 성과를 하나의 결과로 합치는 데 유용하다.
4. JOIN과 UNION의 차이
- JOIN: 두 개 이상의 테이블을 기준이 되는 공통 필드를 통해 결합하여 새로운 데이터를 생성하는 방식.
- 주로 고객 정보, 마케팅 캠페인, 거래 데이터 등을 통합하여 보다 정밀한 분석을 수행할 때 사용됨.
- UNION: 여러 테이블에서 가져온 데이터를 동일한 컬럼 구조를 가진 경우 하나의 결과 집합으로 합치는 방식.
- 예를 들어, 여러 마케팅 채널(이메일, SNS, 광고 등)의 성과 데이터를 하나로 합칠 때 유용함.
항목 UNION JOIN 데이터 결합 방식 여러 테이블의 데이터를 위아래로 합침 공통 키를 기준으로 좌우로 결합 컬럼 개수 결합하려는 테이블의 컬럼 개수와 데이터 타입이
동일해야 함테이블 간의 공통 키를 사용하여 여러 개의
컬럼을 조합중복 데이터 기본적으로 제거됨 (UNION ALL 사용 시 제거 안 됨) 중복 데이터가 있을 경우 그대로 유지됨
- 예를 들어, 여러 마케팅 채널(이메일, SNS, 광고 등)의 성과 데이터를 하나로 합칠 때 유용함.
- 그로스 마케팅에서는 JOIN을 활용하여 고객 분석, 유입 경로별 전환율, 마케팅 비용 대비 수익을 계산하고,
UNION을 활용하여 다채널 성과 분석, 국가별 데이터 비교, A/B 테스트 결과 비교 등에 사용 가능.
Python과 SQL 연동
1. 환경 설정
- Python 라이브러리: mysql-connector-python, pandas
- 데이터베이스: MariaDB
- 기능:
- MariaDB에 연결
- 테이블 생성
- pandas DataFrame을 사용하여 데이터를 삽입
- pandas를 활용하여 데이터 조회 및 출력
2. Python 설치
sudo apt-get update
sudo apt-get install python3-pip
필요한 라이브러리 설치
pip install mysql-connector-python pandas
- MariaDB 연결: mysql.connector.connect()를 사용하여 MariaDB에 연결합니다.
- 테이블 생성: 기존 employees 테이블이 존재하면 삭제한 후 새로 생성합니다.
- 데이터 삽입: pandas DataFrame을 활용하여 데이터를 저장한 후 executemany()를 사용하여 삽입합니다.
- 데이터 조회 및 변환: 조회한 데이터를 pandas.DataFrame으로 변환하여 분석할 수 있도록 합니다.
- 데이터 시각화: ace_tools.display_dataframe_to_user()를 사용하여 데이터를 화면에 출력합니다.
3. 웹 어플리케이션 이해 및 적용
1) Maria database를 설치하기 위한 Linux 명령
sudo apt update && sudo apt upgrade
sudo apt-get install mariadb-server
2) MariaDB(Mysql) 데이터베이스 생성 및 계정 생성
sudo mysql –u root –p
_________
CREATE DATABASE backend default CHARACTER SET UTF8;
show databases;
use backend;
GRANT ALL PRIVILEGES ON backend.* TO gabkeun@localhost IDENTIFIED BY '1234';
exit
mysql –u user –p
_________
- Maria database를 로그인 할 때는 password 부분에 입력을 해도 보안상의 이유로 화면에 아무 표시도 되지 않는다.
3) Python / Python virtual environment installation(Ubuntu 24.04 LT)
sudo apt-get update
sudo apt-get install python3-pip
sudo apt install python3.12-venv
3-1) Ubuntu 20.04LT 일 경우 아래와 같이 하세요.
sudo add-apt-repository ppa:deadsnakes/ppa
sudo apt update
sudo apt install python3.12-venv
4) Python virtual environment setting
python3 -m venv myenv #Python virtual environment setting
source myenv/bin/activate
- Ubuntu version 20.04LT(GCP 기준)인 경우 파이썬 가상환경이 필요 없으며 파이썬 실행시 “python3” 로 실행하면 동작한다.
5) mysql-connector-python, Flask installation
pip install mysql-connector-python
pip install Flask
- 모델 정의를 위해 MySQL 데이터베이스의 테이블 구조를 정의합니다. 이 예제에서는 posts 테이블을 사용하여 게시판 애플리케이션의 글을 저장한다.
CREATE TABLE posts (
id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(255) NOT NULL,
content TEXT NOT NULL,
author VARCHAR(255) NOT NULL,
created_at VARCHAR(255),
image VARCHAR(255)
);
이 SQL 명령어는 posts 테이블을 생성하고, 다음과 같은 컬럼을 정의한다.
- id: 게시글의 고유 ID로, 자동 증가(AUTO_INCREMENT) 설정을 통해 고유성을 보장합니다.
- title: 게시글의 제목을 저장하는 문자열 필드입니다. 최대 길이는 100자로 제한되며, NULL을 허용하지 않습니다.
- content: 게시글의 내용을 저장하는 텍스트 필드입니다. NULL을 허용하지 않습니다.
- image_filename: 업로드된 이미지 파일의 이름을 저장하는 문자열 필드입니다. 최대 길이는 100자입니다.
- created_at: 게시글이 작성된 날짜와 시간을 저장하는 필드입니다. 기본값은 현재 시간으로 설정됩니다.
6) 게시판 구현을 이용한 Database의 CRUD 이해
Database를 이용한 어플리케이션의 핵심기능은 CRUD이다. 또한 CRUD를 위한 어플리케이션은 게시판이 가장 적당하며 다음의 설명은 아래의 스샷과 같은 기능을 하는 게시판에 관한 것이다.
7) WEB Integration
먼저 WEB에 연동하려면 서버에 Flask 디렉토리 구조를 만들어야 한다.
다음은 Flask 애플리케이션과 연동된 HTML 템플릿 파일들이다. 이 템플릿들은 Flask 애플리케이션에서 사용하는 templates 폴더 안에 위치해야 한다.
- - lab 디렉토리 안에 site1 하위 디렉토리 만들기. 여기서 templates, static, uploads디렉토리 생성
- - SublineText 이용해서 app.py, index.html, view.html, write.html, styles.css, common.css 파일 만들기
- - FileZilla 이용해서 서버연결 후 각 디렉토리에 해당 파일 넣기
- - 그 후에 python3 app.py 입력 후 주소창에 해당 서버연결주소:포트번호 입력후 사이트 확인하기(http://34.47.85.6:5001/)
lab/site1
│
├── app.py
├── templates/
│ ├── index.html
│ ├── view.html
│ └── write.html
├── static/
│ ├── common.css
│ └── styles.css
│
└── uploads/
디렉토리 및 파일 설명
- lab_day4/: 프로젝트의 루트 디렉토리.
- app.py: Flask 애플리케이션의 주요 파일. 여기에서 라우트와 핸들러를 정의한다.
- templates/: HTML 템플릿 파일을 저장하는 디렉토리이다.
- index.html: 게시글 목록을 보여주는 메인 페이지.
- view.html: 게시글 상세보기 페이지.
- write.html: 새 게시글 작성 페이지.
- static/: 정적 파일을 저장하는 디렉토리.
- styles.css: 스타일 시트를 저장.
- common.css: 스타일 시트를 저장.
- uploads/: 업로드된 이미지 파일을 저장하는 디렉토리.
