데이터베이스 Transact-SQL 기본, 고급(2020.06.09)

복습

 

Ctrl K + C 단체 주석 처리

Ctrl K + U 단체 주석 해제

 

집계 함수는 뒤에 GROUP BY를 해줘야 한다. 결과 안에 필드가 없다면 AS로 이름을 준다. 조건을 줄 때 WHERE문으로도 조건을 줄 수 있지만, WHERE에 들어갈 수 있는 것은 필드의 값에만 쓸 수 있다. 집계 함수 조건으로 쓰고 싶을 때는 HAVING을 사용한다. ORDER BY는 집계 함수도 사용 가능, 필드도 사용 가능 ASC 오름차순, DESC내림차순.

 

ROLLUP() 잡화는 잡화별로 서적은 서적 별로 전자는 전자 별로 합계를 내보자.

 

bouTBL 테이블에  헷갈릴 수도 있으므로 NULL을 없앤다. 

groupName과 num을 그룹으로 총 구매금액 정리

num을 빼면 groupName 별로 나온다. 

하지만 그룹네임 별로 총계를 내고 싶다면 ROLLUP 함수를 사용한다.

 

서적의 총합, 의류의 총합, 잡화의 총합, 전자의 총합, 그리고 모든 것의 총합 출력

 

 GROUPING_ID(),

 

groupName의 마지막 줄 NULL에 만약 ' 총 구매금액'이라는 글자를 넣고 싶다면 추가행이 1이면 C#이든 SQL을 쓰던 groupName을 '총 구매금액'으로 바꾸라는 명령을 주면 된다.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

WITH

Comman Table Expression 을 표현하기 위한 구문이다. 가상의 테이블을 잠시 동안 사용하겠다는 의미. 

서브 쿼리는 쿼리 안에다가 쿼리를 쓰는 방법인데 아래와 같이 테이블이 들어가야 할 자리에 서브쿼리에서 생성된 쿼리가 테이블의 역할을 한다.

가상의 테이블 t 가 되는 것이다.

WHIT 구문을 사용해서 위의 결과와 똑같이 출력되는 것을 볼 수 있다. 첫번째 방법으로 쓰면 계속 서브 쿼리를 적어줘야 하지만 밑의 WITH 구문은 생성된 t 테이블을 계속 쓸 수 있다고 하지만 해결 불가.

 

재귀적 CTE는 나중에

 

트랜잭션 : (커밋 롤백) 보호 차원에서 잘못된 것을 하면 다시 되돌려 준다. 지금 우리는 오토 트랜잭션이 발생된다.

 

INSERT

INSERT INTO testTBL1 VALUES( 1, '홍길동, 25)

INSERT INTO testTBL! (id, name) VALUES (1, '홍길동') 도 가능

한꺼번에 데이터를 여러 개 넣을 때 위와 같이 하면 편하다.

 

자동으로 증가하는 IDENTITY 문자에는 사용 불가,  1씩 자동 증가, 값을 안 넣어도 자동으로 들어간다.

 

아래 명령을 사용하면 IDENTITY로 된 곳에도 값을 줄 수 있다.

SET IDENTITY_INSERT testTBL2 ON;  --IDENTITY에 값을 주고 싶을 때

SET IDENTITY_INSERT testTBL2 OFF; --IDENTITY에 값을 주기 싫을 때

 

마지막으로 줬던 IDENTITY 값 출력

 

SELECT @@IDENTITY

자동 증가하는 값이 여러 개(A, B, C) 있을 때, 마지막으로 입력한 것이 C면  c의 마지막이 나온다. a에 넣으면 a의 마지막이 나온다.

 

SELECT IDENT_CURRENT('testTBL2'); 테이블 자체의 마지막 max값

 

 

UPDATE

UPDATE 테이블 이름 SET 열 1=값 1, 열 2=값 2... WHERE 조건  (업데이트할 때 필요한 값만 한다.)

 

예) UPDATE testTBL SET 

 

userName이 홍길동인 사람의 나이를 99로 바꾼다. 여기서 where절이 없으면 나이 데이터를 전부 99로 바꾼다. 반드시 WHERE를 적어준다.

 

DELETE

testTBL3 id가 2번인 행을 삭제한다. 이것도 다 날아갈 수 있으니 WHERE가 무조건 있어야 한다.

 

DELETE로 데이터를 다 날려버리면 IDENTITY는 원래 있던 숫자의 순서대로 간다. 예를 들어 5번까지 있던 테이블을 DELETE를 사용해서 초기화하고 데이터를 새로 주면 6번부터 시작하지만, TRUNCATE TABEL을 쓰면 자동으로 아이덴티티가 1부터 시작한다. TRUNCATE TABLE 또한 데이터를 다 날려버릴 수 있으니 주의한다.

 

MERGE

하나의 문장에서 경우에 따라서 INSERT도 할 수 있고, UPDATE도 할 수 있고 DELETE도 할 수 있다. 나중에 다시 배운다.

 

---

데이터 형식

데이터 베이스 숫자 데이터 형식

DECIMAL 괄호 사이엔 원하는 사이즈를 넣는다 (10,[2]) 면 전체길이 10에 소수점 길이 2까지

문자 데이터 형식

CHAR(4)에 'UN'을 넣으면 두 개의 공백 발생

VARCHAR(4)에 'UN'을 넣으면 공백이 없다.

 

IMAGE[(n)]  이미지 데이터

BINARY[(n)] 고정길이의 이진 데이터 값

 

날짜와 시간 데이터 형식

기타 데이터 형식

 

* CURSOR는 나중에 프로시저를 만들 때 10개의 데이터에서 하나하나 데이터를 찾아갈 때 쓰는 값이 CURSOR이다.

TALBE 전에 WITH문을 쓸 때처럼 쓴다,

 

GEOMETRY 위도, 경도

 

사용자 정의 데이터 형식

 

CREATE TYPE 사용자 정의 데이터 형식_이름 FROM 기존 데이터 형식 NULL 또는 NOT NULL

 

CREATE TYPE myTypeName FROM NCHAR(10) NOT NULL; 

 

CREATE TABLE 할 때 myTpeName을 적으면 NCHAR(10)의 데이터 형식이 생긴다.

 

변수의 사용

c#과 동일하게 변수를 만들 수 있다 그때는 DECLARE @변수 이름 데이터 형식 으로 선언 한다. 변수에 값을 대입 할 때는 SET @변수이름 = 변수의 값.  변수의 값을 출력할 때는 SELECT@변수 이름

변수 선언 , 변수의 값 초기화, 변수의 출력

 

변수의 활용

데이터 형식 변환 함수

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

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

숫자형을 문자형으로 바꿀 수 있다. 그 반대는 불가능

현재 까진 명시적 형 변환

 

스칼라 함수

구성 함수 별로 안 쓴다 패스. 필요하면 찾아보자.

 

많이 쓰는 것들

 

날짜 관련 함수

 

SELECT GETDATE(); 회원이 가입한 시간을 넣고 싶을 때 현재 시간이 바로 들어간다.

SELECT DATAADD(DAY, 1, GETDATE())  DAY에 1을 더해서 내일이 된다.

 

DATEDIFF() 별로 안 씀

DATENAME() 별로 안 씀

 

SELECT YEAR(GETDATE()); 올해 연도만 나온다. MONTH DAY도 나온다,

 

수치 연산 함수

 

SELECT ABS();  절댓값 리턴

SELECT ROUND(3.141592, 2 ); 반올림 소수점 2자리까지만 출력  ********많이 씀

SELECT ROUND(1234.5678, -2 );왼쪽으로 가는 반올림 (1200)

SELECT CAST(RAND()*100 AS INT); 0 ~ 100부터 랜덤 한 수소 수점 밑도 나오는데 CONVERT 써서 소수점 밑의 자리는 버린다.

 

메타 데이터 함수 잘 안 씀

 

논리 함수 잘 안 씀

 

문자열 함수

 

SELECT ASCII('A');  결과 : 65 문자에 대한 아스키코드값을 돌려주거나 아스키코드값의 문자를 돌려준다.

 

SELECT CONCAT('SQL' , ' Server ' , 2019);  결과 : SQL Server 2019    값들을 합쳐서 출력

SELECT 'SQL'+' Server ' + CONVERT(VARCHAR(4),2019)';  똑같은 거

 

SELECT UNICODE('하); 결과 : 54616  문자의 유니코드 값을 돌려주거나 유니코드 값의 문자를 돌려준다.

 

SELECT CHARINDEX('Server', 'SQL Server 2019'); Server의 시작 위치를 리턴한다

결과 : 5       -- 1부터 시작한다.

 

SELECT LEFT/RIGHT() 글자를 왼쪽/오른쪽으로부터 숫자만큼만 끊어서 출력

결과 : SQL

SELECT SUBSTRING(@rawstr, 5, 6); 5번째 글자부터 6글자 출력

 

SELECT LEN(@rawstr); 결과 : 15 문자열의 길이를 리턴

 

SELECT UPPER(@rawstr)  문자열 전부를 대문자로 바꾼다. 값을 비교할 때 많이 쓴다.

SELECT LOWER(@rawstr) 문자열 전부를 소문자로 바꾼다.

 

SELECT LTRIM(@rawstr) 앞에 있는 공백을 없애준다

SELECT RTRIM(rawstr) 뒤에 있는 공백을 없애준다.

 

SELECT REPLACE(@rawstr, 'Server', '서버'); ' SQL Server 2019 ' 에서 Server를 서버로 바꾼다. 

REVERSE()는 문자열 순서 거꾸로

SPACE(5) 공백을 지정한 수만큼 반복한다.

STR() 숫자를 문자로 변환한다. 

STUFF('SQL 서버 2016',5,2, 'Server) 5번째 위치부터 2개 없애고 Server를 끼워 넣는다.

문자를 지정한 위치의 개수만큼 삭제한 후에 새로운 문자를 끼워 넣는다.

 

**FORMAT( value, format)  value는 출력할 값, format 은 출력할 형식

SELECT FORMAT( GETDATE(), 'dd/MM/yyyy' ); 

 

SELECT FORMAT( GETDATE(), 'yyyy/MM/dd hh:mm:ss tt' );  tt는 오전 오후

 

SELECT FORMAT( GETDATE(), 'd', 'us'  ); 결과 : 06/09/2020

 

SELECT FORMAT( GETDATE(), 'yyyy-MM-dd tt hh:mm:ss ' ); -> 제일 많이 쓰는 포맷

 

순위 함수

 

<순위 함수 이름> () OVER ( [PARTITION BY <partition_by_list>] ORDER BY <order_by_list>)

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

분석 함수 PASS

 

PIVOT / UNPIVOT

데이터를 뽑으면 무조건 밑으로 내려간다. 하지만 보통사람들은 분류 별로 한번에 보고 싶어한다. 엑셀에도 피봇이 똑같이 있는데, 데이터를 가로로 표현하는 것이 피봇이다.

 

여러 열로 변환하여 테이블 한환 식을 회전하고 필요하면 집계까지 수행

 

PIVOT (집계함수 (열)

          FOR 새로운 열로 변경할 열이름

          IN (열 목록) AS 피벗이름)

 

season에 대해서 amount를 더해서 출력

FOR다음에는 그루핑하고 싶은 열

 

JSON 데이터

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

 

xml을 주로 썼었는데 쓸모없는 데이터가 많아져서 크기가 자꾸 늘어난다. 따라서 java scrpit에서 경량화된 포맷을 개발했다. 너무 좋아서 지금은 표준이 되었고 많이 쓴다. xml은 한물 갔다. 

xml 보다 훨씬 간결하다.

 

FOR JSON AUTO 를 이용해서  SELECT 문의 결과를 JSON 형태로 변환한다.

변환 결과 : 

 

JSON 불러오기 각종 JSON 함수를 가져다가 다시 불러온다.

 

들어온 데이터를 가상으로 만들어서 @json이라는 변수 안에 넣고 처리했다.

결과는 DB에 있는 데이터와 상관이 없는 JSON데이터에서 추출한 데이터이다.

 

SELECT name, height
FROM userTBL
WHERE height >= 180
FOR JSON AUTO;  --JASON 데이터 생성
---------------------------------------------------------------------------------------

 

DECLARE @json VARCHAR(MAX)
SET @json = N'{ "userTBL" :
[
{"name":"임재범","height":182},
{"name":"이승기","height":182},
{"name":"성시경","height":186}
]
}';  --위에서 만든 JSON이 아닌 위와 같은 JSON을 가져왔다고 가정

--SELECT ISJSON(@json);    단순확인
--ISJSON 은 JSON 형식인지 확인 맞으면 1 틀리면 0
SELECT JSON_QUERY(@json,'$.userTBL[0]'); -- 뒤에 넣는건 키값을 찾아가는 경로
--$는 경로의 형식이다.
SELECT JSON_VALUE(@json,'$.userTBL[0].name'); -- 0번 인덱스의 이름

SELECT * FROM OPENJSON(@json, '$.userTBL')
WITH (
name NCHAR(8) '$.name',
height INT '$.height');

--SELECT name, height INTO tempUser2
--FROM userTBL;

--SELECT * FROM tempUser2;
--DELETE tempUser2;  tempUser2 테이블 다 비우기

--SELECT된 결과를 새로운 테이블에 넣을 때는 밑과 같이 사용

INSERT INTO tempuser2       
SELECT * FROM OPENJSON(@json,'$.userTBL')
WITH (
name NCHAR(8) '$.name',
height INT '$.height');

SELECT * FROM tempuser2; --확인

 

 

 

 

10개 -> 8개 NULL 인 것 제외

SELECT INTO 는 데이터를 전혀 새로운 테이블에 집어넣을 때 사용한다.

 

 

 

INSERT INTO 는 있는 테이블에 새로운 데이터를 집어넣을 때 사용한다.