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', '男', 'zhugeliang@example.com'),
('孙悟空', '123', '男', 'sunwukong@example.com'),
('林黛玉', '123', '女', 'lindaiyu@example.com');
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 DISTINCT
    u.username AS 用户名,
    p.project_name AS 项目名称
FROM
    Users u
JOIN
    Projects 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_id
    FROM Projects
    GROUP BY principal_investigator_id
    ORDER BY COUNT(*) DESC
    LIMIT 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;

触发器

触发器一：项目状态更新时记录日志

DELIMITER $$
CREATE TRIGGER trg_after_project_status_update
AFTER UPDATE ON Projects
FOR EACH ROW
BEGIN
    IF NEW.status <> OLD.status THEN
        INSERT INTO ProjectLogs (project_id, user_id, log_date, log_content)
        VALUES (NEW.project_id, @CURRENT_USER_ID, NOW(), '项目状态已更新');
    END IF;
END;
$$
DELIMITER ;

触发器二：用户角色变更时记录日志

DELIMITER $$
CREATE TRIGGER trg_after_fund_insert
AFTER INSERT ON Funds
FOR EACH ROW
BEGIN
    INSERT INTO ProjectLogs (project_id, user_id, log_date, log_content)
    VALUES (NEW.project_id, @CURRENT_USER_ID, NOW(), CONCAT('项目获得资金：', NEW.source, '，金额为：', NEW.amount));
END;
$$
DELIMITER ;

触发器三：用户角色变更时记录日志

CREATE TABLE UserRoleLogs (
    log_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '日志ID',
    user_id INT NOT NULL COMMENT '用户ID',
    old_role_id INT COMMENT '旧角色ID',
    new_role_id INT COMMENT '新角色ID',
    log_date DATETIME NOT NULL COMMENT '日志日期',
    log_content TEXT COMMENT '日志内容',
    FOREIGN KEY (user_id) REFERENCES Users(user_id),
    FOREIGN KEY (old_role_id) REFERENCES Roles(role_id),
    FOREIGN KEY (new_role_id) REFERENCES Roles(role_id)
);

DELIMITER $$
CREATE TRIGGER trg_after_user_role_change
AFTER INSERT ON UserRoles
FOR EACH ROW
BEGIN
    DECLARE old_role_name VARCHAR(50);
    DECLARE new_role_name VARCHAR(50);
    SELECT role_name INTO new_role_name FROM Roles WHERE role_id = NEW.role_id;
    IF new_role_name IS NOT NULL THEN
        INSERT INTO UserRoleLogs (user_id, new_role_id, log_date, log_content)
        VALUES (NEW.user_id, NEW.role_id, NOW(), CONCAT('用户', NEW.user_id, '被赋予了新角色：', new_role_name));
    END IF;
END;
$$
DELIMITER ;

存储过程 

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

DELIMITER $$
CREATE PROCEDURE AssignUserRole(IN userId INT, IN roleName VARCHAR(50))
BEGIN
    DECLARE roleId INT;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET @errorMsg = 'Role not found.';
    SELECT role_id INTO roleId FROM Roles WHERE role_name = roleName;
    IF roleId IS NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = @errorMsg;
    END IF;
    IF NOT EXISTS (SELECT 1 FROM UserRoles WHERE user_id = userId AND role_id = roleId) THEN
        INSERT INTO UserRoles (user_id, role_id) VALUES (userId, roleId);
    END IF;
    SELECT 'Role assigned successfully.' AS message;
END $$
DELIMITER ;

存储过程 2: 更新项目状态并记录日志

DELIMITER $$
CREATE PROCEDURE UpdateProjectStatus(IN projectId INT, IN newStatus ENUM('申请中', '审批中', '执行中', '结题'))
BEGIN
    DECLARE OLD_STATUS ENUM('申请中', '审批中', '执行中', '结题');
    SELECT status INTO OLD_STATUS FROM Projects WHERE project_id = projectId;
    UPDATE Projects SET status = newStatus WHERE project_id = projectId;
    INSERT INTO ProjectLogs (project_id, user_id, log_date, log_content)
    VALUES (projectId, USER(), NOW(), CONCAT('Project status changed from ', OLD_STATUS, ' to ', newStatus));
    SELECT 'Project status updated successfully.' AS message;
END $$
DELIMITER ;

存储过程 3: 分配项目资金并检查预算

ALTER TABLE Projects ADD total_budget DECIMAL(10, 2) NOT NULL DEFAULT 0 COMMENT '项目总预算';
DELIMITER $$
CREATE PROCEDURE AllocateProjectFunds(
    IN p_project_id INT,
    IN p_source VARCHAR(100),
    IN p_amount DECIMAL(10, 2)
)
BEGIN
    DECLARE v_total_budget DECIMAL(10, 2);
    DECLARE v_allocated_funds DECIMAL(10, 2);
    SELECT total_budget INTO v_total_budget FROM Projects WHERE project_id = p_project_id;
    SELECT SUM(amount) INTO v_allocated_funds FROM Funds WHERE project_id = p_project_id;
    IF v_total_budget >= (v_allocated_funds + p_amount) THEN
        INSERT INTO Funds (project_id, source, amount) VALUES (p_project_id, p_source, p_amount);
        INSERT INTO ProjectLogs (project_id, user_id, log_date, log_content)
        VALUES (p_project_id, USER(), NOW(),CONCAT('资金已成功分配给项目', p_project_id, '，金额：', p_amount, '，来源：', p_source));
        SELECT '资金分配成功' AS message;
    ELSE
        SELECT '预算不足，无法分配资金' AS message;
    END IF;
END $$
DELIMITER ;

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：/a/748629.html

如若内容造成侵权/违法违规/事实不符，请联系我们进行投诉反馈qq邮箱809451989@qq.com，一经查实，立即删除！

相关文章

@PathVariable注解的使用及源码解析

@PathVariable注解的使用及源码解析

前言 PathVariable 注解是我们进行JavaEE开发&#xff0c;最常见的几个注解之一&#xff0c;这篇博文我们以案例和源码相结合&#xff0c;帮助大家更好的了解PathVariable 注解 使用案例 1.获取 URL 上的值 RequestMapping("/id/{id}") public Object getId(Path…
阅读更多...
Kubernetes Artemis系列 | 使用 ArtemisCloud Operator 部署 artemis

Kubernetes Artemis系列 | 使用 ArtemisCloud Operator 部署 artemis

目录 一、ArtemisCloud Operator 介绍二、部署ArtemisCloud Operator三、使用 ArtemisCloud Operator 部署 artemis四、管理队列五、缩减规模时消息迁移 一、ArtemisCloud Operator 介绍 ArtemisCloud Operator 是一个用于管理和部署云端基础设施的工具。它基于 Kubernetes 平…
阅读更多...
精益软件开发：从理念到实践

精益软件开发：从理念到实践

目录 前言1. 精益软件开发的起源与背景1.1 精益制造的起源1.2 精益思想在软件开发中的应用 2. 精益软件开发的核心原则2.1 消除浪费2.2 强调学习和持续改进2.3 快速交付2.4 尊重团队成员 3. 实施精益软件开发的方法3.1 精简流程3.2 持续反馈和迭代3.3 持续集成和持续交付3.4 建…
阅读更多...
Vue 学习之 axios

Vue 学习之 axios

目录 执行安装命令&#xff1a;npm install axios 使用的时候导入 axios以data&#xff0c;params&#xff0c;headers传参方式的区别 axios封装 是一个基于 promise 的 网络请求库&#xff0c;作用于浏览器和 node.js 中。使用Axios可以在前端项目中发送各种方式的HTTP请求…
阅读更多...
chromium源码魔改思路

chromium源码魔改思路

1.首先确定需要要改动的JS的API 比如要改动navigator.webdriver false 2.在官网查找JS的API https://developer.mozilla.org/zh-CN/docs/Web/Guide 3.在chromium源码官网查找源码 https://source.chromium.org/chromium/chromium/src 直接修改webdriver()返回值即可 4.然后…
阅读更多...
idea常用配置 | 快捷注释

idea常用配置 | 快捷注释

idea快速注释 一、类上快速注释 &#xff08;本方法是IDEA环境自带的&#xff0c;设置特别方便简单易使用&#xff09; 1、偏好设置->编辑器->文件和代码模版 | File-Settings-Editor-File and Code Templates 2、右下方的“描述”中有相对应的自动注注释配置格式 贴…
阅读更多...
Actor-agnostic Multi-label Action Recognition with Multi-modal Query

Actor-agnostic Multi-label Action Recognition with Multi-modal Query

标题&#xff1a;基于多模态查询的非特定行为者多标签动作识别 源文链接&#xff1a;https://openaccess.thecvf.com/content/ICCV2023W/NIVT/papers/Mondal_Actor-Agnostic_Multi-Label_Action_Recognition_with_Multi-Modal_Query_ICCVW_2023_paper.pdfhttps://openaccess.t…
阅读更多...
Java研学-Shiro安全框架(二)

Java研学-Shiro安全框架(二)

四 Shiro 鉴权 1 介绍 授权功能&#xff1a;就是为用户分配相关的权限的过程&#xff1b;鉴权功能&#xff1a;判断当前访问用户是否有某个资源的访问权限的过程。我们的权限管理系统是基于角色的权限管理&#xff0c;所以在系统中应该需要下面三个子模块&#xff1a;用户管理…
阅读更多...
Theta方法：一种时间序列分解与预测的简化方法

Theta方法：一种时间序列分解与预测的简化方法

Theta方法整合了两个基本概念:分解时间序列和利用基本预测技术来估计未来的价值。 每个数据科学爱好者都知道&#xff0c;时间序列是按一定时间间隔收集或记录的一系列数据点。例如&#xff0c;每日温度或经济指标的月值。把时间序列想象成不同成分的组合&#xff0c;趋势(数据…
阅读更多...
观测云产品更新 | Pipelines、智能监控、日志数据访问等

观测云产品更新 | Pipelines、智能监控、日志数据访问等

观测云更新 Pipelines 1、Pipelines&#xff1a;支持选择中心 Pipeline 执行脚本。 2、付费计划与账单&#xff1a;新增中心 Pipeline 计费项&#xff0c;统计所有命中中心 Pipeline 处理的原始日志的数据大小。 监控 1、通知对象管理&#xff1a;新增权限控制。配置操作权…
阅读更多...
PCL 使用列文伯格-马夸尔特法计算变换矩阵

PCL 使用列文伯格-马夸尔特法计算变换矩阵

目录 一、算法原理1、计算过程2、主要函数3、参考文献二、代码实现三、结果展示本文由CSDN点云侠原创，原文链接。如果你不是在点云侠的博客中看到该文章，那么此处便是不要脸的爬虫与GPT。 一、算法原理 1、计算过程 2、主要函数 void pcl
阅读更多...
民用无人驾驶航空器运营合格证怎么申请

民用无人驾驶航空器运营合格证怎么申请

随着科技的飞速发展&#xff0c;无人机已经从遥不可及的高科技产品飞入了寻常百姓家。越来越多的人想要亲自操纵无人机&#xff0c;探索更广阔的天空。但是&#xff0c;飞行无人机可不是简单的事情&#xff0c;你需要先获得无人机许可证&#xff0c;也就是今天所要讲的叫民用无…
阅读更多...
免费可视化工具为何成为数据分析新宠？

免费可视化工具为何成为数据分析新宠？

免费可视化工具为何越来越受欢迎&#xff1f;在当今数据驱动的时代&#xff0c;数据分析和展示已成为各行各业不可或缺的核心能力。然而&#xff0c;传统的数据处理和可视化工具往往价格昂贵&#xff0c;且使用门槛较高&#xff0c;这为许多中小企业和个人用户带来了不小的困扰…
阅读更多...
微信浏览器自动从http跳转到https的坑

微信浏览器自动从http跳转到https的坑

只要访问过同地址的https地址&#xff0c;就只能一直https了&#xff0c;无法再用https访问了&#xff0c;只能全站加上https才行。
阅读更多...
电脑怎么设置锁屏密码？这3个方法你知道吗

电脑怎么设置锁屏密码？这3个方法你知道吗

在日常生活中&#xff0c;电脑已成为我们工作和娱乐的重要工具。为了保护个人信息和数据安全&#xff0c;设置锁屏密码是必不可少的一步。通过设置锁屏密码&#xff0c;您可以有效防止未经授权的访问&#xff0c;确保电脑上的隐私和数据不被泄露。本文将详细介绍电脑怎么设置锁…
阅读更多...
【CSS in Depth 2 精译】1.5 渐进式增强

【CSS in Depth 2 精译】1.5 渐进式增强

文章目录 1.5 渐进式增强1.5.1 利用层叠规则实现渐进式增强1.5.2 渐进式增强的选择器1.5.3 利用 supports() 实现特性查询启用浏览器实验特性 1.5 渐进式增强 要用好 CSS 这样一门不断发展演进中的语言&#xff0c;其中一个重要的因素就是要与时俱进&#xff0c;及时了解哪些功…
阅读更多...
使用Python进行大数据处理Dask与Apache Spark的对比

使用Python进行大数据处理Dask与Apache Spark的对比

&#x1f47d;发现宝藏 前些天发现了一个巨牛的人工智能学习网站&#xff0c;通俗易懂&#xff0c;风趣幽默&#xff0c;忍不住分享一下给大家。【点击进入巨牛的人工智能学习网站】。 使用Python进行大数据处理Dask与Apache Spark的对比 随着数据量的增加和数据处理需求的增长…
阅读更多...
旅游管理系统源码小程序

旅游管理系统源码小程序

便捷旅行&#xff0c;尽在掌握 旅游管理系统是一款基于FastAdminElementUNIAPP开发的多端&#xff08;微信小程序、公众号、H5&#xff09;旅游管理系统&#xff0c;拥有丰富的装修组件、多端分享、模板消息、电子合同、旅游攻略、旅游线路及相关保险预订等功能&#xff0c;提…
阅读更多...
[leetcode]avoid-flood-in-the-city 避免洪水泛滥

[leetcode]avoid-flood-in-the-city 避免洪水泛滥

. - 力扣&#xff08;LeetCode&#xff09; class Solution { public:vector<int> avoidFlood(vector<int>& rains) {vector<int> ans(rains.size(), 1);set<int> st;unordered_map<int, int> mp;for (int i 0; i < rains.size(); i) {i…
阅读更多...
Redis实战—基于setnx的分布式锁与Redisson

Redis实战—基于setnx的分布式锁与Redisson

本博客为个人学习笔记&#xff0c;学习网站与详细见&#xff1a;黑马程序员Redis入门到实战 P56 - P63 目录 分布式锁介绍 基于SETNX的分布式锁 SETNX锁代码实现 修改业务代码 SETNX锁误删问题 SETNX锁原子性问题 Lua脚本 编写脚本 代码优化 总结 Redisson 前言…
阅读更多...
最新文章

Copyright @ 2022~2023