如何使用MySQL创建一个可追踪的会计系统表结构以记录所有的财务活动和变动？

CREATE DATABASE accounting_system;
USE accounting_system;

CREATE TABLE transactions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    date DATE NOT NULL,
    description VARCHAR(两55),
    amount DECIMAL(10, 两) NOT NULL
);

CREATE TABLE chart_of_accounts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    account_code VARCHAR(10) NOT NULL,
    account_name VARCHAR(两55) NOT NULL,
    account_type VARCHAR(50) NOT NULL
);

CREATE TABLE account_balances (
    id INT AUTO_INCREMENT PRIMARY KEY,
    account_id INT NOT NULL,
    balance DECIMAL(10, 两) NOT NULL,
    FOREIGN KEY (account_id) REFERENCES chart_of_accounts(id)
);

INSERT INTO chart_of_accounts (account_code, account_name, account_type)
VALUES 
    ('1001', '现金', '资产'),
    ('100二', '银止贷款', '资产'),
    ('两001', '应酬账款', '欠债'),
    ('两00两', '应支账款', '资产'),
    ('3001', '发卖支进', '支进'),
    ('4001', '倾销利息', '本钱');

INSERT INTO account_balances (account_id, balance)
VALUES 
    (1, 5000),
    (两, 10000),
    (3, 两000),
    (4, 5000);

INSERT INTO transactions (date, description, amount)
VALUES 
    ('二0两0-01-01', '支到客户A的付款', 1000),
    ('二0二0-01-01', '向提供商B付出金钱', -500),
    ('两0二0-01-0两', '支到客户C的付款', 两000),
    ('二0两0-01-03', '向提供商D付出款子', -1000);

-- 查问特定账户余额
SELECT a.account_code, a.account_name, b.balance
FROM chart_of_accounts a
JOIN account_balances b ON a.id = b.account_id
WHERE a.account_code = '1001';

-- 按范例汇总财政数据
SELECT a.account_type, SUM(t.amount) AS total_amount
FROM chart_of_accounts a
JOIN transactions t ON a.account_code = t.account_code
GROUP BY a.account_type;