财务管理系统

最新推荐文章于 2024-09-15 08:03:51 发布

致优韵

最新推荐文章于 2024-09-15 08:03:51 发布

阅读量163

点赞数 3

文章标签：数据库 sql

本文链接：https://blog.csdn.net/2302_80149165/article/details/140048139

版权

1.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 '用户密码',
    email VARCHAR(100) UNIQUE COMMENT '用户邮箱',
    gender ENUM('男', '女') NOT NULL COMMENT '用户性别',
    phone VARCHAR(20) UNIQUE COMMENT '用户电话'
);
 -- 角色表
CREATE TABLE roles (
    role_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '角色ID',
    role_name VARCHAR(50) NOT NULL UNIQUE COMMENT '角色名称'
);
 -- 用户角色关联表
CREATE TABLE user_roles (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '用户角色关联ID',
    user_id INT COMMENT '用户ID',
    role_id INT COMMENT '角色ID',
    FOREIGN KEY (user_id) REFERENCES users(user_id),
    FOREIGN KEY (role_id) REFERENCES roles(role_id)
);
 -- 账户表
CREATE TABLE accounts (
    account_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '账户ID',
    account_name VARCHAR(100) NOT NULL COMMENT '账户名称',
    account_type VARCHAR(50) NOT NULL COMMENT '账户类型',
    balance DECIMAL(10, 2) NOT NULL DEFAULT 0.00 COMMENT '账户余额',
    user_id INT COMMENT '用户ID',
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);
 -- 分类表
CREATE TABLE categories (
    category_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '分类ID',
    category_name VARCHAR(100) NOT NULL UNIQUE COMMENT '分类名称'
);
  交易表
CREATE TABLE transactions (
    transaction_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '交易ID',
    transaction_date DATE NOT NULL COMMENT '交易日期',
    description VARCHAR(255) NOT NULL COMMENT '交易描述',
    amount DECIMAL(10, 2) NOT NULL COMMENT '交易金额',
    account_id INT COMMENT '账户ID',
    category_id INT COMMENT '分类ID',
    FOREIGN KEY (account_id) REFERENCES accounts(account_id),
    FOREIGN KEY (category_id) REFERENCES categories(category_id)
);
 
 
-- 预算表
CREATE TABLE budgets (
    budget_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '预算ID',
    budget_name VARCHAR(100) NOT NULL COMMENT '预算名称',
    start_date DATE NOT NULL COMMENT '开始日期',
    end_date DATE NOT NULL COMMENT '结束日期',
    amount DECIMAL(10, 2) NOT NULL COMMENT '预算金额',
    account_id INT COMMENT '账户ID',
    FOREIGN KEY (account_id) REFERENCES accounts(account_id)
);

2.DML

-- 插入用户数据
INSERT INTO users (username, password, email, gender, phone) VALUES
('刘备', '123', 'liubei@example.com', '男', '13800138000'),
('关羽', '123', 'guanyu@example.com', '男', '13900139000'),
('张飞', '123', 'zhangfei@example.com', '男', '13700137000');
 
 
-- 插入角色数据
INSERT INTO roles (role_name) VALUES
('管理员'),
('普通用户'),
('财务');
 
 
-- 假设刘备是管理员，关羽是普通用户，张飞是财务
 
-- 插入用户角色关联数据
INSERT INTO user_roles (user_id, role_id) VALUES
(1, 1), -- 刘备是管理员
(2, 2), -- 关羽是普通用户
(3, 3); -- 张飞是财务
 
 
-- 插入账户数据
INSERT INTO accounts (account_name, account_type, balance, user_id) VALUES
('刘备储蓄账户', '储蓄账户', 10000.00, 1),
('关羽工资账户', '工资账户', 8000.00, 2),
('张飞支出账户', '支出账户', 5000.00, 3);
 

-- 插入分类数据
INSERT INTO categories (category_name) VALUES
('收入'),
('支出'),
('工资');
 
 
-- 插入交易数据
INSERT INTO transactions (transaction_date, description, amount, account_id, category_id) VALUES
('2023-04-01', '工资收入', 5000.00, 2, 1), -- 关羽工资账户收入
('2023-04-02', '购买办公用品', -200.00, 3, 2), -- 张飞支出账户支出
('2023-04-03', '储蓄利息', 100.00, 1, 1); -- 刘备储蓄账户收入
 
 
-- 插入预算数据
INSERT INTO budgets (budget_name, start_date, end_date, amount, account_id) VALUES
('第一季度办公用品预算', '2023-01-01', '2023-03-31', 1500.00, 3),
('第二季度市场推广预算', '2023-04-01', '2023-06-30', 3000.00, 3);

3.er图和模型图

4.触发器和测试语句

DELIMITER //
CREATE TRIGGER trg_after_user_insert
AFTER INSERT ON users
FOR EACH ROW
BEGIN
    DECLARE new_user_id INT;
    SET new_user_id = NEW.user_id;
     INSERT INTO accounts (account_name, account_type, balance, user_id)
    VALUES (CONCAT(NEW.username, '储蓄账户'), '储蓄账户', 0.00, new_user_id);
END;
//DELIMITER ;


-- 测试语句：插入一个新用户，并检查是否自动创建了账户
INSERT INTO users (username, password, email, gender, phone) VALUES ('赵云', '123', 'zhaoyun@example.com', '男', '13600136000');
SELECT * FROM accounts WHERE user_id = (SELECT user_id FROM users WHERE username = '赵云');

DELIMITER //
CREATE TRIGGER trg_after_transaction_update_balance
AFTER INSERT ON transactions
FOR EACH ROW
BEGIN
    UPDATE accounts
    SET balance = balance + NEW.amount
    WHERE account_id = NEW.account_id;
    -- 如果amount是负数，表示支出，但这里为了简化，我们假设所有交易都是收入
    -- 如果需要处理支出，可以在此添加逻辑
END //
DELIMITER ;
-- 测试语句：插入一条新的交易记录，检查账户余额是否更新
INSERT INTO transactions (transaction_date, description, amount, account_id, category_id) VALUES
('2023-04-04', '另一笔收入', 500.00, 1, 1); -- 向刘备储蓄账户增加500元
-- 检查账户余额
SELECT account_name, balance FROM accounts WHERE account_id = 1;

DELIMITER //
CREATE TRIGGER trg_before_budget_check_balance
BEFORE INSERT ON budgets
FOR EACH ROW
BEGIN
    DECLARE v_balance DECIMAL(10, 2);
    SELECT balance INTO v_balance FROM accounts WHERE account_id = NEW.account_id;
    IF v_balance < NEW.amount THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '账户余额不足以覆盖预算金额';
    END IF;
END //
DELIMITER ;
-- 测试语句：尝试插入一个预算，其金额大于账户余额
-- 这应该会触发错误，因为张飞支出账户的余额不足以支付这个预算
INSERT INTO budgets (budget_name, start_date, end_date, amount, account_id) VALUES
('第三季度办公用品预算', '2023-07-01', '2023-09-30', 6000.00, 3);

5.存储过程

DELIMITER //
CREATE PROCEDURE GetAccountBalanceByUsername(IN p_username VARCHAR(50), OUT p_balance DECIMAL(10, 2))
BEGIN
    -- 声明变量
    DECLARE v_user_id INT;
    
    -- 根据用户名查找用户ID
    SELECT user_id INTO v_user_id FROM users WHERE username = p_username;
    
    -- 检查是否找到了用户ID
    IF v_user_id IS NOT NULL THEN
        -- 如果找到了用户ID，则根据用户ID查找账户余额
        SELECT balance INTO p_balance FROM accounts WHERE user_id = v_user_id;
    ELSE
        -- 如果没有找到用户ID，则设置余额为NULL（或者可以根据需要设置其他值或抛出错误）
        SET p_balance = NULL;
    END IF;
END //
DELIMITER ;


-- 调用存储过程
CALL GetAccountBalanceByUsername('刘备', @balance);

6.简单查询

SELECT username, email
FROM users;

SELECT account_name, balance
FROM accounts
WHERE balance > 5000;

SELECT category_name
FROM categories;

7.复杂查询

-- 查询用户名是"刘备"的用户所关联的所有角色名称
SELECT r.role_name
FROM users u
JOIN user_roles ur ON u.user_id = ur.user_id
JOIN roles r ON ur.role_id = r.role_id
WHERE u.username = '刘备';

SELECT 
    u.username, 
    a.account_name, 
    a.balance, 
    COALESCE(SUM(CASE WHEN t.amount < 0 THEN t.amount ELSE 0 END), 0) AS total_expense
FROM users u
JOIN accounts a ON u.user_id = a.user_id
LEFT JOIN transactions t ON a.account_id = t.account_id
GROUP BY u.user_id, u.username, a.account_id, a.account_name, a.balance;

SELECT 
    SUM(CASE WHEN t.amount < 0 THEN t.amount ELSE 0 END) AS total_expense_during_budget
FROM transactions t
JOIN accounts a ON t.account_id = a.account_id
JOIN budgets b ON a.account_id = b.account_id
WHERE b.budget_name = '第一季度办公用品预算'
  AND t.transaction_date BETWEEN b.start_date AND b.end_date
  AND a.account_type = '支出账户';