Upper, lower (대문자, 소문자 전환)
len (문자열 개수 반환)
ltrim, rtrim, trim (공백 제거)
concat, + (로우 합치기)
- 주의 : 문자열끼리 합쳐야 하며, 왼쪽에 숫자 오른쪽에 문자열 숫자가 있으면 숫자로 변환해서 합친다.
- 문자열 숫자 + 숫자하면 이상한 숫자나온다.
sign(숫자) -> 숫자 양,0,음 을 구분 (각각 1, 0, -1 반환)
ABS(숫자) -> 절대값 반환
Ceiling(숫자) -> 숫자에 소수점이 있으면 현재 정수에 올림해서 반환
Floor(숫자) -> 숫자에 소수점이 있으면 소숫점 버림해서 반환
round(숫자, 자릿수)
- 자릿수에 0 : 정수 일의자리수까지 다 나옴
-1 : 십의 자리수(일의 자리수 반올림 함)
-2 : 백의 자리수 ..... (백의 자리수 반올림 함)
1 : 소수점 첫째자리
2 : 소수점 둘째자리 .....
TOP
- select top 3 * -> (테이블에서 위쪽 3번째까지 출력)
as , '', []
- 컬럼 명을 지어줄때 앞에 숫자가 있으면 오류 뜸 그래서 ''문자열로 바꾸거나 []로 감싸줌
Distinct
- select distinct 컬럼명 -> (중복제거)
left, right(문자열, 글자수)
subString(문자열, 시작위치, 글자수)
- 문자열의 시작위치에서 글자수만큼 반환
replace(문자열, 이전문자열, 바꿀문자열)
- 문자열에서 이전문자열을 바꿀문자열로 바꿈
select replace('_SQL_CLASS','_','NO');
결과 NOSQL_CLASScharindex(찾을문자열,문자열,[시작위치])
- 시작위치가 없으면 자동으로 맨처음부터 시작
- 찾을 문자열이 총 2개 있으면 처음 만난거만 반환, 나머지 찾고 싶으면 시작위치 바꿔서 찾기
isNull(칼럼, 값)
- 칼럼 값에 null이 있으면 값으로 대체
Coalesce(값 , 값, .....)
- null이 아닌 가장 처음값 반환 (사용할 일이 있을까...?)
날짜 함수
- 구성요소 : 년(yy),월(mm),일(dd),시(hh),분(mi),초(ss)
year('2022-05-05') ,month(), day()
dateadd(yy,5,'2022-05-05') -> 5년 더함(year 해도 똑같음)
datediff(DD,'2019-12-31,'2022-01-15)/365 결과 : 2
detepart(mm,'2022-02-20 05:23:15') 결과 : 2
- month()를 사용해도 되지만 동적인 쿼리 만들때 이용될 듯
dateFromParts(2022,8,20) 결과 : 2022-08-20
case when 조건1 then 결과1
when 조건2 then 결과2
else 결과
end
IIF(x = x, 0, 1)
연산자 우선순위
- ( )괄호
- AND
- OR
나이 <= 22 or 성별 = '여' and 결혼유무 = '기혼'
- 성별 = '여' and 결혼유무 = '기혼'
- 나이 <= 22 or
where 컬럼명 in(), where 컬럼명 between and
where 컬럼명 is null
where 컬럼명 in('데이터A','데이터B')
결과 : 컬럼에서 데이터A 또는 데이터B 인 데이터를 가져옴
where 컬럼명 between 20 and 22
결과 : 20이상 22이하인 데이터 가져옴
where 컬럼명 is null
결과 : null인 데이터 가져옴
where 컬럼명 is null or (컬럼명 between 20 and 22)
where 컬럼명 is not null
Like'패턴'
- % : 없거나 1개 이상의 글씨
- _ : 1개의 글자
select *
from human2
where 번호 like 'b%';
(첫번째 글자 b로 시작하는거는 전부 가져옴)
결과 : b1, bba2, bd3, 등등
where 번호 like'b[0-9][a-z]'
(a-z 는 대소문자 구분 안함)
where 번호 like '[ab]%'
(처음 글자가 a나 b로 시작하고 그 뒤는 없던지 여러개든지 데이터 가져옴)
where 번호 not like '[a-z][0-9][0-9]'
(번호가 영어1개, 숫자2개가 아닌 데이터 가져옴)
count(*)
count(칼럼) null제외
count(distinct 칼럼) null제외,중복제외
sum(),avg() null제외 / ,max(),min()
문자 집계쿼리
XML (확장 가능한 태그로 둘러싸인 언어)
- 계층구조를 가진 요소들로 구성되어 있다
<요소명> 요소 내용 </요소명>
select concat('',거주지)
from human2
for xml path('tag')
결과 : <tag>부천 </tag><tag>부천 </tag><tag>부산 </tag>
----------
select concat('',거주지)
from human2
for xml path('')
-- as로 컬럼명을 정해주지 않아서 태그에 자동으로 추가가 안됨
결과 : 부천 부천 부산 순천 왕십리포항 서울
----------
select 거주지
from human2
for xml path('')
결과 : <거주지>부천 </거주지><거주지>부천 </거주지><거주지>부산 </거주지>
----------
select 거주지
from human2
for xml path('tag')
결과 : <tag><거주지>부천 </거주지></tag><tag><거주지>부천 </거주지></tag>
-- for xml path()에서 path()에 ''만 넣으면 컬럼명이 있을때는
-- 자동으로 컬럼명을 태그로 만들어주지만 없으면 그냥 없는 채로 출력된다.
-- 컬럼명이 있는데 또 path에 추가하면 거기서 또 태그가 추가된다.STUFF
- 문자열의 위치와 길이를 지정하여 다른 문자로 치환하는 함수.
- stuff(문자열, 시작위치, 문자길이, 치환문자)
select stuff((select concat(',',거주지)
from human2
for xml path('')),1,1,'') 내역;
-- concat으로 앞에 ,를 합쳐줌
-- stuff로 앞의 ,를 지워줌
결과 : 부천 ,부천 ,부산 ,순천 ,왕십리,포항 ,서울
--------
select stuff((select concat(',',거주지)
from human2
for xml path('')),1,2,'') 내역;
--문자길이 부분에 2를하면
-- (,부) 를 ''로 대체
결과 : 천 ,부천 ,부산 ,순천 ,왕십리,포항 ,서울
stuff밖에 있는 group by의 영향을 받지 않는다.
select 성별 ,stuff((select concat(',',거주지)
from human2
for xml path('')),1,1,'') 내역
from human2
group by 성별
결과 : 성별 | 내역
남 부천, 부천, 부산, 순천, 포항
여 | 부천, 부천, 부산, 순천,
---------
select 성별 ,stuff((select concat(',',거주지)
from human2
where 성별 = a.성별
for xml path('')),1,1,'') 내역
from human2 a
group by 성별
결과 : 성별 | 내역
남 부천, 순천, 포항
여 | 부천, 부산, 왕십리, 서울
having
select left(번호,1) id, avg(나이) 평균나이
from human2
group by left(번호,1)
having avg(나이) > 20
결과 : id | 평균나이
a 30
b 29
-- 특이한 점
select left(번호,1) id, avg(나이) 평균나이
from human2
group by left(번호,1)
having avg(나이) > 20
-- 오라클(oracle)에서는 group by left(번호,1) as a 로 하면
-- select 절에서 a만 적어도 쿼리문 실행순서로 인해서 인식을 하는데
-- msSQL은 인식을 못한다....
-------------------
-- 미혼이거나 여자인 고객 중 적어도 2명 이상의 고객이 사는 거주지 및 고객수
-- (고객수 기준 내림차순)
select 거주지, count(*) 고객수
from human2
where 결혼유무 = '미혼' or 성별 = '여'
group by 거주지
having count(*) >= 2
order by 고객수 desc
결과 : 부천 2
안녕하세요.