MSSQL에서는 날짜를 여러가지형태로 가공할 수 있는 함수들을 많이 제공하고 있습니다.

이번 포스팅에서는 대표적인 날짜함수(GETDATE,DATEADD,DATEPART,DATEDIFF)의 사용법에 대해서 알아보도록 하겠습니다.

GETDATE 

GETDATE함수는 현재 컴퓨터에 설정되어있는 시스템 시간을 불러와주는 함수입니다.

2018-03-31 오전 01:40:30 

위와같이 GETDATE()함수는 년월일은 물론이거니와 분,초 까지도 구해줍니다.

하지만 모든 사용자들이 위와같은 형식으로 사용하지는 않겠죠.

여기서 우리는 우리가 표시할 시간이나 날짜만 적절히 뽑아 올 수도 있고

날짜 형태를 바꿔서 출력할수도 있습니다.

사용법

--현재 날짜 출력--

SELECT GETDATE() AS 시스템일자

--현재 날짜의 연,월,일 출력--

SELECT YEAR(GETDATE()) AS 년,

MONTH(GETDATE()) AS 월,

DAY(GETDATE()) AS 일

출력 포맷 변경

--YYYY/MM/DD--

Select Convert(varchar(10),Getdate(),111)

--YYYYMMDD--

Select Convert(varchar(10),Getdate(),112)

--HH:MM:SS--

Select Convert(varchar(8),Getdate(),108)

--HH:MM:SS:mmm--

Select Convert(varchar(12),Getdate(),114)

--HHMMSS--

Select Replace(Convert(varchar(8),Getdate(),108),':','')

--HHMMSSmmm--

Select Replace(Convert(varchar(12),Getdate(),114),':','')

--YYYY/MM/DD HH:MM:SS--

Select Replace(Convert(varchar(30),Getdate(),120),'-','/')

--YYYY/MM/DD HH:MM:SS--

Select Replace(Convert(varchar(30),Getdate(),121),'-','/')

--YYYY/MM/DD HH:MM:SS--

Select Convert(varchar(10),Getdate(),111) + Space(1) + Convert(varchar(8),Getdate(),108)

--YYYYMMDDHHMMSS--

Select Convert(varchar(10),Getdate(),112) + Replace(Convert(varchar(8),Getdate(),108),':','')

DATEADD 

DATEADD함수는 날짜에 원하는 일수를 더해서 출력해주는 함수입니다.

100일뒤에는 몇일이다 이런것은 쉽게 계산하기 힘든데

DATEADD함수를 쓰면 이런것들을 편리하게 계산할 수 있습니다.

이 함수를 좀 더 응용하면 100일 뒤에는 무슨 요일이다 이렇게도 응용가능합니다.

사용법

--2개월 후 출력--

SELECT DATEADD(MM,2,GETDATE()) AS '2개월후'

--30일전 출력--

SELECT CONVERT(NVARCHAR(8),DATEADD(DAY,-30,'20180124'),112)AS '30일전'

--1달 뒤 요일계산

SELECT DATENAME(WEEKDAY,DATEADD(MM,1,GETDATE())) AS'요일계산'

DATEPART 

DATEPART함수는 날짜에서 지정한 날짜형식의 부분만 출력해주는 함수입니다.

아주 다양하게 활용이 가능한 함수입니다.

사용법

--현재 년도 구하기

SELECT DATEPART(YEAR,GETDATE());

--현재 월 구하기--

SELECT DATEPART(MONTH,GETDATE())

--현재 일 구하기--

SELECT DATEPART(DAY,GETDATE())

--현재 분기 구하기--

SELECT DATEPART(QQ,GETDATE())

--올해의 몇번째 날인지 구하기--

SELECT DATEPART(DAYOFYEAR,GETDATE())

--올해의 몇째 주인지 구하기--

SELECT DATEPART(WEEK,GETDATE())

--이번주의 몇번째 날인지 구하기--

SELECT DATEPART(WEEKDAY,GETDATE())

--오늘이 무슨요일인지 구하기--

SELECT

CASE DATEPART(WEEKDAY, GETDATE())

WHEN '1' THEN '일요일'

WHEN '2' THEN '월요일'

WHEN '3' THEN '화요일'

WHEN '4' THEN '수요일'

WHEN '5' THEN '목요일'

WHEN '6' THEN '금요일'

ELSE '토요일'

END AS '요일'

DATEDIFF 

DATEDIFF함수는 지정한 두 날자간의 간격을 계산해주는 함수입니다.

정해준 날짜형식에 맞춰 리턴값이 다르므로 적절하게 잘 설정해주셔야 합니다.

사용법

--지정일과의 현재와의 년도차이 계산--

SELECT DATEDIFF(YY,'2000-01-01',GETDATE())

--지정일과의 현재와의 월차이 계산--

SELECT DATEDIFF(MM,'2000-01-01',GETDATE())

--지정일과 현재와의 일차이 계산--

SELECT DATEDIFF(DD,'2000-01-01',GETDATE())

데이터베이스를 사용하다보면 테이블안에있는 데이터의 특정 문자들만 치환 해줘야 하는 일이 생깁니다. 이번 포스팅에서는 특정 문자열을 바꿔주는 REPLACE함수와 STUFF함수에 대해서 알아보도록 하겠습니다.

REPLACE

지정된 문자열 값을 특정 문자열로 바꿔주는 함수입니다.

사용법

--문법--

REPLACE('문자열','치환예정문자','치환할문자')

--예시--

REPLACE('ABCDEFG','DEF','XXX')

예제

--MY_TABLE에서 이름(NM_KOR)을 이씨를 김씨으로 바꿔서 출력--

SELECT REPLACE(NM_KOR,'이','김')AS 사원명 FROM MY_TABLE

STUFF

지정된 문자열의 시작위치와 크기를 지정하여 원하는 문자로 치환하는 함수입니다.

사용법

--문법--

STUFF('문자열','시작위치','크기','치환할문자')

--예시--

STUFF('ABCDEFG',2,3,'XXX')

예제

--MY_TABLE에서 이름(NM_KOR)칼럼의 이순신을 이성계로 바꿔서 출력--

SELECT STUFF(NM_KOR,2,2,'성계')AS 사원명 FROM MY_TABLE

데이터베이스를 사용하다보면 테이블안에있는 데이터의 특정 문자들만 치환 해줘야 하는 일이 생깁니다. 이번 포스팅에서는 특정 문자열을 바꿔주는 REPLACE함수와 STUFF함수에 대해서 알아보도록 하겠습니다.

REPLACE

지정된 문자열 값을 특정 문자열로 바꿔주는 함수입니다.

사용법

--문법--

REPLACE('문자열','치환예정문자','치환할문자')

--예시--

REPLACE('ABCDEFG','DEF','XXX')

예제

--MY_TABLE에서 이름(NM_KOR)을 이씨를 김씨으로 바꿔서 출력--

SELECT REPLACE(NM_KOR,'이','김')AS 사원명 FROM MY_TABLE

STUFF

지정된 문자열의 시작위치와 크기를 지정하여 원하는 문자로 치환하는 함수입니다.

사용법

--문법--

STUFF('문자열','시작위치','크기','치환할문자')

--예시--

STUFF('ABCDEFG',2,3,'XXX')

예제

--MY_TABLE에서 이름(NM_KOR)칼럼의 이순신을 이성계로 바꿔서 출력--

SELECT STUFF(NM_KOR,2,2,'성계')AS 사원명 FROM MY_TABLE

LEFT 

Left함수는 문자열을 받아서 왼쪽부터 원하는 길이만큼 자르는 함수이며

주민등록번호만으로도 생년월일을 구하거나 이름을 잘라서 성만 출력하는 등

사용법

--문법--

LEFT(문자열,길이)

--예시--

LEFT(NAME,2)

예제

--테이블(MY_TABLE)에서 이름(Name)을 잘라 성만 출력--

SELECT LEFT(Name,1) AS 이름 FROM MY_TABLE

RIGHT

RIGHT함수는 LEFT함수와 기능은 같지만 방향만 다른 함수입니다.

RIGHT함수는 문자열을 받아서 오른쪽부터 원하는 길이만큼 자르는 함수이며

LEFT함수와 마찬가지로 다양하게 사용이 가능합니다.

사용법

--문법--

RIGHT(문자열,길이)

--예시--

RIGHT(NAME,3)

예제

--테이블(MY_TABLE)에서 이름(NM_KOR)을 잘라 이름만 출력--

SELECT RIGHT(Name,2) AS 이름 FROM MY_TABLE

SUBSTRING

SubString함수의 기능은 문자열을 받아서 일정한 영역만큼 잘라낸 후 리턴하는 기능을 가지고 있습니다.

주민등록번호만으로도 성별을 잘라서 활용하거나 날짜를 잘라서 월별로 그룹을 만드는등

다양한 방법으로 활용 가능합니다.

정말 많이쓰이는 문자열함수 중 하나입니다.

사용법

--문법--

SUBSTRING(문자열,시작자리번호,자를문자수)

--예시--

SUBSTRING(resident_number,0,6)

예제

--테이블(MY_TABLE)에서 이름 2번째자리에서 2개만 잘라서 출력--

SELECT SUBSTRING(Name,2,2) AS 이름 FROM MY_TABLE

--테이블(MY_TABLE)에서 날짜(DT)를 잘라 0000년00월00일 형식으로 만들기--

SELECT SUBSTRING(DT,1,4)+'년'+SUBSTRING(DT,5,2)+'월'+SUBSTRING(DT,7,2)+'일' AS일자 FROM MY_TABLE

select d.name, d.dbid, spid, login_time, nt_domain, nt_username, loginame
from sysprocesses p
inner join sysdatabases d on p.dbid = d.dbid
where d.name = 'DB_Name'

or

exec sp_who2

kill spid

alter database DB_Name set multi_user;

alter database DB_Name set sigle_user;

SQL Server 2014: 실습9 SELECT, FROM, WHERE, BETWEEN, AND, IN, LIKE, ANY, ALL 등

–앞으로 사용할 DB 생성–

sqlDB 데이터베이스 생성

–사용할 테이블 생성–

테이블 userTbl, buyTbl 작성

–데이터 입력–

데이터 입력.

userTbl 정보

buyTbl 정보

–DB 백업–

USE tempdb;
BACKUP DATABASE sqlDB TO DISK=’D:\DB_Backup\sqlDB2016.bak’ WITH INIT;

참고로 백업 및 복원은 https://archmond.net/?p=7083 에서 알아봤음.

–기본 WHERE 절–

WHERE절은 조회 시 조건을 줄 수 있다.

USE sqlDB;
SELECT * FROM userTbl WHERE name=’김경호’;

김경호 사용자만 추출

SELECT * FROM userTbl WHERE birthYear >= 1970 AND height >= 182;

1970년 이후 출생, 182cm 이상인 사람

SELECT userID, name FROM userTbl WHERE birthYear >= 1970 OR height >= 182;

1970년 이후 출생 or(또는) 182cm 이상인 사람은 7명

–BETWEEN, AND 사용–

SELECT userID, name FROM userTbl WHERE height >= 180 AND height <= 183;

키가 180에서 183cm인 사람 찾기

SELECT userID, name FROM userTbl WHERE height BETWEEN 180 AND 183;

동일한 값을 찾기 위해 BETWEEN A AND B를 사용함.

–IN 사용–

SELECT userID, name FROM userTbl WHERE addr=’경남’ OR addr=’전남’ OR addr=’경북’;

지역이 경남, 전남, 경북인 사람 찾기

–LIKE–

SELECT name, height FROM userTbl WHERE name LIKE ‘김%’;

성이 김씨인 사람 찾기

SELECT name, height FROM userTbl WHERE name LIKE ‘_종신’;

앞 한 글자 + 종신 이라는 이름을 가진 사람 찾기. %는 무엇이든. _는 한 글자. 도서에 의하면 검색 문자열의 앞에 _나 %가 들어가면 SQL Server 성능에 나쁜 영향을 끼칠 수 있다고 함.(name 열의 인덱스가 있어도 전체 데이터를 검색한다고 함)

–서브쿼리(SubQuery, 하위쿼리)–

쿼리문 속에 또다시 쿼리문이 있는 서브쿼리.

먼저 김경호의 키는 177이다.

SELECT name, height FROM userTbl WHERE height > (SELECT height FROM userTbl WHERE name=’김경호’);

김경호보다 키가 큰 사람 출력.

김경호가 177cm이므로 방금 전 쿼리와 동일한 결과가 나옴.

–ANY구문–

SELECT name, height FROM userTbl WHERE height >= (SELECT height FROM userTbl WHERE addr=’경남’);

지역이 경남인 사람보다 키가 크거나 같은 사람을 찾기. 오류가 난다. 하위 쿼리가 값을 둘 이상 반환했다고 알려줌.

서브쿼리가 173, 170 이렇게 두 개의 값을 반환하기 때문.

SELECT name, height FROM userTbl WHERE height >= ANY (SELECT height FROM userTbl WHERE addr=’경남’);

>= 연산자 뒤에 ANY를 넣는 것으로 170, 173cm보다 큰…(결국 170보다 큰) 사람을 반환함

–ALL구문–

이번엔 ALL을 써보자. 7명만 출력됨. 170보다 크거나 같을 뿐 아니라, 173보다도 크거나 같아야 한다 -> 결국 173보다 크거나 같은 사람만 반환.

–ANY 구문–

SELECT name, height FROM userTbl WHERE height = ANY (SELECT height FROM userTbl WHERE addr=’경남’);

170이거나 173cm인 사람을 출력.

–IN 구문–

SELECT name, height FROM userTbl WHERE height IN (SELECT height FROM userTbl WHERE addr=’경남’);

=ANY와 IN은 같은 의미.

참고 사이트

https://archmond.net/?p=7181

조인이란?

두개이상의 테이블이나 데이터베이스를 연결하여 데이터를 검색하는 방법입니다. 자신이 검색하고 싶은 컬럼이 다른 테이블에 있을경우 주로 사용하며 여러개의 테이블을 마치 하나의 테이블인 것처럼 활용하는 방법입니다. 보통 Primary key혹은 Foreign key로 두 테이블을 연결합니다. 테이블을 연결하려면 적어도 하나의 칼럼은 서로 공유되고 있어야합니다.고등학교 수학시간때 배웠던 벤다이어그램을 활용하면 쉽게 이해할 수 있습니다.

INNER JOIN

쉽게말해 교집합이라고 생각하시면 됩니다. 기준테이블과 Join한 테이블의 중복된 값을 보여줍니다.

결과값은 A의 테이블과 B테이블이 모두 가지고있는 데이터만 검색됩니다.

--문법-- SELECT 테이블별칭.조회할칼럼, 테이블별칭.조회할칼럼 FROM 기준테이블 별칭 INNER JOIN 조인테이블 별칭 ON 기준테이블별칭.기준키 = 조인테이블별칭.기준키.... --예제-- SELECT A.NAME, --A테이블의 NAME조회 B.AGE --B테이블의 AGE조회 FROM EX_TABLE A INNER JOIN JOIN_TABLE B ON A.NO_EMP = B.NO_EMP AND A.DEPT = B.DEPT

LEFT OUTER JOIN

기준테이블의 값 + 테이블과 기준테이블의 중복된 값을 보여줍니다.

왼쪽 테이블을 기준으로 JOIN을 하겠다고 생각하시면 됩니다.

그럼 결과값은 A테이블의 모든 데이터와 A테이블과 B테이블의 중복되는 값이 검색되겠네요

--문법-- SELECT 테이블별칭.조회할칼럼, 테이블별칭.조회할칼럼 FROM 기준테이블 별칭 LEFT OUTER JOIN 조인테이블 별칭 ON 기준테이블별칭.기준키 = 조인테이블별칭.기준키 ..... --예제-- SELECT A.NAME, --A테이블의 NAME조회 B.AGE --B테이블의 AGE조회 FROM EX_TABLE A LEFT OUTER JOIN JOIN_TABLE B ON A.NO_EMP = B.NO_EMP AND A.DEPT = B.DEPT

RIGHT OUTER JOIN

LEFT OUTER JOIN의 반대입니다.

오른쪽 테이블을 기준으로 JOIN을 하겠다고 생각하시면 됩니다.

그럼 결과값은 B테이블의 모든 데이터와 A테이블과 B테이블의 중복되는 값이 검색되겠군요

--문법-- SELECT 테이블별칭.조회할칼럼, 테이블별칭.조회할칼럼 FROM 기준테이블 별칭 RIGHT OUTER JOIN 조인테이블 별칭 ON 기준테이블별칭.기준키 = 조인테이블별칭.기준키 ..... --예제-- SELECT A.NAME, --A테이블의 NAME조회 B.AGE --B테이블의 AGE조회 FROM EX_TABLE A RIGHT OUTER JOIN JOIN_TABLE B ON A.NO_EMP = B.NO_EMP AND A.DEPT = B.DEPT

FULL OUTER JOIN

쉽게말해 합집합을 생각하시면 됩니다.

A테이블이 가지고 있는 데이터 , B테이블이 가지고있는 데이터 모두 검색됩니다.

사실상 기준테이블의 의미가 없습니다.

--문법-- SELECT 테이블별칭.조회할칼럼, 테이블별칭.조회할칼럼 FROM 기준테이블 별칭 FULL OUTER JOIN 조인테이블 별칭 ON 기준테이블별칭.기준키 = 조인테이블별칭.기준키 ..... --예제-- SELECT A.NAME, --A테이블의 NAME조회 B.AGE --B테이블의 AGE조회 FROM EX_TABLE A FULL OUTER JOIN JOIN_TABLE B ON A.NO_EMP = B.NO_EMP AND A.DEPT = B.DEPT

CROSS JOIN

크로스 조인은 모든 경우의 수를 전부 표현해주는 방식입니다.

기준테이블이 A일경우 A의 데이터 한 ROW를 B테이블 전체와 JOIN하는 방식입니다.

그러니 결과값도 N * M 이 되겠죠?

위사진에서는 A테이블에 데이터가 3개, B테이블에는 데이터가 4개가 있으므로 총 12개가 검색됩니다.

--문법(첫번째방식)-- SELECT 테이블별칭.조회할칼럼, 테이블별칭.조회할칼럼 FROM 기준테이블 별칭 CROSS JOIN 조인테이블 별칭 --예제(첫번째방식)-- SELECT A.NAME, --A테이블의 NAME조회 B.AGE --B테이블의 AGE조회 FROM EX_TABLE A CROSS JOIN JOIN_TABLE B ===================================================================================== --문법(두번째방식)-- SELECT 테이블별칭.조회할칼럼, 테이블별칭.조회할칼럼 FROM 기준테이블 별칭,조인테이블 별칭 --예제(두번째방식)-- SELECT A.NAME, --A테이블의 NAME조회 B.AGE --B테이블의 AGE조회 FROM EX_TABLE A,JOIN_TABLE B

SELF JOIN

셀프 조인은 자기자신과 자기자신을 조인한다는 의미입니다.

하나의 테이블을 여러번 복사해서 조인한다고 생각하시면 될듯합니다.

자신이 가지고 있는 칼럼을 다양하게 변형시켜 활용할 경우에 자주사용합니다.

--문법-- SELECT 테이블별칭.조회할칼럼, 테이블별칭.조회할칼럼 FROM 테이블 별칭,테이블 별칭2 --예제-- SELECT A.NAME, --A테이블의 NAME조회 B.AGE --B테이블의 AGE조회 FROM EX_TABLE A,EX_TABLE B

프로그래밍 언어중에서 조건에따라 작업방식을 달리 할 수 있는 조건문이라는 것이 있습니다.

대표적인 문법이 IF문과 CASE문인데요.

MSSQL에서도 조건절인 CASE문과 IF문을 지원하니 한번 활용해보시는 것도 좋을것 같습니다.

CASE WHEN

가장 많이쓰이는 조건문입니다. 조건에 따라 값을 지정해 주는 역할을 합니다.

다중 CASE WHEN

IF ELSE

같은 조건문입니다. CASE문과 마찬가지로 조건에 따라 원하는 작업을 수행할 수 있습니다.

SET NOCOUNT란?

쿼리문 또는 프로시저의 영향을 받은 행 수를 나타내는 메시지가 결과 집합의 일부로 반환되지 않도록 하는것

구문 : SET NOCOUNT{ON/OFF}

사용하는 이유

MSSQL에서 프로시저를 만들경우 프로시저의 속도(성능)에 대해서 생각을 안할 수 없습니다.

프로시저의 속도가 프로그램의 속도에 밀접한 관련이 있는만큼

프로시저의 성능에 대해 초점을 맞추고 쿼리문을 짜야합니다.

이번 포스팅에서는 SET NOCOUNT라는 함수를 사용하여

쿼리문의 속도를 향상시키는 방법에 대해 알아보겠습니다.

MSSQL에서 프로시저를 만들고 실행을 해보면

위와 같은 메시지를 보신적이 있으실텐데요.

위 메시지는 INSERT나 UPDATE DELETE 처럼 테이블에 영향을 주게되면 출력이 됩니다.

하지만 위에 보이는 0개행이 영향을 받았다는 메시지는 전혀 필요가 없죠

괜히 메시지를 출력하는데 서버 부하만 걸릴뿐입니다.

이 메시지는 프로시저 시작점에 SET NOCOUNT ON이라는 문구를 삽입해줌으로써 제거해줄 수 있습니다.

예제

이렇게 해주면 1개의 메시지만 출력이 됩니다.

피벗테이블이란?

테이블을 조회한 데이터를 특정 데이터 컬럼으로 사용, 요약된 결과를 만들어 표시하는 것입니다.

사용자 입장에서 데이터를 좀 더 쉽게 볼 수있도록 출력 형태를 가공할때 사용합니다.

예제

피벗테이블을 활용하여 세로로 되어있는 칼럼을 가로로 바꿔보는 예제 (행을 열로 변환)

MM_TEST 테이블안에는 위와같이 DT(날짜),QT(수량)의 데이터가 10만개가 있습니다.

위 테이블의 월별 합계 수량을 가로로 나타내시오

해결방법

1. 테이블의 월별 합계 수량을 Select 합니다.

2. Select한 쿼리문을 피벗테이블을 활용하여 데이터를 가공합니다. (행,열 전환)

3. 정적 피벗테이블을 동적 피벗테이블로 전환합니다.

피벗테이블안에는 동적으로 파라미터를 받을 수 없습니다.

그러므로 피벗테이블 쿼리를 문자열로 만들어 그 문자열을 EXEC시켜주는 방식으로 동적 피벗테이블을 구현합니다.

최종 결과