65 lines
1.5 KiB
Plaintext
65 lines
1.5 KiB
Plaintext
CREATE TABLE chat_mysql.users (
|
|
username VARCHAR(50) PRIMARY KEY,
|
|
password VARCHAR(255) NOT NULL
|
|
);
|
|
SET SQL_SAFE_UPDATES = 0;
|
|
DELETE FROM chat_mysql.users;
|
|
SET SQL_SAFE_UPDATES = 1;
|
|
INSERT INTO chat_mysql.users (username, password)
|
|
VALUES
|
|
('admin', '123'),
|
|
('query', '123'),
|
|
('tom', '123'),
|
|
('jack', '123'),
|
|
('lucy', '123'),
|
|
('lily', '123');
|
|
select * from chat_mysql.users;
|
|
|
|
|
|
CREATE TABLE chat_mysql.contacts (
|
|
user_name VARCHAR(50) NOT NULL,
|
|
contact_name VARCHAR(50) NOT NULL,
|
|
|
|
PRIMARY KEY (user_name, contact_name),
|
|
|
|
FOREIGN KEY (user_name)
|
|
REFERENCES chat_mysql.users(username),
|
|
|
|
FOREIGN KEY (contact_name)
|
|
REFERENCES chat_mysql.users(username)
|
|
);
|
|
INSERT INTO chat_mysql.contacts (user_name, contact_name)
|
|
VALUES
|
|
('admin', 'query'),
|
|
('admin', 'tom'),
|
|
('admin', 'jack'),
|
|
('admin', 'lucy'),
|
|
('query', 'admin'),
|
|
('query', 'tom'),
|
|
('tom', 'admin'),
|
|
('tom', 'query'),
|
|
('admin', 'lily'),
|
|
('lily', 'admin');
|
|
select * from chat_mysql.contacts;
|
|
|
|
CREATE TABLE chat_mysql.messages (
|
|
id INT PRIMARY KEY AUTO_INCREMENT,
|
|
message_id VARCHAR(64) NOT NULL,
|
|
sender_name VARCHAR(50) NOT NULL,
|
|
receiver_name VARCHAR(50) NOT NULL,
|
|
content VARCHAR(1000) NOT NULL,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
FOREIGN KEY (sender_name)
|
|
REFERENCES chat_mysql.users(username),
|
|
|
|
FOREIGN KEY (receiver_name)
|
|
REFERENCES chat_mysql.users(username)
|
|
);
|
|
|
|
#删除某个人的发送聊天数据
|
|
SELECT *
|
|
FROM chat_mysql.messages
|
|
WHERE sender_name = 'test';
|
|
DELETE FROM chat_mysql.messages WHERE sender_name = 'test';
|