MS SQL - 기본 개념 정리 (SSMS)

 SSMS  (SQL Server Management Studio)  

 

SQL Server를 포함해 Azure SQL Database와 같은 모든 SQL 인프라를 관리하는 통합환경도구

 

[기본 단축키]

 

ctrl + n : 쿼리창 켜기

f5 : 쿼리문 실행

테이블명 드래그 후 alt + f1 : 테이블의 열 목록 확인 가능

 

그 외 단축키 관련 참고 내용

https://unions5.tistory.com/99

 CRUD 관련 내용  

 

In( 'a' , 'b' , 'c' ) : a,b,c 중 하나라도 있으면 true

Null 값/ 아닌 값 찾기 : IS NULL / IS NOT NULL

상위 n개 조회 :  Select TOP n 값

지정한 행 개수만큼 건너뛰고 출력 : OFFSET n(행 개수) ROWS
>> OFFSET은 무조건 ORDER BY와 함께 사용해야함
지정한 행 개수만큼 건너뛰고 지정한 행 개수 만큼 출력
 : OFFSET n(행개수) ROWS 
  FETCH NEXT m(행개수) ROWS ONLY

<LIKE>

%A : A로 시작
A% : A로 끝
%A% : A를 포함
++ 만약 %가 있는 행만 추출하고 싶다면
LIKE '%#%%' ESCAPE '#' 로 작성 , # 자리에는 ! , & , / 등 실제 문자열에 쓰이지 않는 것으로!

A_ : A로 시작하면서 뒤의 글자는 무엇이든 상관 없음, 전체 글자수는 2글자
_A : A로 끝나면서 전체 글자수는 2글자
_A_ : 세글자 중 가운데 글자만 A
ex) A__C : A와 C 사이에 두글자 있는 경우

[A,B,C]% == [A-C]% : A,B,C  중 하나의 글자로 시작하는 모든 문자열
A[^A,^B,^C] : A로 시작하면서 두번째 글자가 A,B,C가 아닌 문자열

DISTINCT 컬럼명 FROM 테이블명 : 중복 데이터 제거
++ GROUP BY로도 중복데이터 제거 가능

 

USE 사용하여 데이터베이스 선택 OR 그냥 클릭?

INSERT INTO 테이블명 ( 컬럼명...) 
VALUES( 데이터들..)
>> 컬럼명 생략가능

문자열 앞에 N 쓰는 경우 : 유니코드로 인식되어서 다른 언어로 프로그램 실행시 글자가 깨지지 않음
++ nvarchar 로 자료형 지정

부모자식 관계가 있을 경우 입력과 삭제
부모 입력 후 자식 입력
자식 삭제 후 부모 삭제 

 

 

 

 Join 관련 내용  

 

CROSS JOIN :  교차조인
모든 경우의 수를 모두 포함한 것이라고 생각하면됨

SELF JOIN : 동일한 테이블을 join 
전일 대비 비교와 같은 경우에 쓰임
전일대비 비교시 on 문 : a.date = dateadd(day, -1, b.date)

ANY : 값이 하나라도 만족하는 경우 반환
< ANY : 최소값 반환
> ANY : 최대값 반환

서브쿼리의 값이 둘 이상의값이면 비교연산자( = ,  >= ) 사용이 불가능하기에
이런 상황일때 ANY를 사용함

Exists : 서브쿼리 내의 결과값이 하나라도 있으면 True , 그 외에는 False 반환
True 이면 메인 쿼리 실행됨

From 문에 서브쿼리 사용 : Inline View

컬럼명이 예약어(open, high, low...)와 동일한 경우 컬럼명에 대괄호 붙여 사용하기 
ex) [open]

 

 

 CTE ( 공통 테이블 식 )  

 

데이터 베이스에 없는 테이블이 필요할 때 사용

WITH cte테이블명 ( 열이름1, 열이름2...)
AS
( select 문을 통해 각 컬럼에 해당하는 데이터 반환 )
SELECT * FROM cte테이블명 WHERE 조건

>> select 로 반환한 열 개수와 cte 테이블의  열 개수가 동일해야함

UNION : 중복데이터 제거
UNION ALL : 중복데이터 포함
>> 다른 쿼리에서 중복을 제거한 다음 UNION ALL 사용 권장

INTERSECT : 내부조인과 비슷하지만 중복결과 걸러 내 반환한다는 차이점이 있다
EXCEPT : NOT IN과 비슷하지만 결과값에서 중복을 제거한 유일한 행을 반환한다는 차이점이 있다
>> INTERSECT는 교집합 , EXCEPT는 차집합 같은 개념 
EXCEPT의 경우에는 순서에 따라 결과가 달라질수있으므로 주의

재귀 CTE
WITH cte 테이블명 ( 열이름1, 열이름2... )
AS 
( SELECT * FROM 테이블 A  
UNION ALL 
SELECT * FROM 테이블 B)
SELECT * FROM cte 테이블명

 

 

 문자열 함수  

 

문자열 연결 : CONCAT
CAST( 숫자형데이터 AS nvarchar(50) ) >> 문자열로 자료형 변환
CONVERT( nvarchar(50) , 숫자형데이터 ) >> 문자열로 자료형 변환

ISNULL ( 컬럼명 , 대체할 값 ) : null 값은 대체할 값으로 대체
COALESCE ( 컬럼명1, 컬럼명2, ... ) : 순차적으로 null값이 아닌 경우가 나올때까지 찾아서 반환

TRIM : 공백 + 마침표 제거가능
ex) SELECT TRIM ( '.,! ' FROM '    #    Do It ! SQL     . ')
>> #    Do It SQL

LEN : 앞의 공백은 개수에 포함하지만 뒤의 공백은 포함하지 않음
CHARINDEX( 특정 문자 , 문자열 ) : 특정문자까지의 길이 반환 문자가 없으면 0
LEFT( 문자열 , 숫자 ) , SUBSTRING ( 문자열, 시작번호, 끝번호 )
REPLACE( 원본문자열,  변경하려는 문자열 , 변경 문자열 )
REPLICATE( 문자열, 반복횟수 ) : 문자열 반복할 때 사용
SPACE(횟수) : 횟수만큼 공백 반환
REVERSE(문자열) : 역순으로 표시
STUFF( 문자열 , 위치 , 개수 , 새 문자열 ) 
: 문자열에서 지정한 위치( 8이면 7번째 위치)부터 개수만큼 삭제하고 새 문자열을 끼워 넣음

 

 

 날짜 함수  

GETDATE : 현재 시간
DATEADD( 날짜 형식 , 숫자 , 날짜 ) : 날짜에서 지정한 날짜 형식(연월일...)에 숫자만큼 더한 날짜 반환
DATEDIFF( 날짜형식 , 시작날짜, 종료날짜 ) : 날짜 차이 구하기
DATEPART( 날짜형식 , 날짜 ) , DATENAME( 날짜형식 , 날짜 ) : 특정 날짜 형식의 값 반환(1월이면 1)
>> DATENAME은 월요일이면 월요일 반환 DATEPART는 1 반환
>> 그냥 YEAR() , MONTH() 쓰는게 더 나음 , 요일은 datename으로 weekday쓸것

날짜 포맷 번호
작성법 : SELECT CONVERT(varchar , getdate() , 번호)
1 : mm/dd/yy
2 : yy.mm.dd
111 : yyyy/mm/dd
112 : yyyymmdd

 

 집계 함수  

 

21억을 넘어가는 숫자는 CAST( 컬럼명 AS bigint ) 를 통해 자료형을 변환해주면 오류가 발생하지 않는다.

 

SUM , AVG, MIN , MAX 는 mysql과 동일

 

ROLLUP :

Group BY Rollup ( A, B) >> A 의 각 항목에 대한 부분합이 마지막에 

A	B	값
a	null	A가 a인  행들의 값의 부분합

위와 같은 형태로 나옴

마지막에는 A와 B가 null 으로 나오고 총 합이 나옴

 

CUBE :

GROUP BY CUBE( A,B) >> (A,B) , (NULL , B) , (A, NULL) , (NULL,NULL) 의 고윳값을 집계하는 그룹을 만듦

 

STDEC : 모든 값의 편차

STDEVP : 모집단의 표준편차

 

 수학 함수  

 

 

ABS : 절댓값 반환

SIGN : 양수, 음수, 0 판단 >> 1, -1, 0

CEILING : 지정한 숫자에서 올림한 정수

FLOOR : 지정한 숫자에서 내림한 정수

ROUND( 숫자 , 소수점 자리) : 지정한 숫자의 반올림한 정수 , 소수점 자리까지 반올림함

LOG(A,B) : B는 밑의 자리, 생략하면 e 로 자연로그로 계산

EXP(n) : e의 n제곱값 반환

POWER(a, b) : a의 b제곱

SQUARE(a) : a의 제곱값

SQRT : 제곱근 반환

RAND : 난수 생성

 

 

 순위 함수  

 

ROW_NUMBER() OVER ( ORDER BY 컬럼명 ) : 유일값으로 순위 부여

>> ORDER BY에 한가지 이상의 정렬조건 넣어 그에 따른 순위 부여 가능

 

PARTITION : 그룹별로 순위 부여할때 사용

>>  순위함수() OVER(PARTITION BY 컬럼명1 ORDER BY 컬럼명 2 DESC/ASC)

>> 컬럼명1의 각각의 그룹에 대한 순위를 부여함 

 

RANK : 같은 순위일때 같은 값 부여 ( ex : 1 2 2 4 )

DENSE_RANK : 같은 순위 개수 무시 (ex : 1,2,2,3)

 

NTILE(숫자) OVER (ORDER BY 정렬조건) : 숫자 만큼씩 행들을 그룹화하여 해당하는 그룹별로 순위 부여

 

 

 분석 함수  

 

LAG (컬럼명) OVER ( ORDER BY 정렬조건 )  , 컬럼명

LEAD(컬럼명) OVER ( ORDER BY 정렬조건 ) , 컬럼명

>>

LAG : 현재 행에서 앞의 행에 접근

LEAD : 현재 행에서 뒤의 행에 접근

컬럼명 뒤에 숫자(OFFSET) 을 통해 몇번째 앞/뒤 행인지를 설정할 수 있음

ex) LAG( 컬럼명 , 2 ) : 두번째 앞의 행부터 , 행의 값이 없으면 NULL 반환

 

CUME_DIST() OVER (ORDER BY 정렬조건) : 0 초과 1 이하 범위의 누적 분포 값 반환

++ 가장 높은 값을 1으로 계산하고 이에 대한 누적분포 값 계산하여 반환함

 

PERCENT_RANK() OVER (ORDER BY 정렬조건) : 가장 높은 값을 1로 기준하여 이에 대한 분포 순위 반환

 

PERCENTILE_CONT , PERCENTILE_DISC : 중앙값 계산

 

FIRST_VALUE , LAST_VALUE : 첫번째 / 마지막 행의 값 반환