Definition
Thedatatableisanimportantobjectofthedatabaseandthebasicunitforstoringdata.Afterthetablestructureiscreated,itinvolvesinsertingnewdataintothetable,aswellasmodifyinganddeletingexistingdata,whichisdataupdate.Dataupdatecanbeimplementedintwoways:"ObjectExplorer"andT-SQLstatements.ThedataupdatefunctionoftheSQLlanguageensuresthattheDBAordatabaseusercanmaintaindataontheestablisheddatabase.
Datarevisionisaprocessofnewdataitemsorrecords,andreplacementofolddataitemsorrecordsinadatafileordatabase.Thisisachievedbydeleting,modifyingandinsertingoperations.Itisthetimeseriesofgeographicinformationsystemthatestablishesgeographicdatatomeetthepremiseofdynamicanalysis.Remotesensingdataisanimportantsourceandbasisforthematicdataupdateofgeographicinformationsystemduetoitsmulti-temporalandfastacquisitioncharacteristics.Theconditionforrealizingthisprocessistoeffectivelyimprovethegeometricaccuracyandclassificationaccuracyofremotesensingimagedata.Atthesametime,itisnecessarytoestablishasysteminterfacebetweentheremotesensingdataprocessingsystemandthegeographicinformationsystemtoimprovetheaccuracyandefficiencyofdifferentdatastructuresanddataconversion.
Dataupdateincludesthreetypesofoperations:insert,modify,anddeletedata.
Datainsertion
SQLdatainsertiongenerallyhastwoformats,oneissingle-rowdatainsertion,andtheotherissubqueryresultinsertion.ThekeywordisINSERT.
Singlerowdatainsertion
1.Thegeneralformofsinglerowdatainsertionis:
INSERTINTOtablename(columnname1,columnname2,...,columnNamen)
VALUES(constant1,constant2,…,constantn)
where:thetablenameisthetableofcontentstobeinsertedintothedata;inthenewrecord,thecolumnnameis1Thevalueofisconstant1,thevalueofcolumnname2isconstant2,...,thevalueofcolumnnamenisconstantn.TheattributelistofthecatalogtablethatdoesnotappearintheINTOclausewilldefaulttoanullvalueinthenewdata.
Note:Whentheconstantstructure(includingorderanddatatype)intheVALUESclauseisthesameasthestructureofthetableofcontents,thecolumnnamesintheINTOclausecanbeomitted.
2.Examples
Example1:Recordanewcourse(coursenumber:03-01;coursename:operatingsystem;hours:48;credits:3;semester:4;TeacherID:X401055;Classroom:20303)insertedintothecurriculum.
INSERTINTOcurriculum
VALUES('03-01','operatingsystem',48,3,'4','X401055','20303')
YoucanomitthecolumnnamesintheINTOclauseatthistime.
Example2:Insertanewgraderecord(studentnumber:040101;coursecode:03-01)intothegradetable.
INSERTINTOtranscript(studentnumber,coursecode)
VALUES('040101','03-01')
ItisnotpossibletoomitINTOatthistimeThecolumnnameintheclause.
Insertsub-queryresults
1.Datainsertioncanalsoinsertsub-queryresultsandmultipledatarecordsintothetargetrecordinbatches.Thegeneralformatisasfollows:
INSERTINTOtablename(columnname1,columnname2,...,columnnamen)
Subquery
2.Examples
Example:Findtheaveragegradeforeachcourse,andputtheresultintothetargettable,thatis,PJ(coursenumber,averagegrade).
INSERTINTOPJ(coursenumber,averagegrade)
SELECTcoursenumber,AVG(grade)
FROMscoretable
GROUPBYcoursenumber
Datamodification
Generalformat
ThekeywordfordatamodificationisUPDATE,thegeneralformatis:
UPDATEtablename
SETupdatecontent
WHEREupdatecondition
Amongthem,theupdatecontentintheSETclauseappearsintheformof"attributename=expression".
Example
Example1:ChangethenativeplaceofLiKuitoShandong.
UPDATEStudentForm
SETHometown='Shandong'
WHEREName='LiKui'
Example2:ChangefromLiaoningProvinceAllstudentscoresaresetto80
UPDATEscoretable
SETscore=80
WHEREstudentIDIN(SELECTstudentID
FROMStudenttable
WHEREnativeplace='Liaoning')
Note:Subqueriescanbenestedintheupdateoperationtocompletetheupdateofcomplexlogic.
Datadeletion
Generalformat
ThekeywordfordatadeletionisDELETE,thegeneralformatis:
DELETE
FROMtablename
WHEREdeletecondition
Amongthem,theDELETEclausedeletesthedatainthetableanddoesnotaffectthestructureofthetable.
Example
Example:DeletethescorerecordofthestudentwhosestudentIDis‘040104’.
DELETE
FROM
cWHEREstudentID='040104'
Note:IftheWHEREclauseisnotadded,thetargettablewillbedeletedAllrecordsin.