주요 학습내용

1. Aggregate Functions

2. Scalar Functions

3. Subquery

I. Aggregate Functions(집계 함수)

• 여러 컬럼 혹은 테이블 전체 컬럼으로부터 하나의 결과값을 반환하는 함수
  

• 사용 databases
  

1. count

• 예시) crime_status 테이블에서 경찰서는 총 몇군데?

select count(distinct police_station) from crime_status

• 예시) crime_type은 총 몇 가지? / 각 항목은?

select count(distinct crime_stype) from crime_status;

select distinct crime_stype from crime_status

2. SUM

• 예시) 범죄 총 발생 건수?

select sum(case_number) from crime_status where status_type like '발생';

• 예시) 살인 총 발생 건수는?

select sum(case_number) from crime_status 
where crime_stype = '살인' and status_type = '발생';

3. AVG/MIN/MAX

4. Group By

• 그룹화하여 데이터를 조회
  

  • distinct 사용하여 그룹화 한 경우, 정렬 불가능(order by)

5. Having

• 조건에 집계함수가 포함되는 경우 where 대신 HAVING 사용

• 예제) crime_status에서 경찰서별로 총 발생 범죄 건수 검색

mysql>select police_station, sum(case_number) 발생건수 from crime_status
    -> where status_type = '발생'
    -> group by police_station
    -> order by 발생건수 desc
    -> limit 10;

• 예제) 경찰서별로 평균 범죄 발생 건수와 병균 범죄 검거 건수를 검색

mysql> select police_station, status_type, avg(case_number) 평균건수 from crime_status
    -> group by police_station, status_type
    -> limit 10;

• 예제) 경찰서 별로 발생한 범죄 건수의 합이 4,000건 보다 큰 경우를 검색

mysql> select police_station, sum(case_number) 범죄건수
    -> from crime_status
    -> where status_type = '발생'
    -> group by police_station
    -> having 범죄건수 > 4000;

II. Scalar Functions

• 사용할 table

1. UCASE (대문자 변환)

mysql> select ucase(menu), price from sandwich where price > 15;

2. LCASE (소문자 변환)

mysql> select lcase(menu), price from sandwich where price > 15;

3. MID (문자열 부분 반환)

• 🤔 python slice랑 비슷함

• mid(column, 시작번호, 길이)

• 예제) 11위 카페 이름 중 두번째 단어만 조회

# 1) ranking = 11 우선 조회
mysql> select cafe from sandwich where ranking = 11;
# 2) 두번째 단어만 조회
mysql> select mid(cafe, -4, 4) from sandwich where ranking=11;

4. LENGTH (길이 반환)

• 🤔 python len과 비슷함
• 공백의 경우 : 공백의 길이만큼 반환됨
  
• 예제) sandwich 테이블에서 top3의 주소 길이를 검색

mysql> select ranking, address, length(address) 주소길이 from sandwich
    -> where ranking <= 3
    -> order by ranking asc;

5. ROUND(지정한 자리에서 반올림)

• 0: 소수점 첫째 자리
• 1: 소수점 둘째 자리
• -1 : 1의 자리

• 예제) sandwich 테이블에서 소수점 자리 반올림하여 1달러 단위까지 출력(랭킹 최하위 3개만)

mysql> select ranking, cafe, menu, round(price, 0) from sandwich
    -> order by ranking desc
    -> limit 3;

6. NOW (현재 날짜, 시간 반환)

select now();

7. FORMAT(천단위 콤마)

• FORMAT(적용column, 소수점 길이)

  • FORMAT으로 나온 값 => STRING
    • 값 비교 해야할 경우, ROUND 사용

• 예제) oil_price 테이블에서 가격을 100원 단위로 반올림 했을 때, 2,000원 이상인 경우 format 적용하여 조회
• oil_price

mysql> select 상호, format(가격, 0) from oil_price
    -> where round(가격, -3) >= 2000;

III. Subquery

🙌

• 하나의 SQL문 안에 포함되어 있는 또 다른 SQL문을 말함
• 메인쿼리가 서브퀴리를 포함하는 종속적인 관계이다
  • 서브쿼리는 메인쿼리의 컬럼 사용 가능
  • 메인쿼리는 서브쿼리의 컬럼 사용 불가
• Subquery는 괄호로 묶어서 사용
• 단일 행 혹은 복수 행 비교 연산자와 함께 사용 가능
• subquery에서는 order by를 사용 X

사용 TABLES

1. Scalar Subquery(select절에 사용)

• 결과는 하나의 Column이어야 한다

• 예제) 서울은평경찰서의 강도 검거 건수와 서울시 경찰서 전체의 강도 검거 건수를 조회

mysql> select case_number,
    -> (select avg(case_number) from crime_status
    -> where crime_stype like '강도' and status_type = '검거') avg
    -> from crime_status
    -> where police_station like '은평' and crime_stype like '강도' and status_type = '검거';

2. Inline View(from절에 사용)

• from 절에 사용하는 서브쿼리, 메인쿼리에서는 인라인 뷰에서 조회한 column만 사용가능하다
• 예제) 경찰서 별로 가장 많이 발생한 범죄 건수와 범죄 유형을 조회

mysql> select c.police_station, c.crime_stype, c.case_number
    -> from crime_status c,
    -> (select police_station, max(case_number) count from crime_status
    -> where status_type like '발생' group by police_station) m
    -> where c.police_station = m.police_station
    -> and c.case_number = m.count;

3. Nested(중첩) Subquery(where 절에 사용)

사용 TABLE

3-1) Single Row

• 하나의 행을 검색하는 서브쿼리
  

mysql> select name from celeb where name = (select host from snl_show where id = 1);

3-2) Multiple Row

• 하나 이상의 행을 검색하는 서브쿼리

• IN

  • 예제) SNL에 출연한 영화배우를 조회

mysql> select c.name from celeb as c
    -> where c.name in (select s.host from snl_show as s where c.job_title like '%영화배우%')
    -> order by c.name;

• EXISTS

  
  • 예제) 범죄 검거 혹은 발생 건수가 2,000건보다 큰 경찰서 조회

mysql> select name from police_station p
    -> where exists
    -> (select police_station from crime_status c where p.name = c.reference and case_number > 2000);

• ANY

  
  • 예제) SNL에 출연한 적이 있는 연예인 이름 조회

mysql> select name from celeb
    -> where name = any(select host from snl_show);

• ALL

  
  • 예제) snl에 출연했고, id =1 인 연예인 이름 조회

mysql> SELECT name from celeb
    -> where name = all(select host from snl_show where id = 1);

3-3) Multiple Column

• 하나 이상의 열을 검색하는 서브쿼리
• 서브쿼리 내에 메인쿼리 컬럼이 같이 사용되는 경우
• 예제) 강동원과 성별, 소속사가 같은 연예인의 이름, 성별, 소속사를 조회

mysql> select name, sex, agency from celeb
    -> where (sex, agency) in (select sex, agency from celeb where name = '강동원');

[자료 출처] 제로베이스 데이터 스쿨