第6章SQL语言6.1SQL概述SQL最早是在IBM公司研制的数据库管理系统SystemR上实现的。由于它接近于英语口语，具有功能丰富、使用灵活、语言简洁易学等优点，所以受到广泛的支持。经不断发展完善和扩充，SQL被美国国家标准局（ANSI)确定为关系型数据库语言的美国标准，后又被国际标准化组织（ISO)采纳为关系型数据库语言的国际标准。如今无论是OracleSybase、Informix、SQLServer等大型数据库管理系统，还是VisualFoxPro、PowerBuilder这些微机上常用的数据库开发系统，都支持SQL语言作为查询语言。SQL语言具有如下主要特点：（1）SQL是一种一体化的语言。很多数据语言都包括数据定义、数据操作等语言，用来分别实现数据定义和数据操作等功能。而SQL包括数据定义、数据查询、数据操作和数据控制功能，是集各种功能于一体化语言，它可以完成数据库活动中的全部工作。（2）SQL语言是一种高度非过程化的语言，使用时只需要告诉计算机“做什么”，而不需要告诉计算机“怎么做”，即不需要叙述具体操作过程，SQL语言就可以将要求交给系统，由系统自动完成全部工作。（3）SQL语言使用方式灵活。它既可以直接以命令方式交互使用，也可以嵌入程序设计语言中以程序方式使用。现在很多数据库应用开发工具都将SQL语言直接融入到自身的语言之中，使用起来更方便，VisualFoxPro就是如此，这些使用方式为用户提供了灵活的选择余地。（4）SQL语言非常简洁。虽然SQL语言功能很强，但它只有为数不多的儿个命令动词下表给出了SQL语言使用的命令动词及其实现的功能。另外，SQL语句本身也非常接近英语的自然语言，因此容易学习和掌握。SQL功能命令动词数据查询SELECT定义表结构CREATE数据定义修改表结构ALTERDROP删除表插入记录INSERT修改记录UPDATE数据操作删除记录DELETE授予权限GRANT数据控制收回权限REVOKE由于VisualFoxPro自身在安全控制方面的缺陷，它没有提供数据控制功能，对SQL的支持主要体现在数据查询、数据定义和数据操作这三个方面。6.2查询功能SQL的核心是查询。SQL的查询命令也称为SELECT命令，它的基本形式由SELECT一FROM一WHERE查询块组成，多个查询块可以嵌套执行。VisualFoxPro的SQL查询语句的一般格式如下：SELECT［ALL|DISTINCT】《字段表达式1>［[AS】列标题1】；[[,《字段表达式2>][[AS]列标题2]];FROM<表名或视图名>[《表名或视图名>[，J]
第 6 章 SQL 语言 6.1 SQL 概述 SQL 最早是在 IBM 公司研制的数据库管理系统 System R 上实现的。由于它接近于英语 口语，具有功能丰富、使用灵活、语言简洁易学等优点，所以受到广泛的支持。经不断发展 完善和扩充，SQL 被美国国家标准局(ANSI)确定为关系型数据库语言的美国标准，后又被国 际标准化组织(ISO)采纳为关系型数据库语言的国际标准。如今无论是 Oracle 、Sybase、 Informix、SQL Server 等大型数据库管理系统，还是 Visual FoxPro、PowerBuilder 这些 微机上常用的数据库开发系统，都支持 SQL 语言作为查询语言。 SQL 语言具有如下主要特点： （1）SQL 是一种一体化的语言。很多数据语言都包括数据定义、数据操作等语言，用 来分别实现数据定义和数据操作等功能。而 SQL 包括数据定义、数据查询、数据操作和数据 控制功能，是集各种功能于一体化语言，它可以完成数据库活动中的全部工作。 （2）SQL 语言是一种高度非过程化的语言，使用时只需要告诉计算机“做什么”，而不 需要告诉计算机“怎么做”，即不需要叙述具体操作过程，SQL 语言就可以将要求交给系统， 由系统自动完成全部工作。 （3）SQL 语言使用方式灵活。它既可以直接以命令方式交互使用，也可以嵌入程序设 计语言中以程序方式使用。现在很多数据库应用开发工具都将 SQL 语言直接融入到自身的语 言之中，使用起来更方便，Visual FoxPro 就是如此，这些使用方式为用户提供了灵活的选 择余地。 （4）SQL 语言非常简洁。虽然 SQL 语言功能很强，但它只有为数不多的几个命令动词， 下表给出了 SQL 语言使用的命令动词及其实现的功能。另外，SQL 语句本身也非常接近英语 的自然语言，因此容易学习和掌握。 SQL 功能 命令动词 数据查询 SELECT 数据定义 定义表结构 CREATE 修改表结构 ALTER 删除表 DROP 数据操作 插入记录 INSERT 修改记录 UPDATE 删除记录 DELETE 数据控制 授予权限 GRANT 收回权限 REVOKE 由于 Visual FoxPro 自身在安全控制方面的缺陷，它没有提供数据控制功能，对 SQL 的支持主要体现在数据查询、数据定义和数据操作这三个方面。 6.2 查询功能 SQL 的核心是查询。SQL 的查询命令也称为 SELECT 命令，它的基本形式由 SELECT－FROM －WHERE 查询块组成，多个查询块可以嵌套执行。Visual FoxPro 的 SQL 查询语句的一般格 式如下： SELECT [ALL|DISTINCT] <字段表达式 1> [[AS] 列标题 1] ; [[,<字段表达式 2>] [[AS] 列标题 2].]; FROM <表名或视图名>[,<表名或视图名>[,.]];
[WHERE《条件表达式>];[GROUPBY<分组字段名>[HAVING<条件表达式》]][ORDERBY<排序字段名>[ASC|DESC]]从SELECT的命令格式来看似乎非常复杂，实际上只要理解了命令中各个短语的含义，SQLSELECT还是很容易掌握的，其中SELECT和FROM是必需的，WHERE、GROUPBY和ORDERBY是常用的，它们的含义如下：?SELECT：指出要查询的数据。ALL表示查询结果中可以包含重复记录，这是缺省值；DISTINCT则表示将查询结果中的重复记录删除。AS用于说明在查询结果中该列的标题。FROM：指出要查询的数据来源于哪个表或视图。-?WHERE：指出查询条件，只有满足查询条件的记录才会显示在查询结果中。GROUPBY：用于对查询结果进行分组。HAVING必须与GROUPBY一起使用，用于筛S选出满足一定条件的分组。.ORDERBY：用于对查询结果进行排序。ASC表示按字段表达式升序排列，这是缺省值：DESC表示按字段表达式降序排列。6.2.1简单查询1、投影查询所谓投影查询，是指选择表中的全部列或部分列。【例6-1】查询学生档案表信息。SELECT*FROMXSDA结果如下图所示。分析：查询某个表的信息，如果没有说明，则是指输出该表的所有属性列，可以有两种方法。一种方法是在SELECT后列出所有的字段名，也可以使用字符“*”。查询口回区学号姓名性别出生日期是否团员籍贯简历照片男00001李平IF江苏Memogen12/11/78湖南陈遥女12/24/8000002FMemo Gen00003李江苏女10/16/77FMemo gen浙江00004刘玲女F10/15/78MemoIgen男北京00005王国胜05/05/82ITmemogen00006王刚男安徽04/21/88ITmemo gen浙江00007宋海英女10/01/86MemoGenT2、选择查询所谓选择查询，是指选择表中满足指定条件的记录，可以通过WHERE子句实现。WHERE子句常用的查询条件如下表所示。查询条件谓词比较=,>,<, >=, 《=, 1=, <>确定范围BETWEE AND, NOT BETWEEN AND确定集合IN, NOT IN字符匹配LIKE, NOT LIKE空值IS NULL,IS NOTNULI
[WHERE <条件表达式>]; [GROUP BY <分组字段名>[HAVING <条件表达式>]]; [ORDER BY <排序字段名>[ASC|DESC]] 从 SELECT 的命令格式来看似乎非常复杂，实际上只要理解了命令中各个短语的含义， SQL SELECT 还是很容易掌握的，其中 SELECT 和 FROM 是必需的，WHERE、GROUP BY 和 ORDER BY 是常用的，它们的含义如下： ⚫ SELECT：指出要查询的数据。ALL 表示查询结果中可以包含重复记录，这是缺省值； DISTINCT 则表示将查询结果中的重复记录删除。AS 用于说明在查询结果中该列的 标题。 ⚫ FROM：指出要查询的数据来源于哪个表或视图。 ⚫ WHERE：指出查询条件，只有满足查询条件的记录才会显示在查询结果中。 ⚫ GROUP BY：用于对查询结果进行分组。HAVING 必须与 GROUP BY 一起使用，用于筛 选出满足一定条件的分组。 ⚫ ORDER BY：用于对查询结果进行排序。ASC 表示按字段表达式升序排列，这是缺省 值；DESC 表示按字段表达式降序排列。 6.2.1 简单查询 1、投影查询 所谓投影查询，是指选择表中的全部列或部分列。 【例 6-1】查询学生档案表信息。 SELECT * FROM XSDA 结果如下图所示。 分析：查询某个表的信息，如果没有说明，则是指输出该表的所有属性列，可以有两种 方法。一种方法是在 SELECT 后列出所有的字段名，也可以使用字符“*”。 2、选择查询 所谓选择查询，是指选择表中满足指定条件的记录，可以通过 WHERE 子句实现。WHERE 子句常用的查询条件如下表所示。 查询条件 谓词 比较 =,>,<,>=,<=,!=,<> 确定范围 BETWEE AND, NOT BETWEEN AND 确定集合 IN, NOT IN 字符匹配 LIKE, NOT LIKE 空值 IS NULL, IS NOT NULL
【例6-2】查询高等数学成绩在80以上的学生的学号和高等数学成绩。SELECT学号，高等数学FROMXSCJWHERE高等数学>=80或SELECT学号，高等数学FROMXSCJWHERENOT高等数学<8O结果如下图所示。口回区照查询学号高等数学0000185.00000396.04【例6-3】查询英语成绩在70分和80分之间（包括70分和80分）的学生的学号和英语成绩。SELECT学号，英语FROMXSCJWHERE英语BETWEEN70AND80结果如下图所示。提示：与本例中的查询条件“英语BETWEEN70AND80”等价的表达式有：英语>=70AND英语<=80NOT（英语<70OR英语>80)查询口D区学号英语0000277.0N0000674.0【例6-4】查询学生档案表中姓“王”的学生信息。SELECT*FROMXSDAWHERE姓名LIKE“王%”结果如下图所示。分析：谓词LIKE可以用来进行字符串的匹配，字符串可以包含通配符%和。其中，%代表任意多个字符，代表任意的单个字符。提示：与本例中的查询条件“姓名LIKE“王%””等价的表达式有：LEFT（姓名2)=”王”SUBSTR(姓名，1,2)=”王”查询口回区学号姓名性别出生日期是否团员籍贯简历照片00005王国胜男V05/05/82IT北京memo gen男00006王刚17安徽04/21/88memogen6.2.2排序在SQL查询命令中，可以用ORDERBY子句对查询结果按照一个或多个属性列的升序或降序进行排列，缺省值为升序。ORDERBY子句的格式如下：ORDERBY<排序字段名》［ASCDESC】[TOPN[PERCENT]]
【例 6-2】查询高等数学成绩在 80 以上的学生的学号和高等数学成绩。 SELECT 学号,高等数学 FROM XSCJ WHERE 高等数学>=80 或 SELECT 学号,高等数学 FROM XSCJ WHERE NOT 高等数学<80 结果如下图所示。 【例 6-3】查询英语成绩在 70 分和 80 分之间（包括 70 分和 80 分）的学生的学号和英 语成绩。 SELECT 学号,英语 FROM XSCJ WHERE 英语 BETWEEN 70 AND 80 结果如下图所示。 提示：与本例中的查询条件“英语 BETWEEN 70 AND 80”等价的表达式有： 英语>=70 AND 英语<=80 NOT (英语<70 OR 英语>80) 【例 6-4】查询学生档案表中姓“王”的学生信息。 SELECT * FROM XSDA WHERE 姓名 LIKE “王%” 结果如下图所示。 分析：谓词 LIKE 可以用来进行字符串的匹配，字符串可以包含通配符%和_。其中，% 代表任意多个字符，_代表任意的单个字符。 提示：与本例中的查询条件“姓名 LIKE “王%”等价的表达式有： LEFT(姓名,2)=”王” SUBSTR(姓名,1,2)=”王” 6.2.2 排序 在 SQL 查询命令中，可以用 ORDER BY 子句对查询结果按照一个或多个属性列的升序或 降序进行排列，缺省值为升序。ORDER BY 子句的格式如下： ORDER BY <排序字段名> [ASC|DESC] [TOP N [PERCENT]]
【例6-5】查询成绩表信息，要求记录按高等数学成绩降序排列。SELECT*FROMXSCJORDERBY高等数学DESC结果如下图所示。区查询学号高等数学 英语 计算机基础 马哲总分H0000396.087.095.095.00.095.00.00000185.086.092.00000570.069.076.085.00.00000762.046.067.075.00.00000660.074.071.085.00.00000441.054.064.085.00.00000230.077.067.085.00.0【例6-6】查询成绩表信息，要求按马哲成绩降序，马哲成绩相同时按高等数学成绩升序排列。SELECT*FROMXSCJORDERBY马哲DESC高等数学结果如下图所示。分析：SQL查询命令中的排序与VisualFoxPro中的SORT排序命令一样，都支持多个排序字段，并可以分别对各个排序字段进行升序或降序控制。口回区查询学号高等数学英语计算机基础马哲总分V0000185.086.092.095.00.00000396.087.095.095.00.00000230.077.067.085.00.00000441.054.064.085.00.071.00000660.074.085.00.00000570.069.076.085.00.00000762.046.067.075.00.016.2.3简单的计算查询SQL不仅具有一般的检索能力，而且还有计算方式的检索，如检索学生的平均成绩、检索学生的最高分等。可用于计算检索的函数有：①COUNT一计数②SUM一求和③AVG一计算平均值①MAX一求最大值MIN一求最小值【例6-7】统计学生档案表中学生的总人数和籍贯数。SELECTCOUNT（*）AS总人数，COUNT（DISTINCT籍贯）AS籍贯数；FROM XSDA结果如下图所示。分析：在学生档案表中，学生总人数就是记录数，统计全表的记录总数用COUNT（*)。在统计籍贯时，为了去除重复的籍贯，要使用DISTINCT短语。同时要输出有意义的列名
【例 6-5】查询成绩表信息，要求记录按高等数学成绩降序排列。 SELECT * FROM XSCJ ORDER BY 高等数学 DESC 结果如下图所示。 【例 6-6】查询成绩表信息，要求按马哲成绩降序，马哲成绩相同时按高等数学成绩升 序排列。 SELECT * FROM XSCJ ORDER BY 马哲 DESC,高等数学 结果如下图所示。 分析：SQL 查询命令中的排序与 Visual FoxPro 中的 SORT 排序命令一样，都支持多个 排序字段，并可以分别对各个排序字段进行升序或降序控制。 6.2.3 简单的计算查询 SQL 不仅具有一般的检索能力，而且还有计算方式的检索，如检索学生的平均成绩、检 索学生的最高分等。可用于计算检索的函数有： ①COUNT—计数 ②SUM—求和 ③AVG—计算平均值 ④MAX—求最大值 ⑤MIN—求最小值 【例 6-7】统计学生档案表中学生的总人数和籍贯数。 SELECT COUNT(*) AS 总人数,COUNT(DISTINCT 籍贯) AS 籍贯数; FROM XSDA 结果如下图所示。 分析：在学生档案表中，学生总人数就是记录数，统计全表的记录总数用 COUNT(*)。 在统计籍贯时，为了去除重复的籍贯，要使用 DISTINCT 短语。同时要输出有意义的列名
还需要使用AS短语。口回查询总人数籍贯数75A【例6-8】求成绩表中学生的平均英语成绩。SELECT_AVG(英语）FROMXSCJ结果如下图所示。口回区照查询AV_英语F70.43一【例6-9】求计算机基础的最高成绩和最低成绩。SELECTMAX（计算机基础），MIN（计算机基础）FROMXSCJ结果如下图所示。雕查询口回区Max_计算机基础Min_计算机基础口95.064.0十区6.2.4分组与计算查询在SQL查询命令中，常用GROUPBY子句对查询结果进行分组，可实现数据的分类汇总功能。GROUPBY子句的格式如下：GROUPBY<分组字段名>[HAVING<条件表达式>]【例6-10】求各城市学生的平均年龄。SELECT籍贯，AVG（YEAR(DATE（)）-YEAR（出生日期））AS平均年龄：FROMXSDAGROUPBY籍贯结果如下图所示。00×咖查询籍贯平均年龄V安徽21.00北京27.00湖南29.00江苏31.50浙江27.00【例6-11】查询平均年龄在28岁以下(包括28岁)的各城市学生的平均年龄。SELECT籍贯，AVG（YEAR（DATE（)）-YEAR（出生日期））AS平均年龄：FROMXSDAGROUPBY籍贯HAVING平均年龄<=28
还需要使用 AS 短语。 【例 6-8】求成绩表中学生的平均英语成绩。 SELECT AVG(英语) FROM XSCJ 结果如下图所示。 【例 6-9】求计算机基础的最高成绩和最低成绩。 SELECT MAX(计算机基础),MIN(计算机基础) FROM XSCJ 结果如下图所示。 6.2.4 分组与计算查询 在 SQL 查询命令中，常用 GROUP BY 子句对查询结果进行分组，可实现数据的分类汇总 功能。GROUP BY 子句的格式如下： GROUP BY <分组字段名> [HAVING <条件表达式>] 【例 6-10】求各城市学生的平均年龄。 SELECT 籍贯,AVG(YEAR(DATE())-YEAR(出生日期)) AS 平均年龄; FROM XSDA GROUP BY 籍贯 结果如下图所示。 【例 6-11】查询平均年龄在 28 岁以下(包括 28 岁)的各城市学生的平均年龄。 SELECT 籍贯,AVG(YEAR(DATE())-YEAR(出生日期)) AS 平均年龄; FROM XSDA GROUP BY 籍贯 HAVING 平均年龄<=28