MySQL数据库基础练习系列：科研项目管理系统

DDL

CREATE TABLE Users (user_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '用户ID',username VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名',password VARCHAR(255) NOT NULL COMMENT '密码',gender ENUM('男', '女') NOT NULL COMMENT '性别',email VARCHAR(100) UNIQUE COMMENT '邮箱'
);CREATE TABLE Roles (role_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '角色ID',role_name VARCHAR(50) NOT NULL UNIQUE COMMENT '角色名称'
);CREATE TABLE UserRoles (user_id INT COMMENT '用户ID',role_id INT COMMENT '角色ID',PRIMARY KEY (user_id, role_id),FOREIGN KEY (user_id) REFERENCES Users(user_id),FOREIGN KEY (role_id) REFERENCES Roles(role_id)
);CREATE TABLE Projects (project_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '项目ID',project_name VARCHAR(100) NOT NULL COMMENT '项目名称',project_description TEXT COMMENT '项目描述',principal_investigator_id INT COMMENT '主研人ID',start_date DATE NOT NULL COMMENT '开始日期',end_date DATE NOT NULL COMMENT '结束日期',status ENUM('申请中', '审批中', '执行中', '结题') NOT NULL COMMENT '项目状态',FOREIGN KEY (principal_investigator_id) REFERENCES Users(user_id)
);CREATE TABLE Funds (fund_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '资金ID',project_id INT NOT NULL COMMENT '项目ID',source VARCHAR(100) NOT NULL COMMENT '资金来源',amount DECIMAL(10, 2) NOT NULL COMMENT '资金金额',FOREIGN KEY (project_id) REFERENCES Projects(project_id)
);CREATE TABLE Achievements (achievement_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '成果ID',project_id INT NOT NULL COMMENT '项目ID',achievement_name VARCHAR(100) NOT NULL COMMENT '成果名称',achievement_type ENUM('论文', '专利', '获奖', '其他') NOT NULL COMMENT '成果类型',description TEXT COMMENT '成果描述',FOREIGN KEY (project_id) REFERENCES Projects(project_id)
);CREATE TABLE ProjectLogs (log_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '日志ID',project_id INT NOT NULL COMMENT '项目ID',user_id INT NOT NULL COMMENT '用户ID',log_date DATETIME NOT NULL COMMENT '日志日期',log_content TEXT NOT NULL COMMENT '日志内容',FOREIGN KEY (project_id) REFERENCES Projects(project_id),FOREIGN KEY (user_id) REFERENCES Users(user_id)
);

DML

INSERT INTO Roles (role_name) VALUES
('管理员'),
('项目负责人'),
('项目成员');
INSERT INTO Users (username, password, gender, email) VALUES
('诸葛亮', '123', '男', '[e-mail]'),
('孙悟空', '123', '男', '[e-mail]'),
('林黛玉', '123', '女', '[e-mail]');
INSERT INTO UserRoles (user_id, role_id) VALUES
(1, 1), -- 诸葛亮是管理员
(2, 2), -- 孙悟空是项目负责人
(2, 3), -- 孙悟空也是项目成员
(3, 3);  -- 林黛玉是项目成员
INSERT INTO Projects (project_name, project_description, principal_investigator_id, start_date, end_date, status) VALUES
('三国历史研究项目', '研究三国历史背景', 1, '2023-01-01', '2023-12-31', '执行中'),
('西游记文化研究', '探究西游记的文学价值', 2, '2023-02-01', '2024-01-31', '申请中'),
('红楼梦解读', '分析红楼梦的深层含义', 2, '2023-03-01', '2023-11-30', '审批中');
INSERT INTO Funds (project_id, source, amount) VALUES
(1, '国家社会科学基金', 50000.00),
(2, '企业赞助', 30000.00),
(3, '学校科研基金', 45000.00),
(1, '地方政府资助', 20000.00); -- 同一个项目可以有多个经费来源
INSERT INTO Achievements (project_id, achievement_name, achievement_type, description) VALUES
(1, '三国历史研究报告', '论文', '详细分析了三国时期的历史事件'),
(2, '西游记文化解读', '论文', '深入探讨了西游记的文化内涵'),
(3, '红楼梦人物分析', '论文', '对红楼梦中的主要人物进行了深入剖析'),
(2, '西游记新发现', '专利', '发现了西游记中的新文学元素'); -- 同一个项目可以有多个成果
INSERT INTO ProjectLogs (project_id, user_id, log_date, log_content) VALUES
(1, 1, '2023-01-10 10:00:00', '项目启动会议召开'),
(2, 2, '2023-02-15 15:30:00', '提交项目申请书至学院'),
(3, 3, '2023-03-20 09:45:00', '开始收集红楼梦相关资料'),
(1, 1, '2023-04-01 14:15:00', '第一阶段研究成果汇报');

ER图 

 ER图

 模型图

简单查询

一、查询用户信息，仅显示用户的姓名与项目名称，用中文显示列名

SELECT DISTINCTu.username AS 用户名,p.project_name AS 项目名称
FROMUsers u
JOINProjects p ON u.user_id = p.principal_investigator_id;

 

二、根据项目名称进行模糊查询，模糊查询要进行索引，需要给出explain语句

EXPLAIN SELECT project_id, project_name
FROM Projects
WHERE project_name LIKE '%三国%';

 

三、统计用户的项目信息，查询所有用户的项目数量，并进行倒序排列

SELECT u.username AS 用户名,COUNT(p.project_id) AS 项目数量
FROM Users u
LEFT JOIN Projects p ON u.user_id = p.principal_investigator_id
GROUP BY u.user_id, u.username
ORDER BY 项目数量 DESC;

 

复杂查询

一、查询用户的基本信息，项目信息

SELECT u.user_id,u.username,u.gender,u.email,p.project_id,p.project_name,p.project_description,p.start_date,p.end_date,p.status
FROM Users u
LEFT JOIN Projects p ON u.user_id = p.principal_investigator_id;

 

二、查看项目中项目阶段最多的项目对应的类型

SELECT p.project_name,p.status
FROM Projects p
WHERE (SELECT COUNT(*) FROM Projects p2 WHERE p2.status = p.status) = (SELECT MAX(cnt) FROM (SELECT status, COUNT(*) as cnt FROM Projects GROUP BY status) as subquery);

 

三、查询项目最多的用户，并且查询用户的全部信息与当前项目阶段

SET @MostProjectsUserId = (SELECT principal_investigator_idFROM ProjectsGROUP BY principal_investigator_idORDER BY COUNT(*) DESCLIMIT 1
);SELECT u.*,p.project_id,p.project_name,p.status AS current_project_status
FROM Users u
JOIN Projects p ON u.user_id = p.principal_investigator_id
WHERE u.user_id = @MostProjectsUserId;

 

触发器

触发器一：确保在Projects表中插入新项目时，主研人必须是已存在的用户

DELIMITER //
CREATE TRIGGER trg_check_principal_investigator
BEFORE INSERT ON Projects
FOR EACH ROW
BEGINIF NOT EXISTS (SELECT 1 FROM Users WHERE user_id = NEW.principal_investigator_id) THENSIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'The principal investigator does not exist.';END IF;
END //
DELIMITER ;-- 测试语句
INSERT INTO Projects (project_name, project_description, principal_investigator_id, start_date, end_date, status)
VALUES ('新项目测试', '测试项目描述', 999, '2023-05-01', '2023-12-31', '申请中'); -- 假设999是一个不存在的用户ID-- 预期会失败，因为用户ID 999 不存在-- 成功的测试语句
INSERT INTO Projects (project_name, project_description, principal_investigator_id, start_date, end_date, status)
VALUES ('新项目测试成功', '测试项目描述成功', 2, '2023-05-01', '2023-12-31', '申请中'); -- 假设用户ID 2 是存在的（孙悟空）-- 检查新项目是否成功插入
SELECT * FROM Projects WHERE project_name = '新项目测试成功';

 

触发器二：在插入新的项目日志时，自动设置日志日期为当前时间（如果未提供）

DELIMITER //
CREATE TRIGGER trg_set_log_date_if_null
BEFORE INSERT ON ProjectLogs
FOR EACH ROW
BEGINIF NEW.log_date IS NULL THENSET NEW.log_date = NOW();END IF;
END //
DELIMITER ;-- 测试语句
INSERT INTO ProjectLogs (project_id, user_id, log_content) VALUES
(2, 2, '项目申请已被接收');-- 检查触发器是否工作
SELECT * FROM ProjectLogs WHERE log_content = '项目申请已被接收';

 

触发器三：在更新项目状态时，检查状态是否合法（此触发器实际上已经确保了状态的合法性，因为status列是一个ENUM类型，所以这里仅作为示例）

DELIMITER //
CREATE TRIGGER trg_check_project_status_update
BEFORE UPDATE ON Projects
FOR EACH ROW
BEGINIF NEW.status NOT IN ('申请中', '审批中', '执行中', '结题') THENSIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid project status!';END IF;
END //
DELIMITER ;-- 测试语句（确保成功）
UPDATE Projects SET status = '审批中' WHERE project_id = 2; -- 将西游记文化研究的状态更改为审批中，应该成功-- 检查Projects表，确保状态已更新
SELECT * FROM Projects WHERE project_id = 2;

 

存储过程 

存储过程 1: 添加新用户

DELIMITER //
CREATE PROCEDURE AddUser(IN p_username VARCHAR(50), IN p_password VARCHAR(255), IN p_gender ENUM('男', '女'), IN p_email VARCHAR(100))
BEGININSERT INTO Users (username, password, gender, email)VALUES (p_username, p_password, p_gender, p_email);
END //
DELIMITER ;-- 测试语句
CALL AddUser('刘备', '123', '男', '[e-mail]');-- 验证新用户是否添加成功
SELECT * FROM Users WHERE username = '刘备';

 

存储过程 2: 分配用户角色

DELIMITER //
CREATE PROCEDURE AssignUserRole(IN p_user_id INT, IN p_role_id INT)
BEGININSERT INTO UserRoles (user_id, role_id)VALUES (p_user_id, p_role_id);
END //
DELIMITER ;-- 测试语句
CALL AssignUserRole(3, 2); -- 假设林黛玉（user_id=3）需要被分配为项目负责人（role_id=2）-- 验证角色是否分配成功
SELECT * FROM UserRoles WHERE user_id = 3;

 

存储过程 3: 记录项目日志

DELIMITER //
CREATE PROCEDURE RecordProjectLog(IN p_project_id INT, IN p_user_id INT, IN p_log_content TEXT)
BEGININSERT INTO ProjectLogs (project_id, user_id, log_date, log_content)VALUES (p_project_id, p_user_id, NOW(), p_log_content);
END //
DELIMITER ;-- 测试语句
CALL RecordProjectLog(1, 2, '项目有新的研究进展'); -- 假设为三国历史研究项目（project_id=1）添加日志，由孙悟空（user_id=2）记录-- 验证日志是否记录成功
SELECT * FROM ProjectLogs WHERE project_id = 1 AND log_content LIKE '%项目有新的研究进展%';