《數(shù)據(jù)庫技術及應用》第八章 表數(shù)據(jù)的創(chuàng)建與維護

第八章表數(shù)據(jù)的創(chuàng)建與維護學習目標:掌握表數(shù)據(jù)的插入掌握表數(shù)據(jù)的修改掌握表數(shù)據(jù)的刪除8.1插入數(shù)據(jù)

插入數(shù)據(jù)即向表中寫入新的記錄(表的一行數(shù)據(jù)即為一條記錄)。插入的新記錄必須完全遵守表的完整性約束,所謂完整性約束指的是,列是哪種數(shù)據(jù)類型,新記錄對應的值就必須是這種數(shù)據(jù)類型,列上有什么約束條件,新記錄的值也必須滿足這些約束條件。

在MySQL中,可以通過“INSERT”語句來實現(xiàn)插入數(shù)據(jù)的功能。“INSERT”語句有兩種方式插入數(shù)據(jù):第一,插入特定的值,即所有的值都是在“INSERT”語句中明文確定的;第二,插入某查詢的結果,其結果指的是插入表中的是哪些值,“INSERT”語句本身看不出來,完全由查詢結果確定。 “INSERT”語句的基本語法如下:插入數(shù)據(jù) 插入一條完整的記錄可以理解為向表的所有字段插入數(shù)據(jù),一般有兩種方法可以實現(xiàn):第一,只指定表名,不指定具體的字段,按字段的默認順序填寫數(shù)值,然后插入記錄;第二,在表名的后面指定要插入的數(shù)值所對應的字段,并按指定的順序寫入數(shù)值。 (1)不指定字段名,按默認順序插入數(shù)值 在MySQL中,若想按默認的數(shù)值順序插入某記錄,可用如下語句:

特別注意:“VALUES”后面所跟的值列表必須和表的字段前后順序一致,且數(shù)據(jù)類型匹配。若某列的值允許為空,且插入的記錄此字段的值也為空,則必須在“VALUES”后面跟上“NULL”。插入數(shù)據(jù)

【例8．1】在第7章例7．3中創(chuàng)建了學院信息表deptinfo,重慶工程學院的軟件學院編號是“D001”,現(xiàn)需要將此學院的信息加入學院信息表中。 已知的信息有學院名稱、學院編號和描述(“重慶工程學院”),學院編號是學校為其分配的,自增長列可設置為1。deptinfo表需要的4個字段信息目前都有了,那么就可以省略表的字段名,然后按字段默認順序插入數(shù)據(jù)。要實現(xiàn)此目標必須要經(jīng)過以下幾個步驟。插入數(shù)據(jù)

從deptinfo表的結構中,可以分析得出以下結論:

①表中字段的前后順序(Field)。 ②字段的數(shù)據(jù)類型(type)。 ③每一個字段是否允許為空(NULL)。 ④約束(KEY)。插入數(shù)據(jù)

【例8．2】在deptinfo表中,需再插入一條計算機學院的信息,學院描述不是很清楚。

(2)指定字段名,按指定順序插入數(shù)值插入數(shù)據(jù)

【例8．3】學校新進一名學生,目前只知道他的學號和名字是“16900101李馴”,是一名男生,其他信息暫時不知?，F(xiàn)在請將此學生已知的信息插入學生表studentinfo中,其他信息以后再修改。 可直接用以下SQL語句實現(xiàn):

(3)同時插入多條記錄 可用以下格式一次性插入多條語句:插入數(shù)據(jù)

【例8．4】169001班有4名學生到學校報到,他們的信息分別如下所示:16900129,趙菁菁,女,出生于1998年8月10日;16900130,李勇,男,出生于1999年2月24日;16900131,張力,男,出生于1998年6月12日;16900132,張衡,男,出生于1999年1月4日。如何更快地將這些數(shù)據(jù)插入數(shù)據(jù)庫的表中呢?8.2修改數(shù)據(jù) 表中已經(jīng)存在的數(shù)據(jù)也可能會出現(xiàn)需要修改的情況。在修改數(shù)據(jù)的過程中,必須先明確兩點:第一,需要修改哪些值?即修改的數(shù)據(jù)其所在行要滿足什么樣的條件?第二,需要修改成什么值?修改數(shù)據(jù)可用“UPDATE”語句實現(xiàn)。其語法格式為:

修改數(shù)據(jù)的操作可以看成把表先從行的方向上篩選出那些要修改的記錄,然后將篩選出來的記錄的某些列的值進行修改。

注意:不能修改自增長列和主鍵列。修改數(shù)據(jù) (1)修改一個字段的值 若數(shù)據(jù)表中只有一個字段的值需要修改,則只需要在“UPDATE”語句的“SET”子句后跟一個表達式(即“字段名=修改后的值”的形式)。 【例8．5】在例8．4中插入的學生“李勇”進校之后更換了手機號碼,現(xiàn)在的號碼是,請將其在student表中的數(shù)值作相應的修改。修改數(shù)值之前,可以使用“SELECT”語句查詢李勇的信息,發(fā)現(xiàn)入校時并沒有登記手機號碼。修改數(shù)據(jù) (2)修改幾個字段的值 有時,某些記錄可能需要同時修改多個字段的值,那么此時可將所有待修改的表達式都放在“SET”語句后面,然后用逗號把它們隔開。 【例8．6】在例8．4中插入學生“張力”,需用修改他的家庭地址為重慶市巴南區(qū),電話號碼為“19923456789”。8.3刪除數(shù)據(jù) DELETE命令用于刪除指定數(shù)據(jù)的記錄行,而不能刪除列。可以一次刪除多行,這主要是根據(jù)后面所帶的條件來判斷。如果查詢語句查詢出來的行有多個。若不帶條件,會把表中的所有數(shù)據(jù)都刪除掉,在業(yè)務中,一般不會這么做。 刪除表中的數(shù)據(jù)可以用DELETE語句來實現(xiàn)。其語法格式如下:刪除數(shù)據(jù) (1)刪除指定行 【例8．7】將學院信息表deptinfo中的“電子信息學院”刪除。

(2)刪除所有行

為了下一步操作,先執(zhí)行如下語句,將deptinfo復制一張新表department。刪除數(shù)據(jù) 【例8．8】將示例數(shù)據(jù)庫student中剛創(chuàng)建的部門信息表department的所有記錄進行刪除。

在沒有WHERE子句的情況下,刪除了department表中的所有數(shù)據(jù),但是在刪除數(shù)據(jù)之前,最好再一次確定這個表是否真的不需要了,因為這種刪除方式,找回數(shù)據(jù)幾乎不可能,所以要慎用不帶WHERE子句的刪除語句。8.4數(shù)據(jù)完整性概述 數(shù)據(jù)庫的完整性包括數(shù)據(jù)庫的正確性與數(shù)據(jù)庫的相容性。完整性檢查和控制的防范對象主要是不合語義、不正確的數(shù)據(jù),防止它們進入數(shù)據(jù)庫。完全性控制的防范對象是非法用戶和非法操作。防止它們對數(shù)據(jù)庫中的數(shù)據(jù)進行非法的獲取。 關系數(shù)據(jù)庫的完整性規(guī)則是數(shù)據(jù)庫設計的重要內容。絕大部分關系型數(shù)據(jù)庫管理系統(tǒng)RDBMS都可自動支持關系完整性規(guī)則,只要用戶在定義(建立)表的結構時,注意選定主鍵、外鍵及其參照表,RDBMS可自動實現(xiàn)其完整性約束條件。數(shù)據(jù)的完整性主要包括域完整性、實體完整性、參照完整性和用戶自定義完整性4種。(1)域(列)完整性 域完整性(DomainIntegrity)是對數(shù)據(jù)表中字段屬性的約束,通常指數(shù)據(jù)的有效性,包括字段的值域、字段的類型及字段的有效規(guī)則等約束。 域完整性約束,通過表的創(chuàng)建過程中,定義在列上的約束條件來限制,主要包括以下3種: ①列值非空(NOTNULL):NULL或NOTNULL。指定表中的屬性列是否為空,控制用戶必須輸入的列,默認值為NULL。 ②列值唯一(UNIQUE):唯一性約束,指定表中列保持唯一值即不能出現(xiàn)重復。 ③默認值(DEFAULT):DEFAULT約束為某一列指定了默認值,當向表中輸入數(shù)據(jù)記錄時,可以不輸入該列數(shù)據(jù)值,而采用默認值。(2)實體(行)完整性 實體完整性(EntityIntegrity)是對關系中的記錄唯一性,也就是主鍵的約束。準確地說,實體完整性是指關系中的主屬性值不能為NULL且不能有相同值。定義表中的所有行能唯一的標識,一般用主鍵,唯一索引UNIQUE,自增長列等屬性。 實體完整性規(guī)則說明如下: ①實體完整性規(guī)則是針對基本關系而言。一個基本表通常對應現(xiàn)實世界的一個實體集。 ②現(xiàn)實世界的實體是可以區(qū)分的,即它們具有某種唯一性標識。 ③關系模型中通常是以主碼作為唯一性標識。 ④主碼中的屬性即主屬性不能取空值。如果主屬性取空值,就說明存在某個不可以標識的實體,即存在不可區(qū)分的實體。(3)參照完整性 參照完整性(ReferentialIntegrity)屬于表間規(guī)則。對于永久關系的相關表,在更新、插入或刪除記錄時,如果只改其一,就會影響數(shù)據(jù)的完整性。 在關系數(shù)據(jù)庫中,關系之間的聯(lián)系是通過公共屬性實現(xiàn)的。這個公共屬性經(jīng)常是一個表的主鍵,同時是另一個表的外鍵。參照完整性體現(xiàn)在兩個方面:實現(xiàn)了表與表之間的聯(lián)系,外鍵的取值必須是另一個表的主鍵的有效值,或是“空”值。 參照完整性規(guī)則(ReferentialIntegrity)要求:若屬性組F是關系模式R1的主鍵,同時F也是關系模式R2的外鍵,則在R2的關系中,F的取值只允許兩種可能:空值或等于R1關系中的某個主鍵值。R1稱為“被參照關系”模式,R2稱為“參照關系”模式。(4)用戶定義完整性 關系模型要求任何關系數(shù)據(jù)庫系統(tǒng)都應該支持實體完整性和參照完整性。實體完整性保證關系數(shù)據(jù)庫中每個表中的記錄都是唯一的。參照完整性保證相互關聯(lián)的數(shù)據(jù)表之間的數(shù)據(jù)保持一致。除此之外,不同的關系數(shù)據(jù)庫系統(tǒng)根據(jù)其應用環(huán)境的不同,往往還需要一些特殊的約束條件。用戶定義的完整性就是針對某一具體關系數(shù)據(jù)庫的約束條件。 用戶定義完整性(User-DefinedIntegrity)使用戶可以定義不屬于其他任何完整性類別的特定業(yè)務規(guī)則。所有完整性類別都支持用戶的定義完整性,可以涵蓋實體完整性、域完整性、參照完整性等完整性類型。例如,在使用CREATETABLE創(chuàng)建表時定義的所有列級約束和表級約束。另外,其他存儲過程以及觸發(fā)器都可以定義完整性。8.5完整性約束的管理 在MySQL中,主要有PRIMARYKEY(主鍵約束)、FOREIGNKEY(外鍵約束)、UNIQUE(唯一約束)、DEFAULT(默認值)4種，這里將介紹表級約束的語法如下:

對一個數(shù)據(jù)列建立的約束,稱為列級約束。對多個數(shù)據(jù)列建立的約束,稱為表級約束。列級約束既可以在列定義時聲明,也可以在列定義后聲明。表級約束只能在列定義后聲明。8.5.1默認值約束 DEFAULT約束提供了一種為數(shù)據(jù)表中的任何一列提供默認值的手段。如果用DEFAULT約束為某一列指定了默認值,則當向表中輸入數(shù)據(jù)記錄時,可以不輸入該列數(shù)據(jù)值,而采用默認值。 【例8．9】給學生信息表studentinfo中學生的性別設置默認值。 這時分兩種情況,如果學生表還沒有創(chuàng)建,可以在創(chuàng)建表時指定默認值。其代碼如下:8.5.2主鍵約束

主鍵用PRIMARYKEY表示,通常一個表必須指定一個主鍵,也只能有一個主鍵,可以指定一個字段作為表的主鍵,也可以指定兩個或兩個以上的字段作為表的主鍵,其值能唯一標識表中的每一行,因此構成主鍵的字段值不允許為空,值或者值的組合不允許重復。

我們可以在創(chuàng)建表時創(chuàng)建主鍵,也可以對一個已有表中的已有主鍵進行修改或者為沒有主鍵的表增加主鍵。設置主鍵通常有兩種方式,即表的完整性約束和列的完整性約束。

當表結構已經(jīng)創(chuàng)建成功時,這時只能用修改表結構的語句來設置默認值。其代碼如下:(1)表的完整性約束主鍵

【例8．10】創(chuàng)建課程信息表courseinfo,表結構見表8．1。表的完整性約束主鍵

其中的PRIMARYKEY(course_ID)就是制定courseinfo表的course_ID字段被設定為該表的主鍵,采用的是表級完整性約束。CONSTRAINTCOURSEPK部分不是必需的,只是以一種顯式的方式說明PRIMARYKEY(course_ID)是一個約束,并且約束被命名為COURSEPK,如果省略這個,系統(tǒng)將自動為該主鍵命名。(2)復合主鍵

【例8．11】創(chuàng)建臨時表temp,用ID和name字段作為復合主鍵。

注意:當表中有兩個以上的字段做主鍵時,只能定義成表級約束,不能定義成列級約束。(3)修改表的主鍵 【例8．12】修改臨時表temp,刪除原來的主鍵,增加新的主鍵。

如果表temp在定義時未指定主鍵,這時也可以用MODIFY關鍵字將主鍵修改為ID,代碼如下:8.5.3唯一約束

唯一約束用UNIQUE表示,唯一約束又稱為替代鍵,是沒有被選作主鍵的候選鍵,替代鍵與主鍵一樣,是表的一列或一組列,它們的值在任何時候都是唯一的。唯一約束與主鍵的區(qū)別在于一個表可以有多個唯一約束,并且唯一約束的列可以為空值。

設置唯一約束也可以使用表的完整性約束和列的完整性約束兩種方式。唯一約束 【例8．13】創(chuàng)建一張雇員表employees,并同時創(chuàng)建它的主鍵約束與唯一約束。有兩種方式。

唯一約束

【例8．14】給學生信息表studentinfo的student_code列添加唯一約束。

唯一約束不允許出現(xiàn)重復的值,但是可以為多個NULL。同一個表可以有多個唯一約束,多個列組合的約束。在創(chuàng)建唯一約束時,如果不給唯一約束名稱,就默認和列名相同。8.5.4外鍵約束

外鍵約束是保證一個或兩個表之間的參照完整性,外鍵是構建于一個表的兩個字段或兩個表的兩個字段之間的參照關系。創(chuàng)建外鍵時,主要是為外鍵定義參照語句reference_definition,語法格式如下:

說明:

①外鍵被定義為表的完整性約束,reference_definition中包含了外鍵所參照的表和列,還可以聲明參照動作。外鍵約束

②RESTRICT:限制,當要刪除或更新父表中被參照列上的,且已經(jīng)在外鍵中出現(xiàn)了的值時,拒絕對父表的刪除或更新操作。

③CASCADE:級聯(lián),從父表刪除或更新在外鍵中出現(xiàn)的值時,自動刪除或更新子表中匹配的行。

④SETNULL:置為