[멋쟁이사자처럼부트캠프_그로스마케팅] Day 8 SQL 기본키, 외래키, JOIN, UNION, ALTER, CONSTRAINTS

*밀린거 폭풍 업로드 가겠습니다 화이팅🙃

1.기본키, 외래키

2. JOIN

3. 고급 SQL(집계함수)

4. UNION

5. ALTER

6.CONSTRAINTS

1. 기본키, 외래키

1) PRIMARY KEY (기본 키)

테이블에서 각 행(row)을 유일하게 식별하는 키
NOT NULL + UNIQUE 속성을 가짐 (중복 X, NULL X)
한 테이블에 하나만 존재 가능

중복 방지: 기본 키는 UNIQUE 속성을 가지므로 같은 값이 두 번 이상 입력될 수 없습니다. 즉, 테이블에서 각 행을 유일하게 식별할 수 있도록 보장합니다.
무결성 유지: 기본 키 값이 삭제되면 그 값과 연결된 다른 데이터의 무결성이 깨질 수 있습니다. 따라서 기본 키는 다른 테이블에서 외래 키(Foreign Key)로 참조될 경우 삭제가 제한될 수 있습니다. ON DELETE CASCADE 또는 ON DELETE SET NULL 같은 옵션이 없으면, 기본 키로 참조된 데이터를 삭제할 수 없습니다.

📌 예제 (기본 키 설정)

CREATE TABLE employees (
    id INT PRIMARY KEY,  -- id는 기본 키 (중복 불가, NULL 불가)
    name VARCHAR(50),
    department VARCHAR(50)
);

2) FOREIGN KEY (외래 키)

한 테이블의 특정 컬럼이 다른 테이블의 기본 키를 참조
데이터 무결성 유지 (존재하지 않는 값을 삽입하지 못하도록 제한)
참조된 테이블의 데이터가 삭제되면? ON DELETE CASCADE 등 옵션 사용 가능

📌 예제 (외래 키 설정)

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(id)
);

department_id가 departments 테이블의 id를 참조 → 없는 값 삽입 불가

❓ 외래 키를 기본 키로 하면 안 되나?

✔ 가능하지만, 기본 키는 반드시 유일(unique)해야 하므로 주의

✔ 한 테이블에서 같은 외래 키 값을 여러 번 사용할 수도 있음 (ex: 같은 부서에 여러 직원)

✔ 해결책: 외래 키 + 기본 키 조합 (COMPOSITE PRIMARY KEY)

CREATE TABLE project_assignment (
    employee_id INT,
    project_id INT,
    PRIMARY KEY (employee_id, project_id),
    FOREIGN KEY (employee_id) REFERENCES employees(id),
    FOREIGN KEY (project_id) REFERENCES projects(id)
);

✅ employee_id + project_id를 기본 키(복합 키)로 설정 가능

3) REFERENCES (참조)

FOREIGN KEY를 설정할 때 참조할 기본 키를 지정하는 역할
ON DELETE CASCADE 옵션 등을 통해 참조된 데이터 삭제 시 동작 설정 가능

📌 예제 (ON DELETE CASCADE 활용)

CREATE TABLE departments (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE CASCADE
);

departments의 데이터가 삭제되면, 연결된 employees 데이터도 자동 삭제됨

📌 REFERENCES로 인해 삽입이 차단되는 경우

INSERT INTO employees (id, name, department_id) VALUES (1, 'Alice', 99);

department_id = 99는 departments 테이블에 없으면 오류 발생 (참조 무결성 위반)

-오류 메시지 예시

ERROR: foreign key constraint fails

4) ON DELETE CASCADE

ON DELETE CASCADE는 외래 키가 참조하는 부모 테이블의 데이터가 삭제될 때, 자식 테이블의 데이터도 자동으로 삭제되도록 설정하는 옵션이다.

CREATE TABLE authors (
author_id INT PRIMARY KEY,
name VARCHAR(100)
);
CREATE TABLE books (
book_id INT PRIMARY KEY,
title VARCHAR(200),
author_id INT,
FOREIGN KEY (author_id) REFERENCES authors(author_id) ON DELETE CASCADE
);
#`authors` 테이블의 특정 `author_id`가 삭제되면, 
#`books` 테이블에서 해당 `author_id`를 참조하는 모든 행도 자동으로 삭제된다.

INSERT INTO authors VALUES (1, '이문열');
INSERT INTO authors VALUES (2, '한강');

INSERT INTO books VALUES (101, '삼국지', 1);
INSERT INTO books VALUES (102, '채식주의자', 2);

DELETE FROM authors WHERE author_id = 1;
#`authors` 테이블에서 `author_id = 1`을 삭제하면, 
#`books` 테이블에서 해당 `author_id`를 가진 책들도 함께 삭제된다.

SELECT * FROM books;
# author_id = 1인 이문열이 삭제되었기 때문에, books 테이블에서 삼국지도 자동으로 삭제됨.

5) DISTINCT

DISTINCT는 SELECT 문에서 중복된 데이터를 제거하고 고유한 값만 반환할 때 사용한다.

CREATE TABLE sales (
    sale_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    customer_name VARCHAR(100)
);

INSERT INTO sales VALUES (1, 'Laptop', '김영희');
INSERT INTO sales VALUES (2, 'Laptop', '이철수');
INSERT INTO sales VALUES (3, 'Tablet', '박지수');
INSERT INTO sales VALUES (4, 'Laptop', '김영희'); -- 중복 데이터

SELECT DISTINCT product_name FROM sales;

-출력값

product_name

Laptop

Tablet

Laptop이 중복되어 있었지만, 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;

2. JOIN

INNER JOIN = JOIN	두 테이블에서 일치하는 데이터만 반환	교집합을 반환하며, 일치하지 않는 데이터는 제외
LEFT JOIN	왼쪽 테이블의 모든 데이터 + 오른쪽 테이블에서 일치하는 데이터	왼쪽 테이블을 기준으로, 일치하는 데이터가 없으면 NULL 반환
RIGHT JOIN	오른쪽 테이블의 모든 데이터 + 왼쪽 테이블에서 일치하는 데이터	오른쪽 테이블을 기준으로, 일치하는 데이터가 없으면 NULL 반환
FULL OUTER JOIN	두 테이블의 모든 데이터 포함	합집합을 반환하며, 일치하지 않는 데이터는 NULL로 표시 (MySQL 미지원)

1) JOIN 예제

- INNER JOIN 예제

SELECT [customers.name](<http://customers.name/>), customers.email, orders.product, orders.price, orders.order_date
FROM customers  -- 왼쪽(Left) 테이블
INNER JOIN orders ON [customers.id](<http://customers.id/>) = orders.customer_id;  -- 오른쪽(Right) 테이블

왼쪽 테이블(Left Table): FROM 절에 등장하는 테이블 → customers
오른쪽 테이블(Right Table): JOIN 절에서 합쳐지는 테이블 → orders
조인 조건: ON customers.id = orders.customer_id
- customers.id(왼쪽 테이블)과 orders.customer_id(오른쪽 테이블)의 값이 일치하는 행만 반환된다.
- ON 키워드는 SQL에서 JOIN을 수행할 때 두 테이블을 결합하는 기준(조인 조건)을 지정하는 역할을 한다. 즉, 테이블 간 관계를 설정하여 원하는 데이터를 효율적으로 조인하는 것이 목적이다.

-주문이 가장 많은 고객 찾기

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;

2) FULL OUTER JOIN 대체 (MariaDB / MySQL)

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;

LEFT JOIN + RIGHT JOIN을 UNION하여 두 테이블의 모든 데이터 포함
UNION을 사용하면 중복된 행이 제거됨

3) ON vs WHERE

	ON	WHERE
역할	JOIN의 조인 조건을 설정	JOIN 결과에서 추가 필터링
적용 범위	JOIN 실행 시 조건 적용	JOIN 실행 후 결과에 조건 적용
사용 예시	외부 조인(LEFT JOIN, RIGHT JOIN)에서 NULL 값 유지	외부 조인에서 NULL 값이 필터링될 수 있음

-LEFT JOIN 예제

sql
복사편집
SELECT customers.name, customers.email, orders.product
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id AND orders.price > 100;  -- ON 사용

→ 가격이 100 이상인 주문만 고객과 연결되며, 주문이 없는 고객도 포함됨 (NULL 가능)

sql
복사편집
SELECT customers.name, customers.email, orders.product
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id
WHERE orders.price > 100;  -- WHERE 사용

→ 가격이 100 이상인 주문이 없는 고객은 제외됨 (NULL 제거됨)

4) 외래 키 없이 JOIN이 가능한가?

✅ 가능

JOIN은 테이블 간 데이터 조회를 위한 SQL 연산이므로 외래 키 설정 없이도 동작
단, 동일한 컬럼명이 아닌 ON 조건에 의해 조인됨

📌 예제 (외래 키 없이 JOIN)

sql
복사편집
SELECT a.name, b.salary
FROM employees a
JOIN payroll b ON a.emp_id = b.emp_number;  -- 컬럼명이 다르지만 조인 가능

employees.emp_id와 payroll.emp_number가 일치하는 경우만 조인됨
컬럼명이 같을 필요는 없고, 조인 조건(ON)만 충족하면 됨

✅ 외래 키(Foreign Key)와의 차이

	외래 키 있음 (FOREIGN KEY)	외래 키 없음 (JOIN만 사용)
데이터 무결성	부모-자식 관계 보장 (DELETE, UPDATE 제한 가능)	무결성 보장 X (논리적 관계만 설정)
자동 참조 무결성	ON DELETE CASCADE 등 옵션 가능	데이터 삭제 시 직접 관리 필요
성능 영향	무결성 유지로 인한 약간의 오버헤드	무결성 제약이 없어 더 빠를 수 있음

즉, JOIN은 단순 조회 용도로 가능하지만, 외래 키는 데이터 무결성을 보장하는 역할을 함

3. 고급 SQL - 집계 함수와 그룹화

COUNT(*)	행의 개수 계산
SUM(컬럼명)	해당 컬럼 값의 합계
AVG(컬럼명)	평균 값 계산
MAX(컬럼명)	최대 값
MIN(컬럼명)	최소 값

-그룹화가 헷갈렸던 예제

SELECT m.campaign_name, m.ad_spend, SUM(p.amount) AS total_revenue,
(SUM(p.amount) - m.ad_spend) AS profit
FROM marketing_spend m
LEFT JOIN purchases p ON m.campaign_id = p.campaign_id
GROUP BY m.campaign_name, m.ad_spend;

-> group by에 ad_spend 가 들어가는 이유: 집계 기준을 명확히 하기 위해.

SQL은 집계되지 않은(SUM이 적용되지 않은) ad_spend가 하나의 값으로 결정될 수 있는지 확인하려고 함

✔ 하지만, campaign_name은 중복될 수 있기 때문에 SQL이 어떤 ad_spend 값을 선택해야 하는지 불분명해짐

즉, campaign_name이 중복된다면, 어떤 ad_spend 값을 선택해야 하는지 모호해짐 → GROUP BY에 포함해야 함!

→ SQL이 그룹별로 ad_spend 값을 하나로 정해야 하기 때문이며, SUM(ad_spend)를 사용하면 해결할 수 있다.

4. UNION

UNION은 두 개 이상의 테이블에서 데이터를 가져와 동일한 컬럼 구조로 하나의 결과 집합으로 결합하는 SQL 연산자이다. 즉, 여러 데이터 소스를 하나의 테이블처럼 합쳐 분석할 때 유용하다.

1) UNION의 특징

컬럼 개수와 데이터 타입이 동일해야 한다.
- 결합하려는 모든 테이블의 컬럼 개수와 데이터 타입이 동일해야 정상적으로 동작한다.
중복 데이터를 자동 제거한다.
- UNION 연산자는 기본적으로 중복된 데이터를 제거하여 반환한다.
중복 데이터를 포함하려면 UNION ALL을 사용한다.
- UNION ALL은 중복 제거 없이 모든 데이터를 결합한다.

여러 마케팅 채널별 성과 통합 예시

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;

각 국가별 마케팅 캠페인 데이터를 하나로 합치고, 매출 순으로 정렬한다.

JOIN과 UNION의 차이

JOIN: 두 개 이상의 테이블을 기준이 되는 공통 필드를 통해 결합하여 새로운 데이터를 생성하는 방식.
- 주로 고객 정보, 마케팅 캠페인, 거래 데이터 등을 통합하여 보다 정밀한 분석을 수행할 때 사용됨.
UNION: 여러 테이블에서 가져온 데이터를 동일한 컬럼 구조를 가진 경우 하나의 결과 집합으로 합치는 방식.
- 예를 들어, 여러 마케팅 채널(이메일, SNS, 광고 등)의 성과 데이터를 하나로 합칠 때 유용함.

	UNION	JOIN
데이터 결합 방식	여러 테이블의 데이터를 위아래로 합침	공통 키를 기준으로 좌우로 결합
컬럼 개수	결합하려는 테이블의 컬럼 개수와 데이터 타입이 동일해야 함	테이블 간의 공통 키를 사용하여 여러 개의 컬럼을 조합
중복 데이터	기본적으로 제거됨 (UNION ALL 사용 시 제거 안 됨)	중복 데이터가 있을 경우 그대로 유지됨

-서브쿼리 예제

SELECT c.customer_id, [c.name](<http://c.name/>), c.email, b.cart_total
FROM customers c
JOIN (
SELECT customer_id, SUM(price * quantity) AS cart_total
FROM cart_items
GROUP BY customer_id
) b ON c.customer_id = b.customer_id
LEFT JOIN purchases p ON c.customer_id = p.customer_id
WHERE p.customer_id IS NULL;

MariaDB 활용 SQL 문법

5. ALTER TABLE 사용방법

ALTER TABLE 문은 MariaDB에서 기존 테이블의 구조를 변경하는 데 사용됩니다. 이를 통해 열 추가, 삭제, 수정, 인덱스 추가, 제약 조건 변경 등의 작업을 수행할 수 있습니다

1) 열 추가 (ADD COLUMN)

새로운 email 열을 추가합니다.

ALTER TABLE employees ADD COLUMN email VARCHAR(100) AFTER name;

2) 열 삭제 (DROP COLUMN)

기존 department 열을 삭제합니다.

ALTER TABLE employees DROP COLUMN department;

3) 열 이름 변경 (CHANGE COLUMN)

salary 열의 이름을 monthly_salary로 변경합니다.

ALTER TABLE employees CHANGE COLUMN salary monthly_salary DECIMAL(10,2) NOT NULL;

CHANGE COLUMN을 사용할 때는 기존 데이터 타입을 명시해야 합니다.

4) 열 데이터 타입 변경 (MODIFY COLUMN)

monthly_salary의 데이터 타입을 BIGINT로 변경합니다.

타입이 작을때 bigint 사용

ALTER TABLE employees MODIFY COLUMN monthly_salary BIGINT NOT NULL;

5) 기본 키 변경 (ADD/DROP PRIMARY KEY)

기존 id 기본 키를 제거하고 email을 기본 키로 설정합니다.

ALTER TABLE employees MODIFY id INT;  -- AUTO_INCREMENT 제거
ALTER TABLE employees DROP PRIMARY KEY;
ALTER TABLE employees ADD PRIMARY KEY (email);

auto_incerment 제거해야 기본키를 다른거로 수정할 수 있음(int로 변환)
기본 키를 변경하려면 email 열이 NOT NULL이어야 함.

6) 테이블 이름 변경 (RENAME TO)

employees 테이블을 staff로 변경합니다.

ALTER TABLE employees RENAME TO staff;

7) 인덱스 추가 및 삭제 (ADD/DROP INDEX)

name 열에 인덱스를 추가합니다.

ALTER TABLE staff ADD INDEX idx_name (name);

인덱스를 삭제하려면:

ALTER TABLE staff DROP INDEX idx_name;

6. SQL 제약조건(Constraints)

1️⃣ UNIQUE 제약 조건 (UNIQUE)

특정 열의 값이 중복되지 않도록 제한하는 제약 조건.

ALTER TABLE company_data ADD CONSTRAINT unique_emp_name UNIQUE (emp_name);

emp_name 열의 값이 중복되지 않도록 보장.

2️⃣ CHECK 제약 조건 (CHECK)

특정 열의 값이 주어진 조건을 충족해야 함.

ALTER TABLE company_data ADD CONSTRAINT check_salary CHECK (monthly_salary >= 3000000);

monthly_salary 값이 3,000,000 이상이어야 함.

3️⃣ DESC로는 제약 조건이 보이지 않음

제약 조건을 확인하려면:

SHOW CREATE TABLE company_data;

'STUDY' 카테고리의 다른 글

[멋쟁이사자처럼부트캠프_그로스마케팅] Day 10 웹 애플리케이션 구조, Flask, 웹크롤링 (1)	2025.02.19
[멋쟁이사자처럼부트캠프_그로스마케팅] Day 9 GCP VM 인스턴스 설정 및 MariaDB + Pandas 연동 방법 (0)	2025.02.18
[멋쟁이사자처럼부트캠프_그로스마케팅] Day7 GCP VM 설정 및 MariaDB 설치 & SQL 기본 문법 (1)	2025.02.13
[멋쟁이사자처럼부트캠프_그로스마케팅] Day6 GCP VM 설정 및 MariaDB 설치 & SQL 기본 문법 (0)	2025.02.13
[멋쟁이사자처럼부트캠프_그로스마케팅] Day5 Pandas-2 (0)	2025.02.13

내 블로그 - 관리자 홈 전환	`Q` `Q`
새 글 쓰기	`W` `W`

글 수정 (권한 있는 경우)	`E` `E`
댓글 영역으로 이동	`C` `C`

이 페이지의 URL 복사	`S` `S`
맨 위로 이동	`T` `T`
티스토리 홈 이동	`H` `H`
단축키 안내	`Shift` + `/` `⇧` + `/`

˖◛⁺˖

[멋쟁이사자처럼부트캠프_그로스마케팅] Day 8 SQL 기본키, 외래키, JOIN, UNION, ALTER, CONSTRAINTS

1. 기본키, 외래키

1) PRIMARY KEY (기본 키)

2) FOREIGN KEY (외래 키)

3) REFERENCES (참조)

4) ON DELETE CASCADE

5) DISTINCT

2. JOIN

1) JOIN 예제

2) FULL OUTER JOIN 대체 (MariaDB / MySQL)

3) ON vs WHERE

4) 외래 키 없이 JOIN이 가능한가?

📌 예제 (외래 키 없이 JOIN)

3. 고급 SQL - 집계 함수와 그룹화

4. UNION

MariaDB 활용 SQL 문법

5. ALTER TABLE 사용방법

6. SQL 제약조건(Constraints)

3️⃣ DESC로는 제약 조건이 보이지 않음

'STUDY' 카테고리의 다른 글

티스토리툴바

개인정보

단축키

내 블로그

블로그 게시글

모든 영역

[멋쟁이사자처럼부트캠프_그로스마케팅] Day 8 SQL 기본키, 외래키, JOIN, UNION, ALTER, CONSTRAINTS

1. 기본키, 외래키

1) PRIMARY KEY (기본 키)

2) FOREIGN KEY (외래 키)

3) REFERENCES (참조)

4) ON DELETE CASCADE

5) DISTINCT

2. JOIN

1) JOIN 예제

2) FULL OUTER JOIN 대체 (MariaDB / MySQL)

3) ON vs WHERE

4) 외래 키 없이 JOIN이 가능한가?

📌 예제 (외래 키 없이 JOIN)

3. 고급 SQL - 집계 함수와 그룹화

4. UNION

MariaDB 활용 SQL 문법

5. ALTER TABLE 사용방법

6. SQL 제약조건(Constraints)

3️⃣ DESC로는 제약 조건이 보이지 않음

'STUDY' 카테고리의 다른 글

관련글

티스토리툴바

개인정보

단축키

내 블로그

블로그 게시글

모든 영역