33.MS_SQL 2008 - 카테고리 ( Category ) 응용(2)

--[4] 수정: Modify / Edit

Update Categories

Set

       CategoryName = '콤퓨타'

Where CategoriesID = 1

 

--[5] 삭제: Delete

Delete Categories Where CategoriesID = 2

 

--[6] 검색: Search / Find  --Like와is Null 사용

Select *From Categories

Where

       CategoryName Like '%퓨%'

       And

       SuperCategory is Null --널(NULL)값비교

 

--[7] 뷰(View) 생성: Select문전용

--[a] 대분류데이터를출력하는구문을줄여서출력

 Select CategoriesID, CategoryName

 From Categories

 Where SuperCategory IS Null

 Go

 

--[b] 위구문을줄여주는뷰(View) 생성

Create View dbo.GetTopCategory

As

       Select CategoriesID, CategoryName

       From Categories

       Where SuperCategory Is Null

Go

 

--[c] 뷰(가상테이블) 사용: 약간줄임

Select *From GetTopCategory

Order By CategoryName Asc

 

--[d] 뷰(가상테이블) 수정: 암호화

sp_helptext GetTopCategory --뷰구문보기

 

--[!] 뷰구문수정: 암호화처리

Alter View dbo.GetTopCategory  --다른사람들이못봄

With Encryption --개체암호화옵션

As

       Select CategoriesID, CategoryName

       From Categories

       Where SuperCategory Is Null

Go

 

sp_helptext GetTopCategory --안보임

 

--[!] 뷰구문수정: 스키마바인딩적용

Alter View dbo.GetTopCategory

With SchemaBinding --Categories 테이블변경불가능(With SchemaBinding)

As

       Select CategoriesID, CategoryName

       From dbo.Categories

       Where SuperCategory Is Null

Go

 

--[!] 뷰에다가직접데이터입력

Insert Into GetTopCategory(CategoryName) Values('가전')

Go

Select *From Categories

Go

 

--[!] 뷰의모든것을변경

Alter View dbo.GetTopCategory

As

       Select *

       From dbo.Categories

       Where SuperCategory Is Null

      

       With Check Option --조건절에해당하는데이터만입력/수정가능

Go

 

--추가후다시실행하면애러

Insert Into GetTopCategory Values('오디오', 5, 2)

Go

--기본

Set Identity_Insert Categories On

 

--애러

Insert Into GetTopCategory Values(7, '오디오', 5, 2)

Go

---

33Categories(2).sql