数据库课程设计---客户信息管理系统.doc

学 号： 2010131114课 程 设 计题 目客户信息管理系统学 院计算机科学与信息工程学院专 业计算机科学与技术班 级2010计算机1班学生姓名刘小燕指导教师康世瀛2012年6月10日重庆工商大学课程设计成绩评定表 学院： 计信学院 班级： 10计算机一班 学生姓名：刘小燕 学号： 2010131114项目分值优秀(100x90)良好(90x80)中等(80x70)及格(70x60)不及格(x2000查询一次性购物金额大于2000的销售明细select * from selldetailwhere customerno=c2003007查询编号为c2003007的客户的购物记录select * from selldetailwhere year(sell_date)=2009查询2009年的销售商明细select a.customerno,a.customername,b.invoiceno,ductname,b.quantity,b.sell_price,b.sell_date from customer a,selldetail b,product c where a.customerno=b.customerno and ductno=ductno and b.customerno=c2003007查询客户编号为c2003007的的客户名称、购物发票编号、所购商品名称、数量、单价和购物日期select b.customerno,a.customername,sum(quantity *sell_price) 总金额from customer a,selldetail bwhere a.customerno=b.customernogroup by b.customerno,a.customernameorder by 总金额desc在销售明细里面查询每位客户的累计消费总金额，并按照消费金额降序排列，同时可找出消费最高的客户update customer set cust_level=vip客户from customer a,( select customerno,sum(quantity*sell_price)总消费 from selldetail group by customerno having sum(quantity*sell_price)2000) bwhere a.customerno=b.customernoselect * from customerwhere cust_level=vip客户把所有累计消费金额大于2000的客户升级为vip客户更新前的更新后新增一位vip客户select top 8 b.customerno,a.customername,sum(quantity *sell_price) 总金额from customer a,selldetail bwhere a.customerno=b.customernogroup by b.customerno,a.customernameorder by 总金额desc查询消费总金额排名前8名的客户2-5 对用户表的查询操作select * from user_check查询所有的用户，一共有15个用户select count(*) 客户数 from user_checkwhere user_level=一般客户or user_level=vip客户查询用户表里面级别为客户的数目，包括一般用户和vip用户3、 定义视图3-1 定义客户表的视图create view cust_vip_view as select * from customerwhere cust_level=vip客户select * from cust_vip_view建立vip客户的视图，显示vip客户的基本信息create view cust_sex_view as select * from customerwhere sex=男select * from cust_sex_view建立男客户的视图，显示男客户的基本信息3-2定义员工表的视图create view staff_dept_view as select * from staffwhere department=业务科select * from staff_dept_view建立员工视图，显示业务科的所有员工3-3定义商品表的视图create view prodt_price_view as select * from productwhere price between 100 and 300select * from prodt_price_view建立商品单价的视图，显示价格在100300间的商品3-4建立商品明细表的视图create view detail_prodt_view as select * from selldetailwhere productno=p2009003select * from detail_prodt_view建立一个商品明细表的视图，显示商品编号为p2009003的商品销售明细create view detail_saler_view as select * from selldetailwhere salerno=s2005011select * from detail_saler_view建立一个商品明细表的视图，显示编号为s2005011的销售员的销售情况create view detail_cust_view as select a.customerno,a.customername,b.invoiceno,ductname,b.quantity,b.sell_price,b.sell_datefrom customer a,selldetail b,product cwhere a.customerno=b.customerno and ductno=ductno and b.customerno=c2007011select * from detail_cust_view查询客户编号为c2003007的的客户名称、购物发票编号、所购商品名称、数量、单价和购物日期create view detail_consume_order asselect top 8 b.customerno,a.customername,sum(quantity *sell_price) 总金额from customer a,selldetail bwhere a.customerno=b.customernogroup by b.customerno,a.customernameorder by 总金额descselect * from detail_consume_order建立一个销售明细的视图，显示消费总金额排名前8名的客户create view detail_consume_year asselect sum(quantity *sell_price) 总金额from selldetailwhere year(sell_date)=2009select * from detail_consume_year建立一个销售明细的视图，显示2009年度交易总金额3-5 建立用户表的视图create view user_cust_countas select count(*) 客户数from user_checkwhere user_level=一般客户or user_level=vip客户select * from user_cust_count建立一个用户表的视图，显示用户级别为客户（包括一般客户和vip客户）的总数目4、 定义游标4-1 定义客户表上的游标declare cur_cust cursor forselect * from customerwhere cust_level=vip客户order by customernoopen cur_custselect cursor内数据条数=cursor_rowsfetch next from cur_custwhile (fetch_status-1) begin select cursor读取状态=fetch_status fetch next from cur_cust endclose cur_custdeallocate cur_cust利用游标选取客户级别为vip的客户的所有字段，并逐行显示游标中的信息4-2定义销售明细表上的游标declare cur_cust_detail cursor forselect a.customerno,a.customername,b.invoiceno,ductname,b.quantity,b.sell_price,b.sell_datefrom customer a,selldetail b,product cwhere a.customerno=b.customerno and ductno=ductno and b.customerno=c2003007open cur_cust_detailselect cursor内数据条数=cursor_rowsfetch next from cur_cust_detailwhile (fetch_status-1) begin select cursor读取状态=fetch_status fetch next from cur_cust_detail endclose cur_cust_detaildeallocate cur_cust_detail利用游标选取编号为c2003007的客户的客户名称、购物发票编号、所购商品名称、数量、单价和购物日期，并逐行显示游标中的信息declare cur_detail_top5 cursor forselect top 5 b.customerno,a.customername,sum(quantity *sell_price) 总金额from customer a,selldetail bwhere a.customerno=b.customernogroup by b.customerno,a.customernameorder by 总金额descopen cur_detail_top5select cursor内数据条数=cursor_rowsfetch next from cur_detail_top5while (fetch_status-1) begin select cursor读取状态=fetch_status fetch next from cur_detail_top5 endclose cur_detail_top5deallocate cur_detail_top5利用游标选取消费前5名的客户的编号、姓名和消费总金额5、 定义存储过程5-1定义客户表上的存储过程create procedure customer_search c_no char(8)as select * from customer where customerno=c_noexec customer_search c2006098带输入参数的存储过程，根据客户编号查询该客户的基本信息create procedure vipcustomer_searchas select * from customer where cust_level=vip客户exec vipcustomer_search建立存储过程，显示vip客户的基本信息5-2定义商品表上的存储过程create procedure product_searchasselect productno,productname from productwhere productname=17寸显示器exec product_search建立存储过程，显示商品名为17寸显示器的商品编号、商品名5-3定义销售明细表上的存储过程create procedure detail_product_search cust_no char(8) as select a.customerno,a.customername,b.invoiceno,ductname,b.quantity,b.sell_price,b.sell_date from customer a,selldetail b,product cwhere a.customerno=b.customerno and ductno=ductno and b.customerno=cust_noexec detail_product_search c2011003建立一个带输入参数的存储过程，根据输入的客户编号查询客户名称、购物发票编号、所购商品名称、数量、单价和购物日期create procedure detail_consume_top5asselect top 5 b.customerno,a.customername,sum(quantity *sell_price) 总金额from customer a,selldetail bwhere a.customerno=b.customernogroup by b.customerno,a.customernameorder by 总金额descexec detail_consume_top5建立一个存储过程，查询消费前五名的客户6、 定义触发器6-1 定义客户表上的触发器6-1-1仅允许dbo用户可以删除customer表中的数据create trigger tr_cust on customer for delete asbegin if user=dbo commitelse begin prin