mysql内连接查询练习_SQL练习4-多表查询1 表的加法
1)union ⾃动去重
2)union all 保留重复
2 表的联结
1)内联结 inner join
根据关系(on)内容取交集：同时存在于两个表中的数据。
2)左联结 left join
取左侧数据，右侧选择与左侧⼀样的数据，通过where语句选择是否包含交集部分。
3)右联结 right join
取右侧数据，左侧选择与右侧⼀样的数据，通过where语句选择是否包含交集部分。
4)全联结 full join(mySQL不⽀持)
3 联结应⽤案例
1)查询所有学⽣的学号、姓名、选课数、总成绩
2)查询平均成绩⼤于85的所有学⽣的学号、姓名、平均成绩物联网平台开源代码是什么意思
3)查询学⽣的选课情况：学号、姓名、课程号、课程名称(三表联结)
4 case表达式
1)查询每门课程的及格⼈数和不及格⼈数
2)使⽤分段[100-85],[85-70],[70-60],[<60]来统计各科成绩，分别统计各分数段⼈数：课程ID和课程名称
5 sqlzoo 练习
1) 列出 賽事編號matchid 和球員名 player ，該球員代表德國隊Germany⼊球的
select matchid,player
from goal
where teamid = 'GER';
2) 顯⽰賽事1012的 id, stadium, team1, team2
select id,stadium,team1,team2
from game
where id = 1012;
3) 顯⽰每⼀個德國⼊球的球員名，隊伍名，場館和⽇期
select a.player, a.teamid,b.stadium,b.mdate
from goal as a left join game as b
on a.matchid = b.id
amid = 'GER';
js在线混淆加密
4) 列出球員名字叫Mario 有⼊球的 隊伍1 team1, 隊伍2 team2 和 球員名 player
am2,b.player
from game as a inner join goal as b
on a.id = b.matchid
where b.player like 'Mario%';
5) 列出每場球賽中⾸10分鐘gtime<=10有⼊球的球員player, 隊伍teamid, 教練coach, ⼊球時間gtime
分析思路：每场球赛，⾸⼗分钟⼊球：goal表，条件：gtime<10 ;
⼊球的球员：goal表，player
⼊球队伍ID：goal表，teamid
教练：eteam表，coach
表联结：goal表、eteam表，通过teamID和ID，内联结
select b.ime
from goal as b inner join eteam as c
amid = c.id
ime <= 10;
6) 列出'Fernando Santos'作為隊伍1 team1 的教練的賽事⽇期，和隊伍名。
分析思路：'Fernando Santos'教练：eteam表，条件：coach='Fernando Santos'；
参赛⽇期：game表，mdate；
队伍名：eteam表，teamname；
表联结：game表、eteam表，通过team1与ID，实现内联结
select a.amname
from game as a inner join eteam as c
am1 = c.id
ach = 'Fernando Santos';进制转换公式c语言
7) 列出場館 'National Stadium, Warsaw'的⼊球球員。场馆：game表，条件：stadium='National Stadium, Warsaw'；⼊球球员：goal表，player；
表联结：game、goal，通过id、matchid，实现内联结
select b.player
from game as a inner join goal as b
on a.id = b.matchid
where a.stadium = 'National Stadium, Warsaw';
8) 列出全部賽事，射⼊德國⿓⾨的球員名字。球员名字：goal表，player；
表联结：game、goal，通过id、matchid，实现内联结；
射⼊德国龙门：条件：1)goal中teamid不等于'GER'；2)对应game中 team1 or team2 有'GER'；
distinct去掉重复球员
select distinct b.player
from game as a inner join goal as b
on a.id = b.matchid
amid <> 'GER'
and (a.team1='GER' am2='GER');
9) 列出隊伍名稱teamname和該隊⼊球總數队伍名称：eteam表，teamname
⼊球总数：goal表，count(gtime)
每个队伍：分组，goal表的teamid
表联结：goal、eteam，通过teamid、id，实现内联结
amname,ime)
from goal as b inner join eteam as c
amid = c.id
group amname;
10) 列出場館名和在該場館的⼊球數字。场馆名：game表，stadium
⼊球数字：goal表，count(⼊球时间)
每个场馆⼊球数字：分组：game表stadium
表联结：game、goal表，通过id、matchid，实现左联结(所有场馆)mysql面试题sql语句多表联查
select a.stadium,count(gtime)
from game as a left join goal as b
on a.id = b.matchid
group by a.stadium;
11) 每⼀場波蘭'POL'有參與的賽事中，列出賽事編號 matchid, ⽇期date 和⼊球數字每⼀场'POL'参与的赛事：game表，team1='POL'或 team2='POL'
赛事编号：goal表，matchid
⽇期：game表，mdate
每场赛事⼊球数字：根据matchid、mdate分组，计算⼊球总数count(gtime)
表联结：game、goal，通过id、matchid，实现内联结
select a.id,a.mdate,ime)
from game as a inner join goal as b
on a.id = b.matchid
am1='POL' am2='POL'
group by a.id, a.mdate;
12) 每⼀場德國'GER'有參與的賽事中，列出賽事編號 matchid, ⽇期date 和德國的⼊球數字赛事编号：goal表，matchid
一二三乱码入口
⽇期：game表，mdate
德国⼊球数字：条件筛选德国队伍，teamid = 'GER'；根据matchid分组，计算⼊球总数count(gtime)
表联结：game、goal，通过id、matchid，实现内联结
select a.id, a.mdate, count(gtime)
from game as a inner join goal as b
on a.id = b.matchid
amid = 'GER'
group by a.id, a.mdate;
13) 列出每⼀场⽐赛以及每⽀球队的进球数，结果按照mdate, matchid, team1 and team2排序每⼀场⽐赛每⽀球队的进球数：每场⽐赛有两⽀球队，team1、team2，分别汇总team1、team2 进球数；
- 构想联结后的表格，根据赛事分组后，当team1=teamid时，表⽰team1在该场赛事进球1次，使⽤case语句计数求和，得到该场⽐赛team1的进球总和：
sum(case when team1=goal.matchid then 1 else 0 end) as 'score1'
sum(case when team2=goal.matchid then 1 else 0 end) as 'score2'
- 每⼀场⽐赛：按赛事id、mdate、team1、team2分组
表联结：game、goal，通过id、matchid，实现左联结(所有赛事)
排序：order by mdate, matchid, team1 and team2
select a.am1,
sum(case amid then 1 else 0 end) as 'score1',
team2,
sum(case amid then 1 else 0 end) as 'score2'
jdk环境变量配置win2012
from game as a left join goal as b
on a.id = b.matchid
group by a.id,a.am2
order by a.mdate,a.am2;

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。