6.2 使用SQL建立查询

 

6.2  使用SQL建立查询

SQL结构化查询语言（Structured Query Language）是一种适用于数据库管理系统的标准化查询语言。SQL语言的标准经历了SQL86、SQL89、SQL92和SQL99几个阶段。

在Visual FoxPro数据表管理系统中，嵌入的SQL语言只是SQL结构化查询语言中的一个子集，它可以在Visual FoxPro的命令窗口和任何程序中使用。

标准的SQL语言包括以下4个部分的内容。

（1）数据定义：用于定义和修改基本表，定义视图和定义索引。如Create（建立）、Drop（删除）和Alter（修改）。

（2）数据操纵：用于对表或视图的数据进行添加、删除和修改。如Insert（插入）、Delete（删除）和Update（更新）。

（3）数据查询：用于检索数据。如Select（选择）。

（4）数据控制：用于控制用户对数据的存取权力。如Grant（授权）和Revote（回收权限）。

6.2.1  Visual FoxPro常用的SQL命令

Visual FoxPro系统的SQL查询语言只包括标准SQL语言前三部分的大部分内容。下面将介绍Visual FoxPro系统常用的SQL数据查询命令。

1．建立表结构（CREATE TABLE）

表中每个字段由字段名、字段类型、精度、比例、是否允许取空值和参照完整性规则来定义。

格式：

Create Table | DBF<表名1>[NAME <长表名>][FREE]

(<字段名1> <字段类型> [(字段宽度[,小数位数])]

[NULL｜NOT NULL]

[CHECK <逻辑表达式1> [ERROR <文本信息1>]]

[DEFAULT <表达式1>] [PRIMARY KEY｜UNIQUE]

[REFERENCES <表名2> [TAG <标识名1>]

[,<字段名2>…]

【例6-1】使用SQL命令创建一个名为“学生通讯表”（xstx.dbf）的自由表，含有编号、姓名、电话号码、手机号码、城市5个字段。

create table xstx ;

free (编号N(4),姓名C(6),电话号码C(12),手机号码C(12),城市C(10))

USE xstx

MODI STRU

此时将打开该表（xstx.dbf）的“表设计器”窗口，并显示出建立的相应表结构，如图6-1所示。

图6-1  建立的表结构

【例6-2】建立一个“仓库”数据库，再在其中使用SQL命令建立一个“部门需求表”（bmxq.dbf），其中包含有设备号、部门名称、需求数量、需求时间、结束时间5个字段，并以“设备号”字段为关键字建立主索引。

CREATE DATABASE 仓库

CREATE TABLE BMXQ (设备号C(20) PRIMARY KEY,;

部门名称C(20),需求数量N(4),需求时间D(8),结束时间D(8))

OPEN DATABASE 仓库

BROWSE

此时将打开该数据表的浏览窗口，如图6-2所示。

图6-2  建立的数据表

2．修改表结构（ALTER TABLE）

使用SQL命令修改表结构时，可以使用ALTER TABLE命令进行，而在使用本命令之前，不必事先打开被修改的数据表。

格式一：

ALTER TABLE <表名1> ADD｜ALTER [COLUMN]

<字段名1><字段类型>[(字段宽度[,小数位数])]

[NULL｜NOT NULL]

[CHECK <逻辑表达式1> [ERROR <文本信息1>]]

[DEFAULT <表达式1>] [PRIMARY KEY｜UNIQUE]

[REFERENCES <表名2> [TAG <标识名1>]

[NOCPTRANS]

功能：为指定的表增加（ADD）给定的字段，或者修改（ALTER）指定的字段。

【例6-3】在例6-1所创建的学生通讯表（xstx.dbf）中添加“邮编”字段，并将“城市”字段的宽度改为8。

ALTER TABLE XSTX ADD 邮编C(6)

ALTER TABLE XSTX ALTER 城市C(8)

BROWSE

此时将打开xstx.dbf表的浏览窗口，发现新添加了“邮编”字段，并且“城市”字段的宽度被改为8，如图6-3所示。

图6-3  修改表结构

格式二：

ALTER TABLE <表名1>  ALTER [COLUMN]

<字段名1><字段类型>[(字段宽度[,小数位数])] <字段名2>

[NULL｜NOT NULL]

[SET DEFAULT <表达式2>

[SET CHECK <逻辑表达式2> [ERROR <文本信息2>]]

[DROP DEFAULT]

[DROP CHECK]

功能：设置或删除指定表中指定字段的默认值或者约束条件。

【例6-4】在例6-2所创建的部门需求表（bmxq.dbf）中，为“需求时间”字段设置一个默认值为当天的日期。

OPEN DATABASE 仓库

ALTER TABLE BMXQ ALTER 需求时间SET DEFAULT DATE()

BROWSE

此时在打开的数据表浏览窗口中，如果追加记录时，则“需求时间”字段的默认值为当天日期，如图6-4所示。

图6-4  设置字段的默认值

格式三：

ALTER TABLE <表名1>  [DROP [COLUMN] <字段名3>

[SET CHECK <逻辑表达式3> [ERROR <文本信息3>

[DROP CHECK]

[ADD PRIMARY KEY <表达式3> TAG <标识名2>]

[DROP PRIMARY KEY]

[RENAME COLUMN <字段名4> TO <字段名5>]

[NOVALIDATE]

功能：删除指定表中的指定字段，设置或删除指定表中指定字符的约束条件，增加或删除主索引、候选索引、外部关键字索引，以及对字段重命名等。

【例6-5】在例6-1所创建的学生通讯表（xstx.dbf）中，删除“手机号码”字段，并将其“电话号码”字段更名为“住宅号码”。

ALTER TABLE XSTX DROP COLUMN 手机号码

ALTER TABLE XSTX RENAME COLUMN 电话号码TO 住宅号码

BROWSE

此时在打开的浏览器窗口中删除了“手机号码”字段，并且将“电话号码”字段名更改为“住宅号码”，如图6-5所示。

图6-5  设置字段的默认值

3．对记录作删除标记（DELETE）

使用DELETE语句，既可以将单个记录作删除标记，也可以将多个记录作删除标注，根据WHERE后的条件表达式来决定。

【例6-6】在学生档案表（xsda.dbf）中，将“籍贯”为“四川”的记录作删除标记。

DELETE FROM XSDA WHERE 籍贯="四川"

BROWSE

此时在打开该表的浏览窗口中可以看到有2条记录已经做了删除标记。

图6-6  将指定段做删除标记

4．插入记录（INSERT）

在已经存在的数据表尾部追加一条新的记录，其新增的记录数据可以是INSERT命令自带的，也可以来自一个数组。

【例6-7】在学生通讯表（xstx.dbf）中，使用INSERT命令增加一条记录。

INSERT INTO XSTX(编号,姓名,住宅号码,城市,邮编);

VALUES(1001,"张建华","(010)8675345","北京","100061")

USE xstx

BROWSE

此时打开该表的浏览窗口，看到新增加了一条记录，如图6-7所示。

图6-7  插入记录

6.2.2  SELECT命令的格式

SELECT命令是SQL查询中唯一的一条命令，但它几乎能完成从简单到复杂的各种各样的查询任务。如多数据表的查询、统计查询、查询排序、合并查询、生成各种查询文件等。

格式：

SELECT 表名1. 字段名1 [AS 标题名1], 表名1. 字段名2 [AS 标题名2], …

FROM 数据库名! 表名1 [，数据库名! 表名2 ] [, 数据库名! 表名3 ]

[TO FILE 文本文件名| into table | into cursor 表文件名]

[ WHERE 选定条件]

[GROUP BY 分组字段名]

[HAVING 分组中的满足条件]

[ORDER BY 排序字段名1 [ASC | DESC] [, 排序字段名2 [ASC | DESC] ...]]

说明：

（1）SELECT子句用于指明查询输出的项目，可以是字段或表达式。

（2）FROM子句指明被查询的表或视图。

（3）INTO 子句用来指明查询结果保存到何处，可以是数组、临时表或表。TO子句也可以指明查询结果输出到何处，可以是文件、打印机或主窗口。若SELECT语句中INTO子句和TO子句同时出现，则TO子句不起作用。

（4）WHERE子句说明查询的条件。DISTINCT选项用来消除查询结果中重复出现的记录或字段，TOP n为输出前面n条记录。PERCENT选项指定输出的百分率。

（5）GROUP BY子句按列的值分组，列值相同的为一组。HAVING子句选择满足条件的组进行输出。

（6）ORDER BY子句对查询结果按指定的列的值进行排序，ASC为升序，DESC为降序。

（7）UNION子句把两个或多个SELECT语句的查询结果组合起来，默认删除重复行，保留要加ALL选项。

6.2.3  SELECT语句的应用

在前面介绍了SELECT语句的命令格式和相关参数，下面通过一些相关的实例来说明该语句的应用。

1．单表的查询

【例6-8】在学生档案表（xsda.dbf）中，显示学号为JD1001或者班级为“大一一班”的学生，并且籍贯为“广东”学生的姓名、班级、籍贯、年龄（指现年龄）字段内容。

其中，“学生档案表”（xsda.dbf）的数据如图6-8所示。

图6-8  学生档案表（xsda.dbf）

Use XSDA

Select 姓名,班级,籍贯,Year(Date())-Year(出生年月)  As 年龄From XSDA ；

where  (学号="JD1001" Or 班级="大一一班")  And 籍贯="广东"

查询的结果如图6-9所示。

图6-9  查询的结果

【例6-9】列出学生档案表（xsda.dbf）中所有的班级，除去重复的班级。

Select 班级As 班级名称From  XSDA.DBF  Distinct

查询的结果如图6-10所示。

图6-10  查询的结果

【例6-10】 统计学生成绩表（xscj.dbf）中学生各门课程的高考成绩及总分。学生成绩表（xscj.dbf）的数据如图6-11所示。

图6-11  学生成绩表（xscj.dbf）

Use  xscj  Exclusive

Select 学号,语文As 语文成绩,数学As 数学成绩,外语As 外语成绩,政治As 政治成绩,;

语文+数学+外语+政治As 高考总分From XSCJ

查询的结果如图6-12所示。

图6-12  查询的结果

【例6-11】若已经将其总分填入到数据表中，则统计学生成绩表（xscj.dbf）中专业为“微机”的平均成绩、最高成绩和最低成绩。

Select 专业,Avg(总分) As 平均成绩,Max(总分) As 最高成绩,Min(总分) As 最低成绩；

FROM XSCJ Where 专业="微机"

查询的结果如图6-13所示。

【例6-12】在“学生成绩表”（xscj.dbf）中，查询“机电”专业并且语文成绩在80～99分之间的学生的编号、学号、姓名、语文、专业。

图6-13  查询的结果

Select 编号,学号,姓名,语文,专业From xscj Where 专业Like ”机电%” And 语文；

Between 80 And  99

查询的结果如图6-14所示。

图6-14  查询的结果

2．多表的查询

在前面的几个查询例子中，均是以单个数据表的形式进行查询的。同样，Selcet-SQL可以对多个数据表进行查询。

【例6-13】在学生档案表（xsda.dbf）和学生成绩表（xscj.dbf）中，查询“年龄>20”的记录，并要求显示学号、姓名、专业、语文、数学、外语和政治。

Select A.学号,A.姓名,B.专业,B.语文,B.数学,B.外语,B.政治;

 From xsda A,xscj B Where A.编号=B.编号and a.年龄>20

查询的结果如图6-15所示。

图6-15  查询的结果

3．嵌套查询

在一个SELECT语句的WHERE子句中出现另一个SELECT语句，称为嵌套查询。WHERE子句中出现的SELECT语句叫做子查询，子查询必须用括号括起来。

【例6-14】在学生成绩表（xscj.dbf）中，查询年龄大于“微机”专业的所有学生的各项信息。

Select * From XSCJ Where 语文>All(Select 语文From XSCJ Where 专业="微机")

查询的结果如图6-16所示。

图6-16  查询的结果

6.2.4  查询结果的处理

查询结果的处理主要包括查询结果的输出、查询结果排序、查询结果分组和筛选等处理。

1．查询结果的输出

定向输出也是SELECT命令的一个选项，通过使用定向输出选项，可以将查询结果以多种形式的文件输出到指定的目标。输出子句默认为Query（Browse）方式。

格式之一：

Into <目标>

格式之二：

To File<文件名>[Additive] [To Print/To Screen]

说明：

（1）输出子句默认为Query（Browse）方式。

（2）Into <目标>，目标可以是Dbf <数据表名>、Table <表名>、Cursor<临时表名>、Array<数组名>之一。其中Dbf <数据表名>和Table <表名>都是表，创建后即被打开。Cursor是临时表，创建后即被打开，但不保存。

（3）To File<文件名>，可以将查询的结果输出到一个文本文件。选择[Additive]表示把查询结果追加到文本文件，否则，将覆盖To File <文件名>指定的文本文件中的内容。

（4）To Print，将查询输出结果送至打印机。

（5）To Screen，将查询输出结果送至屏幕。

【例6-15】在学生档案表（xsda.dbf）和学生成绩表（xscj.dbf）中，查询“年龄>20”的记录，并要求显示学号、姓名、专业、语文、数学、外语和政治，将结果输出到数据表文件xscj-1.dbf中。

Select A.学号,A.姓名,B.专业,B.语文,B.数学,B.外语,B.政治;

 From xsda A,xscj B Where A.编号=B.编号and a.年龄>20;

Desc Into Dbf xscj-1

Use xscj-1

Browse

查询的结果如图6-17所示。

图6-17  查询的结果

2．查询结果排序

SELECT的查询结果是按数据表的自然顺序给出的，因此查询的结果通常是无序的。如果需要使查询的结果有序排列，就要在SELECT命令中使用Order By <排序项目>选项，对查询的结果进行排序。使SELECT的查询结果按By<排序项目>指定的字段名和[Asc/Desc] 指定的升降序排列查询结果。其格式为：

Order By<排序项目> [Asc/Desc] [,<排序项目> [Asc/Desc]...]

说明：

（1）By<排序项目>必须是主SELECT<选项>的字段名或表达式。

（2）有多个排序项目时，用逗号“，”分隔。

（3）降序用Desc表示，升序用Asc表示，默认值为升序。

【例6-16】在学生档案表（xsda.dbf）和学生成绩表（xscj.dbf）中，查询“年龄>20”的记录，并要求显示学号、姓名、专业、语文、数学、外语和政治，按语文成绩的降序排列输出。

Select A.学号,A.姓名,B.专业,B.语文,B.数学,B.外语,B.政治  From xsda A,xscj B；

 Where A.编号=B.编号and a.年龄>20 Order By B.语文

查询的结果如图6-18所示。

图6-18  查询的结果