SQL

SQL(Structured Query Language)

• DDL(데이터 정의어) : 테이블과 같은 데이터 구조를 정의하는데 사용되는 명령어들로 데이터 구조와 관련된 명령어

• DML(데이터 조작어) : 데이터베이스에 저장된 데이터를 조회하거나 삽입, 수정, 삭제하는 등의 역할을 하는 언어

• DCL(데이터 제어어) : 데이터베이스에 접근하고 객체들을 사용하도록 권한을 주고 회수하는 명령어

• TCL(트랜잭션 제어어) : 논리적인 작업의 단위를 묶어서 DML에 의해 조작된 결과를 작업단위 별로 제어하는 명령어

SQL 문법 순서

SQL 실제 실행 순서

• FROM : 각 테이블 확인

• ON : 조인 조건 확인

• JOIN : 테이블 조인(병합)

• WHERE : 데이터 추출 조건 확인

• GROUP BY : 특정 칼럼으로 데이터 그룹화

• HAVING : 그룹화 이후 데이터 추출 조건 확인

• SELECT : 데이터 추출

• DISTINCT : 중복 제거

• ORDER BY : 데이터 정렬

테이블 구조 보기

DESC tablename

DB 만들기

use master
GO
if NOT EXISTS (
	select name from sys.databases
	where name = N'DevelopmentDB'
)
create database [DevelopmentDB]
GO

TABLE

테이블 조회

• 내림차순 정렬

SELECT 필드명 FROM 테이블명 ORDER BY 필드명 DESC;

• 오름차순 정렬

SELECT 필드명 FROM 테이블명 ORDER BY 필드명 ASC;

테이블 생성

CREATE TABLE users(
id INTEGER PRIMARY KEY,
name TEXT
);

CREATE TABLE 테이블명 (
	컬럼1 데이터타입 제약조건,
	컬럼2 데이터타입 제약조건,
	컬럼3 데이터타입 제약조건,
	...
);
// NOT NULL : 공백을 허용하지 않겠다는 옵션
// AUTO_INCREMENT : 자료형이 INT(정수형)일때 적용 가능, 데이터가 많아질 때마다 1씩 자동 증가
// PRIMARY KEY : 중복 값 허용 안하는 컬럼 1개 선택 (테이블 내 고유값 필요한 경우)

테이블 삭제

DROP TABLE users;

테이블 변경

• table 이름 변경

ALTER TABLE <기존 table명> RENMAE TO <변경하고 싶은 table명>

• 새로운 column 추가

ALTER TABLE <table명> ADD COLUMN <추가할 colum명> <column 타입> <옵션>;

• column 이름 변경

ALTER TABLE <table명> RENAME COLUMN <기존 column명> TO <변경하고 싶은 column명>;

• 테이블 속성 변경

ALTER TABLE [테이블명] change [해당column명] [바꿀 column명] [변경할속성]

• 데이터 제약 조건 추가, 삭제

-- primary key
ALTER TABLE [테이블명] ADD CONSTRAINT primary key (컬럼명);

-- foreign key
ALTER TABLE [테이블명] ADD CONSTRAINT foreign key (컬럼명)
references [부모테이블명] (pk컬럼명)

DB

Entity(엔터티)

• 테이블

• 업무에서 필요하고 유용한 정보를 저장하고 관리하기 위한 집합적인 것이다.

• 인스턴스의 집합

• 속성이 존재해야하고 다른 엔터티와 최소 한 개 이상의 관계가 있어야함

Attribute(속성)

• 열(column)

• 인스턴스로 의마상 더이상 분리되지 않는 최소의 데이터 단위

Relationship(관계)

• 행(row)

• 데이터베이스에 저장된 데이터 내용의 전체 집합을 의미

Identifier(식별자)

• 하나의 엔터티 내에 구성되어 있는 여러 속성들 중에 엔터티를 대표할 수 있는 속성

• 하나의 엔터티 안에는 반드시 유일한 식별자가 존재해야 함

데이터 삽입 (INSERT)

INSERT INTO users (name, age) VALUES ('홍길동', 23);

동시에 여러 행 INSERT 방법

INSERT INTO users (name, age) VALUES ('가나다',20), ('hongil',44)

select된 결과 INSERT 방법

INSERT INTO dbo.users (deptno, name, age) 
     SELECT deptno + 1
          , 'tansan'
          , 30
       FROM users
      WHERE deptno IN (60, 70, 80)

특별한 값 INSERT 방법

--NULL 값 넣기
insert into s_customer values (216, 'Tester', null, null, 'good' 2, 12, null);
-- Empty String 넣기
insert into s_customer values (216, "", "", "", 'good' 2, 12, "");
-- 함수 사용하여 insert하기
insert into s_customer values (27,"", 'Student', USER, SYSDATE, null);
-- USER : 현재 USER 이름, SYSDATE : 현재 시스템 일자,시간

고유키 값 만들기(Sequence 생성기 이용)

Sequence 확인

select object_name from user_objects where object_type = 'SEQUENCE';

고유키 생성 및 입력

insert into s_dept(id, name, region_id) values (s_dept_id.NEXTVAL, 'Education', 1);
-- Sequence_name.NEXTVAL : 현재값의 다음값을 주고 자신은 다음값을 가진다.
-- Sequence_name.CURRRVAL : 현재값을 준다.

• 한번 부여된 Sequence 값은 되돌릴 수 없다.

• 재정의하여 초기값을 재설정 가능

column 선택 조회 (SELECT)

전체 테이블 *

LIMIT

SELECT rowid, name FROM users LIMIT 2;

OFFSET

SELECT rowid, name FROM users LIMIT 1 OFFSET 2;

레코드 삭제 (DELETE)

DELETE FROM users WHERE rowid=2;
SELECT rowid, * FROM users;

rowid  name  age  address
-----  ----  ---  -------
1      홍길동   30   서울
3      가나다   23   부산

수정 (UPDATE)

UPDATE users SET name="홍길동", address="제주도" WHERE rowid='3'
SELECT * FROM users;

rowid  name  age  address
-----  ----  ---  -------
1      홍길동   30   서울
3      홍길동   23   제주도

UPDATE dbo.dept // 스키마 명
   SET dname = 'SALES TEAM' // 업데이트 컬럼 = 업데이트값값
     , loc = 'TEXAS'

TRUNCATE

• 테이블 내 데이터를 삭제하는 쿼리

• 다만 테이블을 삭제하는 것이 아닌 테이블 내 데이터를 삭제하여 소위 테이블 껍데기는 남아 있음

• AUTO COMMIT이여서 한번 삭제하면 복구 X

DROP

• 테이블 전체를 삭제하는 쿼리

• AUTO COMMIT이여서 한번 삭제하면 복구 X

기타 함수

• COUNT : 선택한 행의 레코드 갯수 조회

SELECT COUNT(*) FROM users;
// users 테이블 전체 레코드 갯수

• AVG : 평균

SELECT AVG(age) FROM users WHERE age>=30;
// 30살 이상인 사람들 나이의 평균

• MAX : 가장 높은 값

SELECT name, MAX(age) FROM users;
// 나이가 가장 많은 사람과 그 나이를 조회

wildecards(%, _)

• % : 이 자리에 문자열이 존재할 수도 아닐 수도 있다

• _ : 무조건 단일 문자가 들어가야 한다

SELECT * FROM users WHERE name LIKE '%동';

NULL 체크

WHERE col_name is not null
WHERE col_name is null

SELECT ANIMAL_TYPE, INFNULL(NAME, 'No name') NAME, SEX_UPON_INTKE FROM ANIMAL_INS

연산자 우선순위

1. 모든 비교 연산자

2. NOT

3. AND

4. OR

AS

GROUP BY

• count

• sum

• max

• min

• avg

JOIN

데이터베이스에서 ‘두 개 이상의 테이블’을 연결하여 ‘하나의 결과의 테이블’로 만드는것을 의미

• Inner Join

• Outer Join

Inner JOIN

SELECT * FROM 테이블1 INNER JOIN 테이블2 ON 테이블1.열 = 테이블2.열;

SELF INNER JOIN

SELECT 테이블1.열, 테이블2.열
FROM 테이블1 t1
JOIN 테이블1 t2
ON 테이블1.열 = 테이블2.열;

OUTER JOIN

• 어느 한쪽 테이블이 기준이 되어 다른쪽 테이블의 연결되는 조건에 상관없이 기준이 되는 테이블은 무조건 추출되는 조인이다.

LEFT OUTER JOIN

SELECT *
FROM 테이블1
LEFT JOIN 테이블2
ON 테이블1.열 = 테이블2.열;

RIGHT OUTER JOIN

SELECT *
FROM 테이블1
RIGHT JOIN 테이블2
ON 테이블1.열 = 테이블2.열;

FULL OUTER JOIN

SELECT *
FROM 테이블1
FULL OUTER JOIN 테이블2
ON 테이블1.열 = 테이블2.열;

JOIN 정리본

INNER JOIN (내부조인)은 두 테이블을 조인할 때 두 테이블에 모두 지정한 열의 데이터가 있어야한다. OUTER JOIN (외부조인)은 두 테이블을 조인할 때 1개의 테이블에만 데이터가 있어도 결과가 나온다. CROSS JOIN (상호조인)은 한쪽 테이블의 모든 행과 다른 쪽 테이블의 모든 행을 조인하는 기능이다. SELF JOIN (자체조인)은 자신이 자신과 조인한다는 의미로 1개의 테이블을 사용한다.

APPLY 연산자

SELECT .. FROM [외부 테이블] CROSS | OUTER APPLY 
(SELECT .. FROM [내부 테이블] WHERE [조인 조건자])

• CROSS APPLY = INNER JOIN
• 내부 테이블의 집합으로부터 조인 키로 결합한 외부 테이블의 행만을 반환합니다.

• OUTER APPLY = LEFT OUTER JOIN
• 내부 테이블의 집합으로부터 조인 키에 결합한 외부 테이블의 행 + 그렇지 않은 행 전부를 반환합니다. 그렇지 않은 행은 열에서 NULL을 표시합니다.
• 테이블 반환 함수를 테이블처럼 사용해 조인 가능
• 조인 조건을 ON이 아닌 내부 테이블의 WHERE 문으로 설정 가능
• outer apply 안에 select 문에 집계함수를 쓸 때 group by를 반드시 사용하지 않아도 됨

Inline view (인라인 뷰)

서브쿼리

• 조건절에서 상수값이나 원래의 값으로 비교할 수 없고 어떤 가공을 한 값으로 비교하고자 할 때에 많이 사용

• 주 쿼리의 속성을 그대로 이어 받는다

Multi Row Sub Query (다중 행 서브쿼리)

• IN : 같은 값을 찾음 (=)

• >ANY : 최소값을 반환

• <ANY : 최대값을 반환

• <ALL : 최소값을 반환

• >ALL : 최대값을 반환

> any(a, b) : a,b 중 최소보다 큰

> all(a, b) : a,b 중 최대보다 큰

< any(a, b) : a,b 중 최대보다 작은

< all(a, b) : a,b 중 최소보다 작은

MSSQL

데이터 검색

SELECT TOP n * FROM 테이블명

SELECT * FROM 테이블명 WHERE 칼럼 LIKE '패턴'
// 'k%'(k로 시작되는),'%k%'(중간에 k가 있는), 'k%'(k로 끝나는)
// 'p_'(p로 시작하는 2자리), '_ _p'(3자리 데이터중 p로 끝나는)

WHERE ISNULL(칼럼,"") // is null
WHERE ISNULL(칼럼,"")<> // not null

BETWEEN A AND B

SELECT * FROM employeeWHERE salary WHERE BETWEEN 300 AND 500;
// salary가 300이상 500이하인 데이터 조회

ROLLUP

SELECT job, deptno, SUM(sal) AS total_sal
	FROM emp
WHERE job IN ('MANAGER', 'CLERK', 'SALESMAN')
GROUP BY ROLLUP(job, deptno)

SELECT job, deptno, SUM(sal) AS total_sal
	FROM emp
WHERE job IN ('MANAGER', 'CLERK', 'SALESMAN')
GROUP BY ROLLUP(job, deptno)
HAVING GROUPING(job) = 1 OR GROUPING

PIVOT

SELECT [PIVOT열1], [PIVOT열2], ...FROM 테이블명 PIVOT
(나타내고자하는 값, FOR 행->열로 바꿀 열 IN ([PIVOT열1], [PIVOT열2], ...)) as 별칭
// UNPIVOT은 반대

CASE WHEN

CASE WHEN 조건절 THEN 참일때 값 ELSE 거짓일때 값 END 컬럼명
--MSSQL 다중 CASE WHEN 사용 방법 
CASE WHEN [조건절1] THEN [참일 때 값]
WHEN [조건절2] THEN [참일 때 값]
WHEN [조건절3] THEN [참일 때 값]
ELSE [조건에 맞는 것이 없을 때 값] END [컬럼명]

프로시저 오류 처리

RAISERROR('메시지', 오류심각도, 상태[옵션])
// 1~10 : 일반 사용자 정보
// 11~16 : 사용자 정의 오류
// 17~25 : 시스템 오류

함수

1. 수치함수
ROUND(수치값, 반올림위치) // 반올림 및 자르기
ABS(수치데이터) // 절댓값
SIGN(수치데이터) // 부호
SQRT(수치값) // 제곱근
POWER(수치값, n) // n승

2. 문자열 함수 정리
Ascii() // 문자열의 제일 왼쪽 문자의 아스키 코드 값을 반환
Char() // 정수 아스키 코드를 문자로 반환
Charindex() // 문자열에서 지정한 식의 위치를 반환
Difference() // 두 문자식에 SOUNDEX 값 간의 차이를 정수로 반환
Left(), Right() // 문자열 왼쪽이나 오른쪽에서부터 지정한 수만큼의 문자를 반환
Len() // 문자열의 길이 반환
Lower() // 대문자를 소문자로 반환
Ltrim(), Rtrim() // 문자열의 왼쪽이나 오른쪽 공백 제거
Nchar() // 지정한 정수 코드의 유니코드 문자 반환
Replace() // 문자열에서 바꾸고 싶은 문자 다른 문자로 반환
Replicate() // 문자식을 지저한 횟수만큼 반복
Reverse() // 문자열을 역순으로 출력
Space() // 지정한 수만큼의 공백 문자 반환
Substring() // 문자,이지느텍스트 또는 이미지 식의 일부를 반환
Unicode() // 식에 있는 첫번째 문자의 유니코드 정수 값으 반환
Upper() // 소문자를 대문자로 반환
Isnumeric() // 해당 문자열이 숫자형이면 1 아니면 0을 반환
Isdate() // 해당 문자열이 Datatime이면 1 아니면 0을 반환

Stored Procedure(저장 프로시저)

• 데이터베이스내에서 SQL 명령을 컴파일할때 캐시를 이용할 수 있으므로 처리가 매우 빠르다.

• 반복적으로 SQL 명령을 실행할 경우 매회 명령마다 네트워크를 경유할 필요가 없다.

• 어플리케이션마다 새로 만들 필요없이 이미 만들어진 프로시저를 반복 사용한다.

• 데이터베이스 로직을 수정 시 프로시저는 서버측에 있으므로 어플리케이션을 다시 컴파일할 필요가 없다.

• 저장프로시저 : 일련의 작업을 처리한 결과, 리턴값이 없거나 많을 수도 있음, 서버에서 실행되기 때문에 속도가 빠름

• 함수 : 여러 작업을 위한 기능, 리턴값이 필수, 클라이언트에서 실행되기 때문에 프로시저보다 느리다.

일반 쿼리문 VS 저장 프로시저

• 구문분석 : 구문 자체에 오류가 없는지 분석

• 개체 이름 확인 : 테이블이 현재 데이터베이스에 있는지 확인

• 사용권한 확인 : 현재 테이블에 접근중인 사용자가 권한이 있는지 확인

• 최적화 : 해당 쿼리문이 가장좋은 성능을 낼 수 있는 경로를 결정

• 컴파일 및 실행 계획 등록 : 해당 실행계획 결과를 메모리(캐시)에 등록

1. 저장프로시저 정의 단계
Create Procedure → 구문 분석→ 지연된 이름 확인 → 생성권한 확인 → 시스템 테이블에 등록
• 구문분석 : 구문의 오류 파악
• 지연된 이름 확인 : 저장 프로시저를 정하는 시점에서 해당 개체(테이블)가 존재하지 않아도 상관없다. 프로시저 실행 당시에 테이블 존재 여부 확인함(개체이름 확인)
• 생성권한 확인 : 현재 사용자가 저장 프로시저를 생성할 권한이 있는지 확인
• 시스템 테이블 등록 : 저장 프로시저의 이름 및 코드가 시스템 테이블에 등록

2. 처음 저장 프로시저 실행
구문분석 단계 빠진 일반 쿼리문 작동 방식과 동일
• 정의 단계에서 지연된 이름확인에서 미루어두었던 해당 개체 존재 유무를 개체 이름 확인을 통해 수행한다.

3. 이후 저장 프로시저 실행
저장 프로시저 두번째 실행 → 메모리(캐시)확인 → 실행
• 메모리(캐시)에 있는 것을 그대로 가져와 재사용하게 되어 수행시간을 단축

명령어

CREATE PROC 프로시저명 AS SQL 문 -- 저장 프로시저
CREATE PROC 프로시저명 변수선언 AS SQL 문 -- 인수를 가지는 저장 프로시저
CREATE PROC 프로시저명 WITH ENCRYPTION AS SQL 문 -- 저장 프로시저 보안 설정
CREATE PROC 프로시저명 인수1 데이터형, ...인수2 데이터형 OUTPUT -- 출력파라미터로 지정
AS SQL문 RETURN 리턴값 -- RETURN 값을 가지는 저장 프로시저
DROP PROCEDURE 프로시저명1, 프로시저명2 -- 저장 프로시저 삭제
EXEC 프로시저명 파라미터 -- 프로시저 호출

BEGIN…END -- 문장의 블록 (SQL문과 변수선언은 BEGIN~END 사이에 작성) BEGIN END는 생략가능
DECLARE @변수명 데이터형 -- 변수 선언
SET @변수명=값 -- 변수에 값 지정
SET NOCOUNT ON -- 결과 적용된 행의 개수를 표시하지 않도록하는 옵션
PRINT @변수명 -- 한개의 변수 출력
SELECT @변수1, @변수2 -- 여러개의 변수 출력
IF  -- 조건 수행
	수행1
ELSE
	수행2
WHILE 조건1 -- 반복 수행
	BEGIN
		IF 조건2
	BREAK -- WHILE 루프를 빠져나간다
	CONTINUE -- 수행을 처리하지 않고 조건1로 되돌아간다
수행
	END
EXEC 저장프로시저 -- SQL문을 실행
EXEC @(변수로 지정된 SQL문)
GO -- BATCH를 구분 지정

IF EXISTS (SELECT문) -- 조건문
	BEGIN -- 값이 하나이상 있을 시 실행
		SELECT1
	END
ELSE
	BEGIN -- 값이 없을 때 실행
		SELECT2
	END
-- 리턴형식 bool

IF EXISTS (SELECT TOP 1 1 from ...)
	BEGIN
	 ...
	END
-- ()안에 쿼리문이 있느냐 없느냐 있으면 (BEGIN)
-- 상위 1개 행만 반환하고 해당 행에 대해 정수 1 즉, true 조건이면 BEGIN 실행

MERGE 구문

	MERGE INTO (변경할 테이블)
		USING (비교할 테이블)
			ON (조건문)
		WHEN MATCHED THEN
		(조건을 만족할 경우)
			UPDATE SET
		WHEN NOT MATCHED THEN
		(조건을 만족하지 않을 경우)
			DELETE

• Target 테이블과 Source 테이블 모두 존재하는 경우엔 MATCHED 조건에 해당하고, 해당 행은 Target 테이블의 행을 Update하거나 Delete할 수 있다.

• Target 테이블에는 존재하지만 Source 테이블에는 존재하지 않는 경우는 NOT MATCHED BY SOURCE 조건에 해당하며, 해당 행은 MATCHED와 마찬가지로 Target 테이블의 행을 Update하거나 Delete할 수 있다.

• Source 테이블에는 존재하지만 Target 테이블에는 존재하지 않는 경우는 NOT MATHCED BY TARGET 조건에 해당하고, 해당 행은 Target 테이블로 INSERT만 가능하다.

WHEN 구문

• MERGE문에 1개만 포함

• MATCHED절을 2개 포함시킬 경우, 추가 조건(AND condition)절을 지정해야 하고 각각 UPDATE/DELETE 동작을 지정해야 한다.

• MERGE문에는 1개만 포함

• BY TARGET은 생략할 수 있다.

• ON절에 일치하지 않지만 추가 검색 조건을 충족하는 모든 행에 대해 INSERT 수행한다.

• MERGE문에 최대 2개

• WHEN MATCHED가 적용되지 않은 경우에만 적용

• 2개 포함시킬 경우, 추가 조건절을 지정해야 하고 각각 UPDATE/DELETE 동작을 지정

• 추가 검색 조건은 대상 테이블의 열만 참조할 수 있다.

EXITS, NOT EXITS

• EXITS연산자는 서브쿼리에 데이터가 존재하는지 체크하고 존재할 경우 TRUE를 반환

• NOT EXITS 연산자는 서브쿼리에 데이터가 존재하지 않을 경우 TRUE를 반환

SELECT 컬럼 값 변수로 지정하는 방법

declare @name varchar(100);
declare @age int;

select @name = NAME, @age = AGE from Student where seq = 10;

dbo

dbo와 스키마 차이

• TEST라는 데이터베이스이 소유자(dbo)가 test일지라도, table이라는 테이블은 test.table로 참조할 수 없다.

• 테이블명 앞에 붙는 명칭은 스키마 이름

Trigger

1. 데이터베이스 트리거
데이터베이스에서 데이터 INSERT, DELETE, UPDATE 등의 작업이 발생할 때 특정 작업을 자동으로 실행한다.

2. 타이머 또는 스케줄링 트리거
정기적으로 반복되는 작업을 예약하여 지정된 시간 트리거를 실행할 수 있다.

3. 이메일 또는 메시지 기반 트리거
이메일 도착, 메시지 수신 등과 같은 외부 이벤트를 감지하고 해당 이벤트에 대해 자동으로 처리하는 기능 구현이 가능

4. 파일 시스템 트리거
파일 또는 디렉토리의 생성, 수정 또는 삭제와 같은 파일 시스템 변경 사항을 감지하고 연관된 작업을 실행할 수 있다.

5. 외부 API 호출 기반 트리거
외부 서비스나 API 호출 결과에 따라 특정 작업이 자동으로 실행되도록 설정할 수 있다.

MSSQL 트리거

• 자동으로 실행되는 프로시저의 일종, 수동으로는 실행 불가

• 한 테이블과 관련되어서만 존재 할 수 있음

-- 트리거 생성
CREATE TRIGGER [TriggerName]
ON [TableName]
[AFTER, BEFORE] INSERT, UPDATE, DELETE // 원하는 이벤트 지정
AS
BEGIN
    // 실행될 코드 또는 작업을 여기에 작성
END;

query

set nocount on;

    select CodeID, CodeName
    from Information.CommonCodeDetail
    where CodeTypeID = 'AREA' and // 논리연산자 and, or
		CodeName like '%' + isnull(@CodeName, '') + '%' // isnull(null값이 있는 칼럼명,'')

 set nocount off;

UNION

• 열의 개수와 순서가 모든 쿼리에서 동일해야함

• 데이터 형식이 호환되어야합니다

-- UNION
select [컬럼1], [컬럼2], [컬럼3], ... FROM 테이블명
UNION
select [컬럼1], [컬럼2], [컬럼3], ... FROM 테이블명
-- UNION ALL
select [컬럼1], [컬럼2], [컬럼3], ... FROM 테이블명
UNION ALL
select [컬럼1], [컬럼2], [컬럼3], ... FROM 테이블명

• 두 테이블의 데이터가 새로운 '행'으로 추가되어서 순차적으로 데이터가 추가

Cursor문

Cursor

• 데이터 작업을 할 때 테이블에서 집합 단위가 아닌 행 단위로 처리를 해야할 때 사용하는 방식

• DML (SELECT, UPDATE, DELETE, INSERT)

특징

• 집합이 아닌 행 단위로 작업을 수행하기때문에 속도가 느리다

• 테이블에서 여러 개의 행을 조회한 후 쿼리의 결과를 한 행씩 처리하는 방식

내 서버의 메모리 사용량

SELECT physical_memory_kb,
virtual_memory_kb,
committed_kb,
commmitted_target_kb,
FROM sys.dm_os_sys_info;

• physical_memory_kb : 서버에 설치된 총 실제 메모리

• virtual_memory_kb : SQL Server에서 사용할 수 있는 총 가상 메모리 양

• Committed_kb : 데이터베이스 페이지에서 사용하기 위해 버퍼 캐시에서 현재 할당한 메모리 양

• Committed_target_kb : 버퍼 캐시가 사용하고자하는 메모리 양

STRING_AGG()

SELECT job, STRING_AGG(ename, ',') WITHIN GROUP(ORDER BY ename) enames
	FROM emp
	WHERE job IN ('MANAGER', 'SALESMAN')
	GROUP BY job

CHARINDEX()

• 문자열의 앞부터 또는 특정 위치부터 문자를 검색

• CHARINDEX(”찾을 문자”, “문자열”, “시작위치”)

SELECT CHARINDEX('sql', 'Microsoft SQL Server')

SELECT CHARINDEX('sql', 'Microsoft SQL Server', 11)

SELECT CHARINDEX('SQL', 'Microsoft SQL Server' COLLATE Latin1_General_CS_AS)
// 대소문자 구분 : COLLATE Latin1_General_CS_AS
// 대소문자 구문안함 : COLLATE Latin1_General_CI_AS (기본값)

DECLARE @str VARCHAR(100) = 'SQL Server 2017, SQL Server 2019'
SELECT CHARINDEX('sql', @str, CHARINDEX('sql', @str) + 1)

IIF

DECLARE @score INT = 80
SELECT IIF(@score >= 70, '합격', '불합격') AS score
// score
// 합격

TOP

SELECT TOP(5) ename, job, sal FROM emp ORDER BY sal
// sal로 정렬 후 5개만 조회

시작일자 종료일자 사이 모든 일자 조회

// 재귀 쿼리(WITH CTE)
WITH DateRange(Date) AS
(
	SELECT CONVERT(DATE, '2021-12-01') --시작일자
		UNION ALL
	SELECT DATEADD(d, 1, Dates)
		FROM DateRange
	WHERE Dates < CONVERT(DATE, '2021-12-7) --종료일자		
)

SELECT * FROM DateRange 
OPTION (MAXRECURSION 0) // 재귀 쿼리의 반복 횟수를 설정하는 부분
												// 0은 무한반복, 생략하면 최대 100회까지만 반복

// 2021-12-01 ~ 2021-12-07

// master..spt_value
SELECT DATEADD(d, number, CONVERT(DATE, '2021-12-01')) Dates
	FROM master..spt_values
WHERE type = 'p'
	AND number <= DATEDIFF(d, CONVERT(DATE, '2021-12-01'),
	CONVERT(DATE, '2021-12-07'))
	
// 2021-12-01 ~ 2021-12-07

Partition by

SELECT 순위함수() OVER(PARTITION BY 컬럼명 ORDER BY 컬럼명) FROM 테이블명

• 순위함수 : ROW_NUMBER, RANK, DENSE_RANK

SELECT empno
     , ename
     , job
     , sal
     , SUM(sal) OVER(PARTITION BY job)
  FROM emp
 WHERE job IN ('MANAGER', 'SALESMAN')
 ORDER BY job

Paging Query

DECLARE @PAGE_NO INT = 1 -- 페이지번호
DECLARE @PAGE_SIZE INT = 5 -- 한 페이지에 보여줄 row 수

SELECT rownum, name, age, totalCnt
from
(
	SELECT ROW_NUMBER() OVER(ORDER BY age) AS rownum, name, age,
		COUNT(*) OVER() AS totalCnt
	from [테이블]
) A
WHERE rownum BETWEEN ((@PAGE_NO-1)*@PAGE_SIZE)+1 AND
(@PAGE_NO * @PAGE_SIZE)

SELECT t1.* 
		FROM 테이블명 AS t1 WITH(NOLOCK)  
		INNER JOIN ( 
			SELECT id 
			FROM 테이블명 WITH(NOLOCK)
			ORDER BY reg_date  DESC
			OFFSET (@PAGE_NO) * @PAGE_SIZE ROWS
			FETCH NEXT (@PAGE_SIZE)  ROWS ONLY 
		) AS t2
		ON t1.id = t2.id ORDER BY reg_date  DESC;

OFFSET ROWS FETCH

ORDER BY 컬럼
OFFEST 숫자 {ROW | ROWS}
FETCH {FIRST | NEXT} 숫자 {ROW | ROWS} ONLY

• OFFSET 숫자 {ROW | ROWS} : 해당 숫자 이후의 행부터 출력

• FETCH {FIRST | NEXT} 숫자 {ROW|ROWS} ONLY : 숫자의 개수만큼 출력

조회 순번 매기기

SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS rownum

• ROW_NUMBER 함수를 사용할 때는 ORDER BY 필수

분석함수

over()

SELECT
    employee_id,
    salary,
    avg(salary) over() 전체평균
FROM employees;

over(ORDER BY 컬럼)

• 쿼리 결과 전체를 집계

over(partition by 컬럼)

over(partition by 컬럼1 ORDER BY 컬럼2)

Select 문 두개 합치기

select 
	(select * from Table1) as A,
	(select * from Table2) as B

--   | A  B
-----|-------
-- 1 | 3  4
-- 2 | 2  8

count( ) 함수

• count(name) : name이라는 컬럼의 개수, null 값 포함 X

• count(*) : 전체 행의 개수, null 값 포함

• count(1) : 전체 행의 개수, null 값 포함

DATE(날짜관련)

date_add

SELECT * FROM cordinghero.test where date > date_add(now(),interval -1 month);

• date > date_add 라면 뒤쪽에 -1 month이므로 한 달이내

• date < date_add 라면 한 달이 지난 데이터

특정날짜 1일부터 특정날짜까지

B.BaseDate between DATEADD(DAY, 1 - DAY(@BaseDate), @BaseDate) AND @BaseDate

년, 달, 일 (조회)

DATENAME(yy,@BaseDate) + '년 ' + DATENAME(MM, @BaseDate) + '월 ' + DATENAME(dd, @BaseDate) + '일 ' + DATENAME(WEEKDAY, @BaseDate)

특정 달까지 조회

select * from where
DATEADD(MONTH,0,[날짜]) <= @EndDate

dense_rank()

ROW NUMBER()

WITH (NOLOCK)

• 커밋되지 않은 읽기를 허용하겠다.

• 읽기 작업시 조회하는 테이블이 잠겨있어도 기다리지 않겠다.

• 데드락이 발생하거나 교착상태가 발생하지 않는다.

select * from test_table WITH (LOCK)

임시테이블 컬럼명 가져오기

select name from tempdb.sys.columns 
where object_id = object_id('tempdb..#js_WeightingTable')
-- #js_WeightingTable = 임시테이블명
-- name = 컬럼명

테이블 컬럼명 가져오기

select * from syscolumns 
where id = object_id('테이블명')

SQL 로그 파일

1. 로그 파일 확인

DBCC SQLPERF(LOGSPACE) -- 1
EXEC SP_HELPFILE -- 2

2. 로그 파일 용량 줄이기

BACKUP LOG [Database] WITH TRUNCATE_ONLY -- 축소작업
BACKUP LOG [Database] WITH NO_LOG --로그삭SQ제

SQL 쿼리 처리 속도 확인

SET STATISTICS TIME ON
[실행할 쿼리문]
SET STATISTICS TIME OFF

IN, NOT IN 연산자

IN

select * from emp 
where ename IN ('JONES', 'SCOTT', 'Bob')

NOT IN, 주의사항

SELECT * FROM emp
WHERE ename NOT IN ('JONES', 'SCOTT', 'MILLER')

SELECT * FROM emp
WHERE ISNULL(ename.'test') NOT IN ('JONES', 'SCOTT', 'MILLER')

• NOT IN을 사용할 때는 해당 칼럼에 NULL이 존재할 경우 해당 값이 조회되어야 한다면, 꼭 ISNULL함수로 치환해야 해당 값이 누락되지 않고 조회

사용자 정의 함수(User-defined Fuction, UDF)

• 해당 작업의 결과를 값으로 반환
• 반드시 하나의 반환 값이 있어야 함

• 반환 값은 단일 스칼라 값이나 결과 집합
• 단일 스칼라 값 : 단일 데이터 값을 의미
• 결과 집합 : 열 또는 행의 일부

이점

• 모듈식 프로그래밍
• 함수를 한 번 만들어 데이터베이스에 저장한 후 여러 번 호출 가능

• 빠른 실행
• 사용시 재분석 및 다시 최적화할 필요 X

• 네트워크 트래픽 감소
• 필터링을 필요로 하는 작업의 경우 함수를 호출하여 전송되는 행과 데이터를 줄일 수 있음

스칼라 함수 (Scalar User-defined Fuction, scalar UDF)

• RETURNS절에 정의된 유형의 단일 데이터 값을 반환

• 단일 스칼라 함수 : 단일 스칼라 값을 반환하는 함수
• 단순한 수학식을 사용, 내장 함수에 사용하여 정의

• 다중 스칼라 함수 : 단일 스칼라 값을 반환하고 여러개의 SQL 문으로 구성된 함수
• 반환 형식 : text, ntext, image, cursor, timestamp를 제외한 모든 데이터 형식
• 좀 더 복잡한 연산을 행

테이블 반환 함수 (Table-valued Fuction, TVF)

• 테이블 데이터 형식을 반환

• 테이블은 단일 SELECT 문의 결과 집합

• SELECT문, JOIN절 또는 하위 쿼리에서 테이블과 동일하게 사용 가능

시스템 함수 (System Fuction)

• 사용 할 수 있는 많은 시스템 기능을 제공

• 수정 불가

Fuction 생성

CREATE FUNCTION FN_TEST(@P_MSG VARCHAR(10))
RETURNS VARCHAR
AS
BEGIN
	DECLARE @V_RETURN VARCHAR(100);
	DECLARE @V_STR VARCHAR(10);
	SET @V_STR = 'abc';
	SET @V_RETURN = concat(@V_STR, @P_MSG);
	RETURN @V_RETURN;
END

Function 실행

Function 수정

Function 삭제

MSSQL , 0으로 나누기 오류가 발생하였습니다 관련 처리

-- 방법 1
select case when isnull(data1,0) = 0 then 0 else data2 end as 'test'
from A

-- 방법 2 오류값이 null값으로 반환
SET ANSI_WARNINGS OFF
SET ARITHIGNORE ON
SET ARITHABORT OFF

group by(집계함수) vs partition by(윈도우함수)

group by

select address, count(*) from Address group by address;

--Result
address | count
----------------
서울시  |   3
인천시  |   2
부산시  |   1

partition by

select address, count(*) over(partition by address) from Address;

--Result
address | count
----------------
서울시  |   3
서울시  |   3
서울시  |   3
인천시  |   2
인천시  |   2
부산시  |   1

비율 관련 함수

• CUME_DIST
• 파티션 내 전체 건수 중에서 현재 행보다 작거나 같은 건수에 대한 누적 백분율

• PERCENT_RANK
• 파티션에서 가장 처음 값은 0 가장 나중의 값은 1으로 행의 순서별 백분율 조회

• NTILE
• 파티션별로 전체 행을 N등분한 결과를 조회

• RATIO_TO_REPORT
• 파티션 내 전체 SUM(컬럼)에 대한 행별 컬럼값의 백분율을 소수점까지 조회

# PERCENT_RANK 예시 : 부서 내 급여 퍼센트(등수) 조회
selct deptino, ename, sal,
	-- 부서별로 파티션, 급여 내림차순
	PERCENT_RANK() OVER (partition by depno order by sal desc) as per_sal
from emp;

#NTILE 예시 : 급여 높은 순서로 4등분
select deptno, ename, sal
	-- 4등분 >> 1,2,3,4 할당
	NTILE(4) OVER (order byt sal desc) as n_title
from emp;

OUTER JOIN VS INNER JOIN 성능

• 빠른 시간내에 해야할 경우 left outer join 남발

• 대신 성능이 안 좋아짐

• inner join 시간 복잡도 O(n)

• outer join 시간 복잡도 O(n^2)

쿼리문 빨간줄 표시 해결

SQL 들여씌기 오류

트랜잭션(Transaction)

• 하나 혹은 두개 이상의 SQL문들로 이루어진 작업의 논리적인 단위

• 하나의 트랜잭션안의 모든 SQL은 동일한 효과
• 성공하면 전부 성공 실패하면 전부 실패

• 하나의 트랜잭션은 커밋될 수도 있고 롤백 될 수도 있다.
• 커밋 : 하나의 트랜잭션안의 모든 변화들을 데이터베이스에 영구적으로 반영시키는 작업
• 롤백 : 하나의 트랜잭션안의 모든 변화들을 취소

커밋 또는 롤백하기 전 상태

• 모든 데이터의 변화는 데이터베이스 버퍼에 저장된다.

• 현재의 사용자는 자신이 변화시킨 데이터를 SELECT문을 통해서 볼 수 있지만 자신 이외의 사용자는 그 변화된 내용을 볼 수 없다.

• 자신이 변화시킨 Row에 대해서는 록(Lock)이 걸린 상태이기 때문에 다른 사용자가 해당 Row를 변화시키지 못한다. ( Row Level Locking Mechanism )
• 테이블의 row마다 걸리는 lock

커밋 한 후 상태

• 트랜잭션 내부에서 변화된 내용은 데이터베이스에 영구적으로 반영

• 다른 모든 사용자가 변화된 내용을 볼 수 있다.

• 해당 Row에 대한 록(Lock)이 제거된다.

인덱스(INDEX)

• 하나 혹은 두개 이상의 컬럼과 ROWID로 구성

• index를 구성하는 컬럼값으로 정렬되어 있으며 모든 값이 동일한 경우 ROWID로 정렬되어 있다.

• 테이블과는 물리적으로 다른 저장장소에 저장된다.

테이블 액세스 방법

1. BY ROWID : 테이블의 특정 Row에 대한 위치 정보를 가지고 직접 테이블을 액세스

2. Full-table-Scan : 테이블에 존재하는 모든 Row에 대해서 순차적으로 액세스

3. By INDEX : 먼저 INDEX를 읽고서 INDEX의 ROWID를 가지고 테이블을 Random 액세스

인덱스 생성

-- 기본 생성
create index [index_name] on [table_name] (column_name);
-- Unique index 생성
create unique index [index_name] on [table_name] (column_name);
-- primary key, unique 제약조건을 주면 unique index는 자동적으로 만들어짐

인덱스 삭제

DROP INDEX [인덱스명] ON [테이블명]

클러스터형 인덱스

create clustered index [index_name] on [table_name] (column_name);

실행 계획 논리 및 물리 연산자 참조

Compute Scalar

• 그러면 사용자에게 반환되거나. 쿼리의 다른 위치에서 참조되거나, 둘 다 발생할 수 있습니다.

Stream Aggregate

• 그룹 내의 열에 따라 입력을 정렬해야 함

• 이전 Sort 연산자, 정렬된 인덱스 찾기 또는 스캔으로 인해 데이터가 아직 정렬되지 않은 경우 최적화 프로그램은 이 연산자 앞에 Sort 연산자를 사용

• 물리 연산자

재귀적 공통 테이블 표현식 (CTE)

앵커 멤버(Anchor Member)

재귀 멤버(Recursive Member)

실행 과정

1. 앵커 멤버 실행
• 앵커 멤버 쿼리가 먼저 실행되어 초기 결합 집합을 생성

2. 재귀 멤버 실행
• 재귀 멤버 쿼리가 앵커 멤버의 결과를 사용하여 새로운 결과 집합을 생성

3. 반복 실행
• 재귀 멤버 쿼리가 반복적으로 실행되어 각 반복에서 이전 결과를 사용하여 새 결과를 생성

4. 종료 조건 확인
• 재귀 멤버의 종료 조건이 만족되면 반복이 중단

with 절의 동작 방식

Inline View

• with 절로 정의된 테이블이 참조된 횟수만큼 반복 수행

• with절이 본절에서 1번 사용될 때 활용하는 방식

• 서브쿼리와 다를 것 없는 성능

Materialize

• with 절로 정의된 테이블이 2번 이상 사용될 때 활용

• with절을 통해 저장된 결과값을 불러와 실행 횟수를 줄여줌으로써 성능의 개선

Bulk Insert

BULK INSERT A 테이블 FROM '파일경로' WITH (FIRSTROW  = 2, FIELDTERMINATOR = ',', ROWTERMINATOR = '\n', BATCHSIZE = 100)

• FIRSTROW : 시작할 행

• FIELDTERMINATOR : 구분자

• ROWTERMINATOR : 행 구분자

• BATCHSIZE : 일괄 처리 행 수

REPLICATE

OUTPUT 절

• output 구문에는 서브쿼리 X

insert into Customer (BusinessCode, CustomerName) output inserted.* values ('0006','대우') 

DELETE Sales.ShoppingCartItem
    OUTPUT DELETED.*;

declare @ProductID nvarchar(4) = '5006'
declare @ProductName nvarchar(50) = '배터리3'
declare @Standard int = 50

merge Product as T -- Target
using 
(
	select @ProductID as ProductID, @ProductName as ProductName, @Standard as Standard
) as S on -- Source
	T.ProductID = S.ProductID
when matched and S.Standard != 0  then
	update
		set ProductName = S.ProductName,
			Standard = S.Standard
when matched and S.Standard = 0 then
	delete
when not matched then
	insert (ProductID, ProductName, Standard)
	values (S.ProductID, S.ProductName, S.Standard)
output inserted.*, deleted.*
;

declare @ProductID nvarchar(4) = '5006'
declare @ProductName nvarchar(50) = '배터리3'
declare @Standard int = 50

if(@ProductID != '' and @ProductName != '' and @Standard != 0)
begin
	insert into Product (ProductID, ProductName, Standard) values(@ProductID, @ProductName, @Standard)
	select * from Product where ProductID != @ProductID and ProductName != @ProductName and Standard != @Standard and exists (select 1 from Product where ProductID = @ProductID)
end

Emergency 백업

1. 비상로그 백업(Tail-Log Backup)

-- 비상로그 백업
-- 절대 기존의 트랜잭션 로그 백업과 같은 파일명으로 백업시키지 말 것
BACKUP LOG [DB이름] TO DISK = '비상로그 백업을 저장시킬 디렉토리'
WITH NO_TRUNCATE, FORMAT

-- FORMAT 옵션은 만약 이전의 비상로그 백업이 같은 파일명으로 존재한다면 해당 백업 세트 제거

2. DB 백업본 불러와서 붙이기

3. 복원 시킬 시점 찾기

select top 100 QS.creatino_time,
-- SQL
SUBSTRING(ST.text, (QS.statement_start_offset/2)+1,
((CASE QS.statemnet_end_offeset when -1 then DATELENGTH(st.text)
else QS.statement_end_offset end - QS.statement_start_offset)/2) + 1
) as statement_text,
ST.text,
-- 실행계획
QS.total_worker_time,
QS.last_worker_time,
QS.max_worker_time,
QS.min_worker_time
from
sys.dm_exec_query_stats QS
-- sys.dm_exec_query_stats : 실행된 쿼리에 대한 통계 테이블
cross apply
sys.dm_exec_sql_text(QS.sql_handle) ST
-- 복원해야 될 시점
where creation_time between '2022-06-29 11:00:00' and '시간'
order by
QS.creation_time desc

4. Restore 진행 및 Recovery State를 ‘RECOVERY’로 바꾸기

-- 비상로그 백업본에서 해당 시점을 찾아 복원
RESTORE LOG AdventureWorks2019_dummy from DISK = 'E:\SQL Server BackUp\EmergencyTrnBak.bak'
WITH MOVE 'AdventureWorks2017' TO 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\AdventureWorks2019_Dummy.mdf',
MOVE 'AdventureWorks2017_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\AdventureWorks2019_Dummy_Log.ldf',
NORECOVERY, STOPAT = '2022-06-29 11:19:35.946', STATS=10 		-- 실수한 'UPDATE 트랜잭션'의 시작 시간 -0.001초 지점으로 롤백

-- 복원이 끝난 후 DB의 'Restoring...' 상태를 해제.
RESTORE DATABASE AdventureWorks2019_Dummy WITH RECOVERY

5. 복원한 Dummy DB를 통해 원래 DB의 바뀐 부분 원복하기

'E:\SQL Server BackUp\EmergencyTrnBak.bak'
WITH MOVE 'AdventureWorks2017' TO 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\AdventureWorks2019_Dummy.mdf',
MOVE 'AdventureWorks2017_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\AdventureWorks2019_Dummy_Log.ldf',
NORECOVERY, STOPAT = '2022-06-29 11:19:35.946', STATS=10 		-- 실수한 'UPDATE 트랜잭션'의 시작 시간 -0.001초 지점으로 롤백

-- 복원이 끝난 후 DB의 'Restoring...' 상태를 해제.
RESTORE DATABASE AdventureWorks2019_Dummy WITH RECOVERY

변경 내용 추적

1. 세팅

-- 데이터베이스에 변경 내용 추적 설정
alter database BusinessDB
set change_tracking = on
(change_retention = 2 days, auto_cleanup = on) -- 추적 정보가 유지되는 기간을 지정
--이 기간보다 오래된 변경 내용 추적 정보는 정기적으로 제거

-- 테이블에 대한 변경 내용 추석 설정
alter table BusinessDB.dbo.Product
enable change_tracking
with (TRACK_COLUMNS_UPDATED = on)

2. 변경 내용 가져오기

declare @last_synchronization_version bigint;

SELECT
    CT.ProductID, ProductName, Standard , CT.SYS_CHANGE_OPERATION,
    CT.SYS_CHANGE_COLUMNS, CT.SYS_CHANGE_CONTEXT
	FROM
    BusinessDB.dbo.Product AS P
RIGHT OUTER JOIN
    CHANGETABLE(CHANGES BusinessDB.dbo.Product, @last_synchronization_version) AS CT
on P.ProductID = CT.ProductID;