《数据库处理——基础设计与实现(第十版)》第三章PPT课件

DAVIDM KROENKE SDATABASEPROCESSING 10thEdition 2 1 DavidM Kroenke s ChapterTwo IntroductiontoStructuredQueryLanguagePartTwo DatabaseProcessing Fundamentals Design andImplementation DAVIDM KROENKE SDATABASEPROCESSING 10thEdition 2 2 UsingMSAccess DAVIDM KROENKE SDATABASEPROCESSING 10thEdition 2 3 UsingMSAccess Continued DAVIDM KROENKE SDATABASEPROCESSING 10thEdition 2 4 UsingMSAccess Continued DAVIDM KROENKE SDATABASEPROCESSING 10thEdition 2 5 UsingMSAccess Results DAVIDM KROENKE SDATABASEPROCESSING 10thEdition 2 6 UsingMSSQLServer SQLQueryAnalyzer DAVIDM KROENKE SDATABASEPROCESSING 10thEdition 2 7 UsingOracle SQL Plus DAVIDM KROENKE SDATABASEPROCESSING 10thEdition 2 8 UsingOracle QuestSoftware sTOAD DAVIDM KROENKE SDATABASEPROCESSING 10thEdition 2 9 UsingMySQL MySQLCommandLineClient DAVIDM KROENKE SDATABASEPROCESSING 10thEdition 2 10 UsingMySQL MySQLQueryBrowser DAVIDM KROENKE SDATABASEPROCESSING 10thEdition 2 11 SortingtheResults ORDERBY SELECT FROMORDER ITEMORDERBYOrderNumber Price DAVIDM KROENKE SDATABASEPROCESSING 10thEdition 2 12 SortOrder AscendingandDescending SELECT FROMORDER ITEMORDERBYPriceDESC OrderNumberASC NOTE ThedefaultsortorderisASC doesnothavetobespecified DAVIDM KROENKE SDATABASEPROCESSING 10thEdition 2 13 WHEREClauseOptions AND SELECT FROMSKU DATAWHEREDepartment WaterSports ANDBuyer NancyMeyers DAVIDM KROENKE SDATABASEPROCESSING 10thEdition 2 14 WHEREClauseOptions OR SELECT FROMSKU DATAWHEREDepartment Camping ORDepartment Climbing DAVIDM KROENKE SDATABASEPROCESSING 10thEdition 2 15 WHEREClauseOptions IN SELECT FROMSKU DATAWHEREBuyerIN NancyMeyers CindyLo JerryMartin DAVIDM KROENKE SDATABASEPROCESSING 10thEdition 2 16 WHEREClauseOptions NOTIN SELECT FROMSKU DATAWHEREBuyerNOTIN NancyMeyers CindyLo JerryMartin DAVIDM KROENKE SDATABASEPROCESSING 10thEdition 2 17 WHEREClauseOptions RangeswithBETWEEN SELECT FROMORDER ITEMWHEREExtendedPriceBETWEEN100AND200 DAVIDM KROENKE SDATABASEPROCESSING 10thEdition 2 18 WHEREClauseOptions RangeswithMathSymbols SELECT FROMORDER ITEMWHEREExtendedPrice 100ANDExtendedPrice 200 DAVIDM KROENKE SDATABASEPROCESSING 10thEdition 2 19 WHEREClauseOptions LIKEandWildcards TheSQLkeywordLIKEcanbecombinedwithwildcardsymbols SQL92Standard SQLServer Oracle etc Exactlyonecharacter AnysetofoneormorecharactersMSAccess basedonMSDOS Exactlyonecharacter Anysetofoneormorecharacters DAVIDM KROENKE SDATABASEPROCESSING 10thEdition 2 20 WHEREClauseOptions LIKEandWildcards Continued SELECT FROMSKU DATAWHEREBuyerLIKE Pete DAVIDM KROENKE SDATABASEPROCESSING 10thEdition 2 21 WHEREClauseOptions LIKEandWildcards Continued SELECT FROMSKU DATAWHERESKU DescriptionLIKE Tent DAVIDM KROENKE SDATABASEPROCESSING 10thEdition 2 22 WHEREClauseOptions LIKEandWildcards SELECT FROMSKU DATAWHERESKULIKE 2 DAVIDM KROENKE SDATABASEPROCESSING 10thEdition 2 23 SQLBuilt inFunctions TherearefiveSQLBuilt inFunctions COUNTSUMAVGMINMAX DAVIDM KROENKE SDATABASEPROCESSING 10thEdition 2 24 SQLBuilt inFunctions Continued SELECTSUM ExtendedPrice ASOrder3000SumFROMORDER ITEMWHEREOrderNumber 3000 DAVIDM KROENKE SDATABASEPROCESSING 10thEdition 2 25 SQLBuilt inFunctions Continued SELECTSUM ExtendedPrice ASOrderItemSum AVG ExtendedPrice ASOrderItemAvg MIN ExtendedPrice ASOrderItemMin MAX ExtendedPrice ASOrderItemMaxFROMORDER ITEM DAVIDM KROENKE SDATABASEPROCESSING 10thEdition 2 26 SQLBuilt inFunctions Continued SELECTCOUNT ASNumRowsFROMORDER ITEM DAVIDM KROENKE SDATABASEPROCESSING 10thEdition 2 27 SQLBuilt inFunctions Continued SELECTCOUNT DISTINCTDepartment ASDeptCountFROMSKU DATA DAVIDM KROENKE SDATABASEPROCESSING 10thEdition 2 28 ArithmeticinSELECTStatements SELECTQuantity PriceASEP ExtendedPriceFROMORDER ITEM DAVIDM KROENKE SDATABASEPROCESSING 10thEdition 2 29 StringFunctionsinSELECTStatements SELECTDISTINCTRTRIM Buyer in RTRIM Department ASSponsorFROMSKU DATA DAVIDM KROENKE SDATABASEPROCESSING 10thEdition 2 30 TheSQLkeywordGROUPBY SELECTDepartment Buyer COUNT ASDept Buyer SKU CountFROMSKU DATAGROUPBYDepartment Buyer DAVIDM KROENKE SDATABASEPROCESSING 10thEdition 2 31 TheSQLkeywordGROUPBY Continued Ingeneral placeWHEREbeforeGROUPBY SomeDBMSproductsdonotrequirethatplacement buttobesafe alwaysputWHEREbeforeGROUPBY TheHAVINGoperatorrestrictsthegroupsthatarepresentedintheresult ThereisanambiguityinstatementsthatincludebothWHEREandHAVINGclauses Theresultscanvary sotoeliminatethisambiguitySQLalwaysappliesWHEREbeforeHAVING DAVIDM KROENKE SDATABASEPROCESSING 10thEdition 2 32 TheSQLkeywordGROUPBY Continued SELECTDepartment COUNT ASDept SKU CountFROMSKU DATAWHERESKU302000GROUPBYDepartmentORDERBYDept SKU Count DAVIDM KROENKE SDATABASEPROCESSING 10thEdition 2 33 TheSQLkeywordGROUPBY Continued SELECTDepartment COUNT ASDept SKU CountFROMSKU DATAWHERESKU302000GROUPBYDepartmentHAVINGCOUNT 1ORDERBYDept SKU Count DAVIDM KROENKE SDATABASEPROCESSING 10thEdition 2 34 QueryingMultipleTables Subqueries SELECTSUM ExtendedPrice ASRevenueFROMORDER ITEMWHERESKUIN SELECTSKUFROMSKU DATAWHEREDepartment WaterSports Note ThesecondSELECTstatementisasubquery DAVIDM KROENKE SDATABASEPROCESSING 10thEdition 2 35 QueryingMultipleTables Subqueries Continued SELECTBuyerFROMSKU DATAWHERESKUIN SELECTSKUFROMORDER ITEMWHEREOrderNumberIN SELECTOrderNumberFROMRETAIL ORDERWHEREOrderMonth January ANDOrderYear 2004 DAVIDM KROENKE SDATABASEPROCESSING 10thEdition 2 36 QueryingMultipleTables Joins SELECTBuyer ExtendedPriceFROMSKU DATA ORDER ITEMWHERESKU DATA SKU ORDER ITEM SKU DAVIDM KROENKE SDATABASEPROCESSING 10thEdition 2 37 QueryingMultipleTables Joins Continued SELECTBuyer SUM ExtendedPrice ASBuyerRevenueFROMSKU DATA ORDER ITEMWHERESKU DATA SKU ORDER ITEM SKUGROUPBYBuyerORDERBYBuyerRevenueDESC DAVIDM KROENKE SDATABASEPROCESSING 10thEdition 2 38 QueryingMultipleTables Joins Continued SELECTBuyer ExtendedPrice OrderMonthFR