Join - 기본 셋팅

create database metadb;

use metadb;

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());

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 user_tb;
select * from image_tb;
select * from love_tb;
select * from reply_tb;

연습하기

• 여행사진을 올린 사람의 아이디를 알고 싶을 때

use metadb;
select * from user_tb;

select * from image_tb;
select user_id from image_tb where content = '여행사진';

select username
from user_tb
where id = (select user_id from image_tb where content = '여행사진');

• 가장 회원 가입을 늦게 한 유저 네임을 알고 싶을 때

select max(id) from user_tb;

• 이미지 옆에 좋아요 갯수를 알고 싶을 때

select 1, count(*)
 from love_tb
 where image_id = 1
union all
select 2, count(*) 
from love_tb 
where image_id = 2
union all
select 3, count(*) 
from love_tb 
where image_id = 3;

select 1 image_id, count(*) love_count
from love_tb
where image_id = 1
union all
select 2, count(*)
from love_tb
where image_id = 2
union all
select 3, count(*)
from love_tb
where image_id = 3;

Mylove	N
image_id	love_count
1	1
2	1
3	2

 

 

1. 랜덤 엑세스

2. 드라빙은 풀스캔

3. 인라인뷰 (FROM에 걸림)

select id, username, substr(email, 1, 4) my_email
from user_tb
where my_email = 'ssar';

select *
from (
	select id, username, substr(email, 1, 4) my_email
	from user_tb
) love_tb
where my_email = 'ssar';

4. inner join

select *
from
(
	select 1 image_id, count(*) love_count
	from love_tb
	where image_id = 1
	union all
	select 2, count(*)
	from love_tb
	where image_id = 2
	union all
	select 3, count(*)
	from love_tb
	where image_id = 3
) lo inner join image_tb im on im.id = lo.image_id;

select im.* , 3 love_count
from image_tb im;

5. 스칼라 서브 쿼리(SELECT에 걸림)

select im.*, 3 love_count
from image_tb im;

select *, (select username from user_tb where id = 1)
from image_tb;

select *
from love_tb
where image_id = 3;

select *, 
(select count(*) from love_tb where image_id = 3)
from image_tb;

6.그룹핑(Grouping) : 그룹으로 묶음

• DB : 가로연산은 되는데 세로 연산이 안됨

• GROUP BY는 세로 연산이 가능함

• 그룹핑 > 무조건 찌끄러뜨림 > 셀렉트

• 그룹핑을 하면 무조건 찌끄러뜨려야함 아니면 실행 안됨!

7. SQL 쿼리의 실행 순서