Greenbook二手书交易系统源码

create database net_eicp_greenbook; use net_eicp_greenbook; create table GB_user ( U_id int identity(10000,1)primary key, U_loginname nvarchar(20)not null unique, U_pswd binary(16)not null, U_realname nvarchar(20), U_addr nvarchar(100), U_mobile nvarchar(20), U_email nvarchar(50)not null unique, U_qq bigint, U_homepage nvarchar(100), U_privacyoption smallint, U_avatar binary(16), U_registered datetime, U_buycount int not null, U_buytotal money not null, U_buystars int not null, U_sellcount int not null, U_selltotal money not null, U_sellstars int not null ); create table GB_category ( C_id int identity(1,1)primary key, C_parent nvarchar(20)not null, C_child nvarchar(20)not null ); create table GB_book ( B_id int identity(1,1)primary key, B_title nvarchar(100)not null, B_isbn nvarchar(20), B_author nvarchar(100), B_publisher nvarchar(50), B_version nvarchar(20), B_language nvarchar(30), B_originalprice numeric(5,2)not null check(B_originalprice>=0), B_categoryid int references GB_category(C_id), B_introduction ntext, B_visits int not null ); create table GB_supply ( S_id int identity(1,1)primary key, S_bookid int not null references GB_book(B_id)on delete cascade, S_sellerid int not null references GB_user(U_id)on delete cascade, S_amount int not null check(S_amount>=0), S_price numeric(5,2)not null check(S_price>0), S_quality numeric(2,0)check(S_quality>0), S_photohash binary(16), S_description ntext ); create table GB_request ( R_id int identity(1,1)primary key, R_buyerid int not null references GB_user(U_id)on delete cascade, R_bookid int not null references GB_book(B_id)on delete cascade, R_amount int not null, R_reqtime datetime not null, R_reqsupply int references GB_supply(S_id), unique(R_buyerid,R_bookid) ); create table GB_transaction ( T_id int identity(1,1)primary key, T_buyerid int not null references GB_user(U_id)on delete cascade, T_bookid int not null references GB_book(B_id)on delete cascade, T_amount int not null, T_reqtime datetime not null, T_supplyid int not null references GB_supply(S_id), T_dealtime datetime, T_rate tinyint not null check(T_rate in(1,2,3,4,5)), T_ratetext ntext, T_revrated bit ); create table GB_message ( M_id int identity(1,1)primary key, M_sender int not null references GB_user(U_id), M_receiver int not null references GB_user(U_id)on delete cascade, M_message ntext not null, M_time datetime not null, M_read bit ); create index index1 on GB_message(M_sender); create index index2 on GB_message(M_receiver); create index index3 on GB_book(B_title); create index index4 on GB_user(U_loginname); create index index5 on GB_request(R_buyerid); create index index6 on GB_request(R_bookid); create index index7 on GB_transaction(T_buyerid); insert into GB_user values('系统管理员',0x00,null,null,null,'bspub@139.com',null,null,null,null,null,0,0,0,0,0,0); create procedure sp_readssmss @uid int,@mode smallint as begin if @mode=0 begin /*所有未读*/ select M_sender,U_loginname,M_message,M_time,M_id from GB_message,GB_user where M_receiver=@uid and M_read=0 and U_id=M_sender order by M_id desc update GB_message set M_read=1 where M_receiver=@uid and M_read=0 end else if @mode=1 begin /*所有*/ select M_sender,U_loginname,M_message,M_time,M_id from GB_message,GB_user where M_receiver=@uid and U_id=M_sender order by M_id desc update GB_message set M_read=1 where M_receiver=@uid and M_read=0 end else if @mode=2 begin /*前20条*/ select top 20 M_sender,U_loginname,M_message,M_time,M_id from GB_message,GB_user where M_receiver=@uid and U_id=M_sender order by M_id desc update GB_message set M_read=1 where M_id in (select top 20 M_id from GB_message where M_receiver=@uid order by M_id desc) end end create trigger tg_onremoveuser on GB_user instead of delete as begin update GB_user set U_privacyoption=3 where U_id in (select U_id from deleted) end