数据库技术提升-MySQL数据库原理、设计与应用【2.0】

4.4.7 用户表

        由于网站分为前后台,这里所说的用户是指前台用户,也就是网站的访客通过注册账号成为网站的用户。一般来说,数据库中的用户表包含两类信息,一类是登录信息,即账号和密码,账号通常是编号、用户名,邮箱或手机号等具有唯一性的值;另一类是用户的个人信息,如姓名、性别、出生年月、所在地等。
接下来通过表 4-23 展示用户表的具体结构。

下面根据表 4-23 所示的表结构,完成用户表的创建,对应的 SQL,语句如下
CREATE TABLE sh user(
id INT UNSIGNED PRIMARY KEY AUTO INCREMENT COMMENT '用户 id'name VARCHAR(100)NOT NULL UNIQUE DEFAULT ''COMMENT '用户名'password VARCHAR(255)NOT NULL DEFAULT"COMMENT '密码'Salt CHAR(32)NOT NULL DEFAULT"COMMENT'密码盐'email VARCHAR(128)NOT NULL DEFAULT ''COMMENT '邮箱'mobile CHAR (11)NOT NULL DEFAULT"COMMENT 手机号'level TINYINT UNSIGNED NOT NULL DEEAULT 0 COMMENT'用户级别'money DECIMAL(10，2)UNSIGNED NOT NULL DEFAULT 0 COMMENT'金额'Gender TINYINT UNSIGNED NOT NULL DEFAULT O COMMENT 性别'G9 VARCHAR (20)NOT NULL DEFAULT ''COMMENT 'Q?',
is active TINYINT UNSIGNED NOT NULL DEEAULT 0 COMMENT'是否激活'
reg time DATETIME DEFAULT NULL COMMENT'注册时间'Create time DATETIME NOT DULL DEFAULT CURRENT TIMESTAMP COMMENT '创建时间'update time DATETIME DEFAULT NULL COMMENT '更新时间')ENGINE-InnODB DEFAULT CHARSET-utf8;

需要注意的是,考虑到 Web 项目的安全性,应对用户的密码进行加密,不能明文存储否则一旦发生数据泄露,将会给网站带来极大的损失。密码的加密方式有很多种,比较常见的是 MD5 加盐算法,下面通过具体 SQL 语句进行演示。
 

mySql>SELECT MD5 (CONCAT (MD5 ('password'),'salt'));
IMD5 (CONCAT (MD5('password'),'salt'))
一F
Id514dee5e76bbb718084294c835f312c
十一-:----十
1 row in set(0.00 sec)

        在上述 SQL 语句中,MD5('password')表示使用 MD5()函数对 password 字符串进行MD5(消息摘要算法第五版)运算,运算的结果是一个字符串,由 32个字符构成。CONCATO)函数用于字符串拼接,此处是在 MD5('password")运算获得的字符串基础上拼接了 salt 字符串。最后,再次使用 MD5()函数对拼接结果进行一次 MD5 运算。
        MD5算法具有不可逆性,通过计算后的结果将无法还原成原文,而对于完全相同的内容,其计算后的结果也是相同的,因此 MD5算法经常用于密码的存储。但这种方式也有缺点,如果将世界上所有的密码与计算结果全部存储起来进行检索,则密码就会被逆向查找出来。为了解决这个问题,通常会为每个用户生成一个 salt(盐),用于在对密码进行 MD5 计算时加人 salt,以提高密码加密的复杂度,使密码更不容易被破解。
        用户表创建完成后,为用户表添加测试数据,具体 SQL 语句如下

INSERT INTo sh user (id, name, password, salt,money,is active) VALUES
(1,'Alex',MD5 (CONCAT(MD5('123'),'salt1'))，'salt1',1000,1),
(2,'Bi11',MD5(CONCAT (MD5('123')，'salt2'))，'salt2'，1000，1);

        在上述测试数据中,password 字段对密码进行了加密,其中 123 是密码原文,Alex 和Bill 的密码相同,但 salt不同,所以密码的加密结果也不同。在实际开发中,读者可以借助-些函数生成一个随机字符串,作为 salt 使用,如 MD5(RAND())接下来查看 sh user 表中的记录,观察密码加密结果,如下所示。

4.4.8 评论表

当用户购买商品后,可以发表评论。对于已经发表的评论,可以进行追加。商品评论表的具体结构如表 4-24 所示。

在表 4-24 中,is_staff 字段表示该评论是否为网站的后台用户(即工作人员)发表的,用于工作人员回复用户的评论、解答疑问等。
下面根据表 4-24 的结构,创建商品评论表,对应的 SQL语句如下

CREATE TABLE sh goods comment(
id INT UNSIGNED PRIMARY KEY AUTO INCREMENT COMMENT "评论 id'
parent id INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '上级评论 id',
user id IN'T UNSIGNED NOT NULL DEFAULT 0 COMMENT '用户 id',
goods id INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '商品 id',
Content TEXTNOTULLOMMENT评论内容iS Staff TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT'是否为工作人员'
is shOW TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT'是否显示'
iS del TINYINT UNSIGNED NOT NULL DEFAULT O COMMENT '是否删除'.
Create time DATETIME NOT NULL DEFAULT CURRENT TIMESTAMP COMMENT '创建时间',
update time DATETIME DEFAULT NULL COMMENT 更新时间'
)ENGINE= InDODB DEFAULT CHARSE'T=utf8;
数据表创建完成后,为商品评论表添加测试数据,具体 SQL,语句如下
INSERT INTo 'sh goods coment*(id, user id, goods id, content,
is show,create time)VALUES
(1,1,8，"好'，0，'2017-11-08 00:00:00'),
(2，2，10，'不错'，1,'2017-12-03 00:00:00')
(3,3,9，'满意'，1,'2017-12-30 00:00:00')，
(4，4，4，'携带方便'，1,'2018-01-19 00:00:00')
(5，4,7，'中低音效果特别棒'，1,'2018-01-19 00:00:00'),
(6,5,8，'卡机'，1,'2018-01-22 00:00:00')，
(7，6，5，'黑夜拍照也清晰'，1,'2018-02-15 00:00:00')
(8,7,9，'掉色、有线头'，0，'2018-03-03 00:00:00')，
(9，4，9，"还行"，1,'2018-04-05 00:00:00")，
(10，8，9，"特别彰显气质'，1,'2018-04-16 00:00:00');

4.5 动手实践:商品购物流程设计

        数据库的学习在于多看、多学、多想、多动手,只有将理论与实际相结合,才能够体现出数据库开发与管理的重要性,展现知识学习的价值与力量。接下来请结合本章所学的知识完成电子商务网站中的商品购物流程的数据库设计。
【实践目标】
此实践的目标是能够根据文字提示,完成商品购物流程的数据库设计
【实践需求】
用户在电子商务网站中购买商品时,用户将想要购买的商品添加到购物车,填写收货地址,然后下订单,等待收货。在收到货以后,可以对商品进行打分评价。请动手实现购物车。收货地址、订单、商品评分的数据表设计。
手动实践
1.购物车
用户可以将想要购买的商品添加到购物车,从而方便一次购买多件商品。将商品添加到购物车后不会影响商品的库存。接下来通过表 4-25展示购物车表的结构。

在表 4-25 中,goods_price 字段表示商品加入到购物车时的单价,并不一定是商品的最新价格,这是因为商品的价格有可能会变化,当变化时,购物车会显示商品的最新价格,并根据 goods_price保存的价格计算该商品的价格浮动变化,以提醒用户该商品已经涨价或降价。
下面根据表 4-25 的结构,创建购物车表,对应的 SOL语句如下

CREATE TABLE sh user shopcart(id INT UNSIGNED PRIMARY KEY AUTO INCREMENT COMMENT '购物车 id'
user id INT UNSIGNED NOT NULL, DEFAULT O COMMENT '用户 id'.
goods id INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '商品 id',
Goods price DECIMAL (10,2)UNSIGNED NOT NULL DEFAULT 0 COMMENT'单价'
Goods nU INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '购买件数'.
is seleCt TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否选中'
create time DATETIME NOT NULL DEFAULT CURRENT TIMESTAMP COMMENT '创建时间'
update time DATETIME DEFAULT NULL COMMENT 更新时间'
ENGINE,- InnODB DEFAULT CHARSET-utf8;

2.收货地址
当用户决定下订单前,需要选择收货地址。一个用户可以有多个收货地址,并根据实际情况选择某一个作为默认地址。接下来通过表 4-26展示收货地址表的结构。

下面根据表 4-26 的结构,创建收货地址表,对应的 SQL,语句如下

CREATE TABLE sh user address(id INT UNSIGNED PRIMARY KEY AUTO INCREMENT COMMENT '地t id'
user id INT UNSIGNED NOT NULL DEFAULT O COMMENT '用户 id',
is default TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否默认',
provinCe VARCHAR(20)NOT NULL DEFAULT"COMMENT '省'
City VARCHAR(20)NOT NULL DEFAULT"COMMENT'district VARCHAR (20)NOT NULL DEFAULT "'COMMENT 'K",addresS VARCHAR(255)NOT NULL DEFAULT ''COMMENT '具体地址'
zip VARCHAR(20)NOT NULL DEFAULT"COMMENT'邮编'Consignee VARCHAR (20)NOT NULL DEFAULT COMMENT收件人'
phone VARCHAR(20)NOT NULL DEFAULT'COMMENT'联系电话'
Create time DATETIME NOT NULL, DEFAULT CURRENT TIMESTAMP COMMENT '创建时间'.
update time DATETIME DEFAULT NULL COMMENT 更新时间'
)ENGINE-InnODB DEFAULT CHARSET-utf8;

3.订单
当用户确定购买一件或多件商品后,就可以下订单并进行支付。订单表的具体结构如表 4-27 所示。

        在表 4-27 中,“是否有效”用于在订单创建后由工作人员进行确认;“是否取消"用于取消订单:“是否付款”表示用户是否付款:“物流状态"的值有3种,0表示未发货,1表示已发货,2表示用户确认收货:“是否删除"值为0表示不删除,1表示删除到回收站,2表示从回收站中删除。每当订单的状态(包括订单创建,工作人员确认,取消订单,付款,发货,确认收货)发生变化时,就需要记录日志,日志中保存操作类型,时间、日志信息,以及用户或工作人员的留言(如取消订单的理由)等信息。
        由于一个订单可以包含多件商品,因此还需要通过订单商品表保存订单中的商品,具体结构如表 4-28 所。

4.商品评分
当用户购买商品后,可以对商品进行评分,共有1~5分可选。商品评分表的具体结构
如氚茫犧徘卒咻 4-29 所示。

在表4-29中,“是否无效”用于商家遇到恶意差评时,工作人员可以将评分设为无效,无效的评分将不会参与到商品的平均评分计算中。
下面根据表 4-29 的结构,创建商品评分表,对应的 SQ1. 语句如下

CREATE TABLE sh goods score(id INT UNSIGNED PRIMARY KEY AUTO INCREMENT COMMENT '评分id',
user id INT UNSIGNED NOT NULL, DEFAULT O COMMENT 'H户 id'gOOds id INT UNSIGNED NOT NULL DEFAULT 0 COMMENT "商品h id',
GOOdS SCOre TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '商品评分',
SerVice SCOre TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT'服务评分',
expreSS ScOre TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT'物流评分',
is invalid TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT'是否无效',
Create time DATETIME NOT NULL DEFAULT CURRENT TIMESTAMP COMMENT'评分时间',
ENGINE= INNODB DEFAULT CHARSET-utf8,

4.6 本章小结

        本章主要讲解了数据库设计的基本理论和具体实战,读者应掌握数据库设计的基本流程、数据建模工具的使用,并深人理解数据库范式的作用和局限。通过本章的学习,读者应具备分析用户需求,设计合理,规范的数据库的能力。