SQL 시리즈 (5장) 🪴

<SQL 첫걸음> 을 보고 직접 학습하고 기록하는 포스트! 🔥

DATABASE 마스터를 꿈꾸며...🔥

💚 5장 집계와 서브쿼리

💚 20강 COUNT로 행 개수 구하기

• 실제 연습을 위해 지난 프로젝트였던 hoxylush DB를 가지고 연습! 🦖
• 대표적인 집계함수는 5개! COUNT, SUM, AVG, MIN, MAX
• SQL은 데이터베이스라 불리는 '데이터 집합'을 다루는 언어. 이 같은 집합의 개수나 합계가 궁금하다면 SQL이 제공하는 집계함수를 사용해 간단하게 구할 수 있씀!

1. COUNT로 행 개수 구하기

• SQL은 집합을 다루는 집계함수를 제공함! -> 인수로 '집합'을 지정함!

💚 select count(*) from locations; 💚

-> 16개의 행 반환

• 집계함수의 특징은 복수의 값(집합)에서 하나의 값을 계산해내는 것.
• where 구 지정하기

💚 select count(*) from locations where name LIKE ='%역%'; 💚
# locations 테이블에서 '역'이 들어가있는 행들만 추출해서 몇개인지 세어보기! 
# 앞에서 like 배운거 응용했는데 ㅠㅠ 대박쓰 진짜 나옴..

2. 집계함수와 NULL 값

• COUNT의 인수로 열명을 지정할 수 있음. 열명을 지정하면 그 열에 한해서 행의 개수를 구할 수 있음! 특히, '*'를 인수로 사용할 수 있는 것은 COUNT 함수 뿐!
• 여기서! 👊NULL값을 어떻게 취급할것인가!!!!👊 En general, SQL에서는 NULL값을 고려해야함. 집계함수는 집합 안에 NULL값이 있을 경우 이를 제외하고 처리함.
• NULL이 있는 열명을 언급해서 count명령어로 불러내면 NULL값은 제외하고 계산됨. 하지만 count(*)의 경우 모든 열의 행수를 카운트하기 때문에 NULL값은 셈에 들어간다.
  
  
  
• 왜냐면 phone_number에는 null값이 19개나 되기때문에 이걸 다 제하고 8개만 카운트돼서 나오는 것!

3. DISTINCT로 중복제거

• 집합 안에 중복된 값이 있는지 여부가 문제될 때도 있음!
  
  -> 이런식으로 선릉역이 두개가 중복됨! 그럴 때 사용하는 것이 DISTINCT!
• DISTINCT는 예약어로 열명이 아님. select구에서 distinct를 지정하면 중복된 데이터를 제외한 결과를 클라이언트로 반환. 중복 여부는 select구에 지정된 모든 열을 비교해 판단!

💚 select all name from locations; 💚
💚 select distinct name from locations; 💚

신기해...! 🦖

4. 집계함수에서 DISTINCT

• SQL에서는 NULL을 처리하는 것이 무엇보다 중요!
• 그렇다면 !!! 열에서 NULL값도 제외하고, 중복하지않는 데이터를 구할 수는 없을까?
  -> 우리는 집계함수의 인수로 DISTINCT를 사용할 수 있어!

💚 select count(all phone_number), count(distinct phone_number) from users; 💚
# count all로 phone_number의 null값 제거,
# distinct에서 phone_number의 중복값 제거 + null 제거!

• 다시 말해 count(all...)은 null값만 제거해주고, count(distinct...)는 중복값제거+null제거라는 것 !

💚 21강 COUNT 이외의 집계함수

• 집계함수는 COUNT만 있는 것이 아니야! SUM 집계함수를 사용해 집합의 합계치를 구할 수 있씀!
• 최솟값, 최댓값을 찾는 경우에도 집계함수를 사용해 처리할 수 있뜸!

1. SUM으로 합계 구하기
   

💚 select sum*age) from owners; 💚

• SUM 집계함수에 지정되는 집합은 수치형 뿐! 문자열형이나 날짜시간형의 집합에서는 합계를 구할 수 없써...
• SUM 집계함수도 COUNT와 마찬가지로 NULL값을 무시함. NULL값을 제거한 뒤 합계를 냄.

2. AVG로 평균내기

• 아까 위에서 구한 SUM을 개수로 나누면 평균! -> SUM / COUNT
• 하지만 굳이, sum, count 쓰지 않더라도 AVG라는 집계함수를 통해 평균값을 구할 수 있음!
  

💚 select avg(age), sum(age)/count(age) from owners; 💚

• 근데 간단하게 요렇게만해도 나옴!
  
• AVG 집계함수도 NULL값은 무시. 즉, NULL값을 제거한 뒤 평균값을 계산.
• NULL을 0으로 간주해서 평균을 내고 싶다면 case를 사용해서 NULL을 0으로 변한한 뒤 AVG 사용하기!

3. MIN, MAX로 최솟값, 최댓값 구하기

• MIN, MAX도 집계함수! 이들 함수는 문자열형과 날짜시간형에도 사용가능!
• NULL값을 무시하는 기본규칙은 다른 집계함수와 같음!
  

💚 select min(running_time), max(running_time), min(release_date), max(release_date) from movies; 💚

• 신기하다! 진짜 날짜시간형에도 최솟값, 최댓값을 구할 수 있다니 🦖

💚 22강 그룹화 - GROUP BY

• GROUP BY를 사용해 그룹화하는 방법에 대해 알아보는 시간 🌱

💚 select * from 테이블명 group by 열1, 열2, ... 💚

1. group by로 그룹화

• 그룹으로 나눌 때 GROUP BY 구를 사용!
• tickets table로 group을 나눠봐야지!
  

💚 select seat_remain from tickets group by seat_remain; 💚

• group by 구에 열을 지정하여 그룹화하면 지정된 열의 값이 같은 행이 하나의 그룹으로 묶임! select 구에서 seat_remain 열을 지정했으므로 그룹화된 seat_remain 열의 데이터가 반환되는 것!

• group by 를 지정해 그룹화하면 distinct와 같이 중복을 제거하는 효과가 있씀!

• 😇 그렇다면! distinct로 중복을 제거하는 것과 group by로 그룹화하는 것은 어떤 차이가 있을까? 😇
  -> group by 구를 지정하는 경우에는 집계함수와 함께 사용하지 않으면 별 의미가 없슴.
  -> group by 구로 그룹화된 각각의 그룹이 하나의 집합으로서 집계함수의 인수로 넘겨지기 때문.

• group by를 사용하는 경우?
  -> 점포의 일별 매출 데이터가 중앙 판매 관리시스템에 전송되어 점포별 매출실적을 집계해 어떤 점포가 매출이 올라가는지, 어떤 상품이 인기가 있는지 등을 분석할 때.
  -> 여기서 점포별, 상품별, 월별, 일별 등 특정 단위로 집계할 때 group by를 자주 사용.

2. HAVING 구로 조건 지정

• 집계함수는 where 구의 조건식에서는 사용할 수 없음.
  
• seat_remain 열을 그룹화하여 행 개수가 하나만 존재하는 그룹을 검색하고 싶었지만 에러가 발생! 에러가 발생한 이유는? group by의 where구의 내부처리 순서와 관계가 있음!
• 즉, where 구로 행을 검색하는 처리가 group by로 그룹화하는 처리보다 순서상 앞서기 때문! (select 구에서 지정한 별명을 where 구에서 사용할 수 없었던 것과 같은 이유로 그룹화가 필요한 집계함수는 where 구에서 지정할 수 없음!)

🐤 내부처리순서: where구 -> group by 구 -> select 구 -> order by 구

• where 구에서는 집계함수(count, sum, avg, min, max)를 사용할 수 없음!
  
• 그럼 집계한 결과에서 조건에 맞는 값을 따로 걸러내려면? 🌱 HAVING 구를 활용! 🌱
• Having구는 group by 구 뒤에 기술, where 구와 동일하게 조건식을 지정할 수 있음.
• 조건식에는 그룹별로 집계된 열의 값이나 집계함수의 계산결과가 전달된다고 생각하면 됨.
• 위에 where구에 넣어주었던 조건식(에러난 것)을 having 구를 통해 바꿔보기!
  -> having 구에 집계함수를 넣어줌으로써 원하는 값을 출력할 수 있음!

🐤 having 구의 내부처리 순서: where 구 -> group by 구 -> having 구 -> select 구 -> order by 구

• select 구보다 먼저 처리되므로 having구에서 별명 사용 불가.
• 예를 들어, count에 total이라는 별명을 붙이면, order by구에서는 사용가능, 하지만 group by구나 having 구에서는 사용 불가.

3. 복수열의 그룹화

• group by 사용할 때 주의할 점! -> group by에 지정한 열 이외의 열은 집계함수를 사용하지 않은 채 select 구에 기술해서는 안됨!
• 무슨말이냐면~

![](https://velog.velcdn.com/images%2Fmajaeh43%2Fpost%2F024b6d02-563a-4cae-896d-7f299a0501ed%2Fimage.png)

• group by에서 지정한 열은 seat_remain이고, 그 밖에 나머지 열(price, departure_time열)은 집계함수를 사용하지 않았으니 select 구에 기술해서는 안된다는 말 !
  
• 요렇게 집계함수를 사용하면 집합은 하나의 값으로 계산되므로 그룹마다 하나의 행을 출력 가능 ! 이런식의 쿼리로는 문제 없이 출력 가능!
  -> group by 에서 지정한 열 이외의 열은 집계함수를 사용하지 않은 채 select 구에 지정할 수 없음!
  
• 이렇게 미리 group by에서 지정한 열인 경우에는 select 구에서도 사용 가능!

4. 결괏값 정렬

• group by로 그룹화해도 실행결과 순서를 정렬할 수는 없당! -> 이럴 때는 order by 구를 사용해 결과를 정렬할 수 있음! group by 구로 그룹화한 경우에도 order by 구를 사용해 정렬할 수 있음!
  
  -> 요렇게 sum(price)로 정렬해주니 깔끔 따로 desc 안해주면 기본값은 asc!

💚 23강 서브쿼리

• 서브쿼리는 select 명령에 의한 데이터 질의로, 상부가 아닌 하부의 부수적인 질의를 의미함. (무슨소리일까ㅏ...? 😇)
• 서브쿼리는 SQL 명령문 안에 지정하는 하부 select 명령으로 괄호로 묶어 지정함!
• 서브쿼리는 SQL 명령의 WHERE 구에서 주로 사용! where 구는 select, delete, update 구에서 사용할 수 있는데 이들 중 어떤 명령에서든 서브쿼리를 사용할 수 있음!

1. delete의 where구에서 서브쿼리 사용하기

• owners table으로 연습~! 🦖
  
• 첫번째 행을 삭제해보장. 누가봐도 첫번째 행의 age가 가장 작다.

💚 delete from owners where name = '박은혜'; 💚

• 삭제 완료 ! 근데 앞선 방법처럼 age가 가장 작은 행이 어떤 행인지 알때는 이렇게 바로 삭제가 가능한데, 그렇지 않을 때는 어떻게 찾아서 삭제할 수 있을까?? min을 써서 삭제가 가능한데 한 번 해볼까...? 🦖
  
• (방금 insert into를 통해 id=4인 데이터를 넣어주었당!)이제 id=2인 값(age가 최소인 값)을 삭제해볼까?
• 여기서 id=2인 행은 age 값이 가장 적다. 이 select 명령을 delete 명령의 where 구에서 사용하면 하나의 delete 명령으로 원하는 행을 삭제할 수 있음!

💚 delete from owners where age = (select min(age) from owners); 💚
# (select min(age) from owners) 이부분이 서브쿼리 형태.

• 이런 오류가 뜨는데! 그 이유는? 데이터를 추가하거나 갱신할 경우 동일한 테이블을 서브쿼리에서 사용할 수 없도록 되어 있기 때문이라네..?^^
• 에러 발생 안시키고 실행하려면 임시 테이블을 만들고 처리하면 된다고 함 !

2. 스칼라 값

• 서브쿼리를 사용할 때는 그 select 명령이 어떤 값을 반환하는지 주의할 필요가 있음!
• 여러가지 패턴 중에서도 다음과 같은 4가지가 일반적인 서브쿼리 패턴 !
  1) 하나의 값을 반환하는 패턴

💚 select min(running_time) from movies; 💚

2) 복수의 행이 반환되지만 열은 하나인 패턴

💚 select running_time from movies; 💚

3) 하나의 행이 반환되지만 열이 복수인 패턴

💚 select min(release_date), max(running_time) from movies; 💚

4)복수의 행, 복수의 열이 반환되는 패턴

💚 select release_date, running_time from movies; 💚

-> select 명령이 하나의 값만 반환하는 것을 '스칼라 값을 반환한다'고 한다!

• 스칼라 값을 반환하는 select 명령을 특별 취급하는 이유는 서브쿼리로서 사용하기 쉽기때문!

3. select 구에서 서브쿼리 사용하기

• 위에서처럼 where구에 서브쿼리를 사용함. 그 밖에도 서브쿼리는 select구, update의 set구 등 다양한 구 안에서 지정가능.
• select 구에서 서브쿼리를 지정할 때는 스칼라 서브쿼리가 필요함!

💚 select
	(select count(*) from movies) AS sql1,
    (select count(*) from actors) AS sql2; 💚

-> 각 테이블의 행 개수를 각 서브쿼리로 구해봄.

• 맨 위쪽 select 명령에는 from 구가 없다. MySQL 등에서는 실제로 FROM 구를 생략가능.

4. SET 구에서 서브쿼리 사용하기

• update의 set 구에서도 서브쿼리를 사용할 수 있음! 하지만 별로 많이 쓰이진 않음...^^

5. FROM 구에서 서브쿼리 사용하기

• FROM 구에 테이블 지정말고도 서브쿼리를 지정할 수 있씀! 괄호로 select 명령을 묶으면 됨!
• select구나 set구에서는 스칼라 서브쿼리를 지정해야 하지만 from구에 기술할 경우에는 스칼라 값을 반환하지 않아도 됨!

💚 select * from (select * from movies) sq; 💚

• select 명령 안에 select 명령이 들어있는 것처럼 보임. 이걸 '네스티드(nested) 구조', 또는 '중첩구조'나 '내포구조'라 부름ㅁ! sq는 테이블의 별명임.

6. insert명령과 서브쿼리

• insert명령과 서브쿼리를 조합해 사용가능. insert 명령에는 values구의 일부로 서브쿼리를 사용하는 경우와 values구 대신 select 명령을 사용하는 두 가지 방법이 있음.

💚 24강 상관 서브쿼리

• 서브쿼리를 사용해 delete 명령과 select 명령을 결합할 수 있씀! 서브쿼리의 일종인 '상관 서브쿼리'를 exists 술어로 조합시켜서 서브쿼리를 사용하는 방법에 대해 알아보자...!🦖
• EXISTS 술어를 사용하면 서브쿼리가 반환하는 결괏값이 있는지를 조사할 수 있음.. EXIST는 단지 반환된 행이 있는지를 확인해보고 값이 있으면 참, 없으면 거짓을 반환하기 때문에 꼭 스칼라 값을 반환할 필요는 없음 !

1. EXISTS

• 서브쿼리를 사용해 검색할 때 '데이터가 존재하는지 아닌지' 판별하기 위해 조건을 지정할 수 있음 !

💚 update movies SET title = '있음' where exists (select * from actors where first_name = '정'); 💚

-> ^^ 왜 이렇게 되었을까....? ^^ 분명 '정'이라는 단어가 있으면 title을 '있음'이라고 바꿔달라고 명령 내린건데 다 바뀌어버렸네...?^^
-> actors 테이블의 모든 열 중에서, first_name이 '정'인 데이터가 존재하면, movies 테이블의 title을 '있음'이라고 바꿔줘.... 어디서 오류가 났을까... ㅠㅠ 보류....!

2. NOT EXISTS

• '없음'의 경우, 행이 존재하지 않는 상태가 참이 되므로 이때는 NOT EXISTS를 사용.
  
  -> '정'이 없기때문에 어떤 변화도 일어나지 않았다고 한다...

3. 상관 서브쿼리

• 서브쿼리에는 명령 안에 중첩구조로 된 select 명령이 존재함.

💚 update movies SET title = '있음' where exists (select * from actors where first_name = '정'); 💚

• 여기서 보다시피 first_name = '정'에서 actor 테이블의 first_name 열값이 부모의 '정' 열값과 일치하는 행을 검색하는 것임. 이렇게 부모 명령과 자식인 서브 쿼리가 특정 관계를 맺는 것을 '상관 서브쿼리'라 부름

• 테이블명 붙이기
  그렇다면, 만약 두 열이 모두 같은 이름을 가진다면? where first_name = first_name이라면?
  -> 그러면 잘 동작하지 않을 것임. 그래서 여기서는 열이 어느 테이블의 것인지 명시적으로 나타낼 필요가 있음! 열명 앞에 ❣️'테이블명.'❣️을 붙이기만 하면 됨!

4. IN

• 스칼라 값끼리 비교할 때는 '=' 연산자를 사용. 다만 집합을 비교할 때는 사용 못해!
• IN을 사용하면 집합 안의 값이 존재하는지 조사할 수 있음!

💚 열명 IN (집합) 💚
💚 where no = 3 or no = 5; 💚

• IN과 NULL
  집계함수에서는 집합 안의 NULL 값을 무시하고 처리했음. 하지만 IN에서는 집합안에 NULL값을 무시하지 않음~! 중요한건 NULL을 비교하려면 IS NULL을 사용해야함! 또한, NOT IN의 경우, 집합 안에 NULL값이 있으면 왼쪽 값이 집합 안에 포함되어 있지 않아도 참을 반환하지 않음!

🍩 5장 집계 함수까지는 괜찮았는데 야금야금 어렵고 헷갈리는 것들이 생긴다. distinct 라던가 having...^^ 스칼라 들어가면서부터 너뮤 멘붕.. 연습 더 해야지 ㅠㅠ 손에 익을 때까지! 화이띵...! 🍩