[문과 코린이의 IT 기록장] MSSQL - 데이터 베이스 활용 (날짜 관련해서 많이 사용하는 업무 처리 문제들, 최종 실습 테이블, 숫자 관련 데이터 타입)

[문과 코린이의 IT 기록장] MSSQL - 데이터 베이스 활용 (날짜 관련해서 많이 사용하는 업무 처리 문제들, 최종 실습 테이블, 숫자 관련 데이터 타입)

[문과 코린이의 IT 기록장] MSSQL - 데이터 베이스 활용 (날짜 관련해서 많이 사용하는 업무 처리 문제들, 최종 실습 테이블, 숫자 관련 데이터 타입)

---

< 참고 강의 영상 >

MSSQL Server 2016 기반의 데이터베이스 입문에서 활용까지 Part.3 대시보드 - 인프런 | 강의 (inflearn.com)

MSSQL Server 2016 기반의 데이터베이스 입문에서 활용까지 Part.3 - 인프런 | 강의

2022.01.26 - [문과 코린이의, [SQL] 기록] - [문과 코린이의 IT 기록장] MSSQL - GROUP BY 추가 설명, 다중 그룹

[문과 코린이의 IT 기록장] MSSQL - GROUP BY 추가 설명, 다중 그룹

2022.01.27 - [문과 코린이의, [SQL] 기록] - [문과 코린이의 IT 기록장] MSSQL - 데이터 베이스 활용 (테이블 스크립트, 2개 이상의 컬럼을, 기본키로 만들기, CLUSTERED VS NONCLUSTERED, DB내 인덱스 확인, CLUSTERED INDEX / NONCLUSTERED INDEX 실습)

[문과 코린이의 IT 기록장] MSSQL - 데이터 베이스 활용 (테이블 스크립트, 2개 이상의 컬럼을, 기본

2022.01.28 - [문과 코린이의, [SQL] 기록] - [문과 코린이의 IT 기록장] MSSQL - 데이터 베이스 활용( 한글 영문 우선순위, 기본키(PK) / 유니크(UQ) 제약조건 추가/삭제, 기본키명 직접 지정 방법)

[문과 코린이의 IT 기록장] MSSQL - 데이터 베이스 활용( 한글 영문 우선순위, 기본키(PK) / 유니크(UQ)

---

1. 날짜 관련해서 많이 사용하는 업무 처리 문제들

/* 날짜 관련해서 많이 해보게 되는 문제들 */

-- 테이블 생성
CREATE TABLE dbo.tbl_date
(
	cus_id		CHAR(10)		NOT NULL		CONSTRAINT		PK_CUSID		PRIMARY KEY,
	cus_name	NVARCHAR(20)	NOT NULL,
	cus_date	DATE			NOT NULL
)

EXEC sp_helpindex tbl_date

-- INSERT
INSERT INTO dbo.tbl_date 
VALUES
	('aaa',N'강길동','2000-01-01'),
	('bbb',N'박길동','2000-02-01'),
	('ccc',N'장길동','2000-03-01'),
	('ddd',N'동길동','2000-04-01'),
	('eee',N'조길동','2000-05-01'),
	('fff',N'이길동','2000-06-01'),
	('ggg',N'나길동','2000-07-01'),
	('hhh',N'한길동','2000-08-01'),
	('iii',N'정길동','2000-09-01');


-- SELECT
SELECT * FROM dbo.tbl_date;

ex 1. 회원 아이디 ddd에 대해서 날짜를 5일 연장하고 싶다면?

DATEADD함수 : 어떤 날짜에 기간을 더해주는 함수

- 형식 : DATEADD(더할 날짜 단위(ex. 일 단위? 월 단위?), 더할 숫자(간격), 날짜(컬럼명) )

UPDATE dbo.tbl_date 
SET cus_date = DATEADD(DAY,5,cus_date)
WHERE cus_id = 'ddd';
-- 업데이트 쿼리를 쓸 때는 무조건 조건을 걸어두는 것을 중요하게 생각해야함.

 

ex 2. 회원 이름이 강길동에 대해서 날짜를 6개월 추가시키고 싶다면?

UPDATE dbo.tbl_date
SET cus_date = DATEADD(MONTH,6,cus_date)
WHERE cus_name = '강길동';

 

ex 3. 회원 아이디가 eee에 대해서 기간을 5일 빼고 싶다면?

UPDATE dbo.tbl_date
SET cus_date = DATEADD(DAY,-5,[cus_date])
WHERE cus_id = 'eee';

 

ex 5. 데이터를 수정이나 변경하지 않고, 5일 추가된 결과만 보고 싶다면?

SELECT DATEADD(DAY,10,cus_date)
FROM dbo.tbl_date

 

ex 6. 실제 마감일(2000-7-5)보다 10일 연장된, 날짜 마감을 넘긴 회원은 몇명인지 알고 싶다면?

 * 제대로 마감일 전에 등록 회원 / 10일 연장 기간에 등록한 회원 / 날짜 마감을 아에 넘긴 회원

-- 변수를 사용하지 않은 것
SELECT count(*) as '연장 마감을 넘긴 회원'
	FROM dbo.tbl_date
	WHERE cus_date > DATEADD(DAY,10,'2000-07-05')

-- 변수를 사용한 것
DECLARE @a INT; -- 변수선언 @변수명 데이터타입;
SET @a = 10; -- 값 대입

DECLARE @b DATE;
SET @b = '2000-07-05' -- 원래 마감일

DECLARE @duedate DATE = DATEADD(DAY,@a,@b); -- 연장된 마감일

SELECT count(*) as '연장 마감을 넘긴 회원'
	FROM dbo.tbl_date
	WHERE cus_date > @duedate

 

---

2. 최종 실습 테이블

- dbo.tbl_members : 회원 테이블 - dbo.tbl_sales1 : 매출(주문) 테이블1 - dbo.tbl_sales2 : 매출(주문) 테이블2 - dbo.tbl_products : 상품 테이블 - dbo.tbl_vendors : 벤더사

CREATE DATABASE SCRIPT1

USE SCRIPT1

-- 회원 테이블
CREATE TABLE dbo.tbl_members
(
	m_id		CHAR(16)		NOT NULL		PRIMARY KEY,
	m_name		NVARCHAR(100)	NOT NULL,
	m_address	NVARCHAR(100)	NULL,
	m_country	CHAR(50)		NULL,
	m_tel		CHAR(50)		NULL,
	m_email		CHAR(300)		NULL,
);

-- 매출(주문) 테이블 1 (가장 중요한 것만 보관 - 주문번호가 가장 중요 (영수증과 관련한 것))
CREATE TABLE dbo.tbl_sales1
(
	s1_num		INT			NOT NULL		PRIMARY KEY,	 -- 주문번호
	s1_date		DATETIME	NOT NULL,						 -- 매출이 발생한 날짜 기록
	m_id		CHAR(16)	NOT NULL,						 -- 누가 주문을 했는지 알기 위함 (매출을 발생시켰는지) 
	-- [주소,번호,이메일 등까지 알필요 x - 아이디값만 알면 됨] 필요하다면 JOIN을 통해 정보를 가져오면 됨.
);

-- 매출(주문) 테이블 2
CREATE TABLE dbo.tbl_sales2
(
	s2_num			INT				NOT NULL, -- 주문번호 (s1_num과 같아야 함)
	s2_ordertem		INT				NOT NULL, -- 주문 횟수 (몇개를 주문했는가?)
	p_id			NVARCHAR(50)	NOT NULL, -- 상품의 고유 id
	qty				INT				NOT NULL, -- 상품 수량
	otem_price		MONEY			NOT NULL, -- 상품 가격

	-- s2_num / s2_ordertem 두개의 컬럼을 묶어서 하나의 기본키로 지정되어야 함. (기본키는 하나여야 함)
	PRIMARY KEY CLUSTERED 
	(
		s2_num, s2_ordertem
	)
);


-- 상품 테이블
CREATE TABLE dbo.tbl_products
(
	p_id		NVARCHAR(50)		NOT NULL		PRIMARY KEY,-- 상품 id
	p_name		NVARCHAR(300)		NOT NULL,					-- 상품 이름
	p_price		MONEY				NOT NULL,					-- 상품 가격
	p_detail	NVARCHAR(1000)		NULL,						-- 상품 구조 
	v_id		NVARCHAR(25)		NOT NULL,					-- 상품 공급사 id
);

CREATE TABLE dbo.tbl_vendor
(
	v_id		NVARCHAR(25)		NOT NULL		PRIMARY KEY,	-- 공급사 id
	v_name		NVARCHAR(50)		NOT NULL,						-- 공급사 이름
	v_businesslicense		CHAR(9)	NOT NULL,						-- 사업자 번호
);

 

---

3. 숫자 관련 데이터타입

1) NUMERIC / DECIMAL

- CONVERT함수와 함께 사용하는 것들에 대해 숙지 (문자->숫자 변환) : NUMERIC / DECIMAL

 * 이 둘은 기능이 같음

SELECT
CONVERT(INT, '1234') AS '정수',					 -- '1234'는 문자로 인식
CONVERT(FLOAT,'1234.56') AS '실수(부동소수점)',
CONVERT(NUMERIC,'1234.5678') AS '실수',			 -- 조건이 없는 NUMERIC : 소수점 첫번째부터 반올림시켜버림 ( => 1235 )
CONVERT(DECIMAL,'1234.5678') AS '실수',			 -- 조건이 없는 DECIMAL : 소수점 첫번째부터 반올림시켜버림 ( => 1235 )
CONVERT(NUMERIC(8,3),'1234.5678') AS '실수',		 -- 조건이 있는 NUMERIC : 소수점 셋째자리까지 표현 ( => 1234.568 )
CONVERT(DECIMAL(8,3),'1234.5674') AS '실수'		 -- 조건이 있는 DECIAML : 소수점 셋째자리까지 표현 ( => 1234.567 ) // 반올림 대상 X

결과물

2) MONEY / DECIMAL

- 3자리마다 콤마를 찍어주기 위해서는, MONEY로 지정하거나, MONEY로 컨버팅하기.

- 타입이 VARCHAR라도 숫자 포맷이면 변환이 가능하다.

SELECT(123456789) -- 그냥 출력
SELECT CONVERT(MONEY, 123456789) -- MONEY타입으로 변환 (3자리마다 콤마가 찍히지는 않음 / 뒤에 .00붙음)
SELECT CONVERT(VARCHAR,CONVERT(MONEY,123456789),1) -- MONEY타입으로 변환한 것을, 다시 CONVERT로 VARCHAR를 사용 (3자리마다 콤마가 찍힘)
SELECT CONVERT(VARCHAR,CONVERT(DECIMAL(12,2),123456789),1) -- DECIMAL타입으로 변환 (3자리마다 콤마도 찍히지 않으며 .00도 붙지 않음)
SELECT REPLACE(CONVERT(VARCHAR,CONVERT(MONEY,123456789),1),'.00','') -- .00을 없애주기 위함. (외국은 .00을 찍는 경우가 있어서 MONEY타입이 이렇게 만들어진 것)

결과물

3) MONEY / SUBSTRING

- SUBSTRING / CONVERT / LEN 함수 등의 사용법을 숙지한다.

- 기본 형식 : SUBSTRING(expression, start, length)

SELECT SUBSTRING('abcdef',2,3)
SELECT SUBSTRING('123456789',1,2)
SELECT SUBSTRING('123456789',3,4)

결과물

SELECT(123456789)
SELECT CONVERT(MONEY,'123456789')
SELECT CONVERT(VARCHAR, CONVERT(MONEY,'123456789'),1) -- 스타일 1번이 3자리마다 콤마를 찍어주는 것

SELECT SUBSTRING(CONVERT(VARCHAR,CONVERT(MONEY,'123456789'),1),1,11) -- 이는 금액이 변화하면 출력에 문제가 발생함.
/* 개선 방안 */
SELECT LEN(CONVERT(VARCHAR,CONVERT(MONEY,123456789),1))-3 -- .00이라는 3자리를 빼주는 것 (총 자리수 - 3자리)
SELECT SUBSTRING(CONVERT(VARCHAR,CONVERT(MONEY,'123456789'),1),1,LEN(CONVERT(VARCHAR,CONVERT(MONEY,123456789),1))-3)

SELECT SUBSTRING
( -- 가독성을 위해 여러 줄로 사용하는 법
	CONVERT(VARCHAR, CONVERT(MONEY,123456789),1),		-- SUBSTRING의 첫번째 값
	1,													-- SUBSTRING의 두번째 값
	LEN(CONVERT(VARCHAR,CONVERT(MONEY,123456789),1))-3  -- SUBSTRING의 세번째 값
) 
AS '.00제거'

---

* 유의사항 - 아직 공부하고 있는 문과생 코린이가, 정리해서 남겨놓은 정리 및 필기노트입니다. - 정확하지 않거나, 틀린 점이 있을 수 있으니, 유의해서 봐주시면 감사하겠습니다. - 혹시 잘못된 점을 발견하셨다면, 댓글로 친절하게 남겨주시면 감사하겠습니다 :)