[문과 코린이의 IT 기록장] MSSQL - 데이터 베이스 활용(최종 실습 스크립트 작성하기, 참조 무결성, INSERT for 조인, 뷰 실습)

[문과 코린이의 IT 기록장] MSSQL - 데이터 베이스 활용(최종 실습 스크립트 작성하기, 참조 무결성, INSERT for 조인, 뷰 실습)

< 참고 강의 영상 >

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

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

데이터베이스 구축에서부터 테이블 생성, 각종 SQL 쿼리문을 사용해서 데이터베이스 내 데이터를 조작, 변경할 수 있는 강의입니다., - 강의 소개 | 인프런...

www.inflearn.com

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

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

[문과 코린이의 IT 기록장] MSSQL - GROUP BY 추가 설명, 다중 그룹 < 참고 강의 영상 > MSSQL Server 2016 기반의 데이터베이스 입문에서 활용까지 Part.2 대시보드 - 인프런 | 강의 (inflearn.com)..

vansoft1215.tistory.com

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

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

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

vansoft1215.tistory.com

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

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

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

vansoft1215.tistory.com

1. 최종 실습 스크립트 작성하기

- 쇼핑몰 테이블 스크립트를 쿼리창에서 작성한다.

- 기본키는 테이블 스크립트를 작성시 같이 작성하고 외래키는 별도로 작성한다.

- 각 테이블에 데이터를 입력한다.

- 각 테이블을 연결(JOIN)하여 원하는 결과를 출력한다.
* 조인(JOIN)
* 뷰(VIEW)

- 백업과 복원을 수행한다.

 CREATE DATABASE [7TO7] -- CREATE DATABASE
 
USE [7TO7] -- CHANGE DATABASE
 
-- CREATE TABLE
----------------------------------------------------------------------
-- [ 1 ] dbo.tbl_members : 회원 테이블
----------------------------------------------------------------------
CREATE TABLE dbo.tbl_members
(
	m_id		CHAR(16)			NOT NULL		PRIMARY KEY,	-- 누가 주문했는지 ID
	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,
);
 
 
----------------------------------------------------------------------
-- [ 2 ] dbo.tbl_sales1 : 매출(주문) 테이블 1
----------------------------------------------------------------------
CREATE TABLE dbo.tbl_sales1
(
	s1_num		INT				NOT NULL		PRIMARY KEY,	-- 주문번호
	s2_date		DATETIME		NOT NULL,						-- 날짜(0000-00-00 00:00:00)
	m_id		CHAR(16)		NOT NULL,						-- 누가 주문했는지 ID
);
 
----------------------------------------------------------------------
-- [ 3 ] dbo.tbl_sales2 : 매출(주문) 테이블 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
 
	
	PRIMARY KEY CLUSTERED -- 두개의 컬럼을 하나의 기본키로 만들어놓은 것.
	(
		s2_num, s2_ordertem
	)
);
 
 
----------------------------------------------------------------------
-- [ 4 ] dbo.tbl_product : 상품 테이블
----------------------------------------------------------------------
CREATE TABLE dbo.tbl_products
(
	p_id		NVARCHAR(50)		NOT NULL		PRIMARY KEY,
	p_name		NVARCHAR(300)		NOT NULL,
	p_price		MONEY				NOT NULL,
	p_detail	NVARCHAR(1000)		NULL,							-- 상품 상세 설명
	v_id		NVARCHAR(25)		NOT NULL						-- 공급사 ID(회사명)
);
 
 
 
----------------------------------------------------------------------
-- [ 5 ] dbo.tbl_vendors : 벤더사 테이블
----------------------------------------------------------------------
CREATE TABLE dbo.tbl_vendors
(
	v_id		NVARCHAR(25)		NOT NULL		PRIMARY KEY
);
 
 
 
 
 
-- ALTER TABLE
 
----------------------------------------------------------------------
-- 외래키(FOREIGN KEY) 정의 [1]
-- dbo.tbl_members테이블의 m_id 기본키를, dbo.tbl_sales1의 m_id를 외래키로 연결하기
----------------------------------------------------------------------
ALTER TABLE dbo.tbl_sales1
	ADD -- 삭제는 drop
		CONSTRAINT FK_sales1_members		-- 외래키 이름 : FK_지정테이블명_참조테이블명
			FOREIGN KEY	(m_id)
				REFERENCES tbl_members(m_id);  -- 기본키가 있는 테이블 (기본키)
 
 
EXEC sp_helpconstraint tbl_sales1
 
 
 
----------------------------------------------------------------------
-- 외래키(FOREIGN KEY) 정의 [2]
----------------------------------------------------------------------
ALTER TABLE dbo.tbl_sales2
	ADD
		CONSTRAINT FK_tbl_sales2_sales1 
			FOREIGN KEY(s2_num) -- 기본키는 외래키가 될 수 있음.
				REFERENCES tbl_sales1(s1_num), -- 다음 외래키 지정시 콤마(,)를 적어줘야 함.
		CONSTRAINT FK_tbl_sales2_product
			FOREIGN KEY(p_id)
				REFERENCES tbl_products(p_id);  -- 마지막은 세미콜론(;) 작성 필요.
 
 
----------------------------------------------------------------------
-- 외래키(FOREIGN KEY) 정의 [3]
----------------------------------------------------------------------
ALTER TABLE dbo.tbl_products
	ADD
		CONSTRAINT	FK_tbl_products_vendors
			FOREIGN KEY(v_id)
				REFERENCES	tbl_vendors(v_id);

2. 참조 무결성

- 테이블과 테이블간 관계를 맺게 되면(외래키지정), 참조하는 테이블열의 값이 아닌 다른 값이 들어올 수 없다.
- 또한 입력된 값도, 참조하는 테이블열이 아닌 다른 값으로 수정 및 변경할 수 없다.
- 참조 무결성에 위배되기 때문에 이러한 수정 및 입력이 되지 않는 것이다.
- RDBMS가 일관성있게 데이터가 유지될 수 있도록 관리한다.

 ----------------------------------------------------------------------
-- 참조 무결성 확인 
-- INSERT DATA
----------------------------------------------------------------------
-- 테이블과 테이블간 관계를 맺게 되면(외래키지정), 참조하는 테이블열의 값이 아닌 다른 값이 들어올 수 없다.
-- 또한 입력된 값도, 참조하는 테이블열이 아닌 다른 값으로 수정 및 변경할 수 없다.
-- 참조 무결성에 위배되기 때문에 이러한 수정 및 입력이 되지 않는 것이다.
-- RDBMS가 일관성있게 데이터가 유지될 수 있도록 관리한다.
 
 
-- [1] dbo.tbl_members
SELECT * FROM dbo.tbl_members
 
/* 기본 입력 */ 
-- 회원테이블에 입력
INSERT INTO dbo.tbl_members VALUES('antman','앤트맨','서울','America','010-2134-5678','antman@naver.com');
INSERT INTO dbo.tbl_members VALUES('batman','베트맨','서울','America','010-2134-5338','batman@naver.com');
-- 매출테이블에 입력
INSERT INTO dbo.tbl_sales1 VALUES(1001,'2000-12-25 15:22:10','batman'); -- m_id로 회원-매출 테이블 기본키-외래키 연결되어있음. 즉, 이를 추가하려면 회원테이블에 해당 m_id가 존재해야 함.
INSERT INTO dbo.tbl_sales1 VALUES(1002,'2000-12-25 15:23:10', 'antman');
INSERT INTO dbo.tbl_sales1 VALUES(1003,'2000-12-25 15:23:10', 'superman'); -- 해당 m_id가 회원 테이블에 존재하지 않으므로, 에러 발생
 
/* 수정,변경 */
UPDATE dbo.tbl_sales1
	SET m_id = 'superman'
	WHERE s1_num = '1001'
-- 오류 발생 : sales1테이블 외래키인 m_id와 연결된 기본키는, 회원 테이블의 m_id이며, 이 테이블 내에 해당 값이 존재하지 않으므로 오류가 발생한다. 
UPDATE dbo.tbl_sales1
	SET m_id = 'batman'
	WHERE s1_num = '1001'
-- 수정,변경 가능 O

 ----------------------------------------------------------------------
-- INSERT DATA
----------------------------------------------------------------------
-- [1] dbo.tbl_members
INSERT INTO dbo.tbl_members VALUES('antman','앤트맨','서울','America','010-2134-5678','antman@naver.com');
INSERT INTO dbo.tbl_members VALUES('batman','베트맨','서울','America','010-2134-5338','batman@naver.com');
INSERT INTO dbo.tbl_members VALUES('abc','ABC','부산','KLorea', '010-5421-3424','abc@naver.com');
 
-- [2] dbo.tbl_vendors
INSERT INTO dbo.tbl_vendors
	VALUES
		('럭키금성'),
		('애플'),
		('LG'),
		('불티나'),
		('그램');
 
-- [3] dbo.tbl_products
SELECT * FROM dbo.tbl_products
INSERT INTO dbo.tbl_products(p_id,p_name,p_price,p_detail,v_id) 
	VALUES
		('GS101','텔레비전',990000,'커브드형의 미래지향적 최신형 모델', '럭키금성'),
		('GS102','냉장고',700000,'얼음물에 얼린듯한 느낌의 냉장고', '애플'),
		('GS103','김치냉장고',550000,'김치는 역시 김치냉장고가 최고', 'LG'),
		('GS104','오디오',440000,'오디오의 명가에서 만든 최고의 오디오', '불티나'),
		('GS105','컴퓨터',220000,'세계에서 최고로 가벼운 컴퓨터 그램', '그램');
 
 
ALTER TABLE dbo.tbl_products
	ALTER COLUMN v_id NVARCHAR(25)	NOT NULL;
 
-- [4] dbo.tbl_sales1
SELECT * FROM dbo.tbl_sales1
INSERT INTO dbo.tbl_sales1 VALUES(1001,'2000-12-25 15:22:10','batman'); 
INSERT INTO dbo.tbl_sales1 VALUES(1002,'2000-12-25 15:23:10', 'antman');
INSERT INTO dbo.tbl_sales1 VALUES(1003,'2000-12-25 15:23:10', 'abc'); 
 
-- [5] dbo.tbl_sales2
SELECT * FROM dbo.tbl_sales2 ORDER BY s2_num 
 
SELECT s2_num, SUBSTRING(CONVERT(VARCHAR,SUM(otem_price),1),1,LEN(CONVERT(VARCHAR,SUM(otem_price),1))-3)  AS '가격 합계'
	FROM dbo.tbl_sales2
	GROUP BY s2_num
	ORDER BY s2_num 
 
INSERT INTO dbo.tbl_sales2(s2_num,s2_ordertem,p_id,qty,otem_price)
	VALUES
		(1001,1,'GS101',3,990000*3),
		(1001,2,'GS102',1,700000*1),
		(1002,1,'GS101',2,990000*2),
		(1003,1,'GS103',1,550000*1);

3. INSERT for 조인, 뷰 실습

- 조인, 뷰 등의 실습을 위한 데이터 입력
- 앞에서 배운 다양한 입력 방법 등을 활용해, 데이터를 입력
- 기본적인 쇼핑몰 데이터베이스 구축
* 스크립트 테이블 생성
* 외래키 지정 (참조 무결성)
* 데이터 입력
* 각종 쿼리 실습 (CRUD / JOIN / VIEW / SUB QUERY ...)

1) CRUD

- 프로그래밍 언어와 DB연동을 통해 구현해볼 수 있는 기본적인 기능단위

- 4개의 기능을 묶어서 CRUD로 표현한다. : Create(생성) / Read(읽기) / Update(수정) / Delete(삭제)

- 프로그래밍 언어를 공부할 때는, 해당 프로그램이 기본적으로 갖춰야 할 기능(인터페이스)단위로써도 설명이 되어지곤 한다.

- CRUD 각각은 아래와 같은 SQL문으로 각각 대응된다.

* Create = Insert / Read = Select / Update = Update / Delete = Delete

- 이러한 4가지 기능이 구현되지 않은 프로그램은, 완전하다고 할 수 없다.

- 꼭 DB연동이 아니더라도, (파일)프로그램의 가장 기본적인 기능단위라고 할 수 있다.

2) JOIN

- SQL을 사용하면서 반드시 있어야만 하는 기능 중 하나.

- 테이블과 테이블을 서로 묶어서(연결하여) 원하는 정보를 만들어낸다. (SELECT)

- 따라서, 테이블과 테이블의 관계 및 구조에 대해서 잘 알아야한다.

- 테이블 관계를 잘 모른다면 조인을 작성하기가 어렵다.

(1) 조인이 필요한 이유

- 기본적으로 테이블이 쪼개져있기 때문.

- 단지 하나의 테이블만을 사용해서 DB를 구축한다면, 조인도 필요하지 않음.

ex. A회사가 여러 제품을 쇼핑몰에 납품한다면?

: 해당 판매회사에 대한 정보를 매번 상품 입력할때마다 할 필요 x. 해당 제품 공급사 테이블에 관련 정보를 한번만 입력해놓음. 즉, 이러한 과정에서 테이블간의 관계가 만들어지므로, 조인이 필요함.

(2) 조인의 장점 (= 테이블 분산의 장점)

- 데이터가 중복되지 않도록 해주며, 반복적인 작업을 줄여준다.

- 데이터가 중복되지 않으므로, 일관성이 유지된다. => 일관성 유지

- 추후 DB를 더 확장하거나 할 경우, 유연하게 대처 가능하다. => 데이터 확장성

(3) 조인을 써야하는 상황

ex. 회원테이블 매출테이블 각각 출력하면 원하는 정보를 보기가 어렵다.

SELECT * FROM dbo.tbl_members

SELECT * FROM dbo.tbl_sales1

만약, 결제한 회원들에 대한, 이름, 주소, 이메일을 보고자 한다면?

=> 조인을 사용하면, 각각의 테이블에 들어있는 데이터를 하나의 쿼리로 만들어낼 수 있다.

=> 그러기 위해서는, 데이터 분산시 외래키 지정을 잘 해줘야 한다.

(4) 조인 사용방법

 ----------------------------------------------------------------------
-- JOIN
----------------------------------------------------------------------
 
SELECT m_name, m_address, m_email
	FROM dbo.tbl_members, dbo.tbl_sales1
	WHERE dbo.tbl_members.m_id = dbo.tbl_sales1.m_id

(5) 조인 별칭 사용

- 모호한 것을 없애주며, 긴 테이블명을 줄여준다.

- 별침을 지정했다면, 적극적으로 사용해야한다.

 SELECT M.m_id,m_name, m_address, m_email
	FROM dbo.tbl_members AS M, dbo.tbl_sales1 AS S1
	WHERE M.m_id = S1.m_id

(6) 3개 이상 테이블 조인 - 조인 2번 이상 발생

a. FROM절에 3개의 테이블명을 명시해주기

b. 그 후, 각각의 테이블과 테이블간의 관계를 잘 생각해 JOIN해주면 됨.

c. 주어진 문제에 따라 필요한 조건을 추가

 ---------------------------------------------------------------------
-- 3개 이상 테이블 join
---------------------------------------------------------------------
-- Q. 김치냉장고(GS103)를 구매한 고객의 아이디와 이름을 알고 싶다면?
 
SELECT * FROM dbo.tbl_members -- 고객의 아이디와 이름
SELECT * FROM dbo.tbl_sales1 -- 구매한 고객의 id파악
SELECT * FROM dbo.tbl_sales2 -- 김치냉장고(GS103)를 구매한 고객
 
SELECT Mem.m_id, m_name 
	FROM dbo.tbl_members AS Mem, dbo.tbl_sales1 AS S1, dbo.tbl_sales2 AS S2
	WHERE Mem.m_id = S1.m_id AND S1.s1_num = S2.s2_num AND S2.p_id='GS103'

(7) 3개 이상 테이블 조인 - 서브쿼리

- 조인은 보통 같은 결과물을 서브쿼리로도 만들 수 있다.

- 어떤 결과의 출력을, 조인과 서브쿼리 모두로 작성할 수 있다면, 어느쪽이 더 성능상 유리할지를 다각도로 테스트하여 판단해야 한다.

* 성능은 보통 단순 쿼리만의 문제는 아니다.

* DBMS의 기술적 성능, 물리적 HW의 성능, DB 구조 등에 영향이 큼

* 영향을 끼치는 요소가 굉장히 많음

* 인덱스 존재유무, 데이터 양 등도 영향을 끼칠 수 있음.

[문과 코린이의 IT 기록장] MSSQL - 서브 쿼리 : IN 연산자, 상관 서브쿼리, 다중 INSERT 여러 방법, 서

[문과 코린이의 IT 기록장] MSSQL - 서브 쿼리 : IN 연산자, 상관 서브쿼리, 다중 INSERT 여러 방법, 서브쿼리 연습문제, IN, EXISTS 연산자, IN, EXISTS 연산자 연습문제 < 참고 강의..

vansoft1215.tistory.com

 ---------------------------------------------------------------------
-- 서브쿼리 (보기에 조금 더 직관적임 - 성능은 상대적으로 떨어질 수도 있음)
---------------------------------------------------------------------
SELECT M.m_id,m_name,m_address,m_tel
	FROM dbo.tbl_members M, dbo.tbl_sales1 S1, dbo.tbl_sales2 S2
	WHERE 
        M.m_id 
		IN( 
			SELECT S1.m_id 
			FROM dbo.tbl_sales1 S1
			WHERE S1.s1_num -- s1_num = 아래의 연산자 결과들과 비교
				IN( -- 다중 결과가 나올 경우에는 IN연산자 사용
					SELECT S2.s2_num 
					FROM dbo.tbl_sales2 S2
					WHERE S2.p_id = 'GS103'
				)
		);

3) VIEW

- 보통 조인을 설명하는 과정에서, 뷰가 등장한다.

- 일종의 가상테이블로써, 조인은 테이블간의 관계를 잘 이해해야 짤 수 있지만, 뷰(VIEW)는 잘 몰라도 누구라도 쉽게 같은 결과물을 출력해낼 수 있다.

- 즉, 뷰 자체가 어떤 컬럼(열)이나 데이터를 갖고 있지는 않다.

* 테이블 = 실제 데이터를 가짐 vs 뷰 = 데이터를 갖는 실제 테이블 X (쿼리를 갖고 있다고 해도 무방)

- 조인 쿼리문에서, 복잡한 부분을 빼내어 뷰로 정의해, 새롭게 쿼리를 만들 수 있다.

- DBMS별로 지원 여부가 조금씩 다르다.

(1) 뷰 생성

 CREATE VIEW 뷰이름 -- 테이블같이 사용하면 됨
	AS -- 가독성 있게
    	... -- 복잡한 쿼리문들

(2) 뷰 삭제

 DROP VIEW 뷰이름;
-- 뷰를 업데이트하려면, 뷰를 삭제한 후에 다시 뷰를 생성해야 한다. (갱신 불가능)

(3) 뷰 확인

- SSMS 개체 탐색기에서 '뷰' 메뉴를 클릭해보면 생성된게 보인다.

(4) 뷰 사용

- 그냥 테이블 사용하듯이 사용

(5) 뷰 사용의 주된 이유

- 복잡한 쿼리문을, 단순화시킬 수 있어 자주 사용됨. ex. 조인 자체가 없는 것처럼 보임

- 복잡한 쿼리문을, 손쉽게 재사용할 수 있어 자주 사용됨.

- 초보자도 만들어진 뷰만 잘 사용하면, 굳이 어렵고 복잡한 쿼리를 짤 필요가 없음.

- 복잡한 쿼리란 '조인'에서 많이 볼 수 있다.

(6) Ctrl + Shift + R

- SSMS 쿼리창에 자꾸 빨간 밑줄이 생길 경우.

: 보통 Intellisense Cache Referesh가 갱신되지 않아서 그런 경우가 있다.

: 쿼리 창에 실행하는 쿼리문만 단독으로 써놓아본다. (나머지는 주석 처리)

=> 일종의 버그

 ---------------------------------------------------------------------
-- 뷰
---------------------------------------------------------------------
-- 하나 이상의 테이블에 들어있는 데이터를 가상 테이블로 엮는다.
-- 여러개의 테이블을 묶어서 뷰를 만드는 경우, '복합뷰'라고도 부른다.
-- CREATE VIEW문을 사용하는 경우 이만 있어야 하고, 일괄 처리시 보통 쿼리물 첫줄에 위치한다. ('잘못된 구문'이라는 오류 발생)
-- 그렇지만, 블록을 지정해서 실행하는 것은 가능하다.
-- 뷰는 주로 읽기 전용으로 활용된다.
 
SELECT * FROM dbo.tbl_members;
 
---------------------------------------------------------------------
-- [1] VIEW 생성 및 삭제 그리고 사용 : 기본
---------------------------------------------------------------------
-- 뷰 생성
CREATE VIEW MembersSelectAll  
	AS
		-- 뷰로 묶을 쿼리문 작성
		SELECT * FROM dbo.tbl_members
 
-- 뷰 삭제
DROP VIEW MembersSelectAll;
 
-- 뷰 사용 (마치 테이블을 사용하듯이 사용하면 된다)
SELECT * FROM MembersSelectAll
 
 
 
---------------------------------------------------------------------
-- [2] VIEW 생성 및 삭제 그리고 사용 : 재활용 낮음
---------------------------------------------------------------------
-- 뷰 생성
CREATE VIEW MembersSales1Sales2
	AS
		-- 뷰로 묶을 쿼리문 작성
		-- 뷰는 쿼리로 작성하는 가상 테이블이다.
		-- 즉 이 아래의 쿼리의 결과를, 가상 테이블로 만들어주는 것일 뿐이다.
		SELECT M.m_id, m_name, m_address, m_tel, m_email
			FROM
				dbo.tbl_members M, dbo.tbl_sales1 S1, dbo.tbl_sales2 S2
			WHERE 
				S1.m_id = M.m_id
			AND	
				S2.s2_num = S1.s1_num
			AND
				S2.p_id = 'GS103'
 
 
-- 뷰 삭제
DROP VIEW dbo.MembersSales1Sales2
 
-- 뷰 사용
SELECT m_id, m_name 
	FROM dbo.MembersSales1Sales2
 
 
---------------------------------------------------------------------
-- [3] VIEW 생성 및 삭제 그리고 사용 : 재활용 높음
---------------------------------------------------------------------
-- 뷰 생성
CREATE VIEW MS1S2
	AS
		-- 쿼리문 작성(뷰)
		SELECT M.m_id, m_name, m_address,m_tel,m_email	
			FROM dbo.tbl_members M, dbo.tbl_sales1 S1, dbo.tbl_sales2 S2
			WHERE S1.m_id = M.m_id
			AND	S1.s1_num = S2.s2_num 
 
-- 뷰 삭제
DROP VIEW MS1S2
 
-- 뷰 사용
SELECT m_id, m_name, m_address, m_tel, m_email
	FROM 
		dbo.MS1S2
	WHERE
		p_id = 'GS103' 
		-- 오류 발생. (MS1S2의 뷰에는 p_id라는 컬럼이 존재하지 않기 때문) 
		-- 뷰를 만들 때, p_id 컬럼도 추가해서 만들어줬다면 뷰 사용 가능

4) System view

 ---------------------------------------------------------------------
-- VIEW 활용 : 시스템뷰
---------------------------------------------------------------------
-- 뷰 확인
-- 뷰를 만들면, 뷰의 이름이 sysobjects 테이블에 저장된다.
-- 뷰 열에 관한 정보는, syscolumns 테이블에 추가된다.
-- 뷰 종속관계에 대한 정보는, sysdepends 테이블에 추가된다.
-- CREATE VIEW문의 텍스트는, syscomments 테이블에 추가
 
SELECT * FROM sysobjects; -- sys.objects는 시스템 뷰(System Views)에 존재함.
 
-- 우리가 만든 테이블, MS1S2가 존재하는지 확인해보자
SELECT * 
	FROM sysobjects
	WHERE name = 'MS1S2'
 
-- 시스템 뷰 활용
-- 이러한 뷰를 활용하면 특정 객체를 찾는데 유용하다.
-- Q. 특정 컬럼이 포함된 테이블을 찾고 싶다면 어떻게 할까?
SELECT *
	FROM
		INFORMATION_SCHEMA.COLUMNS
	WHERE
		-- COLUMN_NAME = 's2_num'
		COLUMN_NAME LIKE'%_num%'

- 뷰를 만들면, 뷰의 이름이 sysobjects 테이블에 저장된다.

- 뷰 열에 관한 정보는 syscolumns 테이블에 추가

- 뷰 종속 관계에 관한 정보는, sysdepends 테이블에 추가

- CREATE VIEW 문의 텍스트는 syscomments 테이블에 추가

* 즉 뷰를 만들면, 그와 관련된 정보들이 저장이 되어지는 곳임. (데이터베이스 엔진에서 제공해주는 것)

// 특정 컬럼이 있는지 없는지 검색 가능. (해당 관련 컬럼을 새로 만들까 말까 고민할 때, 검색으로 확인하기 좋음)

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

'문과 코린이의, [C#] 기록 > 문과 코린이의, [SQL] 기록' 카테고리의 다른 글

[문과 코린이의 IT 기록장] RDBMS Modeling 기초 (RDBMS 개념, 테이블 만들기 - 학생 수강신청 관리 (문제), Key, Data Type, Primary Key(PK) 설계시 고려사항) (0)	2022.08.10
[문과 코린이의 IT 기록장] MSSQL - 데이터 베이스 활용 (백업과 복원, 백업시 초보자 주의사항, MDF, LDF 파일로 복원하기, MDF, LDF 관계 이해, 트랜잭션) (0)	2022.02.22
[문과 코린이의 IT 기록장] MSSQL - 데이터 베이스 활용 (날짜 관련해서 많이 사용하는 업무 처리 문제들, 최종 실습 테이블, 숫자 관련 데이터 타입) (0)	2022.02.16
[문과 코린이의 IT 기록장] MSSQL - 데이터 베이스 활용( 한글 영문 우선순위, 기본키(PK) / 유니크(UQ) 제약조건 추가/삭제, 기본키명 직접 지정 방법) (0)	2022.01.28
[문과 코린이의 IT 기록장] MSSQL - 데이터 베이스 활용 (테이블 스크립트, 2개 이상의 컬럼을, 기본키로 만들기, CLUSTERED VS NONCLUSTERED, DB내 인덱스 확인, CLUSTERED INDEX / NONCLUSTERED INDEX 실습) (0)	2022.01.27

내 블로그 - 관리자 홈 전환	`Q` `Q`
새 글 쓰기	`W` `W`

글 수정 (권한 있는 경우)	`E` `E`
댓글 영역으로 이동	`C` `C`

이 페이지의 URL 복사	`S` `S`
맨 위로 이동	`T` `T`
티스토리 홈 이동	`H` `H`
단축키 안내	`Shift` + `/` `⇧` + `/`

벼리네 it 기록장

[문과 코린이의 IT 기록장] MSSQL - 데이터 베이스 활용(최종 실습 스크립트 작성하기, 참조 무결성, INSERT for 조인, 뷰 실습)

1. 최종 실습 스크립트 작성하기

2. 참조 무결성

3. INSERT for 조인, 뷰 실습

1) CRUD

2) JOIN

3) VIEW

4) System view

'문과 코린이의, [C#] 기록 > 문과 코린이의, [SQL] 기록' 카테고리의 다른 글

티스토리툴바

단축키

내 블로그

블로그 게시글

모든 영역

	CREATE DATABASE [7TO7] -- CREATE DATABASE

	USE [7TO7] -- CHANGE DATABASE

	-- CREATE TABLE
	----------------------------------------------------------------------
	-- [ 1 ] dbo.tbl_members : 회원 테이블
	----------------------------------------------------------------------
	CREATE TABLE dbo.tbl_members
	(
	m_id CHAR(16) NOT NULL PRIMARY KEY, -- 누가 주문했는지 ID
	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,
	);


	----------------------------------------------------------------------
	-- [ 2 ] dbo.tbl_sales1 : 매출(주문) 테이블 1
	----------------------------------------------------------------------
	CREATE TABLE dbo.tbl_sales1
	(
	s1_num INT NOT NULL PRIMARY KEY, -- 주문번호
	s2_date DATETIME NOT NULL, -- 날짜(0000-00-00 00:00:00)
	m_id CHAR(16) NOT NULL, -- 누가 주문했는지 ID
	);

	----------------------------------------------------------------------
	-- [ 3 ] dbo.tbl_sales2 : 매출(주문) 테이블 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


	PRIMARY KEY CLUSTERED -- 두개의 컬럼을 하나의 기본키로 만들어놓은 것.
	(
	s2_num, s2_ordertem
	)
	);


	----------------------------------------------------------------------
	-- [ 4 ] dbo.tbl_product : 상품 테이블
	----------------------------------------------------------------------
	CREATE TABLE dbo.tbl_products
	(
	p_id NVARCHAR(50) NOT NULL PRIMARY KEY,
	p_name NVARCHAR(300) NOT NULL,
	p_price MONEY NOT NULL,
	p_detail NVARCHAR(1000) NULL, -- 상품 상세 설명
	v_id NVARCHAR(25) NOT NULL -- 공급사 ID(회사명)
	);



	----------------------------------------------------------------------
	-- [ 5 ] dbo.tbl_vendors : 벤더사 테이블
	----------------------------------------------------------------------
	CREATE TABLE dbo.tbl_vendors
	(
	v_id NVARCHAR(25) NOT NULL PRIMARY KEY
	);





	-- ALTER TABLE

	----------------------------------------------------------------------
	-- 외래키(FOREIGN KEY) 정의 [1]
	-- dbo.tbl_members테이블의 m_id 기본키를, dbo.tbl_sales1의 m_id를 외래키로 연결하기
	----------------------------------------------------------------------
	ALTER TABLE dbo.tbl_sales1
	ADD -- 삭제는 drop
	CONSTRAINT FK_sales1_members -- 외래키 이름 : FK_지정테이블명_참조테이블명
	FOREIGN KEY (m_id)
	REFERENCES tbl_members(m_id); -- 기본키가 있는 테이블 (기본키)


	EXEC sp_helpconstraint tbl_sales1



	----------------------------------------------------------------------
	-- 외래키(FOREIGN KEY) 정의 [2]
	----------------------------------------------------------------------
	ALTER TABLE dbo.tbl_sales2
	ADD
	CONSTRAINT FK_tbl_sales2_sales1
	FOREIGN KEY(s2_num) -- 기본키는 외래키가 될 수 있음.
	REFERENCES tbl_sales1(s1_num), -- 다음 외래키 지정시 콤마(,)를 적어줘야 함.
	CONSTRAINT FK_tbl_sales2_product
	FOREIGN KEY(p_id)
	REFERENCES tbl_products(p_id); -- 마지막은 세미콜론(;) 작성 필요.


	----------------------------------------------------------------------
	-- 외래키(FOREIGN KEY) 정의 [3]
	----------------------------------------------------------------------
	ALTER TABLE dbo.tbl_products
	ADD
	CONSTRAINT FK_tbl_products_vendors
	FOREIGN KEY(v_id)
	REFERENCES tbl_vendors(v_id);

	----------------------------------------------------------------------
	-- 참조 무결성 확인
	-- INSERT DATA
	----------------------------------------------------------------------
	-- 테이블과 테이블간 관계를 맺게 되면(외래키지정), 참조하는 테이블열의 값이 아닌 다른 값이 들어올 수 없다.
	-- 또한 입력된 값도, 참조하는 테이블열이 아닌 다른 값으로 수정 및 변경할 수 없다.
	-- 참조 무결성에 위배되기 때문에 이러한 수정 및 입력이 되지 않는 것이다.
	-- RDBMS가 일관성있게 데이터가 유지될 수 있도록 관리한다.


	-- [1] dbo.tbl_members
	SELECT * FROM dbo.tbl_members

	/* 기본 입력 */
	-- 회원테이블에 입력
	INSERT INTO dbo.tbl_members VALUES('antman','앤트맨','서울','America','010-2134-5678','antman@naver.com');
	INSERT INTO dbo.tbl_members VALUES('batman','베트맨','서울','America','010-2134-5338','batman@naver.com');
	-- 매출테이블에 입력
	INSERT INTO dbo.tbl_sales1 VALUES(1001,'2000-12-25 15:22:10','batman'); -- m_id로 회원-매출 테이블 기본키-외래키 연결되어있음. 즉, 이를 추가하려면 회원테이블에 해당 m_id가 존재해야 함.
	INSERT INTO dbo.tbl_sales1 VALUES(1002,'2000-12-25 15:23:10', 'antman');
	INSERT INTO dbo.tbl_sales1 VALUES(1003,'2000-12-25 15:23:10', 'superman'); -- 해당 m_id가 회원 테이블에 존재하지 않으므로, 에러 발생

	/* 수정,변경 */
	UPDATE dbo.tbl_sales1
	SET m_id = 'superman'
	WHERE s1_num = '1001'
	-- 오류 발생 : sales1테이블 외래키인 m_id와 연결된 기본키는, 회원 테이블의 m_id이며, 이 테이블 내에 해당 값이 존재하지 않으므로 오류가 발생한다.
	UPDATE dbo.tbl_sales1
	SET m_id = 'batman'
	WHERE s1_num = '1001'
	-- 수정,변경 가능 O

	----------------------------------------------------------------------
	-- JOIN
	----------------------------------------------------------------------

	SELECT m_name, m_address, m_email
	FROM dbo.tbl_members, dbo.tbl_sales1
	WHERE dbo.tbl_members.m_id = dbo.tbl_sales1.m_id

	SELECT M.m_id,m_name, m_address, m_email
	FROM dbo.tbl_members AS M, dbo.tbl_sales1 AS S1
	WHERE M.m_id = S1.m_id

	---------------------------------------------------------------------
	-- 3개 이상 테이블 join
	---------------------------------------------------------------------
	-- Q. 김치냉장고(GS103)를 구매한 고객의 아이디와 이름을 알고 싶다면?

	SELECT * FROM dbo.tbl_members -- 고객의 아이디와 이름
	SELECT * FROM dbo.tbl_sales1 -- 구매한 고객의 id파악
	SELECT * FROM dbo.tbl_sales2 -- 김치냉장고(GS103)를 구매한 고객

	SELECT Mem.m_id, m_name
	FROM dbo.tbl_members AS Mem, dbo.tbl_sales1 AS S1, dbo.tbl_sales2 AS S2
	WHERE Mem.m_id = S1.m_id AND S1.s1_num = S2.s2_num AND S2.p_id='GS103'

	---------------------------------------------------------------------
	-- 서브쿼리 (보기에 조금 더 직관적임 - 성능은 상대적으로 떨어질 수도 있음)
	---------------------------------------------------------------------
	SELECT M.m_id,m_name,m_address,m_tel
	FROM dbo.tbl_members M, dbo.tbl_sales1 S1, dbo.tbl_sales2 S2
	WHERE
	M.m_id
	IN(
	SELECT S1.m_id
	FROM dbo.tbl_sales1 S1
	WHERE S1.s1_num -- s1_num = 아래의 연산자 결과들과 비교
	IN( -- 다중 결과가 나올 경우에는 IN연산자 사용
	SELECT S2.s2_num
	FROM dbo.tbl_sales2 S2
	WHERE S2.p_id = 'GS103'
	)
	);

	CREATE VIEW 뷰이름 -- 테이블같이 사용하면 됨
	AS -- 가독성 있게
	... -- 복잡한 쿼리문들

	DROP VIEW 뷰이름;
	-- 뷰를 업데이트하려면, 뷰를 삭제한 후에 다시 뷰를 생성해야 한다. (갱신 불가능)

	---------------------------------------------------------------------
	-- 뷰
	---------------------------------------------------------------------
	-- 하나 이상의 테이블에 들어있는 데이터를 가상 테이블로 엮는다.
	-- 여러개의 테이블을 묶어서 뷰를 만드는 경우, '복합뷰'라고도 부른다.
	-- CREATE VIEW문을 사용하는 경우 이만 있어야 하고, 일괄 처리시 보통 쿼리물 첫줄에 위치한다. ('잘못된 구문'이라는 오류 발생)
	-- 그렇지만, 블록을 지정해서 실행하는 것은 가능하다.
	-- 뷰는 주로 읽기 전용으로 활용된다.

	SELECT * FROM dbo.tbl_members;

	---------------------------------------------------------------------
	-- [1] VIEW 생성 및 삭제 그리고 사용 : 기본
	---------------------------------------------------------------------
	-- 뷰 생성
	CREATE VIEW MembersSelectAll
	AS
	-- 뷰로 묶을 쿼리문 작성
	SELECT * FROM dbo.tbl_members

	-- 뷰 삭제
	DROP VIEW MembersSelectAll;

	-- 뷰 사용 (마치 테이블을 사용하듯이 사용하면 된다)
	SELECT * FROM MembersSelectAll



	---------------------------------------------------------------------
	-- [2] VIEW 생성 및 삭제 그리고 사용 : 재활용 낮음
	---------------------------------------------------------------------
	-- 뷰 생성
	CREATE VIEW MembersSales1Sales2
	AS
	-- 뷰로 묶을 쿼리문 작성
	-- 뷰는 쿼리로 작성하는 가상 테이블이다.
	-- 즉 이 아래의 쿼리의 결과를, 가상 테이블로 만들어주는 것일 뿐이다.
	SELECT M.m_id, m_name, m_address, m_tel, m_email
	FROM
	dbo.tbl_members M, dbo.tbl_sales1 S1, dbo.tbl_sales2 S2
	WHERE
	S1.m_id = M.m_id
	AND
	S2.s2_num = S1.s1_num
	AND
	S2.p_id = 'GS103'


	-- 뷰 삭제
	DROP VIEW dbo.MembersSales1Sales2

	-- 뷰 사용
	SELECT m_id, m_name
	FROM dbo.MembersSales1Sales2


	---------------------------------------------------------------------
	-- [3] VIEW 생성 및 삭제 그리고 사용 : 재활용 높음
	---------------------------------------------------------------------
	-- 뷰 생성
	CREATE VIEW MS1S2
	AS
	-- 쿼리문 작성(뷰)
	SELECT M.m_id, m_name, m_address,m_tel,m_email
	FROM dbo.tbl_members M, dbo.tbl_sales1 S1, dbo.tbl_sales2 S2
	WHERE S1.m_id = M.m_id
	AND S1.s1_num = S2.s2_num

	-- 뷰 삭제
	DROP VIEW MS1S2

	-- 뷰 사용
	SELECT m_id, m_name, m_address, m_tel, m_email
	FROM
	dbo.MS1S2
	WHERE
	p_id = 'GS103'
	-- 오류 발생. (MS1S2의 뷰에는 p_id라는 컬럼이 존재하지 않기 때문)
	-- 뷰를 만들 때, p_id 컬럼도 추가해서 만들어줬다면 뷰 사용 가능

	---------------------------------------------------------------------
	-- VIEW 활용 : 시스템뷰
	---------------------------------------------------------------------
	-- 뷰 확인
	-- 뷰를 만들면, 뷰의 이름이 sysobjects 테이블에 저장된다.
	-- 뷰 열에 관한 정보는, syscolumns 테이블에 추가된다.
	-- 뷰 종속관계에 대한 정보는, sysdepends 테이블에 추가된다.
	-- CREATE VIEW문의 텍스트는, syscomments 테이블에 추가

	SELECT * FROM sysobjects; -- sys.objects는 시스템 뷰(System Views)에 존재함.

	-- 우리가 만든 테이블, MS1S2가 존재하는지 확인해보자
	SELECT *
	FROM sysobjects
	WHERE name = 'MS1S2'

	-- 시스템 뷰 활용
	-- 이러한 뷰를 활용하면 특정 객체를 찾는데 유용하다.
	-- Q. 특정 컬럼이 포함된 테이블을 찾고 싶다면 어떻게 할까?
	SELECT *
	FROM
	INFORMATION_SCHEMA.COLUMNS
	WHERE
	-- COLUMN_NAME = 's2_num'
	COLUMN_NAME LIKE'%_num%'

[문과 코린이의 IT 기록장] MSSQL - 데이터 베이스 활용(최종 실습 스크립트 작성하기, 참조 무결성, INSERT for 조인, 뷰 실습)

1. 최종 실습 스크립트 작성하기

2. 참조 무결성

3. INSERT for 조인, 뷰 실습

1) CRUD

2) JOIN

3) VIEW

4) System view

'문과 코린이의, [C#] 기록 > 문과 코린이의, [SQL] 기록' 카테고리의 다른 글

'문과 코린이의, [C#] 기록/문과 코린이의, [SQL] 기록' Related Articles

티스토리툴바

단축키

내 블로그

블로그 게시글

모든 영역