[20230804] 클라우드 플랫폼 기반 웹서비스 개발자 양성 과정 21일차.

INDEX

• 테이블에서 원하는 컬럼의 데이터를 빠르게 찾을 수 있도록 하는 스키마
• CREATE INDEX 인덱스명 ON 테이블명 (컬럼, ...);

CREATE INDEX bbq_업소명_idx ON bbq (업소명);
CREATE INDEX bbq_위치_idx ON bbq (위도, 경도);

SELECT * FROM bbq 
WHERE 업소명 = 'BBQ치킨';

board

ORDER BY T3.favorite_count DESC
LIMIT 3;

검색 결과 리스트 불러오기 SQL

SELECT T1.board_number, T1.title, T1.contents, T1.image_url, T1.write_datetime, T1.view_count,
	T1.profile_image, T1.nickname,
    T2.comment_count,
    T3.favorite_count
FROM (
	SELECT 
		B.board_number, B.title, B.contents, B.image_url, B.write_datetime, B.view_count,
		U.profile_image, U.nickname
	FROM board AS B
	INNER JOIN user AS U 
	ON B.writer_email = U.email
) AS T1 INNER JOIN
(
	SELECT B.board_number, count(C.user_email) AS comment_count
	FROM board AS B
	LEFT JOIN comment AS C
	ON B.board_number = C.board_number
    GROUP BY B.board_number
) AS T2
ON T1.board_number = T2.board_number
LEFT JOIN
(
	SELECT B.board_number, count(F.user_email) AS favorite_count
	FROM board AS B
	LEFT JOIN favorite AS F
	ON B.board_number = F.board_number
    GROUP BY B.board_number
) AS T3
ON T1.board_number = T3.board_number
WHERE T1.title LIKE '%3%'
ORDER BY T1.write_datetime DESC;

VIEW

• 읽기 전용의 가상의 테이블
• 물리적으로 존재하는 테이블이 아니기 때문에 입력, 수정, 삭제를 진행할 수 없음
• 자주 사용되는 복잡한 SELECT 쿼리를 미리 작성하여 테이블처럼 만들어 둔것
• CREATE VIEW 뷰이름 AS SELECT ...

CREATE VIEW board_view AS
SELECT T1.board_number, T1.title, T1.contents, T1.image_url, T1.write_datetime, T1.view_count,
	T1.profile_image, T1.nickname,
    T2.comment_count,
    T3.favorite_count
FROM (
	SELECT 
		B.board_number, B.title, B.contents, B.image_url, B.write_datetime, B.view_count,
		U.profile_image, U.nickname
	FROM board AS B
	INNER JOIN user AS U 
	ON B.writer_email = U.email
) AS T1 INNER JOIN
(
	SELECT B.board_number, count(C.user_email) AS comment_count
	FROM board AS B
	LEFT JOIN comment AS C
	ON B.board_number = C.board_number
    GROUP BY B.board_number
) AS T2
ON T1.board_number = T2.board_number
LEFT JOIN
(
	SELECT B.board_number, count(F.user_email) AS favorite_count
	FROM board AS B
	LEFT JOIN favorite AS F
	ON B.board_number = F.board_number
    GROUP BY B.board_number
) AS T3
ON T1.board_number = T3.board_number;

최신 게시물 불러오기 SQL 3

SELECT * FROM board_view
ORDER BY write_datetime DESC;

특정 유저 게시물 불러오기 SQL

SELECT * FROM board_view
WHERE nickname = 'nickname';