mssql -- View 생성, 삭제, CRUD

mssql -- View 생성, 삭제, CRUD

참고 : https://msdn.microsoft.com/ko-kr/library/ms187956(v=sql.120).aspx

          https://msdn.microsoft.com/en-us/library/ms173492.aspx

          http://www.w3schools.com/sql/sql_view.asp

          http://www.tutorialspoint.com/sql/sql-using-views.htm

          https://msdn.microsoft.com/ko-kr/library/ms190328(v=sql.110).aspx

          https://msdn.microsoft.com/ko-kr/library/ms190324(v=sql.110).aspx

1. view 만들기

CREATE VIEW view_name 

AS

SELECT column_name(s)

FROM table_name

WHERE condition

2. view 삭제하기

DROP VIEW view_name

3. view 에서 CRUD 실습하기

CREATE TABLE students

( id int primary key identity(1,1),

  name varchar(50) NOT NULL,

  grade char(1),

  eng int,

  math int

)

INSERT INTO students (name, grade, eng, math) VALUES ('Tom','1', 90, 95)

-- sql server 2008 부터, 여러 행 한꺼번에 입력 가능...

INSERT INTO students (name, grade, eng, math) 

VALUES ('Jon','2', 92, 55), ('Ace','1', 70, 85), ('Jane','1', 96, 97), ('Kim','2', 80, 85)

INSERT INTO students (name, grade, eng, math) 

VALUES ('Jon','2', 92, 55)

INSERT INTO students (name, grade, eng, math) 

VALUES ('Ace','1', 70, 85)

INSERT INTO students (name, grade, eng, math) 

VALUES ('Jane','1', 96, 97)

INSERT INTO students (name, grade, eng, math) 

VALUES ('Kim','2', 80, 85)

select * from students

go

-- 같은 이름의 view 가 존재하면, 삭제하라.

if object_id('vw_pota', 'V') is not null

drop view vw_pota ;

go

-- view 생성

create view vw_grade_one

as

select * from students

where grade = '1'

go

select * from vw_grade_one

go

-- 원본 테이블 내용 바꾸면, view 에서도 바뀐다.

update students set eng = 100

where name='Tom'

go

update vw_grade_one set math = 100

where name='Tom'

go

-- view 에서 바뀌면, 원본 table 내용도 바뀜.

delete vw_grade_one 

where name='Tom'

go

delete students

where name='Ace'

go

select * from vw_grade_one

go

select * from students

go

-- view 삭제

drop view vw_grade_one

go

4. view 변경 제한하기

-- with check option 으로 view 수정에 제약을 걸수 있다. 

    참고 :  http://zarez.net/?p=3002  

              http://www.codeproject.com/Articles/266985/Views-In-SQL-Server