數(shù)據(jù)庫(kù)課程設(shè)計(jì)客戶信息管理系統(tǒng)(共40頁(yè))

1、第 頁(yè)學(xué) 號(hào)： 2010131114課 程 設(shè) 計(jì)題 目客戶信息管理系統(tǒng)學(xué) 院計(jì)算機(jī)科學(xué)與信息工程學(xué)院專 業(yè)計(jì)算機(jī)科學(xué)與技術(shù)班 級(jí)2010計(jì)算機(jī)1班學(xué)生姓名劉小燕指導(dǎo)教師康世瀛2012年6月10日貴州航天(hngtin)職業(yè)技術(shù)學(xué)院課程設(shè)計(jì)成績(jī)(chngj)評(píng)定表 學(xué)院(xuyun)：貴州航天職業(yè)技術(shù)學(xué)院班級(jí)： 12計(jì)算機(jī)一班 學(xué)生姓名：周旭 學(xué)號(hào)： A123GZ0530101029項(xiàng)目分值優(yōu)秀(100 x90)良好(90 x80)中等(80 x70)及格(70 x60)不及格(x2000查詢一次性購(gòu)物(u w)金額大于2000的銷售明細(xì)select * from SellDetailwher

2、e CustomerNo=C2003007查詢編號(hào)為C2003007的客戶的購(gòu)物記錄select * from SellDetailwhere year(Sell_date)=2009查詢(chxn)2009年的銷售商明細(xì)(mn x)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 an

3、d b.customerNo=C2003007查詢客戶(k h)編號(hào)為C2003007的的客戶名稱、購(gòu)物發(fā)票編號(hào)、所購(gòu)商品名稱、數(shù)量、單價(jià)和購(gòu)物日期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在銷售明細(xì)里面查詢每位客戶的累計(jì)消費(fèi)總金額(jn )，并按照消費(fèi)金額降序排列，同時(shí)可找出消費(fèi)最高的客戶

4、update Customer set cust_level=VIP客戶(k h)from Customer a,( select customerNo,sum(quantity*Sell_price)總消費(fèi)(xiofi) from SellDetail group by customerNo having sum(quantity*Sell_price)2000) bwhere a.customerNo=b.customerNoselect * from Customerwhere cust_level=VIP客戶把所有累計(jì)消費(fèi)金額大于2000的客戶升級(jí)為VIP客戶更新前的更新(gngxn)

5、后新增一位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查詢(chxn)消費(fèi)總金額排名前8名的客戶(k h)2-5 對(duì)用戶表的查詢操作select * from User_check查詢所有的用戶，一共有15個(gè)用戶select count(*) 客戶(k h)數(shù) from

6、User_checkwhere User_level=一般(ybn)客戶or User_level=VIP客戶查詢(chxn)用戶表里面級(jí)別為客戶的數(shù)目，包括一般用戶和VIP用戶定義視圖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 * f

7、rom cust_sex_view建立男客戶(k h)的視圖，顯示男客戶的基本信息3-2定義(dngy)員工表的視圖create view staff_dept_view as select * from Staffwhere department=業(yè)務(wù)科select * from staff_dept_view建立員工視圖，顯示(xinsh)業(yè)務(wù)科的所有員工3-3定義商品表的視圖create view prodt_price_view as select * from Productwhere price between 100 and 300select * from prodt_pric

8、e_view建立商品單價(jià)的視圖，顯示價(jià)格在100300間的商品3-4建立商品明細(xì)表的視圖create view Detail_prodt_view as select * from SellDetailwhere productNo=P2009003select * from Detail_prodt_view建立一個(gè)商品(shngpn)明細(xì)表的視圖，顯示商品編號(hào)為P2009003的商品銷售明細(xì)(mn x)create view Detail_saler_view as select * from SellDetailwhere salerNo=S2005011select * from De

9、tail_saler_view建立一個(gè)(y )商品明細(xì)表的視圖，顯示編號(hào)為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 * f

10、rom Detail_cust_view查詢客戶編號(hào)為C2003007的的客戶名稱、購(gòu)物發(fā)票編號(hào)、所購(gòu)商品名稱、數(shù)量、單價(jià)和購(gòu)物日期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 Deta

11、il_consume_order建立一個(gè)銷售明細(xì)的視圖，顯示(xinsh)消費(fèi)總金額排名前8名的客戶(k h)create view Detail_consume_year asselect sum(quantity *Sell_price) 總金額from SellDetailwhere year(Sell_date)=2009select * from Detail_consume_year建立一個(gè)銷售明細(xì)的視圖(sht)，顯示2009年度交易總金額3-5 建立(jinl)用戶表的視圖create view user_cust_countas select count(*) 客戶(k h)

12、數(shù)from User_checkwhere User_level=一般(ybn)客戶or User_level=VIP客戶select * from user_cust_count建立一個(gè)用戶表的視圖，顯示用戶級(jí)別為客戶（包括一般客戶和VIP客戶）的總數(shù)目定義游標(biāo)4-1 定義客戶表上的游標(biāo)declare cur_cust cursor forselect * from Customerwhere cust_level=VIP客戶order by customerNoopen cur_custselect cursor內(nèi)數(shù)據(jù)條數(shù)=cursor_rowsfetch next from cur_cu

13、stwhile (fetch_status-1) begin select cursor讀取狀態(tài)=fetch_status fetch next from cur_cust endclose cur_custdeallocate cur_cust利用游標(biāo)選取客戶級(jí)別為VIP的客戶的所有字段，并逐行顯示游標(biāo)中的信息4-2定義(dngy)銷售明細(xì)表上的游標(biāo)declare cur_cust_detail cursor forselect a.customerNo,a.customerName,b.invoiceNo,ductName,b.quantity,b.Sell_price,b.Sell_Da

14、tefrom Customer a,SellDetail b,Product cwhere a.customerNo=b.customerNo and ductNo=ductNo and b.customerNo=C2003007open cur_cust_detailselect cursor內(nèi)數(shù)據(jù)(shj)條數(shù)=cursor_rowsfetch next from cur_cust_detailwhile (fetch_status-1) begin select cursor讀取狀態(tài)(zhungti)=fetch_status fetch next from cur_cust_detai

15、l endclose cur_cust_detaildeallocate cur_cust_detail利用(lyng)游標(biāo)選取編號(hào)為C2003007的客戶的客戶名稱、購(gòu)物發(fā)票編號(hào)、所購(gòu)商品名稱、數(shù)量、單價(jià)和購(gòu)物日期，并逐行顯示游標(biāo)中的信息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.custom

16、erNo,a.customerNameorder by 總金額DESCopen cur_detail_top5select cursor內(nèi)數(shù)據(jù)(shj)條數(shù)=cursor_rowsfetch next from cur_detail_top5while (fetch_status-1) begin select cursor讀取狀態(tài)(zhungti)=fetch_status fetch next from cur_detail_top5 endclose cur_detail_top5deallocate cur_detail_top5利用游標(biāo)選取消費(fèi)前5名的客戶(k h)的編號(hào)、姓名和消費(fèi)

17、總金額定義存儲(chǔ)(cn ch)過(guò)程5-1定義客戶表上的存儲(chǔ)(cn ch)過(guò)程create procedure customer_search c_no char(8)as select * from Customer where customerNo=c_noexec customer_search C2006098帶輸入?yún)?shù)的存儲(chǔ)(cn ch)過(guò)程，根據(jù)客戶編號(hào)查詢?cè)摽蛻舻幕拘畔reate procedure VIPcustomer_searchas select * from Customer where cust_level=VIP客戶exec VIPcustomer_search建立

18、存儲(chǔ)過(guò)程，顯示VIP客戶的基本信息5-2定義商品表上的存儲(chǔ)過(guò)程create procedure product_searchasselect productNo,productName from Productwhere productName=17寸顯示器exec product_search建立(jinl)存儲(chǔ)過(guò)程，顯示商品名為17寸顯示器的商品(shngpn)編號(hào)、商品名5-3定義(dngy)銷售明細(xì)表上的存儲(chǔ)過(guò)程create procedure detail_product_search cust_no char(8) as select a.customerNo,a.customer

19、Name,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建立一個(gè)帶輸入?yún)?shù)的存儲(chǔ)過(guò)程，根據(jù)輸入的客戶編號(hào)查詢客戶名稱、購(gòu)物發(fā)票編號(hào)、所購(gòu)商品名稱、數(shù)量、單價(jià)和購(gòu)物日期create procedure detail_consume_top5

20、asselect 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建立一個(gè)存儲(chǔ)過(guò)程，查詢消費(fèi)前五名的客戶定義(dngy)觸發(fā)器6-1 定義(dngy)客戶表上的觸發(fā)器6-1-1僅允許(ynx)dbo用戶可以刪除Customer表中的數(shù)據(jù)create t

21、rigger Tr_cust on Customer for delete asbegin if user=dbo commitelse begin print 僅允許dbo用戶可以刪除Customer表中的數(shù)據(jù)! rollback transaction endEnd6-1-2僅允許dbo用戶可以修改Customer表中的數(shù)據(jù)create trigger Tr_cust on Customer for update asbegin if user=dbo commitelse begin print 僅允許(ynx)dbo用戶可以修改(xigi)Customer表中的數(shù)據(jù)(shj)! rol

22、lback transaction endend6-2在SellDetail上創(chuàng)建觸發(fā)器，插入數(shù)據(jù)時(shí)要先檢查Customer表、Staff表和Product里面是否存在和SellDetail表同值的客戶編號(hào)、員工編號(hào)與商品編號(hào)。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表中不存在客戶編號(hào)或者Staff表中不存在員工編號(hào)或者Product中不存在商品編號(hào)，不能插入！ rollback transaction end測(cè)試：插入一條商品編號(hào)、員工編號(hào)不存在于相應(yīng)的表中的記錄insert into SellDetail values(I0000000021,P2013001,S2012003,C20