SQL基礎(chǔ)修改數(shù)據(jù)（課堂PPT）

1、2022-5-31第四節(jié) SQL基礎(chǔ)-修改數(shù)據(jù)通過(guò)本節(jié)學(xué)習(xí)，需要掌握：在表格中增加數(shù)據(jù)修改表格數(shù)據(jù)刪除表格數(shù)據(jù)提交數(shù)據(jù)變更和回退操作2022-5-32數(shù)據(jù)修改語(yǔ)言(DML)DML (Data Manipulation Language)是SQL的核心部分，用于在表格中插入數(shù)據(jù)更新表格數(shù)據(jù)刪除表格數(shù)據(jù)一組DML語(yǔ)句構(gòu)成了一個(gè)邏輯整體，稱(chēng)為事務(wù)(transaction)。為了保證數(shù)據(jù)的有效性，當(dāng)事務(wù)中任意一個(gè)語(yǔ)句執(zhí)行失敗，都應(yīng)當(dāng)停止其他語(yǔ)句的執(zhí)行。2022-5-33第四節(jié) SQL基礎(chǔ)-修改數(shù)據(jù)在表格中增加數(shù)據(jù)修改表格數(shù)據(jù)刪除表格數(shù)據(jù)提交數(shù)據(jù)變更和回退操作2022-5-34在表中增加一行記錄50DE

2、VELOPMENTDETROITDEPTNODNAMELOC10ACCOUNTINGNEW YORK20RESEARCHDALLAS30SALESCHICAGO40OPERATIONSBOSTON新增行DEPTDEPTNODNAMELOC10ACCOUNTINGNEW YORK20RESEARCHDALLAS30SALESCHICAGO40OPERATIONSBOSTON50DEVELOPMENTDETROITDEPT在DEPT表中新增一行記錄2022-5-35INSERT語(yǔ)句可以在表中增加記錄。INSERT INTOtable (column , column ) VALUES(value

3、, value );上述語(yǔ)法中，一次只能增加一行記錄。如果省略column，必須按照表格里字段的順序構(gòu)造value。否則按column的順序。建議INSERT語(yǔ)句中寫(xiě)column部分。字符型或者日期型的值必須帶單引號(hào)。2022-5-36INSERT INTO dept (deptno, dname, loc)VALUES(50, DEVELOPMENT, DETROIT);1 row created.可以先使用DESCRIBE語(yǔ)句查看表結(jié)構(gòu)DESC deptNameNameNull?Null?TypeTypeDEPTNONOT NULLNUMBER(2)DNAMEVARCHAR2(14)LOC

4、VARCHAR2(13)2022-5-37INSERT語(yǔ)句中的NULL值隱式方法：省略賦NULL值的字段INSERT INTOdept (deptno, dname)VALUES(60, MIS);1 row created.顯示方法：指定字段的值為NULL。如果字段類(lèi)型是字符型或者日期型，也可以指定為(空串)。INSERT INTOdeptVALUES(70, FINANCE, NULL);1 row created.2022-5-38INSERT語(yǔ)句中的NULL值(2)NOT NULL的字段不可以賦NULL值INSERT語(yǔ)句中如果存在column部分，未被枚舉的字段在新生成的行中的值都為N

5、ULLINSERT INTO emp(empno, ename, job, mgr, hiredate, sal, comm, deptno)VALUES(7196, GREEN, SALESMAN, 7782, SYSDATE, 2000, NULL, 10);SYSDATE表示當(dāng)前日期和時(shí)間，USER表示當(dāng)前登錄數(shù)據(jù)庫(kù)的用戶(hù)名。2022-5-39INSERT語(yǔ)句增加一個(gè)員工INSERT INTOempVALUES(2296, AROMANO, SALESMAN, 7782, TO_DATE(FEB 3, 08, MON DD, YY), 1300, NULL, 10);驗(yàn)證SELECT*F

6、ROM empWHEREempno = 2296;缺省時(shí)間是0點(diǎn)2022-5-310INSERT語(yǔ)句的替換變?cè)?1)INSERT INTO dept (deptno, dname, loc)VALUES(&department_id, &department_name, &location);Enter value for department_id: 80Enter value for department_name: EDUCATIONEnter value for location: ATLANTA1 row created.2022-5-311INSERT語(yǔ)句的

7、替換變?cè)?2)ACCEPT department_id PROMPT Please enter the -department number: ACCEPT department_name PROMPT Please enter the - department name: ACCEPT location PROMPT Please enter the location: INSERT INTOdept (deptno, dname, loc)VALUES(&department_id, &department_name, &location);Please enter

8、 the department number: 90Please enter the department name: PAYROLLPlease enter the location: HOUSTON1 row created.一條SQL*Plus指令換行的時(shí)候要用-連接起來(lái)2022-5-312INSERT語(yǔ)句使用已有表格數(shù)據(jù)INSERT INTO table column (, column) subquery;不需要使用value子句SELECT子查詢(xún)中字段的數(shù)量和順序需要和INSERT子句中的相同。字段名稱(chēng)不一定相同。2022-5-313INSERT INTO managers(id,

9、 name, salary, hiredate)SELECTempno, ename, sal, hiredateFROM empWHEREjob = MANAGER;3 rows created.2022-5-314第四節(jié) SQL基礎(chǔ)-修改數(shù)據(jù)在表格中增加數(shù)據(jù)修改表格數(shù)據(jù)刪除表格數(shù)據(jù)提交數(shù)據(jù)變更和回退操作2022-5-315修改表數(shù)據(jù)EMPNOEMPNO ENAMEENAMEJOBJOBDEPTNODEPTNO7839 KINGPRESIDENT107698 BLAKEMANAGER307782 CLARKMANAGER107566 JONESMANAGER20EMPEMPEMPNOEMPN

10、O ENAMEENAMEJOBJOBDEPTNODEPTNO7839 KINGPRESIDENT107698 BLAKEMANAGER307782 CLARKMANAGER207566 JONESMANAGER20更新EMP表中的一行記錄2022-5-316UPDATE語(yǔ)句可以在表中修改已有的記錄。UPDATEtableSETcolumn = value , column = valueWHEREcondition;上述語(yǔ)法中，一次可以更新多行記錄。通常情況下，使用主鍵(pk)來(lái)確定要更新的記錄，除非你確定不會(huì)意外地更新一些不需要更新的記錄。比如，如果使用員工名字來(lái)確定記錄就是比較不可靠的，因

11、為可能存在重名。2022-5-317使用WHERE子句的時(shí)候，表格中由condition確定的記錄被更新。UPDATEempSETdeptno = 20WHEREempno = 7782;WHERE子句被省略的時(shí)候，表格中的所有記錄都被更新。UPDATEempSETdeptno = 20;2022-5-318UPDATE語(yǔ)句使用已有表格數(shù)據(jù)UPDATEtableSET(column, column, ) =(SELECTcolumn, column, FROMtableWHEREcondition)WHEREcondition;2022-5-319修改7698號(hào)員工的職業(yè)和部門(mén)，使得與7499

12、號(hào)員工一致。UPDATEempSET(job, deptno) = (SELECTjob, deptnoFROM empWHEREempno = 7499)WHEREempno = 7698;2022-5-320假設(shè)employee表和emp表具有相同的數(shù)據(jù)把所有和7788號(hào)員工相同職業(yè)的員工的部門(mén)號(hào)設(shè)置與7788號(hào)相同。UPDATEemployeeSETdeptno =(SELECTdeptnoFROM empWHEREempno = 7788)WHEREjob =(SELECTjobFROM empWHEREempno = 7788);2 rows updated.2022-5-321UP

13、DATE語(yǔ)句引發(fā)的錯(cuò)誤如果更新的字段涉及到完整性約束，UPDATE將可能執(zhí)行失敗。UPDATEempSETdeptno = 55WHERE deptno = 10;UPDATE emp *ERROR at line 1:ORA-02291: integrity constraint (USR.EMP_DEPTNO_FK) violated parent key not foundDEPT表中的deptno字段在EMP表中被當(dāng)作外鍵使用。deptno=55不存在于DEPT表中，故在EMP表中不能隨意更新。2022-5-322第四節(jié) SQL基礎(chǔ)-修改數(shù)據(jù)在表格中增加數(shù)據(jù)修改表格數(shù)據(jù)刪除表格數(shù)據(jù)提交

14、數(shù)據(jù)變更和回退操作2022-5-323刪除表數(shù)據(jù)DEPTDEPT在DEPT表中刪除一行記錄DEPTNODEPTNO DNAMEDNAMELOCLOC10ACCOUNTINGNEW YORK20RESEARCHDALLAS30SALESCHICAGO40OPERATIONSBOSTON50DEVELOPMENTDETROIT60MISDEPTNODEPTNO DNAMEDNAMELOCLOC10ACCOUNTINGNEW YORK20RESEARCHDALLAS30SALESCHICAGO40OPERATIONSBOSTON60MIS2022-5-324DELETE語(yǔ)句可以在表中刪除已有的記錄。

15、DELETE FROMtableWHEREcondition;上述語(yǔ)法中，一次可以刪除多行記錄。類(lèi)似UPDATE語(yǔ)句，使用主鍵(pk)來(lái)確定要?jiǎng)h除的記錄，除非你確定不會(huì)意外地刪除一些不需要?jiǎng)h除的記錄。2022-5-325使用WHERE子句的時(shí)候，表格中由condition確定的記錄被刪除。DELETE FROM deptWHEREdname = DEVELOPMENT;WHERE子句被省略的時(shí)候，表格中的所有記錄都被刪除。當(dāng)需要?jiǎng)h除一張大表的所有數(shù)據(jù)時(shí)，不建議用這種方法。合適的方法后面的章節(jié)會(huì)介紹。DELETE FROM dept;2022-5-326DELETE語(yǔ)句使用已有表格數(shù)據(jù)DELET

16、EtableWHERE(column, column, ) =(SELECTcolumn, column, FROMtableWHEREcondition);2022-5-327刪除所有SALES部門(mén)的員工記錄。DELETE FROM empWHEREdeptno = (SELECTdeptnoFROM deptWHEREdname = SALES);2022-5-328DELETE語(yǔ)句引發(fā)的錯(cuò)誤如果刪除的字段涉及到完整性約束，DELETE將可能執(zhí)行失敗。DELETE FROMdeptWHEREdeptno = 10;DELETE FROM dept *ERROR at line 1:ORA-

17、02292: integrity constraint (USR.EMP_DEPTNO_FK) violated child record foundDEPT表中的deptno字段在EMP表中被當(dāng)作外鍵使用。 EMP表中存在deptno=10的記錄，故在DEPT表中不能被刪除。2022-5-329第四節(jié) SQL基礎(chǔ)-修改數(shù)據(jù)在表格中增加數(shù)據(jù)修改表格數(shù)據(jù)刪除表格數(shù)據(jù)提交數(shù)據(jù)變更和回退操作2022-5-330數(shù)據(jù)庫(kù)事務(wù)(transaction)ORACLE數(shù)據(jù)庫(kù)通過(guò)事務(wù)來(lái)保證數(shù)據(jù)一致性，即使是在用戶(hù)退出或者系統(tǒng)強(qiáng)行關(guān)閉。事務(wù)中包含了一組DML語(yǔ)句，并保證數(shù)據(jù)變化的一致性。形象地，在兩個(gè)賬戶(hù)間轉(zhuǎn)賬包

18、含了一個(gè)賬戶(hù)的入賬和另一個(gè)賬戶(hù)的出賬。這兩個(gè)動(dòng)作要么同時(shí)成功，要么同時(shí)失敗。2022-5-331有三種類(lèi)型的事務(wù)類(lèi)型類(lèi)型描述描述DML (Data manipulation language)包含一條或一組DML語(yǔ)句，作為邏輯整體DDL (Data definition language)僅包含一條DDL語(yǔ)句DCL (Data control language)僅包含一條DCL語(yǔ)句2022-5-332一個(gè)數(shù)據(jù)庫(kù)事務(wù)起始于一個(gè)SQL語(yǔ)句，中止于以下4種事件之一：COMMIT或ROLLBACK語(yǔ)句DDL/DCL隱式提交用戶(hù)退出時(shí)自動(dòng)提交系統(tǒng)強(qiáng)行關(guān)閉時(shí)取消事務(wù)事務(wù)結(jié)束后，下一條SQL語(yǔ)句將開(kāi)始一個(gè)新

19、的事務(wù)。2022-5-333COMMIT和ROLLBACK可以確保數(shù)據(jù)一致性在數(shù)據(jù)變更真正生效前可以預(yù)覽數(shù)據(jù)組合邏輯上相關(guān)的操作語(yǔ)句語(yǔ)句功能功能COMMIT結(jié)束當(dāng)前事務(wù)，并將所有數(shù)據(jù)變更提交數(shù)據(jù)庫(kù)生效SAVEPOINT name在當(dāng)前事務(wù)內(nèi)部創(chuàng)建一個(gè)保存點(diǎn)ROLLBACK TO SAVEPOINT nameROLLBACK指令結(jié)束當(dāng)前事務(wù)，并取消所有數(shù)據(jù)變更。ROLLBACK TO SAVEPOINT name放棄該保存點(diǎn)，并取消所有在保存點(diǎn)之后的數(shù)據(jù)變更2022-5-334INSERTINSERTUPDATEUPDATEINSERTINSERTDELETEDELETETransactionC

20、OMMITSavepoint ASavepoint BROLLBACK to Savepoint BROLLBACK to Savepoint AROLLBACK2022-5-335隱式事務(wù)提交一個(gè)事務(wù)在下列情況下會(huì)被自動(dòng)提交DDL語(yǔ)句DCL語(yǔ)句用戶(hù)正常退出數(shù)據(jù)庫(kù)，而缺少顯式的COMMIT或者ROLLBACK。一個(gè)事務(wù)在下列情況下會(huì)被自動(dòng)回退用戶(hù)異常退出數(shù)據(jù)庫(kù)系統(tǒng)強(qiáng)行關(guān)閉2022-5-336數(shù)據(jù)在COMMIT/ROLLBACK前的狀態(tài)事務(wù)中所有數(shù)據(jù)變更都會(huì)被臨時(shí)存放在數(shù)據(jù)庫(kù)的緩沖池中，直到事務(wù)被提交或者回退。因而事務(wù)中早先的狀態(tài)是可以被恢復(fù)的。用戶(hù)可以通過(guò)SELECT語(yǔ)句查詢(xún)自己發(fā)起事務(wù)的數(shù)據(jù)

21、變更，即使事務(wù)未被提交。但不能看到他人發(fā)起事務(wù)中未被提交的數(shù)據(jù)。用戶(hù)發(fā)起事務(wù)中的變更數(shù)據(jù)將被數(shù)據(jù)庫(kù)系統(tǒng)鎖定。除該用戶(hù)外，其他用戶(hù)不能對(duì)鎖定的數(shù)據(jù)進(jìn)行變更操作，直到鎖定的數(shù)據(jù)被提交或回退。2022-5-337數(shù)據(jù)在COMMIT后的狀態(tài)數(shù)據(jù)變更在數(shù)據(jù)庫(kù)中正式生效數(shù)據(jù)不可被還原所有用戶(hù)可以看到變更數(shù)據(jù)數(shù)據(jù)鎖將被釋放，一度被鎖定的數(shù)據(jù)重新可以被所有用戶(hù)修改所有的保存點(diǎn)(savepoint)都被清除2022-5-338提交數(shù)據(jù)UPDATEempSETdeptno = 10WHEREempno = 7782;1 row updated.COMMIT;Commit complete.2022-5-339IN

22、SERT INTO department(deptno, dname, loc)VALUES(50, ADVERTISING, MIAMI);1 row created.UPDATEemployeeSETdeptno = 50WHEREempno = 7876;1 row updated.COMMIT;Commit complete.2022-5-340數(shù)據(jù)在ROLLBACK后的狀態(tài)所有數(shù)據(jù)變更都取消，數(shù)據(jù)狀態(tài)恢復(fù)到事務(wù)開(kāi)始前數(shù)據(jù)鎖將被釋放，一度被鎖定的數(shù)據(jù)重新可以被所有用戶(hù)修改DELETE FROM employee;14 rows deleted.ROLLBACK;Rollback com

23、plete.2022-5-341回退到一個(gè)保存點(diǎn)使用SAVEPOINT語(yǔ)句創(chuàng)建一個(gè)保存點(diǎn)。如果保存點(diǎn)名稱(chēng)已經(jīng)存在，則新的保存點(diǎn)將覆蓋舊的保存點(diǎn)。使用ROLLBACK TO SAVEPOINT語(yǔ)句回退到已有保存點(diǎn)狀態(tài)UPDATE SAVEPOINT update_done;Savepoint created.INSERT ROLLBACK TO update_done;Rollback complete.2022-5-342語(yǔ)句級(jí)的回退事務(wù)中如果一條DML語(yǔ)句執(zhí)行失敗，則只有本條語(yǔ)句被回退。之前的數(shù)據(jù)變更不會(huì)被回退，用戶(hù)可以顯式地提交或者回退。ORACLE在任何DDL語(yǔ)句執(zhí)行后都會(huì)隱式地提交，故即使DDL語(yǔ)句執(zhí)行失敗，也不能回退到更早之前的狀態(tài)。2022-5-343讀一致性(Read Consistency)數(shù)據(jù)庫(kù)用戶(hù)有兩種類(lèi)型的數(shù)據(jù)操作讀操作(SELECT)寫(xiě)操作(INSERT, UPDATE, DELETE)讀一致性能夠保證任何時(shí)候數(shù)據(jù)看起來(lái)都是一樣的用戶(hù)在讀數(shù)據(jù)的時(shí)候不會(huì)看到正在被修改的