帮我写一个运动会管理信息系统,存储过程,触发器
时间: 2023-06-30 12:10:29 浏览: 49
好的,下面是运动会管理信息系统的存储过程和触发器:
## 存储过程
### 1. 创建运动会项目
```sql
CREATE PROCEDURE create_game(
IN name VARCHAR(255),
IN start_time DATETIME,
IN end_time DATETIME,
IN location VARCHAR(255)
)
BEGIN
INSERT INTO game (game_name, game_start_time, game_end_time, game_location)
VALUES (name, start_time, end_time, location);
END
```
### 2. 创建运动员
```sql
CREATE PROCEDURE create_athlete(
IN name VARCHAR(255),
IN age INT,
IN gender VARCHAR(10),
IN school VARCHAR(255)
)
BEGIN
INSERT INTO athlete (athlete_name, athlete_age, athlete_gender, athlete_school)
VALUES (name, age, gender, school);
END
```
### 3. 将运动员分配到运动项目
```sql
CREATE PROCEDURE assign_athlete_to_event(
IN athlete_id INT,
IN event_id INT
)
BEGIN
INSERT INTO athlete_event (athlete_id, event_id)
VALUES (athlete_id, event_id);
END
```
### 4. 将裁判分配到运动项目
```sql
CREATE PROCEDURE assign_judge_to_event(
IN judge_id INT,
IN event_id INT
)
BEGIN
INSERT INTO judge_event (judge_id, event_id)
VALUES (judge_id, event_id);
END
```
### 5. 记录比赛成绩
```sql
CREATE PROCEDURE add_score(
IN athlete_id INT,
IN event_id INT,
IN judge_id INT,
IN score DECIMAL(5,2)
)
BEGIN
INSERT INTO score (athlete_id, event_id, judge_id, score)
VALUES (athlete_id, event_id, judge_id, score);
END
```
## 触发器
### 1. 自动更新运动员的总成绩
当比赛成绩表中新增一条记录时,自动更新对应运动员的总成绩。
```sql
CREATE TRIGGER update_athlete_total_score
AFTER INSERT
ON score
FOR EACH ROW
BEGIN
UPDATE athlete SET athlete_total_score = athlete_total_score + NEW.score
WHERE athlete.athlete_id = NEW.athlete_id;
END
```
### 2. 自动更新运动员的排名
当比赛成绩表中新增一条记录时,自动更新对应运动员的排名。
```sql
CREATE TRIGGER update_athlete_rank
AFTER INSERT
ON score
FOR EACH ROW
BEGIN
SET @rank = 0;
UPDATE (SELECT athlete_id, SUM(score) AS total_score FROM score GROUP BY athlete_id) AS t
JOIN athlete ON t.athlete_id = athlete.athlete_id
SET athlete_rank = @rank := @rank + 1
ORDER BY t.total_score DESC;
END
```
相关推荐
![java](https://img-home.csdnimg.cn/images/20210720083646.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)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)