MySQL-新闻管理系统实战、AES加密调用-学习笔记10

1. 介绍

数据库属于操作持久层的那一部分，包括MySQL、Redis、MongoDB等。在所有的项目中都是离不开数据库的。

• 以编辑的身份简介系统
  1. 首先需要账户密码才能登录编辑或审批账户。
  2. 一旦登录会询问你需要做什么操作
     • 新闻列表：新闻比较多需要用LIMIT语句分页查询
     • 新建新闻：定义新闻标题、内容、是否置顶显示等等，但是需要审批才能发表
     • 编辑新闻：需要审批后，撤下新闻，编辑才能重新编辑，再重新给管理员审批后发表
     • 退出：退出系统
• 用户体系
  • 普通用户不需要登录就可以阅读
  • 编辑指向用户，指编辑继承了用户的功能，也就是阅读。同样的道理，管理员也指向用户

• 新闻属性：用到了四张表
  

2. 创建逻辑库和数据表

2.1 介绍

• t_role：用户、编辑、管理员等等
• t_type：新闻类型
• t_user：用户信息
  • password为500是因为加密了密码
• t_news：新闻
• 🔑：代表主键，⚫：代表唯一性

t_role	t_type	t_user	t_news	内容表
🔑 id: int UNSIGNED	🔑 id: int UNSIGNED	🔑 id: int UNSIGNED	🔑 id: int UNSIGNED	MongoDB那里创建
⚫ role: varchar(20)	⚫ type: varchar(20)}	⚫ username: varchar(20)	title: varchar(40)
		password: varchar(500)	⚫ editor_id:int UNSIGNED
		email: varchar(100)	⚫ type_id: int UNSIGNED
		role_id: int UNSIGNED	content_id: char(12)
			⚫ is_top: tinyint UNSIGNED
			create_time: timestamp(0)
			update_time: timestamp(0)
			⚫ state: enum

2.2 数据加密

• 对称加密：加密和解密是一样的，速度快但强度低，多用于文件加密，AES较DES相比更加安全
  • 加密：SELECT AES_ENCRYPT(原始数据, 密钥字符串);，返回会是乱码，因此要转成十六进制SELECT HEX(AES_ENCRYPT(原始数据, 密钥字符串));
  • 解密：AES_DECRYPT(加密结果, 密钥字符串)
  • 举个例子：

SELECT HEX(AES_ENCRYPT("Hello","123456")); # -> 80BD7E27815D64910103A273906BE5BC
SELECT AES_DECRYPT(UNHEX("80BD7E27815D64910103A273906BE5BC"), "123456");

• 非对称加密：分公钥解密和私钥加密

2.3 创建数据表

CREATE DATABASE vega;
USE vega;
#创建t_type
CREATE TABLE t_type(
	id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
	type VARCHAR(20) NOT NULL UNIQUE
);

#添加VALUE
INSERT INTO t_type(type) VALUE ("要闻"), ("体育"), ("科技"), ("娱乐"), ("历史");

#创建t_role
CREATE TABLE t_role(
	id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
	role VARCHAR(20) NOT NULL UNIQUE
);

INSERT INTO t_role(id, role) VALUE (1, "管理员"), (2, "新闻编辑");

#创建t_user
CREATE TABLE t_user(
	id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
	username VARCHAR(20) NOT NULL UNIQUE,
	password VARCHAR(500) NOT NULL,
	email VARCHAR(100) NOT NULL,
	role_id INT UNSIGNED NOT NULL,
	
	INDEX(username)# 对user name加索引
);

INSERT INTO t_user(username, password, email, role_id)
VALUES 
	("admin", HEX(AES_ENCRYPT("123456", "miyao")), "admin@123.com", 1),
	("scott", HEX(AES_ENCRYPT("234567", "miyao")), "scott@123.com", 2);

#创建t_news
CREATE TABLE t_news(
	id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
	title VARCHAR(40) NOT NULL,
	editor_id INT UNSIGNED NOT NULL,
	type_id INT UNSIGNED NOT NULL,
	content_id CHAR(12) NOT NULL, #在MongoDB中主键是固定的，12个字符
	is_top TINYINT UNSIGNED NOT NULL,
	create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, # 默认当前时间
	update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	state ENUM("草稿", "待审批", "已审批", "隐蔽") NOT NULL,

	INDEX(editor_id),
	INDEX(type_id),
	INDEX(state),
	INDEX(create_time),
	INDEX(is_top)
);