-- 샘플 DB 구성
create database if not exists library;
use library;
-- 도서관 시스템에 DB 구축
-- 학생 테이블
create table student(
id int primary key auto_increment,
name varchar(20) not null
);
-- 도서 테이블
create table book(
id int primary key auto_increment,
title varchar(50) not null
);
-- 대출 테이블
create table borrow(
id int primary key auto_increment,
student_id int not null,
book_id int not null,
foreign key(student_id) references student(id),
foreign key(book_id) references book(id)
);
-- 리뷰 테이블
create table review(
id int primary key auto_increment,
student_id int not null,
book_id int not null,
star int not null,
foreign key(student_id) references student(id),
foreign key(book_id) references book(id)
);
무관계/남남(Independent): 공통 부모만 공유할 뿐, 서로를 잇는 선이 아예 없음
예: 대출 ↔ 리뷰
주의: 이 '남남'인 관계를 한 쿼리에서 직접 JOIN 하면 데이터가 곱해지는 '행 곱셈' 대참사가 발생
DISTINCT 개념
-- 1단계 DISTINCT 개념 확인
select * from student;
select * from book;
select * from borrow;
select * from review;
-- 문제 : 학생들 중 책을 한번이라도 빌린 학생의 학번과 이름을 출력하시오.
select
s.id,
s.name
from student s
join borrow b on s.id = b.student_id;
-- 명단에 중복을 없애 주세요
-- DISTINCT 로 중복을 제거할 수 있다.
select
distinct s.id,
s.name
from student s
join borrow b on s.id = b.student_id;
-- 문제 2. 리뷰가 등록된 책 이름만 알고 싶다.
select
b.title
from book b
join review r on b.id = r.book_id;
select
distinct b.title
from book b
join review r on b.id = r.book_id
-- 2단계 count 와 count(distinct)
select * from student;
select * from book;
select * from borrow;
select * from review;
-- 상황 : 전체 대출 정보 출력(학생id, 학생이름, 책id, 책이름)
select
s.id as 학생id,
s.name as 학생이름,
b.id as 책id,
b.title as 책이름
from student s
left join borrow br on s.id = br.student_id
left join book b on br.book_id = b.id
order by s.name asc;
-- 전체 대출이 일어난 횟수만 알고싶다면
select count(*) as 총대출횟수 from borrow;
-- 대출한 학생수만 확인
select
count(distinct student_id) as 대출한학생수
from borrow;
-- distinct 는 문법적으로 select 절 바로 앞에만 사용할 수 있다.
-- 단 count함수 안에 distinct를 사용할 수 있다.
-- (뭉쳐진 바구니 안에 중복된 데이터는 제거하고 카운트 한다)
-- 대출 테이블에서 총 대출 횟수, 대출학생수 각각 출력하시오
select
count(*) as 총대출횟수,
count(distinct student_id) as 대출학생수
from borrow;
-- 각 도서별 대출 통계 (그룹바이사용)
use library;
select * from student;
select * from book;
select * from borrow;
select * from review;
-- 자바 기초 책이 몇 번 대출 되었을까? (각 책에 대한 대출 횟수, 대출 학생 수 출력)
select
b.title as 도서명,
br.book_id as 대출테이블에북아이디,
b.id as 책테이블에아이디
from book b
left join borrow br on b.id = br.book_id;
-- 추가적인 집계합수를 사용해 보자
select distinct
b.title as 도서명,
-- br.book_id as 대출테이블에북아이디,
-- b.id as 책테이블에아이디
count(br.student_id) as 총대출횟수,
count(distinct br.student_id) as 총대출학생수
from book b
left join borrow br on b.id = br.book_id
group by b.id, b.title;
select * from borrow order by book_id = 1;
-- 김철수 학생이 자바 기초 이전에 대출한 적이 있지만 한번 더 대출함
insert into borrow(student_id, book_id) values(1 ,1);
select * from borrow;
-- 데이터 삭제
delete from borrow where id = 8;
-- 행 별로 중복을 제고 하고 싶다면 distinct 사용하면 된다
-- 집계 함수 count 경우 중복된 값을 카운트에 제거하고 싶다면 distinct를 사용.
문제 1: 자바 기초 책이 몇 번 대출되었을까? (각 책에 대한 대출 횟수, 대출 학생 수 출력)
문제 2:추가적인 집계합수를 사용해 보자
행 곱셈 문제
-- 4단계 행 곱셈 문제
-- 서로 아무런 관련이 없는 두 테이블을 부모 테이블이
-- 같다고 해서 억지로 조인을 하게 되면 경우의 수 (데이터 뻥튀기)
-- 현상이 발생.
-- 도서별 대출 수와 리뷰 수를 한 번에 조회하고 싶다
select * from borrow where book_id = 1;
select * from review where book_id = 1;
-- 대출 테이블과 리뷰글은 서로 아무런 연결고리가 없는 상태
-- 단지 부모테이블 book_id 를 사용하고 있다
-- 두 테이블을 동시에 join하면 어떻게 될까?
select
br.id as borrow_id,
rv.id as review_id
from borrow br
left join review rv on br.book_id = rv.book_id
where rv.book_id = 1;
-- 대출이랑 리뷰 테이블 서로 아무 연결고리가 없는데
-- 누가 진짜 짝궁인지 모르니깐 그냥 1번 책에서 나온 기록끼리
-- 모든 조합을 다 짝지어주자
-- 3 * 3 = 9행 발생 -> 행 곱셈문제 발생
-- 데이터 뻥튀기 된 이후에 집계 함수 바로 사용하면 대 참사 발생
select
b.title as 도서명,
count(br.id) as 대출수_잘못됨,
count(rv.id) as 리뷰수_잘못됨
from book b
left join borrow br on b.id = br.book_id
left join review rv on b.id = rv.book_id
group by b.id, b.title;
-- 뻥튀기 데이터 해결
select
b.title as 도서명,
count(distinct br.id) as 대출수_정상,
count(distinct rv.id) as 리뷰수_정상
from book b
left join borrow br on b.id = br.book_id
left join review rv on b.id = rv.book_id
group by b.id, b.title;
