transact---sql高級查詢（上）
1:多表的查詢和笛卡兒積
2:表格別名的用法
3:使用sql server的統計函數
4:用group by子句實現分組的查詢
A:多表查詢和笛爾兒積
到目前為止,我們所用的都是單個表查詢,但是在更多的情況的下,需要對多個表進行同時查詢,這時可以把多個表的名字全部填寫在from子句中.
? 比如:查詢出每個職工的姓名,學歷,所在部門名稱.由于我們需要的結果來自于兩個表,所以必須用多表查詢
?????? select 姓名,學歷,部門名稱,負責人 from work,部門 [分析為什么是錯誤的]
? 原因:問題出在對表格連接條件的限制上.在上面的語句中,沒能對表格連接條件作任何限制,所以sql會在work表中每取出一條記錄,就與部門表中的所有記錄組合一次,那么假設work表有m條記錄,而部門表中有n條記錄,則得出的結果為m*n條記錄這就是笛爾兒積,所以笛爾兒積返回的大多數的結果是冗余的、無用的,所以應該避免笛爾兒積的產生.
? 解決笛爾兒積的方法:事實上由于笛爾兒積是因為兩個表的連接條件沒有限制造成的,所以只要我們對兩個表的連接進行條件限制,就可以避免笛爾兒積的產生.可以通過一個where子句,來連接兩個表的公共的字段就可以了.
? 所以將上面的語句改成:select 姓名,學歷,部門名稱,負責人 from work,部門 where work.部門編號=部門.部門編號
B:使用表格的別名
? A:當使用多個表進行查詢時,如果有兩個表中有相同的列,應該指明選中的是哪個表中的列.
??? 比如:在work表檢索出在address表中都有的職工的職工號,姓名,學歷,基本工資
???????? select 職工號,姓名,學歷,基本工資 from work,address where work.職工號=address.職工號
???????? 上面的語句是錯誤的,原因是對于work和address表都有職工號,姓名列,所以應該指明是哪個表的職工號和姓名.
??? 改成:select work.職工號,work.姓名,學歷,基本工資 from work,address where work.職工號=address.職工號
??? 或者:select address.職工號,address.姓名,學歷,基本工資 from work,address where work.職工號=address.職工號
??? 想一想:為什么對于學歷,基本工資沒有指明表名:即:work.學歷,work.基本工資[只有一個表有這些列]
? B:允許使用別名來訪問表.
??? 格式:1:表名 as 別名
???????? 2:表名 別名
??? 例如:上面的語句可改寫成:
???????? select w.職工號,w.姓名,學歷,基本工資 from work as w,address as a where w.職工號=a.職工號
上面的語句中在from中引用兩個表,并且為表work指明了別名w,為表address指明了別名a,所以就可以用w來代表work表,用a來代表address表.或者省略as直接改成:select w.職工號,w.姓名,學歷,基本工資 from work w,address a where w.職工號=a.職工號
? C:如果使用了別名,則以后所有查詢語句中,都必須使用別名列
??? 比如:select work.職工號,work.姓名,學歷,基本工資 from work w,address a where w.職工號=a.職工號 [是錯誤的]
C:使用統計函數:
? sql跟我們提供了以下幾個統計函數:
???? sum:返回一個數字列的總和
???? avg:對一個數字列求平均值
???? min:對一個數字列求最小值
???? max:對一個數字列求最大值
???? count:返回滿足select語句中指定的條件的記錄個數
????? 舉列:1:求出work表中所有男職工的基本工資的和
???????????? select sum(基本工資) as 性別為男的基本工資 from work where 性別=\'男\'
?????????? 2:求出work表中所有職稱是經理的最高工資和最低工資,平均工資
???????????? select max(基本工資) as 最高工資,min(基本工資) as 最低工資,avg(平均工資) as 平均工資 from work???
?????????? 3:與統計函數一起使用distinct關鍵字[通常只與count函數使用]
???????????? 例:1:檢索出work表中學歷的個數
????????????????? select count(學歷) from work
??????????????? 2:檢索出work表中學歷的種類的個數
????????????????? select count(distinct 學歷) from work
????????????????? 試一試:select distinct count(學歷) from work 可行否?
??????????????? 3:有work和部門表,檢索出在銷售部工作的員工的個數
????????????????? select \'銷售部的人數\'=count(職工號) from work a,部門 b
????????????????? where a.部門編號=b.部門編號 and b.部門名稱=\'銷售部\'
??????????????? 4:在work表中檢索出其基本工資小于職工平均工資的人數
????????????????? select count(職工號) as 人數 from work
????????????????? where 基本工資<(select avg(基本工資) from work)
??????????????? 5:有學科表和學費表,從學費表檢索出有多少個學網頁設計的人
????????????????? select count(學號) as 網頁設計的人數 from 學費 a,學科 b
????????????????? where a.所學專業代號=b.課程編號 and b.課程名稱=\'網頁設計\'
D:使用group by子句對結果進行分類[只用于統計函數]
??? 舉列:1:檢索出work表各職稱的人數.
?????????? select 職稱,count(職稱) as 職稱人數 from work group by 職稱
???????? 2:檢索出各學歷的平均工資.
?????????? select 學歷,avg(基本工資) from work group by 學歷
???????? 3:有學科表和學費表,要求統計出各學科的學生數目.
?????????? select 所學專業代號,count(所學專業代號) as 人數 into #abc from 學費 group by 所學專業代號
?????????? select 課程名稱,人數 from #abc,學科 where 所學專業代號=課程編號
???????? 4:有職工表和商品銷售表,要求檢索出每個職工的職工號,姓名,銷售總量.
?????????? select 職工號,sum(銷售量) as 銷售總量 into #abcd from 商品銷售 group by 職工號
?????????? select 職工.職工號,姓名,銷售總量 from 職工,#abcd where #abcd.職工號=職工.職工號
???????? 5:查詢出每個部門最高的基本工資,顯示部門名稱和最高基本工資
?????????? select 部門名稱,max(基本工資) from work group by 部門名稱
?????????? 說明:1:在group by中不支持對列名的分配的別名
?????????????????? select 學歷 as 職工學歷,count(學歷) from work group by 職工學歷 [錯錯]
?????????????????? 改為:select 學歷 as 職工學歷,count(學歷) from work group by 學歷
??????????????? 2:select后面每一列數據除了在統計函數中的列以外都必須在group by子句出現
?????????????????? 比如:select 學歷,性別,sum(基本工資) from work group by 學歷[錯錯]
?????????????????? 改為:select 學歷,性別,sum(基本工資) from work group by 學歷,性別?????
?????????????????? 意義:各學歷各性別的基本工資之和
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子句限制用戶對某些底層基表的行的訪問
? 對不同用戶授予不同的權限
連接查詢
通過連接運算符可以實現多個表查詢。連接是關系數據庫模型的主要特點，也是它區別于其它類型數據庫管理系統的一個標志。
在關系數據庫管理系統中，表建立時各數據之間的關系不必確定，常把一個實體的所有信息存放在一個表中。當檢索數據時，通過連接操作查詢出存放在多個表中的不同實體的信息。連接操作給用戶帶來很大的靈活性，他們可以在任何時候增加新的數據類型。為不同實體創建新的表，爾后通過連接進行查詢。
連接可以在SELECT 語句的FROM子句或WHERE子句中建立，似是而非在FROM子句中指出連接時有助于將連接操作與WHERE子句中的搜索條件區分開來。所以，在Transact-SQL中推薦使用這種方法。
SQL-92標準所定義的FROM子句的連接語法格式為：
FROM join_table join_type join_table
[ON (join_condition)]
其中join_table指出參與連接操作的表名，連接可以對同一個表操作，也可以對多表操作，對同一個表操作的連接又稱做自連接。
join_type 指出連接類型，可分為三種：內連接、外連接和交叉連接。內連接(INNER JOIN)使用比較運算符進行表間某(些)列數據的比較操作，并列出這些表中與連接條件相匹配的數據行。根據所使用的比較方式不同，內連接又分為等值連接、自然連接和不等連接三種。
外連接分為左外連接(LEFT OUTER JOIN或LEFT JOIN)、右外連接(RIGHT OUTER JOIN或RIGHT JOIN)和全外連接(FULL OUTER JOIN或FULL JOIN)三種。與內連接不同的是，外連接不只列出與連接條件相匹配的行，而是列出左表(左外連接時)、右表(右外連接時)或兩個表(全外連接時)中所有符合搜索條件的數據行。
交叉連接(CROSS JOIN)沒有WHERE 子句，它返回連接表中所有數據行的笛卡爾積，其結果集合中的數據行數等于第一個表中符合查詢條件的數據行數乘以第二個表中符合查詢條件的數據行數。
連接操作中的ON (join_condition) 子句指出連接條件，它由被連接表中的列和比較運算符、邏輯運算符等構成。
無論哪種連接都不能對text、ntext和image數據類型列進行直接連接，但可以對這三種列進行間接連接。例如：
SELECT p1.pub_id,p2.pub_id,p1.pr_info
FROM pub_info AS p1 INNER JOIN pub_info AS p2
ON DATALENGTH(p1.pr_info)=DATALENGTH(p2.pr_info)
?
(一)內連接
內連接查詢操作列出與連接條件匹配的數據行，它使用比較運算符比較被連接列的列值。內連接分三種：
1、等值連接：在連接條件中使用等于號(=)運算符比較被連接列的列值，其查詢結果中列出被連接表中的所有列，包括其中的重復列。
2、不等連接： 在連接條件使用除等于運算符以外的其它比較運算符比較被連接的列的列值。這些運算符包括>、>=、<=、<、!>、!<和<>。
3、自然連接：在連接條件中使用等于(=)運算符比較被連接列的列值，但它使用選擇列表指出查詢結果集合中所包括的列，并刪除連接表中的重復列。
例，下面使用等值連接列出authors和publishers表中位于同一城市的作者和出版社：
SELECT *
FROM authors AS a INNER JOIN publishers AS p
ON a.city=p.city
?
又如使用自然連接，在選擇列表中刪除authors 和publishers 表中重復列(city和state)：
SELECT a.*,p.pub_id,p.pub_name,p.country
FROM authors AS a INNER JOIN publishers AS p
ON a.city=p.city
?
(二)外連接
內連接時，返回查詢結果集合中的僅是符合查詢條件( WHERE 搜索條件或 HAVING 條件)和連接條件的行。而采用外連接時，它返回到查詢結果集合中的不僅包含符合連接條件的行，而且還包括左表(左外連接時)、右表(右外連接時)或兩個邊接表(全外連接)中的所有數據行。
如下面使用左外連接將論壇內容和作者信息連接起來：
SELECT a.*,b.* FROM luntan LEFT JOIN usertable as b
ON a.username=b.username
?
下面使用全外連接將city表中的所有作者以及user表中的所有作者，以及他們所在的城市：
SELECT a.*,b.*
FROM city as a FULL OUTER JOIN user as b
ON a.username=b.username
?
(三)交叉連接
交叉連接不帶WHERE 子句，它返回被連接的兩個表所有數據行的笛卡爾積，返回到結果集合中的數據行數等于第一個表中符合查詢條件的數據行數乘以第二個表中符合查詢條件的數據行數。
例，titles表中有6類圖書，而publishers表中有8家出版社，則下列交叉連接檢索到的記錄數將等
于6*8=48行。
SELECT type,pub_name
FROM titles CROSS JOIN publishers
ORDER BY typ