SQL 첫걸음 CH05. 집계와 서브쿼리

집계함수
COUNT(집합)
SUM(집합)
AVG(집합)
MIN(집합)
MAX(집합)

1 _ 행 개수 구하기 COUNT

 

 - count 예제

sample51

 - sample51의 행의 갯수를 알 수 있다.

 - 명령어가 from -> where -> select 순서로 진행되기 때문에 name이 A인 행 2개를 세었다.

 

** count() 내부에 *(전체) 로 집합을 설정하면, 하나의 행에 NULL이 아닌 열이 하나라도 있으면 모두 센다

 

 - DISTINCT로 중복 제거 

 - 해당 열을 중복값 없이 출력할 수 있다.

 

 - count 내부에 distinct를 넣는 것으로 null 값을 세지 않을 수도 있다.

---

2 _ COUNT 이외의 집계함수

 : 기본적으로 집계함수에서는 NULL 값을 무시한다 ( 완전히 존재하지 않는 것으로 취급한다 )

만약, NULL에 값을 부여해서 처리하고 싶다면 NULL 값을 특정 값으로 변환해서 집계함수를 사용해야한다.

 

 - 문자열의 경우 A보다 C가 값이 크다.

 

---

3 _ 그룹화 GROUP BY

 : 집계함수를 사용할 때 값들을 일정한 조건으로 그룹을 만들어서 결과를 보고싶을 때 그룹화를 한다.

SELECT * FROM 테이블명 GROUP BY 열1, 열2 ...

 - name을 기준으로 그룹화를 했기 때문에 name 열 내부에 있던 값들을 기준으로 그룹이 나누어졌다

따라서 중복값 A 를 갖던 quantity 1 과 2는 sum을 통해 3으로 합쳐졌다.

B와 C는 각각 그룹화했지만 값이 1개씩 밖에 없어서 별 다른 변화가 없다.

 

** 내부 처리 순서가 where - group by - select - order by 이기 때문에

그룹화가 필요한 집계함수는 where 구에서 지정할 수 없다.

따라서, 그룹화가 한 집계함수를 지정하고싶으면 HAVING 구를 이용해야한다

where - group by - having - select - order by

 

having을 where처럼 써서 그룹화된 집계함수를 불러왔다.

---

 - 복수열의 그룹화

 : 위의 코드에서 name열은 그룹화를 했기 때문에, select의 요소로 지정이 가능하지만,

a나 quantity 열 같은 경우는 그룹화가 되어있지 않기 때문에 select의 요소로 지정할 수 없다

( 그룹화 되어있는 행 내부에 a, quantity의 여러 값들이 존재하기 때문에 어떤 값을 불러올지 확정할 수가 없다)

 -> 따라서 집계함수를 써서 값을 지정해줘야한다.

 

 - 결과값 정렬 ( order by )

 

---

4 _ 서브쿼리

 : 쿼리 안에 또 다른 쿼리를 작성

(SELECT 명령)

-- a열의 최소값인 데이터의 행을 삭제하는 예제

--> mySQL은 해당 명령어를 사용하면, 데이터를 추가하거나 갱신할 때 동이란 테일블을 서브쿼리에서 사용할 수 없도록 에러가 뜨게 만든다. ( 다른 프로그램은 정상 작동 )

이를 방지하기 위해서 다음과 같이 사용해야한다.

 - 이는 sample54 내부의 a열 최소값을 a에 저장시킨 후 해당 테이블을 x로 저장한 후 불러오는 과정이다

( 같은 이름의 테이블에서 데이터를 가지고 올 수 없기 때문에 x라는 테이블을 거쳐서 와야한다. )

 

** 명령문에서 서브쿼리는 항상 먼저 작동한다.

 

---

서브쿼리의 종류

 : 서브쿼리는 선언된 위치에 따라 명칭이 바뀐다.

 

1) 스칼라

 : select 결과가 하나의 행과 하나의 열로 되어있음.

select (select * from 테이블명)
from 테이블명;

 - sample51 과 54의 행의 갯수를 각각 sq1, sq2로 저장해서 나타내는 모습,

1행에 1열로 된 데이터로서 '스칼라' 라고 부른다.

 

2) 서브쿼리

select *
from 테이블명
where 열명 = (select * from 테이블명)

 - 서브쿼리를 이용해서 update 할 수 있다.

 

3) 인라인뷰

 : as 키워드를 이용하여 가상의 테이블명을 반드시 선언해야한다 

select * from (select * from 테이블명) as 가상테이블명

 

 

---

5 _ 데이터 베이스 내부에서 변수 선언하기 ( pagination 숫자 처리 )

 : 데이터베이스에서 사용할 수 있는 변수가 있다.

게시판의 경우 게시글이 삭제되기도 하고 생성되기도 하면서 게시글의 총 량이 변하기 때문에

이를 페이징하기 위해서는 고정된 no 숫자로는 불가능하다. 따라서 상황에 따라 변화되고 저장될 수 있는 변수가 필요하다.

-> 해당 select 는 @rownum:=@rownum +1 과 no , a 를 불러오는데,

sample54테이블에서 불러 올 때 @rownum 을 0으로 초기화 하고 불러온다.

따라서 게시글이 삭제되거나 생성되도 @rownum이 새롭게 배정되기 때문에

각 페이지마다 게시글의 수를 조정할 수 있다.

 

** tmp : 임시 테이블 생성 / 생성된 곳에서만 사용이 가능하고 이후에 불러올 수 없다.

 

---

MySQL에서 @rownum을 사용하여 페이지네이션을 만드는 방법은, SELECT 문에서 LIMIT 및 OFFSET 절을 사용하는 것입니다. 아래는 예시 코드입니다:

lessCopy code
SELECT * FROM 
( SELECT @rownum := @rownum + 1 AS row_number, table_name.* 
FROM table_name, (SELECT @rownum := 0) r ) numbered_rows 
WHERE row_number > {start_index} 
LIMIT {page_size};

 

이 예시에서, table_name을 사용하는 부분을 자신이 사용하는 테이블 이름으로 변경하고, {start_index}를 가져올 첫 번째 행의 인덱스로 대체하고 {page_size}를 한 페이지에 표시할 행 수로 대체합니다. @rownum 변수는 하위 쿼리에서 0으로 초기화된 후, table_name 테이블의 각 행마다 증가합니다.

WHERE 절을 사용하여 row_number로 필터링하여 원하는 페이지 범위에 해당하는 행만 검색할 수 있습니다. LIMIT 절은 반환되는 행 수를 {page_size}로 제한합니다.

예를 들어, 100개의 행이 있는 테이블에서 20-29번째 행을 검색하려면, 인덱스가 0에서 시작하기 때문에 {start_index} = 19를 사용하고, {page_size} = 10을 사용하면 됩니다.

 

---

6 _ 상관 서브쿼리 Exists

 : select 명령의 값이 존재하는지 확인하는 명령어

EXISTS (SELECT 명령)

sample551

sample552

 - sample552의 no2열에 있는 값이 sample551의 no 열에 존재한다면 해당 행의 a 값을 '있음' 으로 UPDATE 한다.

---

 - NOT EXISTS 예제

 - sample552의 no2열에 있는 값이 sample551의 no 열에 존재하지 않는다면 해당 행의 a 값을 '없음' 으로 UPDATE 한다.

 

---

열 명이 동일하다면?

 - sample551과 sample552에서 비교하려고 하는 열의 이름이 no로 동일하다면

where no = no 를 써야하는가?

-> 이 때를 대비해서 다음과 같이 쓸 수 있다.

  - 실제로는 sample551은 no , sample552는 no2로 서로 달라서 문제가 없지만 둘다 no 라고 가정한다면

no 앞에 테이블명을 붙여주는 것으로 둘을 구분할 수 있다.

 

---

 - IN 연산자

 : 스칼라 값끼리 비교할 때는 = 연산자를 사용하낟. 다만 집합을 비교할 때는 in 연산자를 사용한다.

또한 IN은 OR 연산자를 쉽게 사용할 수 있게 도와준다

IN 연산자
열명 IN(집합)

 - in 연산자는 or을 간단하게 줄여주기도 한다.

 

 - in 연산자 예제 ( 단일행 서브쿼리와 다중행 서브쿼리 비교 )

 - no 의 값을 지정해서 행을 찾으려고 했으나, 서브쿼리 내부에 select된 값이 2개이기 때문에

연산자 = 로는 no에 위치를 찾을 수 없다. ( 단일행 서브쿼리 )

 

따라서 IN 연산자를 이용해서 다중행 서브쿼리를 이용해야한다.

 - in 연산자를 이용해서 no의 위치를 특정하면, 서브쿼리 내부에 지정된 값이 여러개이더라도 no의 위치를

특정할 수 있다. ( 다중행 서브쿼리 )

---

*** 서브쿼리

1. 스칼라 서브쿼리 : select 뒤에 서브쿼리가 들어감

select (select * from 테이블명)

from 테이블명

where 조건식

 

2. 서브쿼리 : where 절의 열명 뒤에 서브쿼리가 들어감

select *

from 테이블명

where 열명 = (select * from 테이블명)

 

3. 인라인뷰 : from 뒤의 테이블 자리에 서브쿼리가 들어감 ( mySQL : as 키워드를 통해 가상의 테이블명을 꼭 설정해야함 )

select *

from ( select * from 테이블명 ) as 가상의 테이블명

 

*** 다중행 서브쿼리 IN, ANY, ALL

-> IN 과 ANY는 or 의 역할 / ALL 은 and 의 역할

 - any : 집합 안의 값들중 하나라도 조건식에 만족하면 true

(여기서 집합 안의 값은 3, 5 이므로, no >= 3 이기만 해도 조건을 만족하기 때문에 결과값으로 3 이상의 no를 출력했다)

 - all : 집합 안의 값들 모두가 조건식을 만족해야 true

( 집합 안의 값이 3, 5 이므로, no >= 5 를 만족하는 no값만 출력하기 때문에 no=5 가 출력됬다. )