MS SQL 명령어 모음

목차1-1 DB정보 알기1-2 DBCC1-3 로그백업1-4 데이타베이스 백업1-5 변수의 크기1-6 문자열 변형1-7 UNION 사용1-8 테이타입력1-9 데이타베이스 의존성확인1-10 동적쿼리1-11 CASE1-12 동적테이블1-13 테스트 테이타 입력1-14 블로킹 모니터링1-15 function1-16 전표번호 생성2-1 테이블생성2-2 identity()함수2-3 매월 첫날과 마지막날2-4 원격쿼리

---

1-1 DB정보 알기

--시스템에 생성된 테이블 보기select * from sysobjects where type='U'--버전알아내기select @@version--서비스 실행상태보기net start			-- 현재 시작되어 있는 서비스net start	 mssqlservernet start mssql$instnet stop sqlagent

1-2 DBCC

-- 데이타베이스 용량 줄이기dbcc shrinkdatabase(testdb,10)-- 데이타베이스 파일 줄이기 10M로 줄이기dbcc shrinkfile(testdb,10)--테이블 검사dbcc checktabledbcc checkdb--테이블, 색인의 조각비율dbcc showcontig--테이블 색인을 재작성 filefactor 20dbcc dbreindex(test,'',20)dbcc shwo_statistics---지정한 시스템 테이블의 일관성과 테이블간의 일치하지 않는 내용 검사dbcc checkcatalog test--I/O량 확인set statistics io on

1-3 로그백업

--로그백업backup log pubs to pubslog--로그를 그냥 비울때backup log pubs with no_logbackup log pubs with truncate_only--로그 점유율dbcc sqlperf(logspace)

1-4 데이타베이스 백업

--백업backup database pubs to disk='c:\temp\pubs' with init--복원restore database pubs to disk='c:\temp\pubs'

1-5 변수의 크기

bigint		8바이트 정수int		4바이트 정수smallint		2바이트 정수tinyint		1바이트 정수bit		0,1float		부동소숫점(15자리)real		부동소숫점(7자리)datetime		8바이트 날짜시간smalldatetime	4바이트 날짜시간char, varchar	8000자이하nchar,nvarchar	4000자 이하 유니코드text		8000자 이상Ntext		4000자 이상 유니코드binary, varbinary	 8000 바이트 이하 이진image		8000 바이트 이상 그림money		8바이트 화폐smallmoney	4바이트 화폐table		테이블자체sql_variant	text, ntext, image, timestamp 를 제외한 모든 자료형 cursor		커서timestamp	유일한수

1-6 문자열 변형

convert(char(30), title)		--30글자짜리 문자열로 변형substring(title,1,30)			-- 전체문장중 30글자만 표시하고 나머지는 공백으로 남겨둠	cast(title as char(30))		-- 문자열변경replicate(0,5)			--0을 5번 반복

select getdate()--날짜select convert(varchar(30), getdate(),111)select convert(varchar(30), getdate(),112)--시간select convert(varchar(30), getdate(),108)

-- 앞자리 0으로 채우기create function fn_FillStr(	@No bigint,	@Length int,	@ReplaceChar char(1))returns varchar(8000)asbegin	return 	( 		replicate(@ReplaceChar, @Length - len(convert(varchar(20), @No))) + Convert(varchar(20), @No)	)end goselect dbo.fn_FillStr(123,6,'0')--3자리마다 콤마찍기create function fn_CommaFormat(	@strNumber varchar(30),	@chrComma char(1))returns varchar(50)asbegin	if len(@strNumber) >3		set @strNumber = dbo.fn_CommaFormat(substring(@strNumber,1,len(@strNumber)-3),',') + @chrComma + Right(@strNumber,3)		return(@strNumber)endgoselect dbo.fn_CommaFormat(12345678,',')go

1-7 UNION 사용

UNION ALL	-- 중복데이터도 모두 가져옴UNION		-- 중복데이터는 삭제

1-8 테이타입력

--새로운 테이블에 입력 : 색인이나 키는 복사하지 않는다.select * into test2from t1-- 이미 존재하는 테이블에 테이터 입력insert test2	select * 	from test1

1-9 데이타베이스 의존성확인

sp_help testsp_helpconstraint testsp_depends testsp_helpindex test--모든 제약 중지alter table test nocheck constraint all--모든 제약 시작alter table testcheck constraint all

1-10 동적쿼리

declare @sql varchar(255)set @sql = 'select count(*) from 'set @sql = @sql + 'titles'exec(@sql)

1-11 CASE

select ... ,	case whe sex=1 then '남' else '여' end as '성별'from ...

1-12 동적테이블

declare @i int, @sql varchar(1000)set @i=0while @i < 30begin	set @i = @i + 1	set @sql = 'create table a' + convert(varchar(3),@i) + ' ('	set @sql = @sql + 'id int)'	select @sql	exec (@sql)endgoselect name from sysobjects where type='U' and like 'a%' order by name

1-13 테스트 테이타 입력

--테이블만들기use tempdbdrop table testgocreate table test(	id int indentity,	name char(80) default 'default name',	date smalldatetime default getdate())create clustered index test_idxon test(id)go--실행코드set nocount ondeclare @i smallintset @i=0while @i < 10000begin	set @i=@i+1	insert test default valuesendset nocount offselect max(id() from test --결과확인

1-14 블로킹 모니터링

sp_whosp_blockkill 51dbcc inputbuffer (52)-- 분산트랜젝션 처리를 하는 도중 오류가 발생하면 자동적으로 롤백처리set xact_abort on

1-15 function

create function fn_test(@id varchar(4)) return tableasreturn(	select * from where id=@id)--사용select * from dbo.fn_test('123')

1-16 전표번호 생성

--날짜와 번호로 생성declare @OrderNum bigint--select @OrderNum = max(OrderNum) from ordersset @OrderNum=2if @OrderNum is null or @OrderNum = ''	set @OrderNum = substring(convert(char(8), getdate(),112),1,8) + '00001'else	set @OrderNum = substring(convert(char(8), getdate(),112),1,8) +	right(replace(str(convert(int,right(@OrderNum,5))+1), ' ','0'),5)select @OrderNum

2-1 테이블생성

create database test on primary(	NAME = N'test_data',	FILENAME = N'c:\temp\test_Data.MDF',	SIZE=5,	MAXSIZE=100,	FILEGROWTH = 10%)log on(	NAME = N'test_log',	FILENAME = N'c:\temp\test_Log.MDF',	SIZE=2,	FILEGROWTH = 1MB)

2-2 identity()함수

select indentity(int,1,1) as Num, Nameinto #testfrom test

2-3 매월 첫날과 마지막날

declare @CurrDate datetimeset @CurrDate = getdate()select dateadd(dd, -1*day(@CurrDate)+1, @CurrDate) as 시작일,dateadd(mm, 1, @CurrDate) - day(@CurrDate) as 마지막일

2-4 원격쿼리

select * from openquery([killer\sql2000],'select * from pubs.dbo.sales')select * from [killer\sql2000].pubs.dbo.sales

-- DB를 선택해 내림차순으로 authors테이블의 데이터를 추출
USE pubs
SELECT *
FROM authors
ORDER BY au_lname ASC, au_fname ASC

-- 테이블명, 소유자, 타입, 만든날짜등 테이블에 관한 모든정보를 가져오는 프로시져
EXEC sp_help "테이블명"
EXEC sp_columns "테이블명"

-- 컬럼 제목바꾸기
SELECT title_id AS Title_No, pub_id AS 출판사번호, price, title FROM titles

-- 사용자변수와 시스템 함수(시스템 변수/전역변수)
-- SQLServer는 정의된 변수만을 전역으로 사용할수 있다.
@@error
@@identity
@@lock_timeout
@@nestlevel
@@rowcount
@@trancount

-- decimal, numeric 숫자유형(차이없음)
-- decimal(precision, scale) 예를들어 (10, 5)의 경우 전체 10자리 숫자중에서
-- 소수점 이하 5자리를 사용할수 있는 의미
decimal[(p, [s])] precision의 범위, 1~38 디폴트 : 18, scale의 범위 1~precision, 디
폴트 : 0

-- SELECT에서의 자료형 바꾸기
--1)
SELECT title_id AS 타이틀넘버
, pub_id AS 출판사번호
, price
, CONVERT(char(30), title) AS 줄인제목
FROM titles
--2)
SELECT title_id AS 타이틀넘버
, pub_id AS 출판사번호
, price
, SUBSTRING(title, 1, 30) AS 줄인제목
FROM titles

-- CONVERT() / CAST()
-- CONVERT()는 자료형을 바꾸는 함수로 다양한용도로 사용한다. 이기능은 SQLServer의
고유
-- 기능이기 때문에 ANSI와 호환되지않는다.
CONVERT (datatype[(length)], expression[, style])
-- style은 날짜함수에서 사용한다.

SELECT CONVERT(int, '32.2')
SELECT CONVERT(int, '32')
SELECT CONVERT(varchar(5), 32.2)
SELECT CONVERT(float, 32)

-- 숫자에 대한 산술적 연산함수(자주 쓰는것만...)
CEILING(numeric_expr) -- 주어진값보다 크거나 같은, 가장작은 정수
FLOOR(numeric_expr) -- 주어진값보다 작거나 같은, 가장큰 정수
RAND(seed) --램덤수
ROUND(numeric_expr, length) --반올림

SELECT title_id, price, FLOOR(price) AS Floor, CEILING(price) AS ceiling, ROUND
(price, )
AS Round FROM titles

-- 문자에 대한 함수(자주 쓰는것만...)
+
CHARINDEX('pattern', expression)
LEFT(character_expression, integer_expression)
LEN(string_expression)
LOWER(char_expression)
LTRIM(char_expression)
PATINDEX('%pattern%', expression)
REPLACE(string_expression, string_expression, string_expression)
REPLICATE(char_expression, integer_expression)
RIGHT(char_expression, integer_expression)
RTRIM(char_expression)
SPACE(integer_expression)
STUFF(char_expression, start, length, char_expression)
SUBSTRING(_expression, start, length)
UPPER(char_expression)

-- 날짜에 대한 함수
SELECT CONVERT(varchar(30), GETDATE(), 9)
SELECT CONVERT(varchar(30), GETDATE(), 2)
SELECT CONVERT(varchar(30), GETDATE(), 102)

-- 조건에 맞는 행 가져오기
SELECT title_id, qty FROM sales WHERE qty >= 20
SELECT title_id, qty FROM sales WHERE title_id = 'bu1032'

-- 비교문 (=, >, <, >=, <=, <>, !=, !>, !<)
-- 정렬
SELECT title_id, qty FROM sales ORDER BY qty ASC
SELECT title_id, qty FROM sales ORDER BY qty DESC

-- 몇 개만 가져오기
SELECT TOP 5 title_id, qty FROM sales

-- 범위
SELECT title_id, qty FROM sales WHERE qty EETWEEN 10 AND 20
SELECT title_id, qty FROM sales WHERE qty >= 10 AND qty <= 20

-- 목록
SELECT title_id, qty FROM sales WHERE title_id IN ('BU1032', 'BU1111', 'MC3021')
SELECT title_id, qty FROM sales WHERE title_id = 'BU1032' OR 'BU1111' OR 'MC3021'

-- 문자열 / LIKE와 패턴 매칭
/*
_(underscore) : 어떤 것이든 한문자가 와야한다.
% : 아무것도 없는 경우를 포함하여 어떤것이든 상관없다.
[] : []안에 있는 글자들
[^] : ^다음에 있는 글자를 제외한 다른것이 와야한다.
book_
book%
%book%
[st]ing
[b-f]ing
M[^c]%
자세한 것은 온라인 설명서 참조
*/
SELECT title_id, title FROM titles WHERE title LIKE '%computer%'
SELECT title_id, title FROM titles WHERE title LIKE 'computer%'
SELECT title_id, title FROM titles WHERE title LIKE 'computer_'

-- PATINDEX는 해당 패턴이 다음 문자열에 있는지 알려준다.
SELECT PATINDEX('%Mi%', 'James Mike')

-- 중복된 행 제거 - DISTINCT
SELECT DISTINCT title_id FROM sales ORDER BY title_id

-- 요약 정보 처리
/*
AVG ([ALL|DISTINCT] expression) 표현식의 전체나 각각의 평균값
COUNT ([ALL|DISTINCT] expression) 표현식의 전체나 각각의 개수
COUNT (*) 선택된 모든행의 개수 주의 NULL값을 계산되지 않는다.
MAX (expression) 표현식에서 가장 큰값
MIN (expression) 표현식에서 가장 작은 값
SUM ([ALL|DISTINCT] expression) 수치 표현식에서 전체나 각각의 합계
*/
SELECT MAX(qty), MIN(qty) FROM sales
SELECT COUNT(*) FROM sales
SELECT COUNT(title_id) FROM sales

-- ISNULL NULL 값을 다른값으로 바꾸기
-- ISNULL (표현식, 널일때의 값)
SELECT AVG(ISNULL(price, 0)) FROM sales

-- GROUP BY / HAVING
SELECT title_id, sum(qty) AS SUMqty FROM sales GROUP BY title_id HAVING SUM(qty)
>= 30
SELECT title_id, sum(qty) AS SUMqty FROM sales GROUP BY ALL title_id

-- COMPUTE / COMPUTE BY
-- 그룹별로 상세 내역과 그룹 합계를 한꺼번에 출력하는 함수
SELECT type, title_id, price FROM titles ORDER BY type COMPUTE avg(price) BY type

-- ROLLUP과 CUBE (표현식은 10개를 넘을수 없다. GROUP BY ALL을 사용할수 없다.)
-- 각 평균과 각 type별 그룹 평균
SELECT type, pub_id, AVG(price)
FROM titles GROUP BY type, pub_id WITH ROLLUP

-- 각 평균과 각 type별 그룹 평균
SELECT type, pub_id, AVG(price)
FROM titles GROUP BY type, pub_id WITH CUBE

-- JOIN (조인)
-- INNER JOIN (두 값을 비교해 일치하는 데이터만을 가져온다.)
SELECT title_ price, pub_name FROM titles, publishers
WHERE titles.pub_id = publishers.pub_id

SELECT title_id price, pub_name FROM titles INNER JOIN publishers
ON titles.pub_id = publishers.pub_id

-- OUTER JOIN (어느 한쪽의 데이터를 가져온다.)
SELECT title_id price, pub_name FROM titles LEFT OUTER JOIN publishers
ON titles.pub_id = publishers.pub_id

-- 흐름제어문
/*
BEGIN...END 명령문의 시작 / 끝을 블록화한다.
IF ELSE 조건에 맞는 처리를 한다.
WHILE 조건에 맞는동안 반복한다.
BREAK 가장안쪽의 WHILE반복을 무조건 빠져나간다.
CONTINUE 가장안쪽의 WHILE 반복을 무조건 다시 시작한다.
RETURN 무조건 끝낸다.
GOTO 라벨 무조건 라벨부분으로 건너뛴다.
WAITFOR 지정된만큼 지연을 한다.
CASE 주어진 조건에 따라 다른 처리를 한다.
/**/ 주석처리
-- 주석처리
DECLARE 변수를 정의 한다.
SET(SELECT) 변수를 초기화한다.
EXECUTE 사용자정의 함수, 시스템 프로시저, 사용자정의 저장프로시저, 확장프로시저를
수행한다.
PRINT 주어진 메시지를 출력한다.
RAISERROR 주어진 메시지나 오류번호를 출력한다.
FORMAT

[출처] MSSQL 명령어 정리|작성자 sunbeoriki