二级考试重点SQL语句整理后.docx
《二级考试重点SQL语句整理后.docx》由会员分享，可在线阅读，更多相关《二级考试重点SQL语句整理后.docx（16页珍藏版）》请在冰豆网上搜索。
二级考试重点SQL语句整理后
二级考试重点:
SQL语句
VisualFoxPro在SQL方面支持数据定义、数据查询和数据操纵功能,
SQL语言的核心是查询。
SQL语言的查询命令也称作SELECT命令,它的基本形式由SELECT-FROM-WHERE查询块组成,多个查询块可以嵌套执行。
VisualFoxPro的SQL-SELECT命令的语法格式如下:
SELECT[ALL|DISTINCT][TOPnExpr[PERCENT]]
[Alias]Select_Item[ASColumn_Name][,[Alias]Select_Item[ASColumn-Name]…]
FROM[FORCE][DatabaseName!
]Table[[As]Local_Alias]
[[INNER|LEFT[OUTER]|RIGHT[OUTER]|FULL[OUTER]JOIN
DatabaseName!
]Table[[AS]Local_Alias]
[ONJoinCondition…]
-[[INTODestination]
|[TOFILEFileName[ADDITIVE]|TOPRINTER[PROMPT]|TOSCREEN]]
[WHEREJoinCondition[ANDJoinCondition…]
[AND|ORFilterCondition[AND|ORFilterCondition…]]]
[GROUPBYGroupColumn[,GroupColumn…]]
[HAVINGFilterCondition]
[UNION[ALL]SELECTCommand]
[ORDERBYOrder_Item[ASC|DESC][,Order_Item[ASC|DESC]…]]
从SELECT的命令格式来看似乎非常复杂,实际上只要理解了命令中各个短语的含义,SQLSELECT还是很容易掌握的,其中主要短语的含义如下:
·SELECT说明要查询的数据；
·FROM说明要查询的数据来自哪个（些）表,可以基于单个表或多个表进行查询；
·WHERE说明查询条件,即选择元组（行）的条件；
·GROUPBY短语用于对查询结果进行分组,可以利用它进行分组汇总；
·HAVING短语通常跟随GROUPBY使用,它用来限定分组必须满足的条件；
·ORDERBY短语用来对查询的结果进行排序。
以上短语是学习和理解SQLSELECT命令必须要掌握的。
SELECT查询命令的使用非常灵活,用它可以构造各种各样的查询。
本节将通过大量的实例来介绍SELECT命令的使用,在例子中再具体解释各个短语的含义。
以下为订货仓库数据库:
仓库表:
仓库号
城市
面积
WH1
北京
370
WH2
上海
500
WH3
广州
200
WH4
武汉
400
职工表:
仓库号
职工号
工资
WH2
E1
1220
WH1
E3
1210
WH2
E4
1250
WH3
E6
1230
WH1
E7
1250
订购表：
职工号
供应商号
订购单号
订购日期
E3
S7
OR67
2001/06/23
E1
S4
OR73
2001/07/28
E7
S4
OR76
2001/05/25
E6
NULL
OR77
Null
E3
S4
OR79
2001/06/13
E1
NuLL
OR80
Null
E3
NULL
OR90
Null
E3
S3
OR91
2001/07/13
注:
Null是空值,这里的意思是还没有确定供应商,自然也没有确定订购日期
供应商表:
供应商号
供应商名
地址
S3
振华电子厂
西安
S4
华通电子公司
北京
S6
607厂
郑州
S7
爱华电子厂
北京
1.简单查询
首先从几个最简单的查询开始,这些查询基于单个表,查询由SELECT和FROM短语构成（无条件查询）或由SELECT、FROM和WHERE短语构成（条件查询）。
例1从职工关系表中检索所有工资值。
SELECT工资FROM职工
结果是:
1220
1210
1250
1230
1250
可以看到,在结果中有重复值，1250出现了2次。
如果要去掉重复值，则需要加上DISTINCT短语:
SELECTDISTINCT工资FROM职工
DISTINCT短语的作用是去掉查询结果中的重复值。
例2检索仓库关系表中的所有元组（行）。
SELECT*FROM仓库
结果是:
WH1北京370
WH2上海500
WH3广州200
WH4武汉400
其中"*"是通配符,表示选择所有属性（字段）,这里的命令等同于:
SELECT仓库号,城市,面积FROM仓库
例3检索工资多于1230元的职工号。
SELECT职工号FROM职工WHERE工资>1230
结果是:
E4
E7
这里用WHERE短语指定了查询条件,查询条件可以是任意复杂的逻辑表达式。
例4检索哪些仓库有工资多于1210元的职工。
SELECTDISTINCT仓库号FROM职工WHERE工资>1210
结果是:
WH1
WH2
WH3
在这个例子中,显然我们只对惟一的仓库号感兴趣。
但在例1中我们也许对所有的工资值都感兴趣,因此不管是否有重复值。
决定是否消除重复值是有一定实际意义的,也是SQL的重要一面。
再回到例1,假设我们对所有职工的平均工资感兴趣,那么指定DISTINCT显然是错误的,我们将在计算检索中再来讨论这个问题。
3.多表查询
例5检索工资多于1230元的职工的职工号、所在城市。
SELECT职工号,城市FROM职工,仓厍;
WHERE（工资>1230）AND（职工.仓库号=仓库.仓库号）
结果是:
E4上海
E7北京
这里的"职工.仓库号=仓库.仓库号"是连接条件。
如果在检索命令的FROM之后有两个关系表,那么这两个表之间肯定有一种联系（否则无法构成检索表达式）。
从前面的讨论已经知道,仓库表和职工表之间存在着一个一对多的联系。
当FROM之后的多个关系表中含有相同的属性（字段）名时,这时必须用表名前缀指明属性所属的关系表,如职工.仓库号,"."前面是关系表名,后面是属性名
例6找出工作在面积大于400的仓库的职工号以及这些职工工作的城市。
select职工号,城市from仓库,职工;
Where（面积>400）and（职工.仓库号=仓库.仓库号）
结果是:
E1上海
E4上海
3.嵌套查询
接着来讨论另一类基于多个关系表的查询,这类查询所要求的结果出自一个表,但相关的条件却涉及多个表。
在第2节的例子中,WHERE之后是一个相对独立的参件,这个条件或者为真,或者为假。
但是,有时需要用另外的方式来表达检索要求,例如,当检索关系表x中的元组（行）时,它的条件依赖于相关的关系表y中的元组的属性（字段）值,这时使用SQL的嵌套查询功能将非常方便。
下面就来看几个例子。
例7哪些城市至少有一个仓库的职工的工资为1250元?
这个例子要求查询仓库表中的城市信息,而查询条件是职工表的工资字段值,为此可以使用如下的嵌套查询:
SELECT城市FROM仓库WHERE仓库号IN;
（SELECT仓库号FROM职工WHERE工资=1250）
.结果是:
.北京
上海
在这个命令中含有两个SELECT—FROM—WHERE查询,即内层子查询和外层查询,内层查询检索到的仓库号值是WH1和WH2,这样就可以写出等价的命令:
SELECT城市FROM仓库WHERE仓库号IN（"WH1","WH2"）
这里IN相当于集合运算符∈。
例8查询所有职工的工资都多于1210元的仓库的信息。
这个检索要求也可以描述为:
没有一个职工的工资少于或等于1210元的仓库的信息。
这样可以有SQL命令:
SELECT*FROM仓库WHERE仓库号NOTIN;
（SELECT仓库号FROM职工WHERE工资<=1210）
结果是:
WH2上海500
WH3广州200
WH4武汉400
内层SELECT—FROM—WHERE查询指出所有职工的工资少于或等于1210元的仓库的仓库号值的集合,在这里该集合只有一个值"WH1";然后再从仓库表中检索元组的仓库号属性值不在该集合中的每个元组。
有的读者也许已经注意到刚才的检索出现了错误,尽管在"武汉"的"WH4"仓库还没有职工,但该仓库的信息也被检索出来了。
所以我们必须认真分析检索要求,写出正确的语句。
如果我们的检索要求要排除那些还没有职工的仓库,检索要求可以叙述为:
检索所有职工的工资都多于1210元的仓库的信息,并且该仓库至少要有一名职工。
这样描述就很清楚了,因为我们对没有职工的仓库不感兴趣。
这样,写出的SQL命令也就复杂一些了:
SELECT*FROM仓库WHERE仓库号NOTIN;
（SELECT仓库号FROM职工WHERE工资<=1210）;
AND仓库号IN（SELECT仓库号FROM职工）
这样,内层是两个并列的查询,在结果中将不包含没有职工的仓库信息。
例9找出和职工E4挣同样工资的所有职工。
SELECT职工号FROM职工WHERE工资=;
（SELECT工资FROM职工WHERE职工号="E4"）
结果是:
E4
E7
4.几个特殊的运算符
下面有几个特殊的运算符,它们是Between...And和Like等,下面通过例子来解释
例10检索出工资在1220元到1240元范围内的职工信息
这个查询的条件是在什么范围之内,显然可以使用Between...And,谓词有如下查询语句
Select*from职工where工资Between1220and1240
结果是:
WH2E11220
WH3E61230
这里Between...And的意思是在"...和...之间",这个查询的条件等价于:
（工资>=1220）And（工资<=1240）
显然使用Between...And表达条件更清晰、更简洁
例11从供应商关系表中检索出全部公司的信息（不要工厂或其他供应商的信息）。
这是一个字符串匹配的查询,显然应该使用LIKE运算符:
SELECT*FROM供应商WHERE供应商名LIKE"%公司"
结果是:
s4华通电子公司北京
这里的LIKE是字符串匹配运算符,通配符"%"表示0个或多个任意字符,另外还有一个通配符"_"（下划线）表示一个任意字符。
例12找出不在北京的全部供应商信息。
SELECT*FROM供应商WHERE地址!
="北京"
结果是:
s3振华电子厂西安
S6607厂郑州
在SQL中,"!
="表示"不等于"。
另外还可以用否定运算符NOT写出等价的命令:
SELECT*FROM供应商WHERENOT（地址="北京"）
NOT的应用范围很广,例如,可以有NOTIN、NOTBETWEEN等。
假如提出和例10相反的请求,找出工资不在1220元和1240元之间的全部职工信息,可以用命令
SELECT*FROM职工WHERE工资NOTBETWEEN1220AND1240
结果是:
WH1E31210
WH2E41250
WH1E71250
5.排序
使用SQLSELECT可以将查询结果排序,排序的短语是ORDERBY,格式如下:
ORDERBYOrder_Item[ASC|DESC][,Order_Item[ASC|DESC]...]
可以看出,可以按升序（ASC）或降序（DESC）排序,可以按一列或多列排序。
下面是两个查询结果排序的例子。
例13按职工的工资值升序检索出全部职工信息。
SELECT*FROM职工ORDERBY工资
结果是:
WH1E31210
WH2E11220
WH3E61230
WH2E41250
WHlE71250
这里ORDERBY是排序子句,如果要将结果按降序排列,只要加上DESC:
SELECT*FROM职工ORDERBY工资DESC
例14先按仓库号排序,再按工资排序输出全部职工信息。
SELECT*FROM职工ORDERBY仓库号,工资
结果是:
WH1E31210
WH1E71250
WH2E11220
WH2E41250
WH3E61230
这是一个按多列排序的例子。
注意:
ORDERBY是对最终的查询结果进行排序,不可以在子查询中使用该短语。
6.简单的计算查询
SQL语言是完备的,不仅具有一般的检索能力,而且还有计算方式的检索,例如检索职工的平均工资、检索某个仓库中职工的最高工资值等。
用于计算检索的函数有:
COUNT（计数）、SUM（求和）、AVG（计算平均值）、MAX（求最大值）、MIN（求最小值）。
这些函数可以用在SELECT短语中对查询结果进行计算。
例15找出供应商所在地的数目。
.
SELECTCOUNT（DISTINCT地址）FROM供应商
参见前面给出的供应商的记录值,其中共有3个地址:
北京、西安和郑州,所以结果为3。
注意,如果是对关系中的元组个数进行计数,COUNT函数不应使用DISTINCT。
SELECTCOUNT（*）FROM供应商
将给出供应商关系中的记录数。
例16求支付的工资总数。
SELECTSUM（工资）FROM职工
结果是:
6160
这个结果是职工关系中的工资值的总和,它并不管是否有重复值,这时若使用命令:
SelectSum（Distinct工资）From职工
将得出错误的结果是4910
例17求北京和上海的仓库职工的工资总和
SelectSum（工资）From职工Where仓库号in;
（select仓库号from仓库where城市="北京"or城市="上海"）
结果是:
4930
例18求所有职工的工资都多于1210元的仓库的平均面积
SelectAvg（面积）From仓库Where仓库号NotIn;
（Select仓库号From职工Where工资<=1210）
结果是:
366.67
这里要注意,以上结果的运算包含了尚没有职工WH4仓库，所以是不完善的,如果要排除没有职工的仓库,以上语句应该改为:
SELECTAVG（面积）FROM仓库WHERE仓库号NOTIN;
（SELECT仓库号FROM职工WHERE工资<=1210）;
AND仓库号IN（SELECT仓库号FROM职工）
结果是:
350
例19求在WH2仓库工作的职工的最高工资值。
SELECTMAX（工资）FROM职工WHERE仓库号="WH2"
结果是:
1250
与MAX函数相对应的是MIN函数（求最小值）。
比如,求最低工资值可以有如下命令:
SELECTMIN（工资）FROM职工WHERE仓库号="WH2"
7.分组与计算查询
第6节的几个例子是针对整个关系的计算查询,而利用GROUPBY子句进行分组计算查询使用的更加广泛。
GROUPBY短语的格式如下:
GROUPBYGroupColumn[,GroupGolumn…][HAVINGFilterCondition]
可以按一列或多列分组,还可以用HAVING进一步限定分组的条件。
下面是几个分组计算查询的例子。
例20求每个仓库的职工的平均工资。
SELECT仓库号,AVG（工资）FROM职工;
GROUPBY仓库号
结果是:
WH11230
WH21235
WH31230
在这个查询中,首先按仓库号属性进行分组,然后再计算每个仓库的平均工资。
GroupBy子句一般跟在WHERE子句之后,没有WHERE子句时,跟在FROM子句之后。
另外,还可以根据多个属性进行分组。
在分组查询时,有时要求分组满足某个条件时才检索,这时可以用HAVING子句来限定分组。
例21求至少有两个职工的每个仓库的平均工资。
SELECT仓库号,COUNT（*）,AVG（工资）FROM职工;
GROUPBY仓库号HAVINGCOUNT（*）>=2
结果是:
WH121230
WH221235
HAVING子句总是跟在GROUPBY子句之后,一般不可以单独使用。
HAVING子句和WHERE子句不矛盾,在查询中是先用WHERE子句限定元组（筛选行）,然后进行分组,最后再用Having子句限定分组。
8.利用空值查询
SQL支持空值,当然也可以利用空值进行查询。
假设在订购单关系中,一名职工正在准备订购单,但尚未选定供应商,这样若把信息录入数据库,则供应商号和订购日期两个属性均为空值,在前面给出的订购单记录中有3条这样的记录。
订购表
职工号
供应商号
订购单号
订购日期
E3
S7
OR67
2001/06/23
E1
S4
OR73
2001/07/28
E7
S4
OR76
2001/05/25
E6
NULL
OR77
Null
E3
S4
OR79
2001/06/13
E1
NuLL
OR80
Null
E3
NULL
OR90
Null
E3
S3
OR91
2001/07/13
例22找出尚未确定供应商的订购单。
SELECT*FROM订购单WHERE供应商号ISNULL
结果是:
E6NULLOR77NULL
E1NULLOR80NULL
E3NULLOR90NULL
注意:
查询空值时要使用ISNULL,而=NULL是无效的,因为空值不是一个确定的值,所以不要用”=”这样的运算符进行比较
例24列出已经确定了供应商的订购单的信息
Select*From订购单Where供应商号IsNotNull
在SQL标准中还支持左联接与右联接,它们与等值连接和自然连接不同。
原来的连接是只有满足连接条件,相应的结果才会出现在结果表中；而这两个连接运算是,首先保证一个表中满足条件的元组都在结果表中,然后将满足连接条件的元组与另一个表的元组进行连接,不满足连接条件的则应将来自另一表的属性值置为空值。
下面将提到超连结查询。
9.超连接查询
VisualFoxPro有专门的连接运算语法格式,它支持超连接查询,与连接运算有关的语法格式如下:
SELECT…
FROMTableINNER|LEFT|RIGHT|FULLJOINTable
ONJoinCondition
WHERE...
其中:
•INNERJOIN等价于JOIN,为普通连接（innerjoinon也可写成fromwhere的形式，见例25）
Leftjoin为左连接
Rightjoin为右连接
Fulljoin为完全连接,即两个表中的记录不管是否满足连接条件将都在目标表或查询结果中出现,不满足连接条件的记录的对应部分为Null
On指定连接条件
例25普通连接（即只有满足连接条件的记录才出现在查询结果中）
Select仓库.仓库号,城市,面积,职工号,工资;
From仓库InnerJoin职工;
ON仓库.仓库号=职工.仓库号
和
SELECT仓库.仓库号,城市,面积,职工号,工资;
FROM仓库,职工WHERE仓库.仓库号=职工.仓库号
以上二种连接语句的结果都是:
WH2上海500E11220
WH1北京370E31210
WH2上海500E41250
WH3广州200E61230
WH1北京370E71250
例26左连接（即除满足连接条件的记录出现在查询结果中外,左边第一个表中不满足连接条件的记录也出现在查询结果中）。
SELECT仓库.仓库号,城市,面积,职工号,工资;
FROM仓库LEFTJOIN职工;
ON仓库.仓库号=职工.仓库号
结果是:
WH1北京370E31210
WH1北京370E71250
WH2上海500E11220
WH2上海500E41250
WH3广州200E61230
WH4武汉400NULLNULL
为了看到右连接和全连接的效果,假设在职工表中插入了如下一条记录:
"WH8","E8",1200
例27右连接（即除满足连接条件的记录出现在查询结果中外,第二个表中不满足条件的记录也出现在查询结果中）。
SELECT仓库.仓库号,城市,面积,职工号,工资;
FROM仓库RIGHTJOIN职工;
ON仓库.仓库号=职工.仓库号
结果是:
WH2上海500E11220
WH1北京370E31210
WH2上海500E41250
WH3广州200E61230
WH1北京370E71250
NULLNULLNULLE81200
实际上职工"E8"所在的仓库并不存在,这在实际应用中是不允许的。
例28全连接（即除满足连接条件的记录出现在查询结果中外,两个表中不满足连接条件的记录也出现在查询结果中）。
SELECT仓库.仓库号,城市,面积,职工号,工资;
FROM仓库FULLJOIN职工;
ON仓库.仓库号=职工.仓库号
结果是:
WH1北京370E31210
WHl北京370E71250
WH2上海500E11220
WH2上海500E41250
WH3广州200E61230
WH4武汉400NULLNULL
NULLNULLNULLE81200
注意:
JOIN连接格式，在连接三张以上表时的书写方法要特别注意,在这种格式中JOINON的顺序是很重要的,三张以上表的联结中，JOIN的顺序要和ON的顺序（相应的连接条件）正好相反
例29要求检索出每个订单的订单号，职工号，仓库号，仓库所在城市。
Select订购.订购单号，仓库.仓库号,仓库.城市,职工.职
升级会员 