第5章结构化查询语言结构化查询语言(Structured Query Language,SQL)是一种通用且功能极其强大的关系数据库语言,也是关系数据库的标准语言,具有数据定义、数据操纵(包括数据更新和数据查询)、数据控制等功能。本章主要介绍Access支持的SQL功能及其使用方法。
由于不同的关系数据库管理系统在实现标准SQL语言时各有差别,因此本章的SQL语言均指Access所支持的SQL语言。本章的SQL命令均在“销售管理”数据库中进行，以下不再赘述。
5.1SQL语言概述
SQL语言是数据库的标准语言,目前几乎所有关系数据库管理系统都支持SQL标准。SQL语言的主要特点如下。
(1) 高度集成化。SQL语言集数据定义、数据操纵(包括数据更新和数据查询)、数据控制功能于一体,可以独立完成数据库操作和管理中的全部工作,为数据库应用系统的开发提供了良好的手段。
(2) 高度非过程化。SQL是一种非过程化的语言。使用SQL语言进行数据操作不必告诉计算机怎么做,只要提出做什么,系统就可以自动完成全部工作,从而大幅减轻用户的负担,同时有利于提高数据独立性。
(3) 面向集合的操作方式。SQL语言采用集合操作方式,操作对象和操作结果都是记录集。
(4) 简洁易学。SQL语言不仅功能强大,而且非常简洁,完成核心功能只用了9个命令动词(CREATE、ALTER、DROP、INSERT、UPDATE、DELETE、SELECT、GRANT、REVOKE),许多复杂的工作仅通过一条SQL命令就可以完成。另外,SQL语言接近英语的自然语法,易学易用。
(5) 用法灵活。SQL语言按其使用方式可分为交互式命令语言(又称自含型或自主型语言)和宿主型语言(可嵌入C、C++、COBOL等高级过程性语言中)两种结构形式,其语法基本相同。
5.2SQL数据定义
使用SQL的CREATE、ALTER和DROP命令可以实现数据定义功能,包括表、索引等的创建、修改和删除。
1. 定义表
使用CREATE TABLE 命令,其语法为CREATE TABLE <表名> 
(<字段名l> <数据类型1>\[(<大小>)\] \[NOT NULL\] \[PRIMARY KEY|UNIQUE\] 
\[,<字段名2> <数据类型2> \[(<大小>)\] \[NOT NULL\] \[PRIMARY KEY|UNIQUE\]\]
\[,...\] )说明:
① 在上述格式中,“< >”表示必选项,“［ ］”表示可选项,“|”表示多选一,且命令关键字不区分大小写。
② 在定义表时,必须指定表名、各个字段名及相应的数据类型和字段大小(由系统自动确定的字段大小省略),并且各个字段之间用半角逗号分隔。
③ 字段的数据类型用字符表示,如:
Text(短文本)、Byte(字节)、Integer(长整型数字)、Single(单精度型数字)、Float(双精度型数字)、Currency(货币)、Memo(长文本)、Date(日期/时间)、Logical(是/否)、OLEObject(OLE对象)、Counter(自动编号)等。
④ NOT NULL指定字段不允许为空值,PRIMARY KEY定义主键,UNIQUE定义唯一键。
⑤ 语句中的各个关键词不区分大小写。
Access数据库与程序设计（第3版）——微课版第5章结构化查询语言【例51】在“销售管理”数据库中,使用SQL语句定义emp表,结构如下。
员工号(Text,3字符)、姓名(Text,4字符)、性别(Text,1字符)、婚否(Logical)、聘用日期(Date)、照片(OLEObject)、简历(Memo),员工号为主键,姓名不允许为空值。
操作步骤如下。
① 打开“销售管理”数据库,执行“创建”→“查询”→“查询设计”命令,打开查询设计视图,然后直接关闭“显示表”对话框。
② 执行“查询工具/设计”→“查询类型”→“数据定义”命令,在“数据定义”查询窗口中输入SQL语句,如图51所示。注意: 在“数据定义”查询窗口中一次只能输入一条SQL语句。

③ 在设计视图下,单击功能区的“运行”按钮,执行SQL语句,创建emp表。
④ 可以将SQL语句保存为一个查询对象,也可以直接关闭“数据定义”查询窗口。
在“导航”窗格中选择emp表,打开表设计视图,结果如图52所示。
图51使用SQL语句定义emp表
图52在表设计视图中查看emp表

【例52】在“销售管理”数据库中,使用SQL语句定义item表和sale表。
item表结构: 商品号(Text,6字符)、品牌(Text,10字符)、类别(Text,6字符)、型号(Text,15字符)、价格(Single),商品号为主键。
sale表结构: 序号(Counter)、员工号(Text,3字符)、商品号(Text,6字符)、销售量(Integer)、销售日期(Date),序号为主键。
操作步骤如下。
① 打开“数据定义”查询窗口,按图53所示输入SQL语句。
② 单击功能区的“运行”按钮,执行SQL语句,创建item表,然后关闭查询窗口。
③ 打开一个新的“数据定义”查询窗口,按图54所示输入SQL语句。
④ 单击功能区的“运行”按钮,执行SQL语句,创建sale表。
图53使用SQL语句定义item表
图54使用SQL语句定义sale表

2. 建立索引
使用CREATE INDEX命令建立索引,其语法为CREATE \[UNIQUE\] INDEX <索引名称> ON <表名>
(<索引字段1>\[ASC|DESC\]\[,<索引字段2>\[ASC|DESC\]\[,...\]\]) 
\[WITH PRIMARY\]说明:
① UNIQUE指定唯一索引,WITH PRIMARY指定主索引。
② ASC和DESC指定索引值的排列方式,ASC表示升序,DESC表示降序,默认为升序。
【例53】使用SQL语句建立索引,在item表的“型号”字段上建立唯一索引,索引名称为uni_model;在“类别”和“价格”2个字段上分别按升序和降序建立组合索引,索引名称为idx_tprice。
操作步骤如下。
① 打开“数据定义”查询窗口,输入以下SQL语句:create unique index uni_model on item(型号)② 单击功能区的“运行”按钮,执行SQL语句,建立uni_model索引，然后关闭查询窗口。
③ 打开一个新的“数据定义”查询窗口,输入以下SQL语句:create index idx_tprice on item(类别, 价格 desc)④ 单击功能区的“运行”按钮,执行SQL语句,建立idx_tprice索引。
3. 建立表间关系
使用CREATE TABLE命令在定义表的同时可以建立表间关系。
【例54】在“销售管理”数据库中,使用SQL语句定义sale2表,并通过“员工号”字段与emp表建立关系,通过“商品号”字段与item表建立关系。
操作步骤如下。
① 打开“数据定义”查询窗口,按图55所示输入SQL语句。语句中的references 关键词表示参照引用。
图55使用SQL语句定义sale2表
② 单击功能区的“运行”按钮,执行SQL语句,创建sale2表,并建立表间关系。
在“关系”窗口中加入emp表、item表和sale2表,结果如图56所示。
图56“关系”窗口
4. 修改表
使用ALTER TABLE命令可以修改表的结构,包括修改、添加和删除字段等。
(1) 修改字段,其语法为ALTER TABLE <表名> ALTER \[column\] <字段名> <数据类型>(<大小>)说明: 使用该命令时不能修改字段名。
(2) 添加字段,其语法为ALTER TABLE <表名> ADD \[column\] <字段名> <数据类型>(<大小>)(3) 删除字段,其语法为ALTER TABLE <表名> DROP \[column\] <字段名>例如,使用SQL语句修改表,在emp表中增加一个“电话号码”字段(Integer),然后将该字段修改为Text型(11字符),最后删除该字段,可以使用以下命令。alter table emp add column 电话号码 integer
alter table emp alter 电话号码 text(11)
alter table emp drop 电话号码5. 删除索引或表
使用DROP命令可以删除表上的索引或删除表。
(1) 删除索引，其语法为DROP INDEX <索引名称> ON <表名>(2) 删除表，其语法为DROP TABLE <表名>注意: 删除表后,在表上定义的索引也会被一起删除。

例如,使用SQL语句删除item表中的uni_model索引项。drop index uni_model on item使用SQL语句删除sale表。drop table sale5.3SQL数据更新
使用SQL的INSERT、UPDATE和DELETE命令可以实现数据更新操作,包括插入记录、更新记录和删除记录。
1. 插入记录
使用INSERT命令,其语法为INSERT INTO <表名> \[(<字段名1>\[,<字段名2>\[,...\]\])\]
VALUES(<表达式1>\[,<表达式2>\[,...\]\])说明: 如果缺省字段名,则必须为新记录中的每个字段都赋值,且数据类型和顺序要与表中定义的字段一一对应。
【例55】使用SQL语句在emp表中插入2条员工记录。
操作步骤如下。
① 打开“数据定义”查询窗口,输入以下SQL语句:insert into emp values
("a01","马立","男",yes,#2011-5-15#,null,null)② 单击功能区的“运行”按钮,执行SQL语句,在emp表中插入记录。
③ 重复上述2个步骤,插入第2条记录:insert into emp(员工号,姓名,性别,聘用日期) values
("b01","陈慧娟","女",#2011-7-10#)打开emp表,结果如图57所示。“婚否”字段中的0表示“否”,-1表示“是”。
图57emp表中的记录
2. 更新记录
使用UPDATE 命令,其语法为UPDATE <表名> SET <字段名1>=<表达式1>\[,<字段名2>=<表达式2>\[,...\]\]
\[WHERE <条件>\]例如,使用SQL语句将emp表中所有男员工的“婚否”字段改为“否”。update emp set 婚否=no where 性别="男"3. 删除记录
使用DELETE 命令,其语法为DELETE FROM <表名> \[WHERE <条件>\]例如,使用SQL语句删除emp表中的所有记录。delete from emp5.4SQL数据查询
数据查询是数据库的核心操作,使用SQL语言的SELECT命令可以实现数据查询操作,包括选择查询、投影查询、连接查询、子查询、合并查询等。
5.4.1SQL查询语法
SELECT命令是SQL的核心语句,具有灵活的使用方式和丰富的功能。在Access中,查询的数据来源可以是表,也可以是另一个查询对象。
SELECT命令的语法为SELECT \[ALL|DISTINCT\] \[TOP <数值> \[PERCENT\]\]<目标列> \[\[AS\] <列标题>\] 
FROM <表1或查询1>\[\[AS\] <别名1>\],<表2或查询2>\[\[AS\]<别名2>\]
\[ WHERE <连接条件> AND <筛选条件>\]
\[ GROUP BY <分组项> \[ HAVING <分组筛选条件>\]\]
\[ ORDER BY <排序项> \[ ASC | DESC \]\]第4章介绍了使用查询设计视图建立查询的方法。实际上,在查询设计视图中建立的查询都由Access中的SQL语法转换引擎自动转换为了SQL语句。
表51中列出了SELECT命令中各子句与查询设计视图中各栏目之间的对应关系。表51SELECT子句与查询设计视图栏目之间的对应关系SELECT子句查询设计视图栏目SELECT子句查询设计视图栏目SELECT <目标列>“字段”栏GROUP BY <分组项>“总计”栏FROM <表或查询>“显示表”对话框ORDER BY <排序项>“排序”栏WHERE <筛选条件>“条件”栏单击功能区的“视图”列表按钮,从“视图”列表中选择“SQL视图”或“设计视图”选项,即可以在SQL命令窗口和查询设计视图之间切换。
基本的SQL查询
5.4.2基本的SQL查询
基本的SQL查询包括投影、选择、排序、分组等操作。
1. 投影
投影查询是指从数据源中选择若干列,相当于关系代数中的投影运算,其格式为SELECT <目标列1>\[,<目标列2>\[,...\]\] FROM <表或查询>说明:
① <目标列>可以是数据源中已有的字段,也可以是一个计算表达式。
② <目标列>使用“”,表示选择数据源中的所有字段。
【例56】查询“员工”表中所有员工的员工编号、姓名、性别和聘用日期。
操作步骤如下。
① 打开SQL命令窗口,输入以下SQL语句:select 员工编号, 姓名, 性别, 聘用日期 from 员工② 单击功能区的“运行”按钮,执行SQL语句,查询结果如图58所示。
单击功能区的“视图”列表按钮,选择“设计视图”选项,切换到查询设计视图,结果如图59所示。
图58查询员工记录
图59查询设计视图

若要查询“员工”表中的所有记录,则可以使用以下SQL语句:select  from 员工【例57】查询“员工”表中所有员工的员工编号、姓名和聘用年数。select 员工编号, 姓名, year(date())-year(聘用日期) as 聘用年数
from 员工使用AS关键字可以改变输出列的列标题,本例将第3列的标题指定为“聘用年数”。
【例58】在“销售”表中查询每件商品的商品编号。select distinct 商品编号 from 销售在第一个字段名前加DISTINCT关键字,可以删除查询结果中重复的记录。本例中如果不加DISTINCT关键字,则在查询结果中将包含很多重复的记录。
2. 选择
选择查询是从表中选择满足条件的记录,相当于关系代数中的选择运算,其格式为SELECT <目标列> FROM <表名> WHERE <筛选条件>说明:
① <筛选条件>是一个逻辑表达式,由多个关系表达式通过逻辑运算符连接而成。
② 有关查询条件的设置请参见4.2.3节。
【例59】查询“员工”表中所有已婚的女员工的员工编号、姓名和聘用日期。select 员工编号, 姓名, 聘用日期 from 员工 where 性别="女" and婚否=yes【例510】查询“商品”表中商品编号以“D”开头的所有商品记录。select  from 商品 where 商品编号 like "D"3. 排序
在SELECT语句中使用ORDER BY子句可以对查询结果按照一个或多个列的升序(ASC)或降序(DESC)进行排列,默认是升序，该子句的格式为ORDER BY <排序项> \[ASC|DESC\]说明:
① <排序项>既可以是字段名,也可以是目标列的序号,如第1列为1,第2列为2……。可以有多个排序项，表示多级排序。
② 若要从查询结果中选择排在前面的若干记录(用数值或百分比指定),则可以在目标列前加上TOP短语,其格式为SELECT TOP <数值> \[PERCENT\] <目标列> FROM <表或查询>【例511】查询“商品”表中销售价在900～1200(包括900和1200)的商品记录,并按销售价降序排列。select  from 商品 where 销售价 between 900 and 1200 order by 销售价 desc【例512】查询“销售”表中员工编号为“001”“003”和“005”的员工的销售记录,并按“员工编号”排序,对同一员工再按销售日期降序排列。select  from 销售 where 员工编号 in("001","003","005")
order by 员工编号, 销售日期 desc图510查询销售价最低的3种商品【例513】查询“商品”表中销售价最低的3种商品的商品编号、品牌和销售价。select top 3 商品编号,品牌,销售价 from 商品 order by 销售价查询结果如图510所示。
4. 分组
在SELECT语句中使用GROUP BY子句可以按照一列或多列的值进行分组，该子句的格式为GROUP BY <分组项> \[ HAVING <分组筛选条件>\]说明:
① 使用HAVING短语可以对分组后的结果进行筛选。注意: HAVING短语必须和GROUP BY子句同时使用。

② 分组查询通常与SQL聚合函数一起使用,先按指定的数据项分组,再对各组进行总计,如计数、求和、求平均值等。如果未分组,则聚合函数作用于整个表。
常用的SQL聚合函数如表52所示。表52常用的SQL聚合函数函数功能函数功能COUNT统计记录个数(不包括空值Null)MIN求一列值中的最小值AVG求一列数值型数据的平均值MAX求一列值中的最大值SUM求一列数值型数据的总和【例514】统计“商品”表中的商品数。select count() as 商品数 from 商品查询结果如图511所示。
【例515】统计“商品”表中各类别商品的数量。select 类别, count() as 各类别数量 from 商品 group by 类别查询结果如图512所示。
图511统计商品数
图512统计各类别商品数

【例516】在“销售”表中查询至少有3次销售记录的员工编号。select 员工编号 from 销售 
group by 员工编号 having count()>=3图513例516的
查询结果