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

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

INSERT[IGNORE][INTO]

表名(字段名稱1[,

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

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

||102|可再生能源學院|院長

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

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

||102|可再生能源學院|院長||103|NULL|王萊

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

|李大國

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

|馬逖||105|經(jīng)濟與管理學院|周海明||106|控制與計算機工程學院|姜尚|+--------+----------------------+----------+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|外國語學院

|李大國

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

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

如果用INSERT語句插入這兩條數(shù)據(jù)，則執(zhí)行結(jié)果將出錯。INSERTINTOdepartment1VALUES("101","外國語學院","李大國"),("107","數(shù)理學院","董蔚來");ERROR1062(23000):Duplicateentry'101'forkey'department.PRIMARY'mysql>REPLACEINTOdepartment1->VALUES("101","外國語學院","李大國"),->("107","數(shù)理學院","董蔚來");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ù)的記錄，只有滿足條件的記錄才會被修改。如果省略WHERE子句，則默認修改所有的記錄。4.2修改數(shù)據(jù)1．修改指定記錄的數(shù)據(jù)【例4-7】將department1表中院系代碼deptno為103的院系名稱deptname修改為“能源動力與機械工程學院”。UPDATEdepartment1SETdeptname="能源動力與機械工程學院"WHEREdeptno="103";mysql>SELECT*FROMdepartment1;+--------+----------------------+----------+|deptno|deptname|director|+--------+----------------------+----------+|101|外國語學院

|李大國

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

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

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

|李大國

||102|可再生能源學院|院長||103|能源動力與機械工程學院|王萊||104|電氣與電子工程學院

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

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

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

|院長李大國||102|可再生能源學院

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

|院長周海明||106|控制與計算機工程學院

|院長姜尚

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

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

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

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

("120211030110","王琦","男","2003-01-23","團員","機械2101","103",600,"優(yōu)秀學生干部，二等獎學金"),

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