具体创建代码
时间: 2024-02-09 12:10:08 浏览: 17
由于您没有提供具体的数据库管理系统,我这里提供一个通用的SQL语句示例,您可以根据实际情况进行修改。
1. 创建车型表
```sql
CREATE TABLE car_type(
car_type_id INT PRIMARY KEY,
car_type_name VARCHAR(50),
seats INT
);
```
2. 创建路线表
```sql
CREATE TABLE route(
route_id INT PRIMARY KEY,
start_point VARCHAR(50),
end_point VARCHAR(50)
);
```
3. 创建驾驶员表
```sql
CREATE TABLE driver(
driver_id INT PRIMARY KEY,
driver_name VARCHAR(50),
contact VARCHAR(50)
);
```
4. 创建车次表
```sql
CREATE TABLE car_trip(
car_trip_id INT PRIMARY KEY,
car_type_id INT,
route_id INT,
driver_id INT,
start_time DATETIME,
end_time DATETIME,
price FLOAT,
sold_seats INT,
FOREIGN KEY (car_type_id) REFERENCES car_type(car_type_id),
FOREIGN KEY (route_id) REFERENCES route(route_id),
FOREIGN KEY (driver_id) REFERENCES driver(driver_id)
);
```
5. 创建售票表
```sql
CREATE TABLE ticket(
ticket_id INT PRIMARY KEY,
car_trip_id INT,
seat_number INT,
FOREIGN KEY (car_trip_id) REFERENCES car_trip(car_trip_id)
);
```
6. 创建按线路统计乘坐次数的存储过程
```sql
CREATE PROCEDURE sp_route_trip_count
@route_id INT
AS
BEGIN
SELECT COUNT(*) AS trip_count
FROM car_trip
WHERE route_id = @route_id;
END
```
7. 创建按车次统计某段时间的乘坐次数的存储过程
```sql
CREATE PROCEDURE sp_car_trip_count
@car_trip_id INT,
@start_time DATETIME,
@end_time DATETIME
AS
BEGIN
SELECT COUNT(*) AS trip_count
FROM ticket
WHERE car_trip_id = @car_trip_id AND sold_time BETWEEN @start_time AND @end_time;
END
```
8. 创建售票时自动修改相应年次己售座位数的触发器
```sql
CREATE TRIGGER tr_sold_seats_update
ON ticket
AFTER INSERT
AS
BEGIN
UPDATE car_trip
SET sold_seats = sold_seats + 1
WHERE car_trip_id = (SELECT car_trip_id FROM inserted);
END
```
9. 创建车型数量视图
```sql
CREATE VIEW car_type_count_view
AS
SELECT car_type_name, COUNT(*) AS car_type_count
FROM car_trip
JOIN car_type ON car_trip.car_type_id = car_type.car_type_id
GROUP BY car_type_name;
```
10. 创建路线数量视图
```sql
CREATE VIEW route_count_view
AS
SELECT start_point + '-' + end_point AS route_name, COUNT(*) AS route_count
FROM car_trip
JOIN route ON car_trip.route_id = route.route_id
GROUP BY start_point, end_point;
```
以上是一个简单的SQL语句示例,供您参考。请根据实际情况进行修改和完善。