第七章结构化查询语言-SQL

第七章结构化查询语言—SQL

第一节SQL概述

第二节SQL定义功能

第三节SQL操纵功能约

第四节SQL查询功能1第一节SQL概述

SQL是结构化查询语言(StructuredQueryLanguage)的缩写。首先由美国ANSI(美国国家标准协会)提出，后被ISO采纳为国际标准。一、

SQL语言发展简史二、SQL语言的特点及分类是一种一体化的语言。它包括了数据定义、数据查询、数据操纵和数据控制等功能。数据定义

CREATE、ALTER、DROP等数据查询

SELECT

数据操纵

INSERT、UPTATE、DELETE等由于VFP在数据安全性方面的欠缺，不支持SQL的数据控制功能。(2)是一种高度过程化的语言。(3)非常简洁，但功能很强。(4)可以直接以命令方式交互使用，又可以嵌入程序设计语言中以程序方式使用。

返回2第二节SQL的定义功能

一、定义表的结构格式：

CREATETABLE<表名>(字段属性描述)

该命令直接创建指定名称的表的结构，各字段的名称、类型、宽度等与括号中的描述相同。如果此时有打开的数据库，则建立的是数据库表，否则是自由表。【例7.1】用SQL命令创建仓库表（CKB.DBF）的结构，要求如下。

CREATETABLECkB(CKHC(6),CKMC(10),CHFL,MJN(3,0))

返回字段名数据类型宽度小数位备注CKH字符型6仓库号CKM字符型10仓库名CHF逻辑型存货否MJ数值型30面积3

二、修改表的结构

用ALTERTABLE命令可以修改表的结构并且可以指定数据的完整性控制信息。该命令包括四种具体格式，分别可以完成不同的修改操作。（1）追加新字段

ALTER

TABLE<表名>ADD<字段名及属性信息>（2）修改原字段的属性

ALTER

TABLE<表名>ALTER<字段名及属性信息>（3）删除字段

ALTER

TABLE<表名>DROP<字段名>（4）字段更名

ALTER

TABLE<表名>RENAME<原字段名>TO<新字段名>【例7.2】为CKB表添加一个管理员（GLY）字段，类型为字符型，宽度位为8。ALTERTABLECKBADDGLYC(8)返回4【例7.3】将CKB表的管理员（GLY）字段的宽度修改为10。ALTERTABLECKBALTERGLYC(10)【例7.4】将CKB表的管理员（GLY）字段名更改为“负责人（FZR）”。ALTERTABLECKBRENAMEGLYTOFZR三、表的删除

格式：DROPTABLE<表名>说明：直接从磁盘上删除指定的表。若是数据库表，则必须先打开数据库。

【例7.5】删除CKB表DROPTABLECKB返回5主要包括数据的插入（INSERT）、删除（DELETE）、更新（UPDATE）三种功能。一、数据插入SQL的INSERT命令用于向指定的表中追加一条记录，无论该表是否已经打开，也不必从键盘输入数据，而是直接把数据写在命令中。该命令动词是INSERT，因此也叫“插入命令”。格式：

Insertinto<表名>[(字段1，字段2，…)];Values(表达式1，表达式2，…)

若只插入部分字段，必须在表名后用括号依次指明所插入的字段名，且与VALUES后括号中表达式的值一一对应。当插入整条记录时，则不必指出字段名，但VALUES后括号中表达式的值，必须依次为表内所有字段的值。返回第三节SQL的操纵功能6【例7.7】向XSQK表插入一条记录，学号、姓名、班级分别是“0801100204”，“王一”和“流体力学”这三个值。

INSERTINTOXSQK(xh,xm,bj)VALUES("0801100204","王一","流体力学")【例7.8】向CJ表插入一条记录，“xh”、“kcdm”、“cj”分别为“0801100204”、“150104”、86。

INSERTINTOCJVALUES("0801100204"，"150104",86)

可以“浏览”方式查看插入结果。

返回7二、删除数据格式：

DELETEFROM<表名>[WHERE<条件>]

功能：对指定表中满足条件的记录做删除标记。如果不指定条件，则对所有记录添加删除标记。说明：此命令无须事先打开表，注意条件子句用WHERE。【例7.9】将XSQK表中年龄大于25岁的女生进行逻辑删除。解：

DELETEFROMXSQK;WHERE(YEAR(DATE())-YEAR(CSRQ)>25)ANDXB='女'返回8三、更新命令格式：

UPDATE<表名>SET字段名1=<表达式1>[,字段名2=<表达式2>…][WHERE<条件>]

功能：对指定表中满足条件的记录，用指定表达式的值更新指定字段的值。说明：如果不指定条件,该命令默认的范围为所有记录。【例7.10】将XSQK表中所有学生的入学成绩都提高5分。UPDATEXSQKSETRXCJ=RXCJ+5【例7.11】将XSQK表中1990年及其以后出生的所有学生的入学成绩都提高10分。UPDATEXSQKSETRXCJ=RXCJ+10；WHEREYEAR(CSRQ)>=1990四、SQL与VFP命令对比1、VFP需先打开表，SQL不用。2、SQL中要出现表名，VFP除USE和SETRELATION外都不出表名。返回9

查询功能是SQL的核心，SQL的查询命令又称为SELECT语句。一、SELECT命令的格式SELECT[ALL│DISTINCT][TOPn[PERCENT]];[别名.]<字段１>[AS]<别名>[,[别名2]<字段２>[AS]<别名>]>…];FROM[数据库名!]<表名１>[[AS]<别名>][,[数据库名2！]<表名２…];[[INNER│LEFT[OUTER]│RIGHT[OUTER]│FULL[OUTER]JOIN<数据库名>!]<表名>[WHERE<筛选条件>|<联接条件>];[GROUPBY<分组字段1>[，<分组字段2>…]]［HAVING<分组条件>］];[ORDERBY<排序字段1>[ASC|DESC][<排序字段2>[ASC|DESC]…]];[INTOTABLE<表名>]│[INTOCURSOR<表名>]|[INTOARRAY<数组名>][TOFILE<文件名>]

这条命令看起来很复杂，但其基本形式由SELECT-FROM模块构成，其余多为可选项，要根据查询的需求去选用。第四节SQL的数据查询返回10二、基本查询格式：SELECT[ALL|DISTINCT]<字段列表>FROM<表名>1、查询指定字段【例7.12】

从学生情况表（XSQK）中查询所有学生的学号（XH）、姓名（XM）、班级（BJ）和入学成绩（RXCJ）信息。SELECTXH,XM,BJ,RXCJFROMXSQK

【例7.13】查询成绩表(CJ)中的所有学生成绩信息。SELECT*FROMCJ&&“*”为通配符，表示所有字段。该命令等价于：SELECTXH,KCDM,CJFROMCJ

2、查询经过计算的列

【例7.14】查询学生情况表（XSQK）中所有学生的学号（XH）、姓名（XM）和年龄。SELECTXH,XM,YEAR(DATE())-YEAR(CSRQ)AS年龄FROMXSQK3、去掉重复值【例7.15】查询学生情况表（XSQK）中所有学生的班级（BJ），要求去掉重复信息。SELECTDISTINCTBJFROMXSQK返回11三、条件查询

在SELECT语句中，查询条件用WHERE子句来描述。主要有以下几种情况：1．比较大小用于比较的运算符包括：=，>，<，>=，<=，#、!=或<>。【例7.16】查询学生情况表(XSQK)中所有男生的学号(XH)、姓名(XM)和性别(XB)。SELECTXH,XM,XBFROMXSQKWHERExb="男"【例7.17】查询学生情况表(XSQK)中入学成绩(RXCJ)多于565分（含）的学生的学号(XH)、姓名(XM)和入学成绩(RXCJ)。SELECTXH,XM,RXCJFROMXSQKWHERERXCJ>=5652．多重条件使用逻辑运算符AND、NOT和OR可以进行复合条件查询。【例7.18】查询学生情况表(XSQK)中所有男生且入学成绩(RXCJ)高于500分的学生的学号(XH)、姓名(XM)和入学成绩(RXCJ)。SELECTXH,XM,RXCJFROMXSQKWHEREXB="男"ANDRXCJ>500返回12【例7.19】查询学生情况表(XSQK)中金融091班和政法092班的所有学生信息。SELECT*FROMXSQKWHEREBJ="金融091"ORBJ="政法092"3.确定范围BETWEEN…AND…和NOTBETWEEN…AND…分别用来描述宇段值在或不在指定范围的条件。其中，AND的左端给出查询范围的下限，AND的右端给出的是查询范围的上限。【例7.20】查询学生情况表(XSQK)中入学成绩(RXCJ)在550到600分之间的学生的学号(XH)、姓名(XM)和入学成绩(RXCJ)。SELECTXH,XM,RXCJFROMXSQKWHERERXCJBETWEEN550AND6004．确定集合这里所说的集合是相同类型的常量所组成的集合。谓词IN用来描述字段的值属于指定的集合，NOTIN则描述字段值不属于指定的集合。【例7.21】查询学生情况表(XSQK)中金融091班和政法092班的所有学生信息。SELECT*FROMXSQKWHEREBJIN("金融091","政法092")返回135．部分匹配查询LIKE是字符匹配运算符，进行匹配运算时可以使用通配符“％”和下划线“_”，其中，“％”表示0个或多个任意字符，“_”表示1个任意字符。例如，第2个字符为B的字符串可以表示为“_B％”。LIKE的格式为：<字符型字段名>LIKE<含通配符的字符串常量>【例7.22】查询XSQK表中所有姓“张”的学号（XH）和姓名（XM）。SELECTXH,XMFROMXSQKWHEREXMLIKE"张%"四、统计查询SQL提供了许多库函数，可以进一步增强检索功能，这些函数主要有：COUNT(列名|*)按列统计记录个数SUM(<列名>)计算一列值的总和AVG(<列名>)计算一列值的平均值MAX(<列名>)求一列值中的最大值MIN(<列名>)求一列值中的最小值返回14如果指定DISTINCT，则表示在计算时要取消指定行或列中的重复值。使用SUM，AVG，MAX，MIN函数时，指定列必须是数值型。另外，在标准SQL中只能在SELECT子句和HAVING短语中使用库函数。【例7.23】查询成绩表（CJ）中学号为“0901100103”的学生的学号（XH），总分和平均分。SELECTXH,SUM(CJ)AS总分,AVG(CJ)AS平均分FROMCJ;WHEREXH="0901100103"【例7.24】查询成绩表（CJ）中课程代码（KCDM）为“150205”的课程代码，最高分和最低分。SELECTKCDM,MAX(CJ)AS最高分,MIN(CJ)AS最低分;FROMCJWHEREKCDM="150205"【例7.25】统计成绩表（CJ）中有多少门课。SELECTCOUNT(DISTINCTKCDM)AS课程数FROMCJ【例7.26】统计XSQK表中学生人数。SELECTCOUNT(*)AS学生人数FROMXSQK

返回15五、分组查询

GROUPBY子句可以将查询结果按一列或多列值分组，列值相等的为一组，还可以利用HAVING短语按一定的条件对分组后的数据进行筛选。GROUPBY子句一般应跟在WHERE子句之后，没有WHERE子句时，跟在FROM子句后。如果对分组有要求的话，则可以用HAVING短语指定筛选条件。该短语必须与GROUPBY配合使用。需要强调的是，HAVING短语与WHERE子句之间并不矛盾，WHERE子句用于表的选择运算，HAVING短语用于设置分组的筛选条件，即满足该条件的分组数据才被输出。【例7.27】查询成绩表（CJ）中每门课程的课程代码（KCDM）、最高分和最低分。SELECTKCDM,MAX(CJ)AS最高分,MIN(CJ)AS最低分;FROMCJGROUPBYKCDM【例7.28】查询成绩表（CJ）中每名学生的学号（XH）、最高分和最低分。SELECTXH,MAX(CJ)AS最高分,MIN(CJ)AS最低分FROMCJ;GROUPBYXH返回16【例7.29】统计输出班级人数超过18（含）人的各班级的入学成绩平均分。SELECTBJ,AVG(RXCJ)AS平均分FROMXSQK;GROUPBYBJHAVINGCOUNT(*)>=18【例7.30】在成绩表（CJ）里查询选修了“150104”和“150205”的学生平均成绩在80分及以上的学生的学号和平均成绩。SELECTXH,KCDM,AVG(CJ)AS平均成绩FROMCJ;WHEREKCDMIN("150104","150205")GROUPBYXH;HAVINGAVG(CJ)>=80六、查询的排序利用ORDERBY子句可以对查询的结果按指定字段进行排序。其中，ASC表示升序，DESC表示降序，缺省时表示升序。另外，利用该子句还可以实现多重排序。【例7.31】查询学生情况表(XSQK)中的学生信息，要求按入学成绩由高到低顺序排序。SELECT*FROMXSQKORDERBYRXCJDESC返回17【例7.32】查询学生情况表(XSQK)中的学生学号（XH）、姓名（XM）、班级（BJ）和入学成绩（RXCJ）。要求查询结果按班级升序排列，班级相同再按入学成绩降序排列。SELECTXH,XM,BJ,RXCJFROMXSQKORDERBYBJ,RXCJDESC【例7.33】统计输出班级人数超过18（含）人的各班级的入学成绩平均分,并按平均分降序排列。SELECTBJ,AVG(RXCJ)AS平均分FROMXSQK;GROUPBYBJHAVINGCOUNT(*)>=18ORDERBY2DESC如果用户需要输出满足条件的前几个记录，可以使用以下短语：

TOP<数值表达式>[PERCENT]

其中，<数值表达式>表示要显示前几个记录的数目。若使用PERCENT，则<数值表达式>应取0.01至99.99间的实数，表示要显示所有查询结果中的前百分之几的记录。注意，TOP短语要与ORDERBY短语同时使用才有效。【例7.34】查询入学成绩最高的三位同学的姓名和入学成绩。SELECTTOP3XM,RXCJFROMXSQKORDERBYRXCJDESC返回18【例7.35】查询入学成绩最低的30%同学的姓名和入学成绩。SELECTTOP30PERCENTXM,RXCJFROMXSQK;ORDERBYRXCJDESC七、利用空值查询在SQL中可以查询某字段取值为空(NULL)的记录，空值不同于零和空格，它不占任何存储空间，中是一个特殊的符号“NULL”。输入NULL的方法是复合键CTRL+0（零）。【例7.36】查询课程表（KCB）中没有课程名称的记录信息。SELECT*FROMKCBWHEREKCMCISNULL注：查询空值时要使用“ISNULL”，而“=NULL”是无效的，因为空值不是一个确定的值。八、连接查询如果查询涉及两个或两个以上的表，则需要将相关的表按某个字段连接起来，这种查询称为连接查询（多表查询）。返回191、等值连接查询（1）2个表查询条件格式

表名1．公共字段名=表名2．公共字段名其中，引用不同表中的字段需在字段名前加表名和“.”。【例7.37a】查询“赵刚”同学的学号、姓名，性别和成绩。SELECTXSQK.XH,XM,XB,CJFROMXSQK,CJ;WHEREXSQK.XH=CJ.XHANDXM="赵刚"（2）3个表查询查询条件格式表名1．公共字段名1=表名2．公共字段名1AND表名2．公共字段名2=表名3．公共字段名2【例7.37b】查询“赵刚”同学的学号、姓名，性别、选修的课程名称和成绩。SELECTXSQK.XH,XM,XB,KCMC,CJ;FROMXSQK,CJ,KCB;WHERECJ.KCDM=KCB.KCDMANDXSQK.XH=CJ.XH;ANDXM="赵刚"返回202、超连接查询超连接查询包括内部连接、左连接、右连接和全连接等几种情况的查询。这里只介绍内连接。格式如下：（1）2个表查询条件格式FROM表名1[INNER]JOIN表名2ON表名1.公共字段名=表名2.公共字段名可以看出，超连接查询是通过FROM短语给出连接类型，用ON短语给出连接条件进行的。其中INNER可以省略。【例7.37c】查询“赵刚”同学的学号、姓名，性别和成绩。SELECTXSQK.XH,XM,XB,CJFROMXSQKJOINCJ;ONXSQK.XH=CJ.XHWHEREXM="赵刚"（2）3个表查询条件格式FROM表名1

JOIN表名2JION表名3ON表名2.公共字段名=表名3.公共字段名ON表名1.公共字段名=表名2.公共字段名返回21【例7.37d】查询“赵刚”同学的学号、姓名，性别、选修的课程名称和成绩。SELECTXSQK.XH,XM,XB,KCMC,CJ;FROMXSQKJOINCJJOINKCB;ONCJ.KCDM=KCB.KCDMONXSQK.XH=CJ.XH;WHEREXM="赵刚"九、嵌套查询所谓嵌套查询，是指在WHERE子句条件中涉及字段与另一个SELECT查询结果的比较，也就是说，嵌套查询是指在一个SELECT语句中包含另一个SELECT语句的查询。SQL支持嵌套查询，这正是SQL结构化的具体体现。

处于内层的查询称为子查询，处于外层的查询称为父查询。在子查询的SELECT语句不能使用ORDERBY子句，即ORDERBY子句只能对最终查询结果排序。1、返回一个值的子查询当子查询的返回值只有一个时，可以使用>，<，>=，<=，!=或<>等比较运算符将父查询和子查询连接起来。返回22【例7.38】从成绩表中查询“赵刚”同学的考试成绩信息，要求显示CJ表的所有字段。SELECT*FROMCJ;WHEREXH=(SELECTXHFROMXSQKWHEREXM="赵刚")2、返回一组值的子查询如果子查询的返回值不止一个，而是一个集合时，则不能直接使用比较运算符，可以在运算符和子查询之间插入ANY、SOME、ALL。WHERE子句中的条件书写格式为：

<字段名><关系比较运算符>[ANY|SOME|ALL](子查询)其中，ANY和SOME是作用相同的谓词。ANY代表某个值，ALL代表所有值。从上述格式可以看出，使用谓词ANY或ALL时必须同时使用比较运算符。例如，>ANY的语义为：大于子查询结果中的某个值。>ALL的语义为：大于子查询结果中的所有值。由于子查询的结果是记录的集合，故也可使用谓词IN来实现。返回23【例7.39】查询选修了课程代码（KCDM）为“150204”的学生的姓名（XM）。SELECTXMFROMXSQKWHEREXH=ANY;(SELECTXHFROMCJWHEREKCDM="150205")也可以写成：SELECTXMFROMXSQKWHEREXHIN;(SELECTXHFROMCJWHEREKCDM="150205")【例7.40】查询高于男生入学成绩最高分的女生的姓名和入学成绩。SELECTXM,RXCJFROMXSQKWHERERXCJ>ALL;(SELECTRXCJFROMXSQKWHEREXB="男")ANDXB="女"也可以写成：SELECTXM,RXCJFROMXSQKWHERERXCJ>;(SELECTMAX(RXCJ)FROMXSQKWHEREXB="男")ANDXB="女"返回24EXISTS也称为存在量词，WHERE子句中使用量词EXISTS表示当子查询的结果非空时，条件为真：反之，则为假。EXISTS前也可以加NOT，表示检测条件为“不存在”。【例7.41】查询选修了课程代码（KCDM）为“140101”的学生的姓名（XM）。SELECTXMFROMXSQKWHEREEXISTS;(SELECT*FROMCJWHEREXSQK.XH=CJ.XHANDKCDM="140101")十、查询结果输出1、将查询结果存入表中

若