create database buyFlowers
go
create table admin --管理员表
(
adminId int not null identity(1000,1),
adminName varchar(40) not null,
adminPass varchar(40) not null,
)
create table users --客户表
(
usersId int not null identity(2000,1),
usersName varchar(40) not null,
usersPass varchar(40) not null,
usersRepass varchar(40) not null,
usersPassQuestion varchar(40),
usersPassReply varchar(40),
usersTrueName varchar(40) not null,
usersAddress varchar(100) not null,
usersPhone varchar(100) not null,
usersE_mail varchar(100) not null,
)
create table sort --产品类别表
(
sortId int not null identity(4000,1),
sortName varchar(40) not null,
)
create table goods --商品表
(
goodsId int not null identity(3000,1),
goodsName varchar(40) not null,
goodsmaterial varchar(200) not null,
goodsPackage varchar(200) not null,
goodsLanguage varchar(200) not null,
goodsPrice float not null,
goodsCent varchar(40) not null,
goodsScope varchar(100) not null,
goodsPlace varchar(100) not null,
goodsHabitus varchar(100) not null,
sortName varchar(40) not null,
constraint pk_goodsId primary key (goodsId),
)
create table produce --厂商表
(
produceId int not null identity(5000,1),
produceName varchar(40) not null,
)
--alter table produce
--drop constraint pk_produce
--drop table produceDetails
create table produceDetails --厂商产品明细表
(
produceId int not null,
produceName varchar(40) not null,
sortId int not null,
goodsId int not null,
)
--drop table orders
--drop table ordersDetails
create table orders --订单表
(
ordersId int not null identity(7000,1),
usersName varchar(40) not null,
ordersPrice varchar(40) not null,
ordersDatetime varchar(40) not null,
constraint pk_ordersId primary key (ordersId),
)
--alter table ordersDetails
--drop constraint fk_ordersId
--alter table orders
--drop constraint pk_ordersId
--drop table ordersDetails
create table ordersDetails --订单明细表
(
ordersId int not null ,
goodsName varchar(40) not null,
goodsPrice varchar(40) not null,
goodsCount int not null,
)
create table leaveLanguage --留言表
(
leaveLanguageId int not null identity(9000,1),
name varchar(40),
qq varchar(40),
email varchar(100),
leaveLanguage varchar(200) not null,
dateTime varchar(40) not null,
)
create table safeLog --安全日志表
(
logId int not null identity(10000,1),
usersId varchar(40) not null,
landingDatetime varchar(40),
exitDatetime varchar(40),
)
--drop table safeLog
create procedure Pro_orders @usersName varchar(40),
@ordersPrice varchar(40), @ordersDatetime varchar(40),@ordersId int output
as
begin
begin transaction
insert orders
values (@usersName,@ordersPrice,@ordersDatetime)
select @ordersId=max(ordersId)
from orders
commit transaction
end
