728x90

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;

728x90
728x90

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

728x90
728x90

조인이란?

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

 

728x90
728x90

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

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

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

 

CASE WHEN

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

더보기

--CASE사용법--

CASE WHEN 조건절 THEN 참일때 값 ELSE 거짓일때 값 END 컬럼명

 

더보기

--테이블(MY_TABLE)에서 성별(GENDER)이 001이면 여, 그게아니면 남자로 검색--

SELECT DISTINCT GENDER, CASE WHEN GENDER = '001' THEN '여' ELSE '남' END AS 성별 FROM MY_TABLE

 

 

다중 CASE WHEN

더보기

--테이블(MY_TABLE)에서 성적(SCORE)별 학점을 계산

SELECT *,

(CASE WHEN SCORE>= '90' THEN 'A학점'

WHEN (SCORE>= '80' AND SCORE < '90') THEN 'B학점'

WHEN (SCORE>= '70' AND SCORE < '80') THEN 'C학점'

WHEN (SCORE>= '60' AND SCORE < '70') THEN 'D학점'

ELSE 'F학점' END) AS '학점'

FROM MY_TABLE

 

 

IF ELSE

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

더보기

--IF 사용법--

IF 조건 참일때 값 ELSE 거짓일때 값 END 컬럼명

 

더보기


--@NUM 이 30일때 30이라고 출력, 40일경우 40이라고 출력, 아닐경우 아니라고 출력하기--

DECLARE @NUM INT

SET @NUM = 40 IF(@NUM = 30)

PRINT 'NUM은 30입니다.'

ELSE IF(@NUM=40)

PRINT 'NUM은 40입니다'

ELSE

PRINT 'NUM은 30이나 40이 아닙니다.'

 

 

728x90

'IT이야기 > MS-SQL' 카테고리의 다른 글

[MSSQL] 실습 SELECT, FROM, WHERE, BETWEEN, AND, IN, LIKE, ANY, ALL 등  (0) 2020.02.06
JOIN의 종류설명 및 사용법 & 예제  (0) 2019.10.29
SET NOCOUNT 사용법  (0) 2019.10.29
피벗테이블  (0) 2019.10.29
프로시저 사용법  (0) 2019.10.29
728x90

SET NOCOUNT란?

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

구문 : SET NOCOUNT{ON/OFF}

 

사용하는 이유

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

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

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

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

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

 

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

 

 

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

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

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

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

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

 

예제

더보기

CREATE PROCEDURE PROC_NAME (

COMPANY NVARCHAR(7),--회사

NO_TRAN NVARCHAR(20), --전표번호

PLANT NVARCHAR(7), --공장

DT_TRAN NVARCHAR(8) --작업일 )

AS

BEGIN

SET NOCOUNT ON

INSERT INTO EX_TABLE(CD_COMPANY,NO_TRAN,CD_PLANT,,DT_TRAN) VALUES(@P_CD_COMPANY,@P_NO_TRAN,@P_CD_PLANT,@P_DT_TRAN)

SET NOCOUNT OFF

RETURN

END;

 

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

 

 

728x90

'IT이야기 > MS-SQL' 카테고리의 다른 글

JOIN의 종류설명 및 사용법 & 예제  (0) 2019.10.29
조건문 (CASE WHEN, IF) 함수 사용법 & 예제  (0) 2019.10.29
피벗테이블  (0) 2019.10.29
프로시저 사용법  (0) 2019.10.29
저장 프로시져 내용 검색  (0) 2019.10.24
728x90

피벗테이블이란?

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

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

 

예제

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

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

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

 

해결방법

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

더보기

DECLARE @DT_FROM NVARCHAR(6) = '200802'

DECLARE @DT_TO NVARCHAR(6) = '200904'

SELECT MAX(DT) AS DT, SUM(QT) AS QT FROM MM_TEST

WHERE DT BETWEEN @DT_FROM AND @DT_TO

GROUP BY DT

ORDER BY DT

 

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

더보기

DECLARE @DT_FROM NVARCHAR(6) = '200802'

DECLARE @DT_TO NVARCHAR(6) = '200904'

SELECT * FROM

( SELECT MAX(DT) AS DT, SUM(QT) AS QT FROM MM_TEST

WHERE DT BETWEEN @DT_FROM AND @DT_TO

GROUP BY DT )Q

PIVOT (

SUM(QT) FOR DT IN ([200802],[200803],[200804],[200805],[200806],[200807],[200808],[200809],[200810],[200811],[200812],[200901],[200902],[200903],[200904])

)P

 

 

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

더보기

DECLARE @DT_FROM NVARCHAR(6) = '200802'; --첫번째칼럼

DECLARE @DT_TO NVARCHAR(6) = '200904'; --마지막컬럼

DECLARE @DT_NO NVARCHAR(6); --칼럼(하나)

DECLARE @DT_LAST NVARCHAR(300); --합쳐진 칼럼(문자열)

SET @DT_LAST = '' --초기화

DECLARE MYCUR CURSOR FOR --커서 선언

SELECT A.DT FROM (

SELECT DT FROM MM_TEST

WHERE DT BETWEEN @DT_FROM AND @DT_TO

GROUP BY DT)A

ORDER BY DT

OPEN MYCUR

FETCH NEXT FROM MYCUR INTO @DT_NO

WHILE(@@FETCH_STATUS=0) --반복문

BEGIN

SET @DT_LAST = @DT_LAST + '['+@DT_NO+'],' --칼럼 합치기

FETCH NEXT FROM MYCUR INTO @DT_NO

END

CLOSE MYCUR

DEALLOCATE MYCUR --반복문 종료

SET @DT_LAST = LEFT(@DT_LAST, LEN(@DT_LAST)-1) --마지막 ,제거

EXEC('

SELECT * FROM( SELECT DT, SUM(QT) AS QT FROM MM_TEST

WHERE DT BETWEEN '+@DT_FROM+' AND '+@DT_TO+ '

GROUP BY DT )Q

PIVOT ( SUM(QT) FOR DT IN ('+ @DT_LAST +') )

AS P') --문자열 쿼리 실행

GO

 

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

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

 

최종 결과

 

 

728x90

'IT이야기 > MS-SQL' 카테고리의 다른 글

조건문 (CASE WHEN, IF) 함수 사용법 & 예제  (0) 2019.10.29
SET NOCOUNT 사용법  (0) 2019.10.29
프로시저 사용법  (0) 2019.10.29
저장 프로시져 내용 검색  (0) 2019.10.24
MSSQL 함수 모음  (0) 2019.10.24
728x90

프로시저란?

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

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

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

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

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

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

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

 

프로시저 사용법

프로시저 생성 문법

더보기

CREATE PROC [프로시저명] AS [쿼리문]

프로시저 생성 예제

더보기

CREATE PROCEDURE UP_EXPRO (

@P_COMPANY NVARCHAR(500),--회사코드

@P_BIZAREA NVARCHAR(500),--고객사코드

@P_PLANT NVARCHAR(500) --공장코드

)

AS BEGIN

SET NOCOUNT ON

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SELECT A.CD_SHOP, A.BIZAREA, B.NM_BIZAREA, FROM SA_Z_DZ_POS_SHOP_WJT A

LEFT OUTER JOIN BIZAREA B ON A.COMPANY = B.COMPANY AND A.BIZAREA = B.BIZAREA

WHERE A.CD_COMPANY = @P_CD_COMPANY AND( ISNULL(@P_CD_BIZAREA,'')='' OR @P_CD_BIZAREA = A.CD_BIZAREA)

SET NOCOUNT OFF RETURN END;

 

 

프로시저 1개 조회

더보기

sp_helptext [프로시저명]

전체프로시저 조회

더보기

select * from INFORMATION_SCHEMA.ROUTINES

프로시저 삭제

더보기

DROP PROCEDURE [프로시저명]

 

728x90

'IT이야기 > MS-SQL' 카테고리의 다른 글

SET NOCOUNT 사용법  (0) 2019.10.29
피벗테이블  (0) 2019.10.29
저장 프로시져 내용 검색  (0) 2019.10.24
MSSQL 함수 모음  (0) 2019.10.24
DB Collation 변경, SINGLE USER MODE  (0) 2019.10.23
728x90

저장 프로시져 내용 검색

SELECT o.name

FROM sysobjects o JOIN syscomments c

on o.id = c.id

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

 

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

o.type 구분 확인.

728x90

'IT이야기 > MS-SQL' 카테고리의 다른 글

피벗테이블  (0) 2019.10.29
프로시저 사용법  (0) 2019.10.29
MSSQL 함수 모음  (0) 2019.10.24
DB Collation 변경, SINGLE USER MODE  (0) 2019.10.23
Image, varbinary를 Text로 변환  (0) 2019.09.24
728x90

날짜 함수
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 -- 전체평균

728x90

'IT이야기 > MS-SQL' 카테고리의 다른 글

프로시저 사용법  (0) 2019.10.29
저장 프로시져 내용 검색  (0) 2019.10.24
DB Collation 변경, SINGLE USER MODE  (0) 2019.10.23
Image, varbinary를 Text로 변환  (0) 2019.09.24
MSSQL 설치 정보 확인  (0) 2019.01.21
728x90

데이터베이스 데이터 정렬 설정 또는 변경 (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

728x90

'IT이야기 > MS-SQL' 카테고리의 다른 글

저장 프로시져 내용 검색  (0) 2019.10.24
MSSQL 함수 모음  (0) 2019.10.24
Image, varbinary를 Text로 변환  (0) 2019.09.24
MSSQL 설치 정보 확인  (0) 2019.01.21
데이터베이스 위치 변경하는 방법  (0) 2019.01.21

+ Recent posts