07/04

09 서브쿼리

10 테이블 연관관계
1:n 관계 - n쪽에 foreign 키가 있고, n이 자식이고, 연관관계의 주인임.

n대n관계

상품 재고수량 | 유저id
바나나 50 ssar
딸기 40 mas

중간테이블이 필요하다.
기본키 + 외래키 모두 + (생각해야하는 항목)

--

문제 풀이 및 실습

select week, sum(if(day='일', num_day, null)) '일'
from cal
group by week;

select * from student;
desc student;

-- 키를 160, 170, 180 이름을 나열해보세요.
select if(height< 170, name, null) '160',
 if(170<= height&& height<180, name, null) '170', 
 if( 180<=height, name, null) '180'  
from student;

select
if(height = 160, name, 0) '160',
if(height = 170, name, 0) '170',
if(height = 180, name, 0) '180'
from
(
select name, floor(height/10)*10 height
from student
) st;

select deptno 
from dept 
where loc = 'DALLAS';

select * from dept;


-- 서브 쿼리
select * from emp
where deptno = (select deptno from dept where loc = 'DALLAS');


select ename, sal*12 allsal
from emp
where allsal > 30000;

-- 인라인 뷰(테이블에서 특정 조건을 만족하는 테이블을 출력한 결과를
가상의 테이블로 만들기)
select * from
(select ename, sal*12 allsal
from emp) e
where e.allsal > 30000;

-- 스칼라 서브쿼리(단일 열 또는 단일 행 출력)
select round(avg(sal)) from emp;

select ename, sal, (select round(avg(sal)) from emp) as '월급평균'
from emp;

drop table emp_tb;

10장 테이블 연관관계 실습

CREATE TABLE user_tb(
id int primary key auto_increment,
username varchar(20),
password varchar(20),
profile_url varchar(100),
email varchar(100),
createDate TIMESTAMP
);
CREATE TABLE image_tb(
id int primary key auto_increment,
photo_url varchar(100),
content varchar(1000),
user_id int,
createDate TIMESTAMP
);
CREATE TABLE love_tb(
id int primary key auto_increment,
image_id int,
user_id int,
createDate TIMESTAMP
);
CREATE TABLE reply_tb(
id int primary key auto_increment,
content varchar(100),
user_id int,
image_id int,
createDate TIMESTAMP
);

INSERT INTO user_tb(username, password, profile_url, email, createDate)
VALUES('ssar', '1234' , '/profile/1.jpg', 'ssar@nate.com', now());
INSERT INTO user_tb(username, password, profile_url, email, createDate)
VALUES('cos', '1234' , '/profile/2.jpg', 'cos@nate.com', now());
INSERT INTO user_tb(username, password, profile_url, email, createDate)
VALUES('love', '1234' , '/profile/3.jpg', 'love@nate.com', now());


select * from user_tb;


INSERT INTO image_tb(photo_url, content, user_id, createDate)
values('/image/1.jpg', '여행사진', 1, now());
INSERT INTO image_tb(photo_url, content, user_id, createDate)
values('/image/2.jpg', '강아지사진', 1, now());
INSERT INTO image_tb(photo_url, content, user_id, createDate)
values('/image/3.jpg', '친구사진', 2, now());

INSERT INTO love_tb(user_id, image_id, createDate)
values(1, 1, now());
INSERT INTO love_tb(user_id, image_id, createDate)
values(2, 2, now());
INSERT INTO love_tb(user_id, image_id, createDate)
values(2, 3, now());
INSERT INTO love_tb(user_id, image_id, createDate)
values(3, 3, now());

INSERT INTO reply_tb(content, user_id, image_id, createDate)
VALUES('재밌겠다~~!', 1, 1, now());

select *
from image_tb img
inner join user_tb u on img.user_id = u.id;

select * from love_tb;
select * from reply_tb;

-- pk는 화면에보이지 않아도 들고가야한다. 테이블에서 id

select * from image_tb where id = 1;

select * 
from image_tb it inner join user_tb ut
on it.user_id = ut.id;

select * 
from image_tb it inner join user_tb ut
on it.user_id = ut.id
where it.id = 1;

-- 바로 위에 것보다 연산을 덜 하므로 속도가 빨라짐. 
select *
from (select * from image_tb where id = 1) it inner join user_tb ut
on it.user_id = ut.id;


select *
from reply_tb rt right outer join image_tb it
on rt.image_id = it.id;

--> --- ( reft/right ) outer join ---  좌우 기준으로 다 보여주고 싶은 테이블을 정한다.

--> inner 조인 vs outer 조인
inner 조인은 조인하는 테이블 양쪽에 일치하는 레코드가 있는 행만 표시하며, 
outer 조인은 right outer 조인을 예시로 들면 
right outer 조인은 오른쪽 테이블의 경우, 데이터가 일치하지 않더라도
모든 데이터를 표시하고, 왼쪽 테이블은 일치하는 값은 표시, 일치하지 않는
데이터는 NULL값으로 표시한다.

--> reft outer 조인 vs Full outer 조인

래프트 아우터 조인은 출력 결과의 튜플 수가 래프트(기준) 테이블의 튜플 수와 같습니다. 즉, 래프트(기준) 테이블의 모든 튜플이 결과에 포함됩니다.
풀 아우터 조인은 출력 결과의 튜플 수가 조인하는 테이블들 중 튜플의 수가 가장 많은 테이블의 튜플 수와 같습니다. 즉, 양쪽 테이블 중 튜플 수가 더 많은 테이블의 튜플 수와 일치합니다.
따라서, 래프트 아우터 조인은 래프트 테이블의 튜플 수와 같은 특징을 가지고 있으며, 풀 아우터 조인은 조인하는 테이블들 중 튜플 수가 많은 테이블의 튜플 수와 같은 특징을 가지고 있습니다.
mysql에서는 Full outer조인을 직접 지원하지 않음. union등으로 구현가능.

select
image.id image_id,
image.content image_content,
image.photo_url image_photo_url,
image_user.username image_user_username,
image_user.profile_url image_user_profile_url,
reply_user.username reply_user_username,
reply.content reply_content
from image_tb image
left outer join reply_tb reply on image.id = reply.image_id
left outer join user_tb image_user on image.user_id = image_user.id
left outer join user_tb reply_user on reply.user_id = reply_user.id
left outer join love_tb love on image.id = love.image_id;

-->
on 조건절에는 열의 별칭이 아닌 실제 열의 이름이 입력되어야한다.
실행순서가 select보다 앞서기 때문에 열의 별칭을 입력하면 이해하지 못해서 에러가 발생한다.