为了正常的体验网站,请在浏览器设置里面开启Javascript功能!

会员(会员帐号PK

2017-09-27 26页 doc 782KB 27阅读

用户头像

is_721103

暂无简介

举报
会员(会员帐号PK会员(会员帐号PK 賣方評比 拍賣編號(PK)(FK) 賣方帳號(PK)(FK) 時間(PK) 評比 意見 ER-D 買方評比 拍賣品 拍賣編號(PK)(FK) 拍賣編號(PK) 買方帳號(PK)(FK) 商品名稱 時間(PK) 直接購買價 會員 評比 起標價格 買方 意見 出價增額 會員帳號(PK) 商品數量 密碼 所在地區 姓名 開始時間 賣方 性別 結束時間 出價 地址 分類代號(FK) 電話 拍賣編號(PK)(FK) 會員帳號(FK) 買方帳號(PK)(FK) 商品新舊 出價時間(PK) 付款方式 出價金...
会员(会员帐号PK
会员(会员帐号PK 賣方評比 拍賣編號(PK)(FK) 賣方帳號(PK)(FK) 時間(PK) 評比 意見 ER-D 買方評比 拍賣品 拍賣編號(PK)(FK) 拍賣編號(PK) 買方帳號(PK)(FK) 商品名稱 時間(PK) 直接購買價 會員 評比 起標價格 買方 意見 出價增額 會員帳號(PK) 商品數量 密碼 所在地區 姓名 開始時間 賣方 性別 結束時間 出價 地址 分類代號(FK) 電話 拍賣編號(PK)(FK) 會員帳號(FK) 買方帳號(PK)(FK) 商品新舊 出價時間(PK) 付款方式 出價金額 交貨方式 數量 意見 問與答 分類 分類代號(PK) 流水號(PK) 分類主題 拍賣編號(FK) 父親分類(FK) 買方帳號(FK) 買方問題 賣方回答 問題時間 回答時間 1 Schema 會員(會員帳號PK,密碼,姓名,性別,地址,電話,買評價,賣評價) 分類(分類代號PK,分類主題,父親分類FK) 拍賣品(拍賣編號PK,商品名稱,直接購買價,起標價格,出價增額,商品數量,所在地區,開始時間,結束時間,分類代號FK,會員帳號FK,商品新舊,付款方式,交貨方式,目前出價次數,得標者編號) 賣方評比(拍賣編號PK.FK,賣方帳號PK.FK,時間PK,評比,意見) 買方評比(拍賣編號PK.FK,買方帳號PK.FK,時間PK,評比,意見) 出價記錄(拍賣編號PK.FK,買方帳號PK.FK,出價時間PK,出價金額,數量,意見,狀態) 問與答(流水號PK,拍賣編號FK,買方帳號FK,買方問題,賣方回答,問題時間,回答時間) 2 ◎建立資料表 <<建立會員>> create table 會員( 會員帳號 char(10) primary key, 密碼 char(10), 姓名 char(10), 性別 char(1), --1男 2女 地址 varchar(30), 電話 char(13), 買評價 int default 0, 賣評價 int default 0 ) --輸入會員資料 Insert into 會員(會員帳號,密碼,姓名,性別) values('M001','1234','王仁甫',1) Insert into 會員(會員帳號,密碼,姓名,性別) values('M002','2345','孫協志',1) Insert into 會員(會員帳號,密碼,姓名,性別) values('M003','3456','梁靜茹',2) Insert into 會員(會員帳號,密碼,姓名,性別) values('M004','4567','張惠妹',2) <<建立分類>> Create Table 分類( 分類代號 int primary key , 分類主題 char(10), 父親分類 int ) --輸入分類資料 Insert into 分類(分類代號,分類主題) values(1,'電腦') Insert into 分類(分類代號,分類主題) values(2,'圖書') Insert into 分類(分類代號,分類主題) values(3,' 服飾 ') Insert into 分類(分類代號,分類主題) values(4,'汽車') Insert into 分類(分類代號,分類主題,父親分類) values(5,'桌上型',1) 3 Insert into 分類(分類代號,分類主題,父親分類) values(6,'女裝',3) Insert into 分類(分類代號,分類主題) values(7,'其他') <<建立拍賣品>> create table 拍賣品( 拍賣編號 char(10) primary key, 商品名稱 char(10), 直接購買價 money default 9999, 起標價格 money default 1, 出價增額 money default 10, 商品數量 int default 1, 所在地區 char(10), 開始時間 datetime default getdate(), 結束時間 datetime default getdate()+10, 分類代號 int default 7, 會員帳號 char(10), 商品新舊 char(1) default 1, --1新 2舊 付款方式 char(1) default 'A',--A轉帳 B現金 交貨方式 char(1) default 'A',--A寄送 B面交 目前出價次數 int default 0, 得標者編號 char(10) null, ) --輸入拍賣品資料 Insert into 拍賣品(拍賣編號,商品名稱,所在地區,會員帳號,分類代號,得標者編號) values('S001','電腦','台北市','M001',1,'M002') Insert into 拍賣品(拍賣編號,商品名稱,所在地區,會員帳號,分類代號) values('S002','隨身聽','台北市','M001',1) Insert into 拍賣品(拍賣編號,商品名稱,所在地區,會員帳號,分類代號,得標者編號) values('S003','法拉利','高雄市','M003',4,'M001') Insert into 拍賣品(拍賣編號,商品名稱,所在地區,會員帳號,分類代號) 4 values('S004','電扇','新竹市','M003',7) Insert into 拍賣品(拍賣編號,商品名稱,所在地區,會員帳號,分類代號) values('S005','娃娃','花蓮縣','M004',3) Insert into 拍賣品(拍賣編號,商品名稱,所在地區,會員帳號,分類代號) values('S006','牛仔褲','台北市','M004',3) Insert into 拍賣品(拍賣編號,商品名稱,所在地區,會員帳號,分類代號,得標者編號) values('S007','背包','新竹市','M004',3,'M001') <<建立賣方評比>> create table 賣方評比( 拍賣編號 char(10), 賣方帳號 char(10), 時間 datetime default getdate(), 評比 int default 0, --1差2普通3良好 意見 char(100), constraint pk_salecon primary key(拍賣編號,賣方帳號,時間) ) --輸入賣方評比 Insert into 賣方評比(拍賣編號,賣方帳號,評比) values('s001','m001',3) Insert into 賣方評比(拍賣編號,賣方帳號,評比) values('s003','m003',3) 5 <<建立買方評比>> create table 買方評比( 拍賣編號 char(10), 買方帳號 char(10), 時間 datetime default getdate(), 評比 int default 0, 意見 char(100), constraint pk_buycon primary key(拍賣編號,買方帳號,時間) ) --輸入買方評比 Insert into 買方評比(拍賣編號,買方帳號,評比) values('s001','m002',3) Insert into 買方評比(拍賣編號,買方帳號,評比) values('S003','M001',3) <<建立出價記錄>> create table 出價記錄( 拍賣編號 char(10), 買方帳號 char(10), 出價時間 datetime default getdate(), 出價金額 money, 數量 int default 1, 意見 char(100), 狀態 char(1) default 'N',--N未得標 S已得標 constraint pk_gore primary key(出價時間,買方帳號,拍賣編號) ) --輸入出價記錄資料 Insert into 出價記錄(拍賣編號,買方帳號,出價時間,出價金額) values('S002','M003','2004/12/1',100) Insert into 出價記錄(拍賣編號,買方帳號,出價時間,出價金額) values('S002','M002','2004/12/15',200) Insert into 出價記錄(拍賣編號,買方帳號,出價時間,出價金額) values('S003','M001','2004/12/1',100) Insert into 出價記錄(拍賣編號,買方帳號,出價時間,出價金額) 6 values('S003','M002','2004/12/3',120) Insert into 出價記錄(拍賣編號,買方帳號,出價時間,出價金額) values('S003','M001','2004/12/5',150) Insert into 出價記錄(拍賣編號,買方帳號,出價時間,出價金額) values('S003','M004','2004/12/6',1000) Insert into 出價記錄(拍賣編號,買方帳號,出價時間,出價金額) values('S003','M001','2004/12/7',5000) Insert into 出價記錄(拍賣編號,買方帳號,出價時間,出價金額) values('S003','M004','2004/12/6',15000) Insert into 出價記錄(拍賣編號,買方帳號,出價時間,出價金額) values('S004','M001','2004/12/6',200) Insert into 出價記錄(拍賣編號,買方帳號,出價時間,出價金額) values('S004','M002','2004/12/7',300) Insert into 出價記錄(拍賣編號,買方帳號,出價時間,出價金額,狀態) values('S001','M002','2004/12/19',100,'S') Insert into 出價記錄(拍賣編號,買方帳號,出價時間,出價金額,狀態) values('S003','M001','2004/12/13',22000,'S') Insert into 出價記錄(拍賣編號,買方帳號,出價時間,出價金額,狀態) values('S007','M001','2004/12/15',200,'S') <<建立問與答>> Create Table 問與答( 流水號 int primary key identity(1,1), 拍賣編號 char(10), 買方帳號 char(10), 買方問題 char(100), 7 賣方回答 char(100), 問題時間 datetime default getdate(), 回答時間 datetime ) --輸入問與答資料 insert into 問與答 values ('S003','M001','可以便宜一點嗎??','很抱歉,沒辦法唷!!^^','2004/11/29','2004/11/30') ◎建立View 1.列出各個商品之出價記錄,vw_出價記錄(拍賣編號,商品名稱,直接購買價,起標價格,出價金額,狀態,出價者編號) create view vw_出價記錄 as select a.拍賣編號, 商品名稱, 直接購買價, 起標價格, a.出價金額, 狀態=case a.狀態 when 'S' then '得標' when 'N' then '未得' end, a.買方帳號 as 出價者編號 from 出價記錄 a join 拍賣品 b on a.拍賣編號=b.拍賣編號 8 2.列出各個商品出價最高價,vw_商品出價最高價(拍賣編號,商品名稱,出價次數,目前最高價) create view vw_商品出價最高價 as select 拍賣編號,商品名稱,count(拍賣編號) as 出價次數,max(出價金額) as 目前最高價 from vw_出價記錄 group by 拍賣編號,商品名稱 3.求出各個商品之最高出價者是誰,vw_商品最高出價者(拍賣編號,商品名稱,最高價,出價者編號) Create View vw_商品最高出價者 as select 拍賣編號,商品名稱,出價金額 as 最高價,出價者編號 from vw_出價記錄 x where 出價金額=( select max(出價金額) from vw_出價記錄 where x.拍賣編號=拍賣編號 ) 4.那此"分類"拍賣品(以出價次數)和性別之比較,vw_分類性別百分比(分類主題,男,女) Create View vw_分類性別百分比 As Select 分類主題, 男 = 100 * (Select count(*) From 出價記錄 9 Where 拍賣編號 in (Select 拍賣編號 From 拍賣品 Where 分類代號=R1.分類代號) and 買方帳號 in (Select 會員帳號 From 會員 Where 性別 = 1))/ (Select count(*) From 出價記錄 Where 拍賣編號 in (Select 拍賣編號 From 拍賣品 Where 分類代號=R1.分類代號)), 女 = 100 * (Select count(*) From 出價記錄 Where 拍賣編號 in (Select 拍賣編號 From 拍賣品 Where 分類代號=R1.分類代號) and 買方帳號 in (Select 會員帳號 From 會員 Where 性別 = 2))/ (Select count(*) From 出價記錄 Where 拍賣編號 in (Select 拍賣編號 From 拍賣品 Where 分類代號=R1.分類代號)) From 分類 R1 Where (Select count(*) From 出價記錄 Where 拍賣編號 in (Select 拍賣編號 From 拍賣品 Where 分類代號=R1.分類代號)) <> 0 Union Select 分類主題,男='0',女='0' From 分類 R1 Where (Select count(*) From 出價記錄 Where 拍賣編號 in (Select 拍賣編號 From 拍賣品 Where 分類代號=R1.分類代號)) = 0 5. 所有的分類與他的父分類的顯示,vw_分類一覽表(分類代號,分類主題,父親分類主題) Create View vw_分類一覽表 As Select 分類代號, 分類主題, 父親分類主題 = (Select 分類主題 From 分類 Where 分類代號=R1.父親分類) From 分類 R1 10 6.找出購買最多商品的會員,前10名,vw_購買最多商品的會員 (會員帳號,得標數量) Create View vw_購買最多商品的會員 As Select Top 10 會員帳號 = 買方帳號, 得標數量 = Count(*) From 出價記錄 Where 狀態 = 'S' Group By 買方帳號 7.查出評比,vw_會員評比(會員帳號,評比次數,被評比次數,分數) Create View vw_會員評比 As Select 會員帳號, 評比次數 = isnull((Select count(*) From 賣方評比 Where 賣方帳號 = R1. 會員帳號),0) + isnull((Select count(*) From 買方評比 Where 買方帳號 = R1.會員帳號),0), 被評比次數= isnull((Select count(*) From 賣方評比 R2 Join 拍賣品 R3 On R2.拍賣編號 = R3.拍賣編號Where R3.會員帳號 = R1.會員帳號),0) + isnull((Select count(*) From 買方評比 R2 Join 拍賣品 R3 On R2.拍賣編號 = R3.拍賣編號Where R3.會員帳號 = R1.會員帳號),0), 分數 = isnull((Select sum(評比) From 賣方評比 R2 Join 拍賣品 R3 On R2. 拍賣編號 = R3.拍賣編號Where R3.會員帳號 = R1.會員帳號),0) + isnull((Select sum(評比) From 買方評比 R2 Join 拍賣品 R3 On R2.拍賣編 號 = R3.拍賣編號Where R3.會員帳號 = R1.會員帳號),0) From 會員 R1 11 8.商品的出價數量,vw_商品出價數量(拍賣編號,商品名稱,出價數量) Create View vw_商品出價數量 As Select 拍賣編號, 商品名稱, 出價數量 = isnull((Select count(*) From 出價記錄 where 拍賣編號 = R1.拍賣編號),0) From 拍賣品 R1 9.每月熱門出價商品,看出價數量,vw_每月熱門出價商品(年,月,拍賣編號,商品名稱,出價數量) Create View vw_每月熱門出價商品 As Select 年 = datepart(year,R1.出價時間), 月 = datepart(month,R1.出價時間), R1.拍賣編號, R2.商品名稱, 出價數量 = count(*) From 出價記錄 R1 Join 拍賣品 R2 On R1.拍賣編號=R2.拍賣編號 Group By datepart(year,R1.出價時間),datepart(month,R1.出價時間),R1.拍賣編號,R2.商品名稱 12 10. 得標後買家未評比次數,vw_得標後買家未評比次數(會員帳號,未評比次數) Create View vw_得標後買家未評比次數 As Select 會員帳號 = R1.買方帳號, 未評比次數 = count(*) From 出價記錄 R1 Left Join 買方評比 R2 On R1.買方帳號 = R2.買方帳號 And R1.拍賣編號 = R2.拍賣編號 Where R1.狀態 = 'S' and R2.評比 is Null Group By R1.買方帳號 ◎建立Trigger 1.建立一個Trigger,當會員資料表裡的會員資料刪除,則出價記錄裡的買方出價資料也會被刪除 Create Trigger trDelVIPdata on 會員 for delete as declare @id varchar(10) select @id=會員帳號 from deleted delete 出價記錄 where 買方帳號=@id 刪除前: 13 刪除後: 2.建立一個Trigger,當刪除一筆[出價記錄]時,會至[拍賣品]中的<目前出價次數>-1 Create Trigger trDelBidCount on 出價記錄 for delete as declare @oid varchar(10) select @oid=拍賣編號 from deleted update 拍賣品 set 目前出價次數 = 目前出價次數 - 1 where 拍賣編號=@oid 刪除前: 14 刪除後: 15 3.建立一個Trigger,當新增一筆[出價記錄]時,會至[拍賣品]中的<目前出價次數>+1 Create Trigger trInsBidCount on 出價記錄 for insert as declare @oid varchar(10) select @oid=拍賣編號 from inserted update 拍賣品 set 目前出價次數 = 目前出價次數 + 1 where 拍賣編號=@oid 新增前: 新增後: 16 4.建立一個Trigger,當會員資料表裡的會員資料刪除,則問與答裡的買方問題 資料也會被刪除 Create Trigger trDelQandA on 會員 for delete as declare @id varchar(10) select @id=會員帳號 from deleted delete 問與答 where 買方帳號=@id 刪除前: 17 刪除後: 5.建立一個Trigger,當刪除父親類別ID時,其父親類別所屬的子類別也會跟 著被刪除 Create Trigger trDelkind on 分類 for delete as declare @kid int select @kid=分類代號 from deleted delete 分類 where 父親分類=@kid 刪除前: 刪除後: 18 ◎建立Stored Procedure 1.輸入拍賣編號,將狀態設為已結標,拍賣品的得標者編號改為最高出價者編號 Create proc sp_SetState @sno char(10) as Update 出價記錄 Set 狀態='S' Where 拍賣編號=@sno And 出價金額 = (Select Max( 出價金額) From 出價記錄 Where 拍賣編號=@sno ) Update 拍賣品 Where 狀 Set 得標者編號 = (Select 買方帳號 From 出價記錄態='S' And 拍賣編號=@sno) Where 拍賣編號=@sno 輸入前: 輸入後: sp_SetState ‘S002’ 19 2.自動將所有拍賣品符合結標時間的商品結標 Create proc sp_AutoState As Update 出價記錄 Set 狀態='S' From 出價記錄 R1 Where 拍賣編號 in (Select 拍賣編號 From 拍賣品 Where 得標者編號 is Null And 結束時間 <= getdate()) And 出價金額 = (Select Max( 出價金額) From 出價記錄 Where 拍賣編號 = R1.拍賣編號 ) Update 拍賣品 Set 得標者編號 = (Select 買方帳號 From 出價記錄 Where 狀態='S' and 拍賣編號=R1.拍賣編號) From 拍賣品 R1 Where 拍賣編號 in (Select 拍賣編號 From 拍賣品 Where 得標者編號 is Null And 結束時間 <= getdate()) 執行前: 20 執行後: sp_AutoState 21 3.查詢會員資料 Create proc sp_QueryNumber @mid char(10) As Select * From 會員 Where 會員帳號 = @mid 測試: sp_QueryNumber ‘M001’ 4.驗正會員帳號密碼 (正確回傳1 否則回傳0) Create proc sp_CheckPassword @mid char(10) , @pwd char(10) As Select 會員帳號 From 會員 Where 會員帳號 = @mid and 密碼 = @pwd If @@rowcount > 0 return 1 else return 0 測試: declare @t int exec @t = sp_CheckPassword 'M001','1234' select @t 5.查詢會員有那些拍賣品 Create proc sp_NumberHasProduct @mid char(10) As Select 拍賣編號,商品名稱,會員帳號,R1.分類代號,R2.分類主題,開始時間,結束時間, isnull(得標者編號,'尚無得標者') From 拍賣品 R1 Join 分類 R2 On R1.分類代號 = R2.分類代號 Where 會員帳號=@mid 22 測試: sp_NumberHasProduct 'M001' 23
/
本文档为【会员(会员帐号PK】,请使用软件OFFICE或WPS软件打开。作品中的文字与图均可以修改和编辑, 图片更改请在作品中右键图片并更换,文字修改请直接点击文字进行修改,也可以新增和删除文档中的内容。
[版权声明] 本站所有资料为用户分享产生,若发现您的权利被侵害,请联系客服邮件isharekefu@iask.cn,我们尽快处理。 本作品所展示的图片、画像、字体、音乐的版权可能需版权方额外授权,请谨慎使用。 网站提供的党政主题相关内容(国旗、国徽、党徽..)目的在于配合国家政策宣传,仅限个人学习分享使用,禁止用于任何广告和商用目的。

历史搜索

    清空历史搜索