在Navicat创建的SQL server 程序脚本（2）
示例2：网上商城 数据库 结构与数据
/*
Navicat Premium Data Transfer
Source Server : SQL SERVER
Source Server Type : SQL Server
Source Server Version : 11003128
Source Host : localhost:1433
Source Catalog : 网上商城
Source Schema : dbo
Target Server Type : SQL Server
Target Server Version : 11003128
File Encoding : 65001
Date: 06/06/2024 08:25:07
*/
-- ----------------------------
-- Table structure for 订单
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[订单]') AND type IN ('U'))
DROP TABLE [dbo].[订单]
GO
CREATE TABLE [dbo].[订单] (
[订单ID] int NOT NULL,
[会员ID] int NULL,
[订单号] varchar(20) COLLATE Chinese_PRC_CI_AS NULL,
[订单金额] decimal(20,2) NULL,
[下单时间] datetime NULL
)
GO
ALTER TABLE [dbo].[订单] SET (LOCK_ESCALATION = TABLE)
GO
-- ----------------------------
-- Records of 订单
-- ----------------------------
INSERT INTO [dbo].[订单] ([订单ID], [会员ID], [订单号], [订单金额], [下单时间]) VALUES (N'1', N'1', N'O210912082615101', N'183.00', N'2021-09-12 08:26:15.000')
GO
INSERT INTO [dbo].[订单] ([订单ID], [会员ID], [订单号], [订单金额], [下单时间]) VALUES (N'2', N'2', N'O210912082615102', N'273.00', N'2021-09-12 08:26:15.000')
GO
INSERT INTO [dbo].[订单] ([订单ID], [会员ID], [订单号], [订单金额], [下单时间]) VALUES (N'3', N'4', N'O210912082615103', N'549.00', N'2021-09-12 08:26:15.000')
GO
INSERT INTO [dbo].[订单] ([订单ID], [会员ID], [订单号], [订单金额], [下单时间]) VALUES (N'4', N'11', N'O210912082615104', N'494.00', N'2021-09-12 08:26:15.000')
GO
INSERT INTO [dbo].[订单] ([订单ID], [会员ID], [订单号], [订单金额], [下单时间]) VALUES (N'5', N'6', N'O210912082615105', N'5400.00', N'2021-09-12 08:26:15.000')
GO
INSERT INTO [dbo].[订单] ([订单ID], [会员ID], [订单号], [订单金额], [下单时间]) VALUES (N'6', N'7', N'O210912082615106', N'98.00', N'2021-09-12 08:26:15.000')
GO
INSERT INTO [dbo].[订单] ([订单ID], [会员ID], [订单号], [订单金额], [下单时间]) VALUES (N'7', N'8', N'O210912082615107', N'3299.00', N'2021-09-12 08:26:15.000')
GO
INSERT INTO [dbo].[订单] ([订单ID], [会员ID], [订单号], [订单金额], [下单时间]) VALUES (N'8', N'9', N'O210912082615108', N'407.00', N'2021-09-12 08:26:15.000')
GO
INSERT INTO [dbo].[订单] ([订单ID], [会员ID], [订单号], [订单金额], [下单时间]) VALUES (N'9', N'1', N'O210912065632109', N'530.00', N'2021-09-12 18:56:32.000')
GO
INSERT INTO [dbo].[订单] ([订单ID], [会员ID], [订单号], [订单金额], [下单时间]) VALUES (N'10', N'12', N'O210912065632110', N'486.00', N'2021-09-12 18:56:32.000')
GO
INSERT INTO [dbo].[订单] ([订单ID], [会员ID], [订单号], [订单金额], [下单时间]) VALUES (N'11', N'2', N'O210912065632111', N'549.00', N'2021-09-12 18:56:32.000')
GO
INSERT INTO [dbo].[订单] ([订单ID], [会员ID], [订单号], [订单金额], [下单时间]) VALUES (N'12', N'8', N'O210912065632112', N'196.00', N'2021-09-12 18:56:32.000')
GO
INSERT INTO [dbo].[订单] ([订单ID], [会员ID], [订单号], [订单金额], [下单时间]) VALUES (N'13', N'9', N'O210912065632113', N'255.00', N'2021-09-12 18:56:32.000')
GO
INSERT INTO [dbo].[订单] ([订单ID], [会员ID], [订单号], [订单金额], [下单时间]) VALUES (N'14', N'1', N'O210913102745114', N'16.00', N'2021-09-13 10:27:45.000')
GO
INSERT INTO [dbo].[订单] ([订单ID], [会员ID], [订单号], [订单金额], [下单时间]) VALUES (N'15', N'6', N'O210913102745115', N'12.00', N'2021-09-13 10:27:45.000')
GO
INSERT INTO [dbo].[订单] ([订单ID], [会员ID], [订单号], [订单金额], [下单时间]) VALUES (N'16', N'5', N'O210913102745116', N'80.00', N'2021-09-13 10:27:45.000')
GO
INSERT INTO [dbo].[订单] ([订单ID], [会员ID], [订单号], [订单金额], [下单时间]) VALUES (N'17', N'2', N'O210913102745117', N'49.00', N'2021-09-13 10:27:45.000')
GO
INSERT INTO [dbo].[订单] ([订单ID], [会员ID], [订单号], [订单金额], [下单时间]) VALUES (N'18', N'7', N'O210913081530118', N'98.00', N'2021-09-13 20:15:30.000')
GO
INSERT INTO [dbo].[订单] ([订单ID], [会员ID], [订单号], [订单金额], [下单时间]) VALUES (N'19', N'7', N'O210913081530119', N'860.00', N'2021-09-13 20:15:30.000')
GO
INSERT INTO [dbo].[订单] ([订单ID], [会员ID], [订单号], [订单金额], [下单时间]) VALUES (N'20', N'12', N'O210913081530120', N'144.00', N'2021-09-13 20:15:30.000')
GO
INSERT INTO [dbo].[订单] ([订单ID], [会员ID], [订单号], [订单金额], [下单时间]) VALUES (N'21', N'11', N'O210913081530121', N'1880.00', N'2021-09-13 20:15:30.000')
GO
INSERT INTO [dbo].[订单] ([订单ID], [会员ID], [订单号], [订单金额], [下单时间]) VALUES (N'22', N'2', N'O210913081530122', N'549.00', N'2021-09-13 20:15:30.000')
GO
INSERT INTO [dbo].[订单] ([订单ID], [会员ID], [订单号], [订单金额], [下单时间]) VALUES (N'23', N'4', N'O210913081530123', N'16.00', N'2021-09-13 20:15:30.000')
GO
INSERT INTO [dbo].[订单] ([订单ID], [会员ID], [订单号], [订单金额], [下单时间]) VALUES (N'24', N'8', N'O210914043320124', N'16.00', N'2021-09-14 16:33:20.000')
GO
INSERT INTO [dbo].[订单] ([订单ID], [会员ID], [订单号], [订单金额], [下单时间]) VALUES (N'25', N'1', N'O210914043320125', N'8.00', N'2021-09-14 16:33:20.000')
GO
INSERT INTO [dbo].[订单] ([订单ID], [会员ID], [订单号], [订单金额], [下单时间]) VALUES (N'26', N'4', N'O210914043320126', N'38.00', N'2021-09-14 16:33:20.000')
GO
-- ----------------------------
-- Table structure for 订单详情
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[订单详情]') AND type IN ('U'))
DROP TABLE [dbo].[订单详情]
GO
CREATE TABLE [dbo].[订单详情] (
[详情ID] int NOT NULL,
[订单ID] int NULL,
[商品ID] int NULL,
[下单数量] int NULL
)
GO
ALTER TABLE [dbo].[订单详情] SET (LOCK_ESCALATION = TABLE)
GO
-- ----------------------------
-- Records of 订单详情
-- ----------------------------
-- ----------------------------
-- Table structure for 购物车
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[购物车]') AND type IN ('U'))
DROP TABLE [dbo].[购物车]
GO
CREATE TABLE [dbo].[购物车] (
[购物车ID] int NOT NULL,
[会员ID] int NULL,
[商品ID] int NULL,
[购买数量] int NULL
)
GO
ALTER TABLE [dbo].[购物车] SET (LOCK_ESCALATION = TABLE)
GO
-- ----------------------------
-- Records of 购物车
-- ----------------------------
-- ----------------------------
-- Table structure for 会员
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[会员]') AND type IN ('U'))
DROP TABLE [dbo].[会员]
GO
CREATE TABLE [dbo].[会员] (
[会员ID] int NOT NULL,
[登录名] varchar(20) COLLATE Chinese_PRC_CI_AS NULL,
[用户名] varchar(30) COLLATE Chinese_PRC_CI_AS NULL,
[密码] varchar(50) COLLATE Chinese_PRC_CI_AS NULL,
[性别] char(2) COLLATE Chinese_PRC_CI_AS NULL,
[出生日期] date NULL,
[所在城市] varchar(50) COLLATE Chinese_PRC_CI_AS NULL,
[邮箱] varchar(50) COLLATE Chinese_PRC_CI_AS NULL,
[积分] int NULL,
[注册时间] date NULL
)
GO
ALTER TABLE [dbo].[会员] SET (LOCK_ESCALATION = TABLE)
GO
-- ----------------------------
-- Records of 会员
-- ----------------------------
-- ----------------------------
-- Table structure for 商品
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[商品]') AND type IN ('U'))
DROP TABLE [dbo].[商品]
GO
CREATE TABLE [dbo].[商品] (
[商品ID] int NOT NULL,
[类别ID] int NOT NULL,
[商品编号] varchar(50) COLLATE Chinese_PRC_CI_AS NULL,
[商品名称] varchar(200) COLLATE Chinese_PRC_CI_AS NULL,
[价格] decimal(20,2) NULL,
[库存数量] int NULL,
[销售数量] int NULL,
[上架时间] date NULL,
[是否热销] smallint NULL,
[商品图片] varchar(255) COLLATE Chinese_PRC_CI_AS NULL
)
GO
ALTER TABLE [dbo].[商品] SET (LOCK_ESCALATION = TABLE)
GO
-- ----------------------------
-- Records of 商品
-- ----------------------------
INSERT INTO [dbo].[商品] ([商品ID], [类别ID], [商品编号], [商品名称], [价格], [库存数量], [销售数量], [上架时间], [是否热销], [商品图片]) VALUES (N'1', N'1', N'G0101', N'林清玄启悟人生系列：愿你，归来仍是少年', N'29.00', N'996', N'4', N'2021-06-07', N'0', N'/resources/upload/g0111.jpg')
GO
INSERT INTO [dbo].[商品] ([商品ID], [类别ID], [商品编号], [商品名称], [价格], [库存数量], [销售数量], [上架时间], [是否热销], [商品图片]) VALUES (N'2', N'1', N'G0102', N'平凡的世界：全三册（激励青年的不朽经典）', N'94.00', N'947', N'53', N'2021-06-07', N'1', N'/resources/upload/g0112.jpg')
GO
INSERT INTO [dbo].[商品] ([商品ID], [类别ID], [商品编号], [商品名称], [价格], [库存数量], [销售数量], [上架时间], [是否热销], [商品图片]) VALUES (N'3', N'1', N'G0103', N'曾国藩全集（全六卷 绸面精装插盒珍藏版）', N'255.00', N'998', N'2', N'2021-06-07', N'0', N'/resources/upload/g0113.jpg')
GO
INSERT INTO [dbo].[商品] ([商品ID], [类别ID], [商品编号], [商品名称], [价格], [库存数量], [销售数量], [上架时间], [是否热销], [商品图片]) VALUES (N'4', N'1', N'G0104', N'中外文化文学经典系列 红岩 导读与赏析', N'29.00', N'995', N'5', N'2021-07-07', N'0', N'/resources/upload/g0114.jpg')
GO
INSERT INTO [dbo].[商品] ([商品ID], [类别ID], [商品编号], [商品名称], [价格], [库存数量], [销售数量], [上架时间], [是否热销], [商品图片]) VALUES (N'5', N'2', N'G0201', N'古琴 老杉木乐器伏羲式_七弦琴 ', N'3299.00', N'19', N'1', N'2021-07-07', N'0', N'/resources/upload/g0211.jpg')
GO
INSERT INTO [dbo].[商品] ([商品ID], [类别ID], [商品编号], [商品名称], [价格], [库存数量], [销售数量], [上架时间], [是否热销], [商品图片]) VALUES (N'6', N'2', N'G0202', N'专业演奏级乐器洞箫_8孔正手G调', N'549.00', N'97', N'3', N'2021-07-07', N'0', N'/resources/upload/g0212.jpg')
GO
INSERT INTO [dbo].[商品] ([商品ID], [类别ID], [商品编号], [商品名称], [价格], [库存数量], [销售数量], [上架时间], [是否热销], [商品图片]) VALUES (N'7', N'3', N'G0301', N'密园小农 当地新鲜圆生菜 约500g ', N'8.00', N'959', N'41', N'2021-08-07', N'1', N'/resources/upload/g0311.jpg')
GO
INSERT INTO [dbo].[商品] ([商品ID], [类别ID], [商品编号], [商品名称], [价格], [库存数量], [销售数量], [上架时间], [是否热销], [商品图片]) VALUES (N'8', N'3', N'G0302', N'寻真水果 山东烟台栖霞红富士苹果 5kg ', N'98.00', N'989', N'11', N'2021-08-23', N'0', N'/resources/upload/g0312.jpg')
GO
INSERT INTO [dbo].[商品] ([商品ID], [类别ID], [商品编号], [商品名称], [价格], [库存数量], [销售数量], [上架时间], [是否热销], [商品图片]) VALUES (N'9', N'3', N'G0303', N'密园小农 新鲜自然成熟 西红柿 500g', N'6.00', N'985', N'15', N'2021-08-30', N'0', N'/resources/upload/g0313.jpg')
GO
INSERT INTO [dbo].[商品] ([商品ID], [类别ID], [商品编号], [商品名称], [价格], [库存数量], [销售数量], [上架时间], [是否热销], [商品图片]) VALUES (N'10', N'4', N'G0401', N'三星 500GB SSD固态硬盘 SATA3.0接口 ', N'400.00', N'498', N'2', N'2021-09-01', N'0', N'/resources/upload/g0411.jpg')
GO
INSERT INTO [dbo].[商品] ([商品ID], [类别ID], [商品编号], [商品名称], [价格], [库存数量], [销售数量], [上架时间], [是否热销], [商品图片]) VALUES (N'11', N'4', N'G0402', N'爱国者 128GB Type-C USB3.1 手机U盘 ', N'86.00', N'459', N'41', N'2021-09-01', N'1', N'/resources/upload/g0412.jpg')
GO
-- ----------------------------
-- Table structure for 商品类别
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[商品类别]') AND type IN ('U'))
DROP TABLE [dbo].[商品类别]
GO
CREATE TABLE [dbo].[商品类别] (
[类别ID] int NOT NULL,
[类别名称] varchar(30) COLLATE Chinese_PRC_CI_AS NULL
)
GO
ALTER TABLE [dbo].[商品类别] SET (LOCK_ESCALATION = TABLE)
GO
-- ----------------------------
-- Records of 商品类别
-- ----------------------------
INSERT INTO [dbo].[商品类别] ([类别ID], [类别名称]) VALUES (N'1', N'图书')
GO
INSERT INTO [dbo].[商品类别] ([类别ID], [类别名称]) VALUES (N'2', N'乐器')
GO
INSERT INTO [dbo].[商品类别] ([类别ID], [类别名称]) VALUES (N'3', N'蔬菜水果')
GO
INSERT INTO [dbo].[商品类别] ([类别ID], [类别名称]) VALUES (N'4', N'电脑及配件')
GO
INSERT INTO [dbo].[商品类别] ([类别ID], [类别名称]) VALUES (N'5', N'家用电器')
GO
-- ----------------------------
-- Primary Key structure for table 订单详情
-- ----------------------------
ALTER TABLE [dbo].[订单详情] ADD CONSTRAINT [_copy_2] PRIMARY KEY CLUSTERED ([详情ID])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO
-- ----------------------------
-- Primary Key structure for table 购物车
-- ----------------------------
ALTER TABLE [dbo].[购物车] ADD CONSTRAINT [_copy_3] PRIMARY KEY CLUSTERED ([购物车ID])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO
-- ----------------------------
-- Primary Key structure for table 会员
-- ----------------------------
ALTER TABLE [dbo].[会员] ADD CONSTRAINT [_copy_7] PRIMARY KEY CLUSTERED ([会员ID])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO
-- ----------------------------
-- Primary Key structure for table 商品
-- ----------------------------
ALTER TABLE [dbo].[商品] ADD CONSTRAINT [_copy_8] PRIMARY KEY CLUSTERED ([商品ID], [类别ID])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO
-- ----------------------------
-- Primary Key structure for table 商品类别
-- ----------------------------
ALTER TABLE [dbo].[商品类别] ADD CONSTRAINT [PK__网上商城.商品类__4567E80278C684EF] PRIMARY KEY CLUSTERED ([类别ID])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO
