基于WEB的大学生运动会软件系统的设计与实现，创建五张表，对每张表添加至少五条数据，并对每张表进行视图创建，对每张表进行索引创建，进行五次多表连接查询，创建五个存储过程，创建五个存储函数并进行调用。使用sql语句

五张表的创建：

-- 创建运动员表
CREATE TABLE athlete (
  id INT PRIMARY KEY,
  name VARCHAR(20) NOT NULL,
  age INT NOT NULL,
  gender VARCHAR(4) NOT NULL,
  college VARCHAR(50) NOT NULL,
  phone VARCHAR(20) NOT NULL
);

-- 创建比赛项目表
CREATE TABLE event (
  id INT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  start_time DATETIME NOT NULL,
  end_time DATETIME NOT NULL,
  location VARCHAR(100) NOT NULL
);

-- 创建比赛成绩表
CREATE TABLE result (
  id INT PRIMARY KEY,
  athlete_id INT NOT NULL,
  event_id INT NOT NULL,
  score FLOAT NOT NULL,
  FOREIGN KEY (athlete_id) REFERENCES athlete(id),
  FOREIGN KEY (event_id) REFERENCES event(id)
);

-- 创建裁判表
CREATE TABLE referee (
  id INT PRIMARY KEY,
  name VARCHAR(20) NOT NULL,
  gender VARCHAR(4) NOT NULL,
  phone VARCHAR(20) NOT NULL
);

-- 创建比赛评分表
CREATE TABLE score (
  id INT PRIMARY KEY,
  referee_id INT NOT NULL,
  event_id INT NOT NULL,
  score FLOAT NOT NULL,
  FOREIGN KEY (referee_id) REFERENCES referee(id),
  FOREIGN KEY (event_id) REFERENCES event(id)
);

五条数据的插入：

-- 插入运动员数据
INSERT INTO athlete (id, name, age, gender, college, phone)
VALUES 
  (1, '张三', 20, '男', 'XX大学', '13812345678'),
  (2, '李四', 21, '女', 'YY大学', '13987654321'),
  (3, '王五', 19, '男', 'ZZ大学', '13711112222'),
  (4, '赵六', 22, '女', 'XX大学', '13344445555'),
  (5, '钱七', 20, '男', 'YY大学', '13277778888');

-- 插入比赛项目数据
INSERT INTO event (id, name, start_time, end_time, location)
VALUES 
  (1, '100米赛跑', '2022-06-01 08:00:00', '2022-06-01 10:00:00', '田径场'),
  (2, '200米赛跑', '2022-06-02 08:00:00', '2022-06-02 10:00:00', '田径场'),
  (3, '跳高比赛', '2022-06-03 08:00:00', '2022-06-03 10:00:00', '田径场'),
  (4, '篮球比赛', '2022-06-04 08:00:00', '2022-06-04 10:00:00', '体育馆'),
  (5, '足球比赛', '2022-06-05 08:00:00', '2022-06-05 10:00:00', '体育场');

-- 插入比赛成绩数据
INSERT INTO result (id, athlete_id, event_id, score)
VALUES 
  (1, 1, 1, 10.2),
  (2, 2, 1, 11.3),
  (3, 3, 1, 10.8),
  (4, 4, 1, 10.5),
  (5, 5, 1, 10.7);

-- 插入裁判数据
INSERT INTO referee (id, name, gender, phone)
VALUES 
  (1, '张三', '男', '13812345678'),
  (2, '李四', '女', '13987654321'),
  (3, '王五', '男', '13711112222'),
  (4, '赵六', '女', '13344445555'),
  (5, '钱七', '男', '13277778888');

-- 插入比赛评分数据
INSERT INTO score (id, referee_id, event_id, score)
VALUES 
  (1, 1, 1, 8.5),
  (2, 2, 1, 9.0),
  (3, 3, 1, 8.8),
  (4, 4, 1, 8.6),
  (5, 5, 1, 8.9);

五个视图的创建：

-- 创建运动员视图
CREATE VIEW athlete_view AS
SELECT id, name, age, gender, college
FROM athlete;

-- 创建比赛项目视图
CREATE VIEW event_view AS
SELECT id, name, start_time, end_time, location
FROM event;

-- 创建成绩视图
CREATE VIEW result_view AS
SELECT r.id, a.name AS athlete_name, e.name AS event_name, score
FROM result r
JOIN athlete a ON r.athlete_id = a.id
JOIN event e ON r.event_id = e.id;

-- 创建裁判视图
CREATE VIEW referee_view AS
SELECT id, name, gender
FROM referee;

-- 创建评分视图
CREATE VIEW score_view AS
SELECT s.id, r.id AS result_id, ref.name AS referee_name, e.name AS event_name, score
FROM score s
JOIN referee ref ON s.referee_id = ref.id
JOIN event e ON s.event_id = e.id
JOIN result r ON s.event_id = r.event_id AND s.referee_id = r.athlete_id;

五个索引的创建：

-- 创建运动员姓名索引
CREATE INDEX idx_athlete_name ON athlete (name);

-- 创建比赛项目名称索引
CREATE INDEX idx_event_name ON event (name);

-- 创建成绩分数索引
CREATE INDEX idx_result_score ON result (score);

-- 创建裁判姓名索引
CREATE INDEX idx_referee_name ON referee (name);

-- 创建评分分数索引
CREATE INDEX idx_score_score ON score (score);

五次多表连接查询：

-- 查询所有运动员的姓名和所在学校以及参加的比赛项目名称
SELECT a.name, a.college, e.name AS event_name
FROM athlete a
JOIN result r ON a.id = r.athlete_id
JOIN event e ON r.event_id = e.id;

-- 查询所有比赛项目的名称和举办地点以及参加的运动员姓名
SELECT e.name, e.location, a.name AS athlete_name
FROM event e
JOIN result r ON e.id = r.event_id
JOIN athlete a ON r.athlete_id = a.id;

-- 查询所有比赛成绩的运动员姓名和比赛项目名称以及获得的分数
SELECT a.name AS athlete_name, e.name AS event_name, r.score
FROM result r
JOIN athlete a ON r.athlete_id = a.id
JOIN event e ON r.event_id = e.id;

-- 查询所有裁判评分的比赛项目名称和对应的评分分数以及评分裁判姓名
SELECT e.name AS event_name, s.score, ref.name AS referee_name
FROM score s
JOIN event e ON s.event_id = e.id
JOIN referee ref ON s.referee_id = ref.id;

-- 查询所有参加比赛的大学名称和参加的比赛项目名称以及获得的成绩分数
SELECT a.college, e.name AS event_name, r.score
FROM result r
JOIN athlete a ON r.athlete_id = a.id
JOIN event e ON r.event_id = e.id;

五个存储过程的创建：

-- 创建查询所有运动员的存储过程
CREATE PROCEDURE get_athlete()
BEGIN
  SELECT * FROM athlete;
END;

-- 创建查询所有比赛项目的存储过程
CREATE PROCEDURE get_event()
BEGIN
  SELECT * FROM event;
END;

-- 创建查询所有比赛成绩的存储过程
CREATE PROCEDURE get_result()
BEGIN
  SELECT * FROM result;
END;

-- 创建查询所有裁判的存储过程
CREATE PROCEDURE get_referee()
BEGIN
  SELECT * FROM referee;
END;

-- 创建查询所有评分的存储过程
CREATE PROCEDURE get_score()
BEGIN
  SELECT * FROM score;
END;

五个存储函数的创建和调用：

-- 创建计算每个运动员参加比赛项目数的函数
CREATE FUNCTION athlete_event_count(athlete_id INT)
RETURNS INT
BEGIN
  DECLARE event_count INT;
  SELECT COUNT(*) INTO event_count
  FROM result
  WHERE athlete_id = athlete_id;
  RETURN event_count;
END;

-- 创建计算一个比赛项目的平均分数的函数
CREATE FUNCTION event_avg_score(event_id INT)
RETURNS FLOAT
BEGIN
  DECLARE avg_score FLOAT;
  SELECT AVG(score) INTO avg_score
  FROM result
  WHERE event_id = event_id;
  RETURN avg_score;
END;

-- 创建计算一个裁判评分的平均分数的函数
CREATE FUNCTION referee_avg_score(referee_id INT)
RETURNS FLOAT
BEGIN
  DECLARE avg_score FLOAT;
  SELECT AVG(score) INTO avg_score
  FROM score
  WHERE referee_id = referee_id;
  RETURN avg_score;
END;

-- 创建计算一个大学参加比赛次数的函数
CREATE FUNCTION college_event_count(college_name VARCHAR(50))
RETURNS INT
BEGIN
  DECLARE event_count INT;
  SELECT COUNT(*) INTO event_count
  FROM result r
  JOIN athlete a ON r.athlete_id = a.id
  WHERE a.college = college_name;
  RETURN event_count;
END;

-- 创建计算一个运动员获得总分数的函数
CREATE FUNCTION athlete_total_score(athlete_id INT)
RETURNS FLOAT
BEGIN
  DECLARE total_score FLOAT;
  SELECT SUM(score) INTO total_score
  FROM result
  WHERE athlete_id = athlete_id;
  RETURN total_score;
END;

-- 调用示例
SELECT athlete_event_count(1); -- 返回运动员1参加的比赛项目数
SELECT event_avg_score(1); -- 返回比赛项目1的平均分数
SELECT referee_avg_score(1); -- 返回裁判1的平均评分分数
SELECT college_event_count('XX大学'); -- 返回XX大学参加比赛的次数
SELECT athlete_total_score(1); -- 返回运动员1获得的总分数