实验四 约束和存储过程.doc

实验四 约束和存储过程 实验目的和要求 1、掌握使用Transact_SQL语句创建、删除约束的方法。 2、掌握使用Transact_SQL语句创建、修改、删除、查看存储过程的方法。 二、实验内容和步骤 1、现有一个商店的数据库，记录客户及其购物情况，组成：Customer：存储客户信息，包括客户号CustomerID、客户姓名Cname、地址Address、电子邮件Email、性别Gender、身份证号CardID、电话号码TelCode。 商品表Goods：存储商品信息，包括商品号GoodsID、商品名称GoodsName、单价UnitPrice、商品类别Category。 订单表Orders：存储客户与订单之间的关系，包括订单号、客户号、下单日期。 订单商品表OrderGoods：存储每个订单中包含的商品信息，包括订单号、商品号、商品数量。 （1）请用SQL 语句建表，并在定义中进行如下声明： 每个表的主外键； 客户的姓名不能为null ； 电子邮件中必须唯一，且必须包含@和.； 客户的性别必须是男或者女，默认为男； 商品单价必须大于； 购买数量必须在到之间；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 调试过程 实验结果 总结 通过此次实验： 本人熟悉了check,unique,default等约束的使用； 了解了on delete cascade级联删除和on update cascade级联更新--客户表Customer create table Customer ( CustomerID int primary key,--主键 Cname varchar(20) not null,--客户的姓名不能为null Address 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,--身份证号不能重复 TelCode int ) --商品表Goods create table Goods ( GoodsID int primary key,--商品号,主键 GoodsName varchar(20) not null,--商品名字段不能为空 UnitPrice money check(UnitPrice0),--商品单价必须大于 Category varchar(20)--商品类别 ) --订单表Orders create table Orders ( OrderID int primary key,--订单号,主键 CustomerID int foreign key references Customer(CustomerI