計算思維與計算機導(dǎo)論 課件 09-數(shù)據(jù)庫技術(shù)

8數(shù)據(jù)庫技術(shù)

天津科技大學(xué)

計算機基礎(chǔ)與信息技術(shù)教研部1目錄9.1數(shù)據(jù)庫概述9.2關(guān)系數(shù)據(jù)庫 9.3SQLite簡介9.4SQL9.5視圖 9.1.1數(shù)據(jù)庫體系結(jié)構(gòu)數(shù)據(jù)庫（DataBase，DB）是指長期存儲在計算機內(nèi)、有組織的、統(tǒng)一管理的相關(guān)數(shù)據(jù)的集合。數(shù)據(jù)庫管理系統(tǒng)數(shù)據(jù)庫管理系統(tǒng)（DBMS）是用于建立、使用、管理和維護數(shù)據(jù)庫的系統(tǒng)軟件，是數(shù)據(jù)庫系統(tǒng)的核心組成部分。大型：Oracle、Sybase、SQLServer、DB2等中小型數(shù)據(jù)庫管理系統(tǒng)有SQLite、MySQL、Access等國產(chǎn)自主研發(fā)：螞蟻集團OceanBase、華為GaussDB等數(shù)據(jù)庫管理系統(tǒng)數(shù)據(jù)庫管理系統(tǒng)具有以下主要功能：（1）數(shù)據(jù)定義：定義數(shù)據(jù)庫的各種對象。（2）數(shù)據(jù)處理：數(shù)據(jù)的基本操作（3）數(shù)據(jù)庫的控制和管理：實現(xiàn)對數(shù)據(jù)庫的控制和管理，確保數(shù)據(jù)正確有效和數(shù)據(jù)庫系統(tǒng)的正常運行。括數(shù)據(jù)的并發(fā)性控制、完整性控制、安全性控制和數(shù)據(jù)庫的恢復(fù)（4）數(shù)據(jù)庫的建立和維護：數(shù)據(jù)庫的建立包括數(shù)據(jù)庫初始數(shù)據(jù)的輸入、轉(zhuǎn)換等數(shù)據(jù)庫的維護包括數(shù)據(jù)庫的轉(zhuǎn)儲、恢復(fù)、重組織與重構(gòu)造、性能監(jiān)視與分析等數(shù)據(jù)庫系統(tǒng)（DBS）數(shù)據(jù)庫系統(tǒng)（DataBaseSystem，DBS）是指帶有數(shù)據(jù)庫并利用數(shù)據(jù)庫技術(shù)進行數(shù)據(jù)管理的計算機系統(tǒng)。數(shù)據(jù)庫系統(tǒng)的軟件（1）數(shù)據(jù)庫管理系統(tǒng)（2）操作系統(tǒng)（3）應(yīng)用系統(tǒng)（4）應(yīng)用開發(fā)工具：編程語言：C、Python、Java、PHP、Delphi、VB數(shù)據(jù)庫工具：SQLiteStudio等用戶用戶主要包括以下幾類。（1）終端用戶（2）應(yīng)用程序員（3）數(shù)據(jù)庫管理員（DataBaseAdministrator，DBA）數(shù)據(jù)庫中存儲和管理的數(shù)據(jù)都來源于現(xiàn)實世界的客觀事物，計算機不能直接處理這些具體事物?，F(xiàn)實世界信息世界

機器世界抽象（概念模型）抽象（數(shù)據(jù)模型）9.1.2概念模型9.1.2概念模型人們經(jīng)常使用實體聯(lián)系模型來表示概念模型。1．實體客觀存在并且可以相互區(qū)別的事物稱為實體。實體可以是具體的人、事、物。如一名學(xué)生、一本書、一門課程等；也可以是事件，如學(xué)生的一次選課、一場比賽、一次借書等。概念模型2．實體的屬性實體所具有的某一特性稱為屬性。如學(xué)生實體有學(xué)號、姓名、性別、出生日期、專業(yè)等多個屬性。屬性包括屬性名和屬性值，如：學(xué)號、姓名、性別、出生日期、專業(yè)等為屬性名，（13011103、許志華、男、06/12/1995、機械工程）為某個學(xué)生實體的屬性值。概念模型3．實體型用實體名及其屬性名來抽象描述同一類實體，稱為實體型。如：學(xué)生（學(xué)號、姓名、性別、出生日期、專業(yè)）就是一個實體型，它描述的是學(xué)生這一類實體。概念模型4．實體集同類型實體的集合稱為實體集。如：全體學(xué)生就是一個實體集而（13011103、許志華、男、06/12/1995、機械工程）是這個實體集中的一個實體。概念模型5．實體間的聯(lián)系實體間的聯(lián)系通常是指兩個實體集之間的聯(lián)系。聯(lián)系有以下3種類型：（1）一對一聯(lián)系（1：1）（2）一對多聯(lián)系（1：n）（3）多對多聯(lián)系（m：n）概念模型一對一聯(lián)系（1:1） 如果對于實體集A中的每一個實體，實體集B中至多有一個實體與之對應(yīng)；反之亦然，則稱A與B具有一對一聯(lián)系。例如：一個班級只有一個班長，而一個班長只能在一個班級任職。班級和班長之間具有一對一的聯(lián)系概念模型一對多聯(lián)系（1:n） 如果對于實體集A中的每一個實體，實體集B中有n個實體（n≥0）與之對應(yīng)；反之，對于實體集B中的每一個實體,實體集A中至多只有一個實體與之對應(yīng)，則稱A與B具有一對多聯(lián)系。例如：一個班級有多個學(xué)生，而每個學(xué)生只在一個班級中學(xué)習(xí)，則班級與學(xué)生之間具有一對多的聯(lián)系。概念模型多對多聯(lián)系（m:n） 如果對于實體集A中的每一個實體，實體集B中有n個實體（n≥0）與之對應(yīng)；反之，對于實體集B中的每一個實體,實體集A中也有m個實體（m≥0）與之對應(yīng)，則稱A與B具有多對多聯(lián)系。例如：一個學(xué)生可以選多門課，一門課也可以被多個學(xué)生選擇課程與學(xué)生之間具有多對多的聯(lián)系。概念模型6．E-R（Entity-Relationship實體-聯(lián)系）圖

包含實體、屬性和聯(lián)系E-R圖（1）實體：用矩形框表示（2）屬性：用橢圓形框表示，并用無向邊將其與對應(yīng)實體連接起來。（3）聯(lián)系：用菱形框表示，框內(nèi)寫明聯(lián)系名，并用無向邊分別與有關(guān)實體連接起來，同時在無向邊旁標注聯(lián)系的類型（1：1，1：n或m：n）。關(guān)系模型數(shù)據(jù)模型是用來抽象和表示現(xiàn)實世界中事物與事物之間聯(lián)系的結(jié)構(gòu)模式。用二維表結(jié)構(gòu)表示實體及實體間聯(lián)系的數(shù)據(jù)模型稱為關(guān)系模型。9.2關(guān)系數(shù)據(jù)庫關(guān)系數(shù)據(jù)庫是基于關(guān)系模型的數(shù)據(jù)庫。在關(guān)系數(shù)據(jù)庫中，數(shù)據(jù)存儲在二維結(jié)構(gòu)的表中，而一個關(guān)系數(shù)據(jù)庫中，包含多個數(shù)據(jù)表。關(guān)系術(shù)語（1）關(guān)系： 一個關(guān)系就是一張二維表，每個關(guān)系有一個關(guān)系名。如：學(xué)生（2）元組： 表中的一行即為一個元組，也稱為記錄。如一行（3）屬性：表中的一列即為一個屬性，也稱字段。如：學(xué)號、姓名、性別、出生日期、專業(yè)、生源地、民族、政治面貌、入學(xué)成績關(guān)系術(shù)語（4）域：屬性的取值范圍。性別屬性的域為文本（男，女）入學(xué)成績屬性的域為0～600的數(shù)字關(guān)系術(shù)語（5）關(guān)鍵字關(guān)系中能唯一標識元組的一個或一組屬性稱為關(guān)鍵字。如：學(xué)號（6）主關(guān)鍵字主關(guān)鍵字是在關(guān)系中指定的唯一標識元組的關(guān)鍵字。一個關(guān)系中只能有一個主關(guān)鍵字。如：學(xué)號是主關(guān)鍵字。關(guān)系術(shù)語（7）外部關(guān)鍵字：如果一個關(guān)系R中的某個屬性不是本關(guān)系的主關(guān)鍵字，而是另一個關(guān)系S的主關(guān)鍵字，則稱該屬性為本關(guān)系R的外部關(guān)鍵字，R為參照關(guān)系，S為被參照關(guān)系。課程號是課程表的主關(guān)鍵字，學(xué)號是表學(xué)生表的主關(guān)鍵字，課程號和學(xué)號都是成績表的外部關(guān)鍵字關(guān)系術(shù)語（7）關(guān)系模式對關(guān)系的描述，它對應(yīng)一個關(guān)系的結(jié)構(gòu)。其格式為：課程表的關(guān)系模式為：關(guān)系名（屬性1，屬性2，…，屬性n）課程（課程號，課程名，課時，學(xué)分）關(guān)系完整性關(guān)系模型的完整性規(guī)則用于定義和保護表內(nèi)部或表之間的數(shù)據(jù)關(guān)系，是對關(guān)系的某種約束條件。包括：實體完整性參照完整性用戶定義完整性。關(guān)系完整性（1）實體完整性實體完整性也稱行完整性。實體完整性規(guī)定：關(guān)系中所有元組的主關(guān)鍵字值不能為空值。

如：

學(xué)生表中學(xué)號為主關(guān)鍵字，所有學(xué)生的學(xué)號不能為空關(guān)系完整性（2）參照完整性參照完整性規(guī)定：若一個關(guān)系R的外部關(guān)鍵字F是另一個關(guān)系S的主關(guān)鍵字，則R中的每一個元組在F上的值必須是S中某一元組的主關(guān)鍵字的值，或者取空值。成績表中課程號是外部關(guān)鍵字課程號是課程表的主關(guān)鍵字成績表中的所有課程號都必須是課程信息表中的某個課程號。關(guān)系完整性（3）用戶自定義完整性

用戶定義完整性是針對某一具體關(guān)系的約束條件，它反映某一具體應(yīng)用所涉及的數(shù)據(jù)必須滿足的語義要求。例如：成績表，成績應(yīng)為數(shù)值型數(shù)據(jù)，取值范圍為0～100學(xué)生表，性別為字符型數(shù)據(jù)，取值范圍為（男，女）9.3SQLite簡介SQLite是部署廣泛的SQL數(shù)據(jù)庫引擎之一SQLite使用嵌入式SQL數(shù)據(jù)庫引擎，各引擎不與程序進程進行獨立通信，而是被集成在用戶程序中SQLite被廣泛應(yīng)用于消費電子、醫(yī)療、工業(yè)控制、軍事等各領(lǐng)域。SQLite安裝下載：sqlite-dll-win64-x64-3400100.zip工具包sqlite-tools-win32-x86-3400100.zip解壓縮到一個文件夾下，如D:\SQLiteSQLite在msdos命令提示符下，切換到D:\SQLite到輸入命令sqlite3，進入“SQLite數(shù)據(jù)庫管理系統(tǒng)”。此時，可以進行數(shù)據(jù)庫和數(shù)據(jù)操作。9.3.2SQLiteStudioSQLiteStudio是SQLite數(shù)據(jù)庫的可視化工具，軟件內(nèi)部集成了SQLite數(shù)據(jù)庫創(chuàng)建數(shù)據(jù)庫執(zhí)行“數(shù)據(jù)庫→添加數(shù)據(jù)庫”命令或者單擊“添加數(shù)據(jù)庫”按鈕創(chuàng)建表在數(shù)據(jù)庫中，表是整個數(shù)據(jù)庫系統(tǒng)的基礎(chǔ)，所有的原始數(shù)據(jù)都存儲在表中創(chuàng)建表的工作包括：創(chuàng)建字段字段命名定義字段的數(shù)據(jù)類型設(shè)置字段屬性創(chuàng)建表之前先要完成數(shù)據(jù)庫的連接創(chuàng)建表執(zhí)行“結(jié)構(gòu)→新建表”命令或者單擊“連接到數(shù)據(jù)庫”按鈕，填寫“表名”。表中至少要有一個列創(chuàng)建表添加列：單擊“添加列

”按鈕，打開“列”對話框設(shè)定：列的名稱數(shù)據(jù)類型約束數(shù)據(jù)類型包括①Text：文本②Integer：整數(shù)值③Real：浮點數(shù)④Blob：以二進制進行存儲，任意數(shù)據(jù)類型⑤Numeric：親和類型，在插入文本數(shù)據(jù)時，自動轉(zhuǎn)換為Integer或者是Real類型數(shù)據(jù)，⑥Null：表示Null值，表示缺失信息的占位，可以根據(jù)輸入數(shù)據(jù)的類型決定數(shù)據(jù)類型。數(shù)據(jù)類型數(shù)據(jù)表中某一列可能包含多種數(shù)據(jù)類型，它們也可以排序。SQLite數(shù)據(jù)庫根據(jù)數(shù)據(jù)親緣性與其他系統(tǒng)或數(shù)據(jù)庫中的數(shù)據(jù)進行類型轉(zhuǎn)換數(shù)據(jù)類型布爾值被存儲為整數(shù)0（false）和1（true）Date與Time沒有單獨的存儲類型，轉(zhuǎn)換為Text、Real或者Integer存儲。列的約束（1）主鍵：主鍵用于保證數(shù)據(jù)實體的完整性，主鍵可以是單列，也可以是多列的組合。一個表只能定義一個主鍵，不允許重復(fù)、不允許空值列的約束（2）外鍵：外鍵用于保證數(shù)據(jù)的參照完整性，即相關(guān)表中數(shù)據(jù)的一致性。外鍵引用其他表中主鍵字段，說明表與表之間的關(guān)系。學(xué)號為“學(xué)生”表的主鍵，學(xué)號為“選課”表的外鍵列的約束在更新或刪除主鍵時，可能會破壞參照完整性。處理：“RESTRICT（限制）：則會報錯，不執(zhí)行改數(shù)據(jù)操作；“CASCATE（級聯(lián)）”：將外鍵一起更新或刪除。列的約束【例9.1】將“選課”表的“學(xué)號”字段設(shè)置為“學(xué)生”表中“學(xué)號”主鍵的外鍵，并限制更新操作、級聯(lián)刪除操作。雙擊“選課”表的“學(xué)號”字段，選中“外鍵”，配置外鍵。此時刪除、更新主鍵，會影響到外鍵。列的約束（3）唯一：確保該列中的所有值都不重復(fù)。（4）條件：條件約束用于保證用戶定義完整性，列的所有值滿足一定條件。如：成績字段的值大于等于0且小于等于100列的約束【例9.2】為“學(xué)生”表增加一個“電話號碼”新的列，該列的長度必須為11位。增加一列，電話號碼，設(shè)定條件列的約束SQLite有常用函數(shù)列的約束【例9.3】將“選課”表中的成績值限制在0～100分。索引索引是在數(shù)據(jù)表中的列上建立的一種數(shù)據(jù)庫對象，它保存著列的排序，并記錄索引列在表中的物理存儲位置，從而實現(xiàn)快速查找數(shù)據(jù)，提高查詢的效率。索引在“教學(xué)管理”數(shù)據(jù)庫中，學(xué)生表的索引，可以創(chuàng)建、編輯和刪除索引。9.3.5修改表結(jié)構(gòu)可以根據(jù)需要修改表的結(jié)構(gòu)，設(shè)置字段類型、約束等?！纠?.4】將學(xué)生表中“出生日期”的數(shù)據(jù)類型從原來的“Text”類型更改為“Integer”類型。選中“出生日期”字段，單擊“編輯列”按鈕9.3.6數(shù)據(jù)記錄操作在數(shù)據(jù)表的維護過程中，可以:增加記錄刪除記錄修改記錄數(shù)據(jù)記錄操作1．增加記錄【例9.5】向?qū)W生表添加一條學(xué)生信息。打開學(xué)生表，單擊“數(shù)據(jù)”選項卡，單擊“插入行”按鈕，依次填寫數(shù)據(jù)記錄操作2．修改記錄雙擊數(shù)據(jù)表中的字段，該字段變?yōu)榭删庉嫚顟B(tài)，可以直接修改內(nèi)容。3．刪除記錄選中數(shù)據(jù)行，單擊“刪除行”按鈕，即可刪除記錄。9.4.1SQL簡介SQL（StructuredQueryLanguage，結(jié)構(gòu)化查詢語言）是一種通用的且功能強大的關(guān)系數(shù)據(jù)庫語言，也是關(guān)系數(shù)據(jù)庫的標準語言。包括：（1）數(shù)據(jù)定義語言（DataDefinitionLanguage，DDL），（2）數(shù)據(jù)處理語言（DataManipulationLanguage，DML）（3）數(shù)據(jù)控制語言（DataControlLanguage，DCL）常量SQL中常量的表示方法（1）數(shù)字型常量：25、-25、12.4。（2）文本型常量：西文的單/雙撇號括起來

如

'英語'和"英語"（3）二進制常量：直接以二進制進行存儲。算術(shù)運算符算術(shù)運算符用于進行算術(shù)運算關(guān)系運算符用于比較兩個操作數(shù)的關(guān)系邏輯運算符邏輯運算符用于對操作數(shù)進行邏輯運算函數(shù)函數(shù)是一種能夠完成某種特定操作或功能的數(shù)據(jù)形式，函數(shù)的返回值稱為函數(shù)值。函數(shù)調(diào)用的格式：函數(shù)名([參數(shù)1][,參數(shù)2][,…])。max(12,34,56,38,30)運行SQL語句SQLiteStudio中，使用查詢編輯器編寫和運行SQL語句①執(zhí)行菜單“工具→打開SQL編輯器”命令或者單擊“打開SQL編輯器

”按鈕②單擊上方的“執(zhí)行語句”按鈕，執(zhí)行SQL語句9.4.2CREATETABLE使用CREATETABLE語句定義表。語法格式為：CREATETABLE<表名>

(<字段名l><數(shù)據(jù)類型1>[(<大小>)][NOTNULL][PRIMARYKEY|UNIQUE]

,<字段名2><數(shù)據(jù)類型2>[(<大小>)][NOTNULL][PRIMARYKEY|UNIQUE]

[,…])（1）數(shù)據(jù)類型：用字符表示。（2）PRIMARYKEY：字段主鍵為主鍵。（3）UNIQUE：字段的唯一約束，不允許出現(xiàn)重復(fù)值。（4）NOTNULL：字段不允許為空。CREATETABLE【例9.6】使用SQL語句定義一個名為STUDENT的表。學(xué)號（文本型，8字符）、姓名（文本型，20字符）、性別（文本型，2字符）、出生日期（文本）、貸款否（整型）、簡歷（文本）、照片（BLOB），學(xué)號為主鍵，姓名不允許為空值。CREATETABLESTUDENT(學(xué)號TEXT(8)PRIMARYKEY,姓名TEXT(20)NOTNULL,性別TEXT(2),

出生日期

TEXT,貸款否INT,簡歷TEXT,照片BLOB)CREATETABLE9.4.3INSERTINTO使用INSERTINTO語句插入記錄，語法格式為：INSERTINTO<表名>[(<字段名1>[,<字段名2>[，…]])]VALUES(<表達式1>[,<表達式2>[,…]])如果INTO后缺少字段名列表，則必須為該表的所有字段賦值，且各項數(shù)據(jù)和表中的字段順序一一對應(yīng)。提示執(zhí)行完插入、更新、刪除等操作，單擊“刷新表數(shù)據(jù)

”按鈕，將顯示執(zhí)行完SQL語句后的數(shù)據(jù)表。INSERTINTO【例9.7】使用SQL語句向STUDENT表中插入兩條學(xué)生記錄。INSERTINTOSTUDENTVALUES("22801109","朱曉","女","2004/9/26","是",null,null)INSERTINTOSTUDENT(學(xué)號,姓名,性別)VALUES("22801110","彭宇","男")9.4.4UPDATE使用UPDATE語句更新記錄，語法格式為：UPDATA<表名>SET<字段名1>=<表達式1>[,<字段名2>=<表達式2>[，…]][WHERE<條件>]如果不帶WHERE子句，則更新表中所有的記錄；如果帶WHERE子句，則只更新表中滿足條件的記錄。UPDATE【例9.8】使用SQL語句將“學(xué)生”表中所有女生的“貸款否”字段改為“否”。UPDATE學(xué)生SET貸款否="否"WHERE性別="女"9.4.5DELETE使用DELETE語句刪除記錄，其語法格式為：DELETEFROM<表名>[WHERE<條件>]如果不帶WHERE子句，則刪除表中所有的記錄；如果帶WHERE子句，則只刪除表中滿足條件的記錄。DELETE【例9.9】使用SQL語句刪除“學(xué)生”表中學(xué)號為“22801110”的學(xué)生記錄。DELETEFROM學(xué)生WHERE學(xué)號="22801110"9.4.6SELECT語句SELECT語句從數(shù)據(jù)庫中選取數(shù)據(jù)，其語法格式為：SELECT[ALL|DISTINCT][TOP<數(shù)值>[PERCENT]]<目標列>[[AS]<列標題>]FROM<表或查詢1>[[AS]<別名1>],<表或查詢2>[[AS]<別名2>][WHERE<連接條件>AND<篩選條件>][GROUPBY<分組項>[HAVING<分組篩選條件>]][ORDERBY<排序項>[ASC|DESC]][LIMITN]關(guān)系運算是一種抽象的查詢語言，它用對關(guān)系的運算來表達查詢，包括：投影選擇連接1．單表查詢單表查詢僅涉及從一個表中的查詢數(shù)據(jù)。（1）投影在關(guān)系運算中，從關(guān)系中挑選若干屬性組成新的關(guān)系稱為投影。SELECT[DISTINCT]<目標列1>[,<目標列2>[，…]]

FROM<表或查詢>①查詢指定的字段：指定要查詢的各字段名。②查詢所有的字段：在目標列中使用“*”。③消除重復(fù)的記錄：在字段名前加上DISTINCT④查詢計算的值。投影【例9.10】查詢“學(xué)生”表中所有學(xué)生的姓名、性別和出生日期。SELECT姓名,性別,出生日期FROM學(xué)生（2）選擇在關(guān)系運算中，從關(guān)系中找出滿足給定條件的那些元組稱為選擇。條件是邏輯表達式，值為真的元組將被選取。SELECT<目標列>FROM<表名>WHERE<條件>選擇【例9.11】查詢“選課”表中成績在80～90分的記錄。SELECT*FROM選課WHERE成績BETWEEN80AND90選擇【例9.12】查詢“學(xué)生”表中出生日期在“2004/1/1”之后的女生。SELECT*FROM學(xué)生WHERE出生日期>="2004/1/1"and性別="女"（3）排序排序使用ORDERBY子句對查詢結(jié)果按照一個或多個列的升序（ASC）或降序（DESC）排列，默認是升序?！纠?.13】查詢成績在50～90分的記錄，同一門課程按成績降序排。SELECT*FROM選課WHERE成績BETWEEN50AND90ORDERBY課程號,成績DESC排序使用LIMIT子句可以選出排在前面的若干記錄。【例9.14】查詢“選課”表中成績排在前5名的記錄。SELECT*FROM選課ORDERBY成績DESCLIMIT5（4）分組查詢使用GROUPBY子句可以對查詢結(jié)果按照某一列的值分組分組查詢通常與SQL聚合函數(shù)一起使用，先按指定的字段分組，再對各組進行合計。如果未分組，則聚合函數(shù)作用于整個查詢結(jié)果。分組查詢【例9.15】統(tǒng)計“學(xué)生”表中各系的學(xué)生人數(shù)。SELECT系部,COUNT(*)AS各系人數(shù)FROM學(xué)生GROUP

BY系部2．多表查詢多表查詢指的是從多個表中查詢數(shù)據(jù)。（1）笛卡兒積笛卡兒積是指在數(shù)學(xué)中，集合A的元素與集合B的元素的所有可能有序?qū)Φ募?，表示為A×B。在關(guān)系運算中，笛卡兒積指的是X關(guān)系的每一條記錄和Y關(guān)系的每一條記錄連接構(gòu)成的新關(guān)系。多表查詢【例9.16】查詢“學(xué)生”表和“選課”表的笛卡兒積。SELECT*FROM學(xué)生,選課“學(xué)生”表中有20條記錄，“選課”表有8條記錄，查詢結(jié)果將有160條記錄。（2）連接運算在關(guān)系運算中，連接運算是從兩個關(guān)系的笛卡兒積中選擇屬性間滿足一定條件的元組。條件放在WHERE子句中，語法格式為：SELECT<目標列>FROM<表名1>,<表名2>WHERE<表名1