仓酷云

标题: MSSQL编程：chapter8: Managing Schema Objects [打印本页]

作者: 飘飘悠悠 时间: 2015-1-16 22:33
标题: 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等。

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

作者: 谁可相欹 时间: 2015-2-5 20:14
所以你总能得到相应的升级版本，来满足你的需求。

作者: 冷月葬花魂 时间: 2015-3-3 21:03
多走走一此相关论坛，多看一些实例开发，多交流0经验，没什么的，我也是刚学没多久!加油

作者: 分手快乐 时间: 2015-3-11 13:46
一个百万级别的基本信息表A，一个百万级别的详细记录表B，A中有个身份证id，B中也有身份id；先要找出A中在B的详细记录。

作者: 再见西城 时间: 2015-3-18 21:23
你觉得我的非分区索引无法对起子分区，你可以提醒我一下呀！没有任何的提醒，直接就变成了非分区表。不知道这算不算一个bug。大家也可以试试。

作者: 再现理想 时间: 2015-3-26 18:52
再开发调试阶段和OLAP环境中，外键是可以建立的。新版本中加入了SETNULL和SETDEFAULT属性，能够提供能好的级联设置。

欢迎光临仓酷云 (http://ckuyun.com/)