answer.sql
来自「一般的数据库系统」· SQL 代码 · 共 395 行 · 第 1/2 页
SQL
395 行
update 供应商 set 供应商名='鹏达电子元件公司',地址='武汉市解放大道234号' where 供应商号='S03'
(23)删除职工“E001”在2001年5月12日签发的订购单；
delete from 订购单 where 职工号='E001' and 订购日期='2001.5.12'
(24)删除最近五年内没有签发订单的客户的订购单信息以及供应商资料；
delete from 订购单 where 供应商号 not in (select 供应商号 from 订购单 where year(getdate())-year(订购日期)<5 )
delete from 供应商 where 供应商号 not in (select 供应商号 from 订购单 where year(getdate())-year(订购日期)<5 )
4、create view vTotalValue as
select 职工号,sum(金额) from 订购单 group by 职工号
5、
CREATE FUNCTION dbo.ifincrease(@supplierID char(3))
RETURNS bit AS
BEGIN
declare @value1 money,@value2 money --用来存储上一条记录和当前记录的年度总金额
declare @ret bit
set @ret=1
declare annualCur cursor for
select sum(金额) from 订购单 where 供应商号=@supplierID group by year(订购日期) order by year(订购日期)
open annualCur
fetch next from annualCur into @value1
while @@Fetch_status=0 begin
fetch next from annualCur into @value2
if @value2<@value1 begin
set @ret=0 --若当前年度的总金额小于上一年度，则为非递增，返回0
break
end
set @value1=@value2
end
close annualCur
deallocate annualCur
return @ret
END
--查询
select 供应商号,dbo.ifIncrease(供应商号) from 供应商
--其他的使用例子
select 结果=dbo.ifincrease('S01') --测试一
select 结果=case dbo.ifincrease('S01') --测试二
when 1 then '是'
when 0 then '否'
end
6.
create procedure DeleteAndSave
@supplierID char(3)
as
begin
if exists(select * from sysobjects where xtype='U' and name='被删订购单')
insert 被删订购单 select * from 订购单 where 供应商号=@supplierID
else
select * into 被删订购单 from 订购单 where 供应商号=@supplierID
delete from 订购单 where 供应商号=@supplierID
end
go
--测试例子
execute DeleteAndSave 'S02'
execute DeleteAndSave 'S04'
7. 方法一：
用可视化界面：在“约束表达式”输入框中输入：工资 between 500 and 800000
在“默认值”输入框中输入：getdate()
方法二：
用SQL语句：
alter table 职工 add constraint salrange check (工资 between 500 and 800000)
create default today as getdate()
go
exec sp_bindefault 'today','订购单.订购日期'
三、
1.注册信息输入页面代码（部分信息）register.htm：
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312">
<title>无标题文档</title>
<style type="text/css">
<!--
.style5 {color: #FFFF00; }
-->
</style>
</head>
<body>
<form action="DoRegister.asp" method="post" name="register" id="register">
<table width="300" border="0" align="center" cellpadding="0" cellspacing="0" bordercolor="#CCCCFF" bgcolor="#669999">
<tr>
<td height="26"><div align="center" class="style5">用户名：</div></td>
<td><input name="username" type="text" id="username"></td>
</tr>
<tr>
<td height="32"><div align="center" class="style5">密码：</div></td>
<td><input name="password" type="password" id="password"></td>
</tr>
<tr>
<td height="31"><div align="center" class="style5">密码确认：</div></td>
<td><input name="passwordconf" type="password" id="passwordconf"></td>
</tr>
<tr>
<td height="31"><div align="center" class="style5">真实名：</div></td>
<td><input name="realname" type="text" id="realname"></td>
</tr>
<tr>
<td height="22"><div align="center" class="style5">性别：</div></td>
<td><input name="sex" type="radio" value="1" checked>
男
<input type="radio" name="sex" value="0">
女</td>
</tr>
<tr>
<td height="26"><div align="center" class="style5">email:</div></td>
<td><input type="text" name="textfield"></td>
</tr>
<tr>
<td height="27" colspan="2"><div align="center">
<input type="submit" name="Submit" value="提交">
</div></td>
</tr>
</table>
</form>
</body>
</html>
2. 信息写入DoRegister.asp：
<%@LANGUAGE="VBSCRIPT" CODEPAGE="936"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312">
<title>无标题文档</title>
</head>
<body>
<%set cn=Server.CreateObject("adodb.Connection")
cn.provider="sqloledb"
ProvStr="Server=.;Database=BBS;UID=sa;PWD=;"
cn.Open ProvStr
username=request("username")
set rs=server.createobject("ADODB.recordset")
rs.open "select * from userinfo where username='"+ username + "'",cn,1,3
if rs.eof then
rs.addnew
rs("username")=username
rs("email")=request("email")
rs("realname")=request("realname")
rs("password")=request("password")
rs("sex")=request("sex")
rs.update
response.Write "<script language='javascript'>"
response.Write "alert('注册成功。');"
response.Write "</script>"
response.end
else
response.Write "<script language='javascript'>"
response.Write "alert('该登录名已被占用，请重新选择登录名。');"
response.Write "history.go(-1);"
response.Write "</script>"
response.end
end if
rs.close%>
</body>
</html>
3. 密码输入页面（login.htm）：
建立一个网页Login.asp,含有用户名和密码两个表单输入项的表单，两输入项的名称分别为userName和password,表单的action＝“Logincheck.asp”.
4. 密码验证（logincheck.asp）:
<%set cn=Server.CreateObject("adodb.Connection")
cn.provider="sqloledb"
ProvStr="Server=.;Database=BBS;UID=sa;PWD=;"
cn.Open ProvStr
username=request("username")
password=request.form("password")
set rs=Server.CreateObject("adodb.recordset")
rs.open "select * from userinfo where username='"&username&"' and password='"&password&"'",cn,1,3
if rs.eof then
response.Write "<script language='javascript'>"
response.Write "alert('用户名或密码不正确,或者还未注册.');"
response.Write "history.go(-1);"
response.Write "</script>"
response.end
else
response.Write "<script language='javascript'>"
response.Write "alert('登录成功.');"
response.Write "</script>"
response.end
end if
rs.close
cn.close%>
⌨️ 快捷键说明
复制代码Ctrl + C
搜索代码Ctrl + F
全屏模式F11
增大字号Ctrl + =
减小字号Ctrl + -
显示快捷键?