Scalar Functions
입력 값을 기준으로 단일 값을 반환하는 함수
- UCASE : 영문을 대문자로 변환하는 함수
- LCASE : 영문을 소문자로 변환하는 함수
- MID : 문자열 부분을 반환하는 함수(공백포함)
- LENGTH : 문자열의 길이를 반환하는 함수(공백포함)
- ROUND : 지정한 자리에서 숫자를 반올림하는 함수(0이 소수점 첫째 자리, 0이 디폴트)
- NOW : 현재 날짜 및 시간을 반환하는 함수
- FORMAT : 숫자를 천단위 콤마가 있는 형식으로 반환하는 함수
실습환경
AWS RDS(database-1) zerobase에 접속
UCASE
select UCASE(string)
예제) sandwich 테이블에서 가격이 15달러 이상인 메뉴를 대문자로 표현
LCASE
select LCASE(string)
예제) sandwich 테이블에서 가격이 5달러 미만인 메뉴를 소문자로 표현
MID
select MID(string, start_position, length)
string : 원본문자열
start : 문자열 반환 시작 위치(첫글자 1, 마지막글자 -1)
length : 반환할 문자열 길이
예제) sandwich 테이블에서 11위 카페이름 중 두번째 단어만 조회
LENGTH
select LENGTH(string)
'' => 0, ' ' => 1, null => null로 출력
예제) sandwich 테이블에서 top 3의 주소 길이 검색
ROUND
select ROUND(number, decimals_place)
number : 반올림할 숫자
decimals : 반올림할 소수점 위치(옵션값, 디폴트는 0)
예제) sandwich 테이블에서 가격을 반올림해서 1달러 단위까지만 표시(최하위 3개만 표시)
NOW
select NOW()
국가 설정이 안돼있어서 시간이 안맞게 나옴
FORMAT
select FORMAT()
꼭 표시할 소숫점 자리수를 줘야함,
0은 1의 자리
1은 소숫점 첫째자리까지 표시(둘째자리에서 반올림함)
예시) oil_price 테이블에서 가격이 백원단위에서 반올림 했을 때 2000원 이상인 경우 천원단위에서 콤마를 넣어서 조회
Subquery
- 하나의 SQL문 안에 포함되어 있는 또 다른 SQL문을 말한다.
- 메인쿼리가 서브쿼리를 포함하는 종속적인 관계이다.
- 서브쿼리는 메인쿼리의 컬럼을 사용 가능, 메인쿼리는 서브쿼리의 컬럼 사용 불가
- subquery는 괄호로 묶어서 사용
- 단일 행 혹은 복수 행 비교 연산자와 함께 사용 가능
- subquery에서는 order by 사용 불가
- 종류
- 스칼라 서브쿼리(scalar subquery) - select 절에 사용
- 인라인 뷰(inline view) - from 절에 사용
- 중첩 서브쿼리(nested subquery) - where 절에 사용
스칼라 서브쿼리(scalar subquery)
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 '검거';
인라인 뷰(inline view)
메인쿼리에서는 인라인 뷰에서 조회한 column만 사용 가능
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;
중첩 서브쿼리(nested subquery)
- Single Row : 하나의 행을 검색하는 서브쿼리
- Multiple Row : 하나 이상의 행을 검색하는 서브쿼리
- Multiple Column : 하나 이상의 열을 검색하는 서브쿼리
Single Row subquery
서브쿼리가 비교연산자(=,>,>=,<,<=,<>,!=)와 사용되는 경우,
서브쿼리의 검색결과는 한 개 행의 결과값을 가져야 한다.
(두 개 이상인 경우 에러발생)
select column_names
from table_name
where column_name = (select column_name from table_name where condition)
order by column_names;
두 개 이상의 값을 가져올 때, 에러발생
1개 행
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;
예제) snl에 출연한 영화배우를 조회
서브쿼리가 될 쿼리의 행이 2개이다.
Multiple Row - EXISTS
select column_names
from table_name
where exists (select column_name from table_name where condition)
order by column_names;
예제) 범죄 검거 혹은 발생 건수가 2000건보다 큰 경찰서 조회
select name
from police_station p
where exists (select police_station from crime_status c where p.name = c.reference and case_number > 2000);
Multiple Row - ANY
서브쿼리 결과 중에 최소한 하나라도 만족하면 True를 리턴(비교연산자 이용)
select column_names
from table_name
where column_name = any (select column_name from table_name where condition)
order by column_names;
예제) snl에 출연한 적이 있는 연예인 이름 조회
select name
from celeb
where name = any (select host from snl_show where name = host);
Multiple Row - ALL
서브쿼리 결과를 모두 만족하면 True를 리턴(비교연산자 이용)
select column_names
from table_name
where column_name = all (select column_name from table_name where condition)
order by column_names;
Multiple 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='강동원');
