조인이란?

두개이상의 테이블이나 데이터베이스를 연결하여 데이터를 검색하는 방법입니다. 자신이 검색하고 싶은 컬럼이 다른 테이블에 있을경우 주로 사용하며 여러개의 테이블을 마치 하나의 테이블인 것처럼 활용하는 방법입니다. 보통 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시켜주는 방식으로 동적 피벗테이블을 구현합니다.

최종 결과

프로시저란?

데이터베이스에서의 프로시저란 프로그래머가 생성해놓은 쿼리문을 마치 하나의 메서드 형식으로 관리하는 것입니다.

실무에서는 굉장히 복잡한 쿼리문을 많이사용해요.

많은 컬럼을 조회하고 여러 테이블을 조인하고 거기다가 WHERE조건까지...

심한것은 하나의 쿼리를 만드는데 1000라인이 넘어가는경우도 종종있어요.

이렇게 장문의 쿼리를 사용할때마다 써줘야한다면 굉장히 불편할거에요.

그러므로 이 장문의 쿼리를 프로시저에 저장해주고,

쿼리문이 저장된 프로시저를 호출하여 프로그래밍을 하는것이 훨씬 효율적입니다.

프로시저 사용법

프로시저 생성 문법

프로시저 생성 예제

프로시저 1개 조회

전체프로시저 조회

프로시저 삭제

대한민국 만세~!

대한민국 만세~!

저장 프로시져 내용 검색

SELECT o.name

FROM sysobjects o JOIN syscomments c

on o.id = c.id

WHERE o.type = 'P' AND c.text LIKE '%텍스트%'

프로시져 내용 검색 말고도 트리거, 함수, 뷰테이블 검색이 가능함.

o.type 구분 확인.

날짜 함수
select getdate() <= 현재 시스템의 날짜를 가저온다. (시스템에 설정된 나라의 형식을 따른다.)

select Year(getdate()),month(getdate()),day(getdate()) <= 년,월,일 을 뽑는다

select getdate(),datename(Weekday,getdate()) <= 요일을 뽑는다

select datediff(yy,'1945-08-15',getdate()) <= 적어준 날의 년과 현재날의 년과의 차이를 출력

select datediff(dd,'1945-08-15',getdate()) <= 적어준 날의 일과 현재날의 일과의 차이를 출력

-- convert(바꿀형식, 원본데이타) ; 바꿀형식으로 데이타를 바꾼다 아래의 예는 문자형으로 바꾸어 문자와 '+' 기호를 사용하여 결합하는 예이다
select convert(char(4),year(getdate())) + '년'

--select * from insa => ex) example problem

select name, datediff(yy,ibsa_date,getdate()) as 근무년,datediff(dd,ibsa_date,getdate()) as 근무일
from insa
where datediff(dd,ibsa_date,getdate()) > 0

select name as 이름, convert(char(4), year(ibsa_date)) + '년' + convert(char(2),month(ibsa_date)) + '월' +
convert(char(2), day(ibsa_date)) + '일' + datename(weekday,ibsa_date) as 입사일 from insa

수식관련 내장함수
floor => 소수점아래 제거,
round => 반올림함수, n 자리까지 남김. 0 인경우 일의자리까지 남김
select floor(13.7), floor(-13.7), round(13.75, 1)
result -> 13 -14 13.80

select floor(13.7), floor(-13.7), round(13.75, 0)
result -> 13 -14 14.00

select floor(13.7), floor(-13.7), round(13.75, -1)
result -> 13 -14 10.00

--select replicate('*',10) -- 해당문자를 n 번 반복 예에서는 * 을 10번 출력

select name, (gi_pay + su_pay) as [급여], replicate('*', floor((gi_pay + su_pay)/100000)) as [그래프]
from insa

stuff 내장함수
select stuff('영업팀', 3, 1, '부') as stuff

select stuff('서울시', 3, 1, '특별시') as stuff

select name as [이름], stuff(buseo, 3, 1, '팀') as [팀명] from insa

select name, gi_pay, su_pay from insa order by gi_pay desc, su_pay
select * from insa order by ibsa_date desc, substring(ssn,1,6)

select * from insa order by convert(varchar(10),ibsa_date, 102) desc, substring(ssn,1,6)

select top 5 name, city, (gi_pay + su_pay) as [보수], ssn as [주민등록번호] from insa

where city='서울' order by (gi_pay + su_pay ) desc

select top 5 with ties name, city, (gi_pay + su_pay) as [보수], ssn as [주민등록번호] from insa where city='서울' order by (gi_pay + su_pay ) desc

select top 25 percent name, city, (gi_pay + su_pay) as [보수], ssn as [주민등록번호] from insa where city='서울' order by (gi_pay + su_pay ) desc

select top 25 name, city, (gi_pay + su_pay) as [보수], ssn as [주민등록번호] from insa
where city='서울' order by (gi_pay + su_pay ) desc

SELECT * FROM INSA WHERE GI_PAY >= 1000000 AND GI_PAY <= 2000000

SELECT * FROM INSA WHERE GI_PAY BETWEEN 1000000 AND 2000000

--SELECT NAME AS 이름, IBSA_DATE AS 입사일 FROM INSA WHERE IBSA_DATE LIKE '%2004%'

SELECT NAME AS 이름, IBSA_DATE AS 입사일 FROM INSA
WHERE IBSA_DATE BETWEEN '2004-01-01' AND '2004-12-31'

문자함수
LEFT, RIGHT
LEFT는 지정해준 자릿수만큼 왼쪽에서부터 문자열을 반환한다.
당연히 RIGHT는 반대이다.
형식 : LEFT(문자, 자릿수)

LTRIM, RTRIM
LTRIM은 문자열의 왼쪽 공백을 제거한다. 역시 RTRIM은 반대일 경우 사용된다.
형식: LTRIM(문자)

LEN
LEN함수는 문자열에서 문자의 갯수를 추출한다.
형식: LEN(문자)
Len함수는 문자 뒤쪽의 공백은 문자로 계산하지 않는다.

UPPER, LOWER
UPPER는 소문자를 대문자로, LOWER는 대문자를 소문자로 바꾼다.
형식: UPPER(문자)

REVERSE
REVERSE는 문자열을 반대로 표시한다.
형식: REVERSE(문자열)

REPLACE
REPLACE함수는 지정한 문자열을 다른 문자열로 바꾸어준다.
형식: R-PLACE(문자, 타겟문자, 바꿀문자)

REPLICATE
REPLICATE함수는 문자열을 지정된 횟수만큼 반복한다.
형식: REPLICATE(문자, 횟수)

STUFF
STUFF함수는 문자열에서 특정 시작위치에서 지정된 길이만큼 문자를 바꾸어준다.
형식: STUFF(문자, 시작위치, 길이, 바꿀문자)

SUBSTRING
SUBSTRING은 STUFF와 비슷하지만 문자를 바꾸는 것이 아니라 그 문자를 반환한다.
형식: SUBSTRING(문자, 시작위치, 길이)

PATINDEX, CHARINDEX
PATINDEX와 CHARINDEX는 문자열에서 지정한 패턴이 시작되는 위치를 뽑아준다.
형식: PATINDEX(문자패턴, 문자) - 문자패턴은 Like 사용과 같다.
형식: CHARINDEX(문자패턴, 문자) - 문자패턴은 일반형식을 사용한다.

SPACE
SPACE함수는 지정한 수 만큼 공백을 추가한다.
형식: SPACE(횟수)

시간 및 날짜 함수
GETDATE()
GETDATE()는 현재 시간을 표시해준다.
DATEADD
DATEADD함수는 날자에 지정한 만큼을 더한다.
형식: DATEADD(날자형식, 더할 값, 날자)

DATEDIFF
DATEDIFF함수는 두 날자사이의 날자형식에 지정된 부분을 돌려준다.
형식: DATEDIFF(날자형식, 시작 날자, 끝 날자)

DATENAME
DATENAME함수는 지정한 날자의 날자형식의 이름을 돌려준다.
형식: DATENAME(날자형식, 날자)

DATEPART
DATEPART함수는 날자에서 지정한 날자형식부분만 추출해줍니다.
형식: DATEPART(날자형식, 날자)
주일은 일요일부터 1로 시작해서 토요일날 7로 끝나게 된다.

NULL 함수

ISNULL
ISNULL은 NULL값을 대체값으로 바꾼다.
형식: ISNULL(NULL값, 대체값)

NULLIF
NULLIF함수는 두개의 표현식을 비교하여 같으면 NULL을 반환한다.
형식: NULLIF(표현식1, 표현식2)

COALESCE
COALESCE함수는 NULL이 아닌 첫번째 표현식이 반환된다.
형식: COALESCE(표현식)

GETANSINULL
GETANSINULL은 데이터베이스의 기본 NULL 상태를 표시해준다.
형식: GETANSINULL(데이터베이스 이름)

테이블의 칼럼정보 보기
exec sp_columns sales

/* Substring 처리 */
select payterms from sales
select substring(payterms,1,3) from sales

/* 문자열연결 연산자 '+' */
select substring(payterms,1,4)+ ' 테스트' from sales

/*현재날짜 */
select getdate()

/* 1년후날짜 */
select getdate() + 365

/* 날짜데이타 변경 CONVERT(변경될문자열의 데이타타입및길이, 날짜데이타, 출력형식) */
select convert(varchar(30), getdate(), 2)

select convert(varchar(30), getdate(), 102)

/* YYYYMMDD 형식으로 가져오기( Varchar(8)로 설정 ) */
select convert(varchar(8), getdate(), 112)

/* 해당 [월] 만 가져오기 */
select datepart(mm,getdate())
select month(getdate())

/* 현재일자에 20개월추가 (월 추가) */
select dateadd(mm,20,getdate())

/* 현재일자에 100일 후의 날짜 */
select dateadd(dd,100,getdate())

/* Oracle 에서의 NVL함수에 대응하는 MS-SQL함수 */
NVL(price,0) ===> ISNULL(price,0)

/* 판매수량이 30권 이상인책에 대하여 각 서적별 판매수량총계 조회 */
select title_id as '서적코드' , sum(qty) as '판매수량' from sales
group by title_id
having sum(qty) >= 30

/* COMPUTE & COMPUTE BY 사용법 */
select type, title_id, price from titles
order by type
compute avg(price) by type
해당 select칼럼이 별로 Average값을 구해준다. ( Order by 반드시 필요 )

select type, title_id, price from titles
order by type
compute avg(price) by type
마지막행에 Select된 전체칼럼중 Average(price)칼럼에 대해 평균값을 구해준다.

/* Rollup & Cube */
select type, avg(price) from titles group by type --각 타입별 평균
select avg(price) from titles -- 전체평균

데이터베이스 데이터 정렬 설정 또는 변경 (MSDN, SQL Server 2014 기준)

http://msdn.microsoft.com/ko-kr/library/ms175835.aspx

1. DB 생성시에는 언제나 Collation을 설정하도록 되어 있습니다.

기본값 때문에 잊고 지낼 때가 많지만 모든 DB는 자신의 Collation을 보유하고 있습니다.

CREATE DATABASE

MyOptionsTest

COLLATE

korean_wansung_ci_as

2. 생성된 DB의 Collation 변경

Collation은 생각보다 쉽게 변경할 수 있습니다.

ALTER DATABASE

MyOptionsTest

COLLATE

korean_wansung_ci_as

3. 테이블 컬럼의 Collation 변경

MSSQL에서는 DB 외에 char, nvarchar, text 등의 필드에도 Collation이 설정 됩니다.

ALTER TABLE

MyTestTable

ALTER COLUMN

MyColumnName varchar(50)

COLLATE

korean_wansung_ci_as

주의) 컬럼의 Collation을 변경 할 때는 COLLATE만 단독으로 변경하는 구문이 지원되지 않기 때문에

필드 정의 등 복잡한 부분 들까지 함께 입력 해 주어야 합니다.

4. SINGLE USER MODE

작업을 진행하다 보면 겪게 되는 Collation 변경의 진짜 장벽은 배타적 락 문제 입니다.

다른 유저가 변경되어야 하는 DB를 사용중인 경우 SQL Server는 Collation 변경을 허용하지 않습니다.

이 경우 해당 DB를 SINGLE USER MODE로 변경하는 작업이 추가로 필요 합니다.

데이터베이스 데이터 정렬 설정 또는 변경(MSDN, SQL Server 2014 기준)

http://msdn.microsoft.com/ko-kr/library/ms175835.aspx

-- SINGLE USER MODE 변경

ALTER DATABASE

MyTestDB

SET SINGLE_USER

WITH ROLLBACK IMMEDIATE

-- 작업 진행

-- MULTI USER MODE 복귀 (기본값)

ALTER DATABASE

MyTestDB

SET MULTI_USER