用sql写一个收费系统
时间: 2023-12-26 07:06:30 浏览: 43
由于具体的收费系统涉及到不同的业务场景和数据结构,因此以下仅提供一个基本的收费系统的SQL示例供参考:
1. 创建数据库和表
```
CREATE DATABASE fee_system;
USE fee_system;
CREATE TABLE users (
id INT NOT NULL AUTO_INCREMENT,
username VARCHAR(50) NOT NULL,
password VARCHAR(50) NOT NULL,
balance DECIMAL(10,2) NOT NULL DEFAULT 0.00,
PRIMARY KEY (id)
);
CREATE TABLE services (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
description TEXT NOT NULL,
price DECIMAL(10,2) NOT NULL DEFAULT 0.00,
PRIMARY KEY (id)
);
CREATE TABLE transactions (
id INT NOT NULL AUTO_INCREMENT,
user_id INT NOT NULL,
service_id INT NOT NULL,
amount DECIMAL(10,2) NOT NULL,
timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
FOREIGN KEY (user_id) REFERENCES users (id),
FOREIGN KEY (service_id) REFERENCES services (id)
);
```
2. 插入数据
```
INSERT INTO users (username, password, balance) VALUES
('user1', 'password1', 100.00),
('user2', 'password2', 50.00),
('user3', 'password3', 20.00);
INSERT INTO services (name, description, price) VALUES
('Service A', 'Description A', 10.00),
('Service B', 'Description B', 20.00),
('Service C', 'Description C', 30.00);
```
3. 查询余额
```
SELECT username, balance FROM users WHERE username='user1';
```
4. 查询服务
```
SELECT * FROM services;
```
5. 购买服务
```
-- 转账
UPDATE users SET balance=balance-30.00 WHERE username='user1';
-- 添加交易记录
INSERT INTO transactions (user_id, service_id, amount) VALUES (1, 3, 30.00);
```
6. 查询交易记录
```
SELECT transactions.*, services.name
FROM transactions
INNER JOIN services ON transactions.service_id = services.id
WHERE transactions.user_id = 1;
```
相关推荐
![](https://img-home.csdnimg.cn/images/20210720083646.png)
![-](https://csdnimg.cn/download_wenku/file_type_lunwen.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)