江苏省计算机等级考试VFP第六章考点归纳.docx
《江苏省计算机等级考试VFP第六章考点归纳.docx》由会员分享,可在线阅读,更多相关《江苏省计算机等级考试VFP第六章考点归纳.docx(18页珍藏版)》请在冰豆网上搜索。
江苏省计算机等级考试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)fromxs
selectcount(distinctxdh)fromxs
▪表-----FROM
✪连接查询:
是一种基于多个关系的查询;
格式1:
Select字段列表from表1,表2
Where表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表2
INNER|LEFT|RIGHT|FULLJOIN表3
ON表2.字段A=表3.字段A
ON表1.字段B=表2.字段B
WHERE<筛选条件>
例2:
设学生表S(学号,姓名,性别,年龄),课程表C(课程号,课程名,学分)和学生选课表SC(学号,课程号,成绩),检索学号,姓名和学生所选课程名和成绩,正确的SQL命令是:
A.SELECT 学号,姓名,课程名,成绩 FROM S,SC,C WHERE 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”
▪筛选记录---WHERE
WHERE与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成绩=NULL
CSELECT学号,课程号FROMSCWHERE成绩ISNULL
DSELECT学号,课程号FROMSCWHERE成绩
▪查询尚未确定供应商的订购单的信息:
Select*from订购单where供应商号isnull
▪查询已经确定了供应商的订购单的信息:
Select*from订购单where供应商号isnot
null
▪例:
如下命令查询雇员表中“部门号“字段为空值的记录:
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语句中,而且需按编号指出
例:
已知借阅(JY)表中含读者类型(lx),借阅日期(jyrq)和还书日期(hsrq)等字段.下列SQL命令用来统计教师、学生借书过期罚款人次和罚款金额.其中罚款金额的算法如下:
(1)对于学生类读者(lx字段值为"X")来说,借阅期限为30天.每超过一天,罚款金额以每本书每天0.05元计算;
(2)对于教师类读者(lx字段值为"J")来说,借阅期限为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
▪创建表---CreateTable
CreateTable表名(字段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|notnull
Check逻辑表达式ERROR出错信息
DEFAULT默认值
PRIMARYKEY|UNIQUE
ADD是增加一字段,ALTER修改字段类型和宽度
例1:
为“歌手”表增加一个字段“最后得分”的SQL语句是:
A.ALTERTABLE歌手ADD最后得分F(6,2)
B.ALTERDBF歌手ADD最后得分F6,2
C.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|NOTNULL
SETCHECK逻辑表达式ERROR出错信息
SETDEFAULT 默认值
DROPCHECK
DROPDEFAULT
修改或设置已存在字段的有效性规则、默认值等。
例1:
为“评分”表的“分数”字段添加有效性规则:
“分数必
须大于等于0并且小于等于10”,正确的SQL语句是:
A.CHANGETABLE评分ALTER分数SETCHECK
分数>=0AND分数<=10
B.ALTERTABLE评分ALTER分数SETCHECK
分数>=0AND分数<=10
C.ALTERTABLE评分ALTER分数CHECK分数>=0AND分数<=10
D.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订购单DROPUNIQUE
TAGabcd删除候选索引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”歌手号