【資料庫】資料庫基礎

一、SQL語句

（1）Select 查詢語句

         文法：SELECT [ALL|DISTINCT] <目標列運算式> [AS 列名]
                     [,<目標列運算式> [AS 列名] ...] FROM <表名> [，<表名>…]
                     [WHERE <條件運算式> [AND|OR <條件運算式>...]
                     [GROUP BY 列名 [HAVING <條件運算式>]]
                     [ORDER BY 列名 [ASC | DESC]]
          解釋：[ALL|DISTINCT]        ALL：全部

                                                         DISTINCT：不包括重複行
                      <目標列運算式> 對欄位可使用AVG、COUNT、SUM、MIN、MAX、運算子等
                      <條件運算式>
                                             查詢條件 謂詞
                                             比較 =、>,<,>=,<=,!=,<>,
                                             確定範圍 BETWEEN AND、NOT BETWEEN AND
                                             確定集合 IN、NOT IN
                                             字元匹配 LIKE（“%”匹配任何長度，“_”匹配一個字元）、NOT LIKE
                                             空值          IS NULL、IS NOT NULL
                                             子查詢      ANY、ALL、EXISTS
                                             集合查詢 UNION（並）、INTERSECT（交）、MINUS（差）
                                             多重條件 AND、OR、NOT
                      <GROUP BY 列名> 對查詢結果分組
                       [HAVING <條件運算式>] 分組篩選條件
                       [ORDER BY 列名 [ASC | DESC]] 對查詢結果排序；ASC：升序 DESC：降序

         例子：

          例1：從表score 、student中查詢學生的 學號、姓名、課程名、成績 

                   select student.sno as 學號, student.name as 姓名, course as 課程名, score as 成績 from
score,student where student.sid=score.sid and
score.sid=:sid
          例2：

                  select   student.sno as 學號,
student.name as 姓名, AVG(score) as 平均分 from score,student
where student.sid=score.sid and student.class=:class and (term=5 or term=6) group by student.sno, student.name having count(*)>0 order by 平均分 DESC

          例3：模糊查詢

                  select * from score where sid like '9634'   

       
          例4：集合查詢

                  select * from student where class in (select class from student where name='陳小小')

（2）INSERT插入語句
          文法：INSERT INTO <表名> [（<欄位名1> [,<欄位名2>, ...])] VALUES (<常量1> [,<常量2>, ...])

          例1：INSERT INTO 借書表（rid,bookidx,bdate）VALUES (edit1.text,edit2.text,date)

          文法：INSERT INTO <表名> [（<欄位名1> [,<欄位名2>, ...])]  子查詢 
          例2：INSERT INTO score1(sno,name) SELECT sno,name FROM student WHERE class=’9634’

（3）UPDATE-SQL
          文法：UPDATE 〈表名〉

                       SET           列名1 = 常量運算式1[,列名2 = 常量運算式2 ...]
                       WHERE   <條件運算式> [AND|OR <條件運算式>...]

 
          例子：update score set credithour=4   where course='資料庫'

（4）DELETE-SQL  （刪除記錄）
          文法：DELETE FROM〈表名〉[WHERE <條件運算式> [AND|OR <條件運算式>...]]
         例子：Delete from student where sid='003101'

（5）CREATE TABLE
          CREATE TABLE | DBF TableName1 [NAME LongTableName] [FREE]
          (FieldName1 FieldType [(nFieldWidth [, nPrecision])]
           [NULL | NOT NULL]
          [CHECK lExpression1 [ERROR cMessageText1]]
          [DEFAULT eExpression1]
          [PRIMARY KEY | UNIQUE]
          [REFERENCES TableName2 [TAG TagName1]]
          [NOCPTRANS]
          [, FieldName2 ...]
          [, PRIMARY KEY eExpression2 TAG TagName2
                      |, UNIQUE eExpression3 TAG TagName3]
         [, FOREIGN KEY eExpression4 TAG TagName4 [NODUP]
            REFERENCES TableName3 [TAG TagName5]]
        [, CHECK lExpression2 [ERROR cMessageText2]])
                      | FROM ARRAY ArrayName

（6）ALTER TABLE
          ALTER TABLE TableName1
          ADD | ALTER [COLUMN] FieldName1
          FieldType [(nFieldWidth [, nPrecision])]
          [NULL | NOT NULL]
          [CHECK lExpression1 [ERROR cMessageText1]]
          [DEFAULT eExpression1]
          [PRIMARY KEY | UNIQUE]
          [REFERENCES TableName2 [TAG TagName1]]
          [NOCPTRANS]

（7）DROP TABLE
           DROP TABLE [路徑名.]表名

（8）CREATE INDEX
          CREATE INDEX index-name ON table-name（column[，column…]）
          例：CREATE INDEX uspa ON 口令表(user,password)

（9）DROP INDEX
         DROP INDEX table-name.index-name|PRIMARY
         例：DROP INDEX 口令表.uspa

二、在程式中使用靜態SQL語句
       在程式設計階段，將SQL命令文本作為TQuery組件的SQL屬性值設定。

三、在程式中使用動態SQL語句
        動態SQL語句是指在SQL語句中包含有參數變數的SQL語句（如：select * from student where class=:class），在程式中可以為參數賦值。給參數賦值的方法有：

        1、利用參數編輯器為參數賦值
              選中TQuery組件，在對象監視器OI中點取Params項，在彈出的參數編輯視窗中設定參數的值。
              例：SELECT bookidx AS 書號,藏書表.bookname AS 書名, bdate AS 借書日期 FROM 借書表,藏書表 where 借書表.bookidx=藏書表.bookidx and rid=:rid

        2、在程式運行中通過程式為參數賦值
            （1）根據參數在SQL語句中出現的順序，使用TQuery的Params屬性為參數賦值；

              例：在借書表中插入一條記錄
                       with Query1 do
                       begin
                       SQL.clear;
                       SQL.add('Insert Into 借書表（bookidx,rid,rdate）');
                       SQl.add('Values(:bookidx,:rid,:rdate)');
                       Params[0].AsString := bookidxEdit.Text;
                       Params[1].AsString := ridEdit.Text;
                       Params[2] .AsDate:=date;
                       ExecSQL;
                       End;

          （2）根據SQL語句中的參數名字，調用ParamByName方法為參數賦值；
                     ParamByName('bookidx').AsString := bookidxEdit.Text;
                     ParamByName('rid').AsString := ridEdit.Text;
                     ParamByName('rdate') .AsDate:=date;
                     ExecSQL;
                     有：AsString 、AsSmallInt 、AsInteger 、AsWord 、AsBoolean 、AsFloat 、AsCurrency 、AsBCD 、AsDate 、AsTime 、AsDateTime轉換函式

           3、使用資料來源為參數賦值
                 把TQuery的DataSource屬性設定為另一個資料來源（T DataSource名字），Delphi會把未賦值的參數與指定的資料來源中的各欄位相比較，並將匹配的欄位的值賦給未賦值的參數，可實現主表—明細表應用。

四、對TQuery返回的資料集進行修改
        一般情況下，TQuery返回的資料集是唯讀，不能修改；
        對不包含集操作（如：SUM、COUNT）的單表SELECT查詢，設定TQuery的RequsetLive屬性為True，則可修改TQuery返回的資料集。

varI: Integer;ListItem: string;beginfor I := 0 to Query1.ParamCount - 1 dobeginListItem := ListBox1.Items[I];case Query1.Params[I].DataType offtString:Query1.Params[I].AsString := ListItem;ftSmallInt:Query1.Params[I].AsSmallInt := StrToIntDef(ListItem, 0);ftInteger:Query1.Params[I].AsInteger := StrToIntDef(ListItem, 0);ftWord:Query1.Params[I].AsWord := StrToIntDef(ListItem, 0);ftBoolean:beginif ListItem = 'True' thenQuery1.Params[I].AsBoolean := TrueelseQuery1.Params[I].AsBoolean := False;end;ftFloat:Query1.Params[I].AsFloat := StrToFloat(ListItem);ftCurrency:Query1.Params[I].AsCurrency := StrToFloat(ListItem);ftBCD:Query1.Params[I].AsBCD := StrToCurr(ListItem);ftDate:Query1.Params[I].AsDate := StrToDate(ListItem);ftTime:Query1.Params[I].AsTime := StrToTime(ListItem);ftDateTime:Query1.Params[I].AsDateTime := StrToDateTime(ListItem);end;end;end; 

一、資料集表的開啟與關閉
       開啟：設定資料集組件的Active屬性為True或調用資料集組件的Open方法
        關閉：設定資料集組件的Active屬性為False或調用資料集組件的Close方法

二、建立資料庫應用程式
        利用嚮導建立：使用Database菜單/Form Wizard選項；
        建立主從表：設定從表的MasterSource、MasterField屬性；
        建立查詢表：使用TQuery組件；

三、資料庫表記錄的定位
        使用TDBNavigator組件；
         調用資料集組件的First、Next、Prior、Last方法；
          資料集組件的EOF屬性（或BOF屬性）用來判斷記錄指標是否指向第一條記錄（或最後一條記錄）；
         使用資料集的書籤BookMark（GetBookMark：獲得目前記錄的BookMark記號；GotoBookMark：從目前記錄直接轉到指定BookMark的那條記錄；FreeBookMark：釋放某個BookMark）
          使用GotoKey、FindKey方法尋找記錄進行定位；

四、資料庫表欄位對象的使用

（1）建立永久的欄位對象
           雙擊或單擊再右擊TTable(TQuery)對象開啟欄位編輯器，使用其快顯功能表增加欄位對象、刪除欄位對象、定義新的欄位對象（欄位編輯器的快顯功能表的New Fields選項，可建立計算欄位）；

（2）欄位對象的屬性、方法、事件
          欄位對象名：如Table1Name、Query1Sid
          屬性:Alignment（對齊）、Calculated（是否是從其它欄位值計算得到）、DisplayLabel（顯示的標題）、DisplayWidth（顯示的寬度）、DisplayFormat（顯示的格式）、EditMask（輸入的限制）、FieldName（欄位名）、ReadOnly（是否唯讀）、Visible（是否顯示）
          事件：OnChange（欄位值發生變化時觸發）、OnGetText（當欄位對象獲得欄位值時觸發）、OnSetText（當欄位對象被設定欄位值時觸發）、OnValiData（當修改、插入、進行有效性檢驗時觸發）

（3）欄位對象的類型轉換
          有：AsString 、AsSmallInt 、AsInteger 、AsWord 、AsBoolean 、AsFloat 、AsCurrency 、AsBCD 、AsDate 、AsTime 、AsDateTime轉換函式
         如：Edit1.Text:=Table1Name.Value ;
         Table1Bdate.AsString:=DateToStr(DATE) ;

（4）對欄位對象的訪問
          動態欄位對象的訪問：Table1.Fields[0]. DisplayLabel:= '學生編號'
          Table1.FieldByName('Sid'). DisplayLabel:= '學生編號' Table1.Fields[0].Assignment:=taCenter
           Edit1.Text:= Table1.FieldByName('Sid').AsString
            永久欄位對象的訪問：Query1Sid.DisplayLabel:= '學生編號'
            Query1Sid.DisplayWidth:= 12

五、對資料庫表資料的操作方法

（1）訪問表中某一欄位的資料的方法：
Table1.FieldByName（'bookidx'）.AsString
Table1.Field[0].AsInteger
Table1.Fieldvalues['bookidx']

（2）資料庫表的一些屬性：
目前記錄號：Table1.Recno
記錄總數：Table1.RecordCount
得到表的欄位名：Table1.GetFieldNames（ListBox1。Items）

（3）資料維護的方法：
Edit方法：把資料集設定為編輯狀態；
Append方法：把資料集設定為插入狀態（最後）；
Insert方法：把資料集設定為插入狀態（目前記錄後）；
Post方法：把修改的記錄寫回資料集；
Cancel方法：取消當前的操作；
Delete方法：刪除表中目前記錄；
AppendRecord方法：
InsertRecord方法：table1.InsertRecord（['963409', NIL, NIL，'考試']）；
SetRecords方法：
Abort方法：取消各種方法的調用；

（4）輸入資料的合法性驗證
對資料庫表建立合法性驗證機制（如在DBD設定表的Validity Check、Table Lookup、Referential Integrity等屬性）；
 在欄位編輯表Fields Editor（雙擊Ttable對象），選擇欄位，編寫其OnValidate事件，要求非空可設定其Required屬性為True；
 在程式中防止不合法輸入（如：使用TDBcombobox對象，TDBlookupcombobox對象）；

六、資料檢索
（1）利用索引排序
如：TABLE1.IndexName:='uspa' 或TABLE1.IndexFieldNames:='user_id'
（2）使用GotoKey方法尋找資料庫中的記錄
 要求尋找欄位建立了索引，非主索引要設定Ttable對象的IndexName屬性。
 調用SetKey方法，把要尋找的Ttable對象置成尋找模組；
 把尋找值送進被尋找的Field的尋找緩衝區；
調用Ttable對象的GotoKey方法，測試該方法的傳回值判斷尋找是否成功；
（3）使用FindKey方法尋找資料庫中的記錄
把尋找值作為參數傳遞給FindKey函數，允許有多個尋找值，要求把要尋找的多個欄位的索引名賦給Ttable對象的IndexName屬性；
（4）不精確尋找
GotoNearest方法
FindNearest方法
（5）使用Locate方法尋找資料庫中的記錄(不用建索引)
table1.locate(‘欄位名1；欄位名2’，VarArroyof（[‘值1’，‘值2’]），[LoCaseInsensitive，LoPartialKey])
LoCaseInsensitive：忽略大小寫；IoPartialKey：不精確尋找
（6）設定尋找範圍的方法
SetRangeStart、SetRangeEnd、EditRangeStart、EditRangeEnd、SetRange（[Start Values]，[End Value]）、ApplyRange、CancelRange
（7）用TQuery組件的動態SQL語句進行尋找

七、修改資料庫中的記錄
在程式中對資料庫記錄進行操作可按下列的步驟進行：
（1）移動資料指標到要修改的記錄；
（2）調用Edit方法將Ttable組件設定成編輯狀態；
（3）修改欄位值；（Table1.Fieldvalues['欄位名']:=值、Table1.Field[0].AsString:=值）
（4）可用Nil對欄位賦空值；
（5）調用Post方法將修改後的記錄寫入資料庫；

八、插入和刪除記錄
刪除：移動指標到相應記錄處，調用Delete方法；
插入：調用Insert、InsertRecord方法（目前記錄處插入）或Append、InsertRecord方法（表的末尾插入）；

如何選擇一個好的資料庫（三大資料庫比較）
【開放性】

SQL Server：只能在windows 上運行，沒有絲毫的開放性，作業系統的系統的穩定對資料庫是十分重要的。Windows9X系列產品是偏重於案頭應用，NT server只適合中小型企業。而且windows平台的可靠性，安全性和伸縮性是非常有限的。它不象unix那樣久經考驗，尤其是在處理大資料量的關鍵業務時.

Oracle：能在所有主流平台上運行（包括 windows）。完全支援所有的工業標準。採用完全開放策略。可以使客戶選擇最適合的解決方案。對開發商全力支援。

DB2：能在所有主流平台上運行（包括windows）。最適于海量資料。DB2在企業級的應用最為廣泛,在全球的500家最大的企業中,幾乎85%以上用DB2資料庫伺服器,而國內到97年約佔5%.

【延展性,並行性】

SQL server：並行實施和共存模型並不成熟。很難處理日益增多的使用者數和資料卷。伸縮性有限。

Oracle：平行伺服器通過使一組結點共用同一簇中的工作來擴充windownt的能力,提供高可用性和高伸縮性的簇的解決方案。如果windowsNT不能滿足需要, 使用者可以把資料庫移到UNIX中。

DB2 ：DB2具有很好的並行性。DB2把資料庫管理擴充到了並行的、多節點的環境.資料庫分區是資料庫的一部分，包含自己的資料、索引、設定檔、和交易記錄。資料庫分區有時被稱為節點或資料庫節點

【安全性】

SQL server：沒有獲得任何安全性憑證。

Oracle Server：獲得最高認證層級的ISO標準認證。

DB2：獲得最高認證層級的ISO標準認證。

【效能】

SQL Server：多使用者時效能不佳

Oracle：效能最高， 保持windowsNT下的TPC-D和TPC-C的世界記錄。

DB2：適用於資料倉儲和線上事物處理,效能較高。

【用戶端支援及應用模式】

SQL Server：C/S結構，只支援windows客戶，可以用ADO,DAO,OLEDB,ODBC串連.

Oracle：多層次網路計算，支援多種工業標準，可以用ODBC,JDBC,OCI等網路客戶串連

DB2：跨平台，多層結構，支援ODBC,JDBC等客戶

【操作簡便】

SQL Server：操作簡單,但只有圖形介面.

Oracle：較複雜, 同時提供GUI和命令列，在windowsNT和unix下操作相同

DB2： 操作簡單,同時提供GUI和命令列，在windowsNT和unix下操作相同

【使用風險】

SQL server：完全重寫的代碼，經曆了長期的測試，不斷延遲，許多功能需要時間來證明。並不十分相容早期產品。使用需要冒一定風險。

Oracle：長時間的開發經驗，完全向下相容。得到廣泛的應用。完全沒有風險。

DB2：在巨型企業得到廣泛的應用，向下相容性好。風險小。