InformixdatabaseserverproductsprovidetheGlobal LanguageSupport (GLS)feature.InadditiontoU.S.ASCIIEnglish,GLS allowsyouto workin otherlocalesandusenon-ASCIIcharactersinSQLdataandidentifiers.You canusetheGLSfeatureto conformtothecustomsofaspecific locale.The localefilescontainculture-specificinformation,suchasmoneyanddate formatsandcollationorders.For moreGLSinformation,seetheIBM Informix: GLSUser'sGuide.
Summary
Adatabase containsacollection ofrelatedinformationbutdiffersina fundamentalwayfromothermethodsofstoringdata.Thedatabasecontains notonlythedata,but alsoadatamodelthatdefineseachdataitemand specifiesitsmeaningwithrespecttotheotheritemsand totherealworld.
Morethanoneusercanaccessand modifyadatabase atthesametime.Each userhasadifferentviewofthecontentsofadatabase,and eachuser’saccess tothosecontentscanbe restrictedinseveralways.
Arelationaldatabaseconsistsoftables,and thetablesconsistof columnsand rows.Therelationalmodelsupportsthreefundamentaloperationsontables: selections,projections, andjoins.
Anobject-relationaldatabaseextends thefeaturesofa relationaldatabase.You candefinenewdatatypestostoreand manageaudio,video,largetext documents,andsoforth. Youcandefinecomplextypesthatcombineoneor moreexistingdatatypestoprovidegreaterflexibilityinhowyouorganize yourdataincolumns andtables.Youcandefinetypesandtablesthatinherit thepropertiesofotherdatabaseobjectsand addnew propertiesthatare specifictotheobjectthatyoudefine.
Tomanipulateand querya database,useSQL.IBMpioneeredSQLandANSI standardizedit.Informixextensionsthatyoucanusetoyouradvantage add totheANSI-definedlanguage.IBM Informixtoolsalsomakeit possibleto maintainstrictcompliancewithANSIstandards.
Twolayersofsoftwaremediateall yourworkwithdatabases. Thebottom layerisalwaysadatabase serverthatexecutesSQLstatementsandmanages thedataondiskand incomputermemory.Thetoplayerisoneofmany applications,somefromIBMand somewrittenbyyou,byothervendors,or yourcolleagues.Middlewareisthecomponentthatlinksthedatabaseserver totheapplication, andisprovidedbythedatabasevendortobindtheclient programswiththedatabaseserver.IBM InformixStoredProcedureLanguage (SPL)isanexample ofsucha tool.
Chapter
2.
Composing
SELECT
Statements
IntroducingtheSELECTStatement . . . 2-2 OutputfromSELECTStatements . . . 2-3 OutputfromLargeObjectDataTypes . . . 2-3 OutputfromUser-DefinedDataTypes. . . 2-4 OutputinNon-DefaultCodeSets . . . 2-4 SomeBasicConcepts . . . 2-4 Privileges . . . 2-4 RelationalOperations . . . 2-5 SelectionandProjection. . . 2-5 Joining . . . 2-7 Single-TableSELECTStatements. . . 2-8 UsingtheAsteriskSymbol(*). . . 2-9 ReorderingtheColumns . . . 2-9 UsingtheORDERBYClausetoSorttheRows. . . 2-10 AscendingOrder . . . 2-11 DescendingOrder . . . 2-11 SortingonMultipleColumns . . . 2-12 SelectingSpecificColumns . . . 2-14 SelectingSubstrings . . . 2-19 ORDERBYandNon-EnglishData. . . 2-20 UsingtheWHEREClause . . . 2-22 CreatingaComparisonCondition . . . 2-22 IncludingRows . . . 2-24 ExcludingRows . . . 2-24 SpecifyingARangeofRows. . . 2-25 ExcludingaRangeofRows . . . 2-26 UsingaWHEREClausetoFindaSubsetofValues . . . 2-27 IdentifyingNULLValues . . . 2-29 FormingCompoundConditions . . . 2-30 UsingExact-TextComparisons . . . 2-30 UsingVariable-TextSearches. . . 2-31 UsingaSingle-CharacterWildcard. . . 2-32 MATCHESandNon-DefaultLocales . . . 2-36 ProtectingSpecialCharacters . . . 2-37 UsingSubscriptinginaWHEREClause . . . 2-37 UsingaFIRSTClausetoSelectSpecificRows . . . 2-38 FIRSTClauseWithoutanORDERBYClause . . . 2-39 FIRSTClausewithanORDERBYClause . . . 2-40 ExpressionsandDerivedValues . . . 2-41 ArithmeticExpressions . . . 2-41 CASEExpressions . . . 2-45 SortingonDerivedColumns . . . 2-47 UsingRowidValuesInSELECTStatements. . . 2-48 Multiple-TableSELECTStatements . . . 2-49
CreatingaCartesianProduct . . . 2-50 CreatingaJoin . . . 2-52 CrossJoin(IDS) . . . 2-52 Equi-Join . . . 2-52 NaturalJoin . . . 2-55 Multiple-TableJoin . . . 2-57 SomeQueryShortcuts. . . 2-59 UsingAliases. . . 2-59 TheINTOTEMPClause . . . 2-61 Summary . . . 2-62
InThisChapter
SELECTisthemostimportantand themostcomplexSQLstatement. Youcan useitand theSQLstatementsINSERT,UPDATE,and DELETEtomanipulate data.YoucanusetheSELECTstatementtoretrievedatafromadatabase,as partofanINSERTstatementtoproducenewrows, oraspart ofanUPDATE statementtoupdateinformation.
TheSELECTstatementistheprimarywaytoqueryinformationina database. Itisyour keytoretrievingdataina program,report,form,orspreadsheet. YoucanuseSELECTstatementswitha querytoolsuchasDB–Accessor embedSELECTstatementsinanapplication.
ThischapterintroducesthebasicmethodsforusingtheSELECTstatementto queryand retrievedatafromrelationaldatabases.Itdiscusseshowtotailor yourstatementstoselectcolumnsorrowsofinformationfromoneormore tables,howtoinclude expressionsandfunctionsinSELECTstatements,and howtocreatevariousjoinconditions betweendatabasetables.Thesyntax and usagefortheSELECTstatementaredescribedindetailintheIBM Informix: GuidetoSQLSyntax.
Mostexamples inthismanualcomefromthetablesinthestores_demo database,whichisincludedwiththesoftwareforyour IBM InformixSQL APIordatabase utility.Intheinterest ofbrevity,theexamplesshowonlypart ofthedatathatisretrievedforeachSELECTstatement. Forinformationon thestructure andcontentsofthedemonstration database,seethe
IBM Informix:GuidetoSQLReference.Foremphasis,keywordsareshown in uppercaselettersintheexamples,althoughSQLisnotcasesensitive.