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

서브쿼리 : 쿼리 내부에 또 다른 쿼리가 작성되어 있는 쿼리이다.

• 값을 넣는 곳이면 어디든 사용이 가능하지만 WHERE, FROM, SELECT 위치에서 많이 사용한다.

WHERE 절에서의 서브쿼리 사용

• employee_number가 3138인 사원의 부서명, 부서전화번호, 부서주소를 조회하라.

SELECT *
FROM department
WHERE department_name = (
	SELECT department_name
    FROM employee
    WHERE employee_number = 3138
);

address가 부산광역시인 사원의 부서명, 부서전화번호, 부서주소를 조회하라.

SELECT * FROM department
WHERE department_name IN (
	SELECT department_name
	FROM employee
	WHERE address = '부산광역시'
);

FROM 절에서의 서브쿼리 사용

• address가 '부산'을 포함하는 사원중에서 부서가 null인 사원 전체 컬럼을 조회.

SELECT *
FROM (
	SELECT *
	FROM employee
	WHERE address LIKE '%부산%'
) AS T
WHERE department_name IS null;

SELECT *
FROM department AS D
INNER JOIN (
	SELECT *
    FROM employee
    WHERE name LIKE '%정%' 
) AS E
ON D.department_name = E.department_name;

SELECT 절에서의 서브쿼리 사용

• 모든 필드의 값이 동일하게 나와야 하기 때문에 값이 하나만 나와야 한다.
• 하나의 데이터를 추가해서 보여줄 때 많이 사용한다.
• 카운트, 총합, 보기 편하기 위해서 추가적인 데이터를 포함해서 볼 때
• 기능을 하기 위해서는 쓰이진 않는다.

SELECT department_name, (SELECT name FROM employee WHERE employee_number = 3138)
FROM department;

집계함수 : 테이블에서 집계결과를 나타내주는 함수이다. (SELECT절에서 사용)

COUNT : 레코드의 개수를 출력한다.

-행의 개수를 카운트.

SELECT count(*) FROM employee;

SELECT * FROM employee;
SELECT department_name FROM employee;
SELECT count(department_name) FROM employee; # null값을 제외하고 카운트 되어 13개가 나온다.

기획부 개수를 카운트.

SELECT count(*) 
FROM employee
WHERE department_name = '기획부';

SUM : 해당 컬럼의 합계

SELECT sum(department_name) FROM employee; # 합을 구할 수 없기 때문에 0이 나온다.

기획부 사원들의 사원번호의 합계

SELECT sum(employee_number) FROM employee WHERE department_name = '기획부';

SELECT sum(*) FROM employee;

• 컬럼을 기준으로 하기 때문에 무조건 하나의 컬럼이 있어야 한다.

SELECT sum(경도) FROM bbq;
SELECT sum(경도) FROM bbq WHERE 업소명 = '교촌치킨';

AVG : 해당 컬럼의 평균

SELECT avg(경도) FROM bbq;

MIN : 해당 컬럼의 최소값
MAX : 해당 컬럼의 최대값

SELECT min(위도), max(위도) FROM bbq;

SELECT * FROM bbq ORDER BY 업소명;

• BBQ 관련된 업소명을 모두 'BBQ치킨'으로 변경

UPDATE bbq SET 업소명 = 'BBQ치킨' WHERE 업소명 LIKE '%BBQ%' OR 업소명 LIKE '%비비큐%';

• BHC 관련된 업소명을 모두 'BHC치킨'으로 변경

UPDATE bbq SET 업소명 = 'BHC치킨' WHERE 업소명 LIKE '%BHC%' OR 업소명 LIKE '%B.H.C%';

SELECT * FROM bbq WHERE 업소명 IN('BBQ치킨', 'BHC치킨', '교촌치킨')
ORDER BY 업소명;

• GROUP BY : 조회된 결과에서 하나 이상의 레코드를 기준으로 그룹화하는 쿼리

SELECT 업소명, count(*) FROM bbq
WHERE 업소명 IN('BBQ치킨', 'BHC치킨', '교촌치킨')
GROUP BY 업소명;

• 조회된 결과에서 찾아야 하는데 소재지가 없기 때문에 에러가 뜨게 된다.

SELECT 업소명, count(*) FROM bbq
WHERE 업소명 IN('BBQ치킨', 'BHC치킨', '교촌치킨')
GROUP BY 소재지;

SELECT 업소명, 소재지, count(*) FROM bbq
WHERE 업소명 IN('BBQ치킨', 'BHC치킨', '교촌치킨')
GROUP BY 업소명, 소재지;

• HAVING : GROUP BY의 결과에서 필터링하고자 할 때 사용 가능

SELECT 업소명, count(*) AS 수 FROM bbq
GROUP BY 업소명
HAVING 수 >= 10;

BOARO-DML2

최신 게시물 리스트 (최신게시물 불러오기 SQL2)
게시물번호, 제목, 내용, 사진, 작성날짜및시간, 조회수
작성자프로필사진, 작성자닉네임
댓글수, 좋아요수

# board, user, favorite, comment
SELECT T1.board_number, T1.title, 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.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
ORDER BY T1.write_datetime DESC;

• 분리

SELECT 
	B.board_number, B.title, 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;

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;

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;

• 좋아요 탑3

SELECT T1.board_number, T1.title, 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.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
ORDER BY T3.favorite_count DESC
LIMIT 3;

정규화(Normalization)

• 제1정규형 (1NF) : 각 열(column)은 고유한 값(원자 값)만을 가진다. 즉, 하나의 열에는 하나의 값만 있어야 하며, 배열이나 리스트와 같은 복수의 값이 들어갈 수 없다.

• 제2정규형 (2NF) : 제1정규형을 만족하면서, 부분적 함수 종속성(partial functional dependency)을 제거한다. 즉, 테이블의 모든 속성이 기본키에 완전하게 종속되어야 합니다. 이는 테이블이 여러 키를 갖는 경우 중요하다.

• 제3정규형 (3NF): 제2정규형을 만족하면서, 이행적 함수 종속성(transitive functional dependency)을 제거한다. 이는 한 속성이 간접적으로 다른 속성에 종속되는 경우를 방지한다.

-정규화는 중복 데이터를 제거하고 데이터베이스 구조를 효율화하는 데 중요하지만, 모든 상황에 적합한 것은 아니다. 때로는 성능 향상이나 특정 쿼리를 간소화하기 위해 비정규화(denormalization)를 선택하는 것이 더 적합할 수 있다.