练习:
创建三个表和对应的主外键约束
create table sporter 运动员
主键 姓名 性别 代表队
1 博尔特 男 牙买加,
2 苏炳添 男 中国,
3 伊辛巴耶娃 女 俄罗斯,
4 范尼凯克 男 南非,
5 刘翔 男 中国,
6 罗伯斯 男 古巴,
7 苏尔 女 美国,
8 盖伊 男 美国,
9 加特林 男 美国,
10 张培萌 男 中国,
11 席尔瓦 女 巴西
create table grade 成绩 ( 第一 7分 , 第二 4 , 第三 2 , 其他名次0 )
运动员 项目 成绩
1 101 7,
1 103 4,
1 104 2,
1 105 4,
2 101 2,
3 106 7,
8 101 0,
8 103 2,
9 101 4,
4 103 7,
4 105 7,
5 104 7,
6 104 4,
7 106 2,
10 101 0,
11 106 4
create table items 参赛项目
项目主键 项目名称 所在场地
101 男子100M 诺坎普,
103 男子200M 温布利,
104 男子110M栏 鸟巢,
105 男子400M 卢日尼基,
106 女子撑杆跳 温布利
完成以下操作:
1. 查询每个运动员所获得的总分数 (使用sum(),按运动员进行分组)
2. 查询分数最高的运动员的名字 (先通过sum()查询出所有运动员总积分表,用子查询嵌套使用max()聚合运动员总积分表)
3. 查询每个运动员的金牌数 (金牌数即所得分为7分者,使用lefe join()避免漏掉没获得金牌数的运动员,统计金牌数为count(g.grade = 7))
创建三个表和对应的主外键约束
create table sporter 运动员
主键 姓名 性别 代表队
1 博尔特 男 牙买加,
2 苏炳添 男 中国,
3 伊辛巴耶娃 女 俄罗斯,
4 范尼凯克 男 南非,
5 刘翔 男 中国,
6 罗伯斯 男 古巴,
7 苏尔 女 美国,
8 盖伊 男 美国,
9 加特林 男 美国,
10 张培萌 男 中国,
11 席尔瓦 女 巴西
create table grade 成绩 ( 第一 7分 , 第二 4 , 第三 2 , 其他名次0 )
运动员 项目 成绩
1 101 7,
1 103 4,
1 104 2,
1 105 4,
2 101 2,
3 106 7,
8 101 0,
8 103 2,
9 101 4,
4 103 7,
4 105 7,
5 104 7,
6 104 4,
7 106 2,
10 101 0,
11 106 4
create table items 参赛项目
项目主键 项目名称 所在场地
101 男子100M 诺坎普,
103 男子200M 温布利,
104 男子110M栏 鸟巢,
105 男子400M 卢日尼基,
106 女子撑杆跳 温布利
完成以下操作:
1. 查询每个运动员所获得的总分数 (使用sum(),按运动员进行分组)
2. 查询分数最高的运动员的名字 (先通过sum()查询出所有运动员总积分表,用子查询嵌套使用max()聚合运动员总积分表)
3. 查询每个运动员的金牌数 (金牌数即所得分为7分者,使用lefe join()避免漏掉没获得金牌数的运动员,统计金牌数为count(g.grade = 7))
4. 查询每个代表队的积分总和,积分总和排序 (使用sum(),并对代表队进行分组)
5. 场地--“温布利”,所进行的项目名称以及冠军获得者 (通过g.grade = 7和‘温布利’可以推出)
6. 查询所参加项目平均分数不低于4分的运动员编号 (不需要考虑项目名,按成员分组,去运动员的avg(),通过having来筛选出平均分不低于4分)
7. 查询每个运动员的名字以及其参加项目的名字 (简单)
5. 场地--“温布利”,所进行的项目名称以及冠军获得者 (通过g.grade = 7和‘温布利’可以推出)
6. 查询所参加项目平均分数不低于4分的运动员编号 (不需要考虑项目名,按成员分组,去运动员的avg(),通过having来筛选出平均分不低于4分)
7. 查询每个运动员的名字以及其参加项目的名字 (简单)
8. 统计每个代表队的队员数,查出:代表队名称、队员数 (以代表队分组,使用count(1))
9. 查询男、女运动员各有多少个 (通过sex分组,用count(1))
10. 美国队运动和中国队运动员打起来了,现组委会让中国队的每个运动员和美国队每个运动员PK,请查出对阵表 显示如下: (利用一张表可赋多次,求出两个队表的笛卡尔积)
刘翔 盖伊
刘翔 加特林
刘翔 苏尔
苏炳添 盖伊
苏炳添 加特林
苏炳添 苏尔
张培萌 盖伊
张培萌 加特林
张培萌 苏尔
9. 查询男、女运动员各有多少个 (通过sex分组,用count(1))
10. 美国队运动和中国队运动员打起来了,现组委会让中国队的每个运动员和美国队每个运动员PK,请查出对阵表 显示如下: (利用一张表可赋多次,求出两个队表的笛卡尔积)
刘翔 盖伊
刘翔 加特林
刘翔 苏尔
苏炳添 盖伊
苏炳添 加特林
苏炳添 苏尔
张培萌 盖伊
张培萌 加特林
张培萌 苏尔
11.显示每个项目的名称以及前三名的运动员名字,如下显示: (建立冠军、亚军、季军三个表,再将三个表通过项目名 进行连接,通过分数区分
冠军、亚军、季军)
项目名称 冠军 亚军 季军
男子100M 博尔特 加特林 苏炳添
男子110栏 刘翔 罗伯斯 博尔特
男子200M XXX XXX XXX
...
12. 查询每个代表队中积分排名第二的运动员 (子嵌套,多重子嵌套,可考虑使用limit)
项目名称 冠军 亚军 季军
男子100M 博尔特 加特林 苏炳添
男子110栏 刘翔 罗伯斯 博尔特
男子200M XXX XXX XXX
...
12. 查询每个代表队中积分排名第二的运动员 (子嵌套,多重子嵌套,可考虑使用limit)
13. 找出报名人数最少的项目,并取消该项目 (delete from 表名 where 条件)(在修改关联外键表数据时,需要先修改从关联外键表中的数据,才能再修改关联外键表中的数据)
14. 由于“加特林”使用违禁药物,所有成绩积分清0 (delete from 表名 where 条件)