SQL資料庫

在大數據時代中，資料庫系統的數據類型與規模在不斷擴增，這給資料庫管理帶來了一定的挑戰。在社會生產生活中，對於資料庫的應用範圍逐步增大，提升資料庫開發及應用的效率，是保障我國社會生產生活高效運轉的關鍵。

SQL作為一種操作命令集, 以其豐富的功能受到業內人士的廣泛歡迎, 成為提升資料庫操作效率的保障。SQL Server資料庫的應用，能夠有效提升數據請求與返回的速度，有效應對複雜任務的處理，是提升工作效率的關鍵。

由於SQL Servers資料庫管理系統具有較高的數據管理性能，因其優越的性能，應用範圍非常廣，大量應用於伺服器和客戶體系結構中。SQL Servers資料庫的性質主要體現在以下幾個方面：系統的吞吐量、響應時間以及并行處理能力, 發出請求伺服器回應的速度、還有不同屏幕之間切換的速度等等。

1）非過程化語言

2）統一的語言

3）是所有關係資料庫的公共語言

SQL資料庫的數據體系結構基本上是三級結構，但使用術語與傳統關係模型術語不同。在SQL中，關係模式(模式)稱為“基本表”(base table)；存儲模式(內模式)稱為“存儲文件”(stored file)；子模式(外模式)稱為“視圖”(view)；元組稱為“行”(row)；屬性稱為“列”(column)。

在正式學習SQL語言之前，首先讓我們對SQL語言有一個基本認識，介紹一下SQL語言的組成：

1.一個SQL資料庫是表(Table)的集合，它由一個或多個SQL模式定義。

2.一個SQL表由行集構成，一行是列的序列(集合)，每列與行對應一個數據項。

3.一個表或者是一個基本表或者是一個視圖。基本表是實際存儲在資料庫的表，而視圖是由若干基本表或其他視圖構成的表的定義。

4.一個基本表可以跨一個或多個存儲文件，一個存儲文件也可存放一個或多個基本表。每個存儲文件與外部存儲上一個物理文件對應。

5.用戶可以用SQL語句對視圖和基本表進行查詢等操作。在用戶角度來看，視圖和基本表是一樣的，沒有區別，都是關係(表格)。

6.SQL用戶可以是應用程序，也可以是終端用戶。SQL語句可嵌入在宿主語言的程序中使用，宿主語言有FORTRAN，COBOL，PASCAL，PL/I，C和Ada語言等。SQL用戶也能作為獨立的用戶介面，供交互環境下的終端用戶使用。

國內外普遍採用SQL系統資料庫產品，可以說，作為集成網路的資料庫系統必須採用這類產品。關於SQL資料庫產品種類較多，常用的有Oracle、Sybase、Informix等。

這些資料庫管理系統在世界上佔據了絕大部分的資料庫市場，其中屬Oracle佔有市場的份 額最大。Sybase是一種多媒體資料庫，由於其優良的性能，使其處在上升期。目前國內開 始使用Sybase的用戶越來越多，使用的範圍也越來越廣。在選擇資料庫時應注意一個問 題，即必須與網路操作系統相匹配，根據這些分散式資料庫的性能，在選用Netware網路 操作系統時，一般應使用Sybase或Oracle資料庫，若選用Windows NT Advanced Server 網路操作系統時，應選用微軟公司的產品MS SQL Server，因為微軟公司的這個資料庫其 核心就是Sybase，是一個分散式多媒體資料庫。這樣選擇資料庫，很容易建立起Client/ Server結構，使整個網路數據的公布和利用更加合理。

SQL包括了所有對資料庫的操作，主要是由4個部分組成：

1.數據定義：這一部分又稱為“SQL DDL”，定義資料庫的邏輯結構，包括定義資料庫、基本表、視圖和索引4部分。

2.數據操縱：這一部分又稱為“SQL DML”，其中包括數據查詢和數據更新兩大類操作，其中數據更新又包括插入、刪除和更新三種操作。

3.數據控制：對用戶訪問數據的控制有基本表和視圖的授權、完整性規則的描述，事務控制語句等。

4.嵌入式SQL語言的使用規定：規定SQL語句在宿主語言的程序中使用的規則。

下面我們將分別介紹：

SQL數據定義功能包括定義資料庫、基本表、索引和視圖。

首先，讓我們了解一下SQL所提供的基本數據類型：(如^00100009b^)

1.資料庫的建立與刪除

(1)建立資料庫：資料庫是一個包括了多個基本表的數據集，其語句格式為：

CREATE DATABASE <資料庫名> 〔其它參數〕

其中，<資料庫名>在系統中必須是唯一的，不能重複，不然將導致數據存取失誤。〔其它參數〕因具體資料庫實現系統不同而異。

例：要建立項目管理資料庫(xmmanage)，其語句應為：

CREATE DATABASE xmmanage

(2)資料庫的刪除：將資料庫及其全部內容從系統中刪除。

其語句格式為：DROP DATABASE <資料庫名>

例：刪除項目管理資料庫(xmmanage)，其語句應為： DROP DATABASE xmmanage

2.基本表的定義及變更

本身獨立存在的表稱為基本表，在SQL語言中一個關係唯一對應一個基本表。基本表的定義指建立基本關係模式，而變更則是指對資料庫中已存在的基本表進行刪除與修改。

SQL是一種查詢功能很強的語言，只要是資料庫存在的數據，總能通過適當的方法將它從資料庫中查找出來。SQL中的查詢語句只有一個：SELECT，它可與其它語句配合完成所有的查詢功能。SELECT語句的完整語法，可以有6個子句。完整的語法如下：

SELECT 目標表的列名或列表達式集合

FROM 基本表或(和)視圖集合

〔WHERE條件表達式〕

〔GROUP BY列名集合

〔HAVING組條件表達式〕〕

〔ORDER BY列名〔集合〕…〕

簡單查詢，使用TOP子句

查詢結果排序order by

帶條件的查詢where,使用算術表達式，使用邏輯表達式，使用between關鍵字，使用in關鍵字，

模糊查詢like

整個語句的語義如下：從FROM子句中列出的表中，選擇滿足WHERE子句中給出的條件表達式的元組，然後按GROUPBY子句(分組子句)中指定列的值分組，再提取滿足HAVING子句中組條件表達式的那些組，按SELECT子句給出的列名或列表達式求值輸出。ORDER子句(排序子句)是對輸出的目標表進行重新排序，並可附加說明ASC(升序)或DESC(降序)排列。

在WHERE子句中的條件表達式F中可出現下列操作符和運算函數：

算術比較運算符：<，<=，>，>=，=，<>。

邏輯運算符：AND，OR，NOT。

集合運算符：UNION(並)，INTERSECT(交)，EXCEPT(差)。

集合成員資格運算符：IN，NOT IN

謂詞：EXISTS(存在量詞)，ALL，SOME，UNIQUE。

聚合函數：AVG(平均值)，MIN(最小值)，MAX(最大值)，SUM(和)，COUNT(計數)。

F中運算對象還可以是另一個SELECT語句，即SELECT語句可以嵌套。

上面只是列出了WHERE子句中可出現的幾種主要操作，由於WHERE子句中的條件表達式可以很複雜，因此SELECT句型能表達的語義遠比其數學原形要複雜得多。

下面，我們以上面所建立的三個基本表為例，演示一下SELECT的應用：

1.無條件查詢

例：找出所有學生的的選課情況

SELECT st_no，su_no

FROM score

例：找出所有學生的情況

SELECT*

FROM student

“*”為通配符，表示查找FROM中所指出關係的所有屬性的值。

2.條件查詢

條件查詢即帶有WHERE子句的查詢，所要查詢的對象必須滿足WHERE子句給出的條件。

例：找出任何一門課成績在70以上的學生情況、課號及分數

SELECT UNIQUE student.st_class，student.st_no，student.st_name，student.st_sex，student.st_age，score.su_no，score.score

FROM student，score

WHERE score.score>=70 AND score.stno=student,st_no

這裡使用UNIQUE是不從查詢結果集中去掉重複行，如果使用DISTINCT則會去掉重複行。另外邏輯運算符的優先順序為NOT→AND→OR。

例：找出課程號為c02的，考試成績不及格的學生

SELECT st_no

FROM score

WHERE su_no=‘c02’AND score<60

3.排序查詢

排序查詢是指將查詢結果按指定屬性的升序(ASC)或降序(DESC)排列，由ORDER BY子句指明。

例：查找不及格的課程，並將結果按課程號從大到小排列

SELECT UNIQUE su_no

FROM score

WHERE score<60

ORDER BY su_no DESC

4.嵌套查詢

嵌套查詢是指WHERE子句中又包含SELECT子句，它用於較複雜的跨多個基本表查詢的情況。

例：查找課程編號為c03且課程成績在80分以上的學生的學號、姓名

SELECT st_no，st_name

FROM student

WHERE stno IN (SELECT st_no

FROM score

WHERE su_no=‘c03’ AND score>80 )

這裡需要明確的是：當查詢涉及多個基本表時用嵌套查詢逐次求解層次分明，具有結構程序設計特點。在嵌套查詢中，IN是常用到的謂詞。若用戶能確切知道內層查詢返回的是單值，那麼也可用算術比較運算符表示用戶的要求。

5.計算查詢

計算查詢是指通過系統提供的特定函數(聚合函數)在語句中的直接使用而獲得某些只有經過計算才能得到的結果。常用的函數有：

COUNT(*) 計算元組的個數

COUNT(列名) 對某一列中的值計算個數

SUM(列名) 求某一列值的總和(此列值是數值型)

AVG(列名) 求某一列值的平均值(此列值是數值型)

MAX(列名) 求某一列值中的最大值

MIN(列名) 求某一列值中的最小值

例：求男學生的總人數和平均年齡

SELECT COUNT(*)，AVG(st_age)

FROM student

WHERE st_sex=‘男’

例：統計選修了課程的學生的人數

SELECT COUNT(DISTINCT st_no)

FROM score

注意：這裡一定要加入DISTINCT，因為有的學生可能選修了多門課程，但統計時只能按1人統計，所以要使用DISTINCT進行過濾。

數據更新包括數據插入、刪除和修改操作。它們分別由INSERT語句，DELETE語句及UPDATE語句完成。這些操作都可在任何基本表上進行，但在視圖上有所限制。其中，當視圖是由單個基本表導出時，可進行插入和修改操作，但不能進行刪除操作；當視圖是從多個基本表中導出時，上述三種操作都不能進行。

1.數據插入

將數據插入SQL的基本表有兩種方式：一種是單元組的插入，另一種是多元組的插入。

單元組的插入：向基本表score中插入一個成績元組(100002，c02，95)，可使用以下語句：

INSERT INTO score(st_no，su_no，score) VALUES(‘100002’，‘c02’，95)

由此，可以給出單元組的插入語句格式：

INSERT INTO表名(列名1〔，列名2〕…) VALUES(列值1〔，列值2〕…)

其中，列名序列為要插入值的列名集合，列值序列為要插入的對應值。若插入的是一個表的全部列值，則列名可以省略不寫如上面的(st_no，su_no，score)可以省去；若插入的是表的部分列值，則必須列出相應列名，此時，該關係中未列出的列名取空值。

多元組的插入：這是一種把SELECT語句查詢結果插入到某個已知的基本表中的方法。

例如：需要在表score中求出每個學生的平均成績，並保留在某個表中。此時可以先創建一個新的基本表stu_avggrade，再用INSERT語句把表score中求得的每一個學生的平均成績(用SELECT求得)插入至stu_avggrade中。

CREATE TABLE stu_avggrade

(st_no CHAR(10) NOT NULL，//定義列st_no學號，類型為10位定長字元串，非空

age_grade SMALLINT NOT NULL )// 定義列age_grade平均分，類型為短整形，非空

INSERT INTO stu_avggrade(st_no，age_grade)

SELECT st_no，AVG(score)

FROM score

GROUP BY st_no //因為要求每一個學生所有課程的平均成績，必須按學號分組進行計算。

2.數據刪除

SQL的刪除操作是指從基本表中刪除滿足WHERE<條件表達式>的記錄。如果沒有WHERE子句，則刪除表中全部記錄，但表結構依然存在。其語句格式為：

DELETE FROM表名〔WHERE 條件表達式〕

下面舉例說明：

單元組的刪除：把學號為100002的學生從表student中刪除，可用以下語句：

DELETE FROM student

WHERE st_no=‘100002’//因為學號為100002的學生在表student中只有一個，所以為單元組的刪除

多元組的刪除：學號為100002的成績從表score中刪除，可用以下語句：

DELETE FROM score

WHERE st_no=‘100002’//由於學號為100002的元組在表score中可能有多個，所以為多元組刪除

帶有子查詢的刪除操作：刪除所有不及格的學生記錄，可用以下語句

DELETE FROM student

WHERE st_no IN

(SELETE st_no

FROM score

WHERE score<60)

3.數據修改

修改語句是按SET子句中的表達式，在指定表中修改滿足條件表達式的記錄的相應列值。其語句格式如下：

UPDATE 表名 SET 列名=列改變值〔WHERE 條件表達式〕

例：把c02的課程名改為英語，可以用下列語句：

UPDATE subject

SET su_subject=‘英語’

WHERE su_no=‘c02’

例：將課程成績達到70分的學生成績，再提高10%

UPDATE score

SET score=1.1*score

WHERE score>=70

SQL的刪除語句和修改語句中的WHERE子句用法與SELECT中WHERE子句用法相同。數據的刪除和修改操作，實際上要先做SELECT查詢操作，然後再把找到的元組刪除或修改。

由於資料庫管理系統是一個多用戶系統，為了控制用戶對數據的存取權利，保持數據的共享及完全性，SQL語言提供了一系列的數據控制功能。其中，主要包括安全性控制、完整性控制、事務控制和併發控制。

1.安全性控制

數據的安全性是指保護資料庫，以防非法使用造成數據泄露和破壞。保證數據安全性的主要方法是通過對資料庫存取權力的控制來防止非法使用資料庫中的數據。即限定不同用戶操作不同的數據對象的許可權。

存取權控制包括權力的授予、檢查和撤消。權力授予和撤消命令由資料庫管理員或特定應用人員使用。系統在對資料庫操作前，先核實相應用戶是否有權在相應數據上進行所要求的操作。

(1)權力授予：權力授有資料庫管理員專用的授權和用戶可用的授權兩種形式。資料庫管理員專用授權命令格式如下：

|CONNECT |

GRANT|RESOURCE|TO 用戶名〔IDENTIFED BY 口令〕

|DBA |

其中，CONNECT表示資料庫管理員允許指定的用戶具有連接到資料庫的權力，這種授權是針對新用戶；RESOURCE表示允許用戶建立自己的新關係模式，用戶獲得CONNECT權力后，必須獲得RESOURCE權力才能創建自己的新表；DBA表示資料庫管理員將自己的特權授予指定的用戶。若要同時授予某用戶上述三種授權中的多種權力，則必須通過三個相應的GRANT命令指定。

另外，具有CONNECT和RESOURCE授權的用戶可以建立自己的表，並在自己建立的表和視圖上具有查詢、插入、修改和刪除的權力。但通常不能使用其他用戶的關係，除非能獲得其他用戶轉授給他的相應權力。

例：若允許用戶SSE連接到資料庫並可以建立他自己的關係，則可通過如下命令授予權力：

GRANT CONNECT TO SSE IDENTIFIED BY BD1928

GRANT RESOURCE TO SSE

用戶可用的授權是指用戶將自己擁有的部分或全部權力轉授給其他用戶的命令形式，其命令格式如下：

|SELECT |

|INSERT |

|DELETE |

GRANT|UPDATE(列名1[，列名2]…)|ON|表名 |TO|用戶名|〔WITH GRANT OPTION〕

|ALTER | |視圖名| |PUBLIC|

|NDEX |

|ALL |

若對某一用戶同時授予多種操作權力，則操作命令符號可用“，”相隔。

PUBLIC 表示將權力授予資料庫的所有用戶，使用時要注意：

任選項WITH GRANT OPTION表示接到授權的用戶，具有將其所得到的同時權力再轉授給其他用戶權力。

例：如果將表student的查詢權授予所有用戶，可使用以下命令：

GRANT SELECT ON student TO PUBLIC

例：若將表subject的插入及修改權力授予用戶SSE並使得他具有將這種權力轉授他人的權力，則可使用以下命令：

GRANT INSERT，UPDATE(su_subject) ON subject TO SSE WITH GRANT OPTION

這裡，UPDATE後面跟su_subject是指出其所能修改的列。

(2)權力回收：權力回收是指回收指定用戶原已授予的某些權力。與權力授予命令相匹配，權力回收也有資料庫管理員專用和用戶可用的兩種形式。

DBA專用的權力回收命令格式為：

|CONNECT |

REVOKE|RESOURCE|FROM用戶名

|DBA |

用戶可用的權力回收命令格式為：

|SELECT |

|INSERT |

|DELETE |

REVOKE|UPDATE(列名1〔，列名2〕…) |ON|表名 |FROM |用戶名|

|ALTER | |視圖名| |PUBLIC|

|INDEX |

|ALL |

例：回收用戶SSE的DBA權力：

REVOKE DBA FROM SSE

2.完整性控制

資料庫的完整性是指數據的正確性和相容性，這是資料庫理論中的重要概念。完整性控制的主要目的是防止語義上不正確的數據進入資料庫。關係系統中的完整性約束條件包括實體完整性、參照完整性和用戶定義完整性。而完整性約束條件的定義主要是通過CREATE TABLE語句中的〔CHECK〕子句來完成。另外，還有一些輔助命令可以進行數據完整性保護。如UNIQUE和NOT NULL，前者用於防止重複值進入資料庫，後者用於防止空值。

3.事務控制

事務是併發控制的基本單位，也是恢復的基本單位。在SQL中支持事務的概念。所謂事務，是用戶定義的一個操作序列(集合)，這些操作要麼都做，要麼一個都不做，是一個不可分割的整體。一個事務通常以BEGIN TRANSACTION開始，以COMMIT或ROLLBACK結束。

SQL提供了事務提交和事務撤消兩種命令：

(1)事務提交：事務提交的命令為：

COMMIT 〔WORK〕

事務提交標誌著對資料庫的某種應用操作成功地完成，所有對資料庫的操作都必須作為事務提交給系統時才有效。事務一經提交就不能撤消。

(2)事務撤消：事務撤消的命令是：

ROLLBACK 〔WORK〕

事務撤消標誌著相應事務對資料庫操作失敗，因而要撤消對資料庫的改變，即要“回滾”到相應事務開始時的狀態。

當系統非正常結束時(如掉電、系統死機)，將自動執行ROLLBACK命令