实验四约束和存储过程.docx
《实验四约束和存储过程.docx》由会员分享，可在线阅读，更多相关《实验四约束和存储过程.docx（13页珍藏版）》请在冰点文库上搜索。
实验四约束和存储过程
实验四约束和存储过程
一、实验目的和要求
1、掌握使用Transact_SQL语句创建、删除约束的方法。
2、掌握使用Transact_SQL语句创建、修改、删除、查看存储过程的方法。
二、实验内容和步骤
1、现有一个商店的数据库，记录客户及其购物情况，由以下四个关系组成：
1）客户表Customer：
存储客户信息，包括客户号CustomerID、客户姓名Cname、地址Address、电子邮件Email、性别Gender、身份证号CardID、电话号码TelCode。
2）商品表Goods：
存储商品信息，包括商品号GoodsID、商品名称GoodsName、单价UnitPrice、商品类别Category。
3）订单表Orders：
存储客户与订单之间的关系，包括订单号、客户号、下单日期。
4）订单商品表OrderGoods：
存储每个订单中包含的商品信息，包括订单号、商品号、商品数量。
（1）请用SQL语句建表，并在定义中进行如下声明：
每个表的主外键；
客户的姓名不能为null；
电子邮件中必须唯一，且必须包含@和.；
客户的性别必须是男或者女，默认为男；
商品单价必须大于0；
购买数量必须在到0之间30；
（2）设置商品表Goods的“商品名字段不能为空”。
（3）设置客户表Customer的“身份证号不能重复”。
（4）设置客户表Customer的“客户的住址只能是海淀、朝阳和东城。
”
（5）设置Orders的下单日期默认值为当前日期。
（5）设置OrderGoods与Customer、Goods之间的参照完整性约束的删除和更新的方式为级联删除和级联更新。
（6）在该数据库中创建一个存储过程GetTotalPrice，实现根据客户名，查找该客户的订单总额。
（本题为选作。
提示：
该题中涉及到游标的知识，我们将结合触发器介绍游标，有余力的同学可以先自行查阅资料）
2、在学生成绩数据库中，根据要求创建存储过程。
（1）创建名为PrSno的存储过程，根据学生学号，查询该生的选课情况，其中包括学生学号、姓名、性别、课程号、课程名和成绩。
执行该存储过程，查询学号为“99001”的选课学生情况。
（2）创建存储过程PrCourseName。
根据课程名，查询课程名和该课程的平均成绩。
如果执行时，没有带参数，则显示以“数据”开头的课程信息。
（3）创建名为PrSno2的存储过程，查询某同学所有课程成绩。
如果存在不及格课程，则将该门课程的成绩修改为60分；否则显示所有课程成绩。
（4）创建名为PrSno3的存储过程。
参数是学号，输出信息是指定学号的选课门数、平均分、最高分、最低分。
并执行该存储过程，输出学号为‘99001’的相关信息。
3、已知用户表（用户名，用户密码）。
请创建该表，并编写一个实现用户注册的存储过程。
当用户名不存在时，注册成功，存储过程返回0；当用户名已存在，注册失败，存储过程返回1。
二、实验环境
1.Windows7+SQL
三、调试过程
四、实验结果
五、总结
通过此次实验：
1.本人熟悉了check,unique,default等约束的使用；
2.了解了ondeletecascade级联删除和onupdatecascade级联更新
3.知道创建存储过程的基本结构和语法
4.在实验过程中部分细节的调试也使我对代码的把握及语义的实现有了更深的认识
六、附录
--客户表Customer
createtableCustomer
（
CustomerIDintprimarykey,--主键
Cnamevarchar（20）notnull,--客户的姓名不能为null
Addressvarchar（50）check（Address='海淀'orAddress='朝阳'orAddress='东城'）,--客户的住址只能是海淀、朝阳和东城
Emailvarchar（50）check（Emaillike'%@%.%'）,--电子邮件中必须唯一，且必须包含@和.
Genderchar
（2）check（Gender='男'orGender='女'）default'男',--客户的性别必须是男或者女，默认为男
CardIDintunique,--身份证号不能重复
TelCodeint
）
--商品表Goods
createtableGoods
（
GoodsIDintprimarykey,--商品号,主键
GoodsNamevarchar（20）notnull,--商品名字段不能为空
UnitPricemoneycheck（UnitPrice>0）,--商品单价必须大于
Categoryvarchar（20）--商品类别
）
--订单表Orders
createtableOrders
（
OrderIDintprimarykey,--订单号,主键
CustomerIDintforeignkeyreferencesCustomer（CustomerID）ondeletecascadeonupdatecascade,--客户号,外键,级联删除和级联更新
OrderTimedatetimedefaultgetdate（）--下单日期默认值为当前日期
）
--订单商品表OrderGoods：
存储每个订单中包含的商品信息，包括订单号、商品号、商品数量。
createtableOrderGoods
（
OrderIDint,
GoodsIDint,
GoodsQuantityintcheck（GoodsQuantity>=0andGoodsQuantity<=30）--购买数量必须在到之间
primarykey（OrderID,GoodsID）,--联合主键
--设置OrderGoods与Customer、Goods之间的参照完整性约束的删除和更新的方式为级联删除和级联更新。
foreignkey（OrderID）referencesOrders（OrderID）ondeletecascadeonupdatecascade,
foreignkey（GoodsID）referencesGoods（GoodsID）ondeletecascadeonupdatecascade
）
/*
--在该数据库中创建一个存储过程GetTotalPrice，实现根据客户名，查找该客户的订单总额。
/*创建存储过程*/
createprocedureGetTotalPrice
@cnamevarchar（20）
as
begin
selectCustomer.CustomerID'客户号',UnitPrice'单价',GoodsQuantity'数量',totalPrice=UnitPrice*GoodsQuantity'总额'
fromCustomer,Orders,OrderGoods
whereCustomer.CustomerID=Orders.CustomerIDandOrderGoods.OrderID=Orders.OrderID
andCname=@cname
end
/*执行存储过程*/
execGetTotalPrice
*/
--
（1）
createprocedurePrSno
@snochar（8）
as
begin
selects.sno,sname,ssex,o,cname,grade
fromstudents,coursec,sc
wheres.sno=@snoando=oands.sno=sc.sno
end
execPrSno'95006'
--
（2）
createprocedurePrCourseName
@cnamevarchar（20）='数据%'--执行时，没有带参数，则显示以“数据”开头的课程信息
as
begin
selectcname,avg（grade）'平均成绩'
fromcoursec,sc
wherecnamelike@cnameando=o
groupby（cname）
end
execPrCourseName
execPrCourseName'数学'
--（3）
createprocedurePrSno2
@snochar（8）
as
begin
updatesc
setgrade=60
wheregrade<60
selectsno,cno,grade
fromsc
wheresc.sno=@sno
end
execPrSno2'95003'
--（4）
createprocedurePrSno3
@snochar（8）
as
begin
selectsno,count（*）'选课门数',avg（grade）'平均分',max（grade）'最高分',min（grade）'最低分'
fromsc
wheresno=@sno
groupbysno
end
execPrSno3'95011'
createtableUserLogin
（
userNamevarchar（20）notnull,
userPwdchar（8）notnull
）
insertintoUserLogin
values（'JOJO_Chen','20121107'）
createprocedureregister_Login
@login_Namevarchar（20）,
@login_Pwdchar（8）,
@flagtinyintoutput
as
begin
declare@loginNamevarchar（20）
select@loginName=userName
fromUserLogin
whereuserName=@login_Name
if（@loginNameisNULL）
begin
set@flag=0
return
end
else
begin
set@flag=1
return
end
end
/*执行存储过程*/
declare@UserLogintinyint
execregister_Login'JOJO_Chen','20121107',@UserLoginoutput
select@UserLogin
execregister_Login'JOJO','20121107',@UserLoginoutput
select@UserLogin
升级会员 