delphi完美經典–第十四章

第14章 資料庫概念及SQL介紹

關係型資料庫就是一種二維表格式的資料結構，依據資料表之間的關聯來作訪問的操作。

一、資料庫基本概念

1、資料庫結構：

資料庫的組織圖由下而上依序為欄位(Field)、記錄(record)、資料表(Table)。

2、開放資料庫連接協議(ODBC)

ODBC提供者與資料庫之間的介面。通過ODBC當作我們訪問資料庫的介面，就可輕易訪問不同資料庫。當然，這樣的訪問，必須通過ODBC驅動程式完成，也就是資料庫廠商必須提供支援OS的ODBC驅動程式。

ODBC的架構：由應用程式、驅動程式管理器、驅動程式、資料來源四個構成要素。

ODBC視窗中有三種與資料來源有關的選項卡可供設定“資料來源名稱” 。分別是：

使用者DSN：只能提供給本機電腦使用，且僅供目前使用者使用。

系統DSN：只能提供給本機電腦使用，本系統上或其他具有存取權限的使用者都可使用。

檔案DSN：設定在這個選項卡的資料來源可被安裝同一個驅動程式的所有使用者使用，此種資料來源並不限定在某一個使用者身上或某一個電腦上。檔案資料來源並不以資料來源名稱記錄我們設定的資料來源，而是以檔案名稱記錄。

雖這三種使用對象不同，但設定方式幾乎一樣。

3、SQL Explorer

Delphi的DatabaseExplorer在不同Delphi版本中有些不同的功能特徵。在Delphi Enterprise版本中名稱是“SQL Explorer”：可直接存取非關係型資料庫(像dBASE、Paradox)、通過ODBC訪問所支援的資料庫、或任何支援SQL的關係型資料庫。在Delphi Professional版本中名稱是“Database Explorer”，僅支援訪問非關係型資料庫和通過ODBC訪問所支援的資料庫。

二、結構化查詢語言 (SQL)(SQL)

1、SQL文法：不區分大小寫，但建議將SQL關鍵字以大寫表示，非SQL關鍵字以小寫或大小寫混合表示。

SQL的命名規則中，對資料包、欄位的命名有些規範。以下是命名的基本原則：

資料表命名：避免命名為中文或空。當資料表名稱、欄位名稱含有空格或使用關鍵字時，需使用“[]”將名稱擴住，否則，會造成SQL語法錯誤。

欄位命名：避免使用中文、空格或特殊符號。常用類型：

Ø  CHAR(n)：固定長度的字串類型。意味著不管字串是否達到n個長度，都會佔用n個Bytes空間(以空格補滿剩餘空間)。

Ø  VARCHAR(n)：可變長度。佔用大小是其實際大小，不補空格。

因CHAR長度固定，所以處理速度較快。但比較麻煩，需用trim之類的函數去除空格。

Ø  INTEGER：整型，以4Bytes為儲存單位，其中第一個bit用來記錄正負數。

2、SQL指令：依用途分為兩類：資料定義語言 (Data Definition Language)(DDL)和資料操作語言(DML)。先說明下出現在文法中的符號：

[]：表示它是可以省略的。

|、{}：多個選項只能選取一項時，以“|”分隔。若這個多選一的項目可省略，用[]括起；不可省略，則用{}括起。

[,…]：除表示此項目可被省略外，同時也表示此項目若存在，其文法與前面的項目相同。

3、SQL語句：

1）CREATE語句：建立資料表或資料表的索引。

建立資料表文法：

CREATE  TABLE table_name

{

         column_definition [NULL | NOT NULL][PRIMARY KEY | UNIQUE] [,

column_definition [NULL | NOT NULL] [PRIMARY KEY | UNIQUE] [,…] ]

}

column_definition：格式“欄位名稱 類型(大小)”。

[PRIMARY KEY |UNIQUE]：主索引是唯一值，但唯一值不一定是主索引。

CREATE TABLEFriend_Table

{

         Id int NOT NULL PRIMARY KEY,

         Name char(12) NOT NULL,

         Phone char(15) NOT NULL,

         Age int NULL

}

建立資料表索引：

CREATE INDEX能在一個已存在的資料表中，建立次索引(非主索引)。文法：

CREATE [UNIQUE] INDEXindex_name ON table_name

{

         Column_name

}

[UNIQUE]：設定建立索引的欄位具有唯一性。

2）ALTER TABLE語句：提供修改資料表的能力

ALTER TABLEtable_name

{ ADDcolumn_definition [,…] | DROP column_name [,…] }

ALTER TABLEFriend_Table ADD Sex CHAR(6), Address CHAR(50)

ALTER TABLEFriend_Table DROP Sex, Address       //刪除Sex和Address欄位

3）DROP語句

可刪除資料表索引或整個資料表。

DROP TABLE語句：DROP TABLEtable_name [,…]

DROP INDEX語句：(Access一次僅能刪除一個索引名稱，而SQL Server一次能刪除多個索引名稱)：

DROP INDEXindex_name ON table_name

DROP INDEXtable_name.index_name [,…]

4）SELECT語句

SELECT[predicate] {* | column_list} FROM tableexpression [,…]

[WHERE clause]

[GROUP BYclause]

[HAVING clause]

[ORDER BYclause]

predicate：可用ALL、DISTINCT、TOPn[PERCENT]中的一個條件來限制要返回的查詢結果。其中，ALL表示要顯示所有資料；DISTINCT表示查詢結果中，若多條記錄完全相同(只管查詢的欄位)，則不顯示重複的欄位；TOPn[PERCENT]為顯示欄位資料的前n條或前n百分比的記錄。

{* |column_list}：查詢所有欄位或選擇性查詢指定欄位。column_list可是“table_name.*”、“[table_name.]column_name”。若指定欄位名稱，則可同時搭配AS關鍵字為此查詢欄位設定“別名”以方便顯示查詢結果。如“SELECT id AS 編號, name AS 姓名 FROM Table”。

tableexpression：一個或多個(逗號分隔)資料表名稱。

GROUP BY clause：可將查詢結果按照clause設定的條件分組。

HAVING clause：通常與GROUP BY子句搭配使用。HAVING子句可用彙總函式，而WHERE子句不可用彙總函式。

ORDER BY clause：對所得結果作遞增(ASC)或遞減(DESC)的排序。

SQL提供AVG()、COUNT()、MAX()、MIN()、SUM()五個標準的彙總函式。AVG()將符合查詢條件的某一欄位所有記錄取平均值。COUNT()用來取得符合查詢條件的記錄條數。SUM()用來計算合格某欄位值的總和。

SELECT INTO：將包括欄位名稱、欄位定義的查詢結果，新增到一個新的資料表。一般，這個新的資料表不該存在，若存在，可能覆蓋或產生錯誤。文法：

SELECTcolumn_name [,…] INTO New_Table_Name FROM Source_Table_Name [,…]

New_Table_Name：要建立的新資料表名稱，用來存放查詢結果。

當含多個來源資料表時，若兩個或兩個以上的表含有相同的欄位名稱，則必須使用“表名.欄位名”的格式。

5、INSERT、UPDATE語句

INSERT語句：新增一條或多條記錄到一個資料表中。

INSERT [INTO]table_name(column_name1 [,column_name2 [,…]])

         VALUE(value1[, value2 [,…]])

[INTO]：有些資料庫允許省略，但Access不允許。

當欄位類型為文字時，需以單引號將欄位值括住，且各欄位值之間以逗號隔開。未指定欄位自動填入預設值。

INSERT INTO 產品來源(產品名稱, 產地)

VALUE (‘葡萄牛奶’, ‘黑龍江’)

INSERT語句一次只能新增一條記錄，當一次要新增多條記錄時，可通過INSERT搭配SELECT來完成。

INSERT [INTO]table_name[(column_name [,…])]

SELECT {* |columu_list} FROM tableexpression [,…]

UPDATE用來更新現有記錄。

UPDATEtable_name

SET column_ref =new_value[, column_ref2 = new_value2 [,…]]

[WHERE criteria]

UPDATE 產品來源

SET 產品名稱 = ‘特級_’ + 產品名稱

WHERE 產地 = ‘廣東’

6、DELETE語句

DELETE [FROM]table_name

[WHERE criteria]

三、SQL進階指令使用

1、UNION運算：SQL指令中可用SELECT語句配合UNION運算對多個資料表作聯集的查詢。即取得兩個或多個資料表的共同資料，以便做各資料記錄的串連，其中的共同條件指各查詢結果的欄位數相同。

         query1UNION [ALL] query2 [UNION (ALL) queryn [,…]]

query1、query2……queryn：每個query都是一個SELECT語句，且查詢結果的欄位數必須相同。

ALL：若未設定ALL，查詢結果中重複的資料只會列出一條，加上後，則重複資料會全數顯示。

在搭配UNION的SELECT語句中，對於ORDERBY子句的搭配上有特殊的限制：ORDER BY子句只能在最後一個SELECT語句中搭配使用，以便達到對整個查詢結果做排序的操作。

SELECT 連絡人姓名 AS 賓客名稱, ‘供應商’ AS 關係 FROM 供應商

UNION

SELECT 經理人, ‘員工’ FROM 員工 ORDER BY 關係 ASC

2、JOIN運算

SQL指令可對兩個資料表做交集，即取得兩個或多個資料表之間合格記錄，用來作不同資料表之間的資料合併。依其資料合併的方式，將交集分為左交集(LEFT JOIN)、右交集(RIGHT JOIN)、內部交集(INNER JOIN)。

table1 [LEFT |RIGHT | INNER] JOIN table2

ON table1.field1compopr table2.field2

compopr：比較子，如=、<>、<=、>=。

左交集即table1所有記錄+table2合格記錄。

右交集即table2所有記錄+table1合格記錄。

內部交集即table1和table2中合格記錄。

3、特殊運算子

除一般運算子<、=等外，還有特殊運算子：LIKE、IS、IN、AND、OR、BETWEEN……AND等6種。

①    {WHERE | HAVING}  [NOT] column_name LIKE match_string

[NOT]：將查詢條件反相。

match_string：條件值，必須以單引號擴住。多個字元：在Access中用*表示，在SQL Server中用%。特定或某範圍的字元、數字：用[]表示。

②    IS：查詢哪些欄位值為NULL，或搭配NOT來查詢哪些欄位值不是NULL。

{WHERE |HAVING}  [NOT]  column_name IS  [NOT]  NULL

[NOT]：兩個NOT都是取反，都設定時，相當於沒設定。

③    IN：查詢指定的欄位值，是否為條件列的其中一個條件指。

{WHER | HAVING}[NOT] column_name IN (value1[,value2[,…]])

④    BETWEEN…AND：查詢指定的欄位值，是否在我們設定的條件範圍內。

{WHER | HAVING}[NOT] column_name BETWEEN value1 AND value2

⑤    AND、OR：且、或運算。

{WHER | HAVING}[NOT] (Bool_exp (AND | OR) Bool_exp)

Bool_exp：一個運算結果為BOOL型的運算式。

4、子查詢：一個查詢語句中，有另一個SELECT語句(以小括弧將SELECT語句擴住)。子查詢可在SELECT語句的查詢欄位列表或WHERE、HAVING子句中。

{WHERE | HAVING} column_name  compare  (subquery)

或{WHERE |HAVING}  column_name  [NOT] IN  (subquery)

或{WHERE | HAVING}  [NOT] EXISTS  (subquery)

compare：一般的比較子，如=、>等。

subquery：子查詢內的SELECT語句。

第一種文法主要用於子查詢返回的值為單一欄位的單條資料時，且我們將返回的該項資料，當做另一個查詢條件中的比較值。