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

第四章 创建查询 Creating Queries,4.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 Introduced Queries are 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 capabilities 查询的功能 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 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. 选择查询是许多工作的基础。,In another words: A Microsoft Access query is a question that you ask about the information stored in your Access tables. The way you ask questions about this information is by using the query tools. Your query can be a simple question about information stored in a single table, or it can be a complex question about information stored in several tables. After you ask the question, Microsoft Access returns only the information you 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 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 different 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 specific 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. 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,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 single 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-4),Figure 4-3 Crosstab：各种产品的季度订单金额 汇总datasheet view,Figure 4-4 交叉表查询：各种产品的季度订单金额 汇总design view,Another 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 view,Figure 4-6 交叉表查询96年各月产品销售额 design view,Figure 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 Wizard,Figure 4-5-4 Crosstab based on Sales By Month Step 2Define data source,Figure 4-5-5 Crosstab based on Sales By Month Step 3 Define row titles,Figure 4-5-6 Crosstab based on Sales By Month Step 4Define column title,Figure 4-5-7 Crosstab based on Sales By Month Step 5Define value on intersection,Figure 4-5-8 Crosstab based on Sales By Month Step 6Name the Crosstab Query,Figure 4-5-9 Crosstab based on Sales By Month Step 7Datasheet view,Figure 4-5-10 Crosstab based on Sales By Month Step 8Design view,SQL There are three SQL (Structured Query Language) query types Union, Pass-Through, and Data Definition which are used 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 view,The SQL Pattern is: SELECT 城市, 公司名称 , 联系人姓名, “客户“ AS 关系 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 Figure 4-8.),Figure 4-8 Top (n)查询订单小计金额前10名 datasheet view,Figure 4-9 Top (n)查询订单小计金额前10名 design view,Compare: The SQL pattern of “Order Subtotals”is : SELECT Order Details.OrderID, Sum(UnitPrice*Quantity*(1-Discount) AS Subtotal FROM Order Details GROUP BY Order Details.OrderID; and the SQL pattern of “Top 10 of Order Subtotals”queries : SELECT TOP 10 Order Details.OrderID, Sum(UnitPrice*Quantity*(1-Discount) AS Subtotal FROM Order Details GROUP BY Order Details.OrderID ORDER 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 think of. 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 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 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 view When youre working with an existing query, you need to be able to open it in Datasheet view. Here are the steps in involved: Select Queries in the list of objects in the Database window. 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 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 querys definition, not the actual query result. How to print query results How to close a query,modify a querys criteria You can limit the records that you see in the result of a query 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 want to 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 of these tasks, and many, many more.,Figure 4-10 The Londons Customers design view,Figure 4-11 The Londons Customers datasheet view,Figure 4-12 The Orders(Subtotals500) design view,Figure 4-13 The Orders(Subtotals500) datasheet view,modify a querys criteria Creating 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 Query Using the Or Condition on a Single Field Using the Or Condition on Multiple Fields,Summary of 4.1 In 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. 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 the fundamental building blocks for Access operations. You use 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 at types of functions Learning how to create an expression Examining special identifier operators and expression The details is included in 数据库管理系统教案-第四章4-2.doc,4.3 Creating Queries Hour 10. Creating Queries Hour 15. Power Query Techniques Although tables act as the ultimate foundation for any 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 developer.,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 Basics Creating 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 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 new 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 Getting 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 Joins,Working with Outer Joins Outer joins 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 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 Joins There are two types of outer joins: left outer 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 included 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 Joins To establish an outer join, you must modify the join between 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 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 Customers Without Orders 无订单的客户 （design view）,Summary of hour 15 As 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 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 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.,