SELECT表达式
接着,让我们看看SELECT表达式,SELECT用来在表中寻找符合特定条件的记录,语法如下:
SELECT [关键字] { * | 表名称.* | [表名称.]字段名称1 [AS 别名1] [, [表名称.] 字段名称2 [AS 别名2] [, ...]]} FROM 表运算式 [, ...] [IN 外部表] [WHERE... ] [GROUP BY... ] [HAVING... ] [ORDER BY... ] [WITH OWNERACCESS OPTION]
关键字:可以用来限制返回之记录的数量,可以设定为ALL、DISTINCT、DISTINCTROW 、或TOP。如果没有指定述语,缺省值为 ALL。
*:选取全部的字段。
别名:代替在表中原有的字段名称。
表运算式:一个或多个以逗号分隔的表名称。
外部表:如果表不存在目前的表当中,须设定包含表运算式中表的表名称。
执行SELECT 表达式,并不会改变表之中现有的资料。
一个 SELECT 表达式的最基本语法是:
SELECT * FROM 表运算式
您可以使用星号(*)来选取表的全部字段。譬如下例选取 [产品] 表中的全部字段:
SELECT * FROM 产品
字段名称包含空格或标点符号时,须使用括号[ ]将它括在其中。例如:
SELECT [电脑 的产品]
如果在FROM子句中的字段名称中,包含多个的表中时,需在字段名称前加上表名称和点(.)运算子,即 表名称.字段名称。譬如下例选取 [产品] 表的 [价格] 字段和 [订单] 表的 [数量] 字段:
SELECT 产品.价格, 订单.数量
FROM 产品, 订单
WHERE 产品.代号 = 订单.代号
当您使用Recordset 对象时,Recordset并不认得rs(表名称.字段名称) 格式,即rs(“产品.价格”)会发生错误,您必须使用AS来设定字段名称的别名。譬如:
SELECT 产品.价格 AS 价格, 订单.数量AS 数量
FROM 产品, 订单
WHERE 产品.代号 = 订单.代号
如此就可以使用rs(“价格”) 和rs(“数量”),来读取其字段的资料。
与SELECT表达式相关的语法:
关键字:ALL/ DISTINCT/ DISTINCTROW/ TOP。
WHERE:寻找FROM中的表符合特定条件的资料记录。WHERE 可以使用下面的BETWEEN、LIKE、IN运算子:
Between...And:判断表达式的值是否落在指定的范围之中。
Like:寻找相符合的关键字。
IN运算式:限定范围。
NOT IN运算式:表示不属于所指定的范围。
ORDER BY子句:可以设定排序的字段。
GROUP BY子句:将查询的结果做统计。
HAVING子句:使用在SELECT 表达式中,筛选已经GROUP BY统计的记录。
Union:可以合并多组查询的结果。
JOIN:连接组合两个表中的字段记录。
子查询(sub query):表达式中,可以包括SELECT表达式。
Select...Into:将查询的结果,建立一个产生的表。
详细介绍如下:
ALL/DISTINCT/DISTINCTROW/TOP关键字
使用SELECT查询时,可以加上关键字,以选取所查询的记录。如下:
ALL:返回所有的记录。
DISTINCT:指定字段中的记录有重复时只返回一条记录,记录不重复。
DISTINCTROW:指定字段中的记录有重复时都不返回。
TOP:返回前面几个记录或几个百分比的记录。
语法如下:
SELECT [ALL | DISTINCT | DISTINCTROW | [TOP n [PERCENT]]] FROM 表
ALL返回所有的记录。不加关键字时,与加ALL的意义相同,将返回所有的记录。譬如下列两个例子的执行结果相同,都从产品表中返回所有的记录:
SELECT ALL * FROM 产品
与以下的执行结果相同:
SELECT * FROM 产品
DISTINCT不选取指定字段之中的重复资料。使用DISTINCT后,查询的结果,列在SELECT DISTINCT后的每个字段的资料值,若相同则只取一条记录,换句话说,指定字段的资料不会有重复的现象。例如,在产品表之中有一些产品名称相同的产品,加上DISTINCT的SQL表达式只会返回一条记录字段为产品名称资料相同的记录:
SELECT DISTINCT 产品名称 FROM 产品
如果您不加上DISTINCT,上例查询将返回数个包含相同产品名称的记录。
如果SELECT DISTINCT子句后指定数个字段,查询的结果,所有字段的组合值不会有重复的现象。
DISTINCTROW则不返回指定字段所有重复的记录。
TOP n [PERCENT] ,返回前面几条记录或几个百分比的记录。排列的顺序可以使用ORDER BY子句来指定。譬如找到成绩前10名的学生姓名:
SELECT TOP 10 姓名 FROM 学生 ORDER BY 成绩
如果您没有包含ORDER BY子句,查询将由学生表返回任意的10个记录。
TOP不在相同值间作选择,如果第10及第11的成绩是相同的,查询将返回11个记录。
您可使用PERCENT来设定前面几个百分比的记录,譬如譬如找到成绩前10%的学生姓名:
SELECT TOP 10 PERCENT 姓名 FROM 学生 ORDER BY 成绩
让我们看一个在ASP程式中使用这个SQL指令的例子。
可以利用Distinct找出不重复的记录,譬如ASP程式rs7.asp如下,[Insert Into 产品(代号,名称) Select Distinct 代号,名称 From 产品 where 代号 ='C2000'] 使用Distinct与Insert Into新增代号为C2000的记录:
<%
'Distinct 代号,名称只判断代号,名称是否相同, 相同者只加一条记录
sql = "Insert Into 产品(代号,名称) Select Distinct 代号,名称 From 产品 where 代号 ='C2000'"
Set a = conn1.Execute(sql)
Set rs3 = Server.CreateObject("ADODB.Recordset")
sql = "Select * from 产品 where 代号 ='C2000'"
rs3.Open sql,conn1,1,1,1
%>
<TABLE COLSPAN=8 CELLPADDING=5 BORDER=0>
<TR>
<TD ALIGN=CENTER BGCOLOR="#800000"><FONT COLOR="#FFFFFF">代号</FONT></TD>
<TD ALIGN=CENTER BGCOLOR="#800000"><FONT COLOR="#FFFFFF">名称</FONT></TD>
<TD ALIGN=CENTER BGCOLOR="#800000"><FONT COLOR="#FFFFFF">价格</FONT></TD>
<TD ALIGN=CENTER BGCOLOR="#800000"><FONT COLOR="#FFFFFF">数量</FONT></TD>
</TR>
<% Do while not rs3.EOF %>
<TR>
<TD BGCOLOR="f7efde" ALIGN=CENTER><%= rs3("代号")%></TD>
<TD BGCOLOR="f7efde" ALIGN=CENTER><%= rs3("名称")%></TD>
<TD BGCOLOR="f7efde" ALIGN=CENTER><%= rs3("价格")%></TD>
<TD BGCOLOR="f7efde" ALIGN=CENTER><%= rs3("数量")%></TD>
</TR>
<%
rs3.MoveNext
Loop
rs3.Close
%>
</TABLE>
WHERE
WHERE寻找FROM中的表符合特定条件的资料记录,WHERE 使用在SELECT、UPDATE、或DELETE表达式。
如果SELECT查询中没有指定WHERE子句,将返回表中所有的资料。如果在SELECT查询多个表,且没有使用WHERE子句、或JOIN 子句的话,查询结果为多个表资料的乘积。
WHERE设定特定的条件,譬如:
FROM 产品 WHERE 分类 = '电脑':表示选择 [分类] 为 [电脑] 的 [产品]
WHERE 价格 Between 1000 And 5000:表示价格介于1000至5000之间 。
一个WHERE子句,最多可包含40个运算式,运算式之间由AND或OR等逻辑运算子相连结。
设定特定的条件时,依照字段类型,前后加不同的符号,譬如:
文字:前后加单引号,譬如WHERE 分类 = '电脑'。
数字:前后不加符号,譬如WHERE 数量 > 100。
日期:前后加#号,譬如WHERE 日期 = #5/15/99#。
WHERE可以使用下面的BETWEEN、LIKE、IN运算子。
WHERE可以使用下面的BETWEEN、LIKE、IN运算子。
Between...And
Between...And判断表达式的值是否落在指定的范围之中。语法为:
expr Between value1 And value2
表示如果expr的值介于value1和value2之间,则Between...And运算子会返回True;否则它会返回False。
您也可以加上逻辑运算 Not expr Between value1 And value2,来判断相反的条件,即expr落在value1和 value 2的范围之外。
如果expr、value1、或value2为Null时,则Between...And会返回Null值。
您不能在 Between...And 运算子中使用通配符*,例如,您不能使用 100* 和 500* 来寻找介于1000至5000中的产品代号。
Like
Like寻找相符合的关键字。
Like跟随一个运算式,列出所要比较的字串或字符串文字。
在一运算式中,您可以使用 Like 运算子将一字段资料和一字串运算式做一比较。您可以使用通配符*来找出相类似的资料,譬如Like "王*",则查询会返回所有字段中以 [王] 做为开头的资料。
Like后运算式可以使用的符号:
%:通配符,表示一个或多个字符,譬如LIKE jack% 将找到所有以jack为起头的字串文字,LIKE %jack%则找到包括jack的字串文字。
_:一个字符的通配符,譬如LIKE jack_ 将找到如jack1、jacka等字串文字,LIKE _jack_ 则找到如ajackb、cjackf等字串文字。
[x-y]:指定字符范围,譬如LIKE [a-c]jack 将找到如ajack1、bjack、cjack等字串文字。
[^x-y]:指定排除的字符范围,譬如LIKE [^a-c]jack 将不会找到如ajack1、bjack、cjack等字串文字。
下例中,会得到以字母A做为开头而其后接着介于B到G之间的任何字母和一个数字的资料:
Like "A[B-G]#"
IN
IN运算式,限定范围。
语法为:
expr [Not] In(value1, value2,…)
其中expr为运算式字段。value1, value2, …为指定的范围清单。
例如,您可以使用In运算子,来指定学生必须居住的城市,为广州市、深圳市、或汕头市:
SELECT *
FROM 学生
WHERE 城市 In ('广州市','深圳市','汕头市')
让我们看一个ASP程式使用这个SQL指令的例子。
我们可以利用IN子句决定范围,譬如ASP程式rs24.asp如下,[SELECT 姓名,科目,分数 From 考试 Where 分数 In (SELECT 分数 From 考试 Where 分数>=60)],使用IN找出分数大于或等于60分的记录:
<%
Set conn1 = Server.CreateObject("ADODB.Connection")
conn1.Open "DBQ=" & Server.MapPath("ntopsamp.mdb") & ";Driver={Microsoft Access Driver (*.mdb)};DriverId=25;FIL=MS Access;"
Set rs2 = Server.CreateObject("ADODB.Recordset")
SqlStr = "SELECT 姓名,科目,分数 From 考试 Where 分数 In (SELECT 分数 From 考试 Where 分数>=60)"
rs2.Open SqlStr,conn1,1,1
Response.Write "<p>In: 低于60分不算"
Do while not rs2.EOF
Response.Write "<BR>" & rs2("姓名") & " " & rs2("科目") & " 分数: " & rs2("分数")
rs2.MoveNext
Loop
rs2.Close
%>
以上的ASP程式rs24.asp,在用户端使用浏览器,浏览执行的结果,显示分数大于或等于60分的记录。
NOT IN
NOT IN运算式,表示不属于所指定的范围。
让我们看一个于ASP程式中使用这个SQL指令的例子。
我们可以利用Not In子句决定不应显示的记录,譬如ASP程式rs24.asp如下,[SELECT 姓名,科目,分数 From 考试 Where 分数 Not In (SELECT 分数 From 考试 Where 分数>=60)],使用IN找出分数不大于或等于60分的记录,即小于60分的记录:
<%
Set conn1 = Server.CreateObject("ADODB.Connection")
conn1.Open "DBQ=" & Server.MapPath("ntopsamp.mdb") & ";Driver={Microsoft Access Driver (*.mdb)};DriverId=25;FIL=MS Access;"
Set rs2 = Server.CreateObject("ADODB.Recordset")
SqlStr = "SELECT 姓名,科目,分数 From 考试 Where 分数 Not In (SELECT 分数 From 考试 Where 分数>=60)"
rs2.Open SqlStr,conn1,1,1
Response.Write "<p>Not In: 高于60分不算"
Do while not rs2.EOF
Response.Write "<BR>" & rs2("姓名") & " " & rs2("科目") & " 分数: " & rs2("分数")
rs2.MoveNext
Loop
rs2.Close
%>
以上的 ASP程式rs24.asp,在用户端使用浏览器,浏览执行的结果,显示分数不大于或等于60分的记录
ORDER BY
ORDER BY子句,可以设定排序的字段。
在一个SQL表达式之中,ORDER BY子句通常放置于最后。
若要以递减顺序来进行排序(Z~A,9 ~0),须于您要递减排序的每一字段的尾部加DESC字。譬如:
SELECT 姓名
FROM 员工
ORDER BY 工资 DESC, 年龄
表示[工资] 字段以递减顺序来进行排序,[年龄] 字段以递增顺序来进行排序。
ORDER BY子句中排序的字段,不可以包含MEMO字段类型或OLE对象类型,否则会发生错误。
在ORDER BY子句之中包含多个的字段时,首先以ORDER BY之后的第一个字段来进行排序。然后,若在此字段之中有相同的资料记录,才会以列第二字段来进行排序,依此类推。
GROUP BY
GROUP BY将查询的结果做统计,语法如下:
SELECT fieldlist FROM table WHERE criteria [GROUP BY groupfieldlist]
使用WHERE子句,可以设定不想要做统计的资料,而使用HAVING子句可以筛选已经统计之后的字段。
不可以对MEMO字段类型或OLE对象类型的字段做统计,否则会发生错误。
譬如ASP程式rs22.asp如下,[SELECT 种类, Avg(价格) As 平均 From 产品 Group By 种类] 使用GROUP BY将查询的结果做 [种类] 统计,统计各个种类统计来平均价格Avg(价格):
<%
Set conn1 = Server.CreateObject("ADODB.Connection")
conn1.Open "DBQ=" & Server.MapPath("ntopsamp.mdb") & ";Driver={Microsoft Access Driver (*.mdb)};DriverId=25;FIL=MS Access;"
Set rs2 = Server.CreateObject("ADODB.Recordset")
SqlStr = "SELECT 种类, Avg(价格) As 平均 From 产品 Group By 种类"
Response.Write "<p>Avg(价格)"
rs2.Open SqlStr,conn1,1,1
Do while not rs2.EOF
Response.Write "<BR>" & rs2("种类") & ": " & rs2("平均")
rs2.MoveNext
Loop
rs2.Close %>
以上的 ASP程式rs22.asp,用户端使用浏览器,浏览执行的结果,显示依据 [种类] 统计来统计平均价格。
让我们多看个例子,譬如ASP程式rs22.asp如下,[SELECT 种类, Sum(数量*价格) As 总计 From 产品 Group By 种类] 使用GROUP BY将查询的结果做 [种类] 统计,统计各个种类的数量与价格相乘的总价Sum(数量*价格):
<%
Set conn1 = Server.CreateObject("ADODB.Connection")
conn1.Open "DBQ=" & Server.MapPath("ntopsamp.mdb") & ";Driver={Microsoft Access Driver (*.mdb)};DriverId=25;FIL=MS Access;"
Set rs2 = Server.CreateObject("ADODB.Recordset")
SqlStr = "SELECT 种类, Sum(数量*价格) As 总计 From 产品 Group By 种类"
rs2.Open SqlStr,conn1,1,1
Response.Write "<p>Sum(数量*价格)"
Do while not rs2.EOF
Response.Write "<BR>" & rs2("种类") & ": " & rs2("总计")
rs2.MoveNext
Loop
rs2.Close %>
以上的 ASP程式rs22.asp,在用户端使用浏览器,浏览执行的结果,显示依据 [种类] 来统计总价。
Groupfieldlist为用以统计的字段名称,最多为10个字段。
在groupfieldlist中字段名称的顺序,将决定统计层次,由最高至最低的层次来分组。
最后,譬如ASP程式rs22.asp如下,[SELECT 姓名,科目,Avg(分数) As 平均 From 考试 Group By 姓名,科目] 使用GROUP BY将查询的结果做依据 [姓名] 和 [科目] 统计,统计各个统计的 [平均] 分数Avg(分数):
<%
Set conn1 = Server.CreateObject("ADODB.Connection")
conn1.Open "DBQ=" & Server.MapPath("ntopsamp.mdb") & ";Driver={Microsoft Access Driver (*.mdb)};DriverId=25;FIL=MS Access;"
Set rs2 = Server.CreateObject("ADODB.Recordset")
SqlStr = "SELECT 姓名,科目,Avg(分数) As 平均 From 考试 Group By 姓名,科目"
rs2.Open SqlStr,conn1,1,1
Response.Write "<p>Group By 姓名,科目"
Do while not rs2.EOF
Response.Write "<BR>" & rs2("姓名") & " " & rs2("科目") & " 平均: " & rs2("平均")
rs2.MoveNext
Loop
rs2.Close
%>
以上的 ASP程式rs22.asp,在用户端使用浏览器,浏览执行的结果,显示依据 [姓名] 和 [科目] 来统计 [平均] 分数。
学会了吗? HAVING使用于SELECT 表达式中,筛选已经GROUP BY统计的记录。在GROUP BY统计记录后,HAVING将筛选与HAVING子句中条件相吻合的记录。
语法如下:
SELECT fieldlist FROM table WHERE selectcriteria GROUP BY groupfieldlist [HAVING groupcriteria]
.groupcriteria表示决定应筛选的统计记录。
HAVING与WHERE相类似,是用来决定选取哪些记录。当使用GROUP BY来统计记录后,HAVING会决定应显示的记录,譬如:
SELECT 产品名称
FROM 产品
GROUP BY 分类
HAVING 单价 > 1000
一个HAVING子句最多可包含40个运算式,运算式之间将由AND或OR等逻辑运算子来连结。
让我们看一个于ASP程式当中使用这个SQL指令的例子。
我们可以利用HAVING子句决定应显示的记录,譬如ASP程式rs23.asp如下,[SELECT 姓名,科目,Avg(分数) As 平均 From 考试 Group By 姓名,科目 Having Avg(分数) >=60],使用Having Avg(分数) >=60找出平均分数大于或等于60分的记录:
<%
Set conn1 = Server.CreateObject("ADODB.Connection")
conn1.Open "DBQ=" & Server.MapPath("ntopsamp.mdb") & ";Driver={Microsoft Access Driver (*.mdb)};DriverId=25;FIL=MS Access;"
Set rs2 = Server.CreateObject("ADODB.Recordset")
SqlStr = "SELECT 姓名,科目,Avg(分数) As 平均 From 考试 Group By 姓名,科目 Having Avg(分数) >=60"
rs2.Open SqlStr,conn1,1,1
Response.Write "<p>Having Avg(分数) >=60"
Do while not rs2.EOF
Response.Write "<BR>" & rs2("姓名") & " " & rs2("科目") & " 平均: " & rs2("平均")
rs2.MoveNext
Loop
rs2.Close
%>
以上的 ASP程式rs23.asp,在用户端使用浏览器,浏览执行的结果,显示找出平均分数大于或等于60分的记录。
我们也可以利用HAVING子句找出重复的记录,譬如ASP程式rs23.asp如下,[SELECT 代号 From 产品 Group By 代号 Having Count(代号) > 1],使用Having Count(代号) > 1找出代号重复的记录:
<%
Set conn1 = Server.CreateObject("ADODB.Connection")
conn1.Open "DBQ=" & Server.MapPath("ntopsamp.mdb") & ";Driver={Microsoft Access Driver (*.mdb)};DriverId=25;FIL=MS Access;"
Set rs2 = Server.CreateObject("ADODB.Recordset")
SqlStr = "SELECT 代号 From 产品 Group By 代号 Having Count(代号) > 1"
rs2.Open SqlStr,conn1,1,1
Response.Write "<p>找出重复Having Count(代号) > 1"
Do while not rs2.EOF
Response.Write "<BR>" & rs2("代号")
rs2.MoveNext
Loop
rs2.Close
%>
以上的 ASP程式rs23.asp,在用户端使用浏览器,浏览执行的结果,显示代号重复的记录。
Union
Union可以合并多组查询的结果。
语法如下:
查询1 UNION [ALL] 查询2 [UNION [ALL]查询3 [ ... ]]
查询为一个SELECT表达式。
当您使用一个 UNION 运算时,不会返回重复的记录;若要返回所有的记录,您可以于UNION后加上ALL,加上ALL执行查询的速度比较快。
在一个UNION运算中的所有查询,字段数目必须相同。字段大小可以不同,字段资料类型也可以不同。
只有在第一个SELECT表达式中可使用别名,在其它SELECT表达式中被省略。
可以在每一个SELECT表达式中使用GROUP BY或HAVING子句,以统计查询的结果。
可以在最后一个SELECT表达式使用ORDER BY 子句,以指定查询的结果的排列顺序。
让我们看一个于ASP程式当中使用这个SQL指令的例子。
可以利用Union合并两组查询的结果,譬如ASP程式rs25.asp如下,[(SELECT 姓名,科目,分数 From 考试 Where 科目='算术' and 姓名='李四') Union (SELECT 姓名,科目,分数 From 考试 Where 科目='算术' and 姓名='张三')],使用Union合并两组SELECT查询的结果,一组为查询李四的算术成绩记录,另一组查询张三的算术成绩记录:
<%
Set conn1 = Server.CreateObject("ADODB.Connection")
conn1.Open "DBQ=" & Server.MapPath("ntopsamp.mdb") & ";Driver={Microsoft Access Driver (*.mdb)};DriverId=25;FIL=MS Access;"
Set rs2 = Server.CreateObject("ADODB.Recordset")
SqlStr = "(SELECT 姓名,科目,分数 From 考试 Where 科目='算术' and 姓名='李四') Union (SELECT 姓名,科目,分数 From 考试 Where 科目='算术' and 姓名='张三')"
rs2.Open SqlStr,conn1,1,1
Response.Write "<p>Union"
Do while not rs2.EOF
Response.Write "<BR>" & rs2("姓名") & " " & rs2("科目") & " 分数: " & rs2("分数")
rs2.MoveNext
Loop
rs2.Close
%>
以上的 ASP程式rs25.asp,在用户端使用浏览器,浏览执行的结果,显示李四和张三的算术分数记录。
JOIN连接组合两个表中的字段记录,包括三种:
INNER JOIN运算式:连接组合两个表中的字段记录。
LEFT JOIN运算式:连接组合两个表中的字段记录,并将包含了LEFT JOIN左边表中的全部记录。
RIGHT JOIN运算式:连接组合两个表中的字段记录,并将包含了RIGHT JOIN右边表中的全部记录。
INNER JOIN设定两个表相关连的运算式,以连接组合两个表中的字段记录。
INNER JOIN语法如下:
FROM 表1 INNER JOIN 表2 ON 表1.字段1 比较运算子 表2.字段2
两个表连接的字段,譬如 [表1.字段1=表2.字段2],必须具有相同的字段类型,但是字段名称不需要相同。
例如,自动编号字段类型可以连接Long 的字段类型,但是单精整数字段类型不能连接双精整数的字段类型。
比较运算子可为=、<、>、<=、>=、或<>。
JOIN连接的字段,不可以包含MEMO字段类型或OLE对象类型,否则会发生错误。
在一个JOIN表达式中,可以连结多个ON子句:
SELECT fields FROM 表1 INNER JOIN 表2 ON 表1.字段1 比较运算子 表2.字段1 AND ON 表1.字段2 比较运算子 表2.字段2) OR ON 表1.字段3 比较运算子 表2.字段3)
JOIN表达式中,可以为巢状式:
SELECT fields FROM 表1 INNER JOIN (表2 INNER JOIN [( ]表3 [INNER JOIN [( ] 表x [INNER JOIN ...)] ON 表3.字段3 比较运算子 表x.字段x)] ON 表2.字段2 比较运算子 表3.字段3) ON 表1.字段1 比较运算子 表2.字段2
在一个INNER JOIN中,可以包括巢状式的LEFT JOIN或RIGHT JOIN,但是在一个LEFT JOIN或RIGHT JOIN中不能包括巢状式的INNER JOIN。
让我们看一个于ASP程式当中使用这个SQL指令的例子。
可以利用Inner Join...On组合两个表中的记录,譬如ASP程式rs26.asp如下,[Select 销售.代号, 产品.名称, 产品.价格, 销售.数量 from 销售 Inner Join 产品 On 产品.代号 = 销售.代号],使用Inner Join...On设定两个表相关连的运算式,组合两个表中的记录:
<%
Set conn1 = Server.CreateObject("ADODB.Connection")
conn1.Open "DBQ="& Server.MapPath("ntopsamp.mdb") &";Driver={Microsoft Access Driver (*.mdb)};DriverId=25;FIL=MS Access;"
Set rs3 = Server.CreateObject("ADODB.Recordset")
sql = "Select 销售.代号, 产品.名称, 产品.价格, 销售.数量 from 销售 Inner Join 产品 On 产品.代号 = 销售.代号"
rs3.Open sql,conn1,1,1,1
%>
<TABLE COLSPAN=8 CELLPADDING=5 BORDER=0>
<TR>
<TD ALIGN=CENTER BGCOLOR="#800000"><FONT COLOR="#FFFFFF">代号</FONT></TD>
<TD ALIGN=CENTER BGCOLOR="#800000"><FONT COLOR="#FFFFFF">名称</FONT></TD>
<TD ALIGN=CENTER BGCOLOR="#800000"><FONT COLOR="#FFFFFF">价格</FONT></TD>
<TD ALIGN=CENTER BGCOLOR="#800000"><FONT COLOR="#FFFFFF">数量</FONT></TD>
</TR>
<% Do while not rs3.EOF %>
<TR>
<TD BGCOLOR="f7efde" ALIGN=CENTER><%= rs3(0)%></TD>
<TD BGCOLOR="f7efde" ALIGN=CENTER><%= rs3(1)%></TD>
<TD BGCOLOR="f7efde" ALIGN=CENTER><%= rs3(2)%></TD>
<TD BGCOLOR="f7efde" ALIGN=CENTER><%= rs3(3)%></TD>
</TR>
<%
rs3.MoveNext
Loop
rs3.Close
%>
</TABLE>
以上的 ASP程式rs26.asp,在用户端使用浏览器,浏览执行的结果,显示组合两个表中的记录,由于销售表中并没有名称字段和价格字段,通过 [Inner Join 产品 On 产品.代号 = 销售.代号] 找到产品表的名称字段和价格字段之资料。
上例使用Inner Join的语法:
Select 销售.代号, 产品.名称, 产品.价格, 销售.数量 from 销售 Inner Join 产品 On 产品.代号 = 销售.代号
结果与以下使用Where相同:
Select 销售.代号, 产品.名称, 产品.价格, 销售.数量 from 销售, 产品 Where 产品.代号 = 销售.代号
LEFT JOIN/RIGHT JOIN
INNER JOIN连接组合两个表中相关连的字段记录,为内部连接,您还可以使用:
使用LEFT JOIN运算:来建立一个左边外部连接。LEFT JOIN除了取得两个表中相关连的字段记录,并将包含了LEFT JOIN左边表中的全部记录,不管在右边表中是否有相符的记录。 使用RIGHT JOIN运算:建立一个右边外部连接。RIGHT JOIN除了取得两个表中相关连的字段记录,并将包含了RIGHT JOIN右边表中的全部记录,不管在左边表中是否有相符的记录。 例如,[销售LEFT JOIN 产品] 可以选取所有的销售记录。[销售RIGHT JOIN 产品] 可以选取所有的产品记录。
譬如利用Left Join...On除了取得两个表中相关连的字段记录,并包含了LEFT JOIN左边表中的全部记录, ASP程式rs26.asp如下,[Select 销售.代号, 产品.名称, 产品.价格, 销售.数量 from 销售 Left Join 产品 On 产品.代号 = 销售.代号],使用Left Join包含了LEFT JOIN左边销售表中的全部记录:
<%
Set conn1 = Server.CreateObject("ADODB.Connection")
conn1.Open "DBQ="& Server.MapPath("ntopsamp.mdb") &";Driver={Microsoft Access Driver (*.mdb)};DriverId=25;FIL=MS Access;"
'conn1.Open "driver={SQL Server};server=(Local);uid=sa;pwd=;database=NtopSamp"
Set rs3 = Server.CreateObject("ADODB.Recordset")
sql = "Select 销售.代号, 产品.名称, 产品.价格, 销售.数量 from 销售 Left Join 产品 On 产品.代号 = 销售.代号"
rs3.Open sql,conn1,1,1,1
%>
<TABLE COLSPAN=8 CELLPADDING=5 BORDER=0>
<TR>
<TD ALIGN=CENTER BGCOLOR="#800000"><FONT COLOR="#FFFFFF">代号</FONT></TD>
<TD ALIGN=CENTER BGCOLOR="#800000"><FONT COLOR="#FFFFFF">名称</FONT></TD>
<TD ALIGN=CENTER BGCOLOR="#800000"><FONT COLOR="#FFFFFF">价格</FONT></TD>
<TD ALIGN=CENTER BGCOLOR="#800000"><FONT COLOR="#FFFFFF">数量</FONT></TD>
</TR>
<% Do while not rs3.EOF %>
<TR>
<TD BGCOLOR="f7efde" ALIGN=CENTER><%= rs3(0)%></TD>
<TD BGCOLOR="f7efde" ALIGN=CENTER><%= rs3(1)%></TD>
<TD BGCOLOR="f7efde" ALIGN=CENTER><%= rs3(2)%></TD>
<TD BGCOLOR="f7efde" ALIGN=CENTER><%= rs3(3)%></TD>
<%
rs3.MoveNext
Loop
rs3.Close
%>
</TABLE>
以上的ASP程式rs26.asp,在用户端使用浏览器,浏览执行的结果,显示包含了LEFT JOIN左边销售表中的全部记录,由于销售表中并没有名称字段和价格字段,代号4的销售记录,通过 [销售 Left Join 产品 On 产品.代号 = 销售.代号] 找到产品表的名称字段和价格字段之资料,其他的代号并没有找到名称字段和价格字段之资料。
利用 Right Join...On除了取得两个表中相关连的字段记录,并包含了RIGHT JOIN右边表中的全部记录,譬如ASP程式rs26.asp如下,[Select 销售.代号, 产品.名称, 产品.价格, 销售.数量 from 销售 Right Join 产品 On 产品.代号 = 销售.代号],使用Right Join包含了RIGHT JOIN右边产品表中的全部记录:
<%
Set conn1 = Server.CreateObject("ADODB.Connection")
conn1.Open "DBQ="& Server.MapPath("ntopsamp.mdb") &";Driver={Microsoft Access Driver (*.mdb)};DriverId=25;FIL=MS Access;"
Set rs3 = Server.CreateObject("ADODB.Recordset")
sql = "Select 销售.代号, 产品.名称, 产品.价格, 销售.数量 from 销售 Right Join 产品 On 产品.代号 = 销售.代号"
rs3.Open sql,conn1,1,1,1
%>
<TABLE COLSPAN=8 CELLPADDING=5 BORDER=0>
<TR>
<TD ALIGN=CENTER BGCOLOR="#800000"><FONT COLOR="#FFFFFF">代号</FONT></TD>
<TD ALIGN=CENTER BGCOLOR="#800000"><FONT COLOR="#FFFFFF">名称</FONT></TD>
<TD ALIGN=CENTER BGCOLOR="#800000"><FONT COLOR="#FFFFFF">价格</FONT></TD>
<TD ALIGN=CENTER BGCOLOR="#800000"><FONT COLOR="#FFFFFF">数量</FONT></TD>
</TR>
<% Do while not rs3.EOF %>
<TR>
<TD BGCOLOR="f7efde" ALIGN=CENTER><%= rs3(0)%></TD>
<TD BGCOLOR="f7efde" ALIGN=CENTER><%= rs3(1)%></TD>
<TD BGCOLOR="f7efde" ALIGN=CENTER><%= rs3(2)%></TD>
<TD BGCOLOR="f7efde" ALIGN=CENTER><%= rs3(3)%></TD>
<%
rs3.MoveNext
Loop
rs3.Close
%>
</TABLE>
以上的 ASP程式rs26.asp,在用户端使用浏览器,浏览执行的结果,显示包含了RIGHT JOIN右边产品表中的全部记录,由于产品表中并没有数量字段,代号4的产品记录,通过 [销售 Right Join 产品 On 产品.代号 = 销售.代号] 找到销售表的数量字段之资料,其他的代号并没有找到数量字段之资料。
搞清楚了吗? 子查询
在一个SELECT、SELECT...INTO、INSERT...INTO、DELETE、或UPDATE 表达式中,可以包括SELECT表达式,这个SELECT表达式叫做子查询(sub query)。
您可以使用三种语法建立子查询:
表达式 [ANY | ALL | SOME] (子查询)
表达式 [NOT] IN (子查询)
[NOT] EXISTS (子查询)
子查询的一个SELECT表达式,与一般SELECT表达式的语法相同,必须包括在括号之中。
您可以使用子查询来替代SELECT表达式的运算式,或在WHERE或 HAVING子句中的运算式。
关键字ANY和SOME的意义相同,用来选择符合子查询的任何记录的比较条件。譬如下例将返回产品中单价大于订单中任何数量大于100的记录:
SELECT * FROM 产品
WHERE 单价 > ANY
(SELECT 单价 FROM 订单
WHERE 数量 > 100)
关键字ALL,用来选择符合子查询的所有记录的比较条件。
譬如在上例中将ANY改为ALL,将返回产品中单价大于订单中所有数量大于100的记录。
关键字IN 述语来撷取在主查询中且只有在子查询之中包含相同值的某些记录。下列范例会返回以百分之 25 或更高的折扣卖出的所有产品:
关键字IN,用来选择在子查询之中的记录。譬如下例将返回订单中数量 > 100的记录:
SELECT * FROM 产品
WHERE 产品代号 IN
(SELECT 产品代号 FROM 订单
WHERE 数量 > 100)
相反地,关键字NOT IN,用来选择不在子查询之中的记录。
在true/false比较中,可以使用EXISTS关键字,来决定子查询是否会返回任何的记录。
关键字ALL的ASP例子,譬如ASP程式rs24.asp如下,[SELECT 姓名,科目,分数 From 考试 Where 科目 = '算术' and 分数 >= All (SELECT 分数 From 考试 Where 科目='算术' and 姓名='张三')] 找出分数大于或等于张三的算术考试的算术记录:
<%
Set conn1 = Server.CreateObject("ADODB.Connection")
conn1.Open "DBQ=" & Server.MapPath("ntopsamp.mdb") & ";Driver={Microsoft Access Driver (*.mdb)};DriverId=25;FIL=MS Access;"
Set rs2 = Server.CreateObject("ADODB.Recordset")
SqlStr = "SELECT 姓名,科目,分数 From 考试 Where 科目 = '算术' and 分数 >= All (SELECT 分数 From 考试 Where 科目='算术' and 姓名='张三')"
rs2.Open SqlStr,conn1,1,1
Response.Write "<p>All高于张三算术所有分数"
Do while not rs2.EOF
Response.Write "<BR>" & rs2("姓名") & " " & rs2("科目") & " 分数: " & rs2("分数")
rs2.MoveNext
Loop
rs2.Close
%>
以上的 ASP程式rs24.asp,在用户端使用浏览器,浏览执行的结果,显示分数大于或等于张三的算术考试的算术记录。
Any
关键字ANY用来选择符合子查询的任何记录的比较条件,譬如ASP程式rs24.asp如下,[SELECT 姓名,科目,分数 From 考试 Where 科目 = '算术' and 分数 >= Any (SELECT 分数 From 考试 Where 科目='算术' and 姓名='张三')] 找出分数大于或等于张三任何算术分数的记录:
<%
Set conn1 = Server.CreateObject("ADODB.Connection")
conn1.Open "DBQ=" & Server.MapPath("ntopsamp.mdb") & ";Driver={Microsoft Access Driver (*.mdb)};DriverId=25;FIL=MS Access;"
Set rs2 = Server.CreateObject("ADODB.Recordset")
SqlStr = "SELECT 姓名,科目,分数 From 考试 Where 科目 = '算术' and 分数 >= Any (SELECT 分数 From 考试 Where 科目='算术' and 姓名='张三')"
rs2.Open SqlStr,conn1,1,1
Response.Write "<p>Any高于张三算术任何分数"
Do while not rs2.EOF
Response.Write "<BR>" & rs2("姓名") & " " & rs2("科目") & " 分数: " & rs2("分数")
rs2.MoveNext
Loop
rs2.Close %>
以上的 ASP程式rs24.asp,在用户端使用浏览器,浏览执行的结果,显示分数大于或等于张三任何算术分数的记录。
Some
关键字SOME和ANY的意义相同,用来选择符合子查询的任何记录的比较条件,譬如ASP程式rs24.asp如下,[SELECT 姓名,科目,分数 From 考试 Where 科目 = '算术' and 分数 >= Some (SELECT 分数 From 考试 Where 科目='算术' and 姓名='张三')] 找出分数大于或等于张三任何算术分数的记录:
<%
Set conn1 = Server.CreateObject("ADODB.Connection")
conn1.Open "DBQ=" & Server.MapPath("ntopsamp.mdb") & ";Driver={Microsoft Access Driver (*.mdb)};DriverId=25;FIL=MS Access;"
Set rs2 = Server.CreateObject("ADODB.Recordset")
SqlStr = "SELECT 姓名,科目,分数 From 考试 Where 科目 = '算术' and 分数 >= Some (SELECT 分数 From 考试 Where 科目='算术' and 姓名='张三')"
rs2.Open SqlStr,conn1,1,1
Response.Write "<p>Some高于张三算术任何分数"
Do while not rs2.EOF
Response.Write "<BR>" & rs2("姓名") & " " & rs2("科目") & " 分数: " & rs2("分数")
rs2.MoveNext
Loop
rs2.Close
%>
以上的 ASP程式rs24.asp,在用户端使用浏览器,浏览执行的结果,显示分数大于或等于张三任何算术分数的记录。
Select...Into
Select...Into将查询的结果,建立一个产生的表。
语法如下:
SELECT 字段1[,字段2[, ...]] INTO 新表 [IN 外部表] FROM 表
新表的名称不可与现存表的名称相同,否则将会发生错误。
Select...Into所建立的新表,其字段的资料类型及大小与所查询的表相同。
让我们看一个于ASP程式当中使用这个SQL指令的例子。
譬如ASP程式rs9.asp如下,[Select * Into 电脑 From 产品 Where 种类 = '电脑'] 将 [产品] 表中所有 [种类] 为 [电脑] 的纪录产生一个新的 [电脑] 表:
<%
Set conn1 = Server.CreateObject("ADODB.Connection")
conn1.Open "DBQ="& Server.MapPath("ntopsamp.mdb") &";Driver={Microsoft Access Driver (*.mdb)};DriverId=25;FIL=MS Access;"
sql = "Select * Into 电脑 From 产品 Where 种类 = '电脑'"
Set a = conn1.Execute(sql)
Set rs3 = Server.CreateObject("ADODB.Recordset")
sql = "Select * from 电脑"
rs3.Open sql,conn1,1,1,1
%>
<TABLE COLSPAN=8 CELLPADDING=5 BORDER=0>
<TR>
<TD ALIGN=CENTER BGCOLOR="#800000"><FONT COLOR="#FFFFFF">代号</FONT></TD>
<TD ALIGN=CENTER BGCOLOR="#800000"><FONT COLOR="#FFFFFF">名称</FONT></TD>
<TD ALIGN=CENTER BGCOLOR="#800000"><FONT COLOR="#FFFFFF">价格</FONT></TD>
<TD ALIGN=CENTER BGCOLOR="#800000"><FONT COLOR="#FFFFFF">数量</FONT></TD>
</TR>
<% Do while not rs3.EOF %>
<TR>
<TD BGCOLOR="f7efde" ALIGN=CENTER><%= rs3("代号")%></TD>
<TD BGCOLOR="f7efde" ALIGN=CENTER><%= rs3("名称")%></TD>
<TD BGCOLOR="f7efde" ALIGN=CENTER><%= rs3("价格")%></TD>
<TD BGCOLOR="f7efde" ALIGN=CENTER><%= rs3("数量")%></TD>
</TR>
<%
rs3.MoveNext
Loop
rs3.Close
%>
</TABLE>
以上的 ASP程式rs9.asp,在用户端使用浏览器,浏览执行的结果,显示新 [电脑] 表的记录。 |