《数据库管理系统》教案第四章课件.ppt

1、第第四四章章 创建查询创建查询 Creating Queries4.1 Understanding and Using Simple Queries 了解和使用简单查询了解和使用简单查询 Hour 3.Queries Introduced 4.2 Using Operators,Functions and Expressions 使用运算符、函数和表达式使用运算符、函数和表达式4.3 Creating Queries 创建查询创建查询4.1 Understanding and Using Simple Queries Hour 3.Queries IntroducedQueries are s

2、tored questions about data.They are an extremely powerful aspect of Microsoft Access.By using queries,you can retrieve just the data you want,how you want it,whenever you want it.In this hour youll learn the following:What is a query?什么是查询什么是查询 Types of queries 查询的类型查询的类型 Query capabilities 查询的功能查询的

3、功能 How to open a query in Datasheet view How to open a query in Design view How to run a query How to add fields to a query,change the sort order of a query,and modify a querys criteria How to save a query How to print query results How to close a query What is a query?什么是查询什么是查询 A Select query is a

4、 stored question about the data stored in a databases tables.一个一个选择查询选择查询 是对存储在表（是对存储在表（Tables）中数据中数据 的一种提问。的一种提问。Select queries are the foundation of much of what you do in Access.They underlie most forms and reports,and they allow you to view the data you want,when you want.选择查询是许多工作的基础。选择查询是许多工作的

5、基础。In another words:A Microsoft Access query is a question that youask about the information stored in your Accesstables.The way you ask questions about thisinformation is by using the query tools.Yourquery can be a simple question aboutinformation stored in a single table,or it can bea complex ques

6、tion about information stored inseveral tables.After you ask the question,Microsoft Access returns only the informationyou requested.You use a simple Select query to define the tables and fields whose data you want to view and also to specify the criteria that limits the data the querys output displ

7、ays.A Select query is a query of a table or tables that just displays data;the query doesnt modify data in any way.For example：The datasheet view of the “Current Product List ”query is shown in Figure 4-1.Figure 4-1 Types of queries 查询的类型查询的类型Access supports many different types of queries.They can

8、be grouped into six basic categories:Select：选择查询选择查询 Total：汇总查询汇总查询 Action：动作查询动作查询 Crosstab：交叉表查询交叉表查询 SQL：SQL查询查询 Top(n)：前前 n 项查询项查询Select These are the most common.As its name implies,the select query selects information from one or more tables(based on specific criteria),and displays the informa

9、tion in a dynaset that you can use to view and analyze specific data;you can make changes to your data in the underlying tables.(see Figure 4-1.)Figure 4-1 The“Current Product List”query“Current Product List”query in in datasheet view datasheet view Total These are special versions of select queries

10、.Total queries provide the capability to sum or produce totals(such as count)in a select query.When you select this type of query,Access adds a Total row in the QBE(Query by Example)pane.(see Figure 4-2,the design view of“Order Subtotals”query.)Figure 4-2 The“Order Subtotals”query in design view Act

11、ion These queries let you create new tables (Make Tables)or change data(delete,update,and append)in existing tables.When you make changes to records in a select query,the changes must be made one record at a time.In action queries,changes can be made to many records during a single operation.Crossta

12、b These queries can display summary data in cross-tabular form like a spreadsheet,with the row and column headings based on fields in the table.By definition,the individual cells of the resultant dynaset are tabularthat is,computed or calculated.(see Figure 4-3 and Figure 4-4)Figure 4-3 Crosstab：各种产

13、品的季度订单金额：各种产品的季度订单金额 汇总汇总datasheet viewFigure 4-4 交叉表查询：各种产品的季度订单金额交叉表查询：各种产品的季度订单金额 汇总汇总design viewAnother example for Crosstab query:The Summary of Sales by Month within 1996 is shown as Figure 4-5 and Figure 4-6.Figure 4-5 交叉表查询交叉表查询96年各月产品销售额年各月产品销售额 datasheet viewFigure 4-6 交叉表查询交叉表查询96年各月产品销售额

14、年各月产品销售额 design viewFigure 4-5-1 交叉表查询交叉表查询“96年各月产品销售额年各月产品销售额”的的 基础查询基础查询 Sales By Month(datasheet view)Figure 4-5-2 交叉表查询交叉表查询“96年各月产品销售额年各月产品销售额”的的 基础查询基础查询 Sales By Month(design view)Figure 4-5-3 Crosstab based on Sales By Month Step 1 Select Crosstab WizardFigure 4-5-4 Crosstab based on Sales B

15、y Month Step 2Define data sourceFigure 4-5-5 Crosstab based on Sales By Month Step 3 Define row titlesFigure 4-5-6 Crosstab based on Sales By Month Step 4Define column titleFigure 4-5-7 Crosstab based on Sales By Month Step 5Define value on intersectionFigure 4-5-8 Crosstab based on Sales By Month S

16、tep 6Name the Crosstab QueryFigure 4-5-9 Crosstab based on Sales By Month Step 7Datasheet viewFigure 4-5-10 Crosstab based on Sales By Month Step 8Design viewSQL There are three SQL(Structured Query Language)query types Union,Pass-Through,and Data Definition which are used for advanced SQL database

17、manipulation(for example,working with client/server SQL databases).You can create these queries only by writing specific SQL commands.(see Figure 4-7.)Figure 4-7 SQL查询查询各城市的客户和供应商各城市的客户和供应商 datasheet viewThe SQL Pattern is:SELECT 城市城市,公司名称公司名称,联系人姓名联系人姓名,客户客户 AS 关系关系 FROM 客户客户UNION SELECT 城市城市,公司名称公

18、司名称,联系人姓名联系人姓名,供应商供应商FROM 供应商供应商ORDER BY 城市城市,公司名称公司名称;Top(n)You can use this query limiter only in conjunction with the other five types of queries.It lets you specify a number or percentage of the top records you want to see in any type of query.(see Figure 4-8.)Figure 4-8 Top(n)查询查询订单小计金额前订单小计金额前

19、10名名 datasheet viewFigure 4-9 Top(n)查询查询订单小计金额前订单小计金额前10名名 design viewCompare:The SQL pattern of“Order Subtotals”is:“Order Subtotals”is:SELECT Order SELECT Order Details.OrderIDDetails.OrderID,Sum(UnitPriceSum(UnitPrice*QuantityQuantity*(1-Discount)(1-Discount)AS SubtotalAS SubtotalFROM Order Detail

20、sFROM Order DetailsGROUP BY Order GROUP BY Order Details.OrderIDDetails.OrderID;and and the SQL pattern of“Top 10 of Order“Top 10 of Order Subtotals”queries Subtotals”queries:SELECT TOP 10 Order Details.OrderID,Sum(UnitPrice*Quantity*(1-Discount)AS SubtotalFROM Order DetailsGROUP BY Order Details.Or

21、derIDORDER BY Sum(UnitPrice*Quantity*(1-Discount)DESC;Queries capabilities 查询的功能查询的功能Queries are flexible.They provide the capability of looking at your data in virtually any way you can thinkof.Here is a sampling of what you can do:Choose tables：选择表选择表 Choose fields：选择字段选择字段 Choose records：选择记录选择记录

22、 Sort records：记录排序记录排序 Perform calculations：完成计算完成计算 Create tables：创建新表创建新表Use a query as a source of data for other queries(subquery)：You can create additional queries based on a set of records that you selected in a previous query.This is very useful for performing ad hoc queries,where you may rep

23、eatedly make small changes to the criteria.The secondary query can be used to change the criteria while the primary query and its data remain intact.(see Figure 4-12)Create forms and reports based on a query Make changes to tables How to open a query in Datasheet viewWhen youre working with an exist

24、ing query,you need to be able to open it in Datasheet view.Here are the steps in involved:1.Select Queries in the list of objects in the Database window.2.Click to select the query that you want to run,and then select Open on the Database window toolbar or double-click the query to run it.The result

25、 of the query appears in Datasheet view(see Figure 4.1).How to open a query in Design view How to run a query How to add fields to a query,change the sort order of a query,and modify a querys criteria How to save a query Its important to understand that when you save a query,youre saving only the qu

26、erys definition,not the actual query result.How to print query results How to close a querymodify a querys criteriaYou can limit the records that you see in the result of aquery by adding criteria to the query.For example,you might want to see just the customers in London(see Figure 4-10 and Figure

27、4-11),or you might wantto view just the orders with sales over$500(see Figure 4-12 and Figure 4-13).You could also view sales that occurred within a specific date range(see Figure 4-6).By using criteria,you can easily accomplish any ofthese tasks,and many,many more.Figure 4-10 The Londons Customers

28、design viewFigure 4-11 The Londons Customers datasheet viewFigure 4-12 The Orders(Subtotals500)design viewFigure 4-13 The Orders(Subtotals500)datasheet viewmodify a querys criteriaCreating Criteria Based on Multiple Conditions Using the And Condition on Multiple Fields Using the And Condition in a S

29、ingle Field Using Wildcards(通配符通配符)in a Query Using Comparison Operators in a Query Using the Or Condition on a Single Field Using the Or Condition on Multiple FieldsSummary of 4.1In this hour you have learned how to work with existing queries.You have learned why queries are important and you have

30、learned about the ins and outs of working in both Datasheet view and Design view.You now know the basics of adding fields and applying sorting and simple criteria.4.2 Using Operators,Functions and Expressions 使用运算符、函数和表达式使用运算符、函数和表达式Operators,functions,and expressions are thefundamental building blo

31、cks for Access operations.Youuse them in such operations as entering criteria in queries,creating calculated fields in forms,and creating summary controls in reports.In This Session Understanding what operators,functions,and expressions are and how they are used Reviewing types of operators Looking

32、at types of functions Learning how to create an expression Examining special identifier operators and expressionThe details is included in 数据库管理系统教案数据库管理系统教案-第四章第四章4-2.doc 4.3 Creating Queries Hour 10.Creating Queries Hour 15.Power Query TechniquesAlthough tables act as the ultimate foundation for a

33、ny application you build,queries are very important as well.Most of the forms and reports that act as the user interface for an application are based on queries.Having an understanding of querieswhat they are and when and how to use themis imperative for your success as an Access application develop

34、er.In hour 10 youll learn the following:The basics of working with queries How to build queries How to add tables and fields to the queries you create How to sort query output How to limit the data that appears in the query output Tips and tricks related to working with queries Query BasicsCreating

35、a basic query is easy because Microsoft has provided a user-friendly,drag-and-drop interface.There are many ways to start a new query in Access 2000/2003.The first way is to select the Queries icon from the Objects list in the Database window;then double-click the Create query in Design View icon or

36、 the Create query by using wizard icon.The second method is to select the Queries icon from the Objects list in the Database window and then click the New command button on the Database window toolbar.The Third way is to select the Table or Query which include necessary information and,then click ne

37、w objects icon in toolbar and select“query”item.The Fourth way is to import existing queries object in other Access databases.In hour 10 youll also learn the following:Ordering Query Results Refining a Query by Using Criteria Building Queries Based on Multiple Tables Creating Calculated Fields Getti

38、ng Help from the Expression Builder In hour 15 youll learn the following:Adding Calculated Fields to Select Queries Creating and Running Parameter Queries Creating and Running Action Queries Using Aggregate Functions to Summarize Numeric Data Working with Outer JoinsWorking with Outer JoinsOuter joi

39、ns are used when you want the records on the one side of a one-to-many relationship to be included in the query result,regardless of whether there are matching records in the table on the many side.With a Customers table and an Orders table,for example,users often want to include only customers with

40、 orders in the query output.An inner join(the default join type)does this.In other situations,users want all customers to be included in the query result,regardless of whether they have orders.This is when an outer join is necessary.Working with Outer JoinsThere are two types of outer joins:left out

41、er joins and right outer joins.A left outer join occurs when all records on the one side of a one-to-many relationship are included in the query result,regardless of whether any records exist on the many side.A right outer join means all records on the many side of a one-to-many relationship are inc

42、luded in the query result,regardless of whether there are any records on the one side.A right outer join should never occur if referential integrity is being enforced because all orders should have associated customers.Working with Outer JoinsTo establish an outer join,you must modify the join betwe

43、en the tables included in the query:Double-click the line joining the tables in the query grid.Working with Outer Joins The Join Properties window appears(see Figure 4-14).Select the type of join you want to create.To create a left outer join between the tables,select Option 2.Select Option 3 if you

44、 want to create a right outer join.Notice in Figure 4-14 that the description is Include ALL Records from Customers and Only Those Records from Orders Where the Joined Fields Are Equal.Figure 4-14 The Join Properties Figure 4-15 Customers Without Orders 无订单的客户无订单的客户 （datasheet view）Figure 4-16 Custo

45、mers Without Orders 无订单的客户无订单的客户 （design view）Summary of hour 15As you can see,Microsoft provides a sophisticated query builder for constructing complex and powerful queries.In this hour you have learned how to work with calculated fields and advanced filters.You have also learned how you can use pa

46、rameters to supply variable criteria to Parameter queries at runtime.Action queries let you modify table data without writing code;you can use these queries to add,edit,or delete table data.Finally,you have learned how to incorporate aggregate functions into queries and how to refine queries with fi

47、eld,field list,and query properties.Activities Practice building several single-table and multitable queries,using Northwind data.Practice sorting the query results as well as entering various criteria.Practice updating the query results.Also practice using the Expression Builder to build some date/time expressions.Finally,practice building each type of Action query:Insert,Update,Delete,and Make Table queries.Use parameters to determine the criteria for Action queries.