吉林大学数据库技术与Oracle sql-08.ppt

ManipulatingData

ObjectivesAftercompletingthislesson,youshouldbeabletodothefollowing:DescribeeachDMLstatementInsertrowsintoatableUpdaterowsinatableDeleterowsfromatableMergerowsinatableControltransactions

DataManipulationLanguageADMLstatementisexecutedwhenyou:AddnewrowstoatableModifyexistingrowsinatableRemoveexistingrowsfromatableAtransactionconsistsofacollectionofDMLstatementsthatformalogicalunitofwork.

AddingaNewRowtoaTableDEPARTMENTSNewrow…insertanewrowintotheDEPARMENTStable…

TheINSERTStatementSyntaxAddnewrowstoatablebyusingtheINSERTstatement.

Onlyonerowisinsertedatatimewiththissyntax.INSERTINTO table[(column[,column...])]VALUES (value[,value...]);

InsertingNewRowsInsertanewrowcontainingvaluesforeachcolumn.Listvaluesinthedefaultorderofthecolumnsinthetable.Optionally,listthecolumnsintheINSERTclause.

Enclosecharacteranddatevalueswithinsinglequotationmarks.INSERTINTOdepartments(department_id,department_name,manager_id,location_id)VALUES(70,PublicRelations,100,1700);1rowcreated.

INSERTINTO departmentsVALUES (100,Finance,NULL,NULL);1rowcreated.INSERTINTO departments(department_id,department_name)VALUES (30,Purchasing);1rowcreated.InsertingRowsWithNullValuesImplicitmethod:Omitthecolumnfromthe

columnlist.Explicitmethod:SpecifytheNULLkeywordintheVALUESclause.

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.InsertingSpecialValuesTheSYSDATEfunctionrecordsthe