第六章SQL语言的应用3

第六章SQL语言的应用章目录6.1SQL语言概述6.2数据定义6.3数据查询6.4数据操纵

SQL被作为关系数据库的标准语言。SQL语句可以用来执行各种各样的操作。目前流行的关系数据库管理系统，如Qracle、Sybase、SQLServer、VisualFoxPro等都采用了SQL语言标准，而且很多数据库都对SQL语句进行了再开发和扩展。

6.1SQL语言概述SQL语言具有如下特点：（1）SQL是一种一体化的语言。尽管设计SQL的最初目的是查询，数据查询也是其最重要的功能之一，但SQL决不仅仅是一个查询工具，它集数据定义、数据查询、数据操纵和数据控制功能于一体，可以独立完成数据库的全部操作。（2）SQL语言是一种高度非过程化的语言。它没有必要一步步地告诉计算机“如何”去做，而只需要描述清楚用户要“做什么”，SQL语言就可以将要求交给系统，自动完成全部工作。（3）SQL语言非常简洁。虽然SQL语言功能很强，但它只有为数不多的9条命令：CREATE、DROP、ALTER、SELECT、INSERT、UPDATE、DELETE、GRANT、REVOKE。另外SQL的语法也非常简单，它很接近英语自然语言，因此容易学习和掌握。

（4）SQL语言可以直接以命令方式交互使用，也可以嵌入到程序设计语言中以程序方式使用。现在很多数据库应用开发工具都将SQL语言直接融入到自身的语言之中，使用起来更方便，VisualFoxPro就是如此。这些使用方式为用户提供了灵活的选择余地。此外，尽管SQL的使用方式不同，但SQL语言的语法基本是一致的。

VisualFoxPro在SQL方面支持数据定义、数据查询和数据操纵功能，但在具体实现方面也存在一些差异。另外，由于VisualFoxPro自身在安全控制方面的缺陷，所以它没有提供数据控制功能。

SQL虽然在各种数据库产品中得到了广泛的支持，但迄今为止，它只是一种建议标准，各种数据库产品中所实现的SQL在语法、功能等方面均略有差异，本章讲述VisualFoxPro中SQL的语法、功能与应用。6.2.1建立表结构命令格式是：CREATETABLE|DBF

<表名1>

[NAME<长表名>][FREE](<字段名1><类型>(<宽度>[,<小数位数>])[NULL|NOTNULL][CHECK<条件表达式1>[ERROR<出错显示信息>]][DEFAULT<表达式1>][PRIMARYKEY|UNIQUE][REFERENCES<表名2>[TAG<标识1>]][<字段名2>……][,PRIMARYKEY

主索引表达式TAG

索引标识][,UNIQUE

候选索引表达式TAG索引标识][,FOREIGNKEY

普通索引表达式TAG索引标识

REFERENCES

父表名[TAG

索引标识]][,CHECK

＜记录有效性＞[ERROR<出错信息>]]）)6.2数据定义例利用SQL命令建立学生管理数据库，其中包含3个表：学生表、选课表和课程表。操作步骤如下：（1）用CREATE命令建立数据库。CREATEDATABASED:\学生管理

（2）用CREATE命令建立学生表。CREATETABLE学生;(学号C(5)PRIMARYKEY,;

姓名C(8),;

入学成绩N(5,1)CHECK(入学成绩>0)ERROR"成绩应该大于0")（3）建立课程表。CREATETABLE课程;(课程号C(5)PRIMARYKEY,;

课程名C(20),;

学分N(1))4）建立选课表。CREATETABLE选课;(学号C(5)REFERENCES学生TAG学号,;

课程号C(5)REFERENCES课程TAG课程号,;

成绩ICHECK(成绩>=0AND成绩<=100);ERROR"成绩值的范围0~100!"DEFAULT60,;PRIMARYKEY学号+课程号TAGxhkxh)利用SQL命令建立数据库6.2.2删除表删除表的SQL命令是：DROPTABLE

<表名>DROPTABLE命令直接从磁盘上删除所指定的表文件。如果指定的表文件是数据库中的表并且相应的数据库是当前数据库，则从数据库中删除了表。否则虽然从磁盘上删除了表文件，但是记录在数据库文件中的信息却没有删除，此后会出现错误提示。所以要删除数据库中的表时，最好应使数据库是当前打开的数据库，在数据库中进行操作。

6.2.3修改表结构修改表结构的命令是ALTERTABLE，该命令有3种格式。略

6.3.1基本查询所谓基本查询是指无条件查询，其格式是：SELECT[<别名>.]<选项>[AS<显示列名>],[…]FROM<表名1>[AS

别名1>][,…]其中:<别名>.

可使用别名来区分不同的表。<选项>显示字段或表达式，“＊”表示所有表字段<显示列名>如果不希望使用字段名，可以根据要求设置一个名称。

6.3数据查询建立基本表：学生(学号，姓名，年龄，性别，所在系)；

课程(课程号，课程名，学分)；选课(学号，课程号，成绩)例：查询所有学生信息：

SELECT*FROM学生

或

SELECT学生.*FROM

学生

或

SELECTXS.*

/*不能用学生.**/

FROM学生XS例：查询所有学生姓名和年龄：

SELECT姓名,年龄

FROM学生例：查询每人的虚岁

SELECT

学号,姓名,年龄+1

AS

年龄

/*不用AS年龄,将无列名*/

FROM

学生6.3.2带条件查询

SELECT<选项>[，…]FROM<表名>[,…]WHERE<筛选条件>（1）<字段名><关系运算符><表达式>（2）<字段名>[NOT]BETWEEN<起始值>AND<终止值>（3）<字段名>

IS[NOT]NULL（4）<字段名>[NOT]IN<值表>（5）<字段名>[NOT]LIKE<字符表达式>（6）<字段名1><关系运算符><字段名2>（联接）（7）<字段名>[NOT]IN

（<子查询>）（以下几种嵌套）（8）<字段名><关系运算符>

（<子查询>）（9）<字段名><关系运算符>ALL（<子查询>）（10）<字段名><关系运算符>ANY|SOME

（<子查询>）（11）[NOT]EXISTS

（<子查询>）SQL支持的关系运算符如下：＝、＜＞、！＝、＃、＝＝、＞、＞＝、＜、＜＝。（1）<字段名><关系运算符><表达式>例：查询所有女生信息

SELECT*FROM学生

WHERE性别=‘女’（2）<字段名>[NOT]BETWEEN<起始值>AND<终止值>例：查询所有年龄在20－25之间的学生信息

SELECT*FROM学生

WHERE年龄BETWEEN20AND25或SELECT*FROM学生

WHERE年龄>=20AND年龄<=25（3）<字段名>

IS[NOT]NULL例：查询所有性别没有输入的学生信息

SELECT*FROM学生

WHERE性别ISNULL6.3.3嵌套查询

有时候一个SELECT命令无法完成查询任务，需要一个子SELECT的结果作为条件语句的条件，即需要在一个SELECT命令的WHERE子句中出现另一个SELECT命令，这种查询称为嵌套查询。通常把仅嵌入一层子查询的SELECT命令称为单层嵌套查询，把嵌入子查询多于一层的查询称为多层嵌套查询。VisualFoxPro只支持单层嵌套查询。

①<字段名>[NOT]IN

（<子查询>）

:测试当前值是否是子查询结果集中的元素，结果集中可以是空集、一个元素或多个元素【例】求至少选修了一门课程的学生学号和姓名。

SELECT学号,姓名

FROM学生

WHERE学号

IN

(SELECT学号FROM选课)【例】求一门课程都没选修的学生学号和姓名。

SELECT学号,姓名

FROM学生

WHERE学号

NOTIN

(SELECT学号FROM选课)

②

<字段名><关系运算符>

（<子查询>）：当前值与子查询结果集中的元素的比较，结果集中最多只一个元素，不能含有多个元素。【例】求年龄大于张三的学生学号、姓名和年龄。

SELECT学号,姓名,年龄

FROM学生

WHERE年龄>(

SELECT年龄

FROM学生

WHERE姓名='张三')③

<字段名><关系运算符>ALL|ANY|SOME（<子查询>）

ALL是量词，要求查询字段要满足子查询的所有元素。ANY、SOME是量词，含义相同，只要求查询字段满足子查询集合中一个元素，则条件成立【例】求其他系中比数学系某一学生年龄小的学生（不大于所有的）。

SELECT*FROM学生

WHERE年龄

<ANY(SELECT年龄

FROM学生

WHERE所在系='数学系')

AND所在系<>'数学系'【例】求其他系中比数学系学生年龄都小的学生。

SELECT*FROM学生

WHERE年龄

<ALL(SELECT年龄

FROM学生

WHERE所在系=‘数学系’)

AND所在系<>‘数学系’④[NOT]EXISTS

（<子查询>）

【例7】求选修了KC02课程的学生姓名。

SELECT姓名FROM学生

WHERE

EXISTS(SELECT*FROM选课

WHERE学生.学号=学号

AND课程号=‘KC02’)/*此处内层使用了外层的信息*/此题可用IN表示嵌套，也可用连接来代替SELECT姓名FROM学生;WHERE

学号IN(SELECT学号;FROM选课WHERE课程号="KC02")【例】求没有选修KC02课程的学生姓名。

SELECT姓名

FROM学生

WHERE

NOTEXISTS

(SELECT*FROM选课

WHERE学生.学号=学号

AND课程号=‘KC02’)/*此处内层使用了外层的信息*/此题可用IN表示嵌套，也可用连接来代替SELECT姓名FROM学生;WHERE

学号NOTIN(SELECT学号;FROM选课WHERE课程号="KC02")6.3.4多表查询在一个表中进行查询，一般说来是比较简单的，而在多表之间查询就比较复杂，必须处理表和表间的联接关系。使用SELECT命令进行多表查询是很方便的。1.在WHERE子句表示

SELECT<字段列表|表达式>FROM<表1>[，表2…..]WHERE<连接条件表达式>（1）<条件表达式>：形式如：

<字段名1><关系运算符><字段名2>（2）这是种普通联接查询，又你内联接查询，只有满足条件的记录才包含在结果里，否则不会出现在结果里，一般是等值联接例：查询所有选了课的学生信息及选课情况SELECT*FROM学生INNERJOIN

选课ON

学生.学号=选课.学号例：查询数学系所有学生信息及选课情况

SELECT*FROM学生LEFTJOIN

选课ON

学生.学号=选课.学号WHERE所在系=‘数学系’例：查询所有课程的先修情况情况

SELECT*FROM选课RIGHTJOIN

课程ON

选课.课程号=课程.课程号6.3.5排序格式：SELECT<字段列表>FROM<表>[WHERE<条件>］

[ORDERBY<排序项>[ASC|DESC］，…]

【例】按学号的顺序查询学生的成绩SELECT学号,成绩FROM选课

ORDERBY学号【例】按年龄降序查询各系学生的学号、姓名、年龄SELECT学号,姓名,年龄,所在系FROM学生ORDERBY所在系,年龄DESC1.简单的计算查询

SQL不仅具有查询功能，还有计算功能，用于计算的函数有：（1）COUNT(*)：计数（2）SUM(字段名|表达式)：求和（3）AVG(字段名|表达式)

：计算平均数（4）MAX(字段名|表达式)

：求最大值（5）MIN(字段名|表达式)

：求最小值【例】求选了课的学生人数。

SELECTCOUNT(DISTINCT学号)

FROM选课【例】求学生平均年龄。

SELECT

AVG(年龄)

FROM学生6.3.6分组与计算查询例：查询张三的总分SELECT学号，SUM(成绩)总分FROM选课WHERE学号=(SELECT学号

FROM学生

WHERE姓名='张三')【例】求数据库课程的最高分学生的学号和成绩。

SELECT学号,MAX(成绩)FROM选课;WHERE课程号=(SELECT课程号;FROM课程;WHERE课程名='数据库')2.分组与计算查询

SELECT<字段列表|函数>FROM<表>

[WHERE<条件>]

[GROUPBY<分类字段列表>,…][HAVING<过滤条件>]【功能】GROUPBY<分类字段列表>是分组计算查询。【例】求每个院系的学生平均年龄。SELECT所在系,AVG(年龄)

FROM学生;GROUPBY所在系说明：(1)HAVING子句总是跟在GROUPBY子句之后，不能单独使用，它是用来对分组限定的【例】求至少有2个学生的学生人数。SELECT所在系,COUNT(*)FROM学生;GROUPBY所在系

HAVINGCOUNT(*)>=2(2)计算函数只能用在SELECT短语中或分组的HAVING短语中，不能用在WHERE短语和ORDERBY短语中:【例】求每个仓库的职工平均工资,并按从高到低排列。SELECT所在系,AVG(年龄)ASNL

FROM学生;GROUPBY年在系;ORDERBYNLDESC&&不可用ORDERBYAVG(年龄)6.3.7集合的并运算格式：SELECT…FROM…WHERE…UNION；

SELECT…FROM…WHERE…功能：将两个查询结果通过并运算合并成一个查询【例】查询“数学系”和“化学系”的学生信息。SELECT*FROM学生WHERE所在系="数学系"

UNION;SELECT*FROM学生WHERE所在系="化学系"

它等价于:SELECT*FROM学生;WHERE

所在系="数学系"OR所在系="化学系"说明:

参加并运算的两个集合的类型应该相同,即字段数和对应字段的值域相同.1.显示查询的部分结果【格式】:SELECT[ALL|DISTINCT]<字段列表>[TOPn[PERCENT]]FROM<表>ORDERBY<列名>ALL：

表示显示全部查询记录，包括重复记录，默认是指ALL。DISTINCT：

表示显示无重复结果的记录。TOPn:表示显示结果的前n条记录,TOPnPERCENT表示显示结果的前n%的记录说明:TOP应与ORDERBY匹配使用6.3.8VFP中SELECT语句几个特殊选项例：查询所有选修了课程的学生学号SELECTALL学号

FROM选课SELECTDISTINCT学号

FROM选课例：查询选课表的“KC01”课程的最高分记录SELECTTOP1*FROM选课;ORDERBY成绩

DESCWHERE课程号="KC01"例：查询选课表的“KC01”课程成绩在前40%记录SELECTTOP40PERCENT*FROM选课;ORDERBY成绩DESCWHERE课程号="KC01"查询的动向一般有:INTOARRAY

<数组名>：将查询结果保存到一个数组中。INTOCURSOR<临时表名>：将查询结果保存到一个临时表中。INTODBF|TABLE

<表名>：将查询结果保存到一个永久表中。TOFILE<文件名>[ADDITIVE]：将查询结果保存到文本文件中。如果带“ADDITIVE”关键字，查询结果以追加方式添加到<文件名>指定的文件，否则，以新建或覆盖方式添加到<文件名>指定的文件。TOSCREEN：将查询结果保在屏幕上显示。TOPRINTER：将查询结果送打印机打印。【例】查询“1001”学生的选课信息(1)将结果输出到数组s中。SELECT*FROM选课WHERE学号="1001";INTOARRAYS1(2)将结果输出到临时表tmp中SELECT*FROM选课WHERE学号="1001";INTOCURSORtmp说明：生成的临时表文件自动打开，为只读文件，可象一般的表文件一样使用，关闭时自动删除(3)将结果输出到自由表temp中SELECT*FROM选课WHERE学号="1001";INTOdbftemp(4)将结果输出到文本文件temp中SELECT*FROM选课WHERE学号="1001";TO[FILE]temp(5)将结果输出到屏幕上SELECT*FROM选课WHERE学号="1001";TOSCREEN(6)将结果输出到打印机上SELECT*FROM选课WHERE学号="1001";TOPRINTER说明:查询语句即有TO子句,又有INTO子句,则TO被忽略6.4.1插入记录

【格式1】INSERTINTO<表名>[(<字段名表>)]

VALUES（<表达式表>）【功能】在指定的表文件末尾追加一条记录。格式1用表达式表中的各表达式值赋值给<字段名表>中的相应的各字段。【说明】若插入的是一条完整的记录（每个字段都有对应的值），则可省了<字段名表>，否则必须指定的字段，没有出现的字段，则取空值（或默认值）。6.4数据操纵【例】将一个新学生记录(学号：‘1004’，姓名：‘王小明’，年龄：20)插入到学生表中。

INSERTINTO

学生(学号,姓名,年龄);

VALUES(“2002”,“王小明”,20)

又如：

INSERTINTO学生;VALUES(“2003”,“王鹏”,20,“男”,“数学系”)

&&基本表只含五个属性，且顺序与值一致【格式2】INSE