1.9的部分语句:
/****** Object: Table [dbo].[bsp_suitpromotions] Script Date: 12/09/2014 14:07:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [bsp_suitpromotions](
[pmid] [int] IDENTITY(1,1) NOT NULL,
[starttime1] [datetime] NOT NULL,
[endtime1] [datetime] NOT NULL,
[starttime2] [datetime] NOT NULL,
[endtime2] [datetime] NOT NULL,
[starttime3] [datetime] NOT NULL,
[endtime3] [datetime] NOT NULL,
[userranklower] [smallint] NOT NULL,
[state] [tinyint] NOT NULL,
[name] [nvarchar](50) NOT NULL,
[quotaupper] [int] NOT NULL,
[onlyonce] [tinyint] NOT NULL,
CONSTRAINT [PK_bsp_suitpromotions] PRIMARY KEY CLUSTERED
(
[pmid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [state_starttime_endtime] ON [bsp_suitpromotions]
(
[state] ASC,
[starttime1] ASC,
[endtime1] ASC,
[starttime2] ASC,
[endtime2] ASC,
[starttime3] ASC,
[endtime3] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'促销活动id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_suitpromotions', @level2type=N'COLUMN',@level2name=N'pmid'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'开始时间1' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_suitpromotions', @level2type=N'COLUMN',@level2name=N'starttime1'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'结束时间1' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_suitpromotions', @level2type=N'COLUMN',@level2name=N'endtime1'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'开始时间2' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_suitpromotions', @level2type=N'COLUMN',@level2name=N'starttime2'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'结束时间2' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_suitpromotions', @level2type=N'COLUMN',@level2name=N'endtime2'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'开始时间3' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_suitpromotions', @level2type=N'COLUMN',@level2name=N'starttime3'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'结束时间3' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_suitpromotions', @level2type=N'COLUMN',@level2name=N'endtime3'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'最低用户等级' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_suitpromotions', @level2type=N'COLUMN',@level2name=N'userranklower'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'状态' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_suitpromotions', @level2type=N'COLUMN',@level2name=N'state'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_suitpromotions', @level2type=N'COLUMN',@level2name=N'name'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'配额上限' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_suitpromotions', @level2type=N'COLUMN',@level2name=N'quotaupper'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'限购一次 ' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_suitpromotions', @level2type=N'COLUMN',@level2name=N'onlyonce'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'套装促销活动表' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_suitpromotions'
GO
/****** Object: Table [dbo].[bsp_suitproducts] Script Date: 12/09/2014 14:07:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [bsp_suitproducts](
[recordid] [int] IDENTITY(1,1) NOT NULL,
[pmid] [int] NOT NULL,
[pid] [int] NOT NULL,
[discount] [int] NOT NULL,
[number] [int] NOT NULL,
CONSTRAINT [PK_bsp_suitproducts] PRIMARY KEY CLUSTERED
(
[recordid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [pid_pmid] ON [bsp_suitproducts]
(
[pid] ASC,
[pmid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [pmid_pid] ON [bsp_suitproducts]
(
[pmid] ASC,
[pid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'记录id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_suitproducts', @level2type=N'COLUMN',@level2name=N'recordid'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'促销活动id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_suitproducts', @level2type=N'COLUMN',@level2name=N'pmid'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'商品id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_suitproducts', @level2type=N'COLUMN',@level2name=N'pid'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'折扣值' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_suitproducts', @level2type=N'COLUMN',@level2name=N'discount'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数量' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_suitproducts', @level2type=N'COLUMN',@level2name=N'number'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'套装商品表' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_suitproducts'
GO
/****** Object: Table [dbo].[bsp_singlepromotions] Script Date: 12/09/2014 14:07:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [bsp_singlepromotions](
[pmid] [int] IDENTITY(1,1) NOT NULL,
[pid] [int] NOT NULL,
[starttime1] [datetime] NOT NULL,
[endtime1] [datetime] NOT NULL,
[starttime2] [datetime] NOT NULL,
[endtime2] [datetime] NOT NULL,
[starttime3] [datetime] NOT NULL,
[endtime3] [datetime] NOT NULL,
[userranklower] [smallint] NOT NULL,
[state] [tinyint] NOT NULL,
[name] [nvarchar](50) NOT NULL,
[slogan] [nvarchar](60) NOT NULL,
[discounttype] [tinyint] NOT NULL,
[discountvalue] [int] NOT NULL,
[coupontypeid] [int] NOT NULL,
[paycredits] [int] NOT NULL,
[isstock] [tinyint] NOT NULL,
[stock] [int] NOT NULL,
[quotalower] [int] NOT NULL,
[quotaupper] [int] NOT NULL,
[allowbuycount] [int] NOT NULL,
CONSTRAINT [PK_bsp_singlepromotions] PRIMARY KEY CLUSTERED
(
[pmid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [pid_starttime_endtime_state] ON [bsp_singlepromotions]
(
[pid] ASC,
[starttime1] ASC,
[endtime1] ASC,
[starttime2] ASC,
[endtime2] ASC,
[starttime3] ASC,
[endtime3] ASC,
[state] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'促销活动id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_singlepromotions', @level2type=N'COLUMN',@level2name=N'pmid'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'商品id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_singlepromotions', @level2type=N'COLUMN',@level2name=N'pid'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'开始时间1' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_singlepromotions', @level2type=N'COLUMN',@level2name=N'starttime1'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'结束时间1' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_singlepromotions', @level2type=N'COLUMN',@level2name=N'endtime1'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'开始时间2' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_singlepromotions', @level2type=N'COLUMN',@level2name=N'starttime2'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'结束时间2' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_singlepromotions', @level2type=N'COLUMN',@level2name=N'endtime2'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'开始时间3' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_singlepromotions', @level2type=N'COLUMN',@level2name=N'starttime3'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'结束时间3' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_singlepromotions', @level2type=N'COLUMN',@level2name=N'endtime3'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'最低用户等级' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_singlepromotions', @level2type=N'COLUMN',@level2name=N'userranklower'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'状态' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_singlepromotions', @level2type=N'COLUMN',@level2name=N'state'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_singlepromotions', @level2type=N'COLUMN',@level2name=N'name'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'广告语' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_singlepromotions', @level2type=N'COLUMN',@level2name=N'slogan'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'折扣类型(0代表折扣,1代表直降,2代表折后价)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_singlepromotions', @level2type=N'COLUMN',@level2name=N'discounttype'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'折扣值' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_singlepromotions', @level2type=N'COLUMN',@level2name=N'discountvalue'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'赠送优惠劵类型id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_singlepromotions', @level2type=N'COLUMN',@level2name=N'coupontypeid'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'支付积分' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_singlepromotions', @level2type=N'COLUMN',@level2name=N'paycredits'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否限制库存' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_singlepromotions', @level2type=N'COLUMN',@level2name=N'isstock'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'库存数量' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_singlepromotions', @level2type=N'COLUMN',@level2name=N'stock'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'配额下限' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_singlepromotions', @level2type=N'COLUMN',@level2name=N'quotalower'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'配额上限' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_singlepromotions', @level2type=N'COLUMN',@level2name=N'quotaupper'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'最大购买数量' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_singlepromotions', @level2type=N'COLUMN',@level2name=N'allowbuycount'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'单品促销活动表' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_singlepromotions'
GO
/****** Object: Table [dbo].[bsp_signproducts] Script Date: 12/09/2014 14:07:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [bsp_signproducts](
[recordid] [int] IDENTITY(1,1) NOT NULL,
[sign] [char](15) NOT NULL,
[pid] [int] NOT NULL,
CONSTRAINT [PK_bsp_signproducts] PRIMARY KEY CLUSTERED
(
[recordid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CREATE NONCLUSTERED INDEX [sign_pid] ON [bsp_signproducts]
(
[sign] ASC,
[pid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'记录id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_signproducts', @level2type=N'COLUMN',@level2name=N'recordid'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'标记' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_signproducts', @level2type=N'COLUMN',@level2name=N'sign'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'商品id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_signproducts', @level2type=N'COLUMN',@level2name=N'pid'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'签名商品表' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_signproducts'
GO
/****** Object: Table [dbo].[bsp_shipaddresses] Script Date: 12/09/2014 14:07:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [bsp_shipaddresses](
[said] [int] IDENTITY(1,1) NOT NULL,
[uid] [int] NOT NULL,
[regionid] [smallint] NOT NULL,
[isdefault] [tinyint] NOT NULL,
[alias] [nvarchar](50) NOT NULL,
[consignee] [nvarchar](20) NOT NULL,
[mobile] [varchar](15) NOT NULL,
[phone] [varchar](12) NOT NULL,
[email] [varchar](50) NOT NULL,
[zipcode] [char](6) NOT NULL,
[address] [nvarchar](150) NOT NULL,
CONSTRAINT [PK_bsp_shipaddresses] PRIMARY KEY CLUSTERED
(
[said] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CREATE NONCLUSTERED INDEX [mobile] ON [bsp_shipaddresses]
(
[mobile] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [uid_isdefault] ON [bsp_shipaddresses]
(
[uid] ASC,
[isdefault] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'配送地址id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_shipaddresses', @level2type=N'COLUMN',@level2name=N'said'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'用户id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_shipaddresses', @level2type=N'COLUMN',@level2name=N'uid'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'区域id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_shipaddresses', @level2type=N'COLUMN',@level2name=N'regionid'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否为默认地址' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_shipaddresses', @level2type=N'COLUMN',@level2name=N'isdefault'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'地址别名' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_shipaddresses', @level2type=N'COLUMN',@level2name=N'alias'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'收货人' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_shipaddresses', @level2type=N'COLUMN',@level2name=N'consignee'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'手机' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_shipaddresses', @level2type=N'COLUMN',@level2name=N'mobile'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'固定电话' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_shipaddresses', @level2type=N'COLUMN',@level2name=N'phone'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'邮箱' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_shipaddresses', @level2type=N'COLUMN',@level2name=N'email'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'邮政编码' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_shipaddresses', @level2type=N'COLUMN',@level2name=N'zipcode'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'详细地址' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_shipaddresses', @level2type=N'COLUMN',@level2name=N'address'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'配送地址表' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_shipaddresses'
GO
SET IDENTITY_INSERT [bsp_shipaddresses] ON
INSERT [bsp_shipaddresses] ([said], [uid], [regionid], [isdefault], [alias], [consignee], [mobile], [phone], [email], [zipcode], [address]) VALUES (30, 1, 411, 0, N'家', N'张三', N'15555555555', N'', N'', N' ', N'中山路3号')
INSERT [bsp_shipaddresses] ([said], [uid], [regionid], [isdefault], [alias], [consignee], [mobile], [phone], [email], [zipcode], [address]) VALUES (31, 1, 2321, 0, N'家', N'李四', N'15511429689', N'', N'', N' ', N'解放路')
INSERT [bsp_shipaddresses] ([said], [uid], [regionid], [isdefault], [alias], [consignee], [mobile], [phone], [email], [zipcode], [address]) VALUES (32, 1, 1467, 1, N'单位', N'王五', N'15555555555', N'', N'', N' ', N'解放路')
SET IDENTITY_INSERT [bsp_shipaddresses] OFF
/****** Object: Table [dbo].[bsp_users] Script Date: 12/09/2014 14:07:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [bsp_users](
[uid] [int] IDENTITY(1,1) NOT NULL,
[username] [nchar](20) NOT NULL,
[email] [char](50) NOT NULL,
[mobile] [char](15) NOT NULL,
[password] [char](32) NOT NULL,
[userrid] [smallint] NOT NULL,
[admingid] [smallint] NOT NULL,
[nickname] [nchar](20) NOT NULL,
[avatar] [char](40) NOT NULL,
[paycredits] [int] NOT NULL,
[rankcredits] [int] NOT NULL,
[verifyemail] [tinyint] NOT NULL,
[verifymobile] [tinyint] NOT NULL,
[liftbantime] [datetime] NOT NULL,
[salt] [nchar](6) NOT NULL,
CONSTRAINT [PK_bsp_users] PRIMARY KEY CLUSTERED
(
[uid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CREATE NONCLUSTERED INDEX [admingid] ON [bsp_users]
(
[admingid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [email] ON [bsp_users]
(
[email] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [mobile] ON [bsp_users]
(
[mobile] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [username] ON [bsp_users]
(
[username] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [userrid] ON [bsp_users]
(
[userrid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'用户id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_users', @level2type=N'COLUMN',@level2name=N'uid'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'用户名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_users', @level2type=N'COLUMN',@level2name=N'username'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'邮箱' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_users', @level2type=N'COLUMN',@level2name=N'email'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'手机' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_users', @level2type=N'COLUMN',@level2name=N'mobile'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'密码' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_users', @level2type=N'COLUMN',@level2name=N'password'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'用户等级id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_users', @level2type=N'COLUMN',@level2name=N'userrid'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'商城管理员组id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_users', @level2type=N'COLUMN',@level2name=N'admingid'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'昵称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_users', @level2type=N'COLUMN',@level2name=N'nickname'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'头像' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_users', @level2type=N'COLUMN',@level2name=N'avatar'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'支付积分' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_users', @level2type=N'COLUMN',@level2name=N'paycredits'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'等级积分' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_users', @level2type=N'COLUMN',@level2name=N'rankcredits'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否验证邮箱' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_users', @level2type=N'COLUMN',@level2name=N'verifyemail'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否验证手机' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_users', @level2type=N'COLUMN',@level2name=N'verifymobile'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'解禁时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_users', @level2type=N'COLUMN',@level2name=N'liftbantime'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'盐值' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_users', @level2type=N'COLUMN',@level2name=N'salt'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'用户表' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_users'
GO
SET IDENTITY_INSERT [bsp_users] ON
INSERT [bsp_users] ([uid], [username], [email], [mobile], [password], [userrid], [admingid], [nickname], [avatar], [paycredits], [rankcredits], [verifyemail], [verifymobile], [liftbantime], [salt]) VALUES (1, N'brnshop ', N'brnshop@brnshop.com ', N'15555555555 ', N'd720e94022edc164faacfac7d77a5328', 8, 2, N'brnshop ', N' ', 404, 1550, 1, 1, CAST(0x0000000000000000 AS DateTime), N'125641')
INSERT [bsp_users] ([uid], [username], [email], [mobile], [password], [userrid], [admingid], [nickname], [avatar], [paycredits], [rankcredits], [verifyemail], [verifymobile], [liftbantime], [salt]) VALUES (2, N'111111@163.com ', N' ', N' ', N'bb6bb2b3a5a990e1d9880d615847f7b3', 7, 1, N'BSP3648569 ', N' ', 4, 4, 0, 0, CAST(0x0000000000000000 AS DateTime), N'927549')
INSERT [bsp_users] ([uid], [username], [email], [mobile], [password], [userrid], [admingid], [nickname], [avatar], [paycredits], [rankcredits], [verifyemail], [verifymobile], [liftbantime], [salt]) VALUES (3, N'555555 ', N' ', N' ', N'4c22ca987df47b8528847eb0f2549f51', 7, 1, N'BSP2259685 ', N' ', 4, 4, 0, 0, CAST(0x0000000000000000 AS DateTime), N'754144')
INSERT [bsp_users] ([uid], [username], [email], [mobile], [password], [userrid], [admingid], [nickname], [avatar], [paycredits], [rankcredits], [verifyemail], [verifymobile], [liftbantime], [salt]) VALUES (4, N'888888 ', N' ', N' ', N'fe212028b961782258a3ecec81a83fad', 7, 1, N'BSP6214184 ', N' ', 4, 4, 0, 0, CAST(0x0000000000000000 AS DateTime), N'272941')
INSERT [bsp_users] ([uid], [username], [email], [mobile], [password], [userrid], [admingid], [nickname], [avatar], [paycredits], [rankcredits], [verifyemail], [verifymobile], [liftbantime], [salt]) VALUES (5, N'uuuuuu ', N' ', N' ', N'3d1dac0294f40310e54428e483043caf', 7, 1, N'BSP3121686 ', N' ', 4, 4, 0, 0, CAST(0x0000000000000000 AS DateTime), N'178695')
INSERT [bsp_users] ([uid], [username], [email], [mobile], [password], [userrid], [admingid], [nickname], [avatar], [paycredits], [rankcredits], [verifyemail], [verifymobile], [liftbantime], [salt]) VALUES (6, N'pppppp ', N' ', N' ', N'7e197cd67a9c00991ae1772e41151207', 7, 1, N'BSP5322347 ', N' ', 4, 4, 0, 0, CAST(0x0000000000000000 AS DateTime), N'873196')
INSERT [bsp_users] ([uid], [username], [email], [mobile], [password], [userrid], [admingid], [nickname], [avatar], [paycredits], [rankcredits], [verifyemail], [verifymobile], [liftbantime], [salt]) VALUES (7, N'qwerty ', N' ', N' ', N'122ca376accc9310ec4bc2cfdb24df9a', 7, 1, N'BSP5115591 ', N' ', 4, 4, 0, 0, CAST(0x0000000000000000 AS DateTime), N'398117')
INSERT [bsp_users] ([uid], [username], [email], [mobile], [password], [userrid], [admingid], [nickname], [avatar], [paycredits], [rankcredits], [verifyemail], [verifymobile], [liftbantime], [salt]) VALUES (8, N'fadsjkl ', N' ', N' ', N'78609756e1b56c7de232410bd5588e46', 7, 1, N'BSP6128712 ', N' ', 4, 4, 0, 0, CAST(0x0000000000000000 AS DateTime), N'897986')
INSERT [bsp_users] ([uid], [username], [email], [mobile], [password], [userrid], [admingid], [nickname], [avatar], [paycredits], [rankcredits], [verifyemail], [verifymobile], [liftbantime], [salt]) VALUES (9, N'8888884444 ', N' ', N' ', N'5f64d53e153ec0663df276377ec7d8b1', 7, 1, N'BSP7855153 ', N' ', 4, 4, 0, 0, CAST(0x0000000000000000 AS DateTime), N'727748')
SET IDENTITY_INSERT [bsp_users] OFF
/****** Object: Table [dbo].[bsp_userranks] Script Date: 12/09/2014 14:07:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [bsp_userranks](
[userrid] [smallint] IDENTITY(1,1) NOT NULL,
[system] [int] NOT NULL,
[title] [nchar](50) NOT NULL,
[avatar] [char](50) NOT NULL,
[creditslower] [int] NOT NULL,
[creditsupper] [int] NOT NULL,
[limitdays] [int] NOT NULL,
CONSTRAINT [PK_bsp_userranks] PRIMARY KEY CLUSTERED
(
[userrid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'用户等级id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_userranks', @level2type=N'COLUMN',@level2name=N'userrid'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否为系统等级' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_userranks', @level2type=N'COLUMN',@level2name=N'system'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'标题' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_userranks', @level2type=N'COLUMN',@level2name=N'title'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'头像' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_userranks', @level2type=N'COLUMN',@level2name=N'avatar'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'积分下限' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_userranks', @level2type=N'COLUMN',@level2name=N'creditslower'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'积分上限' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_userranks', @level2type=N'COLUMN',@level2name=N'creditsupper'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'限制天数' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_userranks', @level2type=N'COLUMN',@level2name=N'limitdays'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'用户等级表' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_userranks'
GO
SET IDENTITY_INSERT [bsp_userranks] ON
INSERT [bsp_userranks] ([userrid], [system], [title], [avatar], [creditslower], [creditsupper], [limitdays]) VALUES (1, 1, N'禁止访问 ', N' ', 0, 0, 30)
INSERT [bsp_userranks] ([userrid], [system], [title], [avatar], [creditslower], [creditsupper], [limitdays]) VALUES (2, 1, N'禁止购买 ', N' ', 0, 0, 30)
INSERT [bsp_userranks] ([userrid], [system], [title], [avatar], [creditslower], [creditsupper], [limitdays]) VALUES (3, 1, N'禁止参加活动 ', N' ', 0, 0, 30)
INSERT [bsp_userranks] ([userrid], [system], [title], [avatar], [creditslower], [creditsupper], [limitdays]) VALUES (4, 1, N'禁止评论 ', N' ', 0, 0, 30)
INSERT [bsp_userranks] ([userrid], [system], [title], [avatar], [creditslower], [creditsupper], [limitdays]) VALUES (5, 1, N'禁止咨询 ', N' ', 0, 0, 30)
INSERT [bsp_userranks] ([userrid], [system], [title], [avatar], [creditslower], [creditsupper], [limitdays]) VALUES (6, 1, N'游客 ', N' ', 0, 0, 0)
INSERT [bsp_userranks] ([userrid], [system], [title], [avatar], [creditslower], [creditsupper], [limitdays]) VALUES (7, 0, N'铜牌会员 ', N'ura_1407011736593555489.png ', 0, 180, 0)
INSERT [bsp_userranks] ([userrid], [system], [title], [avatar], [creditslower], [creditsupper], [limitdays]) VALUES (8, 0, N'银牌会员 ', N'ura_1406081450117210245.jpg ', 180, -1, 0)
SET IDENTITY_INSERT [bsp_userranks] OFF
/****** Object: Table [dbo].[bsp_userdetails] Script Date: 12/09/2014 14:07:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [bsp_userdetails](
[uid] [int] NOT NULL,
[lastvisittime] [datetime] NOT NULL,
[lastvisitip] [char](15) NOT NULL,
[lastvisitrgid] [smallint] NOT NULL,
[registertime] [datetime] NOT NULL,
[registerip] [char](15) NOT NULL,
[registerrgid] [smallint] NOT NULL,
[gender] [tinyint] NOT NULL,
[realname] [nvarchar](10) NOT NULL,
[bday] [datetime] NOT NULL,
[idcard] [varchar](18) NOT NULL,
[regionid] [smallint] NOT NULL,
[address] [nvarchar](150) NOT NULL,
[bio] [nvarchar](300) NOT NULL,
CONSTRAINT [PK_bsp_userdetails] PRIMARY KEY CLUSTERED
(
[uid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CREATE NONCLUSTERED INDEX [registerip] ON [bsp_userdetails]
(
[registerip] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'用户id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_userdetails', @level2type=N'COLUMN',@level2name=N'uid'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'最后访问时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_userdetails', @level2type=N'COLUMN',@level2name=N'lastvisittime'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'最后访问ip' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_userdetails', @level2type=N'COLUMN',@level2name=N'lastvisitip'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'最后访问区域id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_userdetails', @level2type=N'COLUMN',@level2name=N'lastvisitrgid'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'注册时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_userdetails', @level2type=N'COLUMN',@level2name=N'registertime'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'注册ip' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_userdetails', @level2type=N'COLUMN',@level2name=N'registerip'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'注册区域id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_userdetails', @level2type=N'COLUMN',@level2name=N'registerrgid'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'性别' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_userdetails', @level2type=N'COLUMN',@level2name=N'gender'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'真实姓名' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_userdetails', @level2type=N'COLUMN',@level2name=N'realname'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'出生日期' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_userdetails', @level2type=N'COLUMN',@level2name=N'bday'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'身份证号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_userdetails', @level2type=N'COLUMN',@level2name=N'idcard'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'所在区域id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_userdetails', @level2type=N'COLUMN',@level2name=N'regionid'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'详细地址' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_userdetails', @level2type=N'COLUMN',@level2name=N'address'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'简介' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_userdetails', @level2type=N'COLUMN',@level2name=N'bio'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'用户详细信息表' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_userdetails'
GO
INSERT [bsp_userdetails] ([uid], [lastvisittime], [lastvisitip], [lastvisitrgid], [registertime], [registerip], [registerrgid], [gender], [realname], [bday], [idcard], [regionid], [address], [bio]) VALUES (1, CAST(0x0000A3FC00C79C02 AS DateTime), N'127.0.0.1 ', -1, CAST(0x0000A359011E28FE AS DateTime), N' ', -1, 1, N'开发者', CAST(0x0000805700000000 AS DateTime), N'', 411, N'广安大街', N'开源网上商城')
INSERT [bsp_userdetails] ([uid], [lastvisittime], [lastvisitip], [lastvisitrgid], [registertime], [registerip], [registerrgid], [gender], [realname], [bday], [idcard], [regionid], [address], [bio]) VALUES (2, CAST(0x0000A36E006F26D0 AS DateTime), N'127.0.0.1 ', -1, CAST(0x0000A36E006F1EEB AS DateTime), N'127.0.0.1 ', -1, 0, N'', CAST(0x0000000000000000 AS DateTime), N'', 0, N'', N'')
INSERT [bsp_userdetails] ([uid], [lastvisittime], [lastvisitip], [lastvisitrgid], [registertime], [registerip], [registerrgid], [gender], [realname], [bday], [idcard], [regionid], [address], [bio]) VALUES (3, CAST(0x0000A36E0071964A AS DateTime), N'127.0.0.1 ', -1, CAST(0x0000A36E0071964A AS DateTime), N'127.0.0.1 ', -1, 0, N'', CAST(0x0000000000000000 AS DateTime), N'', 0, N'', N'')
INSERT [bsp_userdetails] ([uid], [lastvisittime], [lastvisitip], [lastvisitrgid], [registertime], [registerip], [registerrgid], [gender], [realname], [bday], [idcard], [regionid], [address], [bio]) VALUES (4, CAST(0x0000A36E011378AB AS DateTime), N'127.0.0.1 ', -1, CAST(0x0000A36E011378AB AS DateTime), N'127.0.0.1 ', -1, 0, N'', CAST(0x0000A36E011378AB AS DateTime), N'', 0, N'', N'')
INSERT [bsp_userdetails] ([uid], [lastvisittime], [lastvisitip], [lastvisitrgid], [registertime], [registerip], [registerrgid], [gender], [realname], [bday], [idcard], [regionid], [address], [bio]) VALUES (5, CAST(0x0000A37300A052BF AS DateTime), N'127.0.0.1 ', -1, CAST(0x0000A37300A0501D AS DateTime), N'127.0.0.1 ', -1, 0, N'', CAST(0x0000A37300A0501D AS DateTime), N'', 0, N'', N'')
INSERT [bsp_userdetails] ([uid], [lastvisittime], [lastvisitip], [lastvisitrgid], [registertime], [registerip], [registerrgid], [gender], [realname], [bday], [idcard], [regionid], [address], [bio]) VALUES (6, CAST(0x0000A37300B0FC0C AS DateTime), N'127.0.0.1 ', -1, CAST(0x0000A37300B0B952 AS DateTime), N'127.0.0.1 ', -1, 0, N'', CAST(0x0000A37300B0B952 AS DateTime), N'', 0, N'', N'')
INSERT [bsp_userdetails] ([uid], [lastvisittime], [lastvisitip], [lastvisitrgid], [registertime], [registerip], [registerrgid], [gender], [realname], [bday], [idcard], [regionid], [address], [bio]) VALUES (7, CAST(0x0000A37400E4D624 AS DateTime), N'127.0.0.1 ', -1, CAST(0x0000A37400E4D329 AS DateTime), N'127.0.0.1 ', -1, 0, N'', CAST(0x0000A37400E4D329 AS DateTime), N'', 0, N'', N'')
INSERT [bsp_userdetails] ([uid], [lastvisittime], [lastvisitip], [lastvisitrgid], [registertime], [registerip], [registerrgid], [gender], [realname], [bday], [idcard], [regionid], [address], [bio]) VALUES (8, CAST(0x0000A375009DB9AD AS DateTime), N'127.0.0.1 ', -1, CAST(0x0000A375009DB9AD AS DateTime), N'127.0.0.1 ', -1, 0, N'', CAST(0x0000A375009DB9AD AS DateTime), N'', 0, N'', N'')
INSERT [bsp_userdetails] ([uid], [lastvisittime], [lastvisitip], [lastvisitrgid], [registertime], [registerip], [registerrgid], [gender], [realname], [bday], [idcard], [regionid], [address], [bio]) VALUES (9, CAST(0x0000A38201015BE4 AS DateTime), N'127.0.0.1 ', -1, CAST(0x0000A382010158F7 AS DateTime), N'127.0.0.1 ', -1, 0, N'', CAST(0x0000A382010158F7 AS DateTime), N'', 0, N'', N'')
/****** Object: StoredProcedure [dbo].[bsp_getpartproductlistbypidlist] Script Date: 12/09/2014 14:07:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [bsp_getpartproductlistbypidlist]
@pidlist nvarchar(1000)
AS
BEGIN
SET NOCOUNT ON;
EXEC('SELECT [pid],[psn],[cateid],[brandid],[skugid],[name],[shopprice],[marketprice],[costprice],[state],[isbest],[ishot],[isnew],[displayorder],[weight],[showimg],[salecount],[visitcount],[reviewcount],[star1],[star2],[star3],[star4],[star5],[addtime]
FROM [bsp_products]
WHERE [pid] IN ('+@pidlist+') AND [state]=0 ORDER BY CHARINDEX('+''',''+CONVERT(varchar(10),[pid])+'','','','+@pidlist+','')')
END
GO
/****** Object: Table [dbo].[bsp_searchhistories] Script Date: 12/09/2014 14:07:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [bsp_searchhistories](
[recordid] [int] IDENTITY(1,1) NOT NULL,
[uid] [int] NOT NULL,
[word] [nvarchar](60) NOT NULL,
[times] [int] NOT NULL,
[updatetime] [datetime] NOT NULL,
CONSTRAINT [PK_bsp_searchhistories] PRIMARY KEY CLUSTERED
(
[recordid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [uid_word] ON [bsp_searchhistories]
(
[uid] ASC,
[word] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [updatetime] ON [bsp_searchhistories]
(
[updatetime] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'记录id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_searchhistories', @level2type=N'COLUMN',@level2name=N'recordid'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'用户id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_searchhistories', @level2type=N'COLUMN',@level2name=N'uid'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'搜索词' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_searchhistories', @level2type=N'COLUMN',@level2name=N'word'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'搜索次数' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_searchhistories', @level2type=N'COLUMN',@level2name=N'times'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'最后搜索时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_searchhistories', @level2type=N'COLUMN',@level2name=N'updatetime'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'搜索历史表' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_searchhistories'
GO
SET IDENTITY_INSERT [bsp_searchhistories] ON
INSERT [bsp_searchhistories] ([recordid], [uid], [word], [times], [updatetime]) VALUES (9, 1, N'lumia', 2, CAST(0x0000A3FB008CB5FE AS DateTime))
INSERT [bsp_searchhistories] ([recordid], [uid], [word], [times], [updatetime]) VALUES (10, 1, N'<a>', 5, CAST(0x0000A3FC00AA2EB4 AS DateTime))
INSERT [bsp_searchhistories] ([recordid], [uid], [word], [times], [updatetime]) VALUES (11, 1, N'<a>', 2, CAST(0x0000A3FC00C18F73 AS DateTime))
SET IDENTITY_INSERT [bsp_searchhistories] OFF
/****** Object: StoredProcedure [dbo].[bsp_voteproductreview] Script Date: 12/09/2014 14:07:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [bsp_voteproductreview]
@reviewid int,
@uid int,
@votetime datetime
AS
BEGIN
SET NOCOUNT ON;
UPDATE [bsp_productreviews] SET [quality]=[quality]+1 WHERE [reviewid]=@reviewid;
IF @@ERROR=0
BEGIN
INSERT INTO [bsp_productreviewquality]([reviewid],[uid],[votetime]) VALUES(@reviewid,@uid,@votetime)
END
END
GO
/****** Object: StoredProcedure [dbo].[bsp_reviewproduct] Script Date: 12/09/2014 14:07:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [bsp_reviewproduct]
@pid int
,@uid int
,@oprid int
,@oid int
,@parentid int
,@state tinyint
,@star tinyint
,@quality smallint
,@message nvarchar(200)
,@reviewtime datetime
,@paycredits int
,@pname nvarchar(200)
,@pshowimg nvarchar(100)
,@buytime datetime
,@ip varchar(15)
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO [bsp_productreviews]
([pid]
,[uid]
,[oprid]
,[oid]
,[parentid]
,[state]
,[star]
,[quality]
,[message]
,[reviewtime]
,[paycredits]
,[pname]
,[pshowimg]
,[buytime]
,[ip])
VALUES
(@pid
,@uid
,@oprid
,@oid
,@parentid
,@state
,@star
,@quality
,@message
,@reviewtime
,@paycredits
,@pname
,@pshowimg
,@buytime
,@ip);
if(@@rowcount>0)
BEGIN
UPDATE [bsp_orderproducts] SET [isreview]=1 WHERE [recordid]=@oprid;
END
END
GO
/****** Object: StoredProcedure [dbo].[bsp_returnuserorderusecoupons] Script Date: 12/09/2014 14:07:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [bsp_returnuserorderusecoupons]
@oid int
AS
BEGIN
SET NOCOUNT ON;
UPDATE [bsp_coupons] SET [oid]=0,[usetime]='1900-1-1 00:00:00',[useip]='' WHERE [oid]=@oid;
END
GO
/****** Object: StoredProcedure [dbo].[bsp_getpartproductbyid] Script Date: 12/09/2014 14:07:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [bsp_getpartproductbyid]
@pid int
AS
BEGIN
SET NOCOUNT ON;
SELECT [pid]
,[psn]
,[cateid]
,[brandid]
,[skugid]
,[name]
,[shopprice]
,[marketprice]
,[costprice]
,[state]
,[isbest]
,[ishot]
,[isnew]
,[displayorder]
,[weight]
,[showimg]
,[salecount]
,[visitcount]
,[reviewcount]
,[star1]
,[star2]
,[star3]
,[star4]
,[star5]
,[addtime]
FROM [bsp_products] WHERE [pid]=@pid AND [state]=0;
END
GO
/****** Object: StoredProcedure [dbo].[bsp_getorderstatebyoid] Script Date: 12/09/2014 14:07:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [bsp_getorderstatebyoid]
@oid int
AS
BEGIN
SET NOCOUNT ON;
SELECT [orderstate] FROM [bsp_orders] WHERE [oid]=@oid;
END
GO
/****** Object: StoredProcedure [dbo].[bsp_usecoupon] Script Date: 12/09/2014 14:07:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [bsp_usecoupon]
@couponid int,
@oid int,
@time datetime,
@ip char(15)
AS
BEGIN
SET NOCOUNT ON;
UPDATE [bsp_coupons] SET [oid]=@oid,[usetime]=@time,[useip]=@ip WHERE [couponid]=@couponid;
END
GO
/****** Object: StoredProcedure [dbo].[bsp_updateuserrankbyuid] Script Date: 12/09/2014 14:07:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [bsp_updateuserrankbyuid]
@uid int,
@userrid smallint
AS
BEGIN
SET NOCOUNT ON;
UPDATE [bsp_users] SET [userrid]=@userrid WHERE [uid]=@uid
END
GO
/****** Object: StoredProcedure [dbo].[bsp_updateuserpasswordbyuid] Script Date: 12/09/2014 14:07:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [bsp_updateuserpasswordbyuid]
@uid int,
@password char(32)
AS
BEGIN
SET NOCOUNT ON;
UPDATE [bsp_users] SET [password]=@password WHERE [uid]=@uid;
END
GO
/****** Object: StoredProcedure [dbo].[bsp_updateusermobilebyuid] Script Date: 12/09/2014 14:07:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [bsp_updateusermobilebyuid]
@uid int,
@mobile char(15)
AS
BEGIN
SET NOCOUNT ON;
UPDATE [bsp_users] SET [mobile]=@mobile,[verifymobile]=1 WHERE [uid]=@uid;
END
GO
/****** Object: StoredProcedure [dbo].[bsp_updateuserliftbantimebyuid] Script Date: 12/09/2014 14:07:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [bsp_updateuserliftbantimebyuid]
@uid int,
@liftbantime datetime
AS
BEGIN
SET NOCOUNT ON;
UPDATE [bsp_users] SET [liftbantime]=@liftbantime WHERE [uid]=@uid;
END
GO
/****** Object: StoredProcedure [dbo].[bsp_updateuserlastvisit] Script Date: 12/09/2014 14:07:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [bsp_updateuserlastvisit]
@uid int,
@visittime datetime,
@ip char(15),
@regionid smallint
AS
BEGIN
SET NOCOUNT ON;
UPDATE [bsp_userdetails] SET [lastvisittime]=@visittime,[lastvisitip]=@ip,[lastvisitrgid]=@regionid WHERE [uid]=@uid
END
GO
/****** Object: StoredProcedure [dbo].[bsp_updateuseremailbyuid] Script Date: 12/09/2014 14:07:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [bsp_updateuseremailbyuid]
@uid int,
@email char(50)
AS
BEGIN
SET NOCOUNT ON;
UPDATE [bsp_users] SET [email]=@email,[verifyemail]=1 WHERE [uid]=@uid;
END
GO
/****** Object: StoredProcedure [dbo].[bsp_updateuserdetail] Script Date: 12/09/2014 14:07:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [bsp_updateuserdetail]
@uid int,
@lastvisittime datetime,
@lastvisitip char(15),
@lastvisitrgid smallint,
@registertime datetime,
@registerip char(15),
@registerrgid smallint,
@gender tinyint,
@realname nvarchar(10),
@bday datetime,
@idcard varchar(18),
@regionid smallint,
@address nvarchar(150),
@bio nvarchar(300)
AS
BEGIN
SET NOCOUNT ON;
UPDATE [bsp_userdetails]
SET [gender] = @gender
,[lastvisittime] = @lastvisittime
,[lastvisitip] = @lastvisitip
,[lastvisitrgid]=@lastvisitrgid
,[registertime] = @registertime
,[registerip] = @registerip
,[registerrgid]=@registerrgid
,[realname] = @realname
,[bday] = @bday
,[idcard] = @idcard
,[regionid] = @regionid
,[address] = @address
,[bio] = @bio
WHERE [uid]=@uid;
END
GO
/****** Object: StoredProcedure [dbo].[bsp_updateuser] Script Date: 12/09/2014 14:07:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [bsp_updateuser]
@uid int,
@username nchar(20),
@email char(50),
@mobile char(15),
@password char(32),
@userrid smallint,
@admingid smallint,
@nickname nchar(20),
@avatar char(40),
@paycredits int,
@rankcredits int,
@verifyemail tinyint,
@verifymobile tinyint,
@liftbantime datetime,
@salt nchar(6),
@lastvisittime datetime,
@lastvisitip char(15),
@lastvisitrgid smallint,
@registertime datetime,
@registerip char(15),
@registerrgid smallint,
@gender tinyint,
@realname nvarchar(10),
@bday datetime,
@idcard varchar(18),
@regionid smallint,
@address nvarchar(150),
@bio nvarchar(300)
AS
BEGIN
SET NOCOUNT ON;
UPDATE [bsp_users]
SET [username] = @username
,[email] = @email
,[mobile] = @mobile
,[password] = @password
,[userrid] = @userrid
,[admingid] = @admingid
,[nickname] = @nickname
,[avatar] = @avatar
,[paycredits] = @paycredits
,[rankcredits] = @rankcredits
,[verifyemail] = @verifyemail
,[verifymobile] = @verifymobile
,[liftbantime]=@liftbantime
,[salt] = @salt
WHERE [uid]=@uid;
UPDATE [bsp_userdetails]
SET [gender] = @gender
,[lastvisittime] = @lastvisittime
,[lastvisitip] = @lastvisitip
,[lastvisitrgid]=@lastvisitrgid
,[registertime] = @registertime
,[registerip] = @registerip
,[registerrgid]=@registerrgid
,[realname] = @realname
,[bday] = @bday
,[idcard] = @idcard
,[regionid] = @regionid
,[address] = @address
,[bio] = @bio
WHERE [uid]=@uid;
END
GO
/****** Object: StoredProcedure [dbo].[bsp_updateucenteruser] Script Date: 12/09/2014 14:07:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [bsp_updateucenteruser]
@uid int,
@username nchar(20),
@nickname nchar(20),
@avatar char(40),
@gender tinyint,
@realname nvarchar(10),
@bday datetime,
@idcard varchar(18),
@regionid smallint,
@address nvarchar(150),
@bio nvarchar(300)
AS
BEGIN
UPDATE [bsp_users] SET [username]=@username,[nickname]=@nickname,[avatar]=@avatar WHERE [uid]=@uid;
UPDATE [bsp_userdetails] SET [gender]=@gender,[realname]=@realname,[bday]=@bday,[idcard]=@idcard,[regionid]=@regionid,[address]=@address,[bio]=@bio WHERE [uid]=@uid;
UPDATE [bsp_productconsults] SET [consultnickname]=@nickname WHERE [consultid]=@uid;
UPDATE [bsp_productconsults] SET [replynickname]=@nickname WHERE [replyuid]=@uid;
END
GO
/****** Object: StoredProcedure [dbo].[bsp_updateshipaddressisdefault] Script Date: 12/09/2014 14:07:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [bsp_updateshipaddressisdefault]
@said int,
@uid int,
@isdefault tinyint
AS
BEGIN
IF @isdefault=1
BEGIN
UPDATE [bsp_shipaddresses] SET [isdefault]=0 WHERE [uid]=@uid AND [isdefault]=1;
END
UPDATE [bsp_shipaddresses] SET [isdefault]=@isdefault WHERE [said]=@said AND [uid]=@uid;
END
GO
/****** Object: StoredProcedure [dbo].[bsp_updateshipaddress] Script Date: 12/09/2014 14:07:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [bsp_updateshipaddress]
@said int
,@uid int
,@regionid smallint
,@isdefault tinyint
,@alias nvarchar(50)
,@consignee nvarchar(20)
,@mobile varchar(15)
,@phone varchar(12)
,@email varchar(50)
,@zipcode char(6)
,@address nvarchar(150)
AS
BEGIN
SET NOCOUNT ON;
IF @isdefault = 1
BEGIN
UPDATE [bsp_shipaddresses] SET [isdefault]=0 WHERE [uid]=@uid AND [isdefault]=1
END
UPDATE [bsp_shipaddresses] SET [uid]=@uid,[regionid]=@regionid,[isdefault]=@isdefault,[alias]=@alias,[consignee]=@consignee,[mobile]=@mobile,[phone]=@phone,[email]=@email,[zipcode]=@zipcode,[address]=@address WHERE [said]=@said AND [uid]=@uid
END
GO
2.1的部分语句
/****** Object: Table [dbo].[bsp_suitpromotions] Script Date: 01/13/2015 10:12:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [bsp_suitpromotions](
[pmid] [int] IDENTITY(1,1) NOT NULL,
[starttime1] [datetime] NOT NULL,
[endtime1] [datetime] NOT NULL,
[starttime2] [datetime] NOT NULL,
[endtime2] [datetime] NOT NULL,
[starttime3] [datetime] NOT NULL,
[endtime3] [datetime] NOT NULL,
[userranklower] [smallint] NOT NULL,
[state] [tinyint] NOT NULL,
[name] [nvarchar](50) NOT NULL,
[quotaupper] [int] NOT NULL,
[onlyonce] [tinyint] NOT NULL,
CONSTRAINT [PK_bsp_suitpromotions] PRIMARY KEY CLUSTERED
(
[pmid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [state_starttime_endtime] ON [bsp_suitpromotions]
(
[state] ASC,
[starttime1] ASC,
[endtime1] ASC,
[starttime2] ASC,
[endtime2] ASC,
[starttime3] ASC,
[endtime3] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'促销活动id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_suitpromotions', @level2type=N'COLUMN',@level2name=N'pmid'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'开始时间1' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_suitpromotions', @level2type=N'COLUMN',@level2name=N'starttime1'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'结束时间1' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_suitpromotions', @level2type=N'COLUMN',@level2name=N'endtime1'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'开始时间2' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_suitpromotions', @level2type=N'COLUMN',@level2name=N'starttime2'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'结束时间2' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_suitpromotions', @level2type=N'COLUMN',@level2name=N'endtime2'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'开始时间3' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_suitpromotions', @level2type=N'COLUMN',@level2name=N'starttime3'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'结束时间3' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_suitpromotions', @level2type=N'COLUMN',@level2name=N'endtime3'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'最低用户等级' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_suitpromotions', @level2type=N'COLUMN',@level2name=N'userranklower'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'状态' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_suitpromotions', @level2type=N'COLUMN',@level2name=N'state'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_suitpromotions', @level2type=N'COLUMN',@level2name=N'name'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'配额上限' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_suitpromotions', @level2type=N'COLUMN',@level2name=N'quotaupper'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'限购一次 ' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_suitpromotions', @level2type=N'COLUMN',@level2name=N'onlyonce'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'套装促销活动表' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_suitpromotions'
GO
/****** Object: Table [dbo].[bsp_suitproducts] Script Date: 01/13/2015 10:12:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [bsp_suitproducts](
[recordid] [int] IDENTITY(1,1) NOT NULL,
[pmid] [int] NOT NULL,
[pid] [int] NOT NULL,
[discount] [int] NOT NULL,
[number] [int] NOT NULL,
CONSTRAINT [PK_bsp_suitproducts] PRIMARY KEY CLUSTERED
(
[recordid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [pid_pmid] ON [bsp_suitproducts]
(
[pid] ASC,
[pmid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [pmid_pid] ON [bsp_suitproducts]
(
[pmid] ASC,
[pid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'记录id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_suitproducts', @level2type=N'COLUMN',@level2name=N'recordid'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'促销活动id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_suitproducts', @level2type=N'COLUMN',@level2name=N'pmid'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'商品id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_suitproducts', @level2type=N'COLUMN',@level2name=N'pid'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'折扣值' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_suitproducts', @level2type=N'COLUMN',@level2name=N'discount'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数量' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_suitproducts', @level2type=N'COLUMN',@level2name=N'number'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'套装商品表' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_suitproducts'
GO
/****** Object: Table [dbo].[bsp_singlepromotions] Script Date: 01/13/2015 10:12:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [bsp_singlepromotions](
[pmid] [int] IDENTITY(1,1) NOT NULL,
[pid] [int] NOT NULL,
[starttime1] [datetime] NOT NULL,
[endtime1] [datetime] NOT NULL,
[starttime2] [datetime] NOT NULL,
[endtime2] [datetime] NOT NULL,
[starttime3] [datetime] NOT NULL,
[endtime3] [datetime] NOT NULL,
[userranklower] [smallint] NOT NULL,
[state] [tinyint] NOT NULL,
[name] [nvarchar](50) NOT NULL,
[slogan] [nvarchar](60) NOT NULL,
[discounttype] [tinyint] NOT NULL,
[discountvalue] [int] NOT NULL,
[coupontypeid] [int] NOT NULL,
[paycredits] [int] NOT NULL,
[isstock] [tinyint] NOT NULL,
[stock] [int] NOT NULL,
[quotalower] [int] NOT NULL,
[quotaupper] [int] NOT NULL,
[allowbuycount] [int] NOT NULL,
CONSTRAINT [PK_bsp_singlepromotions] PRIMARY KEY CLUSTERED
(
[pmid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [pid_starttime_endtime_state] ON [bsp_singlepromotions]
(
[pid] ASC,
[starttime1] ASC,
[endtime1] ASC,
[starttime2] ASC,
[endtime2] ASC,
[starttime3] ASC,
[endtime3] ASC,
[state] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'促销活动id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_singlepromotions', @level2type=N'COLUMN',@level2name=N'pmid'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'商品id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_singlepromotions', @level2type=N'COLUMN',@level2name=N'pid'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'开始时间1' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_singlepromotions', @level2type=N'COLUMN',@level2name=N'starttime1'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'结束时间1' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_singlepromotions', @level2type=N'COLUMN',@level2name=N'endtime1'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'开始时间2' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_singlepromotions', @level2type=N'COLUMN',@level2name=N'starttime2'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'结束时间2' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_singlepromotions', @level2type=N'COLUMN',@level2name=N'endtime2'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'开始时间3' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_singlepromotions', @level2type=N'COLUMN',@level2name=N'starttime3'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'结束时间3' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_singlepromotions', @level2type=N'COLUMN',@level2name=N'endtime3'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'最低用户等级' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_singlepromotions', @level2type=N'COLUMN',@level2name=N'userranklower'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'状态' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_singlepromotions', @level2type=N'COLUMN',@level2name=N'state'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_singlepromotions', @level2type=N'COLUMN',@level2name=N'name'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'广告语' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_singlepromotions', @level2type=N'COLUMN',@level2name=N'slogan'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'折扣类型(0代表折扣,1代表直降,2代表折后价)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_singlepromotions', @level2type=N'COLUMN',@level2name=N'discounttype'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'折扣值' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_singlepromotions', @level2type=N'COLUMN',@level2name=N'discountvalue'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'赠送优惠劵类型id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_singlepromotions', @level2type=N'COLUMN',@level2name=N'coupontypeid'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'支付积分' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_singlepromotions', @level2type=N'COLUMN',@level2name=N'paycredits'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否限制库存' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_singlepromotions', @level2type=N'COLUMN',@level2name=N'isstock'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'库存数量' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_singlepromotions', @level2type=N'COLUMN',@level2name=N'stock'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'配额下限' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_singlepromotions', @level2type=N'COLUMN',@level2name=N'quotalower'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'配额上限' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_singlepromotions', @level2type=N'COLUMN',@level2name=N'quotaupper'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'最大购买数量' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_singlepromotions', @level2type=N'COLUMN',@level2name=N'allowbuycount'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'单品促销活动表' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_singlepromotions'
GO
/****** Object: Table [dbo].[bsp_signproducts] Script Date: 01/13/2015 10:12:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [bsp_signproducts](
[recordid] [int] IDENTITY(1,1) NOT NULL,
[sign] [char](15) NOT NULL,
[pid] [int] NOT NULL,
CONSTRAINT [PK_bsp_signproducts] PRIMARY KEY CLUSTERED
(
[recordid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CREATE NONCLUSTERED INDEX [sign_pid] ON [bsp_signproducts]
(
[sign] ASC,
[pid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'记录id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_signproducts', @level2type=N'COLUMN',@level2name=N'recordid'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'标记' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_signproducts', @level2type=N'COLUMN',@level2name=N'sign'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'商品id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_signproducts', @level2type=N'COLUMN',@level2name=N'pid'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'签名商品表' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_signproducts'
GO
/****** Object: Table [dbo].[bsp_shipaddresses] Script Date: 01/13/2015 10:12:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [bsp_shipaddresses](
[said] [int] IDENTITY(1,1) NOT NULL,
[uid] [int] NOT NULL,
[regionid] [smallint] NOT NULL,
[isdefault] [tinyint] NOT NULL,
[alias] [nvarchar](50) NOT NULL,
[consignee] [nvarchar](20) NOT NULL,
[mobile] [varchar](15) NOT NULL,
[phone] [varchar](12) NOT NULL,
[email] [varchar](50) NOT NULL,
[zipcode] [char](6) NOT NULL,
[address] [nvarchar](150) NOT NULL,
CONSTRAINT [PK_bsp_shipaddresses] PRIMARY KEY CLUSTERED
(
[said] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CREATE NONCLUSTERED INDEX [mobile] ON [bsp_shipaddresses]
(
[mobile] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [uid_isdefault] ON [bsp_shipaddresses]
(
[uid] ASC,
[isdefault] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'配送地址id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_shipaddresses', @level2type=N'COLUMN',@level2name=N'said'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'用户id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_shipaddresses', @level2type=N'COLUMN',@level2name=N'uid'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'区域id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_shipaddresses', @level2type=N'COLUMN',@level2name=N'regionid'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否为默认地址' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_shipaddresses', @level2type=N'COLUMN',@level2name=N'isdefault'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'地址别名' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_shipaddresses', @level2type=N'COLUMN',@level2name=N'alias'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'收货人' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_shipaddresses', @level2type=N'COLUMN',@level2name=N'consignee'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'手机' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_shipaddresses', @level2type=N'COLUMN',@level2name=N'mobile'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'固定电话' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_shipaddresses', @level2type=N'COLUMN',@level2name=N'phone'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'邮箱' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_shipaddresses', @level2type=N'COLUMN',@level2name=N'email'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'邮政编码' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_shipaddresses', @level2type=N'COLUMN',@level2name=N'zipcode'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'详细地址' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_shipaddresses', @level2type=N'COLUMN',@level2name=N'address'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'配送地址表' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_shipaddresses'
GO
SET IDENTITY_INSERT [bsp_shipaddresses] ON
INSERT [bsp_shipaddresses] ([said], [uid], [regionid], [isdefault], [alias], [consignee], [mobile], [phone], [email], [zipcode], [address]) VALUES (30, 1, 411, 0, N'家', N'张三', N'15555555555', N'', N'', N' ', N'中山路3号')
INSERT [bsp_shipaddresses] ([said], [uid], [regionid], [isdefault], [alias], [consignee], [mobile], [phone], [email], [zipcode], [address]) VALUES (31, 1, 2321, 0, N'家', N'李四', N'15511429689', N'', N'', N' ', N'解放路')
INSERT [bsp_shipaddresses] ([said], [uid], [regionid], [isdefault], [alias], [consignee], [mobile], [phone], [email], [zipcode], [address]) VALUES (32, 1, 1467, 1, N'单位', N'王五', N'15555555555', N'', N'', N' ', N'解放路')
SET IDENTITY_INSERT [bsp_shipaddresses] OFF
/****** Object: Table [dbo].[bsp_users] Script Date: 01/13/2015 10:12:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [bsp_users](
[uid] [int] IDENTITY(1,1) NOT NULL,
[username] [nchar](20) NOT NULL,
[email] [char](50) NOT NULL,
[mobile] [char](15) NOT NULL,
[password] [char](32) NOT NULL,
[userrid] [smallint] NOT NULL,
[admingid] [smallint] NOT NULL,
[nickname] [nchar](20) NOT NULL,
[avatar] [char](40) NOT NULL,
[paycredits] [int] NOT NULL,
[rankcredits] [int] NOT NULL,
[verifyemail] [tinyint] NOT NULL,
[verifymobile] [tinyint] NOT NULL,
[liftbantime] [datetime] NOT NULL,
[salt] [nchar](6) NOT NULL,
CONSTRAINT [PK_bsp_users] PRIMARY KEY CLUSTERED
(
[uid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CREATE NONCLUSTERED INDEX [admingid] ON [bsp_users]
(
[admingid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [email] ON [bsp_users]
(
[email] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [mobile] ON [bsp_users]
(
[mobile] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [username] ON [bsp_users]
(
[username] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [userrid] ON [bsp_users]
(
[userrid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'用户id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_users', @level2type=N'COLUMN',@level2name=N'uid'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'用户名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_users', @level2type=N'COLUMN',@level2name=N'username'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'邮箱' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_users', @level2type=N'COLUMN',@level2name=N'email'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'手机' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_users', @level2type=N'COLUMN',@level2name=N'mobile'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'密码' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_users', @level2type=N'COLUMN',@level2name=N'password'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'用户等级id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_users', @level2type=N'COLUMN',@level2name=N'userrid'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'商城管理员组id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_users', @level2type=N'COLUMN',@level2name=N'admingid'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'昵称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_users', @level2type=N'COLUMN',@level2name=N'nickname'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'头像' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_users', @level2type=N'COLUMN',@level2name=N'avatar'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'支付积分' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_users', @level2type=N'COLUMN',@level2name=N'paycredits'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'等级积分' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_users', @level2type=N'COLUMN',@level2name=N'rankcredits'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否验证邮箱' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_users', @level2type=N'COLUMN',@level2name=N'verifyemail'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否验证手机' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_users', @level2type=N'COLUMN',@level2name=N'verifymobile'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'解禁时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_users', @level2type=N'COLUMN',@level2name=N'liftbantime'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'盐值' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_users', @level2type=N'COLUMN',@level2name=N'salt'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'用户表' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_users'
GO
SET IDENTITY_INSERT [bsp_users] ON
INSERT [bsp_users] ([uid], [username], [email], [mobile], [password], [userrid], [admingid], [nickname], [avatar], [paycredits], [rankcredits], [verifyemail], [verifymobile], [liftbantime], [salt]) VALUES (1, N'brnshop ', N'brnshop@brnshop.com ', N'15555555555 ', N'd720e94022edc164faacfac7d77a5328', 8, 2, N'brnshop ', N' ', 526, 1764, 1, 1, CAST(0x0000000000000000 AS DateTime), N'125641')
INSERT [bsp_users] ([uid], [username], [email], [mobile], [password], [userrid], [admingid], [nickname], [avatar], [paycredits], [rankcredits], [verifyemail], [verifymobile], [liftbantime], [salt]) VALUES (2, N'111111@163.com ', N' ', N' ', N'bb6bb2b3a5a990e1d9880d615847f7b3', 7, 1, N'BSP3648569 ', N' ', 4, 4, 0, 0, CAST(0x0000000000000000 AS DateTime), N'927549')
INSERT [bsp_users] ([uid], [username], [email], [mobile], [password], [userrid], [admingid], [nickname], [avatar], [paycredits], [rankcredits], [verifyemail], [verifymobile], [liftbantime], [salt]) VALUES (3, N'555555 ', N' ', N' ', N'4c22ca987df47b8528847eb0f2549f51', 7, 1, N'BSP2259685 ', N' ', 4, 4, 0, 0, CAST(0x0000000000000000 AS DateTime), N'754144')
INSERT [bsp_users] ([uid], [username], [email], [mobile], [password], [userrid], [admingid], [nickname], [avatar], [paycredits], [rankcredits], [verifyemail], [verifymobile], [liftbantime], [salt]) VALUES (4, N'888888 ', N' ', N' ', N'fe212028b961782258a3ecec81a83fad', 7, 1, N'BSP6214184 ', N' ', 4, 4, 0, 0, CAST(0x0000000000000000 AS DateTime), N'272941')
INSERT [bsp_users] ([uid], [username], [email], [mobile], [password], [userrid], [admingid], [nickname], [avatar], [paycredits], [rankcredits], [verifyemail], [verifymobile], [liftbantime], [salt]) VALUES (5, N'uuuuuu ', N' ', N' ', N'3d1dac0294f40310e54428e483043caf', 7, 1, N'BSP3121686 ', N' ', 4, 4, 0, 0, CAST(0x0000000000000000 AS DateTime), N'178695')
INSERT [bsp_users] ([uid], [username], [email], [mobile], [password], [userrid], [admingid], [nickname], [avatar], [paycredits], [rankcredits], [verifyemail], [verifymobile], [liftbantime], [salt]) VALUES (6, N'pppppp ', N' ', N' ', N'7e197cd67a9c00991ae1772e41151207', 7, 1, N'BSP5322347 ', N' ', 4, 4, 0, 0, CAST(0x0000000000000000 AS DateTime), N'873196')
INSERT [bsp_users] ([uid], [username], [email], [mobile], [password], [userrid], [admingid], [nickname], [avatar], [paycredits], [rankcredits], [verifyemail], [verifymobile], [liftbantime], [salt]) VALUES (7, N'qwerty ', N' ', N' ', N'122ca376accc9310ec4bc2cfdb24df9a', 7, 1, N'BSP5115591 ', N' ', 4, 4, 0, 0, CAST(0x0000000000000000 AS DateTime), N'398117')
INSERT [bsp_users] ([uid], [username], [email], [mobile], [password], [userrid], [admingid], [nickname], [avatar], [paycredits], [rankcredits], [verifyemail], [verifymobile], [liftbantime], [salt]) VALUES (8, N'fadsjkl ', N' ', N' ', N'78609756e1b56c7de232410bd5588e46', 7, 1, N'BSP6128712 ', N' ', 4, 4, 0, 0, CAST(0x0000000000000000 AS DateTime), N'897986')
INSERT [bsp_users] ([uid], [username], [email], [mobile], [password], [userrid], [admingid], [nickname], [avatar], [paycredits], [rankcredits], [verifyemail], [verifymobile], [liftbantime], [salt]) VALUES (9, N'8888884444 ', N' ', N' ', N'5f64d53e153ec0663df276377ec7d8b1', 7, 1, N'BSP7855153 ', N' ', 4, 4, 0, 0, CAST(0x0000000000000000 AS DateTime), N'727748')
SET IDENTITY_INSERT [bsp_users] OFF
/****** Object: Table [dbo].[bsp_userranks] Script Date: 01/13/2015 10:12:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [bsp_userranks](
[userrid] [smallint] IDENTITY(1,1) NOT NULL,
[system] [int] NOT NULL,
[title] [nchar](50) NOT NULL,
[avatar] [char](50) NOT NULL,
[creditslower] [int] NOT NULL,
[creditsupper] [int] NOT NULL,
[limitdays] [int] NOT NULL,
CONSTRAINT [PK_bsp_userranks] PRIMARY KEY CLUSTERED
(
[userrid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'用户等级id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_userranks', @level2type=N'COLUMN',@level2name=N'userrid'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否为系统等级' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_userranks', @level2type=N'COLUMN',@level2name=N'system'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'标题' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_userranks', @level2type=N'COLUMN',@level2name=N'title'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'头像' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_userranks', @level2type=N'COLUMN',@level2name=N'avatar'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'积分下限' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_userranks', @level2type=N'COLUMN',@level2name=N'creditslower'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'积分上限' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_userranks', @level2type=N'COLUMN',@level2name=N'creditsupper'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'限制天数' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_userranks', @level2type=N'COLUMN',@level2name=N'limitdays'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'用户等级表' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_userranks'
GO
SET IDENTITY_INSERT [bsp_userranks] ON
INSERT [bsp_userranks] ([userrid], [system], [title], [avatar], [creditslower], [creditsupper], [limitdays]) VALUES (1, 1, N'禁止访问 ', N' ', 0, 0, 30)
INSERT [bsp_userranks] ([userrid], [system], [title], [avatar], [creditslower], [creditsupper], [limitdays]) VALUES (2, 1, N'禁止购买 ', N' ', 0, 0, 30)
INSERT [bsp_userranks] ([userrid], [system], [title], [avatar], [creditslower], [creditsupper], [limitdays]) VALUES (3, 1, N'禁止参加活动 ', N' ', 0, 0, 30)
INSERT [bsp_userranks] ([userrid], [system], [title], [avatar], [creditslower], [creditsupper], [limitdays]) VALUES (4, 1, N'禁止评论 ', N' ', 0, 0, 30)
INSERT [bsp_userranks] ([userrid], [system], [title], [avatar], [creditslower], [creditsupper], [limitdays]) VALUES (5, 1, N'禁止咨询 ', N' ', 0, 0, 30)
INSERT [bsp_userranks] ([userrid], [system], [title], [avatar], [creditslower], [creditsupper], [limitdays]) VALUES (6, 1, N'游客 ', N' ', 0, 0, 0)
INSERT [bsp_userranks] ([userrid], [system], [title], [avatar], [creditslower], [creditsupper], [limitdays]) VALUES (7, 0, N'铜牌会员 ', N'ura_1407011736593555489.png ', 0, 180, 0)
INSERT [bsp_userranks] ([userrid], [system], [title], [avatar], [creditslower], [creditsupper], [limitdays]) VALUES (8, 0, N'银牌会员 ', N'ura_1406081450117210245.jpg ', 180, -1, 0)
SET IDENTITY_INSERT [bsp_userranks] OFF
/****** Object: Table [dbo].[bsp_userdetails] Script Date: 01/13/2015 10:12:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [bsp_userdetails](
[uid] [int] NOT NULL,
[lastvisittime] [datetime] NOT NULL,
[lastvisitip] [char](15) NOT NULL,
[lastvisitrgid] [smallint] NOT NULL,
[registertime] [datetime] NOT NULL,
[registerip] [char](15) NOT NULL,
[registerrgid] [smallint] NOT NULL,
[gender] [tinyint] NOT NULL,
[realname] [nvarchar](10) NOT NULL,
[bday] [datetime] NOT NULL,
[idcard] [varchar](18) NOT NULL,
[regionid] [smallint] NOT NULL,
[address] [nvarchar](150) NOT NULL,
[bio] [nvarchar](300) NOT NULL,
CONSTRAINT [PK_bsp_userdetails] PRIMARY KEY CLUSTERED
(
[uid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CREATE NONCLUSTERED INDEX [registerip] ON [bsp_userdetails]
(
[registerip] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'用户id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_userdetails', @level2type=N'COLUMN',@level2name=N'uid'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'最后访问时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_userdetails', @level2type=N'COLUMN',@level2name=N'lastvisittime'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'最后访问ip' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_userdetails', @level2type=N'COLUMN',@level2name=N'lastvisitip'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'最后访问区域id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_userdetails', @level2type=N'COLUMN',@level2name=N'lastvisitrgid'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'注册时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_userdetails', @level2type=N'COLUMN',@level2name=N'registertime'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'注册ip' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_userdetails', @level2type=N'COLUMN',@level2name=N'registerip'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'注册区域id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_userdetails', @level2type=N'COLUMN',@level2name=N'registerrgid'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'性别' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_userdetails', @level2type=N'COLUMN',@level2name=N'gender'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'真实姓名' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_userdetails', @level2type=N'COLUMN',@level2name=N'realname'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'出生日期' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_userdetails', @level2type=N'COLUMN',@level2name=N'bday'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'身份证号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_userdetails', @level2type=N'COLUMN',@level2name=N'idcard'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'所在区域id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_userdetails', @level2type=N'COLUMN',@level2name=N'regionid'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'详细地址' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_userdetails', @level2type=N'COLUMN',@level2name=N'address'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'简介' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_userdetails', @level2type=N'COLUMN',@level2name=N'bio'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'用户详细信息表' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_userdetails'
GO
INSERT [bsp_userdetails] ([uid], [lastvisittime], [lastvisitip], [lastvisitrgid], [registertime], [registerip], [registerrgid], [gender], [realname], [bday], [idcard], [regionid], [address], [bio]) VALUES (1, CAST(0x0000A41C01258AE1 AS DateTime), N'127.0.0.1 ', -1, CAST(0x0000A359011E28FE AS DateTime), N' ', -1, 1, N'开发者', CAST(0x0000805700000000 AS DateTime), N'', 411, N'广安大街', N'开源网上商城')
INSERT [bsp_userdetails] ([uid], [lastvisittime], [lastvisitip], [lastvisitrgid], [registertime], [registerip], [registerrgid], [gender], [realname], [bday], [idcard], [regionid], [address], [bio]) VALUES (2, CAST(0x0000A36E006F26D0 AS DateTime), N'127.0.0.1 ', -1, CAST(0x0000A36E006F1EEB AS DateTime), N'127.0.0.1 ', -1, 0, N'', CAST(0x0000000000000000 AS DateTime), N'', 0, N'', N'')
INSERT [bsp_userdetails] ([uid], [lastvisittime], [lastvisitip], [lastvisitrgid], [registertime], [registerip], [registerrgid], [gender], [realname], [bday], [idcard], [regionid], [address], [bio]) VALUES (3, CAST(0x0000A36E0071964A AS DateTime), N'127.0.0.1 ', -1, CAST(0x0000A36E0071964A AS DateTime), N'127.0.0.1 ', -1, 0, N'', CAST(0x0000000000000000 AS DateTime), N'', 0, N'', N'')
INSERT [bsp_userdetails] ([uid], [lastvisittime], [lastvisitip], [lastvisitrgid], [registertime], [registerip], [registerrgid], [gender], [realname], [bday], [idcard], [regionid], [address], [bio]) VALUES (4, CAST(0x0000A36E011378AB AS DateTime), N'127.0.0.1 ', -1, CAST(0x0000A36E011378AB AS DateTime), N'127.0.0.1 ', -1, 0, N'', CAST(0x0000A36E011378AB AS DateTime), N'', 0, N'', N'')
INSERT [bsp_userdetails] ([uid], [lastvisittime], [lastvisitip], [lastvisitrgid], [registertime], [registerip], [registerrgid], [gender], [realname], [bday], [idcard], [regionid], [address], [bio]) VALUES (5, CAST(0x0000A37300A052BF AS DateTime), N'127.0.0.1 ', -1, CAST(0x0000A37300A0501D AS DateTime), N'127.0.0.1 ', -1, 0, N'', CAST(0x0000A37300A0501D AS DateTime), N'', 0, N'', N'')
INSERT [bsp_userdetails] ([uid], [lastvisittime], [lastvisitip], [lastvisitrgid], [registertime], [registerip], [registerrgid], [gender], [realname], [bday], [idcard], [regionid], [address], [bio]) VALUES (6, CAST(0x0000A37300B0FC0C AS DateTime), N'127.0.0.1 ', -1, CAST(0x0000A37300B0B952 AS DateTime), N'127.0.0.1 ', -1, 0, N'', CAST(0x0000A37300B0B952 AS DateTime), N'', 0, N'', N'')
INSERT [bsp_userdetails] ([uid], [lastvisittime], [lastvisitip], [lastvisitrgid], [registertime], [registerip], [registerrgid], [gender], [realname], [bday], [idcard], [regionid], [address], [bio]) VALUES (7, CAST(0x0000A37400E4D624 AS DateTime), N'127.0.0.1 ', -1, CAST(0x0000A37400E4D329 AS DateTime), N'127.0.0.1 ', -1, 0, N'', CAST(0x0000A37400E4D329 AS DateTime), N'', 0, N'', N'')
INSERT [bsp_userdetails] ([uid], [lastvisittime], [lastvisitip], [lastvisitrgid], [registertime], [registerip], [registerrgid], [gender], [realname], [bday], [idcard], [regionid], [address], [bio]) VALUES (8, CAST(0x0000A375009DB9AD AS DateTime), N'127.0.0.1 ', -1, CAST(0x0000A375009DB9AD AS DateTime), N'127.0.0.1 ', -1, 0, N'', CAST(0x0000A375009DB9AD AS DateTime), N'', 0, N'', N'')
INSERT [bsp_userdetails] ([uid], [lastvisittime], [lastvisitip], [lastvisitrgid], [registertime], [registerip], [registerrgid], [gender], [realname], [bday], [idcard], [regionid], [address], [bio]) VALUES (9, CAST(0x0000A38201015BE4 AS DateTime), N'127.0.0.1 ', -1, CAST(0x0000A382010158F7 AS DateTime), N'127.0.0.1 ', -1, 0, N'', CAST(0x0000A382010158F7 AS DateTime), N'', 0, N'', N'')
/****** Object: StoredProcedure [dbo].[bsp_getpartproductlistbypidlist] Script Date: 01/13/2015 10:12:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [bsp_getpartproductlistbypidlist]
@pidlist nvarchar(1000)
AS
BEGIN
SET NOCOUNT ON;
EXEC('SELECT [pid],[psn],[cateid],[brandid],[skugid],[name],[shopprice],[marketprice],[costprice],[state],[isbest],[ishot],[isnew],[displayorder],[weight],[showimg],[salecount],[visitcount],[reviewcount],[star1],[star2],[star3],[star4],[star5],[addtime]
FROM [bsp_products]
WHERE [pid] IN ('+@pidlist+') AND [state]=0 ORDER BY CHARINDEX('+''',''+CONVERT(varchar(10),[pid])+'','','','+@pidlist+','')')
END
GO
/****** Object: Table [dbo].[bsp_searchhistories] Script Date: 01/13/2015 10:12:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [bsp_searchhistories](
[recordid] [int] IDENTITY(1,1) NOT NULL,
[uid] [int] NOT NULL,
[word] [nvarchar](60) NOT NULL,
[times] [int] NOT NULL,
[updatetime] [datetime] NOT NULL,
CONSTRAINT [PK_bsp_searchhistories] PRIMARY KEY CLUSTERED
(
[recordid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [uid_word] ON [bsp_searchhistories]
(
[uid] ASC,
[word] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [updatetime] ON [bsp_searchhistories]
(
[updatetime] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'记录id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_searchhistories', @level2type=N'COLUMN',@level2name=N'recordid'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'用户id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_searchhistories', @level2type=N'COLUMN',@level2name=N'uid'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'搜索词' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_searchhistories', @level2type=N'COLUMN',@level2name=N'word'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'搜索次数' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_searchhistories', @level2type=N'COLUMN',@level2name=N'times'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'最后搜索时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_searchhistories', @level2type=N'COLUMN',@level2name=N'updatetime'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'搜索历史表' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bsp_searchhistories'
GO
SET IDENTITY_INSERT [bsp_searchhistories] ON
INSERT [bsp_searchhistories] ([recordid], [uid], [word], [times], [updatetime]) VALUES (9, 1, N'lumia', 2, CAST(0x0000A3FB008CB5FE AS DateTime))
INSERT [bsp_searchhistories] ([recordid], [uid], [word], [times], [updatetime]) VALUES (10, 1, N'<a>', 5, CAST(0x0000A3FC00AA2EB4 AS DateTime))
INSERT [bsp_searchhistories] ([recordid], [uid], [word], [times], [updatetime]) VALUES (11, 1, N'<a>', 2, CAST(0x0000A3FC00C18F73 AS DateTime))
INSERT [bsp_searchhistories] ([recordid], [uid], [word], [times], [updatetime]) VALUES (12, -1, N'lumia', 5, CAST(0x0000A4160164F238 AS DateTime))
SET IDENTITY_INSERT [bsp_searchhistories] OFF
/****** Object: StoredProcedure [dbo].[bsp_updateorderproductoid] Script Date: 01/13/2015 10:12:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [bsp_updateorderproductoid]
@recordidlist nvarchar(1000),
@oid int
AS
BEGIN
SET NOCOUNT ON;
EXEC('UPDATE [bsp_orderproducts] SET [oid]='+@oid+' WHERE [recordid] IN ('+@recordidlist+')');
END
GO
/****** Object: StoredProcedure [dbo].[bsp_voteproductreview] Script Date: 01/13/2015 10:12:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [bsp_voteproductreview]
@reviewid int,
@uid int,
@votetime datetime
AS
BEGIN
SET NOCOUNT ON;
UPDATE [bsp_productreviews] SET [quality]=[quality]+1 WHERE [reviewid]=@reviewid;
IF @@ERROR=0
BEGIN
INSERT INTO [bsp_productreviewquality]([reviewid],[uid],[votetime]) VALUES(@reviewid,@uid,@votetime)
END
END
GO
/****** Object: StoredProcedure [dbo].[bsp_updateorderisreview] Script Date: 01/13/2015 10:12:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [bsp_updateorderisreview]
@oid int,
@isreview tinyint
AS
BEGIN
SET NOCOUNT ON;
UPDATE [bsp_orders] SET [isreview]=@isreview WHERE [oid]=@oid;
END
GO
/****** Object: StoredProcedure [dbo].[bsp_updateorderdiscount] Script Date: 01/13/2015 10:12:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [bsp_updateorderdiscount]
@oid int,
@discount decimal(18,2),
@orderamount decimal(18,2),
@surplusmoney decimal(18,2)
AS
BEGIN
SET NOCOUNT ON;
UPDATE [bsp_orders] SET [orderamount]=@orderamount,[surplusmoney]=@surplusmoney,[discount]=@discount WHERE [oid]=@oid;
END
GO
/****** Object: StoredProcedure [dbo].[bsp_updateonlineuseruid] Script Date: 01/13/2015 10:12:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [bsp_updateonlineuseruid]
@olid int,
@uid int
AS
BEGIN
SET NOCOUNT ON;
UPDATE [bsp_onlineusers] SET [uid]=@uid WHERE [olid]=@olid
END
GO
/****** Object: StoredProcedure [dbo].[bsp_updateonlineuserip] Script Date: 01/13/2015 10:12:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [bsp_updateonlineuserip]
@olid int,
@ip char(15)
AS
BEGIN
SET NOCOUNT ON;
UPDATE [bsp_onlineusers] SET [ip]=@ip WHERE [olid]=@olid
END
GO
/****** Object: StoredProcedure [dbo].[bsp_updatecartuidbysid] Script Date: 01/13/2015 10:12:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [bsp_updatecartuidbysid]
@uid int,
@sid char(16)
AS
BEGIN
SET NOCOUNT ON;
UPDATE [bsp_orderproducts] SET [uid]=@uid,[sid]='' WHERE [sid]=@sid AND [oid]=0;
END
GO
/****** Object: StoredProcedure [dbo].[bsp_reviewproduct] Script Date: 01/13/2015 10:12:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [bsp_reviewproduct]
@pid int
,@uid int
,@oprid int
,@oid int
,@parentid int
,@state tinyint
,@star tinyint
,@quality smallint
,@message nvarchar(200)
,@reviewtime datetime
,@paycredits int
,@pname nvarchar(200)
,@pshowimg nvarchar(100)
,@buytime datetime
,@ip varchar(15)
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO [bsp_productreviews]
([pid]
,[uid]
,[oprid]
,[oid]
,[parentid]
,[state]
,[star]
,[quality]
,[message]
,[reviewtime]
,[paycredits]
,[pname]
,[pshowimg]
,[buytime]
,[ip])
VALUES
(@pid
,@uid
,@oprid
,@oid
,@parentid
,@state
,@star
,@quality
,@message
,@reviewtime
,@paycredits
,@pname
,@pshowimg
,@buytime
,@ip);
if(@@rowcount>0)
BEGIN
UPDATE [bsp_orderproducts] SET [isreview]=1 WHERE [recordid]=@oprid;
END
END
GO
/****** Object: StoredProcedure [dbo].[bsp_returnuserorderusecoupons] Script Date: 01/13/2015 10:12:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [bsp_returnuserorderusecoupons]
@oid int
AS
BEGIN
SET NOCOUNT ON;
UPDATE [bsp_coupons] SET [oid]=0,[usetime]='1900-1-1 00:00:00',[useip]='' WHERE [oid]=@oid;
END
GO
/****** Object: StoredProcedure [dbo].[bsp_getpartproductbyid] Script Date: 01/13/2015 10:12:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [bsp_getpartproductbyid]
@pid int
AS
BEGIN
SET NOCOUNT ON;
SELECT [pid]
,[psn]
,[cateid]
,[brandid]
,[skugid]
,[name]
,[shopprice]
,[marketprice]
,[costprice]
,[state]
,[isbest]
,[ishot]
,[isnew]
,[displayorder]
,[weight]
,[showimg]
,[salecount]
,[visitcount]
,[reviewcount]
,[star1]
,[star2]
,[star3]
,[star4]
,[star5]
,[addtime]
FROM [bsp_products] WHERE [pid]=@pid AND [state]=0;
END
GO
/****** Object: StoredProcedure [dbo].[bsp_usecoupon] Script Date: 01/13/2015 10:12:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [bsp_usecoupon]
@couponid int,
@oid int,
@time datetime,
@ip char(15)
AS
BEGIN
SET NOCOUNT ON;
UPDATE [bsp_coupons] SET [oid]=@oid,[usetime]=@time,[useip]=@ip WHERE [couponid]=@couponid;
END
GO
/****** Object: StoredProcedure [dbo].[bsp_updateuserrankbyuid] Script Date: 01/13/2015 10:12:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [bsp_updateuserrankbyuid]
@uid int,
@userrid smallint
AS
BEGIN
SET NOCOUNT ON;
UPDATE [bsp_users] SET [userrid]=@userrid WHERE [uid]=@uid
END
GO
/****** Object: StoredProcedure [dbo].[bsp_updateuserpasswordbyuid] Script Date: 01/13/2015 10:12:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [bsp_updateuserpasswordbyuid]
@uid int,
@password char(32)
AS
BEGIN
SET NOCOUNT ON;
UPDATE [bsp_users] SET [password]=@password WHERE [uid]=@uid;
END
GO
/****** Object: StoredProcedure [dbo].[bsp_updateusermobilebyuid] Script Date: 01/13/2015 10:12:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [bsp_updateusermobilebyuid]
@uid int,
@mobile char(15)
AS
BEGIN
SET NOCOUNT ON;
UPDATE [bsp_users] SET [mobile]=@mobile,[verifymobile]=1 WHERE [uid]=@uid;
END
GO
/****** Object: StoredProcedure [dbo].[bsp_updateuserliftbantimebyuid] Script Date: 01/13/2015 10:12:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [bsp_updateuserliftbantimebyuid]
@uid int,
@liftbantime datetime
AS
BEGIN
SET NOCOUNT ON;
UPDATE [bsp_users] SET [liftbantime]=@liftbantime WHERE [uid]=@uid;
END
GO
/****** Object: StoredProcedure [dbo].[bsp_updateuserlastvisit] Script Date: 01/13/2015 10:12:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [bsp_updateuserlastvisit]
@uid int,
@visittime datetime,
@ip char(15),
@regionid smallint
AS
BEGIN
SET NOCOUNT ON;
UPDATE [bsp_userdetails] SET [lastvisittime]=@visittime,[lastvisitip]=@ip,[lastvisitrgid]=@regionid WHERE [uid]=@uid
END
GO
/****** Object: StoredProcedure [dbo].[bsp_updateuseremailbyuid] Script Date: 01/13/2015 10:12:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [bsp_updateuseremailbyuid]
@uid int,
@email char(50)
AS
BEGIN
SET NOCOUNT ON;
UPDATE [bsp_users] SET [email]=@email,[verifyemail]=1 WHERE [uid]=@uid;
END
GO
/****** Object: StoredProcedure [dbo].[bsp_updateuserdetail] Script Date: 01/13/2015 10:12:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [bsp_updateuserdetail]
@uid int,
@lastvisittime datetime,
@lastvisitip char(15),
@lastvisitrgid smallint,
@registertime datetime,
@registerip char(15),
@registerrgid smallint,
@gender tinyint,
@realname nvarchar(10),
@bday datetime,
@idcard varchar(18),
@regionid smallint,
@address nvarchar(150),
@bio nvarchar(300)
AS
BEGIN
SET NOCOUNT ON;
UPDATE [bsp_userdetails]
SET [gender] = @gender
,[lastvisittime] = @lastvisittime
,[lastvisitip] = @lastvisitip
,[lastvisitrgid]=@lastvisitrgid
,[registertime] = @registertime
,[registerip] = @registerip
,[registerrgid]=@registerrgid
,[realname] = @realname
,[bday] = @bday
,[idcard] = @idcard
,[regionid] = @regionid
,[address] = @address
,[bio] = @bio
WHERE [uid]=@uid;
END
GO
/****** Object: StoredProcedure [dbo].[bsp_updateuser] Script Date: 01/13/2015 10:12:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [bsp_updateuser]
@uid int,
@username nchar(20),
@email char(50),
@mobile char(15),
@password char(32),
@userrid smallint,
@admingid smallint,
@nickname nchar(20),
@avatar char(40),
@paycredits int,
@rankcredits int,
@verifyemail tinyint,
@verifymobile tinyint,
@liftbantime datetime,
@salt nchar(6),
@lastvisittime datetime,
@lastvisitip char(15),
@lastvisitrgid smallint,
@registertime datetime,
@registerip char(15),
@registerrgid smallint,
@gender tinyint,
@realname nvarchar(10),
@bday datetime,
@idcard varchar(18),
@regionid smallint,
@address nvarchar(150),
@bio nvarchar(300)
AS
BEGIN
SET NOCOUNT ON;
UPDATE [bsp_users]
SET [username] = @username
,[email] = @email
,[mobile] = @mobile
,[password] = @password
,[userrid] = @userrid
,[admingid] = @admingid
,[nickname] = @nickname
,[avatar] = @avatar
,[paycredits] = @paycredits
,[rankcredits] = @rankcredits
,[verifyemail] = @verifyemail
,[verifymobile] = @verifymobile
,[liftbantime]=@liftbantime
,[salt] = @salt
WHERE [uid]=@uid;
UPDATE [bsp_userdetails]
SET [gender] = @gender
,[lastvisittime] = @lastvisittime
,[lastvisitip] = @lastvisitip
,[lastvisitrgid]=@lastvisitrgid
,[registertime] = @registertime
,[registerip] = @registerip
,[registerrgid]=@registerrgid
,[realname] = @realname
,[bday] = @bday
,[idcard] = @idcard
,[regionid] = @regionid
,[address] = @address
,[bio] = @bio
WHERE [uid]=@uid;
END
GO
/****** Object: StoredProcedure [dbo].[bsp_updateucenteruser] Script Date: 01/13/2015 10:12:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [bsp_updateucenteruser]
@uid int,
@username nchar(20),
@nickname nchar(20),
@avatar char(40),
@gender tinyint,
@realname nvarchar(10),
@bday datetime,
@idcard varchar(18),
@regionid smallint,
@address nvarchar(150),
@bio nvarchar(300)
AS
BEGIN
UPDATE [bsp_users] SET [username]=@username,[nickname]=@nickname,[avatar]=@avatar WHERE [uid]=@uid;
UPDATE [bsp_userdetails] SET [gender]=@gender,[realname]=@realname,[bday]=@bday,[idcard]=@idcard,[regionid]=@regionid,[address]=@address,[bio]=@bio WHERE [uid]=@uid;
UPDATE [bsp_productconsults] SET [consultnickname]=@nickname WHERE [consultid]=@uid;
UPDATE [bsp_productconsults] SET [replynickname]=@nickname WHERE [replyuid]=@uid;
END
GO
/****** Object: StoredProcedure [dbo].[bsp_updateshipaddressisdefault] Script Date: 01/13/2015 10:12:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [bsp_updateshipaddressisdefault]
@said int,
@uid int,
@isdefault tinyint
AS
BEGIN
IF @isdefault=1
BEGIN
UPDATE [bsp_shipaddresses] SET [isdefault]=0 WHERE [uid]=@uid AND [isdefault]=1;
END
UPDATE [bsp_shipaddresses] SET [isdefault]=@isdefault WHERE [said]=@said AND [uid]=@uid;
END
GO
/****** Object: StoredProcedure [dbo].[bsp_updateshipaddress] Script Date: 01/13/2015 10:12:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [bsp_updateshipaddress]
@said int
,@uid int
,@regionid smallint
,@isdefault tinyint
,@alias nvarchar(50)
,@consignee nvarchar(20)
,@mobile varchar(15)
,@phone varchar(12)
,@email varchar(50)
,@zipcode char(6)
,@address nvarchar(150)
AS
BEGIN
SET NOCOUNT ON;
IF @isdefault = 1
BEGIN
UPDATE [bsp_shipaddresses] SET [isdefault]=0 WHERE [uid]=@uid AND [isdefault]=1
END
UPDATE [bsp_shipaddresses] SET [uid]=@uid,[regionid]=@regionid,[isdefault]=@isdefault,[alias]=@alias,[consignee]=@consignee,[mobile]=@mobile,[phone]=@phone,[email]=@email,[zipcode]=@zipcode,[address]=@address WHERE [said]=@said AND [uid]=@uid
END
GO
2.1版本的577-590行是1.9所没有的
2.1版本的609-681行是1.9所没有的
但是1.9版本707至719是2.1所没有
更新语句如下:(以下有些存储过程可能存在,我懒的判断。请直接根据自己的情况进行处理)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [bsp_updateorderproductoid]
@recordidlist nvarchar(1000),
@oid int
AS
BEGIN
SET NOCOUNT ON;
EXEC('UPDATE [bsp_orderproducts] SET [oid]='+@oid+' WHERE [recordid] IN ('+@recordidlist+')');
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [bsp_updateorderisreview]
@oid int,
@isreview tinyint
AS
BEGIN
SET NOCOUNT ON;
UPDATE [bsp_orders] SET [isreview]=@isreview WHERE [oid]=@oid;
END
GO
/****** Object: StoredProcedure [dbo].[bsp_updateorderdiscount] Script Date: 01/13/2015 10:12:00 ******/
DROP PROCEDURE [bsp_updateorderdiscount]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [bsp_updateorderdiscount]
@oid int,
@discount decimal(18,2),
@orderamount decimal(18,2),
@surplusmoney decimal(18,2)
AS
BEGIN
SET NOCOUNT ON;
UPDATE [bsp_orders] SET [orderamount]=@orderamount,[surplusmoney]=@surplusmoney,[discount]=@discount WHERE [oid]=@oid;
END
GO
/****** Object: StoredProcedure [dbo].[bsp_updateonlineuseruid] Script Date: 01/13/2015 10:12:00 ******/
DROP PROCEDURE [bsp_updateonlineuseruid]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [bsp_updateonlineuseruid]
@olid int,
@uid int
AS
BEGIN
SET NOCOUNT ON;
UPDATE [bsp_onlineusers] SET [uid]=@uid WHERE [olid]=@olid
END
GO
/****** Object: StoredProcedure [dbo].[bsp_updateonlineuserip] Script Date: 01/13/2015 10:12:00 ******/
DROP PROCEDURE [bsp_updateonlineuserip]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [bsp_updateonlineuserip]
@olid int,
@ip char(15)
AS
BEGIN
SET NOCOUNT ON;
UPDATE [bsp_onlineusers] SET [ip]=@ip WHERE [olid]=@olid
END
GO
/****** Object: StoredProcedure [dbo].[bsp_updatecartuidbysid] Script Date: 01/13/2015 10:12:00 ******/
DROP PROCEDURE [bsp_updatecartuidbysid]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [bsp_updatecartuidbysid]
@uid int,
@sid char(16)
AS
BEGIN
SET NOCOUNT ON;
UPDATE [bsp_orderproducts] SET [uid]=@uid,[sid]='' WHERE [sid]=@sid AND [oid]=0;
END
GO