transact—sql進階查詢（下）

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子句限制使用者對某些底層基表的行的訪問
  對不同使用者授予不同的許可權