求一份比较基础的Access的SQL命令大全。

SQL语句大全

一、基础

1、说明：创建数据库

Create DATABASE database-name

2、说明：删除数据库

drop database dbname

3、说明：备份sql server

--- 创建 备份数据的 device

USE master

EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat'

--- 开始 备份

BACKUP DATABASE pubs TO testBack

4、说明：创建新表

create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)

根据已有的表创建新表：

A：create table tab_new like tab_old (使用旧表创建新表)

B：create table tab_new as select col1,col2… from tab_old definition only

5、说明：删除新表

drop table tabname

6、说明：增加一个列

Alter table tabname add column col type

注：列增加后将不能删除。DB2中列加上后数据类型也不能改变，唯一能改变的是增加varchar类型的长度。

7、说明：添加主键： Alter table tabname add primary key(col)

说明：删除主键： Alter table tabname drop primary key(col)

8、说明：创建索引：create [unique] index idxname on tabname(col….)

删除索引：drop index idxname on tabname

注：索引是不可更改的，想更改必须删除重新建。

9、说明：创建视图：create view viewname as select statement

删除视图：drop view viewname

10、说明：几个简单的基本的sql语句

选择：select * from table1 where 范围

插入：insert into table1(field1,field2) values(value1,value2)

删除：delete from table1 where 范围

更新：update table1 set field1=value1 where 范围

查找：select * from table1 where field1 like ’%value1%’ （所有包含‘value1’这个模式的字符串）---like的语法很精妙，查资料!

排序：select * from table1 order by field1,field2 [desc]

总数：select count as totalcount from table1

求和：select sum(field1) as sumvalue from table1

平均：select avg(field1) as avgvalue from table1

最大：select max(field1) as maxvalue from table1

最小：select min(field1) as minvalue from table1[separator]

11、说明：几个高级查询运算词

A： UNION 运算符

UNION 运算符通过组合其他两个结果表（例如 TABLE1 和 TABLE2）并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时（即 UNION ALL），不消除重复行。两种情况下，派生表的每一行不是来自 TABLE1 就是来自 TABLE2。

B： EXCEPT 运算符

EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL)，不消除重复行。

C： INTERSECT 运算符

INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL)，不消除重复行。

注：使用运算词的几个查询结果行必须是一致的。

12、说明：使用外连接

A、left outer join：

左外连接（左连接）：结果集既包括连接表的匹配行，也包括左连接表的所有行。

SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c

B：right outer join:

右外连接(右连接)：结果集既包括连接表的匹配连接行，也包括右连接表的所有行。

C：full outer join：

全外连接：不仅包括符号连接表的匹配行，还包括两个连接表中的所有记录。

二、提升

1、说明：复制表(只复制结构,源表名：a 新表名：b) (Access可用)

法一：select * into b from a where 1<>1

法二：select top 0 * into b from a

2、说明：拷贝表(拷贝数据,源表名：a 目标表名：b) (Access可用)

insert into b(a, b, c) select d,e,f from b

3、说明：跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用)

insert into b(a, b, c) select d,e,f from b in ‘具体数据库’ where 条件

例子：..from b in '"&Server.MapPath("."&"\data.mdb" &"' where..

4、说明：子查询(表名1：a 表名2：b)

select a,b,c from a where a IN (select d from b 或者: select a,b,c from a where a IN (1,2,3)

5、说明：显示文章、提交人和最后回复时间

select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b

6、说明：外连接查询(表名1：a 表名2：b)

select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c

7、说明：在线视图查询(表名1：a

select * from (Select a,b,c FROM a) T where t.a >1

8、说明：between的用法,between限制查询数据范围时包括了边界值,not between不包括

select * from table1 where time between time1 and time2

select a,b,c, from table1 where a not between 数值1 and 数值2

9、说明：in 的使用方法

select * from table1 where a [not] in (‘值1’,’值2’,’值4’,’值6’)

10、说明：两张关联表，删除主表中已经在副表中没有的信息

delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1

11、说明：四表联查问题：

select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where .....

12、说明：日程安排提前五分钟提醒

SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5

13、说明：一条sql 语句搞定数据库分页

select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段

14、说明：前10条记录

select top 10 * form table1 where 范围

15、说明：选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.)

select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b)

16、说明：包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表

(select a from tableA except (select a from tableB) except (select a from tableC)

17、说明：随机取出10条数据

select top 10 * from tablename order by newid()

18、说明：随机选择记录

select newid()

19、说明：删除重复记录

Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...)

20、说明：列出数据库里所有的表名

select name from sysobjects where type='U'

21、说明：列出表里的所有的

select name from syscolumns where id=object_id('TableName')

22、说明：列示type、vender、pcs字段，以type字段排列，case可以方便地实现多重选择，类似select 中的case。

select type,sum(case vender when 'A' then pcs else 0 end),sum(case vender when 'C' then pcs else 0 end),sum(case vender when 'B' then pcs else 0 end) FROM tablename group by type

显示结果：

type vender pcs

电脑 A 1

电脑 A 1

光盘 B 2

光盘 A 2

手机 B 3

手机 C 3

23、说明：初始化表table1

TRUNCATE TABLE table1

24、说明：选择从10到15的记录

select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc三、技巧

1、1=1，1=2的使用，在SQL语句组合时用的较多

“where 1=1” 是表示选择全部 “where 1=2”全部不选，

如：

if @strWhere !='

begin

set @strSQL = 'select count(*) as Total from [' + @tblName + '] where ' + @strWhere

end

else

begin

set @strSQL = 'select count(*) as Total from [' + @tblName + ']'

end

我们可以直接写成

set @strSQL = 'select count(*) as Total from [' + @tblName + '] where 1=1 安定 '+ @strWhere

2、收缩数据库

--重建索引

DBCC REINDEX

DBCC INDEXDEFRAG

--收缩数据和日志

DBCC SHRINKDB

DBCC SHRINKFILE

3、压缩数据库

dbcc shrinkdatabase(dbname)

4、转移数据库给新用户以已存在用户权限

exec sp_change_users_login 'update_one','newname','oldname'

go

5、检查备份集

RESTORE VERIFYONLY from disk='E:\dvbbs.bak'

6、修复数据库

Alter DATABASE [dvbbs] SET SINGLE_USER

GO

DBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK

GO

Alter DATABASE [dvbbs] SET MULTI_USER

GO

7、日志清除

SET NOCOUNT ON

DECLARE @LogicalFileName sysname,

@MaxMinutes INT,

@NewSize INT

USE tablename -- 要 *** 作的数据库名

Select @LogicalFileName = 'tablename_log', -- 日志文件名

@MaxMinutes = 10, -- Limit on time allowed to wrap log.

@NewSize = 1 -- 你想设定的日志文件的大小(M)

-- Setup / initialize

DECLARE @OriginalSize int

Select @OriginalSize = size

FROM sysfiles

Where name = @LogicalFileName

Select 'Original Size of ' + db_name() + ' LOG is ' +

CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' +

CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB'

FROM sysfiles

Where name = @LogicalFileName

Create TABLE DummyTrans

(DummyColumn char (8000) not null)

DECLARE @Counter INT,

@StartTime DATETIME,

@TruncLog VARCHAR(255)

Select @StartTime = GETDATE(),

@TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY'

DBCC SHRINKFILE (@LogicalFileName, @NewSize)

EXEC (@TruncLog)

-- Wrap the log if necessary.

WHILE @MaxMinutes >DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired

AND @OriginalSize = (Select size FROM sysfiles Where name = @LogicalFileName)

AND (@OriginalSize * 8 /1024) >@NewSize

BEGIN -- Outer loop.

Select @Counter = 0

WHILE ((@Counter <@OriginalSize / 16) AND (@Counter <50000))

BEGIN -- update

Insert DummyTrans VALUES ('Fill Log')

Delete DummyTrans

Select @Counter = @Counter + 1

END

EXEC (@TruncLog)

END

Select 'Final Size of ' + db_name() + ' LOG is ' +

CONVERT(VARCHAR(30),size) + ' 8K pages or ' +

CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'

FROM sysfiles

Where name = @LogicalFileName

Drop TABLE DummyTrans

SET NOCOUNT OFF

8、说明：更改某个表

exec sp_changeobjectowner 'tablename','dbo'

9、存储更改全部表

Create PROCEDURE dbo.User_ChangeObjectOwnerBatch

@OldOwner as NVARCHAR(128),

@NewOwner as NVARCHAR(128)

AS

DECLARE @Name as NVARCHAR(128)

DECLARE @Owner as NVARCHAR(128)

DECLARE @OwnerName as NVARCHAR(128)

DECLARE curObject CURSOR FOR

select 'Name' = name,

'Owner' = user_name(uid)

from sysobjects

where user_name(uid)=@OldOwner

order by name

OPEN curObject

FETCH NEXT FROM curObject INTO @Name, @Owner

WHILE(@@FETCH_STATUS=0)

BEGIN

if @Owner=@OldOwner

begin

set @OwnerName = @OldOwner + '.' + rtrim(@Name)

exec sp_changeobjectowner @OwnerName, @NewOwner

end

-- select @name,@NewOwner,@OldOwner

FETCH NEXT FROM curObject INTO @Name, @Owner

END

close curObject

deallocate curObject

GO

10、SQL SERVER中直接循环写入数据

declare @i int

set @i=1

while @i<30

begin

insert into test (userid) values(@i)

set @i=@i+1

end 可能有点长，应该对你有帮助

每次通过单击 Access 程序图标或“开始”菜单上的程序名称来启动 Access 时，实际上是在运行 Msaccess.exe 命令，但您通常不需要键入该命令，甚至看不到该命令。

通过向 Msaccess.exe 命令中添加称为“开关”的子命令，可以更改程序启动方式的某些方面。开关的形式为主命令后加一个空格，再跟斜杠 (/) 和开关的名称。开关有时候后面跟有另一个空格，然后是称为参数的一个或多个特定指令，这些指令可为程序提供有关如何运行 Msaccess.exe 命令的更多信息。例如，以下命令指示 Access 启动，然后打开指定的文件以用于只读用途：

msaccess.exe /ro "c:\My Folder\My Database.accdb"

在此示例中，切换/ro，并且该参数为"c:\My Folder\My Database.accdb"。注意到的文件路径这种情况下设置用引号引起来因为它包含空格。文件路径不包含空格，则不需要引号。

在 Office Access 2007 中，可以省略 msaccess.exe，以完整的文件路径后跟开关和参数来启动该命令。例如，可按如下方式键入上述命令：

"c:\My Folder\My Database.accdb" /ro

详细内容参看下面链接

https://support.office.com/zh-cn/article/用于-Access-的命令行开关-558cfe1d-3c98-4292-bee8-1f5df9702bf1?CTT=5&origin=HA010342116&CorrelationId=300895d4-6820-46bf-994c-5f4acf552e91&ui=zh-CN&rs=zh-CN&ad=CN&ocmsassetID=HA010166605

《ASP编程大全》在此谁敢不从！

1.提取字段列表

//<%

Set cn=Server.CreateObject("ADODB.Connection")

cn.Open "Provider=SQLOLEDBData Source=localhost" &_

"Initial Catalog=pubsUser ID=saPassword="

Set rs=cn.Execute("Select * from authors")

While Not rs.EOF

Response.Write rs("au_fname")

rs.MoveNext

Wend

rs.Close

cn.Close

//%>

2.返回数据集函数

Public Function GetData() As ADODB.Recordset

Dim cn As ADODB.Connection

Dim rs As ADODB.Recordset

Set cn=CreateObject("ADODB.Connection")

cn.Open "Provider=SQLOLEDBData Source=localhost" &_

"Initial Catalog=pubsUser ID=saPassword="

Set rs=CreateObject("ADODB.Connection")

rs.LockType=adLockBatchOptimistic

rs.CursorLocation=adUseClient

rs.CursorType=adOpenStatic

rs.Open "Select * from authors",cn

Set rs.ActiveConnection=Nothing

cn.Close

Set cn=Nothing

Set GetData=rs

End Function

3.从XML文件中读取数据

<!-- Metadata type="typelib" file="c:\program files\common files\system\ado\msado15.dll"-->

<%

'response.write admoderead &" " &adopenstreamfromurl

'response.End()

set rs = server.CreateObject("adodb.recordset")

Set stmData = Server.CreateObject("ADODB.Stream")

stmData.Charset="gb2312"'这一句很重要，否则会报一个错误。Recordset cannot be created from the specified source. The source file or stream must contain Recordset data in XML or ADTG format.移山祝大家好运^_^

xmbm = request.QueryString("xmbm")

if xmbm = "" then xmbm = "110100001"

Dim result

dim xml, XMLlength

Set DCOMServer = CreateObject("RemObjects.SDK.COMServer")

DCOMServer.MessageType = "TROBINMessage"

DCOMServer.ChannelType = "TROWinInetHTTPChannel"

DCOMServer.SetChannelProperty "TargetURL", "http://192.168.1.119:8089/BIN"

Set DCOMService = DCOMServer.CreateService("DCOMService")

xml = cstr("<?xml version="&chr(34)&"1.0"&chr(34)&" encoding="&chr(34)&"gb2312"&chr(34)&" ?>" &chr(13)&chr(10))

xml = xml &cstr(DCOMService.GetItem(xmbm))'用于生成XML主体的DCOM服务，实际中请灵活运用。

Set MegaDemoService = nothing

'response.write xml

'response.End()

stmData.Open

stmData.WriteText xml

stmData.SetEOS

'stmData.SaveToFile server.MapPath("zzzz"&session.SessionID&".xml"),2

'response.End()

stmData.Position = 0

rs.open stmData

if not rs.eof then

response.write "<table border = 1 cellpadding=5 cellspacing=0 bordercolor=black style='border-collapse:collapse'><tr bgcolor='#ddddeee'>"

for each fld in rs.fields

response.write "<td>" &fld.name &"</td>"

next

response.write "</tr>"

do while not rs.eof

response.write "<tr>"

for each fld in rs.fields

response.write "<td>" &fld.value &"</td>"

next

response.write "</tr>"

rs.movenext

loop

end if

rs.close

set rs = nothing

%>

自动显示RecordSet的表格

<script language=jscript runat=server>

// ************************************************************************

// Script Compont Object Model

// Design for Active Server Pages

//

// Copyright 2003 Version 1.0

// Made by 尹曙光

// ************************************************************************

//用于显示一个recordset中的数据,可以指定颜色和样式

//接口函数

function createJDBList(){

var objJDBlist=new JDBlist

objJDBlist.create()

return objJDBlist

}

//类体定义

function JDBlist(){

this.AdoRecordSet

this.Width

this.Border

this.Cellspacing

this.Cellpadding

this.Class

this.TitleClass

this.FirstColor

this.SecondColor

this.create=_JDBlist_Create

this.display=_JDBlist_Display

}

//方法实现

function _JDBlist_Create(){

this.AdoRecordSet=""

this.Width="100%"

this.Border="1"

this.Cellspacing="0"

this.Cellpadding="1"

this.Class=""

this.TitleClass=''

this.FirstColor=''

this.SecondColor=''

}

function _JDBlist_Display(){

var htmlDSO = ''

var nFields = this.AdoRecordSet.Fields.Count

if (nFields)

{

var n = 0

htmlDSO += '<' + 'table width="'+this.Width+'" id="'+this.Id+'" Cellspacing="'+this.Cellspacing+'" Cellpadding="'+this.Cellpadding+'" Border="'+this.Border+'" Class="'+this.Class+'">\n'

htmlDSO += '<' + 'tr>'

for (var i=0i <nFieldsi++)

{

htmlDSO += '<' + 'td class="'+this.TitleClass+'">' + this.AdoRecordSet(i).name + '<' + '/td>'

}

htmlDSO += '<' + '/tr>\n'

if (!(this.AdoRecordSet.EOF&&this.AdoRecordSet.Bof))

this.AdoRecordSet.MoveFirst()

else

htmlDSO += '<' + 'td colspan="'+nFields+'" align="center">没有数据！<' + '/td>'

var rcc=0

while (!this.AdoRecordSet.EOF)

{ // add each record to table

if (rcc%2==0)

htmlDSO += '<' + 'tr bgcolor="'+this.FirstColor+'">'

else

htmlDSO += '<' + 'tr bgcolor="'+this.SecondColor+'">'

for (var i=0i <nFieldsi++)

{

htmlDSO += '<' + 'td >' + this.AdoRecordSet(i).value + '<' + '/td>'

}

htmlDSO += '<' + '/tr>\n'

this.AdoRecordSet.MoveNext()

rcc++

}

htmlDSO += '<' + '/table>'

}

Response.Write(htmlDSO)

}

</script>

4.把数据写入XML文件

<%

class autoXML

Private xmldoc

Private xmlfile

Sub Class_Initialize()

set xmldoc = Server.CreateObject("Microsoft.XMLDOM")

xmldoc.async = fasle

End Sub

Sub Class_Terminate()

set xmldoc = Nothing

End Sub

Public Property Let filePath(str)

xmlfile = str

End Property

Sub getRs(rs)

dim xmlhead

dim pNode,sNode

dim aNode,tNode

dim tableName

dim rf

dim i

set xmlhead = xmldoc.createProcessingInstruction("xml","version=""1.0"" encoding=""GB2312""")

xmldoc.insertBefore xmlhead,xmldoc.childNodes(0)

tableName = split(rs.Source,chr(32))

set pNode = xmldoc.CreateElement(tableName(3))

xmldoc.appendChild pNode

redim aNode(rs.Fields.Count-1)

do while not rs.eof

set sNode = xmldoc.CreateElement(tableName(3)&"list")

pNode.appendChild sNode

for i=0 to rs.Fields.Count-1

if rs.Fields(i).Name=tableName(3) then

tNode = rs.Fields(i).Name&"Child"

else

tNode = rs.Fields(i).Name

end if

set aNode(i) = xmldoc.CreateElement(tNode)

sNode.appendChild aNode(i)

if rs.Fields(i).value<>"" then

aNode(i).text = rs.Fields(i).value

end if

next

rs.movenext

loop

End Sub

Function saveFile()

on error resume next

xmldoc.save xmlfile

saveFile = (err.number=0)

End Function

End Class

%>

调用，把调用recordset就可以了。

<%

dim conn

set conn = Server.CreateObject("adodb.connection")

conn.open "Provider=Microsoft.jet.oledb.4.0data source="&Server.MapPath("db.mdb")

dim rs,sql

set rs = Server.CreateObject("adodb.recordset")

sql = "select * from news"

rs.Open sql,conn,3,2

dim ixml

dim xf

set ixml = new autoXML

xf = "test.xml"

ixml.filePath = Server.MapPath(xf)

ixml.getRs(rs)

if ixml.saveFile() then

response.write "<a href=""" &Server.URLEncode(xf) &""">保存完毕</a>"

else

response.write "保存文件错误"

end if

set ixml = Nothing

rs.close

set rs = Nothing

%>

4.1

方法二：

<%

dim strConn,conn,cmd,xmldoc

dim strSql,strQuery,stm

strTableName="test1"

strConn="Provider=SQLOLEDB.1Persist Security Info=FalseUser ID=saInitial Catalog=testData Source=SHISMpassword="

set conn=server.CreateObject("adodb.connection")

conn.CursorLocation = 3

conn.Open strConn

function getXml(strQuery,conn)

dim xmldoc,cmd,stm

set cmd=server.CreateObject("adodb.command")

cmd.ActiveConnection=conn

strQuery="<ROOT xmlns:sql='urn:schemas-microsoft-com:xml-sql'><sql:query>" & strQuery & "</sql:query></ROOT>"

set stm=server.CreateObject("adodb.stream")

stm.Open

stm.WriteText strQuery,0

stm.Position=0

cmd.CommandStream=stm

cmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}"

set xmldoc=server.CreateObject("msxml2.domdocument")

cmd.Properties("Output Stream") = xmldoc

cmd.Execute ,,1024

getXml=xmldoc.xml

set cmd=nothing

set xmldoc=nothing

set stm=nothing

end function

strQuery="select * from test1 for xml auto"

Response.Write getXml(strQuery,conn)

%>

4.2

<script language="jscript" runat="server">

var conn = new ActiveXObject("adodb.connection")

conn.open("Provider=Microsoft.jet.oledb.4.0data source="+Server.MapPath("db.mdb"))

var sql = "select distinct province from allcity"

var rs = conn.Execute(sql)

var xmldoc = new ActiveXObject("Microsoft.XMLDOM")

xmldoc.async = false

var xmlhead = xmldoc.createProcessingInstruction('xml','version="1.0" encoding="GB2312"')

xmldoc.insertBefore(xmlhead,xmldoc.childNodes(0))

var root = xmldoc.createElement("root")

xmldoc.appendChild(root)

do{

var province = xmldoc.createElement("province")

province.text = rs("province")

root.appendChild(province)

var osql = "select distinct city from allcity where province='"+rs("province")+"'"

var ors = conn.Execute(osql)

do{

var city = xmldoc.createElement("city")

city.text = ors("city")

province.appendChild(city)

var mysql = "select college from allcity where city='"+ors("city")+"'"

var myrs = conn.Execute(mysql)

do{

var college = xmldoc.createElement("college")

college.text = myrs("college")

city.appendChild(college)

myrs.movenext()

}while(!(myrs.eof||myrs.bof))

myrs.Close()

ors.movenext()

}while(!(ors.eof||ors.bof))

ors.Close()

rs.movenext()

}while(!(rs.eof||rs.bof))

xmldoc.save(Server.MapPath("allcity.xml"))

Response.Write("创建成功！")

</script>

5.遍历XML文档

<%@ Language=VBScript %>

<% Option Explicit %>

<table border="1"><tr><th>Name</th><th>Number of Songs</th></tr>

<%

Dim objParser,objRoot,objNode,i

Set objParser=Server.CreateObject("MSXML.DOMDocument")

objParser.Load(Server.MapPath(%%1)) //"albums.xml"

Set objRoot=objParser.documentElement

For i=0 To (objRoot.childNodes.length-1)

If objRoot.childNodes.Item(i).baseName="album" Then

Set objNode=objRoot.childNodes.Item(i)

Response.Write "<tr><td>"

Response.Write objNode.getAttribute("name") &"</td><td align='center'>"

Response.Write objNode.childNodes.length

End if

Next

%>

</td></tr>

</table>

6.搜索XML元素

<%@ Language=VBScript %>

<% Option Explicit %>

//<%

Dim objDocument,objRoot,objNode,objNodeList

Set objDocument=Server.CreateObject("MSXML.DOMDocument")

objDocument.Load(Server.MapPath(%%1)) //"albums.xml"

Set objRoot=objDocument.docmentElement

Set objNodeList=objRoot.SelectNodes("//ablum[@artist='" &Request.Form("artist") &"']")

If objNodeList.length=0 Then

Response.Write "No matches"

Else

Response.Write "<table border='1'><tr><th>Album</th><th>Year</th></tr>"

For each objNode in objNodeList

Response.Write "<tr><td>" &objNode.getAttribute("name")

Response.Write "</td><td>" &objNode.getAttribute("releaseDate") &"</td></tr>"

Next

Response.Write "</table>"

End If

End If

//%>

7.下载文件

<%@ Language=VBScript %>

<% Option Explicit %>

//<%

call downloadFile(replace(Request("file"),"",""))

Function downloadFile(strFile)

' make sure you are on the latest MDAC version for this to work

' get full path of specified file

strFilename = server.MapPath(strFile)

' clear the buffer

Response.Buffer = True

Response.Clear

' create stream

Set s = Server.CreateObject("ADODB.Stream")

s.Open

' Set as binary

s.Type = 1

' load in the file

on error resume next

' check the file exists

Set fso = Server.CreateObject("Scripting.FileSystemObject")

if not fso.FileExists(strFilename) then

Response.Write("<h1>Error:</h1>" &strFilename &" does not exist<p>")

Response.End

end if

' get length of file

Set f = fso.GetFile(strFilename)

intFilelength = f.size

s.LoadFromFile(strFilename)

if err then

Response.Write("<h1>Error: </h1>" &err.Description &"<p>")

Response.End

end if

' send the headers to the users browser

Response.AddHeader "Content-Disposition", "attachmentfilename=" &f.name

Response.AddHeader "Content-Length", intFilelength

Response.CharSet = "UTF-8"

Response.ContentType = "application/octet-stream"

' output the file to the browser

Response.BinaryWrite s.Read

Response.Flush

' tidy up

s.Close

Set s = Nothing

End Function

//%>

8.调用存储过程查询

<%@ Language=VBScript %>

<% Option Explicit %>

//<%

dim db,conn,myconn

db="/database/product.mdb"

Set conn = Server.CreateObject("ADODB.Connection")

myconn="Provider=Microsoft.Jet.OLEDB.4.0Data Source=" &Server.MapPath(""&db&"")

conn.Open MyConn

dim rs

dim sql

dim keyword

dim choose

keyword=request("keyword") '接收页面传送的值

choose=request("choose")

set rs=server.createobject("adodb.recordset")

sql="exec tse" '用exec执行tse存储过程，把keyword，choose给存储过程传递参数

rs.open sql,conn,1,1

if rs.eof and rs.bof then

response.write("没有任何记录！")

response.end

end if

response.write"搜索到的记录如下:<br><br>"

do until rs.eof

response.write""&rs("id")&":"&rs("title")&"" '打印出文章的ID和标题

response.write"<br><br>"

rs.movenext

loop

'打扫战场

rs.close

conn.close

set rs=nothing

set conn = nothing

//%>

9.参数存储过程查询

'存储过程：

'在SQL中建立dbo.tse存储过程

CREATE PROCEDURE [dbo].[tse]

@keyword varchar(20)=null, '定义查询的关键字

@choose int=null'定义查询的类型（1为查询列title,其他为content）

as

if @choose=1

select * from web where title like @keyword + '%'

else

select * from web where content like @keyword + '%'

return

GO

'list.asp页

<%@ Language=VBScript %>

<% Option Explicit %>

//<%

dim db,conn,myconn

db="/database/product.mdb"

Set conn = Server.CreateObject("ADODB.Connection")

myconn="Provider=Microsoft.Jet.OLEDB.4.0Data Source=" &Server.MapPath(""&db&"")

conn.Open MyConn

dim rs

dim sql

dim keyword

dim choose

keyword=request("keyword") '接收页面传送的值

choose=request("choose")

set rs=server.createobject("adodb.recordset")

sql="exec tse '"&keyword&"',"&choose&"" '用exec执行tse存储过程，把keyword，choose给存储过程传递参数

rs.open sql,conn,1,1

if rs.eof and rs.bof then

response.write("没有任何记录！")

response.end

end if

response.write"搜索到的记录如下:<br><br>"

do until rs.eof

response.write""&rs("id")&":"&rs("title")&"" '打印出文章的ID和标题

response.write"<br><br>"

rs.movenext

loop

'打扫战场

rs.close

conn.close

set rs=nothing

set conn = nothing

//%>

---