transact—sql進階查詢（上）

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 學曆,性別     
                   意義:各學曆各性別的基本工資之和