帮我设计一个实时通讯软件的数据库附带sql语句
时间: 2024-01-13 14:04:46 浏览: 27
以下是一个简单的实时通讯软件的数据库设计和相应的SQL语句:
1. 用户表
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50) NOT NULL,
password VARCHAR(50) NOT NULL,
email VARCHAR(50) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
2. 好友关系表
CREATE TABLE friendships (
id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT NOT NULL,
friend_id INT NOT NULL,
status ENUM('pending', 'accepted', 'rejected') DEFAULT 'pending',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id),
FOREIGN KEY (friend_id) REFERENCES users(id)
);
3. 聊天室表
CREATE TABLE chat_rooms (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
created_by INT NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (created_by) REFERENCES users(id)
);
4. 聊天记录表
CREATE TABLE chat_messages (
id INT PRIMARY KEY AUTO_INCREMENT,
room_id INT NOT NULL,
sender_id INT NOT NULL,
message TEXT NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (room_id) REFERENCES chat_rooms(id),
FOREIGN KEY (sender_id) REFERENCES users(id)
);
5. 好友申请表
CREATE TABLE friend_requests (
id INT PRIMARY KEY AUTO_INCREMENT,
from_user_id INT NOT NULL,
to_user_id INT NOT NULL,
message TEXT,
status ENUM('pending', 'accepted', 'rejected') DEFAULT 'pending',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (from_user_id) REFERENCES users(id),
FOREIGN KEY (to_user_id) REFERENCES users(id)
);
6. SQL查询样例:
-- 查询所有聊天记录
SELECT * FROM chat_messages;
-- 查询某个聊天室的聊天记录
SELECT * FROM chat_messages WHERE room_id = 1;
-- 查询某个用户的好友列表
SELECT users.username FROM users
JOIN friendships ON users.id = friendships.friend_id
WHERE friendships.user_id = 1 AND friendships.status = 'accepted';
-- 查询某个用户的未处理好友申请
SELECT friend_requests.*, users.username FROM friend_requests
JOIN users ON friend_requests.from_user_id = users.id
WHERE friend_requests.to_user_id = 1 AND friend_requests.status = 'pending';
-- 插入一条聊天记录
INSERT INTO chat_messages (room_id, sender_id, message)
VALUES (1, 1, 'Hello, world!');
-- 更新一条好友申请的状态
UPDATE friend_requests SET status = 'accepted' WHERE id = 1;
-- 删除一条好友关系
DELETE FROM friendships WHERE id = 1;
相关推荐
![pdf](https://img-home.csdnimg.cn/images/20210720083512.png)
![pdf](https://img-home.csdnimg.cn/images/20210720083512.png)
![pdf](https://img-home.csdnimg.cn/images/20210720083512.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)