麻辣家族討論版版's Archiver

小誌 發表於 2010-5-17 17:45

(PHP+IIS+MsSQL教學第30篇) 認識DML指令 上篇

每一種資料庫都有一種管理維護的程式語言,目前市面上的資料庫所使用的程式語言基礎幾乎都來自SQL:Structured Query Language(結構化查詢語言)。

SQL語言最初是由IBM於1970年代初期所開發的,用於關連式資料庫 (Relational Databases)中的一種資料庫查詢語言,利用 SQL 可以用來定義資料庫結構、建立資料表、指定欄位型態、長度與新增資料、修改資料、刪除資料、查詢資料,是一種資料庫應用的標準語言。

標準的SQL語言規格是由ANSI(美國國家標準學會)與ISO(國際標準組織)這兩個組織所推動制訂的,最初是在1986年由ANSI制定其標準化規格,隨後在1992年時再度推出新的版本,也就是常聽到人家說的「SQL92」。

目前市面上的資料庫所使用的程式語言雖然都以SQL為主,但是卻沒有任何一家廠商的資料庫完整的支援或應用SQL。每家資料庫廠商為了商業或產品應用的考量,都有少部份的功能不支援,或自行增加少部分的SQL延伸功能,例如,Microsoft SQL Server就不算是使用標準的SQL, Microsoft 對標準的SQL做了許多的修改與功能延伸,因此,我們Microsoft SQL Server的應為Transact-SQL,在本章中,我們仍以「SQL」稱之。

儘管每家資料庫廠商的資料庫管理維護語言不盡相同,但是,它們還是源自標準的SQL,所以在SQL的基礎使用仍是適用於各家廠商資料庫。

[color=Red]本篇內容皆以SQL資料庫為使用對象,主資料檔leave.mdf、記錄檔leave.ldf,請您將檔案複製到您的硬碟裡並附加到SQL Server中,以方便您操作範例時使用。[/color]
[attach]569[/attach]

小誌將SQL的敘述組成要素概分如下:
[list]
[*]資料定義語言:Data Definition Language(DDL),用來建立與刪除資料庫、資料表…等的SQL指令。
[*]資料處理語言:Data Manipulation Language(DML),用來新增、刪除、修改與查詢資料庫內資料記錄的SQL指令。
[*]資料管理語言:Data Control Language(DML),用來設定或變更資料庫使用者(角色)的權限。
[*]預存程序:Stored Procedure,SQL Server中預先設立的SQL指令。
[*]其他組成要素
[/list]


[color=DarkOrange][size=5][b]認識DML指令[/b][/size][/color]
DML:資料處理語言,為SQL中的資料處理指令群,包括Select(查詢)、Insert(新增)、Delete(刪除)、Update(修改)等指令。


[color=Green][b][size=4]Select指令[/size][/b][/color]
Select指令您不陌生吧!在上幾篇中,小誌就做了很多的介紹,現在小誌要介紹的是Select指令的進階應用:


[color=Magenta][size=4][i]跨資料表查詢[/i][/size][/color]
小誌現在舉一個實例來說明:在LEAVE資料庫中含有「員工名單」、「休假記錄」這兩個資料表:
[attach]570[/attach]

現在我要從「員工名單」資料表中挑出「休假記錄」資料表內休假「天數」大於三天的員工資料,如果單純的使用一個Select指令是無法達到這個要求的,因此我們必須使用兩個Select指令!首先我們先利用下列敘述找出「休假記錄」資料表內休假「天數」大於三天的員工資料:[code]Select 姓名  From  休假記錄 Where  天數>3[/code]這樣的查詢結果就成為一個「虛擬」資料表(集),接著我們要利用這個「虛擬」資料表(集)的資料為進階篩選條件值,篩選出「員工名單」資料表中被點名休假超過3天的員工資料:[code]Select  *  From  員工名單 where 姓名 In
(Select 姓名 From 休假記錄 Where 天數>3)[/code]如此,就可以從「員工名單」資料表中挑出「休假記錄」資料表內休假「天數」大於三天的員工資料!

[attach]571[/attach]

上圖中:未加框部分的Select指令敘述中含有另一段被框線框起來的Select指令敘述,這種雙Select指令組合的敘述稱之為「SubQuery」!
當我們將這「進階查詢指令」應用於程式中將可得到圖9-3的結果,被條列出來的員工都是休假天數超過三天者。[code]<?
    $conn = mssql_connect("127.0.0.1", "sa", "12345");
    if(@mssql_select_db("LEAVE", $conn))
     {
      //資料庫存在,建立SQL命令字串
      $SQL="Select  *  From  員工名單 where 姓名 In" .
            "(Select 姓名 From 休假記錄 Where 天數>3)";
      //將回傳結果存放於變數中
      $datalist=mssql_query($SQL);
      //取得欄位數量
      $fieldnum=mssql_num_fields($datalist);
?>
<html>
<head>
<title>範例ex09_01</title>
</head>
<center><table border=1 width=100%>
<?
/**************列出欄位抬頭***************/
echo "<tr>";     
      for ($x=0 ;$x<$fieldnum;$x++)
         {
          echo "<td>" . mssql_field_name($datalist,$x) . "</td>";
         }
echo "</tr>";   
/**************列出欄位資料***************/
      //將資料錄轉換為欄位陣列集合
      while ($fielddatas=mssql_fetch_array($datalist))
          {
             //輸出欄位資料
             echo "<tr>";
             for ($x=0;$x<$fieldnum;$x++)
                {
                 echo "<td>" . $fielddatas[$x] . "</td>";
                }
             echo "</tr>";  
          }
?>
<table></body>
</html>
<?
     }
    else
     {
      echo "資料庫不存在";
     }
?>[/code][attach]572[/attach]

小誌 發表於 2010-5-17 17:52

[color=Magenta][size=4][i]分組統計 Group By[/i][/size][/color]
在「休假記錄」資料表內,我們可以發現員工休假的次數可能不只一次,也許請了一次病假,過了幾天又請了事假,因此休假的記錄就不只一次:
[attach]573[/attach]

如果我們希望得知員工的總休假天數那我們可以使用「SUM」函數來統計:[code]Select Sum(天數) AS 員工休假總天數 From  休假記錄[/code]可是我們得到的是全部員工的休假天數ㄚ,那我希望分別得知每一位員工的總休假天數,那又改如何處理?這個時候我們就可以利用「Group By」指令來進行分組統計:[code]Select 姓名,Sum(天數) AS 休假總天數 From  休假記錄 Group By 姓名[/code]執行上列的指令敘述後,結果如下圖:
[attach]574[/attach]

分組統計並不限定使用於單一欄位,資料表中的每一個欄位都可成為分組的依據,同時,我們也可以使用多個欄位來作為分組的依據,例如下列的分組指令敘述:
[attach]575[/attach]

[attach]576[/attach]

小誌 發表於 2010-5-17 17:58

[color=Magenta][size=4][i]分組篩選[/i][/size][/color]
Group By指令了可以用來排序外,也可以同時加上排序指令來進行資料排序,例如:我們以「假別」為分組依據,利用Order By指令針對休假天數來排序:[code]Select假別,Sum(天數) AS 休假總天數 From  休假記錄 Group By假別 Order By Sum(天數)[/code]除了排序之外,我們還可加上Where指令先進行資料篩選後再進行分組與排序:[code]Select假別,Sum(天數) AS 休假總天數 From  休假記錄  Where 年=86 Group By假別 Order By Sum(天數)[/code]上兩式的敘述執行結果如下圖,要特別注意到一點:使用Where指令進行篩選,只能夠在原始資料的情況下進行篩選的動作,也就是說,篩選資料的動作必須發生於分組動作之前,不能在分組動作完成之後才使用Where指令進行分組結果的篩選!
[attach]577[/attach]
圖左為分組與排序;圖右為資料篩選後再進行分組與排序。


在分組動作完成之後,我們還要對分組結果的資料進行篩選時,我們該如何處理?切記:不能使用Where指令!否則您會見到下列的錯誤訊息:
[attach]578[/attach]


如果在分組動作完成之後才進行資料篩選的動作必須使用「Having」指令,例如下例:[code]      //資料庫存在,建立SQL命令字串
      $SQL="Select 年,姓名,假別,sum(天數) AS 年休假數 From 休假記錄 " .
     "Group By 年,姓名,假別 HAVING 年=86";
      //將回傳結果存放於變數中
      $datalist=mssql_query($SQL);
      //取得欄位數量
      $fieldnum=mssql_num_fields($datalist);[/code][attach]579[/attach]


那我可不可以更改篩選的條件值?可以,不過您不可使用「虛擬」欄位為篩選的根據,如下例:[code]Select 年,姓名,假別,sum(天數) AS 年休假數  From 休假記錄
Group By 年,姓名,假別 Having 年休假數>2  [/code]上式中的「年休假數」雖然是「Sum(天數)」的計算結果,但它是一個「虛擬」欄位,不是資料庫中的真實欄位,所以不能用來做為篩選的根據。
[attach]580[/attach]


如果您要使用「年休假數」欄位做為篩選根據,必須撰寫成下式:[code]SQL="Select 年,姓名,假別,sum(天數) AS 年休假數 From 休假記錄 " &_
     "Group By 年,姓名,假別 Having sum(天數)>2"
CMD.CommandText=SQL[/code]執行結果如下圖:
[attach]581[/attach]

頁: [1]

麻辣家族討論版版為 麻辣學園 網站成員  由 昱得資訊工作室 © Since 1993 所提供