第5章 Access高级查询与SQL1
第5章Access高级查询与SQL语言
教学目标
掌握SQL语言的语法结构
教学重点
案例
Select语句的语法
SQL语言的语法结构
教学难点
SQL语言的语法结构
学时
2
教学手段
教授，演示
前面学习了Access的查询。通过设计视图和向导，可以建立选择查询、参数查询、交叉表查询、操作查询等比较复杂的查询。这些已经能够基本上满足大多数查询工作的需要。
但是在Access的高级使用的过程中，经常会使用到一些查询，这些查询用各种查询向导和设计器都无法实现。
Access有3种查询不能直接用查询视图或向导产生，而必须使用SQL查询，在Access中被称为SQL 特定查询。这3种SQL特定查询是：联合查询、传递查询和数据定义查询。
SQL查询是用户使用SQL语句创建的查询。可以用SQL语言来查询、更新和管理Access这样的关系数据库。
SQL语言是一种通用的数据库操作语言，并不是Access用户必须要掌握的，但在实际的工作中有时必须用到这种语言才能完成一些特殊的工作。利用SQL语言可以在Access中建立高级查询。5.1【案例12】创建Access
联合查询
相关知识
5.2【案例13】创建Access SQL
子查询
相关知识
5.3【案例14】创建Access
数据定义查询
相关知识
5.1 相关知识
1．SQL语言简介
SQL语言即结构化查询语言（英文全称为Structured Query Language），是一种通用的关系型数据库操作语言。其发音是“S-Q-L”，或“sequel”。
在20世纪70年代初，E.E.Codd首先提出了关系模型。1974年，由Boyce和Chamberlin提出了SQL 语言。由于它功能丰富，语言简洁，使用方法灵活，备受用户和计算机业界的青睐，被众多的计算机公司和软件公司所采用。
1986年10月，美国国家标准局（ANSI）批准采用SQL作为关系数据库语言的美国标准，1987年，
国际标准化组织将之采纳为国际标准。ANSI于1989年公布了SQL-89标准，1992年又公布了SQL-99标准，后来又公布了新的标准SQL3。
目前所有主要的关系数据库管理系统都支持某种形式的SQL语言，大部分都遵守SQL-89标准。SQL语言由于其功能强大，简捷易学，从而被包括应用程序员、DBA管理员和终端用户广泛使用。
2．SQL主要特点
（1）非过程化的语言：所谓面向过程的语言，是指当用户要求完成某项数据请求时，必须指定存取路径，这就需要用户了解数据存储结构、方式等相关情况，加重了用户负担。
而当使用SQL这种非过程化语言进行数据操作时，只要提出“做什么”，而不必指明“如何做”，对于存取路径的选择和语句的操作过程均由系统自动完成。在关系数据库管理系统（RDBMS）中，所有SQL语句均使用查询优化器，由它来决定对指定数据使用何种存取手段以保证最快的速度，这既减轻了用户的负担，又提高了数据的独立性与安全性。
（2）功能一体化的语言：SQL语言集数据定义语言DDL、数据操纵语言DML、数据控制语言DCL 及附加语言元素于一体，语言风格统一，能够完成包括关系模式定义，数据库对象的创建、修改和删除，数据记录的插入、修改和删除，数据查询，数据库完整性、一致性保持与安全性控制等一系列操作要求。SQL语言的功能一体化特点使得系统管理员、数据库管理员、应用程序员、决策支持系统管理员以及其他各种类型的终端用户只需要学习一种语言形式即可完成多种平台的数据请求。
（3）一种语法两种使用方式：SQL语言即可以作为一种自含式语言，被用户以一种联机交互的方式，在终端键盘上直接键入SQL命令来对数据库进行操作，又可以作为一种嵌入式语言，被程序设计人员在开发应用程序时直接嵌入到高级语言（例如C/C++、PowerBullder、VBScript等）中使用。
而不论在何种使用方式下的SQL语法结构都是基本一致的，因此具有极大的灵活性与方便性。（4）面向集合操作的语言：非关系数据模型采用面向记录的操作方式，操作对象是单一的某条记录，而SQL允许用户在较高层的数据结构上工作，操作对象可以是若干记录的集合，简称记录集。所有SQL语句都接受记录集作为输入，返回记录集作为输出，其面向集合的特性还允许一条SQL语句的结果作为另一条SQL语句的输入。
（5）语法简捷、易学易用的标准语言：SQL语言不仅功能强大，而且语法接近英语口语，符合人类的思维习惯，因此较为容易学习和掌握。同时又由于它是一种通用的标准语言，使用SQL编写的程序也具有良好的移植性。
3．SELECT语句语法结构
SQL语言包含数据定义语言DDL、数据操纵语言DML、数据控制语言DCL等数据库必备功能对应的语句。使用SQL语句创建查询实际上主要就是使用其中的DML语言的SELECT语句。
SELECT语句的主要功能是从数据库返回一组记录的信息，它不会修改数据库的数据。
（1）SELECT语句语法结构。
SELECT [ALL|DISTINCT] <列表达式1> [AS <别名1>][，<列表达式2> [AS <别名2>]...]
FROM <表名1>[，<表名2>...]
[WHERE <条件表达式1> [AND|OR <条件表达式2>...]
[GROUP BY <列名1> [，<列名2>...] [HAVING <条件>]]
[ORDER BY <列名1> [ASC|DESC][，<列名2> [ASC|DESC]...]]
SELECT语句基本上可以概括描述为：“从某一个（或某几个）数据表中选择满足一定条件的记录，并把这些记录相应的某一个（或某几个）字段的值投影成一张二维表格”。
（2）语法描述的约定说明。
加粗的英文大写单词表示是SQL语言的保留关键字，这些是用户在书写时是不能更改的。如“SELECT”等。
斜体表示需要用户根据具体数据库的实际情况而改变的。如“列表达式1”等。
“［］”内的内容为可选项。
“<>”内的内容为必选项。
“[ < > ]”中的内容表示如果选择了“［］”，那么必须指定“< >”中的内容。如：“[AS <别名1>]”表示如果语句中有了“AS”，那么就必须指定“别名1”。
“|”表示“或”，即前后的两个值“二选一”。如“ASC|DESC”。
书写语句时，“［］”和“<>”不能写。
书写语句时，所有的字母、数字、标点等符号一律用英文半角（包括空格），大小写无所谓（语法描述中的大写只是为了利于读者阅读）。
本章所有关于SQL语言相关语句的语法的描述都遵循此原则。
如果把SELECT语句中所有的可选部分都去除，就是一个最简化的SELECT语句，格式如下。SELECT <列表达式1>
FROM <表名1>
后面的举例都是在这个基础上逐步完善的。
4．SELECT语句的各部分功能
在写SELECT语句之前，请先打开“教务管理系统.mdb”，并建立一个空白查询，把查询设计窗口切换到SQL视图，如图5-1-8所示。
（1）SELECT子句。
“ALL| DISTINCT”：“ALL”返回查询所得到的全部记录，而不管这些记录是否有重复；“DISTINCT”，查询所得到的记录如构有重复，则不包括重复行（只返回第一条）；省略“ALL|DISTINCT”默认表示“ALL”。
任务1：在学生信息表中查询班级编号。相应的SELECT语句如下所示。
SELECT ALL 班级编号
FROM 学生信息
运行查询，结果返回的是每个学生所在班级的班级编号。由于每个班都有多个学生，而此处只选择班级编号，因此查询结果有很多重复记录，如图5-1-9所示。
任务2：在学生信息表中查询班级编号，要求不要重复
记录。相应的SELECT语句如下所示。
SELECT DISTINCT 班级编号
FROM 学生信息
运行查询，返回的结果中只剩下了两个班级编号，
如图5-1-10所示。
“<列表达式1> [AS <别名1>]”：“<列表达式1>”是对字段
可以用运算符作运算；“[AS <别名1>]”表示可以为很长
的表达式起一个别名，以方便使用。
任务3：学生信息表中查询每个学生的姓名和年龄。
可以写出如下的SELECT语句。
SELECT 姓名,年龄
FROM 学生信息
执行查询，出现“输入参数值”对话框，如图5-1-11。输入“23”，单击【确定】按钮，结果中每个学生的年龄都是“23”，如图5-1-12所示。
打开学生信息表后，会发现根本没有“年龄”这个字段，
也无法计算出这个“年龄”的值。在Access中，如果出
现上述情况，则Access认为是要执行一个参数查询，
所以要求输入年龄的值。怎样才能查询到年龄呢？仔
细查看学生信息表，发现虽然没有“年龄”字段，但是
有“出生日期”字段，可以利用“出生日期”计算出年龄。
修改SQL视图的SELECT语句如下所示。
SELECT姓名,Year(Date())-Year([出生日期]) AS 年龄
FROM 学生信息
运行查询，结果如图5-1-13所示。
在这个SELECT语句中，“Year(Date())-Year([出生日期])”是一个表达式。
其中，Date()是系统提供的返回系统当前日期的函数；Year()也是一个系统提供的函数，它的作用是从一个日期型的数据中提取年份。
因此，Year(Date())的结果就是当前的年“2007”。同样Year([出生日期])就将是数据表中当前记录的“出生日期”字段的年份。二者相减的结果就是对应的当前记录代表的学生的“年龄”。
但是，这个表达式是凭空加上去的，作为查询结果希望对应的这一列能有个清晰的名称，所以再使用“AS年龄”为这个表达式起个“别名”。查询结果中就有了“年龄”这一列。
在SELECT语句中经常有此类的应用。如：在图书销售系统中，由图书“总库存量”和“总销售量”可以得到某一本图书的当前库存量。
（2）FROM子句：要从哪些数据表中查找您想要的结果，就把对应的数据表的名字写在FROM后面，相邻两个之间用逗号隔开就可以了。
（3）WHERE子句：是用来指定要查询满足何种条件的记录。
<条件表达式1>的构成如表5-1所示。
任务4：在学生信息表中查找年龄为26岁的学生的姓名和民族。
首先，实现查找所有学生的姓名和民族。相应的SELECT语句如下所示。
SELECT 姓名,民族
FROM 学生信息
然后，为这个SELECT语句加上“WHERE”子句，如下所示。
SELECT 姓名,民族
FROM 学生信息
WHERE （Year(Date())-Year([出生日期])）= 26
图5-1-14 单个比较条件表达式
在WHE RE子句中，Year(Date())-Year([出生日期])就是前面用到的计算年龄的表达式；把这个表达式和一个具体的数值“26”相比较就构成了一个比较类型“条件表达式”，只有一个限定条件。
查询结果如图5-1-14所示。
任务5：在学生信息表中查找年龄在26～28岁之间的学生的姓名和民族。
只要把WHERE子句修改成如下所示。
SELECT 姓名，民族
FROM 学生信息
WHERE （Year(Date())-Year([出生日期])）>=26 AND （Year(Date())-Year([出生日期])）<=28
此时，条件表达式就是由“AND”连接的多重条件表达式。“AND”表示“并且”的意思。结果如图5-1-15所示。
同样的，这个例子还可以使用确定范围的条件表达
式“BETWEEN AND”。
“BE TWEEN AND”字面的意思是“在和之间”。
因此，上面的这个例子可以修改WHERE子句为如
下所示。
SELECT 姓名,民族
FROM 学生信息
WHERE （Year(Date())-Year([出生日期])）BETWEEN 26 AND 28
但是，“BETWEEN AND”只能适用于某个字段或表达式在两个值之间的情况。
图5-1-16 NOT条件表达式
如果条件多于两个，可以使用“AND”、“OR”或“NOT”把多个条件连接在一起。“OR”表示“或”的意思，“N OT”表示“非”的意思。
把这个例子再作如下的修改。
SELECT 姓名,民族
FROM 学生信息
WHERE NOT(Year(Date())-Year([出生日期])) BETWEEN 26 AND 28
这就表示：查找年龄不在26～28岁之间的学生的姓名和民族。结果如图5-1-16所示。
任务6：在学生信息表中查找所有“王”姓的学生的姓名和民族。
还是查询学生的姓名和民族，因此，除去“WHERE”子句的SELECT语句如下所示。
SELECT 姓名,民族
FROM 学生信息
那么如何确定“姓…王?”的条件呢？这就要用到“字符匹配”查询，使用“LIKE”谓词，完整的的语句如下。SELECT 姓名,民族
FROM 学生信息
WHERE 姓名LIKE '王%'
“姓名LIKE '王%'”表示姓名字段是“王”字开头。“%”匹配后面的其他多个任意字符，如“王长江”、“王猛”等情形。
如果把上面的语句的“WHERE”子句改写成“WHERE 姓名LIKE '王_'”则可以查询出“所有姓…王?，并且姓名中只有两个字的学生”。“_”表示匹配单个任意字符。
注意：由于不同的数据库产品在实现SQL时使用了自己的语法特性，所以上面两个语句在Access中书写时请把“%”换为“*”，“_”换为“?”。
下面介绍UNION类型的集合查询，也就是在案例中使用的“联合查询”。
UNION前后分别是一个SELECT语句，如下所示。
SELECT语句A
UNION
SELECT语句B
“UNION”的意思是“把前后两个SELECT语句产生的“虚拟表”（结果）合并到一个集合中”。
因此，使用“UNION”连接的两个SQL语句产生的“虚拟表”要有相同的“结构”：列的数目相等，对应的类型一致（本案例则是“完全相同”）。这是使用联合查询必须要注意的地方。
至于每个SELECT语句的查询条件，如“WHERE 国家='中国'”，则视具体情况而定，不一定非要一样。
任务7：如要查询“2004年5月1日以前与本公司有业务往来的客户的公司名称和所在城市的相关情况”，则可能的SQL语句如下所示。
SELECT 公司名称,城市
FROM 供应商
WHERE 进货日期<=#2004-5-1#
UNION
SELECT公司名称,城市
FROM 经销商
WHERE发货日期<=#2004-5-1#
如果在查询中有重复记录（即所选字段内容完全一样的记录），则联合查询只显示重复记录中的第一条记录；要想显示所有的重复记录，需要在“UNION”后加上关键字“ALL”，即写成“UNION ALL”。
5.2 相关知识
1．SQL子查询
子查询就是嵌入在另一个SELECT、SELECT INTO、INSERT INTO、DELETE或UPDATE语句内部的SELECT语句。它可以对基于另一个结果集的结果进行进一步的限制。这叫做嵌入，并且因为一个子查询就是一个SELECT语句，也可以把一个子查询嵌入到另一个子查询里面。当在一个SQL语句中使用一个子查询时，它可以作为一个域列表、WHERE子句或者HAVING子句的一部分。
有3种基本的子查询形式，并且每种都使用不同种类的谓词。
2．IN子查询
IN子查询用于检索这样的一组值，即其中记录的某一列的值都被另一个工作表或查询中的一列的值包含。它从其他工作表中只能返回一列，这是一个限制条件。如果返回的结果多于一列就会产生一个错误。
一个返回所有拥有发票的顾客的列表的SQL语句如下。
SELECT *
FROM tblCustomers
WHERE CustomerID
IN(SELECT CustomerID FROM tblInvoices)
通过使用NOT逻辑操作符，可以检索和IN子查询相反的记录，从而获得所有没有任何发票的顾客列表。
SELECT *
FROM tblCustomers
WHERE CustomerID
NOT IN(SELECT CustomerID FROM tblInvoices)
3．ANY/SOME/ALL子查询
ANY、SOME和ALL子查询谓词被用于比较主查询的记录和子查询的多个输出记录。ANY 和SOME 谓词是同义词并可以被替换使用。
当需要从主查询中检索任何符合在子查询中满足比较条件的记录时，可以使用ANY或SOME谓词。
谓词应该恰好放在子查询开始的括号前面。
SELECT *
FROM tblCustomers
WHERE CustomerID = ANY(SELECT CustomerID FROM tblInvoices)
注意由上面SQL语句所返回的结果集和IN子查询例子所返回的结果集相同。而与ANY和SOME谓词的不同之处在于它们都可以使用等于（=）以外的操作符，比如大于（>）和小于（<）。
SELECT *
FROM tblCustomers
WHERE CustomerID > ANY(SELECT CustomerID FROM tblInvoices)
当想在主查询中检索满足子查询比较条件的所有记录时使用谓词ALL。
SELECT *
FROM tblCustomers
WHERE CustomerID > ALL(SELECT CustomerID FROM tblInvoices)
4．EXISTS子查询
EXISTS谓词是用于子查询在一个结果集中检查所有存在值的记录。换句话说，就是如果子查询没有返回任何行，这个比较就是False。而如果它返回了一行或多行，这个比较就为True。
SELECT *
FROM tblCustomers AS A
WHERE EXISTS
(SELECT * FROM tblInvoicesWHERE A.CustomerID = tblInvoices.CustomerID)
注意：在前面的SQL语句中，tblCustomers工作表使用了一个别名，这就是为何可以在后来的子查询中引用它的原因。当一个子查询以这种格式与一个主查询联接时就称相关查询。
通过使用NOT逻辑操作符，可以检索和EXISTS子查询相反的记录，从而可以得到所有没有任何发票的顾客的结果集。
SELECT *
FROM tblCustomers AS A
(SELECT * FROM tblInvoicesWHERE A.CustomerID = tblInvoices.CustomerID)
5.3 相关知识
在Access中，创建数据定义查询是使用SQL语言的DDL语句。可以使用的DDL语句主要有5种，如表5-3-1所示。注意：在一个数据定义查询中只能使用一条DDL语句。
下面就这几种语句的语法分别讲解。
1．CREATE TABLE语句
CREATE TABLE语句创建一个新表，此时，表是一个空表，表中没有任何数据。这个语句的格式如下所示。
CREATE TABLE table_name
(column_name_1data_type_1[size] [ NOT NULL],
column_name_2data_type_2[size],
…
CONSTRAINT [Index_name1] PRIMARY KEY([column_name_*]);
有关这个语句的语法的相关说明如表5-3-2所示
表5-3-2 CREATE TABLE语句说明
本案例中创建的客户表的CREATE TABLE语句如下所示
这个CREATE TABLE语句创建了一个名为“客户” 的数据表；数据表中共有4个字段，分别为“客户ID”、“名称”、“电话”、“地址”；它们的数据类型是，“客户ID”为integer整型，其余为text文本型；
其中，“客户ID”被“CONSTRAINT”指定为使用约束创建为索引，并有“PRIMARY KEY”设置为主关键字。
2．DROP TABLE语句
DROP TABLE语句删除指定名称的数据表，它的语法格式为：
3．ALTER TABLE语句
ALTER TABLE语句修改已用CREATE TABLE语句创建的表的结构。它的语法格式如下。
（1）ALTER TABLE table_name ADD COLUMN column_name data_type
说明：为表table_name增加一个data_type类型的字段column_name（没有删除某个字段的语法）。举例：为表tblCustomers添加CustomerID字段。
ALTER TABLE tblCustomers ALTER COLUMN CustomerID INTEGER
（2）ALTER TABLE table_name ADD PRIMARY KEY (column_name)
说明：把表table_name的主键更改为column_nam字段。
举例：更改表tblCustomers的主关键字为CustomerID字段。
ALTER TABLE tblCustomers ADD PRIMARY KEY(CustomerID)
（3）ALTER TABLE table_name DROP PRIMARY KEY (column_name)
说明：删除表table_name已经定义的主键column_name。
举例：删除表tblCustomers的主关键字CustomerID。
ALTER TABLE tblCustomers DROP PRIMARY KEY (CustomerID)
4．CREATE INDEX命令
CREATE INDEX index_name ON table_name (column_name)
说明：对表table_name的字段column_name建立名为index_name的索引以加快查询速度。
举例：为表tblCustomers的建立以字段CustomerID为索引，命名为index1。
CREATE INDEX index1 ON tblCustomers(CustomerID)
5．DROP INDEX命令
DROP INDEX index_name ON table_name
说明：删除表table_name的索引index_name。
举例：删除表tblCustomers的索引index1。
DROP INDEX index1 ON tblCustomers
以上，就SQL语言的数据定义语句作了简要的介绍，感兴趣的读者可以查阅专门讲解SQL语言的书籍或文档。