第3章 案例实践-Access查询

已完成的参数化查询
The CustomerID column is now shown last because it is not displayed in the query results
The Criteria for the CustomerID column now contains the text for a prompt to be displayed in the Enter Parameter Value dialog box that will be displayed to get a parameter value from the user
查询结果
The results are sorted by CustomerID
As expected, the query results are identical to those shown in the SQL query
SQL 查询执行
SELECT FROM CustomerID, LastName, FirstName CUSTOMER;
使用两个表的查询
Both the CONTACT table and CUSTOMER table are in the query
The relationship and referential integrity constrain between the two tables is automatically included in the query—this means that the SQL clause
The Save Query button The Execute Query button
The SQL query text
查询结果
Save As 对话框
The Save Query button
The Save As dialog box
Type new query name here
SQLQuery-AW-3-01: Select Query 窗口
Columns in the query are called fields and appear in the bottom pane, together with related property values
添加列到Query
To add a column to the query, click the column name and drag it to a cell in the Field: row in the lower window pane
参数输入对话框
This is the text that was entered into the criteria field for CustomerID
Enter the CustomerID number here
Click the OK button to run the query
查询结果
SQL 查询执行
SELECT FROM WHERE
ORDER BY
LastName, FirstName, Date, Type, Remarks CUSTOMER, CONTACT CUSTOMER.CustomerID = CONTACT.CustomerID AND CustomerID = 3 Date;
The asterisk (*) symbol was dragged and dropped here to add the CUSTOMER.* field to the query
The table name is automatically added to the query to specify the source of the column–this is important if there is more than one table in the query with the same column name
NickName Tina Big Bill Billy
Phone 206-287-7010 206-287-7020 206-287-7030
Email Tina@ BigBill@ Billy@
创建 SALESPERSON 表的SQL语句
CREATE TABLE SALESPERSON( NickName Char(35) NOT NULL, LastName Char(25) NOT NULL, FirstName Char(25) NOT NULL, HireDate DateTime NOT NULL, WageRate Numeric(5,2) NOT NULL DEFAULT(12.50), CommissionRate Numeric(5,3) NOT NULL, Phone Char(12) NOT NULL, Email Varchar(100) NOT NULL UNIQUE, CONSTRAINT SALESPERSON_PK PRIMARY KEY(NickName) );
The user input the parameter value of 3, so the results are shown for the customer with the CustomerID of 3, which is Jessica Christman
SALESPERSON 表结构
NickName Tina Big Bill Billy LastName Smith Jones Jones FirstName Tina William Bill HireDate 10-AUG-04 25-SEP-04 17-MAY-05 WageRate 15.50 15.50 12.50 Commission Rate 12.5% 12.5% 12.0%
ACCESS ANSI-89 SQL
CREATE TABLE SALESPERSON( NickName Char(35) NOT NULL, LastName Char(25) NOT NULL, FirstName Char(25) NOT NULL, HireDate DateTime NOT NULL, WageRate Numeric NOT NULL, CommissionRate Numeric NOT NULL, Phone Char(12) NOT NULL, Email Varchar(100) NOT NULL, CONSTRAINT SALESPERSON_PK PRIMARY KEY(NickName) );
SALESPERSON
Column Name NickName Type Text (35) Key Primary Key Required Yes Remarks
LastName
FirstName HireDate WageRate CommissionRate Phone Email
Text (25)
在设计视图选择查询窗口
SQL View button
Query1: Select Query window in Design View
SQL 视图中 Query Window
Design View button
Query1: Select Query window in SQL View
SQL 查询
Double-click Create query in Design view to create a new query
Click Queries to display the Queries Pane
Table 对话框
Query1: Select Query window
Show Table Dialog Box
Show Table Dialog Box
QBE Query1 : Select Query 窗口
Tables in the query appear in the top pane, together with a list of their columns (the ―field list‖) and an asterisk (*) meaning ―all columns‖
第三章 案例实践
Microsoft Access数据库查询
学习目标
学习使用ACCESS 运行SQL语句 使用SQL语句和QBE实现单表或多表查询 手工设置ACCESS SQL不支持的表和关系属性
使用 Access SQL
SELECT * FROM CUSTOMER;
查询面板
The Queries Pane
Text (25) Date/Time Number Number Text (12) Text (100)
No
No No No No No No
Yes
Yes Yes Yes Yes Yes Yes Unique Double, Default value = $12.50 Double
SALESPERSON 数据
选择各个字段
The CustomerID, LastName, and FirstName fields are in the query
The results will be sorted by LastName in descending order (Z–A)
The results will show only customers with a CustomerID greater than 2
The query now contains an unknown value for CustomerID—we will use a query with a prompt that will be displayed to allow the user to enter a parameter value when the query is run