【數據庫原理】課程筆記

數據庫原理

一、數據庫系統基礎

數據模型的類型

概念數據模型：
- 概念數據模型也稱概念模型或信息模型,是對現實世界中問題域內事務(特性)的描述,是以用戶觀點實現世界的模型(圖形表示)。主要用于描述事物的概念化結構,使數據庫的設計人員在設計初期,避開計算機系統及DBMS具體技術問題,以圖形化方式表事物(實體)數據特征(屬性)及其之間的聯系等,常用的是實體-聯系模型(E-R圖)。
邏輯數據模型：
- 是數據存放的邏輯結構，是以計算機系統的觀點對數據建模。是具體的DBMS所支持的數據模型，如網狀模型、層次模型、關系模型（現在學的SQL server是關系模型）。邏輯模型面向用戶和系統，是對概念模型的進一步分次模型和細化，描述系統功能和執行順序，用于DBMS實現。
- 邏輯數據模型通常由數據結構、數據操作、完整性約束三個基本部分組成，這三個基本部分稱為數據模型的三個要素。
物理數據模型：
- 物理數據模型是對數據庫對象的描述主要包括表、視圖、字段、數據類型、長度、主鍵、外鍵、索引、默認值。概念模型中的對象轉換成物理模型的對象,如存儲位置、結構、方式、方法或索引等,邏輯模型的實現都對應物理模型。
數據庫系統的組成

數據庫、數據庫管理系統、應用系統、數據庫管理員和用戶四部分
- DBA數據庫管理員，具有全部權限

二、關系數據庫基礎

關系及關系模型的基本概念
- 關系、記錄和屬性。關系(Relation)以一個二維表(也稱數據表或表)形式表示數據的邏輯結構,將現實世界中實體(事物)及其之間的聯系歸結(轉化)為簡單的二維表關系,每個關系對應于一個二維表,其中表中的每一行代表一個記錄(或元組(Tuple)),每一列代表一個屬性(或稱數據項),每個屬性對應表中一個字段,屬性名即字段名,屬性值為各行字段值。關系的一般形式定義：
  
  ? 常用 R(D,D……,D?)表示 DㄨD,ㄨ…ㄨD。的子集域D1,D2,…,Dn。上的關系,其中R表示關系名,n是關系的目或元數(列數)。
- 域。域(Domain)是屬性具有相同數據類型值的集合。用域表示屬性的取值范圍,用DOM(A)表示屬性A的域,每個屬性都有一個域,不同屬性可以具有相同的域。
- 關系模型。關系模型(Relation Model)是以二維表結構表示的實體及其聯系關系的邏輯數據模型,可以用鍵(可唯一標識一個記錄的屬性或屬性組)關聯多個關系(二維表),每個關系對應一個二維表,如學生、選課、課程。
鍵、主鍵、外鍵

鍵也稱碼。
- 候選鍵。關系(表)中可唯一確定(標識、調用)記錄的屬性(列)或屬性組。
- 主鍵。主鍵(Primary Key)是在候選鍵中選定一個鍵作為記錄標識。通常,鍵均指主鍵,如果關系中有多個候選鍵,可取其中一個作為該關系的主鍵,如學生(學號,姓名,專業,課程編號)的主鍵可以選“學號”,也可以選(學號,姓名)。
- 外鍵。外鍵,是指若在關系R中包含另一個關系S的主鍵所對應的屬性或屬性組K,則稱K為R的外鍵(碼)。如對于學生(學號,姓名,專業,課程編號),其中的“課程編號”(“課程”關系的主鍵)就是“學生”關系的外鍵。
關系模型的表示

關系模型是對關系結構特征的描述。可形式化表示為R(U,D,Dom,F),其中R為關系名,U為組成該關系的屬性(列)名集合,D為屬性組U中屬性值的域,Dom為屬性域的映像(對應)集合,F為屬性之間數據的依賴關系集合。
通常,關系模式可以簡記為R(U)或R(A1,A2,…,An),其中R為關系名,A1,A2,…,An為屬性名。例如,學生關系的關系模式(二維表)可表示為(另一種表示形式): 學生（學號，姓名，性別，專業，出生日期）
E-R圖組成的三要素為實體、實體間的聯系和屬性

實體與二元聯系的轉換規則：
- 若實體間的聯系為一對一,則將兩個實體轉換成兩個關系模式的過程中,任選-個屬性或屬性組在其中加入另一個關系模式的鍵和聯系的屬性。
- 若實體間的聯系是一對多,則在多的一端實體的關系模式中,加上一的一端實體類型的鍵和聯系的屬性。
- 若實體間的聯系是多對多,則將聯系也轉換為關系模式,其屬性為兩端實體的鍵加上聯系的屬性,而鍵為兩端實體鍵的組合。
一對一聯系的轉換方法

?

一對多聯系的轉換方法
- 【方法一】將聯系與多的一端實體(事物)所對應的關系模式合并,并加一端實體的主鍵和聯系的屬性,組成新的關系(表)的屬性(列)。
- 【方法二】將聯系轉換為一個獨立關系模式,其屬性包含聯系自身的屬性以及相連接的兩端實體的主鍵,組成新的關系(表)的屬性(列),與多對多轉換方法相同。
多對多聯系的轉換方法
- 對于聯系是實體之間多對多的情況,每個實體可以直接轉換為關系模式,聯系則單獨轉換為一個關系模式,其屬性包括聯系自身的屬性和相連各實體的主鍵。
關系模型的完整性

完整性通常包括實體完整性、參照完整性、域完整性、用戶定義完整性。
- 域完整性：常用CHECK約束、UNIQUE約束、DEFAULT默認值…
  
  ? 域完整性(Domain Integrity),是指列的值域的完整性a回感包括數據類型、格式、值域范圍、是否允許空值等,是針對某e口口季00中00年000(一具體數據庫的約束條件,保證表中列不能輸入無效值。域完整性限制了某些屬性中的值,將屬性限制在一個有限集合。如要求身份證號是18位。常用CHECK約UNIQUE 約束、DEFAULT 默認值、NOT NULL/NULL保證列值完整性。
- 用戶定義完整性：
  
  eg：學生百分制成績取值范圍0~100. 即check(成績>=0 and 成績<=100)
關系運算的種類
- 并運算
- 差運算
- 交運算
- 廣義笛卡兒積
專門的關系運算

有選擇運算(水平過濾)、投影運算(垂直過濾字段)、連接運算和除運算四種。

三、SQL Server 2019基礎

數據庫語句的種類及功能
- 數據定義語言（DDL）：
  
  ? SQL Server的功能非常強大且其性能優異高效,其中的數據定義語言的功能包括對數據庫、表(數據表)、視圖、索引等操作對象的定義(建立)和刪除(撤銷)等,見表3-6。具體操作將陸續進行介紹。
- 數據操作語言（DML）：
  
  ? 主要功能包括插入數據insert、更新數據update、刪除數據delete、數據查詢select
- 數據控制語言（DCL）：
  
  ? 為了確保數據庫的安全,需要對用戶使用表中的數據的權限進行管理和控制。數據控制語言主要用于實現對數據庫進行安全管理和權限管理等控制,如GRAMT(賦予權限)、DENY(禁止賦予的權限)、REVOKE(收回權限)等
- 事務管理權限（TML）。x
注釋語句
- 多行注釋：/** …* */
- 單行注釋： – (兩個減號開頭)

四、數據庫、表和數據操作

數據表的建立
- primary key。主鍵約束用于唯一地標識表中的各行，主鍵約束列值不能為NULL，同時也不能與其他行的值有重復，以免造成無法唯一標識行。
- unique約束。設置該列只存放唯一（不重復）的屬性值。
- not null 與null 約束。分別確定該列值不為空或為空。
- default約束。默認約束指該列在未定義時的默認取值。
- check約束。檢查約束可以通過約束條件表達式設置其列值應當滿足的具體條件。

數據表的修改

應對數據表（結構）進行修改，可能的操作有增加新的列、刪除原有的列、修改某列的數據類型三種。

語法格式：

alter table <基本表名>-- 增加新的列add <新列名> <列數據類型> [列完整性約束]-- 刪除指定的列drop column <列名>-- 修改原有列alter column <列名> <列數據類型>
-- 刪除數據表
drop table <數據表名>

數據查詢常用操作

語法：

選擇數據 - 使用SELECT語句來選擇數據庫中的列。
SELECT 列1, 列2 FROM 表名;條件查詢 - 使用WHERE子句來過濾數據。
SELECT 列1, 列2 FROM 表名 WHERE 條件;排序數據 - 使用ORDER BY子句來對結果進行排序。
SELECT 列1, 列2 FROM 表名 ORDER BY 列1 ASC, 列2 DESC;聚合數據 - 使用聚合函數如COUNT(), SUM(), AVG(), MIN(), MAX()等來對數據進行聚合。
SELECT COUNT(列名), AVG(列名) FROM 表名 WHERE 條件;分組數據 - 使用GROUP BY子句來對數據進行分組。
SELECT 列名, COUNT(*) FROM 表名 GROUP BY 列名;連接表 - 使用JOIN語句來連接兩個或多個表。
SELECT 列1, 列2 FROM 表1 JOIN 表2 ON 表1.列名 = 表2.列名;子查詢 - 在查詢中嵌套另一個查詢。
SELECT * FROM 表名 WHERE 列名 IN (SELECT 列名 FROM 另一表 WHERE 條件);創建表 - 使用CREATE TABLE語句來創建新表。CREATE TABLE 表名 (列1 數據類型,列2 數據類型,...
);

ALL: 顯示滿足查詢條件的全部記錄，distinct選項使查詢的結果只含不同記錄，取消后面其他相同的記錄。
SELECT：首先指定要檢索的列。
FROM：然后指定數據來源，即要從哪個表或視圖中檢索數據。
WHERE：接著添加過濾條件，以限制結果集。
GROUP BY：如果需要對結果進行分組，指定分組的列。
HAVING：在分組后，添加條件以過濾分組的結果。
ORDER BY：最后，指定如何對結果集進行升序或降序排列。ASC（默認選項可不寫）選項為升序，DESC為降序（必寫）。
LIMIT（可選）：在某些數據庫系統中，用于限制返回的記錄數量。

常用的聚合函數

聚合函數	功能
COUNT(*)	計算記錄的個數
COUNT(列名)	對一列中的值計算個數
SUM(列名)	對某一列值的總和（此列必須是數值型）
AVG(列名)	對某一列值的平均值（此列必須是數值型）
MAX(列名)	對某一列值的最大值
MIN(列名)	對某一列值的最小值

模糊查詢方法

% 。用于表示任意長度的字符串
_ 。表示任意一個單字符。

-- 使用百分號(%)通配符進行模糊查詢
SELECT * FROM 表名 WHERE 列名 LIKE '模%式';-- 使用下劃線(_)通配符進行模糊查詢
SELECT * FROM 表名 WHERE 列名 LIKE '模_式';-- 組合使用通配符進行模糊查詢
SELECT * FROM 表名 WHERE 列名 LIKE '模_%式';-- 轉義通配符進行模糊查詢
SELECT * FROM 表名 WHERE 列名 LIKE '模\%式' ESCAPE '\';-- 假設數據庫默認不區分大小寫，進行模糊查詢
SELECT * FROM 表名 WHERE 列名 LIKE '模%式';-- 使用正則表達式進行模糊查詢（以MySQL為例）
SELECT * FROM 表名 WHERE 列名 REGEXP '模.*式';

數據插入的操作

插入單個數據

插入數據 - 使用INSERT INTO語句來向表中添加新數據。
INSERT INTO 表名 (列1, 列2) VALUES (值1, 值2);

數據修改的方法

語法：

更新數據 - 使用UPDATE語句來更新表中的數據。
UPDATE 表名 SET 列1 = 值1, 列2 = 值2 WHERE 條件;修改表結構 - 使用ALTER TABLE語句來修改表結構。
ALTER TABLE 表名 ADD 列名 數據類型;

數據刪除的方法

語法：

刪除數據 - 使用DELETE語句來從表中刪除數據。
DELETE FROM 表名 WHERE 條件;

五、索引及視圖

索引類似于圖書目錄,提供了指向包含特定值的數據的指針。利用該指針,可以直接定位到所要查找的相應記錄,從而提高數據庫數據查詢的速度,提升數據庫系統性能。索引技術是數據庫系統實現的重要部分。

索引的特點
- 極大地提高數據查詢的速度,這也是其最主要優點。
- 通過創建唯一性索引,可以保證數據庫中各行數據的唯一性。
- 建立在外碼上的索引可以加速多表之間的連接,有益于實現數據的參照完整性。
- 查詢涉及分組和排序時,也可顯著減少分組和排序的時間。
- 通過使用索引可以在查詢過程中使用優化隱藏器,提高系統的性能。
索引的類型

根據其索引記錄的結構和存放位置可分為聚簇索引（聚集索引）、非聚簇索引（非聚集索引）和其他索引。

聚簇索引也稱為主索引，非聚簇索引也稱為輔助索引。

索引類型簡單說明
聚簇索引
非聚簇索引
唯一索引
視圖的概念（虛表）

視圖（View）是由其他表或視圖上的查詢所定義的一種特殊表。視圖時數據庫基本表（實表）中的部分行和部分列數據的組合。它與基本表不同的是:表中的數據是物理存儲的,而數據庫中并不存儲視圖所包含的數據,這些數據仍然存在原來的基本表中。因此,視圖就像一個窗口,提供用戶以多角度觀察數據庫中的數據的一種機制。

索引類型	簡單說明
聚簇索引
非聚簇索引
唯一索引

定義視圖

語法：

create view 視圖名 [(<列名>[,<列名>]...)]
as <子查詢>
[where check option]

六、存儲過程和觸發器

存儲過程的基本概念

存儲過程是一組為了完成特定功能、可以接收和返回用戶參數的T-SQL語句預編譯集合,經過編譯后存儲在數據庫中,以某個名稱存儲并作為一個單元處理。
存儲過程的特點（優點）
- 存儲過程允許標準組件式編程
- 存儲過程在服務端運行，執行速度快
- 存儲過程能夠減少網絡流量
- 存儲過程可被作為一種安全機制充分利用
創建存儲過程

語法：
```
CREATE PROCEDURE 存儲過程名(參數列表)
AS
BEGIN-- 存儲過程體，可以包含一系列的SQL語句SQL語句1;SQL語句2;...
END;CREATE PROCEDURE 插入新記錄@姓名 NVARCHAR(50),@年齡 INT,@郵箱 NVARCHAR(100)
AS
BEGININSERT INTO 人員信息表 (姓名, 年齡, 郵箱)VALUES (@姓名, @年齡, @郵箱);
END;
```
- CREATE PROCEDURE：這是創建存儲過程的關鍵字。
- 存儲過程名：你創建的存儲過程的名稱，需要根據實際情況來命名。
- (參數列表)：存儲過程可以接收參數，參數列表中定義了參數的名稱和數據類型。如果沒有參數，則可以省略這部分。
- AS：關鍵字，用于標識存儲過程的開始。
- SQL語句1; SQL語句2; ...：存儲過程體，這里是實際執行的SQL語句序列，每條語句以分號結束。
- END;：標識存儲過程的結束
觸發器概述

觸發器(Trigger)是一種特殊的存儲過程,它的執行不是由程序調用,也不是手工啟動,而是由事件來觸發,例如當對一個表進行操作(INSERT、DELETE、UPDATE)時就會激活觸發器的執行。
觸發器的優勢
- 預編譯、已優化、效率較高,避免了SQL語句在網絡傳輸后再解釋的低效率。
- 可以重復使用,減少開發人員的工作量。若用SQL語句,使用一次就得編寫一次。
- 業務邏輯封裝性好,數據庫中很多問題都可在程序代碼中實現,但是將其分離出來在數據庫中處理,使邏輯上更清晰,對于后期維護和二次開發的作用比較明顯。
- 更加安全。不會有SQL語句注入問題。

七、T-SQL應用編程

批處理的結束與退出
- 執行批處理語句
  
  用EXECUTE語句可執行標量值得用戶自定義函數、系統過程、用戶自定義存儲過程等，同時還支持T-SQL批處理內字符串得執行。
- 批處理結束語句
```
GO [count]
```
- 批處理退出語句
```
RETURN [整型表達式]
```
順序結構
- begin … end 語句
- set語句
- select語句
- print語句
選擇結構
- if …else語句
- case語句
循環結構
- where語句

八、數據庫系統安全

數據備份

備份方法：
- 完整備份：指轉存數據庫全部數據，通過單個完整備份，將數據庫恢復到某個時間點的狀態。
- 完整差異備份
- 部分備份
- 部分差異備份
- 文件和文件組備份
- 文件差異備份
- 事務日志備份
事務及其基本特性

事務(Transaction)是數據庫系統的一個操作或處理的基本單位(最小單位),是用戶定義的一組操作序列，通常是一行 SQL Server 語句,或是一個獨立完整增、刪、改等功能的操作。并發控制以事務為單位,一個事務可以是一組(或條)SQL語句或一個模塊程序。事務的開始或結束都可以由用戶顯式控制,若用戶無顯式地定義事務,則由數據庫系統按默認規定自動劃分事務。對事務的操作實行“要么都做,要么都不做”的原則,將事務作為一個不可分割的基本工作單位。

事務的基本特性：
- 原子性
- 一致性
- 隔離性
- 持久性：一旦十五完成，其結果不變，包括系統關閉，都需要保證事務結果。即使系統崩潰，一個提交的事務仍然存在。在事務完成后，數據庫日志便記錄所有對數據的更新或查詢等。
并發控制的概念

數據庫的并發控制是對多用戶程序同時并行存取或處理的控制機制,目的是避免數據的丟失修改、無效數據的讀取與不可重復讀數據現象的發生,從而保持數據的一致性。

事務是數據并發控制的基本單位，是用戶定義的一組操作序列。
并發控制需要處理的問題
- 更新丟失
- 讀臟數據
- 不可重復讀
- 幻讀
常用的技術封鎖

采取并發控制的目的是以有效的方式控制調度并發操作,使每個用戶事務的執行不受其他事務干擾,從而避免造成數據的不一致性。封鎖是并發控制最常用的技術,其基本單位是事務。

九、關系數據庫的規范化

關系模式的范式
- 第一范式（1NF）
  
  若關系模式R的每個關系r的屬性值都是不可分的原子值，則稱R是第一范式1NF的模式。
- 第二范式（2NF）
  
  若A是關系模式R中候選鍵屬性，則稱A是R的主屬性；否則稱A是R的非主屬性。
- 第三范式（3NF）
  
  若關系模式R是1NF,且每個非主屬性都不傳遞依賴于R的候選鍵,則稱R是第三范式(3NF)的模式。若數據庫模式中每個關系模式都是3NF,則稱其為3NF的數據庫模式。

十、數據庫系統設計

概念結構設計的任務

概念結構設計的任務是將需求分析中業務(實體)數據處理等實際需求,抽象為信息結構(概念模型E-R圖),是現實世界(事務)到機器世界(數據及處理)轉換(業務處理轉化為數據處理)的一個重要階段,也是整個數據庫系統設計的關鍵。

概念概念結構設計通常將現實世界中的客觀事物(實體),先抽象為不依賴 DBMS支持的數據模型(E-R圖),概念模型是各種數據模型的共同基礎,便于進行具體描述和直觀表示。
概念結構設計的四個特點
- 直觀易于理解,利用概念模型E-R圖便于研發人員和需求用戶直接交換意見用戶的積極參與是數據庫系統設計成功的關鍵。
- 能夠真實且充分地描述現實世界的具體事物(特征),包括事物及其之間的聯系可以滿足用戶對數據的處理要求,是對現實世界(業務應用)的一個真實直觀模型。
- 易于擴充修改完善,當應用環境和業務需求改變時,方便對概念模型擴充修改完善。
- 便于向關系、網狀、層次等各種具體數據模型轉換。
全局E-R圖模型設計

E-R圖合并中的沖突有屬性沖突、命名沖突、結構沖突三種。
- 屬性沖突：屬性值的類型、取值范圍/單位或值域不同等不一致。
- 命名沖突：在實體名、屬性名或聯系名之間出現命名不一致,通常屬性的命名沖突最常見,主要是同名異義(含義)或異名同義,解決方法常用協商調整等方式。
- 結構沖突：主要共分三種:一是同一對象在不同應用中抽象不同,常將屬性變換為實體或將實體變換為屬性;二是同一實體在不同局部E-R圖中所包含屬性不全相同,或屬性排列次序不全相同。可將該實體的屬性取各局部E-R圖中屬性的并集,再適當設計屬性的次序;三是實體之間的聯系在不同局部E-R圖中分類不同,應當根據應用語義對實體聯系的類型綜合或調整。
邏輯結構設計

概念結構設計面向用戶得到的E-R模型,獨立于具體DBMS。采用計算機處理E-R模型中的數據(事物特征),還必須將其轉化為具體DBMS能處理的數據模型,即邏輯緒構設計。

邏輯結構設計的任務：
- 邏輯結構設計的任務是將概念結構設計得到的概念數據庫模式轉換成邏輯數據庫模式,即將E-R圖轉換成與選定的DBMS所支持的數據模型相符合的邏輯結構。
邏輯結構設計的任務步驟：
- 將概念結構(E-R圖)轉化為關系模型(或其他模型)。
- 將關系模型轉換為特定DBMS支持下的數據模型(數據表)。
- 對數據模型按照規范化要求進行具體優化和完善。
評價物理結構

在物理結構設計完成后,需要進行評審,重點是時空效率。主要權衡系統的時空效率、維護問題和用戶需求,對多種設計方案進行其體評審和考量,可以得到多種方案,對這些方案經過認真評審選出較優方案。對數據庫物理結構的評審主要借助所選用的DBMS.具體考核指標包括如下。
- 查詢和響應時間。高質量應用程序設計較少占用CPU時間和I/O時間
- 更新事務的消耗包括修改索引、重寫物理塊或文件、寫校驗等消耗。
- 生成報告的損耗，主要包括索引、重組、排序、結果顯示的損耗。
- 主在儲空間的損耗。包括程序和數據占用空間。可對緩沖區個數及大小做適當控制以減小損耗。
- 輔助存儲空間的損耗。如數據塊和索引塊占用空間可對索引塊大小及充滿度做適當控制減小。
概念結構設計

概念結構設計是整個數據庫系統設計的關鍵,通過對需求進行綜合、歸納與抽象分析,形成獨立于具體DBMS的概念模型。

概念結構(E-R圖)轉化為關系模型(或其他模型)。

將關系模型轉換為特定DBMS支持下的數據模型(數據表)。
對數據模型按照規范化要求進行具體優化和完善。

評價物理結構

在物理結構設計完成后,需要進行評審,重點是時空效率。主要權衡系統的時空效率、維護問題和用戶需求,對多種設計方案進行其體評審和考量,可以得到多種方案,對這些方案經過認真評審選出較優方案。對數據庫物理結構的評審主要借助所選用的DBMS.具體考核指標包括如下。
- 查詢和響應時間。高質量應用程序設計較少占用CPU時間和I/O時間
- 更新事務的消耗包括修改索引、重寫物理塊或文件、寫校驗等消耗。
- 生成報告的損耗，主要包括索引、重組、排序、結果顯示的損耗。
- 主在儲空間的損耗。包括程序和數據占用空間。可對緩沖區個數及大小做適當控制以減小損耗。
- 輔助存儲空間的損耗。如數據塊和索引塊占用空間可對索引塊大小及充滿度做適當控制減小。
概念結構設計

概念結構設計是整個數據庫系統設計的關鍵,通過對需求進行綜合、歸納與抽象分析,形成獨立于具體DBMS的概念模型。