MSSQL编程：chapter8: Managing Schema Objects

对于insert操作，只需要把event_type改成DELETE_ROWS_EVENT；对于delete操作，改成WRITE_ROWS_EVENTobjectChapter8:ManagingSchemaObjectsPurpose
Aschemaisacollectionofdatabaseobjects.Aschemaisownedbyadatabaseuserandhasthesamenameasthatuser.Schemaobjectsarelogicalstructurescreatedbyuserstocontain,orreference,theirdata.Schemaobjectsincludestructuresliketables,views,andindexes.YoucancreateandmanipulateschemaobjectsusingOracleEnterpriseManager.
Topics
Thisfollowingtopicsarediscussedinthischapter:
AccessingSchemaObjectsManagingTablesManagingIndexesManagingViewsManagingDatabaseResidentProgramUnitsLoadingDataintoTablesViewingScreenshots
Moveyourmouseoverthisicontoshowallscreenshots.Youcanalsomoveyourmouseovereachindividualicontoseeonlythescreenshotassociatedwithit.
AccessingSchemaObjects
BacktoTopicList





FollowthestepsbelowtoaccessschemaobjectsusingEnterpriseManager:
1.
LogintoEnterpriseManagerDatabaseConsolebyopeningyourbrowserandspecifyingtheSYSusernameandpassword.

ClickLogin.


2.
ClickAdministrationandselecttheTableslink.




3.
SelecttheFlashlighticontoselectaparticularschema.




4.
SelectHRandclickSelect.




5.
EnterempintheObjectNamefieldandclickGo.




6.
Theobjectsthatmatchyoursearchcriteriaaredisplayed.




ManagingTables
BacktoTopicList

TablesarethebasicunitofdatastorageinanOracledatabase.Theyholdalluser-accessibledata.Eachtablehascolumnsandrows.Inthissection,youwillperformthefollowingtasks:
ViewingtheAttributesofaTableViewingtheContentsofaTableCreatingaNewTableModifyingaTableDroppingaTableViewingtheAttributesofaTable
BacktoTopicList

FollowthestepsbelowtoviewtheattributesoftheHR.EMPLOYEEStable:
1.
SelecttheEMPLOYEEStableandclickView.




2.
TheTableViewpageappearsdisplayingtheattributesofthetableincludingcolumns,constraints,andstorageoptions.



ClicktheTableslinktoreturntotheTablespropertypage.




ViewingtheContentsofaTable
BacktoTopicList

PerformthestepslistedbelowtoviewthecontentsoftheHR.EMPLOYEEStable:
1.
SelecttheEMPLOYEEStableandselectViewDatafromtheActionsdrop-downmenu.ClickGo.




2.
TheViewDataForTablepageappearsshowingtherowdataintheResultssection.






CreatingaNewTable
BacktoTopicList

Inthissectionyouwillcreatethreenewtablesforyourdatabase:EMPLOYEES,CUSTOMERS,andORDERS.


1.
ClickCreateontheTablespropertypage.




2.
TheCreateTable:TableOrganizationpageappears.SelectStandard,HeapOrganizedandclickContinue.




3.
TheCreateTablepageappears.EnteremployeesintheNamefield.EnterfsownerintheSchemafield.EnterfsdataintheTablespacefield.DefinethecolumnsasfollowsandclickAdd5TableColumns.

EMPLOYEE_IDNUMBER(6)FIRST_NAMEVARCHAR2(20)LAST_NAMEVARCHAR2(25)EMAIL_ADDRESSVARCHAR2(25)PHONE_NUMBERVARCHAR2(20)




4.
Entertheremainingcolumnsasfollows:

HIRE_DATEDATEJOB_IDVARCHAR2(10)SALARYNUMBER(8)MANAGER_IDNUMBER(6)

ClickConstraints.




5.
TheConstraintspageappears.SelectPrimaryfromthedrop-downmenuandclickAdd.




6.
TheAddPrimaryConstraintpageappears.Enteremp_id_pkintheNamefield.SelectEMPLOYEE_IDintheAvailableColumnslist.ClickMovetoselectit.ClickOK.




7.
TheCreateTablepageappears.ClickOK.




8.
TheTablepageappearswithanUpdatemessageindicatingyourtablehasbeencreated.




9.
Repeatsteps2through8tocreatetheCUSTOMERSandORDERStablesintheFSOWNERschemaasdefinedbelow.
CUSTOMERSColumnTypePK?CUSTOMER_IDNUMBER(6)YCUST_FIRST_NAMEVARCHAR2(20)CUST_LAST_NAMEVARCHAR2(20)STREET_ADDRESSVARCHAR2(40)CITYVARCHAR2(30)STATEVARCHAR2(10)POSTAL_CODEVARCHAR2(10)PHONE_NUMBERVARCHAR2(20)ORDERSColumnTypePK?ORDER_IDNUMBER(12)YORDER_DATEDATEORDER_MODEVARCHAR2(8)CUSTOMER_IDNUMBER(6)DELIVERY_MODEVARCHAR2(8)ORDER_STATUSNUMBER(2)SALES_CLERK_IDNUMBER(6)ORDER_TOTALNUMBER(8,2)



ModifyingaTable
BacktoTopicList

YoucanuseEnterpriseManagertomodifytables,suchasaddinganddeletingcolumnsoraddingconstraints.InthissectionyouwilladdaconstrainttotheEMPLOYEEStableyoucreated.YouwillensurethattheEMAIL_ADDRESScolumncontainsavaluebyaddingaNOTNULLconstraint.

FollowthestepsbelowtoaddaNOTNULLconstrainttoyourEMPLOYEEStable:
1.
SelecttheEMPLOYEEStableandclickEdit.




2.
ChecktheNotNULLcolumnfortheEMAIL_ADDRESScolumn.ClickApply.




3.
TheEditTablepageisdisplayedwithamessageconfirmingyourupdate.






DroppingaTable
BacktoTopicList

Youcandrop(delete)atablebyusingEnterpriseManagerasfollows.Forthepurposesofthisexercise,youwillcreateanewtableandthendropthetable.


1.
ClicktheTableslinkintheSchemasectionontheAdministrationpagetoaccesstheSchemaobjectspropertypage.SelectTableintheObjectTypedrop-downmenu.EnterHRintheSchemaNamefieldandJOBSintheObjectNamefield.ClickGo.




2.


SelectCreateLikefromtheActionsdrop-downmenu.ClickGo.




3.


TheCreateTablepageappears.EnterJOBS_HISTintheNamefield.DeselectNotNullfortheJOB_IDandJOB_TITLEcolumns.ClickConstraints.




4.
TheConstraintspageappears.DeletetheconstraintsonthetablebyselectingeachandclickingDelete.Theyarenotneededforthisexercise.ClickGeneraltoreturntotheGeneralpage.




5.
ClickOKtocreatetheJOBS_HISTtable.




6.
Amessageisdisplayedindicatingthatthetablehasbeencreated.




7.
EnterJOBS_HISTintheObjectNamefieldandclickGo.




8.
TheTablespageisdisplayedwiththeJOBS_HISTtableintheResultssection.ClickDeletetothedeletetheJOBS_HISTtable.




9.
ClickYestoconfirmthedeletionofthetable.




10.
Amessageisdisplayedindicatingthetablehasbeendeleted.ClickGotoattempttoretrievethetable.




11.
Noobjectfoundisdisplayedintheresultssectionindicatingyourtablehasbeendropped(deleted).






ManagingIndexes
BacktoTopicList

Indexesareoptionalstructuresassociatedwithtablesandcanbeusedtoimprovequeryperformance.Anindexprovidesaquickaccesspathtotabledata.Indexescanbecreatedononeormorecolumnsofatable.Afteranindexiscreated,itisautomaticallymaintainedandusedbytheOracleDatabaseserver.Changestoatable’sdataorstructureareautomaticallyincorporatedintoallrelevantindexeswithcompletetransparencytotheuser.

Inthissection,youwillperformthefollowingtasks:
ViewingtheAttributesofanIndexCreatingaNewIndexViewingtheAttributesofanIndex
BacktoTopicList

PerformthestepslistedbelowtoviewtheattributesofanindexinyourFSOWNERschema:
1.
ClicktheIndexeslinkintheSchemasectionontheAdministrationpagetoaccesstheIndexespropertypage.




2.
TheIndexespageappears.EnterFSOWNERintheSchemaNamefieldandclickGo.




3.
Theindexescreatedwhenyoudefinedprimarykeysaredisplayed.SelecttheEMP_ID_PKindexdefinedontheEMPLOYEEStablebyclickingtheindexnamelink.




4.
TheEMP_ID_PKindexfortheEMPLOYEEStableisdisplayed.



SelecttheIndexeslinktoreturntotheIndexespropertypage.


CreatingaNewIndex
BacktoTopicList

CreateanindexontheCUSTOMER_IDcolumnintheORDERStablesothatyoucanquicklyaccessallordersforaspecifiedcustomer.TheindexshouldbestoredintheFSINDEXtablespace.

PerformthestepslistedbelowtocreatetheORD_CUSTID_IDXindex:
1.
SelectTablesfortheObjectType.EnterFSOWNERintheSchemafieldandclickGo




2.
.TheTablespageappears.SelecttheORDERStableandselectCreateIndexfromtheActionsdrop-downmenu.ClickGo.




3.
TheCreateIndexpageappears.EnterORD_CUSTID_INDXintheNamefield.EnterfsindexintheTablespacefield.SelectStandardB-treeastheindextype.SelecttheCUSTOMER_IDcolumnbyentering1intheOrdercolumn.AcceptASCastheSortingOrder.ClickOKtocreatetheindex.




ManagingViews
BacktoTopicList

Viewsarecustomizedpresentationsofdatainoneormoretablesorotherviews.Theycanbethoughtofasstoredqueries.Viewsdonotactuallycontaindata,butinsteadtheyderivetheirdatafromthetablesuponwhichtheyarebased.Thesetablesarereferredtoasthebasetablesoftheview.Liketables,viewscanbequeried,updated,insertedinto,anddeletedfrom,withsomerestrictions.Alloperationsperformedonaviewactuallyaffectthebasetablesoftheview.Viewsprovideanadditionallevelofsecuritybyrestrictingaccesstoapredeterminedsetofrowsandcolumnsofatable.Theyalsohidedatacomplexityandstorecomplexqueries.Inthissection,youwillperformthefollowingtasks:
AccessingViewsCreatingaNewViewAccessingViews
BacktoTopicList

Performthestepslistedbelowtoaccessviews:
1.
SelectViewsintheSchemaregionoftheAdministrationpage.EnterHRintheSchemaNamefieldandclickGo.




2.
TheviewsdefinedontablesintheHRschemaaredisplayed.SelecttheEMP_DETAILS_VIEWandclickViewtoviewitsdefinition.




3.
TheViewpageisdisplayedshowingthedefinitionoftheview.



SelecttheViewslink.


CreatingaNewView
BacktoTopicList

Performthestepslistedbelowtocreateanewview:
1.
ClickCreateontheViewspropertypage.




2.
EnterthefollowinginformationandclickOK.

Viewname:CLERK10_ORDSSchema:FSOWNERQuerytext:

SELECTorder_id,customer_id,order_totalFROMordersWHEREsales_clerk_id=10




3.
TheViewspageisdisplayedconfirmingthecreationofyourview.



SelecttheDatabaselink.


ManagingDatabaseResidentProgramUnits
BacktoTopicList

YoucanuseEnterpriseManagertomanagedatabaseresidentprogramunitssuchasPL/SQLpackages,procedures,triggers,andfunctionsandJavasourcesandclasses.Theactionsincludecreatingandcompilingthedatabaseresidentprogramunits,creatingsynonymsforthedatabaseresidentprogramunits,grantingprivilegestousethedatabaseresidentprogramunits,andshowingdependenciesforthedatabaseresidentprogramunits.
1.
ClicktheProcedureslinkintheSchemasectionontheAdministrationpage.




2.
EnterhrintheSchemafieldclickGo.




3.
SelecttheADD_JOB_HISTORYprocedure.SelectGrantPrivilegesfromthedrop-downmenuandclickGo.




4.
SelectEXECUTEastheprivilegeandFSOWNERastheuser.ClickOK.








LoadingDataIntoTables
BacktoTopicList

YoucanuseEnterpriseManagertoloaddataintotablesinbatch.Batchloadingisusefulwhenyouhavealotofdata.Youcanloaddatafromoperatingsystemfilesorfromotherdatabases.Youcanalsoexportdataintofiles.Onemethodofloadingistousecontrol(.ctl)anddata(.dat)files.ThesefilesareformattedasstandardSQL*Loaderfiles.SQL*LoaderisautilitythatyoucanusetoloaddatafromexternalfilesintotablesofanOracledatabase.

InthissectionyouwillloadcustomerinformationintoyourFSOWNER.CUSTOMERStableusingtheload_cust.ctlfile.Createadirectorynamed$HOME/labs.Downloadtheload_cust.zipfileandunziptheload_cust.ctlandload_cust.datfilesinto$HOME/labsforuseinthissection.
1.
ClicktheLoadDataFromFilelinkintheUtilitiessectionontheMaintenancepage.




2.
TheLoadData:ControlFilepageappears.Enterthefullpathofyourcontrolfileonthedatabaseservermachine.Alsoentertheusernameandpasswordforthehostmachine.ClickNext.




3.
TheLoadData:DataFilepageappears.SelectThedatafileisspecifiedinthecontrolfile.ClickNext.




4.
TheLoadData:LoadMethodpageappears.AcceptthedefaultofConventionalPathastheloadingmethod.ClickNext.




5.
TheLoadData:Optionspageappears.SelectGeneratelogfileintheOptionalFilesregion.Youcanacceptthedefaultfilenameandpathorenteradifferentone.ClickNext.




6.
TheLoadData:Schedulepageappears.EnteranameintheJobNamefieldanddescriptionintheDescriptionfield.SelectImmediatelytorunthejobnow.ClickNext.




7.
TheLoadData:Reviewpageappears.Reviewyourfilenamesandloadingmethods.Ifyouwanttochangesomething,youcanclickontheBackbutton.Otherwise,clickSubmitJobtostarttheloading.




8.
TheStatuspageappearswithamessageindicatingLoadDataSubmitSuccessful.ClickonViewJobtoviewthejobsummary.




9.
Thissummarypageshouldindicatethatthejobhassucceeded.Ifnot,youcanviewthelogfilebyclickingonyourjobundertheLogsheadingorbyviewingthelogfiledirectly.




10.
YoucanconfirmthedataloadbynavigatingtotheTablespage,selectingthetable,andselectingViewDataastheaction.ClickGo.




11.
TherowsyouloadedaredisplayedontheViewDataforTable:FSOWNER.CUSTOMERSpage.ClickOKtoreturntotheTablespropertypage.







Moveyourmouseoverthisicontohideallscreenshot
为多种编程语言提供了API。这些编程语言包括C、C++、Python、Java、Perl、PHP、Eiffel、Ruby和Tcl等。

仓酷云

如安全管理、备份恢复、性能监控和调优等，SQL只要熟悉基本操作就可以，只要程序设计部分只要稍加了解即可（如存储过程、触发器等）。

所以你总能得到相应的升级版本，来满足你的需求。

多走走一此相关论坛，多看一些实例开发，多交流0经验，没什么的，我也是刚学没多久!加油

一个百万级别的基本信息表A，一个百万级别的详细记录表B，A中有个身份证id，B中也有身份id；先要找出A中在B的详细记录。

你觉得我的非分区索引无法对起子分区，你可以提醒我一下呀！没有任何的提醒，直接就变成了非分区表。不知道这算不算一个bug。大家也可以试试。

再开发调试阶段和OLAP环境中，外键是可以建立的。新版本中加入了SETNULL和SETDEFAULT属性，能够提供能好的级联设置。