【Mysql实战 】学院大赛管理系统设计与实现

前言

学院大赛管理系统设计与实现
每年学院都举行若干大赛，像挑战杯、软件大赛等。现希望设计并开发一个系统来管理大赛相关的报名以及获奖情况信息。系统主要功能如下：
1、基本信息维护
（1）学生信息管理，包括学号、姓名、性别、身份证号、班级等。在这里可以录入新的学生信息，如果某学生的班级换了，也可从这里修改，学生毕业后，也可以从这里删除。修改信息时，需要先输入要修改的学生学号，然后把学生新的班级名称更新到数据库中。删除学生时，也要根据学生的学号来删除。
（2）教师信息管理，包括教师编号、教师姓名、职称、性别等。在这里可以录入新的教师信息，如果某教师的职称换了，也可从这里修改，教师调走或退休后，也可以从这里删除。修改信息时，需要先输入要修改的教师编号，然后把该教师新的职称更新到数据库中。删除教师时，也要根据教师的编号来删除。
（3）各类大赛信息，包括大赛名称、举办单位、大赛级别、报名时间、初赛时间、复赛时间等。例如：（‘齐鲁软件设计大赛’，‘省教育厅、省计算机协会’，‘省级’，‘每年5月中旬’，‘6月下旬’，‘10月中旬’）。在这里管理每个大赛的信息，有了新的比赛后，可以从这里添加到数据库里，某个大赛不再举办了，也可以删除，大赛的信息变了，也可以从这里修改，比如修改大赛的举办单位、比赛时间等。
2、业务管理
（1）团队管理，能够利用该功能进行组队，维护指导教师、组成员及成员分工信息，包括团队名称、年份、大赛名称、队长、组员、指导教师、状态等。报名时，要先输入团队名称、年份、大赛名称、队长、指导教师，保存后再选择输入各个成员，组员人数一般3-5人，也可以多于5个。注意，大赛名称要跟大赛信息表的数据一致，队长和组员要跟学生表的信息一致，指导教师要跟教师信息表的数据一致。状态包括：组队、放弃、完成作品、一等奖、二等奖、三等奖等。刚组队时录入的是‘组队’，根据实际紧张情况，可以修改为放弃、一等奖等其他状态。
（2）常用查询：可以根据团队名称、队长名称、参赛年份、大赛名称、大赛状态等条件来查询历年的参赛和获奖情况。查询结果包括：年份、团队名称、大赛名称、队长名称、大赛状态。
（3）按学生姓名查询，输入学生的姓名，可以查询该学生参与或主持的团队情况，无论该学生是队长还是组员，都能查出来。查询结果包括：学号、姓名、团队名称、年份、大赛名称、队长、指导教师、状态等。
（4）统计各类大赛的获奖情况，可以统计某一年的各类大赛获奖情况，也可以统计某个时间段内的所有获奖情况（包括一等奖、二等奖、三等奖）。统计结果按照状态（即奖项）来分组统计。统计条件为起始年份、终止年份、奖项等级（国家级、省级、厅级）、奖项名称，其中奖项名称如果不输入，则表示统计所有奖项，如果输入一等奖，则只统计一等奖的。统计结果参考格式如下（条件选择2015年-2016年、省级）：
省级 一等奖 3
省级 二等奖 8
省级 三等奖 10
省级 合计 21
（5）用户管理，这里管理使用该系统的所有人员信息，包括用户登录名、真实姓名、性别、身份证号、用户角色（管理员、教师、学生等）、初始密码。新进来用户需要从这里添加进数据库，如果某个用户调走了，可以删除这个用户，如果某个用户的角色变了，可以从这里修改他的角色信息。
（6）修改密码，根据用户名和输入的新密码，替换原来的密码。

实战项目

学院大赛管理系统设计与实现

1.系统概述

每年学院都举行若干大赛，像挑战杯、软件大赛等。现希望设计并开发一个系统来管理大赛相关的报名以及获奖情况信息。学生通过自行组队参加各类比赛，比赛后可以查看自己队伍的获奖信息。

1.1学生教师账号注册

学生教师通过该系统注册账号，并输入基本信息，得到该系统账号（一人一号）。

1.2学生组队

学生队长通过创建队伍，首先输入队伍基本信息，其次输入队员信息，邀请指导教师，并输入指导信息，学生组队成功（一个人可以创建多个队伍，也可以参加多个队伍）。

1.3队伍参赛

一个队伍只能通过查看大赛信息参加一个大赛。

1.4队伍获奖信息查询

学生老师可以根据队伍查询队伍获奖信息。

2.需求分析

2.1 数据流图

顶层数据流图:

0层数据流图:

2.2数据字典

1.数据项定义

数据项名称：学号
含义说明：标识一个学号，一个学生只有一个学号，一个学号对应一个学生
类型：字符型
长度：20
具体的学号编码规律如下：
学生的学号前四位是入学年份
紧接着两位代表专业，最后三位代表专业内学生代号
学号=入学年份十专业号十专业内学生代号
如学号20105026表示该学生2019年入学，软件测试专业，专业内代号26

数据项名称：身份证号
含义说明：一个用户只能有一个身份证号，每个用户的身份证号都不一样
类型：字符型
长度：20

数据项名称：教师编号
含义：一个教师只能有一个教师号，一个教师号对应一个教师
类型：字符型
长度：20

数据项名称：队伍编号
含义说明：一个队伍只能有一个队伍编号，一个队伍编号对应一个队伍，唯一标识
类型：字符型
长度：20
…

2.数据结构

用户（账号，密码，{用户类型，用户姓名，用户身份证号，用户性别}）
队伍信息（队伍名称，队伍状态，队长学号，队伍创建时间，队员，参与大赛，指导教师，{获奖类型，获奖时间，获奖名称，获奖级别}）
学生信息（学号，身份证号，姓名，性别，班级）
教师信息（教师编号，教师姓名，职称，性别，电话，就业指导率，毕业率）
…

3.数据流

数据流名称：参赛信息
含义说明：学生参加比赛的信息表
去向：审核参赛
数据流量：一份一次
组成：队伍编号+队伍名称+队伍状态+队长学号+参与大赛+指导教师+队员

数据流名称：获奖信息
含义说明：学生参加大赛获得的奖项
去向：学生查看
数据流量：
组成：团队编号+参加大赛+获奖类型+获奖时间+获奖名称+获奖级别+指导教师+队员

数据流名称：学生信息
含义说明：学生的基本信息
去向：学生组队
数据流量：
组成：学号+身份证号+姓名+性别+班级

数据流名称：教师信息
含义说明：教师的基本信息
去向：学生组队
数据流量：
组成：教师编号+教师姓名+职称+性别+电话+就业指导率+毕业率
…

4.数据存储

数据存储名称：学生信息表
含义说明：存放学生的基本信息
组成：学生
说明：

数据存储名称：教师信息表
含义说明：存放教师的基本信息
组成：教师
说明：

数据存储名称：大赛信息表
含义说明：存放大赛的基本信息
组成：大赛+大赛状态
说明：大赛状态是大赛当前的状态

数据存储名称：获奖信息表
含义说明：存放学生的获奖信息
组成：获奖
说明：
…

5.数据处理:

处理过程名称：队伍编号审核
输入：队伍编号
输出：认定合格的队伍编号
加工逻辑：根据队伍信息表和队伍编号，如果队伍编号在队伍信息表中存在并且没有被锁定，那么队伍编号是有效的队伍编号，否则是无效的队伍编号。
…

3.数据库概念结构设计

3.1 实体分析

用户:账号，密码
用户信息:用户账号，用户类型，用户姓名，用户身份证号，用户性别
队伍信息:队伍id，队伍名称，队伍状态，队长学号，队伍创建时间，参与大赛Id，指导教师id
队员信息:队伍编号，队员学号
学生信息:学号，身份证号，姓名，性别，班级
教师信息:教师编号，教师姓名，职称，性别，电话，就业指导率，毕业率
大赛信息:大赛id，大赛名称，组织单位，大赛等级，报名时间，初赛时间，复赛时间，举办年份，大赛状态
获奖信息:获奖id，团队id，大赛id，获奖类型，获奖时间，获奖名称，获奖级别

这些实体间的语义描述如下：
一个学生一个教师均有一个用户，一个用户包含一个用户信息，一个学生可以参加多个队伍，一个队伍有多个成员，一个队伍可参与一个比赛，一个比赛获得一个奖项。

3.2 数据库概念结构设计

4.数据库逻辑结构设计

4.1 关系模型

4.2表与视图的设计

5 数据库物理设计及实施

以下就是设计数据库的代码了：

---

– Table structure for Award

---

DROP TABLE IF EXISTS Award;
CREATE TABLE Award (
award_id int(255) NOT NULL AUTO_INCREMENT COMMENT ‘获奖编号’,
team_id int(255) DEFAULT NULL COMMENT ‘团队编号’,
award_competition_id int(255) DEFAULT NULL COMMENT ‘获奖大赛Id’,
award_type varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT ‘获奖类型’,
award_time year(4) DEFAULT NULL COMMENT ‘获奖时间’,
award_name varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT ‘获奖名称’,
award_level varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT ‘获奖级别’,
PRIMARY KEY (award_id),
KEY team_id (team_id),
KEY award_competition_id (award_competition_id),
CONSTRAINT Award_ibfk_1 FOREIGN KEY (team_id) REFERENCES Team (team_id) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT Award_ibfk_2 FOREIGN KEY (award_competition_id) REFERENCES Competition (Competitioninformation_id) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

---

– Records of Award

---

INSERT INTO Award VALUES (‘1’, ‘1’, ‘1’, ‘省级’, ‘2021’, ‘齐鲁软件设计大赛’, ‘一等奖’);
INSERT INTO Award VALUES (‘2’, ‘2’, ‘2’, ‘省级’, ‘2020’, ‘山东省软件设计大赛’, ‘二等奖’);

---

– Table structure for Competition

---

DROP TABLE IF EXISTS Competition;
CREATE TABLE Competition (
Competitioninformation_id int(255) NOT NULL AUTO_INCREMENT,
Competitioninformation_name varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`

Organizervarchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT '举办单位', Competition_levelvarchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT '大赛级别',

Registration_timevarchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '报名时间',

Preliminary_timevarchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '初赛时间',

Second_round_timevarchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '复赛时间', Year_of_competitiondate DEFAULT NULL COMMENT '参赛时间', Competition_type varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '大赛状态', PRIMARY KEY (Competitioninformation_id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

---

– Records of Competition

---

INSERT INTO Competition VALUES (‘1’, ‘齐鲁软件设计大赛’, ‘省教育厅、省计算机协会’, ‘省级’, ‘每年5月中旬’, ‘6月下旬’, ‘10月中旬’, ‘2020-05-01’, ‘完结’);
INSERT INTO Competition VALUES (‘2’, ‘山东省软件设计大赛’, ‘省教育厅’, ‘省级’, ‘每年5月中旬’, ‘6月下旬’, ‘10月中旬’, ‘2021-05-11’, ‘完结’);
INSERT INTO Competition VALUES (‘3’, ‘中国软件杯’, ‘中央教育厅’, ‘国家级’, ‘每年4月中旬’, ‘7月中旬’, ‘11月下旬’, ‘2021-04-23’, ‘完结’);
INSERT INTO Competition VALUES (‘4’, ‘山东省数学建模大赛’, ‘山东省数学建模协会’, ‘省级’, ‘每年5月下旬’, ‘7月中旬’, ‘8月下旬’, ‘2021-05-21’, ‘报名阶段’);
INSERT INTO Competition VALUES (‘5’, ‘互联网+创新创业大赛’, ‘山东省’, ‘省级’, ‘每年5月下旬’, ‘7月中旬’, ‘11月中旬’, ‘2021-05-27’, ‘报名阶段’);
INSERT INTO Competition VALUES (‘6’, ‘美国数学建模’, ‘美国’, ‘国家级’, ‘每年5月下旬’, ‘7月中旬’, ‘8月上旬’, ‘2021-05-12’, ‘报名阶段’);
INSERT INTO Competition VALUES (‘7’, ‘全国大学生英语竞赛’, ‘中央教育厅’, ‘国家级’, ‘每年4月中旬’, ‘5月下旬’, ‘7月上旬’, ‘2021-04-14’, ‘复赛阶段’);
INSERT INTO Competition VALUES (‘8’, ‘大学生服务外包大赛’, ‘南京大学承办’, ‘国家级’, ‘每年11月中旬’, ‘次年5月中旬’, ‘次年6月下旬’, ‘2020-11-19’, ‘复赛阶段’);
INSERT INTO Competition VALUES (‘9’, ‘蓝桥杯’, ‘ACM协会’, ‘省级’, ‘每年3月中旬’, ‘5月上旬’, ‘5月中旬’, ‘2021-03-18’, ‘完结’);
INSERT INTO Competition VALUES (‘10’, ‘全国大学生计算机能力挑战赛’, ‘计算机协会’, ‘国家级’, ‘每年9月中旬’, ‘10月中旬’, ‘12月下旬’, ‘2020-10-16’, ‘完结’);

---

– Table structure for Student

---

DROP TABLE IF EXISTS Student;
CREATE TABLE Student (
sno int(255) NOT NULL COMMENT ‘学号’,
sid_card varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT ‘身份证号’,
sname varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT ‘姓名’,
ssex varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT ‘性别’,
sclass varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT ‘班级’,
PRIMARY KEY (sno),
KEY sname (sname),
KEY sno (sno)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

---

– Records of Student

---

INSERT INTO Student VALUES (‘1’, ‘37142720051110740X’, ‘梁恩硕’, ‘男’, ‘软测191’);
INSERT INTO Student VALUES (‘2’, ‘371428200011187511’, ‘李贺奥’, ‘男’, ‘软测191’);
INSERT INTO Student VALUES (‘3’, ‘371428200011187512’, ‘王杰’, ‘男’, ‘软测191’);
INSERT INTO Student VALUES (‘4’, ‘371428200011187513’, ‘王博涛’, ‘男’, ‘软测191’);
INSERT INTO Student VALUES (‘5’, ‘371428200011187514’, ‘李子铭’, ‘男’, ‘软件193’);
INSERT INTO Student VALUES (‘6’, ‘371428200011187515’, ‘史善力’, ‘男’, ‘软测171’);
INSERT INTO Student VALUES (‘7’, ‘371428200011187516’, ‘陈英杰’, ‘男’, ‘软测191’);
INSERT INTO Student VALUES (‘8’, ‘371428200011187517’, ‘魏永超’, ‘男’, ‘软测191’);
INSERT INTO Student VALUES (‘9’, ‘371428200011187518’, ‘兰天增’, ‘男’, ‘软测191’);
INSERT INTO Student VALUES (‘10’, ‘371428200011187519’, ‘兰兰1’, ‘男’, ‘软测191’);
INSERT INTO Student VALUES (‘11’, ‘371428200011187520’, ‘兰兰2’, ‘男’, ‘软测194’);

---

– Table structure for Teacher

---

DROP TABLE IF EXISTS Teacher;
CREATE TABLE Teacher (
tno int(255) NOT NULL COMMENT ‘教师编号’,
tname varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT ‘教师姓名’,
ttitle varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT ‘教师职务’,
tsex varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT ‘教师性别’,
tphone int(20) DEFAULT NULL,
tjiuye char(10) COLLATE utf8_unicode_ci DEFAULT NULL,
tbiye char(10) COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY (tno),
KEY tname (tname)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

---

– Records of Teacher

---

INSERT INTO Teacher VALUES (‘1’, ‘邢国波’, ‘副教授’, ‘男’, ‘1555555555’, ‘99%’, ‘99%’);
INSERT INTO Teacher VALUES (‘2’, ‘张三三’, ‘讲师’, ‘男’, ‘1555555554’, ‘99%’, ‘99%’);
INSERT INTO Teacher VALUES (‘3’, ‘李思思’, ‘助教’, ‘女’, ‘1555555578’, ‘99%’, ‘99%’);
INSERT INTO Teacher VALUES (‘4’, ‘王五五’, ‘讲师’, ‘男’, ‘1555555514’, ‘99%’, ‘99%’);
INSERT INTO Teacher VALUES (‘5’, ‘六六’, ‘助教’, ‘男’, ‘1555555151’, ‘99%’, ‘99%’);
INSERT INTO Teacher VALUES (‘6’, ‘七七’, ‘讲师’, ‘女’, ‘1555555515’, ‘99%’, ‘99%’);
INSERT INTO Teacher VALUES (‘7’, ‘八八’, ‘讲师’, ‘女’, ‘1555555556’, ‘99%’, ‘99%’);
INSERT INTO Teacher VALUES (‘8’, ‘九九’, ‘讲师’, ‘男’, ‘1555555557’, ‘99%’, ‘99%’);
INSERT INTO Teacher VALUES (‘9’, ‘十十’, ‘讲师’, ‘男’, ‘1555555558’, ‘99%’, ‘99%’);
INSERT INTO Teacher VALUES (‘10’, ‘测试’, ‘助教’, ‘男’, ‘1555555559’, ‘99%’, ‘99%’);
INSERT INTO Teacher VALUES (‘11’, ‘测试1’, ‘讲师’, ‘女’, ‘1555555577’, ‘99%’, ‘99%’);
INSERT INTO Teacher VALUES (‘12’, ‘测试2’, ‘助教’, ‘男’, ‘1555555787’, ‘99%’, ‘99%’);

---

– Table structure for Team

---

DROP TABLE IF EXISTS Team;
CREATE TABLE Team (
team_id int(11) NOT NULL COMMENT ‘队伍号’,
team_name varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT ‘团队名称’,
team_type varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT ‘状态’,
team_captain_sno int(255) NOT NULL COMMENT ‘队长学号’,
team_tname varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT ‘指导老师’,
team_captain_name varchar(255) COLLATE utf8_unicode_ci DEFAULT ‘’ COMMENT ‘队长姓名’,
team_time int(255) DEFAULT NULL COMMENT ‘队伍创建时间’,
Competitioninformation_id int(255) DEFAULT NULL COMMENT ‘参与大赛Id’,
PRIMARY KEY (team_id),
KEY Team_ibfk_1 (team_captain_sno),
KEY team_tname (team_tname),
KEY team_captain_name (team_captain_name),
KEY Competitioninformation_id (Competitioninformation_id),
CONSTRAINT Team_ibfk_1 FOREIGN KEY (team_captain_sno) REFERENCES Student (sno) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT Team_ibfk_2 FOREIGN KEY (team_tname) REFERENCES Teacher (tname) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT Team_ibfk_3 FOREIGN KEY (team_captain_name) REFERENCES Student (sname) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT Team_ibfk_4 FOREIGN KEY (Competitioninformation_id) REFERENCES Competition (Competitioninformation_id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

---

– Records of Team

---

INSERT INTO Team VALUES (‘1’, ‘一群人’, ‘一等奖’, ‘1’, ‘邢国波’, ‘梁恩硕’, ‘2021’, ‘1’);
INSERT INTO Team VALUES (‘2’, ‘Joker’, ‘二等奖’, ‘2’, ‘邢国波’, ‘李贺奥’, ‘2020’, ‘2’);

---

– Table structure for TeamUser

---

DROP TABLE IF EXISTS TeamUser;
CREATE TABLE TeamUser (
team_id int(255) NOT NULL COMMENT ‘团队编号’,
sno int(255) NOT NULL COMMENT ‘队员学号’,
KEY sno (sno),
KEY TeamUser_ibfk_1 (team_id),
CONSTRAINT TeamUser_ibfk_1 FOREIGN KEY (team_id) REFERENCES Team (team_id) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT TeamUser_ibfk_2 FOREIGN KEY (sno) REFERENCES Student (sno) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

---

– Records of TeamUser

---

INSERT INTO TeamUser VALUES (‘1’, ‘2’);
INSERT INTO TeamUser VALUES (‘1’, ‘3’);
INSERT INTO TeamUser VALUES (‘1’, ‘4’);
INSERT INTO TeamUser VALUES (‘1’, ‘5’);
INSERT INTO TeamUser VALUES (‘1’, ‘6’);
INSERT INTO TeamUser VALUES (‘1’, ‘1’);
INSERT INTO TeamUser VALUES (‘2’, ‘3’);
INSERT INTO TeamUser VALUES (‘2’, ‘4’);
INSERT INTO TeamUser VALUES (‘2’, ‘5’);
INSERT INTO TeamUser VALUES (‘2’, ‘6’);
INSERT INTO TeamUser VALUES (‘2’, ‘2’);

---

– Table structure for User

---

DROP TABLE IF EXISTS User;
CREATE TABLE User (
id int(11) NOT NULL,
account varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT ‘账号’,
passward varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT ‘密码’,
PRIMARY KEY (id),
KEY account (account)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

---

– Records of User

---

---

– Table structure for UserInformation

---

DROP TABLE IF EXISTS UserInformation;
CREATE TABLE UserInformation (
account varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT ‘用户账号’,
user_type varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT ‘用户类型’,
user_sex varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT ‘用户性别’,
user_id_card varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT ‘用户身份证号’,
user_name varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT ‘用户姓名’,
KEY account (account),
CONSTRAINT UserInformation_ibfk_1 FOREIGN KEY (account) REFERENCES User (account) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

---

– Records of UserInformation

---

---

– View structure for Check_award

---

DROP VIEW IF EXISTS Check_award;
CREATE ALGORITHM=UNDEFINED DEFINER=root@% SQL SECURITY DEFINER VIEW Check_award AS select Award.award_type AS award_type,Award.award_level AS award_level,Award.award_time AS award_time,count(Award.award_time) AS 数量 from Award group by Award.award_level,Award.award_type,Award.award_time ;

SELECT *
FROM Check_award
UNION
SELECT award_type,‘合计’,sum(数量) as ‘数量’
FROM Check_award
GROUP by award_type;

存储过程和触发器:
drop trigger if exists usertrigger;
CREATE TRIGGER usertrigger AFTER INSERT ON UserInformation FOR EACH ROW
BEGIN
DECLARE type1,Idcard1,name1,sex1 varchar(255);
set Idcard1 = (SELECT user_id_card FROM UserInformation WHERE account = new.account);
set name1=(SELECT user_name FROM UserInformation WHERE account = new.account);
set sex1=(SELECT user_sex FROM UserInformation WHERE account = new.account);
set type1=(SELECT user_type FROM UserInformation WHERE account = new.account);
case type1
WHEN 1 THEN INSERT INTO Student(sid_card,sname,ssex)VALUES(‘Idcard1’,‘name1’,‘sex1’);
WHEN 2 THEN INSERT INTO Teacher(tname,tsex)VALUES(‘name1’,‘sex1’);
END CASE;
END

drop PROCEDURE IF EXISTS InsertS;
CREATE PROCEDURE InsertS ( sno1 int , sid_card varchar(255),sname1 varchar(255),ssex1 varchar(255),sclass1 varchar(255))
BEGIN
insert into Student (sno,sid_card,sname,ssex,sclass) VALUES(sno1,sid_card1,sname1,ssex1,sclass1);
END
call InsertS(20,‘371414200011187542’,‘测试’,‘男’,‘软测191’,);

drop PROCEDURE IF EXISTS Updatepassward;
CREATE PROCEDURE Updatepassward (account1 varchar(255), passward1 varchar(255))
BEGIN
UPDATE User
SET passward=passward1
WHERE account=account1;
END
call Updatepassward(‘admin’,‘admin’);

drop trigger if exists setpassward;
CREATE TRIGGER setpassward BEFORE INSERT ON User
FOR EACH ROW
BEGIN
SET new.passward=‘123456’;
END

INSERT INTO User(account,passward) VALUES (‘001’,‘’);

drop PROCEDURE IF EXISTS searchStudent;
CREATE PROCEDURE searchStudent ( sid_card1 varchar(255),sname1 varchar(255),ssex1 varchar(255),sclass1 varchar(255))
BEGIN
SELECT *
FROM Student
where (sid_card=sid_card1 and sname=sname1) or (ssex=ssex1 and sclass=sclass1);
END
CALL searchStudent(‘’,‘’,‘男’,‘软测191’);

UPDATE Student
SET sclass=‘软测193’
WHERE sno=10;

DELETE
FROM Student
WHERE sno=11;

UPDATE Teacher
SET ttitle=‘助教’
WHERE tno=11;

DELETE
FROM Teacher
WHERE tno=12;

INSERT INTO Teacher(tno,tname,ttile,tsex)
VALUES (13,‘测试3’,‘讲师’,‘女’);

– 查询队伍信息

SELECT Student.sno,sname,team_name,team_time,Competition.Competitioninformation_name,team_captain_name,team_tname,team_type,award_level,award_name,award_time,award_type
FROM Student,Team,TeamUser,Competition,Award
WHERE Student.sno=TeamUser.sno AND Award.team_id=Team.team_id AND Competition.Competitioninformation_id=Team.Competitioninformation_id and Team.team_id=TeamUser.team_id AND Student.sno=2;

– 查询获奖信息

CREATE VIEW Check_award AS
SELECT award_type,award_level,award_time,COUNT(award_time)‘数量’
FROM Award
GROUP BY award_level,award_type,award_time;

SELECT *
FROM Check_award
UNION
SELECT award_type,‘合计’,sum(数量) as ‘数量’
FROM Check_award
GROUP by award_type;

SELECT award_type,award_level,数量
FROM Check_award
WHERE award_time BETWEEN 2019 AND 2020;

SELECT Student.sno,sname,sclass,ssex
FROM Student,TeamUser
WHERE Student.sno=TeamUser.sno AND TeamUser.team_id=1;

– drop trigger if exists usertrigger;
– CREATE TRIGGER usertrigger AFTER INSERT ON User FOR EACH ROW
– BEGIN
– DECLARE account1,passward1 varchar(255);
– set account1 = (SELECT account1 FROM UserInformation WHERE account = new.account);
– set passward1=(SELECT passward1 FROM UserInformation WHERE account = new.account);
– INSERT INTO UserInformation(account)VALUES(‘new.account’);
– END

– 用户角色

drop trigger if exists userinformationtrigger;
CREATE TRIGGER userinformationtrigger AFTER INSERT ON UserInformation FOR EACH ROW
BEGIN
DECLARE type1,Idcard1,name1,sex1,number1 varchar(255);
set Idcard1 = (SELECT user_id_card FROM UserInformation WHERE account = new.account);
set name1=(SELECT user_name FROM UserInformation WHERE account = new.account);
set sex1=(SELECT user_sex FROM UserInformation WHERE account = new.account);
set type1=(SELECT user_type FROM UserInformation WHERE account = new.account);
set number1=(SELECT number FROM UserInformation WHERE account = new.account);
case type1
WHEN 1 THEN INSERT INTO Student(sno,sid_card,sname,ssex)VALUES(number1,Idcard1,name1,sex1);
WHEN 2 THEN INSERT INTO Teacher(tno,tname,tsex)VALUES(number1,name1,sex1);
ELSE SET type1=0;
END CASE;
END

INSERT INTO User(account) VALUES (‘003’);
INSERT INTO UserInformation VALUES(‘003’,‘1’,‘男’,‘37142820001118752X’,‘触发器测试’,22);

INSERT INTO User(account) VALUES (‘002’);
INSERT INTO UserInformation VALUES(‘002’,‘2’,‘男’,‘37142820001118752X’,‘触发器测试’,23);

– 插入存储过程

drop PROCEDURE IF EXISTS InsertS;
CREATE PROCEDURE InsertS ( sno1 int,sid_card1 varchar(255),sname1 varchar(255),ssex1 varchar(255),sclass1 varchar(255))
BEGIN
insert into Student (sno,sid_card,sname,ssex,sclass) VALUES(sno1,sid_card1,sname1,ssex1,sclass1);
END
call InsertS(20,‘371428200011187521’,‘测试’,‘男’,‘软测191’);

– 修改密码

drop PROCEDURE IF EXISTS Updatepassward;
CREATE PROCEDURE Updatepassward (account1 varchar(255), passward1 varchar(255))
BEGIN
UPDATE User
SET passward=passward1
WHERE account=account1;
END
call Updatepassward(‘admin’,‘admin’);

– 默认密码

drop trigger if exists setpassward;
CREATE TRIGGER setpassward BEFORE INSERT ON User
FOR EACH ROW
BEGIN
SET new.passward=‘123456’;
END

INSERT INTO User(account,passward) VALUES (‘001’,‘’);

– 模糊查询

drop PROCEDURE IF EXISTS searchStudent;
CREATE PROCEDURE searchStudent ( sid_card1 varchar(255),sname1 varchar(255),ssex1 varchar(255),sclass1 varchar(255))
BEGIN
SELECT *
FROM Student
where
(sclass=sclass1 AND sname=sname1 AND ssex=ssex1 AND sid_card=sid_card1)
OR (sclass=sclass1 AND sname=sname1 AND sid_card=sid_card1) OR (ssex=ssex1 AND sname=sname1 AND sid_card=sid_card1)
OR (sclass=sclass1 AND sname=sname1 AND ssex=ssex1) OR (sclass=sclass1 AND sid_card=sid_card1 AND ssex=ssex1)
OR (sclass=sclass1 AND sname=sname1) OR (ssex=ssex1 AND sid_card=sid_card1) OR (sclass=sclass1 AND ssex=ssex1)
OR (sclass=sclass1 AND sid_card=sid_card1) OR (sname=sname1 AND ssex=ssex1) OR (sname=sname1 AND sid_card=sid_card1)
OR sclass=sclass1 OR sname=sname1 OR ssex=ssex1 OR sid_card=sid_card1;
END

CALL searchStudent(‘’,‘’,‘女’,‘’);

总结

1. 进行简单的需求分析（数据流图、数据字典、数据结构等）涉及到软件工程的一些知识。
2. 绘制数据库概念模型。
3. 用ERstudio画出E-R图。
4. 用ERstudio导出SQL语句。
5. 根据需求创建触发器、存储结构。

注:本文章仅用于参考学习，如有错误，请大家指正。