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
同济隐士
- 粉丝: 2
- 资源: 2
最新资源
- 基于Hough变换和区间算术确定MRI序列图像中肺部运动的研究
- jsp+ssm房屋租赁管理系统
- 泥沙自动震动过滤网设备sw17可编辑全套技术资料100%好用.zip
- 基于PCA算法的脑肿瘤T1加权MRI图像聚类分割研究与比较
- 南瓜种子分选振动机(step+exb+说明书)全套技术资料100%好用.zip
- 木材削片机step全套技术资料100%好用.zip
- 学生与图书管理系统|Java|JSP|web网站|增删改查
- 基于博弈论的自动多目标聚类方法研究及其应用
- 校园快递物流系统|SSM|JSP
- 基于期望最大化与分水岭变换的脑部MRI图像分割方法
- EV电动汽车VCU HIL BMS HIL硬件在环仿真 文件包括: 1 新能源电动汽车整车建模说明书, 2 HIL模型包含驾驶员模块,仪表模块,BCU整车控制器模块,MCU电机模块,TCU变速箱模块
- 基于Saprk开发实现的电商平台用户行为分析系统源码+文档说明.zip
- 基于Simulink自动化建模的MBD模型管理工具 鉴于Simulink和TargetLink均提供了自动化处理脚本命令,采用MATLAB编写脚本实现一系列关于软件模型搭建的冗余、耗时且容易出错的工
- comsol 锂枝晶加流动耦合电势场,浓度场生长过程中添加流场,改变枝晶形貌
- 无刷直流电机的MRAS模型参考自适应控制算法,仿真模型 a). 当直流无刷电机的转动惯量由1.23*10-3kg.m2变为3.23*10-3kg.m和5.23*10-3kg.m时,双闭环控制和自适应控
- 毕业论文设计 MATLAB 实现基于POA-CNN-BiLSTM鹈鹕算法优化卷积双向长短期记忆神经网络进行多输入单输出回归预测模型应用于产品质量控制与优化的详细项目实例(含完整的程序,GUI设计和代码
资源上传下载、课程学习等过程中有任何疑问或建议,欢迎提出宝贵意见哦~我们会及时处理!
点击此处反馈
- 1
- 2
前往页