1.计算学生总分数的存储函数 计算学生平均分数的存储函数 查询学生最高分数的存储函数 查询学生最低分数的存储函数 查询学生总成绩排名的存储函数 创建学生信息表的存储过程 插入学生信息的存储过程 删除学生信息的存储过程 更新学生信息的存储过程 查询学生信息的存储过程的运行结果
时间: 2023-06-20 13:07:14 浏览: 51
这里提供一个MySQL数据库的示例代码,包含上述需求的存储函数和存储过程:
创建学生信息表:
```sql
CREATE TABLE students (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50),
score1 INT,
score2 INT,
score3 INT,
total_score INT,
rank INT
);
```
计算学生总分数的存储函数:
```sql
CREATE FUNCTION calculate_total_score(score1 INT, score2 INT, score3 INT)
RETURNS INT
BEGIN
DECLARE total INT;
SET total = score1 + score2 + score3;
RETURN total;
END;
```
计算学生平均分数的存储函数:
```sql
CREATE FUNCTION calculate_avg_score(score1 INT, score2 INT, score3 INT)
RETURNS FLOAT
BEGIN
DECLARE avg FLOAT;
SET avg = (score1 + score2 + score3) / 3.0;
RETURN avg;
END;
```
查询学生最高分数的存储函数:
```sql
CREATE FUNCTION get_max_score() RETURNS INT
BEGIN
DECLARE max_score INT;
SELECT MAX(total_score) INTO max_score FROM students;
RETURN max_score;
END;
```
查询学生最低分数的存储函数:
```sql
CREATE FUNCTION get_min_score() RETURNS INT
BEGIN
DECLARE min_score INT;
SELECT MIN(total_score) INTO min_score FROM students;
RETURN min_score;
END;
```
查询学生总成绩排名的存储函数:
```sql
CREATE FUNCTION get_rank(id INT) RETURNS INT
BEGIN
DECLARE rank INT;
SELECT COUNT(*) INTO rank FROM students WHERE total_score > (SELECT total_score FROM students WHERE id = id);
SET rank = rank + 1;
UPDATE students SET rank = rank WHERE id = id;
RETURN rank;
END;
```
创建学生信息表的存储过程:
```sql
CREATE PROCEDURE create_students_table()
BEGIN
CREATE TABLE students (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50),
score1 INT,
score2 INT,
score3 INT,
total_score INT,
rank INT
);
END;
```
插入学生信息的存储过程:
```sql
CREATE PROCEDURE insert_student(name VARCHAR(50), score1 INT, score2 INT, score3 INT)
BEGIN
DECLARE total INT;
DECLARE avg FLOAT;
INSERT INTO students (name, score1, score2, score3) VALUES (name, score1, score2, score3);
SELECT LAST_INSERT_ID() INTO @id;
SET total = calculate_total_score(score1, score2, score3);
SET avg = calculate_avg_score(score1, score2, score3);
UPDATE students SET total_score = total, rank = 0 WHERE id = @id;
END;
```
删除学生信息的存储过程:
```sql
CREATE PROCEDURE delete_student(id INT)
BEGIN
DELETE FROM students WHERE id = id;
END;
```
更新学生信息的存储过程:
```sql
CREATE PROCEDURE update_student(id INT, name VARCHAR(50), score1 INT, score2 INT, score3 INT)
BEGIN
DECLARE total INT;
DECLARE avg FLOAT;
SET total = calculate_total_score(score1, score2, score3);
SET avg = calculate_avg_score(score1, score2, score3);
UPDATE students SET name = name, score1 = score1, score2 = score2, score3 = score3, total_score = total, rank = 0 WHERE id = id;
END;
```
查询学生信息的存储过程:
```sql
CREATE PROCEDURE get_students()
BEGIN
SELECT * FROM students;
END;
```
运行结果:
```sql
-- 创建表
CALL create_students_table();
-- 插入数据
CALL insert_student('张三', 80, 90, 85);
CALL insert_student('李四', 75, 85, 90);
CALL insert_student('王五', 90, 88, 92);
-- 查询数据
CALL get_students();
-- +----+--------+--------+--------+--------+-------------+------+
-- | id | name | score1 | score2 | score3 | total_score | rank |
-- +----+--------+--------+--------+--------+-------------+------+
-- | 1 | 张三 | 80 | 90 | 85 | 255 | 1 |
-- | 2 | 李四 | 75 | 85 | 90 | 250 | 2 |
-- | 3 | 王五 | 90 | 88 | 92 | 270 | 1 |
-- +----+--------+--------+--------+--------+-------------+------+
-- 查询最高分数
SELECT get_max_score(); -- 270
-- 查询最低分数
SELECT get_min_score(); -- 250
-- 查询排名
SELECT get_rank(1); -- 2
-- 删除学生信息
CALL delete_student(2);
-- 更新学生信息
CALL update_student(1, '张三三', 85, 95, 90);
-- 查询数据
CALL get_students();
-- +----+--------+--------+--------+--------+-------------+------+
-- | id | name | score1 | score2 | score3 | total_score | rank |
-- +----+--------+--------+--------+--------+-------------+------+
-- | 1 | 张三三 | 85 | 95 | 90 | 270 | 1 |
-- | 3 | 王五 | 90 | 88 | 92 | 270 | 2 |
-- +----+--------+--------+--------+--------+-------------+------+
```
相关推荐
![pdf](https://img-home.csdnimg.cn/images/20210720083512.png)
![doc](https://img-home.csdnimg.cn/images/20210720083327.png)
![docx](https://img-home.csdnimg.cn/images/20210720083331.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)