[MS-SQL2011강좌] 17. 뷰에 대한 이해 및 생성, 수정, 삭제 by 강동운님

이 내용은 2011년 6월 현재 SQL2011(코드명 Denali) Beta를 기준으로 작성 되었으며 SQL2011(코드명 Denali) 공식버전(RTM) 발표까지 꾸준히 업데이트 예정입니다.
아울러, 현재 Beta버전이지만 강좌를 따라해 보시는데 아무 문제 없으실거에요. 도움 되시길 바랍니다.
==================================================================================================

안녕하세요. 이스트럭(강동운) 입니다.

 

오늘은 뷰에 대해서 알아볼 차례 인데요~!

사실 이번 강좌 부터는.. 사이트 지기인 코난님께서 쓰셨던 강좌를 바탕으로 제가 살짝 손만 봤습니다. ^^

 

원문: http://www.sqler.com/127289

원문: http://www.sqler.com/127293

 

이번에 소개해 드릴 내용은 뷰 입니다.  중요한 DB의 개체 이지요. 뷰는 가상의 테이블입니다.

 

지난 서브쿼리 강좌에서 인라인 뷰라는 예제를 통해 저희는 뷰라는 단어를 이미 한번 쯤은 들어봤습니다.

 

뷰는 - 테이블의 거울이다. 

뷰는 테이블의 거울이지만 거울은 원래 모습을 다양하게 비출 수도 있지요.

뷰는 테이블의 거울이다라는 샘플을 보여 드리지요. ^_^

 

 

USE pubs GO   --konan_view_test1 이라는 뷰를 생성합니다. CREATE VIEW konan_view_test1 AS SELECT * FROM titles GO   --뷰를 조회 합니다. SELECT * FROM konan_view_test1 GO

 

자 데이터를 조회해 보시면? 테이블과 같은 처리가 되지용? ^_^

　

뷰를 다르게 생성해 쓸 수 있습니다. ^_^

 

 

USE pubs GO --konan_view_test2 이라는 뷰를 생성합니다.   CREATE VIEW konan_view_test2 AS SELECT title, type, price FROM titles GO --뷰를 조회 합니다.   SELECT * FROM konan_view_test2 GO

 

이렇게 쓸 수도 있습니다. ^_^

사용자가 볼 경우를 생각해 보세요. 일반 사용자들이 볼 경우

title_id라는 이상한 값들이 필요 할까요?

pubs_id라는 출판사 ID의 고유 값이 의미가 있을까요? 아니지요?

뷰를 이용하면 사용자가 원하는 데이터에만 초점을 맞추게 할 수 있습니다.

　

또 다른 뷰의 샘플을 봐 보실까요?

 

USE pubs GO   --konan_view_test3 이라는 뷰를 생성합니다. CREATE VIEW konan_view_test3 AS SELECT            titles.title ,          authors.au_lname ,          publishers.pub_name FROM              dbo.titles INNER JOIN       dbo.titleauthor  ON titles.title_id = titleauthor.title_id INNER JOIN       dbo.authors      ON titleauthor.au_id = authors.au_id INNER JOIN       dbo.publishers   ON titles.pub_id = publishers.pub_id GO   --뷰를 조회합니다.. SELECT * FROM konan_view_test3 GO

 

상당히 복잡해 보이는 뷰이지요? ^_^

대강 봐도 조인을 3개의 테이블과 하는 군요. 이럴 경우 저렇게 조인구문을 길게~~

쓰는 것 보다는? 

 

뷰로 생성한후 뷰를 가지고 장난을 치는 것이 복잡한 질의를

계속 사용하는 것보다 훨씬 쉬울 겁니다. ^_^

　

 

 

 

이제 뷰를 생성하는 방법에 대해서 알아보도록 하겠습니다.

 

뷰의 생성 구문은 아래와 같습니다.

　

CREATE VIEW [ < database_name > . ] [ < owner > . ] view_name [ ( column [ ,...n ] ) ]
[ WITH < view_attribute > [ ,...n ] ]
AS
select_statement
[ WITH CHECK OPTION ]

< view_attribute > ::=
     { ENCRYPTION | SCHEMABINDING | VIEW_METADATA }

　

CREATE VIEW(Transact-SQL) Denali: http://msdn.microsoft.com/en-us/library/ms187956(v=SQL.110)

 

 

USE pubs GO   CREATE VIEW titles_view AS SELECT title, type, price, pubdate FROM titles GO   --뷰를 조회합니다. SELECT * FROM titles_view GO

 

간단한 뷰의 생성 샘플 입니다.

　

아래의 쿼리를 이용해서 뷰의 생성 구문을 보실 수 있습니다. ^_^

 

 

sp_helptext titles_view

 

자 위처럼 해 보시문???

뷰 생성 구문이 보일 겁니다. ^_^

만약 정보를 숨기기 위한 뷰라면? 조심 하셔야 겠지요? 

보안상의 문제가 있을 뷰를 생성 하실 경우는 

뷰에대한 암호화 옵션을 주셔서 뷰 생성 구문을 숨기실 수 있습니다.

 

 

USE pubs GO   --속성으로 price * royalty * ytd_sales 값을 amt_due --로 줄 수 있다. CREATE VIEW accounts (title, advance, amt_due) WITH ENCRYPTION AS SELECT title, advance, price * royalty * ytd_sales FROM titles WHERE price > 5 GO --뷰를 생성후 암호화된 뷰의 생성구문을 보려 하면? SET language '한국어’ go sp_helptext accounts     개체 'accounts'의 텍스트가 암호화되었습니다.

 

개체 'accounts'의 텍스트가 암호화되었습니다.

라는 메세지와 함께 뷰 생성 구문을 보실 수 없으실 겁니다.

이때는 반드시 생성하신 분이 뷰 생성 스크립트를 백업을 안전한 곳에 해 두셔야 겠지요?

　

다음은 Check 옵션 입니다.

 

 

USE pubs GO   CREATE VIEW CAonly AS SELECT au_lname, au_fname, city, state FROM authors WHERE state = 'CA' GO

 

 

이런 경우를 생각해 봅시다.

위의 뷰는 뷰의 정의대로 state가 'CA'인 사람의 정보만 나타날듯 하지요?

네. 맞습니다. 그런데...

만약 state가 'CA'인 사람의 데이터를 수정해서 'OR'이라는 곳에 거주하게 한다면?

이게 가능할까요???

 

SELECT * FROM CAonly   --잠시후 말할 뷰의 수정이다. --뷰에대한 수정은 분명 가능하다. --여기선 이 WHERE state = 'CA' 로 --'CA'에서 사는 사람만 보는 것인데.. --이에 대해서 수정을 가해 OR로 바꾸는 것이다. --질의가 될지 안될지 먼저 생각해 보자. UPDATE CAonly SET state = 'OR' WHERE au_lname = 'White'

 

 

답을 보셨나요? ^_^ 넵!!!  수정 됩니다.!!!!!

이런 황당할데가!!!!

그럼 어떻게 이런 수정 작업이 불가하게 해야 할까요!!!!

이럴때 사용하는것이 바로 WITH CHECK OPTION 입니다.

 

 

USE pubs --인포메이션 정보 뷰에서 해당 뷰 이름이 존재하면? --지워라 라는 구문 이다. IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'CAonly') DROP VIEW CAonly GO --뷰생성 CREATE VIEW CAonly AS SELECT au_lname, au_fname, city, state FROM authors WHERE state = 'CA' --체크 옵션을 사용한다. WITH CHECK OPTION GO --CA를 OR로 변화 시키려 하면? UPDATE CAonly SET state = 'OR' WHERE au_lname = 'Carson'   Msg 550, Level 16, State 1, Line 2 대상 뷰가 WITH CHECK OPTION을 지정하거나 WITH CHECK OPTION을 지정하는 뷰에 걸쳐 있고 해당 연산의 하나 이상의 결과 행이 CHECK OPTION 제약 조건을 충족하지 않았으므로 삽입 또는 업데이트 시도가 실패했습니다. 문이 종료되었습니다.

 

 

WITH CHECK OPTION이 걸려 있어서 수정이 불가하다는 메세지를 볼 것이다.

　

다른 뷰 생성의 옵션들은 찬찬히 설명을 드리지요. ^_^

　

아울러 뷰에 대한 정보를 보고 싶으시면???

엔터프라이즈 관리자 에서는

DB선택 -> 뷰 선택 -> 해당 뷰 선택후 등록정보 하면?

뷰에대한 정보를 보실 수 있으며...

질의 분석기에서 정보를 보시려면?

 

 

sp_help CAonly

 

이렇게 뷰에 대한 정보를 보실 수 있습니다.

　

다음은 뷰 수정에 대해서 보시지요. ^_^

 

CREATE VIEW All_authors (au_fname, au_lname, address, city, zip) AS SELECT au_fname, au_lname, address, city, zip FROM pubs..authors GO   --Public역할(일종의 사용자 그룹이라고 생각 하세요)에 --뷰를 SELECT할 수 있는 권한 부여 --GRANT는 SELECT, UPDATE등의 권한을 부여하는 명령 입니다. --추후에 배우게 되실 겁니다. ^_^GRANT SELECT ON All_authors TO public GO   --이때 DROP하고 뷰를 재생성 하면?

 

아직 배우진 않았지만, GRANT 권한할당 명령어가 있습니다.

만약 특정 계정에 대해서 GRANT로 조회권한(SELECT)를 허용한뒤에

뷰를 DROP 후 재 생성하면, 특정 계정에 부여했던 조회권한(SELECT)가 사라지게 됩니다.즉 다시 권한을 부여하는 문제가 생깁니다.

 

따라서 이런 문제를 방지하기 위해 ALTER VIEW 명령어로 VIEW를 수정하는 것이 좋습니다.

 

 

ALTER VIEW All_authors (au_fname, au_lname, address, city, zip) AS SELECT au_fname, au_lname, address, city, zip FROM pubs..authors WHERE state = 'UT' GO

 

 

마지막으로 뷰 삭제에 대해서 알아보도록 하겠습니다.

 

DROP VIEW 뷰명

 

감사합니다.

[출처] SQLER.COM http://www.sqler.com/392650

SQL2011(코드명 Denali) 시리즈 강좌 리스트
[SQL2011강좌] 1. 코드명 Denali 설치 방법
[SQL2011강좌] 2. SSMS 접속 및 간단한 쿼리 실행
[SQL2011강좌] 3. SQL Server 의 MDF, LDF 그리고 데이터베이스 생성하기
[SQL2011강좌] 4. DDL 1탄: 테이블 생성
[SQL2011강좌] 5. DDL 2탄: 쿼리를 이용한 테이블 컬럼 추가 및 삭제
[SQL2011강좌] 6. Primary key와 Unique 제약조건
[SQL2011강좌] 7. DML 1탄 INSERT
[SQL2011강좌] 8. DML 1탄 INSERT(identity 속성)
[SQL2011강좌] 9. Sequence
[SQL2011강좌] 10. pubs, northwind Database 예제 설치
[SQL2011강좌] 11. SELECT(단일 테이블)
[SQL2011강좌] 12. SELECT(JOIN)
[SQL2011강좌] 13. UPDATE, DELETE 절
[SQL2011강좌] 14. DISTINCT, UNION, UNION ALL 키워드
[SQL2011강좌] 15. ORDER BY, GROUP BY, HAVING
[SQL2011강좌] 16. SubQuery(서브쿼리)
[SQL2011강좌] 17. 뷰에 대한 이해 및 생성, 수정, 삭제
[SQL2011강좌] 18. 저장 프로시저에 대한 이해, 생성, 수정, 삭제
[SQL2011강좌] 19. 인덱스에 대한 이해
[SQL2011강좌] 20. 클러스터드 인덱스와 넌 클러스터드 인덱스
[SQL2011강좌] 21. 트랜잭션에 대한이해
[SQL2011강좌] 22. 트랜잭션의 격리수준 4가지
[SQL2011강좌] 23. 잠금에 대한 이해
[SQL2011강좌] 24. 백업과 복구에 대한 이해(풀백업, 차등백업, 트랜잭션 로그 백업)
[SQL2011강좌] 25. 백업과 복구 전략
[SQL2011강좌] 26. 커서란?

SQL2011(코드명 Denali) 시리즈 동영상 강좌 리스트
[SQL2011 동영상 강좌] 1. 코드명 Denali 설치 방법
[SQL2011 동영상 강좌] 2. SSMS 접속 및 간단한 쿼리 실행
[SQL2011 동영상 강좌] 3. SQL Server 의 MDF, LDF 그리고 데이터베이스 생성하기
[SQL2011 동영상 강좌] 4. DDL 1탄: 테이블 생성
[SQL2011 동영상 강좌] 5. DDL 2탄: 쿼리를 이용한 테이블 컬럼 추가 및 삭제
[SQL2011 동영상 강좌] 6. Primary key와 Unique 제약조건
[SQL2011 동영상 강좌] 7. DML 1탄 INSERT
[SQL2011 동영상 강좌] 8. DML 1탄 INSERT(identity 속성)
[SQL2011 동영상 강좌] 9. Sequence
[SQL2011 동영상 강좌] 10. pubs, northwind Database 예제 설치
[SQL2011 동영상 강좌] 11. SELECT(단일 테이블)
[SQL2011 동영상 강좌] 12. SELECT(JOIN)
[SQL2011 동영상 강좌] 13. UPDATE, DELETE 절
[SQL2011 동영상 강좌] 14. DISTINCT, UNION, UNION ALL 키워드
[SQL2011 동영상 강좌] 15. ORDER BY, GROUP BY, HAVING
[SQL2011 동영상 강좌] 16. SubQuery(서브쿼리)
[SQL2011 동영상 강좌] 17. 뷰에 대한 이해 및 생성, 수정, 삭제
[SQL2011 동영상 강좌] 18. 저장 프로시저에 대한 이해, 생성, 수정, 삭제
[SQL2011 동영상 강좌] 19. 인덱스에 대한 이해
[SQL2011 동영상 강좌] 20. 클러스터드 인덱스와 넌 클러스터드 인덱스
[SQL2011 동영상 강좌] 21. 트랜잭션에 대한이해
[SQL2011 동영상 강좌] 22. 트랜잭션의 격리수준 4가지
[SQL2011 동영상 강좌] 23. 잠금에 대한 이해
[SQL2011 동영상 강좌] 24. 백업과 복구에 대한 이해(풀백업, 차등백업, 트랜잭션 로그 백업)
[SQL2011 동영상 강좌] 25. 백업과 복구 전략
[SQL2011 동영상 강좌] 26. 커서란?