江苏省计算机等级考试VFP第六章考点归纳

第七讲SQL语言考点1：数据查询SQL：StructuredQueryLanguage，结构化查询语言，包括数据查询、数据定义、数据操纵和数据控制4部分，但VFP不提供数据控制功能。SQL的核心是数据查询，基本形式由SELECT-FROM-WHERE查询块组成。SQL语句:是一个整体，一行写不完用；续行字段---select消除重复记录-àdistinct，全部记录-à*字段别名-àas重复字段--à表名.字段分组函数：Avg(),Count(),Min(),Max(),Sum()特殊函数：IIF前几、前百分比项-àTOPN/TOPNPERCENT字段，与ORDERBY配合使用分组计算函数:计算平均值avg(cj)as平均成绩计算总分:sum(cj)as总成绩求最高值:max(cj)as最高分求最小值:min(cj)as最低分求人数,门数,课程数:count(*)ócount(字段名)求不合格门数:sum(IIF(cj.cj,1,0))as不合格门数注意不要把count()和sum()相混淆.前者是求记录数,后者是求字段值加起来的总和.count(distinct字段名)：计数时，去掉结果中重复的记录，即结果中重复的记录只算一条。selectcount(xdh)fromxsselectcount(distinctxdh)fromxs表-----FROM连接查询：是一种基于多个关系的查询；格式 1：Select字段列表from表1,表2Where表1.字段名=表2.相同字段名字段列表中的某字段若为两表的公共字段，则需在该字段前加表名进行限定例1：计算刘明同学选修的所有课程的平均成绩，正确的SQL语句是

A．SELECT

AVG(成绩)

FROM选课WHERE姓名=”刘明”

B．SELECT

AVG(成绩)

FROM学生,选课WHERE姓名=”刘明”

C．SELECT

AVG(成绩)FROM学生,选课WHERE学生.姓名=”刘明”

D．SELECT

AVG(成绩)FROM学生,选课WHERE学生.学号=选课.学号AND姓名=”刘明”格式2：SELECT字段列表FROM表1INNER|LEFT|RIGHT|FULLJOIN表2INNER|LEFT|RIGHT|FULLJOIN表3ON表2.字段A=表3.字段AON表1.字段B=表2.字段BWHERE<筛选条件>例2：设学生表S(学号,姓名,性别,年龄),课程表C(课程号,课程名,学分)和学生选课表SC(学号,课程号,成绩),检索学号,姓名和学生所选课程名和成绩,正确的SQL命令是：

A．SELECT

学号，姓名，课程名，成绩

FROM

S，SC，CWHERE

S.学号

=SC.学号

AND

SC.学号=C.学号B．SELECT

学号，姓名，课程名，成绩FROM

（S

JOIN

SC

ON

S.学号=SC.学号）JOIN

C

ON

SC.课程号

=C.

课程号C．SELECT

S.

学号，姓名，课程名，成绩FROM

S

JOIN

SC

JOIN

C

ON

S.学号=SC.学号

ON

SC.课程号=C.

课程号D．SELECT

S.

学号，姓名，课程名，成绩FROM

S

JOIN

SC

JOIN

C

ON

SC.课程号=C.课程号

ON

S.学号=SC.

学号嵌套查询：所要求的结果出自一个关系，但关系的条件却涉及多个关系。其内层基本上也是一个SELECT-FROM-WHERE查询语句，可使用谓词IN或NOTIN来判断在外层的查询条件中是否包含内层查询的结果。格式：Select字段列表from表1where字段Ain(select字段Afrom表2where筛选条件)例1：Select*fromxswherexhin(selectxhfromcj)&&表示只显示选修过课程的XS信息例2：Select*fromxswherexhnotin(selectxhfromcj)&&显示没有选过课的学生信息。例3：显示没有签订任何订单的职员信息（职员号和姓名），正确的SQL语句是

A．SELECT

职员.职员号,姓名

FROM

职员

JOIN

订单ON

订单.职员号=职员.职员号

GROUP

BY

职员.职员号HAVING

COUNT(*)=0

B．SELECT

职员.职员号,姓名

FROM

职员

LEFT

JOIN

订单

ON

订单.职员号=职员.职员号

GROUP

BY

职员.职员号HAVING

COUNT(*)=0

C．SELECT

职员号,姓名

FROM

职员

WHERE

职员号

NOT

IN

(SELECT

职员号

FROM

订单)

D．SELECT

职员.职员号,姓名

FROM

职员

WHERE

职员.职员号

<>

(SELECT

订单.职员号

FROM

订单)

例4：设XDH为XS表中的一个字段（类型为字符型，宽度为2个字节），则下列条件表达式中，与条件表达式XDHNOTIN(“01”,”04”)功能等价的是：AXs.xdh!=”01”ANDXs.xdh!=”04”BXs.xdh!<>”01”ORXs.xdh!<>”04”C!(Xs.xdh>”01”ORXs.xdh>”04”)DXs.xdh=”01”ORXs.xdh=”04”筛选记录---WHEREWHERE与HAVING的区别：WHERE筛选原表记录，HAVING筛选结果记录，进一步限定分组的条件WHERE后只能是原始字段，不能是计算函数HAVING后一般使用别名和计算函数HAVING必须和GROUPBY同时使用，不能单独使用，必须跟在GROUPBY子句之后，与WHERE子句并不矛盾。筛选要满足的条件:针对普通字段的条件，分组要满足的条件:针对计算字段的条件例：条件Avg(cj)>90放在HAVING后面作为分组要满足的条件条件cj>90放在WHERE后面作为筛选条件几个特殊选项字段between值1and值2，等价于：字段值>=值1and字段值<=值2例：成绩notBetween20and100等价于：成绩<20or成绩>100字段LIKE通配表达式通配表达式中可使用以下2个通配符：%：表示任意0或多个字符_（下划线）：表示任意1个字符例：select…where姓名like“李%”select…where学号like“%2%”空值查询（isnull）例：查询有选课记录，但没有考试成绩的学生的学号和课程号，正确的SQL语句是ASELECT学号,课程号FROMSCWHERE成绩=””BSELECT学号,课程号FROMSCWHERE成绩=NULLCSELECT学号,课程号FROMSCWHERE成绩ISNULLDSELECT学号,课程号FROMSCWHERE成绩查询尚未确定供应商的订购单的信息：Select*from订购单where供应商号isnull查询已经确定了供应商的订购单的信息：Select*from订购单where供应商号isnotnull例：如下命令查询雇员表中“部门号“字段为空值的记录：SELECT*FROM雇员WHERE部门号ISNULL

。使用谓词查询：格式：Select字段列表1from表1where[not]exists(Select字段列表2from表2where表1.字段A=表2.字段A)EXISTS或NOTEXISTS是用来检查在子查询中是否有结果返回，即存在元组或不存在元组。其本身并没有进行任何运算或比较，只用来返回子查询结果。例1：设有s(学号,姓名,性别)和sc(学号,课程号,成绩)两个表,如下SQL语句检索选修的每门课程的成绩都高于或等于85分的学生的学号、姓名和性别,正确的是：A．SELECT学号,姓名,性别FROMsWHEREEXISTS(SELECT*FROMSCWHERESC.学号=s.学号AND成绩<=85)B．SELECT学号,姓名,性别FROMSWHERENOTEXISTS(SELECT*FROMSCWHERESC.学号=s.学号AND成绩<=85)C．SELECT学号,姓名,性别FROMSWHEREEXISTS(SELECT*FROMSCWHERESC.学号=S.学号AND成绩>85)D．SELECT学号,姓名,性别FROMSWHERENOTEXISTS(SELECT*FROMSCWHERESC.学号=S.学号AND成绩<85)例2：查询选修C2课程号的学生姓名，下列SQL语句错误的是：ASELECT姓名FROMSWHEREEXISTS(SELECT*FROMSCWHERE学号=S.学号AND课程号=’C2’)BSELECT姓名FROMSWHERE学号IN(SELECT学号FROMSCWHERE课程号=’C2’)CSELECT姓名FROMSJOINSCONS.学号=SC.学号WHERE课程号=’C2’DSELECT姓名FROMSWHERE学号=(SELECT学号FROMSCWHERE课程号=’C2’)使用量词查询where字段A<比较运算符>[ANYISOME|ALL](子查询)ANY或SOME在进行比较运算时，只要子查询中有一行能使结果为真，结果就为真，往往对应于MIN()；ALL则要求子查询中所有行都使结果为真时，结果才为真，往往对应于MAX()。例1：Select…..from…..where工资＞＝all(select工资from……)意义：all后面筛选是一个范围：因此：工资＞＝all,就是＞＝这个范围里的所有工资，反过来，意思就是：比这个范围里的最高值要大.即：select…..from…where工资＞＝（selectmax(工资）from….)例2：Select….from….whereshengao>=any(selectshengao…from..)等价于：select…from…whereshengao>=(selectmin(shengao)….from…)例3：查询选修课程号为”101”课程得分最高的同学，正确的SQL语句是：A．SELECT学生.学号,姓名FROM学生,选课WHERE学生.学号=选课.学号AND课程号=”101”AND成绩>=ALL(SELECT成绩FROM选课)B．SELECT学生.学号,姓名FROM学生,选课WHERE学生.学号=选课.学号AND成绩>=ALL(SELECT成绩FROM选课WHERE课程号=”101”)C．SELECT学生.学号,姓名FROM学生,选课WHERE学生.学号=选课.学号AND成绩>=ANY(SELECT成绩FROM选课WHERE课程号=”101”)D．SELECT学生.学号,姓名FROM学生,选课WHERE学生.学号=选课.学号AND课程号=”101”AND成绩>=ALL(SELECT成绩FROM选课WHERE课程号=”101”)例4：有SQL语句：SELECT

DISTINCT系号FROM教师WHERE工资>=;

ALL

(SELECT

工资FROM教师WHERE系号=“02”)

与如上语句等价的SQL语句是

：A．SELECT

DISTINCT系号FROM教师WHERE工资>=(SELECT

MAX(工资)FROM教师WHERE

系号="02")

B．SELECT

DISTINCT系号FROM教师WHERE工资>=(SELECT

MIN(工资)FROM教师WHERE

系号="02")

C．SELECT

DISTINCT系号FROM教师WHERE工资>=(ANY(SELECT(工资)FROM教师WHERE

系号="02")

D．SELECT

DISTINCT系号FROM教师WHERE工资>=(SOME

(SELECT(工资)FROM教师WHERE

系号="02")查询去向—INTO/TO将查询结果存放到数组中:INTOARRAY数组名将查询结果存到临时表：INTOCURSOR表名查询结束后该表为当前表将查询结果存到永久表：INTODBF|TABLE表名将查询结果存到文本文件中：tofile文本文件名additive表示追加在原文件后，否则覆盖原文件将查询结果直接输出到打印机：toprinter将查询结果直接输出到屏幕：toscreen分组---GROUPBY常规表字段，可以对一个字段或多个字段分组包含SQL字段函数的字段名数值，最左边列为1与分组函数配合使用排序---ORDERBY必须是查询结果字段在子查询中，必须是外层SELECT语句中的字段不可以在子查询中使用该短语可以是字段名，也可以是数值升序为ASC，默认；降序为DESC可对一个或多个字段排序例：Selectcsrq,xh,xmfromxsorderbyxmDESC,xhdesc等价于：selectxh,xm,csrqfromxsorderby2desc,3desc因为，xm在SELECT后面的字段排第二位,xm排第一位联结查询：UNION将第一个SELECT语句最后查询结果与另一个SELECT语句最后查询结果排除重复行后组合起来两个SELECT语句查询结果列数必须相同两个SELECT语句查询结果对应列必须有相同的数据类型和宽度ORDERBY只能出现在最后一个SELECT语句中，而且需按编号指出例：已知借阅（ＪＹ）表中含读者类型(lx)，借阅日期（jyrq）和还书日期（hsrq）等字段．下列ＳＱＬ命令用来统计教师、学生借书过期罚款人次和罚款金额．其中罚款金额的算法如下：(1)对于学生类读者（lx字段值为"Ｘ"）来说，借阅期限为30天．每超过一天，罚款金额以每本书每天0.05元计算；(2)对于教师类读者（lx字段值为"Ｊ"）来说，借阅期限为60天．每超过一天，罚款金额以每本书每天0.05元计算；SELECT“教师”AS类型,COUNT（*）AS罚款人次；SUM（0.05*(hsrq-jyrq-60)）AS罚款金额；FROMsjk!jy；WHEREjy.lx="J"ANDhsrq-jyrq>60;UNION；SELECT"学生“AS类型,COUNT(*)AS罚款人次；

SUM（0.05*(hsrq-jyrq-30)）AS罚款金额；FROMsjk!jy；WHEREjy.lx="X"ANDhsrq-jyrq>30创建表---CreateTableCreateTable表名(字段1数据类型(宽度)null|notnull,字段2数据类型(宽度)null,……）说明：可在表名后通过NAME子句指定长表名可在表名后通过FREE子句表明建立自由表数据类型是大写字母，若宽度固定则宽度应省略此命令可以建立满足实体完整性的主关键字(主索引)PRIMARYKEY、候选索引UNIQUE、定义域完整性的CHECK约束及出错提示信息ERROR、定义默认值DEFAULT等，另外还有描述表之间联系的FOREIGNKEY和REFERENCES等。如果建立自由表，则NAME、PRIMARYKEY、CHECK、DEFAULT、FOREIGNKEY、REFERENCES均不能使用。例1：某银行存款表(ck.dbf)的表结果如下所示：创建ck表的表结构，可以使用下列命令CREATETABLEck(zhC(15),crrqD,cqN(3,0),jeY)例2：利用SQL语句的定义功能建立一个课程表，并且为课程号建立建立主索引，语句格式为：CREATETABLE课程表(课程号C(5)PrimaryKey,课程名C(30))删除表DROPTABLE表名直接从磁盘上删除表文件修改表结构---ALTERTABLE（1）ALTERTABLE表名ADD|ALTERCOLUMN字段名字段类型(宽度)null|notnullCheck逻辑表达式ERROR出错信息DEFAULT默认值PRIMARYKEY|UNIQUEADD是增加一字段，ALTER修改字段类型和宽度例1：为“歌手”表增加一个字段“最后得分”的SQL语句是：A．ALTERTABLE歌手ADD最后得分F(6,2)B．ALTERDBF歌手ADD最后得分F6,2C．CHANGETABLE歌手ADD最后得分F(6,2)D．CHANGETABLE学院INSERT最后得分F6,2例2：为表“金牌榜”增加一个字段“奖牌总数”，同时为该字段设置有效性规则：奖牌总数>=0，应使用SQL语句：ALTER

TABLE

金牌榜

(ADD)奖牌总数I（CHECK）奖牌总数>=0例3：将stock表的股票名称字段的宽度由8改为10，应使用SQL语句A)ALTERTABLEstock股票名称WITHc(10)B)ALTERTABLEstock股票名称c(10)C)ALTERTABLEstockALTER股票名称c(10)D)ALTERstockALTER股票名称c(10)（2）ALTERTABLE表名ALTERCOLUMN字段名NULL|NOTNULLSETCHECK逻辑表达式ERROR出错信息SETDEFAULT

默认值DROPCHECKDROPDEFAULT修改或设置已存在字段的有效性规则、默认值等。例1：为“评分”表的“分数”字段添加有效性规则：“分数必须大于等于0并且小于等于10”，正确的SQL语句是：A．CHANGETABLE评分ALTER分数SETCHECK分数＞=0AND分数＜=10B．ALTERTABLE评分ALTER分数SETCHECK分数＞=0AND分数＜=10C．ALTERTABLE评分ALTER分数CHECK分数＞=0AND分数＜=10D．CHANGETABLE评分ALTER分数SETCHECK分数＞=0OR分数＜=10例2：为“教师”表的职工号字段添加有效性规则：职工号的最左边三位字符是110，正确的SQL语句是

A．CHANGE

TABLE教师ALTER职工号SET

CHECK

LEFT(职工号,3)=“110”

B．ALTERTABLE教师ALTER职工号SET

CHECK

LEFT(职工号,3)=“110”

C．ALTER

TABLE教师ALTER职工号CHECK

LEFT(职工号,3)=“110”

D．CHANGE

TABLE教师ALTER职工号SET

CHECK

OCCURS(职工号,3)="110"

（3）Altertable表名dropcolumn字段名删除一个字段Altertable表名renamecloumn字段名Ato字段名B：字段重命名Altertable表名addprimarykey/unique/foreignkey索引表达式tag索引名创建主索引、候选索引、外关键字Altertable表名dropprimarykey/unique/foreignkey索引表达式tag索引名删除主索引、候选索引、外关键字例1：已有“歌手”表，将该表中的“歌手号”字段定义为候选索引、索引名是temp，正确的SQL语句是：

alterTABLE

歌手

ADD

UNIQUE

歌手号TAG

temp例2：ALTERTABLExscjDROPCOLUMNbz&&删除备注（BZ）字段例3：ALTERTABLE订购单RENAMECOLUMN总金额TO金额例4：ALTERTABLE订购单DROPUNIQUETAGabcd删除候选索引abcd图定义：CREATEVIEW视图名ASSELECT…AS后的SELECT语句指定了视图中的数据，视图的字段名和AS后SELECT子句的字段名相同。删除视图：DROPVIEW视图名当一个视图是由单个表导出时可以进行插入和更新操作，但不能进行删除操作；当视图是从多个表导出时，插入、更新和删除操作都不允许进行。当一个视图是由单个表导出时可以进行插入和更新操作，但不能进行删除操作；当视图是从多个表导出时，插入、更新和删除操作都不允许进行。例1：根据“歌手”表建立视图myview,视图中含有包括了“歌手号”左边第一位是“1”的所有记录，正确的SQL语句是：A．CREATEVIEWmyviewASSELECT*FROM歌手WHERELEFT（歌手号，1）=“1”B．CREATEVIEWmyviewASSELECT*FROM歌手WHERELIKE（“1”歌手号）C．CREATEVIEWmyviewSELECT*FROM歌手WHERELEFT（歌手号，1）=“1”D．CREATEVIEWmyviewSELECT*FROM歌手WHERELIKE（“1”歌手号）例2：有如下SQL语句CREATEVIEWview_stockASSELECT股票名称AS名称，单价FROMstock执行该语句后产生的视图含有的字段名是：A)股票名称、单价B)名称、单价C)名称、单价、交易所D)股票名称、单价、交易所插入数据---INSERTINTO格式1：INSERTINTO表名(字段1,字段2,…,字段n)VALUES(表达式1,表达式2,…表达式n)此格式适用于向表中插入一条不完整记录插入数据---INSERTINTO格式1：INSERTINTO表名(字段1,字段2,…,字段n)VALUES(表达式1,表达式2,…表达式n)此格式适用于向表中插入一条不完整记录格式2：INSERTINTO表名VALUES(表达式1,表达式2,…表达式n)此格式适用于向表中插入一条完整记录，但表达式1,表达式2,…表达式n的顺序和类型要和表完全一致例：插入一条记录到“选课”表中，选课.DBF：学号（C,8），课程号(C,3)，成绩(N,5,1)学号、课程号和成绩分别是“02080111”、“103”和80，正确的SQL语句是

A．INSERTINTO选课VALUES(“02080111”，”103”，80)

B．INSERTVALUES(“02080111”，”103”，80)