힐링 ASMR 과 프로그래밍

MySQL SELECT한 레코드에서 또 SELECT하기 – 하위 질의

하위 질의란?

하위 질의는 서브 쿼리라고도 합니다. 하위 질의는 질의를 실행해서 추출한 데이터를 이용해서 다시 질의를 하는 것을 말합니다.

하위 질의를 이용해서 sales의 최대값 레코드를 표시해 보겠습니다.

select * from table_sales;

select * from table_sales

where sales in(select max(sales) from table_sales);

GROUP BY 함수

MAX(), AVG(), SUM()등 집계를 하는 함수를 GROUP BY 함수라고 하는데 그룹화했을 때 값을 처리하는 함수입니다. 단 GROUP BY 가 없는 경우에는 테이블 전체를 하나의 그룹으로 처리합니다.

평균 이상의 레코드 추출하기.

평균나이 이상인 레코드를 추출해 보겠습니다.

select * from table_member;

select * from table_member

where age >= (select avg(age) from table_member);

IN 사용하기 – 컬럼을 반환하는 하위 질의

이번에는 하위 질의가 컬럼을 반환하는 예입니다.

1단계의 하위 질의에서 조건에 일치하는 컬럼을 반환하고 2단계에서 그 컬럼을 포함한 값을 추출합니다.

명령문은 다음과 같습니다.

select 컬럼명 from 테이블명

where 컬럼명 in (select를 이용한 하위 질의로 추출한 컬럼);

table_sales 에서 sales 가 100이상인 number를 추출하고 table_member에서 해당하는 레코드를 표시해 보겠습니다.

select * from table_member

where number

in (select number from table_sales where sales >= 100)

EXISTS를 사용해서 존재하는 레코드를 대상으로 검색하기.

exists는 특정 컬럼이 아닌 대상이 되는 레코드가 존재하고 있다는 의미입니다.

Table_sales의 number와 table_member의 number가 양쪽에 모두 존재하는 레코드를 추출하고 table_member에서 해당하는 레코드를 표시해 보겠습니다..

select * from table_member as A

where exists (select * from table_sales as B

where A.number = B.number);

select * from table_sales as B where A.number = B.number 은 특정 컬럼이 아니라 조건에 맞는 레코드를 추출하는 것입니다. 그리고 exists를 사용해서 table_member에 존재하는 해당 레코드만 표시합니다.

NOT EXISTS 사용하기.

not exists 는 하위 질의로 추출되지 않은 레코드를 대상으로 합니다.

위의 exists 명령문에 not exists를 적용해서 비교해 보겠습니다.

select * from table_member as A

where not exists (select * from table_sales as B

where A.number = B.number);

하위 질의를 이용한 순위 정하기.

하위 질의를 사용하면 다양한 방법으로 순위를 매길 수 있습니다.

하위 질의를 사용해서 순위를 정하는 방법을 알아 보겠습니다.

1.     table_sales 컬럼 구조만 복사해서 table_rating 를 생성합니다.

2.     table_rating 에 연속 번호 기능을 설정한 rating를 추가합니다.

3.     하위 질의를 사용해서 table_sales의 컬럼 sales를 내림차순으로 정렬하고, number, sales, month를 table_rating에 추가합니다.

create table table_rating like table_sales;

alter table table_rating

add rating int auto_increment primary key;

insert into table_rating (number, sales, month)

(select number, sales, month from table_sales

order by sales desc);

select * from table_rating;

MySQL 그룹화에 조건 설정하기.

그룹별로 데이터 처리하기.

group by로 그룹화하고 검색조건을 설정해 보겠습니다.

Table_01에서 age 평균을 number 별로 계산한 후 평균이 40이상인 데이터만 표시하기 위해서 having 조건 설정합니다.

select number, avg(age) from table_01 group by number having avg(age) >= 40;

먼저 데이터를 추출하고 그룹화하기.

먼저 데이터를 추출하고 그룹화하는 방법은 where 조건으로 데이터를 추출한다음 그룹화 한다는 의미 입니다.

age가 40이상인 데이터를 먼저 추출한 다음 그룹화 해 보겠습니다.

select number, avg(age) from table_01 where age >= 40 group by number;

그룹화하고 나서 정렬하기.

그룹화하고 나서 정렬하기란 select 명령에 group by 와 order by를 모두 사용하는 것을 말합니다.

Age 평균이 40이상인 데이터를 내림차순으로 정렬을 해보겠습니다.

select number, avg(age) from table_01 group by number having avg(age) >= 40 order by avg(age) desc;

그룹화 과정 정리.

1.     그룹화하는 순서는 먼저 where로 조건을 설정하고 group by로 그룹화합니다.

2.     Group by로 먼저 그룹화하고 having으로 조건을 설정합니다.

3.     그룹화한 결과를 정렬할 때는 order by를 사용합니다.

주요 명령인 where와 group by, order by의 순으로 명령을 사용합니다.

MySQL 그룹별로 표시하기.

그룹별로 표시하기.

Table_01에 number 컬럼에는 A1인 데이터가 여러 개 있습니다. 이와 같이 같은 값의 데이터를 그룹으로 만들 수 있습니다. 그룹으로 설정된 데이터는 합계와 평균을 비롯한 각종 데이터 처리를 그룹 단위로 할 수 있습니다.

그룹화할 때는 group by를 사용합니다.

select 컬럼명 from 테이블명 group by 그룹화할 컬럼명;

컬럼 number를 그룹화 하기 전과 후를 비교해 보겠습니다.

select * from table_01;

select * from table_01 group by number;

위와 같이 number에 대해 그룹화를 했을 때 number 데이터는 1종류씩 표시됨을 확인할 수 있습니다. 하지만 그룹화했을 때 그 처리 대상은 같은 그룹에 속해 있는 모든 레코드입니다.

무슨 의미인지는 위쪽 결과를 보시면 A1에 강백호, 강호동, 신동엽이 있습니다만 그룹화 했을 때 대표로 강백호 하나만 나타냈을 뿐 실제로는 A1에 속해 있는 모든 값이 대상입니다.

그러면 그룹별 몇 개가 있는지 count( ) 함수를 사용해 보겠습니다.

select number, count(*) from table_01 group by number;

그룹별 합계와 평균 표시하기.

그룹화로 합계를 계산하는 함수인 sum( )과 평균을 계산하는 함수 avg( )를 사용해 보겠습니다.

나이 합계 구하기.

select number, sum(age) as 나이합계 from table_01 group by number;

나이 평균 구하기.

select number, avg(age) as 나이평균 from table_01 group by number;