day03、多表查询

子查询
子查询又叫嵌套查询。它通常可以位于SELECT后面 FROM后面 WHERE后面,共三种使用场景。
场景一: select后面
语法:
select 字段名,(查询语句) from 表名;
如 : 查询所有学生的信息并显示老师的名字
select ,(
select name from teacher where id=teacher_id
) as teacher_name from student ;
如: 查询每个老师的学生的 最大分数,最小分数,平均分数,分数总和,学生人数,老师名字
select max(score),min(score),sum(score),avg(score),count(),(
select name from teacher where id=teacher_id
) as teacher_name from student group by teacher_id ;
注意:
当位于SELECT后面时，要注意
1.一定要在两个表之间找好对应关系（teacher.id必须是主键或者必须保证teacher.id在teacher表中是唯一的）
2.子查询中只能有一个字段（子查询的结果必须是一行一列）
使用子查询的时候，建议大家养成使用别名的好习惯，这样可以让我们的查询语句更加清晰。别名可以用来命令新字段，也可以用来命名新表.
场景二: from后面
还是学生表student，我们要将成绩进行分级，并且显示汉字的分级与字母的分级。这里可以使用子查询。相当于给student“新增”了2个字段
如: 使用子查询 对成绩划分等级, score<60 ,评级C 并且是差,score>=60 且 score<80 评级B并且是良,score>=80 评级是A并且是优

select *,
case rank
when 'A' then '优'
when 'B' then '良'
when 'C' then '差'
end rank_ch
from (
select *,
case 
when score < 60 then 'C' 
when score >=60 and score <80 then 'B' 
when score >=80 then 'A' 
end as rank 
from student
) a;

注意:
当位于FROM后面时，要注意
1.我们可以把子查询当成一张表
2.必须要有别名,因为子查询优先被执行,子查询的别名,可以让别的查询当做表或者列去操作
场景三: where后面
如: 在不知道teacher_id 和 老师名字的对应关系的情况下,想查询出张老师下面的所有学生信息
select * from student where teacher_id in (
select id from teacher where name=‘张老师’
);
注意:
当位于WHERE后面时，要注意

1. 多条数据要用in而不要用=,如果确定子查询的结果为一行一列的话,就可以用 = 等于
2. 如果返回结果为多行一列的话要用in , 一列是必须的,必须是一列
   3.子查询中的SELECT后面只能有一个字段（多个字段的话会报错）
   行转列、
   示例数据准备
   有id,名字,课程,成绩

create table test_9(
	id int,
	name varchar(22),
	course varchar(22),
	score decimal(18,2)
);
insert into test_9 (id,name,course,score)values(1,'小王','java',99);
insert into test_9 (id,name,course,score)values(2,'小张','java',89.2);
insert into test_9 (id,name,course,score)values(3,'小李','java',88);
insert into test_9 (id,name,course,score)values(4,'小王','MySQL',92.2);
insert into test_9 (id,name,course,score)values(5,'小张','MySQL',42.2);
insert into test_9 (id,name,course,score)values(6,'小李','MySQL',59.2);

查询结果

想要的结果

1、场景一(多行转一行多列)
可以使用下面的SQL语句（group by 与 case when结合使用即可实现）：

select name,max(
case course
when 'java' then score 
end) Java, max(
case course
when 'MySQL' then score 
end) MySQL 
from test_9
group by name;

利用了max函数的内置循环
2、场景二(多行转一行一列)
想要的结果

相关函数
concat(值,’拼接符’,值 ) : 拼接,多行数据只会拼接一行
group_concat(值,’拼接符’,值 ) : 拼接,多行压扁到一行
第一步：拆分问题，先按分组的思路

select name,1 as ‘各科成绩’ from test_9 group by name;

第二步：将课程名与成绩拼接成一列

select name,
concat(course,'=',score) as '各科成绩' 
from test_9 group by name;

第三步：利用group_concat函数将多行压扁到一行

select name,
group_concat(course,'=',score) as '各科成绩' 
from test_9 group by name;

第四步：修改分隔符（默认是逗号）

select name,
group_concat(course,'=',score separator ' | ') as '各科成绩' 
from test_9 group by name;

第五步：按课程名称排序

select name,
group_concat(course,'=',score order by course asc  separator ' | ') as '各科成绩' 
from test_9 group by name;

DQL-连接查询
MYSQL支持的表连接查询有inner join，left join，right join（right join我们工作中基本不用）
我们有一张用户的基本信息表，我们还有一张用户的订单表
现在我们要求在页面上展示，所有用户的订单记录
这种情况下我们就必须使用left join了，因为inner join 会丢数据
假设基本信息表中有A B C三个用户（3条记录）
这种情况下，我们除了使用left join外，还必须要让基本信息表当左表，订单表当右表。
● 内连接inner join

select *
from student stu
inner join teacher tea on tea.id = stu.teacher_id;

左连接

select * from student s
    left join teacher t on s.teacher_id = t.id;

以左边的表为基准,左表中数据都有,右表中不符合条件的就没有,就在指定列上用null代替
生成视图的时候,也是先生成左表的数据