1. 사용자 생성 (root 접속)

CREATE USER 'insta'@'%' IDENTIFIED BY '1234';
GRANT CREATE, SELECT, INSERT, UPDATE, DELETE ON *.* TO 'insta'@'%';
GRANT CREATE ON *.* TO 'insta'@'%';

2. 테이블 생성

create database insta;
use insta;

CREATE TABLE member (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(20),
    password VARCHAR(20),
    user_img VARCHAR(100),
    email VARCHAR(100),
    create_date DATETIME,
    update_date DATETIME
) ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;

CREATE TABLE image (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    content VARCHAR(1000),
    img_url VARCHAR(100),
    user_id BIGINT,
    create_date DATETIME,
    update_date DATETIME
) ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;

CREATE TABLE favorite (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    image_id BIGINT,
    user_id BIGINT,
    create_date DATETIME,
    update_date DATETIME
) ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;

CREATE TABLE tag (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    content VARCHAR(30),
    image_id BIGINT,
    create_date DATETIME,
    update_date DATETIME
) ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;

3. 더미데이터 넣기

INSERT INTO member(username, password, user_img, email, create_date, update_date)
VALUES
('ssar', '1234', NULL, '[email protected]', NOW(), NOW()),
('cos',  '1234', NULL, '[email protected]',  NOW(), NOW()),
('love', '1234', NULL, '[email protected]', NOW(), NOW());

INSERT INTO image (img_url, content, user_id, create_date, update_date)
VALUES ('D드라이브', '여행사진', 1, NOW(), NOW());

INSERT INTO image (img_url, content, user_id, create_date, update_date)
VALUES ('C드라이브', '강아지사진', 1, NOW(), NOW());

INSERT INTO image (img_url, content, user_id, create_date, update_date)
VALUES ('E드라이브', '친구사진', 2, NOW(), NOW());

INSERT INTO favorite (user_id, image_id, create_date, update_date)
VALUES (1, 1, NOW(), NOW());

INSERT INTO favorite (user_id, image_id, create_date, update_date)
VALUES (2, 2, NOW(), NOW());

INSERT INTO favorite (user_id, image_id, create_date, update_date)
VALUES (2, 3, NOW(), NOW());

INSERT INTO favorite (user_id, image_id, create_date, update_date)
VALUES (2, 1, NOW(), NOW());
INSERT INTO favorite (user_id, image_id, create_date, update_date)
VALUES (3, 1, NOW(), NOW());

INSERT INTO tag (content, image_id, create_date, update_date)
VALUES ('가족여행', 1, NOW(), NOW());

4. 스칼라 서브쿼리 활용

select * from member;
select * from image;
select * from favorite;
select * from tag;

select id, img_url, content, user_id, 
3 as '이미지를 좋아요한 개수', 
'ssar' as '이미지 생성한사람',
'가족여행' as 태그
from image e;

-- 스칼라 서브쿼리로 해결하기
select id, img_url, content, user_id, 
(select count(image_id)
from favorite
where image_id = e.id) as '이미지를 좋아요한 개수', 
(select username
from member
where id = e.user_id) as '이미지 생성한사람',
'가족여행' as 태그
from image e;

select count(image_id)
from favorite
where image_id = 1;

select username
from member
where id = 1;