T-SQL도 다른 일반적인 프로그래밍 언어처럼 변수를 선언하고 사용할 수 있다.
변수의 선언과 값의 대입은 다음의 형식을 따른다.
구문 형식
변수의 선언 : DECLARE @ 변수이름 데이터 형식
변수에 값 대입 : SET @변수이름 = 변수의값
변수의 값 출력 : SELECT @변수이름
**변수는 일시적으로 사용되는 것이므로 재사용되지 않는다. 즉, 한번의 실행 이후에는 바로 소멸한다.
함수는 크게 집계함수와 스칼라함수로 나눌 수 있는데, 집합함수는 여러행에 걸쳐 실행을 하는 것이고 스칼라 함수는 단일행 단일값에 적용이 되어서 단일 값의 결과를 돌려주는 함수들을 말한다.
스칼라 함수의 종류
구성함수, 커서 함수, 날짜 및 시간 함수, 수치 연산 함수, 메타데이터 함수, 보안 함수, 문자열 함수, 시스템 함수, 시스템 통계 함수, 텍스트 및 이미지 함수 등으로 나눠진다.
구성 함수 : 현재 구성에 대한 정보를 알 수 있다.
@@langID : 현재 설정된 언어의 코드번호를 알 수 있다.
select @@langID --한국어는 코드 29이다
@@language : 현재 구성에 대한 언어를 확인시켜 준다.
select @@ language --한국어
@@serverName : 현재 인스턴스의 이름을 확인할 수 있다.
select @@serverName
@@serviceName : 현재 서비스의 이름을 돌려준다.
select @@serviceName
@@spID : 현재 사용자 프로세스의 세션 ID를 반환
select @@ spID as [세션 아이디], system_user as [로그인 사용자], user as [사용자]
@@verson : 현재 설정된 sql server의 버전 정보을 돌려준다.
select @@verson
![](https://t1.daumcdn.net/cfile/cafe/170C220F49BF39BDBF)
날짜 및 시간 함수 : 날짜 및 시간 입력 값에 대한 함수
getDate : 현재의 날짜와 시간을 돌려준다.
select getDate()
dateAdd : 날짜에 더한 값을 돌려준다.
selcet dateAdd(day, 100, '2006/10/10') //100일 후의 날짜를 돌려준다. year, month, week, hour, minute, second 등도 올 수있다.
dateIF : 두 날짜의 차이를 돌려준다.
select datedIF(week, getDate(), '2002/10/19')
dateName : 날짜의 지정한 부분만 돌려준다.
select dateName(weekDay, '2002/10/20')
day, month, year : 지정된 날짜의 일/월/년을 돌려준다
select month('2002/11/10')
![](https://t1.daumcdn.net/cfile/cafe/18338C1149BF431571)
수치 연산 함수
asb : 수치의 절대값을 돌려준다.
select abs(-100)
round : 자리수를 올려서 돌려준다
select round(1234.5678, 2), (1234.5678, -2)
rand : 0~1까지 임의의 숫자를 돌려준다.
select rand()
sqrt : 제곱근 값을 돌려준다.
power : 거듭제곱 값을 돌려준다
select power(2,31)
![](https://t1.daumcdn.net/cfile/cafe/111A850949BF4A6ECF)
**이 외에도 수치 연산 함수에는 degrees, acos, exp, asin, floor, sign, atan, log, sin, atn2, log10, ceiling, pi, square, cos, tan, cot, radlans 등이 있다.
메타테이터 함수 : 데이터베이스 및 데이터베이스 개체의 정보를 반환한다.
col_length : 테이블 컬럼의 길이를 돌려준다.
select col_length('userTbl, 'Uname')
db_id();db_name : db의 id번호와 db명을 돌려준다.
select db_id();select db_name()
object_id; object_name : object의 id와 이름을 돌려준다.
![](https://t1.daumcdn.net/cfile/cafe/1305A91C49BF4F0BE5)
문자열 함수 : 문자열을 조작한다.
ascii, char 문자의 아스키코드 값을 돌려주거나 아스키코드 값의 문자를 돌려준다(0~255 범위)
select ascii('A')
select char(65)
unicode, nchar : 문자의 유니코드 값을 돌려주거나 유니코드 값의 문자를 돌려준다.
select unicode('가')
select nchar(44032)
charIndex : 문자열의 시작 위치를 돌려준다.
select charIndex('Server, Sql Server 2005') --'server'이 시작되는 위치 '5'를 돌려준다.
left, right : 왼쪽 / 오른쪽의 지정위치부터 지정한 수만큼 돌려준다.
select left('sql server 2005', 3)
select right('sql server 2005', 4)
substring : 지정한 위치부터 지정한 수만큼 돌려준다.
select substring(N '대한민국 화이팅', 3,2)
len : 문자열의 길이를 알려준다.
select len('sql server 2005")
lower, upper : 소문자를 대문자로, 대문자를 소문자로 변경한다.
select lower('abcdEFGH')
select upper('abcdEFGH')
ltrim, rtrim : 왼쪽 공백문자 및 오른쪽 공백문자를 제거해 준다.
select ltrim(' 공백앞뒤두개 ')
selectrtrim(' 공백앞뒤두개 ')
replace : 문자열의 내용을 지정한 것으로 찾아서 바꾼다.
select replace ('sql server 2005', 'server', '서버')
replicate : 문자열을 지정한 수만큼 반복한다.
select replicate ('sql', 5)
space : 공백을 지정한 수만큼 반복한다.
select space(5)
reverse : 문자열의 순서를 거꾸로 만든다.
select reverse('sql server 2005')
str : 숫자를 문자로 변환한다(cast나 convert를 대신 사용 권장).
stuff : 문자를 지정한 위치의 개수만큼 삭제한 후에 새로운 문자를 끼워 넣는다.
select stuff('sql server 2005', 5,2, '서버')
![](https://t1.daumcdn.net/cfile/cafe/1845ED1549BF91DCDE)
![](https://t1.daumcdn.net/cfile/cafe/152EA51849BFA02CCF)
![](https://t1.daumcdn.net/cfile/cafe/17462B1549BFA18BD5)
순위 함수
SQL 2005는 RANK(), NTILE(), DENSE_RANK(), ROW_NUMBER() 등 4가지 순위 함수를 새로 제공한다. 이 기능은 이전 버전의 SQL Server에서 순번을 처리하기 위해서 피요했던 복잡한 과정들을 단순화시켜서 쿼리의 작성시간은 단축시켜 준다. 잘 알아두면 유용하게 사용할 수 있을 것이다.
구문형식
<순위함수이름>() over(
[partition by <partition_by_list>]
order by <order_by_list>
순위 함수의 큰 장점은 구문이 단순하다는 것이다. 단순한 구문은 코드를 명확하게 만들고 수정을 쉽게 한다. 또한 효율성도 뛰어나다. 즉, SQL Server에 부하를 최소화하면서 순위를 매기는 결과를 줄 수 있다.
예) 키가 큰 순서
select row_number() over(order by height desc) [키큰 순위],
Uname,addr, height
from userTbl
order by height desc
![](https://t1.daumcdn.net/cfile/cafe/155B8B1B49BFD84418)
이름순으로 정렬을 해보자.
select row_number() over(order by height desc, Uname asc) [키큰 순위],
Uname, addr, height
from userTbl
order by height desc
![](https://t1.daumcdn.net/cfile/cafe/1606841C49BFD95174)
지역별로 순위를 주어보자.
select row_number() over(order by height desc) [전체 키큰 순위],
addr, row_number() over(partition by addr
order by height desc, Uname asc) [지역별 키큰 순위],
Uname, height
from userTbl
order by addr,height desc
![](https://t1.daumcdn.net/cfile/cafe/2039F91A49BFE06901)
dense_rank : 점수가 같다면 순위를 같이 메기는 방버
select dense_rank() over(order by height desc) [키큰 순위 같이 메기기],
addr, Uname, height
from userTbl
order by height desc
![](https://t1.daumcdn.net/cfile/cafe/1871621D49BFE25100)
rank() 함수 : 3등이 2명일 경우 4등을 건너 띄고 5등으로 메길 때
select rank() over(order by height desc) [키큰 순위],
addr, Uname, height
from userTbl
order by height desc
![](https://t1.daumcdn.net/cfile/cafe/17410D1E49BFE4D300)
ntile() 함수 : 순서에 따라 그룹으로 분리할 경우 ntile(나눌 그룹의 개수)
select ntile(5) over(order by height desc) [5개 그룹],
addr, Uname, height
from userTbl
order by height desc
![](https://t1.daumcdn.net/cfile/cafe/132F4A1C49BFEA2402)