SQL Server 강의 3일차

안녕하세요~! 문쑹입니다 :)

오늘도 열심히 포스팅을 해보겠습니다...

 

ROLLUP(), GROUPING_ID(), CUBE()함수

총합 또는 중간합계가 필요하다면 GROUP BY 절과 함께 ROLLUP(), CUBE()를 사용하면 된다.

buyTBL 테이블

우선, 운동화에 NULL값이였던 것들에 '잡화'로 바꿔주었습니다.

중간중간 num열이 NULL로 되어 있는 추가된 행이 각 그룹의 소합계를 의미한다. 그리고 마지막 행은 각 소합계의 합계, 즉 총합계의 결과가 나온것을 알수 있다. 

한눈에 데이터인지 합계인지를 알기 위해서는 GROUPING_ID() 함수를 사용할 수 있다. GROUPING_ID()함수의 결과가 0이면 데이터, 1이면 합계를 위해서 추가된 열이라고 보면 됩니다.

 

WITH 절과 CTE

WITH절은 CTE(Common Table Expression)을 표현하기 위한 구문이다. CTE는 기본의 뷰, 파생테이블, 임시테이블을 대신할 수 있으며 더 간결하게 표현되며 재귀적CTE 비재귀적CTE로 구분이 된다.

 

비재귀적 CTE

비재귀적 CTE는 말 그대로 재귀적이지 않은 CTE이다. 단순한 형태이며, 복잡한 쿼리문장을 단순화 시키는 데에 적합하게 사용될 수 있다. WITH절은 2015년부터 나오기 시작하였다. 

WITH CTE_테이블이름(열이름)
AS
(
	<쿼리문>
)
SELECT 열이름 FROM CTE_테이블이름;

비재귀적 CTE의 기본형식입니다.

SELECT t.userID, t.total
FROM (
		SELECT userID, --가상의 테이블이 된다.
		  	   SUM(price*amount) AS total
		FROM buyTBL
		GROUP BY userID
	  ) AS t
ORDER BY t.total DESC;

WITH절이 나오기 전에는 위의 코드처럼 사용했다.

WITH t(userID, total)
AS
(	SELECT userID, --가상의 테이블이 된다.
		   SUM(price*amount) AS total
	FROM buyTBL
	GROUP BY userID
)
SELECT * FROM t
ORDER BY t.total DESC;

하지만 WITH절이 나오고 나서 구문을 단순화 시켜준다.

CTE의 작동

INSERT 문 기본

INSERT는 테이블에 데이터를 삽입하는 명령어다. 아래는 기본적인 형식입니다.

INSERT [INTO] <테이블> [{열1, 열2, ...}] VALUES (값1, 값2 ...)

어려운 것은 없지만 몇 가지만 주의하면 된다.

우선 테이블 이름 다음에 나오는 열은 생략이 가능하다. 하지만, 생략할 경우에 VALUE 다음에 나오는 값들의 순서 및 개수가 테이블이 정의된 열 순서 및 개수와 동일해야한다.

 

자동으로 증가하는 IDENTITY

IDENTITY는 자동으로 1부터 증가하는 값을 입력해준다. CREAT TABLE에서 DEFAULT 문장으로 기본값을 설정해 놓았을 때, INSERT를 사용시 별도의 값을 입력하지 않고 지정해 놓은 디폴트 값을 그대로 사용하려면 값이 입력될 자리에 "DEFAULT"라고 써주면 된다. 간단한 테스트를 해보겠습니다.

CREATE TABLE testTBL2		--INSERT 구문 테스트용 테이블
(
	id INT NOT NULL IDENTITY,	-- 자동증가 아이디
	userName NCHAR(3),			-- 사용자명
	age INT,					-- 나이
	nation NCHAR(4) DEFAULT '대한민국'	--국가
);

id 값에 IDENTITY를 줬기 때문에 id는 자동증가가 되므로 값을 넣어주면 오류가 발생한다.

여기서 '홍길순'에게는 국가를 적어 주지 않았지만 자동으로 '대한민국'이 적용되어있다. 이것은 nation에 DEFAULT를 설정 해주었기 때문입니다.

id값을 자동으로 증가 받고 싶지 않을때는 위의 구문 처럼 해주면 된다. 다시 자동 받고싶을때는 IDENTITY 기능을 꺼주면 되니까 OFF를 적어주면된다.

SET IDENTITY_INSERT testTBL2 ON;		-- id값 자동증가 허용 안함

INSERT INTO testTBL2 (id, userName, age, nation)
     VALUES (5, '케빈', 27, '한국');

SET IDENTITY_INSERT testTBL2 OFF;		-- id값 자동증가 허용

SELECT @@IDENTITY;	-- 현재의 세션(쿼리창)에서 가장 최근에 생성된 ID의 값을 확인할 수 있다.
					-- 즉, 가장 마지막에 실행된 테이블의 id max값을 보여준다.

SELECT IDENT_CURRENT('testTBL2');	-- 특정 테이블에 설정된 현재의 IDENTITY값을 확인할 수 있다.

데이터의 수정 UPDATE

기존에 입력되어 있는 값을 변경하기 위해서는 UPDATE 문을 사용한다.

UPDATE 테이블이름
   SET 열1=값1, 열2=값2, ...
 WHERE 조건;

UPDATE testTBL2
   SET age = 88
 WHERE userName = '홍길동';

이런식으로 UPDATE 문을 사용한다면 홍길동의 나이가 88세로 바뀌는 것을 확인할 수 있을 것입니다. 하지만, WHERE 조건절이 없다면 모든 DB가 88세로 바뀌게 됩니다. 실무에서는 정말 큰 타격입니다(사유서 작성). 그래서 UPDATE를 하실때는 꼭! WHERE을 사용하여 UPDATE를 하시길 바랍니다. 실무에서는 DB의 양이 몇만개 또는 몇십만개가 되는데 다시 되돌릴 수 없는 경우가 발생합니다.

 

데이터의 삭제 : DELETE

DELETE도 UPDATE와 거의 비슷한 개념이다. DELETE는 행 단위로 삭제하는데, 형식은 다음과 같다.

DELETE 테이블 이름 WHERE 조건;

 DELETE testTBL2
 WHERE id = 2;

testTBL2에서 id가 2번인 행을 지워 달라는 코드 입니다. 그리고 DELETE도 UPDATE와 마찬가지로 WHERE 조건절이 없으면 전체 데이터가 삭제 되니까 조심하셔야 합니다!

그리고 DELETE로 데이터 전부를 삭제하고 INSERT로 새로운 값을 입력하면 1번이 아닌 마지막으로 사용한 id값의 증가값이 적용되어 나옵니다.

 DELETE testTBL2

 insert into testTBL2 (userName, age) values ('홍길순', 21);

DELETE 결괏값

하지만 TRUNCATE 명령어를 사용하면 id값이 초기화가 되어 다시 1번부터 만들어집니다.

 TRUNCATE TABLE testTBL2;
  insert into testTBL2 (userName, age) values ('홍길순', 21);

TRUNCATE 결괏값

SQL Server의 데이터 형식

숫자형 데이터 형식

데이터 형식	바이트 수	숫자 범위	설명
★BIT	1	0, 1, NULL	Boolean 형인 참(True,1), 거짓(False,0)에 사용한다.
★TINYINT	1	0 ~ 255	양의 정수
★SMALLINT	2	-32,768 ~ 32,767	정수
★INT	4	약 -21억 ~ +21억	정수
★BIGINT	8	-2^63 ~ +2^63-1	정수
★DECIMAL(p,[s])	5~17	-10^38 ~ +10^38-1	고정 정밀도(p)와 배율(s)을 가진 숫자형이다. 예를들어, decimal(5,2)는 전체 자릿수를 5자리로 하되, 그 중 소수점 이하를 2자리로 하겠다는 의미다.
NUMERIC	5~17		DECIMAL과 동일한 데이터 형식이다.
★FLOAT[(n)]	4~8		ndl 25미만이면 4바이트, 25이상이면 8바이트의 크기를 할당한다.
REAL	4		FLOAT(24)와 동일하다.
MONEY	8	-2^63 ~ +2^63-1	화폐 단위로 사용한다.
SMALLMONEY	4	약 -21억 ~ +21억	화폐 단위로 사용한다.

문자 데이터 형식

데이터 형식	바이트 수	설명
★CHAR[{n}]	0 ~ 8000	고정길이 문자형. character의 약자.
★NCHAR[{n}]	0 ~ 8000	글자로는 0 ~ 4000자. 유니코드 고정 길이 문자형으로, National character의 약자.
★VARCHAR[{n | max}]	0 ~ 2^31-1 (2GB)	가변길이 문자형. n을 사용하면 1~8000까지 크기를 지정할 수 있고, max로 지정하면 최대 2GB 크기를 지정할 수 있다. Variable character의 약자.
★NVARCHAR[{n | max}]	0 ~ 2^31-1	유니코드 가변길이 문자형. n을 사용하면 1~4000까지 크기를 지정할 수 있고, max로 지정하면 최대 2GB 크기를 지정할 수 있다. National Variable character의 약자.
BINARY[{n}]	0 ~ 8000	고정길이의 이진 데이터 값.
★VARBINARY[{n}]	0 ~ 2^31-1	가변길이의 이진 데이터 값. n을 사용하면 1~8000까지 크기를 지정할 수 있고, max로 지정하면 최대 2GB 크기를 지정할 수 있다. 이미지/동영상 등을 저장하기 위해 사용된다. Variable Binary의 약자.

날짜와 시간 데이터 형식

데이터 형식	바이트 수	설명
DATATIME	8	날짜는 1753-1-1 ~ 9999-12-31 까지 저장되며, 시간은 00:00:00 ~ 23:59:59.997까지 저장된다. 정확도는 밀리초(1/1000초) 단위까지 인식한다. 형식은 'yyyy-MM-dd hh:mm:ss'로 사용한다
★DATETIME2	6~8	날짜는 0001-1-1 ~ 9999-12-31까지 저장된다. 시간은 00:00:00 ~ 23:59:59.9999999까지 저장된다. 정확도는 100나노초 단위까지 인식한다. 형식은 'yyyy-MM-dd hh:mm:ss'로 사용한다.
★DATE	3	날짜는 0001-1-1 ~ 9999-12-31까지 저장된다. 날짜 형식만 사용된다. 형식은 'yyyy-MM-dd'로 사용한다
★TIME	5	00:00:00 ~ 23:59:59.9999999까지 저장. 정확도는 100나초초 단위까지 인식된다. 형식은 'hh:mm:ss'로 사용한다
DATETIMEOFFSET	10	DATETIME2와 거의 비슷하지만, 표준 시간대를 인식하며 24시간제를 기준으로 하는 시간도 표시해준다. 'yyyy-MM-dd hh:mm:ss +표준시간' 형식으로 사용된다.
SMALLDATETIME	4	날짜는 1900-1-1 ~ 2079-6-6까지 저장된다. 정확도는 분 단위까지 인식된다.

기타 데이터 형식

데이터 형식	바이트 수	설명
ROWVERSION	8	VARBINARY(8)과 동일하며, 데이터베이스 내에서 자동으로 생성된 고유 이진 숫자를 표시한다. SQL Server 내부적으로 사용되는 것이라서 신경 쓸 필요는 없다.
SYSNAME	128	NVARCHAR(128)과 동일하며, 데이터베이스 개체의 이름에 사용된다. SQL Server 내부적으로 사용된다.
★CURSOR	1	T-SQL 커서를 변수로 처리한다.
★TABLE	N/A	테이블 자체를 저장한다. 임시 테이블과 비슷한 기능이다.
UNIQUEIDENTIFIER	16	복제에서 사용되는 자료형으로, 유일성을 보장하는 GUID 값을 저장한다.
SQL_VARIANT	N/A	다른 데이터 형식의 저장이 가능한 데이터형이다.
HIERARCHYID	N/A	계층 구조가 있는 테이블을 만들거나 다른 위치에 있는 데이터의 계층 구조를 참조할 수 있다.
★XML	N/A	XML 데이터를 저장하기 위한 형식으로 최대 2GB 저장된다.
★GEOMETRY / GEOGRAPHY	N/A	공간 데이터 형식으로, 선 점 및 다각형 같은 공간 데이터 개체를 저장하고 조작할 수 있다.

사용자 정의 데이터 형식

CREATE TYPE commonCode FROM CHAR(8) NULL; --사용자 정의 데이터 형식

유니코드 데이터

CREATE TABLE uniTestTBL
(
	id INT IDENTITY NOT NULL PRIMARY KEY,
	korName NVARCHAR(20) NOT NULL,
	engName VARCHAR(20) NOT NULL,
	code commonCode NOT NULL
);

INSERT INTO uniTestTBL (korName, engName, code)
VALUES(N'박신혜', 'PSH','NMEU0010');		-- 유니코드 (단지 구별을 위해 N사용)

INSERT INTO uniTestTBL (korName, engName, code)
VALUES('김문쑹', 'KMS','NMEU0014');			-- 일반코드

INSERT INTO uniTestTBL (korName, engName, code)
VALUES(n'박신혜', 'PSH','NMEU0010');		-- 유니코드 아님 (대문자만)

 

변수의 사용

SQL에서 변수를 사용할 때의 기본 문법입니다.

변수의 선언 : DECLARE @변수이름 데이터형식;
변수에 값 대입 : SET @변수이름 = 변수의 값;
변수의 값 출력 : SELECT @변수이름 ;

변수를 선언하고 출력할 때는 변수부터 SELECT까지 전체를 실행해야한다.

 

데이터 형식 변환 함수

데이터의 형식을 변환해 주는 함수

CAST( expression AS 데이터형식 [ ( 길이 ) ] )

CONVERT( 데이터형식 [ ( 길이 ) ] , expression [ , 스타일 ] )

TRY_CONVERT( 데이터형식 [ ( 길이 ) ] , expression [ , 스타일 ] ) 

PARSE( 문자열 AS 데이터형식 )

TRY_PARSE( 문자열 AS 데이터 형식 ) 함수를 사용한다.

SELECT AVG(CAST(amount AS FLOAT)) AS '평균구매개수'		-- CAST()
  FROM buyTBL;

SELECT AVG(CONVERT(FLOAT, amount)) AS '평균구매개수'		-- CONVERT()
  FROM buyTBL;
  
SELECT AVG(TRY_CONVERT(FLOAT, amount)) AS '평균구매개수'	-- TRY_CONVERT()
  FROM buyTBL;

결괏값

CAST를 했지만 다시 CAST를 하여 변경할 수 있다.

 

스칼라 함수

구성함수 - 현재 구성에 대한 정보를 알 수 있다.

SELECT @@VERSION;

날짜 및 시간 함수 - 날짜 및 시간 입력 값에 대한 함수.

SELECT GETDATE();
SELECT DATEADD(HOUR, -16, GETDATE()); -- YEAR, MONTH, DAY 다 가능함.
SELECT YEAR(GETDATE());

수치 연산 함수

SELECT ABS(-10); --절댓값
SELECT ROUND(3.141592, 2);
SELECT ROUND(1234.5678, -2);
SELECT CAST( RAND() * 100 AS INT);

-- 응용 --
DECLARE @PI DECIMAL(10, 8);	
SET @PI = 3.141592;
SELECT ROUND(@PI, 2);

메타 데이터 함수 - 데이터베이스 및 데이터베이스 개체의 정보를 반환한다.

논리 함수 - 논리 연산을 수행한다.

 

문자열 함수 - 문자열을 조작한다. 활용도가 높다.

SELECT ASCII('A');
SELECT ASCII('a');
SELECT CHAR(97);
SELECT CONCAT('SQL', 'Server', 2019);
SELECT 'SQL' + 'Server ' + CONVERT(VARCHAR(4), 2019);
SELECT UNICODE('하');
SELECT NCHAR(44032);
SELECT CHARINDEX('Server', 'SQL Server 2019');	-- SQL은 0번이 아닌 1번부터 시작

DECLARE @rawstr VARCHAR(20);
SET @rawstr = '  SQL Server 2019  ';
SELECT CHARINDEX('Server', @rawstr);
SELECT LEFT(@rawstr, 3);
SELECT RIGHT(@rawstr, 4);
SELECT SUBSTRING(@rawstr, 5,6);
SELECT LEN(@rawstr);
SELECT UPPER(@rawstr); -- 비교할 때 많이 사용함

SELECT LTRIM(@rawstr);	-- 글자 중간 왼쪽 공백 제거
SELECT RTRIM(@rawstr);	-- 글자 중간 오른쪽 공백 제거
SELECT TRIM(@rawstr);	-- 글자 중간 전체 공백 제거
SELECT REPLACE(@rawstr, 'Server', '서버');

SELECT FORMAT(GETDATE(), 'dd/MM/yyyy hh:mm:ss');
SELECT FORMAT(GETDATE(), 'd','US');
SELECT FORMAT(GETDATE(), ' tt yyyy, MMMM, dd hh:mm:ss'); -- 알파벳 개수로 표현 방법이 다양해진다.
						 	 -- tt로 오전/오후 표시가능
SELECT FORMAT(GETDATE(), 'yyyy MMMM dd dddd');

 

순위 함수

순번(또는 순위)를 처리하기 위한 함수. RANK(), NTILE(), DENSE_RANK(), ROW_NUMBER()가 있다.

순번을 처리하기 위해서 필요했던 복잡한 과정들을 단순화시켜서 쿼리의 작성 시간을 단축시켜 준다.

2등이 두 명이라면 2등, 2등, 4등 식으로 3등을 빼고 4등부터 순위를 매기는 방법을 살펴보자.

분석함수

집계함수와 같이 행 그룹기반으로 계산되지만, 여러 개의 행을 반환할 수 있다.

분석 함수를 이용하면 이동 평균, 백분율, 누계 등의 결과를 계산할 수 있다.

CUME_DIST(), LEAD(), FIRST_VALUE(), PERCENTILE_CONT(), LAG(), PERCENTILE_DISC(), LAST_VALUE(), PERCENT_RANK()등이 있다.

회원 테이블에서 키가 큰 순서로 정렬한 후에, 다음 사람과 키 차이를 미리 알려면 LEAD() 함수를 사용할 수 있다.

비슷한 용도로는 LAG() 함수를 사용할 수 있는데, LEAD()가 다음 행과의 차이라면 LAG()는 이전 행과의 차이를 구하는 것만 다르다. 다른 함수도 한 번씩 사용해보시길 바랍니다 :)

 

 

 

 

PIVOT / UNPIVOT 연산자

PIVOT 연산자는 한 열에 포함된 여러 값을 출력하고, 이를 여러 열로 변환하여 테이블 반환 식을 회전하고 필요하면 집계까지 수행할 수 있다. 또한, UNPIVOT은 PIVOT의 반대되는 연산을 수행한다.

CREATE TABLE pivotTest
(
	uName NCHAR(3),
	season NCHAR(2),
	amount INT
);

INSERT INTO pivotTest VALUES
('김범수', '겨울', 10), ('윤종신', '여름', 10), ('김범수', '가을', 25), 
('김범수', '봄', 3), ('김범수', '봄', 37), ('윤종신', '겨울', 40), 
('김범수', '여름', 14), ('김범수', '겨울', 22), ('윤종신', '여름', 64)

SELECT * FROM pivotTest
PIVOT (SUM(amount)
	   FOR season
	   IN([봄],[여름],[가을],[겨울])) AS resultPivot;

 

pivotTest 테이블을 PIVOT한 결과

JSON 데이터

JSOS(JacaScript Object Notation)은 현대의 웹과 모바일 응용 프로그램 등과 데이터를 교환하기 위한 개방형 표준 포맷을 말하는데, 속성(Key)과 값(Value)으로 쌍을 이루며 구성되어 있다.

JSON의 기본적인 예제를 실행 시켜보면 LINK가 연결된다. 연결된 링크를 클릭하면 아래와 같이 쿼리문이 작성된다.

작성한 쿼리문을 메모장에다가 정리해주면 자신의 테이블에 있는 쿼리문을 돌리고 상대방에게 쉽게 전달할 수 있다.

위의 데이터를 사용해보겠습니다.

C#에서 사용했던 함수와 거의 같다고 생각하면 되는거 같습니다. QUERY는 쿼리문을 보여주고 VALUE는 값을 보여줍니다.

위에서 출력했던 결과로 WITH을 사용하여 원하는 결과를 출력하였습니다. JSON은 추후에 더 자세하게 다루겠습니다. 

*JSON문 안에서는 --(주석)을 할 수 없습니다! 작성을 할 때 주의하세요.

 

하루하루 정말 양이 많네요 페이지수를 계산하면 하루에 100페이지씩 진도를 나가는 중입니다 :0

하지만 열심히 포스팅을 해보겠습니다 감사합니다 이상 포스팅을 마치겠습니다 :)

Hasta Luego~!