-Scalar Functions
UCASE : 영문을 대문자로 변환
LCASE : 영문을 소문자로 변환
MID : 문자열 부분을 반환
LENGTH : 문자열의 길이
ROUND : 지정한 자리에서 숫자를 반올림
NOW : 현재 날짜 및 시간 반환
FORMAT : 숫자를 천단위 콤마가 있는 형식으로 변환
-UCASE
:select UCASE(string);
ex) select UCASE(menu) from sandwich where price>15;
-LCASE
:select LCASE(string);
ex) select LCASE(menu) from sandwich where price<5;
-MID
:select MID(string,시작지점,가져올길이)
ex) select MID('This is mid test,-4,4); -> test 출력
-LENGTH
:select LENGTH(string)
ex) select LENGTH('This is len test'); -> t h i s i s l e n t e s t +공백3개 = 16
-ROUND
:select ROUND(반올림할 숫자 , 반올림할 소수점 위치)
ex) select ROUND(315.625); -> 316 , 위치 지정안하면 소수점 첫번쨰에서 반올림 = select ROUND(315.625,0)
ex) select ROUND(315.625,1) -> 315.6 , 1은 소수점 첫째가 나오게 반올림하는 것
ex) select ROUND(315.625,-2) -> 300 , 10단위에서 반올림
-NOW
:select NOW();
-FORMAT
:select FORMAT(포맷할 숫자, 표시할 소수점 위치)
ex)select FORMAT(12345.6789,0); -> 12,346
ex)select FORMAT(12345,6789,10); -> 12,345.6789000000 , 10은 소수점 10자리까지 나타낸다는 뜻
ex)select FORMAT(12345,6789,2); ->12,345.68 , 2는 소수점 2자리까지 나타내지만 반올림이 되서 68
ex)select FORMAT(가격,0) from oil_price where ROUND(가격,-3) >= 2000
#여기서 where 절에 FOMAT대신 ROUND를 쓰는건 round는 숫자를 반환 하지만 format은 콤마가 포함된 string이라서
-SQL Subquery
: 하나의 SQL문 안에 있는 또 다른 SQL문
#서브쿼리는 메인 쿼리의 칼럼 사용가능 , 메인쿼리는 서브쿼리의 칼럼 사용 불가
#Subquery는 괄호로 묶어서 사용 , subquery는 order by 사용 X
1. 스카라 서브쿼리 : select 절에 사용
2. 인라인 뷰 서브쿼리 : from절에 사용
3. 중첩 서브쿼리 : where 절에 사용
-스카라 서브쿼리
:select 칼럼1 , (select 칼럼2 from 테이블2 where 조건) from 테이블1 where 조건
ex) 서울은평경찰서 강도 검거 건수와 서울시 경찰서 전체의 평균 강도 검거 건수를 조회
#서울은평경찰서 강도 검거 건수는 메인쿼리 , 서울시 경찰서 전체 평균을 서브쿼리로 지정해서 한번에 가져오기
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 '검거';
-인라인 뷰 서브쿼리
:select a.칼럼,b.칼럼 from 테이블1 a, (select 칼럼1, 칼럼2 from 테이블2) b where 조건
ex) 경찰서 별로 가장 많이 발생한 범죄 건수와 범죄 유형
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;
#우리가 원래 테이블2개를 조인해서 썻듯이, 인라인 뷰를 통해서 하나의 테이블을 더 만들어서 조인한거라고 생각하면된다.
#즉, 우리는 경찰서별로 가장 많이 발생한 범죄들만 있는 테이블을 하나 만들어서 그걸 m이라고 한거고
#원래 있던 crime_status와 조인시킨 것이다. 그래서 경찰서별로 정리된 하나의 테이블에서 경찰서별로 group by해주고
#가장 많이 발생한 범죄니까 max를 사용해서 경찰서별로 가장많이 발생한 테이블을 m이라고 만들고 그걸 조인한거다.
-중첩 서브쿼리
:where 절에서 사용하는 서브쿼리
#single row : 하나의 열을 검색하는 서브쿼리 , mutiple row - 하나이상의 열을 검색 , mutiple column : 하나 이상의 행
-single row 서브쿼리
:select 칼럼이름 from 테이블이름 where 칼럼이름 = (select 칼럼이름 from 테이블이름 where 조건) order by 칼럼이름
#중요한건 where에 있는 괄호절(select~)이 하나의 row만 있어야한다.
ex) select name from celeb where name = (select host from snl_show where id=1);
-multiple row 서브쿼리
◆IN을 쓸때는 서브쿼리 결과 중에 포함될떄
:select칼럼이름 from테이블이름 where 칼럼이름IN=(select 칼럼이름 from 테이블이름 where 조건) order by 칼럼이름
ex) select host from snl_show where host IN ( select name from celeb where job_title LIKE '%영화배우%');
◆EXISTS를 쓸때는 서브쿼리 결과에 값이 있으면 변환
:select칼럼이름 from테이블이름 where EXISTS=(select 칼럼이름 from 테이블이름 where 조건) order by 칼럼이름
ex)select name from police_station p where EXISTS
(select police_station from crime_status c where p.name = c.reference AND case_number > 2000);
#EXISTS 아래로 있는 where 절에서 알맞는 경우가 있으면 police_station 테이블의 name중으로 출력한다.
◆ANY를 쓸때는 서브쿼리 결과중에 최소한 하나라도 만족하면 (비교연산자 사용)
ex)select name from celeb where name = ANY (select host from snl_show);
#snl에 출연한 적이 있는 연예인 이름 조회니까 ANY절에서 true를 만족하면 출력한다.
◆ALL을 쓸때는 서브쿼리 결과를 모두 만족하면 ( 비교연산자 사용)
ex)select name from celeb where name = ALL (select host from snl_show where id =1);
-multi column 서브쿼리 (연관 서브쿼리)
ex) select name ,sex,agency from celeb where (sex,agency) IN
(select sex ,agency from celeb where name = '강동원');
