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

学 号： 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,c.productName,b.quantity,b.Sell_price,b.Sell_Date from Customer a,SellDetail b,Product c where a.customerNo=b.customerNo and b.productNo=c.productNo 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,c.productName,b.quantity,b.Sell_price,b.Sell_Datefrom Customer a,SellDetail b,Product cWhere a.customerNo=b.customerNo and b.productNo=c.productNo 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,c.productName,b.quantity,b.Sell_price,b.Sell_Datefrom Customer a,SellDetail b,Product cwhere a.customerNo=b.customerNo and b.productNo=c.productNo 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,c.productName,b.quantity,b.Sell_price,b.Sell_Date from Customer a,SellDetail b,Product cwhere a.customerNo=b.customerNo and b.productNo=c.productNo 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 print 仅允许dbo用户可以删除Customer表中的数据! rollback transaction endEnd6-1-2仅允许dbo用户可以修改Customer表中的数据create trigger Tr_cust on Customer for update asbegin if user=dbo commitelse begin print 仅允许dbo用户可以修改Customer表中的数据! rollback transaction endend6-2在SellDetail上创建触发器，插入数据时要先检查Customer表、Staff表和Product里面是否存在和SellDetail表同值的客户编号、员工编号与商品编号。create trigger Tr_SellDetail on SellDetail for insert asif exists(select * from inserted where customerNo not in(select customerNo from Customer) or salerNo not in (select staffNo from Staff) or productNo not in(select productNo from Product) begin print Customer表中不存在客户编号或者Staff表中不存在员工编号或者Product中不存在商品编号，不能插入！ rollback transaction