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

3.1相關(guān)知識在SQLServer2019中，數(shù)據(jù)庫對象包括表、視圖、觸發(fā)器、存儲過程、規(guī)則、缺省、用戶自定義的數(shù)據(jù)類型等。SQLServer的DDL是指用來定義和管理數(shù)據(jù)庫以及數(shù)據(jù)庫中的各種對象的語句，這些語句包括CREATE、ALTER和DROP等語句。SQLServer的DML是指用來查詢、添加、修改和刪除數(shù)據(jù)庫中數(shù)據(jù)的語句，包括SELECT、INSERT、UPDATE、DELETE等。第4頁3.1.1數(shù)據(jù)庫定義語句1.創(chuàng)建數(shù)據(jù)庫語法：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頁其中：?database_name：被創(chuàng)建的數(shù)據(jù)庫的名字。?ON：用于指定存儲數(shù)據(jù)庫中數(shù)據(jù)的磁盤文件，除PRIMARY文件組外，用戶可定義用戶的文件組及相關(guān)的用戶文件。?PRIMARY：描述在主文件組中定義的相關(guān)文件，所有的數(shù)據(jù)庫系統(tǒng)表存放在PRIMARY文件組中，同時也存放沒有分配具體文件組的對象。在主文件組中第一個文件被稱為主文件，通常包括數(shù)據(jù)庫的系統(tǒng)表。對于一個數(shù)據(jù)庫來說，只能有一個PRIMARY文件組。如果主文件組沒有指明，則創(chuàng)建數(shù)據(jù)庫時所描述的第一個文件將作為主文件組成員。?LOG

ON：用來指明存儲數(shù)據(jù)庫日志的磁盤文件。如果沒有指定logon，系統(tǒng)將自動創(chuàng)建單個的日志文件，使用系統(tǒng)默認的命名方法。第6頁2.刪除數(shù)據(jù)庫語法：DROPDATABASEdatabase_name刪除數(shù)據(jù)庫將刪除數(shù)據(jù)庫所使用的數(shù)據(jù)庫文件和磁盤文件。第7頁3.1.2表定義語句1.創(chuàng)建表語法：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頁其中：?table_name：新表的名稱，表名必須符合標識符規(guī)則。?column_name：表中的列名，列名必須符合標識符規(guī)則，并且在表內(nèi)唯一。?datetype：列的數(shù)據(jù)類型。?default<defaultValue>：為列設(shè)置缺省值，屬于可選項；?NULL|NOTNULL：為列設(shè)置是否允許為空值，屬于可選項；?<constraintName>：定義約束的名字，屬于可選項；?UNIQUE：建立唯一索引；?PRIMARYKEY：建立主碼；?FOREIGNKEY：建立外碼；?ONfilegroupName：指定該表屬于哪個文件組。第9頁2.刪除表語法：DROPTABLEtable_name第10頁3.1.3索引與視圖定義語句1.創(chuàng)建視圖語句的語法：CREATEVIEW[<database_name>.][<owner>.]view_name[(column[,...n])]

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

select_statement

[WITHCHECKOPTION]

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

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

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

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

?tableName.index|viewName.index：要刪除的表或視圖的索引名稱。

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

?ON<tableName|viewName>：指定表名或視圖名。第17頁3.1.4插入、刪除、修改記錄語句DML語句包括查詢、添加、修改和刪除數(shù)據(jù)庫中的數(shù)據(jù)等操縱語句，即SELECT、INSERT、UPDATE、DELETE等。本小節(jié)主要討論數(shù)據(jù)庫對象的INSERT、UPDATE、DELETE功能。1.插入數(shù)據(jù)語法：INSERT[INTO]table_name/view_name[(column_list)]VALUES{DEFAULT|NULL|expression}其中：?table_name/view_name：表名/視圖名?column_list：由逗號分隔的列名列表，用來指定為其提供數(shù)據(jù)的列。如果沒有指定column_list，表中的所有列都將接收數(shù)據(jù)。沒有包含在column_list的列，將在該列插入一個NULL值(或者該列定義的默認值)。第18頁2.修改數(shù)據(jù)語法：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：指定使用對列定義的默認值替換列中的現(xiàn)有值。?FROM<table_source>：指定用表來為更新操作提供準則。?WHERE<search_condition>：指定條件來限定所更新的行。第19頁3.刪除數(shù)據(jù)語法：DELETEFROM<table_name/view_name>[WHERE<search_condition>]其中：?table_name/view_name：要刪除記錄的表名/視圖名。?WHERE<search_condition>：指出被刪除的記錄所滿足的條件，若省略，表示刪除表中的所有記錄。

第20頁3.2實驗七數(shù)據(jù)庫與數(shù)據(jù)表定義3.2.1實驗目的與要求

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

(2)理解基本表之間的關(guān)系，掌握表結(jié)構(gòu)的建立、修改和刪除操作，創(chuàng)建模式導航圖。第21頁3.2.2實驗案例1.數(shù)據(jù)庫創(chuàng)建與刪除[例3.1]創(chuàng)建一個myorder數(shù)據(jù)庫，該數(shù)據(jù)庫的主要文件為myorder.mdb,事務日志為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ù)庫，只有一個主邏輯設(shè)備，對應一個物理文件myorder.mdf，該文件初始大小3M，最大可擴展為50M；如果初始文件裝不下數(shù)據(jù)，自動按1M進行擴展，直到50M為止。日志文件為myorderLog.ldf，文件初始大小3M，最大可擴展為20M；如果初始文件裝不下數(shù)據(jù)，自動按1M進行擴展。第22頁[例3.3]刪除數(shù)據(jù)庫tmyorder。DROPDATABASEtmyorder第23頁2.創(chuàng)建表[例3.4]創(chuàng)建一個客戶表(客戶編號、客戶姓名、客戶電話、客戶地址、郵政編碼)。CREATETABLECustomer(customerNochar(9)NOTNULLPRIMARYKEY,/*客戶編號*/

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

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

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

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

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

salerNochar(8)NOTNULL,/*業(yè)務員編號*/

orderDatedatetimeNOTNULL,/*訂貨日期*/

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

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

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

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

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