transact---sql高級查詢（下）
5:使用having關鍵字來篩選結果
6:使用compute和compute by子句
7:使用嵌套查詢
8:分布式查詢
E:使用having關鍵字來篩選結果
當完成對數據結果的查詢和統計后,可以使用having關鍵字來對查詢和計算的結果進行一步的篩選
例:檢索出work表中學歷是大專或者是中專的人數
select 學歷,count(學歷) from work group by 學歷 having 學歷 in("'大專"',"'中專"')
說明:1:having關鍵字都與group by用在一起.
2:having不支持對列分配的別名
例如:select 學歷,"'大于5的人數"'=count(學歷) from work group by 學歷 having 大于5的人數>5 [錯錯]
改為:select 學歷,"'大于5的人數"'=count(學歷) from work group by 學歷 having count(學歷)>5
F:使用compute和compute by
使用compute子句允許同時觀察查詢所得到各列的數據的細節以及統計各列數據所產生的匯總列
select * from work [查詢所得到的各列的數據的細節]
compute max(基本工資),min(基本工資) [統計之后的結果]
這個例子中沒有使用by關鍵字,返回的結果是最后添加了一行基本工資的最大值和最小值,也可增加by關鍵字.
例:select * from work order by 學歷
compute max(基本工資),min(基本工資) by 學歷
比較:select 學歷,max(基本工資),min(基本工資) from work group by 學歷
說明:1:compute子句必須與order by子句用在一起
2:compute子句可以返回多種結果集.一種是體現數據細節的數據集,可以按分類要求進行正確的分類；另一種在分類的基礎上進行匯總產生結果.
3:而group by子句對每一類數據分類之后只能產生一個結果,不能知道細節
G:使用嵌套查詢
查詢中再查詢,通常是以一個查詢作為條件來供另一個查詢使用
例:有work表和部門表
A:檢索出在部門表中登記的所有部門的職工基本資料
select * from work where 部門編號 in [not in](select 部門編號 from dbo.部門)
B:檢索出在work表中每一個部門的最高基本工資的職工資料
select * from work a where 基本工資=(select max(基本工資) from work b where a.部門名稱=b.部門名稱)
說明:由外查詢提供一個部門名稱給內查詢,內查詢利用這個部門名稱找到該部門的最高基本工資,然后外查詢根據基本工資判斷是否等于最高工資,如果是的,則顯示出來.
相當于:select * from work,(select 部門名稱,max(基本工資) as 基本工資 from work group by 部門名稱 as t) where work.基本工資=t.基本工資 and work.部門名稱=t.部門名稱
C:用嵌套work表和嵌套部門表,在嵌套work表中檢索出姓名和職工號都在嵌套部門存在的職工資料
select * from 嵌套work where 職工號 in (select 職工號 from 嵌套部門) and 姓名 in (select 姓名 from 嵌套部門) [察看結果,分析原因]
改:select * from 嵌套work a,嵌套部門 b where a.職工號=b.職工號 and a.姓名=b.姓名
改:select * from 嵌套work where 職工號=(select 職工號 from 嵌套部門) and 姓名=(select 姓名 from 嵌套部門) [行嗎?為什么,分析原因?]
在嵌套中使用exists關鍵字[存在]
例:1:用嵌套work表和嵌套部門表,在嵌套work表中檢索出姓名和職工號都在嵌套部門存在的職工資料
select * from 嵌套work a where exists (select * from 嵌套部門 b where a.姓名=b.姓名 and a.職工號=b.職工號)
2:在work表檢索出在部門表沒有的職工
select * from work where not exists (select * from 部門 where 部門.部門編號=work.部門編號)
能否改成:select * from work where exists (select * from 部門 where 部門.部門編號<>work.部門編號)
在列清單中使用select
例:1:在work1表和部門表中檢索出所有部門的部門名稱和基本工資總和
select 部門名稱,(select sum(基本工資) from work1 b where a.部門編號=b.部門編號) from 部門 a
2:檢索各部門的職工人數
select 部門編號,部門名稱,(select count(職工號) from work1 a where a.部門編號=b.部門編號) as 人數 from 部門 b
3:在商品表和銷售表中查詢每一職工的姓名,所屬部門,銷售總量
select 姓名,所屬部門,(select sum(銷售量) from 商品銷售 a where a.職工號=b.職工號) as 銷售總量 from 嵌套部門 b
H:分布式查詢
我們以前的查詢都只是基于一個服務器中的一個數據庫的查詢,如果一個查詢是要跨越一個服務器,像這樣的查詢就是分布式查詢,那么我們以看到分布查詢就是數據源自于兩個服務器.要進行分布式查詢必須先創建一個“鏈接服務器”,以便讓本地的用戶能夠映射到過程服務器.
“鏈接服務器”的創立
A:在“鏈接服務器”里面輸入以后為了方便訪問該鏈接服務器的名稱[任意]
B:在“提供程序名稱”里面選擇“Microsoft OLE DB Provider for SQL Server”
C:在“數據源”里面輸入服務器的網絡名
D:本地登錄,遠程用戶和遠程密碼里面分別輸入一個本地登錄用戶,遠程登錄和遠程密碼以便讓本地SQL Server登錄映射為鏈接服務器上的用戶
E:訪問方法:格式:鏈接服務器的名稱.數據庫名.dbo.表名
鏈接服務器有兩個特點:
1:通過鏈接服務器不能刪除鏈接源服務器的任何對像.
2:能過鏈接服務器可以對鏈接源服務器的表進行insert,updae,delete操作.
視圖
1:什么是視圖
2:視圖和查詢的區別
3:視圖的優點
4:如何創建和管理視圖
5:如何通過視圖修改基本表的數據
6:如何通過視圖實現數據的安全性
A:什么是視圖:
視圖(view):從一個或幾個基本表中根據用戶需要而做成一個虛表
1:視圖是虛表,它在存儲時只存儲視圖的定義,而沒有存儲對應的數據
2:視圖只在剛剛打開的一瞬間,通過定義從基表中搜集數據,并展現給用戶
B:視圖與查詢的區別:
視圖和查詢都是用由sql語句組成,這是他們相同的地方,但是視圖和查詢有著本質區別:
它們的區別在于:1:存儲上的區別:視圖存儲為數據庫設計的一部分,而查詢則不是.
2:更新限制的要求不一樣
要注意:因為視圖來自于表,所以通過視圖可以間接對表進行更新,我們也可以通過update語句對表進行更新,但是對視圖和查詢更新限制是不同的,以下我們會知道雖然通過視圖可以間接更新表但是有很多限制.
3:排序結果:通過sql語句,可以對一個表進行排序,而視圖則不行.
比如:創建一個含有order by子句的視圖,看一下可以成功嗎?
C:視圖的優點:
為什么有了表還要引入視圖呢？這是因為視圖具有以下幾個優點:
1:能分割數據,簡化觀點
可以通過select和where來定義視圖,從而可以分割數據基表中某些對于用戶不關心的數據,使用戶把注意力集中到所關心的數據列.進一步簡化瀏覽數據工作.
2:為數據提供一定的邏輯獨立性
如果為某一個基表定義一個視圖,即使以后基本表的內容的發生改變了也不會影響“視圖定義”所得到的數據
3:提供自動的安全保護功能
視圖能像基本表一樣授予或撤消訪問許可權.
4:視圖可以間接對表進行更新,因此視圖的更新就是表的更新
D:視圖的創建和管理
視圖的創建
1:通過sql語句
格式:create view 視圖名 as select 語句
試一試:分別創建關于一個表或多個表的視圖[因為視圖可以來自于多表]
2:通過企業管理器
說明:1:在完成視圖的創立之后,就可以像使用基本表一樣來使用視圖
2:在創建視圖時,并非所有的select子查詢都可用
如:compute和compute by,order by[除非與top一起連用]
3:但在查詢時,依然都可以用在創建時禁用的select子查詢
4:在視圖創建時,必須為沒有標題列指定標題[思考:能否不用select語句來創建一個視圖]
視圖的刪除:
1:通過sql語句:drop view 視圖名
2:通過企業管理器
說明:與刪除表不同的是,刪除視圖后只是刪除了視圖了定義,并沒有刪除表中的數據.[查看相關性]
修改視圖的定義
1:通過企業管理器
2:通過sql語句:
格式:alter view 視圖名 as 新的select語句
瀏覽視圖信息 sp_helptext 視圖名 [查看視圖創建的語句]
E:如何通過視圖修改基本表的數據.
1:在視圖上使用insert語句
通過視圖插入數據與直接在表中插入數據一樣,但視圖畢竟不是基本表.因此在進行數據插入時還是有一定的限制
1:如果視圖上沒有包括基本表中屬性為not null[不能為空]的列,那么插入操作會因為那些列是null值而失敗.
2:如果某些列因為某些規則或約束的限制而不能直接接受從視圖插入的列時,插入會失敗
3:如果在視圖中包含了使用統計函數的結果,或是包含計算列,則插入操作會失敗
4:不能在使用了distinct語句的視圖中插入值
5:不能在使用了group by語句的視圖中插入值
2:使用update更新視圖中的數據
1:更新視圖與更新表格一樣,但是在視圖中使用了多個基本表連接的情況下,每次更新操作只能更新來自基本表的一個數據列
例如:創建以下視圖:create view del as
select 職工號,姓名,部門名稱,負責人 from work1,部門
where work1.部門編號=部門.部門編號
如果再執行下面的語句時:
update del set 職工號="'001"',部門名稱="'wenda"' where 職工號="'01"'[出現錯誤]
只能夠改成:update del set 職工號="'001"' where 職工號="'01"'
update del set 部門名稱="'wenda"' where 職工號="'01"'
2:不能在使用了distinct語句的視圖中更新值
3:不能在使用了group by語句的視圖中更新值
3:使用delete刪除視圖中數據.
通過視圖刪除數據最終體現為從基本表中刪除數據
格式:delete 視圖名 [where 條件]
說明:當視圖由兩個以上的基表構成時,不允許刪除視圖的數據
例如:建一個視圖kk
create view kk as
select 職工號,姓名,性別,部門名稱 from work1,部門 where work1.部門編號=部門.部門編號 [試著去刪除]
使用with check option的視圖
如果不了解視圖定義內容,則常常會發生向視圖中輸入不符合視圖定義的數據的情況.
比如:create view xm as
select * from work where 性別="'男"'
完全可以插入insert xm values("'001"',"'女"',23,"'2400"'....)
盡管從意義上來說是不合理的,但是上述語句是正確的.為了防止這種情況的發生,可以使用with check option子句來對插入的或更改的數據進行限制.
比如:create view xm as
select * from work where 性別="'男"' with check option
使用schemabinding的視圖[使用綁定到構架]
我們知道視圖是依賴于表,如果在一個表中創建一個視圖,今后如果這個表被刪除了,則這個視圖將不可再用了.為了防止用戶刪除一個有視圖在引用的表,可以在創建視圖的時候加上schemabinding關鍵字.
比如:create view 基本工資 with SCHEMABINDING
as select 姓名,性別,基本工資 from dbo.work
說明:1:不能使用“*”來創建此類型的視圖
2:創建此類型的視圖時,一定要加上dbo.表名.
3:如果在某個表中定義了此類視圖,則用戶將不能對表的結構進行修改,否則會刪除這些綁定
4:如果用戶對表的結構進行列改名,則會刪除綁定而且視圖不可用.
5:如果用戶對表的結構進行列的類型或者大小修改,則會刪除綁定但視圖可用,此時用戶可以刪除視圖所引用的表.
使用with encryption對視圖進行加密
為了保護創建視圖定義的原代碼,可以對視圖進行加密.
比如:create view kk with encryption
as select * from work where 職稱="'經理"'
用sp_helptext來查看一下.或用企業管理器查看一下.
說明:如果應用此項用戶將無法設計視圖
F:使用視圖加強數據的安全
一般通過使用視圖共有三種途徑加強數據的安全性
A:對不同用戶授予不同的使用權.
B:通過使用select子句限制用戶對某些底層基表的列的訪問
C:通過使用where子句限制用戶對某些底層基表的行的訪問
對不同用戶授予不同的權限