1、设计要求

案例2: 校运动会系统

校运动会中有各类比赛，每一比赛类别有类别编号、类别名称和主管属性，每一比赛类别包含很多个比赛项目；每一比赛项目有项目编号、项目名称、比赛时间和级别属性；各个系团队有团队编号、团队名称、领队属性，每一代表团由多名运动员组成，运动员有编号，姓名，年龄，性别等属性；每一名运动员可以参加多个比赛项目，每一比赛项目也有多名运动员参加，运动员参加比赛有成绩属性。

2、ER图绘制

ddl代码

SET foreign_key_checks = 0;



drop table if exists competitionType;

drop table if exists competition;

drop table if exists team;

drop table if exists player;

drop table if exists results;



CREATE TABLE competitionType(

typeid int COMMENT '类型编号' AUTO_INCREMENT,

type_name   varchar(40) COMMENT '类型名称',

manager varchar(40) COMMENT '主管名称',

PRIMARY KEY (typeid) 

);



CREATE TABLE competition(

cid int COMMENT '比赛编号' AUTO_INCREMENT,

gender  int COMMENT '比赛性别，1表示男，0表示女' DEFAULT 1,

cname varchar(40) COMMENT '比赛名称',

ctime DATETIME COMMENT '比赛时间',

typeid int COMMENT '比赛类别编号',

PRIMARY KEY (cid),

FOREIGN KEY (typeid) REFERENCES competitionType(typeid) on delete set null

);



CREATE TABLE team(

tid int COMMENT '团队编号' AUTO_INCREMENT,

tname   varchar(40) COMMENT '团队名称',

leader varchar(40) COMMENT '领队名称',

PRIMARY KEY (tid)

);



CREATE TABLE player(

pid int COMMENT '运动员编号' AUTO_INCREMENT,

pname   varchar(40) COMMENT '姓名',

age int COMMENT '年龄',

gender int COMMENT '性别，1表示男，0表示女' DEFAULT 1,

grade int COMMENT '年级级别',

tid int COMMENT '所属团队编号',

PRIMARY KEY (pid),

FOREIGN KEY (tid) REFERENCES team(tid) on delete set null

);



CREATE TABLE results(

pid int COMMENT '运动员编号',

cid int COMMENT '比赛编号',

score int COMMENT '成绩',

PRIMARY KEY (pid, cid),

FOREIGN KEY (pid) REFERENCES player(pid),

FOREIGN KEY (cid) REFERENCES competition(cid)

);



SET foreign_key_checks = 1;

3、数据字典

在华为云gaussdb数据库中，在执行了上述ddl代码之后，使用“库管理”->“数据字典”->“导出PDF”，即可获得可视化的数据结构以检查数据定义的正确性
以下为本次设计导出的结果

4、业务需求实现sql设计

获取所有团体总成绩，并按总成绩排列展示

delimiter $$
drop procedure if exists teamInfo;
create procedure teamInfo()
begin
	select tname as 院系名称, ifnull(sum(score),0) as 总分
	from team team left join  (select * from player natural join results) as pr on pr.tid = team.tid
	group by team.tid
	ORDER BY sum(score) desc;
end;
$$
delimiter;

展示所有比赛项目的所有参赛人员及其参赛成绩

delimiter $$
drop view if exists competitionInfo;

create view competitionInfo as
with compe(cid, cname2) as (
select cid, 
CONCAT(CASE gender 
		when 1 then "男子" 
		else "女子" END ,cname)  as cname2
from competition)
select cname2 as 项目名称, pname as 运动员, score as 成绩 
from results natural join compe natural join player 
 order by cname2 desc, score desc;
$$
delimiter;

男子只能参加男子比赛，女子只能参加女子比赛

delimiter $$
drop trigger if exists  results_insert_before_trigger;
create trigger results_insert_before_trigger BEFORE INSERT
ON results for each row
BEGIN
	declare pgender int;
	declare cgender int;
	select gender into pgender from player where new.pid = pid;
	select gender into cgender from competition where new.cid = cid;
	if (cgender <> pgender)
			then insert into mytable values(0);
			end if;
end;
$$
delimiter;

5、使用华为云gaussdb

通过华为云GaussDB for MySQL平台进行数据库搭建。
首先，进入官网，点击右上方控制台，进入控制台页面：

接着，在页面左上角单击服务列表，选择“数据库 > 云数据库 GaussDB(for MySQL)”。进入云数据库 GaussDB信息页面：

没有资源，则选择右上方购买数据库实例

通过个人账号按需购买GaussDB数据库，然后等待数据库初始化后登陆，测试连接成功后进入数据库。

6、gaussdb执行上述数据库设计对应效果

打开控制台，点击登录进入数据库实例，新建数据库

执行前述所有sql代码

可以看到左侧表的生成，根据前述3中的操作即可导出数据字典

点击上方数据方案->自动生成测试数据；

新建多个任务添加测试数据，但是在添加compettition表时出错

究其原因是前述的业务3设计的触发器生效了，男子不能参加女子比赛，女子不能参加男子比赛，

选择手动插入一条数据，而后执行业务1查询团队总成绩 和业务2 查询个人成绩，可以看到如下效果