創(chuàng)建簡(jiǎn)單視圖:

use test go create view v1(視圖名) as select name from b

這樣視圖就創(chuàng)建好了.

下面說(shuō)下視圖的本質(zhì):

當(dāng)執(zhí)行? select * from v1 時(shí),? 那么實(shí)質(zhì)上市告訴 sql server 把執(zhí)行 select name from b 語(yǔ)句返回的結(jié)果

給我.

視圖就像在命令執(zhí)行的查詢那樣運(yùn)行---沒(méi)有任何的預(yù)先優(yōu)化過(guò)程.這意味著數(shù)據(jù)在請(qǐng)求和所交付的數(shù)據(jù)之間增加額額外

的一層系統(tǒng)開(kāi)銷,而視圖的運(yùn)行總是比 執(zhí)行運(yùn)行內(nèi)部的sql 更慢.視圖存在的理由就是---對(duì)于用戶是安全或者簡(jiǎn)化的.

• • 視圖可以隱藏敏感數(shù)據(jù),?
  • 作為過(guò)濾器?? 例子如下: use test go create view v2 as select name from b where id=2

.csharpcode, .csharpcode pre { font-size: small; color: rgba(0, 0, 0, 1); font-family: consolas, "Courier New", courier, monospace; background-color: rgba(255, 255, 255, 1) } .csharpcode pre { margin: 0 } .csharpcode .rem { color: rgba(0, 128, 0, 1) } .csharpcode .kwrd { color: rgba(0, 0, 255, 1) } .csharpcode .str { color: rgba(0, 96, 128, 1) } .csharpcode .op { color: rgba(0, 0, 192, 1) } .csharpcode .preproc { color: rgba(204, 102, 51, 1) } .csharpcode .asp { background-color: rgba(255, 255, 0, 1) } .csharpcode .html { color: rgba(128, 0, 0, 1) } .csharpcode .attr { color: rgba(255, 0, 0, 1) } .csharpcode .alt { background-color: rgba(244, 244, 244, 1); width: 100%; margin: 0 } .csharpcode .lnum { color: rgba(96, 96, 96, 1) }

總結(jié):視圖實(shí)際上 只是一個(gè)用戶不可見(jiàn)的 select語(yǔ)句.

更加復(fù)雜的視圖:

對(duì)視圖進(jìn)行 insert update delete

使用 with check potion 限制插入到視圖中的內(nèi)容。-- 為了通過(guò)使用視圖更新或者插入數(shù)據(jù),結(jié)果行必須符合要求

以顯示在視圖結(jié)果中.? 也就是插入或者更新的行 必須滿足視圖中select語(yǔ)句的where條件,

看創(chuàng)建一個(gè)視圖,sql:

create view v4 as select name from a where name like 'a%' with check option .csharpcode, .csharpcode pre { font-size: small; color: rgba(0, 0, 0, 1); font-family: consolas, "Courier New", courier, monospace; background-color: rgba(255, 255, 255, 1) } .csharpcode pre { margin: 0 } .csharpcode .rem { color: rgba(0, 128, 0, 1) } .csharpcode .kwrd { color: rgba(0, 0, 255, 1) } .csharpcode .str { color: rgba(0, 96, 128, 1) } .csharpcode .op { color: rgba(0, 0, 192, 1) } .csharpcode .preproc { color: rgba(204, 102, 51, 1) } .csharpcode .asp { background-color: rgba(255, 255, 0, 1) } .csharpcode .html { color: rgba(128, 0, 0, 1) } .csharpcode .attr { color: rgba(255, 0, 0, 1) } .csharpcode .alt { background-color: rgba(244, 244, 244, 1); width: 100%; margin: 0 } .csharpcode .lnum { color: rgba(96, 96, 96, 1) }

那么現(xiàn)在 向視圖插入一條數(shù)據(jù):

insert into v4(name) values('ss') .csharpcode, .csharpcode pre { font-size: small; color: rgba(0, 0, 0, 1); font-family: consolas, "Courier New", courier, monospace; background-color: rgba(255, 255, 255, 1) } .csharpcode pre { margin: 0 } .csharpcode .rem { color: rgba(0, 128, 0, 1) } .csharpcode .kwrd { color: rgba(0, 0, 255, 1) } .csharpcode .str { color: rgba(0, 96, 128, 1) } .csharpcode .op { color: rgba(0, 0, 192, 1) } .csharpcode .preproc { color: rgba(204, 102, 51, 1) } .csharpcode .asp { background-color: rgba(255, 255, 0, 1) } .csharpcode .html { color: rgba(128, 0, 0, 1) } .csharpcode .attr { color: rgba(255, 0, 0, 1) } .csharpcode .alt { background-color: rgba(244, 244, 244, 1); width: 100%; margin: 0 } .csharpcode .lnum { color: rgba(96, 96, 96, 1) }

那么sql 會(huì)報(bào)錯(cuò):

因?yàn)?子句過(guò)濾了? a% 內(nèi)容,而 插入的ss不符合 這個(gè)過(guò)濾的條件,所以會(huì)報(bào)錯(cuò).

如果想知道某一個(gè)視圖的作用,那么可以用如下方法:

1.

use test go exec sp_helptext v4(視圖名) 返回如下信息: 2. select * from sys.sql_modules where [object_id]= object_id('v4') .csharpcode, .csharpcode pre { font-size: small; color: rgba(0, 0, 0, 1); font-family: consolas, "Courier New", courier, monospace; background-color: rgba(255, 255, 255, 1) } .csharpcode pre { margin: 0 } .csharpcode .rem { color: rgba(0, 128, 0, 1) } .csharpcode .kwrd { color: rgba(0, 0, 255, 1) } .csharpcode .str { color: rgba(0, 96, 128, 1) } .csharpcode .op { color: rgba(0, 0, 192, 1) } .csharpcode .preproc { color: rgba(204, 102, 51, 1) } .csharpcode .asp { background-color: rgba(255, 255, 0, 1) } .csharpcode .html { color: rgba(128, 0, 0, 1) } .csharpcode .attr { color: rgba(255, 0, 0, 1) } .csharpcode .alt { background-color: rgba(244, 244, 244, 1); width: 100%; margin: 0 } .csharpcode .lnum { color: rgba(96, 96, 96, 1) }

結(jié)果如下:

加密視圖:

新建一個(gè)加密的視圖:

create view v5 with encryption as select name from a where name like 'a%' with check option 在運(yùn)行 select * from sys.sql_modules where [object_id]= object_id('v5') .csharpcode, .csharpcode pre { font-size: small; color: rgba(0, 0, 0, 1); font-family: consolas, "Courier New", courier, monospace; background-color: rgba(255, 255, 255, 1) } .csharpcode pre { margin: 0 } .csharpcode .rem { color: rgba(0, 128, 0, 1) } .csharpcode .kwrd { color: rgba(0, 0, 255, 1) } .csharpcode .str { color: rgba(0, 96, 128, 1) } .csharpcode .op { color: rgba(0, 0, 192, 1) } .csharpcode .preproc { color: rgba(204, 102, 51, 1) } .csharpcode .asp { background-color: rgba(255, 255, 0, 1) } .csharpcode .html { color: rgba(128, 0, 0, 1) } .csharpcode .attr { color: rgba(255, 0, 0, 1) } .csharpcode .alt { background-color: rgba(244, 244, 244, 1); width: 100%; margin: 0 } .csharpcode .lnum { color: rgba(96, 96, 96, 1) } 查看視圖時(shí): .csharpcode, .csharpcode pre { font-size: small; color: rgba(0, 0, 0, 1); font-family: consolas, "Courier New", courier, monospace; background-color: rgba(255, 255, 255, 1) } .csharpcode pre { margin: 0 } .csharpcode .rem { color: rgba(0, 128, 0, 1) } .csharpcode .kwrd { color: rgba(0, 0, 255, 1) } .csharpcode .str { color: rgba(0, 96, 128, 1) } .csharpcode .op { color: rgba(0, 0, 192, 1) } .csharpcode .preproc { color: rgba(204, 102, 51, 1) } .csharpcode .asp { background-color: rgba(255, 255, 0, 1) } .csharpcode .html { color: rgba(128, 0, 0, 1) } .csharpcode .attr { color: rgba(255, 0, 0, 1) } .csharpcode .alt { background-color: rgba(244, 244, 244, 1); width: 100%; margin: 0 } .csharpcode .lnum { color: rgba(96, 96, 96, 1) }

definition變成了空值,無(wú)法看到視圖到底做了些什么.

如果對(duì)視圖使用了alter 命令,那么 如果不使用加密,那么 修改后的視圖,將變成非加密的.

總結(jié)

以上是生活随笔為你收集整理的sql server 2008学习9 视图的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。