數(shù)據(jù)庫(kù)系統(tǒng)原理與設(shè)計(jì)實(shí)驗(yàn)教程 第4版 課件 第3章 數(shù)據(jù)庫(kù)定義與更新

第1頁(yè)第3章數(shù)據(jù)庫(kù)定義與更新數(shù)據(jù)庫(kù)系統(tǒng)原理實(shí)驗(yàn)教程第4版3.1相關(guān)知識(shí)3.1.1數(shù)據(jù)庫(kù)定義語(yǔ)句3.1.2表定義語(yǔ)句3.1.3索引與視圖定義語(yǔ)句3.1.4表記錄更新語(yǔ)句3.2實(shí)驗(yàn)七數(shù)據(jù)庫(kù)與數(shù)據(jù)表定義3.2.1實(shí)驗(yàn)?zāi)康呐c要求3.2.2實(shí)驗(yàn)案例3.2.3實(shí)驗(yàn)內(nèi)容3.3實(shí)驗(yàn)八索引與視圖定義3.3.1實(shí)驗(yàn)?zāi)康呐c要求3.3.2實(shí)驗(yàn)案例3.3.3實(shí)驗(yàn)內(nèi)容3.4實(shí)驗(yàn)九數(shù)據(jù)更新操作3.4.1實(shí)驗(yàn)?zāi)康呐c要求3.4.2實(shí)驗(yàn)案例3.4.3實(shí)驗(yàn)內(nèi)容目錄第3頁(yè)

3.1相關(guān)知識(shí)在SQLServer2019中，數(shù)據(jù)庫(kù)對(duì)象包括表、視圖、觸發(fā)器、存儲(chǔ)過(guò)程、規(guī)則、缺省、用戶自定義的數(shù)據(jù)類型等。SQLServer的DDL是指用來(lái)定義和管理數(shù)據(jù)庫(kù)以及數(shù)據(jù)庫(kù)中的各種對(duì)象的語(yǔ)句，這些語(yǔ)句包括CREATE、ALTER和DROP等語(yǔ)句。SQLServer的DML是指用來(lái)查詢、添加、修改和刪除數(shù)據(jù)庫(kù)中數(shù)據(jù)的語(yǔ)句，包括SELECT、INSERT、UPDATE、DELETE等。第4頁(yè)3.1.1數(shù)據(jù)庫(kù)定義語(yǔ)句1.創(chuàng)建數(shù)據(jù)庫(kù)語(yǔ)法：CREATEDATABASEdatabase_name

[ON[PRIMARY]]

([NAME=logical_file_name,]

FILENAME='os_file_name'

[,SIZE=size]

[,MAXSIZE={max_size|UNLIMITED}]

[,FILEGROWTH=growth_increment])[,...n])

[LOGON]([NAME=logical_file_name,]

FILENAME='os_file_name'

[,SIZE=size]

[,MAXSIZE={max_size|UNLIMITED}]

[,FILEGROWTH=growth_increment])[,...n])第5頁(yè)其中：?database_name：被創(chuàng)建的數(shù)據(jù)庫(kù)的名字。?ON：用于指定存儲(chǔ)數(shù)據(jù)庫(kù)中數(shù)據(jù)的磁盤(pán)文件，除PRIMARY文件組外，用戶可定義用戶的文件組及相關(guān)的用戶文件。?PRIMARY：描述在主文件組中定義的相關(guān)文件，所有的數(shù)據(jù)庫(kù)系統(tǒng)表存放在PRIMARY文件組中，同時(shí)也存放沒(méi)有分配具體文件組的對(duì)象。在主文件組中第一個(gè)文件被稱為主文件，通常包括數(shù)據(jù)庫(kù)的系統(tǒng)表。對(duì)于一個(gè)數(shù)據(jù)庫(kù)來(lái)說(shuō)，只能有一個(gè)PRIMARY文件組。如果主文件組沒(méi)有指明，則創(chuàng)建數(shù)據(jù)庫(kù)時(shí)所描述的第一個(gè)文件將作為主文件組成員。?LOG

ON：用來(lái)指明存儲(chǔ)數(shù)據(jù)庫(kù)日志的磁盤(pán)文件。如果沒(méi)有指定logon，系統(tǒng)將自動(dòng)創(chuàng)建單個(gè)的日志文件，使用系統(tǒng)默認(rèn)的命名方法。第6頁(yè)2.刪除數(shù)據(jù)庫(kù)語(yǔ)法：DROPDATABASEdatabase_name刪除數(shù)據(jù)庫(kù)將刪除數(shù)據(jù)庫(kù)所使用的數(shù)據(jù)庫(kù)文件和磁盤(pán)文件。第7頁(yè)3.1.2表定義語(yǔ)句1.創(chuàng)建表語(yǔ)法：CREATETABLE<tableName>(<columnName1><dataType>[default<defaultValue>][NULL|NOTNULL][,

<columnName2><dataType>[default<defaultValue>][NULL|NOTNULL]…][,[CONSTRAINT<constraintName1>]{UNIQUE|PRIMARYKEY}(<columName1>[,<columName2>…])[,…n]][,[CONSTRAINT<constraintName2>]FOREIGNKEY(<columName1>[,<columName2>…])REFERENCE[<dbName>.owner.]<refTable>(<refColumn1>[,<refColumn2>…])[,…n]])[ON<filegroupName>]第8頁(yè)其中：?table_name：新表的名稱，表名必須符合標(biāo)識(shí)符規(guī)則。?column_name：表中的列名，列名必須符合標(biāo)識(shí)符規(guī)則，并且在表內(nèi)唯一。?datetype：列的數(shù)據(jù)類型。?default<defaultValue>：為列設(shè)置缺省值，屬于可選項(xiàng)；?NULL|NOTNULL：為列設(shè)置是否允許為空值，屬于可選項(xiàng)；?<constraintName>：定義約束的名字，屬于可選項(xiàng)；?UNIQUE：建立唯一索引；?PRIMARYKEY：建立主碼；?FOREIGNKEY：建立外碼；?ONfilegroupName：指定該表屬于哪個(gè)文件組。第9頁(yè)2.刪除表語(yǔ)法：DROPTABLEtable_name第10頁(yè)3.1.3索引與視圖定義語(yǔ)句1.創(chuàng)建視圖語(yǔ)句的語(yǔ)法：CREATEVIEW[<database_name>.][<owner>.]view_name[(column[,...n])]

[WITH<view_attribute>[,...n]]AS

select_statement

[WITHCHECKOPTION]

<view_attribute>::={encryption|schemabinding|view_metadata}第11頁(yè)3.1.3索引與視圖定義語(yǔ)句其中：?view_name：視圖的名稱，視圖名稱必須符合標(biāo)識(shí)符規(guī)則。?column：視圖中的列名。當(dāng)列是從算術(shù)表達(dá)式、函數(shù)或常量派生的，或兩個(gè)或更多的列可能會(huì)具有相同的名稱(如聯(lián)接），或視圖中的某列被賦予了不同于派生來(lái)源列的名稱時(shí)必須指定列名。如果未指定column，則視圖列將獲得與SELECT語(yǔ)句中的列相同的名稱。?n：表示可以指定多列的占位符。?select_statement：定義視圖的SELECT語(yǔ)句。?WITHCHECKOPTION：表示當(dāng)對(duì)視圖進(jìn)行更新操作時(shí)必須滿足視圖定義的謂詞條件。第12頁(yè)在創(chuàng)建視圖前需考慮如下原則：(1)只能在當(dāng)前數(shù)據(jù)庫(kù)中創(chuàng)建視圖。(2)視圖名稱必須遵循標(biāo)識(shí)符的規(guī)則，且對(duì)每個(gè)用戶必須唯一，該名稱不得與該用戶擁有的任何表的名稱相同。(3)可以在其它視圖上建立視圖。(4)不能將規(guī)則或DEFAULT定義與視圖相關(guān)聯(lián)。(5)定義視圖的查詢不可以包含ORDERBY、COMPUTE或COMPUTEBY子句或INTO關(guān)鍵字。(6)不能在視圖上定義全文索引。(7)不能創(chuàng)建臨時(shí)視圖，也不能在臨時(shí)表上創(chuàng)建視圖。(8)下列情況下必須在視圖中指定每列的名稱：①視圖中有任何從算術(shù)表達(dá)式、內(nèi)置函數(shù)或常量派生出的列。②視圖中兩列或多列具有相同名稱。③希望使視圖中的列名與它的源列名不同，可在視圖中重新命名列。無(wú)論重命名與否，視圖列都會(huì)繼承其源列的數(shù)據(jù)類型。第13頁(yè)2.刪除視圖如果不需要某視圖，可以刪除該視圖。刪除視圖后，視圖所基于的數(shù)據(jù)并不受到影響。語(yǔ)法：DROPVIEWview_name

[,...n]第14頁(yè)3.創(chuàng)建索引當(dāng)為表建立主鍵和唯一約束時(shí)，SQLServer自動(dòng)創(chuàng)建唯一索引。如果表中不存在聚集索引，則為主鍵創(chuàng)建一個(gè)唯一的聚集索引。默認(rèn)情況下對(duì)UNIQUE約束創(chuàng)建唯一的非聚集索引。創(chuàng)建索引語(yǔ)句的語(yǔ)法：CREATE[UNIQUE][CLUSTERED|NONCLUSTERED]INDEXindex_nameON{TABLE|VIEW}(column[ASC|DESC][,...n])

[ONfilegroup]第15頁(yè)其中：?UNIQUE：為表或視圖創(chuàng)建唯一索引，聚集索引必須是UNIQUE索引。?CLUSTERED：創(chuàng)建聚集索引，如果沒(méi)有指定CLUSTERED，則創(chuàng)建非聚集索引。?NONCLUSTERED：創(chuàng)建非聚集索引。?index_name：索引名，索引名必須遵循標(biāo)識(shí)符規(guī)則。?TABLE：要?jiǎng)?chuàng)建索引的表。?VIEW：要建立索引的視圖的名稱。?column：應(yīng)用索引的列。?ONfilegrou：在給定的filegroup上創(chuàng)建指定的索引。該文件組必須已經(jīng)通過(guò)執(zhí)行CREATEDATABASE或ALTERDATABASE創(chuàng)建。第16頁(yè)4.刪除索引語(yǔ)法：DROPINDEXindex[,...n]ON<tableName|viewName>

或者：DROPINDEXtableName.index|viewName.index[,...n]其中：

?tableName.index|viewName.index：要?jiǎng)h除的表或視圖的索引名稱。

?n：表示可以指定多個(gè)索引的占位符。

?ON<tableName|viewName>：指定表名或視圖名。第17頁(yè)3.1.4插入、刪除、修改記錄語(yǔ)句DML語(yǔ)句包括查詢、添加、修改和刪除數(shù)據(jù)庫(kù)中的數(shù)據(jù)等操縱語(yǔ)句，即SELECT、INSERT、UPDATE、DELETE等。本小節(jié)主要討論數(shù)據(jù)庫(kù)對(duì)象的INSERT、UPDATE、DELETE功能。1.插入數(shù)據(jù)語(yǔ)法：INSERT[INTO]table_name/view_name[(column_list)]VALUES{DEFAULT|NULL|expression}其中：?table_name/view_name：表名/視圖名?column_list：由逗號(hào)分隔的列名列表，用來(lái)指定為其提供數(shù)據(jù)的列。如果沒(méi)有指定column_list，表中的所有列都將接收數(shù)據(jù)。沒(méi)有包含在column_list的列，將在該列插入一個(gè)NULL值(或者該列定義的默認(rèn)值)。第18頁(yè)2.修改數(shù)據(jù)語(yǔ)法：UPDATEtable_name/view_name

SETcolumn_name=expression|DEFAULT|NULL[FROM<table_source>[,...n]][WHERE<search_condition>]其中：?table_name/view_name：需要更新的表/視圖的名稱。?column_name：要更改數(shù)據(jù)的列名。?expression：返回的值將替換column_name的現(xiàn)有值。?DEFAULT：指定使用對(duì)列定義的默認(rèn)值替換列中的現(xiàn)有值。?FROM<table_source>：指定用表來(lái)為更新操作提供準(zhǔn)則。?WHERE<search_condition>：指定條件來(lái)限定所更新的行。第19頁(yè)3.刪除數(shù)據(jù)語(yǔ)法：DELETEFROM<table_name/view_name>[WHERE<search_condition>]其中：?table_name/view_name：要?jiǎng)h除記錄的表名/視圖名。?WHERE<search_condition>：指出被刪除的記錄所滿足的條件，若省略，表示刪除表中的所有記錄。

第20頁(yè)3.2實(shí)驗(yàn)七數(shù)據(jù)庫(kù)與數(shù)據(jù)表定義3.2.1實(shí)驗(yàn)?zāi)康呐c要求

(1)掌握數(shù)據(jù)庫(kù)的建立、刪除和修改操作。

(2)理解基本表之間的關(guān)系，掌握表結(jié)構(gòu)的建立、修改和刪除操作，創(chuàng)建模式導(dǎo)航圖。第21頁(yè)3.2.2實(shí)驗(yàn)案例1.數(shù)據(jù)庫(kù)創(chuàng)建與刪除[例3.1]創(chuàng)建一個(gè)myorder數(shù)據(jù)庫(kù)，該數(shù)據(jù)庫(kù)的主要文件為myorder.mdb,事務(wù)日志為myorder.ldf，它們都位于e:\mySQL目錄下。CREATEDATABASEmyorderON(NAME='myorder',FILENAME='e:\mySQL\myorder.mdf',SIZE=3,MAXSIZE=50,FILEGROWTH=1)LOGON(NAME='myorderLog',FILENAME='e:\mySQL\myorderLog.ldf',SIZE=3,MAXSIZE=20,FILEGROWTH=1)本例中：myorder數(shù)據(jù)庫(kù)，只有一個(gè)主邏輯設(shè)備，對(duì)應(yīng)一個(gè)物理文件myorder.mdf，該文件初始大小3M，最大可擴(kuò)展為50M；如果初始文件裝不下數(shù)據(jù)，自動(dòng)按1M進(jìn)行擴(kuò)展，直到50M為止。日志文件為myorderLog.ldf，文件初始大小3M，最大可擴(kuò)展為20M；如果初始文件裝不下數(shù)據(jù)，自動(dòng)按1M進(jìn)行擴(kuò)展。第22頁(yè)[例3.3]刪除數(shù)據(jù)庫(kù)tmyorder。DROPDATABASEtmyorder第23頁(yè)2.創(chuàng)建表[例3.4]創(chuàng)建一個(gè)客戶表(客戶編號(hào)、客戶姓名、客戶電話、客戶地址、郵政編碼)。CREATETABLECustomer(customerNochar(9)NOTNULLPRIMARYKEY,/*客戶編號(hào)*/

customerNamevarchar(40)NOTNULL,/*客戶名稱*/

telephonevarchar(20)NOTNULL,/*客戶電話*/

addresschar(40)NOTNULL,/*客戶住址*/

zipchar(6)NULL/*郵政編碼*/)第24頁(yè)[例3.6]建立一張訂單表(訂單編號(hào)、客戶編號(hào)、業(yè)務(wù)員編號(hào)、訂貨日期、訂單金額、發(fā)票號(hào)碼)，要求給該表建立主鍵約束和關(guān)于員工表和客戶表的外鍵約束。CREATETABLEOrderMaster(orderNochar(12)NOTNULLPRIMARYKEY,/*訂單編號(hào)*/

customerNochar(9)NOTNULL,/*客戶編號(hào)*/

salerNochar(8)NOTNULL,/*業(yè)務(wù)員編號(hào)*/

orderDatedatetimeNOTNULL,/*訂貨日期*/

orderSumnumeric(9,2)NOTNULL,/*訂單金額*/

invoiceNochar(10)NOTNULL,/*發(fā)票號(hào)碼*/

CONSTRAINTOrdermasterFK1FOREIGNKEY(customerNo)REFERENCESCustomer(customerNo),CONSTRAINTOrdermasterFK2FOREIGNKEY(salerNo)REFERENCESEmployee(employeeNo))第25頁(yè)3.2.3實(shí)驗(yàn)內(nèi)容(1)創(chuàng)建一個(gè)BookDB數(shù)據(jù)庫(kù)，要求至少一個(gè)數(shù)據(jù)文件和一個(gè)日志文件。(2)創(chuàng)建圖書(shū)借閱管理相關(guān)5張關(guān)系表，表結(jié)構(gòu)如圖3-1至圖3-5所示。第26頁(yè)第27頁(yè)第28頁(yè)第29頁(yè)3.3實(shí)驗(yàn)八索引與視圖定義3.3.1實(shí)驗(yàn)?zāi)康呐c要求(1)掌握索引的建立和刪除操作。(2)掌握視圖的創(chuàng)建和查詢操作。第30頁(yè)3.3.2實(shí)驗(yàn)案例1.創(chuàng)建索引[例3.7]在員工表中按生日建立一個(gè)非聚簇索引birthdayIdx。CREATENONCLUSTEREDINDEXbirthdayIdxONEmployee(birthday)[例3.8]在訂單主表中，首先按訂單金額的降序，然后按客戶編號(hào)的升序建立一個(gè)非聚簇索引sumcustIdx。CREATEINDEXsumcustidxONOrderMaster(orderSumDESC,customerNo)[例3.9]在訂單主表中按發(fā)票號(hào)碼創(chuàng)建一個(gè)唯一性索引uniqincoiceIdx。CREATEUNIQUEINDEXuniqincoiceIdxONOrderMaster(invoiceno)[例3.10]刪除birthdayIdx索引DROPINDEXbirthdayIdxONEmployee第31頁(yè)2.定義視圖[例3.11]建立一個(gè)女員工的視圖，要求顯示員工編號(hào)、姓名、性別和薪水。CREATEVIEWemp_viewASSELECTemployeeNo,employeeName,sex,salaryFROMEmployeeWHEREsex='f'第32頁(yè)[例3.13]修改emp_view視圖，要求視圖只顯示薪水3000元以上的女員工信息。ALTERVIEWemp_viewASSELECTemployeeNo,employeeName,sex,salaryFROMEmployeeWHEREsex='f'ANDsalary>3000[例3.14]刪除視圖emp_viewDROPVIEWemp_view第33頁(yè)3.3.3實(shí)驗(yàn)內(nèi)容(1)根據(jù)基本表創(chuàng)建以下索引：①在圖書(shū)表中按出版社號(hào)建立一個(gè)非聚集索引PublishingnoIdx。②在讀者表中按身份證號(hào)建立一個(gè)非聚集索引IdentifycardIdx。③在讀者表中，首先按工作單位的升序，然后按最大借書(shū)數(shù)量降序建立一個(gè)非聚集索引WorkunitCountIdx。(2)創(chuàng)建一個(gè)圖書(shū)名稱中含有“數(shù)據(jù)”的圖書(shū)視圖BookView。(3)創(chuàng)建一個(gè)包含讀者編號(hào)、讀者姓名、圖書(shū)編號(hào)、圖書(shū)名稱、借閱日期、歸還日期的視圖BorrowView。(4)創(chuàng)建一個(gè)視圖，要求顯示至少借閱了3本書(shū)的讀者信息ReaderView。(5)在視圖BorrowView中查詢2016年3月1日以前借閱的圖書(shū)。(6)在視圖ReaderView中查詢姓張的讀者信息。(7)在視圖BorrowView基礎(chǔ)上再建一個(gè)只包含“合生元有限公司”的讀者所借圖書(shū)信息的視圖BorrowView1。(8)刪除視圖BorrowView。第34頁(yè)3.4實(shí)驗(yàn)九數(shù)據(jù)更新操作3.4.1實(shí)驗(yàn)?zāi)康呐c要求

(1)掌握基本表的INSERT、UPDATE、DELETE操作。

(2)正確理解更新操作中涉及到的相關(guān)約束問(wèn)題。第35頁(yè)3.4.2實(shí)驗(yàn)案例[例3.15]在客戶表中插入一條信息(C20220004,雙良股份有限公司江陰市,220012)。INSERTCustomerVALUES('C202200