MySQL數(shù)據(jù)庫教程課件

第4章數(shù)據(jù)操作學(xué)習(xí)目標(biāo)熟練掌握INSERT、UPDATE和DELETE語句。了解不同的數(shù)據(jù)完整性約束對數(shù)據(jù)操作結(jié)果的影響。4.1插入數(shù)據(jù)INSERT語句：

INSERT[IGNORE][INTO]

表名(字段名稱1[,

字段名稱2…])VALUES({表達(dá)式1|DEFAULT}[,{表達(dá)式2|DEFAULT}…]);（1）IGNORE：當(dāng)插入不符合數(shù)據(jù)完整性約束的數(shù)據(jù)時(shí)，不執(zhí)行該語句，當(dāng)作一條警告處理。（2）字段名稱：省略時(shí)表示要插入全部字段的數(shù)據(jù)，否則必須指定要插入數(shù)據(jù)的字段名稱。（3）VALUES子句：指定各個(gè)字段要插入的具體數(shù)據(jù)。數(shù)據(jù)的順序與字段名稱的順序一致。（4）表達(dá)式：可以是常量、變量或者一個(gè)表達(dá)式，也可以是空值。字符串型或日期和時(shí)間型數(shù)據(jù)常量必須用英文單引號或雙引號引起來。（5）DEFAULT：插入該字段的默認(rèn)值4.1插入數(shù)據(jù)1．插入一條記錄的全部數(shù)據(jù)【例4-1】向院系表department中插入一條完整的院系數(shù)據(jù)（101，外國語學(xué)院，李大國）。（1）對所有插入數(shù)據(jù)省略字段名稱INSERTINTOdepartmentVALUES("101","外國語學(xué)院","李大國");（2）對所有插入數(shù)據(jù)不省略字段名稱INSERTINTOdepartment(deptno,deptname,director)VALUES("101","外國語學(xué)院","李大國");mysql>INSERTINTOdepartment->VALUES("101","外國語學(xué)院","李大國");QueryOK,1rowaffected(0.01sec)mysql>SELECT*FROMdepartment;+--------+------------+----------+|deptno|deptname|director|+--------+------------+----------+|101|外國語學(xué)院|李大國

|+--------+------------+----------+4.1插入數(shù)據(jù)2．插入一條記錄的部分?jǐn)?shù)據(jù)【例4-2】向院系表department中插入一條院系數(shù)據(jù)（102，可再生能源學(xué)院），僅插入院系代碼deptno和院系名稱deptname字段的數(shù)據(jù)。INSERTINTOdepartment(deptno,deptname)VALUES("102","可再生能源學(xué)院");director默認(rèn)值為院長mysql>INSERTINTOdepartment(deptno,deptname)->VALUES("102","可再生能源學(xué)院");QueryOK,1rowaffected(0.01sec)mysql>SELECT*FROMdepartment;+--------+----------------+----------+|deptno|deptname|director|+--------+----------------+----------+|101|外國語學(xué)院|李大國

||102|可再生能源學(xué)院|院長

|+--------+----------------+----------+4.1插入數(shù)據(jù)2．插入一條記錄的部分?jǐn)?shù)據(jù)【例4-3】向院系表department中插入一條院系數(shù)據(jù)（103，王萊），僅插入院系代碼deptno和負(fù)責(zé)人director字段的數(shù)據(jù)。

INSERTINTOdepartment(deptno,director)VALUES("103","王萊");deptname默認(rèn)值為NULLmysql>INSERTINTOdepartment(deptno,director)->VALUES("103","王萊");QueryOK,1rowaffected(0.01sec)mysql>SELECT*FROMdepartment;+--------+----------------+----------+|deptno|deptname|director|+--------+----------------+----------+|101|外國語學(xué)院|李大國

||102|可再生能源學(xué)院|院長||103|NULL|王萊

|+--------+----------------+----------+4.1插入數(shù)據(jù)3．插入多條記錄的數(shù)據(jù)，各條數(shù)據(jù)之間用英文逗號隔開。【例4-4】向院系表department中插入3條院系數(shù)據(jù)（104，電氣與電子工程學(xué)院，馬逖）、（105，經(jīng)濟(jì)與管理學(xué)院，周海明）和（106，控制與計(jì)算機(jī)工程學(xué)院，姜尚）。INSERTINTOdepartmentVALUES("104","電氣與電子工程學(xué)院","馬逖"),("105","經(jīng)濟(jì)與管理學(xué)院","周海明"),("106","控制與計(jì)算機(jī)工程學(xué)院","姜尚");mysql>INSERTINTOdepartment->VALUES("104","電氣與電子工程學(xué)院","馬逖"),->("105","經(jīng)濟(jì)與管理學(xué)院","周海明"),->("106","控制與計(jì)算機(jī)工程學(xué)院","姜尚");QueryOK,3rowsaffected(0.01sec)Records:3Duplicates:0Warnings:0mysql>SELECT*FROMdepartment;+--------+----------------------+----------+|deptno|deptname|director|+--------+----------------------+----------+|101|外國語學(xué)院

|李大國

||102|可再生能源學(xué)院|院長||103|NULL|王萊||104|電氣與電子工程學(xué)院

|馬逖||105|經(jīng)濟(jì)與管理學(xué)院|周海明||106|控制與計(jì)算機(jī)工程學(xué)院|姜尚|+--------+----------------------+----------+4.1插入數(shù)據(jù)4.插入查詢結(jié)果中的數(shù)據(jù)INSERT[IGNORE][INTO]表名1(字段名稱1[,字段名稱2,…])SELECT(字段名稱1[,字段名稱2,…])FROM表名2;【例4-5】新建數(shù)據(jù)表department1，其結(jié)構(gòu)與院系表department完全相同，然后將department表中的所有數(shù)據(jù)插入department1表中。CREATETABLEdepartment1LIKEdepartment;INSERTINTOdepartment1SELECT*FROMdepartment;

mysql>SELECT*FROMdepartment1;+--------+----------------------+----------+|deptno|deptname|director|+--------+----------------------+----------+|101|外國語學(xué)院

|李大國

||102|可再生能源學(xué)院|院長||103|NULL|王萊||104|電氣與電子工程學(xué)院

|馬逖||105|經(jīng)濟(jì)與管理學(xué)院|周海明||106|控制與計(jì)算機(jī)工程學(xué)院|姜尚|+--------+----------------------+----------+4.1插入數(shù)據(jù)5．插入并替換已存在的數(shù)據(jù)REPLACE語句的語法格式與INSERT語句基本相同。但在插入的數(shù)據(jù)不滿足主鍵約束時(shí)，REPLACE語句可以在插入數(shù)據(jù)之前將與新數(shù)據(jù)沖突的舊數(shù)據(jù)刪除，使新數(shù)據(jù)能夠正常插入。4.1插入數(shù)據(jù)5．插入并替換已存在的數(shù)據(jù)【例4-6】向department1表中插入兩條院系數(shù)據(jù)（101，外國語學(xué)院，李大國）和（107，數(shù)理學(xué)院，董蔚來），其中有一條數(shù)據(jù)與數(shù)據(jù)庫中已有的數(shù)據(jù)完全相同。REPLACEINTOdepartment1VALUES("101","外國語學(xué)院","李大國"),("107","數(shù)理學(xué)院","董蔚來");

如果用INSERT語句插入這兩條數(shù)據(jù)，則執(zhí)行結(jié)果將出錯(cuò)。INSERTINTOdepartment1VALUES("101","外國語學(xué)院","李大國"),("107","數(shù)理學(xué)院","董蔚來");ERROR1062(23000):Duplicateentry'101'forkey'department.PRIMARY'mysql>REPLACEINTOdepartment1->VALUES("101","外國語學(xué)院","李大國"),->("107","數(shù)理學(xué)院","董蔚來");QueryOK,3rowsaffected(0.01sec)Records:2Duplicates:1Warnings:04.2修改數(shù)據(jù)UPDATE語句：UPDATE表名SET字段名稱1=值1[,字段名稱2=值2…][WHERE條件];（1）SET子句：用于指定要修改的字段名稱及其值。（2）WHERE子句：用于限定要修改數(shù)據(jù)的記錄，只有滿足條件的記錄才會(huì)被修改。如果省略WHERE子句，則默認(rèn)修改所有的記錄。4.2修改數(shù)據(jù)1．修改指定記錄的數(shù)據(jù)【例4-7】將department1表中院系代碼deptno為103的院系名稱deptname修改為“能源動(dòng)力與機(jī)械工程學(xué)院”。UPDATEdepartment1SETdeptname="能源動(dòng)力與機(jī)械工程學(xué)院"WHEREdeptno="103";mysql>SELECT*FROMdepartment1;+--------+----------------------+----------+|deptno|deptname|director|+--------+----------------------+----------+|101|外國語學(xué)院

|李大國

||102|可再生能源學(xué)院|院長||103|NULL|王萊||104|電氣與電子工程學(xué)院

|馬逖||105|經(jīng)濟(jì)與管理學(xué)院|周海明||106|控制與計(jì)算機(jī)工程學(xué)院|姜尚||107|數(shù)理學(xué)院

|董蔚來|+--------+----------------------+----------+4.2修改數(shù)據(jù)1．修改指定記錄的數(shù)據(jù)【例4-8】將department1表中院系代碼deptno為102的負(fù)責(zé)人director修改為“張國慶”。UPDATEdepartment1SETdirector="張國慶"WHEREdeptno="102";mysql>SELECT*FROMdepartment1;+--------+----------------------+----------+|deptno|deptname|director|+--------+----------------------+----------+|101|外國語學(xué)院

|李大國

||102|可再生能源學(xué)院|院長||103|能源動(dòng)力與機(jī)械工程學(xué)院|王萊||104|電氣與電子工程學(xué)院

|馬逖||105|經(jīng)濟(jì)與管理學(xué)院|周海明||106|控制與計(jì)算機(jī)工程學(xué)院

|姜尚||107|數(shù)理學(xué)院

|董蔚來|+--------+----------------------+----------+4.2修改數(shù)據(jù)2．修改全部記錄的數(shù)據(jù)修改全部記錄的數(shù)據(jù)時(shí)，不需要使用WHERE子句?！纠?-9】將department1表中所有院系的負(fù)責(zé)人director均修改為“院長+姓名”的形式。UPDATEdepartment1SETdirector=CONCAT("院長",director);CONCAT函數(shù)的功能是將多個(gè)字符串連接成一個(gè)字符串。mysql>SELECT*FROMdepartment1;+--------+------------------------+------------+|deptno|deptname|director|+--------+------------------------+------------+|101|外國語學(xué)院

|院長李大國||102|可再生能源學(xué)院

|院長張國慶||103|能源動(dòng)力與機(jī)械工程學(xué)院|院長王萊||104|電氣與電子工程學(xué)院|院長馬逖||105|經(jīng)濟(jì)與管理學(xué)院

|院長周海明||106|控制與計(jì)算機(jī)工程學(xué)院

|院長姜尚

||107|數(shù)理學(xué)院

|院長董蔚來|+--------+------------------------+------------+4.3刪除數(shù)據(jù)DELETE語句：DELETEFROM表名[WHERE條件];1．刪除滿足指定條件的數(shù)據(jù)【例4-10】將department1表中院系代碼deptno為101的數(shù)據(jù)刪除。

DELETEFROMdepartment1WHEREdeptno="101";4.3刪除數(shù)據(jù)DELETE語句：DELETEFROM表名[WHERE條件];2．刪除全部數(shù)據(jù)【例4-11】使用DELETE語句刪除department1表中的所有院系數(shù)據(jù)。DELETEFROMdepartment1;mysql>SELECT*FROMdepartment1;Emptyset(0.00sec)4.3刪除數(shù)據(jù)2．刪除全部數(shù)據(jù)TRUNCATE語句：TRUNCATE[TABLE]表名;（1）使用不帶WHERE子句的DELETE語句，會(huì)刪除數(shù)據(jù)表中的所有數(shù)據(jù)，但仍然會(huì)在數(shù)據(jù)庫中保留數(shù)據(jù)表的定義；（2）使用TRUNCATE語句，會(huì)刪除原來的數(shù)據(jù)表并重新創(chuàng)建數(shù)據(jù)表，執(zhí)行速度比DELETE語句快?！纠?-12】使用TRUNCATE語句刪除department1表中的所有院系數(shù)據(jù)。TRUNCATEdepartment1;4.4課堂案例：學(xué)生成績管理數(shù)據(jù)庫的數(shù)據(jù)操作1．插入各張表中的數(shù)據(jù)對于創(chuàng)建數(shù)據(jù)表時(shí)指定了外鍵約束、建立了參照完整性的子表，必須先插入父表中的數(shù)據(jù)，然后才能插入子表中的數(shù)據(jù)。例如，學(xué)生表student和院系表department之間存在外鍵約束，department表是父表、student表是子表，外鍵約束要求student表中的院系代碼deptno字段值要么為空值，要么是department表中已經(jīng)存在的主鍵值。因此，必須先插入department表中的數(shù)據(jù)，然后才能插入student表中的數(shù)據(jù)。4.4課堂案例：學(xué)生成績管理數(shù)據(jù)庫的數(shù)據(jù)操作1．插入各張表中的數(shù)據(jù)（1）向院系表department中插入院系數(shù)據(jù)。如果前面已經(jīng)插入過數(shù)據(jù)，則進(jìn)行覆蓋。插入前5條數(shù)據(jù)。REPLACEINTOdepartmentVALUES("101","外國語學(xué)院","李大國"),("102","可再生能源學(xué)院","張國慶"),("103","能源動(dòng)力與機(jī)械工程學(xué)院","王萊"),("104","電氣與電子工程學(xué)院","馬逖"),("105","經(jīng)濟(jì)與管理學(xué)院","周海明");4.4課堂案例：學(xué)生成績管理數(shù)據(jù)庫的數(shù)據(jù)操作1．插入各張表中的數(shù)據(jù)（2）向?qū)W生表student中插入學(xué)生數(shù)據(jù)。插入前5條數(shù)據(jù)，如果獎(jiǎng)懲情況無數(shù)據(jù)，則awards字段可以插入空值（NULL）或者空字符串（""）。INSERTINTOstudentVALUES("120211010103","宋洪博","男","2003-05-15","黨員","英語2101","101",698,"三好學(xué)生，一等獎(jiǎng)學(xué)金"),

("120211010105","劉向志","男","2002-10-08","團(tuán)員","英語2101","101",625,NULL),

("120211010230","李媛媛","女","2003-09-02","團(tuán)員","英語2102","101",596,""),

("120211030110","王琦","男","2003-01-23","團(tuán)員","機(jī)械2101","103",600,"優(yōu)秀學(xué)生干部，二等獎(jiǎng)學(xué)金"),

("120211030409","張虎","男","2003-07-18","群眾","機(jī)械2104","103",650,"北京市數(shù)學(xué)建模一等獎(jiǎng)");4.4課堂案例：學(xué)生成績管理數(shù)據(jù)庫的數(shù)據(jù)操作1．插入各張表中的數(shù)據(jù)（3）向課程表course中插入課程數(shù)據(jù)。插入前5條數(shù)據(jù)。INSERTINTOcourseVALUES("10101400","學(xué)術(shù)英語",64),("10101410","通用英語",48),("10300710","現(xiàn)代控制理論",40),("10400350","模擬電子技術(shù)基礎(chǔ)",56),("10500131","證券投資學(xué)",32);4.4課堂案例：學(xué)生成績管理數(shù)據(jù)庫的數(shù)據(jù)操作1．插入各張表中的數(shù)據(jù)（4）向教師表teacher中插入教師數(shù)據(jù)。插入前5條數(shù)據(jù)。INSERTINTOteacherVALUES("10100391","楊麗","女","副教授","101"),("10112583","周家羅","男","教授","101"),("10309242","宋江科","男","教授","103"),("10423769","林達(dá)","女","教授","104"),("10501561","趙曉麗","女","副教授","105");4.4課堂案例：學(xué)生成績管理數(shù)據(jù)庫的數(shù)據(jù)操作1．插入各張表中的數(shù)據(jù)（5）向選修成績表score中插入選修成績數(shù)據(jù)。插入前5條數(shù)據(jù)INSERTINTOscoreVALUES("120211010103","10101400",85,"2021-2022","2"),("120211010103","10500131",93,"2021-2022","1"),("120211010103","10600611",88,"2021-2022","1"),("120211010103","10700140",70,"2021-2022","1"),("120211010105","10101400",68,"2021-2022","2");4.4課堂案例：學(xué)生成績管理數(shù)據(jù)庫的數(shù)據(jù)操作1．插入各張表中的數(shù)據(jù)（6）向講授安排表teaching中插入講授安排數(shù)據(jù)。插入前5條數(shù)據(jù)。INSERTINTOteachingVALUES("英語2101","10100391","10101400"),("財(cái)務(wù)2101","10100391","10101400"),("英語2102","10112583","10101400"),("財(cái)務(wù)2101","10112583","10101410"),("電氣2111","10309242","10300710");4.4課堂案例：學(xué)生成績管理數(shù)據(jù)庫的數(shù)據(jù)操作2．修改表中的數(shù)據(jù)（1）修改未指定外鍵約束的字段值不受任何限制，可以直接修改。（2）只要設(shè)置了外鍵約束，對父表的主鍵字段值和子表的外鍵字段值的修改就一定會(huì)受到參照完整性的限制。例如，對于學(xué)生表student和院系表department之間的外鍵約束，修改子表中的deptno外鍵字段值時(shí)，要么為空值，要么是父表中已經(jīng)存在的主鍵值；修改父表中的deptno主鍵字段值時(shí)，不同的ONUPDATE選項(xiàng)有不同的效果。①RESTRICT：如果student表中存在該院系的學(xué)生，則不允許修改。②CASCADE：允許修改，同時(shí)自動(dòng)修改student表中相關(guān)學(xué)生的deptno字段的值為新的院系代碼。③SETNULL：允許修改，同時(shí)自動(dòng)修改student表中相關(guān)學(xué)生的deptno字段的值為NULL。④NOACTION：不采取動(dòng)作，其作用和RESTRICT一樣。⑤SETDEFAULT：允許修改，同時(shí)自動(dòng)修改student表中相關(guān)學(xué)生的deptno字段的值為默認(rèn)值。4.4課堂案例：學(xué)生成績管理數(shù)據(jù)庫的數(shù)據(jù)操作2．修改表中的數(shù)據(jù)（1）由于學(xué)生轉(zhuǎn)專業(yè)，因此需要將學(xué)號sno為120211080101的班級classno修改為“風(fēng)電2101”，院系不變。UPDATEstudentSETclassno="風(fēng)電2101"WHEREsno="120211080101";由于班級classno字段既不是主鍵也不是外鍵，因此可以直接修改。4.4課堂案例