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

《《数据库管理系统》教案第四章课件.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 st

2、ored 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 quest

6、ion 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 displa

7、ys.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 b

8、e 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 informat

9、ion 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 o

10、r 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 (M

11、ake 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 cr

12、oss-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 viewFigure

13、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年各月产品销售额年各月产品销售额 design viewFigure 4-5-1 交交叉叉表表查查询询“96

14、年年各各月月产产品品销销售售额额”的的 基基础础查查询询 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 By Month Step 2Define data sourceFigure 4-5-5 C

15、rosstab 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 Step 6Name the Crosstab QueryFigure 4-5-9 Cross

16、tab 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 manipulation(for example,working with client/s

17、erver 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 城市城市,公司名称公司名称,联系人姓名联系人姓名,供应商供应商FROM 供应商供应商ORDER BY 城市城市,

18、公司名称公司名称;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 viewFigure 4-9 Top(n)查询查询订单小计金额

19、前订单小计金额前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 SQL pattern of“Top 10 of Order Subtotals”queries:SELECT TOP 10 Order Details.OrderID,Sum(UnitPrice

20、*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 the capability of looking at your data in virtually any way you can thinkof.Here is a sampling of what yo

21、u 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 previo

22、us 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

23、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 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

24、 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

25、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 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

26、 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 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 accompl

27、ish 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(Subtotals500)design viewFigure 4-13 The Orders(Subtotals500)datasheet viewmodify a querys criteriaCreating Criteria Based on Multiple Condition

28、s 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 FieldsSummary of 4.1In this hour you have learned how to work wit

29、h 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 使用运算符、函数和表达式使用运算

30、符、函数和表达式Operators,functions,and expressions are thefundamental building blocks 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

31、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 expressionThe details is included in 数据库管理系统教案数据库管理系统教案-第四章第四章4-2.doc 4.3 Creating Queries Hour 10.Creating Queries Hour 15

32、.Power Query TechniquesAlthough 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

33、 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 o

34、utput Tips and tricks related to working with queries Query BasicsCreating 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 th

35、e 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 sele

36、ct 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

37、 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 Summa

38、rize Numeric Data Working with Outer JoinsWorking with Outer JoinsOuter 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

39、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 ne

40、cessary.Working with Outer JoinsThere 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 jo

41、in 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.Workin

42、g with Outer JoinsTo 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 JoinsThe Join Properties window appears(see Figure 4-14).Select the type of join you want to create.To create a

43、 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-

44、15 Customers Without Orders 无订单的客户无订单的客户 （datasheet view）Figure 4-16 Customers 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 calcu

45、lated 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 inco

46、rporate 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.