WebShop(不带约束)

-- =========================================================
-- 创建数据库 WebShop
-- 请首先在D盘建立一个名字为Data的文件夹，或修改语句中的盘符
-- =========================================================
CREATE DATABASE WebShop
ON
(
NAME='D:\Data\WebShop_data',
FILENAME='D:\Data\WebShop.mdf',
SIZE=3,
MAXSIZE=10,
FILEGROWTH=2
)
LOG ON
(
NAME='D:\Data\WebShop_log',
FILENAME='D:\Data\WebShop.ldf',
SIZE=3,
MAXSIZE=10,
FILEGROWTH=2
)
go
USE WebShop
go

-- ==========================
-- 1.创建客户表 Customers
-- ==========================
CREATE TABLE Customers
(
c_ID char(5), -- 客户编号
c_Name varchar(30), -- 客户名称
c_TrueName varchar(30), -- 真实姓名
c_Gender char(2), -- 性别
c_Birth datetime, -- 出生日期
c_CardID varchar(18), -- 身份证号
c_Address varchar(50), -- 客户地址
c_Postcode char(6), -- 邮政编码
c_Mobile varchar(11), -- 手机号码
c_Phone varchar(15), -- 固定电话
c_Email varchar(50), -- 电子邮箱
c_Password varchar(30), -- 密码
c_SafeCode char(6) , -- 安全码
c_Question varchar(50), -- 提示问题
c_Answer varchar(50), -- 提示答案
c_Type varchar(10) -- 用户类型(普通用户、VIP用户)
)

INSERT Customers VALUES('C0001','liuzc','刘志成','男','1972-05-18','120104************','湖南株洲市','412000','133********','0733-*******','liuzc518@https://www.sodocs.net/doc/b710896059.html,','123456','6666','你的生日哪一天','5月18日','普通')
INSERT Customers VALUES('C0002','liujin','刘津津','女','1986-04-14','430202************','湖南长沙市','410001','133********','0731-*******','amy@https://www.sodocs.net/doc/b710896059.html,','123456','6666','你出生在哪里','湖南长沙','普通')
INSERT Customers VALUES('C0003','wangym','王咏梅','女','1976-08-06','120102************','湖南长沙市','410001','135********','0731-*******','wangym@https://www.sodocs.net/doc/b710896059.html,','123456','6666','你最喜爱的人是谁','女儿','VIP')
INSERT Customers VALUES('C0004','hangxf','黄幸福','男','1978-04-06','120102************','广东顺德市','310001','136********','0757-********','hangxf@https://www.sodocs.net/doc/b710896059.html,','123456','6666','你最喜爱的人是谁','我的父亲','普通')
INSERT Customers VALUES('C0005','hangrong','黄蓉','女','1982-12-01','220102************','湖北武汉市','510001','136********','024-********','hangrong@https://www.sodocs.net/doc/b710896059.html,','123456','6666','你出生在哪里','湖北武汉','普通')
INSERT Customers VALUES('C0006','chenhx','陈欢喜','男','1970-02-08','430202************','湖南株洲市','412001','136********','0733-********','chenhx@https://www.sodocs.net/doc/b710896059.html,','123456','6666','你出生在哪里','湖南株洲','VIP')
INSERT Customers VALUES('C0007','wubo','吴波','男','1979-10-10','430202************','湖南株洲市','412001','136********','0733-********','wubo@https://www.sodocs.net/doc/b710896059.html,','123456','6666','你的生日哪一天','10月10日','普通')
INSERT Customers VALUES('

C0008','luogh','罗桂华','女','1985-04-26','430201************','湖南株洲市','412001','135********','0733-*******','guihua@https://www.sodocs.net/doc/b710896059.html,','123456','6666','你的生日哪一天','4月26日','普通')
INSERT Customers VALUES('C0009','wubin','吴兵','女','1987-09-09','430201************','湖南株洲市','412001','138********','0733-*******','wubin0808@https://www.sodocs.net/doc/b710896059.html,','123456','6666','你出生在哪里','湖南株洲','普通')
INSERT Customers VALUES('C0010','wenziyu','文子玉','女','1988-05-20','320908************','河南郑州市','622000','138********','0327-8208208','wuziyu@https://www.sodocs.net/doc/b710896059.html,','123456','6666','你的生日哪一天','5月20日','VIP')
GO
-- ==============================
--2. 创建商品类别表 TYPES
-- ==============================
CREATE TABLE Types
(
t_ID char(2), -- 商品的分类编号
t_Name varchar(50), -- 商品的分类名称
t_Description varchar(100) -- 商品类别描述
)
INSERT INTO Types VALUES('01','通信产品','包括手机和电话等通信产品')
INSERT INTO Types VALUES('02','电脑产品','包括台式电脑和笔记本电脑及电脑配件')
INSERT INTO Types VALUES('03','家用电器','包括电视机、洗衣机、微波炉等')
INSERT INTO Types VALUES('04','服装服饰','包括服装产品和服饰商品')
INSERT INTO Types VALUES('05','日用商品','包括家庭生活中常用的商品')
INSERT INTO Types VALUES('06','运动用品','包括篮球、排球等运动器具')
INSERT INTO Types VALUES('07','礼品玩具','包括儿童、情侣、老人等礼品')
INSERT INTO Types VALUES('08','女性用品','包括女人用化妆品等女性用品')
INSERT INTO Types VALUES('09','文化用品','包括光盘、图书、文具等文化用品')
INSERT INTO Types VALUES('10','时尚用品','包括一些流行的商品')
GO

-- ==============================
-- 3.创建商品信息表Goods
-- ==============================
CREATE TABLE Goods
(
g_ID char(6), -- 商品编号
g_Name varchar(50), -- 商品名称
t_ID char(2), -- 商品的分类号（外键）
g_Price float, -- 商品价格
g_Discount float, -- 商品折扣
g_Number smallint, -- 库存数量
g_ProductionDate datetime, -- 商品生产日期
g_Image varchar(100), -- 商品图片
g_Status varchar(10), -- 商品状态 // 热点( 热点 )// 促销 (促销) // 推荐 ( 推荐 )
g_Description varchar(1000) -- 商品描述
)

INSERT INTO Goods VALUES('010001','诺基亚6500 Slide','01',1500,0.9,20,'2007-06-01','pImage/010001.gif','热点','彩屏,1600万色,TFT,240×320像素,2.2英寸')
INSERT INTO Goods VALUES('010002','三星SGH-P520','01',2500,0.9,10,'2007-07-01','pImage/010002.gif','推荐','彩屏,26万色,TFT,240×320像素,触摸屏,2.6英寸')
INSERT INTO Goods VALUES('010003','三星SGH-F210','01',3500,0.9,30,'2007-07-01','pImage/010003.gif','热点','彩屏,26万色,TFT,128×220像素,1.46英寸')
INSERT INTO Goods VALUES('010004','三星SGH-C17

8','01',3000,0.9,10,'2007-07-01','pImage/010004.gif','热点','彩屏,65536色,CSTN,128×128像素,内置摄像')
INSERT INTO Goods VALUES('010005','三星SGH-T509','01',2020,0.8,15,'2007-07-01','pImage/010005.gif','促销','彩屏,26万色,TFT,176×220像素,1.8英寸,GPRS')
INSERT INTO Goods VALUES('010006','三星SGH-C408','01',3400,0.8,10,'2007-07-01','pImage/010006.gif','促销','彩屏,65536色,CSTN,128×160像素,1.8英寸,GPRS Class 10')
INSERT INTO Goods VALUES('010007','摩托罗拉 W380','01',2300,0.9,20,'2007-07-01','pImage/010007.gif','热点','折叠,彩屏,65536色,TFT,128×160像素,1.8英寸')
INSERT INTO Goods VALUES('010008','飞利浦 292','01',3000,0.9,10,'2007-07-01','pImage/010008.gif','热点','直板,彩屏,26万色,TFT,176×220像素,2.0英寸,GPRS')
INSERT INTO Goods VALUES('020001','联想旭日410MC520','02',4680,0.8,18,'2007-06-01','pImage/020001.gif','促销','内存容量: 512M;硬盘容量: 80G;屏幕尺寸: 14.1寸')
INSERT INTO Goods VALUES('020002','联想天逸F30T2250','02',6680,0.8,18,'2007-06-01','pImage/020002.gif','促销','内存容量:512M/硬盘容量:80G/光驱类型:内置')
INSERT INTO Goods VALUES('030001','海尔电视机HE01','03',6680,0.8,10,'2007-06-01','pImage/030001.gif','促销','超大屏幕，超级视听享受')
INSERT INTO Goods VALUES('030002','海尔电冰箱HDFX01','03',2468,0.9,15,'2007-06-01','pImage/030002.gif','热点','安全节能王,最佳选择 ')
INSERT INTO Goods VALUES('030003','海尔电冰箱HEF02','03',2800,0.9,10,'2007-06-01','pImage/030003.gif','热点','家庭主妇的最爱')
INSERT INTO Goods VALUES('060001','红双喜牌兵乓球拍','06',46.8,0.8,45,'2007-06-01','pImage/060001.gif','促销','价廉物美,超值享受')
INSERT INTO Goods VALUES('040001','劲霸西服','04',1468,0.9,60,'2007-06-01','pImage/040001.gif','推荐','展现男人的魅力')

GO

-- ============================
-- 4.创建员工表 Employees
-- ============================
CREATE TABLE Employees
(
e_ID char(10), -- 员工编号
e_Name varchar(30), -- 员工姓名
e_Gender char(2), -- 性别
e_Birth datetime, -- 出生年月
e_Address varchar(100), -- 员工地址
e_Postcode char(6), -- 员工邮政编码
e_Mobile varchar(11), -- 员工手机号码
e_Phone varchar(15), -- 员工固定电话
e_Email varchar(50) -- 员工电子邮箱
)
INSERT Employees VALUES('E0001','张小路','男','1982-09-09','湖南株洲市','412000','133********','0733-*******','zhangxl@https://www.sodocs.net/doc/b710896059.html,')
INSERT Employees VALUES('E0002','李玉蓓','女','1978-06-12','湖南株洲市','412001','138********','0733-*******','liyp@https://www.sodocs.net/doc/b710896059.html,')
INSERT Employees VALUES('E0003','王忠海','男','1966-02-12','湖南株洲市','412000','139********','0733-*******','wangzhh@https://www.sodocs.net/doc/b710896059.html,')
INSERT Employees VALUES('E0004','赵光荣','男','1972-02-12','湖南株洲市','412000','136********','0733-*******','zhaogr@https://www.sodocs.net/doc/b710896059.html,')
INSERT Employees VALUES('E0005','刘丽

丽','女','1984-05-18','湖南株洲市','412002','139********','0733-*******','liulili@https://www.sodocs.net/doc/b710896059.html,')
GO

-- ================================
-- 5.创建支付方式表 Payments
-- ================================
CREATE TABLE Payments
(
p_Id char(2), -- 支付方式编号
p_Mode varchar(20), -- 支付方式
p_Remark varchar(100) -- 支付说明
)
INSERT INTO Payments VALUES('01','货到付款','货到之后再付款')
INSERT INTO Payments VALUES('02','网上支付','采用支付宝等方式')
INSERT INTO Payments VALUES('03','邮局汇款','通过邮局汇款方式')
INSERT INTO Payments VALUES('04','银行电汇','通过各商业银行电汇')
INSERT INTO Payments VALUES('05','其它方式','赠券等其它方式')

-- ============================
-- 6.创建订单表 Orders
-- ============================
CREATE TABLE Orders
(
o_ID char(14), -- 订单编号
c_ID char(5), -- 客户编号（外键）
o_Date datetime, -- 订货日期
o_Sum float, -- 订单金额
e_ID char(10), -- 员工编号（外键）
o_SendMode varchar(50), -- 送货方式
p_Id char(2), -- 支付方式（外键）
o_Status bit, -- 是否已派货
)
INSERT INTO Orders VALUES('200708011012','C0001','2007-08-01',1387.44,'E0001','送货上门','01',0)
INSERT INTO Orders VALUES('200708011430','C0001','2007-08-01',5498.64,'E0001','送货上门','01',1)
INSERT INTO Orders VALUES('200708011132','C0002','2007-08-01',2700,'E0003','送货上门','01',1)
INSERT INTO Orders VALUES('200708021850','C0003','2007-08-02',9222.64,'E0004','邮寄','03',0)
INSERT INTO Orders VALUES('200708021533','C0004','2007-08-02',2720,'E0003','送货上门','01',0)
INSERT INTO Orders VALUES('200708022045','C0005','2007-08-02',2720,'E0003','送货上门','01',0)
GO
-- ================================
-- 7.创建订单明细表 OrderDetails
-- ================================
CREATE TABLE OrderDetails
(
d_ID int IDENTITY(1,1), -- 编号
o_ID char(14), -- 订单编号（外键）
g_ID char(6), -- 商品编号（外键）
d_Price float, -- 购买价格
d_Number smallint -- 购买数量
)

INSERT INTO OrderDetails(o_Id,g_Id,d_price,d_number) VALUES('200708011012','010001',1350,1)
INSERT INTO OrderDetails(o_Id,g_Id,d_price,d_number) VALUES('200708011012','060001',37.44,1)
INSERT INTO OrderDetails(o_Id,g_Id,d_price,d_number) VALUES('200708011430','060001',37.44,1)
INSERT INTO OrderDetails(o_Id,g_Id,d_price,d_number) VALUES('200708011430','010007',2070,2)
INSERT INTO OrderDetails(o_Id,g_Id,d_price,d_number) VALUES('200708011430','040001',1321.2,1)
INSERT INTO OrderDetails(o_Id,g_Id,d_price,d_number) VALUES('200708011132','010008',2700,1)
INSERT INTO OrderDetails(o_Id,g_Id,d_price,d_number) VALUES('200708021850','030003',2520,1)
INSERT INTO OrderDetails(o_Id,g_Id,d_price,d_number) VALUES('200708021850','020002',5344,1)
INSERT INTO OrderDetails(o_Id,g_Id,d_price,d_number) VALUES('200708021850','040001

',1321.2,1)
INSERT INTO OrderDetails(o_Id,g_Id,d_price,d_number) VALUES('200708021850','060001',37.44,1)
INSERT INTO OrderDetails(o_Id,g_Id,d_price,d_number) VALUES('200708021533','010006',2720,1)
INSERT INTO OrderDetails(o_Id,g_Id,d_price,d_number) VALUES('200708022045','010006',2720,1)

GO
-- ================================
-- 8.创建用户表 Users
-- ================================
CREATE TABLE Users
(
u_ID varchar(10), -- 用户编号
u_Name varchar(30) , -- 用户名称
u_Type varchar(10), -- 用户类型
u_Password varchar(30) -- 用户密码
)
INSERT INTO Users VALUES('01','admin','超级','admin')
INSERT INTO Users VALUES('02','amy','超级','amy0414')
INSERT INTO Users VALUES('03','wangym','普通','wangym')
INSERT INTO Users VALUES('04','luogh','查询','luogh')