用mysqldm代码设计全过程ATM程序功能如下: 1. 账户登录,用户输入账号和密码,经过验证后可进入主界面 2. 主界面,用户登录后显示欢迎信息,在该界面显示各种功能按钮,如:查询余额、取款、存款、查看明细记录、转账、修改密码等,点击各个功能按钮,可进入相应界面进行操作。
时间: 2024-03-26 22:35:17 浏览: 27
好的,我可以为你提供一个MySQL数据库的设计和实现,来支持这个ATM程序的功能。以下是数据库的设计:
1.用户表(user):
```sql
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`account` varchar(20) NOT NULL COMMENT '账号',
`password` varchar(32) NOT NULL COMMENT '密码',
`name` varchar(20) DEFAULT NULL COMMENT '姓名',
`phone` varchar(20) DEFAULT NULL COMMENT '电话',
`address` varchar(50) DEFAULT NULL COMMENT '地址',
`balance` decimal(10,2) DEFAULT '0.00' COMMENT '余额',
PRIMARY KEY (`id`),
UNIQUE KEY `idx_account` (`account`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';
```
2.交易记录表(transaction):
```sql
CREATE TABLE `transaction` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL COMMENT '用户id',
`type` tinyint(4) NOT NULL COMMENT '交易类型:1-取款,2-存款,3-转账',
`amount` decimal(10,2) NOT NULL COMMENT '交易金额',
`target` varchar(20) DEFAULT NULL COMMENT '交易对象(如果是转账)',
`time` datetime NOT NULL COMMENT '交易时间',
PRIMARY KEY (`id`),
KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='交易记录表';
```
接下来,我会为你提供一些MySQL存储过程的代码,来实现ATM程序的各种功能:
1.登录功能:
```sql
CREATE PROCEDURE `login`(
IN p_account VARCHAR(20),
IN p_password VARCHAR(32),
OUT p_result TINYINT
)
BEGIN
DECLARE v_id INT;
DECLARE v_password VARCHAR(32);
SELECT id, password INTO v_id, v_password FROM user WHERE account = p_account;
IF v_id IS NULL THEN
SET p_result = -1; -- 账号不存在
ELSEIF v_password <> p_password THEN
SET p_result = -2; -- 密码错误
ELSE
SET p_result = 1; -- 登录成功
END IF;
END
```
2.查询余额功能:
```sql
CREATE PROCEDURE `query_balance`(
IN p_user_id INT,
OUT p_balance DECIMAL(10,2)
)
BEGIN
SELECT balance INTO p_balance FROM user WHERE id = p_user_id;
END
```
3.取款功能:
```sql
CREATE PROCEDURE `withdraw`(
IN p_user_id INT,
IN p_amount DECIMAL(10,2),
OUT p_result TINYINT
)
BEGIN
DECLARE v_balance DECIMAL(10,2);
SELECT balance INTO v_balance FROM user WHERE id = p_user_id;
IF p_amount > v_balance THEN
SET p_result = -1; -- 余额不足
ELSE
UPDATE user SET balance = balance - p_amount WHERE id = p_user_id;
INSERT INTO transaction(user_id, type, amount, time) VALUES(p_user_id, 1, p_amount, NOW());
SET p_result = 1; -- 取款成功
END IF;
END
```
4.存款功能:
```sql
CREATE PROCEDURE `deposit`(
IN p_user_id INT,
IN p_amount DECIMAL(10,2),
OUT p_result TINYINT
)
BEGIN
UPDATE user SET balance = balance + p_amount WHERE id = p_user_id;
INSERT INTO transaction(user_id, type, amount, time) VALUES(p_user_id, 2, p_amount, NOW());
SET p_result = 1; -- 存款成功
END
```
5.查看明细记录功能:
```sql
CREATE PROCEDURE `query_transaction`(
IN p_user_id INT
)
BEGIN
SELECT type, amount, target, time FROM transaction WHERE user_id = p_user_id;
END
```
6.转账功能:
```sql
CREATE PROCEDURE `transfer`(
IN p_user_id INT,
IN p_target VARCHAR(20),
IN p_amount DECIMAL(10,2),
OUT p_result TINYINT
)
BEGIN
DECLARE v_balance DECIMAL(10,2);
SELECT balance INTO v_balance FROM user WHERE id = p_user_id;
IF p_amount > v_balance THEN
SET p_result = -1; -- 余额不足
ELSEIF NOT EXISTS (SELECT id FROM user WHERE account = p_target) THEN
SET p_result = -2; -- 转账对象不存在
ELSE
UPDATE user SET balance = balance - p_amount WHERE id = p_user_id;
INSERT INTO transaction(user_id, type, amount, target, time) VALUES(p_user_id, 3, p_amount, p_target, NOW());
UPDATE user SET balance = balance + p_amount WHERE account = p_target;
INSERT INTO transaction(user_id, type, amount, target, time) VALUES((SELECT id FROM user WHERE account = p_target), 3, p_amount, (SELECT account FROM user WHERE id = p_user_id), NOW());
SET p_result = 1; -- 转账成功
END IF;
END
```
7.修改密码功能:
```sql
CREATE PROCEDURE `change_password`(
IN p_user_id INT,
IN p_old_password VARCHAR(32),
IN p_new_password VARCHAR(32),
OUT p_result TINYINT
)
BEGIN
DECLARE v_password VARCHAR(32);
SELECT password INTO v_password FROM user WHERE id = p_user_id;
IF v_password <> p_old_password THEN
SET p_result = -1; -- 原密码错误
ELSE
UPDATE user SET password = p_new_password WHERE id = p_user_id;
SET p_result = 1; -- 修改密码成功
END IF;
END
```
以上就是一个简单的ATM程序的MySQL数据库设计和实现。当然,实际情况可能更加复杂,需要根据实际需求进行调整和扩展。