《《数据库管理系统》教案-第四章.ppt》由会员分享，可在线阅读，更多相关《《数据库管理系统》教案-第四章.ppt（65页珍藏版）》请在金锄头文库上搜索。
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
2、 stored 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 capabilitie
3、s 查询的功能查询的功能 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 Sel
4、ect query is a 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
6、it can bea complex question 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 dat
7、a the querys output displays. 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 differ
8、ent types of queries. They can 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 sp
9、ecific criteria), and displays the information 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 in 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 des
11、ign view Action 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 sin
12、gle operation.Crosstab 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
13、-4)Figure 4-3 Crosstab：各种产品的季度订单金额：各种产品的季度订单金额 汇总汇总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 viewFigur
14、e 4-6 交叉表查询交叉表查询96年各月产品销售额年各月产品销售额 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
15、 Crosstab based on Sales By 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
16、 based on Sales By Month Step 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 u
17、sed for advanced SQL database 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
18、 关关系系 FROM 客户客户UNION SELECT 城市城市, 公司名称公司名称, 联系人姓名联系人姓名, 供应商供应商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 Fi
19、gure 4-8.)Figure 4-8 Top (n)查询查询订单小计金额前订单小计金额前10名名 datasheet viewFigure 4-9 Top (n)查询查询订单小计金额前订单小计金额前10名名 design viewCompare:The SQL pattern of “Order Subtotals”is :SELECT Order Details.OrderID, Sum(UnitPrice*Quantity*(1-Discount) AS SubtotalFROM Order DetailsGROUP BY Order Details.OrderID;and the S
20、QL pattern of “Top 10 of Order Subtotals”queries :SELECT TOP 10 Order Details.OrderID, Sum(UnitPrice*Quantity*(1-Discount) AS SubtotalFROM Order DetailsGROUP BY Order Details.OrderIDORDER BY Sum(UnitPrice*Quantity*(1-Discount) DESC; Queries capabilities 查询的功能查询的功能Queries are flexible. They provide t
21、he 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： 选择记录选择记录 Sort records： 记录排序记录排序 Perform calculations ： 完成计算完成计算 Create tables： 创建新表创建新表Use a query as a source of data for
22、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 repeatedly make small changes to the criteria. The secondary query can be used to change the criteria while t
23、he primary query and its data remain intact.(see Figure 4-12)Create forms and reports based on a query Make changes to tablesHow to open a query in Datasheet viewWhen youre working with an existing query, you need to be able to open it in Datasheet view. Here are the steps in involved:1.Select Queri
24、es 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 of the query appears in Datasheet view (see Figure 4.1). How to open a query in Design view How to ru
25、n 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 querys definition, not the actual query result. How to print query results How to close a querymodi
26、fy 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 4-11), or you might wantto view just the orders with sales over $500 (see Figure 4-12 and Fig
27、ure 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 design viewFigure 4-11 The Londons Customers datasheet viewFigure 4-12 The Orders(Su
28、btotals500) 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 Single Field Using Wildcards(通配符通配符) in a Query Using Comparison Operators in a Que
29、ry 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 learned about the ins and outs of working in both Datasheet view and Design view
30、. 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 blocks for Access operations. Youuse them in such operations as entering criter
31、ia 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 at types of functions Learning how to create an expression Examining sp
32、ecial 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 any application you build, queries are very important as well. Most of
33、 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 developer. In hour 10 youll learn the following: The basics of working wi
34、th 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 a basic query is easy because Microsoft has provided a user-frie
35、ndly, 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 the Create query by using wizard icon.The second method is
36、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 new objects icon in toolbar and select “query” item. The Four
37、th 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 Getting Help from the Expression Builder In hour 15 youll l
38、earn 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 joins are used when you want the records on the one side
39、 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 orders in the query output. An inner join (the d
40、efault 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 outer joins and right outer joins. A left ou
41、ter 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 included in the query result, regardless
42、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 between the tables included in the query
43、:Double-click the line joining the tables in the query grid.Working with Outer JoinsThe 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 want to create a right outer j
44、oin. 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 Customers Without Orders 无订单的客户无订单的
45、客户 （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 parameters to supply variable
46、 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 field, field list, and
47、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.
