SQL

SQL(Structured Query Language)

• DDL(데이터 정의어) : 데이터 베이스를 생성하거나 테이블을 만드는 언어

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

• DCL(데이터 제어어) : 사용자의 권한을 설정하는 언어

SQL 문법 순서

SQL 실제 실행 순서

• FROM : 각 테이블 확인

• ON : 조인 조건 확인

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

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

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

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

• SELECT : 데이터 추출

• DISTINCT : 중복 제거

• ORDER BY : 데이터 정렬

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 [해당필드] [바꿀 필드명] [변경할속성]

DB

Entity(엔터티)

• 테이블

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

• 인스턴스의 집합

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

Attribute(속성)

• 컬럼

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

Relationship(관계)

• 행

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

Identifier(식별자)

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

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

데이터 삽입 (INSERT)

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

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   제주도

기타 함수

• 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

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을 표시합니다.

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 컬럼명

프로시저 오류 처리

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)

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 동작을 지정

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

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