数据库 | 设计一个朋友圈

朋友圈的结构真的是很让我头疼——到底怎么在多层架构里实现，脚本怎么写；还有那个鬼虚拟机，kind of 学艺不精自我阉割的感觉，从头搞吧那就。

每个图片都有自己的URL：统一资源定位地址protocol :// hostname [:port] / path / [;parameters][?query]#fragment 。hostname用的就是DNS域名或IP地址，port可省。
CDN内容分发网络(Content Delivery Network)：我也不知道干嘛的。

我设计的朋友圈的基本结构：发布{内容，评论，点赞}，用户，时间线。

批处理的实现：
动态：发布动态

评论：

• 发布评论
• 删除评论

用户：

• 用户注册
• 用户注销
  视图：
• 用户查找好友时，是按照姓名来查找的，牵涉到同名的问题。

第三章内容 关系数据库标准语言

SQL 结构化查询语言Structured Query Language
特点：1. 综合定义DDL、操纵DML、控制DCL功能；2. 高度非过程化：存取路径由系统自动完成；3. 面向集合的操作：关系代数、集合代数。
体系结构 外模式–视图、基本表，模式–基本表，内模式–存储文件

数据类型
模式 模式和基本表的关系：搜索路径包含一组模式的列表，类似工作环境 。

方式一 CREATE SCHEMA *** AUTHORIZATION *** CASCADE/RESTRICT{} ;
方式二 CREATE TABLE ***.ss(***);

表 RDBMS将模式信息、完整性约束条件存入数据字典。
索引提高查询效率：RDBMS数据访问方式，基于属性值（而不是指针）的查询，通过索引实现逻辑层到物理层的映射。B+树：动态平衡/HASH索引：快。

视图 其实就是可以做选择操作的虚表，有什么用呢？1.简化用户操作2.安全（这里之后理解了再补）
执行：RDBMS将视图定义存入数据字典
1 行列子集视图
要求：单个表，保留主码
举例：（有点牵强哈）看看用户的地址分布，1.保证安全性，不去看密码2.使用CHECK OPTION只看有地址的用户，同时含主键。

CREATE VIEW users_loc(uid, unam,uloc) 
AS 
	SELECT uid,unam,uloc 
	FROM users
	WHERE uloc IS NOT NULL
	WITH CHECK OPTION;

嘿嘿没报错
2 带表达式视图
通过函数计算一些数值，这是一个很好的计算年龄的函数！

CREATE VIEW date_diff(unam,diff)
AS 
	SELECT unam,TIMESTAMPDIFF(YEAR,ubir,CURDATE())
	FROM users

于是乎写了一个可以查看“自己动态”的视图，就像pyq里点击自己头像的操作，正好和外模式对应上了！

CREATE VIEW my_moments 
AS 
	SELECT tmid 
	FROM timeline_of_00000000
	WHERE tsta = 0
	WITH CHECK OPTION;

3 分组视图
带聚集函数和GROUP BY。
[理解group by：按…分组，可以进行count/average等操作]
举例1：计算每个用户的朋友圈数量，降序排序。

CREATE VIEW total_moments
AS 
	SELECT muid,COUNT(*)  num    %这里num来命名计数
	FROM moments
	GROUP BY muid
	ORDER BY num DESC;

举例2：找重名的人。

CREATE VIEW same_name
AS 
	SELECT unam,COUNT(*)  snam   
	FROM users
	GROUP BY unam 
	HAVING snam > 1;

查看视图的语句，有两种：

show create view users_loc;
desc users_loc;

删除视图：

drop view users_loc;

以年龄为例，视图的使用可以为：

SELECT unam,diff FROM date_diff 
WHERE unam = 'Maeve';

第五章内容 数据库完整性

完整性干嘛用？ -正确反映显示数据
数据失真的因素？
-内因：1. 输入错误：手机号位长，身份证号格式，昵称重复； 2. 系统处理：内容覆写，没有设置检查用户的昵称能否重复；
-外因：3. 系统故障：可能是自己误删了某个表就没了； 4. 恶意破坏：盗号呗；
方法：1. 操作的权限管理；2. 内容的完整性审核；

完整性约束不说人话定义：对关系模式R，具体关系r$\in$R满足约束条件F( r )；范围：属性、元组、表、多表，作用范围越小，代价越小：。
 定义？-DDL定义语言
 检查？-针对改变数据库的操作insert…检查规则；考虑成本。
 违反处理？-拒绝NO ACTION，级联CASCADE，设为空值SET-NULL
 负责？-RDBMS对全表扫描自动建立索引。

分别看一下：（要用“完整性约束命名子句CONSTRAINT”便于修改，不妙的时MYSQL不支持哦）
1.实体完整性：定义列级PRIMARY KEY, NOT NULL；表级PRIMARY KEY(r0,r1)；违反拒绝主码值不唯一、属性值为空。
2.参照完整性：定义参照表FOREIGN KEY, 被参照表PREFERENCES对照的主码；违反的话三种都可以使用，默认拒绝；需自己定义外码是否为空值，例如想不到……就比如某个学生还没分配专业，专业号就为空。
3.用户定义完整性：UNIQUE, NOT NULL, CHECK；违反拒绝；

由以上，我们来初步建立表格：
考虑问题：1. 为了提高效率，需要给每个表都设置排序或索引字段吗？2. 随机生成？系统按序生成？3. 域和命名子句区别？4. 注册时选择地址怎么做？

提前声明一下各种报错：constraint和check都不能用；VARCHAR后面一定带数！男女性别的也没过；
约束条件语句 很不幸，MySql的CHECK会被忽略，CONSTRAINT会报错，所以考虑以下几种实现方式：

• 例如男女性别使用enum枚举类型；
• 使用触发器 TRIGGER（在NAVICAT中直接使用）

BEGIN
	IF NEW.ubir >= NOW()
	THEN
		DELETE FROM users WHERE ubir=new.ubir;
	END IF;
END

密码长度的约束

BEGIN
	IF length(NEW.upwd) < 6
	THEN
		DELETE FROM users WHERE upwd=new.upwd;
	END IF;
END

1 建库

CREATE DATABASE pyq DEFAULT CHARACTER SET=utf8mb4 COLLATE utf8mb4_unicode_ci;
USE pyq

2.1 用户表

表	用户	主键	外键	排序字段	索引字段
users	DBA	uid	/	uid	uid
属性名	类型	默认值	实体	参照	用户定义
uid	varchar(8)	系统按序生成	PRIMARY KEY	REFERENCES	/
unam	varchar(20)	wxid+系统生成	/	来自	NOT NULL
uloc	vachar(20)	/	/	/	设置选择
usex	char(2)	/	/	男，女，无
ubir	date	/	/	1900-01-01 to 2021-12-31
upwd	varchar	12345678	/	/	NOT NULL, len>=8

CREATE TABLE users
(
	uid VARCHAR(8) PRIMARY KEY,
	unam VARCHAR(20) NOT NULL,
	uloc VARCHAR(20),
	usex CHAR(2)
	CONSTRAINT csex CHECK(usex in ('女','男','无'),
	ubir DATE
	CONSTRAINT cbir CHECK(ubir BETWEEN 1900-01-01 00:00:00 AND 2021-12-31 23:59:59),
	upwd	VARCHAR NOT NULL
	CONSTRAINT cpwd CHECK(len(upwd)>=8),
	UNIQUE INDEX users_idx(uid ASC)
);

2.2 好友表

表	用户	主键	外键	排序字段	索引字段
friends	DBA	/	fid1,fid2	fid1	fid1
属性名	类型	默认值	实体	参照	用户定义
fid1	varchar(8)	/	/	外键，参照users.uid	NOT NULL
fid2	varchar(8)	/	/	外键，参照users.uid	NOT NULL

CREATE TABLE friends
(
	fid1 VARCHAR(8) NOT NULL,
	fid2 VARCHAR(8) NOT NULL,
	FOREIGN KEY(fid1) REFERENCES users(uid),
	FOREIGN KEY(fid2) REFERENCES users(uid),
	INDEX friends_idx(fid1)
);

这里不唯一可以使用索引吗？
2.3 动态表

表	用户	主键	外键	排序字段	索引字段
moments	DBA	mid	uid	mid	mid
属性名	类型	默认值	实体	参照	用户定义
mid	varchar(16)	系统按序生成	主键	/	/
muid	varchar(8)	/	/	外键，参照users.uid	不为空
mtim	datetime	/	/	/	不为空, 2010-01-01 00:00:00 to 2025-12-31 23:59:59
mcon	text	/	/	/	不为空
mpic	varchar	/	/	/	/

CREATE TABLE moments
(
	mid VARCHAR(16) PRIMARY KEY,
	muid VARCHAR(8) NOT NULL,
	mtim DATETIME NOT NULL
	CONSTRAINT ctim CHECK(mtim BETWEEN 2010-01-01 00:00:00 AND 2025-12-31 23:59:59),
	mcon TEXT NOT NULL,
	mpic VARCHAR,
	FOREIGN KEY(muid) REFERENCES users(uid),
	UNIQUE INDEX moments_idx(mid)
);

2.4 时间线表

表	用户	主键	外键	排序字段	索引字段
timeline_of_uid	DBA	/	mid	/	mid
属性名	类型	默认值	实体	参照	用户定义
tmid	varchar(16)	/	/	外键，参照moments.mid	NOT NULL, UNIQUE
tsta	tinyint	/	/	/	0是自己1是别人

CREATE TABLE timeline_of_00000000
(
	tmid VARCHAR(16) NOT NULL UNIQUE,
	tsta TINYINT NOT NULL
	CONSTRAINT csta CHECK(tsta in VALUES(0, 1)),
	FOREIGN KEY(tmid) REFERENCES moments(mid),
	UNIQUE INDEX timeline_idx(tmid)
);

2.5 评论表

表	用户	主键	外键	排序字段	索引字段
comments	DBA	cid	cmid	cid	cmid
属性名	类型	默认值	实体	参照	用户定义
cid	varchar(8)	系统按序生成	主键	/	/
cmid	varchar(16)	/	/	外键，参照moments.mid	NOT NULL
cuid	varchar(8)	/	/	外键，参照users.uid	NOT NULL
creuid	varchar(8)	/	/	外键，参照users.uid	/
ctim	datetime	/	/	/	不为空, 2010-01-01 00:00:00 to 2025-12-31 23:59:59
ccon	tinytext	/	/	/	不为空

CREATE TABLE comments
(
	cid VARCHAR(8) PRIMARY KEY,
	cmid VARCHAR(16) NOT NULL,
	cuid VARCHAR(8)  NOT NULL,
	creuid VARCHAR(8)  NOT NULL,
	ctim DATETIME NOT NULL
	CONSTRAINT tim CHECK(ctim BETWEEN 2010-01-01 00:00:00 AND 2025-12-31 23:59:59),
	ccon TINYTEXT NOT NULL,
	FOREIGN KEY(cmid) REFERENCES moments(mid),
	FOREIGN KEY(cuid) REFERENCES users(uid),
	FOREIGN KEY(creuid) REFERENCES users(uid),
	UNIQUE INDEX timeline_idx(cmid)
);

2.6 点赞表

表	用户	主键	外键	排序字段	索引字段
likes	DBA	/	lmid	/	lmid
属性名	类型	默认值	实体	参照	用户定义
lmid	varchar(16)	/	/	外键，参照moments.mid	NOT NULL
luid	varchar(8)	/	/	外键，参照users.uid	NOT NULL
ltim	datetime	/	/	/	不为空, 2010-01-01 00:00:00 to 2025-12-31 23:59:59

CREATE TABLE likes
(
	lmid VARCHAR(16) NOT NULL,
	luid VARCHAR(8)  NOT NULL,
	ltim DATETIME NOT NULL
	CONSTRAINT ctim CHECK(ltim BETWEEN 2010-01-01 00:00:00 AND 2025-12-31 23:59:59),
	FOREIGN KEY(lmid) REFERENCES moments(mid),
	FOREIGN KEY(luid) REFERENCES users(uid),
	UNIQUE INDEX likes(lmid)
);

第四章内容 数据库安全

1 最外层保护措施：用户标识->口令
2 存取控制->存取依据（数据字典中）
授权 GRANT 定义用户存取权限（数据对象，操作类型） ；发出者：DBA，创建者，有权限的用户；接受者：具体用户/PUBLIC。
回收 REVOKE 注意CASCADE
3 创建数据库用户，创建数据库角色（简化授权）

🚩实战：
首先创建用户（其实可以再授权时创建用户，直接加上密码什么的就可以）：引号都是要加的，host处写localhost、 ‘%(远程连接的通配符)、IP。
CREATE USER 'username'@'host' IDENTIFIED BY 'password';
注意mysql的密码策略，可以先用SHOW VARIABLES LIKE 'validate_password%';语句查看。这里要先修改一下，之后默认密码为8位：set global validate_password_policy=LOW;
创建几个用户：CEO，assistant，PUBLIC（默认）。
CEO掌握最高权力，拥有所有权限，并且可以把权限传播给别人。

GRANT ALL PRIVILIGES
ON users.*					% .* 和不加有什么区别？
TO ‘CEO’@‘%’
WITH GRANT OPTION;

撤回的自然就是：

REVOKE ALL PRIVILEGES
ON users.*
FROM 'CEO'

招一个助理来干手动添加数据的苦力活，只赋予属性列的权限，但是不能给ta删除数据的权限（没了怎么办！），还不能让它传播。

GRANT INSERT(unam)
ON users.*
TO assistant;

大众看别人的年龄信息吧，这里很不错可以使用视图。（但是怎么实现只修改自己的昵称呢？）

GRANT SELECT
ON date_diff
TO 'PUBLIC'@'%' 
IDENTIFIED BY '00000000';

查看所有用户的时候，首先要USE mysql;

select user,host,authentication_string from user;