数据分析—mysql多表查询

Vicky

风控

数据分析—mysql多表查询

1.表的加法：union

1.1 表的加法：union, unionl连接的两个表的结构需要一样。

1.2 union 重复的数据行去重；union all 保留重复的数据行；

1.3实例：

-- 1.3.1 表的加法 union ,union 重复的数据行去重.

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

-- 1.3.2 表的加法 union ,union all 保留重复的数据行；

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

2.表的联结：

表和表之间是通过列产生关系的。

2.1 表的联结：交叉联结

2.1.1交叉联结概述

交叉连接也叫笛卡尔积，cross join，交叉连接是表中的每一行，都与另一个表中的每一行合并在一起。

如图：

表一有三行数据，用1,2,3分别表示表中的三行数据，

表二有两行数据，用a,b分别表示表中的两行数据，

表一和表二进行交叉联结的过程，是这样的，

表一的第一行数据分别与表二中的两行合并在一起，这样就产生了两行数据；

表一的第二行数据分别与表二中的两行合并在一起，这样又产生了两行数据；

表一的第三行数据分别与表二中的两行合并在一起，这样又产生了两行数据；

交叉连接结果的行数，是两张表中行数的成绩，表一有三行数据，表二有两行，所以他有3*2= 6行数据。

交叉连接在生活的体现：扑克牌

扑克牌有4种花色，有13张牌，4*13=52张牌，加上大小王，合计54张牌。

交叉联结的业务应用

交叉连接在实际业务中应用比较少，其结果行数太多，没有什么价值，那为什么使用呢，是因为交叉连接是所有连接的基础，其他的连接都是在此基础上增加了过滤条件。

2.2表的联结：内联结

定义：查找出同时存在于两张表中的数据，inner join。

练习：查询学生的学号,姓名和课程号

第一步：

1.两个表通过学号进行内连接，将两个表中都有的学号所在的行都取出来。

2.学生表里取出的是学号0001这一行，成绩表中0001有两行取出两行数据，

第二步：将两个表里取出的数据通过交叉联结方式合并，

将成绩表001这一行，分别与成绩表中两个合并，最终产生两行数据，合并后的表有了两个表里的全部信息。

CREATE TABLE if not EXISTS score1(
`学号` VARCHAR(50),
`课程号` VARCHAR(50),
`成绩` int,
PRIMARY KEY(`学号`,`课程号`)
INSERT into score1 VALUES
('0001','0001',80),
('0001','0002',90),
('0005','0003',60)
CREATE TABLE IF NOT EXISTS student1(
`学号` int PRIMARY KEY,
`姓名` VARCHAR(50),
`出生日期` VARCHAR(50),
`性别` VARCHAR(50)
insert into student1 VALUES
('0001','猴子','1989-01-01','男'),
('0002','猴子','1990-12-21','女')
# 语句：
select a.学号,a.姓名,b.课程号
from student1 as a 
inner join score1 as b
on a.学号 = b.学号;

2.3表的联结：左联结

定义：左联结将左侧表中的数据全部取出来

分析：

第一步：两个表通过学号产生了匹配关系，左侧的表做为主表，全部被读取出来，右边的表中只选出和左边表相同学号的行，也就是学号0001所在的两行数据，

第二步：将两个表取出的数据通过交叉联结方式做合并，因为学号0002在右边的表里没有对应的行，所以这一行对应的列的值是空值。

如图：

语句：

select a.学号,a.姓名,b.课程号
from student1 as a 
left join score1 as b
on a.学号 = b.学号;

2.3.1 去除重合数据的左联结。

如图：

语句：

select a.学号,a.姓名,b.课程号
from student1 as a 
left join score1 as b
on a.学号 = b.学号
where b.课程号 is null

2.4表的联结：右联结

定义：右联结将右侧表中的数据全部取出来

运行：

第一步：两个表通过学号产生了匹配关系，右侧的表做为主表，全部被读取出来，左边的表中只选出和右边表相同学号的行，也就是学号0001所在的一行数据。

第二步：将两个表取出的数据通过交叉联结方式做合并，因为学号0005在右边的表里没有对应的行，所以这一行对应的列的值是空值。

如图：

语法：

select a.学号,a.姓名,b.课程号
from student1 as a 
right join score1 as b
on a.学号 = b.学号;

2.4.1 去除重合数据的右联结。

如图：

select a.学号,a.姓名,b.课程号
from student1 as a 
right join score1 as b
on a.学号 = b.学号
where a.学号 is null

2.5 表的联结：全联结

mysql 不支持全联结，理解概念即可。

定义：全联结查询结果返回左表和右表中的所有行，如果某一行和另一个表中行有匹配的时候了，两个行进行合并，如果某一行和另一个表中没有匹配的行的时候，另一个表的值对应的地方用空值填充。

3.sql解决业务的三个步骤：

练习1：查询所有学生的学号，选课数，总成绩。

select st.学号,count(s.课程号) 选课数,sum(s.成绩) 总成绩
from student st 
left join score s 
on s.学号 = st.学号
GROUP BY st.学号

练习2：查询平均成绩大于85分的所有学生的学号和平均成绩

第一步：翻译

1.查询出所有学生的学号和平均成绩。

学号(student 表)
平均成绩(每个学生的平均成绩：在score表中，按照学号分组；平均成绩：avg(成绩))

2.平均成绩>85

第二步：分析

第三步：sql语句

select st.学号,avg(s.成绩) 平均成绩
from student st
left join score s
on st.学号 = s.学号
GROUP BY st.学号
having avg(s.成绩) >85;

第四步：结果

-- 练习3：查询学生的选课情况：学号，姓名，课程号，课程名称。

第一步：翻译

1.查询出所有学生的学号，姓名，课程号，课程名称

学号、姓名(student )
课程号、课程名称(course）

2.student做主表进行左联结(left join)

3.通过score表对student表和course表进行连接。

student与score（学号）
course 与score（课程号)

第二步：分析

第三步：sql语句

select st.`学号`,st.`姓名`,c.`课程号`,c.`课程名称`
from student st 
left join score s on st.学号 = s.学号
left join course c on s.`课程号` = c.`课程号`

第四步：查询结果展示

4. case表达式

4.1 case概述

定义：使用case表达式可以帮助我们解决复杂的查询问题，case表达式的作用就相当于进行一个条件判断的函数，用来判断每一行是不是满足某个条件。

语法：

CASE WHEN <判断表达式> THEN <表达式1>
     WHEN <判断表达式> THEN <表达式2>
     WHEN <判断表达式> THEN <表达式3>
     ELSE <表达式>
END

练习1：查询学生成绩是及格还是不及格(>=80分及格）;

select 学号,课程号,成绩,
(case when 成绩>=80 then '及格'
      when 成绩<80 then '不及格'
			else null 
end) as 是否及格
from score

练习2：查询每门课程的及格人数，和不及格人数。

select 课程号,
sum(case when 成绩>=80 then 1 else 0 end) 及格人数,
sum(case when 成绩<80 then 1 else 0 end) 不及格人数
from score
GROUP BY 课程号

4.2 case表达式注意事项：

1.else 可以省略不写，默认为空值，但是不建议省略。

2.end 不可以省略。

3.case表达式可以写在 sql表达式的任意子句里面

4.3 应用场景：

当有多种情况需要条件判断的时候可以使用case表达式。

练习1：使用分段[100-85],(85-71],[70-60],(<60]来统计各科成绩，分别统计各分数段人数，课程号和课程名称。

第一步：翻译

1.对课程号和课程名称分组，group by 课程号,课程名称。

2.根据要求对成绩进行分段。

[100-85] 成绩<=100 and 成绩>=85 同 between 85 and 100

[85-71] 成绩< 85 and 成绩>=70

[70-60] 成绩< 70 and 成绩>=60

[[<60] 成绩<60

3.对分组和分段数据进行笛卡尔积连接。

第二步：分析

第三步：sql语句

select c.课程号,c.课程名称,
sum(case when 成绩<=100 and 成绩>=85 then 1 else 0 end) '[100-85]',
sum(case when 成绩< 85  and 成绩>=70 then 1 else 0 end) '[85-71]',
sum(case when 成绩< 70 and 成绩>=60 then 1 else 0 end) '[70-60]',
sum(case when 成绩<60 then 1 else 0 end) '[<60-5]'
from course c 
join  score s on c.课程号 = s.课程号
GROUP BY c.课程号,c.课程名称

sqlzoo练习：The JOIN operation/zh。 The JOIN operation/zh

1.SELECT matchid, player  FROM goal 
  WHERE teamid = 'GER'

2.SELECT id,stadium,team1,team2
  FROM game where id = '1012'

3.SELECT go.player,go.teamid,ga.stadium,ga.mdate
 FROM game  ga
JOIN goal  go
ON (id=matchid)
where go.teamid = 'GER'

4.select team1,team2 ,player
from game 
join goal 
on (id=matchid)
where player LIKE 'Mario%'

5.SELECT player, teamid,coach, gtime
FROM goal 
join eteam 
on teamid=id
WHERE gtime<=10

6.select g.mdate,e.teamname
from game g
join eteam e