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

Sql

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

hojomu 2023. 3. 15. 11:18

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

1 _ 행 개수 구하기 COUNT

- count 예제

- 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

- 복수열의 그룹화

: 위의 코드에서 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을 사용하면 됩니다.