1️⃣ DB 서버 설치
🧩 apt update
🧩 apt install -y mariadb-server
2️⃣ MariaDB 설정 변경 (전체 IP 허용)
🛠 vi /etc/mysql/mariadb.conf.d/50-server.cnf
📝 27번 줄 수정 →
bind-address = 0.0.0.0
3️⃣ MariaDB 재시작
🔄 systemctl restart mariadb
4️⃣ 실행 상태 및 포트 확인
🔍 systemctl status mariadb
🔍 apt install -y net-tools
🔍 netstat -anlp | grep :3306
✅ 3306 포트가 열려 있어야 접속 가능
5️⃣ 보안 설정 (root 계정 초기화)
🔐 mysql_secure_installation
입력 순서:
엔터 → n → Y → 암호 → 암호 → Y → Y → Y → Y
6️⃣ DB 및 사용자 생성
🔑 mariadb -u root -p 접속 후 아래 순서대로 입력
- CREATE DATABASE black;
- CREATE USER 'sjb'@'%' IDENTIFIED BY '암호';
- GRANT ALL PRIVILEGES ON black.* TO 'sjb'@'%';
- FLUSH PRIVILEGES;
- SELECT user, host FROM mysql.user;
- exit
👤 6. 사용자 추가 및 DB 생성
🛠️ mariadb -u root -p
👤 CREATE USER 'root'@'%' IDENTIFIED BY 'qwer1234';
🔍 SELECT user, host FROM mysql.user;
🔑 GRANT ALL PRIVILEGES ON *.* TO 'root'@'%';
♻️ FLUSH PRIVILEGES;
📌 설명
┗ 👤 계정 생성
┗ 🔍 생성된 계정 확인
┗ 🔑 권한 부여
┗ ♻️ 권한 적용
💾 DB 생성 후 MySQL 연동
📁 CREATE DATABASE mydb;
🔗 USE mydb;
⚙️ 연동 예시 (Spring 기준)
username: root
password: 암호
리눅스 가상 머신 MariaDB와 윈도우즈 PC MySQL 연결
📄 SELECT 문 연습
🧱 기본 문법
🔍 SELECT [열]
📁 FROM [테이블]
🧾 WHERE [조건]
🍳 1. 특정 단어가 포함된 제목 조회
SELECT film_id, title, length FROM film WHERE title LIKE '%EGG%';
🟢 설명: EGG라는 단어가 어디든 포함된 title 조회
🥚 2. 특정 단어로 시작하는 제목 조회
SELECT film_id, title, length FROM film WHERE title LIKE 'EGG%';
🟢 설명: EGG로 시작하는 title만 조회
⏱️ 3. 길이 조건에 맞는 영화 조회
SELECT film_id, title, length FROM film WHERE length >= 120 AND length < 150;
🟢 설명: 영화 길이가 120분 이상 150분 미만인 film 목록 조회
🎞️ 4. 특정 rental_id의 결제 내역 조회
SELECT * FROM payment WHERE rental_id = 709;
🟢 설명: 709번 영화를 빌린 결제 내역 조회
🔗 테이블 간 관계에서 다른 테이블의 컬럼 불러오기
🧩 조인 종류
- 🔗 JOIN, INNER JOIN → 자주 사용됨 (⭐ 빠르고 안정적)
- 🧵 UNION, 서브쿼리 → 가능은 하지만 성능 저하 우려 있음
🎬 1. 709번 영화를 빌려간 내역 조회
SELECT * FROM payment INNER JOIN rental ON payment.rental_id = rental.rental_id INNER JOIN inventory ON rental.inventory_id = inventory.inventory_id WHERE film_id = 709;
🟢 설명: film_id가 709인 영화를 빌린 결제 내역 조회
💰 2. 결제 금액이 5달러 이상인 영화 제목 조회
SELECT film.title FROM payment INNER JOIN rental ON payment.rental_id = rental.rental_id INNER JOIN inventory ON rental.inventory_id = inventory.inventory_id INNER JOIN film ON inventory.film_id = film.film_id WHERE payment.amount >= 5;
🟢 설명: amount >= 5인 결제에 해당하는 영화 제목 조회
🎥 3. Action 장르의 영화 제목 조회
SELECT title FROM film INNER JOIN film_category ON film.film_id = film_category.film_id INNER JOIN category ON film_category.category_id = category.category_id WHERE name = 'Action';
🟢 설명: category.name이 'Action'인 영화 제목을 조회
→ 조인을 통해 카테고리 이름 기준으로 필터링
🔎 조인 꿀팁
- INNER JOIN은 꼭 바로 인접한 테이블끼리 하지 않아도 됨
- 앞에서 조인한 결과를 다음 조인의 조건으로 쓸 수 있음
- 즉, 조인 순서보단 조인 조건의 정확성이 더 중요함 💡
🐾 보호소에서 중성화한 동물 조회
🔍 조건 키워드
- 🔎 LIKE → 포함된다
- 🚫 NOT LIKE → 포함되지 않는다
🧬 문제 핵심 요약
🏥 입소 당시 상태는 Intact
🏡 퇴소 시 상태는 Spayed 또는 Neutered
→ 즉, 입소 땐 중성화 X → 퇴소 시 중성화 완료된 경우
🧾 SQL 문
SELECT A.ANIMAL_ID, A.ANIMAL_TYPE, A.NAME
FROM ANIMAL_INS AS A JOIN ANIMAL_OUTS AS B ON A.ANIMAL_ID = B.ANIMAL_ID
WHERE A.SEX_UPON_INTAKE LIKE "%Intact%" AND
(B.SEX_UPON_OUTCOME LIKE "%Spayed%" OR B.SEX_UPON_OUTCOME LIKE "%Neutered%")
ORDER BY A.ANIMAL_ID ASC;
🟢 설명
| A.SEX_UPON_INTAKE LIKE "%Intact%" | 입소할 때 중성화되지 않은 동물 |
| B.SEX_UPON_OUTCOME LIKE "%Spayed%" OR "%Neutered%" | 퇴소할 때 중성화된 동물 |
| JOIN | 입소 기록과 퇴소 기록을 ANIMAL_ID로 연결 |
| ORDER BY A.ANIMAL_ID ASC | 결과를 동물 ID 기준 오름차순 정렬 |
📦 rental 테이블 정렬 & NULL 조회 정리
🔽 1. 반납일 기준 내림차순 정렬
🧾 SELECT * FROM rental ORDER BY return_date DESC;
📌 return_date를 기준으로 최신 반납 순으로 정렬
🧾 2. 반납일 기준 + rental_id 오름차순 정렬
🧾 SELECT * FROM rental ORDER BY return_date DESC, rental_id ASC;
📌 정렬 우선순위
1️⃣ return_date는 내림차순
2️⃣ 같은 날짜일 경우 rental_id는 오름차순
❓ 3. 반납일이 없는 데이터 (NULL값) 조회
🧾 SELECT * FROM rental WHERE return_date IS NULL;
📌 IS NULL은 **비어있는 값(null)**을 찾을 때 사용
🔍 주의: = NULL 은 작동안함! 꼭 IS NULL 사용해야 함
📄 프로그래머스 문제 + 날짜 필터링 SQL 정리
🔍 1. 프로그래머스 - 없어진 기록 찾기
SELECT B.ANIMAL_ID, B.NAME
FROM ANIMAL_OUTS AS B
LEFT OUTER JOIN ANIMAL_INS AS A
ON A.ANIMAL_ID = B.ANIMAL_ID
WHERE A.ANIMAL_ID IS NULL
ORDER BY B.ANIMAL_ID ASC, B.NAME ASC;
📌 설명
- 🔗 LEFT OUTER JOIN: B 테이블(퇴소기록)을 기준으로 A 테이블(입소기록)과 연결
- ❓ A.ANIMAL_ID IS NULL: 입소기록이 없는 경우만 조회
- 📊 정렬: 동물 ID 오름차순, 이름 오름차순
🕓 2. 특정 날짜에 빌린 내역 조회 (2005년 5월 26일)
🧾 SELECT rental_date FROM rental WHERE rental_date LIKE '%2005-05-26%';
📌 설명
- 📅 LIKE로 날짜 문자열에 "2005-05-26"이 포함된 데이터 조회
🕘 3. 2005년 이후 대여 기록 조회
🧾 SELECT * FROM rental WHERE rental_date > NOW() - INTERVAL 20 YEAR;
📌 설명
- ⏳ 현재 날짜 기준 20년 이내 대여기록
- NOW()는 현재 시간, INTERVAL 20 YEAR는 20년 전 날짜
→ 즉, 2005년 이후 데이터만 조회
🔍 INTERVAL이란?
- INTERVAL은 날짜/시간에 덧셈(+) 또는 뺄셈(-) 을 할 때 사용되는 키워드
- MySQL, PostgreSQL, Oracle 등에서 날짜 계산에 쓰임
📅 다양한 사용 예시
문법 설명
| INTERVAL 10 DAY | 10일 |
| INTERVAL 3 MONTH | 3개월 |
| INTERVAL 1 YEAR | 1년 |
| INTERVAL 2 HOUR | 2시간 |
| INTERVAL 30 MINUTE | 30분 |
| INTERVAL 15 SECOND | 15초 |
🗓️ 2005년 6월 데이터 조회 & 날짜 형식 변경
🔍 1. 2005년 6월 데이터 조회 (부등호 활용)
🧾 SELECT * FROM rental WHERE rental_date > '2005-06-01 00:00:00' AND rental_date < '2005-06-30 23:59:59';
📌 설명
- 📅 '2005-06-01'부터 '2005-06-30'까지 대여된 데이터 조회
- 🔢 **부등호(>, <)**로 날짜 범위 필터링
🗂️ 2. 월만 추출해서 출력 (%m월 사용)
🧾 SELECT rental_date, DATE_FORMAT(rental_date, "%m월") FROM rental WHERE rental_date > '2005-06-01 00:00:00' AND rental_date < '2005-06-30 23:59:59';
📌 설명
- 🧾 DATE_FORMAT() 함수 사용
- 📆 대여일을 "06월"처럼 출력
🧾 3. as를 활용한 열 이름 변경
🧾 SELECT rental_date, DATE_FORMAT(rental_date, "%Y년 %m월") AS date FROM rental WHERE rental_date > '2005-06-01 00:00:00' AND rental_date < '2005-06-30 23:59:59';
📌 설명
- 📅 날짜를 "2005년 06월" 형태로 출력
- 🏷️ AS로 열 이름을 date로 바꿔서 출력
💬 프로그래머스 - 조건에 부합하는 중고거래 댓글 조회하기
🧾 SQL 코드
FROM USED_GOODS_BOARD AS A
JOIN USED_GOODS_REPLY AS B ON A.BOARD_ID = B.BOARD_ID
WHERE DATE_FORMAT(A.CREATED_DATE, "%Y-%m") = '2022-10'
ORDER BY B.CREATED_DATE ASC, A.TITLE ASC;
📌 설명
- 🔗 JOIN: 게시글과 댓글을 BOARD_ID 기준으로 연결
- 📅 DATE_FORMAT(A.CREATED_DATE, "%Y-%m") = '2022-10': 2022년 10월 작성된 게시글만 조회
- 📊 ORDER BY: 댓글 작성일 오름차순 → 제목 오름차순
📚 추가 정리: GROUP BY & 집계함수
🧱 대표 집계 함수
- SUM(): 합계
- COUNT(): 개수
- AVG(): 평균
📊 장르별 영화 수 조회
🔗 category → film_category → film 테이블을 조인
SELECT COUNT(film.title), category.name FROM category INNER JOIN film_category ON category.category_id = film_category.category_id INNER JOIN film ON film_category.film_id = film.film_id GROUP BY category.name;
📌 GROUP BY category.name 으로 장르별로 묶어서 영화 수 집계
🧾 도시별 고객 수 + 조건 필터링
SELECT COUNT(customer.customer_id) AS count, city_id FROM customer INNER JOIN address ON customer.address_id = address.address_id GROUP BY address.city_id;
📌 각 city_id별 고객 수를 셈
👉 조건 추가:
HAVING count = 2 → 고객 수가 2명인 도시만 조회
📚 프로그래머스 - 카테고리 별 도서 판매량 집계하기
🧾 SQL 코드
📌 설명
- 📘 BOOK 테이블에서 카테고리 정보 가져옴
- 📈 BOOK_SALES 테이블에서 판매량(SALES) 정보 가져옴
- 🔗 JOIN: BOOK_ID로 두 테이블 연결
- 📅 DATE_FORMAT: 판매일자에서 '2022-01'에 해당하는 데이터만 조회
- ➕ SUM(B.SALES): 카테고리별 총 판매량 집계
- 🧾 GROUP BY A.CATEGORY: 카테고리별로 묶음
- 🔠 ORDER BY A.CATEGORY ASC: 카테고리 이름 오름차순 정렬
💸 프로그래머스 - 조건에 맞는 사용자와 총 거래금액 조회하기
🧾 SQL 코드
📌 설명
- 🔗 JOIN: 게시글(BOARD)과 사용자(USER)를 WRITER_ID와 USER_ID로 연결
- ✅ WHERE A.STATUS = 'DONE': 거래 완료된 게시글만 필터링
- ➕ SUM(A.PRICE): 사용자별 총 거래 금액 집계
- 🧾 GROUP BY B.USER_ID: 사용자 단위로 그룹핑
- 🎯 HAVING SUM(A.PRICE) >= 700000: 총 거래금액이 70만 원 이상인 사용자만 필터링
- 🔢 ORDER BY SUM(A.PRICE) ASC: 총 거래금액 기준 오름차순 정렬
🐟 특정 조건을 만족하는 물고기별 수와 최대 길이 구하기
🧾 SQL 코드
📌 설명
- 🐠 FISH_TYPE별로 그룹화 (GROUP BY)
- 🔢 COUNT(1) → 물고기 수 (별도 컬럼 아니어도 1로 개수 셈)
- 📏 MAX(LENGTH) → 가장 큰 물고기 길이
- 🧮 AVG(IFNULL(LENGTH, 10)) → LENGTH가 NULL이면 10으로 대체해서 평균 계산
- 🎯 HAVING ... >= 33 → 평균 길이가 33 이상인 물고기 종류만 출력
- 🔠 ORDER BY FISH_TYPE ASC → 물고기 종류 알파벳 오름차순 정렬
🎯 LIMIT & IN & 서브쿼리 & 집계 함수 정리
🔢 1. LIMIT: 출력 결과 개수 제한
- 💰 가장 비싼 결제 1건 조회
SELECT * FROM payment ORDER BY amount DESC LIMIT 1; - ⏱ 가장 최근 반납한 rental 내역 조회
SELECT * FROM rental ORDER BY return_date DESC LIMIT 1; - 📄 11번째부터 5개 조회 (페이징)
SELECT * FROM payment ORDER BY amount DESC LIMIT 10, 5; - 🟦 1페이지 (1~10)
SELECT * FROM payment LIMIT 0, 10; - 🟦 2페이지 (11~20)
SELECT * FROM payment LIMIT 10, 10;
✅ 2. IN: 여러 개 값 중 포함 여부 확인
- 🧾 특정 ID 목록 조회
SELECT * FROM payment WHERE payment_id IN (1, 3, 5, 6, 9);
🔁 3. 서브쿼리: 쿼리 안에 쿼리 넣기
- 🎬 rental_id 조회 → rental 테이블 조회
SELECT * FROM rental WHERE rental_id IN (SELECT rental_id FROM payment WHERE amount >= 5); - 📊 평균 결제 금액 이상 조회
SELECT * FROM payment WHERE amount >= (SELECT AVG(amount) FROM payment); - 🧪 FROM 절에도 서브쿼리 가능
SELECT customer_id, staff_id FROM (SELECT * FROM payment) AS table_b;
📌 4. 기타
- 📊 평균 결제 금액
SELECT AVG(amount) FROM payment; - 🧼 중복 제거
SELECT DISTINCT customer_id FROM payment;
📊 프로그래머스 - 상품을 구매한 회원 비율 구하기
🧾 SQL 코드
sql
복사편집
SELECT YEAR(SALES_DATE) AS YEAR,
MONTH(SALES_DATE) AS MONTH,
COUNT(DISTINCT USER_ID) AS PURCHASED_USERS,
ROUND(COUNT(DISTINCT USER_ID) / (
SELECT COUNT(USER_ID)
FROM USER_INFO
WHERE YEAR(JOINED) = '2021'
), 1) AS PUCHASED_RATIO
FROM ONLINE_SALE
WHERE USER_ID IN (
SELECT USER_ID
FROM USER_INFO
WHERE YEAR(JOINED) = '2021'
)
GROUP BY YEAR(SALES_DATE), MONTH(SALES_DATE)
ORDER BY YEAR(SALES_DATE) ASC, MONTH(SALES_DATE) ASC;
📌 설명
- 📆 YEAR(SALES_DATE), MONTH(SALES_DATE)
- → 구매가 발생한 연/월로 그룹핑
- 👥 COUNT(DISTINCT USER_ID)
- → 해당 월에 구매한 회원 수
- 🧾 USER_ID IN (SELECT ... FROM USER_INFO WHERE YEAR(JOINED) = '2021')
- → 2021년에 가입한 사용자들만 필터링
- 📉 ROUND(..., 1)
- → 전체 가입자 중 구매자 비율을 소수점 첫째 자리까지 반올림
- 🔢 GROUP BY + ORDER BY
- → 월별로 정렬된 구매율 통계
🗑️ DELETE 문
🧾 DELETE FROM rental WHERE rental_id = 10;
📌 설명
- rental 테이블에서 rental_id = 10인 데이터를 삭제
- ✏️ 문법: DELETE FROM [테이블명] WHERE [조건];
🛠️ UPDATE 문
🧾 UPDATE rental SET customer_id = 459 WHERE rental_id = 1;
📌 설명
- rental_id = 1인 행의 customer_id를 459로 수정
- ✏️ 문법: UPDATE [테이블명] SET [열=값] WHERE [조건];
➕ INSERT 문
🧾 단일 삽입
INSERT INTO rental (rental_date, inventory_id, customer_id, staff_id) VALUE ('2025-06-09 17:12:00', 367, 459, 1);
📌 설명
- rental 테이블에 해당 값들을 하나의 행으로 삽입
- ✏️ 문법: INSERT INTO [테이블명] (열1, 열2, ...) VALUE (...);
🧾 다중 삽입
INSERT INTO rental (rental_date, inventory_id, customer_id, staff_id) VALUES ('2025-06-09 17:12:00', 367, 459, 1), ('2025-06-09 17:12:00', 367, 459, 2), ('2025-06-09 17:12:00', 367, 459, 3);
📌 설명
- VALUES를 활용해 여러 행을 한 번에 삽입 가능
- 효율적인 대량 데이터 입력 방식
'Database' 카테고리의 다른 글
| Database 6일차 (0) | 2025.06.16 |
|---|---|
| Database 5일차 (2) | 2025.06.13 |
| Database 4일차 (0) | 2025.06.12 |
| Database 3일차 (1) | 2025.06.11 |
| Database 1일차 (7) | 2025.06.05 |