2025/09/05 Database -4

ERD(Entity–Relationship Diagram)

• 데이터베이스를 설계할 때 자주 쓰는 “청사진(설계도)”

• PK (Primary Key)
  테이블의 기본 키. 값이 유일하고 NULL 불가(자동으로 NN)입니다. 복합 PK가 필요하면 여러 컬럼에 체크해도 됩니다(실무에선 보통 1개 컬럼).

• NN (Not Null)
  NULL 금지. 값이 반드시 들어와야 합니다.

• UQ (Unique)
  유니크 제약/인덱스. 중복 금지지만 NULL은 여러 개 허용됩니다(MySQL 규칙). 중복을 막고 싶을 때 사용.

• B… / BIN (Binary)
  문자형(CHAR/VARCHAR/TEXT)에 BINARY 속성을 주어 바이트 단위(대소문자 구분) 비교/정렬을 하게 합니다. 보통은 잘 안 쓰고, 정말 대소문자까지 엄격해야 할 때만 사용.

• UN (Unsigned)
  숫자형 전용. 음수 불가, 양수 범위를 더 넓게 씁니다. (예: INT의 상한이 올라감)

• ZF (Zero Fill)
  숫자 표시 폭을 채우도록 앞을 0으로 패딩합니다(예: 00123). MySQL에선 ZF를 주면 자동으로 UNSIGNED가 되기도 합니다. 참고: 8.0대에서 점점 비권장(deprecated) 추세.

• AI (Auto Increment)
  자동 증가. 보통 정수형 PK에서 사용. 테이블당 1개만 가능, 정수형 & 인덱스여야 활성화됩니다.

• G (Generated)
  생성(계산) 컬럼. 식(Expression)으로 값이 자동 계산됩니다.

  • Virtual: 계산해서 보여주기만 함(디스크 미저장)
  • Stored: 계산 결과를 저장(인덱스 가능)

• Default / Expression
  기본값을 지정합니다.

  • 상수: 0, '', 'unknown' 등
  • 시간: CURRENT_TIMESTAMP
  • (버전에 따라) 표현식 기본값도 가능

---

Tab → 오른쪽 다음 칸으로 이동

Shift + Tab → 왼쪽 이전 칸으로 이동

Enter/Return → 편집 확정 후 아래 행(같은 컬럼)으로 이동

↑ / ↓ 화살표 → 윗행/아랫행으로 이동(편집 모드가 아닐 때)

Space → 체크박스( PK / NN / UQ / UN / ZF / AI / G ) 토글

타이핑 → Datatype 칸에서 바로 INT, VARCHAR(50)처럼 바로 입력 가능

(필요하면 ↑/↓로 후보 이동 → Enter로 확정)

Esc → 현재 셀 편집 취소/편집 모드 종료

---

mysqlsample data 분석(1/2)

NOT NULL은 그 값을 비우지 말라는게 아닌 NULL값을 넣지말라는것
그칸을 비우거나 0을 넣어도 전혀 문제없다.

기본 조회 및 필터링

1. TABLE: customers 모든 고객의 이름과 이메일을 조회

                      SELECT * FROM customers;

2. TABLE: products, COL: productLine

• 'Classic Cars' 제품 라인에 속하는 모든 제품의 이름과 가격을 조회

                SELECT * FROM products
                WHERE productLine = 'Classic Cars';

3. TABLE: orders, COL: orderDate

• 가장 최근에 주문된 10개의 주문을 주문 날짜(orderDate)와 함께 조회

SELECT * FROM orders
ORDER BY orderDate DESC # orderDate 기준으로 최신 날짜 → 오래된 날짜 순서로 정렬
LIMIT 10;          ASC  → 오래된 날짜부터 최근 날짜

4.TABLE: payments, COL: amount

SELECT * FROM payments
WHERE amount >= 100;          # 100달러 이상 결제된 거래(amount)만 조회

---

조인 쿼리

SELECT c.customerName   # customers 테이블의 customerName 컬럼명을 가진 값 조회
FROM customers c;       # customers는 이름의 테이블 명을 별칭'c'로 지정

# 별칭을 사용하지 않았을 경우
SELECT customers.customerName 
FROM customers;         

# 컬럼과 테이블 전부 별칭을 부여한 경우
SELECT c.customerName AS 고객이름    #결과창에 컬럼이름이 '고객이름'으로 출력 
FROM customers c;

* 결론 : 컬럼명은 SELECT에서 지정하고, 테이블명은 FROM에서 지정한다.

1. 주문과 고객정보 조합 / 제품과 제품 라인 결합

SELECT o.orderNumber, c.customerName
FROM orders o
JOIN customers c ON o.customerNumber = c.customerNumber;

• FROM : 기본적으로 조회할 대상(기준이 되는 첫 번째 테이블)을 지정하는 구문
  • 두개 나열시 모든 행을 곱한 값이 나오기 때문에 실무에서 거의 사용 X
• JOIN : FROM에 지정한 기준 테이블에 다른 테이블을 연결(조인)할 때 사용
  • 기준은 orders 테이블이고, 여기에 customers 테이블을 JOIN으로 연결
• 두 테이블을 customerNumber 기준으로 합친 뒤
  • 그 중에서 o.orderNumber + c.customerName 컬럼만 보여주는 것

     SELECT p.productName, p.productLine, pl.textDescription
     FROM products p
     JOIN productlines pl ON p.productLine = pl.productLine;

2. 직원과 상사 정보

    SELECT e1.employeeNumber, e1.firstName, e1.lastName, 
    e2.firstName AS     'ManagerFirstName', e2.lastName AS 'ManagerLastName'
    FROM employees e1
    LEFT JOIN employees e2 ON e1.reportsTo = e2.employeeNumber;

• INNER JOIN: 두 테이블에서 조건에 맞는 행만 출력
• LEFT JOIN: 왼쪽 테이블은 전부 출력, 오른쪽은 조건 맞는 것만 붙이고 없으면 NULL
  • 왼쪽테이블 : FROM 뒤에 오는 테이블 오른쪽은 JOIN 뒤에 오는 테이블

3. 특정 사무실의 직원 목록

SELECT e.employeeNumber, e.lastName, e.firstName, e.extension,
e.email, e.officeCode, e.reportsTo, e.jobTitle
FROM employees e
JOIN offices o ON e.officeCode = o.officeCode
WHERE o.city = 'San Francisco'; # 이 조건에 속하는 값들만  join 비교

---

그룹쿼리

1. 제품 라인별 제품 수 : 각 제품 라인에 속한 제품의 수 조회

SELECT productLine, COUNT(*) AS productCount
FROM products
GROUP BY productLine;

• COUNT(*)
  • 특정 그룹(여기서는 각 productLine)에 행(row)의 개수를 세는 함수

productName	productLine
1969 Harley	Motorcycles
2003 Harley	Motorcycles
1969 Ford Mustang	Classic Cars

• Motorcycles 라인에 상품이 2개 있으므로 → 2
• Classic Cars 라인에 상품이 1개 있으므로 → 1

productLine	productCount
Motorcycles	2
Classic Cars	1

2. 고객별 총 주문 금액: 각 고객별로 총 주문 금액을 계산

SELECT customers.customerNumber, 
       customers.customerName, 
       SUM(orderdetails.quantityOrdered * orderdetails.priceEach) AS totalAmount
FROM customers
JOIN orders ON customers.customerNumber = orders.customerNumber
JOIN orderdetails ON orders.orderNumber = orderdetails.orderNumber
GROUP BY customers.customerNumber, customers.customerName;

3. 가장 많이 팔린 제품: 가장 많이 판매된 제품의 이름과 판매 수량을 조회

SELECT productName, SUM(quantityOrdered) AS totalQuantity
FROM orderdetails od
JOIN products p ON od.productCode = p.productCode
GROUP BY productName
ORDER BY totalQuantity DESC
LIMIT 1;

4. 매출이 가장 높은 사무실: 가장 많은 매출을 기록한 사무실의 위치와 매출액을 조회

SELECT o.city, SUM(od.quantityOrdered * od.priceEach) AS totalSales
FROM orders ord
JOIN orderdetails od ON ord.orderNumber = od.orderNumber
JOIN customers c ON ord.customerNumber = c.customerNumber
JOIN employees e ON c.salesRepEmployeeNumber = e.employeeNumber
JOIN offices o ON e.officeCode = o.officeCode
GROUP BY o.city
ORDER BY totalSales DESC
LIMIT 1;

---

### mysqlsample data 분석(2/2)

서브쿼리

1. 특정 금액 이상의 주문

  SELECT orderNumber, SUM(quantityOrdered * priceEach) AS totalAmount
  FROM orderdetails
  GROUP BY orderNumber
  HAVING totalAmount > 500;  # 합계가 500을 넘는 그룹만 남김

• WHERE : 개별 행(row)에 조건을 줘서 걸러냄
• HAVING : GROUP BY로 묶은 그룹 결과에 조건을 줘서 걸러냄

2. 평균 이상 결제 고객: 평균 결제 금액보다 많은 금액을 결제한 고객들의 목록을 조회

SELECT customerNumber, SUM(amount) AS totalPayment
FROM payments
GROUP BY customerNumber
HAVING totalPayment > (SELECT AVG(amount) FROM payments);

3. 주문 없는 고객: 아직 주문을 하지 않은 고객의 목록을 조회

SELECT customerName
FROM customers
WHERE customerNumber NOT IN (SELECT customerNumber FROM orders);

4. 최대 매출 고객: 가장 많은 금액을 지불한 고객의 이름과 총 결제 금액을 조회

SELECT c.customerName, SUM(od.quantityOrdered * od.priceEach) AS totalSpent
FROM customers c
JOIN orders o ON c.customerNumber = o.customerNumber
JOIN orderdetails od ON o.orderNumber = od.orderNumber
GROUP BY c.customerName
ORDER BY totalSpent DESC
LIMIT 1;

데이터 수정 및 관리

1. 신규 고객 추가: 'customers' 테이블에 새로운 고객을 추가하는 쿼리를 작성하세요.

INSERT INTO customers (customerName, contactLastName, contactFirstName, phone, addressLine1, addressLine2, city, state, postalCode, country, salesRepEmployeeNumber, creditLimit)
VALUES ('New Customer', 'Lastname', 'Firstname', '123-456-7890', '123 Street', 'Suite 1', 'City', 'State', 'PostalCode', 'Country', 1002, 50000.00);

2. 제품 가격 변경: 'Classic Cars' 제품 라인의 모든 제품 가격을 10% 인상하는 쿼리를 작성하세요.

               UPDATE products
               SET buyPrice = buyPrice * 1.10
               WHERE productLine = 'Classic Cars';

3. 고객 데이터 업데이트: 특정 고객의 이메일 주소를 변경하는 쿼리를 작성하세요.

        UPDATE customers
        SET email = 'newemail@example.com'
        WHERE customerNumber = 103;

4. 직원 전보: 특정 직원을 다른 사무실로 이동시키는 쿼리를 작성하세요.

        UPDATE employees
        SET officeCode = '2'
        WHERE employeeNumber = 1002;