简介这份资源是《数据库系统》课程设计报告——证券业务管理系统设计与开发面向高校计算机相关专业学生及需要完成数据库课程设计的自学者帮助解决从需求分析到物理实现、再到前台软件开发的完整设计流程问题。报告以MySQL为数据库平台内容覆盖系统需求分析、业务流与数据流分析、数据字典、概念结构设计实体、属性、联系及PDM图、逻辑结构设计一对一、一对多、多对多关系转化、物理实现表设计、完整性约束、视图、索引、存储过程与触发器以及职工管理、工程负责人管理、系统管理员管理三大模块的功能调试最后还包含前台软件的开发选择与功能实现。资源包为1个docx文档大小约740KB结构完整、目录清晰适合直接参考或作为课程设计模板使用。目前已有34人学习可作为数据库课程设计阶段的重要参考资料。1. 证券业务管理系统课设从建表到跑通的第一道坎证券业务管理系统这个课设题目几乎每届都有人选但真正能跑通、能讲清楚、能扛住答辩追问的不到三成。问题不在业务逻辑有多复杂而在于大多数人把时间花在了写界面和堆功能上忽略了数据库设计才是这个题目的核心得分点。证券业务天然是多表强关联场景客户、证券账户、资金账户、委托指令、成交回报、持仓明细、行情快照每一张表都牵扯到外键约束、事务边界和并发一致性。如果表结构没设计好后面写再多增删改查都是空中楼阁。这篇笔记面向正在做数据库课程设计、选了证券业务管理系统方向的同学也适合想用这个题目练手 SQL 和事务控制的开发者。我会按建库建表、核心业务 SQL、事务与锁、性能排查、答辩加分技巧的顺序把整个方案拆到能直接复现的程度。2. 建库建表证券业务管理系统的表结构怎么定2.1 先想清楚证券业务的实体关系再动手写 CREATE TABLE很多人拿到题目第一反应是打开 Navicat 或者 SQL Server Management Studio 直接建表建到一半发现字段不够用又回头改改完发现外键冲突最后干脆把外键全删了。这是典型的翻车路径。正确做法是先画实体关系图把证券业务的核心实体和它们之间的基数关系理清楚。证券业务管理系统的最小可行实体集包括客户customer、证券账户securities_account、资金账户fund_account、证券信息security_info、委托指令order_instruction、成交记录trade_record、持仓position。关系上一个客户可以开多个证券账户一个证券账户对应一个资金账户一个证券账户可以发多条委托指令一条委托指令可能产生多条成交记录持仓由成交记录汇总而来。这里有一个容易忽略的点证券账户和资金账户为什么要分开因为证券账户管的是股票持仓资金账户管的是可用资金和冻结资金两者的更新频率和事务边界不同。委托买入时先冻结资金成交后才扣减资金并增加持仓如果合在一张表里锁的粒度会变大并发性能会明显下降。2.2 建表 SQL 与字段类型选择下面这套建表语句以 MySQL 8.0 为例SQL Server 用户把AUTO_INCREMENT换成IDENTITY(1,1)、DATETIME换成DATETIME2即可。字段类型的选择直接影响到后续 SQL 性能和存储空间我会在代码后逐项说明。-- 客户表存储个人基本信息 CREATE TABLE customer ( customer_id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT 客户编号, customer_name VARCHAR(64) NOT NULL COMMENT 客户姓名, id_card VARCHAR(18) NOT NULL UNIQUE COMMENT 身份证号, phone VARCHAR(20) COMMENT 联系电话, risk_level TINYINT DEFAULT 1 COMMENT 风险等级 1-5, create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 开户时间 ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT客户信息表; -- 证券账户表一个客户可开多个 CREATE TABLE securities_account ( sa_id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT 证券账户ID, customer_id BIGINT NOT NULL COMMENT 所属客户, sa_code VARCHAR(20) NOT NULL UNIQUE COMMENT 证券账号, market_type TINYINT NOT NULL COMMENT 市场类型 1-上海 2-深圳, open_date DATE NOT NULL COMMENT 开户日期, status TINYINT DEFAULT 1 COMMENT 状态 1-正常 0-冻结, CONSTRAINT fk_sa_customer FOREIGN KEY (customer_id) REFERENCES customer(customer_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT证券账户表; -- 资金账户表与证券账户一对一 CREATE TABLE fund_account ( fa_id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT 资金账户ID, sa_id BIGINT NOT NULL UNIQUE COMMENT 关联证券账户, total_balance DECIMAL(18,2) DEFAULT 0.00 COMMENT 总资产, available DECIMAL(18,2) DEFAULT 0.00 COMMENT 可用资金, frozen DECIMAL(18,2) DEFAULT 0.00 COMMENT 冻结资金, update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, CONSTRAINT fk_fa_sa FOREIGN KEY (sa_id) REFERENCES securities_account(sa_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT资金账户表; -- 证券信息表股票基础数据 CREATE TABLE security_info ( sec_code VARCHAR(10) PRIMARY KEY COMMENT 证券代码, sec_name VARCHAR(32) NOT NULL COMMENT 证券名称, market_type TINYINT NOT NULL COMMENT 市场 1-上海 2-深圳, sec_type TINYINT DEFAULT 1 COMMENT 类型 1-股票 2-基金 3-债券, list_date DATE COMMENT 上市日期 ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT证券信息表; -- 委托指令表记录买卖委托 CREATE TABLE order_instruction ( order_id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT 委托编号, sa_id BIGINT NOT NULL COMMENT 证券账户, sec_code VARCHAR(10) NOT NULL COMMENT 证券代码, direction TINYINT NOT NULL COMMENT 方向 1-买入 2-卖出, price DECIMAL(10,3) NOT NULL COMMENT 委托价格, quantity INT NOT NULL COMMENT 委托数量股, frozen_amount DECIMAL(18,2) DEFAULT 0.00 COMMENT 冻结金额, order_status TINYINT DEFAULT 0 COMMENT 状态 0-待成交 1-部分成交 2-全部成交 3-已撤单, order_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 委托时间, CONSTRAINT fk_order_sa FOREIGN KEY (sa_id) REFERENCES securities_account(sa_id), CONSTRAINT fk_order_sec FOREIGN KEY (sec_code) REFERENCES security_info(sec_code) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT委托指令表; -- 成交记录表 CREATE TABLE trade_record ( trade_id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT 成交编号, order_id BIGINT NOT NULL COMMENT 关联委托, trade_price DECIMAL(10,3) NOT NULL COMMENT 成交价格, trade_quantity INT NOT NULL COMMENT 成交数量, trade_amount DECIMAL(18,2) NOT NULL COMMENT 成交金额, trade_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 成交时间, CONSTRAINT fk_trade_order FOREIGN KEY (order_id) REFERENCES order_instruction(order_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT成交记录表; -- 持仓表 CREATE TABLE position ( pos_id BIGINT PRIMARY KEY AUTO_INCREMENT, sa_id BIGINT NOT NULL COMMENT 证券账户, sec_code VARCHAR(10) NOT NULL COMMENT 证券代码, quantity INT DEFAULT 0 COMMENT 持仓数量, avg_cost DECIMAL(10,3) DEFAULT 0.000 COMMENT 持仓均价, update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uk_sa_sec (sa_id, sec_code), CONSTRAINT fk_pos_sa FOREIGN KEY (sa_id) REFERENCES securities_account(sa_id), CONSTRAINT fk_pos_sec FOREIGN KEY (sec_code) REFERENCES security_info(sec_code) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT持仓表;金额字段统一用DECIMAL(18,2)而不是FLOAT或DOUBLE这是证券系统里最容易被扣分的细节。浮点数在累加过程中会产生精度误差比如 0.1 0.2 在浮点运算里不等于 0.3资金对账时会出现一分钱的偏差答辩老师一问就露馅。价格字段用DECIMAL(10,3)是因为 A 股报价精确到分但部分场内基金和债券报价到厘留三位小数更稳妥。持仓表的UNIQUE KEY uk_sa_sec (sa_id, sec_code)是一个联合唯一约束保证同一证券账户下同一只股票只有一条持仓记录。没有这个约束每次成交都 INSERT 一条新记录查询持仓时就得 GROUP BY 汇总既慢又容易出错。注意外键约束在课设阶段建议保留它能帮你发现数据插入顺序的错误。如果后期做批量导入性能测试可以临时SET FOREIGN_KEY_CHECKS0但生产环境不要长期关闭。2.3 初始化测试数据的三个要点建完表之后需要灌入测试数据否则后面写查询没有数据可查。初始化数据有三个要点第一客户和证券信息要足够多至少 50 个客户、20 只股票否则 GROUP BY 和 JOIN 的效果看不出来第二委托指令和成交记录要有时间跨度覆盖多个交易日方便后面做日期范围查询和窗口函数第三资金账户的余额要合理不能出现可用资金为负数的情况否则事务测试时逻辑会乱。可以用存储过程批量生成也可以写 Python 脚本插入。我一般用 Python 的faker库生成客户信息用随机数生成委托和成交记录。这里不展开脚本细节核心是保证数据分布均匀不要所有委托都是买入、所有成交价格都一样。3. 核心业务 SQL从委托到持仓的完整链路3.1 买入委托的资金冻结与持仓更新证券业务管理系统最核心的一条链路是客户提交买入委托 → 冻结资金 → 成交 → 扣减冻结资金 → 增加持仓。这条链路涉及fund_account、order_instruction、trade_record、position四张表的写操作必须放在一个事务里。-- 买入委托冻结资金并生成委托指令 START TRANSACTION; -- 1. 检查可用资金是否充足 SELECT available INTO avail FROM fund_account WHERE sa_id 1001 FOR UPDATE; -- 2. 计算冻结金额价格 × 数量 手续费手续费按万分之三估算 SET frozen 12.50 * 1000 * 1.0003; -- 3. 如果可用资金足够冻结并生成委托 UPDATE fund_account SET available available - frozen, frozen frozen frozen WHERE sa_id 1001 AND available frozen; -- 4. 插入委托指令 INSERT INTO order_instruction (sa_id, sec_code, direction, price, quantity, frozen_amount, order_status) VALUES (1001, 600519, 1, 12.50, 1000, frozen, 0); COMMIT;这段 SQL 的关键在SELECT ... FOR UPDATE它对资金账户行加了排他锁防止两个并发委托同时读到相同的可用资金然后各自冻结导致超额冻结。没有这个锁两个线程同时买入每个都认为可用资金足够最后冻结总额超过实际余额这就是典型的并发翻车场景。frozen的计算里乘了1.0003这是把手续费预估进去了。实际券商系统的手续费计算更复杂有最低五元限制、印花税、过户费等但课设阶段用一个系数简化即可答辩时能说清楚手续费的构成就行。3.2 成交回报与持仓汇总查询成交之后需要更新委托状态、写入成交记录、扣减冻结资金、更新持仓。持仓更新用INSERT ... ON DUPLICATE KEY UPDATE一条语句搞定避免先查后插的竞态条件。-- 成交处理更新委托状态、写成交记录、更新持仓 START TRANSACTION; -- 1. 更新委托状态为全部成交 UPDATE order_instruction SET order_status 2 WHERE order_id 2001 AND order_status IN (0, 1); -- 2. 写入成交记录 INSERT INTO trade_record (order_id, trade_price, trade_quantity, trade_amount) VALUES (2001, 12.48, 1000, 12480.00); -- 3. 扣减冻结资金成交金额 手续费 UPDATE fund_account SET frozen frozen - 12503.75, total_balance total_balance - 12503.75 WHERE sa_id 1001; -- 4. 更新持仓存在则累加不存在则插入 INSERT INTO position (sa_id, sec_code, quantity, avg_cost) VALUES (1001, 600519, 1000, 12.48) ON DUPLICATE KEY UPDATE quantity quantity VALUES(quantity), avg_cost (avg_cost * quantity VALUES(avg_cost) * VALUES(quantity)) / (quantity VALUES(quantity)); COMMIT;持仓均价的更新公式是(原均价 × 原数量 新均价 × 新数量) / (原数量 新数量)这是加权平均成本的标准算法。注意ON DUPLICATE KEY UPDATE里引用新插入的值要用VALUES()函数MySQL 8.0.20 之后推荐用别名写法但课设环境版本不确定用VALUES()兼容性更好。3.3 用窗口函数查客户收益排名答辩时如果只写了增删改查分数很难拉开差距。加一个窗口函数的查询比如按客户统计总资产并排名能体现你对 SQL 高级特性的掌握。-- 按客户统计总资产并排名同时计算与平均值的差距 SELECT c.customer_id, c.customer_name, SUM(fa.total_balance) AS total_assets, RANK() OVER (ORDER BY SUM(fa.total_balance) DESC) AS rank_no, ROUND(SUM(fa.total_balance) - AVG(SUM(fa.total_balance)) OVER (), 2) AS diff_from_avg FROM customer c JOIN securities_account sa ON c.customer_id sa.customer_id JOIN fund_account fa ON sa.sa_id fa.sa_id GROUP BY c.customer_id, c.customer_name ORDER BY rank_no;RANK()是窗口函数按总资产降序排名相同资产会并列且跳过后续名次。AVG(...) OVER ()是开窗聚合计算所有客户总资产的平均值不需要子查询。这个查询在 MySQL 8.0、SQL Server 2012、Oracle 上都支持答辩时如果老师问“不用窗口函数怎么写”可以答用子查询关联但性能会差一些。4. 事务与并发课设里最容易忽略的得分点4.1 隔离级别选 READ COMMITTED 还是 REPEATABLE READMySQL InnoDB 默认隔离级别是 REPEATABLE READ但证券业务场景下我一般建议显式设为 READ COMMITTED。原因在于 REPEATABLE READ 下事务内多次读取同一行会看到快照如果中间有其他事务更新了资金余额你读到的还是旧值容易导致业务判断失误。READ COMMITTED 每次读取都拿最新已提交数据更符合证券交易对实时性的要求。设置方式-- 会话级别设置隔离级别 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; -- 查看当前隔离级别 SELECT transaction_isolation;但 READ COMMITTED 会带来不可重复读的问题同一个事务里两次查同一行可能结果不同。证券系统里这个问题可以通过FOR UPDATE加锁来规避因为加锁读不受隔离级别影响读的永远是最新已提交数据。4.2 死锁排查两个事务互相等锁怎么办证券系统里死锁的典型场景是事务 A 冻结资金锁 fund_account 行然后插入委托锁 order_instruction 行事务 B 同时插入委托锁 order_instruction 行然后冻结资金锁 fund_account 行。两个事务加锁顺序相反互相等待死锁就发生了。排查死锁的第一步是看 InnoDB 状态-- 查看最近一次死锁信息 SHOW ENGINE INNODB STATUS\G在输出里找LATEST DETECTED DEADLOCK段落它会显示两个事务分别持有什么锁、等待什么锁。解决思路是统一加锁顺序所有事务都先锁 fund_account 再锁 order_instruction不要反过来。另外事务尽量短小不要在事务里做网络请求或文件 IO减少持锁时间。提示课设答辩时如果老师问“你怎么保证并发安全”把FOR UPDATE、统一加锁顺序、短事务这三点讲清楚比背隔离级别定义管用得多。4.3 用 EXPLAIN 看慢 SQL 有没有走索引课设数据量小的时候全表扫描和走索引的差别看不出来但答辩老师可能会问“这条 SQL 走索引了吗”。用EXPLAIN可以看执行计划EXPLAIN SELECT * FROM order_instruction WHERE sa_id 1001 AND order_time 2024-01-01;关注type列ALL是全表扫描ref或range是走索引。key列显示实际使用的索引名。如果type是ALL考虑在sa_id和order_time上建联合索引CREATE INDEX idx_order_sa_time ON order_instruction(sa_id, order_time);联合索引的字段顺序很重要sa_id在前是因为等值查询优先order_time在后用于范围查询。如果反过来order_time的范围查询会导致sa_id的索引失效。5. 避坑与排查证券业务管理系统课设的五个血泪教训5.1 现象插入委托时报外键约束失败原因order_instruction表的sec_code外键指向security_info但security_info里还没有这只股票的数据。很多人先插委托再插证券信息顺序反了。解决先灌security_info和customer、securities_account、fund_account最后插order_instruction和trade_record。如果确实需要乱序插入临时SET FOREIGN_KEY_CHECKS0但记得插完改回 1。5.2 现象资金账户可用余额出现负数原因冻结资金时没有加AND available frozen条件或者并发场景下两个事务同时读到相同的可用余额各自冻结后总额超了。解决冻结 SQL 必须带WHERE available frozen并且用FOR UPDATE加锁。更新后检查ROW_COUNT()如果返回 0 说明余额不足回滚事务。5.3 现象持仓均价越算越偏原因用了FLOAT或DOUBLE存价格和均价累加多次后精度丢失。或者均价更新公式写错了把(原均价 新均价) / 2当成了加权平均。解决金额和价格字段统一用DECIMAL。均价公式必须是(原均价 × 原数量 新均价 × 新数量) / (原数量 新数量)不能简单取平均。5.4 现象SQL Server 连接报 SSL 加密错误原因SQL Server 2016 之后默认强制加密连接但本地开发环境的 JDBC 驱动或客户端没配信任证书。热词里有人搜“驱动程序无法通过使用安全套接字层加密与 SQL Server 建立安全连接”就是这个坑。解决在 JDBC URL 里加encryptfalse或trustServerCertificatetrue仅限本地开发环境。生产环境应该配置正式证书不要图省事关加密。5.5 现象Navicat 连接达梦数据库后看不到表原因达梦数据库默认大小写敏感建表时如果用了小写表名Navicat 可能按大写去查导致看不到。或者模式schema不对表建在了其他用户下。解决达梦建表时统一用大写表名或者在连接设置里关闭大小写敏感。Navicat 连接达梦需要选对驱动版本达梦官方提供了 JDBC 驱动不要用 MySQL 驱动去连。6. 答辩加分三个让老师眼前一亮的进阶技巧6.1 用存储过程封装委托下单逻辑把买入委托的整套逻辑写成一个存储过程答辩时现场调用演示比在 Java 或 Python 里写一堆 JDBC 代码更有说服力。存储过程里可以包含事务控制、异常处理和参数校验。DELIMITER // CREATE PROCEDURE place_buy_order( IN p_sa_id BIGINT, IN p_sec_code VARCHAR(10), IN p_price DECIMAL(10,3), IN p_quantity INT, OUT p_result VARCHAR(64) ) BEGIN DECLARE v_available DECIMAL(18,2); DECLARE v_frozen DECIMAL(18,2); DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; SET p_result FAILED: 系统异常; END; START TRANSACTION; SELECT available INTO v_available FROM fund_account WHERE sa_id p_sa_id FOR UPDATE; SET v_frozen p_price * p_quantity * 1.0003; IF v_available v_frozen THEN ROLLBACK; SET p_result FAILED: 可用资金不足; ELSE UPDATE fund_account SET available available - v_frozen, frozen frozen v_frozen WHERE sa_id p_sa_id; INSERT INTO order_instruction (sa_id, sec_code, direction, price, quantity, frozen_amount, order_status) VALUES (p_sa_id, p_sec_code, 1, p_price, p_quantity, v_frozen, 0); COMMIT; SET p_result SUCCESS; END IF; END // DELIMITER ;调用方式CALL place_buy_order(1001, 600519, 12.50, 1000, result); SELECT result;。存储过程的优势是把业务逻辑收在数据库层应用层只需要调用减少了网络往返和代码重复。缺点是调试不如应用层方便课设里用来演示事务控制刚好合适。6.2 用触发器自动记录资金变动日志答辩老师常问“资金变动有没有留痕”。加一张资金流水表用触发器在fund_account更新时自动插入日志既展示了触发器用法又体现了审计意识。CREATE TABLE fund_log ( log_id BIGINT PRIMARY KEY AUTO_INCREMENT, sa_id BIGINT NOT NULL, change_type VARCHAR(16) NOT NULL, amount DECIMAL(18,2) NOT NULL, before_bal DECIMAL(18,2), after_bal DECIMAL(18,2), log_time DATETIME DEFAULT CURRENT_TIMESTAMP ); DELIMITER // CREATE TRIGGER trg_fund_update AFTER UPDATE ON fund_account FOR EACH ROW BEGIN IF OLD.available ! NEW.available THEN INSERT INTO fund_log (sa_id, change_type, amount, before_bal, after_bal) VALUES (NEW.sa_id, AVAILABLE, NEW.available - OLD.available, OLD.available, NEW.available); END IF; END // DELIMITER ;触发器里用OLD和NEW关键字分别引用更新前和更新后的行。IF OLD.available ! NEW.available判断只有可用资金变化时才记日志避免无意义的记录。这张日志表在答辩时可以直接查出来展示证明系统有审计能力。6.3 用慢查询日志定位性能瓶颈MySQL 的慢查询日志可以记录执行时间超过阈值的 SQL课设里开启它跑一遍业务操作看看有没有意外的慢 SQL。-- 查看慢查询日志是否开启 SHOW VARIABLES LIKE slow_query_log; -- 开启慢查询日志阈值设为 1 秒 SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; -- 查看慢查询日志文件路径 SHOW VARIABLES LIKE slow_query_log_file;开启后执行几次委托和查询操作然后去日志文件里看有没有记录。如果课设数据量太小跑不出慢查询可以手动把long_query_time设为 0这样所有 SQL 都会被记录用来观察哪些查询走了全表扫描。答辩时如果能说出“我用慢查询日志发现某条 JOIN 没走索引加了联合索引后从 0.8 秒降到 0.02 秒”这比背概念有说服力得多。我做了这么多年数据库相关的项目最大的习惯就是建完表先跑一遍EXPLAIN不管数据量大小。课设阶段养成的这个习惯到了工作中能帮你省下大量排查慢 SQL 的时间。证券业务管理系统这个题目表结构设计占四十分事务和并发占三十分剩下的三十分在查询和答辩表现上。把这篇笔记里的建表 SQL、事务模板和排查思路跑一遍基本能覆盖大部分答辩场景。希望帮到你。本文还有配套的精品资源点击获取