day0103_Oracle_ProC数据库开发

ORACLESQL,INTRODUCTION,INTRODUCTION,StoreDataInDifferentFormatmmingModel,1,2,SQLStatement,3,4,RelationalDatabaseManagementSystem,3,sqlplus,WhereisDatastored?,FileDatabase,BasicUnitofDataStorage,TablesarethebasicunitofdatastorageinanOracledatabase.,LanguageofDataAccess-SQL,DataretrievalDatamanipulationlanguage(DML)Datadefinitionlanguage(DDL)TransactioncontrolDatacontrollanguage,DBandDBMS,DBArelationaldatabaseusesrelationsortwo-dimensionaltablestostoreinformation.DBMSTomanagedatabase,youneeddatabasemanagementsystems(DBMS).ADBMSisaprogramthatstores,retrieves,andmodifiesdatainthedatabaseonrequest.,LoggingintoSQL*Plus,FromcommandlineORACLE_HOMEORACLE_SIDtelnet3sqlplususername/password,DisplayingTableStructure,TheSQL*PlusDESCRIBEcommanddisplaysthestructureofatable(columnnames,NOTNULLcolumns,anddatatypes).,SQLDESCRIBEs_dept,NOTNULLcolumnsmustcontaindata.ExamplecolumndatatypeandlengthNUMBER(p,s)VARCHAR2(s)DATECHAR(s),SELECTStatement,CapabilitiesofSQLSELECTStatements,Selection,Projection,Table1,Table2,Table1,Table1,Join,SELECTstatement,WhereClause,OrderbyClause,SingleRowFunctions,3,SelectFromClause,5,DisplayingDatafromMultipleTables,6,GroupFunctions,7,Subqueries,1,4,2,TheBasicQueryBlock,SELECTidentifieswhatcolumnsFROMidentifieswhichtable,SELECTDISTINCT*,columnalias,.FROMtable;,SelectingAllColumns,AllRows,SimplestSELECTstatementcontainsthefollowingtwoclauses:SELECTclauseAsterisk(*)indicatesallcolumnsFROMclause,SQLSELECT*2FROMs_dept;,SelectingSpecificColumns,ListthecolumnsintheSELECTclause.Separatecolumnsbyusingacomma.Specifycolumnsintheorderyouwantthemtoappear.,SQLSELECTdept_id,last_name,manager_id2FROMs_emp;,ArithmeticExpressions,CreateexpressionsonNUMBERandDATEdatatypesbyusingoperators.Add+Subtract-Multiply*Divide/,Displaytheannualsalaryforallemployees.,ArithmeticExpressions,SQLSELECTlast_name,salary*12,commission_pct2FROMs_emp;,LAST_NAMESALARY*12COMMISSION_PCT-.Havel15684Magee1680010Giljum1788012.5Sedeghi1818010Nguyen1830015Dumas1740017.5Maduro16800.,ColumnAliases,Acolumnaliasrenamesacolumnheading.EspeciallyusefulwithcalculationsImmediatelyfollowscolumnnameOptionalASkeywordbetweencolumnnameandaliasDoublequotationmarksarerequiredifanaliascontainsspaces,specialcharacters,oriscase-sensitive.,OperatorPrecedence,Parentheseschangetheorderinwhichastatementisevaluated.,SQLSELECTlast_name,salary,12*(salary+100)2FROMs_emp;.Velasquez250031200,SQLSELECTlast_name,salary,12*salary+1002FROMs_emp;.Velasquez250030100,ConcatenationOperator,TheconcatenationoperatorIsrepresentedbytwoverticalbars(|).Linkscolumnsorcharacterstringstoothercolumns.Createsaresultantcolumnthatisacharacterexpression.,ConcatenationOperator:Example,DisplaythefullnamesoftheemployeeswiththeheadingEmployees.,SQLSELECTfirst_name|last_name“Employees”2FROMs_emp;,Employees-CarmenVelasquezLaDorisNgaoMidoriNagayamaMarkQuick-To-SeeAudryRopeburnMollyUrguhart.,LiteralCharacterString,Aliteralisacharacter,expression,ornumberincludedintheSELECTlist.characterliteralvaluesmustbeenclosedwithinsinglequotationmarks.Eachcharacterstringisoutputonceforeachrowreturned.,LiteralCharacterString:Example,Employees-CarmenVelasquez,PresidentLaDorisNgao,VP,OperationsMidoriNagayama,VP,SalesMarkQuick-To-See,VP,FinanceAudryRopeburn,VP,AdministrationMollyUrguhart,WarehouseManager.,SQLSELECTfirst_name|last_name2|,|titleEmployees3FROMs_emp;,ManagingNullValues,NULLisavaluethatisunavailable,unassigned,unknown,orinapplicable.NULLisnotthesameaszeroorspace.ArithmeticexpressionscontaininganullvalueevaluatetoNULL.,SQLSELECTlast_name,title,2salary*commission_pct/100COMM3FROMs_emp;,NVLFunction,ConvertNULLtoanactualvaluewithNVL.Datatypestousearedate,character,andnumber.Datatypesmustmatch.NVL(start_date,01-JAN-95)NVL(title,NoTitleYet)NVL(salary,1000),SQLSELECTlast_name,title,2salary*NVL(commission_pct,0)/100COMM3FROMs_emp;,DuplicateRows,Thedefaultdisplayofqueriesisallrowsincludingduplicaterows.EliminateduplicaterowsbyusingDISTINCTintheSELECTclause.,SQLSELECTDISTINCTname2FROMs_dept;,SQLSELECTname2FROMs_dept;,DISTINCTwithMultipleColumns,DISTINCTappliestoallcolumnsintheSELECTlist.WhenDISTINCTisappliedtomultiplecolumns,theresultrepresentsthedistinctcombinationofthecolumns.,SQLSELECTDISTINCTdept_id,title2FROMs_emp;,SELECTstatement,WhereClause,OrderbyClause,SingleRowFunctions,3,SelectFromClause,5,DisplayingDatafromMultipleTables,6,GroupFunctions,7,Subqueries,2,4,1,LimitingRowsSelected,RestricttherowsreturnedbyusingtheWHEREclause.TheWHEREclausefollowstheFROMclause.Conditionsconsistofthefollowing:Columnname,expression,constantComparisonoperatorLiteral,SQLSELECTlast_name,dept_id,salary2FROMs_emp3WHEREdept_id=42;,CharacterStringsandNumber,Characterstringsareenclosedwithinsinglequotationmarks.Numbervaluesarenotenclosedwithinquotationmarks.Charactervaluesarecase-sensitive.,SQLSELECTfirst_name,last_name,title2FROMs_emp3WHERElast_name=Magee;,ComparisonandLogicalOperators,Logicalcomparisonoperators=SELECTfirst_name,last_name,start_date2FROMs_emp3WHEREsalaryBETWEEN1500AND2000;,UseINtotestforvaluesinalist.,LIKESQLOperator,YoucanusetheLIKEoperatortoperformwildcardsearchesofvalidsearchstringvalues.Searchconditionscancontaineitherliteralcharactersornumbers.%denotesnoneormanycharacters._denotesonecharacter.,SQLSELECTlast_name2FROMs_emp3WHERElast_nameLIKEM%;,SQLSELECTlast_name2FROMs_emp3WHERElast_nameLIKE_a%;,LIKESQLOperator,YoucanusetheESCAPEidentifiertosearchfor%or_.,SQLSELECTtable_name2FROMuser_tables3WHEREtable_nameLIKES_%ESCAPE;,ISNULLSQLOperator,TestfornullvalueswiththeISNULLoperator.Donotusethe=operator.,SQLSELECTid,name,credit_rating2FROMs_customer3WHEREsales_rep_idISNULL;,NegatingExpressions,Sometimesitiseasiertoexcluderowsyouknowyoudonotwant.LogicalOperators!=SQLOperatorsNOTBETWEENNOTINNOTLIKEISNOTNULL,MultipleConditions,Usecomplexcriteria.CombineconditionswithANDorORoperators.ANDrequiresbothconditionstobeTRUE.,ORrequireseitherconditiontobeTRUE.,SQLSELECTlast_name,salary,dept_id,title2FROMs_emp3WHEREdept_id=414ANDtitle=StockClerk;,SQLSELECTlast_name,salary,dept_id,title2FROMs_emp3WHEREdept_id=414ORtitle=StockClerk;,Displayinformationforthoseemployeesindepartment44whoearn1000ormore,andanyemployeesindepartment42.,SQLSELECTlast_name,salary,dept_id2FROMs_emp3WHEREsalary=10004ANDdept_id=445ORdept_id=42;,RulesofPrecedence:Examples,SQLSELECTlast_name,salary,dept_id2FROMs_emp3WHEREsalary=10004AND(dept_id=445ORdept_id=42);,Displayinformationforthoseemployeesindepartment44or42whoearn1000ormore.,SELECTstatement,WhereClause,OrderbyClause,SingleRowFunctions,3,SelectFromClause,5,DisplayingDatafromMultipleTables,6,GroupFunctions,7,Subqueries,3,4,1,2,TheORDERBYClause,SortrowswiththeORDERBYclause.ASCascendingorder,default.DESCdescendingorder.ORDERBYclauseislastinSELECTcommand.,SQLSELECTlast_name,dept_id,start_date2FROMs_emp3ORDERBYlast_name;,TheORDERBYClause,Thedefaultsortorderisascending.ThesortordercanbereversedbyusingDESC.Youcansortbyexpressionsoraliases.,SQLSELECTlast_nameEMPLOYEE,start_date2FROMs_emp3ORDERBYEMPLOYEEDESC;,NullvaluesaredisplayedLastforascendingsequences.Firstfordescendingsequences.,SortingbyMultipleColumns,Youcanorderbypositiontosavetime.,SQLSELECTlast_name,dept_id,salary2FROMs_emp3ORDERBYdept_id,salaryDESC;,SQLSELECTlast_name,salary*122FROMs_emp3ORDERBY2;,Youcansortbymultiplecolumns.,TheorderofORDERBYlistisorderofsort.YoucansortbyacolumnthatisnotintheSELECTlist.,SELECTstatement,WhereClause,OrderbyClause,SingleRowFunctions,3,SelectFromClause,5,DisplayingDatafromMultipleTables,6,GroupFunctions,7,Subqueries,4,4,1,OverviewofFunctionsinSQL,UsefunctionstoPerformcalculationsondata.Modifyindividualdataitems.Manipulateoutputforgroupsofrows.Alterdateformatsfordisplay.Convertcolumndatatypes.,TwoTypesofSQLFunctions,SinglerowfunctionsCharacterNumberDateConversionMultiplerowfunctionsGroup,SINGLE-ROWFUNCTION,MULTI-ROWFUNCTION,CharacterFunctions,LOWERConvertstolowercaseUPPERConvertstouppercaseINITCAPConvertstoinitialcapitalizationCONCATConcatenatesvaluesSUBSTRReturnssubstringLENGTHReturnsnumberofcharactersNVLConvertsanullvalue,CaseConversionFunctions,ConvertcaseforcharacterstringLOWER(SQLCourse)sqlcourseUPPER(SQLCourse)SQLCOURSEINITCAP(SQLCourse)SqlCourse,ExampleUsingCharacterFunctions,DisplaythefistnameandlastnameofallemployeeswiththelastnameofPatel.,SQLSELECTfirst_name,last_name2FROMs_emp3WHERElast_name=PATEL;norowsreturned,SQLSELECTfirst_name,last_name2FROMs_emp3WHEREUPPER(last_name)=PATEL;FIRST_NAMELAST_NAME-VikramPatelRadhaPatel,CharacterManipulationFunctions,ManipulatecharacterstringsCONCAT(Good,String)GoodStringSUBSTR(String,1,3)StrLENGTH(String)6,NumberFunctions,ROUNDRoundsvaluetospecifieddecimalTRUNCTruncatesvaluetospecifieddecimal,ROUNDandTRUNCFunctions,ROUND(45.923,2)45.92ROUND(45.923,0)46ROUND(45.923,-1)50TRUNC(45.923,2)45.92TRUNC(45.923)45TRUNC(45.923,-1)40,TO_CHARFunctionwithNumbers:Syntax,UsetheseformatswiththeTO_CHARfunctiontodisplayacharactervalueasanumber.9representsanumber.0forcesazerotobedisplayed.$placesafloatingdollarsign.Lusesthefloatinglocalcurrencysymbol.printsadecimalpoint.,printsathousandindicator.,TO_CHAR(number,fmt),TO_CHARFunctionwithNumbers,Ifyouseeastringofpoundsigns(#),theformatmodeldoesnotcontainenoughdigitstotheleftofthedecimal.OracleServerroundsthestoreddecimalvaluetotheformatmodelspecification.,SQLSELECTOrder|TO_CHAR(id)|2wasfilledforatotalof3|TO_CHAR(total,fm$9,999,999)4FROMs_ord5WHEREship_date=21-SEP-92;,TO_NUMBERFunctions:Syntax,ConvertacharacterstringtoanumberformatusingtheTO_NUMBERfunction.,TO_NUMBER(char),ConversionFunctions:Overview,TO_CHARconvertsanumberstringtoacharacterstring.TO_NUMBERconvertsacharacterstringcontainingdigitstoanumber.Conversionfunctionscanuseaformatmodelcomposedofmanyelements.,NestingSingleRowFunctions,Singlerowfunctionscanbenestedtoanylevel.Nestedfunctionsareevaluatedfromdeepestleveltotheleastdeeplevel.,F3(F2(F1(col,arg1),arg2),arg3),Step1=Result1,Step2=Result2,Step3=Result3,NestingFunctions,1.Evaluateinnerfunctiontoconvertanumbervaluetoacharacterstring.Result1=TO_CHAR(manager_id)2.Evaluateouterfunctiontoreplacethenullvaluewithatextstring.NVL(Result1,NoManager),SQLSELECTlast_name,2NVL(TO_CHAR(manager_id),NoManager)3FROMs_emp4WHEREmanager_idISNULL;,SELECTstatement,WhereClause,OrderbyClause,SingleRowFunctions,3,SelectFromClause,4,DisplayingDatafromMultipleTables,6,GroupFunctions,7,Subqueries,5,4,1,Example,LAST_NAMENAME-VelasquezAdministrationNgaoOperationsNagayamaSalesRopeburnAdministrationUrguhartOperationsMenchuOperationsBiriOperationsHavelOperations.,S_emp,S_dept,CartesianProduct,ACartesianproductisformedwhenAjoinconditionisomitted.Ajoinconditionisinvalid.Allrowsinthefirsttablearejoinedtoallrowsinthesecondtable.ToavoidaCartesianproduct,alwaysincludeavalidjoinconditioninaWHEREclause.,SimpleJoinQuery:Syntax,WritethejoinconditionintheWHEREclause.Precedeeachcolumnnamewiththetablenameforclarity.Columnnamesmustbeprefixedwiththetablenamewhenthesamecolumnnameappearsinmorethanonetable.,SELECTtable.column,table.columnFROMtable1,table2WHEREtable1.column1=table2.column2;,QualifyingAmbiguousColumnNames,Usetableprefixestoqualifycolumnnamesthatareinmultipletables.Improveperformancebyusingtableprefixes.Distinguishcolumnsthathaveidenticalnamesbutresideindifferenttablesbyusingcolumnaliases.,TableAliases,Qualifycolumnswithtablealiases.ArevalidonlyforthatSELECTstatement.Qualifyacolumnreferencewiththetablealiasinsteadofthetablenameonceyoucreatethealias.,SQLSELECTCustomerName,2c.region_idRegionID,3RegionName4FROMs_customerc,s_regionr5WHEREc.region_id=r.id;,WhatIsaJoin?,Ajoinisusedtoquerydatafrommorethanonetable.JoinmethodsEquijoinNon-equijoinOuterjoinSelfjoin,S_EMPTableIDLAST_NAMEDEPT_ID-1Velasquez502Ngao413Nagayama314Quick-To-See105Ropeburn506Urguhart417Menchu428Biri439Catchpole4410Havel4511Magee3112Giljum3213Sedeghi3314Nguyen3415Dumas3516Maduro41,RelationsBetweenTables,S_DEPTTableIDNAMEREGION_ID-30Finance131Sales132Sales243Operations350Administration1,S_REGIONTableIDNAME-1NorthAmerica2SouthAmerica3Africa/MiddleEast4Asia5Europe,Equijoin:Example,Equijoinresultwhenacolumninonetablecorrespondstoanothercolumninthesecondtable.Thejoinconditioncontainsanoperatorequal(=).,SQLSELECTs_emp.last_name,s_2FROMs_emp,s_dept3WHEREs_emp.dept_id=s_dept.id;,Non-Equijoin:Example,Non-equijoinresultwhennocolumninonetablecorrespondsdirectlytoacolumninthesecondtable.Thejoinconditioncontainsanoperatorotherthanequal(=).,SQLSELECTe.ename,e.job,e.sal,s.grade2FROMempe,salgrades3WHEREe.salBETWEENs.losalANDs.hisal;,SelfJoins,S_EMP(WORKER),S_EMP(MANAGER),LAST_NAMEMANAGER_IDIDLAST_NAME-Ngao11VelasquezNagayama11VelasquezRopeburn11VelasquezUrguhart22NgaoMenchu22NgaoBiri22NgaoMagee33NagaymaGiljum33Nagayma.,SelfJoins:Example,Joinrowsinatabletorowsinthesametablebyusingaselfjoin.SimulatetwotablesintheFROMclausebycreatingtwoaliasesforthetable.,SQLSELECTworker.last_name|worksfor|2manager.last_name3FROMs_empworker,s_empmanager4WHEREworker.manager_id=manager.id;,OuterJoins:Syntax,Useanouterjointoseerowsthatdonotnormallymeetthejoincondition.Outerjoinoperatoristheplussign(+).Placetheoperatoronthesideofthejoinwherethereisnovaluetojointo.,SELECTtable.column,table.columnFROMtable1,table2WHEREtable1.column(+)=table2.column;,OuterJoins,S_EMP,S_CUSTOMER,SALES_LAST_NAMEIDREP_IDNAME-Magee1111WomansportMagee1111BeisbolSi!Magee1111OjibwayRetailGiljum1212UnisportsGiljum1212FutbolSonoraSedeghi1313HamadaSportDumas1515SportiqueSweetRockSports,NoSalesRepAssignedtoSweetRockSports,OuterJoins:Example,Theouterjoinoperatorcanonlyappearononesideoftheexpression.AconditioninvolvinganouterjoinmaynotUsetheINoperator.BelinkedtoanotherconditionbytheORoperator.,Displaythesalesrepresentativenameandthecustomernameforallcustomersevenifthecustomerhasnosalesrepresentative.,SQLSELECTe.last_name,e.id,2FROMs_empe,s_customerc3WHEREe.id(+)=c.sales_rep_id4ORDERBYe.id;,SELECTstatement,WhereClause,OrderbyClause,SingleRowFunctions,3,SelectFromClause,4,DisplayingDatafromMultipleTables,5,GroupFunctions,7,Subqueries,6,2,1,GroupFunctions,Groupfunctionsoperateonsetsofrowstogiveoneresultpergroup.GroupfunctionsappearinbothSELECTlistsandHAVINGclauses.TheGROUPBYclauseintheSELECTstatementdividesrowsintosmallergroups.TheHAVINGclauserestrictsresultgroups.,GROUPBYandHAVINGClauses:Syntax,GROUPBYdividesrowsintosmallergroups.HAVINGfurtherrestrictstheresultgroups.,SELECTcolumn,group_functionFROMtableWHEREconditionGROUPBYgroup_by_expressionHAVINGgroup_conditionORDERBYcolumn;,GroupFunctions,AVG(DISTINCT|ALL|n)COUNT(DISTINCT|ALL|expr|*)MAX(DISTINCT|ALL|expr)MIN(DISTINCT|ALL|expr)SUM(DISTINCT|ALL|n),GroupFunctions:Example,YoucanuseMAXandMINforanydatatype.,SQLSELECTMIN(last_name),MAX(last_name)2FROMs_emp;,SQLSELECTAVG(salary),MAX(salary),2MIN(salary),SUM(salary)3FROMs_emp4WHEREUPPER(title)LIKESALES%;,YoucanuseAVGandSUMagainstcolumnsthatcanstorenumericdata.,COUNTFunction:Examples,COUNT(*)returnsthenumberofrowsinatable.COUNT(expr)returnsthenumberofnon-nullrows.,SQLSELECTCOUNT(*)2FROMs_emp3WHEREdept_id=31;,SQLSELECTCOUNT(commission_pct)2FROMs_emp3WHEREdept_id=31;,TheGROUPBYClause:Syntax,DividerowsinatableintosmallergroupsbyusingtheGROUPBYclause.IncludethecolumnlistintheGROUPBYclauseifcolumnsareusedintheSELECTclause.OverridethedefaultsortorderbyusingtheORDERBYclause.,SELECTcolumn,group_functionFROMtableWHEREconditionGROUPBYgroup_by_expressionORDERBYcolumn;,WiththeGROUPBYClause,TheGROUPBYclausedisplaysonelineofdataforeachdepartment,andCOUNT(*)displaysthenumberofemployeesineachdepartment(group)displayed.,SQLSELECTdept_id,COUNT(*)”Number”2FROMs_emp3GROUPBYdept_id;,DEPT_IDNumber-312324333343354,TheGROUPBYClause:Examples,NumberofcustomersineachcreditratingJobtitlesandmonthlysalaryforeachjobtitle,SQLSELECTcredit_rating,COUNT(*)#Cust2FROMs_customer3GROUPBYcredit_rating;,SQLSELECTtitle,SUM(salary)PAYROLL2FROMs_emp3WHEREtitleNOTLIKEVP%4GROUPBYtitle5ORDERBYSUM(salary);,TheGROUPBYClause:Example,AllcolumnsintheSELECTlistthatarenotingroupfunctionsmustbeintheGROUPBYclause.TheGROUPBYcolumndoesnothavetobeintheSELECTclause.TheresultsaremoremeaningfuliftheGROUPBYcolumnisintheSELECTclause.,SQLSELECTtitle,MAX(salary)2FROMs_emp3GROUPBYtitle;,IllegalQueriesUsingGroupFunctions,AnycolumnorexpressionintheSELECTlistthatisnotanaggregatefunctionmustbeintheGROUPBYclause.YouwillseeanerrormessageifyoudonotcorrectlycreateyourGROUPBYclause.,SQLSELECTregion_id,COUNT(name)2FROMs_dept;SELECTregion_id,COUNT(name)*ERRORatline1:ORA-00937:notasingle-groupgroupfunction,GroupsWithinGroups:Example,ReturnsummaryresultsforgroupsandsubgroupsbylistingmorethanoneGROUPBYcolumn.DeterminethedefaultsortorderoftheresultsbytheorderofthecolumnsintheGROUPBYclause.,SQLSELECTdept_id,title,COUNT(*)2FROMs_emp3GROUPBYdept_id,title;,IllegalQueriesUsingGroupFunctions,YoucannotusetheWHEREclausetorestrictgroups.UsetheHAVINGclausetorestrictgroups.,SQLSELECTdept_id,AVG(salary)2FROMs_emp3WHEREAVG(salary)20004GROUPBYdept_id;WHEREAVG(salary)2000*ERRORatline3:ORA-00934:groupfunctionisnotallowedhere,TheHAVINGClause:Syntax,UsetheHAVINGclausetofurtherrestrictgroups.Step1:Rowsaregrouped.Step2:Thegroupfunctionisappliedtothegroup.Step3:GroupsmatchingtheHAVINGconditionaredisplayed.,SELECTcolumn,group_functionFROMtableWHEREconditionGROUPBYgroup_by_expressionHAVINGgroup_conditionORDERBYcolumn;,TheHAVINGClause:Example,The“President”groupdoesnotappearintheoutputbecauseitdoesnotmeetthecriteri