Question

谁能给套ASP控制数据库的基本操作的语句?

最好有实例,求简单
关于Access数据库,具体用法哈,详细点就给分

Answer 1

--1、查找员工的编号、姓名、部门和出生日期，如果出生日期为空值，
--显示日期不详,并按部门排序输出,日期格式为yyyy-mm-dd。
select emp_no ,emp_name ,dept ,
isnull(convert(char(10),birthday,120),'日期不详') birthday
from employee
order by dept

--2、查找与喻自强在同一个单位的员工姓名、性别、部门和职称
select emp_no,emp_name,dept,title
from employee
where emp_name<>'喻自强' and dept in
(select dept from employee
where emp_name='喻自强')

--3、按部门进行汇总，统计每个部门的总工资
select dept,sum(salary)
from employee
group by dept

--4、查找商品名称为14寸显示器商品的销售情况，
--显示该商品的编号、销售数量、单价和金额
select a.prod_id,qty,unit_price,unit_price*qty totprice
from sale_item a,product b
where a.prod_id=b.prod_id and prod_name='14寸显示器'

--5、在销售明细表中按产品编号进行汇总，统计每种产品的销售数量和金额
select prod_id,sum(qty) totqty,sum(qty*unit_price) totprice
from sale_item
group by prod_id

--6、使用convert函数按客户编号统计每个客户1996年的订单总金额
select cust_id,sum(tot_amt) totprice
from sales
where convert(char(4),order_date,120)='1996'
group by cust_id

--7、查找有销售记录的客户编号、名称和订单总额
select a.cust_id,cust_name,sum(tot_amt) totprice
from customer a,sales b
where a.cust_id=b.cust_id
group by a.cust_id,cust_name

--8、查找在1997年中有销售记录的客户编号、名称和订单总额
select a.cust_id,cust_name,sum(tot_amt) totprice
from customer a,sales b
where a.cust_id=b.cust_id and convert(char(4),order_date,120)='1997'
group by a.cust_id,cust_name

--9、查找一次销售最大的销售记录
select order_no,cust_id,sale_id,tot_amt
from sales
where tot_amt=
(select max(tot_amt)
from sales)

--10、查找至少有3次销售的业务员名单和销售日期
select emp_name,order_date
from employee a,sales b
where emp_no=sale_id and a.emp_no in
(select sale_id
from sales
group by sale_id
having count(*)>=3)
order by emp_name

--11、用存在量词查找没有订货记录的客户名称
select cust_name
from customer a
where not exists
(select *
from sales b
where a.cust_id=b.cust_id)

--12、使用左外连接查找每个客户的客户编号、名称、订货日期、订单金额
--订货日期不要显示时间，日期格式为yyyy-mm-dd
--按客户编号排序，同一客户再按订单降序排序输出
select a.cust_id,cust_name,convert(char(10),order_date,120),tot_amt
from customer a left outer join sales b on a.cust_id=b.cust_id
order by a.cust_id,tot_amt desc

--13、查找16M DRAM的销售情况，要求显示相应的销售员的姓名、
--性别，销售日期、销售数量和金额，其中性别用男、女表示
select emp_name 姓名, 性别= case a.sex when 'm' then '男'
when 'f' then '女'
else '未'
end,
销售日期= isnull(convert(char(10),c.order_date,120),'日期不详'),
qty 数量, qty*unit_price as 金额
from employee a, sales b, sale_item c,product d
where d.prod_name='16M DRAM' and d.pro_id=c.prod_id and
a.emp_no=b.sale_id and b.order_no=c.order_no

--14、查找每个人的销售记录，要求显示销售员的编号、姓名、性别、

作者： 221.219.39.* 2007-1-9 14:59 回复此发言

--------------------------------------------------------------------------------

2 SQL精华~~~与大家分享~~~~
--产品名称、数量、单价、金额和销售日期
select emp_no 编号,emp_name 姓名, 性别= case a.sex when 'm' then '男'
when 'f' then '女'
else '未'
end,
prod_name 产品名称,销售日期= isnull(convert(char(10),c.order_date,120),'日期不详'),
qty 数量, qty*unit_price as 金额
from employee a left outer join sales b on a.emp_no=b.sale_id , sale_item c,product d
where d.pro_id=c.prod_id and b.order_no=c.order_no

--15、查找销售金额最大的客户名称和总货款
select cust_name,d.cust_sum
from customer a,
(select cust_id,cust_sum
from (select cust_id, sum(tot_amt) as cust_sum
from sales
group by cust_id ) b
where b.cust_sum =
( select max(cust_sum)
from (select cust_id, sum(tot_amt) as cust_sum
from sales
group by cust_id ) c )
) d
where a.cust_id=d.cust_id

--16、查找销售总额少于1000元的销售员编号、姓名和销售额
select emp_no,emp_name,d.sale_sum
from employee a,
(select sale_id,sale_sum
from (select sale_id, sum(tot_amt) as sale_sum
from sales
group by sale_id ) b
where b.sale_sum <1000
) d
where a.emp_no=d.sale_id

--17、查找至少销售了3种商品的客户编号、客户名称、商品编号、商品名称、数量和金额
select a.cust_id,cust_name,b.prod_id,prod_name,d.qty,d.qty*d.unit_p rice
from customer a, product b, sales c, sale_item d
where a.cust_id=c.cust_id and d.prod_id=b.prod_id and
c.order_no=d.order_no and a.cust_id in (
select cust_id
from (select cust_id,count(distinct prod_id) prodid
from (select cust_id,prod_id
from sales e,sale_item f
where e.order_no=f.order_no) g
group by cust_id
having count(distinct prod_id)>=3) h )

--18、查找至少与世界技术开发公司销售相同的客户编号、名称和商品编号、商品名称、数量和金额
select a.cust_id,cust_name,d.prod_id,prod_name,qty,qty*unit_price
from customer a, product b, sales c, sale_item d
where a.cust_id=c.cust_id and d.prod_id=b.prod_id and
c.order_no=d.order_no and not exists
(select f.*
from customer x ,sales e, sale_item f
where cust_name='世界技术开发公司' and x.cust_id=e.cust_id and
e.order_no=f.order_no and not exists
( select g.*
from sale_item g, sales h
where g.prod_id = f.prod_id and g.order_no=h.order_no and
h.cust_id=a.cust_id)
)

19、查找表中所有姓刘的职工的工号，部门，薪水
select emp_no,emp_name,dept,salary
from employee
where emp_name like '刘%'

20、查找所有定单金额高于20000的所有客户编号
select cust_id
from sales
where tot_amt>20000

21、统计表中员工的薪水在40000-60000之间的人数
select count(*)as 人数
from employee
where salary between 40000 and 60000

22、查询表中的同一部门的职工的平均工资，但只查询”住址”是”上海市”的员工
select avg(salary) avg_sal,dept
from employee
where addr like '上海市%'
group by dept

23、将表中住址为"上海市"的员工住址改为"北京市"
update employee
set addr like '北京市'
where addr like '上海市'

24、查找业务部或会计部的女员工的基本信息。
select emp_no,emp_name,dept
from employee
where sex='F'and dept in ('业务','会计')

25、显示每种产品的销售金额总和，并依销售金额由大到小输出。
select prod_id ,sum(qty*unit_price)
from sale_item
group by prod_id
order by sum(qty*unit_price) desc

Answer 2

数据库连接语句：
set conn=server.CreateObject("adodb.connection")
DBPath = Server.MapPath("XXX.mdb")
conn.open "provider=microsoft.jet.oledb.4.0; data source="&DBPath
可以用conn.execute sql直接执行sql语句，如果想获得记录集，需用recordset对象，语句如下：
Set rs = Server.CreateObject("ADODB.RecordSet")
rs.Open sql,conn,1,3
while not rs.eof
......
rs.movenext
wend
数据库关闭语句：

rs.close
set rs=nothing
conn.close
set conn=nothing
对于数据库操作语句sql的具体语法请参考：
http://tieba.baidu.com/f?kz=162101904
http://blog.163.com/fjshqhy_2003/blog/static/1402687820071510243168/
SQL函数请参考：
http://hi.baidu.com/hanyiduo/blog/item/b1aaaaf357b08a54352acc82.html

Answer 3

ASP与Access数据库连接：
dim strConn
dim conn
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+Server.mappath("data/db1.mdb")+";Persist Security Info=False"
set conn = Server.CreateObject("ADODB.Connection")

记录集对象的方法：
rs.movenext 将记录指针从当前的位置向下移一行
rs.moveprevious 将记录指针从当前的位置向上移一行
rs.movefirst 将记录指针移到数据表第一行
rs.movelast 将记录指针移到数据表最后一行
rs.absoluteposition=N 将记录指针移到数据表第N行
rs.absolutepage=N 将记录指针移到第N页的第一行
rs.pagesize=N 设置每页为N条记录
rs.pagecount 根据 pagesize 的设置返回总页数
rs.recordcount 返回记录总数
rs.bof 返回记录指针是否超出数据表首端，true表示是，false为否
rs.eof 返回记录指针是否超出数据表末端，true表示是，false为否
rs.delete 删除当前记录，但记录指针不会向下移动
rs.addnew 添加记录到数据表末端
rs.update 更新数据表记录

＜%

'//查询方法
'//----------------------------(1)-------------------------------
Set RsWorkUserInfo = Server.CreateObject("ADODB.RecordSet")

StrSql = "Select UsersId, LoginName, UserName, Password"
StrSql = StrSql & " From Users"
StrSql = StrSql & " Where UsersId=" & SqlStr(tUserID)

If RsWorkUserInfo.State = 1 Then
RsWorkUserInfo.Close
End If
RsWorkUserInfo.Open StrSql,Conn,1,1

If Not RsWorkUserInfo.Eof Then
LoginName = RsWorkUserInfo("LoginName")
UserName = RsWorkUserInfo("UserName")
Password = RsWorkUserInfo("Password")
End if

RsWorkUserInfo.Close
Set RsWorkUserInfo = Nothing

'//----------------------------(2)-------------------------------
StrSql = "Select UsersId, LoginName, UserName, Password"
StrSql = StrSql & " From Users"
StrSql = StrSql & " Where UsersId=" & SqlStr(tUserID)
Set RsFind = Conn.Execute(StrSql)

If Not RsFind.Eof Then
LoginName = RsFind("LoginName")
UserName = RsFind("UserName")
Password = RsFind("Password")
End if

RsFind.Close
Set RsFind = Nothing

'//修改方法
'//----------------------------(1)-------------------------------
Set RsWorkUserInfo = Server.CreateObject("ADODB.RecordSet")

StrSql = "Select UsersId, LoginName, UserName, Password"
StrSql = StrSql & " From Users"
StrSql = StrSql & " Where UsersId=" & SqlStr(tUserID)

If RsWorkUserInfo.State = 1 Then
RsWorkUserInfo.Close
End If
RsWorkUserInfo.Open StrSql,Conn,1,3

IF Not RsWorkUserInfo.Eof Then
RsWorkUserInfo("LoginName") = LoginName
RsWorkUserInfo("UserName") = UserName
RsWorkUserInfo("Password") = Md5(Password)
RsWorkUserInfo.Update
Update = True
Else
Update = False
End if

RsWorkUserInfo.Close
Set RsWorkUserInfo = Nothing

'//----------------------------(2)-------------------------------
StrSql = "Update Users"
StrSql = StrSql & " Set LoginName=" & SqlStr(LoginName) & ", UserName=" & SqlStr(UserName) & ", Password=" & SqlStr(Password)
StrSql = StrSql & " Where UsersId=" & SqlStr(tUserID)
Conn.Execute(StrSql)

'//添加方法
'//----------------------------(1)-------------------------------
Set RsWorkUserInfo = Server.CreateObject("ADODB.RecordSet")

StrSql = "Select UsersId, LoginName, UserName, Password"
StrSql = StrSql & " From Users"
StrSql = StrSql & " Where UsersId=" & SqlStr(tUserID)

If RsWorkUserInfo.State = 1 Then
RsWorkUserInfo.Close
End If
RsWorkUserInfo.Open StrSql,Conn,1,3

If RsWorkUserInfo.Eof Then
RsWorkUserInfo.AddNew
RsWorkUserInfo("UsersID") = tUserId
RsWorkUserInfo("LoginName") = LoginName
RsWorkUserInfo("UserName") = UserName
RsWorkUserInfo("Password") = Md5(Password)
RsWorkUserInfo.Update
NewRecord = True
Else
NewRecord = False
End if

RsWorkUserInfo.Close
Set RsWorkUserInfo = Nothing

'//----------------------------(2)-------------------------------
StrSql = "Insert Into Users(UsersId, LoginName, UserName, Password)"
StrSql = StrSql & " Values(" & SqlStr(tUserID) & "," & SqlStr(LoginName) & "," & SqlStr(UserName) & "," & SqlStr(Password) & ")"
Conn.Execute(StrSql)

'//删除方法
'//----------------------------(1)-------------------------------
Set RsWorkUserInfo = Server.CreateObject("ADODB.RecordSet")

StrSql = "Delete From Users"
StrSql = StrSql & " Where UsersId=" & SqlStr(tUserID)

If RsWorkUserInfo.State = 1 Then
RsWorkUserInfo.Close
End If
RsWorkUserInfo.Open StrSql,Conn,1,3

RsWorkUserInfo.Close
Set RsWorkUserInfo = Nothing

'//----------------------------(2)-------------------------------
StrSql = "Delete From Users"
StrSql = StrSql & " Where UsersId=" & SqlStr(tUserID)
Conn.Execute(StrSql)

%＞

Answer 4

看看我的<自己辛苦给你写的哦>

//连接数据库:
<%
Set Conn = Server.CreateObject("ADODB.Connection")
Connstr = "DBQ="+server.mappath("data/wc.mdb")+";DefaultDir=;DRIVER={Microsoft Access Driver (*.mdb)};DriverId=25;FIL=MS Access;ImplicitCommitSync=Yes;MaxBufferSize=512;SafeTransactions=0;Threads=3;UserCommitSync=Yes"
Conn.Open Connstr'其中"data/wc.mdb"是数据库路径
%>

//连接数据表:
SQLStr = "Select * From sp"
'这是SQL语句
Set rs = Server.CreateObject("Adodb.Recordset")
rs.open SQLStr,Conn,1,1
'其中"Conn,1,1"的1,1代表不会修改数据表内容,若要修改则为"Conn,3,3"
//关闭数据表:
rs.close
set rs=nothing
//关闭数据库:
conn.close
set conn=nothing
//说明 如果你要在打开一个表的同时再打开另一个表则写为:
SQLStr2 = "Select * From sp"
Set rs2 = Server.CreateObject("Adodb.Recordset")
rs2.open SQLStr2,Conn,1,1
只是把SQLStr改为了SQLStr2 把rs改为了rs2,打开N个表同理

//实例:
1.打开一个表并读出表的值:
index.asp:
<%'打开库:
Set Conn = Server.CreateObject("ADODB.Connection")
Connstr = "DBQ="+server.mappath("data/wc.mdb")+";DefaultDir=;DRIVER={Microsoft Access Driver (*.mdb)};DriverId=25;FIL=MS Access;ImplicitCommitSync=Yes;MaxBufferSize=512;SafeTransactions=0;Threads=3;UserCommitSync=Yes"
Conn.Open Connstr

'打开表:
SQLStr = "Select * From sp"
Set rs = Server.CreateObject("Adodb.Recordset")
rs.open SQLStr,Conn,1,1
'显示页面:%>
姓名:<%=rs("name")%> <br>
班级:<%=rs("class")%>
.......
<%'关闭表及库:
rs.close
set rs=nothing
conn.close
set conn=nothing%>
这里你必须有一个数据库,不知道你对office access熟不?

其实用代码对access数据库操作基本流程就向这个例子一样,比如(在index.asp例子基础上)
1.新增一条记录:
改Conn,1,1为Conn,3,3
'新增代码:
rs.addnew
rs("name")="柔肠寸断"
rs("class")="012"
rs.update'保存代码
'关表和库:
rs.close
set rs=nothing
conn.close
set conn=nothing

2.修改一条记录:
update.asp:
<%
'打开库:
<%
Set Conn = Server.CreateObject("ADODB.Connection")
Connstr = "DBQ="+server.mappath("data/wc.mdb")+";DefaultDir=;DRIVER={Microsoft Access Driver (*.mdb)};DriverId=25;FIL=MS Access;ImplicitCommitSync=Yes;MaxBufferSize=512;SafeTransactions=0;Threads=3;UserCommitSync=Yes"
Conn.Open Connstr

'打开表:
SQLStr = "Select * From sp where name="柔肠寸断""
Set rs = Server.CreateObject("Adodb.Recordset")
rs.open SQLStr,Conn,3,3
'修改代码:
rs("name")="柔肠寸断1"
rs.update'保存代码
<%'关闭表及库:
rs.close
set rs=nothing
conn.close
set conn=nothing%>
3.删除:
delet.asp:
<%
'打开库:
<%
Set Conn = Server.CreateObject("ADODB.Connection")
Connstr = "DBQ="+server.mappath("data/wc.mdb")+";DefaultDir=;DRIVER={Microsoft Access Driver (*.mdb)};DriverId=25;FIL=MS Access;ImplicitCommitSync=Yes;MaxBufferSize=512;SafeTransactions=0;Threads=3;UserCommitSync=Yes"
Conn.Open Connstr

'打开表:
SQLStr = "Select * From sp where name="柔肠寸断""
Set rs = Server.CreateObject("Adodb.Recordset")
rs.open SQLStr,Conn,3,3
'删除代码:
rs.delete
rs.update'保存代码
<%'关闭表及库:
rs.close
set rs=nothing
conn.close
set conn=nothing%>

现在懂原理了吧 其实最主要的就是SQL语句,就是那个

SQLStr = "Select * From sp where name="柔肠寸断""
下面是SQL语句的例子:
select 字段名 from 表名 where 某字段名=某值 order by 某字段名 DESC(DESC代表降序,默认为升序)
自己可以建立一张页面和一个数据库试试.

现在你毕竟是开始,可能有些还是有点困惑,我还有许多实例和asp网站实用代码及asp的iis服务器替代软件,你把邮箱告诉我,我发给你吧,我的邮箱是libmw@163.com,相信只要练习多了就自然精通了~~
我的qq是627474754 有问题我们可以在线解决,乐于帮你.

Answer 5

move,del,movefirst,movelast,find,addnew,updata最常用

Answer 6

不同的数据库用的语句都是不同的哦，ASP仅仅是提供一个数据库的接口而已，实际数据库的基本操作都还是数据库自身带的语句