07DML事务锁

ManipulatingData数据操作Qualityisnotanact!It`sahabit.Objectives(学习目标)Aftercompletingthislesson,youshouldbeabletodothefollowing:•DescribeeachDMLstatement(定义)•Insertrowsintoatable(插入操作)•Updaterowsinatable(更新操作)•Deleterowsfromatable(删除操作)•Controltransactions(事务控制)Qualityisnotanact!It`sahabit.DataManipulationLanguage(DML语言)•ADMLstatementisexecutedwhenyou:(DML-增删改)–Addnewrowstoatable–Modifyexistingrowsinatable–Removeexistingrowsfromatable•AtransactionconsistsofacollectionofDMLstatementsthatformalogicalunitofwork.(事务是系列DML语句的逻辑工作单元)Qualityisnotanact!It`sahabit.AddingaNewRowtoaTable(插入新行)DEPARTMENTSNewrow…insertanewrowintotheDEPARMENTStable…Qualityisnotanact!It`sahabit.TheINSERTStatementSyntax(Insert语法)•AddnewrowstoatablebyusingtheINSERTstatement.•Onlyonerowisinsertedatatimewiththissyntax.(该语法一次只允许一行插入)INSERTINTOtable[(column[,column...])]VALUES(value[,value...]);Qualityisnotanact!It`sahabit.InsertingNewRows(Insert注意点)•Insertanewrowcontainingvaluesforeachcolumn.(含每列值)•Listvaluesinthedefaultorderofthecolumnsinthetable.(注意列的顺序)•Optionally,listthecolumnsintheINSERTclause.(养成写列名的好习惯)•Enclosecharacteranddatevalueswithinsinglequotationmarks.(字符和日期需要单引号)INSERTINTOdepartments(department_id,department_name,manager_id,location_id)VALUES(70,'PublicRelations',100,1700);1rowcreated.Qualityisnotanact!It`sahabit.INSERTINTOdepartmentsVALUES(100,'Finance',NULL,NULL);1rowcreated.INSERTINTOdepartments(department_id,department_name)VALUES(30,'Purchasing');1rowcreated.InsertingRowswithNullValues(插入空值)•Implicitmethod:Omitthecolumnfromthecolumnlist.(隐形方法)•Explicitmethod:SpecifytheNULLkeywordintheVALUESclause.(显形方法）Qualityisnotanact!It`sahabit.INSERTINTOemployees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,commission_pct,manager_id,department_id)VALUES(113,'Louis','Popp','LPOPP','515.124.4567',SYSDATE,'AC_ACCOUNT',6900,NULL,205,100);1rowcreated.InsertingSpecialValues(特殊值的插入)TheSYSDATEfunctionrecordsthecurrentdateandtime.Qualityisnotanact!It`sahabit.INSERTINTOemployeesVALUES(114,'Den','Raphealy','DRAPHEAL','515.127.4561',TO_DATE('FEB3,1999','MONDD,YYYY'),'AC_ACCOUNT',11000,NULL,100,30);1rowcreated.InsertingSpecificDateValues(插入日期值)•Addanewemployee.•Verifyyouraddition.Qualityisnotanact!It`sahabit.•WriteyourINSERTstatementwithasubquery.•DonotusetheVALUESclause.•MatchthenumberofcolumnsintheINSERTclausetothoseinthesubquery.INSERTINTOsales_reps(id,name,salary,commission_pct)SELECTemployee_id,last_name,salary,commission_pctFROMemployeesWHEREjob_idLIKE'%REP%';4rowscreated.CopyingRowsfromAnotherTable(行拷贝-子查询)Qualityisnotanact!It`sahabit.ChangingDatainaTable(更改数据)EMPLOYEESUpdaterowsintheEMPLOYEEStable.Qualityisnotanact!It`sahabit.TheUPDATEStatementSyntax(更改语句语法)•ModifyexistingrowswiththeUPDATEstatement.•Updatemorethanonerowatatime,ifrequired.(一次可改多行)UPDATEtableSETcolumn=value[,column=value,...][WHEREcondition];Qualityisnotanact!It`sahabit.UPDATEemployeesSETdepartment_id=70WHEREemployee_id=113;1rowupdated.•SpecificroworrowsaremodifiedifyouspecifytheWHEREclause.•AllrowsinthetablearemodifiedifyouomittheWHEREclause.UpdatingRowsinaTable(更改数据的注意点)UPDATEcopy_empSETdepartment_id=110;22rowsupdated.Qualityisnotanact!It`sahabit.UPDATEemployeesSETjob_id=(SELECTjob_idFROMemployeesWHEREemployee_id=205),salary=(SELECTsalaryFROMemployeesWHEREemployee_id=205)WHEREemployee_id=114;1rowupdated.UpdatingTwoColumnswithaSubquery(子查询作为更改的数据值)Updateemployee114’sjobandsalarytomatchthatofemployee205.Qualityisnotanact!It`sahabit.UPDATEcopy_empSETdepartment_id=(SELECTdepartment_idFROMemployeesWHEREemployee_id=100)WHEREjob_id=(SELECTjob_idFROMemployeesWHEREemployee_id=200);1rowupdated.UpdatingRowsBasedonAnotherTable(子查询作为更改的过滤条件)UsesubqueriesinUPDATEstatementstoupdaterowsinatablebasedonvaluesfromanothertable.Qualityisnotanact!It`sahabit.UPDATEemployees*ERRORatline1:ORA-02291:integrityconstraint(HR.EMP_DEPT_FK)violated-parentkeynotfoundUPDATEemployeesSETdepartment_id=55WHEREdepartment_id=110;UpdatingRows:IntegrityConstraintError(更改时注意约束)Qualityisnotanact!It`sahabit.DeletearowfromtheDEPARTMENTStable.RemovingaRowfromaTable(删除行)DEPARTMENTSQualityisnotanact!It`sahabit.TheDELETEStatement(删除语法)YoucanremoveexistingrowsfromatablebyusingtheDELETEstatement.DELETE[FROM]table[WHEREcondition];Qualityisnotanact!It`sahabit.•SpecificrowsaredeletedifyouspecifytheWHEREclause.•AllrowsinthetablearedeletedifyouomittheWHEREclause.DeletingRowsfromaTable(删除数据的注意点)DELETEFROMdepartmentsWHEREdepartment_name='Finance';1rowdeleted.DELETEFROMcopy_emp;22rowsdeleted.Qualityisnotanact!It`sahabit.DELETEFROMemployeesWHEREdepartment_id=(SELECTdepartment_idFROMdepartmentsWHEREdepartment_nameLIKE'%Public%');1rowdeleted.DeletingRowsBasedonAnotherTable(利用子查询过滤数据)UsesubqueriesinDELETEstatementstoremoverowsfromatablebasedonvaluesfromanothertable.Qualityisnotanact!It`sahabit.DeletingRows:IntegrityConstraintError(删除时注意约束)DELETEFROMdepartmentsWHEREdepartment_id=60;DELETEFROMdepartments*ERRORatline1:ORA-02292:integrityconstraint(HR.EMP_DEPT_FK)violated-childrecordfoundQualityisnotanact!It`sahabit