項(xiàng)目4數(shù)據(jù)操作項(xiàng)目目標(biāo)熟練掌握INSERT、UPDATE和DELETE語句的使用。了解不同的數(shù)據(jù)完整性約束對數(shù)據(jù)操作結(jié)果的影響。任務(wù)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ù)時，不執(zhí)行該語句，而是發(fā)出警告。（2）INTO表名：指定待插入數(shù)據(jù)的表的名稱。（3）字段名稱：指定需要插入數(shù)據(jù)的字段名稱。（4）VALUES子句：指定各個字段需要插入的具體數(shù)據(jù)。（5）表達(dá)式：可以是常量、變量、計(jì)算表達(dá)式，其值的數(shù)據(jù)類型要與字段的數(shù)據(jù)類型一致。（6）DEFAULT：插入該字段的默認(rèn)值。4.1插入數(shù)據(jù)1．插入一條完整的數(shù)據(jù)【例4-1】向圖書表book中插入一條完整的書目數(shù)據(jù)（100582，大學(xué)物理，李大國，博達(dá)教育出版社，2020-03-01，43.90，2）。（1）在插入數(shù)據(jù)時省略字段名稱，數(shù)據(jù)的順序必須和數(shù)據(jù)表結(jié)構(gòu)的字段順序相同。INSERTINTObook

VALUES('100582','大學(xué)物理','李大國','博達(dá)教育出版社','2020-03-01',43.90,2);mysql>SELECT*FROMbook;+---------+----------+--------+----------------+------------+-------+----------+|book_id|title

|author|publisher

|pubdate

|price|stocknum|+---------+----------+--------+----------------+------------+-------+----------+|100582|大學(xué)物理|李大國|博達(dá)教育出版社|2020-03-01|43.90|

2|+---------+----------+--------+----------------+------------+-------+----------+1rowinset(0.00sec)4.1插入數(shù)據(jù)1．插入一條完整的數(shù)據(jù)【例4-1】向圖書表book中插入一條完整的書目數(shù)據(jù)（100582，大學(xué)物理，李大國，博達(dá)教育出版社，2020-03-01，43.90，2）。（1）在插入數(shù)據(jù)時省略字段名稱，數(shù)據(jù)的順序必須和數(shù)據(jù)表結(jié)構(gòu)的字段順序相同。INSERTINTObook

VALUES('100582','大學(xué)物理','李大國','博達(dá)教育出版社','2020-03-01',43.90,2);（2）在插入數(shù)據(jù)的不省略字段名稱INSERTINTObook(book_id,title,author,publisher,pubdate,price,stocknum)VALUES('100582','大學(xué)物理','李大國','博達(dá)教育出版社','2020-03-01',43.90,2);4.1插入數(shù)據(jù)2．插入一條數(shù)據(jù)的部分字段值【例4-2】向圖書表book中插入一條書目數(shù)據(jù)（110001，平面設(shè)計(jì)基礎(chǔ)，職帆啟航出版社），僅插入圖書編號book_id、書名title和出版社publisher字段的數(shù)據(jù)。INSERTINTObook(book_id,title,publisher)

VALUES('110001','平面設(shè)計(jì)基礎(chǔ)','職帆啟航出版社');mysql>SELECT*FROMbook;+---------+--------------+--------+----------------+------------+-------+----------+|book_id|title

|author|publisher

|pubdate

|price|stocknum|+---------+--------------+--------+----------------+------------+-------+----------+|100582|大學(xué)物理|李大國|博達(dá)教育出版社|2020-03-01|43.90|2||110001|平面設(shè)計(jì)基礎(chǔ)|NULL

|職帆啟航出版社|NULL

|NULL|

1|+---------+--------------+--------+----------------+------------+-------+----------+2rowsinset(0.00sec)stocknum字段插入了默認(rèn)值1，未指定默認(rèn)值的字段值插入了NULL。4.1插入數(shù)據(jù)3．插入多條記錄的數(shù)據(jù)，各條數(shù)據(jù)之間用英文逗號隔開?！纠?-3】向圖書表book中插入3條書目數(shù)據(jù)（100023，計(jì)算機(jī)科學(xué)導(dǎo)論，劉曉婷，博達(dá)教育出版社）、（110705，空間數(shù)據(jù)庫原理，宋曉松，職帆啟航出版社）和（120087，程序設(shè)計(jì)基礎(chǔ)，趙曉妝，知行聯(lián)合出版社）。插入數(shù)據(jù)僅涉圖書編號book_id、書名title、作者author和出版社publisher字段。INSERTINTObook(book_id,title,author,publisher)

VALUES('100023','計(jì)算機(jī)科學(xué)導(dǎo)論','劉曉婷','博達(dá)教育出版社'),

('110705','空間數(shù)據(jù)庫原理','宋曉松','職帆啟航出版社'),

('120087','程序設(shè)計(jì)基礎(chǔ)','趙曉妝','知行聯(lián)合出版社');mysql>SELECT*FROMbook;+---------+----------------+--------+----------------+------------+-------+----------+|book_id|title

|author|publisher

|pubdate

|price|stocknum|+---------+----------------+--------+----------------+------------+-------+----------+|100023|計(jì)算機(jī)科學(xué)導(dǎo)論|劉曉婷|博達(dá)教育出版社|NULL

|NULL|

1||100582|大學(xué)物理

|李大國|博達(dá)教育出版社|2020-03-01|43.90|

2||110001|平面設(shè)計(jì)基礎(chǔ)|NULL

|職帆啟航出版社|NULL|NULL|

1||110705|空間數(shù)據(jù)庫原理|宋曉松|職帆啟航出版社|NULL

|NULL|

1||120087|程序設(shè)計(jì)基礎(chǔ)

|趙曉妝|知行聯(lián)合出版社|NULL

|NULL|

1|+---------+----------------+--------+----------------+------------+-------+----------+5rowsinset(0.00sec)4.1插入數(shù)據(jù)4.插入查詢結(jié)果中的數(shù)據(jù)INSERT[IGNORE][INTO]表名1[(字段名稱1[,字段名稱2,…])]SELECT*FROM表名2;【例4-4】新建數(shù)據(jù)表newbook，其結(jié)構(gòu)與圖書表book完全相同，然后將book表中的所有數(shù)據(jù)插入newbook表中。CREATETABLEnewbookLIKEbook;INSERTINTOnewbookSELECT*FROMbook;

mysql>SELECT*FROMnewbook;+---------+----------------+--------+----------------+------------+-------+----------+|book_id|title

|author|publisher

|pubdate

|price|stocknum|+---------+----------------+--------+----------------+------------+-------+----------+|100023|計(jì)算機(jī)科學(xué)導(dǎo)論|劉曉婷|博達(dá)教育出版社|NULL

|NULL|

1||100582|大學(xué)物理

|李大國|博達(dá)教育出版社|2020-03-01|43.90|

2||110001|平面設(shè)計(jì)基礎(chǔ)|NULL

|職帆啟航出版社|NULL|NULL|

1||110705|空間數(shù)據(jù)庫原理|宋曉松|職帆啟航出版社|NULL

|NULL|

1||120087|程序設(shè)計(jì)基礎(chǔ)

|趙曉妝|知行聯(lián)合出版社|NULL

|NULL|

1|+---------+----------------+--------+----------------+------------+-------+----------+5rowsinset(0.00sec)4.1插入數(shù)據(jù)5．插入并替換已存在的數(shù)據(jù)REPLACE語句的語法格式與INSERT語句基本相同。但在插入的數(shù)據(jù)不滿足主鍵約束時，REPLACE語句可以在插入數(shù)據(jù)之前將與新數(shù)據(jù)沖突的舊數(shù)據(jù)刪除，使新數(shù)據(jù)能夠正常插入。4.1插入數(shù)據(jù)【例4-5】向數(shù)據(jù)表newbook中插入兩條完整的書目數(shù)據(jù)（100023，計(jì)算機(jī)科學(xué)導(dǎo)論，劉曉婷，博達(dá)教育出版社，2019-08-01，49.80，2）和（120034，學(xué)術(shù)英語寫作，王麗，知行聯(lián)合出版社，2017-11-01，46.90，2），其中有一條數(shù)據(jù)與數(shù)據(jù)表中已有的數(shù)據(jù)完全相同。REPLACEINTOnewbook

VALUES('100023','計(jì)算機(jī)科學(xué)導(dǎo)論','劉曉婷','博達(dá)教育出版社','2019-08-01',49.80,2),

('120034','學(xué)術(shù)英語寫作','王麗','知行聯(lián)合出版社','2017-11-01',46.90,2);

如果用INSERT語句插入這兩條數(shù)據(jù)，則執(zhí)行結(jié)果將出錯。INSERTINTOnewbookVALUES('100023','計(jì)算機(jī)科學(xué)導(dǎo)論','劉曉婷','博達(dá)教育出版社','2019-08-01',49.80,2),('120034','學(xué)術(shù)英語寫作','王麗','知行聯(lián)合出版社','2017-11-01',46.90,2);ERROR1062(23000):Duplicateentry'100023'forkey'newbook.PRIMARY'任務(wù)4.2修改數(shù)據(jù)UPDATE語句格式：UPDATE表名SET字段名稱1=值1[,字段名稱2=值2…][WHERE條件表達(dá)式];1．修改指定條件的數(shù)據(jù)【例4-6】將數(shù)據(jù)表newbook中圖書編號book_id為110001的記錄的作者author修改為李明明。UPDATEnewbookSETauthor='李明明'

WHEREbook_id='110001';任務(wù)4.2修改數(shù)據(jù)【例4-7】將數(shù)據(jù)表newbook中圖書編號book_id為110001的記錄的出版日期pubdate修改為2018-07-01，定價(jià)price修改為52.00。UPDATEnewbookSETpubdate='2018-07-01',price=52.00WHEREbook_id='110001';mysql>SELECT*FROMnewbook;+---------+----------------+--------+----------------+------------+-------+----------+|book_id|title

|author|publisher

|pubdate

|price|stocknum|+---------+----------------+--------+----------------+------------+-------+----------+|100023|計(jì)算機(jī)科學(xué)導(dǎo)論|劉曉婷|博達(dá)教育出版社|NULL

|NULL|

1||100582|大學(xué)物理

|李大國|博達(dá)教育出版社|2020-03-01|43.90|

2||110001|平面設(shè)計(jì)基礎(chǔ)|李明明|職帆啟航出版社|2018-07-01|52.00|

1||110705|空間數(shù)據(jù)庫原理|宋曉松|職帆啟航出版社|NULL

|NULL|

1||120034|學(xué)術(shù)英語寫作

|王麗|知行聯(lián)合出版社|2017-11-01|46.90|

2||120087|程序設(shè)計(jì)基礎(chǔ)|趙曉妝|知行聯(lián)合出版社|NULL|NULL|1|+---------+----------------+--------+----------------+------------+-------+----------+6rowsinset(0.00sec)任務(wù)4.2修改數(shù)據(jù)2．修改全部數(shù)據(jù)【例4-8】將數(shù)據(jù)表newbook中作者author字段的值均修改為“第一作者+姓名”的形式。例如，圖書編號book_id為100023的記錄的author應(yīng)修改為“第一作者劉曉婷”。UPDATEnewbookSETauthor=CONCAT("第一作者",author);mysql>SELECT*FROMnewbook;+---------+--------------+--------------+--------------+------------+-------+----------+|book_id|title

|author

|publisher

|pubdate

|price|stocknum|+---------+--------------+--------------+--------------+------------+-------+----------+|100023|計(jì)算機(jī)科學(xué)導(dǎo)論|第一作者劉曉婷|博達(dá)教育出版社|2019-08-01|49.80|

2||100582|大學(xué)物理

|第一作者李大國|博達(dá)教育出版社|2020-03-01|43.90|

2||110001|平面設(shè)計(jì)基礎(chǔ)

|第一作者李明明|職帆啟航出版社|2018-07-01|52.00|

1||110705|空間數(shù)據(jù)庫原理|第一作者宋曉松|職帆啟航出版社|NULL

|NULL|

1||120034|學(xué)術(shù)英語寫作

|第一作者王麗|知行聯(lián)合出版社|2017-11-01|46.90|

2||120087|程序設(shè)計(jì)基礎(chǔ)

|第一作者趙曉妝|知行聯(lián)合出版社|NULL

|NULL|1|+---------+--------------+--------------+--------------+------------+-------+----------+6rowsinset(0.00sec)任務(wù)4.3刪除數(shù)據(jù)DELETE語句格式：DELETEFROM表名[WHERE條件表達(dá)式];1．刪除滿足指定條件的數(shù)據(jù)【例4-9】將數(shù)據(jù)表newbook中圖書編號book_id為110001的數(shù)據(jù)刪除。DELETEFROMnewbookWHEREbook_id='110001';2．刪除全部數(shù)據(jù)【例4-10】使用DELETE語句刪除數(shù)據(jù)表newbook中的所有圖書數(shù)據(jù)。DELETEFROMnewbook;【例4-11】使用TRUNCATE語句刪除數(shù)據(jù)表newbook中的所有圖書數(shù)據(jù)。TRUNCATEnewbook;mysql>SELECT*FROMnewbook;Emptyset(0.00sec)項(xiàng)目案例：圖書館借還書管理數(shù)據(jù)庫的數(shù)據(jù)操作1．向各張表中插入數(shù)據(jù)2．修改表中的數(shù)據(jù)3．刪除表中的數(shù)據(jù)1．向各張表中插入數(shù)據(jù)對于創(chuàng)建數(shù)據(jù)表時指定了外鍵約束、建立了參照完整性的子表，必須先插入父表中的數(shù)據(jù)，然后才能插入子表中的數(shù)據(jù)。在插入數(shù)據(jù)時，不滿足外鍵約束的數(shù)據(jù)無法插入。例如，圖書表book和館藏表stock之間存在外鍵約束，book表是父表、stock表是子表，外鍵約束要求stock表中的圖書編號book_id字段值要么為空值，要么是book表中已經(jīng)存在的主鍵字段值。因此，必須先插入book表中的數(shù)據(jù)，然后才能插入stock表中的數(shù)據(jù)。1．向各張表中插入數(shù)據(jù)（1）向book表中插入書目數(shù)據(jù)。如果前面已經(jīng)插入過數(shù)據(jù)，則進(jìn)行替換。REPLACEINTObookVALUES('100023','計(jì)算機(jī)科學(xué)導(dǎo)論','劉曉婷','博達(dá)教育出版社','2019-08-01',49.80,2),('100582','大學(xué)物理','李大國','博達(dá)教育出版社','2020-03-01',43.90,2),('110001','平面設(shè)計(jì)基礎(chǔ)','李明明','職帆啟航出版社','2018-07-01',52.00,2),('110301','導(dǎo)游情景英語','劉廣志','職帆啟航出版社','2020-01-01',45.00,1),('110705','空間數(shù)據(jù)庫原理','宋曉松','職帆啟航出版社','2022-04-01',65.00,1);1．向各張表中插入數(shù)據(jù)（2）向stock表中插入館藏圖書數(shù)據(jù)。INSERTINTOstockVALUES('10024017','100582','二層B-8-2',0),('10024018','100582','二層B-8-2',0),('10100601','100023','一層J-6-1',2),('10100602','100023','一層J-6-1',1),('11030001','110001','一層C-18-4',0);1．向各張表中插入數(shù)據(jù)（3）向讀者類型表readertype中插入讀者類型數(shù)據(jù)。INSERTINTOreadertypeVALUES(1,'教師',8,90),(2,'學(xué)生',5,60);1．向各張表中插入數(shù)據(jù)（4）向讀者表reader中插入讀者數(shù)據(jù)。INSERTINTOreaderVALUES('1101003091',1,'楊麗','女','外國語學(xué)院'),('1105010561',1,'趙曉麗','女','經(jīng)濟(jì)與管理學(xué)院'),('1105071058',1,'李俊','男','經(jīng)濟(jì)與管理學(xué)院'),('2101240103',2,'宋洪博','男','外國語學(xué)院'),('2104241129',2,'侯明斌','男','新能源學(xué)院');1．向各張表中插入數(shù)據(jù)（5）向讀者表reader中插入讀者數(shù)據(jù)。INSERTINTOborrow(reader_id,stock_id,borrowtime,returntime)VALUES('1101003091','12040712','2023-05-2008:23:10','2023-06-2016:21:53'),('1105010561','11030002','2023-09-2009:01:22','2023-10-3014:39:32'),('1105010561','12026030','2024-03-0208:45:21','2024-03-2015:05:46'),('1101003091','10100602','2024-03-0215:32:27','2024-03-2214:25:10'),('1101003091','11030002','2024-03-2014:50:43','2024-05-1616:30:24');borrow_id字段為自增字段（從1開始自增），不需要插入值。1．向各張表中插入數(shù)據(jù)（5）向讀者表reader中插入讀者數(shù)據(jù)。INSERTINTOborrowVALUES(27,'2107240106','10100602','2025-03-2108:39:08',NULL);手動為自增字段插入值，要注意該值不能違反主鍵約束，后續(xù)自增值將從手動插入的值開始自增。給自增字段插入0或NULL時，系統(tǒng)會自動插入下一個自增值，而不是插入0或NULL。INSERTINTOborrowVALUES(0,'2107240106','12026030','2025-03-2108:40:31',NULL);2．修改表中的數(shù)據(jù)

修改未指定外鍵約束的字段值時，不受任何限制，可以直接修改。只要指定了外鍵約束，對父表的主鍵字段值和子表的外鍵字段值的修改就一定會受到參照完整性的限制。例如，父表readertype和子表reader之間的外鍵約束，不同的選項(xiàng)有不同的效果。①RESTRICT：如果reader表中存在該類型的讀者，則不允許修改，否則允許修改。②CASCADE：允許修改，并且同時自動修改reader表中該類型的所有讀者的type_id字段的值為新的類型編號。③SETNULL：允許修改，并且同時自動修改reader表中該類型的所有讀者的type_id字段的值為NULL（在外鍵字段沒有指定為NOTNULL的條件下）。④NOACTION：不采取動作，其作用和RESTRICT一樣。⑤SETDEFAULT：允許修改，并且同時自動修改reader表中該類型的所有讀者的type_id字段的值為默認(rèn)值。2．修改表中的數(shù)據(jù)（1）由于學(xué)生轉(zhuǎn)專業(yè)，因此需要將reader_id為2107240101的讀者的所屬院系dept修改為人工智能學(xué)院。UPDATEreaderSETdept='人工智能學(xué)院'WHEREreader_id='2107240101';由于dept字段既不是主鍵也不是外鍵，因此可以直接修改。2．修改表中的數(shù)據(jù)（2）由于圖書館進(jìn)行讀者類型調(diào)整，因此需要將學(xué)生的類型編號由原來的2調(diào)整為3。UPDATEreadertypeSETtype_id=3WHEREtype_id=2;由于父表readertype和子表reader的外鍵約束設(shè)置了ONUPDATE子句的選項(xiàng)為CASCADE，所以在reader表中該類型的所有讀者的type_id也會自動修改為3。mysql>SELECT*FROMreaderWHEREtype_id=3;+------------+---------+--------+--------+--------------+|reader_id

|type_id|name

|gender|dept

|+------------+---------+--------+--------+--------------+|2101240103|

3|宋洪博|男

|外國語學(xué)院

||2104241129|

3|侯明斌|男

|新能源學(xué)院

||2106240104|

3|王剛

|男

|人工智能學(xué)院||2106240206|

3|王曉紅|女

|人工智能學(xué)院||2107240101|

3|李淑子|女

|人工智能學(xué)院||2107240106|

3|劉麗華|女

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

|+------------+---------+--------+--------+--------------+6rowsinset(0.00sec)3．刪除表中的數(shù)據(jù)刪除子表中的數(shù)據(jù)時不受影響。刪除父表中的數(shù)據(jù)時一定會受到參照完整性的限制。例如，父表book與子表stock之間的外鍵約束，子表stock中數(shù)據(jù)的刪除不受影響（暫時忽略stock表和borr