MS-SQL 명령어 정리

-- 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

-- 테이블명으로 테이블들 검색
sp_tables '%테이블명%'

-- 컬럼명 검색
sp_columns @table_name='%', @column_name='%컬럼명%'

-- 실행계획결과를 표로 보기
set statistics profile on

-- 테이블의 여러정보(인덱스 등등) 보기
테이블 선택한 상태에서 alt + f1 누름 (이병화가 알려줌 -_-)

-- 뷰 또는 프로시저 스키마 보기
sp_helptext 뷰또는 프로시저이름

-- 지정한 테이블의 데이터와 인덱스에 대한 조각화 정보를 표시
DBCC SHOWCONTIG(테이블명)



-- 특정 단어가 들어간 뷰/프로시저/펑션/트리거 찾기
-- V(뷰), P(프로시저), FN(펑션), TR(트리거)
Select
    distinct c.name as owner, a.name as objname, a.xtype
From
    dbo.sysobjects a with (nolock)
    inner join dbo.syscomments b with (nolock) on a.id = b.id
    inner join dbo.sysusers c with (nolock) on a.uid=c.uid
Where
    a.category < 2
    and b.text like lower('%특정단어%')
Order By
    owner, objname, xtype