1) 실습 - 간단한 스키마 설계하기
(1) user 테이블 만들기
id: 자동으로 생성됩니다. (PK)
password: 4자리 랜덤
name: 한글3자리
gender: male, female 중 랜덤선택
email: 5자리영문@gmail.com
birthday: 6자리 숫자 랜덤하게
age: 2자리 숫자 랜덤
company: [samsung, lg, hyundai]
해설
CREATE TABLE user (
id INT AUTO_INCREMENT PRIMARY KEY,
password VARCHAR(4),
name VARCHAR(3),
gender ENUM('male', 'female'),
email VARCHAR(15),
birthday CHAR(6),
age TINYINT,
company ENUM('samsung', 'lg', 'hyundai')
);
ENUM: enumeration
ENUM
타입의 필드는 사전에 정의된 문자열 값 목록 중 하나의 값을 가질 수 있습니다. 이러한 특성 덕분에 ENUM
은 특정 범위의 값만을 허용해야 하는 경우에 유용(2) board 테이블 만들기
id
title: 5글자
content: 10글자
likes: 1~100사이 숫자
img: “c”
created: 오늘 날짜
user_id (foreign_key)
해설
CREATE TABLE board (
id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(5),
content VARCHAR(10),
likes INT CHECK (likes BETWEEN 1 AND 100),
img CHAR(1) DEFAULT 'c',
created DATE DEFAULT CURRENT_DATE,
user_id INT,
FOREIGN KEY (user_id) REFERENCES user(id)
);
2) 실습 - mysqlsample data 분석
_files/Untitled.png)
기본 조회 및 필터링
고객 목록 조회: 모든 고객의 이름과 이메일을 조회하세요.
TABLE: customers
SELECT * FROM customers;
특정 제품 라인의 제품 조회: 'Classic Cars' 제품 라인에 속하는 모든 제품의 이름과 가격을 조회하세요.
TABLE: products, COL: productLine
SELECT * FROM products
WHERE productLine = 'Classic Cars';
최근 주문: 가장 최근에 주문된 10개의 주문을 주문 날짜(orderDate)와 함께 조회하세요.
TABLE: orders, COL: orderDate
SELECT * FROM orders
ORDER BY orderDate DESC
LIMIT 10;
최소 금액 이상의 결제: 100달러 이상 결제된 거래(amount)만 조회하세요.
TABLE: payments, COL: amount
SELECT * FROM payments
WHERE amount >= 100;
조인 쿼리
주문과 고객 정보 조합: 각 주문에 대한 주문 번호(orders-orderNumber)와 주문한 고객(customers-customerName)의 이름을 조회하세요.
SELECT o.orderNumber, c.customerName
FROM orders o
JOIN customers c ON o.customerNumber = c.customerNumber;
제품과 제품 라인 결합: 각 제품의 이름(products-productName)과 속한 제품 라인의 설명(productlines-textDescription)을 조회하세요.
SELECT p.productName, p.productLine, pl.textDescription
FROM products p
JOIN productlines pl ON p.productLine = pl.productLine;
직원과 상사 정보: 각 직원의 이름과 직속 상사의 이름을 조회하세요.
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;
특정 사무실의 직원 목록: 'San Francisco' 사무실에서 근무하는 모든 직원의 이름을 조회하세요.
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';
그룹쿼리
제품 라인별 제품 수: 각 제품 라인에 속한 제품의 수를 조회하세요.
SELECT productLine, COUNT(*) AS productCount
FROM products
GROUP BY productLine;
고객별 총 주문 금액: 각 고객별로 총 주문 금액을 계산하세요.
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;
가장 많이 팔린 제품: 가장 많이 판매된 제품의 이름과 판매 수량을 조회하세요.
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;
매출이 가장 높은 사무실: 가장 많은 매출을 기록한 사무실의 위치와 매출액을 조회하세요.
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;
서브쿼리
특정 금액 이상의 주문: 500달러 이상의 총 주문 금액을 기록한 주문들을 조회하세요.
SELECT orderNumber, SUM(quantityOrdered * priceEach) AS totalAmount
FROM orderdetails
GROUP BY orderNumber
HAVING totalAmount > 500;
평균 이상 결제 고객: 평균 결제 금액보다 많은 금액을 결제한 고객들의 목록을 조회하세요.
SELECT customerNumber, SUM(amount) AS totalPayment
FROM payments
GROUP BY customerNumber
HAVING totalPayment > (SELECT AVG(amount) FROM payments);
주문 없는 고객: 아직 주문을 하지 않은 고객의 목록을 조회하세요.
SELECT customerName
FROM customers
WHERE customerNumber NOT IN (SELECT customerNumber FROM orders);
최대 매출 고객: 가장 많은 금액을 지불한 고객의 이름과 총 결제 금액을 조회하세요.
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;
데이터 수정 및 관리
신규 고객 추가: '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);
제품 가격 변경: 'Classic Cars' 제품 라인의 모든 제품 가격을 10% 인상하는 쿼리를 작성하세요.
UPDATE products
SET buyPrice = buyPrice * 1.10
WHERE productLine = 'Classic Cars';
고객 데이터 업데이트: 특정 고객의 이메일 주소를 변경하는 쿼리를 작성하세요.
UPDATE customers
SET email = 'newemail@example.com'
WHERE customerNumber = 103;
직원 전보: 특정 직원을 다른 사무실로 이동시키는 쿼리를 작성하세요.
UPDATE employees
SET officeCode = '2'
WHERE employeeNumber = 1002;
