項(xiàng)目4 數(shù)據(jù)表的基本操作

數(shù)據(jù)表的基本操作項(xiàng)目4【能力目標(biāo)】掌握數(shù)據(jù)表的創(chuàng)建、查看、修改和刪除。掌握數(shù)據(jù)表約束的概念和類?別。掌握數(shù)據(jù)表記錄的添加、更新和刪除?！舅仞B(yǎng)目標(biāo)】理解通過嚴(yán)格遵循各種約束進(jìn)行數(shù)據(jù)表的設(shè)置，保證數(shù)據(jù)表中數(shù)據(jù)的完整性、正確性和一致性，培養(yǎng)良好的職業(yè)素養(yǎng)。目標(biāo)感謝學(xué)習(xí)導(dǎo)航圖4-1項(xiàng)目4所講內(nèi)容在數(shù)據(jù)庫(kù)系統(tǒng)開發(fā)中的位置創(chuàng)建、查看、修改、刪除數(shù)據(jù)表4.14.1創(chuàng)建、查看、修改、刪除數(shù)據(jù)表

在創(chuàng)建數(shù)據(jù)表之前，需要確定關(guān)系模型，關(guān)系模型中的每一個(gè)關(guān)系對(duì)應(yīng)數(shù)據(jù)庫(kù)的一個(gè)數(shù)據(jù)表。接下來以學(xué)生成績(jī)管理系統(tǒng)數(shù)據(jù)庫(kù)ssms為例，介紹創(chuàng)建數(shù)據(jù)表的方法。從項(xiàng)目1中可以得知其E-R模型如圖4-2所示。圖4-2學(xué)生成績(jī)管理系統(tǒng)數(shù)據(jù)庫(kù)的E-R模型

4.1.1創(chuàng)建和查看數(shù)據(jù)表

數(shù)據(jù)庫(kù)創(chuàng)建成功后，需要?jiǎng)?chuàng)建數(shù)據(jù)表。創(chuàng)建數(shù)據(jù)表是指在已有的數(shù)據(jù)庫(kù)中建立新的數(shù)據(jù)表。使用SQL語句創(chuàng)建好數(shù)據(jù)表后，可以查看數(shù)據(jù)表結(jié)構(gòu)的定義，確認(rèn)數(shù)據(jù)表的定義是否正確。1．創(chuàng)建數(shù)據(jù)表使用CREATETABLE命令新建數(shù)據(jù)表，語法格式如下。CREATETABLE[IFNOTEXISTS]數(shù)據(jù)表名([列定義]...|[數(shù)據(jù)表索引定義])[數(shù)據(jù)表選項(xiàng)][SELECT語句];

4.1.1創(chuàng)建和查看數(shù)據(jù)表

其中，各參數(shù)的含義如下。

①CREATETABLE：用于創(chuàng)建指定名稱的數(shù)據(jù)表，用戶必須擁有創(chuàng)建數(shù)據(jù)表的權(quán)限。

②IFNOTEXISTS：如果數(shù)據(jù)表不存在，則創(chuàng)建數(shù)據(jù)表。

③數(shù)據(jù)表名：指定要?jiǎng)?chuàng)建的數(shù)據(jù)表名稱。

④列定義：包括列名、數(shù)據(jù)類型，可能還有一個(gè)空值聲明和一個(gè)完整性約束。

⑤數(shù)據(jù)表索引定義：主要定義數(shù)據(jù)表的索引、主鍵、外鍵等。

⑥數(shù)據(jù)表選項(xiàng)：數(shù)據(jù)表創(chuàng)建定義，由列名（col_name）、列的定義（column_definition），以及可能的空值說明、完整性約束或數(shù)據(jù)表索引組成。

⑦SELECT語句：用于在一個(gè)已有數(shù)據(jù)表的基礎(chǔ)上創(chuàng)建數(shù)據(jù)表。

4.1.1創(chuàng)建和查看數(shù)據(jù)表【例4-1】創(chuàng)建學(xué)生成績(jī)管理系統(tǒng)數(shù)據(jù)庫(kù)ssms，并在ssms中創(chuàng)建學(xué)生信息表student、課程表course、成績(jī)表elective。

（1）創(chuàng)建并使用學(xué)生成績(jī)管理系統(tǒng)數(shù)據(jù)庫(kù)ssms。CREATEDATABASEssms;USEssms;

（2）在ssms中創(chuàng)建學(xué)生信息表student。CREATETABLEstudent(S_IDCHAR(6)NOTNULLPRIMARYKEY,NameCHAR(8)NOTNULL,MajorCHAR(10)NULL,SexTINYINT(1)NOTNULLDEFAULT1,BirthdayDATENOTNULL,Total_CreditTINYINT(1)NULL,PhotoBLOBNULL,NoteTEXTNULL);

4.1.1創(chuàng)建和查看數(shù)據(jù)表（3）在ssms中創(chuàng)建課程表course。CREATETABLEcourse(C_IDchar(3)NOTNULLPRIMARYKEY,C_Namechar(16)NOTNULL,Semesterenum('1','2','3','4','5','6','7','8')NOTNULLDEFAULT'1',Credit_Hourtinyint(1)NOTNULL,Credittinyint(1)NULL);

4.1.1創(chuàng)建和查看數(shù)據(jù)表（4）在ssms中創(chuàng)建成績(jī)表elective。CREATETABLEelective(S_IDCHAR(6)NOTNULL,C_IDCHAR(3)NOTNULL,GradeTINYINT(1)NULL,PRIMARYKEY(S_ID,C_ID));

4.1.1創(chuàng)建和查看數(shù)據(jù)表執(zhí)行結(jié)果如圖4-3所示。圖4-3創(chuàng)建學(xué)生信息表student、課程表course、成績(jī)表elective

4.1.1創(chuàng)建和查看數(shù)據(jù)表2．查看數(shù)據(jù)表學(xué)生信息表student創(chuàng)建完成后，可以使用SHOWTABLES命令顯示ssms數(shù)據(jù)庫(kù)中已生成的數(shù)據(jù)表，用DESCRIBE命令顯示學(xué)生信息表student的結(jié)構(gòu)。【例4-2】查看已生成的數(shù)據(jù)表及學(xué)生信息表student的結(jié)構(gòu)。SHOWTABLES;DESCRIBEstudent;圖4-4查看已生成的數(shù)據(jù)表及學(xué)生信息表student的結(jié)構(gòu)

4.1.2修改數(shù)據(jù)表創(chuàng)建數(shù)據(jù)表之后，有時(shí)需要對(duì)數(shù)據(jù)表中的某些信息進(jìn)行修改，例如，修改數(shù)據(jù)表名、字段名、字段的數(shù)據(jù)類型等。修改數(shù)據(jù)表指的是修改數(shù)據(jù)庫(kù)中已經(jīng)存在的數(shù)據(jù)表結(jié)構(gòu)。1．修改數(shù)據(jù)表結(jié)構(gòu)ALTERTABLE用于更改原有數(shù)據(jù)表的結(jié)構(gòu)。例如，可以增加（刪減）列、創(chuàng)建（取消）索引、更改原有列的類型、重新命名列或數(shù)據(jù)表，還可以更改數(shù)據(jù)表的評(píng)注和數(shù)據(jù)表的類型。其語法格式如下。ALTERTABLE數(shù)據(jù)表名ADD列定義 [FIRST|AFTER列名]|MODIFY列定義|ALTER列名{SETDEFAULT值|DROPDEFAULT}|CHANGE列名原列名|DROP列名|RENAME[TO]新數(shù)據(jù)表名

4.1.2修改數(shù)據(jù)表其中，各參數(shù)的含義如下。①ADD子句：向數(shù)據(jù)表中增加新列。要用“FIRST|AFTER列名”指定增加列的位置，否則默認(rèn)加在最后一列。②MODIFY子句：修改指定列的數(shù)據(jù)類型。③ALTER子句：修改數(shù)據(jù)表中指定列的默認(rèn)值，或者刪除列默認(rèn)值。④CHANGE子句：修改列的名稱。⑤DROP子句：刪除列或約束。

4.1.2修改數(shù)據(jù)表【例4-3】在數(shù)據(jù)庫(kù)ssms的student表中增加“Tel”（電話號(hào)碼）列。ALTERTABLEstudentADDTelCHAR(11)NULL;【例4-4】在student表中刪除“Tel”列。ALTERTABLEstudentDROPTel;圖4-5修改student數(shù)據(jù)表中的字段圖4-6在student數(shù)據(jù)表中刪除一列

4.1.2修改數(shù)據(jù)表2．更改數(shù)據(jù)表名如果需要更改數(shù)據(jù)表名，則可以使用RENAMETABLE命令，語法格式如下。RENAMETABLE原數(shù)據(jù)表名TO新數(shù)據(jù)表名...【例4-5】創(chuàng)建mytest數(shù)據(jù)庫(kù)，在數(shù)據(jù)庫(kù)中創(chuàng)建user表，并將user表重命名為user1。（1）創(chuàng)建mytest數(shù)據(jù)庫(kù)。CREATEDATABASEmytest;

4.1.2修改數(shù)據(jù)表（2）創(chuàng)建user表。USEmytest;CREATETABLEuser(S_IDCHAR(6)NOTNULLPRIMARYKEY,NameCHAR(8)NOTNULL,MajorCHAR(10)NULL,SexTINYINT(1)NOTNULLDEFAULT1,BirthdayDATENOTNULL,Total_CreditTINYINT(1)NULL,PhotoBLOBNULL,NoteTEXTNULL);（3）將user表重命名為user1。RENAMETABLEuserTOuser1;

4.1.3刪除數(shù)據(jù)表刪除數(shù)據(jù)表時(shí)可以使用DROPTABLE命令。使用這個(gè)命令時(shí)會(huì)將數(shù)據(jù)表的描述、數(shù)據(jù)表的完整性約束、索引及與數(shù)據(jù)表相關(guān)的權(quán)限等一并刪除。其語法格式如下。DROPTABLE[IFEXISTS]數(shù)據(jù)表名...【例4-6】刪除數(shù)據(jù)庫(kù)mytest中的數(shù)據(jù)表user1。DROPTABLEuser1;認(rèn)識(shí)數(shù)據(jù)表的約束4.2

4.2.1理解約束的概念約束是一種限制，它通過對(duì)數(shù)據(jù)表的行或者列的數(shù)據(jù)做出限制，來確保數(shù)據(jù)表數(shù)據(jù)的完整性和唯一性。MySQL中常見的約束如表4-1所示。約束類型非空約束主鍵約束唯一約束默認(rèn)約束外鍵約束關(guān)鍵字NOTNULLPRIMARYKEYUNIQUEDEFAULTFOREIGNKEY表4-1MySQL中常見的約束

4.2.2掌握非空約束【例4-7】在數(shù)據(jù)庫(kù)mytest中創(chuàng)建一個(gè)班級(jí)表myclass，包含班級(jí)名Class_Name和班級(jí)所在教室的名字Class_Room，這兩個(gè)字段均不為空。因?yàn)槿绻嗉?jí)沒有名字，就無法區(qū)分班級(jí)；如果不知道教室名字，就不知道在哪里上課。CREATETABLEmyclass(Class_NameVARCHAR(20)NOTNULL,Class_RoomVARCHAR(10)NOTNULL);在插入數(shù)據(jù)時(shí)，如果不插入教室數(shù)據(jù)就會(huì)導(dǎo)致插入失敗，插入數(shù)據(jù)時(shí)使用如下命令。INSERTINTOmyclass(Class_Name)VALUES('202061061');

4.2.3掌握主鍵約束主鍵指的是一列或多列的組合，其中由多列組合的主鍵稱為復(fù)合主鍵，其值能唯一地標(biāo)識(shí)數(shù)據(jù)表中的每一行，利用它可強(qiáng)制數(shù)據(jù)表的實(shí)體完整性。1．理解主鍵的特性主鍵有如下特性。①可通過定義PRIMARYKEY約束來創(chuàng)建主鍵，而且PRIMARYKEY約束中的列不能取空值。②當(dāng)為數(shù)據(jù)表定義PRIMARYKEY約束時(shí)，MySQL為主鍵列創(chuàng)建唯一性索引，實(shí)現(xiàn)數(shù)據(jù)的唯一性。③在查詢中使用主鍵時(shí)，該索引可用來對(duì)數(shù)據(jù)進(jìn)行快速訪問。④如果PRIMARYKEY約束是由多列組合定義的，則某一列的值可以重復(fù)，但PRIMARYKEY約束定義中所有列的組合值必須唯一。2．定義主鍵可以用以下兩種方式定義主鍵，使其作為列或數(shù)據(jù)表的完整性約束。①作為列的完整性約束時(shí)，只需在列定義時(shí)加上關(guān)鍵字的PRIMARYKEY。②作為數(shù)據(jù)表的完整性約束時(shí)，需要在語句的最后加上PRIMARYKEY(列…)子句。

4.2.3掌握主鍵約束【例4-8】在數(shù)據(jù)庫(kù)mytest中創(chuàng)建一個(gè)班級(jí)表myclass1，包含班級(jí)名Class_Name和班級(jí)所在教室的名字Class_Room，將Class_Name定義為主鍵。CREATETABLEmyclass1(Class_NameVARCHAR(20)NOTNULLPRIMARYKEY,Class_RoomVARCHAR(10)NOTNULL);【例4-9】在數(shù)據(jù)庫(kù)mytest中創(chuàng)建選課表elective，包含學(xué)號(hào)S_ID、課程號(hào)C_ID和成績(jī)Grade。其中學(xué)號(hào)S_ID和課程號(hào)C_ID構(gòu)成復(fù)合主鍵。CREATETABLEelective(S_IDCHAR(6)NOTNULL,C_IDCHAR(3)NOTNULL,GradeTINYINT(1)NULL,PRIMARYKEY(S_ID,C_ID));

4.2.4掌握唯一約束唯一約束也叫作替代鍵，像主鍵一樣，是數(shù)據(jù)表的一列或多列，它的值在任何時(shí)候都是唯一的。定義唯一約束的關(guān)鍵字是UNIQUE。【例4-10】在數(shù)據(jù)庫(kù)mytest中創(chuàng)建表student1時(shí)，將姓名列Name定義為唯一約束。CREATETABLEstudent1(S_IDVARCHAR(6)NOTNULL,NameVARCHAR(8)NOTNULLUNIQUE,BirthdayDATETIMENULL,PRIMARYKEY(S_ID));

4.2.4掌握唯一約束唯一約束即替代鍵還可以定義為數(shù)據(jù)表的完整性約束，故前面的語句也可這樣定義：CREATETABLEstudent1(S_IDVARCHAR(6)NOTNULL,NameVARCHAR(8)NOTNULL,BirthdayDATETIMENULL,PRIMARYKEY(S_ID),UNIQUE(Name));

4.2.4掌握唯一約束在MySQL中，替代鍵和主鍵的區(qū)別主要有以下幾點(diǎn)。①一個(gè)數(shù)據(jù)表只能創(chuàng)建一個(gè)主鍵，但一個(gè)數(shù)據(jù)表可以有若干個(gè)UNIQUE鍵，甚至它們可以重合，例如，在C1和C2列上定義了一個(gè)替代鍵，并且在C2和C3上定義了另一個(gè)替代鍵，這兩個(gè)替代鍵在C2列上重合了，而MySQL允許這樣。②主鍵字段的值不允許為NULL，而UNIQUE字段的值可取NULL，但是必須使用NULL或NOTNULL聲明。③一般創(chuàng)建PRIMARYKEY約束時(shí)，系統(tǒng)會(huì)自動(dòng)產(chǎn)生PRIMARYKEY索引。創(chuàng)建UNIQUE約束時(shí)，系統(tǒng)會(huì)自動(dòng)產(chǎn)生UNIQUE索引。

4.2.5掌握默認(rèn)約束默認(rèn)約束用于指定某列的默認(rèn)值?！纠?-11】在數(shù)據(jù)庫(kù)mytest中創(chuàng)建表student2時(shí)，將專業(yè)列Major的默認(rèn)值設(shè)置為“信息安全”。CREATETABLEstudent2(S_IDVARCHAR(6)NOTNULL,NameVARCHAR(8)NOTNULL,MajorVARCHAR(10)DEFAULT'信息安全',PRIMARYKEY(S_ID));INSERTINTOstudent2(S_ID,Name)VALUES('201101','黃飛');

4.2.6掌握外鍵約束外鍵和主鍵一樣，也是索引的一種。不同的是，MySQL會(huì)自動(dòng)為所有數(shù)據(jù)表的主鍵創(chuàng)建索引，但是外鍵字段必須由用戶進(jìn)行明確的索引。用于外鍵關(guān)系的字段必須在所有的參照數(shù)據(jù)表中進(jìn)行明確的索引，而InnoDB不能自動(dòng)創(chuàng)建索引。外鍵用于在兩個(gè)數(shù)據(jù)表的數(shù)據(jù)之間建立鏈接，它可以是一列或者多列組合。一個(gè)數(shù)據(jù)表可以有一個(gè)或者多個(gè)外鍵?？梢栽趧?chuàng)建數(shù)據(jù)表或修改數(shù)據(jù)表時(shí)定義一個(gè)外鍵聲明，語法格式如下。CREATETABLE[IFNOTEXISTS]數(shù)據(jù)表名[([列定義],...|[索引定義])]PRIMARYKEY[索引類型](索引列名...) #主鍵...|FOREIGNKEY[索引名](索引列名...)[參照性定義] #外鍵REFERENCES數(shù)據(jù)表名[(索引列名...)][ONDELETE{RESTRICT|CASCADE|SETNULL|NOACTION}][ONUPDATE{RESTRICT|CASCADE|SETNULL|NOACTION}]

4.2.6掌握外鍵約束其中，各參數(shù)的含義如下。①FOREIGNKEY：外鍵，被定義為數(shù)據(jù)表的完整性約束。②REFERENCES：參照性，定義中包含了外鍵所參照的數(shù)據(jù)表和列。這里的數(shù)據(jù)表名叫作被參照數(shù)據(jù)表，而外鍵所在的數(shù)據(jù)表叫作參照數(shù)據(jù)表。其中，外鍵可以引用一列或多列，外鍵中的所有列值在引用的列中必須全部存在。外鍵可以只引用主鍵和替代鍵。③ONDELETE|ONUPDATE：外鍵參照動(dòng)作對(duì)應(yīng)的語句。這里有兩條相關(guān)的語句，即UPDATE和DELETE語句。

4.2.6掌握外鍵約束UPDATE和DELETE語句后可以采取的動(dòng)作有RESTRICT、CASCADE、SETNULL、NOACTION和SETDEFAULT。這些不同動(dòng)作的含義如下。RESTRICT：當(dāng)要?jiǎng)h除或更新父表中被參照列上在外鍵中出現(xiàn)的值時(shí)，拒絕對(duì)父表的刪除或更新操作。CASCADE：從父表刪除或更新行時(shí)自動(dòng)刪除或更新子表中匹配的行。SETNULL：當(dāng)從父表刪除或更新行時(shí)，設(shè)置子表中與之對(duì)應(yīng)的外鍵列為NULL。如果外鍵列沒有指定NOTNULL限定詞，這就是合法的。NOACTION：NOACTION意味著不采取動(dòng)作，就是如果有一個(gè)相關(guān)的外鍵值在被參照數(shù)據(jù)表中，則刪除或更新父表中主要鍵值的操作將不被允許，和RESTRICT一樣。SETDEFAULT：作用和SETNULL一樣，只不過SETDEFAULT是指定子表中的外鍵列為默認(rèn)值。

4.2.6掌握外鍵約束【例4-12】在數(shù)據(jù)庫(kù)mytest中創(chuàng)建兩個(gè)數(shù)據(jù)表——班級(jí)表grade和學(xué)生表student。表student中的GID是學(xué)生所在的班級(jí)ID，即引用了表grade中的主鍵ID，那么GID就可以作為表student的外鍵。被引用的數(shù)據(jù)表grade是主表，引用外鍵的表student是從表，兩個(gè)數(shù)據(jù)表是主從關(guān)系。表student用GID可以連接表grade中的信息，從而建立了兩個(gè)數(shù)據(jù)表的連接。（1）創(chuàng)建班級(jí)表grade。CREATETABLEgrade(IDINT(4)NOTNULLPRIMARYKEY,NameVARCHAR(36));

4.2.6掌握外鍵約束（2）創(chuàng)建學(xué)生表student。CREATETABLEstudent(SIDINT(4)NOTNULL,SnameVARCHAR(36),GIDINT(4)NOTNULL,PRIMARYKEY(SID),FOREIGNKEY(GID)REFERENCESgrade(ID)ONDELETERESTRICTONUPDATERESTRICT);注意語句執(zhí)行后，確保MySQL插入外鍵中的每一個(gè)非空值都已經(jīng)在被參照數(shù)據(jù)表中作為主鍵出現(xiàn)。

4.2.6掌握外鍵約束若在創(chuàng)建表student時(shí)未添加外鍵，則可以使用ALTER命令為數(shù)據(jù)表添加外鍵約束，其語法格式如下。ALTERTABLE數(shù)據(jù)表名ADDFOREIGNKEY(外鍵字段名)REFERENCES主表表名(主鍵字段名)通過設(shè)置數(shù)據(jù)表的約束，能夠保證數(shù)據(jù)表中數(shù)據(jù)的完整性、正確性和一致性。同樣，在進(jìn)行數(shù)據(jù)表的設(shè)置時(shí)，也要求我們嚴(yán)格遵循各種約束，培養(yǎng)良好的職業(yè)素養(yǎng)，職業(yè)素養(yǎng)是職業(yè)人的立身之本。使用ALTER命令為表student添加外鍵約束，具體語句如下。ALTERTABLEstudentADDFOREIGNKEY(GID)REFERENCESgrade(ID);素養(yǎng)小貼士操作數(shù)據(jù)表中的記錄4.3

4.3.1添加數(shù)據(jù)表記錄1．添加新的數(shù)據(jù)表記錄數(shù)據(jù)表創(chuàng)建好后，就可以使用INSERT語句在數(shù)據(jù)表中添加新的數(shù)據(jù)表記錄。其語法格式如下。INSERT[INTO]數(shù)據(jù)表名[(列名,...)]VALUES({expr|DEFAULT},...)|SET列名={expr|DEFAULT},...其中，各參數(shù)的含義如?下。①列名：需要插入數(shù)據(jù)的列名。如果要給全部列插入數(shù)據(jù)，則列名可以省略。②VALUES子句：包含各列需要插入的數(shù)據(jù)清單，數(shù)據(jù)的順序要與列的順序相對(duì)應(yīng)。若沒有給出列名，則在VALUES子句中要給出每一列的值。如果列值為空，則值必須置為NULL，否則會(huì)出錯(cuò)。③SET子句：SET子句用于給列指定值。要插入數(shù)據(jù)的列名在SET子句中指定，等號(hào)后面為指定數(shù)據(jù)。對(duì)于未指定的列，其列值為默認(rèn)值。

4.3.1添加數(shù)據(jù)表記錄【例4-13】向?qū)W生成績(jī)管理系統(tǒng)數(shù)據(jù)庫(kù)ssms的學(xué)生信息表student（數(shù)據(jù)表中的列包括S_ID、Name、Major、Sex、Birthday、Total_Credit、Photo、Note）中插入“201101，黃飛，信息安全，1，2003-02-10，50，NULL，NULL”。USEssms;INSERTINTOstudentVALUES('201101','黃飛','信息安全',1,'2003-02-10',50,NULL,NULL);圖4-10向student表中插入記錄

4.3.1添加數(shù)據(jù)表記錄若表student中專業(yè)的默認(rèn)值為“信息安全”，照片、備注的默認(rèn)值為NULL，則也可以使用如下命令。INSERTINTOstudent(S_ID、Name、Sex、Birthday、Total_Credit)VALUES('201101','黃飛',1,'2003-02-10',50);圖4-11查詢插入結(jié)果也可以使用SET語句實(shí)現(xiàn)。INSERTINTOstudentSETS_ID='201101',Name=’黃飛',Major=default,Sex=1,Birthday='2003-02-10',Total_Credit=50;

4.3.1添加數(shù)據(jù)表記錄2．從已有數(shù)據(jù)表中插入新記錄將其他數(shù)據(jù)表中的數(shù)據(jù)插入新的數(shù)據(jù)表中的語法格式如下。INSERT[INTO]數(shù)據(jù)表名[(列名,...)]SELECT語句【例4-14】在學(xué)生成績(jī)管理系統(tǒng)數(shù)據(jù)庫(kù)ssms中創(chuàng)建數(shù)據(jù)表student1，將數(shù)據(jù)庫(kù)中表student的記錄插入表student1中。（1）創(chuàng)建數(shù)據(jù)表student1。CREATETABLEstudent1(S_IDCHAR(6)NOTNULLPRIMARYKEY,NameCHAR(8)NOTNULL,MajorCHAR(10)NULL,SexTINYINT(1)NOTNULLDEFAULT1,BirthdayDATENOTNULL,Total_CreditTINYINT(1)NULL,PhotoBLOBNULL,NoteTEXTNULL);

4.3.1添加數(shù)據(jù)表記錄查看表student和表student1的記錄，查詢結(jié)果如圖4-12所示。圖4-13插入結(jié)果（2）執(zhí)行以下命令將表student的記錄插入表student1中。圖4-12查看表student和表student1的記錄INSERTINTOstudent1SELECT*FROMstudent;

4.3.2更新數(shù)據(jù)表記錄1．替換舊記錄REPLACE語句可以在插入數(shù)據(jù)之前將與新記錄沖突的舊記錄刪除，從而使新記錄能夠替換舊記錄，正常插入。REPLACE語句的語法格式與INSERT相同?！纠?-15】在數(shù)據(jù)表student1中，【例4-14】例中的記錄已經(jīng)插入，其中學(xué)號(hào)為主鍵（PRIMARYKEY），現(xiàn)在想再插入“201101，劉華，信息安全，1，2003-03-08，48，NULL，NULL”記錄。若直接使用INSERT語句插入數(shù)據(jù)，則會(huì)產(chǎn)生圖4-14所示的錯(cuò)誤。圖4-14直接使用INSERT語句插入數(shù)據(jù)產(chǎn)生錯(cuò)誤INSERTINTOstudent1VALUES('201101','劉華','信息安全',1,'2003-03-08',48,null,null);

4.3.2更新數(shù)據(jù)表記錄使用REPLACE語句，則可以成功插入，如圖4-15所示。圖4-15使用REPLACE語句成功插入數(shù)據(jù)REPLACEINTOstudent1VALUES('201101','劉華','信息安全',1,'2003-03-08',48,null,null);

4.3.2更新數(shù)據(jù)表記錄2．修改數(shù)據(jù)表記錄修改某個(gè)數(shù)據(jù)表中的列值可以使用UPDATE命令，語法格式如下。UPDATE[LOW_PRIORITY][IGNORE]數(shù)據(jù)表名SET列名1=expr1[,列名2=expr2...][WHERE條件]其中，各參數(shù)的含義如下。若語句中不設(shè)定WHERE子句，則更新所有行。列名1、列名2……為要修改的列，expr為列值，expr可以是常量、變量、列名或表達(dá)式?？梢酝瑫r(shí)修改所在數(shù)據(jù)行的多個(gè)列值，中間用逗號(hào)隔開。【例4-16】將學(xué)生成績(jī)管理系統(tǒng)數(shù)據(jù)庫(kù)ssms的數(shù)據(jù)表student1中學(xué)生的總學(xué)分加10。將學(xué)“劉華”的備注填寫為“輔修計(jì)算機(jī)專業(yè)”，學(xué)號(hào)改為“201250”。

4.3.2更新數(shù)據(jù)表記錄UPDATEstudent1SETTotal_Credit=Total_Credit+10;UPDATEstudent1SETS_ID='201250',Note='輔修計(jì)算機(jī)專業(yè)'WHEREName='劉華';SELECTS_ID,Name,Total_Credit,NoteFROMstudent1;圖4-16修改數(shù)據(jù)表記錄

4.3.3刪除數(shù)據(jù)表記錄DELETEFROM數(shù)據(jù)表名[WHERE條件]1．刪除滿足條件的記錄使用DELETE語句可以刪除數(shù)據(jù)表中滿足條件的記錄，語法格式如下。其中，各參數(shù)的含義如下。①FROM子句：用于說明從何處刪除數(shù)據(jù)，數(shù)據(jù)表名為要?jiǎng)h除數(shù)據(jù)的數(shù)據(jù)表名。②WHERE子句：指定的刪除記錄條件。如果省略WHERE子句，則刪除該數(shù)據(jù)表的所有記錄。

4.3.3刪除數(shù)據(jù)表記錄DELETEFROMstudent1WHEREName='劉華';【例4-17】刪除學(xué)生成績(jī)管理系統(tǒng)數(shù)據(jù)庫(kù)ssms的表student1中“劉華”的記錄。也可以使用以下命令。DELETEFROMstudent1WHERES_ID='201250';圖4-17刪除滿足條件的記錄

4.3.3刪除數(shù)據(jù)表記錄2．清除數(shù)據(jù)表數(shù)據(jù)使用TRUNCATETABLE語句將刪除指定數(shù)據(jù)表中的所有數(shù)據(jù)，因此也稱其為清除數(shù)據(jù)表數(shù)據(jù)語句，其語法格式如下。由于TRUNCATETABLE語句將刪除數(shù)據(jù)表中的所有數(shù)據(jù)，且無法恢復(fù)，因此在書寫代碼時(shí)應(yīng)認(rèn)真嚴(yán)謹(jǐn)，避免出現(xiàn)不可挽回的后果，這是一名合格的軟件開發(fā)從業(yè)人員的基本職業(yè)素質(zhì)。TRUNCATETABLE數(shù)據(jù)表名素養(yǎng)小貼士

【知識(shí)拓展】1．MySQL的存儲(chǔ)引擎是什么？MySQL中的數(shù)據(jù)通過用各種不同的技術(shù)存儲(chǔ)在文件（或者內(nèi)存）中。每一種技術(shù)都使用了不同的存儲(chǔ)機(jī)制、索引技巧、鎖定水平，并且最終提供廣泛的、不同的功能。選擇不同的技術(shù)能夠獲得額外的運(yùn)行速度或者功能，從而加強(qiáng)整體的應(yīng)用功能。例如，在研究大量的臨時(shí)數(shù)據(jù)時(shí)，也許需要使用內(nèi)存存儲(chǔ)引擎，通過內(nèi)存存儲(chǔ)引擎在內(nèi)存中存儲(chǔ)所有表格數(shù)據(jù)；也許需要一個(gè)支持事務(wù)處理的數(shù)據(jù)庫(kù)，以確保事務(wù)處理不成功時(shí)數(shù)據(jù)的回退能力。這些不同的技術(shù)及配套的相關(guān)功能在MySQL中稱作存儲(chǔ)引擎（也稱作數(shù)據(jù)表類型）。MySQL默認(rèn)配置了許多不同的存儲(chǔ)引擎，可以預(yù)先設(shè)置或者在MySQL服務(wù)器中啟用?？梢赃x擇適用于服務(wù)器、數(shù)據(jù)庫(kù)和表格的存儲(chǔ)引擎，以便在選擇如何存儲(chǔ)信息、如何檢索這些信息，以及需要數(shù)據(jù)結(jié)合什么性能和功能時(shí)提供最大的靈活性。

【知識(shí)拓展】在MySQL客戶端中，使用以下命令可以查看MySQL支持的引擎。SHOWENGINES;圖4-18查看MySQL支持的引擎

【知識(shí)拓展】常見的3種存儲(chǔ)引擎如下。（1）MyISAMMyISAM不支持事務(wù)，也不支持外鍵。要求訪問速度快，對(duì)事務(wù)完整性沒有要求，或者以SELECT、INSERT為主的應(yīng)用基本都可以使用這個(gè)引擎來創(chuàng)建數(shù)據(jù)表。每個(gè)MyISAM數(shù)據(jù)表由存儲(chǔ)在硬盤上的3個(gè)文件組成，每個(gè)文件都以數(shù)據(jù)表名稱為文件主名，但是擴(kuò)展名不同，用以區(qū)分文件類型。擴(kuò)展名分別如下。①.frm：存儲(chǔ)表定義。②.myd：MYData，存儲(chǔ)數(shù)據(jù)。③.myi：MYIndex，存儲(chǔ)索引。

【知識(shí)拓展】2．MySQL中有哪幾種索引？有何區(qū)別？（1）B-Tree索引B-Tree索引是MySQL數(shù)據(jù)庫(kù)中使用最為頻繁的索引類型，除了Archive存儲(chǔ)引擎，其他所有存儲(chǔ)引擎都支持B-Tree索引，如圖4-19所示。不僅在MySQL中如此，實(shí)際上在其他很多DBMS中，B-Tree索引也同樣是最主要的索引類型，這主要是因?yàn)锽-Tree索引的存儲(chǔ)結(jié)構(gòu)在數(shù)據(jù)庫(kù)的數(shù)據(jù)檢索中有非常優(yōu)異的表現(xiàn)。

圖4-19B-Tree索引

【知識(shí)拓展】（2）哈希索引哈希（Hash）索引就是采用一定的哈希算法，把鍵值換算成新的哈希值，檢索時(shí)不需要類似B-Tree那樣從根節(jié)點(diǎn)到葉子節(jié)點(diǎn)逐級(jí)查找，只需使用一次哈希算法即可立刻定位到相應(yīng)的位置，速度非?？?。（3）兩者之間的區(qū)別①如果是等值查詢，則哈希索引明顯有絕對(duì)優(yōu)勢(shì)，因?yàn)橹恍枰?jīng)過一次運(yùn)算即可找到對(duì)應(yīng)的鍵值。如果鍵值不是唯一的，就需要先找到該鍵所在的位置，然后根據(jù)鏈表往后掃描，直到找到相應(yīng)的數(shù)據(jù)。②如果是范圍查詢檢索，哈希索引就毫無用武之地，因?yàn)樵仁怯行虻逆I值，經(jīng)過哈希算法后有可能變得不連續(xù)了，就沒辦法再利用索引完成范圍查詢檢索了。③哈希索引沒辦法利用索引完成排序，及LIKE‘xxx%’這樣的部分模糊查詢。④哈希索引不支持多列聯(lián)合索引的最左匹配規(guī)則。⑤B+Tree索引的關(guān)鍵字檢索效率比較平均，不像B-Tree那樣波動(dòng)幅度大，在有大量重復(fù)鍵值的情況下，哈希索引的效率是極低的，因?yàn)榇嬖谒^的哈希碰撞問題。

【小結(jié)】本項(xiàng)目對(duì)數(shù)據(jù)表的創(chuàng)建、修改和刪除，數(shù)據(jù)表記錄的添加、更新和刪除進(jìn)行了系統(tǒng)化的介紹，并對(duì)數(shù)據(jù)表的約束進(jìn)行了詳細(xì)闡述，同時(shí)以案例形式介紹了相關(guān)操作及用法。數(shù)據(jù)表及數(shù)據(jù)表記錄的操作決定了數(shù)據(jù)庫(kù)中填入的數(shù)據(jù)，而數(shù)據(jù)表的約束則維護(hù)了數(shù)據(jù)庫(kù)的完整性。

【任務(wù)訓(xùn)練4】操作圖書管理系統(tǒng)數(shù)據(jù)庫(kù)的數(shù)據(jù)表及數(shù)據(jù)表記錄1．實(shí)驗(yàn)?zāi)康恼莆誦ms數(shù)據(jù)庫(kù)中數(shù)據(jù)表的創(chuàng)建、修改和刪除。掌握bms數(shù)據(jù)庫(kù)中數(shù)據(jù)表記錄的添加、更新和刪除。2．實(shí)驗(yàn)內(nèi)容完成bms數(shù)據(jù)庫(kù)中數(shù)據(jù)表的創(chuàng)建、修改和刪除。進(jìn)行bms數(shù)據(jù)庫(kù)中數(shù)據(jù)表記錄的添加、更新和刪除。3．實(shí)驗(yàn)步驟（1）在圖書管理系統(tǒng)數(shù)據(jù)庫(kù)bms中創(chuàng)建圖書類別表bookcategory、圖書信息表bookinfo、讀者信息表readerinfo和借閱信息表borrowinfo。①創(chuàng)建并使用數(shù)據(jù)庫(kù)bms。

CREATEDATABASEbms;USEbms;

【任務(wù)訓(xùn)練4】操作圖書管理系統(tǒng)數(shù)據(jù)庫(kù)的數(shù)據(jù)表及數(shù)據(jù)表記錄②創(chuàng)建圖書類別表bookcategory。

CREATETABLEbookcategory(category_idINTPRIMARYKEY,categoryVARCHAR(20)NOTNULLUNIQUE,parent_idINTNOTNULL);CREATETABLEbookinfo(book_idINTPRIMARYKEY,category_idINT,book_nameVARCHAR(20)NOTNULLUNIQUE,authorVARCHAR(20)NOTNULL,priceFLOAT(5,2)NOTNULL,pressVARCHAR(20)DEFAULT'人民郵電出版社',pubdateDATENOTNULL,storeINTNOTNULL,CONSTRAINTfk_bcidFOREIGNKEY(category_id)REFERENCESbookcategory(category_id));③創(chuàng)建圖書信息表bookinfo。

【任務(wù)訓(xùn)練4】操作圖書管理系統(tǒng)數(shù)據(jù)庫(kù)的數(shù)據(jù)表及數(shù)據(jù)表記錄④創(chuàng)建讀者信息表readerinfo。

CREATETABLEreaderinfo(card_idCHAR(18)PRIMARYKEY,nameVARCHAR(20)NOTNULL,sexENUM('男','女','保密')DEFAULT'保密',ageTINYINT,telCHAR(11)NOTNULL,balanceDECIMAL(7,3)DEFAULT'200.000');⑤創(chuàng)建借閱信息表borrowinfo。

CREATETABLEborrowinfo(book_idINT,card_idCHAR(18),borrow_dateDATENOTNULL,return_dateDATENOTNULL,statusCHAR(1)NOTNULL);

【任務(wù)訓(xùn)練4】操作圖書管理系統(tǒng)數(shù)據(jù)庫(kù)的數(shù)據(jù)表及數(shù)據(jù)表記錄（2）查看數(shù)據(jù)表。使用SHOWTABLES命令查看剛才創(chuàng)建的4個(gè)數(shù)據(jù)表，如圖4-20所示。（3）在創(chuàng)建的4個(gè)數(shù)據(jù)表中插入數(shù)據(jù)。①插入圖書類別表bookcategory的數(shù)據(jù)。

INSERTINTObookcategoryVALUES('1','藝術(shù)','0');INSERTINTObookcategoryVALUES('2','醫(yī)學(xué)','0');INSERTINTObookcategoryVALUES('3','編程語言','1');INSERTINTObookcategoryVALUES('4','數(shù)據(jù)庫(kù)','1');INSERTINTObookcategoryVALUES('5','兒科學(xué)','2');

圖4-20查看創(chuàng)建的數(shù)據(jù)表

【任務(wù)訓(xùn)練4】操作圖書管理系統(tǒng)數(shù)據(jù)庫(kù)的數(shù)據(jù)表及數(shù)據(jù)表記錄②插入圖書信息表bookinfo的數(shù)據(jù)。

INSERTINTObookinfoVALUES('20150201','3','Python從入門到精通','丁征','135.00','人民郵電出版社','2020-03-01','5');INSERTINTObookinfoVALUES('20150202','4','MySQL數(shù)據(jù)庫(kù)應(yīng)用與管理','魯林','43.00','機(jī)械工業(yè)出版社','2019-04-01','2');INSERTINTObookinfoVALUES('20150301','3','Java從入門到項(xiàng)目實(shí)戰(zhàn)','李源華','100.00','水利水電出版社','2020-03-15','3');INSERTINTObookinfoVALUES('20160801','5','內(nèi)科學(xué)','葛志宏','118.00','人民衛(wèi)生出版社','2019-07-01','1');INSERTINTObookinfoVALUES('20170401','5','零基礎(chǔ)小兒推拿','廖天宇','39.80','人民郵電出版社','2019-04-01','4');

【任務(wù)訓(xùn)練4】操作圖書管理系統(tǒng)數(shù)據(jù)庫(kù)的數(shù)據(jù)表及數(shù)據(jù)表記錄③插入讀者信息表readerinfo的數(shù)據(jù)。

INSERTINTOreaderinfoVALUES('21021019950501****','楊磊','男','22','135****5555','500.000');INSERTINTOreaderinfoVALUES('21021019960401****','劉鑫','男','21','135****4444','400.000');INSERTINTOreaderinfoVALUES('21021019970301****','王鵬','男','20','135****3333','300.000');INSERTINTOreaderinfoVALUES('21021019980201****','李月','女','19','135****2222','200.000');INSERTINTOreaderinfoVALUES('21021019990101****','張飛','女','18','135****1111','300.000');

④插入借閱信息表borrowinfo的數(shù)據(jù)。INSERTINTOborrowinfoVALUES('20150201','21021019990101****','2017-05-05','2017-09-15','是');INSERTINTOborrowinfoVALUES('20160801','21021019980201****','2017-06-05','2017-10-05','是');INSERTINTOborrowinfoVALUES('20150301','21021019970301****','2017-08-05','2017-09-05','是');INSERTINTOborrowinfoVALUES('20150202','21021019970301****','2017-10-15','2018-05-15','否');INSERTINTOborrowinfoVALUES('20170401','21021019980201****','2017-10-18','2017-11-28','否');INSERTINTOborrowinfoVALUES('20150202','21021019980201****','2017-07-14','2017-09-08','否');

【任務(wù)訓(xùn)練4】操作圖書管理系統(tǒng)數(shù)據(jù)庫(kù)的數(shù)據(jù)表及數(shù)據(jù)表記錄（4）在讀者信息表readerinfo中增加新的一列email，然后重命名為email_bak。①在讀者信息表readerinfo中增加新的一列email。

ALTERTABLEreaderinfoADDemailVARCHAR(30);

②使用DESCRIBEreaderinfo命令查看剛才新增的列，如圖4-22所示。圖4-21增加新列圖4-22查看新增的列

【任務(wù)訓(xùn)練4】操作圖書管理系統(tǒng)數(shù)據(jù)庫(kù)的數(shù)據(jù)表及數(shù)據(jù)表記錄③將列email重命名為email_bak。

ALTERTABLEreaderinfoCHANGEemailemail_bakVARCHAR(30);

圖4-23重命名列

【思考與練習(xí)】一、填空題1．MySQL可以用

或

命令修改數(shù)據(jù)表。2．語句MySQL中修改數(shù)據(jù)表結(jié)構(gòu)的命令是

。3．語句MySQL中查看數(shù)據(jù)表結(jié)構(gòu)的命令是

。二、選擇題1．性別字段最適合選擇為（）。A．字符串類型 B．整數(shù)型型 C．枚舉類型 D．浮點(diǎn)類型2．（）字段可以采用默認(rèn)?值。A．姓名 B．專業(yè) C．備注D．出生時(shí)間3．顯示數(shù)據(jù)庫(kù)中的表使用（）。A．DESCRIBEB．SHOWTABLESC．SHOWDATABASESD．DELETE4．刪除數(shù)據(jù)表的所有記錄使用（）。A．DELETEB．DROPTABLE C．TRUNCATETABLED．A和C5．刪除記錄內(nèi)容不能使用（）。A．UPDATE B．DELETE和INSERT