《实验四约束和存储过程》由会员分享，可在线阅读，更多相关《实验四约束和存储过程（10页珍藏版）》请在金锄头文库上搜索。
1、word实验四约束和存储过程一、 实验目的和要求1、掌握使用Transact_SQL语句创建、删除约束的方法。2、掌握使用Transact_SQL语句创建、修改、删除、查看存储过程的方法。二、实验容和步骤1、现有一个商店的数据库，记录客户及其购物情况，由以下四个关系组成：1） 客户表Customer：存储客户信息，包括客户号CustomerID、客户ame、地址Address、电子Email、性别Gender、号CardID、TelCode。2） 商品表Goods：存储商品信息，包括商品号GoodsID、商品名称GoodsName、单价UnitPrice、商品类别Category。3） 订单表
2、Orders：存储客户与订单之间的关系，包括订单号、客户号、下单日期。4） 订单商品表OrderGoods：存储每个订单中包含的商品信息，包括订单号、商品号、商品数量。（1）请用SQL 语句建表，并在定义中进行如下声明：每个表的主外键；客户的不能为null ；电子中必须唯一，且必须包含和.；客户的性别必须是男或者女，默认为男；商品单价必须大于0；购买数量必须在到0之间30；（2）设置商品表Goods的“商品名字段不能为空”。 （3）设置客户表Customer的“号不能重复”。（4）设置客户表Customer的“客户的住址只能是海淀、和东城。”（5）设置Orders的下单日期默认值为当前日期。（
3、5）设置OrderGoods与Customer、Goods之间的参照完整性约束的删除和更新的方式为级联删除和级联更新。（6）在该数据库中创建一个存储过程GetTotalPrice，实现根据客户名，查找该客户的订单总额。（本题为选作。提示：该题中涉及到游标的知识，我们将结合触发器介绍游标，有余力的同学可以先自行查阅资料）2、在学生成绩数据库中，根据要求创建存储过程。（1）创建名为PrSno的存储过程，根据学生学号，查询该生的选课情况，其中包括学生学号、性别、课程号、课程名和成绩。执行该存储过程，查询学号为“99001”的选课学生情况。（2）创建存储过程PrCourseName。根据课程名，查询课
4、程名和该课程的平均成绩。如果执行时，没有带参数，则显示以“数据”开头的课程信息。（3）创建名为PrSno2的存储过程，查询某同学所有课程成绩。如果存在不及格课程，则将该门课程的成绩修改为60分；否则显示所有课程成绩。（4）创建名为PrSno3的存储过程。参数是学号，输出信息是指定学号的选课门数、平均分、最高分、最低分。并执行该存储过程，输出学号为99001的相关信息。3、已知用户表（用户名，用户密码）。请创建该表，并编写一个实现用户注册的存储过程。当用户名不存在时，注册成功，存储过程返回0；当用户名已存在，注册失败，存储过程返回1。二、 实验环境1.Windows7+SQL三、 调试过程四、
5、实验结果五、 总结通过此次实验：1. 本人熟悉了check,unique,default等约束的使用；2. 了解了on delete cascade级联删除和on update cascade级联更新3. 知道创建存储过程的基本结构和语法4. 在实验过程中部分细节的调试也使我对代码的把握及语义的实现有了更深的认识六、 附录-客户表Customercreatetable Customer(CustomerID intprimarykey,-主键ame varchar(20)notnull,-客户的不能为null Address varchar(50)check(Address=海淀or Addr
6、ess=or Address=东城),-客户的住址只能是海淀、和东城Email varchar(50)check(Email like%.%),-电子中必须唯一，且必须包含和.Gender char(2)check(Gender=男or Gender=女)default男,-客户的性别必须是男或者女，默认为男CardID intunique,-号不能重复TelCode int)-商品表Goodscreatetable Goods(GoodsID intprimarykey,-商品号,主键GoodsName varchar(20)notnull,-商品名字段不能为空UnitPrice money
7、check(UnitPrice0),-商品单价必须大于Category varchar(20)-商品类别)-订单表Orderscreatetable Orders(OrderID intprimarykey,-订单号,主键CustomerID intforeignkeyreferences Customer(CustomerID)ondeletecascadeonupdatecascade,-客户号,外键,级联删除和级联更新OrderTime datetimedefaultgetdate()-下单日期默认值为当前日期)-订单商品表OrderGoods：存储每个订单中包含的商品信息，包括订单号、
8、商品号、商品数量。createtable OrderGoods(OrderID int,GoodsID int,GoodsQuantity intcheck(GoodsQuantity=0 and GoodsQuantity=30)-购买数量必须在到之间primarykey(OrderID,GoodsID),-联合主键-设置OrderGoods与Customer、Goods之间的参照完整性约束的删除和更新的方式为级联删除和级联更新。foreignkey(OrderID)references Orders(OrderID)ondeletecascadeonupdatecascade,foreig
9、nkey(GoodsID)references Goods(GoodsID)ondeletecascadeonupdatecascade)/*-在该数据库中创建一个存储过程GetTotalPrice，实现根据客户名，查找该客户的订单总额。/*创建存储过程*/create procedure GetTotalPriceame varchar(20)asbeginselect Customer.CustomerID客户号,UnitPrice单价,GoodsQuantity数量,totalPrice=UnitPrice*GoodsQuantity总额from Customer,Orders,Orde
10、rGoodswhere Customer.CustomerID=Orders.CustomerID and OrderGoods.OrderID=Orders.OrderID and ame=ameend/*执行存储过程*/exec GetTotalPrice*/-(1)createprocedure PrSnosno char(8)asbeginselect s.sno,sname,ssex,c.o,ame,grade from student s,course c,scwhere s.sno=sno and c.o=sc.o and s.sno=sc.snoendexec PrSno 95
11、006-(2)createprocedure PrCourseNameame varchar(20)=数据%-执行时，没有带参数，则显示以“数据”开头的课程信息asbeginselect ame,avg(grade)平均成绩from course c,scwhere ame like ame and sc.o=c.ogroupby(ame)endexec PrCourseNameexec PrCourseName 数学-(3)createprocedure PrSno2sno char(8)asbeginupdate scset grade=60where grade60select sno,
12、o,gradefrom scwhere sc.sno=snoendexec PrSno2 95003-(4)createprocedure PrSno3sno char(8)asbeginselect sno,count(*)选课门数,avg(grade)平均分,max(grade)最高分,min(grade)最低分from scwhere sno=snogroupby snoendexec PrSno3 95011createtable UserLogin(userName varchar(20)notnull,userPwd char(8)notnull)insertinto UserLo
13、ginvalues(JOJO_Chen,20121107)createprocedure register_Loginlogin_Name varchar(20),login_Pwd char(8),flag tinyintoutputasbegindeclare loginName varchar(20)select loginName = userName from UserLogin where userName = login_Nameif(loginName isNULL)beginset flag= 0returnendelsebeginset flag = 1returnendend/*执行存储过程*/declare UserLogin tinyintexec register_Login JOJO_Chen,20121107,UserLogin outputselect UserLoginexec register_Login JOJO,20121107,UserLogin outputselect UserLogin /
