数据库 - 第三次实验

CREATE VIEW user_friend
AS  
SELECT u2.uid, u2.nickname, f1.note, f1.type  
FROM user u1 JOIN friend f1 ON u1.uid = f1.uid1  
             JOIN user u2 ON u2.uid = f1.uid2  
WHERE u1.uid = 1;

运行SELECT * FROM user_friend;结果：

![[Pasted image 20231114172756.png]]

SELECT u2.nickname,moment.content,moment.post_time
FROM user u1 JOIN friend f1 ON u1.uid = f1.uid1  
             JOIN user u2 ON u2.uid = f1.uid2  
			 JOIN moment ON moment.uid = u2.uid
WHERE u1.uid = 1 AND moment.type IN ('公开','仅好友可见')
ORDER BY moment.post_time DESC;

结果：

SELECT gname, content, sent_time  
FROM message JOIN group_send ON message.mid = group_send.mid  
             JOIN groups ON group_send.gid = groups.gid  
             JOIN joingroup ON joingroup.gid = groups.gid  
WHERE joingroup.uid = 1  
ORDER BY sent_time DESC;

结果：

SELECT uid_sender, content, sent_time  
FROM send NATURAL JOIN message
WHERE uid_receiver = 1 AND status = '待发送'  
ORDER BY uid_sender, sent_time DESC;

结果：

SELECT content, uid_sender  
FROM message NATURAL JOIN send  
WHERE content LIKE '%晚安%' AND  
      uid_receiver IN(1,6) AND
	  uid_sender IN(1,6) AND
      status = '已发送';

结果：

对于第四问：

CREATE INDEX idx_send ON send (uid_sender, uid_receiver);

快了不少。

对于第五问，添加索引：

CREATE INDEX idx_send ON send(uid_sender,uid_receiver);

效果立竿见影！

当数据库表中的数据量较大时，查询操作需要扫描整个表，这将导致查询速度变慢。 而通过创建索引，数据库系统可以直接在索引中查找相关数据，而不需要扫描整个表。

我们在(uid_sender, uid_receiver)这样一个列的组合，建立索引，send表经常访问，索引效率效果良好。

在第一问建立视图的基础之上，进行查询

SELECT count(*)
FROM user_friend JOIN joingroup ON user_friend.uid = joingroup.uid
WHERE gid = 1;

结果：

可以用勾股定理计算两个用户间的直线距离：设我们有两个用户，经纬度分别为(longitude1,latitude1)与(longtitude2,latitude2)，那么两个用户间距离即为

\[\sqrt{(longtitude1-longtitude2)^2+(latitude1-latitude2)^2} \]

题目好像有些问题，附近的人应该是平方小于100

SELECT u2.nickname, (u1.longitude - u2.longitude)*(u1.longitude - u2.longitude) + (u1.latitude - u2.latitude)*(u1.latitude - u2.latitude) AS distance_square  
FROM user u1 JOIN user u2 ON u1.uid <> u2.uid  
WHERE u1.uid = 1  
GROUP BY u2.nickname, distance_square  
HAVING distance_square < 100;

a) 将用户 0 与 1 之间互相发送过的信息的发送次数减一。

UPDATE message 
SET shared_count = shared_count - 1  
WHERE mid IN (SELECT mid FROM send
              WHERE uid_receiver IN(0,1) AND
			        uid_sender IN(0,1)
);

结果：

b) 删除 shared _ count 等于 0 的所有信息。

DELETE
FROM send
WHERE mid IN(SELECT mid FROM message
             WHERE shared_count = 0
);

DELETE
FROM group_send
WHERE mid IN(SELECT mid FROM message
             WHERE shared_count = 0
);

DELETE
FROM message
WHERE shared_count = 0;

c) 删除用户 0 与 用户 1 之间的信息发送记录。注意这里删除的是发送信息记录，而不是信息本身。

DELETE
FROM send
WHERE uid_receiver IN (0,1) AND uid_sender IN (0,1);

d) 删除用户 0 与用户 1 之间的好友记录 。

DELETE
FROM friend
WHERE uid1 IN (0,1) AND uid2 IN (0,1);

结果：