目录

1.实验目的

2.实验内容和要求

3.实验步骤

1.实验目的

掌握数据库设计的过程和方法。

2.实验内容和要求

每人请从下面的题目当中选择一个，进行数据库设计，通过网络平台或图书馆查找相关文献进行需求分析，按照下面的实验步骤完成设计要求。

大学生竞赛管理系统设计

学科竞赛已经作为学生创新活动的重要组成部分，一个专业的学生可以参加多个学科竞赛。竞赛分为不同等级，按照重要性可以分为 A 类、B 类和 C 类，学生可以多人组队参加一个比赛，每个队可以有多个指导教师，竞赛成绩按照获奖级别有国家级和省级，分为一等奖、二等奖、三等奖，每种竞赛每年都有相对固定的比赛时间、名称和主办机构。试设计一个大学生竞赛管理系统，能够完成对学生竞赛的管理，可以实现统计高校各个学院各个专业某个时间段内竞赛的参加和获奖情况，也可以统计每个同学或每个年级某个时间段内竞赛的参加和获奖情况。

3.实验步骤

（1）根据所选题目进行系统需求分析和系统设计，画出系统的 E-R 图，给出实体或联系的属性，标明联系的种类；

（2）把E-R图转换为关系模式；

以下是包含外键约束的完整关系模式设计：

1. 学生表 (Student)

  • 主键: StudentID
  • 属性:
    • StudentID (学号, 主键)
    • SName (姓名)
    • Gender (性别)
    • BirthDate (出生日期)
    • EnrollmentYear (入学年份)
    • Major (专业)
    • College (学院)
    • TeamID (所属团队, 外键，关联到 Team.TeamID)

2. 竞赛表 (Competition)

  • 主键: CompetitionID
  • 属性:
    • CompetitionID (竞赛ID, 主键)
    • Competition Name (竞赛名称)
    • Category (类别)
    • CompetitionDate (竞赛日期)
    • Year (年份)
    • Organizer (主办机构)

3. 团队表 (Team)

  • 主键: TeamID
  • 属性:
    • TeamID (团队ID, 主键)
    • TeamName (团队名称)
    • ParticipationYear (参赛年份)
    • CompetitionID(竞赛ID, 外键，关联到Competition.CompetitionID)
    • InstructorID (指导教师ID, 外键，关联到 Instructor.InstructorID)

4. 指导教师表 (Instructor)

  • 主键: InstructorID
  • 属性:
    • InstructorID (教师ID, 主键)
    • Instructor Name (指导教师姓名)
    • Department (所属部门)
    • TeamID (指导团队ID, 外键，关联到 Team.TeamID)

5. 竞赛结果表 (CompetitionResult)

  • 主键: (TeamID, CompetitionID)
  • 属性:
    • Competition Name (竞赛名称,外键，关联到Competition. Competition Nnme)
    • TeamID (团队ID, 外键，关联到 Team.TeamID)
    • CompetitionID (竞赛ID, 外键，关联到 Competition.CompetitionID)
    • AwardLevel (获奖等级)
    • AwardRank (获奖级别)
    • AwardDate (获奖日期)

（3）根据关系规范理论进行数据库的逻辑设计，给出数据库表的设计，数据库表设计格式参照下面：

表格 1学生表（student）

字段名

中文含义

类型

约束

备注

StudentID

学号

char(6)

主键

SName

姓名

varchar(20)

not null

Gender

性别

char(1)

not null

男/女

BirthDate

出生日期

date

EnrollmentYear

入学年份

year

not null

Major

专业

varchar(20)

College

学院

varchar(50)

TeamID

所属团队

char(6)

外键

关联到 Team.TeamID

表格 2 竞赛表（Competition）

字段名

中文含义

类型

约束

备注

CompetitionID

竞赛ID

char(6)

主键

CompetitionName

竞赛名称

varchar(50)

not null

Category

类别

varchar(20)

not null

A类/B类/C类

CompetitionDate

竞赛日期

date

not null

CompetitionYear

年份

year

not null

Organizer

主办机构

varchar(50)

表格 3 团队表(Team)

字段名

中文含义

类型

约束

备注

TeamID

团队ID

char(6)

主键

TeamName

团队名称

varchar(50)

not null

ParticipationYear

参赛年份

year

not null

CompetitionID

竞赛ID

char(6)

外键

关联到 Competition.CompetitionID

InstructorID

指导教师ID

char(6)

外键

关联到 Instructor.InstructorID

表格 4 指导教师表（Instructor）

字段名

中文含义

类型

约束

备注

InstructorID

教师ID

char(6)

主键

InstructorName

姓名

varchar(50)

not null

Department

所属部门

varchar(50)

not null

TeamID

指导团队ID

char(6)

外键

关联到 Team.TeamID

表格 5 竞赛获奖表（Competition Award）

字段名

中文含义

类型

约束

备注

TeamID

团队ID

char(6)

主键

关联到 Team.TeamID

CompetitionID

竞赛ID

char(6)

外键

关联到 Competition.CompetitionID

CompetitionName

竞赛名称

varchar(50)

外键

关联到Competition. CompetitionName

AwardLevel

获奖等级

varchar(20)

not null

一等奖/二等奖/三等奖

AwardRank

获奖级别

varchar(20)

国家级/省级

AwardDate

获奖日期

date

（4）对给定的逻辑数据模型选取一个最适合应用环境的物理结构，进行数据库的物理设计，考虑不同的 DBMS 选型，进行设计方案对比，对物理结构进行评价，评价的重点是时间和空间效率；

  1) MySQL

  • 优点：
    • InnoDB 存储引擎支持事务和外键。
    • 高效的查询优化器适合读密集型应用。
    • 提供分区表功能，支持大规模数据管理。
    • 轻量级，社区支持丰富，易于部署。
  • 缺点：
    • 对复杂查询（如大规模 JOIN 操作）性能可能不及商业 DBMS。

   2) PostgreSQL

  • 优点：
    • 提供丰富的数据类型和索引选项。
    • 优化复杂查询性能，适合高复杂性应用。
    • 支持高级功能，如窗口函数和并行查询。
  • 缺点：
    • 性能调优复杂，学习曲线较高。

  3) SQL Server

  • 优点：
    • 企业级功能丰富，如高级分区、加密和备份。
    • 对事务和高并发场景优化良好。
    • 与微软生态系统集成良好。
  • 缺点：
    • 授权费用较高。
    • 跨平台支持有限。

时间与空间效率评价

  • 时间效率：
    • MySQL 在中小型数据库环境中查询速度快，尤其是对于简单查询和索引优化场景。
    • PostgreSQL 在复杂查询中表现更佳。
    • SQL Server 在事务密集型应用中效率最高。
  • 空间效率：
    • MySQL 占用空间较小，适合中小型数据量场景。
    • PostgreSQL 和 SQL Server 提供了更高的存储优化功能，但相对更占用空间。

 推荐方案

在大学生竞赛管理系统中，数据量适中，主要应用场景包括多表关联查询、统计和报表生成，因此推荐使用 MySQL。它具有以下优势：

  • 易于部署和维护，适合教育领域开发需求。
  • 索引和分区优化能很好地支持时间段统计查询。
  • 社区版成本低，满足预算有限的需求。

数据库物理设计

(1) 逻辑模型转物理模型的优化

基于大学生竞赛管理系统的逻辑数据模型，以下优化措施将用于物理设计：

  1. 表结构设计：
    • 所有表字段设置合适的数据类型，避免过度分配空间。
    • 为常用查询字段（如 CompetitionID, TeamID, StudentID）创建索引。
    • 使用分区表（如基于年份 Year 分区）提升查询效率。
  2. 完整性约束：
    • 实现实体完整性：为主键和外键字段添加约束。
    • 实现参照完整性：确保外键引用约束的定义。
    • 实现用户定义完整性：通过 CHECK 约束限制字段值范围。
  3. 索引设计：
    • 单列索引：如 Competition.CompetitionDate，加速时间范围查询。
    • 复合索引：如 CompetitionResult(TeamID, CompetitionID)，提升多条件查询性能。
    • 唯一索引：为 Student.StudentID 和 Competition.CompetitionID 确保数据唯一性。
  4. 存储优化：
    • 合理选择存储引擎：使用 MySQL 的 InnoDB 引擎支持事务和外键。
    • 归档冷数据：对历史数据（如 5 年以上的竞赛记录）归档到独立的存储表。
  5. 数据分区：
    • 基于 Year 对 Competition 和 CompetitionResult 表进行分区，提高按年份统计的性能。

(2) SQL 数据库表设计

以下是物理设计的表定义：

CREATE TABLE Student (

    StudentID CHAR(6) PRIMARY KEY,

    SName VARCHAR(20) NOT NULL,

    Gender CHAR(1) NOT NULL CHECK (Gender IN ('男', '女')),

    BirthDate DATE,

    EnrollmentYear YEAR NOT NULL,

    Major VARCHAR(20),

    College VARCHAR(50),

    TeamID CHAR(6),

    FOREIGN KEY (TeamID) REFERENCES Team(TeamID)

);

CREATE TABLE Competition (

    CompetitionID CHAR(6) PRIMARY KEY,

    CompetitionName VARCHAR(50) NOT NULL,

    Category VARCHAR(20) NOT NULL CHECK (Category IN ('A类', 'B类', 'C类')),

    CompetitionDate DATE NOT NULL,

    CompetitionYear YEAR NOT NULL,

    Organizer VARCHAR(50)

);

CREATE TABLE Team (

    TeamID CHAR(6) PRIMARY KEY,

    TeamName VARCHAR(50) NOT NULL,

    ParticipationYear YEAR NOT NULL,

    CompetitionID CHAR(6),

    InstructorID CHAR(6),

    FOREIGN KEY (CompetitionID) REFERENCES Competition(CompetitionID),

    FOREIGN KEY (InstructorID) REFERENCES Instructor(InstructorID)

);

CREATE TABLE Instructor (

    InstructorID CHAR(6) PRIMARY KEY,

    InstructorName VARCHAR(50) NOT NULL,

    Department VARCHAR(50) NOT NULL,

    TeamID CHAR(6),

    FOREIGN KEY (TeamID) REFERENCES Team(TeamID)

);

CREATE TABLE CompetitionAward(

    CompetitionName varchar(50),

    TeamID CHAR(6),

    CompetitionID CHAR(6),

    AwardLevel VARCHAR(20) NOT NULL CHECK (AwardLevel IN ('一等奖', '二等奖', '三等奖')),

    AwardRank VARCHAR(20) CHECK (AwardRank IN ('国家级', '省级')),

    AwardDate DATE,

    PRIMARY KEY (TeamID, CompetitionID),

    FOREIGN KEY (TeamID) REFERENCES Team(TeamID),

    FOREIGN KEY (CompetitionID) REFERENCES Competition(CompetitionID)

    FOREIGN KEY (CompetitionName) REFERENCES Competition(CompetitionName)

);

（5）在My SQL数据库中创建数据库并使用 SQL 语句创建相应的数据库表；

（6）通过导入文件的方式在数据库表中输入若干条测试数据，也可以直接插入若干条测试数据；

SQL语句：

-- 插入 Competition 表数据

INSERT INTO Competition (CompetitionID, CompetitionName, Category, CompetitionDate, CompetitionYear, Organizer)

VALUES

('C001', '数学竞赛', 'A类', '2024-05-15', 2024, '数学系'),

('C002', '编程大赛', 'B类', '2024-06-20', 2024, '计算机学院'),

('C003', '化学实验竞赛', 'C类', '2024-07-10', 2024, '化学系'),

('C004', '物理挑战赛', 'A类', '2024-08-15', 2024, '物理系'),

('C005', '英语演讲比赛', 'B类', '2024-09-25', 2024, '外国语学院');

-- 插入 Team 表数据

INSERT INTO Team (TeamID, TeamName, ParticipationYear, CompetitionID, InstructorID)

VALUES

('T001', 'Alpha Team', 2024, 'C001', 'I001'),

('T002', 'Beta Team', 2024, 'C002', 'I002'),

('T003', 'Gamma Team', 2024, 'C003', 'I003'),

('T004', 'Delta Team', 2024, 'C004', 'I004'),

('T005', 'Omega Team', 2024, 'C005', 'I005');

-- 插入 Instructor 表数据

INSERT INTO Instructor (InstructorID, InstructorName, Department, TeamID)

VALUES

('I001', '张教授', '数学系', 'T001'),

('I002', '李教授', '计算机学院', 'T002'),

('I003', '王教授', '化学系', 'T003'),

('I004', '赵教授', '物理系', 'T004'),

('I005', '陈教授', '外国语学院', 'T005');

-- 插入 Student 表数据

INSERT INTO Student (StudentID, SName, Gender, BirthDate, EnrollmentYear, Major, College, TeamID)

VALUES

('S001', '小明', '男', '2003-03-12', 2022, '数学', '理学院', 'T001'),

('S002', '小红', '女', '2003-07-25', 2022, '计算机科学', '智能学院', 'T001'),

('S003', '小刚', '男', '2002-11-10', 2021, '化学', '化工学院', 'T001'),

('S004', '小丽', '女', '2004-05-20', 2023, '物理', '理学院', 'T002'),

('S005', '小芳', '女', '2003-12-30', 2020, '英语', '外国语学院', 'T002');

('S006', '小杰', '男', '2003-02-15', 2023, '数学', '理学院', 'T002');

('S007', '小婧', '女', '2003-06-18', 2023, '物理', '理学院', 'T003');

('S008', '小刚', '男', '2002-08-22', 2022, '化学', '化工学院', 'T003');

('S009', '小翠', '女', '2000-11-30', 2021, '英语', '外国语学院', 'T003');

('S010', '小东', '男', '2003-07-10', 2020, '计算机科学', '智能学院', 'T004');

('S011', '小敏', '女', '2002-12-20', 2023, '化学', '化工学院', 'T004');

('S012', '小辉', '男', '2004-07-13', 2022, '化学', '化工学院', 'T004');

('S013', '小龙', '男', '2003-09-18', 2023, '物理', '理学院', 'T005');

('S014', '小佳', '女', '2003-12-12', 2020, '英语', '外国语学院', 'T005');

('S015', '小鹏', '男', '2003-08-15', 2021, '数学', '理学院', 'T005');

-- 插入 CompetitionAward 表数据

INSERT INTO CompetitionAward (TeamID, CompetitionID, CompetitionName, AwardLevel, AwardRank, AwardDate)

VALUES

('T001', 'C001', '数学竞赛', '一等奖', '国家级', '2024-05-16'),

('T002', 'C002', '编程大赛', '二等奖', '省级', '2024-06-21'),

('T003', 'C003', '化学实验竞赛', '三等奖', '省级', '2024-07-11'),

('T004', 'C004', '物理挑战赛', '二等奖', '国家级', '2024-08-16'),

('T005', 'C005', '英语演讲比赛', '一等奖', '国家级', '2024-09-26');

（7）自行设计若干问题并编写 SQL 语句完成对所提问题的实现，SQL 语句要求至少12 条，自定义功能要求，并对 SQL 语句运行，要求包括下面的语句：create table（要求包含实体完整性、参照完整性和用户自定义完整性的定义）、create index、 create view、 select 语句 (至少 5 条，要求把 from、where、group by、having、order by 等子句用上，并实现多表查询)、insert、 delete、update、grant、revoke 语句

1. 查询 2024年8月竞赛信息及参赛队伍成员

SQL语句：

      SELECT

    C.CompetitionName AS Competition,

    C.CompetitionDate AS Date,

    T.TeamName AS Team,

    S.SName AS Member

FROM

    Competition C

    JOIN Team T ON C.CompetitionID = T.CompetitionID

    JOIN Student S ON T.TeamID = S.TeamID

WHERE

    YEAR(C.CompetitionDate) = 2024

    AND MONTH(C.CompetitionDate) = 8

ORDER BY

    C.CompetitionDate, T.TeamName, S.StudentID;

、

2. 统计每个学院参加八月份竞赛的学生人数

SQL语句：

SELECT

    S.College AS College,

    COUNT(S.StudentID) AS StudentCount

FROM

    Competition C

    JOIN Team T ON C.CompetitionID = T.CompetitionID

    JOIN Student S ON T.TeamID = S.TeamID

WHERE

    YEAR(C.CompetitionDate) = 2024

    AND MONTH(C.CompetitionDate) = 8

GROUP BY

    S.College

ORDER BY

StudentCount DESC;

3. 统计同学参加七月竞赛的情况

SQL语句：

SELECT

    S.StudentID AS StudentID,

    S.SName AS StudentName,

    COUNT(C.CompetitionID) AS ParticipationCount

FROM

    Competition C

    JOIN Team T ON C.CompetitionID = T.CompetitionID

    JOIN Student S ON T.TeamID = S.TeamID

WHERE

    YEAR(C.CompetitionDate) = 2024

    AND MONTH(C.CompetitionDate) = 7

GROUP BY

    S.StudentID, S.SName

ORDER BY
ParticipationCount DESC, S.StudentID;

4. 统计每个年级2024年九月竞赛的获奖情况

SQL语句：

SELECT

    S.EnrollmentYear AS EnrollmentYear,

    A.AwardLevel AS AwardLevel,

    COUNT(A.AwardLevel) AS AwardCount

FROM

    Competition C

    JOIN Team T ON C.CompetitionID = T.CompetitionID

    JOIN Student S ON T.TeamID = S.TeamID

    JOIN CompetitionAward A ON T.TeamID = A.TeamID

WHERE

    YEAR(C.CompetitionDate) = 2024

    AND MONTH(C.CompetitionDate) = 9

GROUP BY

    S.EnrollmentYear, A.AwardLevel

ORDER BY

S.EnrollmentYear, A.AwardLevel;

5. 统计每个学生的参赛情况

SQL语句：

SELECT

    S.StudentID AS StudentID,

    S.SName AS StudentName,

    C.CompetitionName AS Competition,

    C.CompetitionDate AS CompetitionDate,

    T.TeamName AS Team

FROM

    Student S

    JOIN Team T ON S.TeamID = T.TeamID

    JOIN Competition C ON T.CompetitionID = C.CompetitionID

ORDER BY

S.StudentID, C.CompetitionDate;

6. 参照上述学生表的表结构，创建学生表(Student1) 时定义实体完整性（列级实体完整性）。

SQL语句：

 CREATE TABLE Student1 (

    StudentID CHAR(6) PRIMARY KEY,            

    SName VARCHAR(20) NOT NULL,                

    Gender CHAR(1) NOT NULL CHECK (Gender IN ('男', '女')), 

    BirthDate DATE,                            

    EnrollmentYear YEAR NOT NULL,              

    Major VARCHAR(20),                         

    College VARCHAR(50),                       

    TeamID CHAR(6),                            

    FOREIGN KEY (TeamID) REFERENCES Team(TeamID)

);

7.参照上述学生表的表结构，创建学生表(Student2) 时定义实体完整性（表级 实体完整性）。

SQL语句：

CREATE TABLE Student2 (

    StudentID CHAR(6),

    SName VARCHAR(20) NOT NULL,               

    Gender CHAR(1),

    BirthDate DATE,

    EnrollmentYear YEAR,

    Major VARCHAR(20),

    College VARCHAR(50),

    TeamID CHAR(6),

    CONSTRAINT PK_Student2 PRIMARY KEY (StudentID),            

    CONSTRAINT CK_Gender CHECK (Gender IN ('男', '女')),        

    CONSTRAINT FK_TeamID FOREIGN KEY (TeamID) REFERENCES Team(TeamID)

);

8.参照上述学生表的表结构，创建学生表(Student3) ，在创建表后再定义实体完整性    （用户自定义完整性的定义）

CREATE TABLE Student3 (

    StudentID CHAR(6),

    SName VARCHAR(20),

    Gender CHAR(1),

    BirthDate DATE,

    EnrollmentYear YEAR,

    Major VARCHAR(20),

    College VARCHAR(50),

    TeamID CHAR(6)

);

ALTER TABLE Student3

    ADD CONSTRAINT PK_Student3 PRIMARY KEY (StudentID);

ALTER TABLE Student3

    MODIFY SName VARCHAR(20) NOT NULL;

ALTER TABLE Student3

     ADD CONSTRAINT CK_Gender1 CHECK (Gender IN ('男', '女'));

ALTER TABLE Student3

    ADD CONSTRAINT FK_TeamID1 FOREIGN KEY (TeamID) REFERENCES Team(TeamID);

ALTER TABLE Student3
    ADD CONSTRAINT CK_EnrollmentYear CHECK (EnrollmentYear >= 2000 AND EnrollmentYear <= 2024);  -- 自定义完整性

9. 插入新的学生记录到 Student 表中

SQL语句：

INSERT INTO Student ( StudentID, SName, Gender, BirthDate, EnrollmentYear, Major, College, TeamID )

VALUES

  ( 'S101', '李华', '男', '2002-05-14', 2021, '计算机科学', '计算机学院', 'T002' ) ;

10. 删除学号为S101的学生

SQL语句：

DELETE FROM Student

WHERE StudentID = 'S101';

11. 更新学号为S002学生的专业信息

SQL语句：

UPDATE Student

SET Major = '数据科学'

WHERE StudentID = 'S002';

12. 为学生管理人员L创建用户表示，具有创建用户或角色的权利

SQL语句：

CREATE USER 'L' @'localhost' IDENTIFIED BY '123456';

GRANT CREATE USER,
CREATE ROLE ON *.* TO 'L' @'localhost' WITH GRANT OPTION;

13. 创建角色并分配权限，创建一个管理员角色，并给其分配对学生表的查询权限

SQL语句：

CREATE ROLE 'S_Administrator' @'localhost';

GRANT SELECT ON test1.student TO 'S_Administrator'@'localhost';

14. 给用户分配权限：给学生管理员授予管理员角色权限

SQL语句：

GRANT SELECT ON test1.student TO 'S_Administrator'@'localhost';

15. 验证权限分配的正确性：

①以L用户名登录数据库，验证学生管理员的权限。

在命令行中使用

mysql -u L -p

命令并输入密码 123456 登录 L 用户身份

激活用户：SET DEFAULT ROLE ALL TO 'L'@'localhost';

验证查询权限：SELECT * FROM student;


16.回收用户权限：REVOKE 'S_Administrator'@'localhost' FROM 'L'@'localhost';

以L身份登录数据库，使用数据库 test，报错，证明权限已被回收，L已经没有权限使用该数据库。

17.创建视图

SQL语句：

CREATE VIEW View_UpcomingCompetitions AS

SELECT

    CompetitionName AS Name,

    Category AS Type,

    CompetitionYear AS Year,

    Organizer AS Organizer,

    CompetitionDate AS Date

FROM

    Competition

ORDER BY

    CompetitionDate ASC;  -- 按竞赛日期升序排序


SELECT * FROM View_UpcomingCompetitions;

18.创建索引

SQL语句：

-- 创建单列索引

CREATE INDEX idx_CompetitionYear ON Competition (CompetitionYear);

-- 创建多列索引

CREATE INDEX idx_Category_Year ON Competition (Category, CompetitionYear);

-- 创建唯一索引

CREATE UNIQUE INDEX idx_Unique_CompetitionName ON Competition (CompetitionName);

-- 创建全文索引

CREATE FULLTEXT INDEX idx_FullText_Organizer ON Competition (Organizer);
SHOW INDEX FROM Competition;

19.  创建报表统计存储过程

存储过程统计每个学院在特定时间段内的参赛次数及获奖等级的分布。

SQL 语句：

DELIMITER //

CREATE PROCEDURE GetCollegeCompetitionStatsWithCursor(

    IN startDate DATE,

    IN endDate DATE

)

BEGIN

    DECLARE done INT DEFAULT 0;

    DECLARE collegeName VARCHAR(50);

    DECLARE participationCount INT DEFAULT 0;

    DECLARE firstPrizeCount INT DEFAULT 0;

    DECLARE secondPrizeCount INT DEFAULT 0;

    DECLARE thirdPrizeCount INT DEFAULT 0;

    -- 定义游标：查询所有学院的列表

    DECLARE college_cursor CURSOR FOR

        SELECT DISTINCT College

        FROM Student

        WHERE TeamID IN (

            SELECT TeamID

            FROM Team

            WHERE CompetitionID IN (

                SELECT CompetitionID

                FROM Competition

                WHERE CompetitionDate BETWEEN startDate AND endDate

            )

        );

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    -- 临时表，用于存储统计结果

    CREATE TEMPORARY TABLE TempStats (

        College VARCHAR(50),

        ParticipationCount INT DEFAULT 0,

        FirstPrizeCount INT DEFAULT 0,

        SecondPrizeCount INT DEFAULT 0,

        ThirdPrizeCount INT DEFAULT 0

    );

  

    OPEN college_cursor;

    read_loop: LOOP

        FETCH college_cursor INTO collegeName;

         IF done THEN

            LEAVE read_loop;

        END IF;

        -- 统计该学院的参赛次数

        SELECT COUNT(*) INTO participationCount

        FROM Student S

        JOIN Team T ON S.TeamID = T.TeamID

        JOIN Competition C ON T.CompetitionID = C.CompetitionID

        WHERE S.College = collegeName

          AND C.CompetitionDate BETWEEN startDate AND endDate;

        -- 统计该学院获得一等奖的次数

        SELECT COUNT(*) INTO firstPrizeCount

        FROM Student S

        JOIN Team T ON S.TeamID = T.TeamID

        JOIN CompetitionAward CA ON T.TeamID = CA.TeamID

        JOIN Competition C ON CA.CompetitionID = C.CompetitionID

        WHERE S.College = collegeName

          AND CA.AwardLevel = '一等奖'

          AND C.CompetitionDate BETWEEN startDate AND endDate;

        -- 统计该学院获得二等奖的次数

        SELECT COUNT(*) INTO secondPrizeCount

        FROM Student S

        JOIN Team T ON S.TeamID = T.TeamID

        JOIN CompetitionAward CA ON T.TeamID = CA.TeamID

        JOIN Competition C ON CA.CompetitionID = C.CompetitionID

        WHERE S.College = collegeName

          AND CA.AwardLevel = '二等奖'

          AND C.CompetitionDate BETWEEN startDate AND endDate;

        -- 统计该学院获得三等奖的次数

        SELECT COUNT(*) INTO thirdPrizeCount

        FROM Student S

        JOIN Team T ON S.TeamID = T.TeamID

        JOIN CompetitionAward CA ON T.TeamID = CA.TeamID

        JOIN Competition C ON CA.CompetitionID = C.CompetitionID

        WHERE S.College = collegeName

          AND CA.AwardLevel = '三等奖'

          AND C.CompetitionDate BETWEEN startDate AND endDate;

        -- 将统计结果插入到临时表中

        INSERT INTO TempStats (College, ParticipationCount, FirstPrizeCount, SecondPrizeCount, ThirdPrizeCount)

        VALUES (collegeName, participationCount, firstPrizeCount, secondPrizeCount, thirdPrizeCount);

    END LOOP;

    CLOSE college_cursor;

    SELECT * FROM TempStats;

    DROP TEMPORARY TABLE TempStats;

END//

DELIMITER ;

调用存储过程

查询 2024 年 5月到 2024 年 8月的报表：

CALL GetCollegeCompetitionStatsWithCursor('2024-05-01', '2024-08-31');

20.创建触发器：自动更新学院参赛信息统计表

触发器在学生新增或删除时，更新 StudentStatistics 表中的参赛次数统计。

-- 创建学院参赛统计信息表

CREATE TABLE StudentStatistics (

    College VARCHAR(50),

    TotalStudents INT DEFAULT 0,

    PRIMARY KEY (College)

);

-- 创建插入触发器

DELIMITER //

CREATE TRIGGER AfterStudentInsert

AFTER INSERT ON Student

FOR EACH ROW

BEGIN

    SET @exists_count = (

        SELECT COUNT(*)

        FROM StudentStatistics

        WHERE College = NEW.College

    );

    IF @exists_count = 0 THEN

        INSERT INTO StudentStatistics (College, TotalStudents)

        VALUES (NEW.College, 1);

    ELSE

             UPDATE StudentStatistics

        SET TotalStudents = TotalStudents + 1

        WHERE College = NEW.College;

    END IF;

END //

DELIMITER ;

-- 创建删除触发器

DELIMITER //

CREATE TRIGGER AfterStudentDelete

AFTER DELETE ON Student

FOR EACH ROW

BEGIN

    -- 更新统计表

    UPDATE StudentStatistics

    SET TotalStudents = TotalStudents - 1

    WHERE College = OLD.College;

END //

DELIMITER ;

-- 插入新学生

INSERT INTO Student (StudentID, SName, Gender, BirthDate, EnrollmentYear, Major, College, TeamID)

VALUES ('S201', '赵丽', '女', '2001-05-15', 2023, '物理', '理学院', 'T005');

-- 查看统计表

SELECT * FROM StudentStatistics;

-- 删除学生

DELETE FROM Student WHERE StudentID = 'S201';

-- 查看统计表

SELECT * FROM StudentStatistics;


插入后：


删除后：

（8）对于 select 语句，需要给出相应的数据查询结果的截图。

（9）对自定义的复杂的查询要求或复杂报表生成要求，以及复杂的自定义完整性要求，使用存储过程和触发器来实现，并进行实验验证，给出验证结果截图。

# 数据库 # oracle
Logo
腾讯云开发者社区

腾讯云面向开发者汇聚海量精品云计算使用和开发经验，营造开放的云计算技术生态圈。

更多推荐

终极指南：Flink SQL连接器版本管理从混乱到有序的升级之路

Apache Flink作为流处理领域的佼佼者，其SQL连接器的版本管理一直是开发者面临的核心挑战。本文将系统讲解Flink SQL连接器版本管理的最佳实践，帮助你轻松应对版本兼容性问题，实现从混乱到有序的升级之旅。## 连接器版本管理的常见痛点 😫在Flink应用开发中，连接器版本管理常常让开发者头疼不已。不同版本的连接器可能导致各种兼容性问题，例如API变更、功能差异甚至运行时错误。

avatar 腾讯云开发者社区

Elasticsearch复杂数据类型终极指南：从入门到精通

Elasticsearch作为功能强大的搜索引擎，支持多种复杂数据类型，让开发者能够灵活处理各种结构化和非结构化数据。本文将带你全面了解Elasticsearch中的复杂数据类型，从基础概念到实际应用，助你轻松掌握数据建模的核心技巧。## 内部对象：构建层级化数据结构在Elasticsearch中，对象类型（Object）是最基础的复杂数据类型之一，用于表示具有嵌套关系的数据。例如，我们可

avatar 腾讯云开发者社区

如何快速搭建Neon无服务器PostgreSQL：面向初学者的完整指南

Neon是一款革命性的无服务器PostgreSQL解决方案，它通过分离存储和计算层，实现了自动扩缩容、类代码式数据库分支以及零级扩展能力。本指南将帮助你从零开始搭建Neon开发环境，体验这款创新数据库的强大功能。## 准备工作：环境要求与依赖项在开始搭建Neon环境前，请确保你的系统满足以下要求：- Linux操作系统（推荐Ubuntu 20.04+或Debian 11+）- Git

avatar 腾讯云开发者社区