查询出编号长度大于4的code_SQL多表查询

1.表的加法：加法（union）:把两个表和在一起

/*
select 课程号,课程名称 
from course
union all
select 课程号,课程名称 
from course1;
*/

2.表的连接：表和表之间通过列产生对应关系的，连接是将表和表的关系合并在一起的操作

什么时候用哪一种联结呢？

当实际业务过程中想要生成固定业务的表单，或者特别说明了要那一张表里的全部数据的时候，会使用左联接或右连接。其他情况都用内连接，来获取两个表的公共部分。连接是在from子句中加入联结，不影响sql的运行语句。

/*
内连接，
*/
select x.学号,y.姓名,x.课程号
from score as x inner join student as y
on x.`学号`=y.`学号`;

/*
左连接，左侧的表为主表，左侧的表全部取出来，右边的表只取出与左边的表相同学号的行
*/
select x.学号,x.姓名,y.课程号 from student as x left join score as y 
on x.学号=y.学号;

/*
左连接,去掉两个表共同的部分
*/
select x.学号,x.姓名,y.课程号 from student as x left join score as y 
on x.`学号`=y.`学号`
where y.课程号 is NULL;

/*
右连接，
*/
select x.学号,x.姓名,y.课程号 from student as x RIGHT join score as y 
on x.学号=y.学号;

/*
右连接,左边表的职位空值
*/
select x.学号,x.姓名,y.课程号 from student as x RIGHT join score as y 
on x.学号=y.学号
where x.学号 is NULL;

/*
查询所有学生的学号，姓名，选课数，总成绩
分析：学生的学号，姓名来自student
选课数和总成绩，课程数，成绩要从score表，并且按学生分组求课程数，和总成绩
因为要所有学生，所以用全部的student表，左连接，课程号连接
*/
select a.学号,a.姓名,count(b.`课程号`)as 选课数,sum(b.成绩)as 总成绩
from student a left join score b on a.学号=b.学号
group by a.学号;

/*
查询平均成绩大于85的所有学生的学号,姓名和平均成绩
平均成绩来源与score表中的成绩,按照学号分组求平均,分组后平均成绩大于85
学号和姓名来自于student表
两个表的连接因为是所有学生所以用student全部数据，连接条件为学生号
*/
select a.学号,a.姓名,avg(b.成绩)as 平均成绩
from student a left join score b on a.学号=b.学号
group by a.`学号`
HAVING avg(b.成绩)>85;

/*
查询学生的选课情况：学号，姓名，课程号，课程名称
学号，姓名来自student表
课程号和课程名称来自course表
student 表和course没有直接连接，可以通过score表连接，
student和score通过学生号连接，score和course通过课程号连接
*/
select a.学号,a.姓名,c.课程号,c.课程名称
from student a inner join score b on a.学号=b.学号
inner join course c on b.课程号=c.课程号;

3.case表达式

/*
查询出每位同学是否及格
*/
select a.学号,b.课程号,b.成绩,
(case when 成绩>=60 then '及格'
when 成绩<60 then '不及格'
else null end 
)as '是否及格'
from student a left join score b on a.学号=b.学号;

/*
查询出每门课程的及格人数和不及格人数
*/
select 课程号,sum(case when 成绩 >=60 then 1 else 0 end )as '及格人数',sum(case when 成绩<60 then 1 else 0 end )as '不及格人数'
from score
group by 课程号;

/*
使用分段【100-85】【85-70】【70-60】【<60】
来统计各科成绩，分别统计个分数段人数，课程号和课程名称
分析：分成【100-85】【85-70】【70-60】【<60】实际上是列名，在select语句中体现用case when 函数
各科成绩用课程号分组
统计个分数段人数，利用sum函数汇总
课程号和课程名称来自course表，成绩来自score,各科成绩 故课程应完全展现用左连接，连接列为课程号
*/
select a.课程号,a.课程名称,sum(case when 成绩 between 85 and 100 then 1 else 0 end )as '【100-85】',
sum(case when 成绩 between 70 and 85 then 1 else 0 end )as '【85-70】',
sum(case when 成绩 between 60 and 70 then 1 else 0 end )as '【70-60】',
sum(case when 成绩 <60 then 1 else 0 end )as '【<60】'
from course a left join score b on a.`课程号` =b.`课程号`
group by a.课程号,a.`课程名称`;

4.练习

/*
 1.查找姓名中以Mario开头的进球球员，符合条件球员参加比赛的对战双方
分析：where子句进球球员姓名=Mario ,goal表
符合条件的比赛 matchid goal表
的对战双方 是在game 表team1,team2 通过id比赛编号=matchid连接
用到两个表 goal 和game 左链接
出现的错误：where player='Mario%';应用模糊查询
*/
select b.team1,b.team2
 from goal a left join game b on a.matchid=b.id比赛编号
where player like'Mario%';

/*
5. 查找进球球员的姓名、球队编号、教练、多长时间进球。要求多长时间进球<=10分钟
分析：进球球员的姓名goal.player，球队编号goal.teamid,教练eteam.coach,多长时间进球goal.gtime
用了两个表goal ,eteam因为查询进球球员的姓名故用goal 表全部数据,用goal.temid=eteam.id
要求筛选where 子句gtime<=10
*/
select a.player,a.teamid,b.coach,a.gtime
from goal a left join eteam b on a.teamid=b.id
where a.gtime<=10;

/*
6. 列出'Fernando Santos'作為隊伍1 team1 的教練的賽事日期，和隊伍名。
分析：教练为eteam表中的coach='Fernando Santos',比赛日期mdate，球队编号team1,来自game表
用到两个表，eteam ,game team1和id是关联列
*/
select a.mdate,c.teamname
from game a  inner join eteam c on a.team1=c.id
where c.coach='Fernando Santos' ;

/*
列出場館 'National Stadium, Warsaw'的入球球員。
*/
select player 
from game a inner join goal b on a.id=b.matchid
where stadium ='National Stadium, Warsaw'

/*
射入德国球门的球员姓名
分析：球员姓名来自goal.player
条件 射入德国球门team1 ,team2有ger 但是teamid 不是ger
*/
select distinct b.player from game a INNER join goal b on a.id=b.matchid
where  'GER' in (a.team1 or a.team2) and b.teamid <>'GER';

/*
9. 查找出球队名称，和每个球队进球人数
分析按球队分组，进球的人数
球队名称在eteam,进球个数在goal表，注意括号英文检查
*/
SELECT a.teamname,count(b.player)
from eteam as a inner join goal  as b ON a.id=b.teamid
 group BY a.teamname

/*
場館名和在該場館的入球數字。
分析：场馆名在game表，入秋数字在goal表
两个表连接通过midchid，和id
按a.id ,mdate分组，求和sum(*)
*/
select a.stadium,count(b.player)
from game a  inner join goal b on a.id=b.matchid
group by a.stadium 

/*
每一場波蘭'POL'有參與的賽事中，列出賽事編號 matchid, 日期date 和入球數字。
注意：分组按a.id和a.mdate一起分组
*/
select a.id,a.mdate, count(b.player)
from game as a inner join goal as b on a.id = b.matchid 
where (team1 = 'POL' OR team2 = 'POL')
group by a.id,a.mdate;

/*
每一場德國'GER'有參與的賽事中，列出賽事編號 matchid, 日期date 和德國的入球數字。
分析：每一场德国有参与的赛事，也就是 team1='GER'OR team2='GER'
两个表连接 
德国的入球数字 teamid='GER'
*/
SELECT b.matchid,a.mdate,count(b.player)
from game a join goal b on a.id=b.matchid
where( team1='GER'OR team2='GER')and b.teamid='GER'
group by a.id,b.matchid,a.mdate

/*
13
*/
SELECT a.mdate,a.team1,
sum(CASE WHEN b.teamid=a.team1 THEN 1 ELSE 0 END )score1,
a.team2,sum(CASE WHEN b.teamid=a.team2 THEN 1 ELSE 0 END )score2
 FROM game  a JOIN goal b ON b.matchid = a.id
group by a.id,a.mdate,a.team1,a.team2
order by a.mdate