- Aggregate Functions (집계함수)
- 여러 칼럼 혹은 테이블 전체 칼럼으로부터 하나의 결과값을 반환하는 함수
COUNT : 총 개수 계산
SUM : 합계 계산
AVG : 평균 계산
MIN : 최솟값
MAX : 최댓값
FIRST : 첫 번째 결과값
LAST : 마지막 결과값
- COUNT : 총 개수를 계산해 주는 함수
SELECT COUNT(column)
FROM tablename
WHERE condition;select count(*) from police_station;-> 테이블 데이터 개수 계산
select count(distinct police_station) from crime_status;-> 경찰서 개수 세기(중복 없게끔 distinct)
- SUM : 숫자 칼럼의 합계를 계산해주는 함수
SELECT SUM(column)
FROM tablename
WHERE condition;select sum(case_number) from crime_status where status_type="발생";-> 범죄 총 발생건수 구하기
- AVG : 숫자 칼럼의 평균을 계산해주는 함수
SELECT AVG(column)
FROM tablename
WHERE condition;select AVG(case_number)
from crime_status
where crime_type like '폭력' and status_type='검거';-> 평균 폭력 검거 건수 구하기
- MIN : 숫자 칼럼 중 가장 작은 값을 찾아주는 함수
SELECT MIN(column)
FROM tablename
WHERE condition;select MIN(case_number)
from crime_status
where crime_type like '강도' and status_type='발생';-> 강도 발생 건수가 가장 적은 경우 구하기
- MAX : 숫자 칼럼 중 가장 큰 값을 찾아주는 함수
SELECT MAX(column)
FROM tablename
WHERE condition;select MAX(case_number)
from crime_status
where crime_type like '살인' and status_type like '검거';-> 살인이 가장 많이 검거된 건수 구하기
- GROUP BY : 그룹화하여 데이터를 조회
SELECT column1, column2, ...
FROM tablename
WHERE condition
GROUP BY column1, column2, ...
ORDER BY column1, column2, ...;select police_station
from crime_status
group by police_station
order by police_station;-> 경찰서 별로 그룹화하기
-> 같은 역할을 하는 DISTINCT는 ORDER BY 사용불가
select police_station, sum(case_number) 발생건수
from crime_status
where status_type like '발생'
group by police_station
order by 발생건수 desc;-> 경찰서별로 총 범죄 발생 건수 구하기
select police_station, status_type, avg(case_number)
from crime_status
group by police_station, status_type limit 10;- HAVING : 조건에 집계함수가 포함되는 경우, WHERE 대신 HAVING 사용
SELECT column1, column2, ...
FROM tablename
WHERE CONDITION
GROUP BY column1, column2, ...
HAVING condition (Aggregate Functions)
ORDER BY column1, column2, ...select police_station, sum(case_number) count
from crime_status
where status_type like '발생'
group by police_station
having count > 4000;-> 경찰서별로 발생한 범죄 건수의 합이 4000건보다 큰 경우를 검색
- Scalar Functions
- 입력값을 기준으로 단일 값을 반환하는 함수
UCASE : 영문을 대문자로 변환
LCASE : 영문을 소문자로 변환
MID : 문자열 부분을 반환
LENGTH : 문자열 길이를 반환
ROUND : 지정한 자리에서 숫자를 반올림 (0이 소수점 첫째 자리)
NOW : 현재 날짜 및 시간을 반환
FORMAT : 숫자를 천단위 콤마가 있는 형식으로 반환
- UCASE : 영문을 대문자로 반환하는 함수
SELECT UCASE(string);- LCASE : 영문을 소문자로 반환하는 함수
SELECT LCASE(string);- MID : 문자열 부분을 반환하는 함수
SELECT MID(string, start_position, length);-> string: 원본 문자열 / start: 문자열 반환 시작 위치 (첫글자는 1, 마지막글자는 -1) / length: 반환할 문자열 길이
- LENGTH : 문자열의 길이를 반환하는 함수
SELECT LENGTH(string);-> 공백도 문자이므로 1의 길이를 가짐
- ROUND : 지정한 자리에서 숫자를 반올림하는 함수
SELECT ROUND(number, decimals_place);-> number: 반올림할 대상숫자 / decimals: 반올림할 소수점 위치(option)
-> 반올림할 위치를 지정하지 않을 경우, 소수점 자리 첫번째에서 반올림
-> 일단위 위치는 -1, 십단위는 -2
- NOW : 현재 날짜 및 시간을 반환하는 함수
SELECT NOW();- FORMAT : 숫자를 천단위 콤마가 있는 형식으로 반환하는 함수 (000,000,000)
SELECT FORMAT(number, decimals_place);-> number: 포맷을 적용할 문자 혹은 숫자 / decimals: 표시할 소수점 위치
-> 소수점을 표시하지 않을 경우 decimals= 0
-> 소수점 두자리까지 표시할 경우 2 (세자리에서 반올림)
- SQL Subquery (서브쿼리)
-
하나의 SQL문 안에 포함되어 있는 또 다른 SQL문을 뜻함
-
메인쿼리가 서브쿼리를 포함하는 종속적인 관계
-> 서브쿼리는 메인쿼리의 칼럼 사용 가능
-> 메인쿼리는 서브쿼리의 칼럼 사용 불가 -
서브쿼리 사용시 주의
-> 서브쿼리는 괄호로 묶어서 사용
-> 단일 행 혹은 복수 행 비교연산자와 함께 사용 가능
-> 서브쿼리에서는 order by 사용X -
서브쿼리 종류
-> 스칼라 서브쿼리 (Scalar Subquery) : SELECT절에 사용
-> 인라인 뷰 (Inline View) : FROM절에 사용
-> 중첩 서브쿼리 (Nested Subquery) : WHERE절에 사용 -
스칼라 서브쿼리
: SELECT절에서 사용하는 서브쿼리. 결과는 하나의 Column이어야 한다.
SELECT column1, (SELECT column2 FROM table2 WHERE condition)
FROM table1
WHERE condition;select case_number,
(SELECT avg(case_number)
FROM crime_status
WHERE crime_type like '강도' and status_type like '검거') avg
FROM crime_status
WHERE police_station like '은평' and crime_type like '강도' and status_type like '검거';-> 서울은평경찰서의 강도검거 건수와 경찰서 전체의 평균 강도검거 건수를 조회
- 인라인 뷰
: FROM절에서 사용하는 서브쿼리. 메인쿼리에서는 인라인 뷰에서 조회한 칼럼만 사용가능하다.
인라인 뷰를 내가 만들어낸 하나의 테이블로 생각하기
SELECT a.column, b.column
FROM table1 a, (SELECT column1, column2 FROM table2) b
WHERE condition;select c.police_station, c.crime_type, 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;-> 경찰서별로 가장 많이 발생한 범죄 건수와 범죄 유형을 조회
-> 어렵!!!!!!!
-
중첩 서브쿼리
: WHERE절에서 사용하는 서브쿼리.-Single Row - 하나의 열을 검색하는 서브쿼리
-Multiple Row - 하나 이상의 열을 검색하는 서브쿼리
-Multiple Column - 하나 이상의 행을 검색하는 서브쿼리 -
Single Row 서브쿼리
: 서브쿼리가 비교연산자(=,>,>=,<,<=,<>,!=)와 사용되는 경우,
서브쿼리의 검색결과는 한 개의 결과값을 가져야 한다. (두 개 이상인 경우 에러)
SELECT column_names
FROM table_name
WHERE column_name = (SELECT column_name
FROM table_name
WHERE condition)
ORDER BY column_name;select name from celeb
where name = (select host from snl_show where id=1);-> id가 1인 host 데이터는 한 개니까 single row 조건 만족!
- Multiple Row 서브쿼리 (IN)
: 서브쿼리 결과 중에 포함될 때
SELECT column_names
FROM table_name
WHERE column_name IN (SELECT column_name
FROM table_name
WHERE condition)
ORDER BY column_names;select host from snl_show
where host IN (select name from celeb where job_title like '%영화배우%');-> SNL에 출연한 영화배우를 조회
- Multiple Row 서브쿼리 (EXISTS)
: 서브쿼리 결과에 값이 있으면 반환
SELECT column_names
FROM table_name
WHERE EXISTS (SELECT column_name
FROM table_name
WHERE condition)
ORDER BY column_names;select name from police_station p
where EXISTS (select police_station from crime_status c
where p.name = c.reference and case_number > 2000);-> 범죄 검거 혹은 발생 건수가 2000건보다 큰 경찰서 조회
- Multiple Row 서브쿼리 (ANY)
: 서브쿼리 결과 중에 최소한 하나라도 만족하면 반환 (비교연산자 사용)
SELECT column_names
FROM table_name
WHERE column_name = ANY (SELECT column_name
FROM table_name
WHERE condition)
ORDER BY column_names;select name from celeb
where name = ANY (select host from snl_show);-> SNL에 출연한 적이 있는 연예인 이름을 조회
- Multiple Row 서브쿼리 (ALL)
: 서브쿼리 결과를 모두 만족하면 반환 (비교연산자 사용)
SELECT column_names
FROM table_name
WHERE column_name = ALL (SELECT column_name
FROM table_name
WHERE condition)
ORDER BY column_names;select name from celeb
where name = ALL (select host from snl_show where id = 1);- Multi Column Subquery (연관 서브쿼리)
: 서브쿼리 내에 메인쿼리 컬럼이 같이 사용되는 경우
SELECT column_names
FROM tablename a
WHERE (a.column1, a.column2, ... ) IN (SELECT b.column1, b.column2, ...
FROM tablename b
WHERE a.column_name = b.column_name)
ORDER BY column_names;select name, sex, agency
from celeb
where (sex, agency) in (select sex, agency from celeb where name = '강동원');-> 강동원과 성별, 소속사가 같은 연예인의 이름, 성별, 소속사를 조회
서브쿼리 좀 어렵다
<제로베이스 데이터 취업 스쿨>
