实验四约束和存储过程

1、实验四 约束和存储过程一、 实验目的和要求 1、掌握使用Transact_SQL语句创建、删除约束的方法。2、掌握使用Transact_SQL语句创建、修改、删除、查看存储过程的方法。二、实验内容和步骤1、现有一个商店的数据库，记录客户及其购物情况，由以下四个关系组成：1） 客户表Customer：存储客户信息，包括客户号CustomerID、客户姓名Cname、地址Address、电子邮件Email、性别Gender、身份证号CardID、电话号码TelCode。2） 商品表Goods：存储商品信息，包括商品号GoodsID、商品名称GoodsName、单价UnitPrice、商品类别Cat

2、egory。3） 订单表Orders：存储客户与订单之间的关系，包括订单号、客户号、下单日期。4） 订单商品表OrderGoods：存储每个订单中包含的商品信息，包括订单号、商品号、商品数量。（1）请用SQL 语句建表，并在定义中进行如下声明：每个表的主外键；客户的姓名不能为null ；电子邮件中必须唯一，且必须包含和.；客户的性别必须是男或者女，默认为男；商品单价必须大于0；购买数量必须在到0之间30；（2）设置商品表Goods的“商品名字段不能为空”。 （3）设置客户表Customer的“身份证号不能重复”。（4）设置客户表Customer的“客户的住址只能是海淀、朝阳和东城。”（5）设置

3、Orders的下单日期默认值为当前日期。（5）设置OrderGoods与Customer、Goods之间的参照完整性约束的删除和更新的方式为级联删除和级联更新。（6）在该数据库中创建一个存储过程GetTotalPrice，实现根据客户名，查找该客户的订单总额。（本题为选作。提示：该题中涉及到游标的知识，我们将结合触发器介绍游标，有余力的同学可以先自行查阅资料）2、在学生成绩数据库中，根据要求创建存储过程。（1）创建名为PrSno的存储过程，根据学生学号，查询该生的选课情况，其中包括学生学号、姓名、性别、课程号、课程名和成绩。执行该存储过程，查询学号为“99001”的选课学生情况。（2）创建存储

4、过程PrCourseName。根据课程名，查询课程名和该课程的平均成绩。如果执行时，没有带参数，则显示以“数据”开头的课程信息。（3）创建名为PrSno2的存储过程，查询某同学所有课程成绩。如果存在不及格课程，则将该门课程的成绩修改为60分；否则显示所有课程成绩。（4）创建名为PrSno3的存储过程。参数是学号，输出信息是指定学号的选课门数、平均分、最高分、最低分。并执行该存储过程，输出学号为99001的相关信息。3、已知用户表（用户名，用户密码）。请创建该表，并编写一个实现用户注册的存储过程。当用户名不存在时，注册成功，存储过程返回0；当用户名已存在，注册失败，存储过程返回1。二、 实验环境

5、1.Windows7+SQL三、 调试过程四、 实验结果五、 总结通过此次实验：1. 本人熟悉了check,unique,default等约束的使用；2. 了解了on delete cascade级联删除和on update cascade级联更新3. 知道创建存储过程的基本结构和语法4. 在实验过程中部分细节的调试也使我对代码的把握及语义的实现有了更深的认识六、 附录 -客户表Customercreate table Customer(CustomerID int primary key,-主键Cname varchar(20) not null,-客户的姓名不能为null Address

6、varchar(50)check(Address='海淀'or Address='朝阳'or Address='东城'),-客户的住址只能是海淀、朝阳和东城Email varchar(50) check(Email like '%.%'),-电子邮件中必须唯一，且必须包含和.Gender char(2) check(Gender='男' or Gender='女')default '男',-客户的性别必须是男或者女，默认为男CardID int unique,-身份证号不能重复Tel

7、Code int)-商品表Goodscreate table Goods(GoodsID int primary key,-商品号,主键GoodsName varchar(20) not null,-商品名字段不能为空UnitPrice money check(UnitPrice>0),-商品单价必须大于Category varchar(20)-商品类别)-订单表Orderscreate table Orders(OrderID int primary key,-订单号,主键CustomerID int foreign key references Customer(CustomerID

8、)on delete cascade on update cascade,-客户号,外键,级联删除和级联更新OrderTime datetime default getdate()-下单日期默认值为当前日期)-订单商品表OrderGoods：存储每个订单中包含的商品信息，包括订单号、商品号、商品数量。create table OrderGoods(OrderID int,GoodsID int,GoodsQuantity int check(GoodsQuantity>=0 and GoodsQuantity<=30)-购买数量必须在到之间primary key(OrderID,G

9、oodsID),-联合主键-设置OrderGoods与Customer、Goods之间的参照完整性约束的删除和更新的方式为级联删除和级联更新。foreign key(OrderID) references Orders(OrderID)on delete cascade on update cascade,foreign key(GoodsID) references Goods(GoodsID)on delete cascade on update cascade)/*-在该数据库中创建一个存储过程GetTotalPrice，实现根据客户名，查找该客户的订单总额。/*创建存储过程*/crea

10、te procedure GetTotalPricecname varchar(20)asbeginselect Customer.CustomerID'客户号',UnitPrice'单价',GoodsQuantity'数量',totalPrice=UnitPrice*GoodsQuantity'总额'from Customer,Orders,OrderGoodswhere Customer.CustomerID=Orders.CustomerID and OrderGoods.OrderID=Orders.OrderID and

11、 Cname=cnameend/*执行存储过程*/exec GetTotalPrice*/-(1)create procedure PrSnosno char(8)asbeginselect s.sno,sname,ssex,o,cname,grade from student s,course c,scwhere s.sno=sno and o=o and s.sno=sc.snoend exec PrSno '95006'-(2)create procedure PrCourseNamecname varchar(20)='数据%'-执行时，没有带参数，则显

12、示以“数据”开头的课程信息asbeginselect cname,avg(grade)'平均成绩'from course c,scwhere cname like cname and o=ogroup by (cname)endexec PrCourseNameexec PrCourseName '数学'-(3)create procedure PrSno2sno char(8)asbeginupdate scset grade=60where grade<60select sno,cno,gradefrom scwhere sc.sno=snoendex

13、ec PrSno2 '95003'-(4)create procedure PrSno3sno char(8)asbeginselect sno,count(*) '选课门数',avg(grade) '平均分',max(grade) '最高分',min(grade) '最低分'from scwhere sno=snogroup by snoendexec PrSno3 '95011'create table UserLogin(userName varchar(20) not null,userPw

14、d char(8) not null)insert into UserLoginvalues('JOJO_Chen','20121107')create procedure register_Loginlogin_Name varchar(20),login_Pwd char(8),flag tinyint outputasbegindeclare loginName varchar(20)select loginName = userName from UserLogin where userName = login_Nameif (loginName is NULL)begin set flag= 0returnendelsebegin set flag = 1r