mysql写一个订票系统
时间: 2023-08-02 18:23:13 浏览: 56
首先,需要在MySQL中创建相应的表,如下所示:
```
CREATE TABLE users (
id INT(11) NOT NULL AUTO_INCREMENT,
username VARCHAR(50) NOT NULL,
password VARCHAR(50) NOT NULL,
phone VARCHAR(20) NOT NULL,
email VARCHAR(50) NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE trains (
id INT(11) NOT NULL AUTO_INCREMENT,
train_no VARCHAR(20) NOT NULL,
start_station VARCHAR(50) NOT NULL,
end_station VARCHAR(50) NOT NULL,
start_time TIME NOT NULL,
end_time TIME NOT NULL,
price DECIMAL(10,2) NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE seats (
id INT(11) NOT NULL AUTO_INCREMENT,
train_id INT(11) NOT NULL,
seat_no VARCHAR(10) NOT NULL,
seat_type VARCHAR(10) NOT NULL,
is_sold TINYINT(1) NOT NULL DEFAULT 0,
PRIMARY KEY (id),
FOREIGN KEY (train_id) REFERENCES trains(id)
);
CREATE TABLE orders (
id INT(11) NOT NULL AUTO_INCREMENT,
user_id INT(11) NOT NULL,
train_id INT(11) NOT NULL,
seat_id INT(11) NOT NULL,
quantity INT(11) NOT NULL,
order_time DATETIME NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (user_id) REFERENCES users(id),
FOREIGN KEY (train_id) REFERENCES trains(id),
FOREIGN KEY (seat_id) REFERENCES seats(id)
);
CREATE TABLE payments (
id INT(11) NOT NULL AUTO_INCREMENT,
order_id INT(11) NOT NULL,
amount DECIMAL(10,2) NOT NULL,
payment_method VARCHAR(20) NOT NULL,
payment_status VARCHAR(20) NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (order_id) REFERENCES orders(id)
);
```
以上是一个简单的订票系统的数据库表结构,包括了用户表、车次表、座位表、订单表和支付表。
接下来,可以使用MySQL的存储过程来实现一些常用的功能,比如查询车次、预订座位、取消订单等。下面是一个查询车次的存储过程示例:
```
CREATE PROCEDURE get_trains(
IN start_station VARCHAR(50),
IN end_station VARCHAR(50),
IN date DATE
)
BEGIN
SELECT t.train_no, t.start_station, t.end_station, t.start_time, t.end_time, t.price, COUNT(s.id) AS seats_available
FROM trains t
LEFT JOIN seats s ON t.id = s.train_id AND s.is_sold = 0
WHERE t.start_station = start_station AND t.end_station = end_station
GROUP BY t.id
HAVING seats_available > 0;
END
```
这个存储过程接受三个参数:出发站、到达站和日期,返回满足条件的车次信息和可用座位数。
其他存储过程可以根据实际需求进行编写。同时,还可以使用MySQL的触发器来实现一些自动化操作,比如在订单表中插入一条记录时,自动将座位表中对应的座位状态设置为已售出。
以上是一个简单的订票系统的MySQL实现,具体的实现还需要根据实际情况进行调整和优化。